Database Normalization

Normal Forms & Anomalies — Complete Guide

1NF · 2NF · 3NF · BCNF · Functional Dependencies

Yeh notes database normalization ke saare concepts cover karte hain — anomalies (insert / update /
delete), functional dependencies, aur 1NF se BCNF tak har normal form ko real examples ke saath.
CS619 viva ke liye complete reference.

Database Normalization — CS619 Study Notes

Database Normalization Page 2

1. Normalization kya hai?

Normalization ek database design technique hai jisme tables ko is tarah structure kiya jata hai ke:

• Data redundancy (duplication) khatam ho

• Data consistency maintain ho
• Insert, Update, Delete anomalies na aayein
• Storage efficient ho

Normalization process Normal Forms (NF) ke through hoti hai — har form ek set of rules hai jo table ko aur
behtar banati hai.

Normal Forms ka order:

Form Full Name Key Rule Anomaly Fixed

UNF Un-Normalized Form No rules — raw data None

1NF First Normal Form Atomic values + Primary Key Repeating groups

2NF Second Normal Form 1NF + No partial dependency Insert anomaly

3NF Third Normal Form 2NF + No transitive dependency Update / Delete anomaly

BCNF Boyce-Codd Normal Form Every determinant is a superkey Overlapping key issues

Note: Practical work ke liye 3NF kaafi hoti hai. BCNF sirf advanced / theoretical cases mein apply hoti hai.

CS619 Study Reference — NF Guide with Anomalies

Database Normalization Page 3

2. Anomalies — Problems in Un-normalized Tables

Jab data properly normalize nahi hota toh teen tarah ki anomalies aati hain. In sab ko samajhne ke liye ek
example table lein:

Un-normalized table: Student_Course

Std_ID Std_Name City Course_ID Course_Name Instructor

S01 Ali Lahore CS101 DBMS Dr. Khan

S01 Ali Lahore CS102 OS Dr. Shah

S02 Sara Karachi CS101 DBMS Dr. Khan

S03 Usman Islamabad CS103 Networking Dr. Ali

Insert Anomaly
Problem: Naya course CS104 add karna chahte hain lekin abhi koi student enroll nahi hua. Table mein
Std_ID compulsory hai toh course save hi nahi ho sakta jab tak koi student enroll na ho. Yeh Insert
Anomaly hai.

Fix: Course ko alag table mein rakh do — student pe depend mat karo.

Update Anomaly
Problem: Ali ka city 'Lahore' se 'Islamabad' change karna hai. Ali ki 2 rows hain (CS101, CS102). Agar sirf
ek row update ki aur doosri miss ho gayi toh data inconsistent ho jata hai — ek row mein Lahore, doosri
mein Islamabad. Yeh Update Anomaly hai.

Fix: Student info ek jagah ho — Student alag table mein.

Delete Anomaly
Problem: Usman (S03) ka enrollment delete karna chahte hain. Usman sirf ek course mein tha (CS103).
Agar Usman ki row delete ki toh CS103 'Networking' course ki saari information bhi gum ho jaati hai —
instructor Dr. Ali ka koi record nahi bachega. Yeh Delete Anomaly hai.

Fix: Course ko alag table mein rakh do taake student delete hone se course safe rahe.

CS619 Study Reference — NF Guide with Anomalies

Database Normalization Page 4

3. Functional Dependencies
Functional Dependency (FD) ka matlab hai: ek column ki value dusre column ki value determine karti hai.
Notation: A → B matlab 'A determines B'.

Type Notation Matlab NF Violation?

Full Dependency Std_ID, Course_ID → Grade Dono miltay hain toh Grade pata chaltaNo
hai— correct hai

Partial Dependency Std_ID → Std_Name Composite key ka sirf ek part Std_Name

Yes
determine
— 2NF violation
karta hai

Transitive Dependency Course_ID → Instructor → Phone Non-key se non-key determine hota haiYes — 3NF violation

Transitive Dependency chain visualize karo:

Course_ID → Instructor → Phone

(PK) (non-key) (non-key)

Phone directly Course_ID pe depend nahi karta — pehle Instructor pe, phir Instructor se Phone. Yeh transitive chain
3NF violation hai.

CS619 Study Reference — NF Guide with Anomalies

Database Normalization Page 5

4. 1NF — First Normal Form

Rule: Har cell mein sirf ek (atomic) value honi chahiye. Koi repeating groups nahi. Ek Primary Key honi
chahiye.

Problem — Multi-valued cell (1NF Violation):

Std_ID Std_Name Courses (BAD) Fees (BAD)

S01 Ali CS101, CS102 5000, 6000

S02 Sara CS101 5000

Problem: 'CS101, CS102' ek cell mein 2 values — atomic nahi!

Fix — After 1NF (ek row per course):

Std_ID Std_Name Course_ID Fee

S01 Ali CS101 5000

S01 Ali CS102 6000

S02 Sara CS101 5000

PK = (Std_ID + Course_ID) composite key

1NF Key Points:

• Koi cell mein comma-separated list nahi honi chahiye
• Koi repeating column groups nahi (jaise Course1, Course2, Course3 alag columns)
• Primary Key define karna compulsory hai
• Har row uniquely identifiable honi chahiye

CS619 Study Reference — NF Guide with Anomalies

Database Normalization Page 6

5. 2NF — Second Normal Form

Rule: 1NF satisfy karo + Koi Partial Dependency nahi honi chahiye. Har non-key attribute puri
composite key pe depend kare, sirf ek hisse pe nahi.

Partial Dependency — Problem:

Hamari 1NF table mein composite PK hai: (Std_ID, Course_ID). Ab dekhein kaunsa column kis pe
depend karta hai:

