Designing A Booking System For A Beauty Salon: Essay On Entity-Relationship And Global Relation Diagram.

Business Requirements

Beauty Salon is a system to be designed to manage the booking for a single beauty parlour. The beauty parlour has a number of staff members most of which are beauty therapists. All beauty therapies will be conducted within the parlour, and as such, there is no need to consider the room availabilities for the booked therapies. Each booking is to be made between a client and any staff member, for a therapy to be conducted by the same staff or a different therapist. For simplicity, we assume all therapists are available outside the timeslots that have already been booked. Moreover, this booking system will not deal with any physical payments other than just recording in the system the total cost for each booked therapy.

Beauty Care and Treatments: There are a number of beauty treatments or services a therapist can undertake, including manicure, pedicure, waxing, threading, facials and massages, to name a few. Different therapists may be able to provide different ranges of specific services, depending on the actual individuals. Some (item­based) services such as waxing will be charged per item while others (time­based) such as massage will be charged per half an hour or per hour. The fees for item­based services are fixed across all the therapists. But the fees for time­based services, hourly or half­hourly, may vary among the therapists due to their different level of expertise. While all therapists can perform itemised services, not all will offer timed services.

Clients: Each regular client or patron will typically have her own client record set up on the system, and this will allow her to more easily make a booking. However, a non­regular customer will also be able to turn up in the beauty parlour and request a service to be done to her. Such customers don't have to create their client profiles and can still receive the services when they just turn up in the parlour, provided there are suitable therapists available at the parlour at the time.

Business Activities: For the typical business activities, the Beauty Salon system will allow one to view which appointments have been made on any given day for any particular therapist, list all the available therapists for a given period of time, and many more.

In this part, you are asked to design a database to support a Beauty Salon Booking System. The major business requirements are summarised below in the Mini Case: A Beauty Salon Booking System. You are asked to develop a detailed Entity­Relationship model for this mini case. Your ER model should consist of a detailed ER diagram integrated with itemised discussions on the features of the entities and relationships and all the assumptions you made where applicable. The ER diagram and the accompanying document should identify keys, constraints, entity types, relationship types, specialisation/generalisation if any, etc.

