This is CIS project.
Shelby4
All_in_one_example.pdf
Page 1 of 20
The Hair Salon Database Project Updated: January 19, 2018 by Richard Holowczak The following materials document the design and development of a database application to support a small hair salon. The project begins with a description of the business and proceeds through conceptual (E-R) modeling, logical (Relational) modeling, Physical modeling and finally implementation of a database application. Notes (Commentary) are provided at the end of each section to explain some specific features of the steps being carried out.
I. Business Scenario Our company has owned and operated a hair and nail salon in midtown Manhattan for 7 years. We have been using spreadsheets and a paper log book to keep track of customers, appointments and payments. We would like to replace this manual method of tracking the business with a database. In our business, Customers make Appointments with their favorite hair stylist (folks who cut and style customers’ hair) or other employee and can indulge in a number of Services such as basic hair cut/styling, hair color, highlights, perm, facial, manicure, pedicure, etc. We need to keep track of materials (shampoo, hair color) and the time it will take to complete each service. While each service has a standard price, promotions and other factors may affect the actual price extended to the Customer for the given service. We also need to keep track of our Employees including their home address, contact information and rate of pay. We need to keep track of each Customer’s contact information as well as their gender. For Appointments we need to know the date and time of the appointment and which customer the appointment is for. Commentary: Based on the above description, construct an Entity Relationship model using UML notation that will capture all of the business’ data needs.
Page 2 of 20
II. ER Model using UML Notation Based on the above description, we develop the following Entity Relationship model using UML notation.
Commentary: What we like about this model:
All relationship lines are going in a horizontal or vertical position. No lines are crossed.
Attribute names are spelled out with no spaces in the names. No abbreviations are used.
Each relationship has two very phrases from which we can make relationship sentences (see next section).
The diagram also has a “legend” in the upper–right corner so we can tell what the diagram represents and who last modified the diagram.
Relationship Sentences One Customer may be making one or more Appointments One Appointment must be a reservation for one and only one Customer One SalonService may be a service rendered as one or more ServiceRendered One ServiceRendered must be a rendering of one and only one SalonService One Employee may be rendering one or more ServiceRendered One ServiceRendered must be rendered by one and only one Employee One Appointment may be a reservation to provide one or more ServiceRendered One ServiceRendered must be a specific service rendered during one and only one Appointment
Page 3 of 20
Commentary: The relationship sentences should make sense. In this example, the verb phrases are underlined. The entity names are in bold letters. The minimum cardinality (may be for 0 and must be for 1) are written in italics. The maximum cardinality is written as “one or more” for * and “one and only one” for 1.
III. Conversion to Relational Model The next step is to Convert the Entity Relationship diagram to a Relational Model. During this step, Identifiers in the Entities become Keys in the Relations. One-to-Many relationships result in a foreign key being copied from the One side to the Many side of the relationship. Customer ( CustomerID (key), FirstName, LastName, PhoneNumber, Street, City, State, ZipCode ) SalonService ( ServiceID (key), ServiceName, ServiceDuration, ServicePrice, ServiceMaterials ) Employee ( EmployeeID (key), FirstName, LastName, Street, City, State, ZipCode, PayRate ) Appointment ( AppointmentID (key), AppointmentDate, AppotinmentTime, CustomerID (fk) ) ServiceRendered ( AppointmentID (fk)(key), LineItemNumber(key), ServiceID (fk), ServiceExtendedPrice, EmployeeID(fk)) This is the “initial set of relations.” Commentary:
Notice that the ServiceRendered entity from the ER model is ID-Dependent which means it needs an attribute in addition to LineItemNumber to form a composite key.
Keys are shown with the (key) designation and foreign keys are shown with the (fk) designation.
Page 4 of 20
IV. Normalization The next step is to Normalize the Relations. 1) Customer ( CustomerID (key) , FirstName, LastName, CustPhone, Street, City, State, ZipCode, Gender ) Sample Data
CustomerID FirstName LastName PhoneNumber Street City State ZipCode Gender C101 Elia Fawcett 201-222-2222 8989 Smith Rd Garfield NJ 07026 F
C102 Ishwarya Roberts 201-222-3333 65 Hope Rd Garfield NJ 07026 M
C103 Frederic Fawcett 201-222-2222 8989 Smith Rd Garfield NJ 07026 M
C104 Goldie Montand 201-222-4321 5235 Ironwood Ln Garfield NJ 07026 F
C105 Dheeraj Alexander 201-222-4545 666 22nd Ave Bergenfield NJ 07621 M
C106 Josie Davis 201-333-6789 4200 Bluejay Ave Bergenfield NJ 07621 F
C107 Faye Glenn 201-333-4242 1522 Main St Cliffside Park NJ 07010 F
C108 Lauren Hershey 201-444-1313 2360 Maxon Rd Englewood NJ 07631 F
Key: CustomerID FD1: CustomerID -> FirstName, LastName, PhoneNumber, Street, City, State, ZipCode, Gender FD2: ZipCode -> City, State 1NF: Meets the definition of a relation 2NF: No partial Key dependencies 3NF: Transitive dependency exists: CustomerID -> ZipCode and ZipCode -> City, State Solution: Split Customer relation into two new relations named CustomerData and ZipCodes: CustomerData (CustomerID (key), FirstName, LastName, CustPhone, Street, ZipCode (fk), Gender ) Key: CustomerID FD1: CustomerID -> FirstName, LastName, PhoneNumber, Street, ZipCode (fk), Gender 1NF: Meets the definition of a relation 2NF: No partial Key dependencies 3NF: No Transitive dependencies BCNF: All determinants are candidate keys ZipCodes( ZipCode (key), City, State) Key: ZipCode FD1: ZipCode -> City, State 1NF: Meets the definition of a relation 2NF: No partial Key dependencies 3NF: No Transitive dependencies BCNF: All determinants are candidate keys
Page 5 of 20
2) SalonService ( ServiceID (key), ServiceName, ServiceDuration, ServicePrice, ServiceMaterials ) Sample Data:
ServiceID ServiceName ServiceDuration ServicePrice ServiceMaterials
SV101 Men's Haircut 20 22.00 None
SV102 Women's Haircut 30 32.00 None
SV103 Child Haircut 20 15.00 None
SV104 Women's Hair Color 60 76.00 Color, Reagent, Gloves, Reagent Brush, Foil
SV105 Women's Hair Style 45 56.00 Shampoo, Conditioner
SV106 Men's Hair Style 45 46.00 Shampoo, Conditioner
Key: ServiceID FD1: ServiceID -> ServiceName, ServiceDuration, ServicePrice, ServiceMaterials 1NF: ServiceMaterials may be treated as a multi-valued attribute. In this case SalonService is not in 1NF. Solution: Split out ServiceMaterials into a separate relation. For this example, however we will keep ServiceMaterials as an attribute of the SalonService relation. 1NF: Meets the definition of a relation 2NF: No partial Key dependencies 3NF: No Transitive dependencies BCNF: All determinants are candidate keys 3) Employee( EmployeeID (key), FirstName, LastName, Street, City, State, ZipCode, PayRate )
EmployeeID FirstName LastName Street City State ZipCode PayRate
E300 Joy Aveda 46 Easton Ave. Garfield NJ 07026 18.00
E400 Geraldo Geraldo 12 Fortis Blvd. Apt. 2A Garfield NJ 07026 22.00
E500 Koy Petruzzio 70 Wilard St. Garfield NJ 07026 20.00
E600 Landry Monroe 73 Holly Terrace Cliffside Park NJ 07010 18.00
E700 Pat Reese 2 Lincoln Place Cliffside Park NJ 07010 23.00
E800 Winter Tanner 215 Elm Ave Teaneck NJ 07665 23.00
Key: EmployeeID FD1: EmployeeID -> FirstName, LastName, Street, City, State, ZipCode, PayRate 1NF: Meets the definition of a relation 2NF: No partial Key dependencies 3NF: Transitive dependency exists: EmployeeID -> ZipCode and ZipCode -> City, State Solution: Split Customer relation into two new relations named EmployeeData and ZipCodes: EmployeeData(EmployeeID (key), FirstName, LastName, Street, ZipCode (fk), PayRate ) Key: EmployeeID FD1: EmployeeID -> FirstName, LastName, Street, ZipCode (fk), PayRate 1NF: Meets the definition of a relation 2NF: No partial Key dependencies 3NF: No transitive dependencies BCNF: All determinants are candidate keys Note: We already have a ZipCodes relation from when the Customer relation was split up. So we re-use that ZipCodes relation. There is no need to create a second ZipCodes relation.
Page 6 of 20
4) Appointment ( AppointmentID (key), AppointmentDate, AppotinmentTime, CustomerID (fk) ) Sample Data:
AppointmentID AppointmentDate AppointmentTime CustomerID
A400 10/22/2017 11:00:00 AM C101
A401 11/6/2017 12:45:00 PM C102
A402 12/7/2017 2:00:00 PM C106
A403 12/18/2017 3:30:00 PM C106
A404 12/21/2017 11:30:00 AM C108
A405 12/31/2017 10:00:00 AM C107
A406 1/11/2018 3:15:00 PM C103
A407 1/12/2018 2:30:00 PM C104
A408 1/22/2018 4:00:00 PM C105
Key: AppointmentID FD1: AppointmentID -> AppointmentDate, AppotinmentTime, CustomerID (fk) 1NF: Meets the definition of a relation 2NF: No partial Key dependencies 3NF: No Transitive dependencies BCNF: All determinants are candidate keys 5) ServiceRendered ( AppointmentID (fk)(key), LineItemNumber(key), ServiceID (fk), ServiceExtendedPrice,
EmployeeID(fk) ) Sample Data:
AppointmentID LineItemNumber ServiceID ServiceExtendedPrice EmployeeID
A400 1 SV104 75.00 E400
A400 2 SV102 25.00 E400
A401 1 SV101 22.00 E500
A402 1 SV104 75.00 E600
A402 2 SV102 30.00 E800
A403 1 SV105 50.00 E300
A404 1 SV105 55.00 E300
A405 1 SV102 30.00 E700
A405 2 SV104 70.00 E700
A405 3 SV105 50.00 E700
Key: AppointmentID, LineItemNumber FD1: AppointmentID, LineItemNumber -> ServiceID (fk), ServiceExtendedPrice, EmployeeID(fk) 1NF: Meets the definition of a relation 2NF: No partial Key dependencies 3NF: No Transitive dependencies BCNF: All determinants are candidate keys
Page 7 of 20
Final Set of Relations Customer ( CustomerID (key) , FirstName, LastName, PhoneNumber, Street, ZipCode (fk), Gender ) ZipCodes ( ZipCode (key), City, State) SalonService ( ServiceID (key), ServiceName, ServiceDuration, ServicePrice, ServiceMaterials ) Employee ( EmployeeID (key), FirstName, LastName, Street, ZipCode (fk), PayRate ) Appointment ( AppointmentID (key), AppointmentDate, AppotinmentTime, CustomerID (fk) ) ServiceRendered ( AppointmentID (fk)(key), LineItemNumber(key), ServiceID (fk), ServiceExtendedPrice, EmployeeID(fk)) Commentary:
Note that only one ZipCodes relation is required. It is shared with both Customer and Employee relations.
As noted previously, the ServiceMaterials attribute has not been normalized in this example. Perhaps it can be done in a future assignment or enhancement to the model.
Page 8 of 20
V. Structured Query Language (SQL) to Create the Schema Create a table in the database for each of the relations in the final set of relations. The following SQL code creates the six tables and adds the PRIMARY KEY constraint to each one: CREATE TABLE ZipCodes
(
zipcode VARCHAR(12) NOT NULL,
city VARCHAR(36),
state VARCHAR(4),
CONSTRAINT pk_zipcodes
PRIMARY KEY (zipcode)
)
CREATE TABLE Customer
(
CustomerID VARCHAR(10) NOT NULL,
FirstName VARCHAR(35),
LastName VARCHAR(35),
PhoneNumber VARCHAR(15),
Street VARCHAR(35),
ZipCode VARCHAR(12),
Gender VARCHAR(2),
CONSTRAINT pk_customer
PRIMARY KEY (CustomerID)
)
CREATE TABLE Appointment
(
AppointmentID VARCHAR(10) NOT NULL,
AppointmentDateTime DATE,
CustomerID VARCHAR(10) NOT NULL,
CONSTRAINT pk_appointment
PRIMARY KEY (AppointmentID)
)
CREATE TABLE SalonService
(
ServiceID VARCHAR(10) NOT NULL,
ServiceName VARCHAR(35),
ServiceDuration INTEGER,
ServicePrice NUMBER,
ServiceMaterials VARCHAR(255),
CONSTRAINT pk_salonservice
PRIMARY KEY (ServiceID)
)
Page 9 of 20
CREATE TABLE Employee
(
EmployeeID VARCHAR(10) NOT NULL,
FirstName VARCHAR(35),
LastName VARCHAR(25),
Street VARCHAR(45),
ZipCode VARCHAR(12),
PayRate NUMBER,
CONSTRAINT pk_employee
PRIMARY KEY (EmployeeID)
)
CREATE TABLE ServiceRendered
(
AppointmentID VARCHAR(10) NOT NULL,
LineItemNumber INTEGER NOT NULL,
ServiceID VARCHAR(10) NOT NULL,
ServiceExtendedPrice NUMBER,
EmployeeID VARCHAR(10) NOT NULL,
CONSTRAINT pk_ServiceRendered
PRIMARY KEY (AppointmentID, LineItemNumber)
)
The following SQL codes add FOREIGN KEY constraints to link the tables together: ALTER TABLE Customer
ADD CONSTRAINT fk_customer_zipcodes
FOREIGN KEY (ZipCode)
REFERENCES ZipCodes (ZipCode)
ALTER TABLE Employee
ADD CONSTRAINT fk_employee_zipcodes
FOREIGN KEY (ZipCode)
REFERENCES ZipCodes (ZipCode)
ALTER TABLE Appointment
ADD CONSTRAINT fk_customer_appointment
FOREIGN KEY (CustomerID)
REFERENCES Customer (CustomerID)
ALTER TABLE ServiceRendered
ADD CONSTRAINT fk_ServiceRendered_Service
FOREIGN KEY (ServiceID)
REFERENCES SalonService (ServiceID)
ALTER TABLE ServiceRendered
ADD CONSTRAINT fk_ServiceRendered_Employee
FOREIGN KEY (EmployeeID)
REFERENCES Employee (EmployeeID)
ALTER TABLE ServiceRendered
ADD CONSTRAINT fk_ServiceRendered_Appointment
FOREIGN KEY (AppointmentID)
REFERENCES Appointment (AppointmentID)
Page 10 of 20
Commentary on SQL:
Most DBMS will store the DATE and TIME in the same column. So AppointmentDate and AppointmentTime were combined into one column in the database named AppointmentDateTime
Keys and Foreign keys should have the same exact name and data type. For example, the Key CustomerID is VARCHAR(10) in the Customer table and also VARCHAR(10) in the Appointment table.
Constraints are given meaningful names such as pk_customer for a primary key and fk_customer_zipcodes for a foreign key.
Columns such as Phone Number and ZipCode should use VARCHAR data type. If a NUMBER or INTEGER data type is used, then the leading zeros will be missing.
After creating the tables and adding the foreign key constraints, the database schema now looks like the following:
Page 11 of 20
Relationship View Using the Relationship View under Database Tools, we can see the relationships (foreign keys) between the tables:
Page 12 of 20
Adding Data to the Tables using SQL INSERT Statements INSERT INTO ZipCodes VALUES ('07026', 'Garfield', 'NJ');
INSERT INTO ZipCodes VALUES ('07621', 'Bergenfield', 'NJ');
INSERT INTO ZipCodes VALUES ('07010', 'Cliffside Park', 'NJ');
INSERT INTO ZipCodes VALUES ('07631', 'Englewood', 'NJ');
INSERT INTO ZipCodes VALUES ('07665', 'Teaneck', 'NJ');
INSERT INTO Customer VALUES ('C101', 'Elia', 'Fawcett', '201-222-2222', '8989 Smith
Rd', '07026', 'F');
INSERT INTO Customer VALUES ('C102', 'Ishwarya', 'Roberts', '201-222-3333', '65
Hope Rd', '07026', 'M');
INSERT INTO Customer VALUES ('C103', 'Frederic', 'Fawcett', '201-222-2222', '8989
Smith Rd', '07026', 'M');
INSERT INTO Customer VALUES ('C104', 'Goldie', 'Montand', '201-222-4321', '5235
Ironwood Ln', '07026', 'F');
INSERT INTO Customer VALUES ('C105', 'Dheeraj', 'Alexander', '201-222-4545', '666
22nd Ave', '07621', 'M');
INSERT INTO Customer VALUES ('C106', 'Josie', 'Davis', '201-333-6789', '4200 Bluejay
Ave', '07621', 'F');
INSERT INTO Customer VALUES ('C107', 'Faye', 'Glenn', '201-333-4242', '1522 Main
St', '07010', 'F');
INSERT INTO Customer VALUES ('C108', 'Lauren', 'Hershey', '201-444-1313', '2360
Maxon Rd', '07631', 'F');
INSERT INTO SalonService VALUES ('SV101', 'Men''s Haircut', 20, 22, 'None');
INSERT INTO SalonService VALUES ('SV102', 'Women''s Haircut', 30, 32, 'None');
INSERT INTO SalonService VALUES ('SV103', 'Child Haircut', 20, 15, 'None');
INSERT INTO SalonService VALUES ('SV104', 'Women''s Hair Color', 60, 76, 'Color,
Reagent, Gloves, Reagent Brush, Foil');
INSERT INTO SalonService VALUES ('SV105', 'Women''s Hair Style', 45, 56, 'Shampoo,
Conditioner');
INSERT INTO SalonService VALUES ('SV106', 'Men''s Hair Style', 45, 46, 'Shampoo,
Conditioner');
INSERT INTO Employee VALUES ('E300', 'Joy', 'Aveda', '46 Easton Ave.', '07026',
18);
INSERT INTO Employee VALUES ('E400', 'Geraldo', 'Geraldo', '12 Fortis Blvd. Apt.
2A', '07026', 22);
INSERT INTO Employee VALUES ('E500', 'Koy', 'Petruzzio', '70 Wilard St. ', '07026',
20);
INSERT INTO Employee VALUES ('E600', 'Landry', 'Monroe', '73 Holly Terrace',
'07010', 18);
INSERT INTO Employee VALUES ('E700', 'Pat', 'Reese', '2 Lincoln Place', '07010',
23);
INSERT INTO Employee VALUES ('E800', 'Winter', 'Tanner', '215 Elm Ave', '07665',
23);
INSERT INTO Appointment VALUES ('A400', '10/22/2017 11:00:00 AM', 'C101');
INSERT INTO Appointment VALUES ('A401', '11/06/2017 12:45:00 PM', 'C102');
INSERT INTO Appointment VALUES ('A402', '12/07/2017 02:00:00 PM', 'C106');
INSERT INTO Appointment VALUES ('A403', '12/18/2017 03:30:00 PM', 'C106');
Page 13 of 20
INSERT INTO Appointment VALUES ('A404', '12/21/2017 11:30:00 AM', 'C108');
INSERT INTO Appointment VALUES ('A405', '12/31/2017 10:00:00 AM', 'C107');
INSERT INTO Appointment VALUES ('A406', '01/11/2018 03:15:00 PM', 'C103');
INSERT INTO Appointment VALUES ('A407', '01/12/2018 02:30:00 PM', 'C104');
INSERT INTO Appointment VALUES ('A408', '01/22/2018 04:00:00 PM', 'C105');
INSERT INTO ServiceRendered VALUES ('A400', 1, 'SV104', 75, 'E400');
INSERT INTO ServiceRendered VALUES ('A400', 2, 'SV102', 25, 'E400');
INSERT INTO ServiceRendered VALUES ('A401', 1, 'SV101', 22, 'E500');
INSERT INTO ServiceRendered VALUES ('A402', 1, 'SV104', 75, 'E600');
INSERT INTO ServiceRendered VALUES ('A402', 2, 'SV102', 30, 'E800');
INSERT INTO ServiceRendered VALUES ('A403', 1, 'SV105', 50, 'E300');
INSERT INTO ServiceRendered VALUES ('A404', 1, 'SV105', 55, 'E300');
INSERT INTO ServiceRendered VALUES ('A405', 1, 'SV102', 30, 'E700');
INSERT INTO ServiceRendered VALUES ('A405', 2, 'SV104', 70, 'E700');
INSERT INTO ServiceRendered VALUES ('A405', 3, 'SV105', 50, 'E700');
Commentary on Data Samples:
We add just enough data to be able to test out the relationships between the tables and to give the application developers something to work with.
Be careful of the order in which data is added. For example, all ZipCodes need to be inserted first, before either Customer or Employee (which both use ZipCode as a foreign key) can be inserted.
When adding VARCHAR data with embedded quotes, use two quotes together. e.g., ‘Women’’s Hair Stlye’
At this point the database schema is ready for the application developers to get to work designing forms, reports and queries.
Page 14 of 20
VI. Database Application The database application consists of a set of Forms, Reports and Queries that are linked together on a Navigation Form. The Navigation Form is the first form that appears when the database is opened. Navigation Form
Different data entry forms and reports can be displayed by clicking on the selection on the left-hand side.
Page 15 of 20
Customer Data Entry Form
The Customer Data Entry form is used to look up existing customers and to input new customer information. The City and State fields are automatically filled in by selecting a ZipCode form the Combo Box.The form has several custom VBA codes to convert the First and Last Names to proper case and to generate a new, unique customer ID when an empty CustomerID field appears after creating a new record.
Page 16 of 20
Salon Services Data Entry Form
The Salon Service Data Entry form is used to query, update and add new salon services. Appointment Data Entry Form
The Appointments Data Entry form is used to create a new appointment for a customer. As with the Customer form, a new Appointment ID can be created by clicking in a blank Appointment ID field after a new record is created. The Customer can be selected from the Customer ID combo box as shown below:
Page 17 of 20
If this is a new Customer making an appointment, the user can click on the New Customer button to bring up the Customer Data Entry form. After the new customer’s information is saved, the user can return to the Appointment Data Entry form and make the appointment.
Page 18 of 20
Appointment and Services Form
The purpose of this form is to enter different services associated with an appointment. This form could also be used to generate a bill for the customer. The Service and the Employee can be selected from their respective combo boxes as shown below:
Page 19 of 20
Customer Appointments Totals Report
This report provides a summary of the number of appointments and total amount spent by each customer. Based on query: SELECT Customer.CustomerID, FirstName, LastName,
SUM(q.TotalSpent) AS TotalSpent,
COUNT(q.AppointmentID) AS NumberOfAppointments
FROM Customer, Appointment, Query_Total_Spent_Each_Appointment AS q
WHERE Customer.CustomerID = Appointment.CustomerID
AND Appointment.AppointmentID = q.AppointmentID
GROUP BY Customer.CustomerID, FirstName, LastName
ORDER BY LastName, FirstName;
And Query Total_Spent_Each_Appointment SELECT Appointment.AppointmentID,
SUM(ServiceExtendedPrice) AS TotalSpent
FROM Appointment, ServiceRendered
WHERE Appointment.AppointmentID = ServiceRendered.AppointmentID
GROUP BY Appointment.AppointmentID
ORDER BY Appointment.AppointmentID;
Page 20 of 20
Services and Discounts Report
This report shows each of the services with totals of the regular service price, the extended price and an indication of the amount of discount applied to the services rendered in the past. Based on query: SELECT SalonService.ServiceID, ServiceName,
SUM(ServicePrice) AS TotalServicePrice,
SUM(ServiceExtendedPrice) AS TotalExtPrice,
FORMAT(1.0 - (SUM(ServiceExtendedPrice) / SUM(ServicePrice)), "0.00%")
AS Discount
FROM SalonService, ServiceRendered
WHERE SalonService.ServiceID = ServiceRendered.ServiceID
GROUP BY SalonService.ServiceID, ServiceName
ORDER BY SalonService.ServiceID, ServiceName;
Customer Addresses Report
This report shows the complete names and addresses of each Customer.
