Entity Relationship Diagram – PEAR

Relational Model

Customer (CustomerID, FirstName, LastName, Street, City, State, ZipCode,

PhoneNumber, Emali)

Primary key – CustomerID

Device (DeviceID, Model, DeviceType,SerialNumber)

Primary key – DeviceID

Parts (PartNumber, Description,Cost)

Primary key – PartNumber

Labour (LabourID, Name, Cost)

Primary key – LabourID

 RepairRequest (ReferenceNumber, Decription, Date, CustomerID, SerialNumber)

Primary key – ReferenceNumber

Foreign key – CustomerID, SerialNumber

Service (ServiceID, Description, ServiceCharge, LabourID)

Primary key – ServiceID

Foreign key – LabourID

RepairService (ReferenceNumber, ServiceID)

Foreign key – ReferenceNumber, ServiceID

PartService (PartNumber, ServiceID)

Foreign key – PartNumber, ServiceID

Explanation

 The E-R diagram for the electronic repair shop contains the following entities such as
Customer, Device, Parts, Labour, RepairRequest, Service, RepairService, and
PartService.
 The E-R diagram states that a customer can have multiple repairs done in this repair
shop. So it is a relationship of one to many.

 Similarly, a device can be registered for repairs multiple times. So this relationship
between "device" and "repair request" is called a one-to-many relationship.
 Also, the diagrams state that a "device" can have multiple "services" but only one
"service" is possible for a "repair". Also, multiple "parts" can be used in one "service"
Normalization

3nf
2nf
 1nf

MYSQL Workbench

Create Database

CREATE DATABASE PEAR;

Create Table

Customer Table:

CREATE TABLE Customers(

CustomerID INT PRIMARY KEY,
FirstName VARCHAR(25),
LastName VARCHAR(25),
Stree VARCHAR(35),
City VARCHAR(35),
ZipCode INT,
PhoneNumber INT,
Email VARCHAR(25)
);
Device Table

CREATE TABLE Device(

DeviceID INT PRIMARY KEY,
Model VARCHAR(25),
DeviceType VARCHAR(25),
SerialNumber VARCHAR(25)
);

Parts Table
CREATE TABLE Parts(
PartNumber INT PRIMARY KEY,
Description VARCHAR(30),
Cost DECIMAL(5,2)
);
Labour Table
CREATE TABLE Labour(
LabourID INT PRIMARY KEY,
Name VARCHAR(30),
Cost DECIMAL(5,2)
);
RepairRequest Table
CREATE TABLE RepairRequest(
ReferenceNumber INT PRIMARY KEY,
Description VARCHAR(30),
Date VARCHAR(30),
CustomerID INT,
DeviceID INT,
FOREIGN KEY(CustomerID) REFERENCES Customers(CustomerID),
FOREIGN KEY(DeviceID) REFERENCES Device(DeviceID)
);
Service Table
CREATE TABLE Service(
ServiceID INT PRIMARY KEY,
Description VARCHAR(35),
ServiceCharge DECIMAL(5,2),
LabourID INT,
FOREIGN KEY(LabourID) REFERENCES Labour(LabourID)
);

RepairService Table
CREATE TABLE RepairService(
ReferenceNumber INT,
ServiceID INT
);
PartService Table
CREATE TABLE PartService(
PartNumber INT,
ServiceID INT,
Quantity INT
);
Insert Table Values

