What is Normalization? Explain 1NF, 2NF & 3NF with examples.

Normalization in DBMS

Normalization is a process in database design used to:

• Organize data efficiently,
• Eliminate redundancy (duplicate data),
• Avoid anomalies (insertion, update, deletion issues),
• Ensure data integrity.

Normalization involves breaking a database into smaller, well-structured tables using normal forms.

---

1. First Normal Form (1NF)

Rule:

• Each column must contain atomic (indivisible) values.
• No repeating groups or arrays.

Example (Unnormalized Table):

RollNo	Name	Subjects
101	Arun	Math, Physics
102	Meera	English

This table violates 1NF because Subjects has multiple values.

1NF Table:

RollNo	Name	Subject
101	Arun	Math
101	Arun	Physics
102	Meera	English

Now, every field has atomic values.

---

2. Second Normal Form (2NF)

Rule:

• Must be in 1NF.
• No partial dependency: Non-prime attributes must depend on the whole primary key, not part of it.

Example:

RollNo	Subject	StudentName
101	Math	Arun
101	Physics	Arun
102	English	Meera

Here, (RollNo, Subject) is the composite key. But StudentName depends only on RollNo, not on both attributes.

2NF Tables:

Student Table:

RollNo	StudentName
101	Arun
102	Meera

Enrollment Table:

RollNo	Subject
101	Math
101	Physics
102	English

Now, non-key attributes depend only on the full key.

---

3. Third Normal Form (3NF)

Rule:

• Must be in 2NF.
• No transitive dependency: Non-key attributes must depend only on the primary key, not on another non-key attribute.

Example:

EmpID	EmpName	DeptID	DeptName
1	Asha	D1	HR
2	Ravi	D2	IT

Here, DeptName depends on DeptID, not directly on EmpID (transitive dependency).

3NF Tables:

Employee Table:

EmpID	EmpName	DeptID
1	Asha	D1
2	Ravi	D2

Department Table:

DeptID	DeptName
D1	HR
D2	IT

Now, all non-key attributes depend only on the primary key, with no transitive dependencies.

---

Summary Table

Normal Form	Conditions	Purpose
1NF	Atomic values, no repeating groups	Eliminate multi-valued columns
2NF	1NF + no partial dependencies	Eliminate partial dependencies
3NF	2NF + no transitive dependencies	Eliminate indirect dependencies