DBS – The Entity Relationship Model

Database Systems
The Entity Relationship Model

Dr. Rudra Pratap Deb Nath

Department of Computer Science

and Engineering

University of Chittagong
rudra@[Link]

4th Semester 2021

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021
1 / 111
DBS – The Entity Relationship Model

Learning goals

Goals
Create non-trivial ER diagrams
Assess the quality of an ER diagram
Perform and explain the mapping of ER diagrams to relations
Use a particular ER notation properly

Motivation
ER diagrams are used widely
ER model is easy to learn
Much simpler than UML
An ER diagram is a good communication tool
Talking the same language

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 2 / 111
DBS – The Entity Relationship Model
Database design

Outline I
1 Database design
Steps of database design
Example design
2 Basic concepts
Example scenarios
Entity types
Attributes
Relationship types
3 Characteristics of relationship types
Degree
Chen notation (cardinality ratio)
Participation constraint
Chen notation (cardinality ratios) for nary relationship types
[min, max] notation (cardinality limits)
Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 2 / 111
DBS – The Entity Relationship Model
Database design

Outline II

4 Additional concepts
Weak entity types
The isa relationship type
5 Alternative notations
6 Mapping basic concepts to relations
Entity types
Relationship types
7 Mapping additional concepts to relations
Weak entity types
Recursive relationship types
N-ary relationship types
Special attributes
Generalization
Christian S. Jensen DBS – The Entity Relationship Model 4th Semester 2021 3 / 111
DBS – The Entity Relationship Model
Database design

Outline III

8 Example schemas

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 4 / 111
DBS – The Entity Relationship Model
Database design
Steps of database design

Steps of database design

Requirements analysis Parts of the real world

Mapping onto a Manual/intelectual

conceptual schema modeling

Conceptual schema
(ER schema)
Mapping onto a Semi-automatic
data model transformation

Relational XML Object-oriented

schema schema schema

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 4 / 111
DBS – The Entity Relationship Model
Database design
Example design

Step 1: Requirements analysis

[Link]
[Link]
[Link]
[Link]
Processes to model
“Students take courses”
“Instructors offer courses”
“The student ID unambiguously identifies a student”
...
Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 5 / 111
DBS – The Entity Relationship Model
Database design
Example design

Step 1: Requirements analysis – object specification

Employees
Attributes: EmpNo, salary, rank

Salary
Type: decimal
EmpNo Length: (8,2)
Degree of availability: 10%
Type: char
Uniqueness: no
Length: 9
Domain: 0. . . 999.999.99
Rank
Degree of availability: 100%
Uniqueness: true Type: String
Length: 4
Degree of availability: 100%
Uniqueness: no

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 6 / 111
DBS – The Entity Relationship Model
Database design
Example design

Step 1: Requirements analysis – relationship

specification

Relationship: “grades”
Participating objects
Instructor as examiner
Student as examinee
Course as topic

Attributes of relationship “grades”

Date
Time
Grade

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 7 / 111
DBS – The Entity Relationship Model
Database design
Example design

Step 2: Mapping onto a conceptual model

Requirements
“Students take courses”
“Instructors offer courses”
“The student ID unambiguously identifies a student”
...
Functional requirements
Secretary needs to feed in the grades
... ⇓ Mapping
name

empID instructor student studID

name
offers takes

lecture courseID

title

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 8 / 111
DBS – The Entity Relationship Model
Database design
Example design

Step 3: Mapping onto a data model

[Link]
Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 9 / 111
DBS – The Entity Relationship Model
Database design
Example design

Step 3: Mapping onto the relational model

name

empID instructor student studID

name
offers takes

lecture courseID

title

⇓ Mapping
Relational model
student (studID: integer, name: string)
takes (studID: integer, courseID: integer)
lecture (courseID: integer, title: string)
Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 10 / 111
DBS – The Entity Relationship Model
Database design
Example design

Step 4: Realization and implementation

Relational model
student (studID: integer, name: string)
takes (studID: integer, courseID: integer)
lecture (courseID: integer, title: string)

⇓ Mapping

Tables in a DB

student takes lecture

studID name studID courseID courseID title
26120 Pedersen 25403 5022 5001 DBS
25403 Hansen 26120 5001 5022 Belief and Knowledge
... ... ... ... ... ...

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 11 / 111
DBS – The Entity Relationship Model
Database design
Example design

Step 4: Realization and implementation

Tables in a DB

student takes lecture

studID name studID courseID courseID title
26120 Pedersen 25403 5022 5001 DBS
25403 Hansen 26120 5001 5022 Belief and Knowledge
... ... ... ... ... ...
⇓ Mapping
Memory, pages, data structures, indexes, files, devices

[Link]
Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 12 / 111
DBS – The Entity Relationship Model
Database design
Example design

Steps of database design

1 Requirements analysis
What are we dealing with?
2 Mapping onto a conceptual model (conceptual design)
What data and relationships have to be captured?
3 Mapping onto a data model (logical design)
How to structure data in a specific model (here: the relational
model)?
4 Realization and implementation (physical design)
Which adaptations and optimizations does a specific DBMS require?

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 13 / 111
DBS – The Entity Relationship Model
Database design
Example design

Steps of database design

1 Requirements analysis
What are we dealing with?
2 Mapping onto a conceptual model (conceptual design)
What data and relationships have to be captured?
3 Mapping onto a data model (logical design)
How to structure data in a specific model (here: the relational
model)?
4 Realization and implementation (physical design)
Which adaptations and optimizations does a specific DBMS require?

A good design avoids redundancy and incompleteness.

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 13 / 111
DBS – The Entity Relationship Model
Basic concepts

Outline

1 Database design

2 Basic concepts
Example scenarios
Entity types
Attributes
Relationship types

3 Characteristics of relationship types

4 Additional concepts

5 Alternative notations
Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 13 / 111
DBS – The Entity Relationship Model
Basic concepts

Entity Relationship Model (ERM)

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 14 / 111
DBS – The Entity Relationship Model
Basic concepts
Example scenarios

Example scenarios

Example scenarios used on the slides

University: students, instructors, courses,. . .
Wine: wine, producers, regions,. . .

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 15 / 111
DBS – The Entity Relationship Model
Basic concepts
Example scenarios

Wine schema color

name country

region
name
grapeType
description
area
side
Order percentage

madeOf
dish
locatedIn
name color

recommends wine produces producer

residual
year vineyard address
Sweetness

critic
owns

name licenseID

organization
license
amount

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 16 / 111
DBS – The Entity Relationship Model
Basic concepts
Example scenarios

University schema (different from the book!)

department assistant empID

name

worksFor

grades
name semester

empID instructor student studID

name
teaches takes
ects
successor
requires course courseID

predecessor
title

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 17 / 111
DBS – The Entity Relationship Model
Basic concepts
Entity types

Entities and entity types

Entities are objects of the real world about which we want to store
information
Only characteristics of entities can be stored in a database
(description), not the entity itself!

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 18 / 111
DBS – The Entity Relationship Model
Basic concepts
Entity types

Entities and entity types

Entities are objects of the real world about which we want to store
information
Only characteristics of entities can be stored in a database
(description), not the entity itself!

Entities are grouped into entity types.

wine

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 18 / 111
DBS – The Entity Relationship Model
Basic concepts
Entity types

Entities and entity types

Entities are objects of the real world about which we want to store
information
Only characteristics of entities can be stored in a database
(description), not the entity itself!

Entities are grouped into entity types.

wine

The extension of an entity type (entity set) is a particular collection of

entities.

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 18 / 111
DBS – The Entity Relationship Model
Basic concepts
Entity types

Entities and entity types

Entities are objects of the real world about which we want to store
information
Only characteristics of entities can be stored in a database
(description), not the entity itself!

Entities are grouped into entity types.

wine

The extension of an entity type (entity set) is a particular collection of

