What is normalization? Explain the normalization process with examples.

 Normalization is a process used in database design to organize and structure data in a relational database efficiently. The goal of normalization is to minimize data redundancy and dependency by breaking down large tables into smaller, related tables and establishing relationships between them.

The normalization process typically involves several normal forms (NF), each addressing specific types of data redundancy and dependency. Let's explain the normalization process using an example:

Consider a hypothetical database for a company's employees. We have a single table called "Employees" with the following attributes:

• Employee_ID (Primary Key)

• First_Name

• Last_Name

• Department

• Supervisor_ID

• Ensure each attribute contains atomic values (indivisible).

• Eliminate repeating groups and multi-valued attributes.

We split the "Department" attribute into a separate table called "Departments" with "Department_ID" (Primary Key) and "Department_Name" attributes. Then, we add a "Department_ID" column to the "Employees" table as a foreign key.

• Meet the requirements of 1NF.

• Eliminate partial dependencies (attributes depending on only part of the primary key).

Suppose "Supervisor_Name" depends only on "Supervisor_ID" (not on any other attribute). In this case, we move "Supervisor_Name" to the "Employees" table, creating a new table called "Supervisors" with "Supervisor_ID" (Primary Key) and "Supervisor_Name" attributes.

• Meet the requirements of 2NF.

• Eliminate transitive dependencies (attributes depending on other non-key attributes).

Suppose "Supervisor_Department" depends on "Supervisor_ID" and "Supervisor_Department_Location" depends on "Supervisor_Department." We move "Supervisor_Department" to a separate table called "Supervisor_Departments" with "Supervisor_Department_ID" (Primary Key), "Supervisor_ID" (Foreign Key), and "Department_ID" (Foreign Key).

This table may suffer from redundancy and anomalies. For example, if an employee belongs to a specific department, the department name may be repeated for each employee in the table, leading to data duplication. To address this, we can normalize the database using the following steps:

1. First Normal Form (1NF):

   Example:

2. Second Normal Form (2NF):

   Example:

3. Third Normal Form (3NF):

   Example:

After normalization, the database structure would include separate tables for "Employees," "Departments," "Supervisors," and "Supervisor_Departments," with relationships established between them using primary and foreign keys. This structure reduces redundancy, ensures data integrity, and facilitates efficient data retrieval and manipulation.

Read More:..

https://databasetown.com/types-of-normalization-in-dbms-with-examples/