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

1. Guest
2. Room
3. Reservation
4. Payment
5. Staff
6. Service
7. Room_Type
8. Department

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)

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.

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.

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)
);