P$0307-$ Jenny's Hair Salon & Spa

Create a file called P$0307.$ Create the ER diagrams in different tabs according to the requirements

analysis below. Export the file as a PDF and submit.

P$0307$a

Jenny's Hair Salon & Spa has several hairstylists. Each hairstylist is assigned a stylist id and their

first name, last name, social security number, salary, mobile number $($if available$),$ and email $($if

available$)$ are recorded. The salon has been in business for a long time and has many clients. Each

client is assigned an identification number for recording purposes. For clients, first name, last name,

mobile number $($if available$),$ email $($if available$),$ and preferred day are recorded. However, some

clients prefer to come when available and do not have a preferred day.

$$ Name the first tab P$0307$a$.$

$$ Draw the entities.

$$ Include attributes associated with each entity and identify them as mandatory or optional.

$$ Identify the UID$/$PK for each entity.

$$ Specify all other candidate keys as unique keys $($UKs$).$

P$0307$b

Each hairstylist is assigned clients. However, when they first start, a hairstylist is not assigned any

clients while they are training. Each client is asked if they have a preferred hairstylist. If they have

no preference, a hairstylist is assigned to them that becomes their preferred hairstylist. Of course,

they can switch their preferred hairstylist at any time.

$$ Name the tab P$0307$b$.$

$$ Include relationships and ER language.

P$0307$c

It has been determined that some clients do not want a preferred hairstylist and are willing to have

whoever is available during their appointment. As a result, if a client prefers a specific hairstylist, it

is recorded. Otherwise, a client is not assigned to a stylist.

$$ Name the tab P$0307$c$.$

$$ Modify the previous data model.

P$0307$d

Jenny's Salon has a few deluxe chairs that have more features than regular chairs. These deluxe

chairs are assigned to the most requested hairstylists from the previous month. Each deluxe chair

is assigned to one hairstylist for a month. The salon wants to maintain a historical record for each

chair keeping track of which stylist was assigned to each chair each month. The deluxe chairs are

$56$

assigned a chair number so they can keep track of who is assigned to each deluxe chair.

$$ Name the tab P$0307$d$.$

$$ Modify the previous data model.

P$0307$e

Jenny's Salon wants to improve the client experience. To accomplish this, they are promoting sev$-$

eral experienced hairstylists to supervisor. As a result, several hairstylists report to each supervisor.

However, there could be times when there are no hairstylists reporting to a supervisor. When hired,

a hairstylist is not assigned to a supervisor until orientation training is completed.

$$ Name the tab P$0307$e$.$

$$ Modify the previous data model.

P$0307$f

Jenny's Salon has decided that new employees report directly to the training supervisor until they

finish orientation training. After orientation training is completed, they are assigned to another su$-$

pervisor.

$$ Name the tab P$0307$f$.$

$$ Modify the previous data model.

P$0307$g

Over time, each client has many appointments with their stylist. Likewise, each stylist has many

appointments with their client. Clients must schedule an appointment and the salon wants to main$-$

tain a record of all appointments. For each appointment, the salon records the date and time of the

appointment, which hairstylist worked with the client, and price paid. On occasion, the preferred

hairstylist is not available when a client arrives for an appointment. In this situation, a different

hairstylist is assigned and recorded for that appointment, so the stylist gets credit for the appoint$-$

ment. Clients can only schedule one appointment on any given day.

$$ Name the tab P$0307$g$.$

$$ Modify the previous data model.

P$0307$h

The salon provides many services, which are identified by service id$.$ In addition, each service has a

description and price. For example, services can include cut, full color, root re$-$touch, shampoo,

perm, waxing, and others. Some services include a combination of services such as shampoo & cut,

shampoo & curl, color & cut, and others. The price paid for an appointment is determined by the

service price. There is one service recorded for each appointment.

$$ Name the tab P$0307$h$.$

$$ Modify the previous data model.

P$0307$i

Service prices change over time and the salon wants to maintain a history of price changes. That is$,$

they want to record service prices, so they have a historical record of when prices change. The price

charged for an appointment is determined by the current price of the service.

$$ Name the tab P$0307$i$.$

$$ Modify the previous data model.