ISYS3414

Practical Database Concepts

Week 4
Entity-Relationship Diagram
Today’s agenda

1. Entity-Relationship (ER) Model

2. Entity Relationship Diagram (ERD)
• Entity
• Attribute
• Relationship
3. ERD design exercise
Recap | Relational algebra and SQL

Write relational algebra expressions and corresponding SQL commands for the
following queries
Recap | Relational algebra and SQL
Which items did Clark, Charles buy?
𝛿 𝐶𝑙𝑖𝑒𝑛𝑡=′𝐶𝑙𝑎𝑟𝑘 ′𝑂𝑅 𝐶𝑙𝑖𝑒𝑛𝑡=′𝐶ℎ𝑎𝑟𝑙𝑒𝑠′ (𝑆𝐴𝐿𝐸)

SELECT * FROM SALE

WHERE Client = ‘Clark’ OR Client = ‘Charles’
Recap | Relational algebra and SQL
Which items did Clark, Charles buy?
𝐼𝑇𝐸𝑀 ⋈𝐼𝑇𝐸𝑀.𝑇𝑦𝑝𝑒=𝑆𝐴𝐿𝐸.𝐼𝑡𝑒𝑚 (𝛿 𝐶𝑙𝑖𝑒𝑛𝑡=′ 𝐶𝑙𝑎𝑟𝑘 ′ 𝑂𝑅 𝐶𝑙𝑖𝑒𝑛𝑡=′ 𝐶ℎ𝑎𝑟𝑙𝑒𝑠 ′ (𝑆𝐴𝐿𝐸))

SELECT SALE.Client, ITEM.Descrip, ITEM.Type, SALE.SDate

FROM SALE, ITEM
WHERE ITEM.Type = SALE.Item AND (Client = ‘Clark’ OR Client = ‘Charles’ )
Recap | Relational algebra and SQL
Which items have been sold in 1998?
𝐼𝑇𝐸𝑀 ⋈𝐼𝑇𝐸𝑀.𝑇𝑦𝑝𝑒=𝑆𝐴𝐿𝐸.𝐼𝑡𝑒𝑚 (𝛿 𝑆𝐷𝑎𝑡𝑒>19980000 (𝑆𝐴𝐿𝐸))

SELECT SALE.Item, ITEM.Type, SALE.NumSold, SALE.SDate

FROM SALE, ITEM
WHERE ITEM.Type = SALE.Item AND SALE.SDate > 19980000
Recap | Relational algebra and SQL
Report details of items with their name, price, number in stock and sold
ITEM_DETAIL  ((𝐼𝑇𝐸𝑀 ⋊ 𝑆𝑇𝑂𝐶𝐾) ⋊𝐼𝑇𝐸𝑀.𝑇𝑦𝑝𝑒=𝑆𝐴𝐿𝐸.𝐼𝑡𝑒𝑚 𝑆𝐴𝐿𝐸)
𝜋𝐼𝑇𝐸𝑀.𝑇𝑦𝑝𝑒,𝐼𝑇𝐸𝑀.𝐷𝑒𝑠𝑐𝑟𝑖𝑝,𝐼𝑇𝐸𝑀.𝑃𝑟𝑖𝑐𝑒,𝑆𝑇𝑂𝐶𝐾.𝑁𝑢𝑚𝑆𝑡𝑜𝑐𝑘,𝑆𝐴𝐿𝐸.𝑁𝑢𝑚𝑆𝑜𝑙𝑑 (ITEM_DETAIL)
SELECT ITEM.Type, ITEM.Descrip, ITEM.Price, STOCK.NumStock, SALE.NumSold
FROM ITEM, STOCK, SALE
WHERE ITEM.Type = STOCK.Type AND ITEM.Type = SALE.Item
Database Design Process

Hardware and
Information about the Problem
Operating System
Characteristics

Requirement Conceptual Logical

Specification Schema Schema
(Text) (Relational / Physical
(ER / UML)
SQL) Schema

Requirements Conceptual Physical

Logical
Gathering and Design Design
Design
Analysis

(Week 3 Relational Algebra)

2
Database Design Process
Requirement Conceptual Logical Physical
Specification Schema Schema Schema
(Text) (ER /UML) (Relational /SQL) (…)

Requirements Conceptual Physical

Logical
Gathering and Design Design
Design
Analysis
 Entity-Relationship (ER) Model

• Mostly used to draw an initial design of a database

o connection between the textual description of requirements and the
relational schema

• Describes the information that we want to store in a graphical way

o High-level view, easy to understand
o There are many different graphical notations

10
Entity-Relatioship Diagram (ERD)| Notation

Various notations
 Entity-Relationship Diagram (ERD)| Notation
We will use Crow’s foot notation
Why is it named “Crow’s foot”?
ER Diagram - Movie DB Example
Entity & Attribute
1
6

Entity
What are entities?
Distinguishable Real world objects or concepts
Ex: student, school, course, movie,…
Identified by nouns
In ER diagram
Entities are represented by rectangles Movie
Notes: Entity set vs Entity
Ex. Student vs student Tuan Tran; University vs RMIT; Course vs Practical DB Concepts
Entity set = Entity type = Blueprint or design of an entity
Entity = a concrete, individual object or occurrence
Entity

Described by attributes (at least one)

