3.3 Normalization

Level: 3 | Version: v1.1 | Author: Meptrasoft

Overview

Normalization is the process of organizing data in a relational database to reduce unnecessary duplication and avoid data-related problems.

It generally involves dividing a large table into smaller, related tables and connecting them using keys.

Normalization helps reduce:

• Data redundancy — unnecessary duplicate data
• Update anomalies — the same fact must be updated in multiple places
• Insert anomalies — a new fact cannot be added without unrelated data
• Delete anomalies — deleting one record accidentally removes another useful fact

A simple idea is:

ONE LARGE TABLE
      ↓
REMOVE REPETITION
      ↓
SEPARATE RELATED DATA
      ↓
CONNECTED TABLES

Normalization organizes relational data so that each fact is stored in an appropriate place and unnecessary duplication is reduced.

Why Is Normalization Needed?

Imagine an employee table like this:

At first, this looks simple.

But notice:

• HR and London are repeated.
• An employee can have multiple skills in one cell.
• Department information is mixed with employee information.
• Updating department details can require changing multiple rows.

These are signs that the table can be better organized.

Problems in Unnormalized Data

1. Data Redundancy

The same information is stored more than once.

For example:

HR → London
HR → London

The department location is repeated for every HR employee.

2. Update Anomaly

Suppose the HR department moves from London to Manchester.

We may need to update multiple employee rows.

If one row is missed:

Employee 101 → HR → Manchester
Employee 103 → HR → London

The database now contains conflicting information.

Update anomaly occurs when the same fact must be changed in multiple places and some copies are missed.

3. Insert Anomaly

Suppose a new department is created, but it does not yet have any employees.

In the original table, department information depends on having an employee row.

We may be forced to insert employee-related data just to store the department.

Insert anomaly occurs when one fact cannot be inserted independently because another unrelated fact is required.

4. Delete Anomaly

Suppose the last employee in the Finance department leaves.

If we delete that employee's row, we may also lose the only stored information about the Finance department.

Delete anomaly occurs when deleting one fact unintentionally removes another useful fact.

Practice Table Setup

The original table can be created as:

CREATE TABLE employee_department (
    emp_id INT,
    emp_name VARCHAR(50),
    department VARCHAR(50),
    dept_location VARCHAR(50),
    emp_skills VARCHAR(100)
);

INSERT INTO employee_department
(emp_id, emp_name, department, dept_location, emp_skills)
VALUES
(101, 'Nick Wise', 'HR', 'London', 'Recruitment, Payroll'),
(102, 'John Cader', 'Finance', 'Australia', 'Budgeting'),
(103, 'Lily Case', 'HR', 'London', 'Recruitment'),
(104, 'Ford Dawid', 'IT', 'Chicago', 'Programming, Testing');

The main problem is:

emp_skills
→ "Recruitment, Payroll"

Multiple skills are stored inside one cell.

This is not a clean relational design.

First Normal Form (1NF)

The first important step in normalization is First Normal Form (1NF).

A table is in 1NF when each column contains atomic values—one value in each cell—and repeating groups are removed.

For example, instead of:

Emp_ID = 101
Skills = Recruitment, Payroll

we store the skills separately:

Now each cell contains one value.

1NF = One value per cell, with no repeating groups.

Normalized Design

We can separate the original table into related tables.

Department

Employee

Employee_Skills

Now:

• Department information is stored once.
• Employee information is stored separately.
• Skills are stored as individual values.
• The tables are connected using keys.

Normalization Transformation

The transformation can be summarized as:

UNNORMALIZED TABLE
        ↓
Remove repeating values
        ↓
Separate related facts
        ↓
Create relationships
        ↓
NORMALIZED TABLES

Before normalization:

Employee + Department + Skills
            ↓
        ONE TABLE

After normalization:

Department
     ↓
Employee
     ↓
Employee_Skills

The goal is not simply to create more tables.

The goal is to store each fact in the appropriate place and establish meaningful relationships between the tables.

2NF and 3NF — Basic Idea

Normalization is commonly discussed through several normal forms.

First Normal Form — 1NF

Focuses on atomic values and removing repeating groups.

1NF → ONE VALUE PER CELL

Second Normal Form — 2NF

Builds on 1NF and ensures that non-key attributes depend on the whole primary key, particularly when a table has a composite key.

2NF → NO PARTIAL DEPENDENCY

Third Normal Form — 3NF

Builds on 2NF and removes transitive dependencies, so non-key attributes depend on the key rather than on another non-key attribute.

3NF → NO TRANSITIVE DEPENDENCY

For most beginner database designs, reaching an appropriate 3NF structure is a common normalization goal.

