Chapter 4: The Enhanced Entity-Relationship (EER) Model 1

CHAPTER 4: THE ENHANCED ENTITY-RELATIONSHIP (EER) MODEL

Answers to Selected Exercises

4.16 – In EER diagram, specialization defines a set of subclasses of an entity type. Describe
any constraints that may apply to a specialization relationship. Support your answer with an
example.

Answer:
A specialization process can be used to classify a class of objects, top-down refinement into
more specialized subclasses such as the following examples. The specialization relationship
is shown labelled using the ISA in a triangle component. The constraints of specialization
relationship can be overlapping where an entity (e.g. person) can belong to more than one
lower-level entity.

E.g. Customer “is a” person.

Employee “is a” person.

Person

ISA

Customer Employee

4.17 - Consider the BANK ER schema of Figure 7.21, and suppose that it is necessary to
keep track of different types of ACCOUNTS (SAVINGS_ACCTS, CHECKING_ACCTS,
...) and LOANS (CAR_LOANS, HOME_LOANS, ...). Suppose that it is also desirable to keep
track of each account's TRANSACTIONs (deposits, withdrawals, checks, ...) and each loan's
PAYMENTs; both of these include the amount, date, time, ... Modify the BANK schema,
using ER and EER concepts of specialization and generalization. State any assumptions you
make about the additional requirements.

Answer:

Copyright © 2016 Pearson Education, Ltd.

2 Chapter 4: The Enhanced Entity-Relationship (EER) Model

4.18 - Describe the relationship between superclass, subclass and attributes using an
example based of staff, sales manager, marketing personnel and secretary. Illustrate the
superclass/subclass relationships with the appropriate attributes associated with the entity.

Answer:
A superclass is an entity type that defines one or more distinct subgroups. A subclass is a
distinct subgrouping of occurrences of an entity type. The relationship between a superclass
and any of its subclasses is defined as superclass/subclass or supertype/subtype. The staff
entity is considered as superclass of sales manager, marketing personnel and secretary
subclasses. The context can be referred to as staff/manager has a superclass/subclass
relationship. Sales manager may have special attributes such as BranchArea and
TravelAllowance. Staff, sales manager, marketing personnel and secretary have common
attributes such as StaffID, FirstName, LastName, Position, Salary and Bonus.

4.19 - Identify all the important concepts represented in the library database case study
described here. In particular, identify the abstraction of classification (entity types and
relationship types), aggregation, identification, and specialization/generalization. Specify
(min, max) cardinality constraints whenever possible. List details that will affect the eventual
design but which have no bearing on the conceptual design. List the semantic separately.
Draw an EER diagram of the library database.

Answer:

Copyright © 2016 Pearson Education, Ltd.

 Chapter 4: The Enhanced Entity-Relationship (EER) Model 3

4.20 - No solution provided.

