Normalization — 1NF, 2NF, 3NF, BCNF — anomalies each resolves: Worked examples

This page is the practice arena for the parent topic. We march through every kind of table normalization can hand you and normalize it by hand. Before each answer, you forecast — commit to a guess, then check.

Everything here leans on Functional Dependencies (the arrow $X \to Y$ ) and Candidate Keys and Superkeys (what "minimal determines everything" means). If those words feel shaky, read those first — I re-anchor them below whenever they matter.

The scenario matrix

Every normalization problem is one (or a blend) of these case classes. The 8 examples below are labelled with the cell they hit — together they touch every row.

#	Case class	What makes it tricky	Covered by
C1	1NF violation — non-atomic cell / repeating group	value is a list, not one thing	Ex 1
C2	2NF violation — partial dependency on a composite key	key has ≥2 cols, a non-prime hangs off part	Ex 2
C3	3NF violation — transitive dependency via a non-key middleman	non-prime → non-prime	Ex 3
C4	BCNF-but-3NF — the "prime loophole"	non-superkey determinant, but target is prime	Ex 4
C5	Degenerate: single-column key	cannot violate 2NF; is it already BCNF?	Ex 5
C6	Multiple candidate keys / overlapping keys	which columns are prime?	Ex 6
C7	All-key relation (every column is prime)	is it automatically BCNF?	Ex 7
C8	Real-world word problem + exam twist (dependency-preservation trap)	BCNF may lose an FD → stop at 3NF	Ex 8

The limiting cases to keep in mind throughout (each appears below):

Fewest columns possible → single-attribute key, always ≥ 2NF (C5).
Everything is a key → all-key relation, always BCNF (C7).
Nothing but trivial FDs → already BCNF, nothing to do (C7).

Figure — Normalization — 1NF, 2NF, 3NF, BCNF — anomalies each resolves

Figure s01 — the ladder of normal forms drawn as a tightening screw. Four cyan boxes (1NF, 2NF, 3NF) and one amber box (BCNF) sit left-to-right, joined by white arrows. Under each arrow an amber caption names the offending arrow that step removes: "kill lists" (1NF→2NF), "kill part-key" (2NF→3NF), "kill non-key middleman" (3NF→BCNF). The top line states the subset chain $BCNF \subset 3NF \subset 2NF \subset 1NF$ — each form is strictly stronger. Read it left to right: every example below moves you one box rightward by killing exactly one kind of bad dependency.

Use this figure as your map for the whole page: whenever an example "fixes 2NF" it is walking you from the first arrow to the second box, and so on. Each example below tells you which arrow of the screw diagram it is walking you across.

Worked example

Ex 1 — C1: the list-in-a-cell (1NF) Author(name, nationality, books = "Dune; Messiah; Children"). Here name and nationality are ordinary one-value columns; the books cell holds a semicolon-separated list. Normalize to 1NF.

Forecast: how many tables will you have after the fix, and what is the key of the new one?

Spot the non-atomic cell. books is a list, not one value. name and nationality are already atomic. Why this step? 1NF's only demand is "every cell holds exactly one value"; a list is the textbook violation.
Ask the queries the list blocks. "Which authors wrote Messiah?" needs a LIKE '%Messiah%' hack; you can't index it, can't join on it, can't delete one book cleanly. Why this step? The reason 1NF exists is these broken operations — naming them proves it's a real problem, not pedantry.
Explode the list into rows. Make Wrote(name, book), one book per row: (Asimov, Dune) (Asimov, Messiah) (Asimov, Children). Why this step? One fact = one row is the whole recipe. The key is the whole pair {name, book} because both together identify the row and neither alone does.
Result: Author(name, nationality) keeps only the atomic per-author facts; Wrote(name, book) holds the exploded list, with name as a foreign key back to Author. Why this step? Splitting keeps each kind of fact in its own table — one author fact per author, one authorship fact per book — so neither is duplicated when the other changes.

Screw-diagram position: this moves you across the first arrow ("kill lists") into the 2NF box of figure s01.

