Chapter 21 of 41

Normalization & Anomalies

Imagine your college keeps all its academic records in one big spreadsheet. Every row has a student's roll number, name, department, the department's HOD, a course, the course name and the grade.

It looks convenient. Everything is in one place. But after a few months, strange problems start to appear: the same HOD is spelled two different ways, a course disappears when its last student drops out, and you cannot add a new course until somebody enrolls in it.

These problems are not bugs in the software. They are caused by the design of the table itself.

Normalization is the process of fixing such designs.

Normalization is the process of organizing the attributes of a database into well-structured relations so that redundancy is reduced and insertion, update and deletion anomalies are avoided.

In this chapter we will see what goes wrong in a badly designed table, why it goes wrong, and the big picture of how normalization fixes it. The individual normal forms are covered in detail in the next chapters.


The Problem: One Table for Everything

Let's build the "one big table" from the story above. We will actually run it in MySQL so you can see the problems with your own eyes.

CREATE TABLE student_course (
  roll_no     INT,
  name        VARCHAR(30),
  dept        VARCHAR(10),
  hod         VARCHAR(30),
  course_id   VARCHAR(10),
  course_name VARCHAR(40),
  grade       CHAR(2),
  PRIMARY KEY (roll_no, course_id)
);

INSERT INTO student_course VALUES
(101, 'Aarav', 'CSE', 'Dr. Mehta', 'CS301', 'Database Systems',  'A'),
(101, 'Aarav', 'CSE', 'Dr. Mehta', 'CS302', 'Operating Systems', 'B'),
(102, 'Diya',  'CSE', 'Dr. Mehta', 'CS301', 'Database Systems',  'A'),
(103, 'Kabir', 'ECE', 'Dr. Iyer',  'EC201', 'Digital Circuits',  'B'),
(104, 'Sneha', 'CSE', 'Dr. Mehta', 'CS305', 'Compiler Design',   'C');

SELECT * FROM student_course;

Output:

+---------+-------+------+-----------+-----------+-------------------+-------+
| roll_no | name  | dept | hod       | course_id | course_name       | grade |
+---------+-------+------+-----------+-----------+-------------------+-------+
|     101 | Aarav | CSE  | Dr. Mehta | CS301     | Database Systems  | A     |
|     101 | Aarav | CSE  | Dr. Mehta | CS302     | Operating Systems | B     |
|     102 | Diya  | CSE  | Dr. Mehta | CS301     | Database Systems  | A     |
|     103 | Kabir | ECE  | Dr. Iyer  | EC201     | Digital Circuits  | B     |
|     104 | Sneha | CSE  | Dr. Mehta | CS305     | Compiler Design   | C     |
+---------+-------+------+-----------+-----------+-------------------+-------+

The primary key is (roll_no, course_id) because one student can take many courses, and one course can have many students. Only the pair identifies a row.

Now look carefully at the data:

  • Dr. Mehta is stored four times, once for every CSE enrollment.

  • Aarav and his department are stored twice, once per course he takes.

  • Database Systems is stored twice, once per student in that course.

This repetition is called redundancy. Redundancy wastes space, but the real damage is something else: it causes anomalies.


What Is an Anomaly?

An anomaly is an unwanted side effect, during an insert, update or delete, that happens because the same fact is stored in more than one place, or because unrelated facts are stored together in the same row.

There are three classic types:

  1. Update anomaly

  2. Deletion anomaly

  3. Insertion anomaly

Let's trigger each one on our table.


1. Update Anomaly

Suppose the CSE department gets a new HOD, Dr. Rao. A developer writes an update, but filters on the student by mistake (or simply updates only the row on the screen):

UPDATE student_course SET hod = 'Dr. Rao' WHERE roll_no = 101;

SELECT DISTINCT dept, hod FROM student_course ORDER BY dept, hod;

Output:

+------+-----------+
| dept | hod       |
+------+-----------+
| CSE  | Dr. Mehta |
| CSE  | Dr. Rao   |
| ECE  | Dr. Iyer  |
+------+-----------+

The database now says the CSE department has two different HODs. Which one is correct? The database cannot tell you.

The fact "the HOD of CSE is X" was stored in four rows. To change it correctly, you must change all four. Miss even one, and the data becomes inconsistent.

Update anomaly: one real-world fact is stored in many rows, so changing it requires changing all of those rows; a partial update leaves contradictory data.


2. Deletion Anomaly

Sneha (roll 104) leaves the college, so her row is deleted:

DELETE FROM student_course WHERE roll_no = 104;

SELECT COUNT(*) AS cs305_rows FROM student_course WHERE course_id = 'CS305';

Output:

+------------+
| cs305_rows |
+------------+
|          0 |
+------------+

