Exam 6: The Relational Database Model: Additional Concepts

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.

Representing a unary one-to-many relationship in a relational database ____.

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.

The Bill of Materials problem is an example of a ____.

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

A ternary relationship involves ____ entity type(s).

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

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

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

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.

Representing a unary many-to-many relationship in a relational database ____.

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

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

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

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

Referential integrity ____.

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

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 Pilot Date of Date of Number Name Birth Hire PILOT Relation Flight Departure Arrival Pilot Airplane Number Date Time Time Number Number FLIGHT Relation Passenger Passenger Telephone Number Name Address Number PASSENGER Relation Flight Passenger Reservation Number Date Number Fare Date RESERVATION Relation Airplarne Passenger Year Number Model Capacity Built Marufacturer AIRPLANE Relation Procedure Procedure Nurriber Narne Frequency MAINTENANCE PROCEDURE Relation Location Telephone Name Address Number Manager MAINTENANCE LOCATION Relation Airplane Procedure Location Number Number 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. 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.

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.

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