Consider the following relational database for Happy Cruise Lines. It keeps trac
ID: 3590660 • Letter: C
Question
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, 2003, 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.
Ship
Number
Ship
Name
Ship
Builder
Launch
Date
Gross
Weight
SHIP Relation
Cruise
Number
Start
Date
End
Date
Cruise
Director
Ship
Number
CRUISE Relation
Port
Name
Country
Number of
Docks
Port
Manager
PORT Relation
Cruise
Number
Port
Name
Country
Arrival
Date
Departure
Date
VISIT Relation
Passenger
Number
Passenger
Name
Social Security
Number
Home
Address
Telephone
Number
PASSENGER Relation
Passenger
Number
Cruise
Number
Stateroom
Number
Fare
VOYAGE Relation
Identify the candidate keys of the following five relations. If you have a CK with moren than one attribute, clearly specify all of the attributes in the CK by using a bracket. For example, the CKs of Employee relation can have two CKs::1) Employee_id (alone), 2) {First_Name, Last_name, Phone_#} together:
SHIP Relation
CRUISE Relation
PORT Relation
VISIT Relation
PASSENGER Relation
Ship
Number
Ship
Name
Ship
Builder
Launch
Date
Gross
Weight
Explanation / Answer
SHIP Relation
CKs :
1) ShipNumber(alone)
2) ShipName(alone)
CRUISE Relation
Ck : CruiseNumber
PORT Relation
CK : { PortName ,Country}together
VISIT Relation
CKs:: {CruiseNumber,PortName}together
PASSENGER Relation
CKs: 1. PassengerNumber(alone)
2. SocialSecurityNumber(alone)
VOYAGE Relation
CK: {PassengerNumber,CruiseNumber}together