Database Reference
In-Depth Information
5.5.2 Determining the Characteristic Entities
The second step involves determining and properly structuring the characteristic entities.
As mentioned above, a characteristic entity is existence-dependent on the entity it
describes. One characteristic exists in the example, namely
Dependent
. Characteristics
also translate to base relations. The foreign key in
Dependent
would be
DepnEmp#
,
which would reference
Employee.Emp#
. Notice that we did not use the attribute name
Emp#
as the foreign key in
Dependent
, but
DepnEmp#
. This decision is based on the
following principle:
It should be noted that not all DBMS products support this principle (some require
that a foreign key must have the same name as the attribute it references). You should
therefore check to ascertain whether the product you are using supports it (Oracle and
DB2 both do). Two strong arguments can be given in defense of this principle:
•
Simply, it makes good sense and leads to a cleaner, more elegant
database design.
•
It avoids confusion when queries involving the joining of multiple
relations are constructed and executed on the database. This will
become clearer in division C (particularly chapter 12) of the text.
Moving on to data integrity, we would require the following integrity constraints on
the
Dependent
relation:
•
Null FKs not allowed
•
Deletion is cascaded from the referenced to the referencing
records
•
Update cascaded from the referenced to the referencing records
Two alternatives exist for choice of primary key:
a.
The foreign key combined with the attribute that distinguishes
different characteristics within the target entity
(e.g. [
Emp#
,
DepnName
]);
b.
Introduce a surrogate (e.g.
DepnRef
). The surrogate could
be defined in such as way as to allow you to key solely on it;
or it could be defined to allow you to key on the foreign key,
combined with it (e.g. [
Emp#
,
DepnRef
]).
