Databases Reference
In-Depth Information
Student
entity
Student
StudentNum
LastName
FirstName
Category
symbol
Single line
indicates an
incomplete
category
210
StudentDorm
entity
Primary
key is also a
foreign key
StudentDorm
StudentNum (FK)
DormNum (FK)
Category
(entity subtype)
DormNum
is a foreign key
(matching entity
not shown)
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 stu-
dent 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—DormNum, ThesisTitle, and ThesisArea—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 wouldn't make much
sense to combine all three of these columns into a single table. A better choice would be to create the follow-
ing table for students living in dorms:
StudentDorm (StudentNum, DormNum)
For seniors, you could create a second table as follows:
SeniorStudent (StudentNum, ThesisTitle, ThesisArea)
Samples of these tables are shown in Figure 6-30. Both tables represent entity subtypes. In both tables,
the primary key (StudentNum) will also be a foreign key matching the student number in the new Student table.
