🗄️ DBMS ANOMALIES & NORMALIZATION Insertion • Update • Deletion Anomaly • 1NF • 2NF • 3NF

🗄️ DBMS ANOMALIES & NORMALIZATION

Insertion • Update • Deletion Anomaly • 1NF • 2NF • 3NF

📘 Interactive Learning Module

Learn how poorly designed database tables create anomalies and how 1NF, 2NF and 3NF solve these problems.

Topics: Insertion Anomaly, Update Anomaly, Deletion Anomaly, First Normal Form, Second Normal Form and Third Normal Form.

⚠️ What are Database Anomalies?

Database anomalies are problems that occur when data is poorly organized or unnecessarily repeated in a database.

Three Major Anomalies

Anomaly	Basic Meaning
Insertion Anomaly	Difficulty inserting new information
Update Anomaly	Same information must be changed in multiple places
Deletion Anomaly	Deleting data may accidentally remove useful information

Why do anomalies occur?

They commonly occur because too much unrelated information has been stored in a single relation.

➕ Insertion Anomaly

An Insertion Anomaly occurs when we cannot insert certain information into a table without also inserting unnecessary information.

Example

Student_ID	Student_Name	Course_ID	Course_Name
101	Rahul	C01	DBMS

Suppose a new course Operating System is created, but no student has enrolled yet.

1

We want to insert the new course.

2

The table expects Student_ID and Student_Name as well.

3

There is no student information to enter.

Therefore, inserting a new course becomes difficult.

This is Insertion Anomaly.

🔄 Update Anomaly

An Update Anomaly occurs when the same information is stored in multiple rows and must be updated repeatedly.

Student_ID	Name	Course_ID	Course_Name
101	Rahul	C01	Database
102	Anita	C01	Database
103	Rohan	C01	Database

1

The course name "Database" appears three times.

2

Suppose the name changes to "DBMS".

3

All three rows must be updated.

4

If one row is not updated, inconsistent data exists.

This is Update Anomaly.

🗑️ Deletion Anomaly

A Deletion Anomaly occurs when deleting one piece of information unintentionally removes another important piece of information.

Student_ID	Student_Name	Course_ID	Course_Name
101	Rahul	C01	DBMS

1

Suppose Rahul leaves the college.

2

We delete Rahul's record.

3

The only record containing information about course C01 is also removed.

We accidentally lose course information.

This is Deletion Anomaly.

1️⃣ First Normal Form — 1NF

A relation is in First Normal Form (1NF) when each attribute contains atomic values and there are no repeating groups or multi-valued cells.

❌ Not in 1NF

Student_ID	Name	Phone
101	Rahul	9876, 8765

The Phone column contains multiple values in one cell.

✅ Convert to 1NF

Student_ID	Name	Phone
101	Rahul	9876
101	Rahul	8765

1NF Rule:

One cell → One atomic value.

2️⃣ Second Normal Form — 2NF

A relation is in 2NF when:

✔ It is already in 1NF.
✔ It has no partial dependency of a non-key attribute on part of a composite key.

Example

Enrollment(Student_ID, Course_ID, Student_Name, Course_Name, Marks)

Suppose the composite key is:

(Student_ID, Course_ID)

Functional Dependencies

Student_ID → Student_Name

Course_ID → Course_Name

Student_ID, Course_ID → Marks

Problem:

Student_Name depends only on Student_ID. Course_Name depends only on Course_ID. Therefore they depend on only part of the composite key. This is called Partial Dependency.

Decompose

STUDENT(Student_ID, Student_Name)

COURSE(Course_ID, Course_Name)

ENROLLMENT(Student_ID, Course_ID, Marks)

The partial dependencies have been removed.

Now the relations satisfy 2NF.

3️⃣ Third Normal Form — 3NF

A relation is in 3NF when:

✔ It is already in 2NF.
✔ There is no transitive dependency of a non-key attribute on the primary key.

Example

EMPLOYEE(Employee_ID, Employee_Name, Department_ID, Department_Name)

Functional Dependencies

Employee_ID → Employee_Name, Department_ID

Department_ID → Department_Name

Transitive Dependency

1

Employee_ID determines Department_ID.

2

Department_ID determines Department_Name.

3

Therefore Employee_ID indirectly determines Department_Name.

Employee_ID → Department_ID → Department_Name

This is a Transitive Dependency.

Decompose into 3NF

EMPLOYEE(Employee_ID, Employee_Name, Department_ID)

DEPARTMENT(Department_ID, Department_Name)

The transitive dependency is removed.

The relations are now in 3NF.

📊 Complete Normalization Example

Starting Table

ENROLLMENT(Student_ID, Student_Name, Course_ID, Course_Name, Department_ID, Department_Name)

Step 1 — 1NF

Make sure every attribute contains atomic values.

All values are atomic → 1NF achieved.

Step 2 — 2NF

Remove partial dependencies from a composite key.

Student_ID → Student_Name
Course_ID → Course_Name

Move these dependencies into separate relations.

STUDENT(Student_ID, Student_Name)
COURSE(Course_ID, Course_Name)
ENROLLMENT(Student_ID, Course_ID)

Step 3 — 3NF

Remove transitive dependencies.

Department_ID → Department_Name

Create a separate department relation.

DEPARTMENT(Department_ID, Department_Name)

🎯 Final Normalized Database

STUDENT(Student_ID, Student_Name)
COURSE(Course_ID, Course_Name)
DEPARTMENT(Department_ID, Department_Name)
ENROLLMENT(Student_ID, Course_ID)

🧮 Normalization Step-by-Step

1

Start with the unnormalized relation.
Identify repeating or multi-valued attributes.

2

Convert to 1NF.
Make every cell atomic.

3

Identify the candidate/primary key.

4

Convert to 2NF.
Remove partial dependencies.

5

Convert to 3NF.
Remove transitive dependencies.

1NF → Atomic Values

2NF → Remove Partial Dependency

3NF → Remove Transitive Dependency

⚖️ 1NF vs 2NF vs 3NF

Normal Form	Main Condition	Removes
1NF	Atomic values	Repeating / multi-valued groups
2NF	1NF + No Partial Dependency	Partial Dependency
3NF	2NF + No Transitive Dependency	Transitive Dependency

1NF → Atomic

2NF → No Partial Dependency

3NF → No Transitive Dependency

❓ Short Questions & Answers

Q1. What is an insertion anomaly?

Insertion anomaly occurs when new information cannot be inserted without also inserting unnecessary information.

Q2. What is an update anomaly?

Update anomaly occurs when the same information is repeated in multiple rows and must be updated in several places.

Q3. What is a deletion anomaly?

Deletion anomaly occurs when deleting one record accidentally removes other useful information.

Q4. What is 1NF?

A relation is in 1NF when every attribute contains atomic values and there are no repeating groups.

Q5. What is 2NF?

A relation is in 2NF when it is in 1NF and has no partial dependency of a non-key attribute on part of a composite key.

Q6. What is 3NF?

A relation is in 3NF when it is in 2NF and has no transitive dependency of a non-key attribute on the primary key.

Q7. What is partial dependency?

Partial dependency occurs when a non-key attribute depends on only part of a composite candidate key.

Q8. What is transitive dependency?

Transitive dependency occurs when A → B and B → C, causing A to indirectly determine C.

B.K. PAUL

Bhairab Ganguly College

DBMS • Anomalies • Normalization • 1NF • 2NF • 3NF

Interactive Learning Module