Page 147 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Database Management Systems/Managing Database

Notes Student_Advisor is not in BCNF because although the attribute Advisor is a determinant, it is
not a candidate key (rather Major is functionally dependent on Advisor).

Converting a Relation to BCNF

A relation that is in 3NF (but not BCNF) can be converted to relations in BCNF as follows:
In the first step, the relation is modified so that the determinant in the relation that is not a
candidate key becomes a component of the primary key of the revised relation. The attribute
that is functionally dependent on that determinant becomes a non-key attribute. This is a legitimate
restructuring of the original relation because of the functional dependency.
The result of applying this to Student_Advisor is given below:
The determinant Advisor becomes part of the composite primary key. The attribute Major (that
was a part of the composite key in the old Student_Advisor relation) gets partitioned into a
separate relation and becomes a non-key attribute in the new relation that is functionally
dependent on the primary key of the new relation (Advisor).
Therefore, these two relations are not only in 3NF, but also in BCNF, since there is only one
candidate key (the primary key) in each relation.

Student
SID Advisor Maj_GPA
123 Satish 4.0
123 Mahesh 3.3
456 Krishna 3.2
789 Praveen 3.7
123 Satish 3.5

Advisor

Advisor Major
Satish Physics
Mahesh English
Krishna Literature
Praveen English
Satish Physics

SID Advisor Maj_GPA Advisor Maj_GPA

The Comparison of 3NF and BCNF can therefore be summarized as follows:

If a relation has only one candidate key (which therefore becomes the primary key), then 3NF
and BCNF are equivalent.
The two resulting relations Student and Advisor with sample data are shown. Then these are free
of data maintenance anomalies.

140 LOVELY PROFESSIONAL UNIVERSITY