Page 146 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Unit 8: Normalization

Notes
SID Major Advisor Maj_GPA
123 Physics Satish 4.0
123 English Mahesh 3.3
456 Literature Krishna 3.2
789 English Praveen 3.7

123 Physics Satish 3.5

Functional Dependencies in STUDENT_ADVISOR

Figure 8.4

SID Major Advisor Maj_GPA

This relation has the following attributes: SID (Student ID), Major, Advisor, and Maj_GPA. The
primary key for this relation is the composite key consisting of SID and Major. Thus the two
attributes Advisor and Maj_GPA are functionally dependent on this key. This reflects the constraint
that although a given student may have more than one major, for each major a student has
exactly one advisor and one GPA.
There is a second functional dependency in this relation. Major is functionally dependent on
Advisor. That is, each Advisor advises in exactly one Major. Notice that this is not a transitive
dependency. Rather, a key attribute (Major) is functionally dependent on a non-key attribute
(Advisor).
Anomalies in Student_Advisor Relation

The Student_Advisor relation is clearly in 3NF, since there are no partial dependencies and no
transitive dependencies. Nevertheless, because of the functional dependency between Major
and Advisor, there are anomalies in this relationship. Consider the following scenarios:

Suppose that in Physics the advisor Satish is replaced by Rajesh. This change must be made in
two rows in the table (update anomaly).
Suppose to insert a row with the information that Rajeev advises in Computer Science. This can
be done only when at least one student majoring in Computer Science (may or may not be the
case) is assigned Rajeev as an advisor (insert anomaly).
If student number 789 withdraws from school, there is a loss of information that Praveen advises
in English (delete anomaly).

The anomalies in Student_Advisor result from the fact that there is a determinant (Advisor) that
is not a candidate key in the relation. R.F. Boyce and E.F. Codd identified this deficiency and
proposed a stronger definition of 3NF that remedies the problem.

Definition of Boyce-Codd Normal Form

A relation is in Boyce-Codd Normal Form (BCNF) if and only if every determinant in the
relation is a candidate key.

LOVELY PROFESSIONAL UNIVERSITY 139