What is BCNF and how is it stricter than 3NF?
A table is in BCNF if, for every non-trivial dependency X -> Y, X is a superkey. 3NF also lets Y be a prime attribute, which BCNF does not allow, so BCNF removes a few anomalies that 3NF misses.
Explain it simply
3NF says every non-key column depends on the key, the whole key and nothing but the key. BCNF says even key columns may only be determined by a key. The gap appears when a table has overlapping candidate keys.
Example
A college assigns each teacher to one subject, but a subject can have several teachers. A student takes a subject with one teacher.
Table enrol(student, subject, teacher) with rules:
(student, subject) -> teacherteacher -> subject
Candidate keys: (student, subject) and (student, teacher). The dependency teacher -> subject has the right side subject, which is part of a key (a prime attribute), so the table is in 3NF. But teacher is not a superkey, so it violates BCNF.
The problem: if Mr. Sen changes his subject, every row for Mr. Sen must change.
BCNF decomposition:
teaches(teacher, subject)with key teacherenrol(student, teacher)
The cost: the rule (student, subject) -> teacher can no longer be enforced by a key alone, so BCNF may lose a dependency.
What to say in the interview
"BCNF requires the left side of every non-trivial dependency to be a superkey. It differs from 3NF only when candidate keys overlap. BCNF may not preserve all dependencies, which is why 3NF is often the practical target."
Common mistakes
- Claiming every 3NF table is in BCNF. The reverse is true: BCNF implies 3NF.