Transcript Comp 231 Database Management Systems 2. Entity Relationship (ER) Model

Comp 231 Database Management Systems
2. Entity Relationship (ER) Model
Department of Computer Science, HKUST 1
Basic Concepts
 A database can be modeled as
 a collection of entities
 relationship among entities
 An entity is an object that exists independently and is
distinguishable from other objects.
 an employee, a company, a car, etc.
 color, age, etc. are not entities
Simplicity is Beauty
A Picture is Worth a Million Words
Department of Computer Science, HKUST 2
• An entity set is a set of entities of the same type.
E.g., a set of employees, a set of departments
 also called entity types
Entity Type :
Entity set:
Employee
e1
e2
e3
A general
specification
The actual
employees
…
Department of Computer Science, HKUST 3
Attributes
• Properties of an entity or a relationship
– name, address, weight, height are properties of a Person
entity.
• date of marriage is a property of the relationship
Marriage.
Department of Computer Science, HKUST 4
Types of Attributes
• Simple attribute: contains a single value.
EmpNo
Employee
Name
Address
Department of Computer Science, HKUST 5
• Composite attribute: consists of several components
EmpNo
Name
Employee
Street
Address
City
Country
Department of Computer Science, HKUST 6
• Multivalue attribute: contains more than one value
Phone
Employee
Email
Department of Computer Science, HKUST 7
• Derived attribute: computed from other attributes
Age
Employee
Bonus
Department of Computer Science, HKUST 8
Key Attributes
• A set of attributes that can uniquely identify an entity
ERD
EmpNo
Employee
Name
tabular
EmpNo
Name
...
123456
John Wong
...
456789
Mary Cheung
...
146777
John Wong
...
Department of Computer Science, HKUST 9
Key Attributes
• Composite key: Name or Address alone cannot
uniquely identify an employee, but together they can!
Name
Employee
Address
Department of Computer Science, HKUST 10
Key Attributes
• An entity may have more than one key
– e.g., EmpNo, (Name, Address)
– only one is selected as the key. (sometimes called the
Primary key)
Name
In many cases, a
key is artificially
introduced (e.g.,
EmpNo) to make
applications more
efficient.
Address
Question: does a
desk has a key?
EmpNo
Employee
Department of Computer Science, HKUST 11
Relationship
• A relationship is an association between one or more
entities.
– Given a customer and an account, the relationship depositor
between them indicates that the customer deposits money into the
account.
Department of Computer Science, HKUST 12
CusNo
Name
AccNo
Name
Amount
Customer
Account
depositor
• A relationship may have attributes
• A relationship type or relationship set identifies
relationships of the same properties
Question: Could Amount be an attribute of
Customer? Or an attribute of Account?
What does Amount mean? How many
values you want to keep?
Department of Computer Science, HKUST 13
Representation of Relationship
• Tabular:
Depositor
CustomerNo AccountNo
A123456
A-101
500
B456789
A-201
900
B456789
A-302
700
• Graph:
Note: this is
NOT an ERD
Amount
A123456
B456789
500
The amount
in each
deposit.
A-101
900
A-201
700
Department of Computer Science, HKUST 14
A-202
Try an Alternative
• Represent Amount as an attribute of Account
AccountNo
Name
Amount
A-101
Current
500
A-201
Saving
900
A-302
Current
700
• Consider Amount as the balance of an account (I.e.,
one value per account) or as the last deposit amount.
• “Multivalue” attribute, though allowed in ER model, is
difficult to implement
Department of Computer Science, HKUST 15
Cardinality of Relationships
• Number of entities that can be associated together in
a relationship set.
• 1:1
Customer
Borrows
Loan
 A customer can borrow 1 loan and vice versa
Department of Computer Science, HKUST 16
1:N
Customer
Customer
Loan
Borrow
Loan
 A Customer can borrow more than 1 loan,
whereas a loan has only one borrower.
Department of Computer Science, HKUST 17
N : M Relationships
Customer
Borrow
loan
 A customer can borrow more than one loan
 A loan can have more than one borrower.
Department of Computer Science, HKUST 18
Notes
• Cardinality specifies the maximum condition.
1:1
N:M
1:N
 The minimum is specified by
existence constraints (explained
later)
 Conditions must be satisfied at all
