Lecture 2 EntityRelationship modelling Dr Peter Chen http

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

Database design lifecycle • Requirements analysis – User needs; what must database do? • Conceptual design – High-level description; often using E/R model Today • 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 2

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

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 4 these to a relational schema later).

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) 5

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

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 NI Name Employees dob 7

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 E 1 E n. • The set of instances of the relationship type is called the relationship set 8

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

Relationship set diagrams • Sometimes its useful to represent the relationship set diagrammatically r 1 e 2 e 3 e 4 e 5 d 1 r 2 d 2 r 3 d 4 r 4 … … … d 5 10

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

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

Recursive relationships • 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 13

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

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 15

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

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

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 total if every entity in E participates in at least one relationship in R. (If not its participation is said to be partial) 18

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 NI Name Employees dob 1 DID since Manages N dname budget Department 19

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 20

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. NI Name Employees p. Name Cost 1 Policy N age Dependents 21

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: • Sub- and superclasses • Specialisation • Generalisation • Categories • Higher/Lower-level entity sets • Attribute inheritance • Aggregation 22

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

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 24

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

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

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

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

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 29