DBMS में Normalization — 1NF, 2NF, 3NF Examples

DBMS में normalization क्या है?

Normalization relational database के columns और tables को क्रम से व्यवस्थित करने की प्रक्रिया है, ताकि हर जानकारी ठीक एक जगह store हो। आप एक चौड़ी, गड़बड़ table से शुरू करते हैं, देखते हैं कि उसके columns एक-दूसरे पर कैसे निर्भर हैं, और उसे keys से जुड़ी छोटी tables में बाँट देते हैं। हर बँटवारा एक नियम के अनुसार होता है जिसे normal form कहते हैं — 1NF, 2NF, 3NF, BCNF, 4NF और 5NF — और हर नियम एक ख़ास तरह का दोहराव हटाता है।

यह विचार Edgar F. Codd का है, IBM के उस शोधकर्ता का जिसने relational model बनाया। उन्होंने 1970 में First Normal Form और 1971 में दूसरा और तीसरा form परिभाषित किया। 1974 में Boyce–Codd Normal Form आया, और Ronald Fagin ने चौथा (1977) और पाँचवाँ (1979) form जोड़ा। पचास साल बाद भी हर अच्छा MySQL schema इन्हीं नियमों पर बनता है।

स्कूल के दफ़्तर की अलमारी सोचिए। अगर हर student की file में principal के पते की photocopy लगी हो, तो जिस दिन पता बदलेगा, किसी को सैकड़ों copies ढूँढकर सुधारनी होंगी — और कुछ ज़रूर छूट जाएँगी। Normalization कहता है: पता एक ही file में रखो, और बाक़ी हर file बस उसकी ओर इशारा करे।

बात आसान रखने के लिए यह lesson एक ही चलता हुआ उदाहरण लेता है — एक college register जिसमें students, उनके courses, marks और departments हैं — गड़बड़ शुरुआत से लेकर 3NF तक। BCNF, 4NF और 5NF को अपने छोटे उदाहरण मिलते हैं, क्योंकि वे ऐसी स्थितियों से जुड़े हैं जो इस register में नहीं हैं। हर diagram notebook के पन्ने जैसा बनाया गया है, ताकि आप उसे अपने notes में उतार सकें।

Normalize क्यों? तीन anomalies

ख़राब design वाली table पहले दिन नहीं टूटती। वह धीरे-धीरे टूटती है, जैसे-जैसे data जुड़ता, बदलता और मिटता है। Database की किताबें इन गड़बड़ियों को anomalies कहती हैं, और ये ठीक तीन हैं।

• Update anomaly — एक ही जानकारी कई rows में store है, इसलिए एक बदलाव सब rows में करना पड़ता है। एक row भी छूटी तो table ख़ुद से ही उलटी बात कहने लगती है।
• Insert anomaly — एक जानकारी दर्ज करने के लिए दूसरी नकली जानकारी गढ़नी पड़ती है। नया department तब तक नहीं जुड़ सकता जब तक कम से कम एक student उसमें न आए, क्योंकि हर row में student का roll number ज़रूरी है।
• Delete anomaly — एक जानकारी हटाने पर ग़लती से दूसरी भी हट जाती है। Science का आख़िरी student गया, तो Science के head का नाम भी उसके साथ चला गया।

Normalization इन तीनों को जड़ से हटाता है। साथ में storage कम लगता है, UPDATE statements सरल हो जाते हैं, और reports पर सच में भरोसा किया जा सकता है।

Keys और dependencies पाँच मिनट में

हर normal form कुछ गिने-चुने शब्दों से परिभाषित होता है। इन्हें एक बार समझ लीजिए, आगे के नियम लगभग अपने-आप साफ़ हो जाएँगे।

अगर functional dependency आपके लिए नई है, तो functional dependency वाला lesson closure और candidate key ढूँढने का तरीक़ा क़दम-दर-क़दम समझाता है।

शुरुआत: unnormalized table (UNF)

नया सीखने वाला अक्सर सब कुछ एक ही sheet में ऐसे लिखता है — हर student की एक row, जिसमें उसके सारे phones, courses और marks ठूँस दिए गए हों:

पहली नज़र में यह छोटी और साफ़ लगती है। ध्यान से देखें तो तीन समस्याएँ दिखती हैं:

