Third Normal Form and Transitive Dependencies

Learning outcomes

Third Normal Form (3NF) addresses dependencies where non-key facts determine other non-key facts. A useful intuition is that each non-key attribute should describe the key, the whole key, and not an independent non-key determinant—while the formal definition handles multiple candidate keys more precisely.

Classic transitive dependency

WorkOrder(  work_order_id,  status_code,  status_name,  opened_at)

Dependencies:

\[ work\_order\_id \rightarrow status\_code,opened\_at \]

\[ status\_code \rightarrow status\_name \]

Status name does not describe WorkOrder independently; it describes Status.

3NF decomposition

WorkOrder(  work_order_id,  status_code,  opened_at)WorkOrderStatus(  status_code,  status_name)

Now the status label is stored once under its determinant.

Formal 3NF condition

For every non-trivial dependency:

\[ X \rightarrow A \]

a relation is in 3NF if at least one is true:

1. X is a superkey; or
2. A is a prime attribute.

This definition handles relations with multiple candidate keys more accurately than slogans.

Why prime attributes matter

An attribute may be non-primary yet still be part of another candidate key. Formal 3NF permits some dependencies involving such prime attributes that BCNF would reject. Chapter 9 examines that distinction.

Manufacturer example

Asset(  asset_id,  manufacturer_id,  manufacturer_name,  serial_number)

If:

\[ asset\_id \rightarrow manufacturer\_id,serial\_number \]

and:

\[ manufacturer\_id \rightarrow manufacturer\_name \]

then manufacturer_name is transitively dependent on asset_id.

Separate Manufacturer

Manufacturer(  manufacturer_id,  manufacturer_name)Asset(  asset_id,  manufacturer_id,  serial_number)

3NF and reference data

Reference entities often emerge from transitive dependencies:

status_code -> status_namecountry_code -> country_namecurrency_code -> currency_namefailure_code -> failure_description

If the right-side facts are governed by the code, store them with the code.

Not every code needs a table

Normalization tells us about dependency structure, not necessarily implementation form. A tiny fixed domain may still be better implemented as a check constraint than a separate lookup table. Formal normal form and practical modeling are related but not identical.

3NF synthesis intuition

Given a minimal cover of dependencies, a standard synthesis approach creates relations that contain determinants and their dependents, ensuring a candidate key is represented if necessary. This tends to preserve dependencies and produce 3NF.

Minimal cover preview

A minimal/canonical cover simplifies a dependency set so that:

• each right side is a single attribute;
• left sides contain no extraneous attributes;
• no dependency is redundant.

This makes synthesis more systematic.

WorkshopHub example

WorkOrderReport(  work_order_id,  asset_id,  serial_number,  status_code,  status_name)

Dependencies:

work_order_id -> asset_id, status_codeasset_id -> serial_numberstatus_code -> status_name

3NF decomposition:

WorkOrder(work_order_id, asset_id, status_code)Asset(asset_id, serial_number)WorkOrderStatus(status_code, status_name)

Why the decomposition is good

• Each relation has a clear grain.
• Each determinant governs its own descriptive facts.
• Updates happen once.
• Independent entities can be inserted independently.
• Lossless joins reconstruct the original report view when needed.

3NF and denormalized reporting tables

A reporting warehouse may intentionally store flattened dimensions or snapshots. That does not mean 3NF is irrelevant; it means redundancy is deliberate and managed for a different workload. This course later covers denormalization explicitly.

Do not confuse 3NF with “every table has an ID”

A table with a surrogate ID can still contain:

idstatus_codestatus_namemanufacturer_idmanufacturer_name

and still violate the underlying dependency structure.

Practice: 3NF decomposition

Employee(  employee_id,  department_id,  department_name,  department_location)

Summary and next lesson

3NF removes problematic non-key determinant chains and places independently governed facts with their own keys. The next lesson combines 1NF, 2NF, and 3NF in one realistic requirements-to-schema walkthrough.

References

• E. F. Codd, foundational normalization work.
• C. J. Date, Database Design and Relational Theory.
• Jeffrey Ullman and Jennifer Widom, A First Course in Database Systems.