Fig. 8.5 shows the design in 3NF.
Now the original Student relation has been broken into three relations, each with a single theme??”
one records information about students, one records information about dorms, and one records information
about faculty members who act as advisors.
In a more complete implementation, one would choose better key values for the Student and Faculty relations.
Perhaps one would choose some sort of ID number instead of relying on a name and hoping one never has to
deal with the possibility of including two John Smiths in the database. A Faculty relation likely would also have
additional attributes, such as office address, salary, etc.
Boyce??“Codd normal form (BCNF) is a refinement of 3NF. A relation is in BCNF if every determinant in
the relation is a candidate key. A candidate key is a valid choice for the key of the relation.
Suppose in our example that room numbers in different dorms were different, such that the value of the
room number itself determined which dorm the student was in (room numbers less than 100 were in Arthur
Dorm, for instance, and room numbers between 100 and 199 were in Brooks Dorm). Room would then be
a determinant, but it obviously would not be a candidate key, so the Student relation would not be in BCNF.
Pages:
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411