Contact management system for a salon

Create a simple, user friendly contact management system for a salon. The application should be able to store all contacts who have visited the salon, the date visited, the services rendered and by whom. It should also be able to create flags to notify the user when they are due for a new appointment. This will enable the owner to maintain her records much more easily, allow for backups, and allow for much greater interaction with the data as desired than the current paper system.

Business Problem:

Salon owner is currently keeping all clients in an address book and needs a way to digitally keep track of the clients and the services and products they buy as well as time they should return.



Scope:

Recording client information, appointments, stylists, services and services rendered, products and products rendered are within the scope. It will also allow for querying client information. Keeping track of the inventory and managing scheduling is out of the scope.



Business Process Flow:

1. A client enters or calls the salon.

2. They schedule a service with a stylist.

3. The service is performed by the stylist and they recommend a date to return.

4. The client can purchase product(s) at any point during the process.





Data Elements Verification:

The following table shows all data elements identified in the user requirements and represented in the ER model.The verification column is based on the ER model.



Objects
Properties
Verification
Client



Client ID
✔

Name
✔

Phone number
✔

Email
✔

DOB
✔

Last Appt.
✔
Service



Service ID
✔

Serv-name
✔

Serv-Desc
✔

Serv-Price
✔
Stylist



Stylist ID
✔

Last name
✔

First Name
✔

Skills
✔
Product



Product ID
✔

Prod-Name
✔

Prod-Desc
✔

Prod-Price
✔
Purchases



Purchase- Date
✔

Quality
✔


User Requirements Specification
Entity Types

Client Entity Type:

● Client ID: 5 digit identifier meant to be unique within the salon

● First_Name: First name of the client

● Last_Name: Last name of the client

● Client_Phone: Phone number for the client

● Email: Email address for the client

● DOB: Birthday of the client.

● Last_Appt: Derived Attribute for the date of last appointment.



Stylist Entity Type:

● Stylist ID: 3 digit identifier meant to be unique within the salon

● First_Name: First name of the stylist

● Last_Name: Last name of the stylist

● Skills: Multi-value attribute for the skills of the stylist



Service Entity Type:

● Service ID: 4 digit identifier meant to be unique within the salon

● Serv_Name: Name of the service

● Serv_Desc: Short description of the service

● Service Price: Price of the service



Product Entity Type:

● Product ID: 4 digit identifier meant to be unique within the salon

● Prod_Name: Name of the product

● Prod_Desc: Short description of the product

● Prod_Price: Price of the product













Business Verification Rules
The business rules in the requirement documents are also checked against the ER model as shown the table below



Relationship
Business Rule
ER Model
Client - Services


Client - schedules
Rule 1
(0,M)
Service - schedules
Rule 2
(0,M)
Client - Stylist


Client - assigned
Rule 1
(0,M)
Stylist - assigned
Rule 2
(0,M)
Client - Product


Client - purchased
Rule 1
(0,M)
Product - purchased
Rule 2
(0,M)
Relationships

Client and Services:

A client will be scheduled for the services they ask for.



Rule 1: A client can have any number of services rendered and a service can be rendered to multiple people.

Rule 2: During an appointment a service can only be rendered to the assigned client.



Client and Stylist:

A client will be assigned a stylist when scheduled for an appointment either by the user's choice or by with assignment by matching services provided.



Rule 1: A stylist can have multiple clients or none.

Rule 2: A client is scheduled one stylist.



Client and Product:

The salon sells various beauty care products. A client can buy as many products as they desire or none at all.



Rule 1: A client can purchase 0 to many products.

Rule 2: A product can be purchased by 0 to many clients



Stylist and Services:

A stylist offers specific services that can be offered to the client.



Rule 1: A stylist can have many services offered.

Rule 2: A service can be offered by multiple stylists.

Model Discussion

The model supports these processes and keeps track of the dates and times of all scheduled appointments. It provides entities for all of the necessary objects that need to be recorded and tracked as well as keeping track of relationship attributes as needed for the appointments and purchases. All business rules have been verified and modeled accordingly.

ERD Model

Question solve based on the diagram below and above description.
1. Database design and creation – implementation of the ER model into a relational database including the design of table structures, testing data and all SQL statements

2. _Database application verification – discussion of how application functions, user views, and business processes are supported by the database.