🌸 Normalization in DBMS (BCA 1st Year)

🌿 What is Normalization?

Definition:
Normalization is a process of organizing data in a database to reduce redundancy (duplicate data) and improve data integrity (accuracy and consistency).

It means dividing large, unorganized tables into smaller, logical tables — so data is stored efficiently.

💡 Why Normalization is Needed

Problem (Before Normalization)	Solution (After Normalization)
Data repeated many times	Data repetition reduced
Difficult to update data	Easier to update/delete/insert
Database becomes confusing	Clear and efficient structure
Inconsistent data	Data remains accurate and consistent

⚙️ Important Points

Normalization is done using normal forms (NF).
Each normal form has rules.
Main normal forms are:
1. 1NF – First Normal Form
2. 2NF – Second Normal Form
3. 3NF – Third Normal Form
4. BCNF – Boyce-Codd Normal Form

🧩 Types of Normalization (With Examples)

🥇 1NF – First Normal Form

📘 Rule:

Each cell must contain only one value.
Each record should be unique.

❌ Before 1NF:

| Student_ID | Student_Name | Subjects        |
|-------------|---------------|----------------|
| 1           | Riya          | DBMS, C, Java  |
| 2           | Sanjay        | DBMS, Python   |

After 1nf

| Student_ID | Student_Name | Subject |
|-------------|---------------|----------|
| 1           | Riya          | DBMS    |
| 1           | Riya          | C       |
| 1           | Riya          | Java    |
| 2           | Sanjay        | DBMS    |
| 2           | Sanjay        | Python  |


🥈 2NF – Second Normal Form

📘 Rule:
	•	Table must be in 1NF.
	•	No partial dependency (non-key columns must depend on the whole primary key).

❌ Before 2NF:

| Student_ID | Subject | Student_Name | Subject_Fee |
|-------------|----------|---------------|--------------|
| 1           | DBMS     | Riya          | 500          |
| 1           | C        | Riya          | 400          |
| 2           | Python   | Sanjay        | 600          |

Problem:
	•	Student_Name depends only on Student_ID.
	•	Subject_Fee depends only on Subject.
→ Partial dependency exists.

✅ After 2NF:

Table 1: Student

| Student_ID | Student_Name |
|-------------|---------------|
| 1           | Riya          |
| 2           | Sanjay        |


Table 2 subject 

| Subject | Subject_Fee |
|----------|--------------|
| DBMS     | 500          |
| C        | 400          |
| Python   | 600          |

Non-key columns depend on the whole key — Table is in 2NF.


🥉 3NF – Third Normal Form

📘 Rule:
	•	Table must be in 2NF.
	•	No transitive dependency (a non-key column should not depend on another non-key column).

❌ Before 3NF:

| Student_ID | Student_Name | City   | Pin_Code |
|-------------|---------------|--------|-----------|
| 1           | Riya          | Pune   | 411001    |
| 2           | Sanjay        | Delhi  | 110001    |




Problem:
	•	City depends on Pin_Code, not on Student_ID.
→ Transitive dependency exists.

✅ After 3NF:

Table 1: Student

| Student_ID | Student_Name | Pin_Code |
|-------------|---------------|-----------|
| 1           | Riya          | 411001    |
| 2           | Sanjay        | 110001    |


Table 2 city

| Pin_Code | City   |
|-----------|--------|
| 411001    | Pune   |
| 110001    | Delhi  |

No transitive dependency — Table is in 3NF.