Database Reference
In-Depth Information
In the process, you have created what is formally called an entity subtype. You can say that the
StudentDorm table is a subtype of the Student table. In other words,
“
students living in dorms
”
is a subtype
(or subset) of
”
Some design methods have specific ways of denoting entity subtypes, but it is not necessary to denote
entity subtypes in DBDL. You can recognize entity subtypes by the fact that the primary key is also a foreign
key, as shown in Figure 6-28.
“
students.
Studentƒ(StudentNum, LastName, FirstName)
214
StudentDorm (StudentNum, DormNum)
FK StudentNum
Student
FK DormNum
Dorm
FIGURE 6-28
Sample DBDL with entity subtypes
Most approaches to diagramming database designs have ways of representing subtypes. In IDEF1X, for
example, a subtype, which is called a category in IDEF1X terminology, is represented in the manner shown
in Figure 6-29. The circle is the symbol used for a category. The single horizontal line below the category
symbol indicates that the category is an incomplete category; that is, there are students who do not fall into
the StudentDorm category.
Student
entity
Student
StudentNum
LastName
FirstName
Category
symbol
Single line
indicates an
incomplete
category
StudentDorm
entity
StudentDorm
StudentNum (FK)
DormNum (FK)
Primary
key is also a
foreign key
DormNum
is a foreign key
(matching entity
not shown)
Category
(entity subtype)
FIGURE 6-29
Entity subtype in an E-R diagram
The issue is more complicated when more than one column can accept null values. Suppose the
DormNum, ThesisTitle, and ThesisArea columns in the following Student table can be null.
Student (StudentNum, LastName, FirstName, DormNum, ThesisTitle, ThesisArea)
In this table, the dorm number is the number of the dorm in which the student resides or is null if the
student does not live in a dorm. In addition, students at this college must write a senior thesis. After students
attain senior standing, they must select a thesis title in the area in which they will write their thesis. Thus,
seniors will have a thesis title and a thesis area, whereas other students will not. You can handle this situa-
tion by allowing the fields ThesisTitle and ThesisArea to be null.
The Student table now has three different columns
that can
be null. The DormNum column will be null for students who do not live in a dorm. The ThesisTitle and
ThesisArea columns will be null for students who have not yet attained senior standing. It would not make
much sense to combine all three of these columns into a single table. A better choice would be to create
the following table for students living in dorms:
—
DormNum, ThesisTitle, and ThesisArea
—
StudentDorm (StudentNum, DormNum)
