Project Report on: “Database Design of Hotel

Management System”

A report submitted for the project requirement of Module: “Database”

Module Tutor: Mr. Raju Karki

Module Code: COM: 312

Bachelor of Business Management

Tribhuvan University

(Nepal Commerce Campus)

Year: May 2021

Submitted by: Submitted to:

Sabina Khadka Raju Karki
Roshna Khanal Lecturer, DBMS
Rina shah Nepal Commerce Campus
Rewati Raman Bhattarai Minbhawan, Kathmandu
Revant Saud
Table of Contents

Chapter 1 : Project Description ................................................................................................................ 6

1.1 Introduction ............................................................................................................................. 6
1.2 Current Business Operations .................................................................................................... 6
1.3 Module of Hotel Management System: ..................................................................................... 7
1.4 Missions and Objective: ........................................................................................................... 9
1.5 Importance: .............................................................................................................................. 9
Chapter 2 : Entity Relationship Model ................................................................................................... 10
2.1 Entity: .................................................................................................................................... 10
2.2 Attributes: .............................................................................................................................. 10
2.3 Relationship: .......................................................................................................................... 11
2.4 Keys: ..................................................................................................................................... 11
2.5 ER Diagram of Hotel Management System: ........................................................................... 12
2.6 Relationship Schema for Hotel Management System: ............................................................. 13
2.7 Physical Schema for Hotel Management System: ................................................................... 13
Chapter 3 : Implementation ................................................................................................................... 14
3.1 Data Definition Language ...................................................................................................... 14
3.1.1 Examples of DDL Commands ............................................................................................ 14
3.1.2 SQL Query ......................................................................................................................... 14
3.1.3 Table Description: .............................................................................................................. 15
3.2 Data Manipulation Language (DML)...................................................................................... 18
Chapter 4 : Normalization...................................................................................................................... 19
4.1 Definition............................................................................................................................... 19
4.2 Types of Normalization .......................................................................................................... 19
Chapter 5 : Conclusion .......................................................................................................................... 24
Annexures ............................................................................................................................................. 25
Bibliography ......................................................................................................................................... 25
Table of Figures

Figure 1: ER Diagram of Hotel Management System ................................................................ 12

Figure 2: Physical Schema for Hotel Management System ........................................................ 13
Figure 3: Hotel Table ................................................................................................................ 15
Figure 4: Booking Table............................................................................................................ 16
Figure 5: Customer Table .......................................................................................................... 16
Figure 6: Location Table ........................................................................................................... 16
Figure 7: Employees Table........................................................................................................ 17
Figure 8: Rooms Table .............................................................................................................. 17
 ABSTRACT

This report is divided into five chapter. The first chapter deals about background issues about
almost everything covered in the project, and the specifies the goals and objectives of the project.

The second chapter deals about the system design of the project. It discusses the entity-
relationship diagram used for modeling the system.

The third chapter deals about the implementation issues and actual implementation. It is further
divided into significant portions, Data Definition Language, Table Description, Data
Manipulation.

The fourth chapter deals with the normalization in database for hotel management system.

The fifth chapter gives our insight about the project.

 Acknowledgement

We would like to express our special thanks of gratitude to our Lecturer Mr. Raju Karki, who
gave us an opportunity to do the database management project on the topic “Hotel Management
System.”

The preparation of the report has helped us in understanding the subject very well and gave us an
exposure to many new concepts. It also helped us to understand the meaning of group work and
team spirit.

Lastly, we would also like to thank everyone including our parents, friends and college
administration who helped us in every step during the preparation of this project.
 Chapter 1 : Project Description
1.1 Introduction

Hotel management is about overseeing every operation of the property. This requires knowledge
of distribution strategy, finance, customer service, staff management, marketing, and more.

Technology in the hotel industry continues to advance at a rapid pace and hotel management
software (HMS) remains essential for hoteliers looking to improve the running of their business.
With software, hotel operators can streamline their administrative processes and improve their
overall hotel management system. The key to reaping the benefits of an effective hotel
management software system is to select the right one for your property. It is critical that you
know exactly what this hotel management technology is, and why it is important for you to
implement it at your hotel.

1.2 Current Business Operations

