Skip to content

HW8: Query the hotel database

Due: Friday, May 1

This homework will be worth double the value of the other homeworks, reflecting its importance as the culmination of all the Hotel Database homeworks.

Instructions

For this assignment, you should work in the same group that you worked with on HW7. In this homework, you will write queries for your hotel database. In homework 7, you should have filled your database with a small set of representative data, but you may have to update your insert scripts/programs to add more/different data in order to test and demonstrate your queries.

You must create at least one index on a table to improve the performance of one or more of these queries. Add the create index code in your table creation script, but explain your reasoning with the query explanation below.

If creating a view will make your query code simpler, then add the create view code to your table creation script and explain your reasoning with the query explanation.

Requirements

For the specific data mentioned in these query descriptions, you may use other specifics that correspond to the data you already have in your tables. The specific data, in italics, (e.g. hotel name, dates, guest names, etc.) should be in the where clauses of the queries.

You may write the queries as SQL scripts, SQL functions or using Python and psycopg. You must use transactions as appropriate.

The query descriptions list some data that must be in place in the database, but those lists may not be exhaustive for your particular hotel model, so the overriding principle is that you must have data in your database that fully tests the query.

Query Set 1: Reservations

A new guest would like to reserve a room in Hotel A from July 15th (check-in) to July 17th (check-out). The guest qualifies as a VIP customer. The select query should return all the different room types that are available in the hotel for those two nights, along with be the average cost per night for that room type (cost per night divided by # of nights). The cost per night must take into account the current season and the days of the week of the requested dates of stay, along with any special rates for gold customers.

The insert queries should insert the new guest and reserve one room of one of the available room types for the guest on their desired dates in the desired hotel.

There must be data in the tables such that there is at least one room type that is not available on the requested day(s), and the room price must vary across the 2 days due to the day of the week. You may assume that the whole day period falls fully within one season. The selected customer category must change the overall price.

Query Set 2: Checking In

Mr. and Mrs. Smith arrive at Hotel B in the late afternoon on the day of their reservation. They have previously reserved a room type double. In order to give the Smiths their room, the desk clerk needs to find an appropriate room. The select query should list all the room numbers of double rooms that are currently unoccupied. The insert quer(ies) should assign the room, assuming Mrs. Smith is the guest who made the reservation, and Mr. Smith an occupant who was not previously in the database.

This query should assume the reservation for Mrs. Smith already exists in the database. There should be at least 1 room of the reserved room type that is already occupied and is not returned as an available room.

Query Set 3: Checking Out

Two nights have passed, and Mr. and Mrs. Smith are ready to check out. Write a query to generate the billing statement for Mrs. Smith. An insert query should insert at least one charge to the reservation for whatever extra service you have modeled in your database. A select query should return the date range, room type, extra feature(s) and total cost for the whole stay. The room price must not be the same on every night of the reservation, but the reservation must fall fully within one season. Mrs. Smith should belong to a guest category that changes the price of the reservation. Insert/update query(s) should "check out" the Smiths from the room and record anything required for the way you store billing history.

Query 4: Find the occupants

Write a query to find the names of the occupants and the person that reserved the room for a specific room on a specific date. There should be data in the database such that this query will return at least 2 people (the reserver and at least 1 occupant).

Query 5: Total spending over a year

For a guest who has made at least 2 reservations in at least 2 different hotels in the chain during a year, write a quer(ies) to find the total amount of money spent by the guest at the chain during a given one-year period.

Final Report

The report for this homework should be formal. An example format is shown here.

Submission

Add me to your group's HW8 github repository. Follow the structure of the sample repo.

  • Submit all the HW7 files, even ones that have not changed.

HW8:

  • Report: Follow the structure of the sample report. Explain any changes you made to HW7 items. Explain each of your queries. Show the data in the database that exists so that the quer(ies) could be run, and show the results.
  • Provide SQL script(s) or Python file(s) for each of the query sets.
  • At the time of submission (and after), each team member's schema must contain the populated tables with constraints enabled, such that the queries will run as described in the document.
  • The team must meet the instructor to demo the queries. Times will be arranged.