2. 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. \begin{tabular}{|l|l|l|l|l|} \hline Ship & Ship & Ship & Launch & Gross \\ Number & Name & Builder & Date & Weight \\ \hline \end{tabular} SHIP Relation \begin{tabular}{|l|l|l|l|l|} \hline Cruise & Start & End & Cruise & Ship \\ Number & Date & Date & Director & Number \\ \hline \end{tabular} CRUISE Relation \begin{tabular}{|l|l|l|l|} \hline PortName & Country & NumberofDocks & PortManager \\ \hline \end{tabular} PORT Relation \begin{tabular}{|l|l|l|l|l|} \hline CruiseNumber & PortName & Country & ArrivalDate & DepartureDate \\ \hline \end{tabular} VISIT Relation Answer the following questions using the MySQL database. The deliverable will be a Microsoft Word or PDF document containing the screenshots of (or the actual) SQL statements used to answer each question. (20 points) a. Create a database called "HAPPY CRUISE LINES". b. Create the six (6) tables using the appropriate DDL statement(s) based on the entity-relationship model you created as well as the relations provided. (Hint: make reasonable assumptions with regards to the data types and lengths using the CSV files provided.) c. Identify the candidate keys of each relation. d. Define the primary key and any alternate keys of each relation using the appropriate DDL statement(s). e. Define the foreign keys of each relation using the appropriate DDL statement(s). 3. Load the records provided in the six (6) CSV files into the appropriate tables. The deliverable will be a Microsoft Word or PDF document containing screenshots of for the actual SQL statements) used to create each table as well as the results from executing a "SELECT * FROM" query against each table. (20 points) 4. Write SQL SELECT commands to answer the following queries. The deliverable will be a Microsoft Word or PDF document containing the screenshots of for the actual SQL statements) showing both the query and results obtained from your MySQL database. ( 3 points each, 40 points total) a. Find the start and end dates of cruise number 35218 . b. List the names and ship numbers of the ships built by the Ace Shipbuilding Corp. that weigh more than 60,000 tons. c. List the companies that have built ships for Happy Cruise Lines. d. Find the total number of docks in all the ports in Canada. e. Find the average weight of the ships built by the Ace Shipbuilding Corp. that have been launched since 2000. f. How many ports in Venezuela have at least three docks? g. Find the total number of docks in each country. List the results in order from most to least. h. Find the total number of ports in each country. i. Find the total number of docks in each country but only include those countries that have at least twelve docks in your answer. j. Find the name of the ship that operated on (was used on) cruise number 35218. k. List the names, country, and state of the passengers who sailed on The Spirit of Nashville on cruises that began during July 2003. 1. Find the names of the company's heaviest ships. m. Find the names of the company's heaviest ships that began a cruise between July 15 , 2003 , and July 31, 2003. "CRUISENUM", "STARTDATE", "ENDDATE", "DIRECTOR", "SHIPNUM" 21644,7/5/20020:00:00,7/12/20020:00:00,"Smith",923007,8/14/20020:00:00,8/24/20020:00:00,"Chen"",2024288,3/28/20030:00:00,4/4/20030:00:00,"Smith",926964,7/1/20030:00:00,7/11/20030:00:00,"Gomez",2027045,7/15/20030:00:00,7/22/20030:00:00,"Adams",1228532,8/17/20030:00:00,8/24/20030:00:00,"Adams",1229191,12/20/20030:00:00,12/27/20030:00:00,"Jones",929890,1/15/20040:00:00,1/22/20040:00:00,"Levin",2035218,2/25/20080:00:00,3/5/20080:00:00,"Carter",3242983,7/15/20110:00:00,7/22/20110:00:00,"Lucas",2842996,7/22/20110:00:00,7/29/20110:00:00,"Lucas",2842999,7/29/20110:00:00,8/5/20110:00:00,"Norton",2843744,7/25/20110:00:00,8/10/20110:00:00,"Goldberg",4144791,7/21/20110:00:00,7/28/20110:00:00,"Barton",2045109,7/2/20110:00:00,7/9/20110:00:00,"Adams",2545231,7/9/20110:00:00,7/16/20110:00:00,"Davidson",2545270,7/16/20110:00:00,8/3/20110:00:00,"Cohen",2545382,8/3/20110:00:00,8/10/20110:00:00,"Stevenson",25 "PASSENGERNUM", "PASSENGERNAME", "SOCSECNUM", "STATE", "COUNTRY" 133861, "Smythe", 83776427, "London", "UK" 142174, "Carter", 42916465, "Georgia", "USA" 225729, "Johnson", 93015012 , "Georgia", "USA" 234149, "Kaplan", 62945822, "Oregon", "USA" 254381, "Lopez", 59244163, "Madrid", "Spain" 259028, "Gonzalez", 13287482, "Texas", "USA" 310656, "Simmons", 49271183, "California", "USA" 394587, "Singh", 34575207, "Illinois", "USA" 425911, "Dodd", 52478221, "New York", "USA" 428247, "Jennings", 48375913, "Texas", "USA" 481912, "DuBois", 48294713, "Paris", "France" 524891, "Carter", 72716328, "Ohio", "USA" 628902, "Smith", 31498577, "Tennessee", "USA" 739716, "Lopez", 32572856, "Arkansas", "USA" 759285, "Jackson", 49218549, "Tennessee", "USA" 842642, "Smith", 10482746, "North Carolina", "USA" 901734, "Jones", 58377163, "Tennessee", "USA" 945198, "Billings", 82711611 , "Oregon", "USA" "PORTNAME", "COUNTRY", "NUMDOCKS", "MANAGER" "Boston", "USA", 15, "Martin" "Caracas", "Venezuela", 18, "Gomez" "Fuzhou", "China", 26, "Yang" "Gdansk", "Poland", 9, "Kowalski" "Halifax", "Canada", 18, "Jones" "Hangzhou", "China", 37, "Zhang" "Long Beach", "USA", 41, "Baker" "Maracaibo", "Venezuela", 12, "Perez" "Miami", "USA", 14, "Vincent" "Mombasa", "Kenya", 10, "Smith" "Montreal", "Canada", 12, "Williams" "New Orleans", "USA", 19, "Adams" "New York", "USA", 32, "Carter" "Oakland", "USA", 35, "Fields" "Puerto Cabello", "Venezuela", 2, "Gonzalez". "Shanghai", "China", 45, "Chen" "Vancouver", "Canada", 23," Smythe" "SHIPNUM", "SHIPNAME", "BUILDER", "LAUNCHDATE", "WEIGHT" 5, "Sea Joy", "Jones", 5/12/1999 0:00:00,80000 7, "The Spirit of Memphis", "Ace Shipbuilding Corp.",10/20/1998 0:00:00, 65000 9, "Ocean IV", "Ajax", 12/10/2003 0:00:00,75000 12 , "Prince A1 ", "Ajax", 7/21/2004 0:00:00,90000 16, "Ocean V", "Ace Shipbuilding Corp.",1/27/2010 0:00:00,95000 20, "Queen Shirley", "Master", 9/1/19990:00:00,80000 25, "The Spirit of Nashville", "Master", 4/30/2006 0:00:00,48000 28, "Sea Peace", "Ace Shipbuilding Corp.",9/22/2010 0:00:00,95000 32, "King of the Sea", "Ace Shipbuilding Corp." ,6/24/2006 0:00:00, 55000 41, "Princess of Florida", "Jones", 2/11/20110:00:00,95000 "CRUISENUM", "PORTNAME", "COUNTRY" , "ARRDATE", "DEPDATE" "PASSENGERNUM", "CRUISENUM", "ROOMNUM", "FARE" 133861,29191,1155,$680.00142174,45270,5346,$999.00225729,26964,3662,$890.00234149,27045,6236,$845.00254381,45270,11383,$1550.00259028,45382,12414,$1499.00310656,35218,4233,$950.00394587,42983,2562,$690.00425911,45382,7434,$875.00428247,45231,10754,$1256.00481912,45231,4252,$1000.00524891,45382,7245,$1290.00628902,42983,4564,$799.00739716,27045,5234,$725.00759285,21644,6435,$1099.00842642,24288,4215,$875.00901734,45109,3182,$950.00945198,45109,8272,$800.00