DB-2019-lec02

1. DATABASES Fall 2019 Lecture 2. Data modeling in ER and UML

Alexander Breyman
Software Engineering Department
Computer Science Faculty
Higher School of Economics , Moscow, 2016
www.hse.ru

2. Database Design

Database Abstraction Layers
1. Conceptual Model
2. Logical Model
3. Physical Database Design
2

3.

Database design
Processing
Requirements
Information
Requirements
Requirements
Engineering
Book of duty
Conceptual
Modelling
Conceptual
design (ER)
DBMS
Logical
Modelling
Logical design
(schema)
Physical
Modelling
Physical design
Hardware/OS
3

4. Book of Duty

• Describe information requirements
– Objects used (e.g., student, professor, lecture)
– Domains of attributes of objects
– Identifiers, references / relationships
• Describe processes
– E.g., examination, degree, register course
• Describe processing requirements
– Cardinalities: how many students?
– Distributions: skew of lecture attendance
– Workload: how often a process is carried out
– Priorities and service level agreements
4

5. Entity/Relationship (ER) Models

• Entity
• Relationship
• Attribute
• Key
• Role
5

6. Entity/Relationship (ER) Models

• Entity
Student
• Relationship
attends
• Attribute
Name
• Key
ID
• Role
Attendant
6

7. Entity/Relationship (ER) Models

• Entity
• Relationship
StudID
Name
Semester
Student
• Attribute
Attendant
• Key
attends
• Role
Course
Lecture
LecID
Title
CP
7

8.

University
prerequisite
StudID
Name
requires
Student
attends
follow-up
CP
Lecture
Title
Semester
Grade
gives
tests
PersID
Name
LecID
Level
Assistant
works-for
Professor
Room
Area
PersID
Name
8

9. Natural Language Version

• Students have a StudID, Name and Semester. The StudID identifies a
student uniquely.
• Lectures have a LecID, CP and Title. The LecID identifies a lecture
uniquely.
• Professors have a PersID, Name, Level and Room. The PersID identifies
a professor uniquely.
• Assistants have a PersID, Name and (research) Area. The PersID
identifies an assistant uniquely.
• Students attend lectures.
• Lectures can be prerequisites for other lectures.
• Professors give lectures.
• Assistents work for professors.
• Students are tested by professors about lectures. Students receive
grades as part of these tests.
• Is this the only possible interpretation?
9

10. Why ER?

• Advantages
– ER diagrams are easy to create
– ER diagrams are easy to edit
– ER diagrams are easy to read (from the layman)
– ER diagrams express all information requirements
• Other aspects
– Minimality
– Tools (e.g., Visio)
– Graphical representation
• General
– Try to be consice, complete, comprehensible, and correct
– Controversy whether ER/UML is useful in practice
– No controversy that everybody needs to learn ER/UML
10

11. Functionalities

E1
1:1
N:1
E1
...
E2
...
R
E2
1:N
N:M
11

12. Functionalities of n-ary relationships

E1
P
En
N
M
R
E2
1
Ek
R : E1 x ... x Ek-1 x Ek+1 x ... x En → Ek
12

13. Example: seminar

1
Student
N
supervise
1
Professor
Topic
Grade
supervise : Professor x Student → Topic
supervise : Topic x Student → Professor
13

14. Constraints

The following is not possible:
1. Students may only do at most one seminar with a
prof.
1. Students may only work on a topic at most once.
The following is possible:
– Profs may recycle topics and assign the same topic
to several students.
– The same topic may be supervised by several
profs.
14

15. Example

Professor
p1
Student
s1
b1
p2
b2
p3
s2
b3
s3
b4
s4
Dashed lines represent
illegal references
p4
t1
b5
b6
t2
t3
t4
Topic
15

16.

Functionalities
Legi
requires
Followup
prerequisite
N
Name
Semester
Student
N
attends
Lecture
M
N
M
Grade
Area
N
works-for
Title
1
Level
Professor
Room
1
Assistant
N
CP
gives
tests
PersNr
Name
M
Nr
1
PersNr
Name
16

17. Two Binary vs One Ternary

• A thief steals a painting as part of a theft.
– Model as two binary relationships
– Model as one ternary relationship
– What is better?

18. Rules of thumb

• Attribute vs. Entity
– Entity if the concept has more than one relationship
– Attribute if the concept has only one 1:1 relationship
• Partitioning of ER Models
– Most realistic models are larger than a page
– Partition by domains (library, research, finances, ...)
– I do not know of any good automatic graph partitioning tool
• Good vs. Bad models
– Do not model redundancy or tricks to improve performance
– Less entities is better (the fewer, the better!)
– Remember the C4 rule. (concise, correct, complete, compr.)

19. (min, max)-Notation

E1
(min1 max1)
En
R
E2
(mini, maxi)
Ek
R → E1 x ... x Ei x ... x En
19

20. Geometric Modelling

PolyID
Polyhedron
1
covers
Polyhedron
N
Surface
SurfaceID
N
boundary
M
Edge
EdgeID
N
StartEnd
M
X
Y
Node
Z
20

21. Geometric Modelling

PolyID
Polyhedron
1 (1,1)
covers
Polyhedron
N
SurfaceID
Surface
N
boundary
M
(2, 2)
Edge
N
EdgeID
(2, 2)
StartEnd
M
X
Y
Node
Z
21

22. Weak Entities

Size
BldNr
Building
RoomNr
Size
N
1
located
Room
• The existince of room depends on the existence of the
associated building.
• Why must such relationships be N:1 (or 1:1)?
• RoomNr is only unique within a building.
• Key of a room: BldNr and RoomNr
22

