Designing a Hotel Management System

Relational Database Design and Modeling for Software Engineers

Ask Author

Back to course home

0% completed

Vote For New Content

Designing a Hotel Management System

On this page

Requirements Analysis

Step-by-Step ER Diagram Creation

Step 1: Identify Entities

Primary Entities

Step 2: Detailing Attributes

Step 3: Defining Relationships

Mapping the ER Diagram to a Relational Schema

Guest Table

Guest_Phone_Number Table

Room_Type Table

Room Table

Reservation Table

Reservation_Room Table

Payment Table

Staff Table

Staff_Phone_Number Table

Department Table

Service Table

Guest_Service Table

In this case study, we'll explore the database modeling of a Hotel Management System using Entity-Relationship (ER) design and then map it to a relational database schema. By following this guide, you'll gain a comprehensive understanding of how to model databases for complex systems like hotels.

Requirements Analysis

Understanding the system's requirements is the first and most crucial step. It ensures that the database will support all necessary functionalities.

For our case study, let's outline the key functionalities that the HMS should support:

Guest Management: Recording guest details, preferences, and history.
Room Management: Tracking room availability, types, and maintenance status.
Reservation Management: Handling bookings, cancellations, and modifications.
Billing and Payment: Managing invoices, payments, and refunds.
Staff Management: Keeping staff records, roles, and schedules.
Service Management: Recording additional services like dining, spa, etc.

Step-by-Step ER Diagram Creation

We will build the ER diagram for the Hospital Management System through the following four steps.

Step 1: Identify Entities

Entities represent objects or concepts in the system that have data stored about them.

Primary Entities

Guest
Room
Reservation
Payment
Staff
Service
Room_Type
Department

Entities for the Hotel Management System

Step 2: Detailing Attributes

We'll define attributes for each entity, including primary keys (PK) and foreign keys (FK).

1. Guest

