Database Management System (DBMS) / Normalization in DBMS

What is Normalisation?

Normalization is the process of organizing data in a database to eliminate redundancy and ensure data integrity.

🎯 Main Goals of Normalization:

Goal

Meaning

✅ Eliminate data redundancy

Avoid storing the same data in multiple places

✅ Avoid anomalies

Prevent insertion, update, and deletion anomalies

✅ Maintain data integrity

Keep data accurate, consistent, and logically organized

✅ Break large tables into smaller ones

Create focused, related tables connected with foreign keys

🔧 What Do We Actually Do in Normalization?

We transform a large, unstructured or poorly structured table into multiple smaller and well-structured tables by:

  1. Identifying functional dependencies

  2. Splitting tables based on those dependencies

  3. Assigning keys (primary/foreign) to maintain relationships

  4. Following specific "normal forms" (rules) — 1NF, 2NF, 3NF, etc.

📊 Example Before Normalization:

Student_ID

Student_Name

Course

Instructor

101

Swati

DBMS

Mr. Roy

102

Ravi

OS

Mrs. Khan

103

Swati

AI

Mr. Roy

🔴 Problems:

  • Repetition of instructor names

  • Swati repeated

  • Difficult to update a course or instructor without affecting other data

✅ After Normalization:

🧩 Student Table

Student_ID

Student_Name

101

Swati

102

Ravi

🧩 Instructor Table

Instructor_ID

Instructor_Name

1

Mr. Roy

2

Mrs. Khan

🧩 Course Table

Course

Instructor_ID

DBMS

1

OS

2

AI

1

🧩 Enrollment Table

Student_ID

Course

101

DBMS

102

OS

103

AI

🔄 Normalization in Action:

Step

What We Do

1NF

Remove repeating groups/arrays → every field must hold atomic values

2NF

Remove partial dependencies → non-key fields depend on whole primary key

3NF

Remove transitive dependencies → non-key attributes should not depend on other non-key attributes

✅ Key Reasons Why Normalization is Necessary

🔁 Eliminate Data Redundancy

  • Redundancy means storing the same data in multiple places.

  • Leads to wastage of storage and update anomalies.

🔸 Example: If a student's college name is repeated in every row, and the college name changes, you must update every record.

♻️ Avoid Update Anomalies

Three types of anomalies occur in unnormalized tables:

Type of Anomaly

What Happens?

Update

You update data in one place but forget elsewhere.

Insert

You cannot insert data because some fields require values that don’t yet exist.

Delete

Deleting a record might remove useful data unintentionally.

🔐 Ensure Data Integrity & Consistency

  • By organizing data correctly, normalization enforces rules (e.g., foreign keys).

  • Ensures that the database reflects real-world relationships properly.

📊 Make Queries More Efficient

  • Smaller, related tables can often be queried more efficiently than one big table.

  • Reduces the need for scanning large volumes of duplicated data.

🔍 Improves Data Maintenance

  • Changes (insert/update/delete) are easier, safer, and faster.

  • Maintenance becomes manageable over time as data grows.

🔗 Improves Relationship Representation

  • Helps in better representation of 1:1, 1:N, and M:N relationships between entities.

  • Simplifies ER modeling and schema design.

🧠 Quick Summary:

Aspect

Before Normalization

After Normalization

Data Redundancy

High

Low

Data Consistency

Prone to anomalies

Strong

Update Complexity

Multiple places

Single place

Table Size

Large & unstructured

Small, logical, and related

Relationships

Implicit or unclear

Clear with foreign keys