Verify: count books = count rows in Wrote for that author (3). "Who wrote Messiah?" is now SELECT name FROM Wrote WHERE book='Messiah' — one clean equality. ✅ 2 tables (Author(name, nationality), Wrote(name, book)); new key = {name, book}.

Worked example

Ex 2 — C2: partial dependency (2NF) Enroll(StudentID, CourseID, Grade, StudentName, DeptOfCourse). Rules: a (StudentID, CourseID) pair has one Grade; a StudentID has one StudentName; a CourseID belongs to one DeptOfCourse.

Forecast: the candidate key is {StudentID, CourseID}. Guess which two attributes are stored redundantly, and how many tables you'll finish with.

Find the candidate key. {StudentID, CourseID} together determine everything; neither alone does. So it is the candidate key — a composite (2-column) key. Why this step? 2NF can only be broken when a "part" of a key exists — you need ≥ 2 columns.
List the FDs and mark determinants that are proper parts of the key. To keep the arrows short I write $S I D$ for StudentID and $C I D$ for CourseID from here on: ${S I D, C I D} \to G r a d e,$ $S I D \to S t u d e n tN am e, C I D \to D e ptO f C o u r se .$ Both $S I D$ and $C I D$ are proper subsets of the key ${S I D, C I D}$ . StudentName and DeptOfCourse are non-prime (in no candidate key). Why this step? 2NF forbids exactly this: a non-prime attribute depending on a proper subset of a candidate key.
Name the anomaly. StudentName repeats for every course the student takes → rename = edit many rows (update anomaly); drop the last enrollment = lose the name (delete anomaly). Why this step? Naming the concrete bug proves the FD in step 2 is genuinely harmful and must be moved — it justifies the split we are about to do.
Split each partial FD into its own table.
- Student(SID, StudentName)
- Course(CID, DeptOfCourse)
- Enroll(SID, CID, Grade) — only the full-key fact stays. Why this step? Each offending FD's determinant becomes the key of its own table, so its dependent attribute is stored exactly once, killing the anomaly named in step 3.

Screw-diagram position: killing the part-key FDs walks you across the second arrow ("kill part-key") from the 2NF box into the 3NF box of figure s01.

Verify: in the new Student, each name is stored once. If Asimov takes 3 courses, his name appeared 3× before and 1× now → redundancy dropped from 3 copies to 1. ✅ 3 tables.

Worked example

Ex 3 — C3: transitive dependency (3NF) Emp(EmpID, DeptID, DeptCity), key = EmpID. Rules: an employee is in one dept; a dept sits in one city.

Forecast: this table is already 2NF (single-column key!). So why isn't it done? Guess the offending chain.

Write the FDs. $E m p I D \to D e pt I D$ and $D e pt I D \to D e ptC i t y$ . Why this step? Chains of arrows are where transitivity hides.
Compose them and display the derived FD. Applying transitivity ( $X \to Y$ and $Y \to Z$ give $X \to Z$ ) to the two arrows above: $E m p I D \to D e pt I D \to D e ptC i t y ⟹ E m p I D \to D e ptC i t y .$ This new FD reaches DeptCity through a non-key middleman DeptID. Why this step? 3NF forbids a non-prime (DeptCity) depending on another non-prime (DeptID) — a non-superkey determinant with a non-prime target. Writing the composed FD explicitly makes the transitive path undeniable.
Check it's really 2NF-clean first. Key is single-column → no proper subset exists → cannot have a partial dependency → already 2NF. Good, the only remaining sin is transitive. Why this step? Normal forms are cumulative; 3NF assumes 2NF. Confirm you're on the right rung.
Name the anomaly. Every employee of a dept repeats DeptCity → move the dept = update many rows; the last employee leaving deletes the dept's city (delete anomaly). Why this step? Naming the concrete bug shows the transitive FD is harmful, justifying the decomposition in the next step.
Break the chain. Emp(EmpID, DeptID) + Dept(DeptID, DeptCity). Why this step? Putting DeptID → DeptCity in its own table stores each city once and turns DeptID in Emp into a plain foreign key — the transitive path no longer duplicates data.

