A relation is in 3NF if and only if it is in 2NF and every non-key attribute is non-transitively depend on the primary key. By the transitive functional dependency, we mean that If x(primary key) -> y and y->z then x->z.
So, z is transitively dependent on x. And according to the definition of 3NF, as such dependency should be there, that is a relation.
Steps for transforming the relation from 2NF to 3NF.
- Determine the non-key attributes that determine some other non-key attributes.
- Male separate relation taking the first one as a primary key.
In a simple language, we can say that we try to find attributes that show transitive dependency between them. this we can search from FD diagram.
Try to avoid this transitivity by using the steps given above.
In our example given in the above fig. we can say that in relation S
So, this shows transitive dependency, which we have to remove. Other relationship P and SP does not show any such dependency.
So, following steps1, we determine the non-key attribute that determine some other non-key attribute. A city that is a non-key attribute determines the status which is a non-key attribute. That is for any two equal values of the city, status definitely has equal values.
The relation can be shown as below.
The advantage of 3NF is that we can solve the problem that we were facing in 2NF. The solution is explained given below.
In relation CS, we can insert a tuple indicating that a particular city has a particular status. So, we can say that the supplier in Italy must have status 30.
On deleting any tuple from relation SC, we don’t lose information about the status of the city in which that supplier lives.
For example: if we delete the second relation from a tuple SC, then we also know that city London has status 20.
If we want to change the status of any city then we to just update one tuple in relation CS.
For Example: IF we wish to change the status of London from 20 to 50 then we have to just update the second tuple in relation CS. Even now the problem is not fully solved. For this, we go for further normalization.
The 3NF or third normal form is all about the introduction of the foreign key in our relation to reduce the redundancy. For solving the problem coming in 2NF we further break the data table to make some new relation. Here we solve the problem of insertion, deletion and updating up a certain extent. Though it is never possible to solve all the problems as it is quite difficult to finish the redundancy completely.
Hope! you would have enjoyed this blog post. Please feel free to write to us at firstname.lastname@example.org if you have any doubts about this topic of 3NF. Have a great time!