Hotel Booking

System Database

Computer Systems Structure

Hotel Booking System Database

Task 1 – Entity Relationship Model

SCENARIO

The structure and implementation of a Database Management System for the purpose of hotel
booking. The database will store customer data, hotel manager data, booking information and room
information.

Figure 1. Entity Relationship Model (ERM) for the Hotel Booking System

P a g e 1 | 14
Hotel Booking System Database

Task 2 – Normalisation
UNF 1NF 2NF 3NF
CustomerID CustomerID CustomerID CustomerID
CustomerName CustomerName CustomerName CustomerName
Address Address Address Address
PhoneNumber PhoneNumber PhoneNumber PhoneNumber
CustomerEmail CustomerEmail CustomerEmail CustomerEmail
CustomerPassword CustomerPassword CustomerPassword CustomerPassword
CCNumber CCNumber CCNumber CCNumber
CCExpiry CCExpiry CCExpiry CCExpiry
CCCVV CCCVV CCCVV CCCVV
CCType CCType CCType CCType
BookingID
StartDate BookingID BookingID BookingID
EndDate StartDate StartDate StartDate
RoomType EndDate EndDate EndDate
BookingStatus RoomType RoomType RequestedRoomType
BookingDate BookingStatus BookingStatus BookingStatus
StaffID BookingDate BookingDate BookingDate
HotelManName StaffID StaffID CustomerID*
HotelManEmail HotelManName HotelManName StaffID*
HotelManPassword HotelManEmail HotelManEmail RoomNo*
RoomNo HotelManPassword HotelManPassword
RoomType RoomNo RoomNo StaffID
RoomType RoomType HotelManName
CustomerID* CustomerID* HotelManEmail
HotelManPassword

RoomNo
RoomType

Unnormalised form(UNF) to first normal form(1NF) involves splitting the single and repeating
groups. A single customer may make many bookings which allows data to repeat.

A table is in second normal form(2NF) if it is in 1NF and all non-key attributes are dependent on part
of the key. Therefore, it is automatically in 2NF due to the tables having a single functionally
dependent attribute.

P a g e 2 | 14
 Hotel Booking System Database

A table is in third normal form(3NF) when the non-key attributes that depend on other non-key(s)
are removed. For instance, Hotel Manager Name (HotelManName) depends on StaffID therefore it
must be separated.

Task 3 – Physical Table Design (including Data

Dictionary)

Part A – Skeleton Tables

Customer (CustomerID, CustomerName, Address, PhoneNumber, CustomerEmail,
CustomerPassword, CCNumber, CCExpiry, CCCVV, CCType)

Booking (BookingID, CustomerID*, StaffID*, RoomNumber*, StartDate, EndDate,

RequestedRoomType, BookingStatus, BookingDate)

Hotel Manager (StaffID, HotelManName, HotelManEmail, HotelManPass)

Room (RoomNumber, RoomType)

Part B – Data Dictionary

Customer
Description:
Customer details
Field Name Data Type Length Inde Nul Default Value Validation rule Description
x l
CustomerID (Primary) varchar(5) 5 PK No Unique number ID for each customer
Name varchar(30) 30 No Name for customer
Address varchar(99) 99 No Address for customer
PhoneNumber varchar(15) 15 Contact number for customer
Email varchar(30) 30 No Must be email format Customer’s registered email address
containing an @ and a ‘.’
Password varchar(30) 30 No Customer’s password
CCNumber integer(16) 16 Yes Customer’s credit card number
CCExpiry varchar(5) 5 Yes Expiry date for customer’s credit card
CCCVV varchar(3) 3 Yes Card verification value for customer’s credit
card
CCType varchar(15) 15 Yes Type of credit card for customer

Indexes

Keyname Type Unique Column Null

PRIMARY BTREE Yes CustomerID No

P a g e 3 | 14
 Hotel Booking System Database

Booking
Description:
Booking details
Field Name Data Type Length Index Null Default Value Validation rule Description
BookingID (Primary) integer PK No Autoincremented unique number ID for every
booking
StartDate date No Cannot be a past date Date the room’s booking begins
EndDate date No Must be a date after the Date the room’s booking ends
start date
RoomType varchar(20) 20 No Single, Double The type of room to be requested
Booking_Status varchar(20) 20 Yes Pending, Active, The status of the booking
Completed, Cancelled
BookingDate date Yes sysdate Date the room is booked
StaffID varchar(8) 8 FK Yes Hotel manager who assigns a room to the
booking
RoomNo integer 3 FK Yes The room assigned to the booking
CustomerID integer 8 FK Yes Customer who creates the booking