Column Depends on Std_ID? Depends on Course_ID? Problem?

Grade Partial (Yes) Partial (Yes) No — full dependency, theek hai

Std_Name Yes No Partial dep — 2NF VIOLATION

City Yes No Partial dep — 2NF VIOLATION

Course_Name No Yes Partial dep — 2NF VIOLATION

Instructor No Yes Partial dep — 2NF VIOLATION

Fix — 3 tables banao (2NF):

Student Table Enrollment Table Course Table

PK: Std_ID PK: Std_ID (FK) PK: Course_ID

→ FK
FK ←
Std_Name PK: Course_ID (FK) Course_Name

City Grade Instructor

Ab Std_Name sirf Student table mein hai — Course delete hone se student info safe.

CS619 Study Reference — NF Guide with Anomalies

Database Normalization Page 7

6. 3NF — Third Normal Form

Rule: 2NF satisfy karo + Koi Transitive Dependency nahi honi chahiye. Non-key column directly
Primary Key pe depend kare — doosre non-key column ke through nahi.

Transitive Dependency — Problem in Course table:

2NF ke baad hamari Course table hai: Course(Course_ID, Course_Name, Instructor,
Instructor_Phone)

Dependency Type Problem?

Course_ID → Course_Name Direct OK — no problem

Course_ID → Instructor Direct OK — no problem

Instructor → Instructor_Phone Direct non-key to non-key 3NF VIOLATION

Course_ID → Instructor → Phone Transitive chain 3NF VIOLATION

Fix — Instructor ko alag table mein le jao:

Course (updated) Instructor (new)

PK: Course_ID PK: Instructor_ID

FK →
Course_Name Instructor_Name

FK: Instructor_ID Instructor_Phone

Ab Phone directly Course_ID se reachable hai via FK — chain toot gayi, 3NF achieve!

Test for 3NF — koi bhi non-key column direct PK se hi determine hona
chahiye:
• Non-key column 'A' kisi doosre non-key column 'B' pe depend kare toh 3NF violation
• Sab non-key columns directly aur sirf PK pe functionally dependent honay chahiyein
• Agar violation mile toh 'B' ko alag table mein nikal do

CS619 Study Reference — NF Guide with Anomalies

Database Normalization Page 8

7. BCNF — Boyce-Codd Normal Form

Rule: 3NF satisfy karo + Har Functional Dependency X → Y mein, X ek superkey hona chahiye. (Yeh
3NF se stricter version hai.)

BCNF vs 3NF — Farq kya hai?

Condition 3NF BCNF

Non-key → Non-key dependency allowed?No No

Determinant must be superkey? Not always Always — strictly

Multiple overlapping candidate keys? May still pass 3NF Must satisfy extra check

How common? Very common scenario Rare edge cases

BCNF Example — Student_Course_Teacher:

Ek table hai jisme student, course, aur teacher ka record hai. Rules yeh hain:

• Ek student ek course mein sirf ek teacher se padhta hai → (Student, Course) → Teacher
• Ek teacher sirf ek course padhata hai → Teacher → Course

Student Course Teacher

Ali DBMS Dr. Khan

Ali OS Dr. Shah

Sara DBMS Dr. Malik

Problem: Teacher → Course yahan hold karta hai. Lekin 'Teacher' koi superkey nahi — ek teacher
multiple students ko padha sakta hai. Yeh BCNF violation hai (even though 3NF pass karta hai).

Fix — 2 tables:
Table 1: Student_Teacher Table 2: Teacher_Course

Student Teacher Teacher Course

Ali Dr. Khan Dr. Khan DBMS

Ali Dr. Shah Dr. Shah OS

Sara Dr. Malik Dr. Malik DBMS

CS619 Study Reference — NF Guide with Anomalies

Database Normalization Page 9

8. Quick Revision — Sab ek jagah

Anomalies — Short Summary:

Anomaly Kab hoti hai Real Example Fix

Insert Naya data tab tak save nahi jab CS104

tak koi related
course add
datanahi
available
ho sakta
na ho
jabCourse
tak student
ko alag
enroll
table
namein
ho rakh do

Update Ek cheez update karne pe multiple

Ali ka
rows
citychange
2 rowskarni
meinpadti
samehain
rakhnaStudent
padegainfo ek table mein centralize karo

Delete Ek record delete karne pe zaruriUsman

info bhidelete
gum hokaro
jatitoh
haiCS103 course
Course
info bhi
ko gum
alag table mein rakh do

Normal Forms — Quick Rules:

NF Single Line Rule What to do if violated

1NF Har cell atomic honi chahiye Multi-valued cell tod do — ek row per value

2NF No partial dependency on composite PK Non-key col jo sirf ek PK column pe depend kare — use alag table me

3NF No transitive dependency (non-key → non-key)Non-key determining non-key ko alag table mein tod do

BCNF Har FD X→Y mein X superkey hona chahiye Non-superkey determinant wali dependency ko alag table mein nikalo

Dependency Types — Cheat Sheet:

Dependency Type Notation Fix Required

Full (good) PK1, PK2 → NonKey None — sahi hai

Partial (2NF issue) PK1 → NonKey (not PK2) Alag table banao

Transitive (3NF issue) PK → NonKey1 → NonKey2 NonKey1 ko alag table mein nikalo

Practical Tip: Real-world databases ke liye 3NF kaafi hoti hai. Yeh 90% anomalies aur redundancy
problems solve kar deta hai. BCNF sirf theoretical ya bahut complex multi-key scenarios mein apply hoti
hai. CS619 viva mein 1NF → 2NF → 3NF ka flow samajhna aur anomalies explain karna sabse important
hai.

CS619 Study Reference — NF Guide with Anomalies