Conceptual Modeling

with ER Diagrams
Peter Chen
introduced ER Diagrams
 E-R Modeling
E-R Modeling is a design methodology for
modeling the
– Entities
– Relationships
using a diagram with specified shapes for
entities, relationships, roles of entities in these
relationships, and cardinality.
 E-R Diagram Conventions
Symbol Meaning

Entity Type

Weak Entity Type

Relationship

Identifying Relationship

Attribute
 ER-Diagram Conventions - CONTINUED

Symbol Meaning

Key Attribute

Multivalued Attribute

Composite Attribute
 Entities, Relationships and Attributes
• A entity is a object or event which needs to be tracked. Attributes characterize entity sets and relationships.
Relationships relate two entity sets

Employee Name

SSNum

Employee Assigned Project

Name Date
 Payroll Application
A contractor does custom work (projects). He
keeps track of employee time for each project.
Besides materials, the customer is charged by
invoice for employee time on his job. To keep
track of employee time, the employee submits a
time sheet with entries of project number, hours,
description. These are entered into the system
along with the employee ID. The hourly rate for
the employee is used for each entry to generate
a labor charge. The labor charges are totaled
for each project and used as a basis for the
customer invoice.
 Understanding the Requirements

Conference
Management

End
Analysts
Users

• Management – know high level business rules

• End users – have used paper system or previous software
system and have expectations
• Analysts – have understanding of what can / cannot be done
and must requirements into system
 ER Diagrams for Payroll

Step 1. Identify the Entity Sets with Attributes

• Customers : Name, Address,Phone,CustID

• Projects : Name,Description,ProjID,Total,CustID
• Employees : Name,SSNo,PayRate,EmpID
• LaborCharges : EmpID,ProjID,Description,Hours,Amount
 ER Diagrams for Payroll

Step 1. Identify Relationships between Entity Sets with

any Attributes

• Customers Has Project … Project is Weak Entity Set

• Employee Works_On Project
• Employee Has_Labor_Charge For Project … Labor_Charge is Weak Entity Set
 E-R Modeling – Entity Sets, Attributes, Relationships
Racquet Club Example

• A Racquet Club has members with one

year or two year contracts.
• Members pay contract amount up front,
quarterly, or monthly.
• Members may reserve a court for the next
day.
 E-R Modeling –
Entity Sets, Attributes, Relationships
Racquet Club Example Continued

• Entity Sets
– Members : Name, Start, Contract_Amount,
Contract_Period, Initial_Balance, Member_ID
– Payments : PayerID, Amount, Paid_Date,
Payment_ID
– Reservations : Member_ID, CourtNumber, Hour
• Relations :
– Member - Makes – Payment
– Member - Makes - Reservation
 Data Modeling –
Racquet Club Example
 Keys, Cardinality Constraints and
Participation Constraints
• A key of a relation is a minimal set of
attributes and roles which uniquely determine
each entity.
• A cardinality constraint m..n constrains the
number of times a single entity can participate
in a role of a relationship : m <= times <= n.
• A participation constraint ensures that an
entity will particpate at least this many times.
 ER-Diagram Cardinality Conventions
Symbol Meaning

Calculated Attribute

Cardinality Constraints and Participation Constraints

0 .. *

0 .. 1

1 .. *

1 .. 1
 Cardinality and Participation
in the E-R model.

1 .. 2 means that at least 1 d must participate in the A relation with C and

that not more that 2 d’s can participate. If A is represented by a table, then
there is at least one row and not more than 2 rows with a single c value.
Many-to-one, one-to-one, and many-to-many
correspondences
 Cardinality & Participation Constraints

Library
Circulation
Motel/Hotel

Video Store

Employment
Company
 Cardinality & Participation Constraints

Library Book is_checked_out to Patron

Circulation
Motel/Hotel Customer rents a room

Video Store Customer rents a video

Employment Applicant is hired for a job

Company
Example of an E-R diagram with an ISA hierarchy.
 Using IsA for data partitioning.

New York Customer Pennsylvania Customer

 Participation Constraints
A participation constraint is a lower bound on the
number of times an entity can particpate in a
relation.
Examples:
An employee belongs to ONE department. This is a
participation and a cardinality constraint 1 .. 1.
Employee Department

0 .. *
0 .. 1
1 .. *
1 .. 1
Participation constraints.
 Relationship Types – Two Entity Set Relations

An employee is assigned to at least one project (participation)

Employee Assigned Project

To

Num-Hours
A customer will pay for
at least one project

Customer Will Pay Project

For

Fixed-Cost
 Relationship Types – One & Three Entity Set Relations

One Set : ReportsTo( SupID, SubID )

subordinate

Employee Reports_To
supervisor

Three Sets :
Sale( ProdID, CustID,SuppID;Date)

Customer Sold Product

Price
Date Supplier
 ER Diagrams for Payroll

Name Phone

Customer

Address CustID

Has

Name
CustID

Description Projects

ProjID
Total
 ER Diagrams for Payroll
Name PayRate

Employee

SSNo EmpID
Has

Year
EmpID

Month TimeSheet

TSID

Has Has
TSID
TSID
...
... Labor Charges PayCheck

NetPay
Amount
 The IS-A Relationship

Name Employee SSNo

Is-A

Salaried disjoint Hourly

Employee Employee

PayRate
Salary
 Construct an ER Diagram

• Lawn Furniture Manufacturer

• Stores Information for Catalog of products
and parts
• Stores Information for Manufacturing,
including quantity on hand
• Identify product entities (picnic table and
lawn chair) with attributes
• Identify relationships IsA and PartOf
 The Part-Of Relationship
OutDoorProducts

IsA

Picnic_Table Lawn_Chair

ProdNo Price Price ProdNo

Part_Of

Top Base Seats

Quantity
Price ProdNo
 From E-R Diagrams to
Relational Database Schema
• Converting entities into relations (tables)
– Each entity becomes a relation
– Each attribute of the entity becomes an
attribute of that relation (column)
– If attributes K1 ... Kn form a key of the entity,
then K1 ... Kn form a candidate key of the
relation.
 From ER Diagrams
to Relational Database Schema
• Translating Entities

Name Employee SSNo

Type Termination
HireDate

Create Table Employee ( Name char(20), Type char(1), HireDate date,

Termination date, SSNo char(9),
Primary Key (SSNo) )
 From E-R Diagrams to
Relational Database Schema
• Representing relationships in the database
schema
– R is a relationship between entity set A and weak
entity set B
• A becomes a relation with primary key K and B+K becomes
a relation with K being a foreign key – e.g. employees and
dependents
– R is a relationship between entity sets A,B and C
• A,B and C become relations with KA , KB , and Kc as keys. R
becomes a relation with foreign keys KA , KB , and Kc
 From ER Diagrams
to Relational Database Schema
• Translating Relationships SaleID

Customer Sold Product

Price
SaleDate Supplier

Create Table Sold ( SaleDate Date, Price Currency, SaleID autonumber,

Customer Integer, Supplier Integer, Product Integer,
Primary Key (SaleID),
Foreign Key ( Customer ) References Customers( CustID),
Foreign Key ( Supplier ) References Suppliers( SuppID ),
Foreign Key ( Product ) References Products ProdID ) )
 Translate into Schema

Name Employee SSNo

Is-A

Salaried disjoint Hourly

Employee Employee

PayRate
Salary
 Translate Into Schema
OutDoorProducts

IsA

Picnic_Table Lawn_Chair

ProdNo Price Price ProdNo

Part_Of

Top Base Seats

Quantity
Price ProdNo