ER Modeling I Exercise Solutions

1
ER Modeling IExercise Solutions
2
Solution to Q1
3
Notes to assist Q2

• Three sets of relationships exist A COURSE may
  generate one or more CLASSes, an INSTRUCTOR
  teaches up to two CLASSes, and a TRAINEE may
  enroll in up to two CLASSes.
• A trainee can take more than one class, and each
  class contains many (10 or more) trainees, so
  there is a MN relationship between TRAINEE and
  CLASS (we must, therefore, create a composite
  entity to serve as the bridge between TRAINEE and
  CLASS). A class is taught by only one
  instructor, but an instructor can teach up to two
  classes. Therefore, there is a 1M relationship
  between INSTRUCTOR and CLASS.
• Finally, a COURSE may generate more than one
  CLASS, while each CLASS is based on one COURSE,
  so there is a 1M relationship between COURSE and
  CLASS. These relationships are all reflected in
  the following E-R diagram. Note the optional and
  mandatory relationships to exist, a CLASS must
  have TRAINEEs enrolled in it, but TRAINEEs do not
  necessarily take CLASSes (some may take "on the
  job training"). An INSTRUCTOR may not be
  teaching any CLASSes, doing research instead, but
  each CLASS must have an INSTRUCTOR. If not
  enough people sign up for a CLASS, a COURSE may
  not generate any CLASSes, but each CLASS must
  represent a COURSE.

4
Solution to Q2
5
Solution to Q3
6
Solution to Q3

• Keep in mind that the preceding E-R diagram
  reflects a set of business rules that may easily
  be modified
• For example, if customers are supplied via a
  commercial customer list, many of the customers
  on that list will not (yet!) have bought
  anything, so INVOICE would be optional to
  CUSTOMER
• We are assuming here that a product can be
  supplied by many vendors and that each vendor can
  supply many products. The PRODUCT may be optional
  to VENDOR if the vendor list includes potential
  vendors from which you have not (yet) ordered
  anything.
• Some products may never sell, so LINE is
  optional to PRODUCT... because an unsold product
  will never appear in an invoice line.
• LINE may be shown as weak to INVOICE, because it
  borrows the invoice number as part of its primary
  key and it is existence-dependent on INVOICE
• The design depends on the exact nature of the
  business rules.

7
Solution to Q3
8
Solution to Q4
(0,N)
(0,N)
agent
1
represents
1
manages
(1,1)
(1,1)
M
1
1
M
M
plays
M
client
instrument
musician
(0,N)
(0,N)
(1,1)
(0,N)
(1,1)
1
(0,N)
1
hosts
performs
(1,1)
M
M
event
(1,1)
9
Notes to assist Q5

• Note the following relationships
• The main components are TEAM and GAME.
• Each team plays each other team at least twice.
• To play a game, two teams are necessary the home
  team and the visitor team.
• Each team plays once as the home team and once as
  the visitor team.
• Given these relationships, it becomes clear that
  TEAM participates in a recursive MN relationship
  with GAME. The relationship between TEAM and GAME
  becomes clearer if we list some attributes for
  each of these entities
• GAME entity TEAM entity
• GAME_NUM TEAM_NUM
• GAME_DATE TEAM_NAME
• GAME_HOME_TEAM TEAM_CITY
• GAME_VISIT_TEAM
• GAME_HOME_POINTS
• GAME_VISIT_POINTS
• Note TEAM_NUM appears at least twice in a
  GAMEonce as GAME_HOME_TEAM and once as
  GAME_VISIT_TEAM.

10
Solution to Q5