1. Cells के अंदर सूचियाँ। Aman की row में दो phone numbers, दो courses और दो marks हैं। SQL साफ़ तरीक़े से नहीं बता सकता कि "Maths कौन पढ़ता है", और table में कहीं नहीं लिखा कि 78 DBMS के हैं, Maths के नहीं।
2. दोहराई गई जानकारी। "Computer → Dr. Rao" हर Computer student की row में दोबारा लिखा है।
3. एक table में बहुत सारे विषय। यह table एक साथ चार चीज़ें बताने की कोशिश कर रही है: students, courses, परिणाम और departments।

ऐसी table UNF (unnormalized form) में कहलाती है। अब हम इसे एक-एक normal form करके ठीक करेंगे।

First Normal Form (1NF)

नियम: table 1NF में तब है जब हर cell में एक ही, न टूटने वाली (atomic) value हो, हर column में एक ही तरह का data हो, कोई दोहराता समूह न हो, और हर row किसी key से अलग पहचानी जा सके।

बदलने का तरीक़ा:

1. हर सूची को अलग rows में तोड़िए — हर student की हर course के लिए एक row।
2. हर course को एक code (c_code) दीजिए ताकि वह key का हिस्सा बन सके।
3. जिस दोहराते समूह का courses से कोई लेना-देना नहीं — phone numbers — उसे अपनी अलग table student_phone में ले जाइए।
4. Primary key चुनिए। एक student कई courses लेता है, इसलिए key है जोड़ी (roll, c_code)।

Phone numbers भी हर row में क्यों न दोहरा दें? Aman के 2 phones और 2 courses हैं, तो उसकी 2 × 2 = 4 rows बनेंगी, और हर नया phone rows को फिर गुणा कर देगा। स्वतंत्र सूचियों को अलग रखना ही वह बात है जिसे आगे 4NF औपचारिक रूप देता है।

अब table 1NF में है और SQL उस पर ठीक से query चला सकता है। पर ध्यान दीजिए, "Aman" और "Dr. Rao" अब भी दोहरा रहे हैं। 1NF data का आकार ठीक करता है, दोहराव नहीं।

Second Normal Form (2NF)

नियम: table 1NF में हो, और हर non-key column पूरी primary key पर निर्भर हो, उसके सिर्फ़ एक हिस्से पर नहीं। यानी कोई partial dependency न हो।

जाँचने के लिए 1NF table की functional dependencies लिखिए:

सिर्फ़ marks को key के दोनों हिस्से चाहिए — एक mark एक student के एक course का होता है। name और dept सिर्फ़ roll पर निर्भर हैं, और c_name सिर्फ़ c_code पर। ये partial dependencies हैं, और इन्हीं की वजह से "Aman" और "DBMS" बार-बार लिखे जा रहे हैं।

बदलने का तरीक़ा: हर partial dependency को उसकी अपनी table में ले जाइए, जिसकी key key का वही हिस्सा हो जिस पर वह निर्भर है।

अब course के नाम एक बार store होते हैं, और enrolment table में सिर्फ़ वही है जो सच में student–course जोड़ी से जुड़ा है। एक काम का shortcut: अगर 1NF table की primary key एक ही column की है, तो वह अपने-आप 2NF में है, क्योंकि key का कोई "हिस्सा" ही नहीं जिस पर कुछ निर्भर हो।

फिर भी students table हर Computer student के लिए Dr. Rao दोहरा रही है। अगली समस्या यही है।

Third Normal Form (3NF)

नियम: table 2NF में हो, और कोई non-key column किसी दूसरे non-key column पर निर्भर न हो — यानी कोई transitive dependency न हो। औपचारिक रूप से: हर dependency X → A में या तो X super key हो, या A prime attribute हो।

students में शृंखला है roll → dept → dept_head। Department का head department की जानकारी है, student की नहीं; वह student तक सिर्फ़ dept के रास्ते पहुँचती है।

बदलने का तरीक़ा: निर्भर column को, उस column के साथ जिस पर वह सच में निर्भर है, बाहर ले जाइए।

अब तीनों anomalies फिर से जाँचिए:

पहले तीन forms याद रखने का मशहूर तरीक़ा William Kent का सार है: हर non-key column को "key, पूरी key, और key के सिवा कुछ नहीं" पर निर्भर होना चाहिए। 1NF key देता है, 2NF उसे पूरी key बनाता है, और 3NF पक्का करता है कि key के सिवा कुछ नहीं।

