Transcript Conceptual Modeling with ER Diagrams

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
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
E-R diagrams for several relationship types
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 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.
Two ways to represent singlerole key constraints.
Cardinality 0..1 can be represented by 0..1 or by
Many-to-one, one-to-one, and many-to-many
correspondences
Example of an E-R diagram with
an ISA hierarchy.
Using IsA for data partitioning.
New York Customer
Pennsylvania Customer
Participation constraints.
Line-based representation vs. cardinality
constraints.
Entities, Relationships and Attributes
• A entity is a object or event which needs to
be tracked.
Employee
• A relationship relates entities in two or
more entity sets.
Employee
Name
Project
Assigned
Date
• Attributes characterize entity sets and
relationships.
Relationship Types – Two Entity Set Relations
Employee
Assigned
To
Project
Num-Hours
Customer
Project
Will Pay
For
Fixed-Cost
Relationship Types – One & Three Entity Set Relations
One Set : ReportsTo( SupID, SubID )
subordinate
Reports_To
Employee
supervisor
Three Sets :
Sale( ProdID, CustID,SuppID;Date)
Customer
Date
Sold
Supplier
Product
Price
ER Diagrams for Payroll
Payroll System Entity Sets & Relationships
Customers : Name, Address,Phone,CustID
Projects : Name,Description,ProjID,Total,CustID
Employees : Name,SSNo,PayRate,EmpID
TimeSheets : TSID,EmpID,Year,Month
LaborCharges : TSID,ProjID,Description,Hours,Amount
PayrollChecks : TSID,GrossPay,SSDeduction,WithHolding,NetPay
CustomerInvoices : InvID,Year,Month,TotalCharge,SalesTax
W2Statements : Year,EmpID,GrossPay,SSPayments,Withholding
ER Diagram
Customers : Name, Address,Phone,CustID
Projects :
Name,Description,ProjID,Total,CustID
ER Diagrams for Payroll
Phone
Name
Customer
CustID
Address
Has
Name
Description
Total
CustID
Projects
ProjID
ER Diagram
Employees : Name,SSNo,PayRate,EmpID
TimeSheets : TSID,EmpID,Year,Month
LaborCharges :
TSID,ProjID,Description,Hours,Amount
PayrollChecks :
TSID,GrossPay,SSDeduction,WithHolding
,NetPay
ER Diagrams for Payroll
PayRate
Name
Employee
EmpID
SSNo
Has
Year
EmpID
TimeSheet
Month
TSID
Has
Has
TSID
TSID
...
Labor Charges
PayCheck
...
NetPay
Amount
The IS-A Relationship
Employee
Name
SSNo
Is-A
Salaried
Employee
Salary
disjoint
Hourly
Employee
PayRate
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
Lawn_Chair
Picnic_Table
Price
ProdNo
Price
Part_Of
Top
Seats
Base
Price
ProdNo
Quantity
ProdNo
From ER Diagrams
to Relational Database Schema
• Translating Entities
Employee
Name
SSNo
Type
Termination
HireDate
Create Table Employee ( Name char(20), Type char(1), HireDate date,
Termination date, SSNo char(9),
Primary Key (SSNo)
)
From ER Diagrams
to Relational Database Schema
• Translating Relationships
Customer
SaleDate
Sold
Supplier
SaleID
Product
Price
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
Employee
Name
SSNo
Is-A
Salaried
Employee
Salary
disjoint
Hourly
Employee
PayRate
Translate Into Schema
OutDoorProducts
IsA
Lawn_Chair
Picnic_Table
Price
ProdNo
Price
Part_Of
Top
Seats
Base
Price
ProdNo
Quantity
ProdNo