Page 142 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Unit 8: Normalization

8.4 Third Normal Form Notes

A table the Second Normal form may show redundancies due to transitive dependencies.
The Third normal form resolves transitive dependencies. A transitive dependency arises when
a non-key column is functionally dependent on another non-key column that in turn is
functionally dependent on the primary key.
The Third Normal Form states that:

1. The table should be in 2nd Normal Form
2. The table should be devoid of transitive dependencies

Notes Transitive Dependencies
Columns dependent on other columns that in turn are dependent on the primary key are
said to be transitively dependent.
Foreign Key: A foreign key is an attribute that completes a relationship by identifying the parent
entity. Foreign keys provide a method for maintaining integrity in the data.

The first rule states that a prerequisite for Third normal form is that the table first satisfies the
rules of the second normal form and in turn the first normal form.
A relation R is said to be in the third normal form (3NF) if and only if it is in 2NF and every non-
key attribute must be non-transitively dependent on the Primary key.
3NF ensures that none of the non-key attributes are dependent upon another attribute which in
turn is dependent on the primary key. Table shows the tables before and after third normal
form. The Accounts table in the second normal form has a transitive dependency as follows:

Acc_id Acc_type
Acc_type Min_bal
This transitive dependency is resolved by decomposing the Accounts table into two tables:
Acc_Detail and Product.
Tables before Third Normal Form

Customer_Accounts Table

Cust_id Name Address
001 Ravi Hyd
110 Tim Sec 'bad
420 Kavi Vizag

Customer Table

Cust.id Acc_id Balance
001 994 1500
110 340 6000
420 699 1500

LOVELY PROFESSIONAL UNIVERSITY 135