Boyce-Codd Normal Form (BCNF)
Boyce-Codd normal form (BCNF) is a stricter version of third normal form: for every non-trivial functional dependency A -> B in a table, A must be a superkey. Unlike 3NF, it allows no exception for a B that is part of a candidate key, a difference that only shows up in tables with overlapping candidate keys.
Boyce-Codd Normal Form (BCNF) is a stricter version of Third Normal Form, designed to close a narrow edge case that 3NF's definition can technically miss. It comes up in a specific, less common scenario: a table with multiple overlapping candidate keys.
The rule, precisely
A table is in BCNF if, for every non-trivial functional dependency A -> B in the table, A is a superkey, meaning A alone is enough to uniquely identify a row.
3NF's rule is slightly looser: it allows a functional dependency A -> B where A isn't a superkey, as long as B is part of some candidate key. BCNF removes that exception entirely: the left-hand side of every dependency must be a superkey, no exceptions.
When does 3NF allow something BCNF doesn't
This edge case specifically shows up when a table has multiple candidate keys that overlap (share a column). Consider a table tracking which instructor teaches which subject in which room, with the rule that each instructor teaches only one subject, but a subject can be taught by multiple instructors, and a room can host multiple subjects:
| instructor | subject | room |
|---|---|---|
| Dr. Rao | Databases | R101 |
| Dr. Lee | Databases | R102 |
Here, {subject, room} -> instructor (given a subject and room, only one instructor teaches it there) and instructor -> subject (each instructor teaches only one subject) both hold as real functional dependencies.
The candidate keys are {subject, room} (it determines instructor) and {instructor, room} (instructor gives subject, so instructor plus room determines the whole row). Every attribute belongs to one of these keys, so the table is in 3NF. It is not in BCNF, because instructor -> subject has instructor on the left side, and instructor alone is not a superkey of this table (it doesn't determine room). Since instructor isn't a superkey, but it still determines another attribute (subject), this violates BCNF's stricter rule.
Fix: decompose based on the problematic dependency.
instructor_subject
| instructor | subject |
|---|---|
| Dr. Rao | Databases |
| Dr. Lee | Databases |
instructor_room
| instructor | room |
|---|---|
| Dr. Rao | R101 |
| Dr. Lee | R102 |
This decomposition is lossless (the shared column instructor is a key of instructor_subject, so joining back on instructor reconstructs the original rows) and both tables satisfy BCNF: in instructor_subject, instructor is the key; in instructor_room, the key is the pair {instructor, room}, because one instructor can teach in several rooms. Neither table has a non-trivial dependency whose left side is not a superkey.
The real tradeoff: BCNF and dependency preservation
The decomposition above is lossless, but it does not preserve the dependency {subject, room} -> instructor. No single table holds subject, room and instructor together, so the database can no longer enforce "one instructor per subject in a room" with a key on one table: two instructors of the same subject could both be booked in R101, and only a join of the two tables would reveal it. This is not a flaw in this example; no BCNF decomposition of this table preserves that dependency. It is a known tradeoff in database theory, and one of the real reasons many practitioners treat 3NF as the practical sweet spot and only push to BCNF when a specific, narrow integrity concern calls for it.
Where this matters in real systems
BCNF violations are rare in typical business schemas (most tables don't have the kind of overlapping-candidate-key structure that triggers the difference from 3NF), but they show up more often in specialized scheduling, timetabling, or resource-allocation systems, exactly the kind of "who teaches what where" structure in the example above. Most production schemas stop at 3NF and only reach for BCNF when this specific overlapping-key pattern genuinely appears.
What interviewers actually ask about BCNF
- "What's the difference between 3NF and BCNF?" BCNF requires the left side of every functional dependency to be a superkey, with no exception; 3NF allows a narrow exception when the right side is part of some candidate key.
- "Give an example of a table in 3NF but not BCNF." The instructor/subject/room example (or an equivalent with overlapping candidate keys) is the classic textbook case.
- "Is BCNF always achievable without losing dependency preservation?" No, some BCNF decompositions are lossless but not fully dependency-preserving, a real, known tradeoff.
- "Why do many real schemas stop at 3NF instead of going to BCNF?" Because the edge case BCNF addresses is narrow and uncommon in typical business data, and pushing to BCNF can sometimes sacrifice dependency preservation.
Key takeaways
- BCNF requires that for every functional dependency
A -> B, A must be a superkey, with no exceptions. - The 3NF/BCNF difference only shows up in tables with multiple overlapping candidate keys, a relatively rare real-world pattern.
- A BCNF decomposition is always achievable losslessly, but isn't always fully dependency-preserving, a genuine theoretical and practical tradeoff.
- Most production schemas target 3NF and only push to BCNF for specific integrity concerns tied to overlapping-key structures.
More in RDBMS Design
Browse all DBMS topics
Log in to save which topics you've read across every subject.
Frequently asked questions
What's the difference between 3NF and BCNF?
BCNF requires the left side of every functional dependency to be a superkey, with no exception; 3NF allows a narrow exception when the right side is part of some candidate key.
Give an example of a table in 3NF but not BCNF.
The instructor/subject/room example (or an equivalent with overlapping candidate keys) is the classic textbook case.
Is BCNF always achievable without losing dependency preservation?
No, some BCNF decompositions are lossless but not fully dependency-preserving, a real, known tradeoff.
Why do many real schemas stop at 3NF instead of going to BCNF?
Because the edge case BCNF addresses is narrow and uncommon in typical business data, and pushing to BCNF can sometimes sacrifice dependency preservation.