Professor: Dr.
Abbas Bassem
Students:
Mohammad Mattar
Mohammad Ajame
Basim El Hage Sleiman
Final Amount Date Inv. ID Comment F Date FID IsAvailable E Time S Time
Discount Invoice Feedback Schedule Days of
the week
Vat M M
M
es
Assigned to
Re
av ciev
Le es
1 1 1
FirstName LastName
Ge 1
n
BID
er
at Name
e 1
C.Address CID App.Id App Date Notes
Phone No 1
Barber Years Ex
Customer
M
Appointment M Performs
1 ts
Se
1 Make
FirstName s Phone No Hire Date
Name Gender M Status 1
M Paid
LastName For
M
includes
description duration amount PID
Reorder level N
SID Service Payment
Inventory
ER Diagram:
price N Sname Method date
Supplier IID 1
ins
Conta
rest
oc M
ks
QIS Last restock
Date M
Product Pname
price brand PID
Schema:
Appointment
App ID App Date Status BID CID
Customer
CID Fname Lname Gender Phone No Address
Barber
BID fname lname Phone no Hire Date Years Ex
Schedule
Days
IsAvailable S Time E Time of the week BID
Payment
Date Method Amount CID
Service
Description Price Sname
Product
Pname Brand price IID
Inventory
Reader Level Supplier LRD QIS
Feedback
FID Fdate Comments CID BID
Invoice
InvID Final Amount Date Disc VAT App ID
Contains
PID
Includes
App ID SID
Method
PID Method
1. Customer
Represents a person receiving services from the barber-
shop.
Attributes: CID, Name, LastName, Gender, Address,
PhoneNo.
2. Appointment
A scheduled meeting between a customer and a barber for
one or more services.
Attributes: App.ID, App.Date, Notes, Status.
Related to: Customer (makes), Barber (performs), Service
(includes), Payment (paid for).
3. Barber
A person who performs services (e.g., haircut, beard trim).
Attributes: BID, Name, FirstName, LastName, YearsExp,
PhoneNo, HireDate.
Related to: Appointment, Feedback, Schedule.
4. Schedule
Indicates the availability of barbers.
Attributes: IsAvailable, STime, ETime, DaysOfTheWeek.
5. Service
Represents the types of services offered (e.g., haircut,
shave).
Attributes: SID, Sname, Description, Duration, Price.
Related to: Appointment (includes), Product (uses).
6. Product
Items used during services (e.g., shampoo, dye).
Attributes: PID, Pname, Price, Brand.
Related to: Inventory (restocks), Service (used in).
7. Inventory
Tracks stock and supply of products.
Attributes: IID, QuantityInStock (QIS), LastRestock-
Date, ReorderLevel, Supplier.
Related to: Product (restocks).
8. Invoice
A billing document generated for services provided.
Attributes: Inv.ID, Date, VAT, Discount, FinalAmount.
Related to: Customer (leaves), Appointment (generates).
9. Payment
A record of a financial transaction for services.
Attributes: PID, Method (Cash, Credit, etc.), Date,
Amount.
Related to: Appointment (paid for).
10. Feedback
Customer comments or reviews about the barber or service.
Attributes: FID, Comment, FDate.
Related to: Customer (leaves), Barber (receives).
1. Customer — Makes — Appointment
Cardinality: 1:M
A customer can make multiple appointments.
Each appointment is made by one customer.
�� 2. Appointment — Performs — Barber
Cardinality: M:1
Each appointment is performed by one barber.
A barber can perform many appointments.
�� 3. Appointment — Includes — Service
Cardinality: M:N
An appointment can include multiple services.
A service can be included in multiple appointments.
Usually implemented through a junction table (e.g., Appointment_Ser-
vice(AppID, SID)).
�� Appointment — Paid For — Payment
Cardinality: 1:M
An appointment can have multiple partial payments.
Each payment is tied to one appointment.
�� Customer — Leaves — Feedback
Cardinality: 1:M
A customer can leave multiple feedback entries.
Each feedback belongs to one customer.
�� Barber — Receives — Feedback
Cardinality: 1:M
A barber can receive many feedbacks.
Each feedback is about one barber.
Customer — Generates — Invoice
Cardinality: 1:M
A customer can have multiple invoices.
Each invoice belongs to one customer.
Inventory — Restocks — Product
Cardinality: 1:M
One inventory entry may restock multiple products.
Each product belongs to one inventory restock event.
Service — Uses — Product
Cardinality: M:N
A service may require multiple products (e.g., haircut uses gel
and spray).
A product can be used in many services.
Typically implemented as:
Service_Product(SID, PID, QuantityUsed).
Barber — Assigned to — Schedule
Cardinality: 1:M
Each barber can have multiple scheduled shifts.
Each schedule is for one barber.
Appointment — Generates — Invoice
Cardinality: 1:1
Each appointment generates one invoice.
Each invoice belongs to a specific appointment.
CREATE TABLE Customer (
CID INT NOT NULL,
Fname VARCHAR(50) NOT NULL,
Lname VARCHAR(50) NOT NULL,
Gender CHAR(1),
PhoneNo VARCHAR(15),
Address VARCHAR(100),
PRIMARY KEY (CID),
UNIQUE (PhoneNo)
);
CREATE TABLE Barber (
BID INT NOT NULL,
Fname VARCHAR(50) NOT NULL,
Lname VARCHAR(50) NOT NULL,
PhoneNo VARCHAR(15),
HireDate DATE,
YearsEx INT,
PRIMARY KEY (BID),
UNIQUE (PhoneNo)
);
CREATE TABLE Schedule (
SID INT NOT NULL,
IsAvailable BIT,
STime TIME,
ETime TIME,
DaysOfWeek VARCHAR(20),
BID INT,
PRIMARY KEY (SID),
FOREIGN KEY (BID) REFERENCES Bar-
ber(BID)
ON DELETE SET NULL
ON UPDATE CASCADE
);
CREATE TABLE Appointment (
AppID INT NOT NULL,
AppDate DATE,
Notes VARCHAR(255),
Status VARCHAR(20),
BID INT,
CID INT,
PRIMARY KEY (AppID),
FOREIGN KEY (BID) REFERENCES Barber(BID)
ON DELETE SET NULL
ON UPDATE CASCADE,
FOREIGN KEY (CID) REFERENCES Customer(CID)
ON DELETE SET NULL
ON UPDATE CASCADE
);
CREATE TABLE Payment (
PID INT NOT NULL,
Date DATE,
Method VARCHAR(20),
Amount DECIMAL(10,2),
AppID INT,
CID INT,
PRIMARY KEY (PID),
FOREIGN KEY (AppID) REFERENCES Appointment(Ap-
pID)
ON DELETE SET NULL
ON UPDATE CASCADE,
FOREIGN KEY (CID) REFERENCES Customer(CID)
ON DELETE SET NULL
ON UPDATE CASCADE
);
CREATE TABLE Service (
SID INT NOT NULL,
Sname VARCHAR(50) NOT NULL,
Description VARCHAR(255),
Price DECIMAL(10,2),
PRIMARY KEY (SID),
UNIQUE (Sname)
);
CREATE TABLE Product (
PID INT NOT NULL,
Pname VARCHAR(50),
Brand VARCHAR(50),
Price DECIMAL(10,2),
IID INT,
PRIMARY KEY (PID),
FOREIGN KEY (IID) REFERENCES Inventory(IID)
ON DELETE SET NULL
ON UPDATE CASCADE
);
CREATE TABLE Inventory (
IID INT NOT NULL,
ReaderLevel INT CHECK (ReaderLevel >= 0),
Supplier VARCHAR(100),
LRD DATE,
QIS INT CHECK (QIS >= 0),
PRIMARY KEY (IID)
);
CREATE TABLE Feedback (
FID INT NOT NULL,
Fdate DATE,
Comments VARCHAR(255),
CID INT,
BID INT,
PRIMARY KEY (FID),
FOREIGN KEY (CID) REFERENCES Custom-
er(CID)
ON DELETE SET NULL
ON UPDATE CASCADE,
FOREIGN KEY (BID) REFERENCES Barber(BID)
ON DELETE SET NULL
ON UPDATE CASCADE
);
CREATE TABLE Invoice (
InvID INT NOT NULL,
FinalAmount DECIMAL(10,2),
Date DATE,
Disc DECIMAL(5,2),
VAT DECIMAL(5,2),
AppID INT,
PRIMARY KEY (InvID),
FOREIGN KEY (AppID) REFERENCES Appointment(AppID)
ON DELETE SET NULL
ON UPDATE CASCADE
);
CREATE TABLE ServiceProduct (
SID INT NOT NULL,
PID INT NOT NULL,
PRIMARY KEY (SID, PID),
FOREIGN KEY (SID) REFERENCES Service(SID)
ON DELETE SET NULL
ON UPDATE CASCADE,
FOREIGN KEY (PID) REFERENCES Product(PID)
ON DELETE SET NULL
ON UPDATE CASCADE
);
CREATE TABLE Includes (
AppID INT NOT NULL,
SID INT NOT NULL,
PRIMARY KEY (AppID, SID),
FOREIGN KEY (AppID) REFERENCES Appointment(AppID)
ON DELETE SET NULL
ON UPDATE CASCADE,
FOREIGN KEY (SID) REFERENCES Service(SID)
ON DELETE SET NULL
ON UPDATE CASCADE
);
CREATE TABLE Method (
PID INT NOT NULL,
Method VARCHAR(20) NOT NULL,
PRIMARY KEY (PID, Method),
FOREIGN KEY (PID) REFERENCES Payment(PID)
ON DELETE SET NULL
ON UPDATE CASCADE
);
INSERT INTO Barber VALUES
(1, 'Fahad', 'Alotaibi', '0501234567', '2020-05-01', 5),
(2, 'Majid', 'Alzahrani', '0509876543', '2018-11-15', 7),
(3, 'Khalid', 'Alharbi', '0504567890', '2019-06-20', 6),
(4, 'Ibrahim', 'Alshammari', '0502345678', '2021-01-10',
4),
(5, 'Saad', 'Aldosari', '0503456789', '2022-09-01', 2);
INSERT INTO Customer VALUES
(1, 'Ali', 'Hassan', 'M', '0551234567', 'Riyadh'),
(2, 'Sara', 'Ahmed', 'F', '0559876543', 'Jeddah'),
(3, 'Omar', 'Salem', 'M', '0551122334', 'Dammam'),
(4, 'Lina', 'Khalid', 'F', '0556677889', 'Makkah'),
(5, 'Yousef', 'Ali', 'M', '0554433221', 'Tabuk'),
(6, 'Nora', 'Saad', 'F', '0559988776', 'Khobar');
(7, 'Adam', 'Khan', 'M', '5556667777', '741 Birch Rd')
(8, 'Emma', 'Green', 'F', '5558889999', '852 Willow Blvd');
INSERT INTO Schedule VALUES
(1, 1, '09:00:00', '17:00:00', 'Sun-Thu', 1),
(2, 1, '10:00:00', '18:00:00', 'Mon-Fri', 2),
(3, 1, '08:00:00', '16:00:00', 'Sat-Wed', 3),
(4, 0, '12:00:00', '20:00:00', 'Fri-Sat', 4),
(5, 1, '11:00:00', '19:00:00', 'Sun-Thu', 5);
INSERT INTO Service VALUES
(1, 'Haircut', 'Basic men''s haircut', 30.00),
(2, 'Beard Trim', 'Professional beard shaping', 20.00),
(3, 'Shave', 'Hot towel shave', 25.00),
(4, 'Hair Wash', 'Hair wash and massage', 15.00),
(5, 'Hair Color', 'Basic coloring', 50.00),
(6, 'Facial', 'Men''s facial treatment', 40.00);
INSERT INTO Inventory VALUES
(1, 10, 'Barber Supplies Co.', '2025-05-01', 100),
(2, 5, 'Beauty Source', '2025-04-25', 50),
(3, 7, 'Men''s Care Ltd.', '2025-05-10', 70),
(4, 8, 'Shaving Essentials', '2025-05-15', 60),
(5, 6, 'Hair Pro', '2025-04-30', 40);
INSERT INTO Product VALUES
(1, 'Shampoo', 'Head&Shoulders', 15.00, 1),
(2, 'Hair Gel', 'Gatsby', 10.00, 2),
(3, 'Beard Oil', 'Viking', 20.00, 3),
(4, 'Razor', 'Gillette', 25.00, 4),
(5, 'Hair Color Kit', 'L''Oréal', 40.00, 5);
INSERT INTO Feedback VALUES (1, '2025-05-10', 'Great ser-
vice!', 1, 1);
INSERT INTO Feedback VALUES (2, '2025-05-11', 'Very satisfied',
2, 2);
INSERT INTO Feedback VALUES (3, '2025-05-12', 'Did not
attend', 3, 3);
INSERT INTO Feedback VALUES (4, '2025-05-13', 'Excellent
shave', 4, 4);
INSERT INTO Feedback VALUES (5, '2025-05-14', 'Cancelled but
okay', 5, 5);
INSERT INTO Invoice VALUES (1, 30.0, '2025-05-10', 0.0, 5.0,
1);
INSERT INTO Invoice VALUES (2, 50.0, '2025-05-11', 0.0, 5.0,
2);
INSERT INTO Invoice VALUES (3, 30.0, '2025-05-12', 0.0, 5.0,
3);
INSERT INTO Invoice VALUES (4, 55.0, '2025-05-13', 5.0, 5.0,
4);
INSERT INTO Invoice VALUES (5, 60.0, '2025-05-14', 10.0, 5.0,
5);
INSERT INTO Invoice (InvID, FinalAmount, Date, Disc, VAT,
AppID)
VALUES
(6, 25.00, '2025-05-16', 0.00, 0.00, 6),
(7, 30.00, '2025-05-17', 0.00, 0.00, 7),
(8, 20.00, '2025-05-18', 0.00, 0.00, 8);
INSERT INTO Method VALUES (1, 'Cash');
INSERT INTO Method VALUES (1, 'Card');
INSERT INTO Method VALUES (2, 'Cash');
INSERT INTO Method VALUES (3, 'Mobile Pay');
INSERT INTO Method VALUES (4, 'Card');
INSERT INTO Method VALUES (5, 'Cash');
INSERT INTO Method VALUES (5, 'Online');
INSERT INTO Method VALUES (2, 'Online');
INSERT INTO Includes VALUES (1, 1);
INSERT INTO Includes VALUES (1, 2);
INSERT INTO Includes VALUES (2, 3);
INSERT INTO Includes VALUES (3, 4);
INSERT INTO Includes VALUES (4, 5);
INSERT INTO Includes VALUES (5, 1);
INSERT INTO Includes VALUES (6, 2);
INSERT INTO Includes VALUES (7, 3);
INSERT INTO Payment VALUES (1, '2025-05-10',
'Cash', 30.0, 1, 1);
INSERT INTO Payment VALUES (2, '2025-05-11',
'Card', 50.0, 2, 2);
INSERT INTO Payment VALUES (3, '2025-05-12',
'Cash', 30.0, 3, 3);
INSERT INTO Payment VALUES (4, '2025-05-13',
'Card', 55.0, 4, 4);
INSERT INTO Payment VALUES (5, '2025-05-14',
'Online', 60.0, 5, 5);
INSERT INTO Appointment VALUES (1, '2025-05-11',
'Basic haircut', 'Completed', 1, 1);
INSERT INTO Appointment VALUES (2, '2025-05-12',
'Shave and trim', 'Completed', 2, 2);
INSERT INTO Appointment VALUES (3, '2025-05-13',
'Hair dye', 'Cancelled', 3, 3);
INSERT INTO Appointment VALUES (4, '2025-05-14',
'Beard shaping', 'Completed', 4, 4);
INSERT INTO Appointment VALUES (5, '2025-05-15',
'Luxury treatment', 'Completed', 5, 5);
INSERT INTO Appointment VALUES (6, '2025-05-16',
'Style refresh', 'No Show', 1, 2);
INSERT INTO Appointment VALUES (7, '2025-05-17',
'Scalp massage', 'Completed', 2, 3);
INSERT INTO Appointment VALUES (8, '2025-05-18',
'Haircut & styling', 'Completed', 3, 4);
INSERT INTO Appointment VALUES (9, '2025-05-19',
'Beard trim', 'Completed', 4, 1);
INSERT INTO Appointment VALUES (10, '2025-05-20',
'Eyebrow trim', 'Completed', 5, 5);
INSERT INTO Appointment (AppID, AppDate, Notes,
Status, BID, CID)
VALUES
(11, '2025-05-21', 'Classic haircut', 'Completed', 1, 6),
(12, '2025-05-22', 'Beard grooming', 'Completed', 2, 7),
(13, '2025-05-23', 'Hair treatment', 'Scheduled', 3, 8);
INSERT INTO ServiceProduct VALUES (1, 1);
INSERT INTO ServiceProduct VALUES (2, 2);
INSERT INTO ServiceProduct VALUES (3, 3);
INSERT INTO ServiceProduct VALUES (4, 4);
INSERT INTO ServiceProduct VALUES (5, 5);
1. ۛ Show total payments made by each customer
2. ៓ Find the most recent appointment for each customer
3. ➘ Find the most popular service (used in most appointments)
4. ഄ List customers who paid with more than one method
5. ݒCalculate average invoice amount per day
6. ۛ Show each appointment and the number of services included (subquery
in SELECT)
7. ⹊⋔ Customers who spent more than the average of all customers