In the existing system, the hotel uses Excel sheets to manage the hotel operations or manually
noting down the reservations and assign rooms. In this method, we would never be able to keep
track the room status like how many are sold, how many are ready to occupy, and how many of
them are out of service, etc. Moreover, we will face grave issues like overbooking or double
booking that would again lead to guest dissatisfaction and loss of business opportunities.
Followings are the key operations of Hotel Management System on day-to-day business:

1) Manually handled operations:

Most of the hotel’s use excel sheet for hotel operations. They use manually handled
operations like Accounting, reserving, booking cancelations etc.

2) Manual record keeping:

The reservations are manually noted down and the rooms are also assigned manually.

3) Payment:
On spot offline payment can only be done still no online payment is not integrated.

6|P a ge
1.3 Module of Hotel Management System:

We carried out a small discussion with Guest house department. We understand that the
following is the working procedure whenever guest arrives and departs.

1) First, recommendation is issued for advance booking in writing by the concern

Department of the Co.
• Intimation is given to guest house with the information whether Company's guest or
Government guest.
• Also, it is described whether Chargeable or not.
• Biodata for guest like, Name of the guest, Occupation of the guest, expected arrival
Date/Time, Expected departure Date/Time, etc...
• Intimation Slip is made for above.
2) After getting intimation slip from concern dept., the reservation procedure is carried out
of room of that type is available. If not, a single room is available then it is checked
whether it is available in another guest house of Co.
• Room Type (Suite, NC Single, NC Double, Non-NC, etc.)
• Expected arrival Date/Time.
• Expected departure Date/Time.
• Reservation Register is made for above said matter.
3) There is an integration among all guest houses of Co. to facilitate available rooms and
other facilities in another guest houses.
4) If Booking is Canceled, then it is (room) available for next booking. (Reservation
Register is updated.)
5) Occupancy Register is made for this procedure. If occupancy is made from advance
booking, all details (Biodata) is replaced in occupancy register. Otherwise, Biodata of the
guest is written.
Occupancy of rooms are of two types:
• From advance booking
• Spot booking

7|P a ge
6) Lodging facility is given to the guests, irrelevant to occupancy of rooms. Following is
main menu for lodging:
• Meal
• Beverage
• Snacks
• Drinks (Hot / Cold drinks) Food Register, Kot book, Kot Register are kept for
lodging facilities.
7) Whenever guests depart from guest house, following entries are recorded in occupancy
register.
• Departure Date/time
• Paid Up to (Date, until bill is paid up)
8) Store Section: Following procedure are followed in store dept. of guest house:
• Requisition of items to place (Requisition Slip is made)
• Receipt according to the requisition.
• Receipt Register is kept for following information:
➢ Receipt date
➢ Item code
➢ Item rate
➢ Quantity of items
• Store section issues the items as per requirement.
• Issue Register, Consumption Sheet are kept.
➢ Issue date
9) Billing:
• Rate of item
• Quantity issued.
• Item code consists of two types of bill:
➢ In between
➢ At the time of departure Various types of bills are prepared according to
charges of rooms, food items, etc.
➢ Food bill
➢ Room bill
8|P a ge
 ➢ Laundry bill
➢ Telephone bill Bills are received by two ways:
❖ Cash bill: About for Co. guest (chargeable), cash is received for room bill

& food bill. For Co. guest Goals:

1.4 Missions and Objective:

The mission is to facilitate easy management and administration of a hotel with capabilities
to do Booking or reservations of the rooms, Cancellation of the rooms, Cash billing, Room
service, Restaurant service, Restaurant Billing, Total Billing, Travel’s arrangement etc. using
the automated hotel management software. One can Keep detailed records or info on an
unlimited number of customers. The system lets the user Know which all rooms are available
for occupancy at any point of time. This makes the Booking considerably faster. And thus
helps the hotel in better management and reduce a lot of paperwork as well as manpower.

• Guest
▪ To increase customer loyalty.
• Investor
▪ To optimize our profitability.
• Colleague
▪ To increase colleague loyalty.
• Process
▪ To maximize efficiency in our service delivery.
▪ To provide reliable and timely service superior to our competition.
• Sustainability
▪ To be a sustainable company.