23. Exams depend on the student

1
Student
N
takes
Grade
Exam
Part
N
Legi
covers
M
Nr
Lecture
N
gives
M
PersNr
Professor
Can the existence of an entity depend on several
other entities? (E.g., exam on student and prof?)
23

24. Corner Case 1

– A human cannot exist without a heart.
– A heart cannot exist without a human.
– Anne lives on Bob‘s heart.
Bob lives on Anne‘s heart.
Possible?
Heart
1
Belongs
to
Person
1
24

25. Corner Case 2

– A human can only survive with at least one kidney.
– Not expressible with ER!
(Why not?)
Kidney
N
Belongs
to
Person
1
25

26. Generalization

Uni-Member
Name
is-a
PersNr
Employee
Student
is-a
Legi
Level
Area
Assistant
Professor
Room
26

27.

requires
(0,*)
(0,*)
Nr
Legi
Name
Student
Semester
(0,*)
(0,*)
Grade
(3,*)
(0,*)
(1,1)
Assistant
(1,1)
Title
gives
tests
(0,*)
Area
CP
Lecture
attends
(0,*)
Level
(0,*)
Works-for
Professor
Room
is-a
PersNr
Name
Employee
27

28. Aggregation

Bicycle
Part-of
Part-of
Frame
Wheel
Part-of
Part-of
Part-of
Part-of
...
...
...
...
...
...
...
...
28

29.

Aggregation and
Generalization
Vehicle
is-a
Manual vehicle
Motor vehicle
is-a
is-a
Bicycle
Tricycle
Part-of
Motorcycle
Car
Part-of
Frame
Wheel
Part-of
Part-of
Part-of
Part-of
...
...
...
...
...
...
...
...
29

30. Why is ER modelling so difficult?

View 3
View 1
ConsoliView 4
date
Global Schema
•No redundancy
•No conflicts
•Avoid synonyms
•Avoid homonyms
View 2
30

31. Consolidation Hierarchies

S1,2,3,4
S1,2,3,
S1,2
S1
Problem: How to achieve
multi-lateral consensus?
S4
S3
S2
S1,2,3,4
S1,2
S1
S3,4
S2
S3
S4
31

32. Example: Professor View

do
Student
Title
Master thesis
supervise
Assistant
write
PhD thesis
Title
Professor
supervise
32

33.

Example: Library View
Faculty
Library
owns
Signature
Document
manages
Authors
lends
Uni-Member
Title
Year
Date
33

34.

Example: Lecture View
Lecture
Textbook
Authors
Title
recommends
Year
Lecturer
Publisher
34

35. Observations

• Lecturer and Professor are synonyms.
• Uni-Member is a generalization of Student, Professor
and Assistant.
• However, libraries are managed by Employees. (View
2 is imprecise in this respect.)
• Dissertations, Master theses and Books are different
species of Document. All are held in libraries.
• Do and Write are synonyms in View 1.
• Things get complicated very quickly – requires
„engineers“
– Not unique
– Need to invent new concepts
– Need to compromise (e.g., authorship of documents)
35

36.

Signature
Library
keeps
Title
faculty
Year
Document
Publisher
writes
Master Thesis
lends
supervise
Dissertation
Book
supervise
recommends
manages
Assistant
Date
Student
Professor
Employee
Uni-Member
Person
Lecturer
36

37. Data Modelling with UML

• Unified Modelling Language UML
• De-facto standard for object-orientierted design
• Data modelling is done with „class diagramms“
– Class in UML ~ Entity in ER
– Attribute in UML ~ Attribute in ER
– Association in UML ~ Relationship in ER
– Compositor in UML ~ Weak Entity in ER
– Generalization in UML ~ Generalization in ER
• Key differences between UML class diagrams and ER
– Methods are associated to classes in UML
– Keys are not modelled in UML
– UML explicitly models aggregation (part-of)
– UML supports the modelling of instances (object diagrams)
• UML has much more to offer (use cases, sequence
37
diagr., ...)

38. Class: Professor

Professor
- PersNr: Integer
+ Name: String
- Level: String
+ promote()
38

39. Associations (directed, undirected)

39

40. Functionalities & Multiplicities

Functionalities &
Multiplicities
Multiplicities
Every instance of A is associated to 4 to 6 instances of B.
Every instance of B is associated to 2 to 5 instances of A.
Be careful: Flipped around as compared to ER.
Be careful: Cannot be used for n-ary relationships.
Functionalities
• Represented as UML multiplicities: 1, *, 1..*, 0..*, or 0..1
• Otherwise, the same as in ER.
40

41. Aggregation

41

42. Generalization

42

43.

Homework Assignment 2
Draw an E/R diagram for Library system based on the following
requirements:
• Library keeps Copies of Books. Each Copy (Instance) has a unique
CopyNumber, a Position on a Shelf, and can be uniquely identified with
CopyNumber together with ISBN.
• Each Book has a unique ISBN number, a Year, a Title, an Author and a
Number-of-pages.
• Books are published by Publishers. A Publisher has a Name and an Address.
• Books have one or more Category (s) assigned. Categories form a hierarchy,
so a category can be just another category subordinate. Category has a
Name and no other properties.
• Each reader is assigned a unique Reader-number. Reader has a Last-name, a
First-name, an Address and Birthday.
• Readers may borrow one or more Copies of Books. When borrowing, the
planned Return-date is recorded.
Higher School of Economics , Moscow, 2014

44.

20, Myasnitskaya str., Moscow, Russia, 101000
Tel.: +7 (495) 628-8829, Fax: +7 (495) 628-7931
www.hse.ru