Sydney Hotel Management System

This project is about developing a hotel management system for a local hotel in Sydney. The system should be able to manage day to day activities including payment and booking systems. The database schema includes tables for bookings, guests, payments, reservations, rooms, and more. The ERD and ELP database schema are also provided.

sydney hotel management system
Student number:
[DATE]
[Company name]
[Company address]

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 | |

| first_name | varchar(100) | NO | | NULL | |
| last_name | varchar(100) | NO | | NULL | |
| address | varchar(16) | NO | | NULL | |
| suburb | varchar(16) | NO | | NULL | |
| state | varchar(16) | NO | | NULL | |
| post_code | int(5) | NO | | NULL | |
| country | int(11) | NO | MUL | NULL | |
| mobile | int(15) | NO | | NULL | |
| phone_other | int(15) | NO | | NULL | |
| email | varchar(50) | NO | | NULL | |
| licence_number | int(15) | NO | | NULL | |
| passport_number | int(15) | NO | | NULL | |
| note | varchar(1000) | NO | | NULL | |
| date-entered | date | NO | | NULL | |
+-----------------+---------------+------+-----+---------+-------+
---notes table---
+----------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------+------+-----+---------+-------+
| note_id | int(11) | NO | PRI | NULL | |
| guest_ID | int(11) | NO | MUL | NULL | |
| entry_date | date | NO | | NULL | |
| entry_time | time | NO | | NULL | |
| initials | int(2) | NO | | NULL | |
| assigned_to | int(11) | NO | MUL | NULL | |
| remarks | varchar(1000) | NO | | NULL | |
| follow_up_date | date | NO | | NULL | |
| complete | tinyint(1) | NO | | NULL | |
+----------------+---------------+------+-----+---------+-------+

---payment method table---
+-------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+----------------+
| payment_method_ID | int(11) | NO | PRI | NULL | auto_increment |
| payment_method | varchar(100) | YES | | NULL | |
| credit_card | tinyint(1) | YES | | NULL | |
+-------------------+--------------+------+-----+---------+----------------+
---payments table---
+--------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| payment_id | int(11) | NO | PRI | NULL | |
| booking_id | int(11) | NO | MUL | NULL | |
| payment_amount | float | NO | | NULL | |
| payment_date | date | NO | | NULL | |
| payment_method | int(11) | NO | MUL | NULL | |
| check_number | int(11) | NO | | NULL | |
| credit_card_type | varchar(100) | NO | | NULL | |
| credit_card_number | int(11) | NO | | NULL | |
| credit_card_name | varchar(100) | NO | | NULL | |
| expiry_date | date | NO | | NULL | |
| payment_terms | varchar(100) | NO | | NULL | |
| notes | varchar(100) | NO | | NULL | |
+--------------------+--------------+------+-----+---------+-------+
---reservation table---
+-------------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------------+--------------+------+-----+---------+-------+
| booking_ID | int(11) | NO | PRI | NULL | |
| guest_ID | int(11) | NO | MUL | NULL | |

| booking_date | date | NO | | NULL | |
| booking_time | time | NO | | NULL | |
| num_of_adults | int(11) | NO | | NULL | |
| num_of_children | int(11) | NO | | NULL | |
| expected_arrival_date | date | NO | | NULL | |
| expected_arrival_time | time | NO | | NULL | |
| expected_departure_date | date | NO | | NULL | |
| expected_departure_time | time | NO | | NULL | |
| VIP | tinyint(1) | NO | | NULL | |
| notes | varchar(400) | NO | | NULL | |
| room_num | int(11) | NO | MUL | NULL | |
| confirmed | tinyint(1) | NO | | NULL | |
| cancelled | tinyint(1) | NO | | NULL | |
+-------------------------+--------------+------+-----+---------+-------+
---room table---
+-------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------+------+-----+---------+-------+
| room_ID | int(11) | NO | PRI | NULL | |
| room_num | int(11) | NO | | NULL | |
| room_rate | float | NO | | NULL | |
| num_of_beds | int(11) | NO | | NULL | |
| view | tinyint(1) | NO | | NULL | |
| quiet | tinyint(1) | NO | | NULL | |
| suite | tinyint(1) | NO | | NULL | |
| deluxe | tinyint(1) | NO | | NULL | |
| room_type | int(11) | NO | MUL | NULL | |
+-------------+------------+------+-----+---------+-------+
---room facilities table---

