HW06: Creating and Populating Databases due Wed 21 Feb 13:00

\begin{purpose}
This assignment provides practice in using SQL scripts to create, populate, and
modify databases.
\end{purpose}

Allowed and Disallowed Resources

In completing this assignment you MAY use/access the following resources:

You may NOT use/access:

Failure to abide by these guidelines will result in a zero for the assignment and the incident will be reported to the university provost as a violation of the university academic integrity policy. A second incident of academic dishonesty (whether from this course or another computer science course) will result in an F in the course.

Description

IMPORTANT: It is likely that your current database design will not accommodate all of the requirements described below. If that is the case then you will need to modify your design. This assignment will be pretty time-consuming if you do it correctly.

Create a text file (with a .sql extension) containing SQL commands that will:

  1. (2 pts) Establish a schema called hoteldb and set the search path to it.
  2. (8 pts) Create tables to implement the hotel database you've been working in for the last two assignments.
  3. (8 pts) Populate the tables with at least 7 reservations with additional corresponding information as follows:
    1. one customer should have three separate reservations with two payment methods (one payment method for one stay and another payment method for the other two stays)
    2. the data should include stays with children and without children
    3. should have multiple customers staying on same day as well as days with only one customer
    4. should have rooms that have not been used and rooms that have been used on multiple nights
    5. should have a single customer with multiple reservations on the same day
  4. Write the following select queries:
    1. (2 pts) a query that will display (in order of date and then room number) customer name and amount paid for room.
    2. (2 pts) a query that will display the total amount a customer has paid across all reservations
    3. (2 pts) list of rooms that haven't been reserved on a given date

  5. (2 pts) Demonstrate a delete query (of your choice).
  6. (2 pts) Demonstrate an update query (of your choice).

Submission and Grading

Once you have completed the document upload your hoteldb.sql file into canvas.