What is 3NF? Explain with Example.

A relation is in third normal from (3NF) if it is in 2NF and no transitive dependency exists. The transitive dependency is an important factor in normalization. Normalization removes transitive dependency from a relation in 2NF. A relation is not in 3NF if the value of a non-key attribute can be obtained by knowing the value of another non-key attribute.

Example:

Suppose the following relation SALES exists:

SALES (CUSTNO, NAME, SAlEMAN, REGION)

The attribute CUSTNO is used as primary key. Each salesman is assigned a unique region. The above relation contains the following functional dependencies:

CUSTNOà NAME, SALESMAN

SALESMAN à REGION

The above relation is in 2NF because the primary key consists of single attribute. A SALESMAN and SALESMAN is functionally dependent on CUSTNO. It means that REGION is transitively dependent on CUSTNO.

 CUSTNO NAME SALESMAN REGION 10 Ahsan Ahmad South 20 Babar Bashir West 30 Chauhan Ahmad South 40 Daood Khalid East 50 Ehtasham Bashir West 60 Farooq Munir North

Table 4.7: The SALES relation

The above relation has different anomalies as follows:

• Insertion anomaly: suppose a new salesman “Abid” joins the company and is assigned to North region. The record of new salesman cannot be entered until a customer is also assigned to him because the primary key of the relation is CUSTNO.
• Deletion Anomaly: Suppose the record of CUSTNO 40 is to be deleted. It will also delete the information of the salesman “Khalid” who is assigned to East.
• Modification Anomaly: Suppose the salesman “Ahmad” is reassigned to East region from South. It requires to update all records in which “Ahmad” appears.

Removing Transitive Dependency

Any transitive dependencies are removed to convert a relation from 2NF to 3NF. The above anomalies occur due to transitive dependency. The transitive dependency can be removed by decomposing the above relation into two relations as follows:

 CUSTNO NAME SALESMAN 10 Ahsan Ahmad 20 Babar Bashir 30 Chauhan Ahmad 40 Daood Khalid 50 Ehtasham Bashir 60 Farooq Munir

Table 4.8: The SALE1 relation

 SALESMAN REGION Ahmad South Bashir West Khalid East Munir North

Table 4.9: the SMAN relation

The above relations can be represented as follows:

SALES1 (CUSTNO, NAME, SALESMAN)

SMAN (SALESMAN, REGION)

Both relations are now in 3NF. There is no transitive dependency in these relations. The above-mentioned anomalies have also been removed. The determinant attribute in transitive dependency becomes the primary key in SMAN relation.