Normalization

Introduction

Normalization is a step-by-step way to split badly designed tables into good ones, so that each fact is stored once.

Normalization is the theory of good table design. It gives names to the problems of repeated data and a method for splitting tables so those problems cannot happen.

How it works

One big table

-- One big table: every fact about a course is repeated in every enrollment
CREATE TABLE enroll_info (
  student_id INTEGER,
  student_name TEXT,
  course TEXT,
  course_title TEXT,
  teacher TEXT,
  grade TEXT,
  PRIMARY KEY (student_id, course)
);
INSERT INTO enroll_info VALUES
  (1, 'Rahim', 'CSE101', 'Programming', 'Dr. Alam', 'A'),
  (2, 'Nadia', 'CSE101', 'Programming', 'Dr. Alam', 'A+'),
  (3, 'Karim', 'CSE101', 'Programming', 'Dr. Alam', 'B'),
  (2, 'Nadia', 'MAT101', 'Calculus', 'Ms. Sultana', 'A');

-- The course is renamed, but only one row is changed: an update anomaly
UPDATE enroll_info SET course_title = 'Structured Programming'
WHERE student_id = 1 AND course = 'CSE101';

SELECT DISTINCT course, course_title FROM enroll_info WHERE course = 'CSE101';

Everything in one table looks simple, but each course's title and teacher are repeated in every enrollment. That causes three kinds of anomaly:

• Update anomaly: the course title is changed in one row but not the others. The example's last query now finds two different titles for CSE101. Which one is right?
• Insert anomaly: a new course with no students yet cannot be added without inventing a fake student.
• Delete anomaly: if the only student in a course drops it, deleting that row also deletes everything we knew about the course.

Functional dependencies

A functional dependency X → Y means: rows that agree on X always agree on Y. "X determines Y."

In the big table: student_id → student_name, course → course_title, teacher, and (student_id, course) → grade. The key is (student_id, course): together they determine everything.

The normal forms

Each normal form adds a rule to the one before:

A short way to remember 3NF: every non-key column depends on the key, the whole key, and nothing but the key.

Syntax

enroll_info(student_id, student_name, course, course_title, grade)
  student_id → student_name                 partial: breaks 2NF
  course → course_title                     partial: breaks 2NF

students(student_id, student_name)
courses(course, course_title)
enrollments(student_id, course, grade)      every table in 3NF

Example

The fix is to decompose: move each group of facts into its own table, keyed by what determines them.

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);
CREATE TABLE courses (
  code TEXT PRIMARY KEY,
  title TEXT NOT NULL,
  teacher TEXT
);
CREATE TABLE enrollments (
  student_id INTEGER REFERENCES students(id),
  course TEXT REFERENCES courses(code),
  grade TEXT,
  PRIMARY KEY (student_id, course)
);
INSERT INTO students VALUES (1, 'Rahim'), (2, 'Nadia'), (3, 'Karim');
INSERT INTO courses VALUES ('CSE101', 'Programming', 'Dr. Alam'), ('MAT101', 'Calculus', 'Ms. Sultana');
INSERT INTO enrollments VALUES (1, 'CSE101', 'A'), (2, 'CSE101', 'A+'), (3, 'CSE101', 'B'), (2, 'MAT101', 'A');

-- The title is stored once, so one change fixes it everywhere
UPDATE courses SET title = 'Structured Programming' WHERE code = 'CSE101';

-- A new course needs no students
INSERT INTO courses VALUES ('PHY101', 'Physics', NULL);

-- The join gives back the big table's rows
SELECT s.id AS student_id, s.name AS student_name, c.code AS course, c.title AS course_title, c.teacher, e.grade
FROM enrollments e
JOIN students s ON s.id = e.student_id
JOIN courses c ON c.code = e.course
ORDER BY c.code, s.id;

Now the title is stored once, so the same UPDATE changes it everywhere; a course can exist with no students; and dropping an enrollment never loses a course. The join puts the original rows back together.

A decomposition must be lossless: joining the pieces gives back exactly the original rows, no more and no fewer. Splitting on a shared key column, as here, guarantees that. It should also preserve dependencies, so every rule can still be checked inside one table.

Line by line

1. The design has three tables, students, courses and enrollments, and each fact lives in one of them.
2. The course title is stored once, in courses, so one UPDATE renames CSE101 everywhere.
3. PHY101 can be inserted with no students, which the single big table could not do without inventing a student.
4. The final query joins the three tables and gives back the rows of the original big table, with the new title in all three CSE101 rows.

Expected result

student_id  student_name  course  course_title            teacher      grade
----------  ------------  ------  ----------------------  -----------  -----
1           Rahim         CSE101  Structured Programming  Dr. Alam     A
2           Nadia         CSE101  Structured Programming  Dr. Alam     A+
3           Karim         CSE101  Structured Programming  Dr. Alam     B
2           Nadia         MAT101  Calculus                Ms. Sultana  A
(4 rows)

In the real world

Every serious application database is designed to about third normal form, because repeated data drifts out of step. Analytics warehouses sometimes go the other way and denormalize on purpose, so that reports need fewer joins.

Common mistakes

• Splitting a table on columns that are not keys, which produces a lossy decomposition with extra rows when joined.
• Normalizing so far that simple questions need many joins, without a reason.
• Treating 1NF as "no repeated rows": it is about atomic values in each cell.

Best practices

• Write the functional dependencies before deciding the tables.
• Aim for 3NF by default.
• Check a decomposition by joining the pieces back and comparing with the original.

Summary

Repeated facts cause update, insert and delete anomalies. Functional dependencies define the normal forms: 1NF needs atomic values, 2NF removes partial dependencies, 3NF removes transitive ones, and BCNF is stricter still. Lossless decomposition fixes a table.

Mark lesson complete