Normalization (1NF, 2NF, 3NF)

Normalization is the systematic process of organizing data in a database. This includes creating tables and establishing relationships between those tables according to strict mathematical rules designed to protect the data and eliminate redundancy.

When a database is not normalized, it suffers from Update Anomalies:

• Insertion Anomaly: Inability to add data because other unrelated data is missing.
• Deletion Anomaly: Unintended loss of data because it was stored in the same row as the data being deleted.
• Update Anomaly: Having to update a piece of information (like a teacher's phone number) in 500 different rows because it was duplicated.

1. First Normal Form (1NF)

A relation is in 1NF if and only if the domain of each attribute contains only atomic (indivisible) values, and the value of each attribute contains only a single value from that domain.

The Rule: No arrays, no lists, no repeating groups.

• Violation: A Student table has a PhoneNumbers column storing "555-1234, 555-9876".
• Solution: Create a separate row for each phone number, or better yet, create a separate Student_Phones table.

2. Second Normal Form (2NF)

A relation is in 2NF if it is in 1NF and every non-prime attribute is fully functionally dependent on the primary key.

The Rule: Eliminate partial dependencies.

• A Prime Attribute is any attribute that is part of a Candidate Key.
• A Partial Dependency occurs when the Primary Key is composite (e.g., made of two columns: StudentID and CourseID), and a non-prime attribute depends on only part of that key.
• Violation: A table Enrollment (StudentID, CourseID, CourseName, Grade). The CourseName depends only on the CourseID, not the StudentID. This is a partial dependency.
• Solution: Split into two tables: Enrollment (StudentID, CourseID, Grade) and Courses (CourseID, CourseName).

3. Third Normal Form (3NF)

A relation is in 3NF if it is in 2NF and there is no transitive dependency for non-prime attributes.

The Rule: Eliminate transitive dependencies. An attribute must not depend on another non-key attribute. (Every non-key attribute must provide a fact about the key, the whole key, and nothing but the key).

• Violation: A table Employees (EmpID, Name, DeptID, DeptLocation). Here, EmpID -> DeptID, and DeptID -> DeptLocation. Therefore, DeptLocation transitively depends on EmpID. If the department moves, you have to update the location for thousands of employees.
• Solution: Split into two tables: Employees (EmpID, Name, DeptID) and Departments (DeptID, DeptLocation).