6.2 Normalization: 1NF, 2NF, 3NF

Normalization is the process of organizing data within a database to minimize redundancy and dependency, making it more efficient and easier to maintain. It involves dividing larger tables into smaller ones and defining relationships between them. The three most common normal forms (NF) are First Normal Form (1NF), Second Normal Form (2NF), and Third Normal Form (3NF).

1. First Normal Form (1NF)

A table is in First Normal Form (1NF) if it satisfies the following conditions:

1. Atomic Values: All columns contain atomic (indivisible) values. Each field must hold a single value, not a list or set of values.
2. Unique Rows: Each row must be unique. There should be a primary key (or a combination of keys) that uniquely identifies each row.
3. No Repeating Groups: There must be no repeating groups of columns. Each column should contain only one attribute, and if there are multiple attributes, they should be placed in separate rows.

Example of 1NF Violation:

The Subjects column contains multiple values (Math, English) which violates 1NF.

Conversion to 1NF:

Now, each value is atomic, and there are no repeating groups. The table is in 1NF.

2. Second Normal Form (2NF)

A table is in Second Normal Form (2NF) if:

1. It is in 1NF.
2. There is no partial dependency. This means that non-key columns should depend on the entire primary key, not just a part of it. In other words, if the primary key is composite (consisting of more than one column), no non-key attribute should depend on just one part of the primary key.

Example of 2NF Violation:

Consider the following table with a composite primary key (StudentID, CourseID):

Here, Instructor and Room depend only on CourseID, not on the whole primary key (StudentID, CourseID). This is a partial dependency.

Conversion to 2NF:

To resolve this, we separate the course-related information (like Instructor and Room) into a new table, as it depends only on CourseID:

Student_Course Table:

Course Details Table:

Now, the Student_Course table only stores the relationship between StudentID and CourseID, and the Course Details table stores the information that depends solely on CourseID. This resolves the partial dependency, and the tables are now in 2NF.

3. Third Normal Form (3NF)

A table is in Third Normal Form (3NF) if:

1. It is in 2NF.
2. There is no transitive dependency. This means that non-key columns should depend only on the primary key and not on other non-key columns.

Example of 3NF Violation:

Consider the following table:

Here, the Instructor_Phone depends on Instructor, not directly on the StudentID or CourseID combination. This is a transitive dependency because Instructor_Phone is indirectly dependent on the primary key via Instructor.

Conversion to 3NF:

To resolve this, we remove the transitive dependency by separating the Instructor_Phone into a new table:

Student_Course Table:

Instructor Table:

Now, Instructor_Phone is only dependent on Instructor, and all non-key attributes depend directly on the primary key of their respective tables. The tables are now in 3NF.

Summary of Normal Forms:

Benefits of Normalization:

• Eliminates Redundancy: By breaking data into smaller tables, normalization reduces data duplication.
• Improves Data Integrity: Ensures consistency and accuracy by minimizing the chances of errors or inconsistencies.
• Optimizes Query Performance: Smaller, well-structured tables can speed up queries and make data management easier.

Normalization is a critical aspect of database design as it ensures efficient storage, retrieval, and integrity of data. By applying 1NF, 2NF, and 3NF, database designers can create databases that are more scalable and easier to maintain.