Part 4 of 5 · 2 min read
Third normal form (3NF) and BCNF
No transitive dependency, and BCNF's stricter version of the same idea.
Third normal form (3NF) and BCNF
Think of a form that asks for a customer's postal code, and then also asks for their city, even though the postal code alone already determines the city.
In plain words: 3NF requires 2NF, plus no transitive dependency: a non-key column cannot depend on another non-key column. BCNF requires that for every functional dependency X → Y in the table, X must be a candidate key. BCNF is stricter than 3NF, and the two differ only in a specific edge case involving overlapping candidate keys.
A worked example. customers (customer_id, postal_code, city) violates 3NF if postal_code → city, since city transitively depends on customer_id through postal_code, a non-key column. Splitting out a postal_codes (postal_code, city) table satisfies both 3NF and BCNF here.
The key lines.
-- violates 3NF: city transitively depends on customer_id through postal_code
customers (customer_id, postal_code, city)
-- satisfies 3NF and BCNF
customers (customer_id, postal_code)
postal_codes (postal_code, city)
Third normal form (3NF), Boyce-Codd normal form (BCNF)
Third normal form requires 2NF and no transitive dependency through a non-key column. Boyce-Codd normal form requires that every functional dependency's left side be a candidate key, a strictly stronger condition than 3NF.
Example: postal_code → city with customer_id as the key violates 3NF, since city depends on postal_code, a non-key column, rather than directly on the key.
For the curious When 3NF and BCNF actually differ
The two normal forms differ only in a specific, fairly rare case: a table with two overlapping candidate keys, where a dependency holds from part of one candidate key to part of another. Most real schemas satisfying 3NF also satisfy BCNF; the distinction matters mainly in tables with multiple, overlapping unique constraints.
Quick notes
- 1NF: every column holds a single, atomic value, never a packed list.
- 2NF: 1NF plus no partial dependency on a composite key.
- 3NF: 2NF plus no transitive dependency through a non-key column.
- BCNF: every functional dependency's left side must be a candidate key, stricter than 3NF.
- Each normal form rules out a specific, nameable redundancy and the update anomaly it causes.