entities.
Often the two terms entity set and entity type are used as synonyms (also
in the book).
Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 18 / 111
DBS – The Entity Relationship Model
Basic concepts
Attributes

Attributes

Attributes model characteristics of entities or relationships.

All entities of an entity type have the same characteristics

Attributes are declared for entity types
Attributes have a domain or value set

name color

year

wine

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 19 / 111
DBS – The Entity Relationship Model
Basic concepts
Attributes

Attributes

Single-valued vs. multi-valued attributes

A person might have multiple phone numbers (or a single one)

phone phone
person number
person number

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 20 / 111
DBS – The Entity Relationship Model
Basic concepts
Attributes

Attributes

Simple attributes vs. composite attributes

An address can be modeled as a string or composed of street and city

street

person address person address

city

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 21 / 111
DBS – The Entity Relationship Model
Basic concepts
Attributes

Attributes

Stored attributes vs. derived attributes

E.g.: birthdate and age

birthday

person
age

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 22 / 111
DBS – The Entity Relationship Model
Basic concepts
Attributes

Keys

A (super) key consists of a subset of an entity type’s attributes

E(A1 , . . . , Am )
{S1 , . . . , Sk } ⊆ {A1 , . . . , Am }
The attributes S1 , . . . , Sk of the key are called key attributes.

The key attribute’s values uniquely identify an individual entity.

A candidate key corresponds to a minimal subset of attributes that fulfills

the above condition.

If there are multiple candidate keys, one is chosen as primary key.

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 23 / 111
DBS – The Entity Relationship Model
Basic concepts
Attributes

Primary keys

Primary key attributes are marked by underlining.

name color

year
wineID
wine

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 24 / 111
DBS – The Entity Relationship Model
Basic concepts
Attributes

Primary keys

Primary key attributes are marked by underlining.

name color

year
wineID
wine

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 24 / 111
DBS – The Entity Relationship Model
Basic concepts
Attributes

Primary keys

Primary key attributes are marked by underlining.

name color

year
wineID
wine

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 24 / 111
DBS – The Entity Relationship Model
Basic concepts
Relationship types

Relationships and relationship types

Relationships describe connections between entities.

Relationships between entities are grouped into relationship types.

producer produces wine

An association between two or more entities is called relationship

(instance). A relationship set is a collection of relationship instances.

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 25 / 111
DBS – The Entity Relationship Model
Basic concepts
Relationship types

Relationships and relationship types

Relationships describe connections between entities.

Relationships between entities are grouped into relationship types.

producer produces wine

An association between two or more entities is called relationship

(instance). A relationship set is a collection of relationship instances.

Often the two terms relationship set and relationship type are used as
synonyms (also in the book).

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 25 / 111
DBS – The Entity Relationship Model
Basic concepts
Relationship types

Mathematical understanding of relationship types

A relationship type R between entity types E1 , E2 , . . . , En can be

considered a mathematical relation.

Instance of a relationship type R:

R ⊆ E1 × E2 × · · · × En

A particular element (e1 , e2 , . . . , en ) ∈ R is called an instance of the

relationship type with ei ∈ Ei for all 1 ≤ i ≤ n.

Hint: This notation does not cover attributes of relationship types.

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 26 / 111
DBS – The Entity Relationship Model
Basic concepts
Relationship types

Recursive relationship types and role names

Role names are optional and used to characterize a relationship type.

Especially useful for recursive relationship types, i.e., an entity type is
participating multiple times in a relationship type.

wife

person marriedTo

husband

sequel

movie sequelOf

original

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 27 / 111
DBS – The Entity Relationship Model
Basic concepts
Relationship types

Attributes of relationship types

Relationship types can also have (descriptive) attributes.

percentage

wine madeOf grapeType

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 28 / 111
DBS – The Entity Relationship Model
Basic concepts
Relationship types

Summary: basic concepts

students take courses

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 29 / 111
DBS – The Entity Relationship Model
Basic concepts
Relationship types

Summary: basic concepts

students take courses

1. Entity

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 29 / 111
DBS – The Entity Relationship Model
Basic concepts
Relationship types

Summary: basic concepts

students take courses

1. Entity → Entity type

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 29 / 111
DBS – The Entity Relationship Model
Basic concepts
Relationship types

Summary: basic concepts

students take courses

1. Entity → Entity type

student

course

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 29 / 111
DBS – The Entity Relationship Model
Basic concepts
Relationship types

Summary: basic concepts

students take courses

1. Entity → Entity type

student

2. Relationship

course

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 29 / 111
DBS – The Entity Relationship Model
Basic concepts
Relationship types

Summary: basic concepts

students take courses

1. Entity → Entity type

student

2. Relationship → Relationship type

course

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 29 / 111
DBS – The Entity Relationship Model
Basic concepts
Relationship types

Summary: basic concepts

students take courses

1. Entity → Entity type

student

2. Relationship → Relationship type

takes

course

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 29 / 111
DBS – The Entity Relationship Model
Basic concepts
Relationship types

Summary: basic concepts

students take courses

1. Entity → Entity type

student

2. Relationship → Relationship type

takes
3. Attribute

course

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 29 / 111
DBS – The Entity Relationship Model
Basic concepts
Relationship types

Summary: basic concepts

students take courses

studID
1. Entity → Entity type semester name

2. Relationship → Relationship type student

3. Attribute
takes

course

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 29 / 111
DBS – The Entity Relationship Model
Basic concepts
Relationship types

Summary: basic concepts

students take courses

studID
semester name
1. Entity → Entity type
student

2. Relationship → Relationship type

takes
3. Attribute

course

ects title
courseID

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 29 / 111
DBS – The Entity Relationship Model
Basic concepts
Relationship types

Summary: basic concepts

students take courses

studID
semester name
1. Entity → Entity type
student

2. Relationship → Relationship type

takes
3. Attribute

4. Primary key course

ects title
courseID

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 29 / 111
DBS – The Entity Relationship Model
Basic concepts
Relationship types

Summary: basic concepts

students take courses

studID
semester name
1. Entity → Entity type
student

2. Relationship → Relationship type

takes
3. Attribute

4. Primary key course

ects title
courseID

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 29 / 111
DBS – The Entity Relationship Model
Basic concepts
Relationship types

Summary: basic concepts

students take courses

studID
semester name
1. Entity → Entity type
student

2. Relationship → Relationship type

takes
3. Attribute

4. Primary key course

5. Role ects title

courseID

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 29 / 111
DBS – The Entity Relationship Model
Basic concepts
Relationship types

Summary: basic concepts

students take courses

studID
semester name
1. Entity → Entity type
student

2. Relationship → Relationship type participant

takes
3. Attribute
regular course
4. Primary key course

5. Role ects title

courseID

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 29 / 111
DBS – The Entity Relationship Model
Characteristics of relationship types

Outline

1 Database design

2 Basic concepts

3 Characteristics of relationship types

Degree
Chen notation (cardinality ratio)
Participation constraint
Chen notation (cardinality ratios) for nary relationship types
[min, max] notation (cardinality limits)

4 Additional concepts

5 Alternative notations
Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 29 / 111
DBS – The Entity Relationship Model
Characteristics of relationship types
Degree

Characteristics of relationship types

Degree
Number of participating entity types
Mostly: binary
Rarely: ternary
In general: n-ary or n-way (multiway relationship types)

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 30 / 111
DBS – The Entity Relationship Model
Characteristics of relationship types
Degree

Characteristics of relationship types

Degree
Number of participating entity types
Mostly: binary
Rarely: ternary
In general: n-ary or n-way (multiway relationship types)

Cardinality ratio / cardinality limits / participation constraint

Number of times entities are involved in relationship instances
Cardinality ratio (Chen notation): 1:1, 1:N, N:M
Participation constraint: partial or total
Cardinality limits ([min,max] notation): [min,max]

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 30 / 111
DBS – The Entity Relationship Model
Characteristics of relationship types
Degree

Characteristics of relationship types

