This content originally appeared on DEV Community and was authored by Kawan Idrees
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.
This content originally appeared on DEV Community and was authored by Kawan Idrees
Kawan Idrees | Sciencx (2024-08-30T14:16:57+00:00) A Beginner’s Guide to Database Normalization. Retrieved from https://www.scien.cx/2024/08/30/a-beginners-guide-to-database-normalization/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.