1. Relational Database Systems Higher Information Systems

2. The Relational Model • data is grouped into entities which are related, in order to minimise data duplication and achieve data integrity • many-to-many relationships between entities are removed and replaced with one-to-many relationships

3. Entity-Occurrence Modelling

4. Entity-Occurrence Modelling • Lines indicate howthe instances ofeach entity arelinked • E.g. Member 1034 has rented DVDs 002 and 015 • DVD 003 has been rented by members 1012 1056

5. Entity-Occurrence Modelling • Each DVD can berented by manyMembers • Each Member canrent many DVDs • So there is a many-to-many relationship between Member and DVD

6. Entity-Occurrence Modelling • This method isonly as good asthe available data • Make up “dummy”data if necessary tofill in the gaps

7. More about keys • An atomic key consists of one attribute • MEMBER(Member Number, Name, Telephone Number) • A compound key consists of two or more attributes • MEMBER(Member Number, Name, Telephone Number) • A surrogate key is a made up attribute designed to identify a record • Member Number is a surrogate key

8. Choosing a key • An atomic key is better than a compound key • A numeric attribute is better than a text attribute • KISS = Keep It Short and Simple • A key must have a value—it cannot be blank (or “null”) • A key should not change over time

9. The flat file revisited… • What is a suitable key? • DVD Code? • Member Number? • (DVD Code, Member Number)?

10. Update Anomalies • There is no way of storing the details of a member who hasn’t rented any DVDs • A value must be provided for both DVD Code and Member Number for the key • This is called an “insertion anomaly”

11. Update Anomalies • If a member’s details have to be amended, this must be done in each record with those details • This can lead to data inconsistency if there is an error or omission in making the change • This is called a “modification anomaly”

12. Update Anomalies • If a DVD is removed from the database, then it may also remove the only record of a member’s details • This is called a “deletion anomaly”

13. Update Anomalies • Insertion anomalies • Modification anomalies • Deletion anomalies • These are characteristics of poorly designed databases • The solution is to use a relational database • We use normalisation to help work out what tables are required and which data items should be stored in each table

14. Normalisation

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

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

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

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

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

20. 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

21. 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.

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

23. First Normal Form (1NF)

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

25. 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

26. 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.

27. 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

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

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

30. 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

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

32. 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

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

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

35. 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

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

37. 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

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

39. Entity-Relationship Diagram

40. Entity-Relationship Diagram • The foreign key is always at the “many” end of the relationship

41. Source documents

42. Source documents • List all the attributes which must be stored in the database

43. Source documents • List all the attributes which must be stored in the database • Identify a key

44. Source documents • There are two attributes called Title

45. Source documents • There are two attributes called Title • Member Number is the same as Member

46. Source documents • There are two attributes called Title • Member Number is the same as Member • Number or No?

47. Source documents • Tidy up UNF • Carry on as before to 3NF

48. Database Design • For each attribute you must decide • its name • its data type • its properties

49. Database Design • For each attribute you must decide • its name • Choose sensible and meaningful field names • Be consistent! • e.g. Number/Num/No/#

50. Database Design • For each attribute you must decide • its name • its data type • text (alphanumeric, string) • numeric (integer, real, currency) • date or time • Boolean (yes or no) • link • object (e.g. picture, sound, file)