Converting to Third Normal Form (3NF) in databases
In the previous section we discussed how to convert to 2NF. Now let’s look at converting to 3NF:
Converting to 3NF
This is about removing the transitive dependencies by identifying the determinant of each transitive dependency, which then becomes a primary key of a new table and then assigning the appropriate attributes. An appropriate name of the new table needs to be chosen as well. BUT, the dependent attributes need to be removed from the original table, but the determinants remain and become foreign keys.
Remember the table that we have been using to demonstrate normalization:
To convert to 3NF:
- Identify the determinants for each transitive dependency and assigning the corresponding attributes. The determinant will be the TillType_id in this example. This then becomes the primary key of a new table.
- Write out the entire composite primary key identified in 1NF (Customer_id and Teller_id).
- The other tables that have already been normalized to proper functional dependencies remain:
All the above tables in 3NF are in desirable states as they do not have both the partial and the transitive dependencies. The FKs represent foreign keys, and are the ones that make the relationships between tables.
After normalizing our table to 3NF, we end up with four tables instead of one. This way, it is easier to make different changes on each of the tables — think deletion anomaly. We can easily delete Teller with Teller_id T01 without losing all the other data.
Congratulations! You have learnt how to convert to 3NF.