Sneha was the only student in CS305. By deleting her, we also lost the fact that a course called Compiler Design with ID CS305 exists at all.

We wanted to delete a fact about a student, and we accidentally deleted a fact about a course.

Deletion anomaly: deleting one fact unintentionally deletes other, unrelated facts that happened to be stored only in the same row.


3. Insertion Anomaly

The college introduces a new course, CS310 Machine Learning. No student has enrolled yet. Let's try to record it:

INSERT INTO student_course
VALUES (NULL, NULL, NULL, NULL, 'CS310', 'Machine Learning', NULL);

Output:

ERROR 1048 (23000): Column 'roll_no' cannot be null

MySQL refuses, and it is right to refuse. roll_no is part of the primary key, and entity integrity says no part of a primary key may be NULL (see the Integrity Constraints chapter).

So we simply cannot store a course until some student takes it. The same happens for a new department with no students yet: there is no way to record its HOD.

Insertion anomaly: a fact cannot be stored on its own because the table forces you to also provide some other, unrelated fact (here, a student).

The only "workarounds" are bad ones, such as inventing a dummy student with roll number 0. That pollutes the data and every report now has to remember to exclude the fake student.


Why Do These Anomalies Happen?

All three anomalies have the same root cause. Recall the functional dependencies (FDs) in this table (see the Functional Dependencies chapter):

roll_no             → name, dept
dept                → hod
course_id           → course_name
roll_no, course_id  → grade

Using attribute closure (see Attribute Closure & Candidate Keys), the only candidate key is {roll_no, course_id}.

Now notice: only the last FD has the key on its left side. The other three have a left side that is not a key of the table:

  • roll_no → name, dept: a fact about a student

  • dept → hod: a fact about a department

  • course_id → course_name: a fact about a course

A non-key left side can repeat across many rows. Every time it repeats, the right side is copied again. That copying is the redundancy, and the redundancy is what makes updates, inserts and deletes misbehave.

In plain words:

The table is mixing facts about several different "things" (students, departments, courses, enrollments) in one place.

A good design follows a simple rule, often summarized as: "every non-key attribute should depend on the key, the whole key, and nothing but the key." The normal forms make this rule precise.


The Fix: Decomposition

Normalization fixes the problem by decomposing (splitting) the big table into smaller tables, so that each table describes one kind of thing:

student_course(roll_no, name, dept, hod, course_id, course_name, grade)
        │
        ├──→ department(dept, hod)
        ├──→ student(roll_no, name, dept)
        ├──→ course(course_id, course_name)
        └──→ enrollment(roll_no, course_id, grade)

Each table now has exactly one job, and in each one the left side of every FD is the key of that table:

Table

Key

Stores the fact

department

dept

Who is the HOD of a department

student

roll_no

A student's name and department

course

course_id

A course's name

enrollment

(roll_no, course_id)

The grade a student got in a course

To try this yourself, create the four tables and load the original five rows into them (before any of the anomaly experiments above):

CREATE TABLE department (dept VARCHAR(10) PRIMARY KEY, hod VARCHAR(30));
CREATE TABLE student    (roll_no INT PRIMARY KEY, name VARCHAR(30), dept VARCHAR(10));
CREATE TABLE course     (course_id VARCHAR(10) PRIMARY KEY, course_name VARCHAR(40));
CREATE TABLE enrollment (roll_no INT, course_id VARCHAR(10), grade CHAR(2),
                         PRIMARY KEY (roll_no, course_id));

INSERT INTO department VALUES ('CSE', 'Dr. Mehta'), ('ECE', 'Dr. Iyer');
INSERT INTO student    VALUES (101, 'Aarav', 'CSE'), (102, 'Diya', 'CSE'),
                              (103, 'Kabir', 'ECE'), (104, 'Sneha', 'CSE');
INSERT INTO course     VALUES ('CS301', 'Database Systems'), ('CS302', 'Operating Systems'),
                              ('CS305', 'Compiler Design'),  ('EC201', 'Digital Circuits');
INSERT INTO enrollment VALUES (101, 'CS301', 'A'), (101, 'CS302', 'B'), (102, 'CS301', 'A'),
                              (103, 'EC201', 'B'), (104, 'CS305', 'C');

Now let's replay all three problem scenarios on the normalized design:

-- New HOD: change ONE row
UPDATE department SET hod = 'Dr. Rao' WHERE dept = 'CSE';

-- New course with no students: no problem
INSERT INTO course VALUES ('CS310', 'Machine Learning');

-- Sneha's only enrollment is removed: the course survives
DELETE FROM enrollment WHERE roll_no = 104;

SELECT * FROM course ORDER BY course_id;

