Krebs Resort & Spa

Nate and Emily Krebs are the owners of the Krebs Resort & Spa (KRS) and they have invited your consulting firm to develop a database solution for KRS. A systems analyst from your firm has already conducted an initial interview with the Krebs and has provided the following information. You need to use this information to develop an ERD. Please read this information very carefully.

1. Guests

Krebs Resort & Spa (KRS) is a membership based resort and spa. They refer to their customers as guests and currently have over 200 guests. On a guests first visit, they must choose one of four guest types: Platinum, Gold, Silver, and Bronze. Each of these guest types has a fixed one-time joining fee associated with it. The guest type chosen also determines the discount that the guest receives when making future expenditures at KRS. For each guest type, the unique guest type name, the one-time joining fee, and the discount associated with the guest type must be recorded. Information about a guest type is recorded even if there is not an existing guest for that guest type (currently KRS has guests with the following guest types: Gold, Silver, and Bronze. There is not a Platinum guest so far; however, the Krebs really want to market the Platinum guest type). In addition to the guest type, guests are assigned a unique sequential six-digit Guest ID number. Also recorded for each guest is his or her name, address, phone number, gender, birth date, and date of joining KRS (which allows KRS to create anniversary marketing letters).

2. Resort Courses

KRS offers over thirty different Resort courses to their guests, such as Rock Climbing, Private Yoga, and Java Jive Scrub instruction. Each course has a unique course number assigned to it when it is created. The course number consists of four letters followed by three digits (e.g., JJSC301 for Advanced Java Jive Scrub). Additional important information about each Resort course is the course name, description, category (e.g., novice, intermediate, advanced), duration (the number of weeks that the course is designed to run), and fee (the price of the course, prior to any applied discounts).

There are some Resort courses that have prerequisites. For example, ROCC101 (Introductory Rock Climbing) is a prerequisite for ROCC201 (Advanced Rock Climbing). Some courses dont have prerequisites and are not prerequisites of other courses. One Resort course can have many prerequisites and can be a prerequisite of many other Resort courses. Guests are encouraged to take courses in the prerequisite order, or in a logical progression (e.g., novice courses prior to intermediate courses in a particular area).

Resort courses are offered periodically in the form of sections. (A section is a specific offering of a specific Resort course). Each section has a reference code such as 010. This code, when combined with the course number, can uniquely identify a specific section (e.g., ROCC201 010, ROCC201 011). Also recorded for each section is the start date for the section, the day(s) of the week the section will be held (e.g., M, MWF, TR, etc.), the time period that the section will be held (e.g., 8:00am-10:00am, 7:30pm-9:00pm, etc.), and the section guide (this is the person that leads/teaches the section). There is exactly one guide for each section, but a guide may lead many sections. A guides record may also be kept even before any section is assigned to the guide (e.g., when the guide is newly recruited, she may be qualified to lead several courses but no actual section will be assigned to her until after several days of orientation).

A guest can register for specific sections. KRS records a unique code and a date for each registration. A registration always applies to exactly one section of a Resort course. Occasionally, KRS has sections for which no one registers; however, most sections sell out quickly; i.e., they have many registrations for a section. KRS has guests that have participated in many Resort courses and thus have registered for many sections. There is exactly one guest for each registration. KRS records guests when they first join; therefore, a guest can be recorded without registering for a section. KRS also needs to record the clerk who makes the registration. Each registration is handled by one and only one clerk. Each clerk may have handled none or many registrations.

3. KRS Resort & Spa Store

KRS also sells resort and spa products in a store located at the resort. This store carries clothing (e.g., bathing suits, sweat bands, etc.), health magazines (e.g., Flex, Womens Health) and also nutritional supplements (e.g., vitamins, protein powders, etc.). Each kind of product for sale in the store is identified by a unique product ID. For instance, Centrum Cardio 180 Vitamin Tablets has a unique product ID of VIT-1342 and there are currently 20 bottles of this product on the shelf available for sale. Also recorded for the product is its description, standard price (the price before any applicable member discounts), the quantity on hand (inventory amount), and the reorder point. This latter quantity is the minimum quantity of the product that should be kept on hand.

A KRS guest may never buy anything in the store. However, when a KRS guest comes into the store and purchases one or more products, a sales invoice is generated with a unique sequential seven-digit sales invoice number (e.g., 0003456). The specific guest making the purchase and the date of the sale are also recorded. Of course, the specific products purchased, along with the quantity of each product, and the price for each product, are also recorded. (The unit price charged for a product must reflect any discount from the products standard price to which the purchasers guest type entitles them.) The overall purchase total (with guest type discount) is also calculated for each sales invoice. The clerk making the sale is also noted. Each sales invoice is handled by exactly one clerk. Each clerk may have handled none or many sales invoices.

4. Krebs Employees

Both the Resort course guides and the clerks (for registration or spa store sales) aforementioned are employees of KRS. Another kind of employee is the managers who manage the clerks (each clerk has to report to exactly one manager but a manager manages at least one clerk; the Krebs are considered managers too). For each employee, the following information needs to be recorded: a unique four-digit Employee ID number, the employee name, address, phone number, and hire date.

There is also some specific information that needs to be recorded for different kinds of employees. For example, before leading a section of a Resort course, a guide must first qualify to lead the course. The date that a guide qualifies for leading a particular course needs to be recorded. KRS makes sure that for each course there are at least three guides qualified to lead the course. Each guide must be qualified to lead at least one course.

Clerks work on shifts and will have their shift time (e.g., 8AM 11AM) recorded in the system. Managers have their own offices and the office number needs to be recorded for each manager.