Create a database in SQLServer for the relational model provided in the HW3 Relational Schema file Populate your database with some data to test your database Write the requested queries in SQL The relational model that is provided is part of the relational model from your HW 2, however it has been simplified The E R diagram for this relational model is also provided to you in the HW3 Data Model file First create an SQL Server database by right clicking on the databases folder in the Object explorer and selecting the New Database option Name the database by the initials of your last names and then A3 For instance a database created by Johnson and White would be named as jw A3 Write the SQL statements for creating the tables in your database, specifying data types and field lengths, establishing primary keys and foreign keys, and implementing all the necessary constraints Save your create statements in a file Use the same naming convention you used to name your database to name your file In your file also include the following SQL statements An SQL statement to add a constraint to one of the tables An SQL statement to add a column to one of the tables An SQL statement to insert data to one of the tables An SQL statement to modify the data in one of the tables An SQL statement to delete a row of data from one of the tables Populate your database with sample data Write the following SQL queries and save them in your file also Put the question numbers above your queries, e g Q1, Q2, Q3 etc Ensure that your queries are correct and produce the results you expected Which cruises have Istanbul as the departure port (Hint the visit order should be equal to 1) Which passengers have visited Venice in October 2015 (Hint you may use the Month and Year functions on SailingDate and see if SailingDate was in October 2015 ) What is the duration of each cruise (Hint Use the DATEDIFF function to determine the duration) What is the number of bookings on each cruise Display cruiseId and number of bookings What is the average rating of all the cruises PLEASE ANSWER ONLY THE SECTIONS THAT ARE DARK COLORED THANK YOU ) World Traveler Cruise Line SHIP Ship ID ShipName Weight Year Built Passenger Capacity CRUISE Cruise Serial Number Sailing Date Return Date VISITS Visit Order HE SHIP Ship Number Ship Name Weight Year Built Passenger Capacity CRUISE CruiseSerialNo Sailing Date ReturnDate Shiold Booking CruiseSerialNo Passengerid Price Satisfactionate Passenger Passenger DPFirstName PlastName PStreetAddress PCity PState PPostal Code PCountry BOOKING Price Satisfaction Rate PORT PortID Port Name Country Docking Fee VISIT Cruise Serial No PortID VisitOrder PORT Portid PortName PortCountry Docking Fee PASSENGER Passenger Number Name (First Name, Middle Name, Last Name) Address (Street Address, City, State, Postal Code, Country)

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Sep 26, 2024

Create a database in SQLServer for the relational model provided in the HW3- Relational Schema file. Populate your database with some data to test your

Create a database in SQLServer for the relational model provided in the HW3- Relational Schema file. Populate your database with some data to test your database. Write the requested queries in SQL. The relational model that is provided is part of the relational model from your HW 2, however it has been simplified. The E-R diagram for this relational model is also provided to you in the HW3_Data Model file

First create an SQL Server database by right clicking on the databases folder in the Object explorer and selecting the New Database option. Name the database by the initials of your last names and then A3. For instance a database created by Johnson and White would be named as jw_A3.
Write the SQL statements for creating the tables in your database, specifying data types and field lengths, establishing primary keys and foreign keys, and implementing all the necessary constraints. Save your create statements in a file. Use the same naming convention you used to name your database to name your file.
In your file also include the following SQL statements
1. An SQL statement to add a constraint to one of the tables.
2. An SQL statement to add a column to one of the tables.
3. An SQL statement to insert data to one of the tables
4. An SQL statement to modify the data in one of the tables.
5. An SQL statement to delete a row of data from one of the tables.
Populate your database with sample data.
Write the following SQL queries and save them in your file also. Put the question numbers above your queries, e.g. Q1, Q2, Q3 etc. Ensure that your queries are correct and produce the results you expected.
1. Which cruises have Istanbul as the departure port (Hint: the visit order should be equal to 1)
2. Which passengers have visited Venice in October 2015? (Hint: you may use the Month and Year functions on SailingDate and see if SailingDate was in October 2015.)
3. What is the duration of each cruise (Hint: Use the DATEDIFF function to determine the duration)
4. What is the number of bookings on each cruise? Display cruiseId and number of bookings.
5. What is the average rating of all the cruises?

PLEASE ANSWER ONLY THE SECTIONS THAT ARE DARK COLORED. THANK YOU :)

World Traveler Cruise Line SHIP Ship ID ShipName Weight Year Built Passenger Capacity CRUISE Cruise Serial Number Sailing Date Return Date VISITS Visit Order HE SHIP Ship Number Ship Name Weight Year Built Passenger Capacity CRUISE CruiseSerialNo Sailing Date ReturnDate Shiold Booking CruiseSerialNo Passengerid Price Satisfactionate Passenger Passenger DPFirstName PlastName PStreetAddress PCity PState PPostal Code PCountry BOOKING Price Satisfaction Rate PORT PortID Port Name Country Docking Fee VISIT Cruise Serial No PortID VisitOrder PORT Portid PortName PortCountry Docking Fee PASSENGER Passenger Number Name (First Name, Middle Name, Last Name) Address (Street Address, City, State, Postal Code, Country)