Consider the following relational database for Happy Cruise Lines. It keeps track of ships, cruises, ports, and passengers. A ‘‘cruise’’ is a particular sailing of a ship on a particular date. For example, the seven-day journey of the ship Pride of Tampa that leaves on June 13, 2009, is a cruise. Note the following facts about this environment.

• Both ship number and ship name are unique in the SHIP Relation.
• A ship goes on many cruises over time. A cruise is associated with a single ship.
• A port is identified by the combination of port name and country.
• As indicated by the VISIT Relation, a cruise includes visits to several ports, and a port is typically included in several cruises.
• Both Passenger Number and Social Security Number are unique in the PASSENGER Relation. A particular person has a single Passenger Number that is used for all of the cruises that she takes.
• The VOYAGE Relation indicates that a person can take many cruises and a cruise, of course, has many passengers.

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 passenger number 473942.
ii. Retrieve the record for the port of Nassau in the Bahamas.
iii. List all of the ships built by General Shipbuilding, Inc.
iv. List the port name and number of docks of every port in Mexico.
v. List the name and number of every ship.
vi. Who was the cruise director on cruise number 38232?
vii. What was the gross weight of the ship used for cruise number 39482?
viii. List the home address of every passenger on cruise number 17543.

Transcribed Image Text:

SHIP Relation Ship Number Number Ship Ship Launch Gross Name Builder Date Weight CRUISE Relation Cruise Start End Cruise Ship Number Date Date Director Number PORT Relation Port Number Port Name Country of Docks Manager VISIT Relation Cruise Port Arrival Departure Number Name Country Date Date