[Solved] CSCI466-Assignment 4 ER Diagram, Normalization, DDL

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.