Lecture 2: Entity/Relationship modelling

1. Lecture 2: Entity/Relationship modelling www.cl.cam.ac.uk/Teaching/current/Databases/ Dr. Peter Chen http://bit.csc.lsu.edu/~chen/

2. Database design lifecycle • Requirements analysis • User needs; what must database do? • Conceptual design • High-level description; often using E/R model • Logical design • Translate E/R model into (typically) relational schema • Schema refinement • Check schema for redundancies and anomalies • Physical design/tuning • Consider typical workloads, and further optimise Today

3. Today’s lecture • E/R modelling • Entities • Attributes • Relationships • Constraints on relationships • Extended E/R modelling • Object ideas

4. Conceptual Design • What are the entities and relationships in the enterprise? • What information about these entities and relationships should we store in the database? • What are the integrity constraints (business rules) that hold? • We can represent this information pictorially in E/R diagrams (and then map these to a relational schema later).

5. E/R basics • An entity is a real-world object that is distinguishable from other objects • Each entity has attributes (with domains) • A particular entity will have a value for each of its attributes • An entity type defines a set of entities that have the same attributes • An entity set is the collection of all entities of a particular entity type (at a particular point in time)

6. Name NI dob Employees Entities and attributes • Entity types are drawn as rectangles, e.g. • Attributes are drawn as ovals, and attached to the entity sets with lines, e.g. Employees

7. Name NI dob Employees Key attributes • A key attribute of an entity type is an attribute whose values are distinct for each entity • Sometimes several attributes (a composite attribute) together form a key • NB: Such a composite should be minimal • We underline key attributes

8. Relationships • A relationship type among two or more entity types defines a set of associations between entities from those types • Mathematically, relationship type R R  E1   En. • The set of instances of the relationship type is called the relationship set

9. Name dname NI DID dob budget Employees Departments Relationships in E/R • Relationship types are represented by diamonds • They connect the participating entity types with straight lines, e.g. Works_in

10. Relationship set diagrams • Sometimes its useful to represent the relationship set diagrammatically r1 d1 e1 r2 d2 e2 e3 r3 d3 e4 d4 r4 e5 d5 … … …

11. since Works_in Name dname NI DID dob budget Employees Departments Relationship attributes • Relationships can also have attributes • NB: A relationship must be uniquely determined by the entities, without reference to the relationship attributes

12. since Works_in2 Name dname NI DID dob budget address Locations capacity Employees Department N-ary relationships • Although relatively rare, we can have n-ary relationships, e.g.

13. Each entity type in a relationship plays a particular role, which is associated with a role name (this is usually suppressed) An recursive relationship is when an entity type plays more than one role in the relationship type In this case the role name is required Recursive relationships

14. name dob NI Employees supervisor subordinate Reports-to Recursive relationships in E/R e.g.

15. Constraints on relationship types • For example: • An employee can work in many departments; a department can have many employees • In contrast, each department has at most one manager • Thus we need to be able to specify the number of relationship instances that an entity can participate in. • For binary relationships the possible ratios are: 1:1, 1:N, N:1, M:N

16. Cardinality ratios 1:1 1:N M:N

17. Cardinality ratios in E/R N M M:N 1 N N:1 1 1 1:1 Note: Sometimes this is written using different arrowheads

18. Participation constraints Every department must have a manager • This is an example of a participation constraint • The participation of an entity set, E, in a relationship set R is said to be totalif every entity in E participates in at least one relationship in R. (If not its participation is said to be partial)

19. Name dname NI DID dob budget Employees Department Participation in E/R diagrams • Total participation is displayed as a bold line between the entity type and the relationship • NB. Sometimes this is written as a double line since N Manages 1

20. Weak entity types • An entity type may not have sufficient attributes to form a primary key • Such an entity type is called a weak entity type • A weak entity can only be identified uniquely by considering the primary key of another (owner) entity

21. pName age Cost Name NI N 1 Policy Dependents Employees Weak entity types cont. • Thus the owner and weak entity types must participate in a 1:N relationship • Weak entity set must have total participation in this identifying relationship set.

22. Extended E/R modelling • What we’ve seen so far is “classic” E/R • Over the years a number of features have been added to the model and the modelling process • These features include:

23. Name NI dob Employees ISA hours cid rate Contract_Emp Temp_Emp ISA hierarchies • We can devise hierarchies for our entity types • If we declareA ISA B, everyA entity is considered to be a B entity

24. Attribute inheritance • As we’d expect, attributes of superclasses are inherited by the subclasses. • Thus: Temp_Emp also has attributes NI, Name and dob • In fact, subclasses inherit relationships too

25. since start dname budget budget PID DID Sponsors N M Projects Departments Aggregation • Suppose we have an entity set of Projects and that each project is sponsored by one or more departments; thus

26. Aggregation cont. • Suppose that employees are assigned to monitor a sponsorship • Monitors should be a relationship between Employees and the Sponsorsrelationship • Aggregation allows us to indicate that a relationship set participates in another relationship set • Use dashed box

27. Aggregation cont. NI name Employees Monitors until since start dname PID DID budget budget Sponsors N M Projects Departments

28. A Data Model from the European Bioinformatics Institute (EBI) See http://intact.sourceforge.net/uml/intactCore.gif

29. Summary You should now understand: • Database design lifecycle • Entities and attributes • Relationships • Cardinality ratios • Participation constraints • Weak entity types • ISA hierarchies & aggregation Next lecture: The relational model