You are provided an Excel file that contains a partial ERD, suggested table definition, and also some sample data. See HMS_Data.xlsx
Use the Excel workbook file HMS_Data.xlsx to perform the following tasks.
- Your first task is to study the sample data and determine appropriate data definitions. Check that the spreadsheet data has been normalized to third normal form. Study the partial ERD on the first sheet that provides a suggested schema.
The file has various other worksheets including:
- Country Lookup
- Facility List
- Payment Methods
- Room Facilities
- Room Type
- Service Charge
- Service Types
- Create an Entity Relationship Diagram (ERD) to help you decide on the relationships.
Your entity relation diagram that models your database design should:
- Include all entities, relationships (including names) and attributes.
- Identify primaryand foreign
- Include cardinality/ multiplicity and show using crow’s feet or UML notation.
- Include participation (optional / mandatory) symbols if applicable.
The E-R should be created as part of a Microsoft Word document. Hand-drawn diagrams will not be accepted. It is recommended that you complete your ERD using Visio or try(Search for ERD gliffy to get started.).
- Using MySQL, you are required to develop a demonstration prototype system that handles hotel bookings and payments. Use MySQL to create a new database called HMS. Create tables according to your ERD. Follow a standard naming convention for table names and also field names. Avoid using spaces and any special characters in table and also field names. Use underscore_caseor use camelCase to separate parts of a name.
- Create relationships between tables and enforce the referential integrity as shown below.
- Guests can have one or more bookings.
- Each room booking can have numerous service charges associated with it.
- Each room is classified by room type, such as deluxe, suite, or twinshare.
- Each room is also provisioned with various facilities such as TV, spa, etc.
- Each guest record may require one or more notes so as to keep a history of information related to the guest record.
- Notes may be assigned to a particular staff person (or database user) to follow up.
- Each room booking may have one or many payments and each payment is identified as to the payment method.
- The database should include suitable validation and integrity checks as well as appropriate referential integrity checks. That is, AS A MINIMUM, your system should ensure that the following events cannot occur:
Referential Integrity Constraints:
- A booking record cannot be entered for a guest that does not exist.
- A guest cannot be deleted for which a reservation or a booking has been recorded. Similarly a guest cannot be deleted once notes have been entered for the guest record. Likewise, staff (users) cannot be deleted once staff persons have been assigned to follow up a note.
- A booking cannot be deleted once the booking has a service charge or payment details associated with it. Similarly a service type cannot be deleted once the service type has been entered against any service charge detail record for a guest booking.
- Only service types that have been entered into the service types table may be entered as charges for service charge details.
- Rooms cannot be deleted once rooms have been assigned to a guest room booking record.
- Payment methods cannot be deleted once payment methods have been recorded against payments and room bookings that have matching payment details cannot be deleted once payment records have been entered.
- Save the data in the Excel file provided in a CSV file format and import the data into your tables in MySQL.
- Save a copy of xlsxas HMS_ERD.xlsx and for each sheet delete the definition and arrange the data so that the sample data appears immediately below the column headings. Position the data for each table starting from cell A1.
- Import your normalised data from Excel into your tables. Save your data in Excel in a CSVfile format. Select your table in MySQL, click the Operations tab and then import the data from the CSV file. Refer to the document titled Import CSV into MySQL to learn how to save in a CSV format and import into MySQL.
- Add at least two new records into the appropriate tables to include your details as a guest, room booking details of your own, and notes details related to your guest record.
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.
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.
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.
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;
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;
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.
Clare Churcher, 2007. Beginning Database Design from Novice to Professional. Apress[Viewed 17 September 2018]. Available from: https://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: https://www.kciti.edu/wp-content/uploads/2017/07/dbms_tutorial.pdf