4.4.21
Normalization = columns ko tables mein is tarah organize karna ki har fact sirf ek baar store ho . Jab ek fact do jagah rehta hai, database aapko galat information de sakta hai (ek copy update hui, doosri nahi). Har normal form ek specific tarah ki duplication hatata hai jo ek specific tarah ka bug (anomaly) create karta hai.
Anomaly KYA hota hai? Ek aisi situation jahan table ki structure hi kharab behaviour force karti hai:
Insert anomaly — aap fact A record nahi kar sakte jab tak aapko unrelated fact B bhi pata na ho.Update anomaly — ek real-world fact change karne ke liye aapko kai rows edit karni padti hain; ek chhoot gayi → inconsistency.Delete anomaly — ek row delete karne se ek unrelated fact bhi accidentally destroy ho jaata hai.
YE KYUN hota hai? Kyunki humne do independent facts ko ek row mein thuns diya . Fix hamesha same recipe hai: us dependency ko dhundho jo belong nahi karti, aur use apni table mein split karo. Normal forms bas batate hain ki kaun si dependency offender hai.
Definition Functional dependency (FD)X → Y X Y X Y X determinant hai.
Candidate key K K → K Prime attribute : ek aisa attribute jo kisi candidate key ka part ho.Non-prime attribute : kisi bhi candidate key ka part nahi.
Table ke FDs aap KAISE padhte hain? Real-world rules list karo ("ek student ka ek hi advisor hota hai", "ek advisor ek hi department mein belong karta hai") — wahi hain FDs.
Form
Forbids (offending FD X → A
Seedha matlab
1NF
non-atomic / repeating values
har cell mein ek hi value
2NF
A X proper part koi partial dependency nahi
3NF
X aur A koi transitive dependency nahi
BCNF
X har determinant ek superkey hai
Intuition Progression ek tightening screw ki tarah hai2NF: "key ke part par depend mat karo." 3NF: "kisi non-key par bhi depend mat karo, jab tak jo tum de rahe ho wo prime na ho." BCNF: "jab tak hata do — har determinant superkey hona chahiye." Har form strictly stronger hai: BCNF ⊂ 3NF ⊂ 2NF ⊂ 1NF .
Ek relation 1NF mein hai agar har attribute value atomic ho (koi lists nahi, koi nested tables nahi, koi repeating groups nahi).
Worked example Violation → fixStudent(name, phones="9991,9992") — phones ek list hai.
Problem kyun? Aap cleanly query nahi kar sakte "9992 kis ka hai?", usse index nahi kar sakte, ek number update nahi kar sakte.
Fix: StudentPhone(student, phone) — ek row mein ek phone.
YE sirf composite keys pe KYUN aata hai: partial dependency ke liye ek ≥2 columns wali key chahiye taki ek "part" exist kare. Agar aapki key single column hai, aap automatically 2NF mein hain.
Worked example Classic partial dependencyEnroll(StudentID, CourseID, Grade, StudentName)
Key = {StudentID, CourseID}. Lekin StudentID → StudentName — name key ke part par depend karta hai.
Ye step (part-key FD dhundho) KYUN? Kyunki StudentName har us course ke liye repeat hota hai jo student leta hai → update anomaly (student rename karo = kai rows edit karo), delete anomaly (last enrollment drop karo = naam gayab).
Fix: Student(StudentID, StudentName) split karo, Enroll(StudentID, CourseID, Grade) rakhho.
2NF mein ho aur har FD X → A A X ya A
Worked example Transitive dependencyEmp(EmpID, DeptID, DeptCity), key = EmpID.
FDs: EmpID → DeptID → DeptCity. To EmpID → DeptCity transitively, ek non-key middleman DeptID ke through.
Problem kyun? Ek dept ke har employee ka DeptCity repeat hota hai → dept move karo = kai rows update karo; sirf ek employee ke jaane se dept ki city delete ho jaati hai.
Fix: Emp(EmpID, DeptID) + Dept(DeptID, DeptCity).
Definition BCNF (Boyce–Codd NF)Har non-trivial FD X → A X superkey hona chahiye. Koi exception nahi, koi "jab tak prime ho" nahi.
3NF se stronger KYUN hai: 3NF allow karta hai ek aisa determinant jo superkey nahi hai, jab tak dependent attribute prime ho. BCNF us loophole ko hata deta hai.
Worked example 3NF mein hai lekin BCNF mein NAHITeach(Student, Subject, Teacher) with rules:
har (Student, Subject) → ek Teacher
har Teacher exactly ek Subject padhata hai, yaani Teacher → Subject.
Candidate keys: {Student, Subject} aur {Student, Teacher}. To Subject aur Teacher dono prime hain.
FD Teacher → Subject BCNF violate karta hai (Teacher superkey nahi hai) lekin 3NF satisfy karta hai (kyunki Subject prime hai — loophole!).
Anomaly: "Teacher X, Subject Y padhata hai" tab tak record nahi kar sakte jab tak koi student use le na le (insert anomaly).
Fix: TeacherSubject(Teacher, Subject) + StudentTeacher(Student, Teacher).
Caveat: BCNF decomposition dependency preservation kho sakta hai — kabhi kabhi 3NF hi sabse zyada hai jo hum saare FDs preserve karte hue rakh sakte hain.
Common mistake Steel-manned misconceptions"Higher normal form hamesha better hota hai, death tak normalize karo."
Ye sahi kyun lagta hai: kam redundancy = kam bugs, strictly achha lagtaa hai.
Fix: Over-normalizing bahut saare JOINs force karta hai (slow reads) aur BCNF dependency preservation tod sakta hai. Real systems kabhi kabhi performance ke liye denormalize karte hain. 3NF usually sweet-spot hota hai.
"2NF single-column keys ke baare mein hai."
Ye sahi kyun lagta hai: aapne composite keys par partial dependency dekhi.
Fix: 2NF violate hone ke liye composite key chahiye hoti hai; single-attribute-key relation automatically 2NF mein hota hai. Rule key ke proper subsets ke baare mein hai, table ke column count ke baare mein nahi.
"Agar table mein duplicate-looking data nahi hai to wo BCNF mein hai."
Ye sahi kyun lagta hai: normalization "redundancy ke baare mein hai."
Fix: Ye dependencies ke baare mein hai, aankhon se dekhi duplication ke nahi. FDs check karo: kya har determinant superkey hai?
"3NF implies BCNF."
Ye sahi kyun lagta hai: dono non-key determinants forbid karte hain.
Fix: 3NF ka "ya A Teacher → Subject ko survive karne deta hai. BCNF nahi karne deta.
Recall Feynman: ek 12-saal ke bacche ko explain karoSocho ek notebook jisme, har baar jab koi dost milne aata hai, aap naye line par uska poora phone number, ghar ka address, aur birthday likhte ho. Agar aapka dost ghar badal le, to aapko har line dhundhni hogi aur address fix karna hoga — ek chhoot gayi aur ab aapki notebook khud se disagree karti hai. Normalization kehta hai: har fact apni chhoti table mein sirf ek baar likho. Doston ke addresses ek "Address book" mein rahein, visits ek "Visit log" mein jo sirf naam se friend ko point kare. Address ek jagah change karo — ho gaya. Har normal form (1, 2, 3, BCNF) bas ek stricter rule hai ki "kya ye fact sahi book mein likha hai?"
"1 Atom, 2 Parts, 3 Trans, BCNF Boss."
1NF = Atom ic cells · 2NF = koi Part ial dependency nahi · 3NF = koi Trans itive dependency nahi · BCNF = har determinant Boss hai (ek superkey).
Aur yeh bhi: "The Key, the Whole Key, and Nothing but the Key (so help me Codd)" = 1NF→key, 2NF→whole key, 3NF/BCNF→nothing but the key.
X → Y Agar do rows X Y X Y
Prime attribute kya hota hai? Ek aisa attribute jo kam se kam ek candidate key mein belong karta ho.
1NF kya require karta hai? Har attribute value atomic ho — koi repeating groups, lists, ya nested relations nahi.
Single-attribute candidate key wala relation automatically kis form mein hota hai? 2NF (partial dependency ke liye composite key ka proper subset chahiye hota hai).
Partial dependency define karo. Ek non-prime attribute ka kisi candidate key ke proper subset par depend karna (2NF violate karta hai).
Transitive dependency define karo. Ek non-prime attribute ka kisi doosre non-prime attribute par depend karna (3NF violate karta hai).
FD X → A A X A
BCNF condition batao. Har non-trivial FD X → A X
3NF ⊄ BCNF KYUN ho sakta hai? 3NF ek non-superkey determinant allow karta hai agar dependent attribute prime ho; BCNF forbid karta hai.
Dependency preservation kaun si form sacrifice kar sakti hai? BCNF — iski decomposition saare FDs nahi rakh sakti; 3NF ko hamesha dependency-preserving banaya ja sakta hai.
Normalization jin teen anomaly types ko fix karta hai unke naam batao. Insert, Update (modification), aur Delete anomalies.
Teach(Student,Subject,Teacher) mein Teacher→Subject ke saath ye 3NF mein kyun hai lekin BCNF mein nahi?Subject prime hai (3NF loophole satisfy hua), lekin Teacher superkey nahi hai (BCNF violate hua).
Forms ka inclusion ordering kya hai? BCNF ⊂ 3NF ⊂ 2NF ⊂ 1NF (BCNF sabse strict hai).
Functional Dependencies — har normal form ke neeche ka algebra.Candidate Keys and Superkeys — 2NF/3NF/BCNF test karne ke liye zaroori.Decomposition — Lossless Join and Dependency Preservation — split ki quality .Denormalization for Performance — jaanboojhkar ulta trade-off.ER Modeling — achhe ER designs aksar already 3NF mein hote hain.Indexes and Joins — over-normalizing read speed kyun hurt kar sakta hai.
every determinant a superkey
Duplicated facts in one row
Functional dependency X to Y
