To bring the design into 2NF, make a new relation called Dorm, and remove the
RA attribute from the Student relation (Fig. 8.4).
146 DATABASE [CHAP. 8
Figure 8-3 Student relation.
Figure 8-4 2NF.
This is progress, but it??™s still true that the Student relation tracks information about something other than
students. In particular, the relation is tracking information about advisors; it??™s tracking not just who the advisor
for each student is, but also what department the advisor belongs to. If a relation is to focus on a single theme,
this doesn??™t seem right.
Third normal form requires that a relation has no transitive dependencies. Said another way, each non-key
attribute must provide a fact about the entity as a whole, not about another nonkey attribute. That is what is still
wrong with the Student relation. MajorAdvisorName and AdvisorDept are both dependent upon the key of the
Student relation, but AdvisorDept is also transitively dependent upon MajorAdvisorName. Once we know who the
student is, we can determine the student??™s advisor, and once we know the advisor, we can determine the advisor??™s
department. This is a transitive dependency: A determines B, and B determines C. To make the design conform
to 3NF, one must remove the transitive dependency from the Student relation.
Pages:
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410