Normalization is a database design technique used to organize data in a relational database efficiently. The main goals of normalization are to:
- Eliminate redundancy – avoid storing the same data multiple times.
- Ensure data integrity – prevent anomalies during insert, update, or delete operations.
- Simplify maintenance – make it easier to manage the database.
Normalization is achieved by dividing a database into multiple tables and defining relationships between them.
Normal Forms (NF)
Normalization is done in stages, called Normal Forms. Each normal form has specific rules:
1. First Normal Form (1NF)
Rule: Each column must contain atomic (indivisible) values, and each record must be unique.
- No repeating groups or arrays in a column.
- Primary key is defined to identify rows uniquely.
Example:
| StudentID | Name | Courses |
|---|---|---|
| 1 | Alice | Math, Science |
Not in 1NF → Courses has multiple values.
1NF Version:
| StudentID | Name | Course |
|---|---|---|
| 1 | Alice | Math |
| 1 | Alice | Science |
2. Second Normal Form (2NF)
Rule: Must be in 1NF and every non-key attribute must be fully functionally dependent on the primary key.
- No partial dependency (a column should not depend only on part of a composite key).
Example:
| StudentID | Course | Instructor |
|---|---|---|
| 1 | Math | Mr. A |
| 1 | Science | Mr. B |
If primary key is (StudentID, Course), then Instructor depends only on Course, not the full key → violates 2NF.
2NF Version:
- Student Table:
StudentID, Name - Course Table:
Course, Instructor - Enrollment Table:
StudentID, Course
3. Third Normal Form (3NF)
Rule: Must be in 2NF and there should be no transitive dependency.
- Non-key attributes should depend only on the primary key, not on other non-key attributes.
Example:
| StudentID | Name | Department | DeptHead |
|---|
DeptHead depends on Department, not StudentID → violates 3NF.
3NF Version:
- Student Table:
StudentID, Name, DepartmentID - Department Table:
DepartmentID, Department, DeptHead
4. Boyce-Codd Normal Form (BCNF)
Rule: A stricter version of 3NF. Every determinant must be a candidate key.
- Handles some special cases where 3NF fails.
- Ensures no anomalies even if a table has multiple candidate keys.
Example:
| Course | Instructor | Room |
|---|
If Instructor can teach only one Course but a Room can have multiple Courses, BCNF ensures table is decomposed properly to avoid conflicts.
5. Fourth Normal Form (4NF)
Rule: Must be in BCNF and have no multi-valued dependencies.
- One attribute should not determine multiple independent attributes.
Example:
| StudentID | Hobby | Language |
|---|
If a student can have multiple hobbies and languages, this creates multi-valued dependency.
4NF Version: Separate tables for hobbies and languages.
6. Fifth Normal Form (5NF)
Rule: Must be in 4NF and decomposed so that no join dependency exists.
- Ensures table can’t be further decomposed without losing information.
- Mostly theoretical; rarely used in practical applications.
✅ Summary Table:
| Normal Form | Requirement |
|---|---|
| 1NF | Atomic values, unique rows |
| 2NF | 1NF + no partial dependency |
| 3NF | 2NF + no transitive dependency |
| BCNF | Every determinant is a candidate key |
| 4NF | BCNF + no multi-valued dependency |
| 5NF | 4NF + no join dependency |