Anomalies and why we normalise
40 min · Intermediate
Need help? Ask a tutor on WhatsApp (opens in a new tab)When the same fact is stored in many rows, the copies sooner or later disagree. Those failures are called anomalies, and normalisation is the step-by-step way of designing tables so that each fact is stored exactly once.
One wide spreadsheet-style table
Redundancy is the same fact stored in more than one row, such as an event's fee repeated on every registration for it. Copies waste space and can drift apart, which is where anomalies come from.
A wide table (or flat table) keeps everything in one table, like a spreadsheet: every row holds a registration with the student's, the event's and the coordinator's details. The fest sheet below shows what that costs.
Example 1 · A fest registration sheet as one table
CREATE TABLE fest_sheet ( reg_no INT PRIMARY KEY, roll_no VARCHAR(10) NOT NULL, student VARCHAR(40) NOT NULL, college VARCHAR(10) NOT NULL, event_code VARCHAR(5) NOT NULL, event_name VARCHAR(30) NOT NULL, fee INT NOT NULL, coordinator VARCHAR(30) NOT NULL, coord_phone VARCHAR(10) NOT NULL);
INSERT INTO fest_sheet VALUES (1, '22CS041', 'Nikhil Rao', 'PESU', 'HACK', 'Hackathon', 300, 'Ms Kavitha', '9845000001'), (2, '22CS041', 'Nikhil Rao', 'PESU', 'QUIZ', 'Tech Quiz', 100, 'Mr Arun', '9845000002'), (3, '22EC007', 'Sana Sheikh', 'RVCE', 'HACK', 'Hackathon', 300, 'Ms Kavitha', '9845000001'), (4, '23ME112', 'Varun Pillai', 'BMSCE', 'DANCE', 'Group Dance', 500, 'Ms Reema', '9845000003'), (5, '22EC007', 'Sana Sheikh', 'RVCE', 'QUIZ', 'Tech Quiz', 100, 'Mr Arun', '9845000002'), (6, '24IT019', 'Ritu Jain', 'PESU', 'HACK', 'Hackathon', 300, 'Ms Kavitha', '9845000001');
SELECT reg_no, student, event_name, fee, coordinator, coord_phoneFROM fest_sheetORDER BY reg_no;
-- How many times is each event's fee stored?SELECT event_code, COUNT(*) AS copies_of_feeFROM fest_sheetGROUP BY event_codeORDER BY event_code;+--------+--------------+-------------+-----+-------------+-------------+ | reg_no | student | event_name | fee | coordinator | coord_phone | +--------+--------------+-------------+-----+-------------+-------------+ | 1 | Nikhil Rao | Hackathon | 300 | Ms Kavitha | 9845000001 | | 2 | Nikhil Rao | Tech Quiz | 100 | Mr Arun | 9845000002 | | 3 | Sana Sheikh | Hackathon | 300 | Ms Kavitha | 9845000001 | | 4 | Varun Pillai | Group Dance | 500 | Ms Reema | 9845000003 | | 5 | Sana Sheikh | Tech Quiz | 100 | Mr Arun | 9845000002 | | 6 | Ritu Jain | Hackathon | 300 | Ms Kavitha | 9845000001 | +--------+--------------+-------------+-----+-------------+-------------+ +------------+---------------+ | event_code | copies_of_fee | +------------+---------------+ | DANCE | 1 | | HACK | 3 | | QUIZ | 2 | +------------+---------------+
What this code does
- Each row is one registration, but it also repeats facts about the student, the event and the coordinator.
- The Hackathon fee and Ms Kavitha's phone are stored three times each. With 5,000 registrations, popular events would repeat their details thousands of times.
Update, insert and delete anomalies
An anomaly is a problem caused by redundancy when you change data: an update, insert or delete leaves the table contradicting itself or loses a fact. There are three kinds, one for each way of changing a table.
Redundancy does harm when the data changes. The two scripts below change the fest sheet to show an update anomaly first, then a delete and an insert anomaly.
Example 2 · Update anomaly: the phone number that changed in one row
CREATE TABLE fest_sheet ( reg_no INT PRIMARY KEY, roll_no VARCHAR(10) NOT NULL, student VARCHAR(40) NOT NULL, college VARCHAR(10) NOT NULL, event_code VARCHAR(5) NOT NULL, event_name VARCHAR(30) NOT NULL, fee INT NOT NULL, coordinator VARCHAR(30) NOT NULL, coord_phone VARCHAR(10) NOT NULL);
INSERT INTO fest_sheet VALUES (1, '22CS041', 'Nikhil Rao', 'PESU', 'HACK', 'Hackathon', 300, 'Ms Kavitha', '9845000001'), (2, '22CS041', 'Nikhil Rao', 'PESU', 'QUIZ', 'Tech Quiz', 100, 'Mr Arun', '9845000002'), (3, '22EC007', 'Sana Sheikh', 'RVCE', 'HACK', 'Hackathon', 300, 'Ms Kavitha', '9845000001'), (4, '23ME112', 'Varun Pillai', 'BMSCE', 'DANCE', 'Group Dance', 500, 'Ms Reema', '9845000003'), (5, '22EC007', 'Sana Sheikh', 'RVCE', 'QUIZ', 'Tech Quiz', 100, 'Mr Arun', '9845000002'), (6, '24IT019', 'Ritu Jain', 'PESU', 'HACK', 'Hackathon', 300, 'Ms Kavitha', '9845000001');
-- Ms Kavitha has a new number; the volunteer updates the row in front of themUPDATE fest_sheet SET coord_phone = '9900099000' WHERE reg_no = 1;
SELECT DISTINCT coordinator, coord_phoneFROM fest_sheetORDER BY coordinator, coord_phone;+-------------+-------------+ | coordinator | coord_phone | +-------------+-------------+ | Mr Arun | 9845000002 | | Ms Kavitha | 9845000001 | | Ms Kavitha | 9900099000 | | Ms Reema | 9845000003 | +-------------+-------------+
What this code does
- The update changed one of the three copies. The table now says Ms Kavitha has two phone numbers, and nobody can tell which is right.
- The correct update needs
WHERE coordinator = 'Ms Kavitha', but nothing in the design forces anyone to write it. Redundancy turns every update into a search for all the copies.
Example 3 · Delete and insert anomalies
CREATE TABLE fest_sheet ( reg_no INT PRIMARY KEY, roll_no VARCHAR(10) NOT NULL, student VARCHAR(40) NOT NULL, college VARCHAR(10) NOT NULL, event_code VARCHAR(5) NOT NULL, event_name VARCHAR(30) NOT NULL, fee INT NOT NULL, coordinator VARCHAR(30) NOT NULL, coord_phone VARCHAR(10) NOT NULL);
INSERT INTO fest_sheet VALUES (1, '22CS041', 'Nikhil Rao', 'PESU', 'HACK', 'Hackathon', 300, 'Ms Kavitha', '9845000001'), (2, '22CS041', 'Nikhil Rao', 'PESU', 'QUIZ', 'Tech Quiz', 100, 'Mr Arun', '9845000002'), (3, '22EC007', 'Sana Sheikh', 'RVCE', 'HACK', 'Hackathon', 300, 'Ms Kavitha', '9845000001'), (4, '23ME112', 'Varun Pillai', 'BMSCE', 'DANCE', 'Group Dance', 500, 'Ms Reema', '9845000003'), (5, '22EC007', 'Sana Sheikh', 'RVCE', 'QUIZ', 'Tech Quiz', 100, 'Mr Arun', '9845000002'), (6, '24IT019', 'Ritu Jain', 'PESU', 'HACK', 'Hackathon', 300, 'Ms Kavitha', '9845000001');
-- Delete anomaly: Varun cancels; his was the only Group Dance registrationDELETE FROM fest_sheet WHERE reg_no = 4;SELECT COUNT(*) AS rows_about_group_danceFROM fest_sheetWHERE event_code = 'DANCE';
-- Insert anomaly: a new event, Robo Race, before anyone registersINSERT INTO fest_sheet (reg_no, event_code, event_name, fee, coordinator, coord_phone)VALUES (7, 'ROBO', 'Robo Race', 250, 'Mr Dev', '9845000004');+------------------------+ | rows_about_group_dance | +------------------------+ | 0 | +------------------------+ ERROR 1364 (HY000): Field 'roll_no' doesn't have a default value
What this code does
- Deleting one registration also deleted the only record of the Group Dance event, its fee and Ms Reema's phone number. The count is 0.
- The new event cannot be saved without a student. MySQL's strict mode (on by default in MySQL 8.0 and later) rejects the row with error 1364, because
roll_nohas no value and no default. The only "fix" would be a fake student row.
- An update anomaly: one fact is stored in many rows, and an update that misses some of them leaves the table contradicting itself.
- An insert anomaly: you cannot store one fact without inventing another. A new event cannot be added until some student registers for it, because every row needs a student.
- A delete anomaly: deleting one fact destroys another. When the only Group Dance registration is cancelled, the event, its fee and its coordinator vanish from the database.
Predict the output
What does the sheet say now?
Use the fest sheet of the examples above.
Round 1 of 2
Only registration 1 gets Ms Kavitha's new phone number. How many different numbers does she now have?
What normalisation means
Normalisation is splitting a table into smaller, linked tables so that every fact is stored once, in the one table where it belongs. It follows rules called normal forms: 1NF, 2NF, 3NF and BCNF.
The cure for redundancy is to split the sheet so each fact lives in one table. The picture shows the idea; the diagram and scripts below build the split fest design and show every anomaly gone.
Example 4 · ER diagram of the normalised fest design
+----------------+ +----------------+ +---------------+| fest_students | | registrations | | events ||----------------| |----------------| |---------------|| PK roll_no | ||------o{ | PK reg_no | o{------|| | PK event_code || name | | FK roll_no | | event_name || college | | FK event_code | | fee |+----------------+ +----------------+ | FK coord_id | +---------------+ o{ | || +---------------+ | coordinators | |---------------| | PK coord_id | | name | | phone | +---------------+Each fact now has one home: a student's name and college -> fest_students an event's name, fee, coordinator -> events a coordinator's phone -> coordinators who registered for what -> registrations (the M:N join table)
What this code does
- Each functional dependency of the sheet became a table:
roll_no → name, college,event_code → event_name, fee, coord_idandcoord_id → name, phone. registrationsis the join table of the many-to-many relationship between students and events. It keeps its ownreg_nobecause the fest prints that number on each pass.
Example 5 · The normalised tables: each anomaly is gone
CREATE TABLE coordinators ( coord_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL, phone VARCHAR(10) NOT NULL);CREATE TABLE events ( event_code VARCHAR(5) PRIMARY KEY, event_name VARCHAR(30) NOT NULL, fee INT NOT NULL, coord_id INT NOT NULL, FOREIGN KEY (coord_id) REFERENCES coordinators (coord_id));CREATE TABLE fest_students ( roll_no VARCHAR(10) PRIMARY KEY, name VARCHAR(40) NOT NULL, college VARCHAR(10) NOT NULL);CREATE TABLE registrations ( reg_no INT PRIMARY KEY, roll_no VARCHAR(10) NOT NULL, event_code VARCHAR(5) NOT NULL, UNIQUE (roll_no, event_code), FOREIGN KEY (roll_no) REFERENCES fest_students (roll_no), FOREIGN KEY (event_code) REFERENCES events (event_code));
INSERT INTO coordinators (name, phone) VALUES ('Ms Kavitha', '9845000001'), ('Mr Arun', '9845000002'), ('Ms Reema', '9845000003');INSERT INTO events VALUES ('HACK', 'Hackathon', 300, 1), ('QUIZ', 'Tech Quiz', 100, 2), ('DANCE', 'Group Dance', 500, 3);INSERT INTO fest_students VALUES ('22CS041', 'Nikhil Rao', 'PESU'), ('22EC007', 'Sana Sheikh', 'RVCE'), ('23ME112', 'Varun Pillai', 'BMSCE'), ('24IT019', 'Ritu Jain', 'PESU');INSERT INTO registrations VALUES (1, '22CS041', 'HACK'), (2, '22CS041', 'QUIZ'), (3, '22EC007', 'HACK'), (4, '23ME112', 'DANCE'), (5, '22EC007', 'QUIZ'), (6, '24IT019', 'HACK');
UPDATE coordinators SET phone = '9900099000' WHERE coord_id = 1; -- one rowDELETE FROM registrations WHERE reg_no = 4; -- event staysINSERT INTO coordinators (name, phone) VALUES ('Mr Dev', '9845000004');INSERT INTO events VALUES ('ROBO', 'Robo Race', 250, 4); -- no student needed
SELECT e.event_code, e.event_name, e.fee, c.name AS coordinator, c.phone, COUNT(r.reg_no) AS registrationsFROM events eJOIN coordinators c ON c.coord_id = e.coord_idLEFT JOIN registrations r ON r.event_code = e.event_codeGROUP BY e.event_code, e.event_name, e.fee, c.name, c.phoneORDER BY e.event_code;
-- Lossless: the joins rebuild the original sheetSELECT r.reg_no, s.name AS student, e.event_name, e.fee, c.name AS coordinatorFROM registrations rJOIN fest_students s ON s.roll_no = r.roll_noJOIN events e ON e.event_code = r.event_codeJOIN coordinators c ON c.coord_id = e.coord_idORDER BY r.reg_no;+------------+-------------+-----+-------------+------------+---------------+ | event_code | event_name | fee | coordinator | phone | registrations | +------------+-------------+-----+-------------+------------+---------------+ | DANCE | Group Dance | 500 | Ms Reema | 9845000003 | 0 | | HACK | Hackathon | 300 | Ms Kavitha | 9900099000 | 3 | | QUIZ | Tech Quiz | 100 | Mr Arun | 9845000002 | 2 | | ROBO | Robo Race | 250 | Mr Dev | 9845000004 | 0 | +------------+-------------+-----+-------------+------------+---------------+ +--------+-------------+------------+-----+-------------+ | reg_no | student | event_name | fee | coordinator | +--------+-------------+------------+-----+-------------+ | 1 | Nikhil Rao | Hackathon | 300 | Ms Kavitha | | 2 | Nikhil Rao | Tech Quiz | 100 | Mr Arun | | 3 | Sana Sheikh | Hackathon | 300 | Ms Kavitha | | 5 | Sana Sheikh | Tech Quiz | 100 | Mr Arun | | 6 | Ritu Jain | Hackathon | 300 | Ms Kavitha | +--------+-------------+------------+-----+-------------+
What this code does
- The phone change touched one row, and every Hackathon line now shows the new number. Group Dance still exists with 0 registrations, and Robo Race was added with no student at all.
- The last query joins the four tables and gives back the sheet's rows (minus the cancelled one). Splitting lost nothing; it only stopped the repetition.
After normalising, each anomaly disappears. A phone number lives in one row of coordinators, so one update fixes it everywhere. An event is a row of events, so it can exist before anyone registers and survives when registrations are cancelled.
Nothing is lost. A split done correctly is lossless: joining the tables back gives exactly the original sheet. The cost is that reading the full picture now needs joins, a trade-off the lesson on denormalising comes back to.
Functional dependencies in plain words
A functional dependency A → B means that if you know A, there is exactly one B. event_code → fee says each event code has one fee. The left side, A, is the determinant.
Normal forms are defined with dependencies. The queries below test a dependency on real data: if every event code shows one name and one fee, the rule holds.
Example 6 · Checking functional dependencies with SQL
CREATE TABLE fest_sheet ( reg_no INT PRIMARY KEY, roll_no VARCHAR(10) NOT NULL, student VARCHAR(40) NOT NULL, college VARCHAR(10) NOT NULL, event_code VARCHAR(5) NOT NULL, event_name VARCHAR(30) NOT NULL, fee INT NOT NULL, coordinator VARCHAR(30) NOT NULL, coord_phone VARCHAR(10) NOT NULL);
INSERT INTO fest_sheet VALUES (1, '22CS041', 'Nikhil Rao', 'PESU', 'HACK', 'Hackathon', 300, 'Ms Kavitha', '9845000001'), (2, '22CS041', 'Nikhil Rao', 'PESU', 'QUIZ', 'Tech Quiz', 100, 'Mr Arun', '9845000002'), (3, '22EC007', 'Sana Sheikh', 'RVCE', 'HACK', 'Hackathon', 300, 'Ms Kavitha', '9845000001'), (4, '23ME112', 'Varun Pillai', 'BMSCE', 'DANCE', 'Group Dance', 500, 'Ms Reema', '9845000003'), (5, '22EC007', 'Sana Sheikh', 'RVCE', 'QUIZ', 'Tech Quiz', 100, 'Mr Arun', '9845000002'), (6, '24IT019', 'Ritu Jain', 'PESU', 'HACK', 'Hackathon', 300, 'Ms Kavitha', '9845000001');
-- event_code -> event_name, fee : one value per code?SELECT event_code, COUNT(DISTINCT event_name) AS names, COUNT(DISTINCT fee) AS fees, COUNT(DISTINCT roll_no) AS studentsFROM fest_sheetGROUP BY event_codeORDER BY event_code;
-- roll_no -> student, college : one value per roll number?SELECT roll_no, COUNT(DISTINCT student) AS names, COUNT(DISTINCT college) AS collegesFROM fest_sheetGROUP BY roll_noORDER BY roll_no;+------------+-------+------+----------+ | event_code | names | fees | students | +------------+-------+------+----------+ | DANCE | 1 | 1 | 1 | | HACK | 1 | 1 | 3 | | QUIZ | 1 | 1 | 2 | +------------+-------+------+----------+ +---------+-------+----------+ | roll_no | names | colleges | +---------+-------+----------+ | 22CS041 | 1 | 1 | | 22EC007 | 1 | 1 | | 23ME112 | 1 | 1 | | 24IT019 | 1 | 1 | +---------+-------+----------+
What this code does
- Every event code has exactly one name and one fee, so the data agrees with
event_code → event_name, fee. Thestudentscolumn is 3 for HACK, soevent_codedoes not determineroll_no. - Every roll number has one name and one college:
roll_no → student, collegeholds. Any count above 1 here would mean the data already contradicts a business rule.
Dependencies come from business rules, not from the rows you happen to have. Data can disprove a dependency (two different fees for HACK), but data alone cannot prove one: six rows that agree today may disagree tomorrow. Still, a quick GROUP BY ... HAVING COUNT(DISTINCT ...) > 1 query is a good check on real data.
In the fest sheet: reg_no → everything (it is the key), roll_no → student, college, event_code → event_name, fee, coordinator and coordinator → coord_phone. Every dependency whose determinant is not the key is a fact being repeated, and is a candidate for its own table.
Predict the output
Test a dependency
Use the fest sheet of the examples above.
Round 1 of 2
Does event_code determine roll_no? How many different students joined the HACK event?
Next step
Check your understanding
11 quick questions