Guest_ID (PK)
First_Name
Last_Name
Date_of_Birth
Gender
Address
Phone_Number
Email
Identification_Type (e.g., Passport, Driver's License)
Identification_Number

2. Room

Room_ID (PK)
Room_Number
Floor
Status (Available, Occupied, Maintenance)
Room_Type_ID (FK)
Department_ID (FK)

3. Room_Type

Room_Type_ID (PK)
Type_Name (Single, Double, Suite)
Description
Price_Per_Night
Max_Occupancy

4. Reservation

Reservation_ID (PK)
Check_In_Date
Check_Out_Date
Number_of_Guests
Reservation_Status (Booked, Checked-In, Checked-Out, Cancelled)
Rate_Applied
Guest_ID (FK)
Room_ID (FK)

6. Payment

Payment_ID (PK)
Payment_Date
Amount
Payment_Method (Credit Card, Cash, etc.)
Reservation_ID (FK)

7. Staff

Staff_ID (PK)
First_Name
Last_Name
Role
Phone_Number
Email
Department_ID (FK)

8. Department

Department_ID (PK)
Department_Name
Description

9. Service

Service_ID (PK)
Service_Name
Description
Price
Service_Date
Guest_ID (FK)

Attributes for the Entities of Hotel Management System

Step 3: Defining Relationships

1. Guest and Reservation

A guest can have multiple reservations.
Each reservation is made by one guest.
Type: One-to-Many (Guest to Reservation)

2. Reservation and Room

A reservation can include multiple rooms.
A room can be part of multiple reservations over time.
Type: Many-to-Many (Reservation to Room)

3. Room and Room_Type

A room belongs to one room type.
A room type includes multiple rooms.
Type: Many-to-One (Room to Room_Type)

4. Guest and Service

A guest can avail themselves of multiple services.
A service can be availed by multiple guests.
Type: Many-to-Many (Guest to Service)

5. Payment and Reservation

A reservation can have multiple payments.
Each payment is for one reservation.
Type: One-to-Many (Reservation to Payment)

6. Room and Department

A room is maintained by one department.
A department manages multiple rooms.
Type: Many-to-One (Room to Department)

7. Staff and Department

A staff member works in one department.
A department has multiple staff members.
Type: Many-to-One (Staff to Department)

Here is the final ER diagram.

ER Diagram for Hotel Management System

Mapping the ER Diagram to a Relational Schema

When converting the ER diagram into a relational schema for our Hotel Management System, we need to carefully handle various components to ensure data integrity and optimal performance. Here are the key considerations:

Entity Tables and Attributes:
- Create a table for each entity identified in the ER diagram, such as Guest, Reservation, Room, Room_Type, Service, Payment, Staff, and Department.
- Define all attributes for each table, specifying appropriate data types and constraints. Also, create seprate table for multi-valued attributes like Phone_Number.
- Set primary keys (PK) for unique identification of records in each table.
Foreign Keys and Relationships:
- Establish foreign keys (FK) to represent relationships between entities, linking tables through common attributes.
- Ensure referential integrity by enforcing foreign key constraints, which maintain consistent and valid references between related tables.
Handling Many-to-Many Relationships:
- Introduce associative (junction) tables to resolve many-to-many relationships into two one-to-many relationships.
  - For example, Reservation_Room connects Reservation and Room.
- Include composite primary keys in associative tables, typically combining the primary keys of the related entities.

Here is the final relational schema diagram.

Relational Schema Diagram for the Hotel Management System

Now, let's translate the ER model into SQL tables.

1. Guest Table

CREATE TABLE Guest (
    Guest_ID INT PRIMARY KEY,
    First_Name VARCHAR(50),
    Last_Name VARCHAR(50),
    Date_of_Birth DATE,
    Gender VARCHAR(10),
    Address VARCHAR(100),
    Email VARCHAR(50),
    Identification_Type VARCHAR(20),
    Identification_Number VARCHAR(50)
);

2. Guest_Phone_Number Table

CREATE TABLE Guest_Phone_Number (
    Guest_ID INT,
    Phone_Number VARCHAR(15),
    PRIMARY KEY (Guest_ID, Phone_Number),
    FOREIGN KEY (Guest_ID) REFERENCES Guest(Guest_ID)
);

3. Room_Type Table

CREATE TABLE Room_Type (
    Room_Type_ID INT PRIMARY KEY,
    Type_Name VARCHAR(20),
    Description TEXT,
    Price_Per_Night DECIMAL(10, 2),
    Max_Occupancy INT
);

4. Room Table

CREATE TABLE Room (
    Room_ID INT PRIMARY KEY,
    Room_Number VARCHAR(10),
    Floor INT,
    Status VARCHAR(20), -- e.g., Available, Occupied, Maintenance
    Room_Type_ID INT,
    Department_ID INT,
    FOREIGN KEY (Room_Type_ID) REFERENCES Room_Type(Room_Type_ID),
    FOREIGN KEY (Department_ID) REFERENCES Department(Department_ID)
);

5. Reservation Table

CREATE TABLE Reservation (
    Reservation_ID INT PRIMARY KEY,
    Check_In_Date DATE,
    Check_Out_Date DATE,
    Number_of_Guests INT,
    Reservation_Status VARCHAR(20), -- e.g., Booked, Checked-In, Checked-Out, Cancelled
    Guest_ID INT,
    FOREIGN KEY (Guest_ID) REFERENCES Guest(Guest_ID)
);

6. Reservation_Room Table

CREATE TABLE Reservation_Room (
    Reservation_ID INT,
    Room_ID INT,
    Rate_Applied DECIMAL(10, 2),
    Notes TEXT,
    PRIMARY KEY (Reservation_ID, Room_ID),
    FOREIGN KEY (Reservation_ID) REFERENCES Reservation(Reservation_ID),
    FOREIGN KEY (Room_ID) REFERENCES Room(Room_ID)
);

7. Payment Table

CREATE TABLE Payment (
    Payment_ID INT PRIMARY KEY,
    Payment_Date DATE,
    Amount DECIMAL(10, 2),
    Payment_Method VARCHAR(20), -- e.g., Credit Card, Cash
    Reservation_ID INT,
    FOREIGN KEY (Reservation_ID) REFERENCES Reservation(Reservation_ID)
);

8. Staff Table

CREATE TABLE Staff (
    Staff_ID INT PRIMARY KEY,
    First_Name VARCHAR(50),
    Last_Name VARCHAR(50),
    Role VARCHAR(50),
    Email VARCHAR(50),
    Department_ID INT,
    FOREIGN KEY (Department_ID) REFERENCES Department(Department_ID)
);

9. Staff_Phone_Number Table

CREATE TABLE Staff_Phone_Number (
    Staff_ID INT,
    Phone_Number VARCHAR(15),
    PRIMARY KEY (Staff_ID, Phone_Number),
    FOREIGN KEY (Staff_ID) REFERENCES Staff(Staff_ID)
);

10. Department Table

CREATE TABLE Department (
    Department_ID INT PRIMARY KEY,
    Department_Name VARCHAR(50),
    Description TEXT
);

11. Service Table

CREATE TABLE Service (
    Service_ID INT PRIMARY KEY,
    Service_Name VARCHAR(50),
    Description TEXT,
    Price DECIMAL(10, 2)
);

12. Guest_Service Table

CREATE TABLE Guest_Service (
    Guest_ID INT,
    Service_ID INT,
    Reservation_ID INT,
    Quantity INT,
    Total_Cost DECIMAL(10, 2),
    PRIMARY KEY (Guest_ID, Service_ID, Reservation_ID),
    FOREIGN KEY (Guest_ID) REFERENCES Guest(Guest_ID),
    FOREIGN KEY (Service_ID) REFERENCES Service(Service_ID),
    FOREIGN KEY (Reservation_ID) REFERENCES Reservation(Reservation_ID)
);

.....

Like the course? Get enrolled and start learning!

On this page

Requirements Analysis

Step-by-Step ER Diagram Creation

Step 1: Identify Entities

Primary Entities

Step 2: Detailing Attributes

Step 3: Defining Relationships

Mapping the ER Diagram to a Relational Schema

Guest Table

Guest_Phone_Number Table

Room_Type Table

Room Table

Reservation Table

Reservation_Room Table

Payment Table

Staff Table

Staff_Phone_Number Table

Department Table

Service Table

Guest_Service Table