Entity Structure in SQL Format
CREATE TABLE Guest (
Guest_ID INT PRIMARY KEY,
Name VARCHAR(100),
Contact_Info VARCHAR(255),
Nationality VARCHAR(50),
Gender VARCHAR(10),
Reservation_History VARCHAR(255)
);
CREATE TABLE Hotel (
Hotel_ID INT PRIMARY KEY,
Name VARCHAR(100),
Location VARCHAR(255),
Num_Rooms INT,
Rating DECIMAL(3, 1),
Contact_Info VARCHAR(255)
);
CREATE TABLE Reservation (
Reservation_ID INT PRIMARY KEY,
Guest_ID INT,
Hotel_ID INT,
Check_in_Date DATE,
FOREIGN KEY (Guest_ID) REFERENCES Guest(Guest_ID),
FOREIGN KEY (Hotel_ID) REFERENCES Hotel(Hotel_ID)
);
CREATE TABLE Department (
Department_ID INT PRIMARY KEY,
D_Head VARCHAR(100),
D_Role VARCHAR(100),
Staff_Count INT,
Contact_Info VARCHAR(255)
);
CREATE TABLE Staff (
Staff_ID INT PRIMARY KEY,
Name VARCHAR(100),
Age INT,
Contact_Info VARCHAR(255),
Salary DECIMAL(10, 2),
Department_ID INT,
FOREIGN KEY (Department_ID) REFERENCES Department(Department_ID)
);
CREATE TABLE Room (
Room_No INT PRIMARY KEY,
Category VARCHAR(50),
Rent DECIMAL(10, 2),
Status VARCHAR(20)
);
How to Design ER Diagrams for Hotel and Hospitality Management
Hotel and hospitality management enterprises provide various accommodation and leisure facilities to their guests and customers. They have a wide range of establishments including hotels, resorts, motels, inns, hostels, and other facilities.
This article will provide you with a comprehensive guide on designing ER diagrams for hotel and hospitality management by defining relationships and mapping cardinalities between its different internal modules.