You must use the same notation scheme for the ER diagram as the textbook (use UML notations as shown in the last page of the textbook, and don't use Crew Foot notations), and the ER diagram should be strictly in the way the textbook uses for. We note that our past experiences show drawing an ER diagram on Microsoft Powerpoint gives one better control and flexibility when compared with drawing diagrams with Microsoft Word.

i. The ER diagram should include, among others, representative attributes for all entity types, proper subclassing if any, and correct participation multiplicities for the relationship types. It should be meaningfully and well designed, and should also include all relevant and necessary aspects, and indicate any supplementary business rules if you decide to introduce.

ii. Map the above ER diagram into a global relation diagram (GRD). The GRD should be in a form similar to Figure 17.9 (page 554, or 516 for edition 5) of the textbook, but all the attributes should be kept there too. Include in the diagram all the primary keys, foreign keys, and the multiplicity constraints.

Note: Please note that an ERD is the artefact of the conceptual database design phase, while a GRD is the artfact of the logical database design phase which relates to the relational models. As such, a good ERD should be conceptually more concise and the relationships there should, in general, remain so rather than becoming extra entities as in a relational model.

A single plain­text file containing SQL statements for creating all the tables and making all the queries. The script should be executable on the School's Microsoft SQL Server, otherwise, the corresponding marks in the above-listed items will be deducted accordingly. Marks will be deducted in the corresponding questions if this SQL script in the plain­text file is not submitted.

The entity relationship is modelled based on the following assumptions.

• There exists bookings made by registered customers. Each booking can have one or more services associated with it.
• Non-regular appointments are made by those customers who are not registered as clients and each appointment can have one more services associated with it.
• A booking services for a client or a non-regular client can be performed by more than one therapist. For example for just one booking with multiple services, the client can get a massage from a different therapist and then proceed to get waxing from a different therapist and all this is within the same booking. The same case applies to an appointment from non-regular customers.
• For time-based service a staff has his or her own rate depending on their expertise. A staff can be an expert in one or more time-based services but all staff can do item-based service.

create table client (

clientID integer primary key,

firstName varchar(50) not null,

lastname varchar(50) not null,

contact varchar(25) not null

create table staff (

staffID integer primary key,

firstName varchar(50) not null,

lastname varchar(50) not null,

address varchar(100) not null

create table nonregular_appointment (

appointmentID integer primary key,

appointmentDate date not null,

appointmentTime varchar(15) not null,

cost decimal(8,2)

create table service (

serviceID integer primary key,

name varchar(50) not null,

serviceType varchar(25) not null

create table booking(

bookingID integer primary key,

clientID integer not null,

cost decimal(8,2) not null,

dateBooked date not null,

timeBooked varchar(15) not null,

foreign key (clientID) references client (clientID)

create table time_based (

serviceID integer primary key,

chargeBasis decimal not null,

foreign key (serviceID) references service (serviceID);

create table item_based (

serviceID integer primary key,

rate decimal not null,

foreign key (serviceID) references service (serviceID)

create table staff_services (

staffID integer not null,

serviceID integer not null,

rate decimal not null,

primary key (serviceID,staffID),

foreign key (serviceID) references time_based (serviceID),

foreign key (staffID) references staff (staffID)

create table booked_services (

bookingID integer not null,

serviceID integer not null,

staffID integer not null,

primary key (serviceID,bookingID),

foreign key (serviceID) references service (serviceID),

foreign key (staffID) references staff (staffID),

foreign key (bookingID) references booking (bookingID)

create table appointment_services (

appointmentID integer not null,

serviceID integer not null,

staffID integer not null,

primary key (serviceID,appointmentID),

foreign key (serviceID) references service (serviceID),

foreign key (staffID) references staff (staffID),

foreign key (appointmentID) references nonregular_appointment (appointmentID)

insert into client (clientID, firstName, lastName, contact) values ('1','Lebron','James','+3342343123');

insert into client (clientID, firstName, lastName, contact) values ('2','Kevin','Durant','+3342343123');

insert into client (clientID, firstName, lastName, contact) values ('3','Steph','Curry','+3342343123');

insert into staff (staffID, firstName, lastName, address) values ('1','Jon','Snow','winterfell');

insert into staff (staffID, firstName, lastName, address) values ('2','Arya','Stark','winterfell');

insert into staff (staffID, firstName, lastName, address) values ('3','Cersei','Lannister','kings landing');

insert into nonregular_appointment (appointmentID, appointmentDate, appointmentTime) values ('1','2018-12-12','10:00AM');

insert into nonregular_appointment (appointmentID, appointmentDate, appointmentTime) values ('2','2018-12-12','11:00AM');

insert into nonregular_appointment (appointmentID, appointmentDate, appointmentTime) values ('3','2018-12-12','12:00PM');

insert into service (serviceID,name, serviceType) values (1,'massage','time-based');

insert into service (serviceID,name, serviceType) values (2,'waxing','time-based');

insert into service (serviceID,name, serviceType) values (3,'Nails','item-based');

insert into booking (bookingID, clientID, cost, dateBooked, timeBooked) values (1,1,233,'2018-12-12','9:00AM');

insert into booking (bookingID, clientID, cost, dateBooked, timeBooked) values (2,2,433,'2018-12-12','10:00AM');

insert into booking (bookingID, clientID, cost, dateBooked, timeBooked) values (3,3,321,'2018-12-12','11:00AM');

insert into time_based (serviceID, chargeBasis) values (1,1);

insert into time_based (serviceID, chargeBasis) values (2,0.5);

insert into item_based(serviceID, rate) values (1,12);

insert into staff_services (staffID, serviceID, rate) values (1,1,12);

insert into staff_services (staffID, serviceID, rate) values (2,2,32);

insert into staff_services (staffID, serviceID, rate) values (1,2,21);

insert into booked_services(bookingID, serviceID, staffID) values (1,1,1);

insert into booked_services(bookingID, serviceID, staffID) values (2,2,2);

insert into booked_services(bookingID, serviceID, staffID) values (1,2,3);

insert into appointment_services(appointmentID, serviceID, staffID) values (1,1,1);

insert into appointment_services(appointmentID, serviceID, staffID) values (2,2,2);

insert into appointment_services(appointmentID, serviceID, staffID) values (1,2,3);