Happy Molly Maid Service
We have a “Happy Molly” house cleaner service company and we work on a schedule based system. Customer goes to our website and schedule service depending upon their requirement (work type). We sent 8 cleaners based on the appointment to the customer’s house with all the necessary tools and equipment. We have cleaners who from 8:00 am to 6:00 pm every day. We sent cleaners with all the equipment at the customer’s house.

Business Rule:
Each customer can have one service each day
We have only 64 cleaners and they work in teams of eight
Each type of work should have a unique ID
Each cleaner must have their own unique ID, last name, and first name
A customer can have only one address
Each customer must have their own unique ID, last name, first name, address, and phone number
In the team of 8, one cleaner will supervise all the work
Cleaners get paid based on hours
Customers pay based on the work order
The work order must contain order ID, time, work type, date, and work type
Customers can have many work orders in a month
Customer’s each work order can have multiple work types
Cleaners can be assigned to one or more orders based on the amount of work
Work Order can have one or many services
Service can only have one and only one work order
CREATE DATABASE MaidService ;

CREATE TABLE Customer
(
 Customer_ID Char(2),
 Customer_First VarChar(255),
 Customer_Last VarChar(255),
 Address VarChar(255),
 PhoneNumber Char(10),
 PRIMARY KEY(Customer_ID)
);  

CREATE TABLE WorkOrder
(
 Order_ID VarChar(2),
 Customer_ID VarChar(2),
 Order_TimeStamp Timestamp,
 OrderPrice VarChar(5),
 PRIMARY KEY(Order_ID),
 FOREIGN KEY(Customer_ID) REFERENCES Customer(Customer_ID)
);

CREATE TABLE Cleaner
(
 Cleaner_ID Char(2),
 Cleaner_First VarChar(255),
 Cleaner_Last VarChar(255),
 PRIMARY KEY(Cleaner_ID)
);

CREATE TABLE WorkInfo
(
 Work_ID VarChar(2),
 WorkDesc VarChar(1000),
 WorkPrice VarChar(3),
 PRIMARY KEY(Work_ID)
);

CREATE TABLE Service
(
 Order_ID VarChar(2),
 Cleaner_ID VarChar(2),
 FOREIGN KEY(Order_ID) REFERENCES WorkOrder(Order_ID),
 FOREIGN KEY(Cleaner_ID) REFERENCES Cleaner(Cleaner_ID)
);

CREATE TABLE WorkDetail
(
 Order_ID VarChar(2),
 Work_ID VarChar(2),
 FOREIGN KEY(Order_ID) REFERENCES WorkOrder(Order_ID),
 FOREIGN KEY(Work_ID) REFERENCES WorkInfo(Work_ID)
);

INSERT INTO Customer
(Customer_ID, Customer_First, Customer_Last, Address, PhoneNumber)
VALUES ('32', 'John', 'Smith', '3003 South Dr., Frisco, TX 75035', '4697736578'),
('45', 'Anthony', 'Fallon', '2808 Brownie Matter St., McKinney, TX 75013','6386785467'),
('76', 'Courtney','Burns','3838 Falling Ct. Murphy, TX 75094','3588377489');

INSERT INTO WorkOrder
(Order_ID, Customer_ID, Order_TimeStamp, OrderPrice)
VALUES ('12', '32', CURRENT_TIMESTAMP,'125'),
('64', '45', CURRENT_TIMESTAMP, '100'),
('73', '76', CURRENT_TIMESTAMP, '100');

INSERT INTO Cleaner
(Cleaner_ID, Cleaner_First, Cleaner_Last)
VALUES ('3', 'Joanna', 'Benson'),
('2', 'Isabella', 'Rosa')
('6','Varina','Matthews')
('1', 'Sham', 'Logan');

INSERT INTO WorkInfo
(Work_ID, WorkDesc, WorkPrice)
VALUES ('1', 'Dust surfaces','25'),
('2','Dust and hand wipe furniture tops','25'),
('3','Dust baseboards, chair rails, and door panels','25'),
('4','Dust ceiling fans (within reach)','25'),
('5','Vacuum carpets','25'),
('6','Dust furniture','25'),
('7','Vacuum and damp mop floors','25'),
('8','Dust blinds, window sills, and lock ledges','25'),
('9','Dust pictures frames','25'),
('10','Empty all Trash cans','25');

INSERT INTO Service
(Order_ID, Cleaner_ID)
VALUES ('12', '3'),
('12', '2'),
('12', '6'),
('64', '1'),
('73', '2');

INSERT INTO WorkDetail
(Order_ID, Work_ID)
VALUES ('12', '1'),
('12', '2'),
('12', '3'),
('12', '4'),
('12', '5'),
('64', '6'),
('64', '7'),
('64', '8'),
('64', '9'),
('73', '1'),
('73', '3'),
('73', '4'),
('73', '10');

SELECT * FROM Customer ;
SELECT * FROM WorkOrder;
SELECT * FROM Cleaner;
SELECT * FROM WorkInfo;
SELECT * FROM Service;
SELECT * FROM WorkDetail;
Back to Top