Skip to content

GP4: Queries and Charts

Due: Monday, April 15th

Google Charts screenshot
Image source: developers.google.com

Instructions

For this assignment, you will (1) write four test queries for your database, and (2) use Flask to get user inputs, run the queries, and display the results. Begin by creating a new folder in your GitHub repository named webapp. The webapp folder should contain the following files and subfolders:

  • .venv/ – Python virtual environment
  • .vscode/
    • launch.json – generated by VS Code
  • functions/
    • one .sql file per query that you write
  • templates/
    • one .html file per query that you write
  • app.py – flask application
  • db.py – database module

Note: Each team member needs to set up their own virtual environment on their laptop. The .venv folder is machine-specific and should not be added to the repository. (The .gitignore file should prevent the .venv folder from being added.)

Requirements

Write an SQL function for each of the following queries. After determining the parameters, write a Jinja template for the user to run the query. The template should include a <form> for user inputs and a <div> for a Table Chart. You are welcome, but not required, to add other content or styling to the template.

The filenames under functions/ and templates/ should match the SQL function names. For example, if you have a SQL function named hotel_search(), then the corresponding filenames should be hotel_search.sql and hotel_search.html.

In app.py, create a @app.route("/") that returns a simple HTML page with a link to each of the four queries. That way, you won't have to enter the route to each query manually in your web browser.

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 of type double that are currently unoccupied and clean. The desk clerk should be able to click the room number to assign to the guests.

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 on GP1. After the bill is displayed on the screen, the user clicks a "check out" button.

Query 4: Special Feature

Write a query for the special feature of your database that only your team has designed. The query should be about as complex as the previous three. After displaying the results of the query, the user should be able to click on something that causes the database to be updated. For example, your feature might involve streaming movies in hotel rooms. The query would show available movies (title, rating, runtime, and other details) along with the rental cost.

Example Files

You may use the flight_search() example application from class as a starting point. (Right-click and Save link as…)

Documentation

Refer to the following documentation sites as needed: