Databases: Normalisation

You need to know three stages of Normalisation, a basic outline of the three stages is shown in the image below.

Normalisation is used to keep a database as efficient as it can possibly be without any unnecessary duplication and no redundant data. When working with large volumes of data this can cause a lot of space and time to be saved as it's less data to process.

1NF - Atomic Data Test

If a table has a primary key it is said to be in First Normal form if the table does not have repeating groups of attributes. All attributes within the table need to be dependent only on the primary key.

2NF - Partial Dependence Test

For a table to be in Second Normal form it must first be in First Normal Form (1NF) and then contain no data that is dependent on only part of the primary key.

3NF - Non-Key Dependence Test

For a table to be in Third Normal Form (3NF) it must be in Second Normal form and contain No data that is not dependent on the primary key.
e.g. (Remove columns that are not dependent upon the primary key.)

Example

To illustrate this let's look at a product ordering system. Initially our database looks like this:

OrderNumber	CustomerID	CustomerName	CustomerAddress	ProductID	ProductName	Quantity	PriceEach
1	3	John Smith	8 Yewbeam Avenue, Cambridge, CB2 1QY	43	Basket	1	7.80
				32	Box	4	6.50
2	5	Fred Roberts	64 Bone Street, Ipswich, IP2 4DF	32	Box	2	6.50
				54	Chair	4	30.50
				12	Desk	1	45.60
3	9	George Jones	78 Church Street, Sheffield, S4 6GF	43	Basket	3	7.80
				15	Table	1	35.70

This is known as un-normalised form, UNF or 0NF.

To change this into 1NF we need to take out duplicated data. To do this we create a new table to hold the products and move the product data to this table along with the primary key which is the OrderNumber.

OrderNumber	ProductID	ProductName	Quantity	PriceEach
1	43	Basket	1	7.80
1	32	Box	4	6.50
2	32	Box	2	6.50
2	54	Chair	4	30.50
2	12	Desk	1	45.60
3	43	Basket	3	7.80
3	15	Table	1	35.70

Every row is not now unique so we create a composite primary key, made up of the OrderNumber column and the ProductID column.

To put our table into 2NF we need to separate out any data that is only dependent on one part of the primary key. This would be the ProductName and PriceEach as these only depend on the ProductID and not on the OrderNumber.

We now have two tables as follows:

ProductID	ProductName	PriceEach
43	Basket	7.80
32	Box	6.50
54	Chair	30.50
12	Desk	45.60
15	Table	35.70

OrderNumber	ProductID	Quantity
1	43	1
1	32	4
2	32	2
2	54	4
2	12	1
3	43	3
3	15	1

Finally, to reach 3NF, we must remove non-key attributes with depend on other non-key attributes in the same table. We can see that in the original table, there are attributes for the customer which depend on the CustomerID attribute, where CustomerName and CustomerAddress depend on CustomerID despite CustomerID not being a key in the table. Therefore, we must separate out our customer details into a separate table which has the primary key of CustomerID. We can then place CustomerID as a foreign key in the Order table.

Our final tables in third normal form are as follows:

OrderNumber	CustomerID
1	3
2	5
3	9

CustomerID	CustomerName	CustomerAddress
3	John Smith	8 Yewbeam Avenue, Cambridge, CB2 1QY
5	Fred Roberts	64 Bone Street, Ipswich, IP2 4DF
9	George Jones	78 Church Street, Sheffield, S4 6GF

ProductID	ProductName	PriceEach
43	Basket	7.80
32	Box	6.50
54	Chair	30.50
12	Desk	45.60
15	Table	35.70

OrderNumber	ProductID	Quantity
1	43	1
1	32	4
2	32	2
2	54	4
2	12	1
3	43	3
3	15	1

What is meant by Third Normal Form?

Answer:

1. (relation) contains no repeating groups of attributes /or/ data is atomic
2. no non-key dependancies.

Why is it important that the relations in a database are in Third Normal Form?

Answer:

• Eliminates update anomalies
• Eliminates insertion anomalies
• Eliminates deletion anomalies
• minimises data duplicates
• Eliminate data redundancy
• Improves consistency