The Relational Model and Normalization - Database Processing: Fundamentals, Design, and Implementation - page 137

keys. We can select either of these as the primary key for the relation. Thus, two STUDENT_
ADVISOR schemas with different candidate keys are possible:

STUDENT_ADVISOR ( StudentID , Major , AdvisorName)

and

STUDENT_ADVISOR ( StudentID , Major, AdvisorName )

Note that STUDENT_ADVISOR is in 2NF because it has no non-key attributes in the sense
that every attribute is a part of at least one candidate key. This is a subtle condition, based on
the fact that technically the definition of 2NF states that no non-prime attribute can be partially
dependent on a candidate key, where a non-prime attribute is an attribute that is not con-
tained in any candidate key. Furthermore, STUDENT_ADVISOR is in 3NF because there are
no transitive dependencies in the relation.
The two candidate keys for this relation are overlapping candidate keys because they
share the attribute StudentID. When a table in 3NF has overlapping candidate keys, it can still
have modification anomalies based on functional dependencies. In the STUDENT_ADVISOR re-
lation, there will be modification anomalies because there is one other functional dependency in
the relation. Because a faculty member can be an advisor for only one major area, AdvisorName
determines Major. Therefore, AdvisorName is a determinant but not a candidate key.
Suppose that we have a student (StudentID = 300) majoring in psychology (Major =
Psychology) with faculty advisor Perls (AdvisorName = Perls). Further, assume that this row
is the only one in the table with the AdvisorName value of Perls. If we delete this row, we will
lose all data about Perls. This is a deletion anomaly. Similarly, we cannot insert the data to rep-
resent the Economics advisor Keynes until a student majors in Economics. This is an insertion
anomaly. Situations like this led to the development of BCNF.
What do we do with the STUDENT_ADVISOR relation? As before, we move the func-
tional dependency creating the problem to another relation while leaving the determinant in
the original relation as a foreign key. In this case, we will create the relations:

STUDENT_ADVISOR ( StudentID , AdvisorName )
ADVISOR_SUBJECT ( AdvisorName , Major)

The AdvisorName column in STUDENT_ADVISOR is the foreign key, and the two final
relations are shown in Figure 3-18.

eliminating Anomalies from Functional Dependencies with BCNF
Most modification anomalies occur because of problems with functional dependencies. You
can eliminate these problems by progressively testing a relation for 1NF, 2NF, 3NF, and BCNF
using the definitions of these normal forms given previously. We will refer to this as the “Step-
by-Step” method.

STUDENT_ADVISOR ADVISOR_SUBJECT

Figure 3-18
the BCNF StUDENt_
aDVISOr and aDVISOr_
SUBJECt relations