Output:

+-----------+-------------------+
| course_id | course_name       |
+-----------+-------------------+
| CS301     | Database Systems  |
| CS302     | Operating Systems |
| CS305     | Compiler Design   |
| CS310     | Machine Learning  |
| EC201     | Digital Circuits  |
+-----------+-------------------+

All three anomalies are gone:

  • The HOD is stored once, so it cannot become inconsistent.

  • CS305 still exists even though nobody is enrolled in it.

  • CS310 was added without inventing a fake student.

And we lost nothing. When we need the original "wide" view, a join rebuilds it:

SELECT s.roll_no, s.name, s.dept, d.hod, e.course_id, c.course_name, e.grade
FROM enrollment e
JOIN student s    ON s.roll_no = e.roll_no
JOIN department d ON d.dept = s.dept
JOIN course c     ON c.course_id = e.course_id
ORDER BY s.roll_no, e.course_id;

Output:

+---------+-------+------+----------+-----------+-------------------+-------+
| roll_no | name  | dept | hod      | course_id | course_name       | grade |
+---------+-------+------+----------+-----------+-------------------+-------+
|     101 | Aarav | CSE  | Dr. Rao  | CS301     | Database Systems  | A     |
|     101 | Aarav | CSE  | Dr. Rao  | CS302     | Operating Systems | B     |
|     102 | Diya  | CSE  | Dr. Rao  | CS301     | Database Systems  | A     |
|     103 | Kabir | ECE  | Dr. Iyer | EC201     | Digital Circuits  | B     |
+---------+-------+------+----------+-----------+-------------------+-------+

Notice that every CSE row now shows Dr. Rao, because the join reads the single stored value.


Decomposition Must Be Done Carefully

Splitting a table is not automatically safe. A careless split can cause two new problems:

  1. Spurious rows: when you join the pieces back, you get rows that never existed in the original. Such a split is called lossy. A safe split is lossless.

  2. Lost constraints: an FD that could be checked inside one table now spans several tables and can only be checked with a join. A good split is dependency preserving.

For example, splitting our table into (roll_no, name, dept, hod) and (dept, course_id, course_name, grade) would be lossy: joining them on dept would pair every CSE student with every CSE grade. Both properties, and how to test them, are covered in the Lossless Join & Dependency Preservation chapter.

So the real goal of normalization is:

Decompose the relation to remove redundancy, while making sure the decomposition is lossless (always) and dependency preserving (whenever possible).


The Normal Forms: A Roadmap

A normal form is a condition that a relation schema must satisfy. Each higher normal form removes one more kind of redundancy. They are nested: a relation in a higher normal form is automatically in all the lower ones.

┌──────────────────────────────────────────────────┐
│ 1NF   atomic values, no repeating groups         │
│  ┌─────────────────────────────────────────────┐ │
│  │ 2NF   no partial dependency                 │ │
│  │  ┌────────────────────────────────────────┐ │ │
│  │  │ 3NF   no transitive dependency         │ │ │
│  │  │  ┌───────────────────────────────────┐ │ │ │
│  │  │  │ BCNF  every determinant is a      │ │ │ │
│  │  │  │       superkey                    │ │ │ │
│  │  │  │  ┌──────────────────────────────┐ │ │ │ │
│  │  │  │  │ 4NF  no MVD from a non-key   │ │ │ │ │
│  │  │  │  │  ┌─────────────────────────┐ │ │ │ │ │
│  │  │  │  │  │ 5NF  JDs only via keys  │ │ │ │ │ │
│  │  │  │  │  └─────────────────────────┘ │ │ │ │ │
│  │  │  │  └──────────────────────────────┘ │ │ │ │
│  │  │  └───────────────────────────────────┘ │ │ │
│  │  └────────────────────────────────────────┘ │ │
│  └─────────────────────────────────────────────┘ │
└──────────────────────────────────────────────────┘

Normal form

Removes

Based on

Covered in

1NF

Multi-valued cells and repeating groups

Structure of values

1NF, 2NF & 3NF

2NF

Non-key attributes depending on part of a key

Functional dependencies

1NF, 2NF & 3NF

3NF

Non-key attributes depending on other non-key attributes

Functional dependencies

1NF, 2NF & 3NF

BCNF

Any FD whose left side is not a superkey

Functional dependencies

BCNF

4NF

Independent multi-valued facts in one table

Multivalued dependencies

4NF & 5NF

5NF

Redundancy that only disappears with a 3-way (or more) split

Join dependencies

4NF & 5NF

In industry, a design in 3NF or BCNF is usually considered "normalized". 4NF and 5NF problems are rarer, but they do appear in interviews and GATE.


Our Example, Normal Form by Normal Form

