Databases
Lecture 4 – Conceptual Modelling
Part 2 (Constraints & Special Relationships)
Bernhard Reus
1
Recall Data Model Definition
Data Model: “An integrated collection of concepts for describing and manipulating data,
relationships between data, and constraints on the data in an organization.”
Connolly & Begg: Database Systems
What constraints are there? How do we formalise them?
2
©Bernhard Reus, University of Sussex, 2004-16
Modelling Constraints
© www.sfu.ca/ ~ldignall/images/
• In the E/R model, we have relationships
• But business rules may restrict how entities can participate in those.
• So constraints are useful information.
n Cardinality Constraints n Participation Constraints n Multiplicity Constraints
In this lecture:
[C&B: Ch.11.6] n About Structural Constraints:
3
Why Constraints?
• Business policies/rules should be reflected in the data model
• E/R model from last time does not express all kinds of policies:
– some policies can be expressed as constraints on the domain of attributes in the E/R model
– some policies can be expressed as constraints on the relationships in the E/R model
4
©Bernhard Reus, University of Sussex, 2004-16
Quiz
Branch
address
start date brNo
age
Staff
address
Employs
dob name
staffNo
street postcode
5
town postcode
street
phoneNum
town
What do you think could be policies/rules that are not already expressed in this E/R model ?
Enterprise Constraints
The collection of all (general) policies and rules of a business (relevant to its data
model) is often called Enterprise Constraints
6
©Bernhard Reus, University of Sussex, 2004-16
Structural Constraints
• Example (Cardinality):
A branch employs many staff but a member of staff is employed by (exactly) one branch.
• Example (Participation) :
Each branch employs staff and each member of
Structural Constraints are those constraints that can be placed on entity types that participate in a relationship.
staff is employed by a branch.
7
Cardinality Constraints
• Examples
– A branch employs many staff.
one-to-many
A member of staff is employed by one branch.
– A member of staff manages (at most) one branch.
A branch is managed by one member of staff. one-to-one
– A client may view many properties.
A property may be viewed by many clients.
many-to-many
8
©Bernhard Reus, University of Sussex, 2004-16
Cardinality Constraints (cont’d)
Cardinality constraints describe the maximum number of possible relationship occurrences for an entity participating in a
relationship:
– 1:M, M:1
– 1:1
– M:M (M:N)
one-to-many, many-to-one
one-to-one many-to-many
For binary relationships
9
Cardinality Constraints (cont’d) • In diagrammatic form:
Branch Staff
Client
1 Employs 1 Manages
one-to-many
M Staff
one-to-one
1 Branch
many-to-many
MN
Views
Property
10
©Bernhard Reus, University of Sussex, 2004-16
Participation Constraints
• determine whether – all (total) or
– some (partial)
Mandatory participation Optional participation
entity occurrences participate in a relationship.
• Total participation indicated in E/R diagram with double line.
11
Participation Constraints (cont’d) All branches employ staff. Each member of staff is
employed at a branch.
Branch 1 Employs M Staff
all branches have staff all employees belong to a branch
All branches are managed by a member of staff. Not everyone is a manager.
Staff 1 Manages 1 Branch
not all staff are tutors
all staff have a tutor
12
©Bernhard Reus, University of Sussex, 2004-16
Relationship Documentation • Relation“BranchEmploysStaff”:
A branch EMPLOYS many staff.
A member of staff IS EMPLOYED BY one branch.
so cardinality is 1:M
A branch may EMPLOY staff.
A member of staff must BE EMPLOYED BY a branch.
so participation is:
partial for Branch, total for Staff .
13
Multiplicity Constraints
• Describe participation and cardinality constraints together by labelling the participation of an entity type in a relationship…
• …using exact upper and lower bounds on the number of entities participating in an association of that type.
• More precise statements about cardinality possible.
14
©Bernhard Reus, University of Sussex, 2004-16
Multiplicity (Example)
A branch is managed by (exactly) one staff.
A member of staff can manage at most two branches.
Staff
participation constraint (0 or >0)
cardinality constraint
(1,1) Manages (0,2) Branch total (for Branch) partial (for Staff)
15
Multiplicity (Example 2)
Client
(0,30)
Views
(1,4)
Property
A client must view at least one property and can view up to four properties.
A property may be viewed by nobody or can be viewed by up to 30 clients.
Relationship Documentation for multiplicity constraints
17
©Bernhard Reus, University of Sussex, 2004-16
Multiplicity (Example cont’d) Client (0,*) Views (1,*) Property
A client must view at least one property or many.
A property may be viewed by no client or many clients.
If there is no upper cardinality bound use *
18
Multiplicity v Cardinality/Participation
• Use EITHER cardinality and participation
constraints OR multiplicity constraints
• Do not use both at the same time.
• Always provide what you are asked for.
• Add documentation about the relationships (as explained earlier) explaining what the relations in E/R diagram actually mean.
• Keep in mind that there are always two directions (for a binary rel’ship).
19
©Bernhard Reus, University of Sussex, 2004-16
Non-binary Relationships
3 participating entity types Staff 1 Registers 1 Branch
M
A member of staff registers a client at one branch.
At a branch a member of staff registers many clients. A client at a branch is registered by one member of staff
Fix two entities to compute cardinality (multiplicity) of third.
20
Staff registers clients at a branch.
Client
Non-binary Relationships
3 participating entity types Staff 1 Registers 1 Branch
M
Participation constraints?
A member of staff registers a client at one branch.
At a branch a member of staff registers many clients. A client at a branch is registered by one member of staff
Staff registers clients at a branch.
Client
Does this mean a client can be registered at only one branch by one member of staff?
21
©Bernhard Reus, University of Sussex, 2004-16
Recursive Relationship
• … is a relationship type where the same entity type participates more than once in different roles.
• Role names must be given explicitly to entity types.
• Example: Staff supervises staff. Supervises
Supervisor
Supervisee
Staff
22
©Bernhard Reus, University of Sussex, 2004-16