Degree
Number of participating entity types
Mostly: binary
Rarely: ternary
In general: n-ary or n-way (multiway relationship types)

Cardinality ratio / cardinality limits / participation constraint

Number of times entities are involved in relationship instances
Cardinality ratio (Chen notation): 1:1, 1:N, N:M
Participation constraint: partial or total
Cardinality limits ([min,max] notation): [min,max]

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 30 / 111
DBS – The Entity Relationship Model
Characteristics of relationship types
Degree

Multiway relationship types

Three binary relationship types

Ternary relationship type

dish dish d-w

recommends wine d-c wine

critic critic c-w

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 31 / 111
DBS – The Entity Relationship Model
Characteristics of relationship types
Degree

Multiway relationship types

Ternary relationship type Three binary relationship types

d1 w1 d1 w1

d2 w2 d2 w2

dish wine dish wine

c1 c2 c1 c2

critic critic

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 32 / 111
DBS – The Entity Relationship Model
Characteristics of relationship types
Degree

Reconstruction of relationship instances

Binary relationship types

d1 w1
Reconstructible relationship
instances
d2 w2
d1 – c1 – w1
d1 – c2 – w2
d2 – c2 – w1
dish wine but also: d1 – c2 – w1

c1 c2

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 33 / 111
DBS – The Entity Relationship Model
Characteristics of relationship types
Degree

Binary vs. n-ary relationship types

Ternary relationship type

d1 w1

Using binary relationships we

d2 w2 can reconstruct the
relationship instance
d1 – c2 – w1
which is not contained in the
dish wine ternary relationship type!

c1 c2

critic

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 33 / 111
DBS – The Entity Relationship Model
Characteristics of relationship types
Chen notation (cardinality ratio)

Characteristics of relationship types

Degree
Number of participating entity types
Mostly: binary
Rarely: ternary
In general: n-ary or n-way (multiway relationship types)

Cardinality ratio / cardinality limits / participation constraint

Number of entities involved in a relationship instance
Cardinality ratio (Chen notation): 1:1, 1:N, N:M
Participation constraint: partial or total
Cardinality limits ([min,max] notation): [min,max]

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 34 / 111
DBS – The Entity Relationship Model
Characteristics of relationship types
Chen notation (cardinality ratio)

Chen notation (cardinality ratio)

R ⊆ E1 × E2

E1 R E2

1:1 1:N 1: at most one

N: arbitrary number
E1 E2 E1 E2

N:1 N:M

E1 E2 E1 E2
Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 35 / 111
DBS – The Entity Relationship Model
Characteristics of relationship types
Chen notation (cardinality ratio)

Functional relationships

1:1, 1:N, and N:1 can be considered a partial functions (often also a total
function)
7 E2 and R−1 : E2 →
1:1 relationship: R : E1 → 7 E1
1:N relationship: R−1 : E2 →
7 E1
N:1 relationship: R : E1 →
7 E2
also referred to as functional relationship.

The “direction” is important!

The function always leads from the “N” entity type to the “1” entity type.

In the context of this lecture, we do not distinguish between partial (→)

7
and total functions (→). Hence, we simply write →.

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 36 / 111
DBS – The Entity Relationship Model
Characteristics of relationship types
Chen notation (cardinality ratio)

Graphical notation

1:N relationship type

producer produces wine

1:1 relationship type

producer owns license

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 37 / 111
DBS – The Entity Relationship Model
Characteristics of relationship types
Participation constraint

Participation constraint
Total
Each entity of an entity type must participate in a relationship, i.e., it
cannot exist without any participation (E2 in the left example).

E1 E2 E1 E2
Partial
Each entity of an entity type can participate in a relationship, i.e., it can
exist without any participation.
Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 38 / 111
DBS – The Entity Relationship Model
Characteristics of relationship types
Participation constraint

Graphical notation
1:N relationship type with total participation of entity type wine

producer produces wine

1:N relationship type with total participation of both involved entity types

producer produces wine

1:1 relationship type with partial participation

producer owns license

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 39 / 111
DBS – The Entity Relationship Model
Characteristics of relationship types
Summary cardinality ratios

Overview cardinality ratios

student takes course

Which relationship type is it? N:M

How many students are there in a course? arbitrary

How many courses does a student take? arbitrary

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 40 / 111
DBS – The Entity Relationship Model
Characteristics of relationship types
Summary cardinality ratios

Overview cardinality ratios

student takes course

Which relationship type is it? 1:N

How many students are there in a course? at most one

How many courses does a student take? arbitrary

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 41 / 111
DBS – The Entity Relationship Model
Characteristics of relationship types
Summary cardinality ratios

Overview cardinality ratios

student takes course

Which relationship type is it? N:1

How many students are there in a course? arbitrary

How many courses does a student take? at most one

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 42 / 111
DBS – The Entity Relationship Model
Characteristics of relationship types
Summary cardinality ratios

Overview cardinality ratios

student takes course

Which relationship type is it? 1:1

How many students are there in a course? at most one

How many courses does a student take? at most one

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 43 / 111
DBS – The Entity Relationship Model
Characteristics of relationship types
Chen notation (cardinality ratios) for nary relationship types

Cardinality ratios for n-ary relationship types

E1

N
En R E2
M

Ek

R : E1 × E2 × ... × Ek−1 × Ek+1 × ... × En → Ek

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 44 / 111
DBS – The Entity Relationship Model
Characteristics of relationship types
Chen notation (cardinality ratios) for nary relationship types

Cardinality ratios for n-ary relationship types

E1

N
En R E2
M

Ek

R : E1 × E2 × ... × Ek−1 × Ek+1 × ... × En → Ek

Remark on notation in general
Using arrows or annotating lines with 1, N, M, etc. is equivalent.
Having both is not necessary but sometimes useful for clarification.
Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 44 / 111
DBS – The Entity Relationship Model
Characteristics of relationship types
Chen notation (cardinality ratios) for nary relationship types

Example relationship: supervises

professor

N 1
student supervises seminarTopic

grades

supervises: professor × student → seminarTopic

supervises: seminarTopic × student → professor

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 45 / 111
DBS – The Entity Relationship Model
Characteristics of relationship types
[min, max] notation (cardinality limits)

Characteristics of relationship types

Degree
Number of participating entity types
Mostly: binary
Rarely: ternary
In general: n-ary or n-way (multiway relationship types)

Cardinality ratio / cardinality limits / participation constraint

Number of entities involved in a relationship instance
Cardinality ratio (Chen notation): 1:1, 1:N, N:M
Participation constraint: partial or total
Cardinality limits ([min,max] notation): [min,max]

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 46 / 111
DBS – The Entity Relationship Model
Characteristics of relationship types
[min, max] notation (cardinality limits)

[min,max] notation (cardinality limits)

[min1, max1] [minn, maxn]

E1 R En

[min2, max2]

E2

Restricts the number of times an entity can participate in a relationship.

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 47 / 111
DBS – The Entity Relationship Model
Characteristics of relationship types
[min, max] notation (cardinality limits)

[min,max] notation (cardinality limits)

[min1, max1] [minn, maxn]

E1 R En

[min2, max2]

E2

R ⊆ E1 × E2 × ... × Ei × ... × En
For each ei ∈ Ei there exists
at least mini instances of relationship type R involving ei and
at most maxi instances of relationship type R involving ei
Cardinality constraint: mini ≤ |{r | r ∈ R ∧ [Link] = ei }| ≤ maxi
Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 47 / 111
DBS – The Entity Relationship Model
Characteristics of relationship types
[min, max] notation (cardinality limits)

[min,max] notation (cardinality limits)

[min1, max1] [minn, maxn]

E1 R En

[min2, max2]

E2

Special values for mini : 0

Special values for maxi : ∗

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 48 / 111
DBS – The Entity Relationship Model
Characteristics of relationship types
[min, max] notation (cardinality limits)

[min,max] notation (cardinality limits)

[min1, max1] [minn, maxn]

E1 R En

