Microsoft Office Tutorials and References
In Depth Information
• A diamond inside a rectangle defines a composite entity. A composite entity is a rela-
tionship that has the characteristics of an entity. For example, Employment connects the
Employee and Position entities in a many-to-many relationship and acts as an entity by
containing the StartDate and EndDate fields, along with the composite key of the
EmployeeNum and PositionID fields.
• An entity subtype, for example, EmployeeBonus, appears in a double rectangle and is
connected without an intervening diamond directly to its related entity, Employee.
You can also show fields in an ERD by placing each individual field in a bubble con-
nected to its entity or relationship. However, typical ERDs have large numbers of entities
and relationships, so including the fields might confuse rather than clarify the ERD.
A database has integrity if its data follows certain rules; each rule is called an integrity
constraint . The ideal is to have the DBMS enforce all integrity constraints. If a DBMS can
enforce some integrity constraints but not others, the other integrity constraints must be
enforced by other programs or by the people who use the DBMS. Integrity constraints can
be divided into three groups: primary key constraints, foreign key constraints, and domain
• One primary key constraint is inherent in the definition of a primary key, which says that
the primary key must be unique. The entity integrity constraint says that the primary key
cannot be null. For a composite key, none of the individual fields can be null. The
uniqueness and nonnull properties of a primary key ensure that you can reference any
data value in a database by supplying its table name, field name, and primary key value.
• Foreign keys provide the mechanism for forming a relationship between two tables, and
referential integrity ensures that only valid relationships exist. Referential integrity is the
constraint specifying that each nonnull foreign key value must match a primary key
value in the primary table. Specifically, referential integrity means that you cannot add a
row with an unmatched foreign key value. Referential integrity also means that you can-
not change or delete the related primary key value and leave the foreign key orphaned.
In some RDBMSs, if you try to change or delete a primary key value, you can specify
one of these options: restricted, cascades, or nullifies. If you specify restricted , the
DBMS updates or deletes the value only if there are no matching foreign key values. If
you choose cascades and then change a primary key value, the DBMS changes the
matching foreign key values to the new primary key value, or, if you delete a primary
key value, the DBMS also deletes the matching foreign key rows. If you choose nullifies
and then change or delete a primary key value, the DBMS sets all matching foreign key
values to null.
• Recall that a domain is a set of values from which one or more fields draw their actual
values. A domain integrity constraint is a rule you specify for a field. By choosing a
data type for a field, you impose a constraint on the set of values allowed for the field.
You can create specific validation rules for a field to limit its domain further. As you
make a field’s domain definition more precise, you exclude more and more unaccept-
able values for the field. For example, in the State table, shown in Figures A-2 and A-4,
you could define the domain for the EnteredUnionOrder field to be a unique integer
between 1 and 50 and the domain for the StateBird field to be any name containing 25
or fewer characters.