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

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

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 select query that will display (in order of date and then room number) customer name and amount paid for room.
    2. (2 pts) same as above except group by customer showing the total the customer paid rather than per-room
    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.

Database Systems

Course Resources

Grades and Homework

Quick Links