times
Department of Computer Science, HKUST 19
Degrees of a Relationship Set
•
Number of entity sets participating in a relationship set.
- Binary
Customer
Borrow
Loan
- Ternary
Customer
Borrow
Loan
Branch
 A customer borrows
a loan from a branch.
 Relationships with degree >3 is very rare.
 Hint: translate a ternary relationship into one
sentence.
 Can you break it up into two or more sentences?
 A customer borrows
a loan. A loan is
made at a branch.
Department of Computer Science, HKUST 20
Recursive Relationship
• A relationship relating entitles of the same type
manager
Employee
work-for
worker
• Employees play different roles: manager or worker
– Without role names, you can’t tell whether 1 employee manages n
other employees or n employees manages 1 employee
• You can “unfold” a recursive relationship to understand it:
manager
Employee
worker
work-for
Employee
Department of Computer Science, HKUST 21
Tabular Representation of Recursive
Relationships
• Without Role Names
• With Role Names
EmployeeNo EmployeeNo
A1234
A6543
A1234
A8734
ManagerNo
WorkerNo
A1234
A6543
A1234
A8734
Where ManagerNo and WorkerNo are Valid
EmployeeNo
Department of Computer Science, HKUST 22
Existence Dependence
• The existence of an entity depends on the existence
of another entity
LoanNo
CusNo
Customer
loan
borrow
Loan
A loan cannot exist if
there is no borrower.
Department of Computer Science, HKUST 23
Weak Entities
• A weak entity cannot be identified with its own
attributes
 no key