+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| room_ID | int(11) | NO | PRI | NULL | |
| facility_ID | int(11) | NO | MUL | NULL | |
| notes | varchar(400) | NO | | NULL | |
+-------------+--------------+------+-----+---------+-------+
---- room type table---
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| room_type_iD | int(11) | NO | PRI | NULL | |
| room_type | varchar(100) | NO | | NULL | |
+--------------+--------------+------+-----+---------+-------+
---service charges table---
+---------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+--------------+------+-----+---------+-------+
| booking_ID | int(11) | NO | PRI | NULL | |
| invoice_num | int(11) | NO | | NULL | |
| service_ID | int(11) | NO | MUL | NULL | |
| service_description | varchar(200) | NO | | NULL | |
| service_date | date | NO | | NULL | |
| service_charge | float | NO | | NULL | |
| quantity | int(11) | NO | | NULL | |
+---------------------+--------------+------+-----+---------+-------+
---service table---

+---------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+--------------+------+-----+---------+-------+
| service_ID | int(11) | NO | PRI | NULL | |
| service_code | varchar(5) | NO | | NULL | |
| service_description | varchar(200) | NO | | NULL | |
+---------------------+--------------+------+-----+---------+-------+
---staff table---
+-----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| staff_ID | int(11) | NO | PRI | NULL | |
| first_name | varchar(100) | NO | | NULL | |
| last_name | varchar(100) | NO | | NULL | |
| title | varchar(20) | NO | | NULL | |
| work_phone | int(11) | NO | | NULL | |
| employment_type | varchar(2) | NO | | NULL | |
+-----------------+--------------+------+-----+---------+-------+
--- users table---
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| user_ID | int(11) | NO | PRI | NULL | |
| staff_id | int(11) | NO | | NULL | |
| initials | varchar(2) | NO | | NULL | |
| first_name | varchar(100) | NO | | NULL | |
| surname | varchar(100) | NO | | NULL | |
| Login_name | varchar(100) | NO | UNI | NULL | |

| password | varchar(100) | NO | | NULL | |
+------------+--------------+------+-----+---------+-------+
Data from all tables showing first 5(five) records
---bookings table---
+-----------+----------+----------+---------------+---------------+----------------+----------------+-------------+-------------
+--------------+
| bookin_ID | guest_ID | room_num | check_in_date | check_in_time | check_out_date | check_out_time | num_of_days | invoice_num |
invoice_date |
+-----------+----------+----------+---------------+---------------+----------------+----------------+-------------+-------------
+--------------+
| 1 | 1 | 4 | 0000-00-00 | 01:15:00 | 0000-00-00 | 09:30:00 | 3 | 134521 |
0000-00-00 |
| 2 | 2 | 12 | 0000-00-00 | 10:00:00 | 0000-00-00 | 10:00:00 | 1 | 134522 |
0000-00-00 |
| 3 | 3 | 20 | 0000-00-00 | 15:15:00 | 0000-00-00 | 07:45:00 | 3 | 134523 |
0000-00-00 |
| 4 | 4 | 18 | 0000-00-00 | 08:00:00 | 0000-00-00 | 08:15:00 | 5 | 134524 |
0000-00-00 |
| 5 | 18 | 23 | 0000-00-00 | 06:30:00 | 0000-00-00 | 08:15:00 | 1 | 134525 |
0000-00-00 |
+-----------+----------+----------+---------------+---------------+----------------+----------------+-------------+-------------
+--------------+
---country table---
+------------+------------+
| country_id | country |
+------------+------------+
| 1 | Algeria |
| 2 | Australia |
| 3 | Austria |
| 4 | Bangladesh |
| 5 | Belgium |
+------------+------------+
---facilities table---
+-------------+------------------------+
| facility_ID | facility |
+-------------+------------------------+
| Facility

