Database Normalization Tutorial
Database normalization is a process used to organize a database into tables and columns. The main goal is to reduce data redundancy and improve data integrity. Normalization involves dividing large tables into smaller ones and defining relationships between them.
First Normal Form (1NF)
A table is in 1NF if it contains only atomic (indivisible) values and each column contains values of a single type.
Example: A table with multiple phone numbers in one column violates 1NF.
To convert to 1NF, separate phone numbers into different rows or columns.
Second Normal Form (2NF)
A table is in 2NF if it is in 1NF and all non-key attributes are fully functionally dependent on the primary key.
Example: If a table has a composite key, no attribute should depend on only part of the key.
To convert to 2NF, move partial dependencies to a separate table.
Boyce-Codd Normal Form (BCNF)
A table is in BCNF if it is in 3NF and every determinant is a candidate key.
BCNF handles certain types of anomalies not addressed by 3NF.
Benefits of Normalization
– Reduces data redundancy
– Improves data integrity
– Makes the database more efficient
– Simplifies queries and updates
Expanded: First Normal Form (1NF)
Example Table (Before 1NF):
Student ID | Name | Phone Numbers |
1 | Alice | 123-456, 789-012 |
Example Table (After 1NF):
Student ID | Name | Phone Numbers |
1 | Alice | 123-456 |
1 | Alice | 789-012 |
Explanation: The phone numbers are separated into individual rows to ensure atomicity.
Expanded: Second Normal Form (2NF)
Example Table (Before 2NF):
| Student ID | CourseID | Student Name | Course Name |
| 1 | 101 | Alice | Math |
Example Table (After 2NF):
Students table:
| Student ID | StudentName |
| 1 | Alice |
Courses Table:
| CourseID | CourseName |
| 101 | Math |
Explanation: StudentName and CourseName are moved to separate tables to eliminate partial dependency.
Expanded: Third Normal Form (3NF)
Example Table (Before 3NF):
EmployeeID | Name | Department | DeptLocation |
1 | John | HR | Building A |
Example Table (After 3NF):
Employees Table:
EmployeeID | Name | Department |
1 | John | HR |
Departments Table:
Department | DeptLocation |
HR | Building A |
Explanation: DeptLocation depends on Department, not EmployeeID, so it’s moved to a separate table.
Expanded: Boyce-Codd Normal Form (BCNF)
Example Table (Before BCNF):
Professor | Subject | Department |
Smith | Math | Science |
Explanation: If a professor can teach multiple subjects but each subject belongs to one department, then Subject → Department is a dependency that violates BCNF.
Normalization Forms Comparison
Form | Requirements | Eliminates | Example Issue |
1NF | Atomic values, unique columns | Repeating groups | Multiple phone numbers in one field |
2NF | 1NF + full functional dependency | Partial dependency | Attributes depending on part of composite key |
3NF | 2NF + no transitive dependency | Transitive dependency | Non-key attribute depending on another non-key |
BCNF | 3NF + every determinant is a candidate key | Anomalies from non-candidate key dependencies | Subject → Department |