Explain BCNF with an example in DBMS

DBMSDatabaseBig Data Analytics

BCNF (Boyce Codd Normal Form) is the advanced version of 3NF. A table is in BCNF if every functional dependency X->Y, X is the super key of the table. For BCNF, the table should be in 3NF, and for every FD. LHS is super key.

Example

Consider a relation R with attributes (student, subject, teacher).

StudentTeacherSubject
JhansiP.NareshDatabase
jhansiK.DasC
subbuP.NareshDatabase
subbuR.PrasadC
F: { (student, Teacher) -> subject
(student, subject) -> Teacher
Teacher -> subject}

Candidate keys are (student, teacher) and (student, subject).

The above relation is in 3NF [since there is no transitive dependency]. A relation R is in BCNF if for every non-trivial FD X->Y, X must be a key.

The above relation is not in BCNF, because in the FD (teacher->subject), teacher is not a key. This relation suffers with anomalies −

For example, if we try to delete the student Subbu, we will lose the information that R. Prasad teaches C. These difficulties are caused by the fact the teacher is determinant but not a candidate key.

Decomposition for BCNF

Teacher-> subject violates BCNF [since teacher is not a candidate key].

If X->Y violates BCNF then divide R into R1(X, Y) and R2(R-Y).

So R is divided into two relations R1(Teacher, subject) and R2(student, Teacher).

R1

TeacherSubject
P.Nareshdatabase
K.DASC
R.PrasadC

R2

StudentTeacher
JhansiP.Naresh
JhansiK.Das
SubbuP.Naresh
SubbuR.Prasad

All the anomalies which were present in R, now removed in the above two relations.

Note

BCNF decomposition does not always satisfy dependency preserving property. After BCNF decomposition if dependency is not preserved then we have to decide whether we want to remain in BCNF or rollback to 3NF. This process of rollback is called denormalization.

raja
Published on 06-Jul-2021 12:14:58
Advertisements