Department of Computer Science and Engineering, HKUST

1
Comp 231 Database Management Systems
2. Entity Relationship (ER) Model
2
Basic Concept

• 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

3
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

Employee
Entity Type
A general specification
e1
The actual employees
Entity set
e2
e3

4
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.

5
Types of Attributes

• Simple attribute contains a single value.

EmpNo
Employee
Name
Address
6
Composite attribute consists of several
components
EmpNo
Name
Employee
Street
Address
City
Country
7
Multivalue attribute contains more than one
value
Phone
Employee
Email
8
Derived attribute computed from other attributes
Age
Employee
Bonus
9
Key Attributes

• A set of attributes that can uniquely identify an
  entity

ERD
tabular
10
Key Attributes

• Composite key Name or Address alone cannot
  uniquely identify an employee, but together they
  can!

Name
Employee
Address
11
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)

In many cases, a key is artificially introduced
(e.g., EmpNo) to make applications more efficient.
Question does a desk has a key?
12
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.

13

• 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?
14
Representation of Relationship

• Tabular

Depositor
The amount in each deposit.
Note this is NOT an ERD
15
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

16
Cardinality of Relationships

• Number of entities that can be associated
  together in a relationship set.
• 1 1

17
1N
Customer
Loan
18
N M Relationships
19
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

20
Degrees of a Relationship Set

• Number of entity sets participating in a
  relationship set.

• A customer borrows a loan from a branch.

• Relationships with degree gt3 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.

21
Recursive Relationship

• A relationship relating entitles of the same type

• Employees play different roles manager or worker
• Without role names, you cant tell whether 1
  employee manages n other employees or n employees
  manages 1 employee
• You can unfold a recursive relationship to
  understand it

22
Tabular Representation of Recursive Relationships

• Without Role Names
• With Role Names
• Where ManagerNo and WorkerNo are Valid EmployeeNo

23
Existence Dependence

• The existence of an entity depends on the
  existence of another entity

LoanNo
CusNo
loan borrow
Customer
Loan
24
Weak Entities

• A weak entity cannot be identified with its own
  attributes? no key
• A weak entity implies existence dependency but
  NOT vice versa

25
LoanNo
Amount
Date_pay
PaymentNo
Amount
Loan
Loan payment
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?
26
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.

27

• Another example of weak entity type

EmpNo
Name
Age
Emp_Dep
Employee
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.

28
What does a DB Design do?
Individual tools are easy to use, but using them
together to solve a problem is difficult. Lets
examine a few problems...
29
Ternary Relationship

Note these are all NM relationships.
30
What are the Differences?
31

• 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 wont help.

Borrow
Customer
Loan
Issue
Cus_Br
Branch
32
Why?
Why?

• Customer, Loan and Branch have a NMP
  relationship

• John borrows a loan which is issued from Wanchai
  branch

• NN1 relationship can be decomposed (A loan is
  issued by ONE BRANCH ONLY)

33
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 2. RB relating E and
  B
• 3. RC relating E and C
• 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
  2. add (ei , ai ) to RA
• 3. add (ei , bi ) to RB
  4. add (ei , ci ) to RC

34
Binary relationships to Ternary?

• Binary relationships may have different meanings
  so that they cant be combined into ternary
  relationships.

• You may have a ternary relationshipCustomer-Loan-
  Branchand other binary relationships between
  Customer, Loan and Branch

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
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 ?

37
My solution

• 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 wont be teachers with the same
  name otherwise, Ill add employee number and use
  it as a key
• goes is NM, why ? A picnic has more than one
  student participating also, a student can go to
  more than 1 picnic. However, this NM
  relationship allows a student to go to more than
  one picnic on the same date
• leading is N1 , why? Depends on your assumptions
• I assume a teacher can only lead 1 picnic on a
  certain date, so given the teacher name and the
  date, I can identify a picnic
• Picnic is made a weak entity. I could have added
  a PicnicNo, but it would be very awkward.

Question How to record number of students in a
picnic?
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

39
E-R Diagram for Company Database
Fname
Minit
Lname
Locations
WORKS_FOR
Address
Name
Sex
Salary
Ssn
DEPARTMENT
Number Of Employees
Startdate
EMPLOYEE
Bdate
MANAGES
CONTROLS
supervisor
supervisee
Hours
SUPERVISION
WORKS_ON
PROJECT
DEPENDENTS_OF
DEPENDENT
Can you translate it back into English?
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 wont be covered
  in this course!)

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.

42
Representing Entity Sets as Tables

• A strong/regular entity set reduces to a table
  with the same attributes.

• A weak entity set becomes a table that includes a
  column for the primary key of the identifying
  strong entity set

43
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.

depositor
44

• For 1N and 11 relationships, you can create a
  table for each relationship
• But it is more concise to merge the
  relationship-table with the entity-table on the
  N side

45
Questions to Think About

• In a 1N 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?

• How can we express existence constraints on table?

46
Questions to Think About (Cont.)

• In a 11 relationship, we can include the key
  from either entity into the table representing
  the other entity. Suppose the Loan-Customer
  relationship is 11, would you include the
  Customer_no into Loan or Loan_no into Customer?

47
Questions to Think About (Cont.)

• How can we express existence constraints on table?

48
Weak Entities

• Since a weak entity has to include the primary
  key of the identifying entity, the 1N
  relationship is already captured. E.g., The
  payment table already contains information about
  the Loan (I.e., loan_no)

49
Questions to Think about Relationship or
attribute?

• We have seen this example before.
• Questions Can I put every attribute on an entity
  type?

50
Summary of Symbols Used in E-R Notation
51
Summary of Symbols Used in E-R Notation
52
E-R Diagram for a Banking Enterprise
53
E-R Diagram for Exercise 2.12
E-R Diagram for a Publisher Enterprise