– Part III
Chapter 12 Entity-Relationship Modeling
Review Questions
12.1 Describe what entity types represent in an ER model and provide examples of entities with a
physical or conceptual existence.
12.2 Describe what relationship types represent in an ER model and provide examples of unary,
binary, ternary, and quaternary relationships.
A relationship type is a set of associations between one or more participating entity types (see
Section 12.2). Examples:
12.3 Describe what attributes represent in an ER model and provide examples of simple,
composite, single-value, multi-value, and derived attributes.
An attribute represents a property of an entity or a relationship type (see Section 12.3).
Examples:
12.4 Describe what the multiplicity constraint represents for a relationship type.
12.5 What are enterprise constraints and how does multiplicity model these constraints?
12.6 How does multiplicity represent both the cardinality and the participation constraints on a
relationship type?
12.7 Provide an example of a relationship type with attributes.
12.8 Describe how strong and weak entity types differ and provide an example of each.
A strong entity type is an entity type that is not existence-dependent on some other entity type
12.9 Describe how fan and chasm traps can occur in an ER model and how they can be resolved.
A fan trap occurs where a model represents a relationship between two entity types, but the
pathway between certain entity occurrences is ambiguous. Resolve the fan trap by
– Part III
Exercises
12.10 Create an ER diagram for each of the following descriptions:
(a) Each company operates four departments, and each department belongs to one
company. (Note when the exact cardinality is known (in this example, 4) a value can
replace the multiplicity range.
(b) Each department in part (a) employs one or more employees, and each employee
works for one department.
(c) Each of the employees in part (b) may or may not have one or more dependants, and
each dependant belongs to one employee.
(d) Each employee in part (c) may or may not have an employment history.
– Part III
11
(e) Represent all the ER diagrams described in (a), (b), (c), and (d) as a single ER
diagram.
– Part III
12
12.11 You are required to create a conceptual data model of the data requirements for a company
that specializes in IT training. The Company has 30 instructors and can handle up to 100
trainees per training session. The Company offers five advanced technology courses, each of
which is taught by a teaching team of two or more instructors. Each instructor is assigned to a
maximum of two teaching teams or may be assigned to do research. Each trainee undertakes
one advanced technology course per training session.
12.12 Read the following case study, which describes the data requirements for a DVD rental
company. The DVD rental company has several branches throughout the USA. The data held
on each branch is the branch address made up of street, city, state, and zip code, and the
telephone number. Each branch is given a branch number, which is unique throughout the
company. Each branch is allocated staff, which includes a Manager. The Manager is
responsible for the day-to-day running of a given branch. The data held on a member of staff
is his or her name, position, and salary. Each member of staff is given a staff number, which is
unique throughout the company. Each branch has a stock of DVDs. The data held on a DVD
is the catalog number, DVD number, title, category, daily rental, cost, status, and the names of
the main actors, and the director. The catalog number uniquely identifies each DVD.
However, in most cases, there are several copies of each DVD at a branch, and the individual
copies are identified using the DVD number. A DVD is given a category such as Action,
Adult, Children, Drama, Horror, or Sci-Fi. The status indicates whether a specific copy of a
DVD is available for rent. Before hiring a DVD from the company, a customer must first
register as a member of a local branch. The data held on a member is the first and last name,
address, and the date that the member registered at a branch. Each member is given a
– Part III
13
member number, which is unique throughout all branches of the company. Once registered, a
member is free to rent DVDs, up to maximum of ten at any one time. The data held on each
DVD rented is the rental number, the full name and number of the member, the DVD number,
title, and daily rental, and the dates the DVD is rented out and date returned. The rental
number is unique throughout the company.
Video
– Part III
14
12.13 Create an ER model for each of the following descriptions:
Figure 13.11
12.14 Create an ER model to represent the data use by the library.
The library provides books to borrowers. Each book is described by title, edition and year of
publication and is uniquely identified using the ISBN. Each borrower is described by his or
her name and address and is uniquely identified using a borrower number. The library
provides one or more copies of each book and each copy is uniquely identified using a copy
number, status indicating if the book is available for loan and the allowable loan period for a
given copy. A borrower may loan one or many books and the date each book is loaned out and
is returned is recorded. Loan number uniquely identifies each book loan.
The answer to this exercise is shown as Figure 13.12.