Create an entity$-$relationship diagram based on the case and then draw a logical data model in MySQL workbench based on this ERD. 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 a database. 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 $2000$ guests. On a guest$$s first visit, they must choose one of

four guest types: Platinum, Gold, Silver, and Bronze. Each of these guest types has a fixed onetime 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$.,$

JJSC$301$ 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, ROCC$101($Introductory

Rock Climbing$)$ is a prerequisite for ROCC$201($Advanced Rock Climbing$).$ Some courses don$$t

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

$2$

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$.,$ ROCC$201010,$

ROCC$201011).$ 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$:$00$am$-10$:$00$am$,7$:$30$pm$-9$:$00$pm$,$ 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 guide$$s 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.