Give a table of your own that is already in 2NF but not in 3

Give a table of your own that is already in 2NF but not in 3NF, along with the functional

dependencies among the attributes of the table drawn as a diagram. Illustrate the process of

normalisation of your table to 3NF.

Solution

A table is in third normal form (3NF) if it satisfies the following conditions:

example,

In the above table , BOOK ID determines GENRE ID, and GENRE ID determines GENRE TYPE. Therefore, BOOK ID determines GENRE TYPE via GENRE ID and we have transitive functional dependency due to which this table does not satisfy 3NF.

Functional Dependencies:

BOOK ID -> GENRE ID;

GENRE ID-> GENRE TYPE;

To bring this table into 3NF, we split the table into 2 sub-tables as follows:

BOOK ID -> GENRE ID, PRICE;

GENRE ID -> GENRE TYPE;

Now, all non-key attributes are fully functional dependent only on the primary key. In table - BOOKS, both GENRE ID and PRICE are only dependent on BOOK ID. In table GENRE, GENRE TYPE is only dependent on GENRE ID.