Second Normal Form

Second Normal Form (2NF) is an important stage in database normalization, aiming to enhance data integrity by addressing partial dependencies within a table. It evolves from the principles of First Normal Form (1NF) by ensuring that every non-key attribute is fully dependent on the entire primary key.

In the context of a hypothetical table containing Student ID, Name, Course, and Professor, the primary key being Student ID, a partial dependency exists between Course and Professor. Here, Professor relies solely on Course and not on the complete primary key.

To achieve 2NF, we can decompose the table into two distinct tables to eliminate this partial dependency while preserving data integrity. The first table would contain student-specific details, such as Student ID and Name. Meanwhile, the second table would store course-related information, associating Course with its respective Professor. Consequently, this separation ensures that each table is in 2NF, as every attribute is functionally dependent on the primary key within its respective table.

This normalization process not only adheres to 2NF principles but also enhances data management by organizing information logically and reducing redundancy, paving the way for more efficient database operations and maintenance.

Original Table:



Normalized Tables:

STUDENT Table:



COURSE Table:


STUDENT_COURSE Table:


Additional Examples:

Second Normal Form (2NF) primarily addresses partial dependencies in tables. Let's consider an illustrative example to demonstrate achieving 2NF through table decomposition, ensuring no partial dependencies exist.

Suppose we have a table called "Employee_Payroll" recording employee information, including Employee ID, Employee Name, Department, and Department Head.

Original Table:


Approach to Achieve 2NF:
  • Identification of Partial Dependencies: The Department Head attribute depends solely on the Department.
  • There's a partial dependency between Department and Department Head.
Decomposition into 2NF:

Employee Information Table:

Department Information Table:


Result:
  • The Employee Information Table stores unique employee details.
  • The Department Information Table maintains a separate list of departments and their corresponding heads.
  • Decomposing the original table into these two tables eliminates the partial dependency issue, ensuring each table contains attributes reliant only on the complete primary key, thereby achieving Second Normal Form (2NF).

Post a Comment

0 Comments