Lecture 2: EntityRelationship modelling

1
Lecture 2 Entity/Relationship modelling
Dr. Peter Chen http//bit.csc.lsu.edu/chen/

• www.cl.cam.ac.uk/Teaching/current/Databases/

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
Todays 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
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
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
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
d2
r2
e2
e3
d3
r3
e4
d4
r4
e5
d5



11
Relationship attributes

• Relationships can also have attributes
• NB A relationship must be uniquely determined by
  the entities, without reference to the
  relationship attributes

12
N-ary relationships

• Although relatively rare, we can have n-ary
  relationships, e.g.

13
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

14
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
  11, 1N, N1, MN

16
Cardinality ratios
11
1N
MN
17
Cardinality ratios in E/R
N
M
MN
1
N
N1
1
1
11
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 total if every
  entity in E participates in at least one
  relationship in R. (If not its participation is
  said to be partial)

19
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
Weak entity types cont.

• Thus the owner and weak entity types must
  participate in a 1N relationship
• Weak entity set must have total participation in
  this identifying relationship set.

22
Extended E/R modelling

• What weve 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
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 wed 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
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 Sponsors relationship
• Aggregation allows us to indicate that a
  relationship set participates in another
  relationship set
• Use dashed box

27
Aggregation cont.
name
NI
Employees
Monitors
until
since
start
dname
PID
DID
budget
budget
Sponsors
N
M
Departments
Projects
28
A Data Model from the European Bioinformatics
Institute (EBI)
See http//intact.sourceforge.net/uml/intactCore.g
if
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