Normalisation

1. Normalisation Mia’s Sandwich Shop The Process Explained

2. Un-normalised Form Order_Number Date of Order Company_No Company_Name Customer_Name Company_Address Postcode Sandwich_Code Sandwich_Description Quantity Price Total_Price To place into 1st Normal Form, those attributes which could hold more than 1 value must be separated from the others.

3. Creating1st Normal Form Order_Number Date of Order Company_No Company_Name Customer_Name Company_Address Postcode Total_Price Now identify an attribute which can be used as a unique identifier – the Primary Key Sandwich_Code Sandwich_Description Quantity Price

4. Keys in 1st Normal Form Order_Number Date of Order Company_No Company_Name Customer_Name Company_Address Postcode Total_Price Orders are made up of items so to create the link insert a copy of Order_Number next to Sandwich_Code Sandwich_Code Sandwich_Description Quantity Price

5. 1st Normal Form Order_Number Date of Order Company_No Company_Name Customer_Name Company_Address Postcode Total_Price This combination of Primary keys forms a Composite key Order_Number Sandwich_Code Sandwich_Description Quantity Price There are no repeating groups and all attributes depend on keys

6. Creating 2nd Normal Form 2NF is only concerned with attributes containing more than 1 key – therefore the Order attributes are already in 2NF Order_Number Date of Order Company_No Company_Name Customer_Name Company_Address Postcode Total_Price Check that each non key attribute requires all parts of the key to uniquely identify it. Remove those attributes which do not require all parts of the key. Order_Number Sandwich_Code Sandwich_Description Quantity Price

7. Creating 2nd Normal Form Order_Number Date of Order Company_No Company_Name Customer_Name Company_Address Postcode Total_Price Order_Number Sandwich_Code Sandwich_Description Quantity Price Sandwich_Description and Price are only dependent on the Sandwich_code, not the Order_Number

8. Creating 2nd Normal Form Order_Number Date of Order Company_No Company_Name Customer_Name Company_Address Postcode Total_Price Order_Number Sandwich_Code Quantity Sandwich_Description Price Create a Primary key for these attributes by copying the key from the attribute set this new group was formed from

9. 2nd Normal Form Order_Number Date of Order Company_No Company_Name Customer_Name Company_Address Postcode Total_Price There are no Part Key Dependencies Order_Number Sandwich_Code Quantity Sandwich_Code Sandwich_Description Price

10. Creating 3rd Normal Form Order_Number Date of Order Company_No Company_Name Customer_Name Company_Address Postcode Total_Price All non key attributes must depend only on the key and not on each other Where these non key or transitive dependencies are found the attributes must be removed and a new entity created Order_Number Sandwich_Code Quantity Sandwich_Code Sandwich_Description Price

11. Creating 3rd Normal Form Order_Number Date of Order Company_No Company_Name Customer_Name Company_Address Postcode Total_Price It is not possible to uniquely identify a company using the Order_Number . This is dependent only on the Company_No It is possible to determine the date of the order and total price details from the Order_Number. Order_Number Sandwich_Code Quantity It is not possible to uniquely identify the Quantity ordered of a particular item by the Sandwich_code alone. This is dependent on the combined keys of Order_Number and Sandwich_Code. Sandwich_Code Sandwich_Description Price It is possible to identify the Sandwich_Description & Price from the Sandwich_Code

12. Creating 3rd Normal Form Order_Number Date of Order Total_Price Now create a Primary key for the new table and link it back to the original . Company_No Company_Name Customer_Name Company_Address Postcode Order_Number Sandwich_Code Quantity Sandwich_Code Sandwich_Description Price

13. Creating 3rd Normal Form Order_Number Date of Order Total_Price Company_No Company_No now becomes the primary key for the company entity. However we need to leave the attribute Company_No in the Order entity to provide the link back. Company_No Company_Name Customer_Name Company_Address Postcode Order_Number Sandwich_Code Quantity Now create some meaningful names for these entities and show the relationships to complete the Entity Relationship Diagram Sandwich_Code Sandwich_Description Price

14. 3rd Normal Form Company Orders Sandwiches Sandwiches Ordered