INFO20003 Database Systems 1
INFO20003 Database Systems
Lecture 03
Introduction to Data Modelling (ER)
Week 2
Dr Renata Borovica-Gajic
INFO20003 Database Systems 3© University of Melbourne
Database Development Lifecycle: Review
Today
Database Planning
Systems Definition
Requirements Definition
and Analysis
Data Conversion and
Loading
Implementation
Application Design
Testing
Operational
Maintenance
Design
Physical Design
Logical Design
Conceptual Design
INFO20003 Database Systems 4© University of Melbourne
The Entity-Relationship Model
• Basic ER modeling concepts
• Constraints
• Conceptual Design
Readings: Chapter 2, Ramakrishnan & Gehrke, Database Systems
INFO20003 Database Systems 5© University of Melbourne
Conceptual Design: Objectives
• What are the entities and relationships in the enterprise?
• What information about these entities and relationships should
we store in the database?
• What are the integrity constraints that hold?
INFO20003 Database Systems 6© University of Melbourne
ER Model: Entity & its attributes
• Entity: Real-world object distinguishable from other objects.
An entity is described (in DB) using a set of attributes.
• Entity Set: A collection of entities of the same type (e.g. all
employees)
– All entities in an entity set have the same set of attributes
– Each entity has a key (underlined)
Employee
SSN
Name
Age
key
entity set
attribute
INFO20003 Database Systems 7© University of Melbourne
ER Model: Relationship
• Relationship: Association among two or more entities.
Relationships can have their own attributes.
– Example: Fred works in the Pharmacy department.
• Relationship Set: Collection of relationships of the same type.
– Example: Employees work in departments.
Name
SSN Age
works in
since
ID Budget
Name
relationship set
(with a descriptive attribute)
Employee Department
INFO20003 Database Systems 8© University of Melbourne
ER Model: Relationship roles
Same entity set can participate in:
• different relationship sets, or even
• different “roles” in the same set
different
relationships
ID Budget
Name
Department works in
since
SSN Age
Name
Employee
reports to
super-
visor
subor-
dinate
different roles
INFO20003 Database Systems 9© University of Melbourne
The Entity-Relationship Model
• Basic ER modeling concepts
• Constraints
• Conceptual Design
Readings: Chapter 2, Ramakrishnan & Gehrke, Database Systems
INFO20003 Database Systems 10© University of Melbourne
Key Constraints: Types
One-to-OneOne-to-Many
(Many-to-One)
Many-to-Many
Key constraints determine the number of objects taking part in the
relationship set (how many from each side)
Types of key constraints:
INFO20003 Database Systems 11© University of Melbourne
Key Constraints: Many-to-Many
Example:
An employee can work in many departments; a department can have many
employees.
Many is represented by a line (red is here just to emphasize it – no need to color).
Employee Departmentworks in
manymany
ID Budget
Name
Departmentworks in
since
SSN Age
Name
Employee
INFO20003 Database Systems 12© University of Melbourne
Key Constraints: One-to-Many
Example:
Each department has at most one manager.
This is the key constraint on Manages.
Employee Departmentmanages
onemany
One-to-many constrains one entity set to have a single entity per a relationship.
An entity of that set can never participate in two relationships of the same
relationship set. This is called a key constraint and is represented by an arrow.
key
constraint
ID Budget
Name
Departmentmanages
since
SSN Age
Name
Employee
INFO20003 Database Systems 13© University of Melbourne
Example: Every employee must work in a department. Each department has at
least one employee. Each department has to have a manager (but not everyone
is a manager).
Participation Constraints
Employee Departmentworks in
Total
participation
Total
participation
Employee Departmentmanages
Partial
participation
Total
participation
Participation constraint explores whether all entities of one entity set take part
in a relationship. If yes this is a total participation, otherwise it is partial. Total
participation says that each entity takes part in “at least one” relationship, and
is represented by a bold line.
ID Budget
Name
Department
manages
since
SSN Age
Name
Employee
works in
since
INFO20003 Database Systems 14© University of Melbourne
Weak Entities
A weak entity can be identified uniquely only by considering (the primary
key of) another (owner) entity. They are represented as a “bold” rectangle.
• Owner entity set and weak entity set must participate in a relationship where
each weak entity has one and only one strong entity to depend on (key
constraint)
• Weak entity set must have total participation in this relationship set. Such
relationship is called identifying and is represented as “bold”.
Weak entities have only a “partial key” (dashed underline)
and they are identified uniquely only when considering
the primary key of the owner entity
Name
SSN …
Policy
Cost
DName …
Age
Employee Dependent
INFO20003 Database Systems 15© University of Melbourne
Ternary Relationships
In general, we can have n-ary relationships, and
relationships can have attributes
Quantity
This is a ternary relationship
with one relationship attribute
ContractPart Department
Supplier
INFO20003 Database Systems 16© University of Melbourne
Ternary vs. Binary Relationships
Second model:
• S “can supply” P, D “needs” P, and D “deals with” S does not imply
that D has agreed to buy P from S. Not the same!
VS.
Are these two models the same?Quantity
ContractPart Department
Supplier
Part Department
Suppliercan supply deals with
Quantity
INFO20003 Database Systems 17
Special attribute type: Multi-valued attributes
• Multi-valued attributes can have multiple (finite set of) values of the same
type.
Example:
For employees we need to capture their home phone number and work
phone number.
Multi-valued attribute
Employee
SSN
Name
Age
Phone num
INFO20003 Database Systems 18
• Composite attributes have a structure hidden inside (each element can be
of different type).
Special attribute type: Composite attributes
Example:
For employees we need to capture an address consisting of a postcode,
street name and number.
Composite attribute
Employee
SSN
Name Age
Address
Postcode Street name
Street num.
INFO20003 Database Systems 19© University of Melbourne
Now you try
University database schema:
• Entities: Courses, Professors
• Each course has id, title, time
• Make up suitable attributes for professors
INFO20003 Database Systems 20© University of Melbourne
Now you try
1. Every professor must teach some course.
2. Every professor teaches exactly one course (no more, no less).
3. Every professor teaches exactly one course (no more, no less),
and every course must be taught by some professor.
INFO20003 Database Systems 21© University of Melbourne
The Entity-Relationship Model
• Basic ER modeling concepts
• Constraints
• Conceptual Design
Readings: Chapter 2, Ramakrishnan & Gehrke, Database Systems
INFO20003 Database Systems 22© University of Melbourne
Conceptual Design Using the ER Model
• Design choices:
– Should a concept be modelled as an entity or an attribute?
– Should a concept be modelled as an entity or a
relationship?
– Should we model relationships as binary, ternary, n-ary?
• Constraints in the ER Model:
–A lot of data semantics can (and should) be captured
INFO20003 Database Systems 23© University of Melbourne
Entity vs. Attribute
Example:
Should “address” be an attribute of Employees or an entity
(related to Employees)?
Answer:
• Depends upon how we want to use address information, and
the semantics of the data:
– If we have several addresses per employee,
address must be an entity
INFO20003 Database Systems 26© University of Melbourne
Notes on the ER design
• ER design is subjective. There are often many ways to model
a given scenario.
• Analyzing alternatives can be tricky, especially for a large
enterprise. Common choices include:
–Entity vs. attribute, entity vs. relationship, binary or n-ary
relationship.
• There is no standard/notation (we will cover two notations,
today we learned Chen’s notation)
INFO20003 Database Systems 27© University of Melbourne
Summary of Conceptual Design
• Conceptual design follows requirements analysis
–Yields a high-level description of data to be stored
• ER model popular for conceptual design
–Constructs are expressive, close to the way people think
about their applications
–Originally proposed by Peter Chen, 1976
Note: there are many variations on ER model
• Basic constructs: entities, relationships, and attributes (of
entities and relationships)
• Some additional constructs: weak entities
INFO20003 Database Systems 28© University of Melbourne
Database Development Lifecycle: Review
Today
Database Planning
Systems Definition
Requirements Definition
and Analysis
Data Conversion and
Loading
Implementation
Application Design
Testing
Operational
Maintenance
Design
Physical Design
Logical Design
Conceptual Design
Next time
INFO20003 Database Systems 29
What’s Examinable
© University of Melbourne
• Need to be able to draw conceptual diagrams on your own
‒ Given a problem, determine entities, attributes, relationships
‒ What is key constraint and participation constraint, weak entity?
‒ Determine constraints for the given entities & their relationships
INFO20003 Database Systems 30
Next Lecture
© University of Melbourne
• Continue exploring modelling
– From conceptual through to physical
– Introducing relational model