2.3Boyce-Codd normal form and higher normalization

Even after normalizing to third normal form, a schema where multiple candidate keys interact in complex ways can still retain update anomalies. A database designer must recognize this residual anomaly and judge whether to push further to Boyce-Codd normal form (BCNF), or how to reconcile the "reversibility of information under decomposition" problem that BCNF decomposition can introduce. This section covers the definition of BCNF, the fourth and fifth normal forms needed in rarer cases, and reversibility of decomposition as a theoretical safety net.

2.3.1Boyce-Codd normal form (BCNF)

• The definition of BCNF is: for every functional dependency X to Y present in the table, the determinant X (the left-hand side) is always a candidate key. Third normal form only guarantees that "no non-key attribute is transitively dependent on another non-key attribute," so in cases where multiple candidate keys exist and a non-key attribute determines part of another candidate key, anomalies can remain even when 3NF is satisfied.
• BCNF closes this blind spot and is a stricter normal form than 3NF. Decomposing to BCNF sweeps away every functional dependency whose determinant is not a candidate key, eradicating the update anomalies caused by that dependency.

2.3.2Fourth and fifth normal form

• Fourth normal form (4NF) satisfies BCNF and additionally eliminates multivalued dependency (a relationship in which a single key value is associated with multiple, mutually independent sets of values). For example, if "an employee has multiple skills and, independently, multiple certifications," storing skills and certifications in the same table forces every combination of the two to be stored redundantly; 4NF resolves this by separating them.
• Fifth normal form (5NF) is based on join dependency (the constraint that even if a table is decomposed into multiple tables, joining them back together losslessly reconstructs the original table), and it eliminates redundancy that arises only from combinations of three or more tables. It is rarely encountered in practice and is positioned as the final stage guaranteeing theoretical completeness.

2.3.3Reversibility of decomposition (lossless decomposition)

• Normalization is a process of decomposing a table, but if the decomposition is done incorrectly, joining the decomposed tables back together may fail to correctly reconstruct the original table's information (lossy decomposition). A database designer must always verify that the join keys of the decomposed tables properly maintain a shared candidate-key/foreign-key relationship.
• To guarantee lossless decomposition, the common attribute across the decomposed tables must be a candidate key in at least one of them. BCNF decomposition can preserve losslessness, but it does not always guarantee dependency preservation (that every original constraint can be verified using only the decomposed tables), so a design judgment is needed as to which constraint should be checked in which table after decomposition.

A designer of a university course-registration system is examining an "assignment" table (student ID, course name, instructor ID). Business rules state that "one instructor teaches only one course" (a functional dependency instructor ID to course name holds) and "a student never takes the same course from two different instructors," giving two candidate keys: (student ID, course name) and (student ID, instructor ID). This table satisfies third normal form, since no attribute is transitively dependent on another non-key attribute, but the determinant of the dependency "instructor ID to course name"—instructor ID—is not a candidate key on its own (only the composite keys are candidate keys). This violates the definition of BCNF (every determinant must be a candidate key), and an update anomaly indeed remains: if an instructor's assigned course changes, unless every row for every student taking that instructor's course is updated without omission, contradictory course names can coexist for the same instructor ID. The fix is to decompose into an "instructor assignment" table (instructor ID, course name) and an "enrollment" table (student ID, instructor ID). In this decomposition, the shared attribute, instructor ID, is a candidate key of the "instructor assignment" table, guaranteeing lossless decomposition—joining the two tables correctly reconstructs the original "assignment" table. The designer also recognizes the resulting performance trade-off: any query conditioned only on student ID and course name now always requires a two-table JOIN. In a domain like course registration, where consistency is the top priority, the designer judges that decomposing all the way to BCNF, prioritizing correctness over performance, is the appropriate choice.

2.3.4Section summary

• BCNF requires every functional dependency's determinant to be a candidate key. In a schema with multiple candidate keys, 3NF can be satisfied while BCNF is not
• Fourth normal form eliminates multivalued dependency; fifth normal form eliminates join dependency. Both are rare in practice and guarantee theoretical completeness
• Always verify that a decomposition is lossless (the shared attribute is a candidate key). Decomposing to BCNF can sometimes sacrifice dependency preservation