Entity Relationship Diagram

1
Entity Relationship Diagram

• This presentation is prepared by Yufei
  Tao.http//www.cs.cityu.edu.hk/taoyf
• Some examples are designed based on the slides
  provided by the textbook website.

2
Overview

• Database design is essentially to decide
• What data to store?
• How to store it?
• We must perform the design in a systematic way to
  achieve the maximum efficiency.
• For this purpose, we need a tool that allows us
  to visualize our current design in a concise but
  accurate manner.
• The ER diagram is such a tool.

3
An example ER diagram
4
Entity and entity set

• An entity is an object.
• A person, a dog, a CD, a
• An entity has attributes.
• A person has a name and an address
• A dog has an owner
• A CD has a length-of-play
• An entity set is a set of entities that share the
  same attributes.
• A person entity set.
• A dog entity set.
• A CD entity set.

5
Entity set examples
customer-id customer- customer- customer-
loan- amount
name street city
number
6
Attribute types

• Simple vs. composite.
• Single-valued vs. multi-valued
• Name vs. phone-numbers
• Derived vs. non-derived
• Date of birth Non-derived
• Age Derived

7
Representation of an entity
multi-valued attribute
derived attribute
8
Relationship and relationship set

• A relationship describes the connection between
  entities.
• A person owns a dog.
• A person bought a CD.
• A man marries a woman.
• A relationship set captures relationships of the
  same type.
• See next.

9
Relationship set (cont.)

• A relationship set is defined between two entity
  sets A, B.
• Each link is a relationship, and the set of all
  links constitutes a relationship set.

10
A relationship set with attributes

• From the diagram, we know that
• There are two types of data customer and
  account.
• Each customer/account has several attributes.
• Each deposit relationship has an attribute, too.

11
Constraints on relationship sets

• Each relationship set is associated with two
  constraints.
• Cardinality constraint
• Participation constraint
• These constraints offer a powerful way to model
  real-world connections.

12
Cardinality constraint

• One to one
• One to many
• Many to one
• Many to many
• We will see examples in the next few slides.

13
Cardinality constraint
One to one (e.g., husband-wife)
One to many (e.g., father-child)
14
One-to-one representation

• A customer can borrow at most one loan.
• A loan can be borrowed by at most one customer.

one
15
One-to-many representation

• A customer can borrow many loans.
• A loan can be borrowed by at most one customer.

many
one
16
Cardinality constraint
Many to one (e.g., child-father)
Many to many (e.g., friendship)
17
Many-to-one representation

• A customer can borrow at most one loan.
• A loan can be borrowed by many customers.

many
one
18
Many-to-many representation

• A customer can borrow many loans.
• A loan can be borrowed by many customers.

19
Constraints of relationship sets

• Each relationship set is associated with two
  constraints.
• Cardinality constraint
• Participation constraint

20
Participation constraint

• Total participation Every entity in the entity
  set must participate in at least one
  relationship.
• Partial participation Some entities may not
  participate.

partial
total

• Some customers may not borrow any loan.
• Every loan must be borrowed by at least one
  customer.

21
Participation constraint (cont.)

• What can we imply from the participation
  constraints in this relationship?

partial
total

• Every customer must borrow at least a loan.
• Some loan may not be borrowed by any customer.
• It does not make sense in practice.

22
More on relationships constraints

• Cardinality constraints.
• 1-1, 1-m, m-1, m-m
• Participation constraints.
• Partial, total
• The two types of constraints are independent.
• Each cardinality constraint can be used with any
  participation constraint.
• See some examples next.

23
More on constraints (cont.)

• What can you imply in the following relationships?

24
More on constraints (cont.)

• What can you imply in the following relationships?

25
More on constraints (cont.)

• What constraints should be applied if
• Every customer should borrow exactly a loan.
• Every loan should be borrowed by exactly one
  customer.

26
More on constraints (cont.)

• Every customer should borrow exactly a loan.
• Every loan should be borrowed by exactly one
  customer.
• Answer

27
Self relationships

• The entity sets participating in a relationship
  can be the same.

28
Keys

• Primary keys of entities are underlined in an ER
  diagram.

29
Keys for relationship sets

• Attributes that can uniquely identify a
  relationship in the relationship set.
• The union of the primary keys of the
  participating entity sets contains the primary
  key of a relationship set.
• Customer-id, loan-number can uniquely decide
  each borrower relationship.
• But it is not a primary key it is not minimum.

30
Keys for 1-to-1 relationship sets

• The primary key can be customer-id or
  loan-number.

31
Keys for 1-m and m-m relationship sets
Primary key is loan_number
Primary key is customer-id, loan_number
32
Ternary relationship set
Two example works-on relationships (John, CityU
branch, manager) (John, Diamond Hill branch,
secretary) They indicate that John takes
different roles in the two branches.
33
An example ER diagram (revisited)
Now we should understand the following design
better.