A Beginner&#s Guide to Database Normalization

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!