Normalizing the Data › Normal Forms of Data › Second Normal Form

Second Normal Form

A data table is in second normal form if it is in first normal form and its entire primary key determines the values of each of its attributes. When a table is in second normal form, each of the attributes is dependent on the whole key and not any part of the key.

Table in first normal form

The POSITION table shown below is in first normal form but not in second normal form:

                 POSITION

EMP ID  JOB ID   EMP NAME  SALARY GRADE     SALARY
1216    ADM      SMITH      18              15000
1041    MGR      JONES      30              30000
1633    INST     DAVIS      23              22000
1063    ADM      EVANS      18              18000

In the POSITION table shown above, the primary key is the concatenation of EMP ID and JOB ID. This table is not in second normal form because some of the non-key attributes are dependent on a part of the primary key. For example, the EMP NAME attribute is dependent on only EMP ID, while the SALARY GRADE attribute is dependent only on JOB ID.

Table in first and second normal forms

The following table is in both first and second normal forms:

        POSITION
EMP ID  JOB ID     SALARY
1216    ADM        15000
1041    MGR        30000
1633    INST       22000
1063    ADM        18000

In the POSITION table shown above, the primary key is the concatenation of EMP ID and JOB ID. The POSITION table is in first normal form because it contains no repeating groups. It is in second normal form because the non-key attribute SALARY is dependent on the entire primary key (the concatenation of EMP ID and JOB ID). If a user knows an EMP ID value and a JOB ID value, the user can easily find out the SALARY for an employee who works in a particular job.