Hotel Management System - Database Design

A relational database design for a Hotel Management System (HMS) developed as an academic team project for CIT 1305 - Database Management System at the University of Tabuk.

The design models hotel operations including reservations, rooms, guests, payments, housekeeping, service requests, employees, departments, and room types.

Main ERD

Reservation & Billing Subsystem

Project Scope

The database design supports:

• Room reservation management
• Check-in and check-out workflows
• Housekeeping task management
• Room service and amenity requests
• Billing and payment processing
• Staff and department management
• Room inventory and maintenance tracking
• Guest profiles and stay history

Data Model

The design contains 9 core entities:

1. Guest
2. Reservation
3. Room
4. RoomType
5. Department
6. Employee
7. Housekeeping
8. ServiceRequest
9. Payment

Key relationships include:

• Guest -> Reservation: 1:N
• Reservation -> Room: N:1
• Room -> Department: N:1
• Employee -> Department: N:1
• Employee -> Housekeeping: 1:N
• Room -> Housekeeping: 1:N
• Guest -> ServiceRequest: 1:N
• Employee -> ServiceRequest: 1:N
• Reservation -> Payment: 1:1

Database Concepts Demonstrated

• Relational database modeling
• Entity Relationship Diagrams (ERD)
• Primary keys and foreign keys
• Referential integrity
• Cardinality and relationship modeling
• Domain constraints
• Unique and NOT NULL constraints
• Lookup tables
• Data normalization and scalable schema design

SQL Reference Schema

schema.sql provides a MySQL 8+ reference implementation derived directly from the entities, relationships, data types, and constraints documented in the project report.

The original academic work is a database design project. The SQL file is included as a practical implementation of the documented schema.

Repository Structure

.
├── README.md
├── schema.sql
├── diagrams/
│   ├── main-erd.png
│   └── reservation-billing-subsystem.png
├── source/
│   ├── main-erd.drawio
│   └── reservation-billing-subsystem.drawio
└── docs/
    └── Hotel-Management-System-Report.pdf

Editable Diagrams

The source/ directory contains editable Draw.io / diagrams.net source files for the ERDs.

Documentation

The full academic design report is available here:

docs/Hotel-Management-System-Report.pdf

Tools and Technologies

• Draw.io / diagrams.net
• Relational database modeling
• SQL / MySQL reference schema
• ERD design