Normalization in DBMS: 1NF, 2NF, 3NF Explained with One Example
One messy school table taken step by step through 1NF, 2NF and 3NF — watch the problems disappear at each stage instead of memorizing definitions.
Why normalization exists — feel the pain first
student_records (the BAD table)
+------+-------+----------------+---------+--------------+-----------+
| roll | name | subjects | teacher | teacher_phone| class |
| 101 | Aman | Math, Science | Sharma | 98765xxxxx | 10 |
| 102 | Priya | Math | Sharma | 98765xxxxx | 10 |
| 103 | Rahul | Science | Verma | 91234xxxxx | 9 |
+------+-------+----------------+---------+--------------+-----------+Three real problems hiding here:
- Update problem: Sharma changes his phone number → you must fix it in EVERY row he appears in. Miss one row = two different numbers for one teacher.
- Insert problem: a new teacher joins but has no students yet → you cannot store him (no roll number to attach!).
- Delete problem: Rahul leaves school → deleting his row also deletes Verma's phone number, which had nothing to do with Rahul leaving.
Normalization removes these three problems step by step.
1NF — one value per cell
Rule: every cell holds a single value, no lists. "Math, Science" in one cell breaks this — you cannot search or count subjects properly.
After 1NF: split the multi-value cell into rows
+------+-------+---------+---------+--------------+-------+
| roll | name | subject | teacher | teacher_phone| class |
| 101 | Aman | Math | Sharma | 98765xxxxx | 10 |
| 101 | Aman | Science | Sharma | 98765xxxxx | 10 |
| 102 | Priya | Math | Sharma | 98765xxxxx | 10 |
| 103 | Rahul | Science | Verma | 91234xxxxx | 9 |
+------+-------+---------+---------+--------------+-------+Searchable now — but look, repetition got WORSE. That's expected: 1NF fixes structure; 2NF and 3NF fix repetition.
2NF — facts must depend on the WHOLE key
The key here is (roll + subject) together. But name and class depend only on roll — Aman is Aman whichever subject the row is about. Rule: remove facts that depend on only part of the key into their own table.
students enrollments
+------+-------+-------+ +------+---------+---------+--------------+
| roll | name | class | | roll | subject | teacher | teacher_phone|
| 101 | Aman | 10 | | 101 | Math | Sharma | 98765xxxxx |
| 102 | Priya | 10 | | 101 | Science | Sharma | 98765xxxxx |
| 103 | Rahul | 9 | | 102 | Math | Sharma | 98765xxxxx |
+------+-------+-------+ | 103 | Science | Verma | 91234xxxxx |
+------+---------+---------+--------------+Aman's name is now stored once. But Sharma's phone still repeats — one more step.
3NF — no fact about a non-key thing
teacher_phone doesn't describe the enrollment — it describes the teacher. It depends on a non-key column (teacher). Rule: facts about a non-key column move to that thing's own table.
students enrollments teachers
+------+-------+ +------+---------+-----+ +---------+--------------+
| roll | name | | roll | subject | t_id| | t_id | name | phone |
| 101 | Aman | | 101 | Math | 1 | | 1 |Sharma| 98765 |
| 102 | Priya | | 101 | Science | 1 | | 2 |Verma | 91234 |
| 103 | Rahul | | 102 | Math | 1 | +---------+--------------+
+------+-------+ | 103 | Science | 2 |
+------+---------+-----+Now re-check the three diseases: Sharma's phone changes → update ONE row in teachers. New teacher with no students → insert into teachers, done. Rahul leaves → delete his rows, Verma's phone survives. All three cured.
The memory trick for interviews
| Form | Rule in plain words | Fixes |
|---|---|---|
| 1NF | One value per cell, no lists | Unsearchable multi-values |
| 2NF | No fact depending on part of the key | Repetition of student data |
| 3NF | No fact about a non-key column | Repetition of teacher data |
Normalization क्यों है — पहले दर्द महसूस कीजिए
student_records (BURI table)
+------+-------+----------------+---------+--------------+-----------+
| roll | name | subjects | teacher | teacher_phone| class |
| 101 | Aman | Math, Science | Sharma | 98765xxxxx | 10 |
| 102 | Priya | Math | Sharma | 98765xxxxx | 10 |
| 103 | Rahul | Science | Verma | 91234xxxxx | 9 |
+------+-------+----------------+---------+--------------+-----------+यहां तीन असली problems छुपी हैं:
- Update problem: Sharma का phone बदला → हर उस row में ठीक करना पड़ेगा जहां वह है. एक row छूटी = एक teacher के दो अलग numbers.
- Insert problem: नया teacher आया पर अभी students नहीं → उसे store ही नहीं कर सकते (जोड़ने को roll number ही नहीं!).
- Delete problem: Rahul school छोड़ गया → उसकी row delete करते ही Verma का phone भी गया, जिसका Rahul के जाने से कोई लेना-देना नहीं था.
Normalization इन तीनों problems को step by step हटाती है.
1NF — हर cell में एक value
Rule: हर cell में single value, कोई list नहीं. एक cell में "Math, Science" इसे तोड़ता है — subjects ठीक से search या count नहीं हो सकते.
1NF के बाद: multi-value cell को rows में तोड़ो
+------+-------+---------+---------+--------------+-------+
| roll | name | subject | teacher | teacher_phone| class |
| 101 | Aman | Math | Sharma | 98765xxxxx | 10 |
| 101 | Aman | Science | Sharma | 98765xxxxx | 10 |
| 102 | Priya | Math | Sharma | 98765xxxxx | 10 |
| 103 | Rahul | Science | Verma | 91234xxxxx | 9 |
+------+-------+---------+---------+--------------+-------+अब searchable है — पर देखिए, repetition और BADH गई. यही expected है: 1NF structure ठीक करता है; repetition 2NF और 3NF ठीक करेंगे.
2NF — हर fact PURI key पर depend करे
यहां key है (roll + subject) मिलकर. लेकिन name और class सिर्फ roll पर depend करते हैं — Aman हर subject वाली row में Aman ही है. Rule: जो facts key के सिर्फ हिस्से पर depend करें, उन्हें अपनी अलग table में निकालो.
students enrollments
+------+-------+-------+ +------+---------+---------+--------------+
| roll | name | class | | roll | subject | teacher | teacher_phone|
| 101 | Aman | 10 | | 101 | Math | Sharma | 98765xxxxx |
| 102 | Priya | 10 | | 101 | Science | Sharma | 98765xxxxx |
| 103 | Rahul | 9 | | 102 | Math | Sharma | 98765xxxxx |
+------+-------+-------+ | 103 | Science | Verma | 91234xxxxx |
+------+---------+---------+--------------+Aman का नाम अब एक बार store है. पर Sharma का phone अभी भी repeat हो रहा है — एक step और.
3NF — non-key चीज़ का कोई fact नहीं
teacher_phone enrollment को describe नहीं करता — teacher को करता है. यह एक non-key column (teacher) पर depend करता है. Rule: non-key column के बारे में facts उस चीज़ की अपनी table में जाएं.
students enrollments teachers
+------+-------+ +------+---------+-----+ +---------+--------------+
| roll | name | | roll | subject | t_id| | t_id | name | phone |
| 101 | Aman | | 101 | Math | 1 | | 1 |Sharma| 98765 |
| 102 | Priya | | 101 | Science | 1 | | 2 |Verma | 91234 |
| 103 | Rahul | | 102 | Math | 1 | +---------+--------------+
+------+-------+ | 103 | Science | 2 |
+------+---------+-----+अब तीनों बीमारियां दोबारा check कीजिए: Sharma का phone बदला → teachers में EK row update. Students के बिना नया teacher → teachers में insert, बस. Rahul गया → उसकी rows delete, Verma का phone बचा रहा. तीनों ठीक.
Interview के लिए याद रखने की trick
| Form | आसान भाषा में rule | ठीक करता है |
|---|---|---|
| 1NF | हर cell में एक value, list नहीं | Unsearchable multi-values |
| 2NF | Key के हिस्से पर depend करने वाला कोई fact नहीं | Student data की repetition |
| 3NF | Non-key column का कोई fact नहीं | Teacher data की repetition |
Frequently Asked Questions
What is normalization in simple words?
Normalization is organizing tables so each fact is stored exactly once — removing update, insert and delete anomalies by splitting data into related tables through 1NF, 2NF and 3NF.
What is the one-line trick to remember 1NF, 2NF and 3NF?
"Every fact depends on the key (1NF), the whole key (2NF), and nothing but the key (3NF)."
What are update, insert and delete anomalies?
Update anomaly: one fact stored in many rows goes inconsistent when partially updated; insert anomaly: a fact cannot be stored without unrelated data; delete anomaly: deleting a row accidentally destroys an unrelated fact.