|una
|
| Spa bath
| 4 | Wi-Fi Internet Access
+-------------+------------------------+
---guests table---
+----------+-------------+------------+-------------+------------------+------------------+-------+-----------+---------
+-----------+-------------+--------------------------+----------------+-----------------+------+--------------+
| guest_ID | guest_title | first_name | last_name | address | suburb | state | post_code | country | mobile
| phone_other | email | licence_number | passport_number | note | date-entered |
+----------+-------------+------------+-------------+------------------+------------------+-------+-----------+---------
+-----------+-------------+--------------------------+----------------+-----------------+------+--------------+
| 0 | GT | GFirstName | G Last NAME | Address | Suburb | State | 0 | 0 |
0 | 0 | Email | 0 | 0 | Note | 0000-00-00 |
| 1 | Mr | Noel | Ritter | 902 91A BRIDGE S | WESTMEAD | NSW | 0 | 2 |
402147779 | 0 | [e-mail] | 12142907 | 0 | | 0000-00-00 |
| 2 | Mr | William | Jainani | 130 HAMRUN CIR | ROOTY HILL | NSW | 2766 | 2 |
414307905 | 96252301 | [e-mail] | 12345 | 0 | | 0000-00-00 |
| 3 | Mr | Mark | Franklin | 5 FRANLEE RD | DURAL | NSW | 0 | 2 |
403551056 | 0 | [e-mail] | 7878 | 0 | | 0000-00-00 |
| 4 | Mi | Nancy | Adams | 18 WESTMORE DR | WEST PENNANT HIL | NSW | 2125 | 2 |
404892387 | 98732383 | | 16745 | 0 | | 0000-00-00 |
+----------+-------------+------------+-------------+------------------+------------------+-------+-----------+---------
+-----------+-------------+--------------------------+----------------+-----------------+------+--------------+
---notes table---
+---------+----------+------------+------------+----------+-------------+----------------------------------------+----------------
+----------+
| note_id | guest_ID | entry_date | entry_time | initials | assigned_to | remarks | follow_up_date
| complete |
+---------+----------+------------+------------+----------+-------------+----------------------------------------+----------------
+----------+
| 1 | 1 | 0000-00-00 | 02:58:56 | 0 | 3 | Restaurant reservations for 2 @ 6 pm. | 0000-00-00
| 0 |
| 2 | 2 | 0000-00-00 | 03:06:56 | 0 | 3 | Give room 12 if available (has a view) | 0000-00-00
| 0 |
| 3 | 3 | 0000-00-00 | 03:14:56 | 0 | 2 | " R/S | 0000-00-00
| 0 |
| 0 | 0 | 0000-00-00 | 00:00:00 | 0 | 0 | | 0000-00-00
| 0 |
| 4 | 4 | 0000-00-00 | 03:22:56 | 0 | 3 | Would like a suite (1 bed) | 0000-00-00
| 0 |
+---------+----------+------------+------------+----------+-------------+----------------------------------------+----------------
+----------+