Boyce–Codd Normal Form (BCNF)

नियम: हर non-trivial functional dependency X → Y में X super key होना चाहिए। BCNF थोड़ा और सख़्त 3NF है: इसमें "या A prime attribute हो" वाली छूट नहीं है।

यह अंतर तभी दिखता है जब table में आपस में overlap करती candidate keys हों। एक coaching centre सोचिए जहाँ:

• हर teacher ठीक एक subject पढ़ाता है (teacher → subject), और
• किसी subject में एक student सिर्फ़ एक teacher से पढ़ता है ((student, subject) → teacher)।

Candidate keys हैं (student, subject) और (student, teacher)। हर column किसी न किसी key का हिस्सा है, इसलिए table 3NF पास कर लेती है। पर teacher → subject में बायाँ हिस्सा super key नहीं, इसलिए BCNF फ़ेल — और anomalies असली हैं। "Mr. Sharma DBMS पढ़ाते हैं" दो बार store है, और अगर Rahul छोड़ दे तो यह जानकारी ही खो जाएगी कि Ms. Khan DBMS पढ़ाती हैं।

बदलने का तरीक़ा: गड़बड़ dependency पर table को teacher_subject(teacher, subject) और student_teacher(student, teacher) में बाँटिए। क्योंकि teacher पहली table की key है, दोनों को join करने पर मूल rows हूबहू वापस मिलती हैं — इसे lossless decomposition कहते हैं।

समझौता: नियम "(student, subject) → teacher" अब किसी एक table के अंदर नहीं रहा, इसलिए primary key उसे लागू नहीं कर सकती। उसे trigger या application code में जाँचना होगा। इसी वजह से कुछ designers 3NF पर रुक जाते हैं जब BCNF में जाने से कोई ज़रूरी dependency खो जाए।

Fourth Normal Form (4NF)

नियम: table BCNF में हो और उसमें super key के अलावा कोई non-trivial multi-valued dependency न हो। Multi-valued dependency X ↠ Y का मतलब है कि X से Y values की पूरी सूची तय होती है, और उस सूची का बाक़ी columns से कोई संबंध नहीं।

मान लीजिए college यह दर्ज करता है कि हर student किन clubs में है और कौन-सी भाषाएँ बोलता है। Clubs और भाषाओं का आपस में कोई संबंध नहीं, पर दोनों एक table में हों तो हर combination लिखना पड़ता है:

2 clubs और 2 भाषाओं के लिए Aman को 4 rows चाहिए। German जोड़ें तो 2 rows और जोड़नी होंगी — हर club के लिए एक — वरना table कुछ ग़लत बात कहने लगेगी। दिलचस्प बात यह है कि यह table पहले से BCNF में है (इसकी इकलौती key तीनों columns हैं और कोई functional dependency नहीं), फिर भी दोहराव से भरी है। 4NF यही कमी पूरी करता है।

बदलने का तरीक़ा: हर स्वतंत्र multi-valued जानकारी को अपनी table में रखिए: student_club(roll, club) और student_language(roll, language)। अब भाषा जोड़ना सिर्फ़ एक row है।

Fifth Normal Form (5NF)

नियम: table 5NF (जिसे Project-Join Normal Form भी कहते हैं) में तब है जब उसे जानकारी खोए बिना छोटी tables में बाँटा न जा सके, सिवाय उन तरीक़ों के जो उसकी candidate keys से पहले ही तय हैं। इसका निशाना join dependency है: ऐसी table जो अपने तीन या अधिक projections के join के बराबर हो, पर किन्हीं दो के join के बराबर नहीं।

स्कूल का एक timetable लीजिए जिसका नियम यह है: अगर teacher कोई subject पढ़ा सकता है, teacher किसी class को दिया गया है, और वह class वह subject पढ़ती है, तो teacher उस class में वह subject पढ़ाता है।

तीनों जानकारियाँ अलग store कीजिए — teacher_subject, teacher_class और class_subject — और पूरा timetable तीनों को join करके दोबारा बन जाता है। सिर्फ़ दो को join करने पर एक नकली row बनती है (Sharma XII-A में Python पढ़ा रहे हैं, जबकि उस class में Python है ही नहीं), इसलिए यहाँ दो हिस्सों में बाँटना सुरक्षित नहीं। तीनों जोड़ी वाली tables बन जाने के बाद किसी class में subject जोड़ना timetable की कई rows की जगह एक insert है।

