SECOND NORMAL FORM (2NF) - IBM Mainframe

A relation is in second normal form if it is in first normal form and every non-key attribute is fully and functionally dependent on the primary key. Thus non-key attribute is functionally dependent on the primary key. A relation in the first normal form will be in the second normal form if one of the following conditions is satisfied:

  • The primary key consists of only one attribute (field or column).
  • No non-key attributes exist in the relation. In other words, all the attributes in the relation are components of the primary key.
  • Every non-key attribute is functionally dependent on the full set of primary key attributes.

Consider a relation called EMPLOYEE with the attributes EmpJD, Department, F_Name, L_Name, Salary and Date-of-birth. Here assume that the primary key for this relation is the composite key EmpJD + Department. In this case the non-key attributes such as F_Name, L_Name, Salary and Date-of-birth are functionally dependent on part of the primary key (EmpJD) but not on the Department. A partial functional dependency is a functional dependency in which one or more non-key attributes (such as F_Name, L_Name, Salary or Date-of-birth) are functionally dependent on part (but not all) of the primary key. The partial functional dependency in the EMPLOYEE table creates a redundancy in that relation, which results in anomalies when the table is updated.

EMPLOYEE-DEPARTMENT Relationship

EMPLOYEE-DEPARTMENT Relationship

Redundant data is data that is unnecessarily expressed multiple times or that depends only on part of a multi-value key. In other words, when a column's value is dependent on the value of one column in the table, but not another, it is considered redundant. For example:

SECOND NORMAL FORM (2NF)

This table contains redundant data, namely the department description, which depends only on the DEPTCODE and does not vary based on the value of the EMP_NO, which is the primary key of the table. So by storing the department code and description in a different table the redundancy is eliminated.

create table employee


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

IBM Mainframe Topics