Normalization is a core concept in Database design that helps you organize data efficiently in a relational database. The goal is to reduce redundancy (duplicate data) and improve data integrity (accuracy and consistency).

1. What is Normalization?

Normalization is the process of structuring a database so that:

• Each piece of data is stored only once
• Relationships between data are clearly defined
• Updates, inserts, and deletes don't create inconsistencies

2. Why is Normalization Important?

Normalization is important because it helps maintain a database that is efficient, consistent, and easy to manage.

• Reduces Data Redundancy: Imagine storing a customer's address every time they place an order. If the address changes, you'd have to update it in multiple places! Normalization helps you store information in one place and refer to it from other places, minimizing repetition.
• Improves Data Integrity: Less redundancy means less chance of inconsistencies. If you update an address in one place, it's updated everywhere it's needed.
• Prevents Anomalies: Normalization helps prevent issues like:
  • Insertion Anomalies: Difficulty adding new data because you're missing related information.
  • Update Anomalies: Having to update the same information in multiple rows.
  • Deletion Anomalies: Accidentally losing valuable information when you delete something seemingly unrelated.
• Easier to Understand and Maintain: A normalized database is generally more logically structured and easier to understand, query, and modify.

3. First Normal Form (1NF): Atomic Values

• All columns contain atomic values (i.e., indivisible values).
• Each row is unique (i.e., no duplicate rows).
• Each column has a unique name.

Example problem:

Convert to 1NF:

4. Second Normal Form (2NF): Remove Partial Dependencies

• Must already be in 1NF
• No partial dependency (non-key attributes depend on only part of a composite key)

Every non-key column must depend on the whole primary key.

Assume:

• Primary Key = (StudentID, Course)

StudentName depends only on StudentID not on (StudentID, Course).

This is a partial dependency.

Convert to 2NF:

Students:

Enrollments:

5. Third Normal Form (3NF): Remove Transitive Dependencies

• Must already be in 2NF
• No transitive dependency (non-key attributes depend on other non-key attributes)

Suppose we have:

Here:

StudentID → DepartmentID
DepartmentID → DepartmentName

Therefore:

StudentID → DepartmentName

This is a transitive dependency.

Convert to 3NF:

Students:

Departments:

6. Boyce-Codd Normal Form (BCNF): Stronger Version of 3NF

• A stricter version of 3NF
• Every determinant must be a candidate key

Example

A university has:

Rules:

Teacher → Room
Subject → Teacher

Candidate Keys: Subject because a subject has one teacher.

Problem

Teacher determines Room, but Teacher is not a candidate key.

This violates BCNF.

Convert to BCNF:

Teachers:

Courses:

7. Normalization Summary