3NF – Third normal form/ What is the 3nf example?/ What is a relation in 3nf?/ How do I get a 3nf?

A relation is in 3NF(What is the 3nf example) 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|What is the 3nf example|

within this blog post(What is the 3nf example), 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|What is the 3nf example?

  • 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 the FD diagram.

Try to avoid this transitivity by using the steps given above.

FD-diagram-in-3NF
FD-Diagram in 3NF

In our example given in the above fig. we can say that in relation S

S#->City
City->Status
S#-> Status

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 determines 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.

Relation SC

S#City
S1Paris
S2London
S3Italy
S4India
S5Paris

Relation CS

CityStatus
Paris10
London20
Italy30
India40

Relation SP

S#P#Qty
S1P1300
S1P2100
S2P3500
S3P4200
S3P1450
S4P5400
S5P2250

Relation P

P#Pname`Color
P1BulbPink
P2TubesYellow
P3FanGreen
P4CoolerBlack
P5LampWhite

The advantage of 3NF is that we can solve the problem that we were facing in 2NF. The solution is explained given below.

Insert:

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.

Delete:

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.

Update:

If we want to change the status of any city then we to just update one tuple in relation to 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 to CS. Even now the problem is not fully solved. For this, we go for further normalization.

Check out some related blog post of Normalization:

What is Normalization and why is it needed?

What is 1NF in DBMS?

What is 2NF in DBMS?

What is BCNF in DBMS?

Conclusion:

The 3NF(What is the 3nf example) 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 relations. 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|What is the 3nf example|

Hope! you would have enjoyed this blog post(What is the 3nf example). Please feel free to write to us at [email protected] if you have any doubts about this topic of 3NF. Have a great time!

Anurag

I am a blogger by passion, a software engineer by profession, a singer by consideration and rest of things that I do is for my destination.