$[$Relational Data Model$][50]$

Consider the following schema defined for Lake Erie Cruises. The schema keeps

track of ships, cruises, ports and passengers. A cruise refers to the sailing of a ship

on a specific date.

Ship$($ship$\_$number, ship$\_$name, ship$\_$builder, departure$\_$date, gross$\_$weight$)$

Cruise$($cruise$\_$number, start$\_$date, end$\_$date, director, ship$\_$number$)$

Port$($port$\_$name, country, dock$\_$number, port$\_$manager$)$

Visit$($cruise$\_$number, port$\_$name, country, arrival$\_$date, departure$\_$date$)$

Passenger$($passenger$\_$number, passenger$\_$name, SSN$,$ Address, Phone$)$

Voyage$($passenger$\_$number, cruise$\_$number, stateroom$\_$number, fare$)$

The following facts have been validated.

Both ship number and ship name are unique in Ship relation.

A ship goes on many cruises. A cruise is associated with a single ship.

$$A port can be uniquely identified by a port name and country

A cruise may visit multiple ports, and a port can be included as a stop by

multiple cruises.

$$ A passenger has a unique passenger number, and a unique SSN $.$

$$A person has a single passenger number that is used for all cruises she takes.

$$A voyage indicates that a person can take many cruises. A cruise, as

expected, can have multiple passengers.

Answer the following questions.

$(20)$ Identify a primary key, and a candidate key for each relation.

$(10)$ Identify the foreign keys of each relation, given your choice of primary keys.

$(10)$ Identify the foreign keys that are also a part of the primary keys of the same

schema they are defined on$.$

$[(10)$ Happy Hour Lines wants to track which passengers visited which ports on

which ships, and on which dates. In this case, which are the relations you will

need? Design schemas as necessary to store this information.