2018.2 ISY1002 - Database Report: Sydney Hotel Management System

This report details the design and implementation of a database for a Sydney hotel, addressing the need for a system to manage day-to-day activities, including bookings and payments. The report outlines the database's purpose, including enabling online reservations, generating bills, and managing employee records. MySQL is selected as the RDBMS due to its scalability and compatibility with languages like PHP and Python. The report includes an ERD, SQL statements demonstrating queries, joins, subqueries, and aggregate functions, and a discussion of challenges and solutions encountered during the project, such as designing a flexible payment system. The report concludes with a reference list.

Database Management System
SYDNEY HOTEL
MANAGEMENT SYSTEM

Background
• A local hotel in Sydney requires a system that can manage
their day to day activities including the payment and
booking systems.
• The system should be able to enter new guests details and
capture information about them this will entail their names,
addresses, mobile-telephone numbers and other relevant
information about the guests. After registration to the
system they should be able to create reservations online
and edit them before the date of checking in.

Purposes and needs of the database.
• The purpose of this database is to:
• Enable guests to do or cancel reservations for the type of rooms
they want, for how long they will stay and for how many people
will stay during the visit.
• The system will then on check out be able to generate a bill of
all the expenses incurred by the guest and those accompanying
them, this also includes the room charges for the entire stay.
• It will enable the guest to clear the bills through various
payment methods.
• The system will also keep a record of all employees of the hotel.

DBMS Selection
• The RDBMS of choice for this project is MySQL, the reason
for using this type of database is because of its scalability
and ability to handle large data sets with ease. Having a
stable system while handling numerous request make
MySQL ideal for handling the hotels large number of
employees and potential guest and their companions.
• The database also adds to its functionality by extending its
use of SQL(Structured Query Language) with other
programming languages like PHP and python for creating
dynamic web applications that can do a myriad of
functions.

HMS ERD

ELP database schema

X12 SQL Statements
SELECT guest_ID, first_name, last_name, state,
country FROM guests WHERE country = '2' OR
Country = '102';
• Result: +----------+------------+-----------+-------+---------+
• | guest_ID | first_name | last_name | state | country |
• +----------+------------+-----------+-------+---------+
• | 1 | Noel | Ritter | NSW | 2 |
• | 2 | William | Jainani | NSW | 2 |
• | 3 | Mark | Franklin | NSW | 2 |
• | 4 | Nancy | Adams | NSW | 2 |
• | 5 | Rick | Burr | NSW | 2 |
• | 6 | U. | Sheppard | NSW | 2 |
• | 7 | Don | Tompkins | NSW | 2 |
• | 8 | Dan | Van Buren | NSW | 2 |
• | 9 | Wendy | Johnson | NSW | 2 |
| 10 | E. | Poirot | IL | 102 |
| 11 | Len | Raymond | VA | 102 |
| 12 | Y. | Kent | MN | 102 |
| 14 | James | Calgary | VA | 102 |
| 15 | Yvonne | Colfax | NC | 102 |
| 16 | Vaughn | Wilson | ID | 102 |
| 17 | Jim | Daton | ID | 102 |
| 18 | Ken | Jefferson | NSW | 2 |
| 19 | Sam | Clinton | NSW | 2 |
| 20 | Larry | Gerry | NSW | 2 |
| 21 | Edwina | Calhoun | NSW | 2 |
| 22 | Julie | Blunt | NSW | 2 |
| 23 | Bill | Hamlin | NSW | 2 |
+----------+------------+-----------+-------+---------+

X12 SQL Statements
• ORDER BY clause
• SELECT first_name,last_name,title,employment_type
FROM staff ORDER BY FIELD(employment_type,'FT','PT');
• Concatenation to combine values including alias
• SELECT CONCAT(guest_title, " ", first_name, " ", last_name)
AS Customer,mobile,`date-entered` AS memberSince FROM guests;
• Expressions to calculate results
• SELECT SUM(payment_amount)as`Booking 1 Payment` ,payment_date FROM payments WHERE
booking_id = 1;

X12 SQL Statements
• 2 or 3 table joins using either new or old syntax
• SELECT guests.last_name, country.country FROM guests INNER JOIN country ON guests.country =
country.country_id;
• Subquery
• SELECT payments.payment_date, payments.payment_method, payments.payment_amount FROM
payments WHERE payments.payment_amount = (SELECT MAX(payments.payment_amount) FROM
payments);
• Functions to aggregate data (SUM, MIN, MAX,
AVERAGE, COUNT) including sub-totals (GROUP BY)
• SELECT COUNT(guests.guest_ID), guests.country FROM guests GROUP BY guests.country ORDER BY
COUNT(guests.guest_ID) DESC;

Group Report
Challenges and
Solutions
• Designing a payment system that accepts
different payment types for different rooms.
• This is due to the fact that most payment
systems accept only one type of payment for
the total amount of the invoice or invoiced
amount.
• This was mitigated by adding another entity to
breakdown the many to many relationship that
exists between the invoice and payment
entities.
•
• Implementing a system that can accept variable
charges for additional cost and fixed charges for
other costs, also any of these cost could be
changed by the manager.

Reference
• Clare Churcher, 2007.
Beginning Database Design
from Novice to Professional.
Apress[Viewed 17 September
2018]. Available from:
http://kva.es/Database.Design.
From.Novice.to.Professional.pd
f
•
• Tutorials Point, 2015. Database
Management Systems.
Tutorials Point (I) Pvt. Ltd.
[Viewed 17 september 2018].
Available from:

THANK YOU