Notes for end sem #1423
Description
🌸 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:
- 1NF – First Normal Form
- 2NF – Second Normal Form
- 3NF – Third Normal Form
- 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.