[min2, max2]

E2

Special values for mini : 0

Special values for maxi : ∗

[0, ∗] represents no restrictions → default

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 48 / 111
DBS – The Entity Relationship Model
Characteristics of relationship types
[min, max] notation (cardinality limits)

[min,max] notation (cardinality limits)

[min1, max1] [minn, maxn]

E1 R En

[min2, max2]

E2

Special values for mini : 0

Special values for maxi : ∗

[0, ∗] represents no restrictions → default

The book uses a slightly different notation: 1..* instead of [1, ∗]

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 48 / 111
DBS – The Entity Relationship Model
Characteristics of relationship types
[min, max] notation (cardinality limits)

Chen notation vs [min,max] notation

1:1 relationship type

[0, 1] [0, 1]
E1 R E2
1 1

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 49 / 111
DBS – The Entity Relationship Model
Characteristics of relationship types
[min, max] notation (cardinality limits)

Chen notation vs [min,max] notation

1:1 relationship type

[0, 1] [0, 1]
E1 R E2
1 1

1:N relationship type

[0, *] [0, 1]
E1 R E2
1 N

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 49 / 111
DBS – The Entity Relationship Model
Characteristics of relationship types
[min, max] notation (cardinality limits)

Chen notation vs [min,max] notation

1:1 relationship type

[0, 1] [0, 1]
E1 R E2
1 1

1:N relationship type

[0, *] [0, 1]
E1 R E2
1 N

N:M relationship type

[0, *] [0, *]
E1 R E2
N M

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 49 / 111
DBS – The Entity Relationship Model
Additional concepts

Outline

1 Database design

2 Basic concepts

3 Characteristics of relationship types

4 Additional concepts
Weak entity types
The isa relationship type

5 Alternative notations

6 Mapping basic concepts to relations

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 49 / 111
DBS – The Entity Relationship Model
Additional concepts
Weak entity types

Weak entity types

N 1
vintage belongs to wine

year
name

residual color
sweetness

The existence of a weak entity depends on the existence of a strong

entity (aka. identifying or owning entity) associated by an identifying
relationship.

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 50 / 111
DBS – The Entity Relationship Model
Additional concepts
Weak entity types

Weak entity types

N 1
vintage belongs to wine

year
name

residual color
sweetness

Total participation of the weak entity type.

Only in combination with 1:N (N:1) (or rarely also 1:1) relationship
types
The strong entity type is always on the “1”-side

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 50 / 111
DBS – The Entity Relationship Model
Additional concepts
Weak entity types

Weak entity types

N 1
vintage belongs to wine

year
name

residual color
sweetness

Weak entities are uniquely identifiable in combination with the

corresponding strong entity’s key.
The weak entity type’s key attributes are marked by underlining with
a dashed line (partial key, discriminator).

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 50 / 111
DBS – The Entity Relationship Model
Additional concepts
The isa relationship type

The isa relationship type

Specialization and generalization is expressed by the isa relationship

type (inheritance).

production name

sparkling
ISA wine color
wine

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 51 / 111
DBS – The Entity Relationship Model
Additional concepts
The isa relationship type

Characteristics

production name

sparkling
ISA wine color
wine

Each sparkling wine entity is associated with exactly one wine entity
sparkling wine entities are identified by the functional isa relationship

Not every wine is also a sparkling wine

Attributes of entity type wine are inherited by entity type sparkling wine

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 52 / 111
DBS – The Entity Relationship Model
Additional concepts
The isa relationship type

Characteristics

w1
w1
w2
w2 w4

w3
w4
sparkling wine
w5

w6

wine

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 53 / 111
DBS – The Entity Relationship Model
Additional concepts
The isa relationship type

Cardinality

production name

sparkling
ISA wine color
wine

The cardinalities are always

isa(E1 [1, 1], E2 [0, 1])
Each entity of entity type E1 (sparkling wine) participates exactly
once, entities of entity type E2 (wine) participate at most once.

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 54 / 111
DBS – The Entity Relationship Model
Additional concepts
The isa relationship type

University example (overlapping specialization)

name universityMember

ISA ISA

studID student employee empID

ISA ISA
rank

department assistant professor office

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 55 / 111
DBS – The Entity Relationship Model
Additional concepts
The isa relationship type

Special characteristics

Overlapping specialization
An entity may belong to multiple specialized entity sets.
→ separate ISA symbols are used

Disjoint specialization
An entity may belong to at most one specialized entity set.
→ arrows to a shared ISA symbol in the diagram

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 56 / 111
DBS – The Entity Relationship Model
Additional concepts
The isa relationship type

University example
name universityMember

ISA

studID student employee empID

ISA rank

department assistant professor office

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 57 / 111
DBS – The Entity Relationship Model
Additional concepts
The isa relationship type

Attributes and relationship types

Lower-level entity types inherit

attributes of the higher-level entity type
participation in relationship types of the higher-level entity type

Lower-level entity types can

have attributes
participate in relationship types that the higher-level entity type does
not participate in

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 58 / 111
DBS – The Entity Relationship Model
Additional concepts
The isa relationship type

Participation constraints

Total generalization/specialization
Each higher-level entity must belong to a lower-level entity type.
Notation: double line

Partial generalization/specialization (default)

Each higher-level entity can (may or may not) belong to a lower-level
entity type.

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 59 / 111
DBS – The Entity Relationship Model
Alternative notations

Outline

1 Database design

2 Basic concepts

3 Characteristics of relationship types

4 Additional concepts

5 Alternative notations

6 Mapping basic concepts to relations

7 Mapping additional concepts to relations

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 59 / 111
DBS – The Entity Relationship Model
Alternative notations

Alternative notations

[Link]

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 60 / 111
DBS – The Entity Relationship Model
Alternative notations

Alternative notations

[Link]

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 60 / 111
DBS – The Entity Relationship Model

Summary

Entity relationship diagrams (ERDs) describe the conceptual schema

of a database
There is also an extended ER model
Basic ER concepts (Entity types, relationship types, attributes)
Degree of relationship types
Cardinalities (Chen, [min,max], total/partial participation)
Weak entity types
isa relationship type

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 64 / 111
DBS – The Entity Relationship Model
Mapping basic concepts to relations

Outline I
1 Database design
Steps of database design
Example design
2 Basic concepts
Example scenarios
Entity types
Attributes
Relationship types
3 Characteristics of relationship types
Degree
Chen notation (cardinality ratio)
Participation constraint
Chen notation (cardinality ratios) for nary relationship types
[min, max] notation (cardinality limits)
Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 64 / 111
DBS – The Entity Relationship Model
Mapping basic concepts to relations

Outline II

4 Additional concepts
Weak entity types
The isa relationship type
5 Alternative notations
6 Mapping basic concepts to relations
Entity types
Relationship types
7 Mapping additional concepts to relations
Weak entity types
Recursive relationship types
N-ary relationship types
Special attributes
Generalization
Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 65 / 111
DBS – The Entity Relationship Model
Mapping basic concepts to relations

Outline III

8 Example schemas

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 66 / 111
DBS – The Entity Relationship Model
Mapping basic concepts to relations

Design notes

Entities correspond to nouns, relationships to verbs.

Each statement in the requirement specification should be reflected
somewhere in the ER schema.
Each ER diagram (ERD) should be located somewhere in the
requirement specification.
Conceptual design often reveals inconsistencies and ambiguities in the
requirement specification, which must be first resolved.

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 66 / 111
DBS – The Entity Relationship Model
Mapping basic concepts to relations
Entity types

University schema with cardinality ratios

department assistant empID

N name

worksFor
grade
name semester
1
grades N
How to create
empID professor 1
student studID relations representing
1 M N
all information of this
name
rank ER diagram?
office teaches takes

ects
N M
M
requires course courseID

N
title

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 66 / 111
DBS – The Entity Relationship Model
Mapping basic concepts to relations
Entity types

Entity types

