Databases
Lecture 3 – Conceptual Modelling (with Entity-Relationship Models)
Bernhard Reus
1
Last time: Data Model
• Every DBMS comes with a data definition language
• which is low level; need for a higher-level description:
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
2
©Bernhard Reus, University of Sussex, 2004-16
Last time: Design Phases vs. Architecture
User 1 User 2 User 3
Cuexstteornmalised levvielws
View 1
Conceptual Design Conceptua
l Schema
DBMS
logical physical
3
conceptual
level
Logical Design
internal
level
Physical Design
physical data organisation
View 2
View 3
Internal schema
Conceptual Modelling
• Before we can design and implement a database we must define a data model for it
• which can be communicated in a non-ambiguous and non-technical way.
• Compare®Software Engineering n What is conceptual design?
[C&B: Ch.11, App. E]
In this lecture:
n What is an Entity-Relationship Model?
n How to draw Entity-Relationship Diagrams.
5
©Bernhard Reus, University of Sussex, 2004-16
data independence
Conceptual Design
“ … is the process of constructing a model of the information used in an enterprise that is independent of all physical considerations.”
[Connolly & Beggs]
6
Entity-Relationship-Modelling
• for conceptual design / modelling … • top-down approach
• consists of identifying
– Entities / Entity types objects/types of interest
– Relationships associations/links between entities – Attributes properties of entities/relationships
– Constraints on entities/relationships/attributes
7
©Bernhard Reus, University of Sussex, 2004-16
Entities (of an Entity Type)
• represent uniquely identifiable data objects, items of interest
• can be physical (students, staff, products)
• can be abstract (courses, orders, projects)
• belong to an Entity Type:
a group of entities with the same properties (e.g. Student, Staff, Product).
8
E/R Diagrams
• E/R models are often written as diagrams.
• Entity types as rectangles with entity type name: Staff
Branch
9
©Bernhard Reus, University of Sussex, 2004-16
Relationships
• … are relations between two or more entity types, for instance:
• relationship and relation are used interchangeably and relationship occurrence is used for an association between two occurrences of entity types (ie. entities):
– The Bristol branch employs Mr Smith as staff.
10
Relationship Types • Example: Branch employs Staff
• Describes a relationship Employs among entity types Branch and Staff.
• Degree of a relationship type: number of entity types involved (degree of Employs is 2)
• Relationship types are best represented diagrammatically as …
11
©Bernhard Reus, University of Sussex, 2004-16
Relationships in E/R diagrams • … links between entity types.
• Name of relationship appears in diamond box: Branch Employs Staff
12
Attributes
• Attributes are
facts, aspects, properties, or details
about an entity or relationship • Examples
– Staff has a phone number, date of birth, address.
– Branch has an address, a manager.
Really an attribute?
– Relationship “Employs” may have a start date for the employment.
13
©Bernhard Reus, University of Sussex, 2004-16
values are taken
• may be single-valued or
multiple-valued
Kinds of Attributes
Composite attribute
Simple attribute
• has a domain in which its •
consists of multiple components each of which can be simple or composite again
Example: parts of an address Example: staff number
Example: age derivable from date of birth
Example: one or several phone numbers
§ may be a key attribute:
part of a group of attributes which uniquely identify entities
Derived attribute
represents a value that is derivable from the value of one or several other related attributes
14
• Keysconsistoftenof just one attribute like:
– national insurance number
– membership number
– serial number
– vehicle registration number
• Thesenumbers uniquely identify a person or object.
• Butkeyscanalsoconsist of several attributes:
– A branch may be uniquely characterized by its postcode and the house number
• Occasionally there are several possibilities for thekey.Alwayschoose the shortest one.
15
Key Attribute
©Bernhard Reus, University of Sussex, 2004-16
• •
Attributes in E/R diagrams
Names of attributes in ovals
Types of attributes usually not recorded in diagrams
name address
name
address
Staff
street town
Staff
postcode
composite attribute (thick line)
16
Attributes in E/R diagrams
• Also for relationships: start_date
for every pair of entity and entity that form an
relationship occurrence, there is a start_date value.
Staff
Avoid generalname“Has”for relationship names if you have more relationships.
Branch
Employs
As for entity types several attributes as well as multi-
valued/composite/derived attributes are possible but of course no
key attributes.
17
©Bernhard Reus, University of Sussex, 2004-16
Special Attributes in Diagrams • Key attribute (could be several):
staffNo Staff
• Multiple-valued attribute: phoneNumber
• Derived attribute:
age Staff
Staff
date_of_birth
18
Composite vs Multi-valued
• Thesetwoareoftenmixedupbystudents:
• Composite:
= several attributes, each with one value
• Multi-valued:
= one attribute with (possibly) several values
• Themulti-valuedattributecanhaveseveralvaluesat the same time (and not just one of them at each time). The number of values is arbitrary.
19
©Bernhard Reus, University of Sussex, 2004-16
Convention
• We’renotallowedtomixspecialattributetypes,so no attribute can be composite and multi-valued at the same time or a key and multi-valued at the same time etc.
• Forentitytypesalwaysusenounsstartingwithan uppercase letter (or all uppercase).
• Forrelationshiptypesalwaysuseverbsstartingwith uppercase letter (or all uppercase).
• Forattributeslowercaseisused(sometimesalso uppercase initials).
20
Our first E/R diagram:
start date Branch Employs
address brNo town street
postcode
age phoneNum
dob
Staff
staffNo
address
street postcode
name
town
21
©Bernhard Reus, University of Sussex, 2004-16
CD collection in E/R
• Returning to our initial motivational example, can you express the data model for the CD collection as an E/R diagram?
• We practice this in exercises in seminar class.
22
Weak Entity Type • … is an entity type that is
existence-dependent
on some other entity type (via a relationship)
• In other words:
an entity of this type can only exist (at all, independent of relationships) if there is an entity in the second participating type of the relationship that it is associated with.
23
©Bernhard Reus, University of Sussex, 2004-16
Weak Entity Types (Diagram)
key entity
partial key weak entity
type
other maxRent
Preference
partial key is rank
strong type
Example
rank location
Stated_by
other
name
Client clientNo
24
Weak Entity Type (Partial Key)
• rank is not a key for Preference, as it does not uniquely identify preferences:
– Two different preferences of different customers can have the same rank.
– Preferences are uniquely identified by their partial key (rank) together with the key of their owner (clientNo).
• A partial key is underlined by a dashed line.
25
©Bernhard Reus, University of Sussex, 2004-16
Weak Entity Type (Properties)
• …areuniquelyidentifiableonlythroughthe relationship with another (strong) entity type (called identifying relationship, double line notation).
The weak entity type can additionally participate in other relationships.
• …canberecognizedbynothavingakey(justa “partial” or no key)
• …arealsocalledchild/dependententitytypes(the strong entity being owner/parent).
26
©Bernhard Reus, University of Sussex, 2004-16