Comp 231 Database Management Systems

1
Comp 231 Database Management Systems
2. Entity Relationship (ER) Model
2
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
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

L-002
L-001
A12345
Wanchai
Central
B56789
C54321

• 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
Example
L-001
L-002
A12345
Wanchai
Central
B56789
C54321
Customer Dummy
A12345 x001
B56789 x002
B56789 x003
C54321 x004
Dummy Loan
x001 L-001
x002 L-001
x003 L-002
x004 L-002
Dummy Branch
x001 Wanchai
x002 Central
x003 Wanchai
x004 Wanchai
35
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

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

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

40
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?
41
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!)

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

43
Translating ERDs into Tables
44
Representing Entity Sets as Tables

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

45
Representing Weak Entity Sets as Tables

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

46
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
share
loan-no
A12345
L-17
10 May 1999
B45678
L-17
17 May 1999
B45678
L-15
23 May 1999
47

• 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

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

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

50
Questions to Think About (Cont.)

• How can we express existence constraints on table?

51
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)

52
Questions to Think about Relationship or
attribute?

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