Chapter 4: Integrity Constraints

Well-structured relations

- a relation that contains minimal redundancy and allows users to insert, modify, and delete the rows in a table without errors or inconsistencies (anomalies)

Referential integrity constraint

- a rule that states that either each foreign key value must match a primary key value in another relation or the foreign value must be null. i. Associations between tables are defined through the use of foreign keys, rows of relations, ii. If relationship is optional, then the foreign key could be null, but must be specified as a property of the foreign key attribute when the database is defined

Entity integrity rule

- a rule that states that no primary key attribute (or component of a primary key attribute) may be null. designed to ensure that every relation has a primary key and that the data values for the for that primary key are all valid - some particular attributes cannot be assigned a data value- no applicable data or the applicable data value is not known when the values are assigned

Domain constraints

- all of the values that appear in a column of a relation must be from the same domain.

Domain definition

- consist usually of: domain name, meaning, data type, size (or length) and allowable values or allowable range

What happens to order data if we choose to delete a customer who has submitted orders. See sales if we don't care about the customer anymore

1. Delete the associated orders (called a cascading delete), in which case we lose not only the customer but also sales history 2. Prohibit deletion of the customer until all associated order are first deleted (a safety check) 3. Place a null value in the foreign key ( an exception that says although an order must have a customer ID value when the order is created, can be null later if the associated customer is deleted

Three types of anomalies

1. Insertion anomaly- the user should be able to enter employee data without supplying course data 2. Deletion anomaly- lose important attributes 3. Modification anomaly- edit the information for the person in each row

null

a value that may be assigned to an attribute when no other value applies or when the applicable value is unknown, not a value but rather it indicated the absence of a value, anomalous

domain

is the set of values that may be assigned to an attribute

R.E.D

referential integrity, entity integrity, domain constraints