A relation is in third normal form if it is in second normal form and no transitive dependencies exist. A transitive dependency in a relation is a functional dependency between two or more non-key attributes. The columns in each table should be a group of columns in which the data in each column contributes to the description of each row in the table. For given row with a unique key, each column appearing in that row should contribute to the description of that row. For example in the following table, the columns F_NAME and LNAME contribute to describing a specific contact using the primary key CONTACTID. But, the COMPANY_NAME and COMPANY LOCATION do not contribute to describing the record with a given CONTACTJD, since it identifies an individual and not a company.


THIRD NORMAL FORM (3NF)In the above relation CONTACTJD is the primary key, so that all the remaining attributes are functionally dependent on this attribute. However there is a transitive dependency—COMPANYLOCATION is dependent on COMPANYJJAME and COMPANYJMAME is functionally dependent on CONTACTJD. So unless the location of the company differs on an individual basis, this column in not dependent on the key value and should be removed to another table. Here one thing that should be noted is that, as a result of the transitive dependency, there are update anomalies in the CONTACTS table as follows:

  • Insertion anomaly - A new company cannot be inserted to the CONTACTS table until a contact person has been assigned to that company.
  • Deletion anomaly - If a company that has only one contact person is deleted from the table, we will lose the information about that company, as the company information is associated with that person.
  • Modification anomaly - If a company changes its location we will have to make the change in all the records where the company name appears. Suppose, if the company has five contact persons, then we will have to make the changes in five places.

The above anomalies arise as a result of the transitive dependency. The transitive dependency can be removed by decomposing the above table into two

create table contacts

When all the columns in a table describe and depend upon the primary key, the table is said to satisfy the third normal form.



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

IBM Mainframe Topics