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

The crucial issue in making the determination between a single many-to-many-to-many relationship and
two (or three) many-to-many relationships is the independence. When all three entities are critical in the
relationship, the three-way relationship (like Sales) is appropriate. When there is independence among the
individual relationships, separate many-to-many relationships are appropriate. Incidentally, if a many-to-
many-to-many relationship is created when it is not appropriate to do so, the conversion to fourth normal
form will correct the problem.

NULLS AND ENTITY SUBTYPES

213

Recall that a null is a special value that represents the absence of a value in a field. In other words, setting a
particular field to null is equivalent to not entering a value in the field. Nulls are used when a value is either
unknown or inapplicable. This section focuses on the second possibility

when the value is inapplicable.
Consider, for example, a Student table in which one of the columns, DormNum, is a foreign key that
identifies a Dorm (dormitory) table. The DormNum column indicates the number of the dormitory in which a
student currently resides. This foreign key is allowed to be null because some students do not live in a
dormitory; for these students, DormNum is inapplicable. Thus, for some rows in the Student table, the
DormNum column would be null.
When there are many students who do not live in dorms, you can avoid using null values in the
DormNum column by removing the DormNum column from the Student table and creating a separate table
named StudentDorm that contains the columns StudentNum (the primary key) and DormNum. Students
living in a dorm would have a row in this new table. Students not living in a dorm would have a row in the
Student table but not in the StudentDorm table.
This change is illustrated in Figure 6-27. Note that StudentNum, the primary key of the StudentDorm
table, is also a foreign key that must match a student number in the Student table.

—

Student

StudentNum LastName FirstName DormNum

1253 Johnson Ann 3

1

1662 Anderson Tom

2108 Lewis Bill

2546 Davis Mary

2

2867 Albers Cathy 2

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

3574 Talen Sue

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