A Beginner&#s Guide to Database Normalization-Mysql Tutorial-php.cn

A Beginner

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

Copy after login

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

Copy after login

After 2NF:

Table: StudentCourses ---------------------------- StudentID | Course ---------------------------- 1 | Math

Copy after login

Table: CourseInstructors ---------------------------- Course | Instructor ---------------------------- Math | Dr. Smith

Copy after login

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

Copy after login

After 3NF:

Table: StudentCourses ---------------------------- StudentID | Course ---------------------------- 1 | Math

Copy after login

Table: CourseInstructors ---------------------------- Instructor | Dept ---------------------------- Dr. Smith | Science

Copy after login

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

Copy after login

After BCNF:

Table: TeacherCourses ---------------------------- TeacherID | Course ---------------------------- 1 | Math

Copy after login

Table: CourseDepartments ---------------------------- Course | Dept ---------------------------- Math | Science

Copy after login

5. Fourth Normal Form (4NF)
Objective: Eliminate multi-valued dependencies.

Example:

Before 4NF:

Table: StudentHobbies ---------------------------- StudentID | Course | Hobby ---------------------------- 1 | Math | Chess

Copy after login

After 4NF:

Table: StudentCourses ---------------------------- StudentID | Course ---------------------------- 1 | Math

Copy after login

Table: StudentHobbies ---------------------------- StudentID | Hobby ---------------------------- 1 | Chess

Copy after login

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

Copy after login

After 5NF:

Table: EmployeeProjects ---------------------------- EmployeeID | Project ---------------------------- 1 | A

Copy after login

Table: EmployeeRoles ---------------------------- EmployeeID | Role ---------------------------- 1 | Developer

Copy after login

Table: ProjectRoles ---------------------------- Project | Role ---------------------------- A | Developer

Copy after login

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!