Visual walkthrough — Normalization — 1NF, 2NF, 3NF, BCNF — anomalies each resolves

Before any symbol appears, three words we will lean on the whole way:

We also need the arrow. When we write $X\to Y$ (read "$X$ arrow $Y$") we mean: knowing $X$ pins down $Y$. If two rows show the same $X$, they are forced to show the same $Y$. That arrow is our only piece of notation; everything else is grids and colours. (Full treatment: Functional Dependencies.)

---

Step 1 — The table we start with

WHAT. A school stuffs everything about enrollments into ONE table:

$\text{Enroll}(\underline{StudentID},\underline{CourseID},StudentName,Grade,AdvisorID,AdvisorDept)$

Term by term, what each column is:

• $StudentID$ — the code for a student (S1, S2, …). Underlined = part of the key.
• $CourseID$ — the code for a course (C1, C2, …). Also underlined = part of the key.
• $StudentName$ — the student's human name.
• $Grade$ — the mark that student got in that course.
• $AdvisorID$ — the teacher who advises that student.
• $AdvisorDept$ — the department that advisor belongs to.

WHY start here. This table looks tidy but is secretly three facts glued together: "who a student is", "what grade they got", "which department advises them". We will pry them apart.

PICTURE. Look at the grid. The pink columns repeat the same value every time a student appears — that repetition is the enemy.

---

Step 2 — 1NF: make every cell atomic

WHAT. Suppose someone had stored the phones as one cell: $Phones=\text{"9991, 9992"}$. Atomic means one value per cell — no lists hiding inside.

WHY this tool, why now. Before we can even talk about which column determines which, each cell must hold exactly one thing. A list in a cell is invisible to the database: you cannot ask "who owns 9992?" because 9992 is buried in text. So the very first repair is: explode every list into its own rows.

PICTURE. On the left, one row with a two-item cell. On the right, two rows, each with a single phone. Same facts — now reachable.

---

Step 3 — Spot the partial dependency (the 2NF offender)

WHAT. Ask of each non-key column: does it really need the whole key, or just part of it?

• $Grade$ needs both $StudentID$ and $CourseID$ (a grade is per student per course). Good — full key.
• $StudentName$ needs only $StudentID$. The course is irrelevant to your name.

So we have found a partial dependency:

$\underset{\text{only PART of the key}}{\underbrace{StudentID}}\to \underset{\text{a non-key column}}{\underbrace{StudentName}}$

• $StudentID$ — a proper subset of the key (proper = strictly smaller, leaves out $CourseID$).
• The arrow $\to$ — "determines": same $StudentID$ forces same $StudentName$.
• $StudentName$ — a non-prime column (belongs to no key), so it is the kind of column the rule protects.

WHY this is a bug. Ana takes 4 courses → her name is written 4 times. Rename her and you must fix all 4 rows; miss one → the table disagrees with itself (update anomaly). Drop her last enrollment → her name vanishes entirely (delete anomaly).

PICTURE. The arrow reaches from only half the key. Watch the blue $StudentName$ cell repeat down the rows — that column is copied, wastefully.

---

Step 4 — 2NF: split the partial dependency out

WHAT. Carve $StudentName$ into its own table, keyed by the part it actually depends on:

$\text{Student}(\underline{StudentID},StudentName)+\text{Enroll}(\underline{StudentID},\underline{CourseID},Grade,AdvisorID,AdvisorDept)$

WHY it works. Now each student's name is stored once in $\text{Student}$. $\text{Enroll}$ keeps only what genuinely needs the whole pair-key ($Grade$) plus the advisor columns we still owe attention to. The join back is lossless because $StudentID$ is the shared link (see Decomposition — Lossless Join and Dependency Preservation).

PICTURE. The blue name column lifts out of the big grid into a small 2-column table. Where four "Ana" cells stood, one remains.

---

Step 5 — Spot the transitive dependency (the 3NF offender)

WHAT. Look inside the new $\text{Enroll}$ at the advisor columns. Two real rules:

$StudentID\to AdvisorIDAdvisorID\to AdvisorDept$

Chain them:

$\underset{\text{the key}}{\underbrace{StudentID}}\to \underset{\text{a NON-key middleman}}{\underbrace{AdvisorID}}\to \underset{\text{non-key, riding along}}{\underbrace{AdvisorDept}}$

This chain means $StudentID\to AdvisorDept$ transitively — the key reaches $AdvisorDept$ only through the non-key middleman $AdvisorID$. (For simplicity here read $StudentID$ as the determinant; the point is the middleman is not a key.)

• The middle arrow's left side, $AdvisorID$, is not a superkey — it does not identify a row of Enroll.
• $AdvisorDept$ is non-prime — belongs to no candidate key.
• Both conditions true at once ⇒ 3NF is violated.

