Sydney Hotel Management System

sydney hotel management system
Student number:
[DATE]

Table of Contents
Background..................................................................................................................................................2
Background Information on the project..................................................................................................2
Assumptions........................................................................................................................................2
Purposes and needs of the database.......................................................................................................2
Mission Statement/ System Definition....................................................................................................2
DBMS Selection.......................................................................................................................................3
ERD..............................................................................................................................................................3
HMS erd...................................................................................................................................................3
ELP Database Schema..............................................................................................................................4
Database Schema........................................................................................................................................4
Data from all tables showing first 5(five) records..................................................................................11
Foreign keys Constraints...........................................................................................................................13
X12 SQL Statements..................................................................................................................................14
WHERE clause with examples of various conditions (Like, Between, =, AND / OR, IN, etc.)..................14
ORDER BY clause....................................................................................................................................15
Concatenation to combine values including alias..................................................................................16
Expressions to calculate results.............................................................................................................17
2 or 3 table joins using either new or old syntax...................................................................................17
Subquery...............................................................................................................................................18
Functions to aggregate data (SUM, MIN, MAX, AVERAGE, COUNT) including sub-totals (GROUP BY). .19
Group Report.............................................................................................................................................20
Challenges and Solutions.......................................................................................................................20

Background
Background Information on the project
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.
A reservation in the hotel would include details about the guests, this includes and not limited to,
number of children and adults visiting the hotel, expected arrival and departure times, any special
conditions can be added to notify the hotel in a note attached to the reservation.
On the check in day a room number is attached to the reservation and the timestamp of the date is
recorded, walk in guests are also entered in the system and allocated rooms.
On check out the system calculates the invoice by getting the days of the stay and calculated by the
room charges for each room occupied, any additional service charges are added to the bill and the guest
can pay the invoice through a number of selected methods. An invoice can be paid through multiple
payment methods.
The system also keeps a record of all the employees and those who have access to the system as users,
the different types of rooms and their charges, the different methods of paying for invoices etc
Assumptions
Some service have variable costing while others costs are fixed. Sometimes these charges can be
adjusted by a manager or by someone with sufficient rights within the system. Charges generated by the
system are gotten by getting the product of the service charge by the number of times it was requested.
Purposes and needs of the database.
The purpose of this database is to:
1. 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.
2. 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.
3. It will enable the guest to clear the bills through various payment methods.
4. The system will also keep a record of all employees of the hotel.

Mission Statement/ System Definition
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.
ERD

HMS erd.
HMS ERD
ELP Database Schema
Database Schema
---bookings table---
+----------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------+------+-----+---------+-------+
| bookin_ID | int(11) | NO | PRI | NULL | |
| guest_ID | int(11) | NO | MUL | NULL | |
| room_num | int(11) | NO | | NULL | |

| check_in_date | date | NO | | NULL | |
| check_in_time | time | NO | | NULL | |
| check_out_date | date | NO | | NULL | |
| check_out_time | time | NO | | NULL | |
| num_of_days | int(11) | NO | | NULL | |
| invoice_num | int(11) | NO | | NULL | |
| invoice_date | date | NO | | NULL | |
+----------------+---------+------+-----+---------+-------+
---country table---
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| country_id | int(11) | NO | PRI | NULL | |
| country | varchar(100) | NO | UNI | NULL | |
+------------+--------------+------+-----+---------+-------+
---facilities table---
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| facility_ID | int(11) | NO | PRI | NULL | |
| facility | varchar(100) | NO | | NULL | |
+-------------+--------------+------+-----+---------+-------+
---guests table---
+-----------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------+------+-----+---------+-------+
| guest_ID | int(11) | NO | PRI | NULL | |
| guest_title | varchar(2) | NO | | NULL | |