A relation is in 3NF or third normal form if and only if it is in 2NF and every non-key attribute is non-transitively dependent 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.
within this blog post, we are going to cover What is 3nf example? What is 1nf 2nf 3nf? What is a relation in 3nf? How do I get a 3nf? 1NF, 2NF, 3NF, BCNF vs 3NF, 1NF, 2NF, 3NF with example PDF, Transitive dependency 3NF, 1st, 2nd, and 3rd normal form examples, Normalization, Step by step normalization example.
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.
Check out some related blog post of 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 to a certain extent. Though it is never possible to solve all the problems as it is quite difficult to finish the redundancy completely.
We have gone through What is 3nf example? What is 1nf 2nf 3nf? What is a relation in 3nf? How do I get a 3nf? 1NF, 2NF, 3NF, BCNF vs 3NF, 1NF, 2NF, 3NF with example PDF, Transitive dependency 3NF, 1st, 2nd, and 3rd normal form examples, Normalization, Step by step normalization example.
Hope! you would have enjoyed this blog post. Please feel free to write to us at [email protected] if you have any doubts about this topic of 3NF. Have a great time!