Assignment 4 ER Diagram, Normalization, DDL

CSCI 466 Assignment 4
ER Diagram, Normalization, DDL (100pts)
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 sytem, 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.
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 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 class. Primary keys
need to be underlined, and foreign keys identified in a clear way.
• A text file, dayspa.sql with valid SQL CREATE TABLE statements to create each of the relations for your
database. Do not forget the foreign key constraints.