its a database, SQL Must be made by SQLite1. 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 workbook file 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 design should: a. Include all entities, relationships (including names) and attributes. b. Identify primary and foreign keys. Assignment Project (week 12) 2015 S2 Last Modified: 23/09/2015 3 | P a g e 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, draw.io, or www.gliffy.com. 3. Using SQLite, you are required to develop a demonstration prototype system that handles hotel bookings and payments. Use SQLite 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 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. 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 a guest that does not exist.