Database Systems
Fall 2025
Recitation 6
Agenda for Today
ERD – Entity-Relationship Diagrams
Conceptual and Logical Data Models
The logical data model describes the data in a much more detailed form than the conceptual data model.
Conceptual Data Model -�Least detailed
Logical Data Model – �More detailed
Conceptual Data Model -�Least detailed
Logical Data Model – �More detailed
ERD
Basic components
8
Entity sets
Attributes
Relationships between entities
Product
name
buys
Set: there can be many or no products, similar to a product table
Similar to a table column
We’ll see
Keys
Every entity set must have a unique identifier = key
9
Product
name
category
price
Example
10
buys
makes
employs
Company
name
category
stockprice
name
price
address
name
ID
Person
Product
A relation(ship)
If A, B are (entity) sets, a relation R is a subset of AxB
E.g., A = {1, 2, 3}, B={a, b, c, d}, R = {<1,a>, <1,c>, <3,b>}
11
1
2
3
a
b
c
d
A relation(ship)
If A, B are (entity) sets, a relation R is a subset of AxB
R = {< Mware, iPud17s >, < Mware, iPud17 >, < HC, iPudMini>}
12
Mware
Snapple
HC
iPud17s
iPudMini
iPud17
smartWatch
makes
Company
Product
Relationship multiplicity in E/R
one-to-one
many-to-one
many-to-many
13
1
2
3
a
b
c
d
1
2
3
a
b
c
d
1
2
3
a
b
c
d
Referential Integrity
made by
Company
Product
made by
Company
Product
At most one
Exactly one
Enhanced example
15
buys
made by
employed�by
Company
name
category
stockprice
name
price
address
name
ID
Person
Product
Attributes on relationships
16
buys
name
category
price
address
name
ID
Person
Product
date
Multi-way relationships
17
purchase: a subset of Product x Store x Person
Purchase
Product
Person
Store
date
Subclasses in E/R ("isa" relationship)
18
medicalHistory
specialty
ID
name
address
Patient
Physician
Person
isa
isa
Always one-to-one relationship, but we don’t draw the two arrows
Weak Entities
Company
Product
made by
number
price
address
name
An entity such that its identifier depends on another entity.
The entity and the dependence relationship are described with a double outline.
Weak Entities
Company
Product
made by
number
price
address
name
Always use an exactly-one arrow with weak entities
Motivation for using Weak Entities
Company
Product
made by
number
price
address
name
made�by
Company
Product
number
price
address
name
Company name
Company name
Exercise
You are asked to design the database schema for a pizza company. The data you need to address is as follows:
Exercise (1A)
You are asked to design the database schema for a pizza company. The data you need to address is as follows:
Restaurant
Employee
address
ssn
name
salary
address
WorksIn
telNumber
Manager
Is a
ManagerOf
Exercise (1B)
You are asked to design the database schema for a pizza company. The data you need to address is as follows:
Restaurant
Employee
address
ssn
name
salary
address
WorksIn
telNumber
Manager
Is a
ManagerOf
Dish
name
Pizza
price
CookedBy
topping
Is a
Exercise (1C)
You are asked to design the database schema for a pizza company. The data you need to address is as follows:
Restaurant
Employee
address
ssn
name
salary
address
WorksIn
telNumber
Manager
Is a
ManagerOf
Dish
name
Pizza
price
CookedBy
topping
Is a
Customer
Order
OrderedBy
telNumber
name
date
includes
quantity
DeliveredBy
From ER Diagrams to Relational DB
Exercise (2)
Convert the following ERD into Relational DB:
Restaurant
Employee
address
ManagerOf
ssn
name
salary
address
WorksIn
telNumber
Exercise (2)
Convert the following ERD into Relational DB:
Restaurant
Employee
address
ManagerOf
ssn
name
salary
address
WorksIn
telNumber
Employee(SSN, name, salary, address, worksInRestaurantAddress)
Restaurant(address, telNumber, managerSSN)
EER Reverse Engineering with MySQL Workbench
EERD
Data Model Reverse Engineering
Reverse Engineering with MySQL Workbench
MySQL Workbench provides an interface for applying a logical EERD reverse engineering process.
Some Properties of the MySQL Workbench EERD
Line styling meaning:
Attributes' icons meaning: