Entity/Relationship Modelling

1. Entity/Relationship Modelling Stanislava Armstrong http://www.cs.nott.ac.uk/~saw/teaching/G64DBS/lecture_slides.html Entity Relationship Modelling

2. The Relational Model – Quick Reminder Entity Relationship Modelling

3. Database Design • Before we look at how to create and use a database we’ll look at how to design one • Need to consider • What tables, keys, and constraints are needed? • What is the database going to be used for? • Conceptual design • Build a model independent of the choice of DBMS • Logical design • Create the database in a given DBMS • Physical design • How the database is stored in hardware Entity Relationship Modelling

4. Entity/Relationship Modelling • E/R Modelling is used for conceptual design • Entities - objects or items of interest • Attributes - facts about, or properties of, an entity • Relationships - links between entities Example: In the database for the kingdom of Kod we could have entities for farms, fruit and other kingdoms. Fruit might have attributes such as variety, price and available quantity and could have relationships with farms (produced on) and kingdoms (purchased by). Entity Relationship Modelling

5. Entity/Relationship Diagrams • E/R Models are often represented as E/R diagrams that • Give a conceptual view of the database • Are also independent of the choice of DBMS • Can identify some problems in a design Farm Price Variety Quantity Produced on Fruit Purchased by Kingdoms Entity Relationship Modelling

6. Entities • Entities represent objects or things of interest • Physical things like fruit, students, lecturers, employees, products • More abstract things like kingdoms, modules, orders, courses, projects • Entities have • A general type or class, such as Fruit or Kingdom • Instances of that particular type, such as blackberry or banana are instances of Fruit • Attributes (such as variety and price) Entity Relationship Modelling

7. Diagramming Entities • In an E/R Diagram, an entity is usually drawn as a box with rounded corners • The box is labelled with the name of the class of objects represented by that entity Farm Price Variety Quantity Produced on Fruit Purchased by Kingdoms Entity Relationship Modelling

8. Attributes • Attributes are facts, aspects, properties, or details about an entity • Fruits have IDs, variety, sugar contents, … • Kingdoms have kings, quantity requirements, locations, … • Attributes have • A name • An associated entity • Domains of possible values • Values from the domain for each instance of the entity they are belong to Entity Relationship Modelling

9. Diagramming Attributes • In an E/R Diagram attributes may be drawn as ovals • Each attribute is linked to its entity by a line • The name of the attribute is written in the oval Farm Price Variety Quantity Produced on Fruit Purchased by Kingdoms Entity Relationship Modelling

10. Relationships • Relationships are an association between two or more entities • Each kingdom purchases several different varieties of fruit • Each farm produces a single variety of fruit • Relationships have • A name • A set of entities that participate in them • A degree - the number of entities that participate (most have degree 2) • A cardinality ratio Entity Relationship Modelling

11. Cardinality Ratios • Each entity in a relationship can participate in zero, one, or more than one instances of that relationship • This leads to 3 types of relationship… • One to one (1:1) • Each fruit variety is produced on only one farm and each farm produces only one variety of fruit • One to many (1:M) • A fruit specialist may tutor many beginner gardeners, but each beginner has just one tutor • Many to many (M:M) • Each fruit variety can be purchased by a number of different kingdoms and each kingdom can purchase a number of different fruit varieties Entity Relationship Modelling

12. Diagramming Relationships • Relationships are links between two entities • The name is given in a diamond box • The ends of the link show cardinality Farm Price Variety Quantity Produced on Fruit Purchased by Kingdoms One Many Entity Relationship Modelling

13. Removing M:M Relationships Fruit Fruit Is Purchased by Purchase for Kingdoms Kingdoms • Many to many relationships are difficult to represent in a database • We can split a many to many relationship into two one to many relationships • An entity represents the M:M relationship Entity Relationship Modelling

14. Example Farm Farm Price Price Variety Quantity Variety Quantity Produced on Produced on Fruit Fruit Is Purchased by Kingdoms Kingdoms for Purchase Entity Relationship Modelling

15. One to One Relationships • Somerelationships between entities, A and B, might be redundant if • It is a 1:1 relationship between A and B • Every A is related to a B and every B is related to an A • Example - the farm-fruit relationship • Is one to one • Every variety of fruit is produced on a farm • We don’t need farms that are not related to the fruit production Entity Relationship Modelling

16. Redundant Relationships a x AB b y c z • We can merge the two entities that take part in a redundant relationship together • They become a single entity • The new entity has all the attributes of the old one a x A B b y c z Entity Relationship Modelling

17. Example Farm Price Manager Variety Quantity Name Produced on Fruit Is Purchase Quantity for Price Fruit Is Purchase Kingdoms Variety for Name Manager Kingdoms Entity Relationship Modelling

18. Making E/R Models To make an E/R model you need to identify • Entities • Attributes • Relationships • Cardinality ratios from a description General guidelines • Since entities are things or objects they are often nouns in the description • Attributes are facts or properties, and so are often nouns also • Verbs often describe relationships between entities Entity Relationship Modelling

19. Making E/R Diagrams • From the description of the requirements identify the • Entities • Attributes • Relationships • Cardinality ratios of the relationships • Draw the E/R diagram and then • Look at one to one relationships as they might be redundant • Look at many to many relationships as they might need to be split into two one to many links Entity Relationship Modelling

20. Entities and Attributes • Sometimes it is hard to tell if something should be an entity or an attribute • They both represent objects or facts about the world • They are both often represented by nouns in descriptions • General guidelines • Entities can have attributes but attributes have no smaller parts • Entities can have relationships between them, but an attribute belongs to a single entity Entity Relationship Modelling

21. Exercise A tour operator has a number of available destinations. Each destination has a location, associated holiday price and a description of local attractions. The tour operator only offers a choice of one hotel per destination. Every hotel has an address, manager and maximum number of rooms, which can be booked. The tour operator also keeps track of its clients, their name, address and telephone number. The client can have more than one holiday booked and several clients can book holidays to the same destination. • Identify the entities, attributes, relationships and cardinality ratios for the problem above. • Draw an E/R diagram for the problem. • Remove any one to one and many to many relationships if such exist. Entity Relationship Modelling

22. Debugging Designs Fruit Is Purchase for Kingdoms • With a bit of practice E/R diagrams can be used to plan queries • You can look at the diagram and figure out how to find useful information • If you can’t find the information you need, you may need to change the design How can you find a list of fruits purchased by the kingdom of Aphrodite? Entity Relationship Modelling

23. Debugging Designs Fruit Is Purchase for Kingdoms ID (3) For each instance of Code in the result of (2) find the corresponding Fruit Variety ID (2) Find the IDs of the instances of the Purchase entity with the same Code as the result of (1) Code Code (1) Find the code for the instance of the Kingdom entity with Name ‘Aphrodite’ Name Entity Relationship Modelling

24. Exercise Destination takes Bookings By Clients How can you find the list of people who are going on a holiday to India? ID location ID Code Code Name Entity Relationship Modelling

25. Reading Material • The Manga Guide to Databases – chapters 3 • Database Systems – A Practical Approach to Design, Implementation and Management by Connolly and Begg – chapters 11 and 12 Entity Relationship Modelling