Normalization in DBMS – 1NF, 2NF, 3NF with Example

In this lesson: Normalization in DBMS made easy — why we normalize and how 1NF, 2NF, 3NF remove redundancy using one student table.

What normalization actually solves

Normalization is the process of organizing a database so that the same piece of information is not repeated in many places. It sounds abstract, but the pain it removes is very real. When data repeats, three ugly problems appear: you waste space, updates become dangerous, and your data slowly drifts out of sync.

Imagine a single big table where every student row also stores their course name and course fee. If two hundred students take "Python", the text "Python" and its fee sit in the table two hundred times. Now the fee changes. You must update all two hundred rows perfectly. Miss even one, and your database now claims two different fees for the same course. Normalization prevents this by making sure each fact lives in exactly one place.

The problem: an unnormalized table

Here is a messy table that stores everything together. Watch how much repeats.

Two things are wrong. First, the courses cell holds a list ("Python, MySQL") instead of a single value — that alone makes searching and joining painful. Second, the fee "2000" for Python is written again and again for every student who takes it. We will fix these step by step.

First Normal Form (1NF)

Rule: every cell must hold a single, atomic value. No lists, no comma-separated groups. We split the multi-value rows so each row holds exactly one course.

Now every cell is a single value — the table is in 1NF. Searching for "who takes MySQL" is now a simple query. But notice the fee 2000 for Python still repeats, and the name "Aarav" repeats too. 1NF fixed the list problem, not the repetition problem.

Second Normal Form (2NF)

Rule: be in 1NF, and every non-key column must depend on the whole primary key. Here the natural key is the pair (student_id, course). But course_fee depends only on course, not on the student — that is a partial dependency. We move course facts into their own table.

-- courses table (fee stored ONCE per course)
CREATE TABLE courses (
    course      VARCHAR(30) PRIMARY KEY,
    course_fee  DECIMAL(8,2)
);

-- enrollment table (who takes what)
CREATE TABLE enrollment (
    student_id INT,
    course     VARCHAR(30),
    PRIMARY KEY (student_id, course),
    FOREIGN KEY (course) REFERENCES courses(course)
);

Now the Python fee is stored one single time in courses. Change it there, and every student instantly sees the new fee. The partial dependency is gone — the table is in 2NF.

Third Normal Form (3NF)

Rule: be in 2NF, and no non-key column may depend on another non-key column. Suppose our student table also had city and pincode. The pincode determines the city, so city depends on pincode — a non-key column — not directly on the student id. This is a transitive dependency.

-- pincode -> city moved to its own table
CREATE TABLE areas (
    pincode CHAR(6) PRIMARY KEY,
    city    VARCHAR(40)
);

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name       VARCHAR(50),
    pincode    CHAR(6),
    FOREIGN KEY (pincode) REFERENCES areas(pincode)
);

Now the city for each pincode is written once in areas. The students table only keeps the pincode. The transitive dependency is removed — the design is in 3NF, clean and safe to update.

Quick recap of the three forms

A handy way to remember it: 1NF fixes the shape of a row, 2NF fixes dependence on part of the key, and 3NF fixes dependence between non-key columns.

Common mistakes

• Skipping straight to 3NF. The forms build on each other. Check 1NF and 2NF first, or you will miss a hidden partial dependency.
• Storing lists in one cell. "Python, MySQL" in a single field breaks 1NF and makes every query harder. One value per cell.
• Over-normalizing tiny tables. Splitting a two-column lookup into more tables just adds joins with no benefit. Normalize where repetition actually hurts.
• Confusing normalization with performance tuning. Normalization is about correctness and no redundancy; speed tuning (indexes, denormalization) is a separate, later concern.

Summary

• Normalization stores each fact once, preventing wasted space and update errors.
• 1NF: every cell holds a single atomic value.
• 2NF: in 1NF, and every column depends on the whole key (no partial dependency).
• 3NF: in 2NF, and no non-key column depends on another non-key column (no transitive dependency).
• 3NF is the safe default for most applications.

Next, see worked examples in 1NF, 2NF and 3NF with examples, or learn the rules that protect these tables in constraints in MySQL.