150 likes | 221 Views
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…….
E N D
ER modelling Practice
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
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.
Entity Choc Bar Ingredient Attribute Name Price Weight Stock level Name Quantity required Price/Kg Stock level Reorder level Possible Solution
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
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
Possible Solution • Entity • Customer • DVD • Attribute • Name • Address • email • preferred genre • Name • Duration • Genre • Price
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
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
Possible solution • Attribute • Name • Number of beds • Name • Address • Name • DOB • Blood group • Name • Specialism • Entity • WARD • NURSE • PATIENT • CONSULTANT
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
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
Possible ER Diagram Stage One STUDENT SUBJECT TEACHER
Possible ER Diagram Stage two STUDENT SUBJECT TEACHER
Possible ER Diagram Stage three STUDENT SUBJECT STUDENT SUBJECT SUBJECT TEACHER TEACHER