Lesson 8 � Intermediate
DBMS: Normalization
Normalization database design ka technique hai jo redundancy (duplicate data) ko eliminate karta hai aur data consistency maintain karta hai.
Normalization hota kya hai?
WHAT
Normalization ek process hai jisme hum large tables ko chhote-chhote logical tables mein divide karte hain taaki: 1. Data redundancy (duplicate) kam ho 2. Insert, Update, Delete anomalies na aayein 3. Data consistency maintain ho
WHEN
Database design karte waqt. Jab table mein bahut zyada duplicate data ho, jab update anomalies aayein (ek jagah change kiya, doosri mein nahi), jab inconsistent data ho.
WHERE
Har relational database � MySQL, PostgreSQL, Oracle. Normalization 1NF se BCNF tak jaata hai. Most systems 3NF tak jaate hain.
Anomalies (Problems)
# Unnormalized Table:
StudentCourse(StudentID, Name, Course, Instructor, InstructorPhone)
# 1. Insertion Anomaly
✓ Naya student add karna ho but course nahi hai � insert nahi kar paate
✓ Student bina course ke exist nahi kar sakta
# 2. Update Anomaly
✓ Instructor ka phone change karna ho � sab rows mein change karo
✓ Ek row miss ho gaye = INCONSISTENT DATA!
# 3. Deletion Anomaly
✓ Student drop ho gaya � course bhi delete ho jaayega
✓ Sirf student ka data jaana tha, course bhi gaya
# Solution: NORMALIZATION
✓ Tables ko alag karo based on functional dependencies
✓ Har table mein sirf ek entity ka data hoNormal Forms
# 1NF (First Normal Form)
✓ Har column mein atomic (single) value ho
✓ No repeating groups, no multi-valued columns
BAD: StudentID | Courses
1 | Math, Science, English (multiple values!)
GOOD: StudentID | Course
1 | Math
1 | Science
1 | English
# Rule: No cell mein multiple values. Har value separately store karo.
# 2NF (Second Normal Form)
? 1NF + No partial dependency
✓ Non-key column poore primary key pe depend kare
BAD: StudentID | CourseID | StudentName | CourseName
(StudentName sirf StudentID pe depend � partial!)
GOOD: Students(StudentID, StudentName)
Courses(CourseID, CourseName)
Enrollments(StudentID, CourseID)
# Rule: Agar composite primary key hai, toh non-key columns puri key pe depend karein.
# 3NF (Third Normal Form)
? 2NF + No transitive dependency
✓ Non-key column doosre non-key pe depend na kare
BAD: StudentID | Name | DeptID | DeptName
(DeptName ✓ DeptID ✓ StudentID � transitive!)
GOOD: Students(StudentID, Name, DeptID)
Departments(DeptID, DeptName)
# Rule: Non-key column ✓ Non-key column dependency nahi honi chahiye.
# BCNF (Boyce-Codd Normal Form)
? 3NF + Har determinant candidate key ho
✓ Stricter version of 3NFDenormalization
# Denormalization = Normalization ka reverse
✓ Chhote tables ko wapas bada table mein merge karo
✓ Read performance ke liye (fewer JOINs)
# When to Denormalize:
✓ Read-heavy systems (analytics, reporting)
✓ Complex JOINs slow ho rahe hain
✓ Real-time queries mein low latency chahiye
# Trade-off:
✓ Faster reads (no JOINs needed)
✓ Slower writes (duplicate data update)
✓ More storage (redundancy wapas aa gayi)
# Real World:
- OLTP (Transaction): 3NF/BCNF (write-optimized)
- OLAP (Analytics): Denormalized (read-optimized)
- Most production: Balance between normalized + denormalized
# Hybrid Approach:
✓ Normalize for transaction tables
✓ Denormalize for reporting/analytics tables
✓ Use materialized views for pre-computed resultsExercise
Question: 1NF ka main rule kya hai✓ Har column mein kya hona chahiye? (2 words)
Question: Normalized database mein kaunsi anomaly solve hoti hai jab instructor ka phone change karna ho? (1 word)
Common mistakes
- Over-normalization: Bahut zyada tables banana � JOINs complex ho jaate hain. 3NF tak sufficient hai mostly.
- Denormalize blindly: Performance ke liye denormalize karna samajh lo. Write performance bhi impact hota hai.
- Functional dependencies skip: Normalization FDs pe based hai. FDs identify karna pehle seekho.
- BCNF zaroori sochna: Most systems 3NF sufficient hai. BCNF sirf specific edge cases mein zaroori hai.
Normalization samajh aa gayi✓ Ab Locks & Concurrency Control seekhte hain � concurrent transactions kaise handle hoti hain.