INSERT INTO Customers VALUES(1,'Alexander','Sheridan','Fancher

Drive','Lewisville',2111,845246235,'[email protected]');
INSERT INTO Customers VALUES(2,'Philip','Smith','Valley
Lane','Reseda',2112,784578548,'[email protected]');
INSERT INTO Customers VALUES(3,'Randall','Howell','Daffodil
Lane','Chantilly',2113,845785225,'[email protected]');
INSERT INTO Customers VALUES(4,'Don','Washington','Bates
Drive','Belmont',2114,745856965,'[email protected]');
INSERT INTO Customers VALUES(5,'Milton','Harden','Airplane
Avenue','Granby',2113,956485262,'[email protected]');
INSERT INTO Customers VALUES(6,'Henry','Moody','Jewell Road','Saint
Paul',2115,823333562,'[email protected]');
INSERT INTO Customers VALUES(7,'John','Powell','Weekley Street','San
Antonio',2116,745216254,'[email protected]');
INSERT INTO Customers VALUES(8,'Walter','Archie','Bernardo
Street','Bloomington',2113,945876586,'[email protected]');
INSERT INTO Customers VALUES(9,'Barry','Sims','Branch
Road','Chattanooga',2114,765485621,'[email protected]');
INSERT INTO Customers VALUES(10,'Gabriel','Wilson','Valley
Road','Dysart',2113,748512458,'[email protected]');
INSERT INTO Device VALUES(1001,'GalaxyA3','Samsung','S0TV12');
INSERT INTO Device VALUES(1002,'iphone14 pro','Apple','AES34LK');
INSERT INTO Device VALUES(1003,'Galaxy book2
pro','Samsung','WER34ED');
INSERT INTO Device VALUES(1004,'K6 note','Lenovo','AD42EGT');
INSERT INTO Device VALUES(1005,'RVI511','Samsung','WRDT990TY');
INSERT INTO Device VALUES(1006,'VIBE p1','Lenovo','WQD42RT5');
INSERT INTO Device VALUES(1007,'Moto g5s','Motorola','WDFR254E');
INSERT INTO Device VALUES(1008,'Galaxy i3','Samsung','WSD32T');
INSERT INTO Device VALUES(1009,'Latitude 3530','Dell','DFC34ERD');
INSERT INTO Device VALUES(1010,'EX215340','Acer','GF567H');
INSERT INTO Device VALUES(1011,'Inspiran 15','Dell','HJKL222R');
INSERT INTO parts VALUES(5861,'Screen',100.00);
INSERT INTO parts VALUES(5862,'battery',200.00);
INSERT INTO parts VALUES(5863,'panel',15.00);
INSERT INTO parts VALUES(5864,'Button',35.00);
INSERT INTO parts VALUES(5865,'screen',12.00);
INSERT INTO parts VALUES(5866,'Mother board',49.00);
INSERT INTO parts VALUES(5867,'button',39.00);
INSERT INTO parts VALUES(5868,'camera',59.00);
INSERT INTO parts VALUES(5869,'screen',95.00);
INSERT INTO parts VALUES(5870,'camera',25.00);
INSERT INTO parts VALUES(5871,'screen',105.00);
INSERT INTO parts VALUES(5872,'Battery',34.00);
INSERT INTO parts VALUES(5873,'screen',45.00);
INSERT INTO Labour VALUES(701,'Simon',50.00);
INSERT INTO Labour VALUES(702,'James',90.00);
INSERT INTO Labour VALUES(703,'Chapa',35.00);
INSERT INTO Labour VALUES(704,'Susie',75.00);
INSERT INTO Labour VALUES(705,'Williams',15.00);
INSERT INTO Labour VALUES(706,'Janice',105.00);
INSERT INTO Labour VALUES(707,'Bazan',150.00);
INSERT INTO Labour VALUES(708,'Linda',55.00);
INSERT INTO Labour VALUES(709,'Logston',85.00);
INSERT INTO Labour VALUES(710,'Breat',150.00);
INSERT INTO RepairRequest VALUES(5551,'Change Display Screen','April
2022',2,1011);
INSERT INTO RepairRequest VALUES(5552,'OS problem','JAN
2022',1,1005);
INSERT INTO RepairRequest VALUES(5553,'Change camera','MARCH
2022',7,1008);
INSERT INTO RepairRequest VALUES(5554,'Overheating','FEB
2022',5,1009);
INSERT INTO RepairRequest VALUES(5555,'Battery Dying Quickly','May
2022',3,1010);
INSERT INTO RepairRequest VALUES(5556,'Connection problems','FEB
2022',8,1007);
INSERT INTO RepairRequest VALUES(5557,'System Crash','MARCH
2022',2,1003);
INSERT INTO RepairRequest VALUES(5558,'App or phone crashing','Jan
2022',4,1006);
INSERT INTO RepairRequest VALUES(5559,'running sluggishly','MARCH
2022',1,1005);
INSERT INTO RepairRequest VALUES(5560,'unresponsive phone','MAY
2022',3,1004);
INSERT INTO Service VALUES(81,'Battery problem',50.00,702);
INSERT INTO Service VALUES(82,'Screen problem',100.00,708);
INSERT INTO Service VALUES(83,'Board problem',150.00,707);
INSERT INTO Service VALUES(84,'button promlem',85.00,705);
INSERT INTO Service VALUES(85,'mother board problem',115.00,701);
INSERT INTO Service VALUES(86,'screen problem',125.00,707);
INSERT INTO Service VALUES(87,'battery problem',190.00,709);
INSERT INTO Service VALUES(88,'camera problem',85.00,706);
INSERT INTO Service VALUES(89,'Display problem',55.00,705);
INSERT INTO Service VALUES(90,'connection problem',95.00,704);
INSERT INTO RepairService VALUES(5552,85);
INSERT INTO RepairService VALUES(5553,87);
INSERT INTO RepairService VALUES(5556,81);
INSERT INTO RepairService VALUES(5558,86);
INSERT INTO RepairService VALUES(5552,90);
INSERT INTO RepairService VALUES(5559,85);
INSERT INTO RepairService VALUES(5551,90);
INSERT INTO RepairService VALUES(5553,90);
INSERT INTO RepairService VALUES(5552,88);
INSERT INTO PartService VALUES(5862,87,2);
INSERT INTO PartService VALUES(5873,86,5);
INSERT INTO PartService VALUES(5862,89,2);
INSERT INTO PartService VALUES(5865,83,1);
INSERT INTO PartService VALUES(5866,88,1);
INSERT INTO PartService VALUES(5870,81,2);
INSERT INTO PartService VALUES(5862,88,1);
INSERT INTO PartService VALUES(5864,85,2);
INSERT INTO PartService VALUES(5863,88,2);
INSERT INTO PartService VALUES(5869,86,1);
Query1
SELECT * FROM Customers;
Query2

SELECT C.FirstName, C.LastName FROM Customers AS C

WHERE ZipCode = '2113'
OR ZipCode = '2114';

Query3
SELECT * FROM Device WHERE DeviceType
LIKE '%Samsung';
Query4
SELECT C.CustomerID, C.FirstName, C.LastName,
D.DeviceID, D.Model, D.DeviceType, D.SerialNumber
FROM Customers AS C
INNER JOIN RepairRequest AS R ON C.CustomerID = R.CustomerID
INNER JOIN Device AS D ON D.DeviceID = R.DeviceID;
Query5
SELECT * FROM Parts WHERE Description
LIKE '%Screen';

Query6

SELECT P.PartNumber, P.Description,P. Cost

FROM Parts AS P
WHERE Cost BETWEEN 10 AND 50;
Query7
SELECT R.ReferenceNumber, R.Description,
R.Date, R.CustomerID, R.DeviceID
FROM RepairRequest AS R
WHERE Date = 'MARCH 2022';
Query8

SELECT R.CustomerID, COUNT(R.ReferenceNumber) AS NumberOFRepairs

FROM RepairRequest AS R
GROUP BY CustomerID
ORDER BY CustomerID ASC;

Query9
SELECT S.ServiceID, S.Description, P.Description,
((P.Cost * PS.Quantity) + S.ServiceCharge) AS CostOFAllPartsUsed
FROM Service AS S
INNER JOIN PartService AS PS ON S.ServiceID = PS.ServiceID
INNER JOIN Parts AS P ON PS.PartNumber = P.PartNumber
GROUP BY ServiceID
ORDER BY ServiceID ASC;

Query10
SELECT R.ReferenceNumber,R.Description AS RepairDesc, S.Description AS
ServiceDesc, P.Description AS PartDesc,
((P.Cost * PS.Quantity) + S.ServiceCharge + L.Cost) AS CostOFAllPartsUsed
FROM RepairRequest AS R
INNER jOIN RepairService AS RS ON R.ReferenceNumber =
RS.ReferenceNumber
INNER JOIN Service AS S ON RS.ServiceID = S.ServiceID
INNER JOIN Labour AS L ON L.LabourID = S.LabourID
INNER JOIN PartService AS PS ON S.ServiceID = PS.ServiceID
INNER JOIN Parts AS P ON PS.PartNumber = P.PartNumber
ORDER BY ReferenceNumber ASC;