Deck 6: The Relational Database Model: Additional Concepts

Question 1

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

Answer 1

B

Question 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

Answer 2

A

Question 3

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.

Answer 3

E

Question 4

A unary many-to-many relationship involves two entities.

Question 5

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.

Question 6

Representing a unary one-to-many relationship in a relational database requires the creation of an additional relation.

Question 7

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

Question 8

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.
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

Question 9

The Bill of Materials problem is an example of a unary one-to-many relationship.

Question 10

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.

Question 11

Referential integrity is an issue in record ____.

A) insertion
B) deletion
C) update
D) All of the above
E) None of the above

Question 12

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.
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

Question 13

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.
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
E) no records will be deleted

Question 14

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.

Question 15

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.
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

Question 16

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

Question 17

Representing a unary many-to-many relationship in a relational database requires the creation of an additional relation.

Question 18

A ternary relationship involves ____ entity type(s).

A) one
B) two
C) three
D) four
E) None of the above.

Question 19

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.

Question 20

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

Question 21

Referential integrity involves two relations logically associated (by foreign keys).

Question 22

Referential integrity in a relational database refers to problems associated with redundant data within a single relation.

Question 23

A ternary relationship involves relations for two different entity types plus an additional relation.

Question 24

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.
Enclosure relation
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?

Question 25

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 relation
Flight relation
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. Record 3 in the AIRPLANE relation would be deleted and the Airplane Number values of records 3, 4, 5, 6, and 7 in the FLIGHT relation would be updated to null.
g. Record 1 in the AIRPLANE relation would be deleted.

Question 26

The Bill of Materials problem is an example of a ternary relationship.

Question 27

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.

Question 28

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.
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 Relation
ANIMAL Relation
ZOOKEEPER Relation
CARES FOR Relation
SPONSOR Relation
CONTRIBUTION Relation
DEPENDENT 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 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.

Question 29

A ternary relationship is the functional equivalent of three different binary many-to-many relationships.

Question 30

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.

Question 31

Referential integrity is a factor in record deletion but not in record insertion or update.

Question 32

Storing a ternary relationship in a relational database requires the creation of an additional relation.

Question 33

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.

Question 34

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 Relation
FLIGHT Relation
PASSENGER Relation
RESERVATION Relation
AIRPLANE Relation
MAINTENANCE PROCEDURE Relation
MAINTENANCE LOCATION Relation
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.
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.

Question 35

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.

Question 36

A ternary relationship may have intersection data associated with it.

Question 37

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.

Question 38

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.

Question 39

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.