student
{[ studID: integer, name: string, semester: integer ]}
course
{[ courseID: integer, title: string, ects: integer ]}
professor
{[ empID: integer, name: string, rank: string, office: integer ]}
assistant
{[ empID: integer, name: string, department: string ]}
Basic approach
For each entity type → relation
Name of the entity type → name of the relation
Attributes of the entity type → Attributes of the relation
Primary key of the entity type → Primary key of the relation
Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 67 / 111
DBS – The Entity Relationship Model
Mapping basic concepts to relations
Entity types

Entity types

student
{[ studID: integer, name: string, semester: integer ]}
course
{[ courseID: integer, title: string, ects: integer ]}
professor
{[ empID: integer, name: string, rank: string, office: integer ]}
assistant
{[ empID: integer, name: string, department: string ]}

Notation of relational schemas

student (studID: integer, name: string, semester: integer)
student: {[ studID: integer, name: string, semester: integer ]}
We do not care about the order of attributes in this context!
Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 67 / 111
DBS – The Entity Relationship Model
Mapping basic concepts to relations
Entity types

Entity types

student
{[ studID: integer, name: string, semester: integer ]}
course
{[ courseID: integer, title: string, ects: integer ]}
professor
{[ empID: integer, name: string, rank: string, office: integer ]}
assistant
{[ empID: integer, name: string, department: string ]}

Notation of relational schemas

student (studID, name, semester)
student: {[ studID, name, semester ]}
And for the moment we also do not care about attribute domains.
Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 67 / 111
DBS – The Entity Relationship Model
Mapping basic concepts to relations
Relationship types

Mapping of N:M relationship types

semester ects

N M
studID student takes course courseID

name title

How to map this information to relations?

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 68 / 111
DBS – The Entity Relationship Model
Mapping basic concepts to relations
Relationship types

Mapping of N:M relationship types

semester ects

N M
studID student takes course courseID

name title

Basic approach
New relation with all attributes of the relationship type
Add the primary key attributes of all involved entity types
Primary keys of involved entity types together become the key of the
new relation

takes: {[ studID → student, courseID → course ]}

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 68 / 111
DBS – The Entity Relationship Model
Mapping basic concepts to relations
Relationship types

Mapping of N:M relationship types

semester ects

N M
studID student takes course courseID

name title

Basic approach
New relation with all attributes of the relationship type
Add the primary key attributes of all involved entity types
Primary keys of involved entity types together become the key of the
new relation

Key attributes “imported” from involved entity types (relations) are called
foreign keys.
Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 68 / 111
DBS – The Entity Relationship Model
Mapping basic concepts to relations
Relationship types

Mapping of N:M relationship types

takes
studID courseID
student course
studID ... 26120 5001 courseID ...
27550 5001
26120 ... 5001 ...
27550 4052
27550 ... 4052 ...
28106 5041
... ... ... ...
28106 5052
28106 5216
28106 5259
29120 5001
29120 5041
semester 29120 5049 ects

N M
studID student takes course courseID

name title

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 69 / 111
DBS – The Entity Relationship Model
Mapping basic concepts to relations
Relationship types

Mapping of N:M relationship types in general

AE1,1 ...

AE1,k(1)
E1 ...

AR,1 ...

AR,k(R)
R
AE2,1 AEn,1 ...

AE2,k(2) AEn,k(n)
E2 ... En

... ...

R: {[ AE1,1 , ..., AE1,k(1) , AE2,1 , ..., AE2,k(2) , ..., AEn,1 , ..., AEn,k(n) , AR,1 , ..., AR2,k(R) ]}

key of E1 key of E2 key of En attributes of R

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 70 / 111
DBS – The Entity Relationship Model
Mapping basic concepts to relations
Relationship types

Mapping of 1:N relationship types

name ects

1 N
empID professor teaches course courseID

rank office title

Is this different from N:M relationship types?

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 71 / 111
DBS – The Entity Relationship Model
Mapping basic concepts to relations
Relationship types

1:N relationship types

name ects

1 N
empID professor teaches course courseID

rank office title

New relation with all attributes of the relationship type

Add primary key attributes of all involved entity types

Initially!
course: {[ courseID, title, ects ]}
professor: {[ empID, name, rank, office ]}
teaches: {[ courseID → course, empID → professor ]}
Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 72 / 111
DBS – The Entity Relationship Model
Mapping basic concepts to relations
Relationship types

1:N relationship types

name ects

1 N
empID professor teaches course courseID

rank office title

New relation with all attributes of the relationship type

Add primary key attributes of all involved entity types
Primary key . . .

Initially!
course: {[ courseID, title, ects ]}
professor: {[ empID, name, rank, office ]}
teaches: {[ courseID → course, empID → professor ]}
Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 72 / 111
DBS – The Entity Relationship Model
Mapping basic concepts to relations
Relationship types

1:N relationship types

name ects

1 N
empID professor teaches course courseID

rank office title

New relation with all attributes of the relationship type

Add primary key attributes of all involved entity types
Primary key of the “N”-side becomes the key in the new relation

Initially!
course: {[ courseID, title, ects ]}
professor: {[ empID, name, rank, office ]}
teaches: {[ courseID → course, empID → professor ]}
Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 72 / 111
DBS – The Entity Relationship Model
Mapping basic concepts to relations
Relationship types

Mapping of 1:N relationship types

