Hotel Booking Management System Database

A relational database project developed using Microsoft SQL Server for managing hotel branches, managers, staff, rooms, customers, reservations, invoices, and customer requests.

Main Features

• Stores hotel branch, manager, and staff information
• Manages room types, room rates, and room availability
• Stores customer details and reservations
• Supports bookings containing one or more rooms
• Records invoices, payments, ratings, and reviews
• Stores additional hotel services requested by customers
• Includes sample data for testing
• Includes analytical SQL queries for revenue, customer spending, reservations, and room ratings

Database Tables

The database contains the following tables:

• Branch
• Manager
• Staff
• Room
• Availability
• Customer_Details
• Reservation
• Booked_Rooms
• Invoice
• Request
• Customer_Request

Booked_Rooms connects reservations with rooms, while Customer_Request connects reservations with additional service requests.

SQL Concepts Used

• Database and table creation
• Primary keys and foreign keys
• Composite primary keys
• INSERT, UPDATE, and SELECT
• INNER JOIN
• Subqueries
• Aggregate functions such as SUM, AVG, and COUNT
• GROUP BY, HAVING, and ORDER BY
• Date filtering using YEAR() and MONTH()
• Many-to-many relationships

Analytical Queries

The SQL script includes queries that:

1. Find customers who have not made reservations since the beginning of 2023
2. Calculate monthly revenue for 2023
3. Find customers with more than three reservations
4. Calculate average ratings and review totals by room type
5. Calculate total revenue generated by each branch
6. Calculate the total amount spent by each customer
7. Compare total and average spending by gender
8. Display branch, manager, and room information
9. Find the customer with the highest number of reservations
10. Find customers who stayed at more than one branch

Requirements

• Microsoft SQL Server
• SQL Server Management Studio (SSMS)

The script uses Microsoft SQL Server syntax, including NVARCHAR, TOP, YEAR(), and MONTH().

How to Run

1. Open SQL Server Management Studio.
2. Connect to a SQL Server instance.
3. Open the SQL script.
4. Make sure the beginning of the script contains:

CREATE DATABASE X_Hotel_fin;
GO

USE X_Hotel_fin;
GO

5. Execute the script to create the tables and insert the sample data.
6. Run the analytical queries at the bottom of the file individually to view their results.

Project Files

hotel-booking-management-system/
├── hotel_booking_management.sql
├── project-report.docx
└── README.md

Recommended file renaming:

FinalXHotel(1).sql                       -> hotel_booking_management.sql
Database Assignment Part 2 final(1).docx -> project-report.docx

Contributors

• Hein Htet
• Hemshika Shreeya Jadoo
• Mohammad Abu Nur Tasfi
• Stephie Li Ah Kane

Purpose

This project was created as a group assignment for the Introduction to Database module at Asia Pacific University of Technology & Innovation.

Note

The records included in the project are sample data created for educational and demonstration purposes.