CSE Roadmap

Core tier · about 4 hours · 5 parts

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.