DATABASE DESIGN 2: DESIGN METHOD - Concepts of Database Management - page 215

For seniors, you could create a second table as follows:

SeniorStudent (StudentNum, ThesisTitle, ThesisArea)

Samples of these tables are shown in Figure 6-30. The StudentDorm and SeniorStudent tables represent
entity subtypes. In both tables, the primary key (StudentNum) will also be a foreign key matching the stu-
dent number in the new Student table.

Student

215

StudentNum LastName FirstName DormNum ThesisTitle ThesisArea

1253 Johnson Ann 3

1662 Anderson Tom 1 P.D.Q. Bach Music

2108 Lewis Bill Cluster sets Math

2546 Davis Mary 2

2867 Albers Cathy 2 Rad. Treatment Medicine

2992 Matthew Mark

3011 Candela Tim 3

3574 Talen Sue

Student StudentDorm

StudentNum LastName FirstName StudentNum DormNum

1253 Johnson Ann 1253 3

1662 Anderson Tom 1662 1

2108 Lewis Bill 2546 2

2546 Davis Mary 2867 2

2867 Albers Cathy 3011 3

2992 Matthew Mark

3011 Candela Tim

SeniorStudent

3574 Talen Sue

StudentNum ThesisTitle ThesisArea

1662 P.D.Q. Bach Music

2108 Cluster sets Math

2867 Rad. Treatment Medicine

FIGURE 6-30 Student table split to avoid use of null values

The DBDL for these tables appears in Figure 6-31. The primary key of the StudentDorm and SeniorStudent
tables (StudentNum) is also a foreign key matching the student number in the new Student table.

Student (StudentNum, LastName, FirstName)

StudentDorm (StudentNum, DormNum)
FK StudentNum Student

FK DormNum Dorm

SeniorStudent (StudentNum, ThesisTitle, ThesisArea)
FK StudentNum Student

FIGURE 6-31 Sample DBDL with entity subtypes