Third Normal Form (3NF)

3NF works by forbidding a non-key column from being decided by another non-key column: if attribute A can be looked up from some set X that is neither the key nor part of a key, then A and X belong in their own table, so each independent fact is stored exactly once and an update touches exactly one row.

The running example, stage 3

This is the last stage of the one messy enrollment sheet the 1NF and 2NF pages have been carrying. Recap: 1NF split the comma-list Courses cell into one row per enrollment (widening the key to {Student_ID, Course_ID}); 2NF moved everything determined by Student_ID alone out of the enrollment rows, producing Enrollment(Student_ID, Course_ID) plus the student table below — which this page calls Student_Department, with the department columns the 2NF page abbreviated now written out in full:

2NF is satisfied — the key is now the single column Student_ID, so no partial dependency is even possible. Yet rows 101 and 102 still both spell out Science / Building A, because the FD Department_ID → Department_Name, Department_Location survived the 2NF split and Department_ID is not a key. That two-hop chain — key → non-key → other non-keys — is this page's violation, and the walkthrough below splits it into Student and Department, finishing the journey from one spreadsheet to a fully 3NF schema.

The mechanism: kill the indirect path to the key

A transitive dependency is a two-hop chain. The key reaches a non-key attribute, and that non-key attribute in turn reaches a third attribute. The third attribute is now glued to the key only through the middle hop — so every row that repeats the middle value is forced to repeat the third value too. That repetition is the redundancy 3NF removes.

Consider Student_Department, with Student_ID as the only key:

The functional dependencies are:

• Student_ID → Department_ID (key decides the department a student is in)
• Department_ID → Department_Name, Department_Location (a non-key column decides two others)

Chaining these gives Student_ID → Department_ID → {Department_Name, Department_Location}. Department_Name and Department_Location depend on the key only transitively, through the non-key Department_ID. The visible symptom: rows 101 and 102 both repeat Science / Building A. Move Science to Building C and you must hunt down every student row, or the table silently disagrees with itself.

The decomposition, traced

Student

Department

Now Science / Building A is stored once. Moving Science to Building C is a single-row UPDATE on Department. Student still joins back on Department_ID, so no fact was lost — this is a lossless, dependency-preserving split, the property a 3NF decomposition is guaranteed to have.

The formal definition (beyond the cookbook)

The "non-key depends on non-key" rule is a shortcut. The precise condition is per dependency. A relation is in 3NF if, for every non-trivial functional dependency X → A, at least one of these holds:

1. X is a superkey (it determines the whole row — the legitimate case), or
2. A is a prime attribute — A is part of some candidate key.

Test it on the bad table: for Department_ID → Department_Name, Department_ID is not a superkey (it doesn't determine Student_Name), and Department_Name is not prime (it's in no candidate key). Both escape hatches fail, so the FD violates 3NF — exactly matching the transitive-dependency diagnosis.

The second clause is the deliberate leniency that distinguishes 3NF from BCNF. BCNF drops clause 2 entirely: for every non-trivial X → A, X must be a superkey, full stop. So a dependency whose right side is a prime attribute is allowed in 3NF but can still violate BCNF. That is why a 3NF table is not automatically in BCNF — and why BCNF, while stricter, sometimes cannot be reached without sacrificing dependency preservation, a trade-off the next lesson explores.

Pitfalls

• Trusting the shortcut over the formal rule. "Non-key determines non-key" misfires when the determined attribute is itself part of a candidate key (a prime attribute). Such a dependency is fine in 3NF. Always fall back to the superkey-OR-prime test before declaring a violation.
• Decomposing without identifying candidate keys first. You cannot apply the rule until you know every candidate key — "prime" is defined relative to them. Engineers skip key analysis, eyeball the columns, and split the wrong way.
• Forgetting the foreign key. If Student drops Department_ID instead of keeping it as an FK to Department, the split becomes lossy — you can no longer reconstruct which student is in which department. The middle-hop attribute must survive as the join column.
• Assuming 3NF means no anomalies at all. 3NF still permits BCNF-violating overlapping-candidate-key situations. Reaching 3NF removes transitive-dependency anomalies, not every redundancy a schema can hold.
• Over-normalizing read-heavy paths. Each split adds a join. On hot, read-dominated queries teams sometimes deliberately denormalize back, accepting controlled redundancy for latency — a conscious trade-off, not an accident.

Takeaways

• 3NF removes the two-hop dependency: a non-key attribute reached only through another non-key attribute gets its own table, so each independent fact lives in one row.
• The formal test is per FD: for every X → A, X is a superkey OR A is prime. The "non-key → non-key" phrasing is just the common-case shorthand.
• Keep the linking attribute as a foreign key so the split stays lossless and dependency-preserving.
• BCNF is strictly stronger: it deletes the "A is prime" escape hatch and demands X be a superkey for every dependency.

Re-authored and deepened for this guide. Built on E. F. Codd's normal-form work and Carlo Zaniolo's modern statement of 3NF (superkey-or-prime); cross-checked against Silberschatz, Korth & Sudarshan, Database System Concepts (7th ed.), Garcia-Molina, Ullman & Widom, Database Systems: The Complete Book, and the PostgreSQL documentation on schema design. The original worked example (Student → Department transitive dependency and its decomposition) was correct and is preserved; added the formal definition, the mechanism diagram, the 3NF-vs-BCNF distinction, and engineering pitfalls.