Please use Gliffy w/ crow's foot notation. Thanks!
CIS 365 - Business Database Systems HW2.3 - FamCation Resort EERD (100 points) . . . . Use Gliffy Crow's Foot notation for your EERD drawing Include all necessary entities, identifier(s), attributes, and relationship cardinalities. Identifiers will be bold and underlined. Use curly, square, or round brackets around attributes where appropriate. Resolve any M:N associative relationship (i.e., create associative entity). Define supertype and subtype entities by indicating their partial/complete overlapping or disjoint relationship, and discriminator. Hint: there are 10 entities in total. Solutions to W2E1 should provide you helpful information. . Read the FamCation Resort business scenario and create an EERD diagram. 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, front desk clerks, technology support, guides, and housekeepers. All employees have a Employee ID (EmpID), name (last name, middle name, first name), contact phone number, hire date, his/her manager's ID. The management team also wants to record the following additional information for guides and housekeepers. A guide can't be a housekeeper, and vice versa. Guides must be certified every two years to lead the group. In addition to employee ID, each guide has a certified date, certificate level (level 1, level 2, and level 3), and renewal date. 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). O Condo and Cleaning Similar to a hotel room, each condo has a Condold. The database table also includes the building . number, unit number, square footage, number of bedrooms, number of bathrooms, and weekly fee. During low season, not all condos are occupied. Each condo is assigned to one housekeeper for cleaning, and each housekeeper is assigned to clean multiple condos. When the cleaning task is completed at the end of the shift, each housekeeper logs into the system to enter his/her employee ID, the date, and the condo IDs. The system automatically creates a unique schedule ID for each condo as the record identifier. Guests . The resort keeps the last name, first name, address (street, city, state), and spouse's first name of the guest who checked in. Each guest is given a unique VIP membership number (GuestID). The resort also keeps the first name, relationship, birth date, and age of the guest's family members for birthday surprises. (Assuming no two family members have the same first name) Due to its high-quality of service, most of the guests are frequent visitors. The resort also allows guests without family members to join the membership. . . Booking Guests pay for a full week, even if they stay only a few days (condo weekly fee). For example, a 17-day stay will be charged for 3 weeks. The Condo Stay Booking records the booking number (BookID), condo ID, guest ID, start date, and end date. Total booking amount is calculated based on the number of days stayed in the condo and the weekly fee. . 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. Activities requiring a reservation 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.). A guest can reserve many different family activities or none during the stay. Each reservation is given a unique ID (ResID) for billing purpose. The reservation also records the guest ID, activity ID, guide ID, reserved date, number of people in the party, and the total cost. Each reservation requires one and only one guide to lead the group. Because the activity fee is charged per person, the total cost for each reservation is PPP x number of people in the party. Billing Invoice: A billing invoice is printed out when the guest is ready to check out. Since invoices are considered output from the FamCation resort database system, we will not include it as an entity in the EERD. Due Date: EOD Monday 1/24/22 Your drawing needs to fit into one page. Submit your EERDs in one .pdf file. Save your file name as HW2.3-Student Name.pdf Submissions must be posted to Canvas to be graded
