Normal Forms: 1NF to BCNF

We left the last lesson holding a clean split: every attribute is now prime (it sits in some candidate key) or non-prime (it sits in none). The promise was that this split does real work. Here is the work — it is exactly the tool that tells a clean table design from a messy one.

So picture two tables. In the first, an “Orders” row lists all the items a customer bought, jammed into a single cell. In the second, a “Students” row repeats the department’s head of department on every line, once per student. Both store the data you asked for. Both also misbehave the moment you try to change anything — edit the head of department and you must hunt down a hundred rows; delete the last student in a department and the department’s details vanish with them.

Those misbehaviours have a name, update anomalies, and they are not bad luck. They are baked into the shape of the table. What we want is a way to read the shape and say, plainly, here is the bar this design has cleared, and here is the one it has tripped on. That is what a normal form is — a named bar. GATE almost always asks the same thing: which is the highest bar a given relation clears? Out in real data work it is the same judgement, only the cost is yours to weigh — how far to normalize before the extra joins hurt more than the anomalies did.

The four bars, plainly

Four bars, each stricter than the last.

• 1NF — atomic values. Every cell holds a single, indivisible value. No lists, no sets, no JSON blob standing in for three facts. The GATE syllabus assumes this by default.
• 2NF — no partial dependency. No non-prime attribute depends on only part of a candidate key. If every candidate key is a single attribute, there is no “part” to depend on, so 2NF holds for free.
• 3NF — no transitive dependency. For every non-trivial FD X → A, at least one of two things must hold: X is a superkey, or A is a prime attribute.
• BCNF — superkey on the left, always. For every non-trivial FD X → A, X must be a superkey. No exception, no escape clause.

Notice where the prime/non-prime split from last lesson lands: it is the whole hinge of 3NF. An attribute is prime if it belongs to some candidate key and non-prime otherwise; 3NF waves through any FD whose right-hand side is prime, and BCNF refuses to. That one difference is the entire gap between the two strongest bars.

How they stack

The bars nest. Clear the innermost and you have automatically cleared every one outside it, which is why we hunt from the inside out — test the strictest first, and step outward only when it fails. One more thing the rules quietly assume: a trivial FD, where the RHS is already a subset of the LHS (such as AB → A), is always allowed. The bars only ever police non-trivial FDs.

How GATE asks this

The usual framing is spare: a small relation R(...), a set of FDs, and the prompt “the highest normal form R satisfies is — ?”. MSQ variants ask which named NF a relation is in, or to sort each FD into its role — the offender versus the well-behaved. The recipe runs strictest-first:

1. Find every candidate key of R. Label each attribute prime or non-prime.
2. Check BCNF. Every non-trivial FD’s LHS must be a superkey. One bad FD and BCNF fails.
3. If BCNF failed, check 3NF. For each FD that broke BCNF, ask the escape question: is its RHS prime? If yes, 3NF survives that FD. If even one FD has both a non-superkey LHS and a non-prime RHS, 3NF fails too.
4. If 3NF failed, check 2NF. Is there a non-prime attribute depending on a strict subset of some candidate key? If yes, 2NF fails, and the highest the table clears is 1NF.

Worked example

R(A, B, C, D) with F = {A → B, A → C, BC → D}. What is the highest normal form R satisfies?

Find the candidate keys. Compute A⁺: start {A}; A → B adds B; A → C adds C; now BC → D fires, both in, and adds D → A⁺ = ABCD. So {A} is a superkey, and being a single attribute it is minimal. Any other key? Check B⁺ = {B}, C⁺ = {C}, D⁺ = {D} — none reaches the full set. So the unique candidate key is {A}. Prime = {A}; non-prime = {B, C, D}.

Check BCNF. For each FD, is the LHS a superkey?

• A → B: LHS A is the key — superkey ✓.
• A → C: same — ✓.
• BC → D: LHS BC is not a superkey (its closure is BCD, missing A) — ✗.

BCNF fails, on account of BC → D.

Check 3NF. The failing FD is BC → D. For 3NF we need either BC a superkey (no) or D prime (no — D is non-prime). Both escape routes are shut, so 3NF fails too.

Check 2NF. The only candidate key is {A}, a single attribute. A partial dependency needs a non-prime attribute leaning on part of a key, and a one-attribute key has no proper part. So 2NF holds trivially, and 1NF is assumed.

Highest NF satisfied: 2NF. The trouble-maker is BC → D — BC is not a key, and D belongs to no key.

In one breath

Normal forms are four nested bars on how a table is shaped: 1NF wants atomic cells, 2NF forbids a non-prime attribute leaning on part of a key, 3NF forbids a non-superkey-to-non-prime dependency, and BCNF demands a superkey on the left of every non-trivial FD. Test strictest-first — find the keys, label prime and non-prime, then check BCNF, fall to 3NF on its prime-RHS escape clause, and fall to 2NF only if a partial dependency bites.

Practice

A question to carry forward

So now you can name the highest bar a table clears — and, just as often, watch it trip on a single bad FD like BC → D. Naming the failure is satisfying. But a database designer cannot stop at the diagnosis; the table still has to be fixed.

The obvious fix is to break the offending table into smaller ones, each clean. Yet splitting is dangerous: cut a table the wrong way and you can no longer rebuild the original by joining the pieces, or you quietly lose the ability to enforce one of the FDs. Here is the thread onward: when you decompose a relation to reach a higher normal form, what must the split preserve so that nothing is lost — and can you always have both at once?