ER Diagram 2

1
ER Diagram 2

• 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
Weak entity sets

• A weak entity set cannot exist by itself.
• It depends on an identifying entity set (strong
  entity set) via an identifying relationship set.
• An identifying relationship is depicted using a
  double diamond
• A weak entity set is depicted using a double
  rectangle

3
Weak entity sets (cont.)

• The partial key of a weak entity contains
  attributes that distinguishes it among the weak
  entities belonging to the same strong entity.
• A partial key is underlined with a dotted line.
• The primary key of a weak entity set is the union
  of
• Its partial key, and
• the primary key of its host strong entity set.
• Primary key for payment (loan-number,
  payment-number)

4
Specialization

• A child entity has all the attributes of a parent
  entity.
• Attribute inheritance.
• But not the vice versa.
• What is the advantage against without using ISA?

5
Specialization (cont.)

• An entity set can have multiple specializations
  based on different features.
• Each particular employee would be
• permanent or temporary,
• and at the same time, an officer, secretary, or
  teller

6
Constraints on specialization

• Disjoint An entity can belong to only one
  lower-level entity set.
• Overlapping The opposite.
• Total Each entity in the parent entity set must
  be specialized.
• Partial The opposite.

7
Aggregation

• Consider the ternary relationship works-on.
• Suppose we want to model managers for tasks
  performed by an employee at a branch.
• Fairly complex.

8
Aggregation (Cont.)

• Aggregation Treat a relationship as an
  abstract entity
• Each employee-branch-job combination has a
  manager.

9
An ER diagram for a small database
10
What have we learned about ER diagrams?

• Entity sets
• Attributes
• Relationship sets
• 1-1, 1-m, m-1, m-m
• total/partial participation
• Attributes
• Weak entity sets
• Specialization
• Overlapping, disjoint
• total/partial
• Aggregation

11
ER gt Tables

• An ER diagram can be easily converted into tables
  in a relational database.
• Next we will explain the conversion for each
  component in an ER diagram.

12
Entity sets

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

13
Composite attributes

• Composite attributes are flattened.
• Specifically, in a table, we create a column for
  each leaf attribute of the entity.

14
Multivalued attributes

• A multivalued attribute is represented by a
  separate table.
• E.g., John has id 1 and two kids Johnson and
  Johndotir.

15
Relationship sets

• A universal solution Create a table with the
  primary keys of the participating entities.

16
Relationship sets (cont.)

• However, we can do better than the universal
  solution, if the relationship set is not
  many-to-many.
• For the following ER diagram, we can be represent
  account-branch by adding branch-number to table
  account.
• See next.

17
Example
18
Relationship sets (cont.)

• Just now, we put the primary key of branch into
  the table of account, in order to represent the
  relationship set.
• Can we put the primary key account-number of
  account into the table of branch?
• NO, because a tuple in branch would have to store
  multiple values under column account-number.
• See next.

19
Example
20
Relationship sets (Cont.)

• We can represent borrower by adding loan-number
  into customer.
• Why not add customer-id to loan?
• Will cause NULL values. See next.

21
Example 2
adding loan-number into customer.
adding customer-id into loan.
22
Representing weak entity sets

• Create a table for each weak entity set that
  includes its local attributes, and the primary
  key of its host entity.
• No table necessary for the identifying
  relationship.

23
Specialization

• Create a table for the parent entity set.
• Create a table for each child entity set,
  including the primary key of the parent and local
  attributes.Table Table
  attributesperson id, name,
  street, city customer person-id,
  credit-ratingemployee person-id, salary

24
Aggregation

• Create a table that contains the primary keys of
  all the entity sets involved.