Mastering Database Management

0 of 78 lessons complete (0%)

Data Modeling

Second Normal Form

You don’t have access to this lesson

Please register or sign in to access the course content.

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

EmployeeIDProjectIDProjectNameHoursWorked
1101ProjectA20
1102ProjectB30
2101ProjectA25
2103ProjectC15

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

ProjectIDProjectName
101ProjectA
102ProjectB
103ProjectC

Table: EmployeeProjectHours

EmployeeIDProjectIDHoursWorked
110120
110230
210125
210315

Explanation:

  1. 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.
  2. 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.”
  3. 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).