1NF → Atomic values
2NF → Full dependency on the key
3NF → No transitive dependency

SQL Example

The normalized tables can be created using:

CREATE TABLE department (
    dept_id VARCHAR(5) PRIMARY KEY,
    department VARCHAR(50),
    dept_location VARCHAR(50)
);

CREATE TABLE employee (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    dept_id VARCHAR(5),
    FOREIGN KEY (dept_id) REFERENCES department(dept_id)
);

CREATE TABLE employee_skills (
    emp_id INT,
    emp_skill VARCHAR(50),
    FOREIGN KEY (emp_id) REFERENCES employee(emp_id)
);

Now the relationships are explicitly represented using foreign keys.

For example:

employee.dept_id
        ↓
department.dept_id

Benefits of Normalization

Normalization can help:

• Reduce unnecessary duplication
• Prevent update anomalies
• Prevent insert anomalies
• Prevent delete anomalies
• Improve data integrity
• Make relationships clearer
• Make database maintenance easier

However, normalization can also create more tables and more joins. In some systems, deliberate denormalization may be used for performance or reporting requirements.

Good database design balances data integrity, maintainability, and performance.

Common Beginner Mistakes

Placement Quick Points

NORMALIZATION
→ ORGANIZE RELATIONAL DATA

1NF
→ ATOMIC VALUES
→ ONE VALUE PER CELL

2NF
→ NO PARTIAL DEPENDENCY

3NF
→ NO TRANSITIVE DEPENDENCY

MAIN GOALS
→ REDUCE REDUNDANCY
→ AVOID INSERT / UPDATE / DELETE ANOMALIES
→ IMPROVE DATA INTEGRITY

• Normalization organizes relational data into appropriate related tables.
• It reduces unnecessary data redundancy.
• It helps prevent insertion, update, and deletion anomalies.
• 1NF focuses on atomic values and repeating groups.
• 2NF removes partial dependency on part of a composite key.
• 3NF removes transitive dependency.
• Normalization improves maintainability and integrity, but excessive normalization can increase query complexity.

Interview Questions

What is normalization?

Normalization is the process of organizing relational data to reduce unnecessary redundancy and prevent data anomalies.

Why is normalization needed?

It helps reduce duplicate data and prevents insertion, update, and deletion anomalies.

What is data redundancy?

Data redundancy is the unnecessary repetition of the same data in multiple places.

What is an update anomaly?

An update anomaly occurs when the same fact is stored in multiple locations and updating only some of those locations creates inconsistent data.

What is an insert anomaly?

An insert anomaly occurs when a new fact cannot be added without also providing unrelated information.

What is a delete anomaly?

A delete anomaly occurs when deleting one record unintentionally removes another useful fact.

What is 1NF?

1NF requires atomic values and removes repeating groups, so each cell contains a single value.

What is 2NF?

2NF builds on 1NF and requires non-key attributes to depend on the whole primary key, eliminating partial dependency.

What is 3NF?

3NF builds on 2NF and removes transitive dependencies so non-key attributes depend on the key rather than on other non-key attributes.

Does normalization always improve query performance?

No. Normalization mainly improves data organization and integrity. It can sometimes require more joins, so performance depends on the workload and design.

Practice & Hands-On Exercises

Beginner Practice

1. Define normalization in your own words.
2. What is data redundancy?
3. Explain update, insert, and delete anomalies.
4. What does 1NF mean?
5. What is the difference between 2NF and 3NF?

Table Analysis

Consider:

6. Identify the repeating/multiple-value problem.
7. Why is storing two skills in one cell undesirable?
8. Which normalization form addresses the atomic-value problem?

SQL Connection

Using the normalized tables:

9. Write a query to display each employee with their department:

SELECT e.emp_name, d.department, d.dept_location
FROM employee e
JOIN department d ON e.dept_id = d.dept_id;

10. Write a query to display each employee's skills:

SELECT e.emp_name, s.emp_skill
FROM employee e
JOIN employee_skills s ON e.emp_id = s.emp_id;

11. Explain how employee.dept_id connects to department.dept_id.
12. Explain how employee_skills.emp_id connects to employee.emp_id.

💡 Tip: Test your queries using the in-browser interactive runner above.

Key Takeaway

UNNORMALIZED DATA
        ↓
REDUNDANCY + ANOMALIES
        ↓
NORMALIZATION
        ↓
RELATED TABLES
        ↓
LESS REDUNDANCY
+ BETTER INTEGRITY
+ EASIER MAINTENANCE

Normalization organizes relational data into related tables so that unnecessary duplication is reduced and data anomalies are avoided.

End of Normalization