Database normalization: is the process of organizing data in a database to reduce redundancy and improve data integrity. Hereโs a quick overview of the key normal forms, with examples.
1. First Normal Form (1NF)
Objective: Ensure each column contains atomic values and that each record is unique.
Example:
Before 1NF:
Table: StudentCourses
----------------------------
StudentID | Name | Courses
----------------------------
1 | Alice | Math, Science
After 1NF
Table: StudentCourses
----------------------------
StudentID | Name | Courses
----------------------------
1 | Alice | Math
1 | Alice | Science
2. Second Normal Form (2NF)
Objective: Eliminate partial dependencies; every non-key attribute should depend on the entire primary key.
Example:
Before 2NF:
Table: StudentCourses
----------------------------
StudentID | Course | Instructor
----------------------------
1 | Math | Dr. Smith
After 2NF:
Table: StudentCourses
----------------------------
StudentID | Course
----------------------------
1 | Math
Table: CourseInstructors
----------------------------
Course | Instructor
----------------------------
Math | Dr. Smith
3. Third Normal Form (3NF)
Objective: Remove transitive dependencies; non-key attributes should only depend on the primary key.
Example:
Before 3NF:
Table: StudentCourses
-----------------------------------
StudentID | Course | Instructor | Dept
-----------------------------------
1 | Math | Dr. Smith | Science
After 3NF:
Table: StudentCourses
----------------------------
StudentID | Course
----------------------------
1 | Math
Table: CourseInstructors
----------------------------
Instructor | Dept
----------------------------
Dr. Smith | Science
4. Boyce-Codd Normal Form (BCNF)
Objective: A stricter version of 3NF to handle anomalies.
Example:
Before BCNF:
Table: TeacherCourses
------------------------------
TeacherID | Course | Dept
------------------------------
1 | Math | Science
After BCNF:
Table: TeacherCourses
----------------------------
TeacherID | Course
----------------------------
1 | Math
Table: CourseDepartments
----------------------------
Course | Dept
----------------------------
Math | Science
5. Fourth Normal Form (4NF)
Objective: Eliminate multi-valued dependencies.
Example:
Before 4NF:
Table: StudentHobbies
----------------------------
StudentID | Course | Hobby
----------------------------
1 | Math | Chess
After 4NF:
Table: StudentCourses
----------------------------
StudentID | Course
----------------------------
1 | Math
Table: StudentHobbies
----------------------------
StudentID | Hobby
----------------------------
1 | Chess
6. Fifth Normal Form (5NF)
Objective: Handle complex join dependencies; further decompose tables without losing information.
Example:
Before 5NF:
Table: ProjectAssignments
---------------------------------
EmployeeID | Project | Role
---------------------------------
1 | A | Developer
After 5NF:
Table: EmployeeProjects
----------------------------
EmployeeID | Project
----------------------------
1 | A
Table: EmployeeRoles
----------------------------
EmployeeID | Role
----------------------------
1 | Developer
Table: ProjectRoles
----------------------------
Project | Role
----------------------------
A | Developer
Conclusion
normalization ensures that your database remains efficient, consistent, and scalable, which simplifies management and enhances query performance as your data grows.
Top comments (0)