Q.1)

Q1) What is normalization in databases? Why is it important? Describe the normal forms (1NF, 2NF, 3NF, 4NF, and BCNF) with appropriate examples.

Normalization

1. Normalization ek systematic process hai jisme database ke data ko organize kiya jata hai taaki redundancy (duplicacy) eliminate ho aur data integrity ensure ho sake.

2. Yeh badi tables ko smaller, well-structured tables mein divide karta hai aur unke beech relationships establish karta hai.

3. Yeh ensure karta hai ki har table ek single concept ko represent kare, jisse insert, update aur delete operations ke dauran aane wali anomalies (errors) kam ho jayein.

4. Yeh repetitive data ko minimize karke aur logical data grouping ensure karke query efficiency ko improve karta hai.

5. Yeh consistency maintain karne mein madad karta hai by ensuring ki har attribute sirf key, puri key, aur kuch nahi balki sirf key par depend kare.

6. Yeh storage wastage ko reduce karta hai aur relational databases mein optimal performance ensure karta hai.

7. Yeh database design mein ek clear structure provide karta hai, jisse maintenance aasan ho jati hai.

8. Yeh anomalies ko remove karta hai jaise ki update anomaly, insert anomaly aur delete anomaly.

---

First Normal Form (1NF)

1. Ek relation 1NF mein tab hota hai jab saare attributes mein sirf atomic (indivisible) values hoti hain.

2. Isme repeating groups ya multivalued attributes allowed nahi hote.

3. Har column mein sirf ek single value store honi chahiye, na ki sets, arrays, ya lists.

4. Ek column ki saari entries ka data type same hona chahiye.

5. Attribute values atomic honi chahiye aur koi repeating groups present nahi hone chahiye.

   Note: Primary key hona ek good practice hai, par yeh 1NF ki formal definition ka hissa NAHI hai.

6. 1NF repeating groups ko eliminate karke higher normal forms ke liye foundation set karta hai.

7. Example: Multiple phone numbers ko ek hi cell mein store karne ki bajaye alag-alag rows mein split karna.

8. Yeh ensure karta hai ki data ek tabular, consistent format mein structured ho jo relational operations ke liye suitable ho.

---

Second Normal Form (2NF)

1. Ek relation 2NF mein tab hota hai jab wo pehle se 1NF mein ho.

2. Har non-key attribute puri primary key par fully functionally dependent hona chahiye.

3. Partial dependency ko remove karna zaroori hai, especially composite key tables mein.

4. Non-key attributes kisi candidate key ke ek part (hisse) par depend nahi kar sakte.

5. Yeh ensure karta hai ki har table ek single theme ko describe kare aur data repetition ko avoid kare.

6. Partial dependency eliminate karne ke liye tables ko naye relations (tables) mein split kiya jata hai.

7. Example: Ek table jisme StudentID, CourseName aur Instructor hain, usko alag-alag Student, Course aur Enrollment tables mein split karna.

8. Yeh update aur insert anomalies ko remove karta hai by ensuring ki non-key attributes sirf complete key par hi depend karein.

---

Third Normal Form (3NF)

1. Ek relation 3NF mein tab hota hai jab wo pehle se 2NF mein ho.

2. Attributes ke beech koi transitive dependency nahi honi chahiye.

3. Ek non-key attribute dusre non-key attribute par depend nahi karna chahiye.

4. Sirf primary key hi non-key attributes ko determine karni chahiye.

5. Yeh data ka logical separation ensure karta hai into stable, non-redundant structures.

6. Example: Ek table jisme StudentID, DepartmentName, aur HOD ho, usko do tables mein split karna jahan HOD DepartmentName par depend kare, na ki StudentID par.

7. Yeh us unnecessary duplication ko eliminate karta hai jo indirect relationships ki wajah se aati hai.

8. 3NF high integrity maintain karne, redundancy kam karne aur efficient updates ensure karne mein madad karta hai.

---

BCNF (Boyce-Codd Normal Form)

1. BCNF normalization ka ek advanced form hai jahan har functional dependency $X \rightarrow Y$ ke liye, determinant $X$ us relation ka ek super key hona chahiye.

2. Ise improper functional dependencies ki wajah se aane wali redundancy ko remove karne ke liye use kiya jata hai aur yeh ensure karta hai ki har attribute sirf ek key par depend kare.

3. BCNF Third Normal Form (3NF) se zyada strict hota hai kyunki yeh non-key attributes par partial dependency bhi allow nahi karta.

4. Agar koi relation BCNF ko violate karta hai, toh use smaller relations mein decompose (split) kiya jata hai taaki har relation BCNF condition ko satisfy kare.

5. BCNF better data consistency ensure karta hai aur update, insertion, aur deletion jaisi anomalies ko reduce karta hai.

6. Example: Agar ek table mein dependency Teacher $\rightarrow$ Subject hai aur Teacher ek super key nahi hai, toh yeh BCNF ko violate karta hai aur isko decompose karna padega.

---

4NF (Fourth Normal Form)

1. 4NF normalization ka ek higher level hai jo ek relation mein multi-valued dependencies ke saath deal karta hai.

2. Ek relation 4NF mein tab hota hai jab wo pehle se BCNF mein ho aur usme koi non-trivial multi-valued dependencies na ho.

3. Yeh tab use hota hai jab ek table mein do ya usse zyada independent multi-valued attributes hon, jo redundancy cause kar sakte hain.

4. 4NF achieve karne ke liye, aisi relations ko separate tables mein decompose kiya jata hai taaki har table sirf ek multi-valued dependency ko handle kare.

5. 4NF us redundancy ko remove karta hai jise BCNF handle nahi kar sakta, especially independent multi-valued data ke cases mein.

6. Example: Agar ek student ke paas multiple hobbies aur multiple skills independently hain, toh table ko STUDENT_HOBBY aur STUDENT_SKILL mein decompose kiya jata hai taaki 4NF satisfy ho sake.