1.5 Importance:
▪ Save time on admin tasks.
▪ Develop strong relationships with your guests.
▪ Implement an effective revenue management system.
▪ Manage various operations.
▪ Increase bookings.
▪ Accurate daily reports.
▪ Analyze your customer base.

9|P a ge
 Chapter 2 : Entity Relationship Model
2.1 Entity:
An entity is a business object and can be either tangible (such as a person or an item) or
intangible (such as an event or a reservation). Every entity in a database must have a different
name.

STEP 1:

Entities are: -

1. Hotel
2. Rooms
3. Cost
4. Facilities
5. Location

2.2 Attributes:
Attributes in DBMS are the descriptive properties owned by each entity of an entity set. Types of
attributes in DBMS- Simple attributes, Composite attributes, Single valued attributes, Multi-
valued attributes, Derived attributes, Key attributes. They help to identify the entity. For
instance:

• The attributes of Hotel entity set are Hotel_ID, H_Name, Rating, Review.
• The attributes of Room entity set are Room_ID, R_Type, R_Name.
• The attributes of Booking entity set are B_ID, P_Name.
• The attributes of Customoer entity set are C_ID, C_NAME, C_Address and
C_ContactNo.
• The attributes of Employee entity set are E_ID, E_NAME, E_Address and
E_ContactNo.
• The attributes of Location entity set are L_Code, L_NAME and L_Map.

10 | P a g e
2.3 Relationship:
Database relationships are associations between tables that are created using join statements to
retrieve data. For example, we have defined a relationship require that associate’s hotel with
booking, hotel with location, hotel with employees, hotel with rooms and customer with
booking.

Notes:

Entities, attributes, and relations are different in ER-diagram prepared by different organizations
because of need and requirement of information of organizations. In this project we use only
those entities, attributes, and relation as per our need and assumption of information that is
required for Hotel management system.

2.4 Keys:
A DBMS key is an attribute or set of an attribute which helps you to identify a row (tuple) in a
relation (table). They allow us to find the relation between two tables. Keys help us uniquely
identify a row in a table by a combination of one or more columns in that table. There are
different types of keys which are listed below:

1. Super key
2. Candidate key
3. Primary key
4. Composite key
5. Foreign key
6. Alternate key

11 | P a g e
2.5 ER Diagram of Hotel Management System:

Figure 1: ER Diagram of Hotel Management System

12 | P a g e
2.6 Relationship Schema for Hotel Management System:

• Hotel (Hotel_ID, H_Name, Rating, Review)

• Booking (Booking_ID, B_Name)
• Rooms (Room_ID, R_Type, R_Name)
• Employees (Employee_ID, E_Name, E_ContactNo, E_Address, Hotel_ID)
• Location (L_Code, L_Name, L_Map)
• Customer (C_ID, C_Name, C_ContactNo, C_Address, Booking_ID)

2.7 Physical Schema for Hotel Management System:

Figure 2: Physical Schema for Hotel Management System

13 | P a g e
 Chapter 3 : Implementation
MySQL is an open-source relational database management system. It is based on the structure
query language (SQL), which is used for adding, removing, and modifying information in the
database. We have used data definition language with CREATE commands for creation
relational model of the database. We have created table using CREATE statement to create table
and insertion can be done with INSERT INTO statement with DML statements.

3.1 Data Definition Language

A data definition language (DDL) is a computer language used to create and modify the structure
of database objects in a database. These database objects include views, schemas, tables, indexes,
etc. This term is also known as data description language in some contexts, as it describes the
fields and records in a database table. We have used CREATE statement here to define a new
table with its respective attributes.

3.1.1 Examples of DDL Commands

CREATE: It is used to create the database or its objects (like table, index, function, views, store
procedure and triggers).
DROP: It is used to delete/remove objects (table) from the database.
ALTER: The ALTER command is used to add, change, or remove columns and/or fields in the
table. It can also be used to rename the table.
RENAME: It is used to rename an object existing in the database.

3.1.2 SQL Query

1) CREATE TABLE Hotel (Hotel_ID INT NOT NULL PRIMARY KEY,
H_Name VARCHAR(10),
Rating VARCHAR(10),
Review VARCHAR(20));
2) CREATE TABLE Booking (Booking_ID NOT NULL PRIMARY KEY,
B_Name VARCHAR(20));

