A-level Computing/AQA/Paper 2/Fundamentals of databases/Database design and normalisation techniques
1st Normal Form - Achieved by ensuring that the table does not contain any repeated attributes or groups and that the data is atomic (lowest level of detail)
2nd Normal Form - Achieved by first ensuring that the database is in 1NF (1st normal form) and that there are no attributes which depend on only part of the primary key, but not all of it. This is done by creating additional tables.
3rd Normal Form - Achieved by ensuring that the database is in 2NF and that all non key attributes which depend upon other non key attributes are removed. This is done by creating additional tables.
Databases are normalised to ensure they are structured efficiently.
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 (1NF) Form 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.)
To illustrate this let's look at a product ordering system. Initially our database looks like this:
| OrderNumber | CustomerName | CustomerAddress | ProductID | ProductName | Quantity | PriceEach |
|---|---|---|---|---|---|---|
| 1 | John Smith | 8 Yewbeam Avenue, Cambridge, CB2 1QY | 43 | Basket | 1 | 7.80 |
| 32 | Box | 4 | 6.50 | |||
| 2 | Fred Roberts | 64 Bone Street, Ipswich, IP2 4DF | 32 | Box | 2 | 6.50 |
| 54 | Chair | 4 | 30.50 | |||
| 12 | Desk | 1 | 45.60 | |||
| 3 | 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 |
And finally, to reach 3NF we must separate out our Customer details from our order table as CustomerAddress (a non-key attribute) is currently dependent on CustomerName (another non-key attribute). Our final, fully normalised tables are as follows:
| OrderNumber | CustomerName |
|---|---|
| 1 | John Smith |
| 2 | Fred Roberts |
| 3 | George Jones |
| CustomerName | CustomerAddress |
|---|---|
| John Smith | 8 Yewbeam Avenue, Cambridge, CB2 1QY |
| Fred Roberts | 64 Bone Street, Ipswich, IP2 4DF |
| 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 |
|
Exercise: Picking Tables What is meant by Third Normal Form? Answer:
Why is it important that the relations in a database are in Third Normal Form? Answer:
|