Database
Design & Query
In this project, designed and implemented a relational database for Pro App, a sharing economy platform, which facilitates customers in outsourcing tasks and projects to local tradespeople and suppliers. My task involved identifying key entities and relationships, creating an ERdiagram, and developing the database schema. Also implemented SQL queries to address key business questions and extract insights that help improve platform operations and customer satisfaction.
The database was designed to support core functionalities such as task requests, bids from tradespeople, membership management, certification tracking, and supply handling. Additionally, generated and analyzed various business metrics, such as transaction fees, membership popularity, customer satisfaction, and spending behavior, to aid the COO in decision-making. Visit full project here.
Skills:
Identifying database entities and their relationship
Creating ERD
SQL query development
Problem solving & Data analysis
ERDiagram
Entities:
CUSTOMER
TRADESPERSON
SUPPLIER
TASK
BID
PROJECT
SUPPLY
MEMBERSHIP
PROVIDER
CERTIFICATE
ASSOCIATIVE ENTITIES
TASK_TRADESPERSON_TRANSACTION
PROJECT_SUPPLIER_TRANSACTION
SUPPLIER_SUPPLY_TRANSACTION
TRADESPERSON_MEMBERSHIP
SUPPLIER_MEMBERSHIP
TRADESPERSON_CERTIFICATE
SQL Query Development
Example of creating database:
create table TASK_TRADESPERSON_TRANSACTION (
Task_Id CHAR(8),
TID CHAR(8),
Start_Date DATE NOT NULL,
Completion_Date DATE,
Transaction_Status CHAR(20) NOT NULL,
Transaction_Amount DECIMAL(10, 2) NOT NULL,
Transaction_Fee DECIMAL(10, 2) NOT NULL,
Tradesperson_rating INT CHECK (Tradesperson_rating BETWEEN 1 AND 5),
Customer_rating INT CHECK (Customer_rating BETWEEN 1 AND 5),
FOREIGN KEY (Task_Id) REFERENCES TASK(Task_Id),
FOREIGN KEY (TID) REFERENCES TRADESPERSON(TID),
PRIMARY KEY (Task_Id, TID));
Business insights through SQL queries:
What is the average transaction fee per completed task?
SELECT AVG(Transaction_Fee) AS Avg_Transaction_Fee
FROM TASK_TRADESPERSON_TRANSACTION
WHERE Transaction_Status = 'Completed';
Which membership type is most popular among tradespeople, and what is the average membership fee paid?
SELECT Membership_Type, COUNT(*) AS Num_Tradespeople, AVG(Membership_Fee) AS Avg_Membership_Fee
FROM TRADESPERSON_MEMBERSHIP
GROUP BY Membership_Type
ORDER BY Num_Tradespeople DESC;
How many suppliers are enrolled in each membership type?
SELECT Membership_Type, COUNT(*) AS Num_Suppliers
FROM SUPPLIER_MEMBERSHIP
GROUP BY Membership_Type;
What is the total amount spent by customers on completed projects?
SELECT SUM(Transaction_Amount) AS Total_Amount_Spent
FROM PROJECT_SUPPLIER_TRANSACTION
WHERE Transaction_Status = 'Completed';
How many tradespeople have obtained certifications, and which certification is the most common?
SELECT c.Certificate_Name, COUNT(*) AS Num_Tradespeople
FROM TRADESPERSON_CERTIFICATE tc
JOIN CERTIFICATE c ON tc.Certificate_Id = c.Certificate_Id
GROUP BY c.Certificate_Name
ORDER BY Num_Tradespeople DESC;
Which tasks have the highest budget and corresponding quote fees?
SELECT Task_Id, Description, Budget, Quote_fee
FROM TASK
ORDER BY Budget DESC
LIMIT 10;
What is the customer satisfaction rating based on completed tasks?
SELECT AVG(Customer_rating) AS Avg_Customer_Rating
FROM TASK_TRADESPERSON_TRANSACTION
WHERE Transaction_Status = 'Completed';
Which tradespeople have completed the most tasks, and what is their average transaction fee?
SELECT t.TID, t.Name, COUNT(ttt.Task_Id) AS Num_Tasks_Completed, AVG(ttt.Transaction_Fee) AS Avg_Transaction_Fee
FROM TRADESPERSON t
JOIN TASK_TRADESPERSON_TRANSACTION ttt ON t.TID = ttt.TID
WHERE ttt.Transaction_Status = 'Completed'
GROUP BY t.TID, t.Name
ORDER BY Num_Tasks_Completed DESC;
How many tasks and projects are requested but still pending completion?
SELECT (SELECT COUNT(*) FROM TASK_TRADESPERSON_TRANSACTION WHERE Transaction_Status = 'Pending') AS Pending_Tasks,
(SELECT COUNT(*) FROM PROJECT_SUPPLIER_TRANSACTION WHERE Transaction_Status = 'Pending') AS Pending_Projects;
