BCNF
Every determinant is a candidate key. Stricter than 3NF; sometimes you lose a dependency.
Level: intermediate
What is it?
Boyce-Codd Normal Form (BCNF) requires that for every nontrivial functional dependency X→Y, X is a superkey. It is stricter than 3NF and focuses on whether every determinant can identify a row.
Why does it matter?
Some relations satisfy 3NF yet retain redundancy because a non-superkey determinant determines another attribute. BCNF removes that class of dependency anomaly, though a decomposition can lose dependency preservation.
Mental model
For each meaningful determinant, ask whether knowing it identifies an entire tuple. If not, the dependency may require decomposition under BCNF.
How it works
- A superkey functionally determines all attributes of the relation; a candidate key is a minimal superkey.
- BCNF is violated by any nontrivial dependency whose determinant is not a superkey.
- BCNF decomposition is lossless under the standard decomposition step based on a violating dependency.
- BCNF decomposition may fail to preserve every original dependency, so constraints can become harder to enforce without joins.
- Formal analysis needs the set of functional dependencies, not just observed example rows.
Steps
- Determine candidate keys and relevant functional dependencies.
- Find a nontrivial dependency X→Y where X is not a superkey.
- Decompose the relation into projections that separate the violating dependency.
- Verify the decomposition is lossless, then check which dependencies remain locally enforceable.
Real-world usage
- Advanced database normalization problems.
- Evaluating the trade-off between BCNF and dependency-preserving 3NF schemas.
Common mistakes
- Assuming BCNF always preserves every functional dependency.
- Confusing superkeys with minimal candidate keys.
- Inferring functional dependencies only from a small set of sample tuples.
Interview questions
What condition defines BCNF?
For every nontrivial functional dependency X→Y, X must be a superkey.
What is one trade-off of BCNF decomposition?
It is lossless for the standard violating-dependency decomposition but may not preserve all dependencies locally, requiring joins or additional enforcement.