GU CS371 Database Design Week 4 Assignment Chapter 5 Relational Database Model

GU CS371 Database Design Week 4 Assignment Chapter 5 Relational Database Model

The assignment comes from Chapter 5 Minicase 1 but includes an additional step:
Using MySQL Workbench, create the relational data model for the database for happy cruise lines, which includes 6 tables. (Note: Be sure primary and foreign keys are correct). Export the PDF version of your model for submission.
Then complete steps a – g. Note: On step g, provide correct SQL syntax for finding the result to each item i – viii. i.e. Do not use the informal relational approach – looking for valid, correct SQL as described in Chapter 4.
You may submit a single word doc with SQL queries and your model pasted in – OR – submit 2 separate files including a PDF of your model and a word doc with your queries.

Fundamentals of Database Management Systems 2nd Edition Chapter 5 Minicase 1
1. 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.

SHIP Relation
Ship Number
Ship Name
Ship Builder
Launch Date
GrossWeight

CRUISE Relation
Cruise Number
Start Date
End Date
Cruise Director
Ship Number

PORT Relation
Port Name
Country
Number of Docks
Port Manager

VISIT Relation
Cruise Number
Port Name
Country
Arrival Date
Departure Date

PASSENGER Relation
Passenger Number
Passenger Name
Social Security Number
Home Address
Telephone Number

VOYAGE Relation
Passenger Number
Cruise Number
Stateroom Number
Fare

Additional step:
Using MySQL Workbench, create the relational data model for the database for happy cruise lines, which includes 6 tables. (Note: Be sure primary and foreign keys are correct). Export the PDF version of your model for submission.

Then complete steps a – g.
Note: On step g, provide correct SQL syntax for finding the result to each item i – viii. i.e. Do not use the informal relational approach – looking for valid, correct SQL as described in Chapter 4.
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:
Note: On step g, provide correct SQL syntax for finding the result to each item i – viii. i.e. Do not use the informal relational approach – looking for valid, correct SQL as described in Chapter 4.
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.