---payment method table---
+-------------------+----------------+-------------+
| payment_method_ID | payment_method | credit_card |
+-------------------+----------------+-------------+
| 1 | Cash | 0 |
| 2 | Amex | 0 |
| 3 | VISA | 0 |
| 4 | Cheque | 0 |
| 5 | Mastercard | 0 |
+-------------------+----------------+-------------+
---payments table---
+------------+------------+----------------+--------------+----------------+--------------+------------------+--------------------
+------------------+-------------+---------------+--------------------+
| payment_id | booking_id | payment_amount | payment_date | payment_method | check_number | credit_card_type | credit_card_number
| credit_card_name | expiry_date | payment_terms | notes |
+------------+------------+----------------+--------------+----------------+--------------+------------------+--------------------
+------------------+-------------+---------------+--------------------+
| 1 | 1 | 0 | 0000-00-00 | 1 | 0 | | 0
| | 0000-00-00 | | |
| 2 | 2 | 0 | 0000-00-00 | 2 | 0 | | 0
| | 0000-00-00 | | Add 2.5% surcharge |
| 3 | 3 | 0 | 0000-00-00 | 4 | 0 | | 0
| | 0000-00-00 | | |
| 4 | 4 | 0 | 0000-00-00 | 2 | 0 | | 0
| | 0000-00-00 | | |
| 5 | 6 | 0 | 0000-00-00 | 5 | 0 | | 0
| | 0000-00-00 | | |
+------------+------------+----------------+--------------+----------------+--------------+------------------+--------------------
+------------------+-------------+---------------+--------------------+
Foreign keys Constraints
+--------------------+-------------------+------------------------+--------------+-----------------+-----------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE |
+--------------------+-------------------+------------------------+--------------+-----------------+-----------------+
| def | hms | bookings_ibfk_1 | hms | bookings | FOREIGN KEY |
| def | hms | bookings_ibfk_2 | hms | bookings | FOREIGN KEY |
| def | hms | guests_ibfk_1 | hms | guests | FOREIGN KEY |
| def | hms | notes_ibfk_1 | hms | notes | FOREIGN KEY |
| def | hms | notes_ibfk_2 | hms | notes | FOREIGN KEY |
| def | hms | payments_ibfk_1 | hms | payments | FOREIGN KEY |
| def | hms | payments_ibfk_2 | hms | payments | FOREIGN KEY |

| def | hms | reservation_ibfk_1 | hms | reservation | FOREIGN KEY |
| def | hms | reservation_ibfk_2 | hms | reservation | FOREIGN KEY |
| def | hms | room_ibfk_1 | hms | room | FOREIGN KEY |
| def | hms | service charge_ibfk_1 | hms | service charge | FOREIGN KEY |
| def | hms | service charges_ibfk_1 | hms | service charges | FOREIGN KEY |
| def | hms | service charges_ibfk_2 | hms | service charges | FOREIGN KEY |
| def | hms | users_ibfk_1 | hms | users | FOREIGN KEY |
+--------------------+-------------------+------------------------+--------------+-----------------+-----------------+
X12 SQL Statements
WHERE clause with examples of various conditions (Like, Between, =, AND /
OR, IN, etc.)
+----------+------------+-----------+-------+---------+
SELECT guest_ID, first_name, last_name, state, country
FROM guests
WHERE country = '2' OR Country = '102';
+----------+------------+-----------+-------+---------+
| 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 |
+----------+------------+-----------+-------+---------+
ORDER BY clause
+------------+------------+--------------+-----------------+
SELECT
first_name,last_name,title,employment_type
FROM
staff
ORDER BY FIELD(employment_type,'FT','PT');
+------------+------------+--------------+-----------------+
| first_name | last_name | title | employment_type |
+------------+------------+--------------+-----------------+
|Saleh | Cleaning | C
| Bob | Parker | Restaurant | FT |
| Andrew | Dwight | Manager | FT |

