Dr. Jenny Brown is the new CEO of a hotel chain, Hassle Free Hotel. The hotel’s headquarter is based in Atlanta, Georgia in the U.S. The company was established in 2004. It has a central reservation system to book rooms for their customers. With more people using Airbnb to book accommodation these days, Hassle Free Hotel is struggling to attract new customers and retain old customers. With the impact of COVID-19 and the reduction of international travel, the hotel is on the brink of bankruptcy. The CEO would like to launch a marketing campaign to revive the company. Before investing money, Dr. Brown would like to conduct an in-depth analysis of the company’s past reservations.

You joined the hotel chain as a freelance analyst 2 weeks ago. Knowing that you have learned about different aspects of accounting information systems such as database concepts and internal control; and that you are proficient in using Microsoft EXCEL and ACCESS to analyse data, Dr. Brown has asked you to help her analyse the hotel chain’s reservation data. To start your work, you are given an ACCESS file (HassleFreeHotel.accdb) with 3 tables (Customers, Rooms, Reservations). 

Note: Data file for this assignment is available on the Blackboard.

The specific requirements from Dr. Brown are described below.

Part 1: ACCESS (Database structure, Query, Report)

1. The first thing Dr. Brown wants you to do is to add two fields to customers’ records: Phone Number and State. 
2. Dr. Brown wants future reservations to record information about which employee has handled the reservation. So you should create an Employee table that stores the following attributes. You need to select appropriate attribute(s) to be the primary key in the Employee table. The design of the Employee table should follow the design of the Customer table. 
   1. Employee ID (use the Number format)
   2. Employee Gender
   3. Employee Last Name
   4. Employee First Name
   5. Employee Home Address
   6. Employee Date of Birth
   7. Employee Phone Number
3. Each employee can make multiple reservations but each reservation is done by only one employee. You need to decide how to link the Employee table to the Reservation table by adding the foreign key to the appropriate table. 
4. Each customer can make multiple reservations but each reservation is linked to only one customer. Each hotel room can be booked by different customers, but each reservation can book only one room. If a customer wants to book two rooms for a stay, two reservations will need to be created. 
5. Link all the tables in an entity relationship diagram and ensure the reference integrity rule is enforced.
6. Using the revised Access file, you create queries to provide useful information to Dr. Brown.  You also create reports based on the queries. You should give a meaningful name to each query and report (e.g., Query1_Length_of_Stay_Booking_Number). The query results and reports should be formatted professionally.
   1. Query 1 shows how many bookings were made for each length of stay. Give an appropriate name to the query, result headings and report. Sort the results by the number of bookings in descending order. 
   2. Query 2 shows the total number of rooms in each hotel. Sort the results by the name of the hotel in ascending order.
   3. Query 3 shows the number of rooms that provide a clean environment (i.e., rooms that are non-smoking and do not allow pets). Sort the results by the number of rooms in descending order.  
   4. Query 4 shows the number of customers in each occupation category. Results should be sorted by the number of customers in descending order. 
   5. Query 5 shows the total capacity of each hotel (hint: add up the room capacity of all the rooms). Results should be sorted by the hotel capacity in descending order.
   6. Query 6 shows the average, maximum, and minimum age of the customers for each hotel.  Calculate their age as at 1 January 2021. Results should be sorted by Hotel Name in ascending order. 

Hint: do not worry about leap years, 1 year=365 days. 

7. Query 7 creates a table that merges all data about Customers, Reservations and Rooms into ONE table. Give a suitable name to the new table.
   Hint (1): exclude Employee table and attributes without any data at the moment.  
   Hint (2): make sure no attribute is duplicated.

Part 2: EXCEL (Cell referencing, Formulas, Formatting, Pivot Table, Pivot Chart)

7. Export the table you created in Query 7 to an EXCEL file.
   Name the Excel file as: LastName_FirstName_StudentNumber_HasselFreeHotel.xlsx.
   Name the worksheet “Hassel Free Hotel Summary Data”. Ensure that there are no duplicate columns. 
8. Create a new worksheet to give an overview of the Excel file such as what information is in each worksheet, who created the file, etc. 
9. On the summary data worksheet, for all records, insert a column to the immediate right of the Customer’s Last Name column to store the full name of each customer by combining their First Name and Last Name. 
10. For all records, insert a column to the immediate right of Customer’s Date of Birth column and calculates the age of customer as at 1/01/2021.
    Hint: do not worry about leap years, 1 year=365 days.  Also, use the Round function to remove decimal places.
11. For all records, insert a column to the immediate right of Customer Age to classify customers into either “Gen X”, “Gen Y” or “Gen Z”. Customers whose age is below 25 are classified as “Gen Z”. Customers whose age is between 25 and 45 are classified as “Gen Y”. Customers whose age is over 45 are classified as “Gen X”.
12. The following table shows the pricing policy of the hotel. Enter the table data beginning at cell V1. Create a column to the immediate right of the Hotel Name column and name it Income.  Calculate the income for each reservation using the VLOOKUP function, and format the cell appropriately. The formula on row 2 should be flexible to allow you to copy it down to all rows. In case of error, the resulting column should show “Error! Contact [Your full name]” instead of “#N/A”. 

Room Capacity	Daily Rate ($)
1	99
2	119
3	149
4	199
5	219

13. Dr. Brown would like to know whether rooms not allowing pets and not allowing smoking were the most popular. Create a pivot table (PT_RoomType) to find out the percentage of reservations for each type of room. You should use Generation Category as a filter. Format the resulting pivot table professionally. Are the results consistent across different generations? Hint: 4 types of rooms 
14. Dr. Brown wants to know how much income was generated by each hotel. Create a pivot table (PT_HotelIncome) to find out the total income of each hotel. Sort the results in descending order.
15. Dr. Brown wants to know whether the hotel with the highest income out-performed the second best performed hotel in all quarters. Create another pivot table (PT_QuarterlyIncome) and a Pivot chart that shows hotel income in each of the four quarters. To facilitate analysis, you need to also create a slicer (Hotel Name) so that Dr. Brown can use the slicer to narrow down to the results of the pivot chart. You should format the pivot table and pivot chart to show appropriate headings and put the Pivot Chart below the Pivot Table results.