Hotel Reservation Schema

Overview

This hotel is a resort hotel on the West Arm of Lake Minnetonka. It is a relatively small hotel, with three floors of rooms. The top floor includes two large suites, and each of the other floors has eight standard-sized hotel rooms. The ground floor includes the customer service counter, a lounge area, a small exercise room, and a restaurant.

The hotel staff currently uses Excel spreadsheets to keep track of the rooms, guests, and reservations, and they have hired you to convert the existing data into a more flexible relational database.

You will build a database that satisfies at least second normal form (2NF) using the data provided here. While you are given the data itself, you are expected to identify the tables, fields, and relationships that will house this data.

This exercise uses realistic data, but not necessarily real data. Some options (like extra beds or room service) that a real hotel is likely to include have been excluded to simplify the exercise.

Hotel Data

Rooms

Room details are listed below:

Rooms 201-204 and 301-304

• Each of these rooms is a Double room with two queen beds and can sleep up to 4 people.
• Each room includes a refrigerator or a microwave, but not both.

Rooms 205-208 and 305-308

• Each of these rooms is a Single room with a single king bed and can sleep up to 2 people.
• Each room includes both a microwave and a refrigerator.

Rooms 401 and 402

• Each suite includes two separate bedrooms: one with two queen-sized beds and the other with one king-sized bed.
• Each suite also includes a shared living room space with a sleeper sofa.
• Each suite can sleep up to 8 people.
• Each suite includes a full kitchen, with a refrigerator, an electric stove and oven, and a microwave oven.
• Each suite is ADA accessible.

Base Room Prices

The base prices for the rooms are the following:

• Double: $174.99/night for 2 adults, with an additional $10 for each additional adult per night.
• Single: $149.99/night for 2 adults.
• Suite: $399.99/night for 3 adults, with an additional $20 for each additional adult per night.
• A jacuzzi adds $25 to the base price.

Additional Data

• All even-numbered rooms on the second and third floor are ADA accessible.
• All odd-numbered rooms on the second and third floor have a jacuzzi bath.
• Children (under age 18) do not incur additional charges if there is at least one adult per room on the reservation.

Room Data

The room data is as follows:

Guests

Guest information is collected only when a guest makes a reservation, so all guests have at least one reservation in the system. Guests who have made reservations include the following:

Reservations

The following reservations appear in the current reservation system:

Comments:

• In the database, a reservation can include multiple rooms reserved by the same guest and for the same dates. There are two examples in the table above.
• Adults are guests whose age is 18 or older.
• Children are guests under the age of 18.
• Start date represents the first evening the guest will stay at the hotel.
• End date represents the day the guest plans to check out.

Instructions

Part 1: ERD

Using the data presented above, create an ERD that represents the database.

Specifications for the ERD:

• The ERD should be created separately from the RDBMS. (E.g., Draw.io; shapes in Google Docs, Word, or PowerPoint; or paper and pencil)
• The design should be in second normal form.
• All tables and columns should be named appropriately so that it is easy to map data to the database.
• The ERD should specify the data type and field size (where appropriate) for each column.
• The ERD should indicate the nullability of all non-key fields.
• The ERD should identify primary keys and foreign keys in each table.
• The ERD should identify all relationships between tables.

Part 2 and 3: Define the Database and Manage the Data

Using your ERD as an example, create a SQL script that performs the following steps:

• Drops the existing database, if it exists, so that the script can rebuild a database with the same name.
• Creates the tables with all appropriate fields, data types, and keys, using the structure indicated in the ERD.
  • Tip: Create the primary tables first, followed by the tables with foreign keys.

The script should run from beginning to end without errors.

Next, using the data provided in the tables above, create the required SQL statements to populate the tables with the data above.

• Include a random name, address, and phone number in the first record of the table for guests. You may use a fictitious address and phone number, as long as you use a random name.
• As with the tables, add data to the primary tables before adding data to the tables with foreign keys.

Second, after adding all of the data above, create SQL statements that will delete Jeremiah Pendergrass and his reservations from the database.

• Deleting data should start with records that reference Jeremiah Pendergrass using a foreign key and then delete the record from the guest table as the last step.
• The scripts should only delete records related to Jeremiah Pendergrass and his reservations. They should not delete any room data.

The script should run from beginning to end without errors.

Part 4: Query the Database

Create a third SQL script that includes queries to retrieve the following data from the database.

• Write a query that returns a list of reservations that end in July 2023, including the name of the guest, the room number(s), and the reservation dates.
• Write a query that returns a list of all reservations for rooms with a jacuzzi, displaying the guest's name, the room number, and the dates of the reservation.
• Write a query that returns all the rooms reserved for a specific guest, including the guest's name, the room(s) reserved, the starting date of the reservation, and how many people were included in the reservation. (Choose a guest's name from the existing data.)
• Write a query that returns a list of rooms, reservation ID, and per-room cost for each reservation. The results should include all rooms, whether or not there is a reservation associated with the room.
• Write a query that returns all the rooms accommodating at least three guests and that are reserved on any date in April 2023.
• Write a query that returns a list of all guest names and the number of reservations per guest, sorted starting with the guest with the most reservations and then by the guest's last name.
• Write a query that displays the name, address, and phone number of a guest based on their phone number. (Choose a phone number from the existing data.)

For each query, include:

• The request from this assignment as a comment above the query, including the number
• The query itself