MySQL + SQL · Lesson 27

Normalization Examples (Solved Step by Step)

In this lesson: Solved normalization examples step by step — unnormalized table to 1NF, 2NF and 3NF with before/after tables.

A worked example

The best way to understand normalization is to take one messy table and walk it through 1NF, 2NF and 3NF step by step. We will use a student-course table and watch it become clean, related tables at each stage.

💡 One table in → three clean tables out, removing a different problem at each step.

The unnormalized table

student_id	student_name	courses	dept	dept_head
1	Aman	C1, C2	Science	Dr. Rao
2	Diya	C1	Arts	Ms. Iyer

Problems: courses holds a list, and dept_head repeats for every student in the same department. Let us fix these.

Convert to 1NF

Make every cell a single value by splitting the course list into rows:

student_id	student_name	course	dept	dept_head
1	Aman	C1	Science	Dr. Rao
1	Aman	C2	Science	Dr. Rao
2	Diya	C1	Arts	Ms. Iyer

Now each cell is atomic. The key is the composite (student_id, course).

Convert to 2NF

Remove partial dependencies: student_name, dept, dept_head depend only on student_id, not on course. Split them out:

students(student_id PK, student_name, dept, dept_head)
enrollments(student_id FK, course)

Now the student details are stored once per student, not repeated for each course.

Convert to 3NF

Remove the transitive dependency: dept_head depends on dept, not directly on student_id. Move it out:

students(student_id PK, student_name, dept FK)
departments(dept PK, dept_head)
enrollments(student_id FK, course)

Now the department head is stored once per department. Changing it is a single-row update.

The final design

We started with one messy table and ended with three clean, related ones:

students — one row per student, linked to a department.
departments — one row per department, with its head.
enrollments — which student takes which course.

Every fact is now stored exactly once, so updates are simple and the data can never contradict itself.

Common mistakes

Jumping to 3NF directly. Do 1NF, then 2NF, then 3NF in order.
Missing the composite key. After 1NF the key is often two columns together.
Leaving repeated details. If a detail repeats, a dependency still needs fixing.
Forgetting foreign keys. Link the split tables back with FKs.

Summary

Normalize step by step: 1NF, then 2NF, then 3NF.
1NF: split list cells into single-value rows.
2NF: move columns that depend on part of the key into their own table.
3NF: move columns that depend on another non-key column out.
The result is several clean tables linked by keys.

Next, revise the rules in normalization, or the theory in functional dependency.

Frequently Asked Questions

How do you normalize a table step by step?

Start with the messy table, then apply each normal form in order: 1NF (make cells atomic and rows unique), 2NF (remove partial dependencies by splitting), and 3NF (remove transitive dependencies by splitting again). Each step produces cleaner, related tables.

What changes when converting to 1NF?

You split any cell that holds multiple values into separate rows, so every cell has a single atomic value, and you make sure each row is unique. Repeating groups within a row are removed.

What changes when converting to 2NF?

You remove partial dependencies: any column that depends on only part of a composite key is moved to a new table keyed on that part. This stops repeating the same detail across many rows.

What changes when converting to 3NF?

You remove transitive dependencies: any non-key column that depends on another non-key column is moved to its own table. For example, department head moves out with department.

Why go through all three steps?

Each step removes a different kind of redundancy. Doing them in order gives a clean design where each fact is stored once, updates are simple, and there is no risk of inconsistent duplicate data.