CIS 365 Business Database Systems

HW2.3 FamCation Resort EERD (110 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:

o There are 10 entities in total.

o Solutions to W2E1 should provide you helpful information.

Read the following 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

managers ID.

The management team also wants to record the following additional information for guides and

housekeepers. A guide cant be a housekeeper, and vice versa.

o 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.

o 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).

Condo and Cleaning

Similar to a hotel room, each condo has a CondoID. 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 spouses 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 guests 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. Thank you!