(Solved): CIS 365 – Business Database Systems HW3 – FamCation Resort EERD...
CIS 365 – Business Database Systems
HW3 – FamCation Resort EERD
• Include all necessary entities, attribute identifier(s), attributes, relationship cardinalities, and constraints.
• 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 11 entities in total
Read the FamCation Resort business scenario and create an EERD diagram.
FAMCation Resort is a luxury family friendly resort. This 180-acre property includes 250 condos, 3 restaurants, 2 fitness centers, and 4 conference rooms. Swimming pools, tennis courts, and golf courses are open to members during their stays. 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. These design rules are listed below:
• This database keeps track of full-time employees (such as managers and technology support) and two types of temporary employees: guides and housekeeping. Each employee has a Personnel ID (PersonID), name (last name, middle name, first name), contact phone number, hire date, his/her manager’s ID. A guide can’t be a housekeeper, and vice versa.
• Guides must be certified every two years to lead the group. Each guide has an employee ID (GuideID), certified date, and renewal date. The database also records the guide’s certificate level (level1, level2, and level3),
• Each condo is assigned to a housekeeper for cleaning, and each housekeeper is assigned to clean multiple condos; for example, Sarah is assigned to clean condo 2, 4, 7, 9, and 14. When the housekeeper completes the task, he/she needs to record the employee ID, condo number, and the completion date for payroll purpose.
• Similar to a hotel room, each condo has a unique number (CondoID). The database table also includes the building number, unit number, square footage, the number of bedrooms, the number of bathrooms, and weekly fee.
• During low season, not all condos are occupied.
• The resort keeps the last name, first name, address, 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.
Guest staying at the condos - Booking
• Guests pay for a full week, even if they stay only a few days (condo weekly fee). A one-week stay always starts on a Saturday (check-in) and ends the following Saturday. 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 during the invoicing process.
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.
To expedite the check-out process for a better customer experience, the resort pre-calculates the final billing invoice by adding the fees for the stay and the activities if any for guest confirmation in advance. The billing invoice prints out the invoice number (InvoiceID), invoice date, booking ID, condo ID, guest ID, guest name (first and last), start date, end date, condo fee, activity fee, invoice total, sales tax, and grand total. The resort local sales tax rate is 9%.
Due Date: EOD Thursday 9/9/21
• Draw your EERDs by using Diagrams.net
• Your drawing needs to fit into one page.
• Submit your EERDs in one .pdf file.
• Save your file name as HW3-Student Name.pdf
• Submissions must be posted to Canvas to be graded.
THIS CONTENT IS PROTECTED AND MAY NOT BE SHARED, UPLOADED, SOLD, OR DISTRIBUTED