Definition of Second Normal Form (2NF):
Second Normal Form (2NF) is a property of a relation in a relational database. A table is said to be in 2NF if it is in 1NF and all non-prime attributes (attributes not part of any candidate key) are fully functionally dependent on the entire primary key. In simpler terms, 2NF eliminates partial dependencies within a table.
Example:
Consider a table that violates 2NF:
Table: EmployeeProjects
| EmployeeID | ProjectID | ProjectName | HoursWorked |
|---|---|---|---|
| 1 | 101 | ProjectA | 20 |
| 1 | 102 | ProjectB | 30 |
| 2 | 101 | ProjectA | 25 |
| 2 | 103 | ProjectC | 15 |
In this table, the primary key is composed of both “EmployeeID” and “ProjectID.”
Identifying Partial Dependencies:
- The partial dependency arises because the “ProjectName” attribute depends only on part of the primary key (i.e., “ProjectID”).
- The “HoursWorked” attribute depends on the entire primary key.
Converting to Second Normal Form:
To achieve 2NF, we need to split the table into two tables to eliminate partial dependencies:
Table: Projects
| ProjectID | ProjectName |
|---|---|
| 101 | ProjectA |
| 102 | ProjectB |
| 103 | ProjectC |
Table: EmployeeProjectHours
| EmployeeID | ProjectID | HoursWorked |
|---|---|---|
| 1 | 101 | 20 |
| 1 | 102 | 30 |
| 2 | 101 | 25 |
| 2 | 103 | 15 |
Explanation:
- Identifying Partial Dependencies:
- The original table had partial dependencies because “ProjectName” depends only on “ProjectID” and not on the entire primary key (“EmployeeID” and “ProjectID”).
- The “HoursWorked” attribute depends on the entire primary key.
- Eliminating Partial Dependencies:
- The “Projects” table contains only information related to projects, with “ProjectID” as the primary key.
- The “EmployeeProjectHours” table contains information about employee hours worked on projects, with the composite key of “EmployeeID” and “ProjectID.”
- Achieving Second Normal Form:
- The resulting tables are now in 2NF.
- All non-prime attributes (attributes not part of any candidate key) are fully functionally dependent on the entire primary key.
By decomposing the original table into two tables based on functional dependencies, we ensure that partial dependencies are eliminated, meeting the requirements of Second Normal Form (2NF).