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.