Database Reference
In-Depth Information
STUDENT
StudentID
STUDENT
StudentID
LastName
FirstName
isGradStudent
LastName
FirstName
isGradStudent
isGradStudent
UNDERGRADUATE
StudentID
UNDERGRADUATE
StudentID (FK)
GRADUATE
StudentID (FK)
GRADUATE
StudentID
HighSchoolGPA
ScoreOnSAT
HighSchoolGPA
ScoreOnSAT
UndergraduateGPA
ScoreOnGMAT
UndergraduateGPA
ScoreOnGMAT
(b)
Database Design for the
Supertype/Subtype Relationship
(a)
Data Model of the Supertype/Subtype
Relationship from Figure 5-13(a)
Figure 6-19
Transformation of the
Supertype/Subtype Entities
Relationships Between Supertype and Subtype entities
Representing relationships between supertype entities and their subtypes is easy. Recall that
these relationships are also called IS-A relationships because a subtype and its supertype
are representations of the same underlying entity. A MANAGER (subtype) is an EMPLOYEE
(supertype), and a SALESCLERK (subtype) is also an EMPLOYEE (supertype). Because of this
equivalence, the keys of all subtype tables are identical to the key of the supertype table.
Figure 6-19(a) shows the data model in Figure 5-13(a), an example for two subtypes of
STUDENT. Notice that the key of STUDENT is StudentID and that the key of each of the sub-
types also is StudentID. UNDERGRADUATE.StudentID and GRADUATE.StudentID are both
primary keys and foreign keys to their supertype.
While we are showing the transformation of a set of exclusive subtypes (with the dis-
criminator attribute isGradStudent), the transformation of a set of inclusive subtypes is done
exactly the same way. Note that discriminator attributes cannot be represented in relational
designs. In Figure 6-19(b), we can do nothing with isGradStudent except note in the design
documentation that isGradStudent determines subtype. Application programs will need to be
written to use isGradStudent to determine which subtype pertains to a given STUDENT.
Recursive Relationships
The representation of recursive relationships is just an extension of the techniques used for
representing relationships between strong entities. These techniques may be a bit difficult to
comprehend at first because they appear strange, but they involve principles that you have
already learned.
1:1 Recursive Relationships
Consider the 1:1 recursive BOXCAR relationship in Figure 6-20(a), which is the same data model
we developed in Figure 5-38. To represent the relationship, we create a foreign key in BOXCAR
that contains the identifier of the boxcar ahead, as shown in Figure 6-20(b). Because the relation-
ship is 1:1, we make the foreign key unique by defining it as unique (shown here as an alternate
key). This restriction enforces the fact that a boxcar can have at most one boxcar in front of it.
Notice that both sides of the relationship are optional. This occurs because the last car on
the train is ahead of no other car and because the first car on the train has no other car ahead of
it. If the data structure were circular, this restriction would not be necessary. For example, if you
wanted to represent the sequence of names of the calendar months, and you wanted December
to lead to January, then you could have a 1:1 recursive structure with required children.
Search WWH ::
Custom Search