WHY this is a bug. Every student advised by teacher T repeats T's department. Move the advisor's department once in the real world → hunt down every student row (update anomaly). The last student of an advisor leaves → the advisor's department is lost (delete anomaly).

PICTURE. A two-hop arrow: key → middleman → tag-along. The yellow $AdvisorDept$ cell repeats for every student of the same advisor.

---

Step 6 — 3NF: cut the chain at the middleman

WHAT. Give the middleman its own table:

$\text{Advisor}(\underline{AdvisorID},AdvisorDept)+\text{Enroll}(\underline{StudentID},\underline{CourseID},Grade,AdvisorID)$

WHY it works. Now $AdvisorDept$ lives once per advisor. $\text{Enroll}$ keeps only $AdvisorID$ as a pointer. The chain is broken: nothing non-key determines another non-key anymore.

PICTURE. The yellow department column detaches into a tiny $\text{Advisor}$ table; Enroll keeps just the arrow-pointer $AdvisorID$.

---

Step 7 — The BCNF loophole: a table that passes 3NF but still leaks

WHAT. A different corner of the school: who teaches whom.

$\text{Teach}(Student,Subject,Teacher)$

with two real rules:

• each $(Student,Subject)\to Teacher$ — a student studies a subject with one teacher.
• each $Teacher\to Subject$ — a teacher teaches exactly one subject.

Work out the keys: from $Teacher\to Subject$, knowing $(Student,Teacher)$ gives $Subject$, so $\{Student,Teacher\}$ is a key. And $\{Student,Subject\}$ is a key too. Therefore both $Subject$ and $Teacher$ are prime (each sits in some key).

Now test the FD $Teacher\to Subject$:

• $Teacher$ is not a superkey (a teacher alone does not identify a row).
• but $Subject$ is prime → 3NF's escape hatch says "fine!". 3NF passes.
• BCNF has no escape hatch → BCNF fails.

WHY this still bugs. You cannot record "Teacher T teaches Subject Y" until some student enrolls — the row needs a $Student$ (insert anomaly). The fact "T teaches Y" is homeless.

PICTURE. The FD $Teacher\to Subject$ arrow starts at a non-superkey; the pink cell shows $Subject$ duplicated for every student of that teacher, yet 3NF waves it through because $Subject$ is prime.

---

Step 8 — BCNF: every determinant must be a superkey

WHAT. Split on the offending determinant $Teacher$:

$\text{TeacherSubject}(\underline{Teacher},Subject)+\text{StudentTeacher}(\underline{Student},\underline{Teacher})$

WHY it works. Now "T teaches Y" lives in $\text{TeacherSubject}$ with $Teacher$ as its key — a determinant that is a superkey. You can register a teacher's subject with no student present. Every remaining FD has a superkey on its left.

PICTURE. $Teacher\to Subject$ becomes its own clean 2-column table; the leftover links students to teachers.

---

The one-picture summary

Every squeeze is the same move: find an arrow whose left side is too small or non-key, and lift the arrow into its own table. 1NF fixes cells; 2NF fixes "part of key"; 3NF fixes "non-key middleman"; BCNF fixes "any non-superkey determinant".

Recall Feynman retelling of the whole walkthrough

We started with one giant notebook page where a student's name, grade, and advisor's department were all crammed on each enrollment line. 1NF: stop stuffing lists in a box — one thing per box, so you can actually search it. 2NF: the student's name only cares about the student, not the course, yet we rewrote it for every course — so we moved names to their own little "Student" book, written once. 3NF: the advisor's department only cares about the advisor, and the advisor is not the key — a middleman — so we gave advisors their own "Advisor" book. BCNF: in a separate teaching table, a rule "each teacher teaches one subject" duplicated the subject over and over, and 3NF let it slide because "subject" happened to be part of a key — but you still couldn't record a teacher's subject before a student showed up. BCNF says no left-side may be a non-key, so we gave teachers-and-their-subjects their own book too. Each step: spot an arrow starting from something too small, and give that arrow its own page — so every fact is written exactly once.

Recall Quick checks

Why is $StudentID\to StudentName$ a partial dependency? ::: $StudentID$ is a proper subset of the composite key $\{StudentID,CourseID\}$ and $StudentName$ is non-prime. Why does $StudentID\to AdvisorID\to AdvisorDept$ violate 3NF? ::: $AdvisorID$ is a non-key middleman and $AdvisorDept$ is non-prime — a transitive dependency. Why does $Teacher\to Subject$ pass 3NF but fail BCNF? ::: $Teacher$ is not a superkey (fails BCNF) but $Subject$ is prime, so 3NF's "or $A$ is prime" hatch excuses it. What is the single repeated move across all forms? ::: Find an arrow whose left side is too small / non-key, and lift it into its own table.