SQL -Structured Query Language - Normalization - 3 NF Tutorial

• The table must be in 2 NF.
• And it should not contain any transitive functional dependency.

Now, what is a transitive dependency ( transitive functional dependency )?

In transitive dependency, if any of the non-key column values have been modified, then it will also indirectly force to change the other non-key column value in the same table.

In this example

The salutation of Reena will automatically be changed to Mrs. by guessing the gender i.e “ F “.

And Salutation of John will automatically change to Mr. by guessing the gender i.e “ M “.

Hence this type of thing we have to eliminate in 3 NF.

To maintain 3 NF

We have again divided larger table 1 into a smaller table 3, to eliminate transitive functional dependency.

In table 3, we separately store the Salutation of users based on their Gender.

Here Gender in table 1 is the foreign key and Gender in table 3 is the primary key.