Redundancy and the three anomalies

Part of the free LogicWiz SQL & Databases course, Module 10: Normalization. The queries and practice run in the app on real Postgres.

Aarav kept all the marks in one big table, like a spreadsheet. Then CSE got a new HOD, he updated "the row", and now the app shows two HODs for CSE. What went wrong?

You'll learn to

• See why storing the same fact many times is dangerous
• Name and show the update, insert and delete anomalies
• Find inconsistent copies with GROUP BY and COUNT(DISTINCT)

Learn

Aarav's one-big-table design: marks_sheet

The key is (student_id, course_id): one row per student per course. Every lesson in this module uses this table.

Redundancy: one fact, many copies

"CSE's HOD is Dr. Rao" is one fact, but it is written in 3 rows. "CS101 is Programming in C, 4 credits" is written 3 times. "Aarav is in CSE" twice.

This is redundancy: the same fact stored more than once. It wastes space, but that is the small problem. The big problem is that copies can disagree, and then the database is lying to you.

Normalization is the process of splitting a table so that each fact is stored exactly once.

Count the copies

SELECT dept, hod, COUNT(*) AS copies
FROM marks_sheet
GROUP BY dept, hod
ORDER BY copies DESC, dept;

Each row of this result is one fact, and copies is how many times the big table repeats it.

The three anomalies

An anomaly is something that goes wrong when you change a redundant table.

• Update anomaly: a fact lives in many rows, so changing it means changing all of them. Miss one and the copies disagree.
• Insert anomaly: you can't store one fact without another. A new department "CIVIL" with HOD Dr. Das can't be added until some student in CIVIL takes a course, because student_id and course_id are the key and can't be NULL.
• Delete anomaly: deleting one fact deletes another. If Kabir drops ME101, we also lose that MECH's HOD is Dr. Iyer and that ME101 exists.

Update anomaly: fixing one row, not all

UPDATE marks_sheet
SET hod = 'Dr. Mehta'
WHERE student_id = 101;

SELECT DISTINCT dept, hod
FROM marks_sheet
WHERE dept = 'CSE'
ORDER BY hod;

Aarav changed "his" rows. Meher's row still says Dr. Rao, so CSE now has two HODs. Which one is true? The database can't tell you.

Insert anomaly: a department with no students

INSERT INTO marks_sheet (dept, hod, student_name, course_title, credits)
VALUES ('CIVIL', 'Dr. Das', '?', '?', 0);

Postgres refuses: student_id is part of the primary key, so it can't be NULL. The only way in is to invent a fake student, which is worse.

Delete anomaly: losing a fact by accident

DELETE FROM marks_sheet
WHERE student_id = 104 AND course_id = 'ME101';

SELECT COUNT(*) AS mech_rows
FROM marks_sheet
WHERE dept = 'MECH';

Kabir dropped one course, and now the database no longer knows that MECH exists, who its HOD is, or that ME101 is Engineering Drawing.

Fixing the HOD with WHERE dept = 'CSE' works today, but the design still allows the mistake tomorrow, in some other query written by someone else. The real cure is structural: store "CSE → Dr. Rao" in one row of a departments table, so there is only one copy to change.

"Explain insert, update and delete anomalies with an example" is a very common exam question. Use one table like marks_sheet and give one concrete line for each: update (two HODs for CSE), insert (a department with no students can't be added), delete (dropping the last student loses the department).

In one line

Redundancy stores one fact many times, which causes update, insert and delete anomalies; normalization stores each fact once.

Interview corner

What is normalization and why do we do it?

Normalization is splitting tables so that each fact is stored exactly once, guided by functional dependencies and the normal forms (1NF, 2NF, 3NF, BCNF). It removes redundancy and with it the update, insert and delete anomalies, so the data cannot contradict itself.

Explain update, insert and delete anomalies with an example.

In a table of (student, course, dept, hod): changing a HOD needs every row of that department updated, or copies disagree (update). A new department with no students can't be added because the key needs a student (insert). Deleting a department's last student also deletes who its HOD is (delete).

Is redundancy always bad?

Uncontrolled redundancy is, because copies drift apart. Controlled redundancy (denormalisation, caches, materialised views, read-optimised reporting tables) is a deliberate trade for faster reads, as long as you have a plan to keep the copies in sync.