4.21 - Figure 8.12 shows an example of an EER diagram for a small private airport database
that is used to keep track of airplanes, their owners, airport employees, and pilots. From the
requirements for this database, the following information was collected: Each airplane has a
registration number [Reg#], is of a particular plane type [of_type], and is stored in a particular
hangar [stored_in]. Each plane_type has a model number [Model], a capacity [Capacity], and
a weight [Weight]. Each hangar has a number [Number], a capacity [Capacity], and a
location [Location]. The database also keeps track of the owners of each plane [owns] and
the employees who have maintained the plane [maintain]. Each relationship instance in
owns relates an airplane to an owner and includes the purchase date [Pdate]. Each
relationship instance in maintain relates to an employee to a service record [service]. Each
plane undergoes service many times; hence, it is related by [plane_service] o a number of
service records. A service record includes as attributes the date of maintenance [Date], the
number of hours spent on the work [Hours], and the type of work done [Workcode]. We use a
weak entity type [service] to represent airplane service, because the airplane registration
number is used to identify a service record. An owner is either a person or a corporation.
Hence, we use a union type (category) [owner] that is a subset of the union of corporation
[Corporation] and person [Person] entity types. Both pilots {Pilot] and employees [Employee]
are subclasses of person. Each pilot has specific attributes license number [Lic_Num] and
restrictions [Restr]; each employee has specific attributes salary [Salary] and shift {Shift]. All

Copyright © 2016 Pearson Education, Ltd.

4 Chapter 4: The Enhanced Entity-Relationship (EER) Model

person entities in the database have data kept on their social security number [Ssn], name
[Name], address [Address], and telephone number [Phone]. For corporation entities, the data
kept includes name [Name], address [Address], and telephone number [Phone]. The
database also keeps track of the types of planes each pilot is authorized to fly [Flies] and the
types of planes each employee can do maintenance work on [Works_on]. Show how the
small airport EER schema of Figure 8.12 may be represented in UML notation. (Note: We
have not discussed how to represent categories (union types) in UML, so you do not have to
map the categories in this and the following question.)

Answer:

4.22 – Aggregation is an abstraction concept for building composite objects from their
component objects. Describe the concept of aggregation with an example using UML
diagram.

Note: UML represents aggregation by placing an open diamond shape at one end of the
relationship line. The diamond shape is used to indicate the aggregating role or contain
instances of the other.

Copyright © 2016 Pearson Education, Ltd.

 Chapter 4: The Enhanced Entity-Relationship (EER) Model 5

Answer:
Aggregation represents a ”has=a” or ”is-part-of” relationship between two entities where one
represents the ”whole” and the other represents the ”part”. The example of aggregation can
be observed from the relationships as follow: ”Branch has supervisors” and ”Branch offers
counter sales service”. The ”Branch” entity uses open diamond shape to indicate
aggregation relationship as follow:

Supervisor Branch

StaffID BranchID

Sales Counter

CounterID

4.23 – No solutions provided.

4.24 - Draw a UML diagram for storing a played game of chess in a database. You may look
at [Link] for an application similar to what you are designing. State
clearly any assumptions you make in your UML diagram. A sample of assumptions you can
make about the scope is a follows:
1. The game of chess is played between two players.

2. The game is played on an 8x8 board like the one shown below:

3. The players are assigned a color of black or white at the start of the game.

4. Each player starts with the following pieces (traditionally called chessmen):
a. 1 king
b. 1 queen
c. 2 rooks
d. 2 bishops
e. 2 knights
f. 8 pawns

5. Every piece has its own initial position.

Copyright © 2016 Pearson Education, Ltd.

6 Chapter 4: The Enhanced Entity-Relationship (EER) Model

6. Every piece has its own set of legal moves based on the state of the game. You do not
need to worry about which moves are legal and which are not other than the following
issues:

a. A piece may move to an empty square or capture an opposing piece.

b. If a piece is captured, it is removed from the board.
c. If a pawn moves to the last row, it is “promoted” but converting it into another
piece (a queen, a rook, a bishop, or a knight).

Note: Some of these functions may be spread over multiple classes.

4.25 – No solution provided.

4.26 - Which of the following EER diagram(s) is/are incorrect and why? State clearly any
assumptions you make.

E1
a) E o N
1 R E3

A E2
B

E1 1
b) E d R
E2 1
A

o
c)
E1 E3
M R N
A C
Answer:
Only (c) is incorrect.

4.27 - Consider the following EER diagram that describes computer systems at a company.
Provide your own attributes and key for each entity type. Supply max cardinality constraints
justifying your choice. Write a complete narrative description of what this EER diagram
represents.

Copyright © 2016 Pearson Education, Ltd.

 Chapter 4: The Enhanced Entity-Relationship (EER) Model 7

Installed

Computer Software
InstalledOS

d U
U U
Laptop Desktop Operating
SoldWith
Supports System

Options Options

Accessory Component

d d
U U U U

Keyboard U Mouse Memory Video card

U

Monitor Sound card

4.31 –No solution provided

Copyright © 2016 Pearson Education, Ltd.