Hotel Management Database

A MySQL-based hotel management system demonstrating relational database design, business logic implementation, and operational workflows for hotel management.

---

✨ Highlights

• 🏨 Relational database design
• 🗄️ Normalized MySQL schema
• ⚙️ Stored procedures
• 🔄 Business rule enforcement with triggers
• 📊 Reporting views
• 🧪 Demo scripts with sample data

---

📖 Overview

This project implements a simplified hotel management database covering the core business operations of a hotel, including room reservations, payments, housekeeping, and maintenance requests.

The system demonstrates practical database engineering concepts such as relational modeling, stored procedures, triggers, views, constraints, and automated business logic using MySQL.

---

🏗️ Architecture

flowchart TD

    CUSTOMER --> BOOKING
    ROOM --> BOOKING

    BOOKING --> PAYMENT

    ROOM --> CLEANING_TASK

    ROOM --> MAINTENANCE_REQUEST

    STAFF --> CLEANING_TASK

Loading

---

🛠️ Technologies

• MySQL
• SQL
• MySQL Workbench
• Stored Procedures
• Triggers
• Views

---

🗂️ Database Modules

Module	Purpose
CUSTOMER	Guest information
ROOM	Room information and availability
BOOKING	Room reservations
PAYMENT	Booking payments
CLEANING_TASK	Housekeeping workflow
MAINTENANCE_REQUEST	Maintenance tickets
STAFF	Hotel staff information

---

📂 Repository Structure

Hotel-Management-Database/
├── 00_schema.sql
├── 01_triggers.sql
├── 02_procedures.sql
├── 03_views.sql
├── 04_seed_data.sql
├── 05_test_demo_manual.sql
├── 06_test_demo_auto.sql
├── ERD_Diagram.png
├── Hotel_Booking.drawio.png
└── README.md

---

🚀 Features

• Normalized relational database schema
• Primary and foreign key constraints
• Stored procedures for booking and checkout
• Trigger-based business rules
• Automated housekeeping workflow
• Reporting views
• Sample data
• Manual and automated demo scripts

---

⚙️ Setup

Run the SQL scripts in the following order:

1. 00_schema.sql
2. 01_triggers.sql
3. 02_procedures.sql
4. 03_views.sql
5. 04_seed_data.sql
6. 05_test_demo_manual.sql or
7. 06_test_demo_auto.sql

The project is designed for MySQL Workbench but is compatible with standard MySQL installations.

---

🔄 Business Workflow

Customer
      │
      ▼
Create Booking
      │
      ▼
Payment
      │
      ▼
Check-out
      │
      ▼
Cleaning Task Created
      │
      ▼
Room Available

---

📷 Screenshots

Recommended screenshots:

• ER Diagram
• Booking workflow
• Procedure execution
• Reporting views
• Trigger demonstration

---

🎯 Demonstrated Concepts

• Procedure-based booking creation (sp_create_booking)
• Booking conflict prevention using triggers
• Automated housekeeping after checkout
• Revenue reporting using SQL views
• Maintenance request management

---

⚠️ Current Limitations

• One booking is limited to one room.
• Cleaning staff assignment is optional.
• No user authentication layer.
• Designed for educational purposes.

---

🔮 Future Improvements

• Multi-room reservations
• Employee scheduling
• Inventory management
• Billing module
• Web interface
• REST API integration
• Role-based access control

---

👤 Author

To Minh Ly