Second Normal Form (2NF)

A table is said to be in 2NF when it is in 1NF and every attribute in the row is functionally dependent upon the whole key, and not just part of the key.

Consider the PROJECT table .

The table has the following rows :

This situation could lead to the following problems :

Insertion : The department of a particular employee cannot be recorded until the employee is assigned a project.

Updation : For a given employee, the employee code, department name and department head are repeated several times. Hence, if an employee is transferred to another department, this change will have to be recorded in every row of the EMPLOYEE table pertaining to that employee. Any omission will lead to inconsistencies.

Deletion : When an employee completes work on a project, the employee’s record is deleted. The  information regarding  the department to which the employee belongs will also be lost.

The primary key here is composite (ECODE + PROJCODE)

The table satisfies the definition of 1NF. You need to now check if it satisfies 2NF.

In the table, for each value of ECODE, there is more than one value of HOURS. For example, for ECODE, E101, there are three values of HOURS -90, 101   and 60.Hence, HOURS is not functionally dependent  on ECODE. Similarly, for each value of PROJCODE, there is more than one value of HOURS. For example, for PROJCODE P27, there are three values of HOURS- 90, 10 AND 72. However , for a combination of the ECODE and PROJCODE values, there is exactly one value of HOURS. Hence, HOURS is functionally dependent on the whole key, ECODE+PROJCODE.

Now, you  must check if DEPT is functionally dependent on the whole key, ECODE+PROJCODE. For each value of  ECODE, there is exactly one value of DEPT. For example, for ECODE 101, there is exactly one value, the System department. Hence, DEPT is functionally dependent on ECODE. However, for each value of PROJCODE, there is more than one value of DEPT. For example, PROJCODE P27 is associated with two values of DEPT. System and Finance. Hence, DEPT is not functionally dependent on PROJCODE. DEPT is, therefore, functionally dependent on part of the key (which is ECODE) and not functionally dependent on the whole key( ECODE+PROJCODE) Similar dependency is true for the DEPTHEAD attribute. Therefore, the table  PROJECT is not in 2NF. For the table to be in 2NF, the non-key attributes must be functionally dependent on the whole key and not part of the key.