Transcript What is a Database System?

Conceptual / semantic modelling
Conceptual / semantic modelling
1
Conceptual / semantic modelling
Overview
 design;
 conceptual design; ER model
•
•
•
•
concepts;
semantic aspects;
problems;
Enhanced ER (EER) modelling;
 transforming an ER (EER) model into a relational
model
2
Conceptual / semantic modelling
Design
 you know the mechanics for building a database
system, but don’t know how to design it
3
Conceptual / semantic modelling
Design






requirements specification
conceptual (semantic) design
logical design
physical design and implementation
testing and validation
maintenance
4
Conceptual / semantic modelling
Conceptual (semantic) design
 constructing a model of the information used in an
organisation
• independently of any targeted DBMS and of any physical
considerations - high level of abstraction
• conceptual model - a more structured description (as opposed
to a NL description)
• try to capture as much meaning as possible
 model - constructed within a theory
• terminology (ambiguity): model
 most popular conceptual data model (theory)
• ER (or E/R) model (theory) - Chen (1976)
• UML notation
5
Conceptual / semantic modelling
ER model
Concept
entity
Brief explanation
any real world object
(anything that has an individual
existence)
attribute
property
something that describes an entity
relationship
a link that exist between two
entities
Examples
student, tutor, department,
module, exam, project, plan,
task
(for student) name, address,
age, fees paid, level of study;
(for task) description,
deadline;
(student is) taught (module)
by (tutor);
(student) takes (exam);
(project) includes (task)
6
Conceptual / semantic modelling
Concepts of the ER model
 semantic, not formal
• may be understood differently (by designers); results: different
models of the same real life system
• however, an ER model is a semi-formal description (see below)
 not directly implementable
• needs to be translated into an implementable model (e.g. the
relational model)
• why, then, doing it in the first instance?
 symbolic level
• formal description based on a set of informally defined concepts
• most popular: ER diagram
7
Conceptual / semantic modelling
Entity
 entity type
• a concept identified as having an independent existence
 entity instance
 strong/regular entity type
 weak entity type
• its existence is dependent on some other entity types
 examples
• think ...
8
Conceptual / semantic modelling
ER diagram - entity (UML)
Student
Student
studNo
Book-Loan
9
Conceptual / semantic modelling
ER diagram - entity (before)
Student
Customer
Book-Loan
Account
10
Conceptual / semantic modelling
Attributes
 a property/characteristic of an entity (or relationship) type
 instances of the same type have properties in common
 attribute domain
• should be included in a fully developed data model
 attributes
•
•
•
•
simple / composite
single-valued / multi-valued
derived
candidate key / primary key / composite key (note: no foreign key!)
11
Conceptual / semantic modelling
ER diagram - attributes (UML)
Student
name {PPK}
dOB {PPK}
day
month
year
sex
/age
address
no
code
city
telNo[1..*]
course
/fees
12
Conceptual / semantic modelling
ER diagram - attributes (before)
year
month
name
day
date-of-birth
sex
age
personal-details
Student
address
course
fees
13
no
code
city
tel-no
Conceptual / semantic modelling
Relationships
 do not mistake with relation (in the relational model)
 relationship type
• a (meaningful) association between entity types
• participants - the entities involved in the relationship
 relationship instance
 degree
•
•
•
•
number of participating entities (types)
binary
ternary, quaternary …
recursive
14
Conceptual / semantic modelling
ER diagram - binary relationships (UML)
Book
Title
Author
Edition
Borrows
Student
name
dOB
Belongs To
Department
name
15
Conceptual / semantic modelling
ER diagram - binary relationships (before)
Department
Book-Loan
has
Student
belongs to
16
Conceptual / semantic modelling
ER diagram - ternary relationship (UML)
Module
code
Student
Teach
name
dOB
Lecturer
name
17
Conceptual / semantic modelling
ER diagram - recursive relationships (UML)
Representative
Student
Represents
name
dOB
Represented
18
Conceptual / semantic modelling
ER diagram - more than one relationship
between two entities
HeadofDepartment
Manages
Staff
Department
name
dOB
name
Member
BelongsTo
19
Conceptual / semantic modelling
ER diagram - relationships with attributes (UML)
IsAffiliatedTo
Staff
Department
name
dOB
name
date
position
20
Conceptual / semantic modelling
ER diagram - relationships with attributes
(before)
member of staff
is affiliated to
Staff
date
duration
Department
position
21
Conceptual / semantic modelling
Structural constraints on relationships
 cardinality constraints
 multiplicity