title year
Ex: student’s name, student’s address, movie’s name,...
Every entity of the same set has the same attributes
Express the detail level that we want to store about an entity set
Movie

Every movie (entity) in Movies (entity

set) have two attributes (title and year)
Entity
ER diagram: The
primary key is
underlined

• Each entity is uniquely identified by a key (one attribute or a set of

attributes) title year
Ex. student’s id, employee’s social security number,…
• An entity can have several candidate keys
o Employee’s social security number, employee’s id number
o One of candidate keys is selected as the primary key

Movie
Attributes
A specific property of an Entity or a Relationship
o An attribute can NOT have attributes
Attributes are represented by ovals title year

M ovie
Examples

A student is assigned with student number, accumulates to a specific

academic year with a GPA, has a email for communication, and a
major to study.

year
ID
GPA

Student email
Example

A project is a temporal endeavor to produce a result such as a product, a service, or a

guideline
temporal = having a start and and end date
endeavor = number of work hours
outcome = product or service or guideline
start_date
ID
end_date

Project outcome
Relationships & Multiplicity
Relationships
What are relationships?
• Associations between two or more entities
Ex. students enrolled in courses, actors stars_in movies

• Identified by verbs
In ER diagram
Relationships are represented by Diamond symbol
Notes: Relationship set vs Relationship
Relationship set = Relationship type = blueprint or design of a relationship between
entity types
Relationship = a specific relationship occurrence among individual entity occurences.
Relationships

Relationships sets:
Binary relationship
Shared transferred data between entities

• Binary relationships: between 2 entity types

• Ternary relationships: between 3 entity types

• Quarternary relationships: between 4 entity types

• n-ary relationships: between n entity types

Degree of a relationship = number of entities

participating in the relationship

Ternary relationship
Example | binary relationship

Student takes Course

Illustration: A student takes a course

Example | ternary relationship

Student joins Course

Lecturer

Illustration: Student (Michael) joins course

(Philosophy) delivered by Lecturer (Gabriel)
Example | quarternary relationship
Coordinator

Student joins Course

Lecturer

Illustration: Student (Michael) joins course (Philosophy) delivered

by Lecturer (Gabriel) and managed by course coordinator (Peter)
Example

Is there a relationship between entities of one entity type?

Software Engineer supervises

team lead

Illustration: Engineer (John) is a team lead and supervises engineer (Alex)

Unary relationship = relationship between entities of one entity type

Relationships

A relationship can have attributes

These attributes store information about the relationship
(not about the entities involved)
Ex. An actor has a role when he stars in a movie

Reminder:
A Relationship is NOT a Relation
Relationships

Example title year name address

role

Movies Stars_in Actors

Instance (or occurrence)

M ark Renton
“Trainspotting”
Ewan M cGreg or
O bi-Wan Kenobi
“Star Wars – Episode III”
Padmé A midala
N atalie Portman
“Jackie”
Jackie Kennedy
Multiplicity

Multiplicity is the number of instances of an entity type related to a single

instance of an associated entity type.

Four types of multiplicity

• Many – to – Many
• One – to – Many
• Many – to – One
• One – to – One
Multiplicity
Many-to-Many
1 or more 1 or more 0 or more 0 or more 1 or more 0 or more 0 or more 1 or more

“Each movie Star joins 0 or several movies” “Each Movie stars 1 or several actors”

23
Multiplicity
One-to-Many
must have 1 1 or more must have 1 0 or more 0 or 1 0 or more 0 or 1 1 or more

“Each movie Star joins 0 or 1 movie” “Each Movie stars 1 or several actors”

23
Multiplicity
One-to-One
must have 1 must have 1 0 or 1 0 or 1 0 or 1 must have 1 must have 1 0 or 1

“Each movie Star joins 0 or 1 movie” “Each Movie stars 0 or 1 actor”

23
Relationship | Participation type
Mandatory participation = Every possible instance of an entity must participate in a relationship
Optional participation = Some instances of an entity do not have to participate in a relationship

Participation type

Multiplicity

23
Relationships
• An entity can participate in relationships with several other entities
• There can be several relationships between 2 entities

R1 Entity B

R_b
Entity A

R2 Entity C

R… Entity …
Movie DB Example
Examples

How many employees can manage one How many employees can work in one
26

department and how many departments department and how many departments
can be managed by one employee? can be worked in by one employee?
Exercise
Exercise 2: Music Copyrights Database

40
(Recall) Entity-Relatioship Diagram (ERD)| Notation
We will use Crow’s foot notation
ERD | Step 1.Identify entities
ERD | Step 1.Identify entities
ERD | Step 1.Identify entities

Concert
ERD | Step 2. Identify entities and attributes
ERD | Step 2. Identify entities and attributes
ERD | Step 2. Identify entities and attributes

Concert
ERD | Step 3. Identify Relationships
ERD | Step 3. Identify Relationships
ERD | Step 3. Identify Relationships

Concert
ERD | Step 4. Identify Relationships and cardinalities
ERD | Step 4. Identify Relationships and cardinalities

Concert
ERD | Step 5. Identify attributes of a relationship
ERD | Step 5. Identify attributes of a relationship
Further reading

Chapter 12. Entity – Relationship Modeling

Appendix C. Alternative ER Modeling Notation