2NF in DBMS

Second Normal Form (2NF) is a step further in database normalization that builds on the foundation established by First Normal Form (1NF). A table is in 2NF if it satisfies the following conditions:

1. It is in 1NF: The table must first meet all the requirements of First Normal Form (1NF).

2. No Partial Dependency: There must be no partial dependency, meaning that non-key attributes should depend on the entire primary key (not just part of it in the case of a composite primary key).

Key Concepts of 2NF

• Partial Dependency: Occurs when a non-prime attribute (an attribute that is not part of the candidate key) depends only on part of a composite primary key rather than the entire key. In 2NF, we remove such partial dependencies.

• Composite Primary Key: A primary key consisting of more than one attribute. It is a combination of multiple attributes used to uniquely identify a record in the table.

Conditions for 2NF

1. 1NF Compliance: The table must first be in 1NF (i.e., all attributes contain atomic values and there are no repeating groups).

2. No Partial Dependency: If the primary key is composite (consists of more than one attribute), then each non-prime attribute must depend on the whole primary key, not just a part of it.

Example of a Table in 1NF but Not in 2NF

Consider the following table StudentCourses:

Here, the primary key is {StudentID, CourseID} (since each student can enroll in multiple courses).

Functional Dependencies:

• StudentID → StudentName (A student determines their name).

• CourseID → InstructorName (A course determines the instructor).

Analysis of Partial Dependency:

• StudentID → StudentName is a partial dependency because StudentName depends only on StudentID, which is part of the composite primary key. StudentName should depend on the entire key {StudentID, CourseID}.

Breaking it Down to 2NF:

To convert the table to 2NF, we remove the partial dependency by creating two new tables:

1. Student Table: This table will store student details.

   StudentID	StudentName
   101	Alice
   102	Bob

2. Courses Table: This table will store course details, along with the instructor for each course.

   CourseID	InstructorName
   C001	Dr. Smith
   C002	Dr. Johnson
   C003	Dr. Lee

3. Student-Course Table: This table stores the relationship between students and their enrolled courses.

   StudentID	CourseID
   101	C001
   101	C002
   102	C001
   102	C003

The primary key is a composite key consisting of both StudentID and CourseID. The StudentID and CourseID both act as foreign keys:

• StudentID is a foreign key referencing the Student table, where StudentID is the primary key.

• CourseID is a foreign key referencing the Course table, where CourseID is the primary key.

Result:

Now, the StudentCourses table is in 2NF because:

• It is in 1NF (all columns contain atomic values).

• There is no partial dependency. The non-prime attributes (like StudentName and InstructorName) now depend on the entire primary key.

When is a Table in 2NF?

1. The table should satisfy 1NF: Each column must contain atomic values, and each record must be unique.

2. No Partial Dependency: If the table has a composite primary key, each non-key attribute must depend on the entire primary key and not just a part of it.

Benefits of 2NF

1. Eliminates Redundancy: By removing partial dependencies, 2NF reduces data redundancy, preventing repeated information and ensuring that data is stored in a more efficient manner.

2. Improves Data Integrity: With the removal of partial dependencies, there is less risk of data anomalies, especially during updates, insertions, and deletions.

3. Simplifies Queries: With fewer redundant data, queries are often simpler and faster.

Conclusion

• 2NF (Second Normal Form) eliminates partial dependencies and ensures that non-key attributes depend on the whole primary key.

• It helps in reducing redundant data and improving data integrity by ensuring that all non-key attributes are fully dependent on the primary key.

• 2NF is essential for designing efficient, consistent databases, and it lays the foundation for further normalization (such as 3NF).