Second Normal Form (2NF)

Process of transforming table from 1NF to 2NF is:

  • Identifying determinants that are part of the table primary key.
  • Removing partial dependencies to a new table.
  • Determining the primary key of the new table.

Identifying Determinants That Are Part of PK

If we have in some table two attributes for example A and B and if for a given value for A we have only one possible value for B, then we say that attribute A is determinant for attribute B.

For process of transforming tables from 1NF to 2NF, we look only for determinants that are part of primary key. Also, tables with simple primary keys (not compound keys) or with no-key attributes should be ignored. That mean that our Project table is already in 2NF.

Process of identifying determinants that are part of PK is:

  • Take one non-key attribute of table and see if it is dependent on one part of the primary key?
  • If answer to previous question is yes then we got one determinant.
  • If answer to previous question is no then check against other part of primary key.
  • Repeat this process for every non-key attribute.

Taking this procedure, we can identify determinants in Project_Employee table and show this as in next figure. Determinants of Project_Employee Table

Employee Name, Department No and Department Name are dependent upon Employee No only. However, Hourly Rate is dependent upon both Project Code and Employee No as an employee may have a different hourly rate depending upon which project he is working on.

Removing Partial Dependencies to a New Table

Partial dependencies are attributes that are dependent on part of the primary key from previous step. In previous figure, these are: Employee Name, Department No and Department Name. we remove these attributes in new table that we call Employee. In order to have a cross-reference between old and new tables we must also copy determinant to the new table. Employee table also contain Employee No attribute from Project_Employee table.

Determining the Primary Key of the New Table

The determinant from previous step becomes a primary key of new table. Next figure shows tables in Second Normal Form. second Normal Form