Indexes

Keyname Type Unique Column Null

PRIMARY BTREE Yes BookingID No

Hotel Manager
Description:
Hotel manager details
Field Name Data Type Length Index Null Default Value Validation rule Description
StaffID (Primary) varchar(5) 5 PK No Unique number ID for each hotel manager
Name varchar(30) 30 No Name for hotel manager
Email varchar(30) 30 No Must be email format Hotel manager’s registered email address
containing an @ and a ‘.’
Password varchar(15) 15 No Hotel manager’s password

Indexes

Keyname Type Unique Column Null

PRIMARY BTREE Yes StaffID No

Room
Description:
Room details
Field Name Data Type Length Index Null Default Value Validation rule Description
RoomNumber Int(5) 5 PK No Unique number for each room
(Primary)
RoomType varchar(20) 20 No Single, Double The type of room

P a g e 4 | 14
 Hotel Booking System Database

Indexes

Keyname Type Unique Column Null

PRIMARY BTREE Yes RoomNumber No

Task 4 – Query Design

Query Purpose SQL Code

1.List of all TT The hotel may require a list of SELECT c.Customer_ID, c.Name, c.Address, c.Phone_Number,
(Trinidad & customers from a specific c.Email
Tobago) country for the purposes of FROM Customer c
Customers marketing. WHERE c.Address LIKE '%TT'
ORDER BY c.Name asc;

2.Highest The hotel may be interested in SELECT b.Room_Type, COUNT(*) AS TypeCount

booked room its most successfully booked FROM Booking b
type room types. WHERE Booking_Status <> 'Active' OR 'Completed'
GROUP BY b.Room_Type
ORDER BY TypeCount desc;

3.List of The hotel manager may need to SELECT b.Booking_ID, b.Booking_Status, b.Booking_Date,
bookings not filter and view pending b.Start_Date, b.End_Date, b.Room_Type,
assigned to a bookings to assign an c.Customer_ID, c.Name, c.Email, c.Phone_Number
room appropriate room. This will be FROM Booking b, Customer c
sorted by booking date.
WHERE c.Customer_ID = b.Customer_ID
AND b.Room_Number is NULL
GROUP BY b.Booking_Date

4.Available The hotel manager may need to SELECT r.Room_Number, r.Room_Type

rooms on a filter and view available rooms FROM Room r
certain day on a certain date for booking WHERE r.Room_Number NOT IN
assignment and approval (SELECT b.Room_Number
purposes.
FROM Booking b
WHERE '2020-11-27' BETWEEN b.Start_Date AND b.End_Date
AND b.Booking_Status <> 'Cancelled')

5.The customer The hotel may want to know SELECT c.Customer_ID, c.Name, COUNT(*) AS BookingCount,
with the most their top customer as well as h.Staff_ID, h.Name AS HotelManName
bookings and the hotel manager who FROM Customer c, Booking b, HotelManager h
corresponding assigned the booking for the WHERE c.Customer_ID = b.Customer_ID
Hotel Manager purposes of record keeping.
AND h.Staff_ID = b.Staff_ID
that assigned
AND Booking_Status <> 'Cancelled'
the booking
P a g e 5 | 14
Hotel Booking System Database

GROUP BY c.Customer_ID, c.Name

ORDER BY COUNT(*) desc

Task 5 – Implementation of the

Database
Part A – Creating Tables
CREATE TABLE Customer (Customer_ID VARCHAR(5) PRIMARY KEY NOT NULL, Name VARCHAR(30) NOT NULL,
Address VARCHAR(99) NOT NULL, Phone_Number VARCHAR(15) NOT NULL UNIQUE, Email VARCHAR(30) NOT
NULL UNIQUE, Password VARCHAR(30) NOT NULL, Creditcard_Number INT(16), Creditcard_Expiry
VARCHAR(5), Creditcard_CVV VARCHAR(3), Creditcard_Type VARCHAR(15));

CREATE TABLE Hotel Manager (Staff_ID VARCHAR(5) PRIMARY KEY, Name VARCHAR(30) NOT NULL, Email
VARCHAR(30) NOT NULL UNIQUE, Password VARCHAR(30) NOT NULL);

P a g e 6 | 14
Hotel Booking System Database

CREATE TABLE Room (Room_Number INT(5) PRIMARY KEY, Room_Type VARCHAR(20) NOT NULL);

