Datasheet

Part I ✦ Laying the Foundation

Entity Integrity

Entity integrity involves the structure (primary key and its attributes) of the entity. If the pri-

mary key is unique and all attributes are scalar and fully dependent on the primary key, then

the integrity of the entity is good. In the physical schema, the table’s primary key enforces

entity integrity. Essentially, entity integrity is normalization.

Normalization is explained in detail in the next chapter, “Relational Database Modeling.”

Domain Integrity

In relational theory terms, a domain is a set of possible values for an attribute, such as inte-

gers, bit values, or characters. Domain integrity ensures that only valid data is permitted in

the attribute. Nullability (whether a null value is valid for an attribute) is also a part of

domain integrity. In the physical schema, the data type and nullability of the row enforce

domain integrity.

Referential Integrity

A subset of domain integrity, referential integrity refers to the domain integrity of foreign keys.

Domain integrity says that if an attribute has a value, then that value must be in the domain.

In the case of the foreign key, the domain is the list of values in the related primary key.

Referential integrity, therefore, is not an issue of the integrity of the primary key but of the

foreign key.

The nullability of the column is a separate issue from referential integrity. It’s perfectly

acceptable for a foreign key column to allow nulls.

Several methods of enforcing referential integrity at the physical-schema level exist. Within a

physical schema, a foreign key can be enforced by declarative referential integrity (DRI) or by

a custom trigger attached to the table.

User-Defined Integrity

Besides the relational theory integrity concerns, the user-integrity requirements must also be

enforced, as follows:

✦ Simple business rules, such as a restriction to a domain, limit the list of valid data

entries. Check constraints are commonly used to enforce these rules in the physical

schema.

✦ Complex business rules limit the list of valid data based on some condition. For exam-

ple, certain tours may require a medical waiver. Implementing these rules in the physi-

cal schema generally requires stored procedures or triggers.

Some data-integrity concerns can’t be checked by constraints or triggers. Invalid, incomplete,

or questionable data may pass all the standard data-integrity checks. For example, an order

without any order detail rows is not a valid order, but no automatic method traps such an

order. SQL queries can locate incomplete orders and help in identifying other less measurable

data-integrity issues, including the following:

Cross-

Reference

05_542567 ch01.qxp 9/27/06 9:58 PM Page 12