• A weak entity implies existence dependency but NOT
vice versa
Department of Computer Science, HKUST 24
LoanNo
Loan
Amount
PaymentNo
Loan
payment
Date_pay
Amount
Payment
• A loan may have 240 payments, each identified by a payment
no 1 - 240.
• The PaymentNo is unique given a particular loan but not
unique globally
• PaymentNo is called partial key
• The primary key of Payment is the combination of
LoanNo and PaymentNo.
Question: Why not combine loan and
payment into one entity type?
Department of Computer Science, HKUST 25
Weak Entity vs Existence Constraint
• In the existence constraint example, LoanNo can
uniquely identify a Loan in the database so it is not a
weak entity.
• The existence constraint means that you cannot
create a Loan record without first knowing who
borrowed the loan.
Department of Computer Science, HKUST 26
• Another example of weak entity type
EmpNo
Name
Age
Employee
Emp_Dep
Dependent
• A child may not be old enough to have a HKID
number
• Even if he/she has a HKID number, the company may
not be interested in keeping it in the database.
Department of Computer Science, HKUST 27
What does a DB Design do?
Individual tools are easy to use, but using them
together to solve a problem is difficult. Let’s
examine a few problems...
Department of Computer Science, HKUST 28
Ternary Relationship
Customer
Borrow
Loan
A customer
borrows a loan
from a branch.
Branch
A customer
borrows a loan. A
loan is issued from
a branch.
Customer
Borrow
Note: these are all N:M relationships.
Department of Computer Science, HKUST 29
Loan
Issue
Branch
What are the Differences?
A customer
borrows a
loan from a
branch.
A customer
borrows a
loan. A loan is
issued from a
branch.
CusNo
A12345
B56789
CusNo
A12345
B56789
LoanNo
L-001
L-001
LoanNo
L-001
L-001
LoanNo
L-001
L-001
Department of Computer Science, HKUST 30
Branch
Central
Wanchai
Branch
Central
Wanchai
• Imagine a bank allows borrowers of the same loan to
go to difference branches for signing documents,
deposit payments, etc.
• The two schemes are not the same. The binary
relationships capture less information.
• Adding a third relationship won’t help.
CusNo
A12345
B56789
Branch
Central
Wanchai
Customer
Borrow
Loan
Issue
Cus_Br
Branch
Department of Computer Science, HKUST 31
Why?
Why?
• Customer, Loan and Branch
have a N:M:P relationship
L-001
A12345
B56789
L-002
Wanchai
Central
C54321
• John borrows a loan which is issued from Wanchai
branch
• N:N:1 relationship can be decomposed (A loan is
issued by ONE BRANCH ONLY)
Department of Computer Science, HKUST 32
Converting Non-Binary Relationships to Binary Form
• In general, any non-binary relationship can be represented using binary
relationships by creating an artificial entity set.
– Replace R between entity sets A, B and C by an entity set E, and three
relationship sets:
1. RA relating E and A
3. RC relating E and C
2. RB relating E and B
– Create a special identifying attribute for E
– Add any attributes of R to E
– For each relationship (ai , bi , ci) in R, create
1. a new entity ei in the entity set E
3. add (ei , bi ) to RB
2. add (ei , ai ) to RA
4. add (ei , ci ) to RC
Department of Computer Science, HKUST 33
Example
Loan
L-001
L-002
Issue
A12345
Customer
Borrow
Dummy
Cus_Br
Branch
B56789
Wanchai
Central
C54321
Customer Dummy
A12345
x001
Dummy
Loan
Dummy Branch
B56789
x002
x001
L-001
x001
Wanchai
B56789
x003
x002
L-001
x002
Central
C54321
x004
x003
L-002
x003
Wanchai
x004
L-002
x004
Wanchai
Department of Computer Science, HKUST 34
Binary relationships to Ternary?
• Binary relationships
may have different
meanings so that they
can’t be combined into
ternary relationships.
Customer
Borrow
Loan
Issue
Buy_stock
Branch
• You may have a ternary relationship
Customer-Loan-Branch
and other binary relationships between Customer, Loan and
Branch
Department of Computer Science, HKUST 35
A case Study
A primary school student writes a composition about a picnic:
Today is Sep 9, the weather is fine.
My classmates, John, Mary and I go to a picnic in Sai Kung.
Our teacher is Ms Wong
My Initial Design:
Name
Students
Picnic
Teacher
Name
date
destination
weather
Department of Computer Science, HKUST 36
Questions ?
• Why “John”, “Mary”, “Miss Wong” are not in the ER
diagram ?
• What do these names tell us ?
• What are the keys of Student, Picnic & Teacher ?
• What are the cardinalities of the relationships ?
Department of Computer Science, HKUST 37
destination
My solution
Name
•
•
•
StudentNo
Name
Student
goes
date
Picnic
weather
leading
Every student has an ID number, it is better to keep it in
the database and use it as a key
I bet that there won’t be teachers with the same name;
otherwise, I’ll add employee number and use it as a key
goes is N:M, why ? A picnic has more than one student
participating; also, a student can go to more than 1 picnic.
However, this N:M relationship allows a student to go to
more than one picnic on the same date
• leading is N:1 , why? Depends on your assumptions
•
Teacher
Question:
How to record
number of
– I assume a teacher can only lead 1 picnic on a certain date, so
students in a
given the teacher name and the date, I can identify a picnic
Picnic is made a weak entity. I could have added a PicnicNo, picnic?
but it would be very awkward.
Department of Computer Science, HKUST 38
E-R Design Decisions
• The use of an attribute or entity set to represent an
object.
– Should an address be an attribute or entity?
• Whether a real-world concept is best expressed by an
entity set or a relation set.
– Should marriage be an entity or relationship?
– Should picnic be an entity or relationship?
• The use of a ternary relationship versus a pair of
binary relationships.
– See the borrow-loan-branch example
• The use of a strong or weak entity set.
– See the employee-dependent example
Department of Computer Science, HKUST 39
E-R Diagram for Company Database
Number
Fname
Minit
Name
Lname
Address
Sex
WORKS_FOR
Salary
Ssn
Locations
Name
Startdate
EMPLOYEE
DEPARTMENT
Number Of Employees
Bdate
MANAGES
supervisor
CONTROLS
supervisee
Hours
SUPERVISION
WORKS_ON
PROJECT
Name
Location
DEPENDENTS_OF
Number
DEPENDENT
Name
Sex
Birthdate Relationship
Can you translate it back
into English?
Department of Computer Science, HKUST 40
Limitations of ER model
• Consider representing Part-time and Full-time
employees in the company database:
– Either you have two entity types will lots of similarity
– Or you have a single entity type with redundancy for most of
the entities within it
• ER model is extended to support other features such
as generalization (but it won’t be covered in this
course!)
Department of Computer Science, HKUST 41
Reduction of an E-R Schema to tables
• Primary keys allow entity sets and relationship sets to be
expressed uniformly as tables which represent the contents
of the database.
• A database which conforms to an E-R diagram can be
represented by a collection of tables. Always!
• Converting an E-R diagram to a table format is the basis for
deriving a relational database design from an E-R diagram.
Department of Computer Science, HKUST 42
Translating ERDs into Tables
Department of Computer Science, HKUST 43
Representing Entity Sets as Tables
• A strong/regular entity set reduces to a table with the same attributes.
customer
customer-name
customer-id
customer-street
customer-city
Jones
321-12-3123
Main
Harrison
Smith
019-28-3746
North
Rye
Hayes
677-89-9011
Main
Harrison
cust-city
cust-no
customer
borrow
loan
date
cust-name
cust-id
share%
Department of Computer Science, HKUST 44
loan-no
Representing Weak Entity Sets as Tables
Amount
loan
Amount
Loanpayment
payment
date
Loan-no
payment-no
• A weak entity set becomes a table that includes a column for the primary
key of the identifying strong entity set
Composite key
payment
loan-no
payment-no
payment-date
payment-amount
L-17
5
10 May 1999
50
L-17
11
17 May 1999
75
L-15
5
23 May 1999
300
Department of Computer Science, HKUST 45
Representing Relationship Sets as Tables
• A many-to-many relationship set is represented as table with
columns for the primary keys of the two participating entity sets,
and any descriptive attributes of the relationship set.
borrower
cust-no
loan-no
share
A12345
L-17
10 May 1999
B45678
L-17
17 May 1999
B45678
L-15
23 May 1999
customer
borrow
loan
date
cust-name
cust-no
share%
Department of Computer Science, HKUST 46
loan-no
customer
borrow
cust-name
cust-no
loan
date
loan-no
 For 1:N and 1:1 relationships, you can
