The Second Normal Form (2NF) is the next stage in database normalization after achieving the First Normal Form (1NF). 2NF builds on 1NF by eliminating partial dependencies, ensuring that every non-key attribute in a table is fully dependent on the entire primary key. In other words, 2NF requires that each non-key attribute must rely on the whole primary key, not just part of it.

Note: A table needs to be in 1NF before it can be converted to 2NF. Partial dependencies only apply to tables with composite primary keys (primary keys consisting of multiple columns).

Requirements of 2NF

To satisfy the requirements of 2NF:

1. The table must already be in 1NF.
2. There should be no partial dependencies, meaning all non-key attributes must depend on the entire primary key, not just part of it.

Example: Student_Course_Enrollment

Consider the following Student_Course_Enrollment table, which records students enrolled in various courses along with their department. In this table, the primary key is a composite key consisting of Student_ID and Course_ID.

In this table:

• The primary key is {Student_ID, Course_ID}.
• Student_Name and Department depend only on Student_ID, not on the entire primary key {Student_ID, Course_ID}. This means we have partial dependencies, violating 2NF.

Converting to 2NF

To bring this table into 2NF, we need to remove the partial dependencies by separating the table into two tables: Student and Enrollment. Here’s how we can structure them:

Step 1: Create the Student Table

The Student table stores information unique to each student, with Student_ID as the primary key.

Step 2: Create the Enrollment Table

The Enrollment table records the enrollment details of each student in various courses, using {Student_ID, Course_ID} as the composite primary key.

Result After Conversion to 2NF

After separating the original table into Student and Enrollment tables:

• Student_Name and Department are now only dependent on Student_ID in the Student table, eliminating partial dependencies.
• The Enrollment table now has only the attributes Student_ID and Course_ID, which fully depend on the composite primary key.

Why 2NF is Important

Achieving 2NF helps reduce redundancy by ensuring that non-key attributes are not duplicated unnecessarily. It also improves data consistency by grouping attributes based on their dependencies on the primary key, making data easier to manage and reducing the chances of update anomalies.

In the next lesson, we’ll cover Third Normal Form (3NF), which addresses transitive dependencies to further reduce redundancy and ensure data integrity.