Add Data Integrity Rules

Data integrity is one of the cornerstones of the relational model. Simply stated data integrity means that the data values in the database are correct and consistent.

Data integrity is enforced in the relational model by entity and referential integrity rules. Although not part of the relational model, most database software enforce attribute integrity through the use of domain information.

Entity Integrity

The entity integrity rule states that for every instance of an entity, the value of the primary key must exist, be unique, and cannot be null. Without entity integrity, the primary key could not fulfill its role of uniquely identifying each instance of an entity.

Referential Integrity

The referential integrity rule states that every foreign key value must match a primary key value in an associated table. Referential integrity ensures that we can correctly navigate between related entities.

Insert and Delete Rules

A foreign key creates a hierarchical relationship between two associated entities. The entity containing the foreign key is the child, or dependent, and the table containing the primary key from which the foreign key values are obtained is the parent.

In order to maintain referential integrity between the parent and child as data is inserted or deleted from the database certain insert and delete rules must be considered.

Insert Rules

Insert rules commonly implemented are:

• Dependent. The dependent insert rule permits insertion of child entity instance only if matching parent entity already exists.
• Automatic. The automatic insert rule always permits insertion of child entity instance. If matching parent entity instance does not exist, it is created.
• Nullify. The nullify insert rule always permits the insertion of child entity instance. If a matching parent entity instance does not exist, the foreign key in child is set to null.
• Default. The default insert rule always permits insertion of child entity instance. If a matching parent entity instance does not exist, the foreign key in the child is set to previously defined value.
• Customized. The customized insert rule permits the insertion of child entity instance only if certain customized validity constraints are met.
• No Effect. This rule states that the insertion of child entity instance is always permitted. No matching parent entity instance need exist, and thus no validity checking is done.

Delete Rules

• Restrict. The restrict delete rule permits deletion of parent entity instance only if there are no matching child entity instances.
• Cascade. The cascade delete rule always permits deletion of a parent entity instance and deletes all matching instances in the child entity.
• Nullify. The nullify delete rules always permits deletion of a parent entity instance. If any matching child entity instances exist, the values of the foreign keys in those instances are set to null.
• Default. The default rule always permits deletion of a parent entity instance. If any matching child entity instances exist, the value of the foreign keys are set to a predefined default value.
• Customized. The customized delete rule permits deletion of a parent entity instance only if certain validity constraints are met.
• No Effect. The no effect delete rule always permits deletion of a parent entity instance. No validity checking is done.

Delete and Insert Guidelines

The choice of which rule to use is determined by Some basic guidelines for insert and delete rules are given below.

• Avoid use of nullify insert or delete rules. Generally, the parent entity in a parent-child relationship has mandatory existence. Use of the null insert or delete rule would violate this rule.
• Use either automatic or dependent insert rule for generalization hierarchies. Only these rules will keep the rule that all instances in the subtypes must also be in the supertype.
• Use the cascade delete rule for generalization hierarchies. This rule will enforce the rule that only instances in the supertype can appear in the subtypes.

Domains

A domain is a valid set of values for an attribute which enforce that values from an insert or update make sense. Each attribute in the model should be assigned domain information which includes:

• Data Type—Basic data types are integer, decimal, or character. Most data bases support variants of these plus special data types for date and time.
• Length—This is the number of digits or characters in the value. For example, a value of 5 digits or 40 characters.
• Date Format—The format for date values such as dd/mm/yy or yy/mm/dd
• Range—The range specifies the lower and upper boundaries of the values the attribute may legally have
• Constraints—Are special restrictions on allowable values. For example, the Beginning_Pay_Date for a new employee must always be the first work day of the month of hire.
• Null support—Indicates whether the attribute can have null values
• Default value (if any)—The value an attribute instance will have if a value is not entered.

Primary Key Domains

The values of primary keys must be unique and nulls are not allowed.

Foreign Key Domains

The data type, length, and format of primary keys must be the same as the corresponding primary key. The uniqueness property must be consistent with relationship type. A one-to-one relationship implies a unique foreign key; a one-to-many relationship implies a non-unique foreign key.