Session 4

Session Four
Normalization Part II,
Integrity and Review for the Midterm

Topics

Normalization Review

Do Not Repeat;
"The Key, the whole key
and nothing but the key,
so help me Codd."

No Repeating Groups.
No Partial Key Dependencies,
No Transitive Dependencies.

Review sheet

Integrity Constraints and Anomalies

Data Integrity refers to the accuracy and consistency of the data and define the relationally correct state for a data-base.
An anomaly occurs when integrity rules have been violated and this results in lost or inaccurate data.

In order for a data base to have integrity (to ensure that users perform only operations which leave the database in a correct, consistent state) it needs to obey several rules called "Integrity Constraints".

The three basic areas for these rules are:

1. Primary Key Constraints
2. Referential Integrity Constraints
3. Domain Integrity Constraints

1. Primary Key Constraints - Unique and Not Null.

Primary Key must be unique.

Entity Integrity Constraint

No primary key value can be NULL.
For a composite key none of the individual attributes that make up the key can be null.

WHY? The primary key value is used to identify individual tuples in a relation; having null values for the primary key implies that we cannot identify some tuples.

Insertion Anomaly - if you use a combination of the customer's last name and the last four digits of their phone number, that combination MUST be unique and they MUST know their phone number when you go to add them.

2. Referential Integrity Constraints - Only Valid Relationships.

A foreign key MUST match an existing primary key value (or else be NULL).

You can NOT add a row with an unmatched foreign key.

Relational Database Design and Referential Integrity

IBM - Referential Integrity

Answers.com - Referential Integrity

You can NOT change or delete the related primary key value and leave the foreign key orphaned.

Restricted - The DBMS will update or delete the value only if there are no matching foreign keys.
Cascades - If you change the primary key value, the DBMS changes the matching foreign key value, or, if you delete a primary key value, the DBMS also deletes the matching foreign key rows.
Nullifies - If you change or delete the primary key value, the DBMS sets all matching foreign keys to null.

Deletion Anomaly - if you delete the Salesperson and he or she is the only one who has contact with a specific customer, do we delete the customer also?

Update Anomaly - The codes for all of you items in your inventory are from the combination of the supplier code and a number. The supplier ACME has its code changed from AC to AM because you add a new supplier ACE which has a code of AC. All the inventory items that were identified AC0301 must be changed to AM0301 and every table that uses that as a foreign key must be updated.

3. Domain Integrity Constraints - Set of Values for an Attribute.

Column Integrity - A column MUST contain only values consistent with the defined data format.

User Defined Integrity - The data stored in the database MUST comply with the rules of the business.

Create specific validation rules for an attribute to limit its domain further.
Exclude various unacceptable values for an attribute.

A business has the following user defined data integrity constraints:

The "county code" may only contain the numbers 01 through 15.
An employee in the Finance Department cannot have a title of : "Programmer".
A Salesman's commission cannot exceed 50% of salary.

All integrity constraints should be enforced by the DBMS or application software.