4.1.1 · Why normalize: redundancy and insertion/deletion/update anomalies

0 · Recall Warm-up

Answer from memory first. Answers are in the Answer Vault at the bottom.

What does a view store? from Lesson 3.3.10
Which design step comes after converting the ER diagram to tables? from Lesson 0.1.4
What is a candidate key? from Lesson 2.2.1

1 · The Hook

A clerk proposes one big table "to make queries easy": every enrolment row also carries the student's name, department, head of department and the course title. Every query becomes a single SELECT. Then the head of CS changes, a new course is announced, and Diya drops her only course. Each of these simple events breaks the table in a different way. Those breakages are anomalies, and removing them is the job of normalisation.

roll	name	dept	hod	cid	title	grade
1	Asha	CS	Rao	C1	DBMS	A
1	Asha	CS	Rao	C2	Networks	B
1	Asha	CS	Rao	C3	Circuits	A
2	Bhavin	EE	Iyer	C3	Circuits	B
3	Chirag	CS	Rao	C1	DBMS	B
3	Chirag	CS	Rao	C2	Networks	A
4	Diya	ME	Shah	C4	Thermo	C

ENROLL_INFO, primary key (roll, cid)

2 · Predict First

Prof. Rao stops being head of CS; Prof. Mehta takes over. How many rows of ENROLL_INFO must change? What happens if the clerk changes only some of them?

Make a guess in your head. The answer opens Section 3.

3 · Core Concept

Predict First — the answer

5 rows (every CS student's enrolment). If only some change, the table says CS has two different heads: an inconsistency. This is an update anomaly, caused by storing "CS's head is Rao" five times.

Redundancy

One fact stored in many rows: "CS's head is Rao" (5 times), "Asha is in CS" (3 times), "C1 is DBMS" (2 times). Redundancy wastes space and, worse, causes three kinds of anomaly.

The three anomalies

Anomaly	What goes wrong	ENROLL_INFO example
Update	A fact stored many times must be changed in every copy; missing one creates inconsistency.	Changing CS's head touches 5 rows.
Insertion	A fact cannot be stored until some unrelated fact exists, because the key would be NULL.	A new course C5 with no students cannot be added: roll (part of the key) would be NULL. A new department CE with its head cannot be stored at all.
Deletion	Deleting one fact accidentally deletes others stored only in the same row.	Deleting Diya's enrolment in C4 also loses Diya herself, ME's head Shah, and the course Thermo.

The cause: mixing facts about different things

ENROLL_INFO mixes facts about students (name, dept), departments (hod), courses (title) and enrolments (grade) in one table. Each kind of fact is determined by a different set of attributes:

roll determines name and dept;
dept determines hod;
cid determines title;
(roll, cid) determines grade.

These "determines" rules are functional dependencies (next lesson). Normalisation splits a table so that each table holds facts determined by its own key only: STUDENT(roll, name, dept), DEPT(dept, hod), COURSE(cid, title), ENROLL(roll, cid, grade). Each fact is then stored once, and all three anomalies disappear.

Splitting has a risk

Splitting must be done so that joining the pieces back gives exactly the original table: no lost rows and no extra (spurious) rows. Splitting into (roll, name, dept, hod) and (cid, title, grade) would be wrong: the pieces share no attribute, so the join is a Cartesian product full of false rows. Chapter 4.3 gives the tests.

4 · Visual

ENROLL_INFO (one big table)                       after normalisation
roll name dept hod  | cid title    grade          STUDENT(roll, name, dept)
 1   Asha  CS  Rao  | C1  DBMS     A              DEPT(dept, hod)          <- Rao stored once
 1   Asha  CS  Rao  | C2  Networks B              COURSE(cid, title)       <- C5 can exist alone
 ...                                              ENROLL(roll, cid, grade)
 4   Diya  ME  Shah | C4  Thermo   C   <- delete this row: lose Diya, Shah, Thermo

5 · Worked Examples

Example 1 Easy

In ENROLL_INFO, how many times is the fact "C3 is Circuits" stored?

Solution

Twice (Asha's and Bhavin's enrolments in C3).

Example 2 Medium

Name the anomaly: a new student Farhan (roll 6, ME) is admitted but has not chosen courses yet.

Solution

Insertion anomaly: his row would need a cid, which is part of the primary key and cannot be NULL. His name and department cannot be recorded.

Example 3 GATE

In ENROLL_INFO, deleting all enrolments of course C3 removes which other facts from the database?

Solution

Rows (1, C3) and (2, C3) go. Asha remains (other rows), but Bhavin, his department EE, EE's head Iyer, and the course Circuits all disappear: a deletion anomaly.

6 · GATE Traps

Insertion anomaly is about not being able to insert a fact (key would be NULL), not about inserting wrong data.
Deletion anomaly loses other facts, not the deleted one.
Update anomaly is the risk of inconsistency when copies are updated partially.
Splitting tables must be lossless (no spurious tuples); an arbitrary split is worse than no split.

7 · Memory Hook

"UID: Update many, Insert can't, Delete too much."

8 · Connect the Dots

9 · Self-Test

GATE format. Try every question before opening the vault.

Q1 · MCQ · 1 mark(s) — Anomalies

A course cannot be recorded until at least one student enrols in it. This is:

(A) an update anomaly
(B) a deletion anomaly
(C) an insertion anomaly
(D) referential integrity

Q2 · NAT · 1 mark(s) — Redundancy

In ENROLL_INFO, how many rows store the fact "Asha is in CS"?

Numerical answer: ________

Q3 · MCQ · 1 mark(s) — Anomalies

Changing a student's department requires updating every one of her enrolment rows. If one row is missed, which anomaly has occurred?

(A) Insertion anomaly
(B) Spurious tuple
(C) Deletion anomaly
(D) Update anomaly

Q4 · MSQ · 2 mark(s) — Deletion

Deleting the single row (4, Diya, ME, Shah, C4, Thermo, C) from ENROLL_INFO loses which facts?

(A) C4 is Thermo
(B) Diya is a student in ME
(C) ME's head is Shah
(D) CS's head is Rao

Q5 · MCQ · 2 mark(s) — Cause

The root cause of all three anomalies in ENROLL_INFO is:

(A) NULL values
(B) storing facts about several different things, each determined by a different set of attributes, in one table
(C) too many columns of type VARCHAR
(D) a missing primary key

10 · Rule Card

Redundancy = one fact stored in many rows.
Update anomaly: copies must all change. Insertion anomaly: a fact needs an unrelated fact (key part would be NULL). Deletion anomaly: deleting one fact loses others.
Cause: one table mixes facts determined by different attribute sets.
Fix: split so each table's facts depend on its own key, without creating spurious tuples.

11 · Concept Ledger

Unlocked in this lesson. From here on, these terms may be used.

redundancy (design)update anomalyinsertion anomalydeletion anomalynormalisationspurious tuple (idea)

12 · Answer Vault

Reveal answers when you’re ready

Recall Warm-up

Its defining query (a normal view stores no rows).
Refinement by normalisation.
A minimal superkey.