SQL Normalization Explained — 1NF, 2NF, 3NF with a Before/After Example

Normalization is the process of structuring a database so the same fact isn't stored in multiple places, which prevents inconsistent data and awkward update logic. It's usually taught with abstract definitions that are hard to connect to a real table. This guide instead starts with one messy, unnormalized table and splits it step by step through 1NF, 2NF, and 3NF.

The Starting Point — An Unnormalized Table

Imagine a single table tracking student course enrollments, storing everything in one place:

enrollments
--------------------------------------------------------------------------
student_id | student_name | courses                | instructor
--------------------------------------------------------------------------
101        | Asha Rao     | Math101, Physics201     | Mr. Iyer, Dr. Nair
102        | Vikram Shah  | Math101                 | Mr. Iyer
--------------------------------------------------------------------------

The courses and instructor columns each hold multiple values in one cell — this immediately breaks the first rule of a relational table.

1NF — First Normal Form: Atomic Values, No Repeating Groups

1NF requires that every column hold a single, atomic value — no comma-separated lists, no repeating groups. We fix this by giving each student-course pair its own row:

enrollments (1NF)
-----------------------------------------------------------------
student_id | student_name | course_id | course_name | instructor
-----------------------------------------------------------------
101        | Asha Rao     | Math101   | Mathematics | Mr. Iyer
101        | Asha Rao     | Physics201| Physics     | Dr. Nair
102        | Vikram Shah  | Math101   | Mathematics | Mr. Iyer
-----------------------------------------------------------------
-- Primary key: (student_id, course_id)

This table is now in 1NF: every cell holds one value, and the primary key (student_id, course_id) uniquely identifies each row. But notice student_name repeats for Asha Rao, and course_nameand instructor repeat for Math101 — that redundancy is what 2NF and 3NF address next.

2NF — Second Normal Form: Remove Partial Dependencies

2NF applies when a table has a composite primary key. It requires that every non-key column depend on the whole key, not just part of it. Here, student_name depends only on student_id (not course_id), and course_name/instructor depend only on course_id — both are partial dependencies. We split them out:

students                          courses
------------------------          ------------------------------------
student_id | student_name         course_id  | course_name | instructor
------------------------          ------------------------------------
101        | Asha Rao              Math101    | Mathematics | Mr. Iyer
102        | Vikram Shah           Physics201 | Physics     | Dr. Nair
------------------------          ------------------------------------

enrollments (2NF)
---------------------------
student_id | course_id
---------------------------
101        | Math101
101        | Physics201
102        | Math101
---------------------------
-- Primary key: (student_id, course_id), each a foreign key

Now each student's name is stored exactly once, and each course's name and instructor are stored exactly once — updating an instructor no longer means updating every enrollment row for that course.

3NF — Third Normal Form: Remove Transitive Dependencies

3NF requires that non-key columns depend only on the primary key — not on another non-key column. Suppose the courses table also stored instructor_department, which really depends on instructor, not on course_id directly:

-- Before 3NF: instructor_department depends on instructor, not course_id
courses
-----------------------------------------------------------------------
course_id  | course_name | instructor | instructor_department
-----------------------------------------------------------------------
Math101    | Mathematics | Mr. Iyer   | Mathematics Dept
Physics201 | Physics     | Dr. Nair   | Physics Dept

-- After 3NF: split instructors into their own table
instructors                              courses (3NF)
-----------------------------------      ---------------------------------
instructor | department                  course_id  | course_name | instructor
-----------------------------------      ---------------------------------
Mr. Iyer   | Mathematics Dept            Math101    | Mathematics | Mr. Iyer
Dr. Nair   | Physics Dept                Physics201 | Physics     | Dr. Nair

If a professor changes department, you now update exactly one row in instructors instead of every course row they teach. This is what "3NF removes transitive dependencies" means in practice.

Querying the Normalized Schema

The final 3NF schema needs JOINs to reassemble the original view, but each fact is now stored exactly once:

SELECT
  s.student_name,
  c.course_name,
  c.instructor,
  i.department
FROM enrollments e
JOIN students s ON s.student_id = e.student_id
JOIN courses c ON c.course_id = e.course_id
JOIN instructors i ON i.instructor = c.instructor
WHERE s.student_id = 101;

Why Normalize? The Three Anomalies It Prevents

• Update anomaly — changing an instructor's department requires updating one row instead of every course row they teach
• Insert anomaly — you can add a new course without needing a student to be enrolled in it first
• Delete anomaly — removing a student's last enrollment doesn't accidentally delete the course or instructor data

Beyond 3NF there are further forms (BCNF, 4NF, 5NF) for edge cases, but for the vast majority of application schemas, reaching 3NF is the practical target.

Frequently Asked Questions