ARNOLDS CALIFORNIA SPA

REQUIRED: Using the narrative and the attributes below, prepare: 1) a UML class diagram (REA diagram with classes, associations, and multiplicities) and 2) a list of corresponding relational tables (list the tables with all their attributes, identify the primary keys [with PK or underline], and indicate the foreign keys [with an asterisk or FK]). The table definitions should only use the attributes listed at the end of this case (remember that there should be no entities without attributes) and all attributes should be used in one and only one table. You may abbreviate attribute names if the abbreviation is clear. List the tables in the following order: Resources, Events, Agents, Types, and Linking tables.

You should apply the standard REA patterns wherever the narrative does not clearly specify otherwise, but avoid adding extra relationships.

Background: When Arnold retired from politics after the state budget crises of 2008, 2009, and 2010, he found less demand for aging action stars than he anticipated. So, to maintain his standard of living, he decided to open a unique health spa serving movie stars and other wealthy clients. Taking advantage of the historically low mortgage rates, he acquired an impressive estate in Rancho Palos Verdes overlooking the Pacific Ocean and converted it into an expensive spa. After some extensive market research and a number of personal visits to other spas (it was a tough job but someone had to do it), he opened Arnolds California Spa offering a variety of activities (such as golf, Pilates, yoga, massage, mud baths, aerobics, weight lifting, dodge ball, etc.) so clients have the option of either 1) getting physically fit with the latest in exercise technology or 2) just relaxing in luxury. His spa quickly became extremely successful and now he plans to expand to other cities. Arnold now realizes that his business needs an accounting system, and he has asked you to analyze his requirements and help him design a system that can grow along with his business.

Arnolds business requirements: During your interview with Arnold, he describes his business requirements for his sales (spa sessions) and purchases processes as follows:

Spa Packages. We offer our clients a selection of spa packages that changes according to the time of year and customer demand. For example, we might offer the Spring Getaway Package in the spring, and the Summer Escape Package in summer. We always offer our standards, including the Relaxing Escape from the Economy Package, the Pure Indulgence Package, the Sports Package, and the Steroid-Free Bodybuilding Package. Lately, we also include the Alternate Facts Package. Each package consists of several activities, such as weight lifting, aerobics, Pilates, mountain biking, yoga, tennis, golf, various skin care therapies, hydrotherapies, body therapies, and massage.

The price of each spa package depends on the specific activities included in the package. For example, the Steroid-Free Bodybuilding Package includes weight lifting, yoga, dodge ball, and scientifically designed ice baths to restore muscles after those hard workouts. Each activity can appear multiple times in each spa package, for example one package can include two rounds of golf. Of course, each activity can be included in multiple spa packages.

We set the price of each spa package by first determining what we want to charge for each of the activities depending on the time of year, the products we expect to use in the activity, and expected popularity. We then add up the charges for each activity to form the overall price for the spa package; however, the clients only see one single price for the whole package. To monitor profitability, we track year-to-date sales information for each activity included in any spa package.

Sales process (spa visits): Arnolds California Spa emphasizes superior customer service. Clients make reservations in advance; each reservation schedules one or more future spa visits and the spa package they want for that visit (each individual spa visit involves only one spa package, but the client can select a different spa package for each visit). When the client arrives for a spa visit, we assign one of our employees to be responsible for the client and direct all the clients activities for that visit. This responsibility only applies to the specific visit. Other employees are also involved in the spa visit depending upon the activities included in the spa package that the client selected. For example, the visit director may conduct a golf lesson for the client while an assisting employee may provide a therapeutic massage for the clients guest (to maintain appropriate discretion, we dont track clients guests). We expect our employees to provide the highest quality customer service and we plan to hold them accountable. Since all our employees can perform a variety of services, we track who directs and who assists with each spa visit as well as the number of hours they spend with the client during that visit.

Since most clients love their experience and want to return, we offer them an opportunity to become members of Arnolds Entourage. Members can attain gold, silver, and bronze entourage levels based on the number of visits and the amounts spent. We have some gold level clients that visit several times a month. We allow our entourage members to pay at the end of the month, so each client payment could apply to several spa visits.

Product Inventory and purchasing. We maintain an inventory of productssuch as sports gear, towels, robes, shampoos, golf balls, body-wrap material, seaweed, massage oils, bodybuilding supplements, raw fruit juices, and vitamins that we use in the spa visits. Products may be used in several different activities. We carefully plan the quantity and cost of products we expect to use in each activity, so we can estimate the overall cost of each activity. To verify our estimates, we also record the actual quantity and cost of each product used in a clients spa visit as well as the total costs for each visit.

We buy our products from various vendors. Each product can be supplied by multiple vendors and each vendor can usually supply multiple products. Weve already worked out agreements for them to deliver products on a regular basis, so we do not track purchase orders. We record purchases by purchase number when they deliver the products. All our vendors require payment in full within 30 days after we receive the products, but depending on timing, we may issue one check to pay for multiple purchases from a vendor.

Other information. We record information about activities before they are offered in any spa package, and we add new spa packages before any client reserves them. We assign each employee an employee number and a job title, like master masseuse, yoga instructor supervisor, hair styling specialist and the guy who will pump you up. However, we only use the title to impress our clients and not for any real business purpose (we dont categorize employees by employee types). We track all our employees in one class. We record information about all agents, resources, and type images before they are linked to other entities.

ATTRIBUTE LIST

Activity description

Activity name

Cash account #

Cash account balance

Cash receipt #

Cash receipt amount

Cash receipt date

Check #

Check amount

Check date

Client #

Client contact hours for this employee in this spa visit

Client name

Cost of this product received on this purchase

Cost of this product used in this spa visit

Employee #

Employee name

Employee title

Entourage level #

Number of visits required to attain this Entourage level

Planned cost of this activity

Price of this activity in this spa package

Product #

Product description

Product standard cost

Purchase #

Purchase date

Quantity of this product planned for use in this activity

Quantity of this product received on this purchase

Quantity of this product used in this spa visit

Quantity on hand (QOH) of this product

Spa package begin offering date

Spa package end offering date

Spa package name

Spa package price

Spa reservation #

Spa reservation date

Spa visit #

Spa visit date

Total labor hours for this spa visit

Vendor #

Vendor name

Year-to-date (YTD) sales $ for this activity

Year-to-date (YTD) sales $ for this client

Year-to-date (YTD) sales $ for all clients in this Entourage level