create a table for each relationship
loan-record
Cust-no
Loan-no
A12345
L-001
B56789
L-002
 But it is more concise to merge the relationship-table with the entity-table
on the “N” side
customer
cust-no
cust-name
A12345
Peter Wong
B56789
Mary Cheung
indicates who
borrowed the loan
loan
loan-no
date
cust-no
L-001
Sep 2000
A12345
L-002
Aug 2001
B56789
Department of Computer Science, HKUST 47
Questions to Think About
 In a 1:N relationship, can we
include the key from the “N”
side in the table representing
the entity in the “1” side? I.e.,
include Loan_no into the
Customer table. Why and Why
not?
CusNo
Cust_name
Loan_no
A12345
Peter Wong
B56789
Mary Cheung
...
...
 How can we express existence constraints on table?
Department of Computer Science, HKUST 48
Questions to Think About (Cont.)
 In a 1:1 relationship, we can include the key from either entity into
the table representing the other entity. Suppose the Loan-Customer
relationship is 1:1, would you include the Customer_no into Loan or
Loan_no into Customer?
customer
borrow
loan
cust-name
cust-no
date
loan-no
customer
cust-no
cust-name
loan-no
A12345
Peter Wong
L-001
B56789
Mary Cheung
L-002
loan
loan-no
date
cust-no
L-001
Sep 2000
A12345
L-002
Aug 2001
B56789
Which one is better?
Department of Computer Science, HKUST 49
Questions to Think About (Cont.)
How can we express existence constraints on table?
customer
borrow
loan
cust-name
cust-no
loan-no
loan
loan-no
date
cust-no
L-001
Sep 2000
A12345
L-002
Aug 2001
date
Not allowed; must be
enforced by DBMS
Department of Computer Science, HKUST 50
Weak Entities
 Since a weak entity has to include the primary key of the
identifying entity, the 1:N relationship is already captured. E.g.,
The payment table already contains information about the Loan
(I.e., loan_no)
Already indicates the
1:N relationship between
loan-no and payment-no
payment
loan-no
payment-no
payment-date
payment-amount
L-17
5
10 May 1999
50
L-17
6
17 May 1999
75
L-17
7
23 May 1999
300
Department of Computer Science, HKUST 51
Questions to Think about: Relationship or attribute?
CusNo
Name
AccNo
Name
Amount
Customer
Account
depositor
 We have seen this example before.
 Questions: Can I put every attribute on an entity type?
Department of Computer Science, HKUST 52