Database Reference
In-Depth Information
STUDENT_HOUSING
HOUSING_FEE
Figure 3-16
the 3NF StUDENt_
HOUSING and HOUSING_
FEE relations
Thus, a non-key attribute (HousingFee) is functionally determined by another non-key at-
tribute (Building), and the relation is not in 3NF.
To put the relation into 3NF, we will have to move the columns of the functional depen-
dency into a separate relation while leaving the determinant in the original relation as a for-
eign key. We will end up with two relations:
STUDENT_HOUSING ( StudentID , Building )
BUILDING_FEE ( Building , HousingFee).
The Building column in STUDENT_HOUSING becomes a foreign key. The two relations
are now in 3NF (work through the logic yourself to make sure you understand 3NF) and are
shown in Figure 3-16.
Boyce-Codd Normal Form
Some database designers normalize their relations to 3NF. Unfortunately, there are still anomalies
due to functional dependences in 3NF. Together with Raymond Boyce, Codd defined BCNF to fix
this situation. A relation is in BCNF if and only if it is in 3NF and every determinant is a candidate key.
For example, consider the relation STUDENT_ADVISOR shown in Figure 3-17, where a
student (StudentID) can have one or more majors (Major), a major can have one or more faculty
advisors (AdvisorName), and a faculty member advises in only one major area. Note that the
figure shows two students (StudentIDs 700 and 800) with double majors (both students show
Majors of Math and Psychology) and two Subjects (Math and Psychology) with two Advisors.
Because students can have several majors, StudentID does not determine Major. Moreover,
because students can have several advisors, StudentID does not determine AdvisorName.
Therefore, StudentID by itself cannot be a key. However, the composite key (StudentID, Major)
determines AdvisorName, and the composite key (StudentID, AdvisorName) determines
Major. This gives us (StudentID, Major) and (StudentID, AdvisorName) as two candidate
STUDENT_ADVISOR
Figure 3-17
the 3NF StUDENt_
aDVISOr relation
 
Search WWH ::




Custom Search