Boyce-Codd Normal Form (BCNF)
What you'll learn: You'll understand how BCNF strengthens 3NF by eliminating subtle dependency anomalies that 3NF sometimes misses.
What is BCNF?
Boyce-Codd Normal Form (BCNF) is a stricter version of Third Normal Form (3NF) that handles edge cases where 3NF still allows certain anomalies. While 3NF requires that non-key attributes depend only on the primary key, BCNF goes further: every determinant must be a candidate key.
A determinant is any attribute (or set of attributes) that functionally determines another attribute. In simpler terms: if knowing column A tells you column B, then A is a determinant.
The Key Difference from 3NF
3NF allows non-key attributes to determine other non-key attributes in specific situations. BCNF says: "If an attribute determines anything else, it must be a candidate key (potential primary key)."
When 3NF Isn't Enough
Imagine a university table tracking professors, courses, and their office hours:
- A professor teaches one course
- Each course has multiple professors
- Each professor uses only one office
If you store (Professor, Course, Office), you might have Professor → Office (professor determines office) but Professor alone isn't a candidate key. This creates anomalies: updating a professor's office requires finding all their rows.
In BCNF, you'd split this into separate tables where each determinant (like Professor) becomes a primary key in its own table.
The Trade-off
BCNF is theoretically cleaner, but achieving it sometimes requires more tables and joins. Most real-world databases stop at 3NF because BCNF violations are rare and the performance cost may not justify the added complexity.
Key Takeaway: BCNF eliminates all dependency anomalies by requiring that every determinant be a candidate key—a stricter rule than 3NF that prevents subtle update anomalies but may require more complex table structures.