Hotel-Database-System

Hotel Guest Management System

Overview

The Hotel Guest Management System is a database-driven project designed to efficiently manage various aspects of hotel operations. It supports storing and processing guest information, managing room availability, handling reservations and services, and processing payments—all within a centralized MySQL database.

This project was collaboratively developed and serves as a strong foundation for building full-stack hotel management software in the future.

---

Objectives

• Create a centralized and normalized relational database for hotel operations.
• Enable core functionality for managing guests, rooms, reservations, services, and payments.
• Design a clean and consistent database schema for seamless data retrieval and manipulation.
• Provide hands-on experience with SQL, including DDL and DML operations using MySQL Workbench.

---

Features

Guest Management

• Store guest profiles with personal and contact details.
• Update or delete guest information as needed.

Room Management

• Track room types, availability, rates, and occupancy status.

Reservation System

• Handle bookings with check-in/check-out dates and reservation statuses.
• Enforce relational integrity via foreign keys.

Service Management

• Define additional services such as room service, spa, etc., including descriptions and rates.

Payment Processing

• Record payments linked to reservations.
• Support multiple payment methods and amounts.

---

Database Schema

The database includes the following main tables:

• Guest (GuestID, FirstName, LastName, Email, PhoneNumber, Address, DateOfBirth)
• Room (RoomID, RoomNumber, RoomType, RoomRate, AvailableStatus)
• Reservation (ReservationID, GuestID, RoomID, CheckInDate, CheckOutDate, ReservationStatus)
• Service (ServiceID, ServiceName, ServiceDescription, ServiceRate)
• Payment (PaymentID, ReservationID, PaymentDate, Amount, PaymentMethod)

Relational integrity is maintained using foreign key constraints.

---

SQL Implementation

Example: Table Creation

CREATE TABLE Guest (
  GuestID INT AUTO_INCREMENT PRIMARY KEY,
  FirstName VARCHAR(50),
  LastName VARCHAR(50),
  Email VARCHAR(100),
  PhoneNumber VARCHAR(20),
  Address VARCHAR(255),
  DateOfBirth DATE
);
INSERT INTO Guest (FirstName, LastName, Email, PhoneNumber, Address, DateOfBirth)
VALUES ('Ronaldo', 'Christiano', 'ronaldo.chris@example.com', '1234567890', '123 Elm St', '1985-05-15');