3) CREATE TABLE Customer (C_ID INT NOT NULL PRIMARY KEY,

C_Name varchar(50),

14 | P a g e
 C_Address VARCHAR(100),
C_ContactNo INT(10) ,
CONSTRAINT FKBookingID_245 FOREIGN KEY(Booking_ID) REFERENCES Booking
(Booking_ID));
4) CREATE TABLE Location (L_Code INT NOT NULL PRIMARY KEY, L_Name
VARCHAR(20), L_Map INT);
5) CREATE TABLE Employees (E_ID INT NOT NULL PRIMARY KEY,
E_Name varchar(50),
E_Address VARCHAR(100),
E_ContactNo INT(10) ,
CONSTRAINT FKHotelID_254 FOREIGN KEY(Hotel_ID) REFERENCES Hotel (Hotel_ID));
6) CREATE TABLE Rooms (Room_ID INT NOT NULL PRIMARY KEY,
R_Type VARCHAR(20),
R_Name VARCHAR(20));

3.1.3 Table Description:

1. Hotel Table:

Bus table contains of Hotel_ID, H_Name and Rating. Where, Hotel_ID is a PRIMARY KEY,
H_Name is the name of the hotel, Rating is the ranking of hotel which are all the attributes that
defines the Bus.

Figure 3: Hotel Table

2. Booking Table:

Booking table contains of Booking_ID and B_Name. Where, Booking_ID is a PRIMARY

KEY, B_Name is the name of the booking which is the attributes that defines the Booking.

15 | P a g e
 Figure 4: Booking Table

3. Customer Table:

Customer table contains of C_ID, C_Name, C_Address, C_ContactNo, and Booking_ID

Where C_ID is a PRIMARY KEY and Booking_ID is a FOREIGN KEY, C_Name is the
name of the Customer, C_Address is the address of the customer, and C_ContactNo is the
contact number of the customer which are all the attributes that defines the Customer.

Figure 5: Customer Table

4. Location Table:

Location table contains of L_Code, L_Name and L_Map Where, L_Code is a PRIMARY
KEY which represents the code of the location, L_Name is the name of the location, L_Map is
the number that represents the location like ward no. All of these attributes define the Location.

Figure 6: Location Table

16 | P a g e
5. Employees Table:

Employees table contains of E_ID, E_Name, E_Address, E_ContactNo, and Hotel_ID Where
E_ID is a PRIMARY KEY and Hotel_ID is a FOREIGN KEY, E_Name is the name of the
Employee, E_Address is the address of the employee, and E_ContactNo is the contact number of
the employee which are all the attributes that defines the Employees.

Figure 7: Employees Table

6. Rooms Table:

Rooms table contains of Room_ID, R_Type and R_Name Where, Room_ID is a PRIMARY
KEY which represents the code of the location, R_Type is the type of the room and its features,
R_Name is the name that represents the room. All these attributes define the Location.

Figure 8: Rooms Table

17 | P a g e
3.2 Data Manipulation Language (DML)
It deals with the manipulation, and includes most common SQL statements such INSERT,
UPDATE, DELETE etc. and it is used to store, modify, delete, and update data in database.
Some of the examples are as below.

1. INSERT INTO Hotel (Hotel_ID, H_Name, Rating, Review) VALUES (001, “Ktm Marriot”, 4.5,
“Good”);

2. INSERT INTO Booking (Booking_ID, B_Name) VALUES (221, “Platinum”);

3. INSERT INTO Customer (C_ID, C_Name, C_Address, C_ContactNo, Booking_ID) VALUES

(2021, “Selena Gomez”, “Toronto”,1684894584, 221);

4. INSERT INTO Location (L_Code, L_Name, L_Map) VALUES (210, “Park Avenues”, 2254);

5. INSERT INTO Employees (E_ID, E_Name, E_Address, E_ContactNo, Hotel_ID) VALUES

(1202, “Peter Parker”, “Naxal”,9810000000, 001);

6. INSERT INTO Rooms (Room_ID, R_Type, R_Name) VALUES (8848, “Deluxe”, “Everest”);

18 | P a g e
 Chapter 4 : Normalization
4.1 Definition
Normalization is the process of organizing data in a database. This includes creating tables and
establishing relationships between those tables according to rules designed both to protect the
data and to make the database more flexible by eliminating redundancy and inconsistent
dependency.

