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

Loading in 2 Seconds...

play fullscreen
1 / 63

Relational Database Systems - PowerPoint PPT Presentation


  • 335 Views
  • Uploaded on

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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - 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)