Database Reference
In-Depth Information
Two alternatives exist for choice of primary key:
a.
Key on the aggregation of the foreign keys.
b.
Introduce a surrogate and key on it.
5.5.5 Determining Entity Subtypes and Super-types
The fifth step relates to identifying and properly implementing subtype-super-type
relationships among the entities. Care should be taken here, in not introducing subtype-
super-type relationships where traditional relationships would suffice (review section 3.5).
Each entity type translates to a base relation. Each base relation will contain attributes
corresponding to properties of the entities that apply within the type hierarchy. Again
being guided by principles established in chapter 3 (section 3.5.4), each subtype will
share the primary key of its super-type. Further, the primary key of a subtype is also the
foreign key of the said subtype. The illustrations provided in chapter 3 (Figures 3-11 and
3.12) are also applicable here.
No subtype-super-type relationship appears in the model of Figure
5-2
. However,
in Figure
5-3
, there are a few:
Programmer
,
Engineer
and
Accountant
are subtypes
of
Employee
;
SystemProgrammer
and
ApplicationProgrammer
are subtypes of
Programmer
. Note also that in a subtype, except for the primary key (which is also a
foreign key), no additional attributes of the super-type need to be repeated, since they
are inherited. However, additional attributes may be specified (in the subtype). For
example (still referring to Figure
5-3
), the
Programmer
entity may contain the attribute
ProgLanguage
to store the programmer's main programming language; this would not
apply to
Employee
.
For subtypes, integrity constraints on foreign (primary) keys may be:
•
Nulls not allowed
•
Deletion of referenced records restricted (in super-type)
•
Deletion of referencing records allowed (in subtype but not in
super-type)
•
Update cascaded from the super-type
5.5.6 Determining Component Entities
Component entities were not discussed in Date's original work on the database design
via the XR model. However, in the interest of comprehensive coverage, this sub-section
has been added. As mentioned in the previous sub-section, care should be taken in
not introducing component relationships where traditional relationships would suffice
(review section 3.5). Each entity type translates to a base relation. Each base relation will
contain attributes corresponding to properties of the entities that apply within the type
hierarchy. Again being guided by principles established in chapter 3 (section 3.5.4), each
component will include a foreign key, which is the primary key in the summary relation.
