ER Diacram, Normalization, DDL $($IOOpts$)$

INTRODUCTION

For this assignment, you will be making another ER diagram modeling the operational data of the enterprise described below. All entities, relationships,

and attributes must be labeled, with cardinalities shown, and identifiers marked appropriately. This time, you will also be converting the ER diagram into

relations that can be used to store the data. You will also supply SQL to add these relations to your database.

DESCRIPTION

Serenity Springs Day Spa is a full$-$service spa that provides hair, makeup, manicures, massages, and waxes. They would like to update their filing system,

which has been entirely paper$-$based up until now. They would like to be able to automatically bill clients, remind clients of upcoming appointments, print

lists of employees, print employees' work schedules, and list the clients scheduled for a particular day.

They need to store basic information like name, phone number, physical address and email for both clients and employees. Employees have a job title, a

specialty, and a pay rate that also need to be stored. Clients can choose a preferred employee for each of the services offered by the spa, but scheduling

constraints may prevent them from getting their preferred employee when actually booking an appointment.

Although each employee has a specialty, they should be able to perform any of the services offered by the spa. The spa would like to be able to automatically

generate a complete list of services and their prices from their new database.

WHAT TO TURN IN$?$

Submit, through Blackboard, the following:

The ER diagram that you have designed. This should be in a common image format, or a PDF$.$ It may be hand drawn, but must be very neat and

very legible for credit to be awarded.

A second page like the one from the first ER diagram assignment, with all of the entities, relationships and attributes from the ER diagram listed

and described.

The relational schema resulting from your ER diagram, in the format discussed in lectures. Primary keys need to be underlined. Foreign keys and

their home relations need to be indicated in a clear way.

A text file, dayspa.sql$,$ containing valid SQL CREATE TABLE statements to create each of the relations for your database. Anything in the file that

is not part of your SQL statements should be made an SQL comment. Do not forget to add the foreign key constraints. Do not select your database

within this script $($no USE$),$ as it will make grading more difficult.