IMAT1408 Database Design Concepts

1. IMAT1408Database Design Concepts Lecture 3 Introduction to Entity Relationship Modelling

2. To introduce the notation used in ER modelling To explain how to identify entities To introduce the concept of a relationship To explain participation condition, multiplicity and degree. Objectives

3. An entity is a set of objects of the same type ie they have the same properties and behave in a similar way Eg Student Lecturer Course Module. Entity

4. An entity has attributes or properties associated with it Eg Last name Date of birth Post code Entities can have relationships (associations) with other entities Eg A Student is associated with a Course A Course is associated with a number of Modules. Entity continued

5. We need to identify the things of interest to the users of the system Identifying Entities Things Tangible Things Roles Played Organisational Units Event, incidents or interactions Aeroplane, Book, Vehicle Employee, Student, Lecturer Department, Accounts Section Flight, Order, Invoice, Payment.

6. In this module we will work from short scenarios (but in real life you would interview users to get the information) When you read a scenario it is useful to underline anything you think may be an entity Look for the nouns. Identifying the Entities

7. SmartProject plc has employees who work on several projects but some employees do not work on any projects. New projects may have no employees assigned. Some projects have many staff involved. Each employee is based in just one department. Some employees are assigned to a single room, some do not have a room at all. Rooms are usually shared. Find the Entities in this Scenario

8. For each noun you have underlined consider: Does the system need to know about this? Is there more than one of these objects? But………. is this another name for an entity you have already got? Check the Possible Entities

9. An association between entities: Customer places an order Student enrols on a course. Relationships between Entities

10. Normally described by a verb Be aware of both directions A customer places an order An order is placed by a customer. Identifying Relationships

11. SmartProject plc has employees who work on several projects but some employees do not work on any projects. New projects may have no employees assigned. Some projects have many staff involved. Each employee is based in just one department. Some employees are assigned to a single room, some do not have a room at all. Rooms are usually shared. Find the Relationships in this Scenario

12. ER Diagram Basic notation of ER Diagram: NB - thecolours are not part of the syntax = Relationship works on Employee Machine = Entity Often hard to name the relationship.

13. The Entities and Relationships in Our Scenario works_on Employee Project based_in assigned Dept Room

14. The Enterprise Rules describe the rules the business uses Eg a student can only ever enrol on one course However another college may allow students to enrol on more than one course This information comes from systems investigation We need to show these rules on the ER model. Enterprise Rules

15. Consider the multiplicity for each direction, eg: A student must enrol on exactly one course A course is enrolled on by zero, one or many students Notice we always start with “A”, so we say “A student………”, “A course……….” etc. Enterprise Rules ctd

16. Otherwise called Membership Class Type of participation of an entity in a relationship: Obligatory (mandatory) every occurrence of an entity must participate OR Non-Obligatory (optional) occurrence(s) of an entity can exist without participating in the relationship. Participation Condition

17. Participation Condition is shown as a dot or blob M works on M Employee Machine   • A dot inside the entity box means there must be at least 1 (obligatory): “An employee must work on one or many machines” • A dot outside the entity box means zero is possible (non-obligatory): “A machine is worked on by zero, one or many employees”

18. The degree is shown by the use of 1 or M on the relationship Multiplicity is shorthand for “degree and participation condition”. Multiplicity and Degree

19. Example of Multiplicity A student must enrol on exactly one course Student 1 enrols M Course A course has 0, one or many students enrolled on it.

20. Finish this diagram: An employee has zero or one carA car must belong to exactly one employee. 1 1 Employee has Car

21. One More Example M Student registers on Module M

22. Employees may work on several projects but some employees do not work on any projects. New projects may have no employees assigned. Some projects have many employees involved. Each employee is based in just one department. Some employees are assigned to a single room, some do not have a room at all. Rooms are usually shared. Fill in the participation conditions and degrees of the relationships on the ERD on the following slide. Multiplicity Exercise

23. Multiplicity Exercise works_on Project Employee works_in based_in Dept Room Do you have enough information to make decisions about every participation condition and degree? If not, make reasonable assumptions.

24. Multiplicity Exercise Solution^ M M works_on Project Employee M works_in M 1 based_in 1 Dept Room • Assumptions: • A room need not have any employees assigned to it • A department need not have any employees, but could have many (0,1,M).

25. The dots on M:M relationships are usually outside (non-obligatory) The dot that goes with the M of a 1:M relationship is usually outside too (that’s the dot beside the 1) These are very good guidelines for beginners to apply - you need to be very sure of any variation from them The other useful rule to remember is that 1:1 relationships are quite rare If you have one it’s probably possible to combine the entities Guidelines

26. Many notations are used for Entity-Relationship modelling We use the Chen notation, which is particularly good for beginners. It is not the same notation as in some of the text books. The meaning and concepts are the same whatever the notation It is important that you do not mix the notation in a model. Notation

27. You should now know what is meant by an entity, a relationship and an enterprise rule and Be able to ‘read’ an ER model. Summary

28. Satzinger, Burd and Jackson Systems Analysis and Design in a Changing World Chapter 5 pages 164 -170 References