A topic in the Open Knowledge Graph — a free, open map of 15,290 topics and the order to learn them in.
Boyce-Codd Normal Form (BCNF) is a stricter form of 3NF where every determinant is a candidate key. Fourth and Fifth Normal Forms address multivalued and join dependencies. While theoretically superior, BCNF and higher forms may not always be practical; understanding when to stop normalizing is crucial.
You already understand normalization through Third Normal Form: 1NF eliminates repeating groups, 2NF removes partial dependencies, and 3NF removes transitive dependencies. Boyce-Codd Normal Form (BCNF) takes the same underlying principle — every fact should be stored exactly once — and states it in its purest form: for every non-trivial functional dependency X → Y in a relation, X must be a superkey. In other words, the only thing that can functionally determine other attributes is a key (or a superset of a key). This is stricter than 3NF, which allows a non-key attribute to be a determinant as long as the dependent attribute is part of a candidate key.
The gap between 3NF and BCNF shows up in schemas with overlapping candidate keys. Consider a relation Tutoring(Student, Subject, Instructor) where each instructor teaches only one subject (Instructor → Subject), and each student-subject pair has one instructor ({Student, Subject} is the primary key). This relation is in 3NF — Subject is part of a candidate key, so Instructor → Subject doesn't violate the 3NF rule. But it violates BCNF because Instructor is not a superkey, yet it determines Subject. The redundancy is real: if an instructor teaches 30 students, the subject is stored 30 times. To reach BCNF, decompose into (Instructor, Subject) and (Student, Instructor).
The tradeoff with BCNF is dependency preservation. After decomposing to BCNF, you may no longer be able to enforce all original functional dependencies using single-table constraints. In the example above, the constraint "each student has one instructor per subject" now spans two tables and requires a join to verify. This is why 3NF is sometimes the pragmatic stopping point — it guarantees both lossless-join decomposition and dependency preservation, while BCNF guarantees only lossless-join.
Beyond BCNF, Fourth Normal Form (4NF) addresses multivalued dependencies — situations where one attribute independently determines two or more sets of values. Fifth Normal Form (5NF) handles join dependencies, where a table can be decomposed into three or more tables and reconstructed only through a natural join of all of them. In practice, most database designers normalize to 3NF or BCNF and stop there. The higher forms are theoretically important but rare in real schemas, and over-normalization can hurt query performance by requiring excessive joins. The art of schema design is knowing when the reduction in redundancy is worth the added complexity.