1 / 15

ER modelling

ER modelling. Practice. Starter. What are the four degrees of relationship? o ne to one (1:1) one to many (1:n) many to one (n:1) many to many ( n:n ) Is there a problem implementing any of these relationships? many to many relationships have to be broken down. The Chocolate Factory…….

seth-barr
Download Presentation

ER modelling

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. ER modelling Practice

  2. Starter • What are the four degrees of relationship? • one to one (1:1) • one to many (1:n) • many to one (n:1) • many to many (n:n) • Is there a problem implementing any of these relationships? • many to many relationships have to be broken down

  3. The Chocolate Factory……. Wants to store details of all it’s chocolate bars and the ingredients required to make them. • Write a list of possible Entities and Attributes that could be used in a database • Construct an ER Diagram to represent the relationships involved.

  4. Entity Choc Bar Ingredient Attribute Name Price Weight Stock level Name Quantity required Price/Kg Stock level Reorder level Possible Solution

  5. CHOC BAR INGREDIENT Possible ER Diagram Choc bar contains many ingredients. CHOC BAR Need to resolve this many to many relationship BAR/ INGREDIENT Each ingredient can be included in many choc bars INGREDIENT

  6. The online DVD store…… Wants to store details of it’s DVDs and its customers. The store wants to record which customers buy which DVDs so it can make recommendations • Write a list of possible Entities and Attributes that could be used in a database • Construct an ER Diagram to represent the relationships involved

  7. Possible Solution • Entity • Customer • DVD • Attribute • Name • Address • email • preferred genre • Name • Duration • Genre • Price

  8. Customer DVD Title possible ER Diagram Customer may purchase many DVDs Customer Need to resolve this many to many relationship DVD Copy Each DVD title can be bought by many customers DVD Title

  9. The Hospital……. A hospital is organised into wards Each ward is staffed by one or more nurses Each patient is assigned to a single ward and is under the care of a single consultant A consultant will see many patients • Write a list of possible Entities and Attributes that could be used in the database • Construct an ER Diagram to represent the relationships involved

  10. Possible solution • Attribute • Name • Number of beds • Name • Address • Name • DOB • Blood group • Name • Specialism • Entity • WARD • NURSE • PATIENT • CONSULTANT

  11. Possible ER Diagram A ward can hold one or more patients and is staffed by one or more nurses A nurse works on one ward is staffed by WARD NURSE holds sees PATIENT CONSULTANT A Consultant will see many Patients A patient is assigned to one ward and is seen by one consultant

  12. The college……. A college employs teachers to deliver subjects Each subject may be chosen by one or more students Each student may study up to 4 subjects A teacher may teach one or more subjects • Construct an ER Diagram to represent the relationships involved

  13. Possible ER Diagram Stage One STUDENT SUBJECT TEACHER

  14. Possible ER Diagram Stage two STUDENT SUBJECT TEACHER

  15. Possible ER Diagram Stage three STUDENT SUBJECT STUDENT SUBJECT SUBJECT TEACHER TEACHER

More Related