🔗

Third Normal Form (3NF)

A table is in 3NF when it's in 2NF and no non-key column depends on another non-key column. Break the hidden transitive dependency — "every non-key attribute depends on the key, the whole key, and nothing but the key."

Transitive Dependency Superkey or Prime Decompose Anomaly-Free

Press Next → or use ← → arrow keys

Section 01

The Story — The Office Phone Directory Disaster

🏢 REAL-WORLD ANALOGY

Facts about the department, stored on the employee

A company sheet lists every employee alongside their department's room and head — repeated on every row of that department. When the department moves, hundreds of rows need editing. A brand-new department with no staff yet can't be recorded. And when the last employee leaves, the department's details vanish with them.

💡

The Core Issue

The room and the head were never facts about the employee — they were facts about the department. Storing them on the employee row is exactly the redundancy 3NF removes.

Section 02

The Foundation — Functional Dependencies

➡️

X → Y means "X determines Y"

If you know X's value, Y's value is fixed and unambiguous. One StudentID always maps to exactly one StudentName — but a name can be shared by many IDs. FDs point one direction only.

🔑

Candidate key

A minimal set of attributes that uniquely identifies every row.

🟢

Prime attribute

Any attribute that is part of some candidate key.

⚪

Non-prime attribute

Everything else — attributes in no candidate key at all.

🧪

Semantics Beat Samples

Never declare an FD from data alone. An FD is a business rule about every legal state — confirm it with domain experts, not with today's rows.

Sections 03–04

Quick Recap — Climbing to 2NF

1️⃣

First Normal Form

Every cell holds a single, atomic value — no lists, no repeating groups — and every row is unique. 1NF makes data queryable and is the entry ticket to every higher form.

2️⃣

Second Normal Form

Eliminates partial dependency — a non-prime attribute depending on only part of a composite key. This only ever happens with multi-column keys.

🪜

The Ladder So Far

1NF removes repeating groups → 2NF removes partial dependency. Each form fixes exactly one species of redundancy the previous one couldn't see. Next rung: the redundancy hiding between non-key columns.

Section 05

The Issue 2NF Leaves Behind

This STUDENT table is perfectly in 2NF — the key is single-column, so no partial dependency is even possible. Yet DeptName and DeptHead repeat on every row.

StudentID key	StudentName	DeptID	DeptName	DeptHead
S1	Aarav	D1	Computer Science	Dr. Rao
S2	Diya	D1	Computer Science	Dr. Rao
S3	Kabir	D2	Mathematics	Dr. Bose
S4	Mira	D1	Computer Science	Dr. Rao

⛓️

The Hidden Chain — Transitive Dependency

StudentID → DeptID (direct), then DeptID → DeptName, DeptHead (non-prime determines non-prime). So DeptName reaches the key only indirectly — a transitive dependency 2NF was never built to catch.

Section 05 · Cost

One Transitive Dependency → Three Anomalies

➕

Insertion anomaly

A new department Physics (D3) exists but has no students yet — it can't be recorded without inventing a StudentID.

✏️

Update anomaly

Dr. Rao is replaced as CS head — every CS student row must change. Miss one and the data contradicts itself.

🗑️

Deletion anomaly

Delete Kabir — the only Maths student — and the Mathematics department and Dr. Bose vanish entirely.

🎯

The Culprit

The table satisfies 2NF completely, yet still suffers redundancy and all three anomalies. The culprit is the transitive dependency — and removing it is precisely the job of 3NF.

Section 06

Third Normal Form — The Definition

📘

Formal Definition

A relation is in 3NF if it is already in 2NF and contains no transitive dependency of a non-prime attribute on a candidate key.

For every non-trivial FD X → Y, at least one must hold:

🅰️

Condition A

X is a superkey — it uniquely identifies the row (contains or is a candidate key).

🅱️

Condition B

Y is a prime attribute — it is part of some candidate key.

🧭

The Rule in One Sentence

If you ever find a dependency where the left side is not a superkey and the right side is not a prime attribute, the table breaks 3NF. Bill Kent's mantra: "every non-key attribute depends on the key, the whole key, and nothing but the key — so help me Codd."

Section 07

Breaking the Chain — Before & After

✂️

The Chain Is Broken

Lift the dependent group (DeptName, DeptHead) into its own table beside its true key, leaving DeptID behind as a foreign key. DeptName now lives once. The split is lossless — a join rebuilds the original exactly.

Section 08

Worked Example — Redundant to Lean

Before: one bloated table where "Comp Sci / Rao" repeats. After: two clean tables in 3NF.

❌ STUDENT — before (2NF, redundant)

SID	Name	DeptID	DeptName	Head
S1	Aarav	D1	Comp Sci	Rao
S2	Diya	D1	Comp Sci	Rao
S3	Kabir	D2	Maths	Bose
S4	Mira	D1	Comp Sci	Rao

✅ STUDENT — after

SID	Name	DeptID FK
S1	Aarav	D1
S2	Diya	D1
S3	Kabir	D2
S4	Mira	D1

✅ DEPARTMENT — after

DeptID PK	DeptName	DeptHead
D1	Computer Science	Dr. Rao
D2	Mathematics	Dr. Bose

✔️

Verification & Anomalies Fixed

In STUDENT the only FD is StudentID → Name, DeptID (left side is the key); in DEPARTMENT it's DeptID → DeptName, DeptHead (left side is the key). Both are in 3NF. Now: insert Physics with zero students · update the CS head once · delete the last Maths student and DEPARTMENT stays intact.

