Aim: To give you practical experience in with database modelling, normalization and writing SQL statements.
Background Information
A local Sydney hotel requires a database system to manage their bookings and payments.
When a guest rings up or comes into the hotel to book a room for the first time they are asked to provide their name, address, and telephone number.
Guests may make reservations for a room which includes the date and time, number of adults, number of children, expected arrival date & time, expected departure date & time, and notes. Reservations may be confirmed and / or cancelled.
When the guest checks in to the hotel reception enter a record of the room number, and check in date & time. Guests may also check in without a reservation.
When the guest checks out then the checkout date & time, and number of days are entered into the database. The number of days is calculated but may be overwritten by a manager and the guest is charged for the accommodation plus additional service charges including restaurant, dry cleaning, valet parking, room service, etc.
Once the checkout details have been completed then the guest is invoiced for the accommodation and also additional service charges. Payments are recorded for each room booking. Payments are expected to be in full.
However, the system allows multiple payments to be recorded against each room booking.
The database system also needs to keep a record of database users, staff, rooms, room type, facilities, payments, payment types, and guest notes.
Assumptions
Some service charges have a fixed cost. However, the charge amount may be adjusted up or down when charges are entered against the guest booking record. Total charges are calculated by the system by multiplying the charge amount (price) by the quantity.
System Requirements
The system is a prototype system and as such is not a full production version. You will be required to enter a representative sample data into your tables in order to test the design and operation of your database. You are required to import the sample data provided into your tables and you are required to enter at least two new records of your own in some of the tables. Project Specification Part A (20 marks):
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_case or use camelCase to separate parts of a name.
Create relationships between tables and enforce the referential integrity as shown below.
Relationships:
•Guestscanoneormorebookings.
•Eachroombookingcannumerousservicechargesassociatedwithit.
•Eachroomisclassifiedroomsuchassuite,ortwinshare.
•EachroomisalsoprvisionedwithariousfacilitiessuchasT, spa,etc.
•Eachguestrecordrequireoneormorenotessoastoahistoryofinformationrelatedtotheguestrecord.
•Notesbeassignedtoaparticularstaffperson(ordatabaseuser)tofollow
•Eachroombookingoneorandeachisidentifiedastothemethod.
5.Thedatabase should includesuitable and checks aswellasappropriatereferentialchecks.Thatis,AMINIMUM,systemshould ensurethatthefollowing cannotoccur:
Referential Integrity Constraints:
•Abookingrecordcannotbeenteredforaguestthatdoesnot exist.
•Aguestcannotbedeletedforwhichaorabookinghasbeenrecorded.Similarlyaguestcannotbedeletedonce notesbeenenteredfortheguestrecord.staff(users)cannotbedeletedoncestaffpersonsbeenassignedtofollowupanote.
•Abookingcannotbedeletedoncethebookinghasaservice chargeordetailsassociatedwithit.Similarlyaservicecannotbedeletedoncetheservicehasbeenenteredagainstservicechargedetailrecordforaguestbooking.
•Onlyservicethatbeenenteredintotheservicetablebeenteredaschargesforservicechargedetails.
•cannotbedeletedonceroomsbeenassignedtoaguestroombookingrecord.
•cannotbedeletedoncebeenrecordedagainstandroomthatmatchingdetailscannotbedeletedoncebeenentered.
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.
2. Part B [18 Marks] Use the Hotel Management System (HMS) database that you created in MySQL to design and execute SQL queries that answer the following questions.
Number your answers to each question clearly. The answer to each question must be tabu

GET THIS PAPER OR A SIMILAR PAPER WITH SUBMIT YOUR HOMEWORKS AND GET AN AMAZING DISCOUNT

“Are you looking for this answer? We can Help click Order Now”