CREATE TABLE Booking (Booking_ID INTEGER PRIMARY KEY AUTOINCREMENT, Start_Date DATE NOT NULL,
End_Date DATE NOT NULL, Room_Type VARCHAR(20) NOT NULL, Booking_Status VARCHAR(20), Booking_Date
DATE DEFAULT sysdate, Customer_ID REFERENCES Customer(Customer_ID), Staff_ID REFERENCES
HotelManager(Staff_ID), Room_Number REFERENCES Room(Room_Number) );

P a g e 7 | 14
Hotel Booking System Database

Part B – Inserting Data

INSERT INTO Customer (Customer_ID, Name, Address, Phone_Number, Email, Password, Creditcard_Number,
Creditcard_Expiry, Creditcard_CVV, Creditcard_Type) VALUES ('C1111', 'Varoun Sankar', '77 Valentinos St,
Night City', '1(868)612-2077', 'vpunk@email.com', 'cyberpunk2077', '2077-2077-2077-2077', '12/77', '277',
'VISA');
INSERT INTO Customer (Customer_ID, Name, Address, Phone_Number, Email, Password, Creditcard_Number,
Creditcard_Expiry, Creditcard_CVV, Creditcard_Type) VALUES ('C2222', 'Kim Kali', '22 Bush St, Fyzabad',
'1(868)610-2000', 'kimki@email.com', 'celestine', '2000-2000-2000-2000', '04/22', '200', 'MASTERCARD');

P a g e 8 | 14
Hotel Booking System Database

INSERT INTO HotelManager (Staff_ID, Name, Email, Password) VALUES (‘H1087’, ‘Leo Messiah’,
‘lmessiah@hoteldb.com’, ‘thegoat10’);

INSERT INTO HotelManager (Staff_ID, Name, Email, Password) VALUES (‘H1986’, ‘Drake Graham’,
‘dgraham@hoteldb.com’, ‘TakeCare11’);

INSERT INTO Room (Room_Number, Room_Type) VALUES ('411', ‘Single');

INSERT INTO Room (Room_Number, Room_Type) VALUES ('320', ‘Double');

P a g e 9 | 14
Hotel Booking System Database

INSERT INTO Booking (Booking_ID, Customer_ID, Staff_ID, Room_Number, Start_Date, End_Date, Room_Type,
Booking_Status, Booking_Date) VALUES (1, 'C1111', 'H1087', 411, '2020-11-25', '2020-11-27', 'Single', 'Active',
'2020-10-27');
INSERT INTO Booking
(Booking_ID, Customer_ID, Staff_ID, Room_Number, Start_Date, End_Date, Room_Type, Booking_Status,
Booking_Date) VALUES (2, 'C2222', 'H1986', 320, '2020-10-20', '2020-10-22', 'Double', 'Cancelled', '2020-10-
01');

Part C – SQL Queries

P a g e 10 | 14
Hotel Booking System Database

In reference to Task 4 – Query Design

1.List of all TT (Trinidad & Tobago) Customers

2.Highest booked room type

3.List of bookings not assigned to a room

P a g e 11 | 14
Hotel Booking System Database

4.Available rooms on a certain day

5.The customer with the most bookings and corresponding Hotel Manager that assigned the
booking

Discussion and Reflection

P a g e 12 | 14
Hotel Booking System Database

Finishing this assignment truly familiarised me with understanding and utilising a database system.

It involved drawing and understanding an Entity Relationship Model, normalising and going into
further detail with its entities and attributes by physical table design. Understanding those tasks
were key in utilising Structured Query Language (SQLite Studio) to create the database.

The task at hand ultimately involved a hotel seeking to provide a database system for customers to
book either single or double rooms and checking their upcoming trips. After a booking is made, hotel
managers would assign an appropriate room to each booking.

For the first task, an Entity Relationship Model was drawn up involving the four main entities which
were Customer, Booking, Hotel Manager and Room. Relevant attributes and tables were assigned
and normalised to remove anomalies and bring the database to a consistent state. The physical table
design was finalised through skeleton tables and data dictionaries which provided detailed
information about the dataset.

The meat of the assignment then dealt with query design and implementation of the database,
bringing the system to life.

There were several hiccups involving errors. One, in relation to inserting foreign keys, made me lose
a good bit of time in deducing but it was a price that needed to be paid for the sake of learning. The
data also seemed prone to human error and might be the main cause of faults within data
structures.

Nevertheless, this assignment was a necessary steppingstone to the world of Computer Systems
Structure.

P a g e 13 | 14