Section 09

Second Drill — The Geography Chain

Chains can be longer than one hop. When columns "travel together," suspect a transitive chain.

CandNo key	CandName	State	Country	PinCode
C1	Aarav	Himachal Pradesh	India	173212
C2	Diya	Himachal Pradesh	India	173212
C3	Liam	California	USA	94016

🧩

The Fix & The Lesson

Lift the chain into a LOCATION(PinCode, State, Country) table; keep only PinCode as a foreign key in CANDIDATE. Both tables are now in 3NF. Lesson: follow every arrow until it dead-ends — city/state/country, code/description, product/category/manager — each link is its own violation.

Section 10

2NF vs 3NF — Side by Side

Aspect	2NF	3NF
Target problem	Partial dependency	Transitive dependency
Dependency removed	Non-prime on part of key	Non-prime on another non-prime
Needs composite key?	Yes — multi-attribute keys only	No — happens even with single-column
Prerequisite	Must be in 1NF	Must be in 2NF
Rule of thumb	Depend on the whole key	Depend on nothing but the key
Remaining risk	Transitive chains still allowed	Rare overlapping-key cases → BCNF

🧠

The Mental Model

1NF removes repeating groups · 2NF removes partial dependency · 3NF removes transitive dependency. Each form kills exactly one redundancy species the previous form could not see.

Section 11

Common Pitfalls & Misconceptions

🧮

The derived-column trap

Storing TotalPrice = Qty × UnitPrice is a non-key dependency — a 3NF violation. Compute it in a view, or document as deliberate denormalization.

🔑

"Single key = auto 3NF"

False. A single-column key guarantees 2NF, not 3NF. The Student–Department table proved transitive chains ignore how many key columns exist.

🔁

Missing multi-hop chains

People check one hop and stop. Follow every arrow until it dead-ends — each link is its own violation.

🧪

Judging FDs from data

Semantics beat samples. An FD is a rule about all legal states, not a pattern in today's rows.

🪓

Over-normalizing

Splitting a two-value Status column into its own table adds joins without removing a real transitive dependency. Normalize facts, not vocabulary.

🎖️

Forgetting the "why"

3NF is a means to integrity, not a medal. If a split adds pain for zero integrity gain, you optimized for the certificate, not the database.

Sections 12–13

Two Lenses — and When 3NF Isn't Enough

🎓

Academic lens

Defined by E. F. Codd (1971); the modern two-condition test is Zaniolo's 1982 reformulation. 3NF is provably lossless and dependency-preserving and sits in the hierarchy 1NF ⊂ 2NF ⊂ 3NF ⊂ BCNF.

🏭

Industry lens

The default target for OLTP (banking, orders, inventory) where write integrity matters — smaller tables, leaner indexes. Warehouses deliberately denormalize (star schemas) for read speed. Rule: normalize to 3NF first, denormalize only where profiling proves need.

🔎

BCNF in One Breath

BCNF is stricter: every determinant (FD left side) must be a superkey — no exception for prime attributes. If your table has a single candidate key (most cases), reaching 3NF usually puts you in BCNF for free. Overlapping candidate keys are the edge case 3NF still permits and BCNF tightens.

Section 14

Test Yourself — Three Interview Questions

🎯 SPOT · TRUE-OR-FALSE · THE SUBTLE ONE

Q1

ORDER(OrderID, CustomerID, CustomerCity), key = OrderID, and CustomerID → CustomerCity. In 3NF? → No. The chain OrderID → CustomerID → CustomerCity is transitive. Move City to a CUSTOMER table, keep CustomerID as FK.

Q2

"3NF primarily eliminates partial dependencies." → False. Partial dependencies are 2NF's job; 3NF eliminates transitive ones — the single most common exam slip.

Q3

R(A, B, C) with {A, B} → C and C → B. C is not a superkey — does it break 3NF? → Still in 3NF! For C → B, Condition A fails, but B is part of candidate key {A, B}, so B is prime and Condition B saves it. (This is exactly why BCNF exists.)

Section 15

Golden Rules of Third Normal Form

🏆 NON-NEGOTIABLE PRINCIPLES

1

Reach 2NF first. 3NF is meaningless until partial dependencies are gone — never skip a rung of the ladder.

2

Hunt non-prime → non-prime dependencies. Follow chains multiple hops, not just one.

3

Apply the test: for every FD X → Y, either X is a superkey or Y is a prime attribute — else decompose.

4

Decompose losslessly — lift the dependent group into its own table, leaving the determinant behind as a foreign key.

5

Confirm FDs with the business, never with sample data alone — and watch for stored derived columns.

6

Normalize for correctness, denormalize for measured speed — "the key, the whole key, and nothing but the key."

FINAL

Nothing But the Key

2NF+Prerequisite

0Transitive dependencies

3Anomalies removed

BCNFThe next rung

🎯

The Sweet Spot of Safe Design

3NF makes every non-key column depend on the key and nothing but the key — dissolving the transitive chains, redundancy, and three anomalies 2NF left behind, all via a lossless split linked by foreign keys. For the vast majority of OLTP schemas, that removes essentially all harmful redundancy.

🧠

One Sentence to Remember

If a non-key column is determined by another non-key column, split it out — 3NF means every non-key attribute depends on the key, the whole key, and nothing but the key. The one tighter step left is BCNF.

🔗 End of tutorial · Press ← to review, or click Restart