INFO20003 Database Systems
INFO20003 Database Systems 1
INFO20003 Database Systems
Lecture 03
Introduction to Data Modelling (ER)
Semester 2 2018, Week 2
Dr Renata Borovica-Gajic
INFO20003 Database Systems 2© University of Melbourne 2018
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 3© University of Melbourne 2018
The Entity-Relationship Model
• Basic ER modeling concepts
• Constraints
• Conceptual Design
Readings: Chapter 2, Ramakrishnan & Gehrke, Database Systems
INFO20003 Database Systems 4© University of Melbourne 2018
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 5© University of Melbourne 2018
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)
Employees
ssn
name
lot
key
entity set
attribute
INFO20003 Database Systems 6© University of Melbourne 2018
ER Model: Relationship
• Relationship: Association among two or more entities.
Relationships can have their own attributes.
–Example: Fred works in Pharmacy department.
• Relationship Set: Collection of relationships of the same type.
–Example: Employees work in departments.
lot
name
Employees
ssn
Works_In
since
dname
budgetdid
Departments
relationship set
(with an attribute)
INFO20003 Database Systems 7© University of Melbourne 2018
ER Model: Relationship roles
Same entity set can participate in:
• different relationship sets, or even
• different “roles” in the same set
subor-
dinate
super-
visor
Reports_To
since
Works_In
dname
budgetdid
Departments
lot
name
Employees
ssn
different roles
different
relationships
INFO20003 Database Systems 8© University of Melbourne 2018
The Entity-Relationship Model
• Basic ER modeling concepts
• Constraints
• Conceptual Design
Readings: Chapter 2, Ramakrishnan & Gehrke, Database Systems
INFO20003 Database Systems 9© University of Melbourne 2018
Key Constraints: Types
1-to-11-to ManyMany-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 10© University of Melbourne 2018
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”.
Employees
dname
budget
did
Departments
since
Works_In
lot
name
ssn
Employees DepartmentsWorks_in
manymany
INFO20003 Database Systems 11© University of Melbourne 2018
Key Constraints: One-to-Many
Example:
Each department has at most one manager.
This is the key constraint on Manages.
since
Manages
dname
budget
did
Departments
lot
name
ssn
Employees
Employees DepartmentsManages
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 key constraint and is represented by an “arrow”.
Key
constraint
INFO20003 Database Systems 12© University of Melbourne 2018
Participation Constraints
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).
lot
name dname
budget
since
did
since
Manages
since
DepartmentsEmployees
ssn
Works_In
Employees DepartmentsWorks_in
Total
participation
Total
participation
Employees DepartmentsManages
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 is partial. Total
participation says that each entity takes part in “at least one” relationship, and
is represented by “bold” line.
INFO20003 Database Systems 13© University of Melbourne 2018
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 one-to-many
relationship set (one owner, many weak entities)
• Weak entity set must have total participation in this relationship set. Such
relationship is called identifying and is represented as “bold”.
…
name
agepname
DependentsEmployees
ssn
Policy
cost
Weak entities have only a “partial key” (dashed underline)
and they are identified uniquely only when considering
the primary key of the owner entity
INFO20003 Database Systems 14© University of Melbourne 2018
Ternary Relationships
In general, we can have n-ary relationships, and
relationships can have attributes
Suppliers
qty
DepartmentsContractParts
This is a ternary relationship
with one relationship attribute
INFO20003 Database Systems 15© University of Melbourne 2018
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!
• How do we record qty?
Suppliers
qty
DepartmentsContractParts
Suppliers
Departments
deals-with
Parts
can-supply
VS.
Are these two models the same?
INFO20003 Database Systems 16© University of Melbourne 2018
Now you try
University database schema:
• Entities: Courses, Professors
• Each course has id, title, time
• Make up suitable attributes for professors
INFO20003 Database Systems 17© University of Melbourne 2018
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 19© University of Melbourne 2018
The Entity-Relationship Model
• Basic ER modeling concepts
• Constraints
• Conceptual Design
Readings: Chapter 2, Ramakrishnan & Gehrke, Database Systems
INFO20003 Database Systems 20© University of Melbourne 2018
Conceptual Design Using the ER Model
• Design choices:
–Should a concept be modeled as an entity or an attribute?
–Should a concept be modeled as an entity or a
relationship?
– Should we model relationships as binary, ternary, n-ry?
• Constraints in the ER Model:
–A lot of data semantics can (and should) be captured
INFO20003 Database Systems 21© University of Melbourne 2018
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
– If the structure (city, street, etc.) is important, address
should be modeled as an entity
INFO20003 Database Systems 24© University of Melbourne 2018
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 25© University of Melbourne 2018
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 26© University of Melbourne 2018
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 27
What’s Examinable
© University of Melbourne 2018
• 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 28
Next Lecture
© University of Melbourne 2018
• Continue exploring modelling
– From conceptual through to physical
– Introducing relational model