Databases Reference
In-Depth Information
• 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.
VOYAGE Relation
Passenger
Cruise
Stateroom
Number
Number
Number
Fare
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.
2.
Super Baseball League Consider the following relational
database for the Super Baseball League. It keeps track
of teams in the league, coaches and players on the teams,
work experience of the coaches, bats belonging to each
team, and which players have played on which teams.
Note the following facts about this environment:
• The database keeps track of the history of all of the
teams that each player has played on and all of the
players who have played on each team.
• The database keeps track of only the current team that
a coach works for.
• Team Number, Team Name, and Player Number are
each unique attributes across the league.
• Coach Name is unique only within a team (and we
assume that a team cannot have two coaches of the
same name).
SHIP Relation
Ship Number
Ship
Ship
Launch
Gross
Number
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
PASSENGER Relation
Passenger
Passenger
Social Security
Home
Telephone
Number
Name
Number
Address
Number
Search WWH ::
Custom Search