सच कहें तो 5NF वाले मामले रोज़ के projects में कम आते हैं। इसे exams और interviews के लिए जानिए, और ऐसे तीन-तरफ़ा संबंधों पर नज़र रखिए जो असल में किसी नियम से चलते हैं।

सभी normal forms एक नज़र में

MySQL में अंतिम design

यह रहा college register का 3NF रूप, असली MySQL code में। इसे editor में चलाइए, फिर आख़िर के पास वाला UPDATE देखिए: department का head ठीक एक row में बदलता है, और हर report उसे अपने-आप ले लेती है।

CREATE TABLE departments (
  dept      VARCHAR(30) PRIMARY KEY,
  dept_head VARCHAR(50) NOT NULL
);

CREATE TABLE students (
  roll INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  dept VARCHAR(30) NOT NULL,
  FOREIGN KEY (dept) REFERENCES departments(dept)
);

CREATE TABLE student_phone (
  roll  INT,
  phone VARCHAR(15),
  PRIMARY KEY (roll, phone),
  FOREIGN KEY (roll) REFERENCES students(roll)
);

CREATE TABLE courses (
  c_code CHAR(5) PRIMARY KEY,
  c_name VARCHAR(40) NOT NULL
);

CREATE TABLE enrolments (
  roll   INT,
  c_code CHAR(5),
  marks  TINYINT UNSIGNED,
  PRIMARY KEY (roll, c_code),
  FOREIGN KEY (roll)   REFERENCES students(roll),
  FOREIGN KEY (c_code) REFERENCES courses(c_code)
);

INSERT INTO departments VALUES
  ('Computer', 'Dr. Rao'), ('Science', 'Dr. Mehta'), ('Commerce', 'Dr. Khan');
INSERT INTO students VALUES
  (101, 'Aman', 'Computer'), (102, 'Priya', 'Computer'), (103, 'Rahul', 'Science');
INSERT INTO student_phone VALUES
  (101, '98765-43210'), (101, '91234-56780'), (102, '99887-76655'), (103, '90011-22334');
INSERT INTO courses VALUES
  ('CS101', 'DBMS'), ('MA101', 'Maths'), ('PH101', 'Physics');
INSERT INTO enrolments VALUES
  (101, 'CS101', 78), (101, 'MA101', 85), (102, 'CS101', 91), (103, 'PH101', 66);

-- JOIN से पुरानी चौड़ी report दोबारा बनाइए
SELECT s.roll, s.name, c.c_name, e.marks, d.dept, d.dept_head
FROM enrolments e
JOIN students    s ON s.roll   = e.roll
JOIN courses     c ON c.c_code = e.c_code
JOIN departments d ON d.dept   = s.dept
ORDER BY s.roll, c.c_code;

-- सिर्फ़ एक row का update: अब कोई update anomaly नहीं
UPDATE departments SET dept_head = 'Dr. R. Rao' WHERE dept = 'Computer';
SELECT s.name, d.dept_head FROM students s JOIN departments d ON d.dept = s.dept;

पहला SELECT साफ़ tables से मूल चौड़ी report फिर से बनाता है। उसका output:

UPDATE के बाद Aman और Priya दोनों के लिए "Dr. R. Rao" दिखता है — क्योंकि नाम सिर्फ़ एक जगह रहता है। इसकी क़ीमत report में तीन JOIN है, पर अब हर value सच के एक ही स्रोत से आती है। ऐसे और हल किए गए अभ्यास normalization examples में देखिए।

Denormalize कब करें (जान-बूझकर)

Normalization सही शुरुआती रास्ता है, पर कोई अटल नियम नहीं। ज़्यादा पढ़ने वाली जगहों पर — dashboards, reports, search pages, analytics — कई joins की लागत थोड़े दोहराव से ज़्यादा भारी पड़ सकती है। Denormalization का मतलब है पढ़ना तेज़ करने के लिए जान-बूझकर दोहराया हुआ column या सारांश table वापस जोड़ना।

