Database Reference
In-Depth Information
Transforming a data model into a database design requires
three major tasks: replacing each entity with a table and
each attribute with a column; representing relationships and
maximum cardinality by placing foreign keys; and represent-
ing minimum cardinality by defining actions to constrain
activities on values of primary and foreign keys.
During database design, each entity is replaced by a
table. The attributes of the entity become columns of the
table. The identifier of the entity becomes the primary key of
the table, and candidate keys in the entity become candidate
keys in the table. A good primary key is short, numeric, and
fixed. If a good primary key is not available, a surrogate key
may be used instead. Some organizations choose to use sur-
rogate keys for all of their tables. An alternate key is the same
as a candidate key and is used to ensure unique values in a
column. The notation AK
n.m
refers to the
n
th alternative key
and the
m
th column in that key.
Four properties need to be specified for each table
column: null status, data type, default value, and data con-
straints. A column can be NULL or NOT NULL. Primary keys
are always NOT NULL; alternate keys can be NULL. Data
types depend on the DBMS to be used. Generic data types
include CHAR(n), VARCHAR(n), DATE, TIME, MONEY,
INTEGER, and DECIMAL. A default value is a value to be
supplied by the DBMS when a new row is created. It can
be a simple value or the result of a function. Sometimes
triggers are needed to supply values of more complicated
expressions.
Data constraints include domain constraints, range
constraints, intrarelation constraints, and interrelation con-
straints. Domain constraints specify a set of values that a
column may have; range constraints specify an interval of
allowed values; intrarelation constraints involve compari-
sons among columns in the same table; and interrelation
constraints involve comparisons among columns in different
tables. A referential integrity constraint is an example of an
interrelation constraint.
Once the tables, keys, and columns have been de-
fined, they should be checked against normalization crite-
ria. Usually the tables will already be normalized, but they
should be checked in any case. Also, it may be necessary to
denormalize some tables.
The second step in database design is to create relation-
ships by placing foreign keys appropriately. For 1:1 strong
relationships, the key of either table can go in the other table
as a foreign key; for 1:N strong relationships, the key of the
parent must go in the child; and for N:M strong relationships,
a new table, called an intersection table, is constructed that
has the keys of both tables. Intersection tables never have
nonkey data.
Four uses for ID-dependent entities are N:M relation-
ships, association relationships, multivalued attributes,
and archetype/instance relationships. An association rela-
tionship differs from an intersection table because the ID-
dependent entity has nonkey data. In all ID-dependent enti-
ties, the key of the parent is already in the child. Therefore, no
foreign key needs to be created. When an instance entity of
the archetype/instance pattern is given a non-ID-dependent
identifier, it changes from an ID-dependent entity to a weak
entity. The tables that represent such entities must have the
key of the parent as a foreign key. They remain weak entities,
however. When the parent of an ID-dependent entity is given
a surrogate key, the ID-dependent entity is also given a sur-
rogate key. It remains a weak entity, however.
Mixed entities are represented by placing the key of the
parent of the nonidentifying relationship into the child. The
key of the parent of the identifying relationship will already
be in the child. Subtypes are represented by copying the
key from the supertype into the subtype(s) as a foreign key.
Recursive relationships are represented in the same ways
that 1:1, 1:N, and N:M relationships are represented. The only
difference is that the foreign key references rows in the table
in which it resides.
Ternary relationships are decomposed into binary rela-
tionships. However, sometimes binary constraints must be
documented. Three such constraints are MUST, MUST NOT,
and MUST COVER.
The third step in database design is to create a plan for
enforcing minimum cardinality. Figure 6-28 shows the ac-
tions that need to be taken to enforce minimum cardinality
for required parents and required children. The actions in
Figure 6-28(a) must be taken for M-O and M-M relationships;
the actions in Figure 6-28(b) must be taken for O-M and
M-M relationships.
Enforcing mandatory parents can be done by defining
the appropriate referential integrity constraint and by set-
ting the foreign key to NOT NULL. The designer must specify
whether updates to the parent's primary key will cascade or
be prohibited, whether deletions to the parent will cascade
or be prohibited, and what policy will be used for finding a
parent when a new child is created.
Enforcing mandatory children is difficult and requires
the use of triggers or application code. The particular actions
that need to be taken are shown in Figure 6-28(b). Enforcing
M-M relationships can be very difficult. Particular challenges
concern the creation of the first parent/child rows and the
deletion of the last parent/child rows. The triggers on the
two tables interfere with one another. M-M relationships
between strong and weak entities are not as problematic as
those between strong entities.
Search WWH ::
Custom Search