Boyce-Codd Normal Form (BCNF): Stricter Than 3NF

WHY IT EXISTS Third Normal Form (3NF) eliminates most data anomalies, but it can miss certain redundancies, especially in tables with multiple, overlapping candidate keys. BCNF was created as a stricter standard to handle these edge cases, removing all redundancies that arise from functional dependencies.

THE MENTAL MODEL For every non-trivial functional dependency (where one set of attributes determines another, like UserID → Email), the determinant on the left side must be a superkey for the table. This means only a full candidate key, or a superset of one, can be the source of a dependency. Nothing else can determine any other attribute.

HOW IT WORKS To check for BCNF, you identify all functional dependencies in a relation. For each dependency X → Y, you verify if X is a superkey. If you find even one dependency where X is not a superkey, the relation is not in BCNF. The solution is to decompose the relation into smaller tables until all of them satisfy the BCNF condition.

WHEN TO USE IT Use BCNF when the highest level of data integrity is non-negotiable and you must eliminate every possible update or deletion anomaly caused by functional dependencies. It is the target for most Online Transaction Processing (OLTP) database schemas.

WHEN NOT TO USE IT The primary footgun is the trade-off with dependency preservation. Sometimes, decomposing a table to achieve BCNF means you can no longer enforce an original functional dependency using a key constraint within a single table. In these rare cases, a designer might intentionally settle for 3NF to keep all dependencies enforceable.

ONE CANONICAL EXAMPLE Consider a table tracking which professor teaches which course to a student: Enrollments(StudentID, Course, Professor). Let's say a professor teaches only one course. This gives us two dependencies: {StudentID, Course} → Professor, and Professor → Course. The second dependency violates BCNF because 'Professor' is not a superkey. To fix this, you would decompose the table into two: StudentProfessor(StudentID, Professor) and ProfessorCourse(Professor, Course). Both new tables are in BCNF, but you can no longer enforce the original {StudentID, Course} → Professor dependency with a single primary key.