What is Boyce-Codd Normal Form (BCNF)?
Understand BCNF, how it differs from 3NF, and see a worked SQL example of decomposing a table to satisfy it.
Expected Interview Answer
Boyce-Codd Normal Form (BCNF) is a stricter version of 3NF requiring that for every non-trivial functional dependency X to Y in a table, X must be a superkey — meaning even candidate-key columns cannot depend on a non-superkey attribute.
A table can satisfy 3NF yet still have anomalies when it has multiple overlapping candidate keys, because 3NF only restricts dependencies on non-key columns while allowing a candidate key to depend on a non-superkey determinant. BCNF closes that gap: every determinant of every functional dependency must itself be able to uniquely identify the row. In practice, BCNF violations show up in tables with composite candidate keys where one column determines another that is also part of a key, and fixing them means decomposing the table further, sometimes at the cost of losing the ability to enforce a dependency with a single constraint.
- Removes anomalies that survive 3NF in overlapping-key tables
- Guarantees every determinant can act as a unique row identifier
- Produces the cleanest possible lossless decomposition for such cases
- Clarifies exactly which functional dependencies exist in the schema
AI Mentor Explanation
Suppose a table keys rows by (Stadium, TimeSlot) to book nets for practice, and Stadium alone determines which GroundsKeeper is on duty, even though GroundsKeeper is not part of the key. This table can satisfy 3NF if GroundsKeeper is treated loosely, but it violates BCNF because Stadium, the determinant, is not a superkey by itself. Splitting into a StadiumStaff table keyed by Stadium removes the anomaly where changing grounds keepers requires updating many booking rows.
Step-by-Step Explanation
Step 1
List all functional dependencies
Identify every column or set of columns that determines another column.
Step 2
Check each determinant
For every dependency X to Y, verify whether X is a superkey of the table.
Step 3
Flag violations
Any dependency where the determinant is not a superkey violates BCNF, even if the table already satisfies 3NF.
Step 4
Decompose losslessly
Split the table so the violating determinant becomes the key of its own table, preserving a lossless join.
What Interviewer Expects
- Correct statement that BCNF requires every determinant to be a superkey
- Ability to construct or recognize a table that is 3NF but not BCNF
- Understanding of the trade-off: BCNF decomposition can lose dependency preservation
- Clarity on when BCNF matters in practice versus 3NF being sufficient
Common Mistakes
- Claiming BCNF and 3NF are always equivalent
- Failing to identify overlapping candidate keys as the trigger for BCNF issues
- Not knowing that BCNF decomposition can sacrifice dependency preservation
- Confusing a superkey with a candidate key when checking a determinant
Best Answer (HR Friendly)
“BCNF is a tighter rule than 3NF. It says that whenever one set of columns determines another column, that determining set must be able to uniquely identify the whole row by itself. Some tables pass 3NF but still have a column determining part of the key without being a full key itself, and those need to be split further to reach BCNF.”
Code Example
-- StudentId + Subject is the key; Subject alone determines Instructor
-- (one instructor teaches only one subject, but many subjects exist)
CREATE TABLE Enrollment (
student_id INT,
subject VARCHAR(50),
instructor VARCHAR(100), -- determined by subject, not by the full key
PRIMARY KEY (student_id, subject)
);
-- Violates BCNF: "subject" is a determinant but not a superkey
-- Decomposed to satisfy BCNF:
CREATE TABLE SubjectInstructor (
subject VARCHAR(50) PRIMARY KEY,
instructor VARCHAR(100)
);
CREATE TABLE StudentSubject (
student_id INT,
subject VARCHAR(50) REFERENCES SubjectInstructor(subject),
PRIMARY KEY (student_id, subject)
);Follow-up Questions
- How does BCNF differ from 3NF in the treatment of candidate keys?
- Can a BCNF decomposition fail to preserve all original functional dependencies?
- What is a superkey versus a candidate key?
- When would you accept a 3NF design instead of pushing to BCNF?
MCQ Practice
1. BCNF strengthens 3NF by requiring that:
BCNF requires that for every functional dependency X to Y, X must be a superkey of the table.
2. A table satisfying 3NF but not BCNF typically has:
BCNF violations under 3NF usually arise from overlapping composite candidate keys where part of a key is determined by a non-superkey attribute.
3. A known trade-off of decomposing a table to BCNF is:
BCNF decomposition guarantees a lossless join but is not always dependency-preserving, unlike 3NF decomposition.
Flash Cards
What does BCNF require? — Every determinant of a functional dependency must be a superkey of the table.
How is BCNF stricter than 3NF? — It removes the 3NF exception that lets a candidate key depend on a non-superkey attribute.
When do BCNF violations typically appear? — In tables with overlapping composite candidate keys.
What can BCNF decomposition sacrifice? — Dependency preservation, even though it stays lossless.