Download
relational database systems n.
Skip this Video
Loading SlideShow in 5 Seconds..
Relational Database Systems PowerPoint Presentation
Download Presentation
Relational Database Systems

Relational Database Systems

62 Vues Download Presentation
Télécharger la présentation

Relational Database Systems

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  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)