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
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

R2

StudentTeacher
JhansiP.Naresh
JhansiK.Das
SubbuP.Naresh