Skip to content

HW7: Create and populate the hotel database

Due: Friday, April 17th

Instructions

For this assignment, you should work in the same group that you worked with on HW4. If you want to change your team, you must request the change by Friday, April 10. In this homework, you will, if needed, revise your hotel database ER and relational models. Then each member of your team will build the database in their own PostgreSQL schema. Together, your team will write SQL scripts, Python scripts using Faker, or a combination of both, to load some data into your tables. Each team member will then run those scripts to load the data into their own copy of the database.

Requirements

Minimum Data

Create at least the following:

  • 5 hotels
  • 2 seasons per hotel
  • 2 types of guest categories with different discounts
  • Each hotel should contain at least 2 room types and at least 3 rooms of each type
  • Each room type should have prices for every day of the week in each of the hotel’s seasons, and some of the prices should vary.

Guests

  • 10 registered guests with a mixture of # of people accompanying them
  • All guests must have at least 1 reservation; 2 guests must have 2 or more reservations
  • At least 3 guests should have already completed their stays in the past, so that billing information will exist for them
  • At least 2 reservations should include multiple room types
  • At least 2 reservations should include multiple rooms of the same type
  • At least 2 reservations should be multi-day

  • At least 2 reservations should be within a special season (fully within)

  • Populate the tables for any extra service(s) you added

When checking your relational model, be sure that you would be able to write the following queries.

Query 1: Reservations

A registered guest would like to reserve a room in Hotel A from July 15th (check-in) to July 17th (check-out). The guest is a "gold" customer. The 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.

Query 2: Checking In

Mr. and Mrs. Smith arrive at Hotel B at 14:00 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. Write a query to list all the rooms numbers of "double" rooms that are currently unoccupied. The desk clerk should then be able to assign a specific room to the guests using an insert query or queries.

Query 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 the Smiths. The billing statement should take into account the Example Scenarios described in HW3, and the guests should have used one of the extra services that were included in your model. Finally, write a query(s) to "check out" the guests from the room.

Submission

  • PNG format image of your (updated) relational model
  • PNG format image of your (updated) ER model
  • SQL script that drops and creates your tables (with no foreign key constraints)
  • SQL script that drops the foreign key constraints
  • SQL script that adds the foreign key constraints
  • SQL script(s) and/or Python scripts that load data
  • PDF document - explain any changes you made to your ER and relational models. You do not have to write the queries, but for each set of queries, minimally write an explanation of what tables would be involved.
  • At the time of submission (and after), each team member's schema must contain the populated tables with constraints enabled