| Gloria | Cartwright | Housekeeping | FT |
| Mary | Sharp | Reception | FT |
| Paul | Velasco | Driver | FT |
| David | Singer | Maintenance | PT |
| Tina | Parker | Kitchen | PT |
| Tom | Fenton | Functions | PT |
| Brad | White | Concierge | PT |
+------------+------------+--------------+-----------------+
Concatenation to combine values including alias
+---------------------------+-----------+-------------+
SELECT CONCAT(guest_title, " ", first_name, " ", last_name) AS Customer,mobile,`date-entered` AS
memberSince
FROM guests;
+---------------------------+-----------+-------------+
| Customer | mobile | memberSince |
+---------------------------+-----------+-------------+
| GT GFirstName G Last NAME | 0 | 0000-00-00 |
| Mr Noel Ritter | 402147779 | 0000-00-00 |
| Mr William Jainani | 414307905 | 0000-00-00 |
| Mr Mark Franklin | 403551056 | 0000-00-00 |
| Mi Nancy Adams | 404892387 | 0000-00-00 |
| Mr Rick Burr | 416162738 | 0000-00-00 |
| Mr U. Sheppard | 423820979 | 0000-00-00 |
| Mr Don Tompkins | 423021761 | 0000-00-00 |
| Mr Dan Van Buren | 433761402 | 0000-00-00 |
| Mi Wendy Johnson | 411479993 | 0000-00-00 |
| Mr E. Poirot | 0 | 0000-00-00 |
| Dr Len Raymond | 0 | 0000-00-00 |
| Mr Y. Kent | 0 | 0000-00-00 |
| Mr James Calgary | 0 | 0000-00-00 |
| Mr Yvonne Colfax | 0 | 0000-00-00 |
| Mr Vaughn Wilson | 0 | 0000-00-00 |

| Mr Jim Daton | 0 | 0000-00-00 |
| Mr Ken Jefferson | 416 | 0000-00-00 |
| Mr Sam Clinton | 2 | 0000-00-00 |
| Mr Larry Gerry | 413 | 0000-00-00 |
| Mr Edwina Calhoun | 2 | 0000-00-00 |
| Mr Julie Blunt | 415 | 0000-00-00 |
| Mr Bill Hamlin | 418 | 0000-00-00 |
| Steve Marsh | 0 | 0000-00-00 |
+---------------------------+-----------+-------------+
Expressions to calculate results
+---------------------------+-----------+-------------+
SELECT SUM(payment_amount)as`Booking 1 Payment` ,payment_date FROM payments WHERE booking_id = 1;
+-------------------+--------------+
| Booking 1 Payment | payment_date |
+-------------------+--------------+
| 96096 | 2018-09-02 |
+-------------------+--------------+
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;
+-----------+-----------+
| last_name | country |
+-----------+-----------+
| Ritter | Australia |
| Jainani | Australia |
| Franklin | Australia |

| Adams | Australia |
| Burr | Australia |
| Sheppard | Australia |
| Tompkins | Australia |
| Van Buren | Australia |
| Johnson | Australia |
| Poirot | USA |
| Raymond | USA |
| Kent | USA |
| Calgary | USA |
| Colfax | USA |
| Wilson | USA |
| Daton | USA |
| Jefferson | Australia |
| Clinton | Australia |
| Gerry | Australia |
| Calhoun | Australia |
| Blunt | Australia |
| Hamlin | Australia |
+-----------+-----------+
Subquery
SELECT
payments.payment_date, payments.payment_method, payments.payment_amount
FROM payments
WHERE
payments.payment_amount = (SELECT
MAX(payments.payment_amount)
FROM
payments);
+--------------+----------------+----------------+

| payment_date | payment_method | payment_amount |
+--------------+----------------+----------------+
| 2018-09-19 | 2 | 87865 |
+--------------+----------------+----------------+
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;
+------------------------+---------+
| COUNT(guests.guest_ID) | country |
+------------------------+---------+
| 15 | 2 |
| 7 | 102 |
| 2 | 0 |
+------------------------+---------+

Group Report
Challenges and Solutions
1. 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.
2. 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.pdf
Tutorials Point, 2015. Database Management Systems. Tutorials Point (I) Pvt. Ltd.[Viewed 17 september
2018]. Available from: http://www.kciti.edu/wp-content/uploads/2017/07/dbms_tutorial.pdf