Third Normal Form (3NF)

DBMSDatabaseMySQL

What is 3NF?

The third step in Normalization is 3NF.

A table is in 3NF, only if a relation is in 2NF and it has no Transitive Functional Dependency

Let us see an example −

Example (Table violates 3NF)

<MovieListing>

Movie_ID
Listing_ID
Listing_Type
DVD_Price ($)
0089
007
Comedy
100
0090
003
Action
150
0091
007
Comedy
100


The above table is not in 3NF because it has a transitive functional dependency −

Movie_ID -> Listing_ID
Listing_ID -> Listing_Type

Therefore, Movie_ID -> Listing_Type i.e. transitive functional dependency.

Example (Table converted to 3NF)

To form it in 3NF, you need to split the tables and remove the transitive functional dependency.

<Movie>

Movie_ID
Listing_ID
DVD_Price ($)
0089
007
100
0090
003
150
0091
007
100

<Listing>

Listing_ID
Listing_Type
007
Comedy
003
Action
007
Comedy

Let us see the result again that is a table in 3NF now −

raja
Updated on 15-Jun-2020 13:34:18

Advertisements