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.