EntityRelationship Modelling 1 What is it about l

Entity-Relationship Modelling 1

What is it about? l l l The model is later transformed into a Logical model (e. g. relational) on which the physical database is built l The most widely used form of Semantic modelling: attempt to capitalise on knowledge of meaning of data to inform the model So we need a vocabulary l 2 ER model is used to show the Conceptual schema of an organisation. Independent of specific data model or DBMS

The Entity Relationship Model l Perspective This was lecture 1! Organisation Information System ERM 3 Conceptual Model Relational Model Logical Model Physical data storage Physical Model

Skills and concepts l So the concepts we want you to learn today are: – The basics of Entity-Relationship modelling – Entities – Relationships – Attributes 4

Entities l Entity - distinguishable “thing” in the real world – Strong (or regular) entity - entities have an independent existence (e. g. staff) – Weak entity - existence dependent on some other entity (e. g. next of kin) Entity type name (singular, no spaces, capital letter at start of each word) space for attributes 5

Attributes l l l 6 Entity types have Attributes (or properties) which associate each entity with a value from a domain of values for that attribute Attributes can be – simple (atomic) e. g. Surname; date of birth – composite e. g. address (street, town, postcode) – multi-valued e. g. phone number – complex nested multi-valued and composite – base or derived e. g. D. O. B. ; age – key Relationship types can also have attributes! (see later)

Notation for attributes Primary Key marked {PK} Composite attribute Derived Attribute {PPK} Partial Key - part of composite PK - or of a weak entity 7 Multi-Valued Attribute (number of values in [ ] brackets)

Relationships 8 l A relationship is “. . An association among entities (the participants). . ” l Relationships link entities with each other Name: verb, capital start letter, arrow indicates direction in which verb makes sense

Relationships: constraints l The degree of a relationship type – – – binary (connects 2 entity types) unary/ recursive (connects 1 entity type with itself) complex (connects 3 or more entity types) l l one to one (1: 1) one to many (1: m) many to many (m: n) Relationship constraints – participation – – 9 Ternary (connects 3) Relationship constraints - cardinality – – – l Degree full/mandatory or partial/optional Multiplicity

Relationships: Degree Binary relationship Recursive (Unary) relationship example Complex relationship – here ternary 10

Relationships: Multiplicity label lines to show cardinality and participation 0. . 1 “zero or one” optional 0. . * “zero or more” 1. . 1 “one” 1. . 4 “between 1 and 4” mandatory 1. . * “one or more” 1. . 1 0. . * Entity 1 has a 1: m relationship with Entity 2; participation for Entity 2 is mandatory, for Entity 1 optional. 11

Total and Partial Participation n Total participation (indicated by double line): every entity in the entity set participates in at least one relationship in the relationship set participation of student in advisor relation is total 4 every student must have an associated instructor n Partial participation: some entities may not participate in any relationship in the relationship set l Example: participation of instructor in advisor is partial

Relationships example Manager Manages 1. . 1 Department 0. . 3 responsibility [1. . *] date. Allocated Each department is managed by ONE manager 13 Relationshi p attributes Each manager manages UP TO 3 departments (but need not manage any department)

Over to You now! l See if you can draw an E-R diagram for this scenario – you are already familiar with this! – “A student registers for up to 8 modules and each module has many students on it. Record the student ID, their full name and address and also each module ID and title. We also want to hold the grade attained by each student for each module” l Remember to show in your model: All primary keys, – Entities – Relationships – Attributes – 14

Unary Example with Data A member of staff may supervise another staff member, but a staff member may be supervised by one or more staff members supervises 0. . * Staff 15 STAFF Member Grey Black Brown White 0. . 1 Age 43 27 35 33 Supervisor Black Brown

Ternary Diagrams are Tricky! “a client at a branch will be registered by one member of staff” Staff 1. . 1 “a member of staff will register a client at one branch” registers 0. . * Client 1. . 1 Branch “a member of staff at a branch may register many clients” Try to determine participation/cardinality by operating in pairs 16 Scenario modified from Connolly & Begg page 350

Key Points l l 17 ERM – Entities (strong, weak) – Attributes (simple, composite, etc) – Relationships l Degree l Cardinality l participation Model with the UML notation at conceptual level

Summary of Symbols Used in E-R Notation

Symbols Used in E-R Notation (Cont. )

Alternative ER Notations l Chen, IDE 1 FX, …

Alternative ER Notations Chen IDE 1 FX (Crows feet notation)

UML l l l UML: Unified Modeling Language UML has many components to graphically model different aspects of an entire software system UML Class Diagrams correspond to E-R Diagram, but several differences.

ER vs. UML Class Diagrams *Note reversal of position in cardinality constraint depiction

ER vs. UML Class Diagrams ER Diagram Notation Equivalent in UML *Generalization can use merged or separate arrows independent of disjoint/overlapping

UML Class Diagrams (Cont. ) l l l Binary relationship sets are represented in UML by just drawing a line connecting the entity sets. The relationship set name is written adjacent to the line. The role played by an entity set in a relationship set may also be specified by writing the role name on the line, adjacent to the entity set. The relationship set name may alternatively be written in a box, along with attributes of the relationship set, and the box is connected, using a dotted line, to the line depicting the relationship set.