CIS $407$ Business Database Systems

HW$2\mathrm{.}1$ FamCation Resort EERD and Relations $(90$ 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$),$ contact phone number, gender, 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.

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

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$).$ Use HKID $($same as EmpID in the Employee entity$)$ as the entity identifier.

o Level $1,$ level $2,$ and level $3$ are values stored in the certificate level attribute. They are stored in the

physical databases and don't need to be described in the ERD. Likewise, you only need to specify

attributes, such as shift, status, badge color, and training hours, but not the actual values.

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, the database records the square footage, number of

bedrooms, number of bathrooms, and daily rate.

$$ 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. Use schedule ID as the surrogate identifier.

Guests and Family