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. Mehtais stored four times, once for every CSE enrollment.Aaravand his department are stored twice, once per course he takes.Database Systemsis 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:
Update anomaly
Deletion anomaly
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 nullMySQL 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 → gradeUsing 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 studentdept → hod: a fact about a departmentcourse_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.
CS305still exists even though nobody is enrolled in it.CS310was 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:
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.
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: 1NFThe 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_nameinside anorderstable so the order list page doesn't need a join.Keeping a precomputed
total_amountcolumn 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.