1NF, 2NF and 3NF with Examples

In this lesson: Learn 1NF, 2NF and 3NF with solved examples, rules, dependency diagrams and exam-style questions. Score full marks.

The one table we will normalize

The best way to learn 1NF, 2NF and 3NF is to take a single messy table and fix it in front of your eyes. Below is a student_marks table exactly as a beginner might first design it — everything crammed together.

Three separate problems are hiding here. The subjects cell holds a list. The teacher's phone is repeated for every student the teacher handles. And the phone number really belongs to the teacher, not to the student row. We will clear these one form at a time.

Step 1: Convert to 1NF

1NF rule: every cell must hold one single value. The subjects column breaks this, so we split each subject into its own row.

Now no cell contains a list, so the table is in 1NF. The natural key here is the pair (student_id, subject), because that combination identifies one specific mark row. Two problems remain: the student name repeats, and the teacher phone repeats.

Step 2: Convert to 2NF

2NF rule: be in 1NF, and every non-key column must depend on the whole key. Our key is (student_id, subject). But student_name depends only on student_id — that is a partial dependency. We move student facts into their own table.

CREATE TABLE students (
    student_id   INT PRIMARY KEY,
    student_name VARCHAR(50)
);

CREATE TABLE marks (
    student_id INT,
    subject    VARCHAR(30),
    teacher    VARCHAR(50),
    teacher_phone VARCHAR(15),
    PRIMARY KEY (student_id, subject),
    FOREIGN KEY (student_id) REFERENCES students(student_id)
);

Now the student name is stored once per student, not once per subject. The partial dependency is gone, so the design is in 2NF. But look at marks: the teacher's phone still repeats, and it depends on the teacher, not on the student-subject key.

Step 3: Convert to 3NF

3NF rule: be in 2NF, and no non-key column may depend on another non-key column. In marks, teacher_phone depends on teacher — both are non-key columns. That is a transitive dependency. We pull teacher facts into their own table.

CREATE TABLE teachers (
    teacher       VARCHAR(50) PRIMARY KEY,
    teacher_phone VARCHAR(15)
);

CREATE TABLE marks (
    student_id INT,
    subject    VARCHAR(30),
    teacher    VARCHAR(50),
    PRIMARY KEY (student_id, subject),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (teacher)    REFERENCES teachers(teacher)
);

Now a teacher's phone number lives in exactly one place. Change it once in teachers and every mark row instantly reflects it. The transitive dependency is removed — the design is in 3NF.

The final normalized design

We started with one crowded table and ended with three clean ones, each describing a single kind of thing:

• students — who the student is.
• teachers — the teacher and their phone.
• marks — which student takes which subject under which teacher.

Every fact now lives in one place. No student name, no teacher phone, is ever written twice. This is what a 3NF design buys you: safety when you update, and clarity when you read.

Side-by-side comparison

Notice the pattern: at each step we asked "what does this column really depend on?" and moved it next to the thing it belongs to.

Common mistakes

• Jumping to 3NF without checking 1NF. If a cell still holds a list, no later form is valid. Always fix atomic values first.
• Thinking a single-column key can break 2NF. Partial dependency needs a composite key. Single-column key tables are already in 2NF once in 1NF.
• Leaving the foreign keys out. After splitting tables, connect them with foreign keys, or you lose the guarantee that links stay valid.
• Normalizing data that never repeats. If a column has no redundancy, splitting it just adds joins for nothing.

Summary

• Apply the forms in order: 1NF, then 2NF, then 3NF.
• 1NF: we removed the multi-value subjects cell.
• 2NF: we moved student_name out because it depended on only part of the key.
• 3NF: we moved teacher_phone out because it depended on another non-key column.
• The result: three focused tables where every fact is stored once.

Read the underlying theory in normalization in DBMS, or learn the keys that hold these tables together in primary and foreign key.