Database Normalisation

An un-normalised database is a database that has not undergone the process to remove unnecessary or repeating information from the system's tables. Normalisation will remove this data providing a more efficient database requiring less storage.

To use Normalisation to create a Relational Database you need to start with the data in one large (un-normalised) table.

Overview

https://www.youtube.com/watch?v=1YMdx97o1U8&list=PLCiOXwirraUDUYF_qDYcZV8Hce8dsE_Ho&index=164

The Process

Step 1

Remove repeating groups to give 1NF

This will involve removing this data into its own table, however you must remember to also include the primary key of the original table into a composite key for the new table.

Step 2

Remove partial dependencies to give 2NF

Using only the tables with a composite key, does each field in that table rely on both of the keys or just one. If it only depends on one it is called a partial dependency, you will need to remove the partially dependent items into a new table and you should use the partially dependent key for the primary key of the new table. In the original table the partial key will now be a foreign key.

Step 3

Remove non-key dependencies to give 3NF

Using each table in turn, does each field relate to the primary key. If it doesn't you should create a new table using the non-key dependant data, then you should use the new table primary key as a foreign key in the original table.

The Outcome

The final normalized table will have no repeating groups and will have all the tables in the database linked via common keys.

The will allow the database to retain data integrity during updates and deletions. Without normalisation each copy of the data will also need to be updated or deleted.

It is possible to enforce referential integrity (there must be an entry in the primary table in order to use the value as a foreign key in the other). For example you will be prevented from adding a customer order for a customer ID no in use.

It will also be faster to sort and search, normalisation produces smaller tables with fewer fields and therefore less data is involved.

It will prevent some deletions, to maintain referential integrity, you will be prevented from removing data from a primary table if the entries are used as a foreign key in another table. For example you couldn't delete a customer ID from a database if orders exist for that customer ID.