Here is a quick preview of how the normal forms would classify the original student_course table. Do not worry about the exact definitions yet; the next chapter explains them.

Key: {roll_no, course_id}

roll_no   → name, dept     depends on PART of the key   → violates 2NF
course_id → course_name    depends on PART of the key   → violates 2NF
dept      → hod            non-key → non-key            → violates 3NF

Highest normal form of student_course: 1NF

The four-table design we built is in BCNF: in every table, the left side of every FD is a key.


Is Redundancy Always Bad? (Denormalization)

Normalization makes writes safe, but it has a cost: to answer a question you may have to join several tables. On very read-heavy systems (dashboards, reports, analytics warehouses), those joins can become expensive.

So sometimes engineers deliberately add redundancy back. This is called denormalization. Examples:

  • Storing customer_name inside an orders table so the order list page doesn't need a join.

  • Keeping a precomputed total_amount column on an order instead of summing order lines every time.

  • Star schemas in data warehouses, where dimension tables are intentionally not fully normalized.

The key point:

Denormalization is a conscious trade-off made after normalizing, not an excuse to skip normalization.

When you denormalize, you accept the risk of anomalies and must handle them yourself, for example with triggers, application logic or batch jobs that keep the copies in sync.

Normalized design

Denormalized design

Each fact stored once

Some facts stored many times

Inserts, updates, deletes are simple and safe

Writes must update every copy

Reads may need joins

Reads are faster and simpler

Typical for transactional (OLTP) systems

Common in reporting / analytical (OLAP) systems


Advantages of Normalization

  • Less redundancy: each fact is stored once, so storage is used efficiently.

  • Consistency: a fact cannot have two contradicting values.

  • No insert/update/delete anomalies.

  • Clearer design: each table represents one entity or one relationship, which is easier to understand and extend.

  • Simpler constraints: most rules become primary key, unique and foreign key constraints that the DBMS enforces for free.

Disadvantages

  • More tables, so queries often need joins.

  • Over-normalization (splitting beyond what the data needs) can make a schema harder to use without real benefit.

  • Some dependencies may be impossible to preserve in the highest normal forms (we will see this with BCNF).


Common Mistakes

  • Thinking redundancy only wastes space. Storage is cheap; the real problem is inconsistency caused by anomalies.

  • Normalizing by intuition only. Splitting "whatever looks repeated" can create lossy joins. Always reason with FDs and keys.

  • Confusing the three anomalies. Update = changing a repeated fact; Deletion = losing an unrelated fact; Insertion = cannot add a fact without an unrelated one.

  • Believing "normalized" means "fast". Normalization is about correctness. Performance may need indexes or controlled denormalization.

  • Thinking the insertion anomaly is just a MySQL error to work around. The error is correct (a primary key column cannot be NULL). The real cause is the design, which forces you to supply a student key for a fact that has nothing to do with students.


Interview Questions

What is normalization and why is it needed?

Normalization is organizing attributes into well-structured relations to reduce redundancy. It is needed because redundancy causes insertion, update and deletion anomalies, which lead to inconsistent or lost data.

Name and explain the three types of anomalies.

Insertion anomaly: cannot insert a fact without also inserting an unrelated fact. Update anomaly: a fact stored in many rows must be updated everywhere, or data becomes inconsistent. Deletion anomaly: deleting one fact also deletes another, unrelated fact.

What is the root cause of anomalies, in terms of FDs?

Functional dependencies whose left-hand side is not a (super)key of the table. The left side can repeat across rows, so the right side gets duplicated.

What two properties should a decomposition have?

It must be lossless (joining the pieces gives back exactly the original relation, no spurious tuples), and ideally dependency preserving (every FD can be checked within a single table).

What is denormalization? When is it used?

Deliberately adding redundancy back into a normalized design to speed up reads, typically in read-heavy, reporting or analytical systems. The cost is extra work to keep duplicate copies consistent.

Up to which normal form do real-world databases usually go?

Usually 3NF or BCNF. 4NF and 5NF issues are less common but should be removed when present.


Summary

  • Redundancy means the same fact is stored in many places.

  • Redundancy causes three anomalies: insertion, update and deletion.

  • The root cause is a table that mixes facts about different things, i.e. FDs whose left side is not a key.

  • Normalization decomposes the table so each table describes one thing and every determinant is a key.

  • A decomposition must be lossless, and should be dependency preserving.

  • The normal forms 1NF ⊃ 2NF ⊃ 3NF ⊃ BCNF ⊃ 4NF ⊃ 5NF each remove a further kind of redundancy.

  • Denormalization is a deliberate, read-performance trade-off made after normalizing.

One fact, one place. That is the whole idea of normalization.