Home >Database >Mysql Tutorial >A Beginner&#s Guide to Database Normalization
A Beginner&#s Guide to Database Normalization
- WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOriginal
- 2024-08-31 06:30:361231browse
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!