Normalisation up to BCNF, and the dependency you have to give up

Normal forms are usually taught as a list of rules to recite. They are easier to apply as a single question asked repeatedly: what determines what, and is the left-hand side a key?

Tools first: closure

The closure X⁺ is every attribute derivable from X. Everything else — candidate keys, normal-form checks — is a closure computation.

Given R(A, B, C, D, E) and F = { A → BC,  CD → E,  B → D }

Compute A⁺:
  start   {A}
  A → BC  {A, B, C}
  B → D   {A, B, C, D}
  CD → E  {A, B, C, D, E}      all attributes

So A⁺ = ABCDE, and A is a superkey. No proper subset of A exists,
so A is a candidate key.

An attribute in some candidate key is prime; here only A is prime.

The forms

1NF — every value is atomic. No lists in a cell, no repeating groups. A phone_numbers column holding "9876543210, 9123456780" fails.

2NF — 1NF, and no non-prime attribute depends on part of a candidate key. Only composite keys can violate this.

R(StudentID, Course, StudentName)      key = {StudentID, Course}
F = { (StudentID, Course) → grade,  StudentID → StudentName }

StudentID → StudentName is a partial dependency: StudentName depends on
half the key. Not in 2NF.

Fix: split into (StudentID, StudentName) and (StudentID, Course, grade).

The practical harm is the duplication — the student’s name is repeated once per enrolled course, and updating it in one row and not the others corrupts the relation.

3NF — 2NF, and for every non-trivial X → Y, either X is a superkey or every attribute of Y is prime.

BCNF — for every non-trivial X → Y, X is a superkey. Full stop. The “or prime” escape hatch is gone, which is the only difference.

The example that shows the difference

R(A, B, C)      F = { AB → C,  C → B }

Candidate keys, by closure:

(AB)⁺ = A B C      superkey; neither A⁺ = A nor B⁺ = B is, so AB is a key
(AC)⁺ = A C B      superkey; A⁺ and C⁺ = CB are not, so AC is a key

Keys are AB and AC, so A, B, C are all prime.

Check 3NF, dependency by dependency:

AB → C   left side is a candidate key                     ✓
C  → B   C is not a superkey, but B is prime              ✓ (3NF's escape hatch)

R is in 3NF. Check BCNF:

C → B    C is not a superkey                              ✗

Not in BCNF. Decompose on the violating dependency C → B:

R1(C, B)        the dependency itself
R2(C, A)        the rest, keeping C to join on

Lossless? For a binary decomposition the test is whether the shared attributes determine one side:

R1 ∩ R2 = {C},  and C → B, so C → R1        ✓ lossless

Dependency preserving? Project F onto each piece:

On R1(C,B):  C → B
On R2(C,A):  C⁺ ∩ {A,C} = {C},  A⁺ ∩ {A,C} = {A}   — nothing non-trivial

Recovered set: { C → B }.  Does it imply AB → C?  No.

AB → C is lost. Enforcing it now requires a join on every insert, which is exactly what a constraint is supposed to avoid.

The trade-off, stated plainly

	Lossless join	Dependency preserving	Redundancy
3NF	always achievable	always achievable	some may remain
BCNF	always achievable	not always	removed

That is the whole result, and it is the standard exam question: is every 3NF relation in BCNF? No — the relation above is the counterexample. Is every BCNF relation in 3NF? Yes, since BCNF’s condition is strictly stronger.

3NF synthesis (from a minimal cover) always gives both properties, which is why production schemas usually stop at 3NF and accept the residual redundancy.

Exam checklist

1. Compute all candidate keys via closures. Mark prime attributes.
2. For each FD, ask: is the left side a superkey?
3. If yes for all → BCNF. If some fail but their right sides are prime → 3NF.
4. Composite key present? Check for partial dependencies (2NF) first.
5. Decomposing: check lossless with R1 ∩ R2 → R1 or R2, then check whether the projected dependencies still cover F.

The traps I keep seeing: forgetting that a relation can have several candidate keys, and forgetting that A → A and other trivial dependencies never violate anything.

What to take forward

• Everything reduces to closure. Compute keys before judging any form.
• 3NF forgives a non-key determinant when the dependent attribute is prime; BCNF does not.
• BCNF can cost dependency preservation. 3NF never does.
• Lossless is testable in one line for a binary split.

---

— Ishaan SandhwarDBMSGATENormalisation