Normalization in DBMS (1NF to 4NF & BCNF)
What is Normalization?
Normalization is the process of organizing data in a database to eliminate redundancy, avoid anomalies
(insertion, deletion, update), and ensure data integrity.
Unnormalized Table
Example:
| StudentID | Name | Course | Instructor |
|-----------|----------|----------------|----------------|
|1 | Adhithya | DBMS, Networks | Smith, John |
|2 | Hari | Java | Alan |
1NF (First Normal Form)
- Rule: Atomic values only, no repeating groups.
- Fix: Split multivalued fields into separate rows.
Result:
| StudentID | Name | Course | Instructor |
|-----------|----------|----------|------------|
|1 | Adhithya | DBMS | Smith |
|1 | Adhithya | Networks | John |
|2 | Hari | Java | Alan |
2NF (Second Normal Form)
- Rule: 1NF + no partial dependency.
- Fix: Separate data based on full key dependencies.
Result:
Student Table:
| StudentID | Name |
|-----------|----------|
|1 | Adhithya |
Normalization in DBMS (1NF to 4NF & BCNF)
|2 | Hari |
Enrollment Table:
| StudentID | Course | Instructor |
|-----------|----------|------------|
|1 | DBMS | Smith |
3NF (Third Normal Form)
- Rule: 2NF + no transitive dependency.
- Fix: Move indirect dependencies to new tables.
Result:
Course Table:
| Course | Instructor |
|----------|------------|
| DBMS | Smith |
Instructor Table:
| Instructor | CourseRoom |
|------------|------------|
| Smith | A1 |
BCNF (Boyce-Codd Normal Form)
- Rule: Every determinant should be a super key.
- Fix: Ensure all dependencies are on super keys.
Result:
Course Table:
| Course | Instructor |
|----------|------------|
| DBMS | Smith |
Instructor Table:
| Instructor | Expertise |
Normalization in DBMS (1NF to 4NF & BCNF)
|------------|------------|
| Smith | Databases |
4NF (Fourth Normal Form)
- Rule: BCNF + no multi-valued dependencies.
- Fix: Separate independent multivalued attributes.
Result:
StudentSkills:
| StudentID | Skill |
|-----------|--------|
|1 | Python |
|1 | Java |
StudentClubs:
| StudentID | Club |
|-----------|----------|
|1 | Robotics |
|1 | Drama |
Summary Table
| Normal Form | Rule Description |
|-------------|------------------------------------------|
| 1NF | Atomic values only |
| 2NF | No partial dependency |
| 3NF | No transitive dependency |
| BCNF | Every determinant is a super key |
| 4NF | No multi-valued dependency |