4.2 Types of Normalization

1. First Normal Form
A relation R is said to be in 1NF if both the following conditions hold:
❖ If R has no multivalued attributes
❖ If R has no composite attributes

C_ID C_Name C_Address C_ContactNo Booking_ID

01 Aswin Naxal 9800000000 2021

9877777777

02 Sagar Bouddha 9822222222 2022

03 Aryan Bhaktapur 9855555555 2025

04 Rohan Lazimpat 9833333333 2041

Conversion into 1NF:

C_ID C_Name C_Address Booking_ID

01 Aswin Naxal 2021

02 Sagar Bouddha 2022

03 Aryan Bhaktapur 2025

04 Rohan Lazimpat 2041

19 | P a g e
C_ID C_ContactNo
01 9800000000

01 9877777777

02 9822222222

03 9855555555

04 9833333333

2. Second Normal Form

A relation R is said to be in 2NF if both the following conditions hold:
❖ Table is in 1NF.
❖ No non-prime attributes of R fully dependents upon the proper subset of any
candidate key of table.

C_ID C_Name C_Address C_ContactNo Booking_ID B_Name

01 Aswin Naxal 9800000000 2021 Platinum

02 Sagar Bouddha 9822222222 2022 Diamond

03 Aryan Bhaktapur 9855555555 2025 Gold

04 Rohan Lazimpat 9833333333 2041 Bronze

Conversion into 2NF:

C_ID B_ID

01 2021

02 2022

03 2025

04 2041

20 | P a g e
C_ID C_Name C_Address

01 Aswin Naxal

02 Sagar Bouddha

03 Aryan Bhaktapur

04 Rohan Lazimpat

3. Third Normal Form

A relation R is said to be in 3NF if both the following conditions hold:
❖ Table must be in 2NF.
❖ No transitive functional dependency between the attributes of given table.

Hotel_ID H_Name Rating Review

005 Marriot 5 Premium

007 Hilton 5 Good

004 Grand 3 Good

009 Solatee 5 Good

Conversion into 3NF:

Hotel_ID H_Name

005 Marriot

007 Hilton

004 Grand

009 Solatee

21 | P a g e
Hotel_ID Rating Review

005 5 Premium

007 5 Good

004 3 Good

009 5 Good

4. Boyce Codd Normal Form (BCNF)

It is an advance version of 3NF. A table complies with BCNF if it is in 3NF and for every
functional dependency X→Y, X should be the super key of the table.

E_ID E_Name E_Address E_ContactNo Hotel_ID

1201 Sulav Jadibuti 9800000100 005

1402 Suchit New-Baneswor 9822222022 007

1503 Aarav Harisiddhi 9855555055 004

1204 Helen Shankhamul 9833333033 009

E_ID E_Address

1201 Jadibuti

1402 New-Baneswor

1503 Harisiddhi

1204 Shankhamul

22 | P a g e
 E_Name Hotel_ID

Sulav 005

Suchit 007

Aarav 004

Helen 009

E_Address E_ContactNo

Jadibuti 9800000100

New-Baneswor 9822222022

Harisiddhi 9855555055

Shankhamul 9833333033

23 | P a g e
 Chapter 5 : Conclusion

In this project we have created one entity-relationships diagram with various entities with its
respective attributes associating with one another through its relationships as Hotel Management
System.

A hotel management system is a set of hotel software solutions that keep operations flowing.
There are accounting packages, customer relationship management (CRM) packages, and a
dizzying array of industry-specific software. Also, we have created the relational schema and
solved it through SQL query by using DDL.

This project follows a basic database system that shows how Hotel Management System
manages its all features and provides facilities to the customer.

24 | P a g e
Annexures
Bibliography
https://www.siteminder.com/r/trends-advice/hotel-management/hotel-management-definitions-
operations-ideas-software/

https://www.tutorialspoint.com/mysql/mysql-introduction.htm

https://www.techopedia.com/definition/1175/data-definition-language-ddl

https://docs.microsoft.com/en-us/office/troubleshoot/access/database-normalization-
description#:~:text=Normalization%20is%20the%20process%20of,eliminating%20redundancy%20and%
20inconsistent%20dependency.

25 | P a g e