• व्यस्त dashboard के लिए हर class की row में student_count रखिए, जिसे trigger या admission वाला code update करे।
• Principal की मासिक report के लिए हर रात normalized tables से एक सपाट reporting table बनाइए।
• Invoice की row पर ही कुल रक़म store कीजिए, ताकि हज़ार invoices की सूची दिखाने के लिए हज़ार item सूचियाँ न जोड़नी पड़ें।

एक चीज़ जो denormalization जैसी दिखती है पर है नहीं: product की क़ीमत order line में copy करना। वह अलग जानकारी है — बिक्री के समय की क़ीमत — और आज की क़ीमत बदलने पर उसे नहीं बदलना चाहिए।

सुनहरा नियम: पहले normalize कीजिए, फिर सिर्फ़ वहीं denormalize कीजिए जहाँ असली ज़रूरत मापी गई हो, और लिखकर रखिए कि सच का स्रोत कौन-सी copy है।

आम गलतियाँ

• Comma से अलग की गई सूचियाँ store करना। subjects = 'DBMS,Maths' जैसा column 1NF तोड़ता है। हर value की एक row वाली child table इस्तेमाल कीजिए।
• एक column वाली key में partial dependency ढूँढना। Partial dependency सिर्फ़ composite key में संभव है; ऐसी 1NF table पहले से 2NF में होती है।
• 2NF और 3NF में उलझना। दो अलग सवाल पूछिए: "क्या यह column key के सिर्फ़ हिस्से पर निर्भर है?" (2NF) और "क्या यह किसी दूसरे non-key column पर निर्भर है?" (3NF)।
• यह सोचना कि AUTO_INCREMENT id सब ठीक कर देगी। नई id जोड़ने से dept → dept_head नहीं हटता; दोहराव वहीं का वहीं रहता है।
• ऐसे बाँटना कि data खो जाए। Decompose करते समय साझा column कम से कम एक नई table की key होना चाहिए, वरना joins नकली rows बनाते हैं।
• बाँटने के बाद foreign keys भूल जाना। इनके बिना कोई enrolment ऐसे student की ओर इशारा कर सकता है जो अब है ही नहीं।
• सिर्फ़ normalize करने के लिए normalize करना। हर column को अलग table में डालने से सरल queries भी मुश्किल हो जाती हैं। जब तक कोई असली anomaly न बचे, 3NF या BCNF पर रुकिए।

अभ्यास प्रश्न (उत्तर सहित)

प्रश्न 1. order_items(order_id, book_id, book_title, qty, customer_name) की primary key (order_id, book_id) है। इसका सबसे ऊँचा normal form क्या है, और इसे कैसे ठीक करेंगे?

प्रश्न 2. employees(emp_id, name, pin_code, city) की primary key emp_id है। मान लीजिए हर PIN code एक ही शहर का है। सबसे ऊँचा normal form क्या है?

प्रश्न 3. Relation R(A, B, C) में dependencies AB → C और C → B हैं। Candidate keys और सबसे ऊँचा normal form बताइए।

प्रश्न 4. क्या हर BCNF table 3NF में होती है? क्या हर 3NF table BCNF में होती है?

प्रश्न 5. एक table उन students के लिए (roll, club, language) store करती है जो कई clubs में हैं और कई भाषाएँ बोलते हैं। यह कौन-सा normal form तोड़ती है, और सुधार क्या है?

सारांश

• Normalization हर जानकारी एक बार store करता है और tables को keys से जोड़ता है, जिससे update, insert और delete anomalies हटती हैं।
• 1NF: atomic values, कोई दोहराता समूह नहीं, हर row के लिए key।
• 2NF: कोई partial dependency नहीं — हर non-key column को पूरी composite key चाहिए।
• 3NF: कोई transitive dependency नहीं — non-key columns सिर्फ़ key पर निर्भर।
• BCNF: हर dependency का बायाँ हिस्सा super key हो।
• 4NF स्वतंत्र multi-valued जानकारी अलग करता है; 5NF join dependencies हटाता है।
• असली projects में 3NF या BCNF का लक्ष्य रखिए, फिर सिर्फ़ मापी गई वजह से denormalize कीजिए।

आगे, हल किए गए प्रश्नों से अभ्यास कीजिए normalization examples में, या theory और गहराई से समझिए functional dependency में।