1 / 63

Relational Database Systems

Relational Database Systems. Higher Information Systems. 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.

amie
Download Presentation

Relational Database Systems

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

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)

More Related