PLEASE DRAW 9 ENTITIES IN TOTAL INCLUDING 2 BINARY ASSOCIATIVE ENTITIES AND 1 TERNARY ASSOCIATIVE ENTITY

CIS 365 - Business Database Systems HW3 - FamCation Resort EERD (150 points) FAMCation Resort is a luxury family friendly resort. Being close to many mountains and rivers, FamCation is famous for its guided family sports activities including hiking, mountain biking, horseback riding, and rafting. The recent growth of the resort business has led to the increase of room bookings, activity reservations, and staffing. The resort has been using an antiquated and poorly designed database system which is suffering from its slow performance and frequent downtime. With approval from the management team, the resort technology support team has been given the budget to redesign and upgrade its database system. The technology team interviewed the management team and relevant staff members to gather business requirements and design rules for the database. Use the following business rules to design your EERD. Resort Employee - This database keeps track of employees with different responsibilities, such as managers, technology support, guides, and housekeepers. All employees have an Employee ID (EmpID), name (last name, middle name, first name), gender, contact phone number, hire date, his/her manager's ID, department, and salary. Housekeepers are paid hourly and don't have a salary record. - The management team also wants to record the following additional information for guides and housekeepers. - Guides must be certified every two years to lead the group. In addition to Guide ID (same as EmpID in the Employee entity), each guide has a certified date, actual renewal date, certificate level (level 1, level 2, and level 3), badge color (white, green, and blue) and training hours (80,120,160) associated with each level. In other words, guide's level determines the badge color and training hours. - Each housekeeper is in one of three shifts (shift 1, shift 2, and shift 3). Some housekeepers are only hired as a temp during the peak season. The database tracks the housekeeper's shift and status (i.e., perm or temp). Use HKID (same as EmpID in the Employee entity) as the entity identifier. Condo and Cleaning - Similar to a hotel room, each condo has a unit number (e.g., 101, 102). Because the same unit numbers are used for different building ( A,B, and C), the database table needs building number and unit number together as the composite identifier. In addition, square footage, number of bedrooms, number of bathrooms, and daily rate are also recorded. - Each housekeeper is assigned to clean multiple condos. Each condo can be assigned to different housekeepers in different days, but only one housekeeper is assigned per condo per day. - When the cleaning task is completed at the end of the shift, each housekeeper logs into the system to enter his/her employee ID, today's date, building number, and unit number. The system automatically creates a unique schedule ID for each cleaning record. So the history of cleaning over time is recorded. Guests - The resort keeps the last name, first name, address (street, city, state), and spouse's first name of the guest in the Guest table. Each guest is given a unique VIP membership number (GuestID). Due to its high-quality of service, most of the guests are frequent visitors. Booking - The Condo Booking entity records the booking number (BookID), building number, unit number, guest ID, start date, and end date for each booking entry. According to the booking table, every condo has been booked for at least once. Total booking amount is calculated during check-out based on the number of days stayed multiplied by condo's daily rate. Activities and Reservations - The resort provides many guided activities for members to enjoy. These activities require a reservation to assign a date and a guide for the tour. Each reservation is only for one activity. - Activities are stored in the database with a unique ID (ActID) along with description, number of hours, price per person (PPP), and type (hiking, horseback riding, etc.). All activities in the database have at least one reservation recorded in the DB. - A guest can reserve many different activities or none during the stay. Each reservation has a unique ID (ResID), guest ID, activity ID, guide ID, reserved date, number of people in the party, and the total activity cost. Because the activity fee is charged per person, the activity total cost is PPP x number of people in the party. Draw EERD (entity, identifier, cardinality, etc.) with PK/FK extensions (150 points) 1. Specify primary key for all the entities. Specify foreign key for all relationships. 2. Use square or round brackets around attributes where appropriate. 3. Resolve all M:M relationships. 4. Draw the EERD diagram by using the Drawio ERD shapes with PK/FK extension. Reference Drawio Tutorial document part 2 on Canvas for PK/FK extension diagram. Hints/Requirements: - There are 9 entities in total including 2 binary associative entities and 1 ternary associative entity. - There is 1 supertype/subtype (or parent/child) relationship. - Use portrait layout and put Employee entity on the very top of the page. Housekeeper on the left and Guide on the right