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):

This table violates 1NF because Subjects has multiple values.

1NF Table:

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:

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

2NF Tables:

Student Table:

Enrollment Table:

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:

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

3NF Tables:

Employee Table:

Department Table:

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

Summary Table