Chapter 6 The Relational Database Model: Additional Concepts
6-1
Last Update: November 10, 2011 — 11PM
Chapter 6: The Relational Database Model: Additional Concepts
Multiple Choice
1. Representing a unary one-to-many relationship in a relational database ____.
a. requires the creation of an additional relation
b. requires adding a new column, the domain of values of which is the same as the
relation’s primary key
c. requires adding a new column, the domain of values of which is the same as the
primary key of another relation in the database
d. requires modifying the primary key of the relation
e. requires replacing the primary key of the relation with an alternate key
2. Representing a unary many-to-many relationship in a relational database ____.
a. requires the creation of an additional relation
b. requires adding a new column, the domain of values of which is the same as the
relation’s primary key
c. requires adding a new column, the domain of values of which is the same as the
primary key of another relation in the database
d. requires modifying the primary key of the relation
e. requires replacing the primary key of the relation with an alternate key
3. The Bill of Materials problem is an example of a ____.
a. unary one-to-many relationship
b. unary many-to-many relationship
c. binary one-to-many relationship
d. binary many-to-many relationship
e. ternary relationship
4. A ternary relationship involves ____ entity type(s).
Chapter 6 The Relational Database Model: Additional Concepts
a. one
b. two
c. three
d. four
e. None of the above.
5. A ternary relationship is the equivalent of ____ binary many-to-many relationship(s).
a. one
b. two
c. three
d. four
e. None of the above.
6. Representing a ternary relationship in a relational database ____.
a. requires the creation of an additional relation
b. requires adding a new column, the domain of values of which is the same as the
relation’s primary key
c. requires adding a new column, the domain of values of which is the same as the
primary key of another relation in the database
d. requires modifying the primary key of the relation
e. requires replacing the primary key of the relation with an alternate key
7. A ternary relationship ____.
a. cannot have intersection data associated with it
b. stores its intersection data in the three relations created to represent the three
binary many-to-many relationships that form the ternary relationship
c. stores its intersection data in the three relations that represent the three entity
types in the ternary relationship
d. stores its intersection data in one of the three relations that represent the three
entity types in the ternary relationship
e. None of the above.
8. Referential integrity is an issue in record ____.
a. insertion
b. deletion
c. update
d. All of the above
e. None of the above
9. Referential integrity ____.
a. is only an issue in the relational database approach
b. is not an issue in the relational database approach
c. involves individual relations in isolation
d. only involves relations that contain redundant data
e. None of the above
The following scenario applies to the next several questions:
Consider relations A and B. Relation A represents the entity on the “one side” of a one–
to-many relationship; Relation B represents the entity on the “many side” of the one–to–
many relationship.
10. The delete rule between relations A and B is cascade. If an attempt is made to delete
a record in relation A and its primary key value appears as a foreign key value in one
or more records of relation B ____.
a. the record in relation A will be the only record deleted and no other changes will
occur
b. the record in relation A will be the only record deleted and the foreign key values
in relation B that match the primary key value of the record in relation A will be
updated to null
c. the record in relation A and all of the records in relation B with foreign key values
that match the primary key value of the record in relation A will be deleted
d. only the records in relation B with foreign key values that match the primary key
value of the record in relation A will be deleted
e. no records will be deleted
6-4
11. The delete rule between relations A and B is set-to-null. If an attempt is made to
delete a record in relation A and its primary key value appears as a foreign key value
in one or more records of relation B ____.
a. the record in relation A will be the only record deleted and no other changes will
occur
b. the record in relation A will be the only record deleted and the foreign key values
in relation B that match the primary key value of the record in relation A will be
updated to null
c. the record in relation A and all of the records in relation B with foreign key values
that match the primary key value of the record in relation A will be deleted
d. only the records in relation B with foreign key values that match the primary key
value of the record in relation A will be deleted
e. no records will be deleted
12. The delete rule between relations A and B is restrict. If an attempt is made to delete a
record in relation A and its primary key value appears as a foreign key value in one or
more records of relation B ____.
a. the record in relation A will be the only record deleted and no other changes will
occur
b. the record in relation A will be the only record deleted and the foreign key values
in relation B that match the primary key value of the record in relation A will be
updated to null
c. the record in relation A and all of the records in relation B with foreign key values
that match the primary key value of the record in relation A will be deleted
d. only the records in relation B with foreign key values that match the primary key
value of the record in relation A will be deleted
e. no records will be deleted
13. The delete rule between relations A and B is restrict. If an attempt is made to delete a
record in relation B ____.
a. the record in relation B will be the only record deleted and no other changes will
occur
b. the record in relation B will be the only record deleted and any matching field
values in relation A will be updated to null
c. the record in relation B and all of the records in relation A with field values that
match the primary key value of the record in relation B will be deleted
d. only the records in relation A with field values that match the primary key value
of the record in relation B will be deleted
Chapter 6 The Relational Database Model: Additional Concepts
6-5
e. no records will be deleted
True/False
1. Representing a unary one-to-many relationship in a relational database requires the
creation of an additional relation.
2. Representing a unary one-to-many relationship in a relational database requires the
addition of one column to the relation that represents the single entity involved in the
relationship.
3. The domain of values of the new column added to the relation to represent the unary
one-to–many relationship is the same as that of the relation’s primary key.
4. Representing a unary many-to-many relationship in a relational database requires the
creation of an additional relation.
5. Representing a unary many-to-many relationship in a relational database requires the
addition of one column to the relation that represents the single entity involved in the
relationship.
6. A unary many-to-many relationship involves two entities.
Chapter 6 The Relational Database Model: Additional Concepts
6-6
7. The Bill of Materials problem is an example of a unary one-to-many relationship.
8. A ternary relationship involves relations for two different entity types plus an
additional relation.
9. A ternary relationship is the functional equivalent of three different binary many-to–
many relationships.
10. A ternary relationship may have intersection data associated with it.
11. Storing a ternary relationship in a relational database requires the creation of an
additional relation.
12. The Bill of Materials problem is an example of a ternary relationship.
13. Referential integrity involves two relations logically associated (by foreign keys).
Chapter 6 The Relational Database Model: Additional Concepts
6-7
14. Referential integrity in a relational database refers to problems associated with
redundant data within a single relation.
15. Referential integrity is a factor in record deletion but not in record insertion or update.
16. A referential integrity problem can result in a foreign key value in one relation having
no corresponding match in the primary key of another relation.
17. If the referential integrity delete rule between two relations is restrict, the deletion of
a record in the relation on the “one side” of the one–to-many relationship may result
in the deletion of one or more records in the relation on the “many side” of the
relationship.
18. If the referential integrity delete rule between two relations is restrict, an attempt to
delete a record in the relation on the “one side” of the one–to-many relationship will
be rejected if one or more records in the relation on the “many side” of the
relationship has the record’s primary key value as its/their foreign key value.
19. If the referential integrity delete rule between two relations is cascade, the deletion of
a record in the relation on the “one side” of the one–to-many relationship may result
in the deletion of one or more records in the relation on the “many side” of the
relationship.
Chapter 6 The Relational Database Model: Additional Concepts
6-8
20. If the referential integrity delete rule between two relations is set-to-null, the deletion
of a record in the relation on the “one side” of the one–to-many relationship may
result in the deletion of one or more records in the relation on the “many side” of the
relationship.
21. If the referential integrity delete rule between two relations is cascade, the deletion of
a record in the relation on the “many side” of the one–to-many relationship may result
in the deletion of one or more records in the relation on the “one side” of the
relationship.
22. If the referential integrity delete rule between two relations is restrict, an attempt to
delete a record on the “many side” of the one–to-many will be rejected if of one or
more records in the relation on the “one side” of the relationship has the
corresponding key value.
Problems
Note to Instructor: Problems 1 and 2 below are the same as Problems 1 and 2 in the
Test Bank for Chapter 5, except that Problem 1 adds a unary one-to-many
relationship for the zookeepers’ supervisors and Problem 2 adds a ternary
relationship (and three new relations) regarding servicing airplanes. Problems 3
and 4 below are separate and involve referential integrity.
1. Consider the following relational database for the Central Zoo. Central Zoo wants to
maintain information about its animals, the enclosures in which they live, and its
zookeepers and the services they perform for the animals. In addition, Central Zoo
has a program by which people can be sponsor of animals. Central Zoo wants to
track its sponsors, their dependents, and associated data.
Chapter 6 The Relational Database Model: Additional Concepts
6-9
Each animal has a unique animal number and each enclosure has a unique enclosure
number. An animal can live in only one enclosure. An enclosure can have several
animals in it or it can be currently empty. A zookeeper has a unique employee number.
Some zookeepers supervise other zookeepers. Every animal has been cared for by at
least one and generally many zookeepers; each zookeeper has cared for at least one and
generally many animals. Each time a zookeeper performs a specific, significant service
for an animal the service type, date, and time are recorded. A zookeeper may perform a
particular service on a particular animal more than once on a given day.
A sponsor, who has a unique sponsor number and a unique social security number,
sponsors at least one and possibly several animals. An animal may have several
sponsors or none. For each animal that a particular sponsor sponsors, the zoo wants to
track the annual sponsorship contribution and renewal date. In addition, Central Zoo
wants to keep track of each sponsor’s dependents. A sponsor may have several
dependents or none. A dependent is associated with exactly one sponsor.
Enclosure
Number
Type
Location
Size
Date
Built
ENCLOSURE Relation
Animal
Number
Species
Animal
Name
Gender
Country
Of Birth
Weight
ANIMAL Relation
Employee
Number
Employee
Name
Title
Year
Hired
ZOOKEEPER Relation
Animal
Number
Service
Type
Date
Time
CARES FOR Relation
Sponsor
Number
Social Security
Number
Sponsor
Name
Address
Telephone
SPONSOR Relation
Animal
Number
Sponsor
Number
Annual
Contribution
Renewal
Date
CONTRIBUTION Relation
Sponsor
Number
Dependent
Name
Relationship
Date of
Birth
DEPENDENT Relation
Chapter 6 The Relational Database Model: Additional Concepts
6-10
a. Identify the candidate keys of each relation.
b. Identify the primary key and any alternate keys of each relation.
c. How many foreign keys does each relation have?
d. Identify the foreign keys of each relation.
e. Indicate any instances in which a foreign key serves as part of the primary key of
the relation in which it is a foreign key. Why does each of those relations require
a multi-attribute primary key?
f. Identify the relations that support many-to-many relationships, the primary keys
of those relations, and any intersection data.
g. Using the informal relational command language described in this chapter, write
commands to:
i. Retrieve the record for animal number 58560.
ii. Retrieve the record for the tiger named Stripes.
iii. List all of the tigers born in India.
iv. List the name and animal number of every animal born in China that
weighs less than 100 pounds .
v. List the name and number of every tiger.
vi. What is the country of birth of animal number 74371?
vii. What is the type and size of the enclosure used for animal number 74371?
viii. List the name and address of every sponsor of animal number 74371.
Answer
Chapter 6 The Relational Database Model: Additional Concepts
6-12
2. Consider the following relational database for Grand Travel Airlines.
Grand Travel Airlines has to keep track of its flight and airplane history. A flight is
uniquely identified by the combination of a flight number and a date. Every
passenger who has flown on Grand Travel has a unique passenger number. For a
particular passenger who has taken a particular flight, the company wants to keep
track of the fare that she paid for it and the date that she made the reservation for it.
Clearly, a passenger may have taken many flights (he must have taken at least one to
be in the database) and every flight has had many passengers on it.
A pilot is identified by a unique pilot (or employee) number. A flight on a particular
date has exactly one pilot. Each pilot has typically flown many flights but a pilot may
be new to the company, is in training, and has not flown any flights, yet. Each
airplane has a unique serial number. A flight on a particular date used one airplane.
Each airplane has flown on many flights and dates, but a new airplane may not have
been used at all, yet.
Grand Travel also wants to maintain data about its airplanes’ maintenance history;
each maintenance procedure has a unique procedure number. A maintenance location
has a unique location name. Grand Travel wants to keep track of which airplane had
which maintenance procedure performed at which location. For each such event it
wants to know the date of the event and the duration.
Pilot
Number
Pilot
Name
Date of
Birth
Date of
Hire
PILOT Relation
Flight
Number
Date
Departure
Time
Arrival
Time
Pilot
Number
Airplane
Number
FLIGHT Relation
Chapter 6 The Relational Database Model: Additional Concepts
6-13
Passenger
Number
Passenger
Name
Address
Telephone
Number
PASSENGER Relation
Flight
Number
Date
Fare
Reservation
Date
RESERVATION Relation
Airplane
Number
Model
Passenger
Capacity
Year
Built
Manufacturer
AIRPLANE Relation
Procedure
Number
Procedure
Name
Frequency
MAINTENANCE PROCEDURE Relation
Location
Name
Address
Telephone
Number
Manager
MAINTENANCE LOCATION Relation
Airplane
Number
Procedure
Number
Location
Name
Date
Duration
SERVICE Relation
a. Identify the candidate keys of each relation.
b. Identify the primary key and any alternate keys of each relation.
c. How many foreign keys does each relation have?
d. Identify the foreign keys of each relation.
e. Indicate any instances in which a foreign key serves as part of the primary key of
the relation in which it is a foreign key. Why does each of those relations require
a multi-attribute primary key?
f. Identify the relations that support many-to-many relationships, the primary keys
of those relations, and any intersection data.
g. Using the informal relational command language described in this chapter, write
commands to:
i. Retrieve the record for airplane number 36325.
ii. Retrieve the record for the pilot named Sarah Johnson who was born on
5/22/1959.
Chapter 6 The Relational Database Model: Additional Concepts
6-14
iii. List all of the airplanes manufactured by Boeing.
iv. List the airplane number and passenger capacity of every airplane
manufactured by Boeing in 1997 .
v. List the airplane number, model, and manufacturer of every airplane.
vi. What company manufactured airplane number 53489?
vii. What was the name and date of birth of the pilot of flight number 182 on
10/30/2003?
viii. List the airplane number, model, and manufacturer of every airplane that
was used on flight 118 in 2003.
Answer
Chapter 6 The Relational Database Model: Additional Concepts
6-15
3. Consider the following ANIMAL and ENCLOSURE relations from the Central Zoo
relational database. The left-hand column of relative record numbers is there to
facilitate answering some of the questions.
Chapter 6 The Relational Database Model: Additional Concepts
6-16
Enclosure
Number
Type
Size
1
0347
Glass Cage
400
2
0636
Fenced Yard
1500
3
0912
Natural Area
2000
4
1483
Fenced Yard
1650
5
1660
Steel Cage
700
ENCLOSURE relation
Animal
Number
Species
Animal
Name
Enclosure
Number
1
13648
Elephant
Jumbo
1483
2
15273
Giraffe
Stretch
0912
3
17543
Elephant
Shirley
0636
4
20165
Giraffe
Necky
0912
5
23743
Giraffe
High Top
0912
6
27579
Panda
Fluffy
0347
7
32565
Elephant
Large Louie
1483
8
33837
Elephant
Charley
0636
9
36340
Panda
Huggy
0347
10
40436
Giraffe
Sarah
0912
ANIMAL relation
a. What would happen if the delete rule between the ENCLOSURE and ANIMAL
relations is restrict and an attempt is made to delete the record for enclosure
number 0912 in the ENCLOSURE relation?
b. What would happen if the delete rule between the ENCLOSURE and ANIMAL
relations is restrict and an attempt is made to delete the record for enclosure
number 1660 in the ENCLOSURE relation?
c. What would happen if the delete rule between the ENCLOSURE and ANIMAL
relations is restrict and an attempt is made to delete the record for animal number
40436 in the ANIMAL relation?
d. What would happen if the delete rule between the ENCLOSURE and ANIMAL
relations is cascade and an attempt is made to delete the record for enclosure
number 0912 in the ENCLOSURE relation?
e. What would happen if the delete rule between the ENCLOSURE and ANIMAL
relations is cascade and an attempt is made to delete the record for enclosure
number 1660 in the ENCLOSURE relation?
f. What would happen if the delete rule between the ENCLOSURE and ANIMAL
relations is set-to-null and an attempt is made to delete the record for enclosure
number 0912 in the ENCLOSURE relation?
g. What would happen if the delete rule between the ENCLOSURE and ANIMAL
relations is set-to-null and an attempt is made to delete the record for enclosure
number 1660 in the ENCLOSURE relation?
Chapter 6 The Relational Database Model: Additional Concepts
6-17
Answer
4. Consider the following AIRPLANE and FLIGHT relations from the Grand Travel
Airlines relational database. The left-hand column of relative record numbers is
there to facilitate answering some of the questions:
Airplane
Number
Manufacturer
Model
Passenger
Capacity
Year
Built
1
04653
Boeing
767
280
1988
2
10582
Airbus
A320
256
1997
3
16420
Canadair
CRJ
54
2001
4
22663
Boeing
767
265
1999
AIRPLANE relation
Flight
Number
Date
Departure
Time
Arrival
Time
Airplane
Number
1
005
11/15/2003
2:14PM
4:20PM
22663
2
018
01/24/2004
11:00AM
1:32PM
10582
3
032
11/15/2003
9:03AM
10:30AM
16420
4
032
11/16/2003
8:57:AM
10:23AM
16420
5
032
11/17/2003
9:00AM
10:33AM
16420
6
120
01/24/2004
7:52PM
9:34PM
16420
7
120
01/28/2004
7:51PM
9:57PM
16420
8
154
11/15/2003
12:58PM
3:30PM
10582
9
197
02//12/2004
5:03PM
7:22PM
22663
FLIGHT relation
Chapter 6 The Relational Database Model: Additional Concepts
6-18
a. What would happen if the delete rule between the AIRPLANE and FLIGHT
relations is restrict and an attempt is made to delete the record for airplane number
16420 in the AIRPLANE relation?
b. What would happen if the delete rule between the AIRPLANE and FLIGHT
relations is restrict and an attempt is made to delete the record for airplane number
04653 in the AIRPLANE relation?
c. What would happen if the delete rule between the AIRPLANE and FLIGHT
relations is restrict and an attempt is made to delete the record for flight number
120 on 01/24/2004 in the FLIGHT relation?
d. What would happen if the delete rule between the AIRPLANE and FLIGHT
relations is cascade and an attempt is made to delete the record for airplane
number 16420 in the AIRPLANE relation?
e. What would happen if the delete rule between the AIRPLANE and FLIGHT
relations is cascade and an attempt is made to delete the record for airplane
number 04653 in the AIRPLANE relation?
f. What would happen if the delete rule between the AIRPLANE and FLIGHT
relations is set-to-null and an attempt is made to delete the record for airplane
number 16420 in the AIRPLANE relation?
g. What would happen if the delete rule between the AIRPLANE and FLIGHT
relations is set-to-null and an attempt is made to delete the record for airplane
number 04653 in the AIRPLANE relation?
Answer