Screw-diagram position: removing the non-key middleman walks you across the third arrow ("kill non-key middleman") from the 3NF box toward the BCNF box of figure s01.

Verify: Dept now holds each city once per dept. In Emp, DeptID is a foreign key with no non-prime hanging off it → the only FD is the key's own → 3NF and BCNF here. ✅ 2 tables.

Worked example

Ex 4 — C4: the "prime loophole" — 3NF but NOT BCNF Teach(Student, Subject, Teacher). Rules:

each (Student, Subject) → one Teacher: $(S t u d e n t, S u bj ec t) \to T e a c h er$
each Teacher teaches exactly one Subject: $T e a c h er \to S u bj ec t$ .

Forecast: this is the famous case. Guess: is Subject prime? Is the table 3NF? Is it BCNF?

Derive the second key-FD from the given ones. We are told $(S t u d e n t, S u bj ec t) \to T e a c h er$ and $T e a c h er \to S u bj ec t$ . Now test the set ${S t u d e n t, T e a c h er}$ : it already contains Student and Teacher, and $T e a c h er \to S u bj ec t$ adds Subject. So ${S t u d e n t, T e a c h er}$ determines Student, Teacher, Subject — all columns. Hence $(S t u d e n t, T e a c h er) \to S u bj ec t$ holds, derived purely from $T e a c h er \to S u bj ec t$ . Why this step? We must prove the second key exists rather than assert it — key-hunting is just closure: keep applying the given FDs until you reach every column.
Read off the candidate keys. {Student, Subject} (given, determines Teacher) and {Student, Teacher} (just derived, determines Subject) both determine everything and are minimal → two candidate keys. Why this step? Prime-ness depends on which columns are in some key, so we need the full list of keys first.
Mark prime attributes. Student, Subject, Teacher all appear in some key → all three are prime. Non-prime attributes: none. Why this step? 3NF's escape hatch depends on the target being prime.
Test 3NF on $T e a c h er \to S u bj ec t$ . Teacher alone is not a superkey (it doesn't fix Student). But the target Subject is prime → 3NF's "or $A$ is prime" clause is satisfied → passes 3NF. Why this step? This is the exact loophole the parent note warns about.
Test BCNF on the same FD, then name the anomaly. BCNF has no prime exception: it demands every determinant be a superkey. Teacher is not → fails BCNF. Concretely you can't record "Teacher X teaches Subject Y" until some student takes it (insert anomaly), because the key needs a Student. Why this step? Pairing the failed test with its real-world bug shows the violation is not a technicality — it blocks a legitimate insert, which justifies decomposing.
Decompose for BCNF. Split on the guilty FD: TeacherSubject(Teacher, Subject) + StudentTeacher(Student, Teacher). Why this step? Making Teacher the key of its own table turns $T e a c h er \to S u bj ec t$ into a key-FD (so it obeys BCNF) and lets you insert a teacher–subject fact with no student present.

Screw-diagram position: this is the last arrow of figure s01 — a table already inside the 3NF box being pushed the final step into the amber BCNF box.

Verify: in TeacherSubject, Teacher is the key and $T e a c h er \to S u bj ec t$ now is a key-FD → BCNF. You can insert a teacher with no students. ✅ 3NF ✔, BCNF ✗ before; both ✔ after. (See the dependency-preservation caveat in Ex 8.)

Worked example

Ex 5 — C5 (degenerate): single-column key — how far up the ladder for free? Product(SKU, Name, Price), key = SKU, FDs: $S K U \to N am e$ , $S K U \to P r i ce$ , and nothing else.

Forecast: the lowest normal form this table is guaranteed to satisfy — and whether it's already BCNF.

1NF check first (cumulative rule). Every cell — SKU, Name, Price — holds a single value; no lists, no nested tables → 1NF holds. We always clear this rung before climbing. Why this step? Normal forms are cumulative; skipping the atomicity check breaks the consistency of "verify every rung in order."
Key is single-column → no proper subset exists → no partial dependency possible → automatically 2NF. Why this step? This kills the common myth "2NF is about column count." 2NF needs a composite key to fail.
Check for a non-key determinant (3NF). Every determinant here is SKU, which IS the key (a superkey). No non-key determinant exists → no transitive chain → 3NF. Why this step? 3NF is violated only by a non-superkey determinant with a non-prime target; here there is no non-superkey determinant at all, so 3NF is automatic.
BCNF check: every non-trivial FD has SKU on the left, and SKU is a superkey → BCNF. Why this step? When the only determinant is the whole key, all four forms hold at once.

Screw-diagram position: this table is born in the amber BCNF box — it never has to cross any arrow of figure s01.

Verify: count offending FDs at each rung: non-atomic cells = 0, partial = 0, transitive = 0, non-superkey determinant = 0 → passes 1NF, 2NF, 3NF, BCNF. ✅ Fully normalized with zero work.

Worked example

Ex 6 — C6: overlapping candidate keys — which columns are prime? Booking(Room, Date, Guest, PassportNo). Rules:

a (Room, Date) has one Guest: $(R oo m, D a t e) \to G u es t$
a Guest has exactly one PassportNo and vice-versa: $G u es t \to P a ss p or tN o$ , $P a ss p or tN o \to G u es t$ .

Forecast: guess all candidate keys, then whether PassportNo is prime.

Swap interchangeable columns. Since Guest ↔ PassportNo (each determines the other), any key using Guest has a twin using PassportNo. Why this step? Two-way arrows make attributes substitutable inside keys.
Build candidate keys. {Room, Date} determines Guest → PassportNo → everything, so {Room, Date} is a key. Its twin would need to replace Room or Date with something interchangeable — but nothing determines Room or Date, so no swap is possible → {Room, Date} (and only it) is the candidate key. Guest/PassportNo are non-prime here. Why this step? Prime-ness is decided before checking any normal form, and the Guest ↔ PassportNo swap only creates new keys if the swapped-out column is itself replaceable — here it is not.
Test both directions of the Guest–PassportNo cycle for 3NF.
- $G u es t \to P a ss p or tN o$ : Guest isn't a superkey; target PassportNo is non-prime → violates 3NF (transitive: ${R oo m, D a t e} \to G u es t \to P a ss p or tN o$ ).
- $P a ss p or tN o \to G u es t$ : PassportNo isn't a superkey either; target Guest is non-prime → this also violates 3NF (transitive: ${R oo m, D a t e} \to P a ss p or tN o$ via the cycle, then $P a ss p or tN o \to G u es t$ ). Why this step? A two-way arrow is two FDs; both must be checked, and both are guilty here — the whole Guest ↔ PassportNo cycle is the offending block to extract.
Fix by extracting the whole cycle. RoomBooking(Room, Date, Guest) + GuestPassport(Guest, PassportNo). Why this step? Pulling the entire Guest ↔ PassportNo pair into one table stores that fact once; inside GuestPassport both Guest → PassportNo and PassportNo → Guest become key-FDs (each column is a candidate key), so both violations from step 3 disappear.

Screw-diagram position: the two-way cycle is a transitive dependency, so this crosses the third arrow ("kill non-key middleman") of figure s01 — and because both directions become key-FDs after the split, you land in the amber BCNF box.

Verify: in GuestPassport, Guest is a key and PassportNo is a key (each determines the other) → both FDs are key-FDs → BCNF. PassportNo's single storage means updating a passport touches 1 row, not every booking. ✅ 2 tables; only candidate key of the original = {Room, Date}.

Worked example

Ex 7 — C7 (limiting): the all-key relation Assign(Programmer, Project, Language), a pure junction: the only FD is the trivial one, {Programmer, Project, Language} → itself. No column is functionally determined by fewer columns.

Forecast: is this in BCNF? How many columns are prime?

1NF check first. Each of Programmer, Project, Language is a single atomic value → 1NF holds. Why this step? Cumulative rule — clear atomicity before climbing.
Find the candidate key. No proper subset determines the rest → the whole set {Programmer, Project, Language} is the only candidate key. Why this step? When nothing is dependent on less, the key is everything.
All columns are prime (they're all in the sole key). Non-prime attributes: none → no 2NF or 3NF violation is even possible (both need a non-prime target). Why this step? 2NF/3NF violations require a non-prime target — there are none, so both rungs pass automatically.
BCNF check. The only non-trivial FDs would need a determinant that isn't the whole key — but no such FD exists. Every (trivial) FD's determinant is a superkey → BCNF automatically. Why this step? This is the second limiting case: an all-key relation is always BCNF.

Screw-diagram position: like Ex 5, this table is born in the amber BCNF box — no arrow of figure s01 needs crossing.

Verify: offending FDs of every kind = 0 (there are no non-trivial FDs at all). Passes all forms. ✅ Nothing to decompose.

Worked example

Ex 8 — C8: word problem + exam twist (dependency-preservation trap) Scenario. A tutoring centre stores Class(Student, Course, Instructor). Business rules discovered by interview: "For any student in a course there's exactly one instructor; and each instructor teaches exactly one course." An exam asks: normalize to BCNF, but state whether you should actually stop at 3NF and why.

Forecast: you've seen this shape (Ex 4!). Predict the FDs, the keys, and the catch the exam is fishing for.

Translate English → FDs. "one instructor per (student,course)": $(S t u d e n t, C o u r se) \to I n s t r u c t or$ . "instructor teaches one course": $I n s t r u c t or \to C o u r se$ . Why this step? FDs are the business rules written as arrows — this is the whole HOW of reading a table.
Keys & prime attrs. By the same closure argument as Ex 4: ${S t u d e n t, C o u r se}$ determines Instructor (given), and ${S t u d e n t, I n s t r u c t or}$ determines Course (because $I n s t r u c t or \to C o u r se$ ). So both are candidate keys and all three columns are prime. Why this step? We re-run closure rather than assume the keys, because prime-ness (which decides the 3NF loophole in the next step) depends on having the complete key list.
Diagnose. $I n s t r u c t or \to C o u r se$ : Instructor not a superkey, but Course prime → 3NF passes, BCNF fails.
BCNF decomposition. InstrCourse(Instructor, Course) + StudentInstr(Student, Instructor).
The twist — dependency preservation. The original FD $(S t u d e n t, C o u r se) \to I n s t r u c t or$ now spans two tables: Student+Course are no longer together, so you cannot enforce it with a single-table key constraint. This is a lost dependency — see Decomposition — Lossless Join and Dependency Preservation. Why this step? BCNF is lossless here (join reconstructs the data) but not dependency-preserving.
Verdict. If enforcing $(S t u d e n t, C o u r se) \to I n s t r u c t or$ cheaply matters, stop at 3NF; the original table already is 3NF. If insert-anomaly-freedom matters more, go BCNF and enforce the lost FD in application code. (In production you might even denormalize to dodge joins — a different trade-off.)

Screw-diagram position: the exam twist is refusing to cross the last arrow of figure s01 — you stop in the 3NF box on purpose because crossing to BCNF would drop a dependency.

Verify: join test — StudentInstr ⋈ InstrCourse on Instructor reconstructs (Student, Course, Instructor) with no spurious rows (lossless, because Instructor is a key of InstrCourse). Dependency test — $(S t u d e n t, C o u r se) \to I n s t r u c t or$ is checkable in neither piece alone → not preserved. ✅ Both facts confirmed.

Recall Quick self-test on the matrix

Which cell is Enroll(StudentID, CourseID, Grade, StudentName)? ::: C2 — partial dependency (2NF). A single-column-key table can violate 2NF. True/False? ::: False — no proper subset of the key exists (C5). An all-key relation is always in which highest form? ::: BCNF (C7). Teacher → Subject passes 3NF but fails BCNF because…? ::: Subject is prime, so 3NF's "or target is prime" loophole saves it (C4). When might you deliberately stop at 3NF? ::: When BCNF decomposition would not preserve a dependency (C8).