DEV Community

Kawan Idrees
Kawan Idrees

Posted on • Edited on

A Beginner's Guide to Database Normalization

#database #mysql #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
Enter fullscreen mode Exit fullscreen mode

After 1NF

Table: StudentCourses
----------------------------
StudentID | Name   | Courses
----------------------------
1         | Alice  | Math
1         | Alice  | Science
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

After 2NF:

Table: StudentCourses
----------------------------
StudentID | Course
----------------------------
1         | Math
Enter fullscreen mode Exit fullscreen mode 
Table: CourseInstructors
----------------------------
Course    | Instructor
----------------------------
Math      | Dr. Smith
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

After 3NF:

Table: StudentCourses
----------------------------
StudentID | Course
----------------------------
1         | Math
Enter fullscreen mode Exit fullscreen mode 
Table: CourseInstructors
----------------------------
Instructor | Dept
----------------------------
Dr. Smith  | Science
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

After BCNF:

Table: TeacherCourses
----------------------------
TeacherID | Course
----------------------------
1         | Math
Enter fullscreen mode Exit fullscreen mode 
Table: CourseDepartments
----------------------------
Course    | Dept
----------------------------
Math      | Science
Enter fullscreen mode Exit fullscreen mode

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

Example:

Before 4NF:

Table: StudentHobbies
----------------------------
StudentID | Course | Hobby
----------------------------
1         | Math   | Chess
Enter fullscreen mode Exit fullscreen mode

After 4NF:

Table: StudentCourses
----------------------------
StudentID | Course
----------------------------
1         | Math
Enter fullscreen mode Exit fullscreen mode 
Table: StudentHobbies
----------------------------
StudentID | Hobby
----------------------------
1         | Chess
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

After 5NF:

Table: EmployeeProjects
----------------------------
EmployeeID | Project
----------------------------
1          | A
Enter fullscreen mode Exit fullscreen mode 
Table: EmployeeRoles
----------------------------
EmployeeID | Role
----------------------------
1          | Developer
Enter fullscreen mode Exit fullscreen mode 
Table: ProjectRoles
----------------------------
Project | Role
----------------------------
A       | Developer
Enter fullscreen mode Exit fullscreen mode

Conclusion
normalization ensures that your database remains efficient, consistent, and scalable, which simplifies management and enhances query performance as your data grows.

Top comments (0)

Read next

animusna profile image

CouchDB: Offline-first multi-master synchronization using Docker and Docker-compose

Aniello Musella -

roxana_haidiner profile image

How to Document Your Database with a GUI Tool

Roxana Maria Haidiner -

zr33_lanor profile image

Recuperar una Base de Datos Oracle Después de un Error en el Archivo spfile.ora

Ronal Mejía -

luminousmen profile image

Locking Mechanisms in High-Load Systems

luminousmen -