Normalisation: 1NF to BCNF

Here is the table the animation starts from. Each row is an enrolment, but it also carries the student's name and the course's name:

"Databases" appears three times and "Asha" twice. Every repetition is a chance for two copies to disagree, and that is the whole disease. The three symptoms are worth naming because exam questions ask for them by name:

Update anomaly

Renaming course C1 means changing three rows. Miss one and the database now claims C1 has two different names.

Insertion anomaly

You cannot record a new course until some student enrols in it, because sid is part of the key and cannot be null.

Deletion anomaly

Delete the last enrolment for a course and you lose the course's name too — a fact you never meant to throw away.

Functional dependency (X → Y)

Knowing X pins down Y. If two rows agree on X they must agree on Y. This is a statement about the world being modelled, not about the rows that happen to be in the table today.

Superkey and candidate key

A superkey determines every attribute in the relation. A candidate key is a superkey with nothing spare in it — remove any attribute and it stops working.

Prime attribute

One that appears in some candidate key. The normal forms are mostly rules about how non-prime attributes are allowed to depend on prime ones.

Partial dependency

A non-prime attribute determined by only part of a composite key. Forbidden by 2NF, and the direct cause of the update, insert and delete anomalies.

Transitive dependency

Key → X → Y where X is not a key. Forbidden by 3NF: the fact really belongs in a table keyed by X.

Lossless decomposition

Splitting a relation so that re-joining it gives back exactly the original rows — no more, no fewer. Guaranteed when the shared attribute is a key of one of the two pieces.

Each form assumes the one before it. The single question that drives all of them: for every arrow X → Y in this relation, is X a key of it? If yes, you are in BCNF and finished.

1. 1Find the key. Neither sid nor cid alone identifies a row, so the key is the pair (sid, cid).
2. 2List the dependencies: sid → sname, cid → cname, (sid, cid) → grade.
3. 3Check 2NF. sid → sname and cid → cname each depend on part of the key. Both are violations.
4. 4Split on each determinant: Student(sid, sname) and Course(cid, cname).
5. 5What is left is Enrol(sid, cid, grade), whose only dependency starts at its own key.
6. 6Re-check: every determinant is now a superkey of its relation, so this is BCNF.

Notice that no dependency was destroyed. Each one simply moved into a relation where its determinant is the key — which is what makes it harmless rather than dangerous.

3NF allows one exception BCNF does not: a dependency X → Y is acceptable to 3NF if Y is a prime attribute, even when X is not a superkey. BCNF removes that loophole. The example here lands in both, so it does not distinguish them — but the trade-off is real: every relation has a lossless 3NF decomposition that preserves all dependencies, whereas BCNF is sometimes only reachable by giving dependency preservation up.

In practice designers normalise to BCNF and then sometimes denormalise deliberately for read performance, accepting the redundancy with their eyes open. That is a different thing from never having normalised at all.