Advanced Features of ER Diagrams

1
Advanced Features of ER Diagrams

• This presentation was prepared by Yufei
  Tao.http//www.cse.cuhk.edu.hk/taoyf

2
Overview

• We will discuss several extensional features of
  ER diagrams.
• These features are not compulsory because, even
  without them, we can still manifest our design
  ideas.
• However, these features are useful, because they
  can simplify the diagram.

3
Outline

• Extensional features.
• Conversion to tables.
• Features that are hard to convert.

4
Attribute types

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

5
Representation of different attributes
multi-valued attribute
derived attribute
6
Specialization

• A child entity inherits all the attributes of a
  parent entity.

7
Multi-specialization

• An entity set can have multiple specializations
  based on different features.

8
Constraints on specialization

• Disjoint An entity can be specialized to at most
  one child entity set.
• Overlapping The opposite.
• Total Every entity must participate in the
  specialization.
• Partial The opposite.

9
Weak entity sets

• All the entities we have seen are strong
  entities.
• A weak entity cannot exist by itself. It must be
  related to a strong entity.

10
Aggregation

• Without using aggregation

11
Aggregation (cont.)

• Using aggregation
• We can treat a relationship as a high-level
  entity set.

12
An ER diagram for a small database
13
Outline

• Extensional features.
• Conversion to tables.
• Features that are hard to convert.

14
Entity sets with composite attributes

• Create a column for each leaf attribute.
• CUSTOMER(id, city, street-num)

15
Multi-valued attributes

• A multi-valued attribute needs a separate table.

primary key emp-id, name
primary key id
foreign key to employee
16
Specialization

• PERSON(id, name, street, city)
• EMPLOYEE(person-id, salary)
• person-id is a foreign key to PERSON.
• CUSTOMER(person-id, credit-rating)

17
Weak entity sets

• Note we should not create a dedicated table for
  loan-payment.
• Otherwise, we cannot prevent payment from
  existing by itself.
• LOAN(loan-number, amount)
• PAYMENT(payment-number, payment-date,
  payment-amount, loan-number)

18
Aggregation

• MANAGES(manager-id, emp-id, job-id, branch-id)

19
Outline

• Extensional features.
• Conversion to tables.
• Features that are hard to convert.

20
Total participation

• Some total participation is not easy to capture.
• It is easy to capture the total participation of
  course.
• PROF(prof-id, prof-name)
• COURSE(c-id, c-name, prof-id), where prof-id is a
  foreign key.
• It is much harder to capture the total
  participation of prof.
• A possible solution triggers.

21
Constraints on specialization

• Constraints on specialization can also be
  difficult to capture.
• Again, triggers could be a solution.