Normalization in DBMS 

Normalization ek process hai jisme hum database ko design karte waqt usme se redundancy (data ka repetition) aur inconsistency (confusing ya galat data) hataate hain.

Normalization step-by-step hota hai using Normal Forms – jaise 1NF, 2NF, 3NF, BCNF, 4NF, and 5NF.

---

 1NF (First Normal Form)

Condition: Table ka har column atomic hona chahiye.
Matlab, har cell mein sirf ek value ho – na ki list ya group.

Example:

 Yeh table 1NF nahi hai kyunki “Courses” column mein multiple values hain.

 1NF version:

 2NF (Second Normal Form)

Condition:

1. Table 1NF mein hona chahiye

2. Har non-key column fully dependent hona chahiye entire primary key par, na ki uske part par.

Example (1NF but not 2NF):

Yahan StudentName sirf StudentID pe depend hai, na ki (StudentID + Course) ke combination par. Isiliye 2NF nahi hai.

 2NF version:

Student Table:

Course Table:

 3NF (Third Normal Form)

Condition:

1. Table 2NF mein hona chahiye

2. Non-key columns sirf primary key pe dependent hone chahiye, doosre non-key column pe nahi.

Example (2NF but not 3NF):

Yahan HOD dependent hai Department pe, not directly on StudentID → violation of 3NF.

 3NF version:

Student Table:

Department Table:

 BCNF (Boyce-Codd Normal Form)

Condition:
Table 3NF mein ho + har determinant super key hona chahiye.

Determinant: Jo kisi column ki value decide karta hai.

Example:

Assume ek teacher ek hi course padhata hai, lekin teacher bhi room decide karta hai.

Yahan Teacher → Course and Teacher → Room, but Teacher primary key nahi hai. Isiliye BCNF violated.

 Split into BCNF:

Teacher_Course Table:

Teacher_Room Table:

 4NF (Fourth Normal Form)

Condition:
No multi-valued dependency if table already in BCNF.

Multi-valued Dependency Example:

Yahan Rahul ke paas do courses aur do hobbies hain → toh combinations ban rahe hain unnecessarily.

 4NF version:

Student_Course Table:

Student_Hobby Table:

 5NF (Fifth Normal Form)

Condition:

4NF me hona chahiye

Table should be broken down to remove join dependencies. 5NF mainly tab use hoti hai jab data redundant ho due to complex joins.

Example:
Suppose ek product multiple vendors se aa raha hai and multiple customers ko jaa raha hai. To avoid unnecessary combinations, hum 5NF apply karte hain.

---

 Summary Table: