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.
Contribute Materials
Your contribution can guide someone’s learning journey. Share your
documents today.
sydney hotel management system Student number: [DATE] [Company name] [Company address]
Secure Best Marks with AI Grader
Need help grading? Try our AI Grader for instant feedback on your assignments.
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
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
|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