Initially
course: {[ courseID, title, ects ]}
professor: {[ empID, name, rank, office ]}
teaches: {[ courseID → course, empID → professor}

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 73 / 111
DBS – The Entity Relationship Model
Mapping basic concepts to relations
Relationship types

Mapping of 1:N relationship types

Initially
course: {[ courseID, title, ects ]}
professor: {[ empID, name, rank, office ]}
teaches: {[ courseID → course, empID → professor}

Improvement by merging
course: {[ courseID, title, ects, taughtBy → professor ]}
professor: {[ empID, name, rank, office ]}

taughtBy is a foreign key and references the primary key of relation

professor.
Values of taughtBy correspond to values of empID in relation professor.

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 73 / 111
DBS – The Entity Relationship Model
Mapping basic concepts to relations
Relationship types

Mapping of 1:N relationship types

Initially
course: {[ courseID, title, ects ]}
professor: {[ empID, name, rank, office ]}
teaches: {[ courseID → course, empID → professor}
Improvement by merging
course: {[ courseID, title, ects, taughtBy → professor ]}
professor: {[ empID, name, rank, office ]}

taughtBy is a foreign key and references the primary key of relation

professor.
Values of taughtBy correspond to values of empID in relation professor.

Relations with the same key can be combined. . .

but only these and no others!
Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 73 / 111
DBS – The Entity Relationship Model
Mapping basic concepts to relations
Relationship types

Mapping of 1:N relationship types

Initially
course: {[ courseID, title, ects ]}
professor: {[ empID, name, rank, office ]}
teaches: {[ courseID → course, empID → professor}
Improvement by merging
course: {[ courseID, title, ects, taughtBy → professor ]}
professor: {[ empID, name, rank, office ]}

taughtBy is a foreign key and references the primary key of relation

professor.
Values of taughtBy correspond to values of empID in relation professor.

If the participation is not total, merging requires null values for the
foreign key. In such cases, it might be preferable for some applications to
have a separate relation.
Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 73 / 111
DBS – The Entity Relationship Model
Mapping basic concepts to relations
Relationship types

Professor and course

professor
empID name rank office course
2125 Socrates C4 226 courseID title ects taughtBy
2126 Russel C4 232 5001 DBS 4 2137
2127 Kopernikus C3 310 5041 Robotics 4 2125
2133 Popper C3 52 5043 Software Engineering 3 2126
2134 Augustinus C3 309 5049 Ethics 2 2125
2136 Curie C4 36 4052 Logic 4 2125
2137 Kant C4 7 5052 Theory of Science 3 2126
5216 Bioethics 2 2126
5259 Chemistry 2 2133
5022 Belief and Knowledge 2 2134
4630 Physics 4 2137

name office rank title ects

1 N
empID professor teaches course courseID

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 74 / 111
DBS – The Entity Relationship Model
Mapping basic concepts to relations
Relationship types

Attention: this does not work

professor
empID name rank office teaches course
2125 Socrates C4 226 5041 courseID title ects
2125 Socrates C4 226 5049 5001 DBS 4
2125 Socrates C4 226 4052 5041 Robotics 4
... ... ... ... ... 5043 Software Engineering 3
2134 Augustinus C3 309 5022 5049 Ethics 2
2136 Curie C4 36 ?? 4052 Logic 4
... ... ... ... ... 5052 Theory of Science 3
5216 Bioethics 2
5259 Chemistry 2
5022 Belief and Knowledge 2
4630 Physics 4

name office rank title ects

1 N
empID professor teaches course courseID

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 75 / 111
DBS – The Entity Relationship Model
Mapping basic concepts to relations
Relationship types

Why can/will there be problems?

professor
empID name rank office teaches course
2125 Socrates C4 226 5041 courseID title ects
2125 Socrates C4 226 5049 5001 DBS 4
2125 Socrates C4 226 4052 5041 Robotics 4
... ... ... ... ... 5043 Software Engineering 3
2134 Augustinus C3 309 5022 5049 Ethics 2
2136 Curie C4 36 ?? 4052 Logic 4
... ... ... ... ... 5052 Theory of Science 3
5216 Bioethics 2
5259 Chemistry 2
5022 Belief and Knowledge 2
4630 Physics 4

Update anomaly: What happens when Socrates moves?

Deletion anomaly: What happens if “Belief and Knowledge” is no
longer taught?
Insert anomaly: Curie is new and does not yet teach any lectures
.Dr.. .Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 76 / 111
DBS – The Entity Relationship Model
Mapping basic concepts to relations
Relationship types

Summary: N:1 relationship types

producer located in area

vineyard address name country region

How to map this ERD to relations?

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 77 / 111
DBS – The Entity Relationship Model
Mapping basic concepts to relations
Relationship types

Summary: N:1 relationship types

producer located in area

vineyard address name country region

How to map this ERD to relations?

producer: {[ vineyard, address, locatedIn → area ]}

area: {[ name, country, region ]}

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 77 / 111
DBS – The Entity Relationship Model
Mapping basic concepts to relations
Relationship types

1:1 relationship types

license owns producer

licenseID amount vineyard address

Is this different from 1:N relationship types?

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 78 / 111
DBS – The Entity Relationship Model
Mapping basic concepts to relations
Relationship types

1:1 relationship types

license owns producer

licenseID amount vineyard address

New relation with all attributes of the relationship type

Add primary key attributes of all involved entity types
Primary key of any of the involved entity types can become the key in
the new relation
Initially!
license: {[ licenseID, amount ]}
producer: {[ vineyard, address ]}
owns: {[ licenseID → license, vineyard → producer ]} or
owns: {[ licenseID → license, vineyard → producer ]}
Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 78 / 111
DBS – The Entity Relationship Model
Mapping basic concepts to relations
Relationship types

1:1 relationship types

Initially
license: {[ licenseID, amount ]}
producer: {[ vineyard, address ]}
owns: {[ licenseID → license, vineyard → producer ]} or
owns: {[ licenseID → license, vineyard → producer ]}
Improvement by merging
license: {[ licenseID, amount, ownedBy → producer ]}
producer: {[ vineyard, address ]}
or
license: {[ licenseID, amount ]}
producer: {[ vineyard, address, ownsLicense → license ]}

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 79 / 111
DBS – The Entity Relationship Model
Mapping basic concepts to relations
Relationship types

1:1 relationship types

Initially
license: {[ licenseID, amount ]}
producer: {[ vineyard, address ]}
owns: {[ licenseID → license, vineyard → producer ]} or
owns: {[ licenseID → license, vineyard → producer ]}
Improvement by merging
license: {[ licenseID, amount, ownedBy → producer ]}
producer: {[ vineyard, address ]}
or
license: {[ licenseID, amount ]}
producer: {[ vineyard, address, ownsLicense → license ]}

It is best to extend a relation of an entity type with total participation.

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 79 / 111
DBS – The Entity Relationship Model
Mapping basic concepts to relations
Relationship types

Why not a single relation?

license owns producer

licenseID amount vineyard address

producer vineyard address licenseID amount

Rotkäppchen Freiberg 42-007 10.000
Weingut Müller Dagstuhl 42-009 250

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 80 / 111
DBS – The Entity Relationship Model
Mapping basic concepts to relations
Relationship types

Why not a single relation?

license owns producer

licenseID amount vineyard address

producer vineyard address licenseID amount

Rotkäppchen Freiberg 42-007 10.000
Weingut Müller Dagstuhl 42-009 250

Only correct in case of total participation ([1,1]) of both involved entity

types

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 80 / 111
DBS – The Entity Relationship Model
Mapping basic concepts to relations
Relationship types

Why not a single relation?

license owns producer

licenseID amount vineyard address

Producers without licenses require null values

producer vineyard address licenseID amount
Rotkäppchen Freiberg 42-007 10.000
Weingut Müller Dagstuhl ⊥ ⊥

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 81 / 111
DBS – The Entity Relationship Model
Mapping basic concepts to relations
Relationship types

Why not a single relation?

license owns producer

licenseID amount vineyard address

Producers without licenses require null values

producer vineyard address licenseID amount
Rotkäppchen Freiberg 42-007 10.000
Weingut Müller Dagstuhl ⊥ ⊥

Possible in case of partial participation ([0,1]) of one involved entity type, total
participation of the other one
→ leads to null values
Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 81 / 111
DBS – The Entity Relationship Model
Mapping basic concepts to relations
Relationship types

Why not a single relation?

license owns producer

licenseID amount vineyard address

Free licenses lead to more null values

producer vineyard address licenseID amount
Rotkäppchen Freiberg 42-007 10.000
Weingut Müller Dagstuhl ⊥ ⊥
⊥ ⊥ 42-003 100.000

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 82 / 111
DBS – The Entity Relationship Model
Mapping basic concepts to relations
Relationship types

Why not a single relation?

license owns producer

licenseID amount vineyard address

Free licenses lead to more null values

producer vineyard address licenseID amount
Rotkäppchen Freiberg 42-007 10.000
Weingut Müller Dagstuhl ⊥ ⊥
⊥ ⊥ 42-003 100.000

Partial participation of both involved entity types

→ leads to null values in all attributes
→ difficult to determine a primary key, waste of memory
Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 82 / 111
DBS – The Entity Relationship Model
Mapping basic concepts to relations
Relationship types

Why not a single relation?

license owns producer

licenseID amount vineyard address

Free licenses lead to more null values

producer vineyard address licenseID amount
Rotkäppchen Freiberg 42-007 10.000
Weingut Müller Dagstuhl ⊥ ⊥
⊥ ⊥ 42-003 100.000

In general: no merging into a single relation!

Standard approach: 2 relations (some null values are tolerable in most
applications)
Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 82 / 111
DBS – The Entity Relationship Model
Mapping basic concepts to relations
Relationship types

Summary: mapping relationship types to relations

M:N relationship type

New relation with relationship type’s attributes
Add attributes referencing the primary keys of the involved entity type
relations
Primary key: set of foreign keys
1:N relationship type
Add information to the entity type relation of the “N”-side:
Add foreign key referencing the primary key of the “1”-side entity type
relation
Add attributes of the relationship type
1:1 relationship type
Add information to one of the involved entity type relations:
Add foreign key referencing the primary key of the other entity type
relation
Add attributes of the relationship type

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 83 / 111
DBS – The Entity Relationship Model
Mapping basic concepts to relations
Relationship types

Foreign keys
A foreign key is an attribute (or a combination of attributes) of a relation
that references the primary key (or candidate key) of another relation.

Example
course: {[ courseID, title, ects, taughtBy ]}
professor: {[ empID, name, rank, office ]}
taughtBy is a foreign key referencing relation professor
Notation
course: {[ courseID, title, ects, taughtBy → professor ]}
professor: {[ empID, name, rank, office ]}

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 84 / 111
DBS – The Entity Relationship Model
Mapping basic concepts to relations
Relationship types

Foreign keys
A foreign key is an attribute (or a combination of attributes) of a relation
that references the primary key (or candidate key) of another relation.

Notation
course: {[ courseID, title, ects, taughtBy → professor ]}
professor: {[ empID, name, rank, office ]}

Alternative notation
course: {[ courseID, title, ects, taughtBy ]}
professor: {[ empID, name, rank, office ]}

Foreign key: [Link] → [Link]

Notation for composite keys: { R.A1 , R.A2 } → { S.B1 , S.B2 }

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 84 / 111
DBS – The Entity Relationship Model
Mapping additional concepts to relations

Outline I
1 Database design
Steps of database design
Example design
2 Basic concepts
Example scenarios
Entity types
Attributes
Relationship types
3 Characteristics of relationship types
Degree
Chen notation (cardinality ratio)
Participation constraint
Chen notation (cardinality ratios) for nary relationship types
[min, max] notation (cardinality limits)
Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 84 / 111
DBS – The Entity Relationship Model
Mapping additional concepts to relations

Outline II

4 Additional concepts
Weak entity types
The isa relationship type
5 Alternative notations
6 Mapping basic concepts to relations
Entity types
Relationship types
7 Mapping additional concepts to relations
Weak entity types
Recursive relationship types
N-ary relationship types
Special attributes
Generalization
Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 85 / 111
DBS – The Entity Relationship Model
Mapping additional concepts to relations

Outline III

8 Example schemas

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 86 / 111
DBS – The Entity Relationship Model
Mapping additional concepts to relations
Weak entity types

Weak entity types

vintage belongsTo wine

N 1

residual
year name color
Sweetness

Entities of a weak entity type are

existentially dependent on a strong entity type
uniquely identifiable in combination with the strong entity type’s key

How to map weak entity types to relations?

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 86 / 111
DBS – The Entity Relationship Model
Mapping additional concepts to relations
Weak entity types

Weak entity types

vintage belongsTo wine

N 1

residual
year name color
Sweetness

Mapping the identifying relationship type

New relation with all attributes of the relationship type
Add primary key attributes of all involved entity types
Foreign key of the “N”-side becomes the key in the new relation
Initially!
wine: {[ color, name ]}
vintage: {[ name → wine, year, residualSweetness ]}
belongsTo: {[ name → wine, year → vintage ]}
Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 87 / 111
DBS – The Entity Relationship Model
Mapping additional concepts to relations
Weak entity types

Weak entity types

vintage belongsTo wine

N 1

residual
year name color
Sweetness

Weak entity types and their identifying relationship types can always be
merged.

wine: {[ color, name ]}

vintage: {[ name → wine, year, residualSweetness ]}

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 88 / 111
DBS – The Entity Relationship Model
Mapping additional concepts to relations
Weak entity types

A more complex example examPart

semester

1 N
student takes exam grade

N
name N
studID comprises conducts

empID
courseID ects
M M

title course professor

exam: rank
comprises: office

conducts:

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 89 / 111
DBS – The Entity Relationship Model
Mapping additional concepts to relations
Weak entity types

A more complex example examPart

semester

1 N
student takes exam grade

N
name N
studID comprises conducts

empID
courseID ects
M M

title course professor

exam: {[ studID → student, examPart, grade ]} rank

comprises: office

conducts:

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 89 / 111
DBS – The Entity Relationship Model
Mapping additional concepts to relations
Weak entity types

A more complex example examPart

semester

1 N
student takes exam grade

N
name N
studID comprises conducts

empID
courseID ects
M M

title course professor

exam: {[ studID → student, examPart, grade ]} rank

comprises: {[ studID, examPart, courseID → course ]} with office
Foreign key: {studID, examP art} → {[Link], [Link] art}
conducts:

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 89 / 111
DBS – The Entity Relationship Model
Mapping additional concepts to relations
Weak entity types

A more complex example examPart

semester

1 N
student takes exam grade

N
name N
studID comprises conducts

empID
courseID ects
M M

title course professor

exam: {[ studID → student, examPart, grade ]} rank

comprises: {[ studID, examPart, courseID → course ]} with office
Foreign key: {studID, examP art} → {[Link], [Link] art}
conducts: {[ studID, examPart, empID → professor ]} with
Foreign key: {studID, examP art} → {[Link], [Link] art}

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 89 / 111
DBS – The Entity Relationship Model
Mapping additional concepts to relations
Weak entity types

Only one examiner per exam: whatexamPart

changes?
semester

1 N
student takes exam grade

N
name N
studID comprises conducts

empID
courseID ects
M 1

title course professor

exam: {[ studID → student, examPart, grade ]} rank

comprises: {[ studID, examPart, courseID → course ]} with office
Foreign key: {studID, examP art} → {[Link], [Link] art}
conducts: {[ studID, examPart, empID → professor ]} with
Foreign key: {studID, examP art} → {[Link], [Link] art}

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 90 / 111
DBS – The Entity Relationship Model
Mapping additional concepts to relations
Weak entity types

Only one examiner per exam: whatexamPart

changes?
semester

1 N
student takes exam grade

N
name N
studID comprises conducts

empID
courseID ects
M 1

title course professor

exam: {[ studID → student, examPart, grade ]} rank

comprises: {[ studID, examPart, courseID → course ]} with office
Foreign key: {studID, examP art} → {[Link], [Link] art}
conducts: {[ studID, examPart, empID → professor ]} with
Foreign key: {studID, examP art} → {[Link], [Link] art}

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 90 / 111
 DBS – The Entity Relationship Model
Mapping additional concepts to relations
Weak entity types

Only one examiner per exam: whatexamPart

changes?
semester

1 N
student takes exam grade

N
name N
studID comprises conducts

empID
courseID ects
M 1

title course professor

rank
office
exam: {[studID → student, examPart, grade, examiner → professor ]}
comprises: {[studID, examPart, courseID → course ]} with
Foreign key: {studID, examP art} → {[Link], [Link] art}
Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 90 / 111
DBS – The Entity Relationship Model
Mapping additional concepts to relations
Recursive relationship types

Recursive relationship types

name
from

area border

to
region

Mapping just like standard N:M relationship types and renaming of foreign
keys

area: {[ name, region ]}

border: {[ from → area, to → area ]}
Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 91 / 111
DBS – The Entity Relationship Model
Mapping additional concepts to relations
Recursive relationship types

Recursive functional relationship types

name mentor

critic studentOf

organization student

Mapping just like standard 1:N relationship types and merging

critic: {[ name, organization, mentor → critic ]}

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 92 / 111
DBS – The Entity Relationship Model
Mapping additional concepts to relations
N-ary relationship types

N-ary relationship types

description sideOrder

dish WName color

recommends wine

residual
year
Sweetness
critic

name organization

Entity types:
All participating entity types are mapped according to the standard rules.
critic: {[ name, organization ]}
dish: {[ description, sideOrder ]}
wine: {[ color, WName, year, residualSweetness ]}
Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 93 / 111
DBS – The Entity Relationship Model
Mapping additional concepts to relations
N-ary relationship types

N-ary relationship types

description sideOrder

dish WName color

recommends wine

residual
year
Sweetness
critic

name organization

N-ary relationship types (N:M:P)

recommends: {[ WName → wine, description → dish, name → critic ]}

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 93 / 111
DBS – The Entity Relationship Model
Mapping additional concepts to relations
N-ary relationship types

N:M:1 relationship type

empID professor office

1 name
rank
semester ects

N M
studID student grades course courseID

name grade title

Relations
student: {[ studID, name, semester ]}
course: {[ courseID, title, ects ]}
professor: {[ empID, name, rank, office ]}
grades:
Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 94 / 111
DBS – The Entity Relationship Model
Mapping additional concepts to relations
N-ary relationship types

N:M:1 relationship type

empID professor office

1 name
rank
semester ects

N M
studID student grades course courseID

name grade title

Relations
student: {[ studID, name, semester ]}
course: {[ courseID, title, ects ]}
professor: {[ empID, name, rank, office ]}
grades: {[ studID → student, courseID → course, empID → professor, grade ]}
Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 94 / 111
DBS – The Entity Relationship Model
Mapping additional concepts to relations
Special attributes

Multi-valued attributes

PID
phone
person number
name

Create a separate relation for each multi-valued attribute

Relations
person: {[ PID, name ]}
phoneNumber: {[ PID → person, number ]}

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 95 / 111
DBS – The Entity Relationship Model
Mapping additional concepts to relations
Special attributes

Composite attributes

street
PID

person address
name
city

Include the component attributes in the relation

Relation
person: {[ PID, name, street, city ]}

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 96 / 111
DBS – The Entity Relationship Model
Mapping additional concepts to relations
Special attributes

Derived attributes

PID birthday

person
name age

Ignored during mapping to relations, can be added later by using views.

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 97 / 111
DBS – The Entity Relationship Model
Mapping additional concepts to relations

Overview of the steps

1 Regular entity type

Create a relation, consider special attribute types
2 Weak entity type
Create a relation
3 1:1 binary relationship type
Extend a relation with foreign key
4 1:N binary relationship type
Extend a relation with foreign key
5 N:M relationship type
Create a relation
6 N-ary relationship type
Create a relation

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 98 / 111
DBS – The Entity Relationship Model
Mapping additional concepts to relations
Generalization

Relational modeling of generalization

department

assistant ISA employee

name empID
professor

office rank

The relational model does not support generalization and cannot express
inheritance.
→ Generalization is simulated.
Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 99 / 111
DBS – The Entity Relationship Model
Mapping additional concepts to relations
Generalization

Relational modeling of generalization

department

assistant ISA employee

name empID
professor

office rank

How to map this information to relations?

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 99 / 111
DBS – The Entity Relationship Model
Mapping additional concepts to relations
Generalization

Alternative 1: main classes

department

assistant ISA employee

name empID
professor

office rank

A particular entity is mapped to a single tuple in a single relation (to its

main class).

employee: {[ empID, name ]}

professor: {[ empID, name, rank, office ]}
assistant: {[ empID, name, department ]}
Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 100 / 111
DBS – The Entity Relationship Model
Mapping additional concepts to relations
Generalization

Alternative 1: main classes

employee
empID name employee:
2123 P. Müller {[ empID, name ]}
2124 A. Schmidt

professor
empID name rank office
2125 Socrates C4 226 professor:
2126 Russel C3 232 {[ empID, name, rank, office ]}
2127 Kopernikus C3 310
2128 Curie C4 36

assistant
empID name department assistant:
2150 C. Meyer DBS {[ empID, name, department ]}
2151 B. Fischer Physics

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 101 / 111
DBS – The Entity Relationship Model
Mapping additional concepts to relations
Generalization

Alternative 2: partitioning
department

assistant ISA employee

name empID
professor

office rank

Parts of a particular entity are mapped to multiple relations, the key is

duplicated.

employee: {[ empID, name ]}

professor: {[ empID → employee, rank, office ]}
assistant: {[ empID → employee, department ]}
Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 102 / 111
DBS – The Entity Relationship Model
Mapping additional concepts to relations
Generalization

Alternative 2: partitioning
employee
empID name
2123 P. Müller
2124 A. Schmidt employee:
2125 Socrates {[ empID, name ]}
... ...
2150 C. Meyer
2151 B. Fischer
professor
empID rank office professor:
2125 C4 226 {[ empID → employee, rank, office ]}
... ... ...
assistant
empID department assistant:
2150 DBS {[ empID → employee, department ]}
2151 Physics
Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 103 / 111
DBS – The Entity Relationship Model
Mapping additional concepts to relations
Generalization

Alternative 3: full redundancy

department

assistant ISA employee

name empID
professor

office rank

A particular entity is stored redundantly in the relations with all its

inherited attributes.

employee: {[ empID, name ]}

professor: {[ empID, name, rank, office ]}
assistant: {[ empID, name, department ]}
Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 104 / 111
DBS – The Entity Relationship Model
Mapping additional concepts to relations
Generalization

Alternative 3: full redundancy

employee
empID name
2123 P. Müller
2124 A. Schmidt employee:
2125 Socrates {[ empID, name ]}
... ...
2150 C. Meyer
2151 B. Fischer
professor
empID name rank office professor:
2125 Socrates C4 226 {[ empID, name, rank, office ]}
... ... ... ...
assistant
empID name department assistant:
2150 C. Meyer DBS {[ empID, name, department ]}
2151 B. Fischer Physics
Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 105 / 111
DBS – The Entity Relationship Model
Mapping additional concepts to relations
Generalization

Alternative 4: single relation

department

assistant ISA employee

name empID
professor

office rank

All entities are stored in a single relation. An additional attribute encodes

the membership in a particular entity type.

employee: {[ empID, name, type, rank, office, department ]}

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 106 / 111
DBS – The Entity Relationship Model
Mapping additional concepts to relations
Generalization

Alternative 4: single relation

employee: {[ empID, name, type, rank, office, department ]}

employee
empID name type rank office department
2123 P. Müller employee ⊥ ⊥ ⊥
2124 A. Schmidt employee ⊥ ⊥ ⊥
2125 Socrates professor C4 226 ⊥
2126 Russel professor C3 232 ⊥
2127 Kopernikus professor C3 310 ⊥
2128 Curie professor C4 36 ⊥
2150 C. Meyer assistant ⊥ ⊥ DBS
2151 B. Fischer assistant ⊥ ⊥ Physics

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 107 / 111
DBS – The Entity Relationship Model

Summary

Mapping ER diagrams to relations

Entity types
Binary relationship types
N-ary relationship types
Weak entity types
Recursive relationship types
Generalization
The “partitioning” alternative is preferred in most applications.
The discussed “mapping” rules aim for a minimum number of
relations, not necessarily minimum null values and redundancy!

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 108 / 111
DBS – The Entity Relationship Model

Appendix

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 109 / 111
DBS – The Entity Relationship Model

1 Database design
Steps of database design
Example design
2 Basic concepts
Example scenarios
Entity types
Attributes
Relationship types
3 Characteristics of relationship types
Degree
Chen notation (cardinality ratio)
Participation constraint
Chen notation (cardinality ratios) for nary relationship types
[min, max] notation (cardinality limits)
4 Additional concepts
Weak entity types
The isa relationship type
5 Alternative notations DBS – The Entity Relationship Model
Dr. Rudra Pratap Deb Nath 4th Semester 2021 109 / 111
6 Mapping basic concepts to relations
DBS – The Entity Relationship Model
Example schemas

Wine schema with

color Chen notation
name country

region
name
grape type
description
N
area
side
order percentage

made of
dish
located in
name color
N
M

M
recommends wine produces producer

residual
P year vineyard address
sweetness

critic
owns

name licenceID

organization
license
amount

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 110 / 111
DBS – The Entity Relationship Model
Example schemas

University schema with cardinality ratios

department assistant empID

N name

worksFor

name semester
1
grades N
empID 1 studID
instructor student

1 M N
name
teaches takes

ects
N M
M
requires course courseID

N
title

Dr. Rudra Pratap Deb Nath DBS – The Entity Relationship Model 4th Semester 2021 111 / 111
DBS-The Entity Relationship Model

Acknowledgement

Christian S. Jensen, Aalborg University

slides of Database System concept book

Dr. Rudra Pratap Deb Nath DBS-The Entity Relatioship Model 4th Semester 2021