DBMS में Normalization: 1NF, 2NF, 3NF एक Example से समझें
एक बिगड़ी हुई school table को step by step 1NF, 2NF और 3NF से गुज़ारा — definitions रटने की बजाय हर stage पर problems को गायब होते देखिए.
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
Normalization आसान शब्दों में क्या है?
Normalization tables को ऐसे organize करना है कि हर fact exactly एक बार store हो — 1NF, 2NF और 3NF से data को related tables में बांटकर update, insert और delete anomalies हटाना.
1NF, 2NF और 3NF याद रखने की one-line trick क्या है?
"Every fact depends on the key (1NF), the whole key (2NF), and nothing but the key (3NF)."
Update, insert और delete anomalies क्या हैं?
Update anomaly: कई rows में stored fact आधा update होने पर inconsistent हो जाता है; insert anomaly: unrelated data के बिना fact store ही नहीं हो पाता; delete anomaly: row delete करने पर कोई unrelated fact भी गलती से मिट जाता है.