Database Design and Analysis: Hotel Management System Project

This project details the development of a digital hotel management system, transitioning from a manual system. It outlines the system's functionality, including user roles (admin and customer), user account management, room management, reservation processes, and payment and cancellation procedures. The project encompasses task 1.1, which presents a scenario for the hotel management system. Task 1.2 includes an Entity-Relationship Diagram (EER). Task 2.1 focuses on logical database design. Task 2.2 provides table creation using Oracle DBMS, including SQL queries for creating tables such as Admin, Customer, Login, Hotel, Room, Reservation, Payment, and Cancellation. Task 2.3 covers the creation of indexes to optimize data retrieval. Task 2.4 involves data population within the tables. Task 2.5 presents SQL queries for retrieving and manipulating data, such as retrieving customer names, counting customers from a specific location, performing inner joins, displaying booking numbers, and retrieving room details. The document concludes with a bibliography of relevant sources.

Running head: Database Design And System Analysis
DATABASE DESIGN AND SYSTEM ANALYSIS
Name of the Student
Name of the University
Author Note

1DATABASE DESIGN AND ANALYSIS
Table of Contents
Task 1.1 2
Task 1.2 4
EER Diagram 4
Task 2.1 5
Logical Database Design 5
Task 2.2 6
Table Creation using Oracle DBMS 6
Room Table: 7
Task 2.3 8
Indexes 8
Task 2.4 9
Data Population 9
Task 2.5 12
SQL Query 12

2DATABASE DESIGN AND ANALYSIS
Task 1.1
A scenario of a Hotel Management System: The Owner of the hotel decided to
develop a digital system for its Hotel instead of the available manual system. The Hotel uses
a manual system for keeping all the necessary details of the hotel, and it is very difficult to
maintain all the records of the hotel, so they wanted to develop a digital system. The new
system mainly performs the following functionality:
 There are mainly two users who are going to use the new system they are admin and
customer. The system maintains the user accounts and stores the relevant pieces of
information such as name, email id, contact number, address and other details of the
user. The system can able to manage the account of both the users.
 Admin can manage all the details of the hotel and the details of the customer, who can
make a room reservation. Only Admin can able to add and modify the rooms of the
hotel and also create a new user account.
 Admin can view, add, delete and modify a new user account. Customer can only
modify their contact details. To perform these activities user of the system have to log
in first to the system, then they can perform such types of task.
 They are managing rooms of the hotel according to their room number, level, room
type and capacity. The hotel has six levels, and every level has more than one rooms.
The cost of the rooms depends on room type and capacity. The hotel has mainly two
types of rooms, AC and Non-Ac. The cost of AC rooms is £105, and the cost of the
non-ac room is £80.
 Both the user of the system can view the details of the rooms, but only admin can add,
delete and modify the details of the room.

3DATABASE DESIGN AND ANALYSIS
 The admin of the system manages the room reservation process, and the process
should include a particular customer, room details, booking date, start and end time.
Customer can also cancel a reservation. Whenever a booking or cancellation process
takes place, a confirmation message sends by the system to the specific customer for
their respective transaction.
 Every customer will charge 30% of the cost of the room as soon as the customer book
a room and rest of the amount have to pay within 24 hours. If the customer failed to
pay the rest amount within 24 hours, then an automatic cancellation of room booking
occurs, and a 10% penalty fee will be deducted from the advance payment. A
notification message is also sent to the customer with all the details of their
transaction.

4DATABASE DESIGN AND ANALYSIS
Task 1.2
EER Diagram

5DATABASE DESIGN AND ANALYSIS
Task 2.1
Logical Database Design

6DATABASE DESIGN AND ANALYSIS
Task 2.2
Table Creation using Oracle DBMS
SQL Query for Admin Table Creation: CREATE TABLE P2543974ADMIN (ID
NUMBER, USERNAME VARCHAR2(4000), NAME VARCHAR2(4000),
EMAIL_ID VARCHAR2(4000),
ADDRESS VARCHAR2(4000), CONTACT_NO NUMBER,
CONSTRAINT "P2543974ADMIN_PK" PRIMARY KEY ("ID", "USERNAME")
ENABLE)
SQL Query for Customer Table Creation:
CREATE TABLE P2543974CUSTOMER (ID NUMBER, USERNAME
VARCHAR2(4000), NAME VARCHAR2(4000), EMAILID VARCHAR2(4000),
ADDRESS VARCHAR2(4000), CONTACT_NUM NUMBER,
CONSTRAINT "P2543974CUSTOMER_PK" PRIMARY KEY ("ID") ENABLE)
SQL Query for Login Table Creation:
CREATE TABLE P2543974LOGIN (LOGIN_ID VARCHAR2(4000), USERNAME
VARCHAR2(4000), PASSWORD VARCHAR2(4000),
CONSTRAINT "P2543974LOGIN_PK" PRIMARY KEY ("USERNAME")
ENABLE)

