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
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.
The above is the detailed content of A Beginner&#s Guide to Database Normalization. For more information, please follow other related articles on the PHP Chinese website!
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
