🔧 Requirement: The MySQL database server and client (eg.MySQLWorkbench)
Hotel Software Guild 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. © mThree
- Entity Relationship Diagram (ERD)
- Database
- Data
- Queries
- Displays the name, address, and phone number of a guest based on their phone number
- Returns a list of reservations that end in July 2023, including the name of the guest, the room number(s), and the reservation dates
- 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
- 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
- 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
- Returns all the rooms accommodating at least three guests and that are reserved on any date in April 2023
- 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
- Heap © mThree
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.
The room data is as follows:
Sample
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:
Sample
The following reservations appear in the current reservation system:
Sample
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.