Project Specification 1. Part A (20 marks) 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 workbookfile HMS_Data.xlsx to perform the following tasks. 1. 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: • Bookings • Country Lookup • Facility List • Guests • Notes • Payment Methods • Payments • Reservations • Room Facilities • Rooms • Room Type • Service Charge • Service Types • Staff • Users 2. Create an Entity Relationship Diagram (ERD) to help you decide on the relationships. (10 marks) Your entity relation diagram that models your database designshould: a. Include all entities, relationships (including names) and attributes. b. Identify primaryand foreign keys. c. Include cardinality/ multiplicity and show using crow’s feet or UML notation. d. 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 www.gliffy.com(Search for ERDgliffy to get started.). 3. 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. (10 marks) 4. Create relationships between tables and enforce the referential integrity as shown below. Relationships: • Guests can have one or more bookings. • Each room booking can have numerousservice charges associated with it. • Each roomis 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 recordmay 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. 5. 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 aguest that does not exist. • Aguest cannot be deleted for which a reservation or a bookinghas 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 ascharges 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. 6. Save the data in the Excel file provided in a CSV file format and import the data into your tables in MySQL. a. Save a copy of HMS_Data.xlsx as 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. b. Import your normalised data from Excel into your tables. Save your data in Excel in a CSV file 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. 7. 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 (24 marks) Use the Hotel Management System (HMS) database that you created in MySQL to design and execute SQL queries thatanswer the following questions. Number your answers to each question clearly. The answer to each question must be tabulated as shown in the example below and include the SQL statement and also the output that is produced when you execute the statement in your database. The output includes the records that are listed and also the message that appears when you run the SQL statement. For example: Question 1: SQL: SELECT lName, position FROM Staff WHERE salary > 20000; Output: +——-+———-+ | lName | position | +——-+———-+ | Brand | Manager | | White | Manager | +——-+———-+ 2 rows in set (0.03 sec) Do not use screen captures to display the SQL statement or the output. You should right-click on the MySQL Command Prompt window; choose Mark and then press the [Enter] key to Copy and then Paste into your Word document that includes the answers to all questions. Format and indent the clauses in your SQL statements for better readability and understanding as shown in the example above. Statements must be syntactically and semantically correct. Format both the SQL and also the Output in Courier New 10 or 11 point. Each question is 2 marks. 1. List the first name, last name ofguests (join guestfirst and last name with a space in between and use the alias Guest Name for the column heading), and email address (for all guests that have an email address only). Sort the output in ascendingorder bythe guest lastname. 2. List the room number, rate, and number of bedsfor all rooms that have 2 beds, are quiet and have a view. 3. List the total amount owing on service charges for each guest grouped by guest last name and the service description. Use the alias “Total Amount” for the sum of the service charges for the guest. Sort the output inascending order by the guest last name.Use selection criteria to filter the result to show only telephone charges. Note that this query does not need to take payments into account. 4. List the Room number, room rate, number of days stay in hotel, payment amount, and payment amount. Group by payment method (description) for payments made by Visa or MasterCard. 5. List the guest last name, first name, mobile, and email for all guests that do not have amobile phone number recorded in the guests table. Sort the output in ascendingorder by the guest last name, and then first name. 6. List the guestlast name for all guests that have a suburb that has the word ‘hill’ anywhere in the suburb name. Sort the output ascending order by the guest name. 7. List the user first name, surname, and remarks from the notes table for all notes that have a follow up date before today’s date and where the complete field has a value of ‘False’. 8. From the guest table select the guest last name, from the bookings table select the room number and check-in date, and from the service charge table select the quantity, service charge, and service description.Calculate the total charge amount by multiplying the quantityby the service charge amount (display the result of the calculation as Total Charge Amount). Enter criteria to select only records where the service charge ID is equal to 7 which is ‘holding fee’. 9. Count the number of rooms grouped by room type. Use the alias “Count of room type” for the count. 10. List the room numberand facility type for room numbers 18 or 19. Sort in ascendingorder of the room number, and also the facility type. 11. List the guest first and last name for all guests that do not have a room booking entered in the database. 12. List the payment method description and sum of payment amounts for all payments which were made after 1-July-2014. 3. Part C (4 marks) 1. Write a page to the department manager that describes your experience building the database. You can discuss any challenges / difficulties that you experienced or solutions that you found. Comment on any limitations and / or strengths of your database design. Comment on whether your database meets all the system requirements as specified in Part A Question 4. Avoid making excuses or comments that reflect negativity. Include an acknowledgement of all students you have spoken to about the assignment.(4 marks) 4. Part D (2 marks) 1. Deliverables for Parts A, B, & C must be printed as a report with a cover sheet attached. See Administrative Details on page 12 for more details. Your report must include headers and footers that include your name, student number, unit name, assignment name, and page numbers. Your report must be checked for spelling and grammar. Your report must also be formatted so that it is well set out and easy to read. a) A soft copy of your assignment documentation report must be zipped and uploaded to vUWS. b) The SQL that can be used to restore your database should also be uploaded to vUWS. You can create the SQL for your database as follows: Use the mysqldump command to create a text version of the database. Use mysqldump to create SQL file that contains a list of SQL statements which can be used to restore/recreate the original database. The syntax is as follows: $ mysqldump -u [uname] -p[pass] [dbname] > [backupfile.sql] WHERE: [uname] Your database username [pass] The password for your database [dbname] The name of your database [backupfile.sql] The filename for your database backup You do not need to log on to MySQL. For example from the DOS command prompt, the syntax is: e:xamppmysqlbin>mysqldump -u root MyDB> e:MyDB_bak.sql This will create the SQL file that can be used to restore your database.