Database Normalization Explained Simply With an Example

Database normalization is a design process aimed at organizing data within a database to eliminate redundancy, improve integrity, and enhance scalability. By systematically applying normalization principles, you create a database structure that is more efficient, reduces anomalies, and ensures consistency in your data.

This article provides a detailed breakdown of normalization, taking you through unnormalized data to the third normal form (3NF) with illustrative examples and tables.

Why Normalize a Database?

Normalization is vital for designing efficient databases. Here are some key objectives:

• 
  Avoid Redundancy: Prevent duplicate data storage to save space.


• 
  Eliminate Update Anomalies: Ensure changes in data occur consistently across the database.


• 
  Improve Query Performance: Facilitate easier data retrieval and manipulation.


• 
  Ensure Data Integrity: Prevent inconsistencies caused by improper data dependencies.


• 
  Enhance Scalability: Create flexible structures that can handle growing datasets.

Normalization consists of several forms (or stages), with each step building on the previous one to further refine the structure.

Step 1: Unnormalized Form (UNF)

Characteristics:

• Contains redundant data.


• May include multi-valued attributes or repeating groups.


• Offers no guarantees of consistency or integrity.

Example of an Unnormalized Table:

This table is unnormalized because:

1. It contains repeating groups like multiple Slot IDs.


2. Non-atomic values could exist in certain fields.

Issues with UNF:

• Redundant information (e.g., Doctor Name is repeated for D1).


• Difficult to update or query due to mixed information.

Step 2: First Normal Form (1NF)

Characteristics of 1NF:

• Data is organized into rows and columns.


• Each column contains atomic values (no arrays or sets).


• Each record is uniquely identified using a primary key.

Transformation to 1NF:

To achieve 1NF:

1. Remove repeating groups by creating separate tables.


2. Ensure that all attributes have atomic values.


3. Introduce a primary key for each table.

Resulting Tables:

Patients Table (1NF):

Doctors Table (1NF):

Surgery Table (1NF):

Step 3: Second Normal Form (2NF)

Characteristics of 2NF:

• Satisfies all requirements of 1NF.


• Removes partial dependencies, i.e., non-key attributes must depend on the entire primary key.

Key Observations:

In the Surgery table from 1NF, the Region depends only on the Council, not the entire primary key. This violates 2NF.

Transformation to 2NF:

To remove partial dependencies:

1. Create a new table for Council and Region.


2. Ensure all attributes depend solely on the primary key.

Resulting Tables:

Appointments Table (2NF):

Council Table (2NF):

Step 4: Third Normal Form (3NF)

Characteristics of 3NF:

• Satisfies all requirements of 2NF.


• Removes transitive dependencies, i.e., non-key attributes must not depend on other non-key attributes.

Key Observations:

In the Surgery table from 2NF, Postcode and Surgery Number have a transitive dependency, where Postcode indirectly depends on Surgery Number.

Transformation to 3NF:

To eliminate transitive dependencies:

1. Create a new table for Postcode and Surgery Number.


2. Ensure all attributes depend directly on the primary key.

Resulting Tables:

Surgery Table (3NF):

Final Database Structure

After normalization, the database consists of the following well-structured tables:

1. 
   

   Patients Table:
   
   Stores patient information with atomic fields.

   
   
   
   
   
   • 
     Patient ID, Patient Name
     
   
   
   
   


2. 
   

   Appointments Table:
   
   Links Slot ID with Total Cost.

   
   
   
   
   
   • 
     Appointment ID, Slot ID, Total Cost
     
   
   
   
   


3. 
   

   Doctors Table:
   
   Contains details about doctors.

   
   
   
   
   
   • 
     Doctor ID, Doctor Name
     
   
   
   
   


4. 
   

   Council Table:
   
   Maps Council to Region.

   
   
   
   
   
   • 
     Council, Region
     
   
   
   
   


5. 
   

   Surgery Table:
   
   Connects Postcode to Surgery Number.

   
   
   
   
   
   • 
     Postcode, Surgery Number
     
   
   
   
   

Why Stop at 3NF?

While 3NF is sufficient for most practical purposes, further normalization forms exist:

• 
  Boyce-Codd Normal Form (BCNF): Ensures stricter elimination of redundancy.


• 
  Fourth Normal Form (4NF): Addresses multi-valued dependencies.


• 
  Fifth Normal Form (5NF): Eliminates redundancy arising from join dependencies.

For most real-world applications, 3NF strikes the right balance between performance and data integrity.

Conclusion

Normalization is a critical process for designing databases that are efficient, scalable, and easy to maintain. By following the steps outlined above, you can create a well-structured database that minimizes redundancy, prevents anomalies, and simplifies data operations.