Relational Models II – SS2 Data Processing Lesson Note (Second Term)

What are integrity constraints in the relational model?

An integrity constraint (IC) is a condition specified on a database schema and restricts the data that can be stored in an instance of the database. If a database instance satisfies all the integrity constraints specified on the database schema, it is a legal instance. A DBMS permits only legal instances to be stored in the database.

Behavioural objectives

By the end of the lesson, students should be able to:

• explain what an integrity constraint and a legal instance are;
• name the four types of integrity constraints and explain domain, entity and referential integrity;
• list the four types of key constraints;
• write a simple SELECT query in Microsoft Access that returns, restricts and sorts records;
• explain the difference between combining criteria with AND and with OR in the query design grid.

Integrity constraints over relations

Many kinds of integrity constraints can be specified in the relational model.

Database integrity refers to the validity and consistency of stored data. Integrity is usually expressed in terms of constraints, which are consistency rules that the database is not permitted to violate.

Constraints may apply to each attribute or they may apply to relationships between tables.

Integrity constraints ensure that changes (update, deletion, insertion) made to the database by authorised users do not result in a loss of data consistency. Thus, integrity constraints guard against accidental damage to the database.

Example: A blood group must be ‘A’ or ‘B’ or ‘AB’ or ‘O’ only. It cannot be any other value.

What are the types of integrity constraints?

The various types of integrity constraints are:

1. Domain integrity
2. Entity integrity constraint
3. Referential integrity constraint
4. Key constraints

Domain integrity

Domain integrity means the definition of a valid set of values for an attribute. You define the data type, the length or size, whether a null value is allowed, whether the value must be unique, the default value, the range (values in between) and any specific values allowed for the attribute.

Entity integrity constraint

This rule states that in any database relation, the value of an attribute of a primary key cannot be null.

Example: Consider a relation “STUDENT” where “Stu_id” is a primary key and must not contain any null value, whereas other attributes may contain null values. For example, “Branch” in the following relation contains one null value.

Referential integrity constraint

This constraint states that if a foreign key exists in a relation, then either the foreign key value must match a primary key value of some tuple in its home relation, or the foreign key value must be null.

Key constraints

A key constraint is a statement or condition that a certain minimal subset of the fields of a relation is a unique identifier for a tuple.

There are four types of key constraints:

• Candidate key
• Super key
• Primary key
• Foreign key

How do you query a relational database?

Several RDBMS vendors provide a GUI to aid their users in developing queries. These can be particularly helpful to novice users, as they enable them to learn the overarching concepts involved in query development without getting bogged down in syntax details.

For this reason, we will start the course with Microsoft Access, which provides perhaps the most user-friendly interface.

A. Download an Access database and review its tables

Throughout this lesson, we will use a database of baseball statistics to help demonstrate the basics of SELECT queries.

1. Open the database in MS Access.

One part of the Access interface that you will use frequently is the “Navigation Pane”, which is situated on the left side of the application window. The top of the Navigation Pane is just beneath the “Ribbon” (the strip of controls that runs horizontally along the top of the window).

The Navigation Pane provides access to the objects stored in the database, such as tables, queries, forms and reports. When you first open the baseball_stats.accdb database, the Navigation Pane should appear with the word Tables at the top. This shows that it is listing the tables stored in the database (PLAYERS, STATS and TEAMS).

2. Double-click on a table’s name in the Navigation Pane to open it. Open all three tables and review the content. Note that the STATS table contains an ID for each player rather than his name. The names associated with the IDs are stored in the PLAYERS table.

B. Write a simple SELECT query

With our first query, we will retrieve data from selected fields in the STATS table.

1. Click on the Create tab near the top of the application window.
2. Next, click on the Query Design button (found on the left side of the Create Ribbon in the group of commands labelled Queries).
3. In the Show Table dialog, double-click on the STATS table to add it to the query and click Close (when you do this in Access 2010, the ribbon switches to the Design ribbon).
4. Double-click on PLAYER_ID in the list of fields in the STATS table to add that field to the design grid below.
5. Repeat this step to add the YEAR and RBI fields.

6. At any time, you can view the SQL that is created by your GUI settings by using the View drop-down list on the far-left side of the Design Ribbon (it is also available when you have the Home tab selected, as shown below).

As you go through the next steps, look at the SQL that corresponds to the queries you are building.

C. Restrict the returned records to a desired subset

1. From the same View drop-down list, select Design View to return to the query design GUI.
2. In the design grid, set the Criteria value for the RBI field to >99.
3. Test the query by clicking on the red exclamation point (it should return 103 records).

D. Sort the returned records

1. Return to Design View.
2. In the design grid, click in the Sort cell under the RBI column and select Descending from the drop-down list.
3. This will sort the records from highest RBI total to lowest.
4. Test the query.

E. Add additional criteria to the selection

1. Return to Design View and set the Criteria value for the YEAR field to >1989. This will limit the results to seasons of 100 or more RBI since 1990.
2. Test the query (it should return 53 records).
3. Return to Design View and modify the Criteria value for the YEAR field to >1989 And <2000, which will further limit the results to just the 1990s.
4. Test the query (it should return 34 records).
5. Return to Design View and change the Criteria value for the YEAR field back to >1989. Beneath that cell (in the “or” cell), add <1960.

As you should be able to guess, I am asking you to write a query that identifies 100 RBI seasons since 1990 OR before 1960. However, the query as written at this point does not quite give that result, as you can see from the WHERE line in the SQL view. Instead, it would return 100 RBI seasons since 1990 and all seasons before 1960 (not just the 100 RBI ones). To produce the desired result, you need to repeat the >99 criterion in the RBI field’s “or” cell, and check the SQL view to see the change.

F. Test the query

Test the query (it should return 74 records).

You have probably recognised by now that the output from these queries is not particularly human-friendly. In the next part of the lesson, we will see how to use a join between the two tables to add the names of the players to the query output.

Evaluation

1. What is an integrity constraint, and what is a legal instance of a database?
2. Name the four types of integrity constraints.
3. Why can the Stu_id of the STUDENT relation not contain a null value, while Branch can?
4. State the rule for referential integrity.
5. In the Access query, what is the difference between putting two criteria in the same row of the design grid and putting one in the “or” cell?

Assignment

1. Explain domain integrity and list the things you can define for an attribute.
2. List the four types of key constraints.
3. Write out the steps for creating a query in Access that returns the PLAYER_ID, YEAR and RBI fields from the STATS table, sorted from highest RBI to lowest.