Normalization & Functional Dependency

Normalization is the process of organizing data in a database to reduce redundancy and eliminate anomalies (Insertion, Update, Deletion). It is one of the most frequently asked topics in DBMS interviews.

---

Learning Objectives

After completing this chapter, you will be able to:

• Define normalization and its purpose.
• Understand the three types of data anomalies.
• Explain Functional Dependency (FD).
• Classify FDs as trivial/non-trivial.
• Understand closures and Armstrong’s Axioms.
• Apply 1NF, 2NF, and 3NF rules.
• Answer normalization interview questions.

---

What is Normalization?

Normalization is a systematic approach to decompose larger tables into smaller, related tables to:

Goal	Description
Eliminate redundancy	Store each fact exactly once
Remove anomalies	Prevent data inconsistencies
Improve integrity	Make constraints easier to enforce
Simplify maintenance	Changes affect minimal tables

---

The Three Anomalies

Consider an unnormalized table storing student-course data:

Student_ID	Student_Name	Course_ID	Course_Name	Instructor
101	Rahul	C01	DBMS	Dr. Sharma
101	Rahul	C02	OS	Dr. Verma
102	Priya	C01	DBMS	Dr. Sharma
103	Amit	C02	OS	Dr. Verma

1. Insertion Anomaly

Cannot add a new course (C03, Networks, Dr. Gupta) until a student enrolls in it. The Student_ID field would be NULL, violating the primary key.

2. Update Anomaly

If Dr. Sharma changes their name, you must update every row where they teach. Miss one — data becomes inconsistent.

3. Deletion Anomaly

If Amit (the only student in OS) drops out, deleting his enrollment also deletes the fact that Dr. Verma teaches OS.

Normalization splits this into Students, Courses, and Enrollments tables — each fact stored exactly once.

---

Functional Dependency (FD)

A Functional Dependency describes the relationship between attributes. If attribute A uniquely determines attribute B, we write:

A → B (read as “A determines B”)

Example

In a Students table:

• Student_ID → Name (knowing the ID tells you the name)
• Student_ID → Email (knowing the ID tells you the email)

Trivial vs Non-Trivial

Type	Definition	Example
Trivial FD	B is a subset of A	{ID, Name} → {ID}
Non-Trivial FD	B is not a subset of A	{ID} → {Name}
Completely Non-Trivial	A and B have no attributes in common	{ID} → {Name}

Rules for FDs (Armstrong’s Axioms)

Rule	Meaning	Example
Reflexivity	If Y ⊆ X, then X → Y	{A,B} → {A}
Augmentation	If X → Y, then XZ → YZ	A → B implies AC → BC
Transitivity	If X → Y and Y → Z, then X → Z	A → B and B → C implies A → C

---

Closure of an Attribute Set (X⁺)

The Closure of attribute set X (written X⁺) is the set of all attributes that can be functionally determined by X.

Example

Given: R(A, B, C, D) with FDs: A → B, B → C, A → D

Calculate A⁺:

1. Start: A⁺ = {A}
2. Using A → B: A⁺ = {A, B}
3. Using B → C: A⁺ = {A, B, C}
4. Using A → D: A⁺ = {A, B, C, D}

Since A⁺ contains all attributes of R, A is a candidate key.

Why Closure Matters

Purpose	Use
Find candidate keys	If X⁺ = all attributes, X is a super key
Check normal forms	Determine if non-key attributes depend on the full key
Validate decompositions	Ensure lossless joins and dependency preservation

---

First Normal Form (1NF)

Rule: Every column must contain atomic (indivisible) values. No repeating groups.

Before 1NF

Student	Courses
Rahul	DBMS, OS
Priya	DBMS

After 1NF

Student	Course
Rahul	DBMS
Rahul	OS
Priya	DBMS

Every cell contains exactly one value. The primary key changes to {Student, Course} (composite).

---

Second Normal Form (2NF)

Rule: Must be in 1NF AND no partial dependency — every non-key attribute must depend on the ENTIRE primary key.

Partial Dependency Example

In 1NF table with PK {Student_ID, Course_ID}:

• Student_Name depends only on Student_ID (partial dependency)
• Course_Name depends only on Course_ID (partial dependency)

Fix: Split into three tables

Students	Course_ID → Course_Name
Enrollments	Student_ID → Student_Name, Course_ID

Now every non-key attribute depends on the full primary key of its table.

---

Third Normal Form (3NF)

Rule: Must be in 2NF AND no transitive dependency — a non-key attribute must not depend on another non-key attribute.

Transitive Dependency Example

In a Courses table with Course_ID → Instructor, Instructor → Office:

Course_ID → Instructor → Office

Office depends on Instructor, not directly on Course_ID. This is transitive.

Fix: Split

Courses	Course_ID → Course_Name, Instructor
Instructors	Instructor → Office

Now every non-key attribute depends on nothing but the key.

---

Normal Forms Quick Reference

Normal Form	Rule	Fix
1NF	Atomic values, no repeating groups	Split multi-valued cells into rows
2NF	No partial dependency	Remove dependencies on part of composite PK
3NF	No transitive dependency	Move inter-non-key dependencies to separate tables

---

Interview Deep Dive

---

Key Takeaways

• Normalization reduces redundancy and eliminates insertion, update, and deletion anomalies.
• Functional Dependency (FD): A → B means A determines B.
• Armstrong’s Axioms: Reflexivity, Augmentation, Transitivity — used to derive all FDs.
• Closure (X⁺): Set of all attributes determined by X. Used to find candidate keys.
• 1NF: Atomic values only.
• 2NF: No partial dependencies (remove dependencies on part of composite PK).
• 3NF: No transitive dependencies (no non-key → non-key).
• Normalization is not always about performance — it’s about integrity. Denormalize only when read performance is critical.