Relational Database Systems

1. Relational Database Systems Bit and Bobs Worked Example

2. Normalisation

3. Un-normalised Form (UNF) • Identify an entity • List all the attributes • Identify a key

4. Un-normalised Form (UNF) • Identify repeating data items

5. Un-normalised Form (UNF) • Identify repeating data items

6. First Normal Form (1NF) • Remove repeating data items to form a new entity • Take the key with you!

7. First Normal Form (1NF) • Remove repeating data items to form a new entity • Take the key with you!

8. First Normal Form (1NF) • Identify a key for the new entity • It will be a compound key • Use the original key and add to it

9. First Normal Form (1NF) • Identify a key for the new entity • It will be a compound key • Use the original key and add to it • Label the foreign key • Order Number is both part of the compound primary key and also a foreign key.

10. First Normal Form (1NF) • A data model is in 1NF if it has no multi-valued attributes

11. First Normal Form (1NF)

12. First Normal Form (1NF) • But what if there were lots of orders for large deluxe red widgets…? • There are still update anomalies

13. Second Normal Form (2NF) • Examine any entity with a compound key (in this case ORDER_ITEM) • See if any attributes are dependent on just one part of the compound key • These are called partial dependencies

14. Second Normal Form (2NF) • Order Number is part of the key • Item Code is part of the key • Description is dependent on the Item Code • Unit Cost is dependent on the Item Code • Quantity is dependent on both Order Number and Item Code.

15. Second Normal Form (2NF) • Description and Unit Cost are partial dependencies • They are dependent on Item Code • Remove these attributes to a new entity • Take a copy of the attribute they are dependent on

16. Second Normal Form (2NF) • Item Code becomes the key of the new entity • And becomes a foreign key in ORDER-ITEM

17. Second Normal Form (2NF) • A data model is in 2NF if it is in 1NF and there are no partial dependencies

18. Second Normal Form (2NF) • We can add an item to the Item table without it having to be on an order • We can delete an order in the Order table without deleting details of the items on the order • We can update item details once in the Item table without affecting the orders for that item in the Order-Item table

19. Second Normal Form (2NF) • But there are still update anomalies with the Order entity

20. Third Normal Form (3NF) • Examine all the entities produced so far • See if there are any non-key attributes which are dependent on any other non-key attributes • These are called non-key dependencies

21. Third Normal Form (3NF) • In the ORDER entity, Customer Name, Address, Post Code and Telephone Number are all dependent on Customer Number

22. Third Normal Form (3NF) • Remove these attributes to a new entity

23. Third Normal Form (3NF) • Remove these attributes to a new entity • Customer Number is the key of the new entity • Leave Customer Number behind as a foreign key

24. Third Normal Form (3NF) • A data model is in 3NF if it is in 2NF and there are no non-key dependencies

25. Third Normal Form (3NF) • We can add a customer to the Customer table without the customer having to place an order • We can delete an order in the Order table without deleting details of the customer who placed the order • We can update a customer’s details once in the Customer table without affecting the orders placed by that customer in the Order table

26. Memory Aid • In 3NF, each attribute is dependent on • the key • the whole key • and nothing but the key