7DATABASE DESIGN AND ANALYSIS
SQL Query for Hotel Table Creation:
CREATE TABLE P2543974LOGIN (LOGIN_ID VARCHAR2 (4000), USERNAME
VARCHAR2 (4000), PASSWORD VARCHAR2(4000), CONSTRAINT
"P2543974LOGIN_PK" PRIMARY KEY ("USERNAME") ENABLE)
SQL Query for Room Table Creation:
CREATE TABLE P2543974ROOM ("ROOM_NO" NUMBER(*,0) NOT NULL
ENABLE, ROOM_TYPE VARCHAR2(20), ROOM_LEVEL NUMBER(*,0),
ROOM_CAPACITY NUMBER(*,0), PRIMARY KEY ("ROOM_NO") ENABLE)
SQL Query for Reservation Table Creation:
CREATE TABLE P2543974RESERVATION(ID INT NOT NULL,CUSTOMER_ID
INT,ROOM_DETAILS VARCHAR2(255),BOOKING_DATE
DATE,ARRIVAL_TIME TIMESTAMP,DEPARTURE_TIME TIMESTAMP
PRIMARY KEY(ID),
CONSTRAINT FK_P2543974CUSTOMER FOREIGN KEY (CUSTOMER_ID)
REFERENCES P2543974CUSTOMER(ID));
SQL Query for Payment Table Creation:
CREATE TABLE P2543974PAYMENT (PAYMENTID varchar2(20) NOT
NULL,Customer_id int NOT NULL,reservation_id varchar2(255) NOT
NULL,AMOUNT INT,STATUS VARCHAR2(20),PRIMARY KEY
(PAYMENTID),
CONSTRAINT FK_C_P2543974CUSTOMER FOREIGN KEY (CUSTOMER_ID)
REFERENCES P2543974CUSTOMER(ID),

8DATABASE DESIGN AND ANALYSIS
CONSTRAINT fk_C_P2543974RESERVATION FOREIGN KEY
(RESERVATION_ID) REFERENCES P2543974RESERVATION(id) );
SQL Query for Cancellation Table Creation:
CREATE TABLE P2543974CANCELLATION (CAN_ID varchar2(20) NOT NULL,
Customer_id int NOT NULL,reservation_id varchar2(255) NOT NULL,
PAYMENT_DETAIL INT,PRIMARY KEY (CAN_ID));
Task 2.3
Indexes
Index is used to search data more quickly without searching entire row in database. It
is used to increase the speed of fetching data from the database table whenever a
database is accessed.
1. Create index cust_name on P2543974Customer(name);
The above SQL query is for creating an index on name attribute of Customer table
because in this table name attribute is used in almost every query.
2. Create index cust_location on P2543974Customer(Address);
The above query is to create an index on Address attribute of Customer table
because in the customer table there is more than one customer from the same
location, so it helps to increase the speed of data retrieval from the database.

9DATABASE DESIGN AND ANALYSIS
3. Create index indx_roomtype on P2543974Room(Room_type);
Index creation on room_type attribute of P2543974Room because in the
P2543974Room table Room_type attribute is a very important attribute of the
table. It is accessed more than any other attribute of the table.
4. Create index reserve_custId on P2543974Reservation(Customer_Id);
The above SQL query creates an index on Customer_Id of Reservation table
because one customer can book more than one room at any point of time. So it
helps to find the customer details of those customers who reserve more rooms
Task 2.4
Data Population
1. Select * from P2543974Login;

10DATABASE DESIGN AND ANALYSIS
2. Select * from P2543974Admin;
3. Select * from P2543974Customer;
4. Select * from P2543974Hotel;
5. Select * from P2543974Room;

11DATABASE DESIGN AND ANALYSIS
6. Select * from P2543974Reservation;
7. Select * from P2543974Payment;
8. Select * from P2543974Cancellation;

12DATABASE DESIGN AND ANALYSIS
Task 2.5
SQL Query
1. Retrieve customer name from customer table whose lives in London
Query: Select name from customer where Address=’London’
Output:
2. Count the number of Customer who belongs to London
Query: select count(ID) from P2543974Customer where Address='London';
Output:

13DATABASE DESIGN AND ANALYSIS
3. Inner Join on P2543974Customer and P2543974Payment table to retrieve the
details of Customer with all the booking details
Query:
Select
P2543974Customer.ID,P2543974Customer.Name,P2543974Payment.Reservation_ID
,P2543974Payment.Amount from P2543974Customer INNER JOIN
P2543974Payment on P2543974Customer.ID=P2543974Payment.Customer_ID;
Output:
4. Display the number of booking of the customer who reserves a room
Query: select Customer_id,count(id) from P2543974reservation group by
Customer_id;

14DATABASE DESIGN AND ANALYSIS
Output:
5. Display the customer name who cancel their room booking
Query:
Select
P2543974Customer.ID,P2543974Customer.Name,P2543974Cancellation.Can_ID,
P2543974Cancellation.Payment_Details from
P2543974Customer,P2543974Cancellation where
P2543974Customer.ID=P2543974Cancellation.CUSTOMER_ID;
Output:

15DATABASE DESIGN AND ANALYSIS
6. Retrieve all the Ac rooms details from the P2543974Room table
Query: select * from P2543974Room where Room_Type='AC';
Output:

16DATABASE DESIGN AND ANALYSIS
Bibliography
Alnawaj'ha, F.Y., 2016. The Performance Of Inner Join Types in SQL. International Journal
of Advanced Studies in Computers, Science and Engineering, 5(11), p.164.
Cole, R.L., Chen, Y., McKenna, W.J., Perfilov, S., Sinha, A. and Szedenits, E., Paraccel
LLC, 2015. Optimizing database queries using subquery composition. U.S. Patent 9,183,254.
Habimana, J., 2015. Query Optimization Techniques-Tips For Writing Efficient And Faster
SQL Queries. International Journal Of Scientific & Technology Research, 4(10), pp.22-26.
Keet, C.M. and Fillottrani, P.R., 2015. An ontology-driven unifying metamodel of UML
Class Diagrams, EER, and ORM2. Data & Knowledge Engineering, 98, pp.30-53.
Konik, R.P., Mittelstadt, R.A., Muras, B.R. and Theuer, M.W., International Business
Machines Corp, 2016. Managing an index of a table of a database. U.S. Patent 9,460,138.