• cardinality
• participation
 relevant to all possibilities - i.e., not to a certain extension
22
Conceptual / semantic modelling
Structural constraints on relationships (UML)
Room
Rents
0..1
0..1
hall
no
Student
0..*
name
dOB
Advises
1
0..*
Tutor
name
Teaches
1..*
23
Conceptual / semantic modelling
ER diagram - structural constraints (before)
M
rented_house
1
1
owns
N
landlord
rents
M
tenant
has
1
house history
24
Conceptual / semantic modelling
Problems with ER models
 connection traps
• a link between two entities seems to have been represented via
another entity, but the actual model does not capture that
 fan traps
• symbolically: two (1:M) relationships fan out of an entity
• it is not always a trap
 chasm traps
• symbolically: a pathway representing a link contains a
relationship with a partial participation
 a good understanding of the application is required
25
Conceptual / semantic modelling
ER diagram - fan trap
Course
Attends
1
1..*
Student
name
dOB
code
Proposes
1
1..*
Project
name
26
Conceptual / semantic modelling
ER diagram - fan trap explanation
has members
Fine Arts
proposes
Ann Boyle
Colour and Depth
Chris Evans
Mixed Media in Modern Art
Monica Black
Humour in Modern Art
these relationships are not represented
27
Conceptual / semantic modelling
ER diagram - solution to a fan trap
Attends
1..*
Student
name
dOB
Takes
1..*
1
1
Project
Course
hall
no
name
can you think of another solution?
28
Conceptual / semantic modelling
ER diagram - chasm trap
Proposes
1..*
1
Course
hall
no
Project
name
Takes
0..1
0..*
Student
name
dOB
29
Conceptual / semantic modelling
ER diagram - solution to a chasm trap
Proposes
1..*
Project
name
Takes
0..1
0..*
1
Student
Course
hall
no
1
Attends
1..*
name
dOB
why not always represent all the relationships?
30
Conceptual / semantic modelling
The Enhanced ER model (EER)
 additional semantic modelling concepts
• for “richer” (more comprehensive) representations
 type hierarchies
 specialisation / generalisation
 subclass / superclass
 attribute inheritance
 aggregation hierarchies
 aggregation
 composition
31
Conceptual / semantic modelling
Specialisation / generalisation
 equivalent names:
• specialisation hierarchy, generalisation hierarchy or IS-A
hierarchy
 specialisation
• identifying classes among the instances of an entity type, by
identifying distinguishing properties (attributes);
• a top down approach
 generalisation
• grouping entity types into one single class, by disregarding
differentiating properties (attributes)
• a bottom up approach
 attribute inheritance
32
Conceptual / semantic modelling
Specialisation / generalisation
Staff
empNo {PK}
name
salary
position
Manager
experience
bonus
Developer
language
projects
Support
area
SalesStaff
speciality
sales
33
Conceptual / semantic modelling
Specialisation / generalisation
Staff
empNo {PK}
name
salary
position
Manager
experience
bonus
Developer
language
projects
Support
area
SalesStaff
speciality
sales
FullTime
notice
PartTime
workLoad
jobLevel
34
Conceptual / semantic modelling
Constraints
 overlapping of the child subclasses
• disjoint - {OR}
• overlapping - {AND}
 participation of the parent superclass
• total - {Mandatory}
• partial - {Optional}
35
Conceptual / semantic modelling
Constraints
Staff
empNo {PK}
name
salary
position
{Optional, OR}
Manager
experience
bonus
Developer
language
projects
Support
area
{Mandatory, OR}
SalesStaff
speciality
sales
FullTime
notice
PartTime
workLoad
jobLevel
36
Conceptual / semantic modelling
Specialisation / generalisation
more than one parent
Manager
SalesStaff
experience
bonus
speciality
sales
{Optional}
SalesManager
target
child inherits attributes of both parents
37
Conceptual / semantic modelling
Aggregation
Course
code
0..*
1..*
Module
code
0..*
1..*
1..1
Supports
Staff
name
0..*
IsRegisteredFor
Student
name
dOB
38
Conceptual / semantic modelling
Composition
Account
no
1..1 1..1
1..1
Agreement
text
date
1..1
1..*
Overdraft
dateValidUntil
amount
0..*
Transaction
date
type
amount
39
Conceptual / semantic modelling
Summary
 conceptual model
 ER Model
 entity
 attribute
 relationship
• multiplicity
 traps
 EER model
 generalisation/specialisation
• constraints
 aggregation
40