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.
|
