FOXCORE RETAIL(A)

DATABASE DESIGN PROJECT

​

​

Submitted By:

Sharanya Sreenivasan

AGENDA

& INDEX

FOXCORE HISTORY

Foxcore is a small retail business that sells inexpensive novelty items at music festivals and tradeshows.

​

​

​

In a hectic sales environment, not all sales were recorded by the consultants, which resulted in lost commissions and unreliable inventory estimates without a clear record of exactly what was sold.

​

​

​

With 17 full-time sales consultants hired for the third season and over 28 products available for sale at various shows across the province, Corrigan and Fox needed to track exact details about which products were sold—by whom, when, and where.

BUSINESS PROBLEM

• Sales Calculation by hand caused issues in determining commission.

​

​

​

​

​

• No data collection

​

​

​

​

• Missing insights about employee performance.

​

​

​

​

• Impact on Customer Service

• Event

​

• Venue

​

• Booth

​

• Product

​

• Salesperson

​

• Sales

ER DIAGRAM

NORMALIZED RELATIONAL DATABASE SCHEMA�

RELATIONAL SCHEMA TABLE��PRODUCT

VENUE

EVENT

BOOTH

SALESPERSON

SALES

SHIFT

SOLDVIA

SQL COMMANDS TO CREATE THE DATABASE AND INSERT VALUES�

CREATE TABLE Product

​

(ProductID INTEGER NOT NULL,

ProductName VARCHAR(50) NOT NULL,

WholesaleCost NUMERIC(7,2) NOT NULL,

MinimumSellingPrice NUMERIC(7,2) NOT NULL,

PRIMARY KEY (ProductID));

INSERT INTO Product VALUES (111,’Bubble guns’,3, 7);

INSERT INTO Product VALUES (112,’Arctic Skin Cooling Towels’,15,20.5);

INSERT INTO Product VALUES (113,’Emoji Pillows’,10.5,12);

INSERT INTO Product VALUES (114,’Heating Pads’,14.8,19.6);

INSERT INTO Product VALUES (115,’Remote Controlled Drones’,19.5,28.4);

​

SELECT * FROM Product;

​

CREATE TABLE Venue

​

(VenueID INTEGER NOT NULL,

VenueName VARCHAR (50) NOT NULL,

State VARCHAR (25) NOT NULL,

City VARCHAR (25) NOT NULL,

Address VARCHAR (50) NOT NULL,

Description VARCHAR (100) NOT NULL,

PRIMARY KEY (VenueID));

​

INSERT INTO Venue Values (12, ’Auditorium’, ’Alberta’, ’Calgary’, ’625 West Street’, ’Parking Lot’);

INSERT INTO Venue Values (13, ’Open Ground’, ’Ontario’, ’Toronto’, ’12 South Street’, ’South Gate’);

INSERT INTO Venue Values (14, ’Marcos Hall, ’Ontario’, ’Ottawa’, ’105 Marcos’, ‘No Parking’);

INSERT INTO Venue Values (15, ’Indoor Hall, ’Ontario’, ’Hamilton’, ’204 Lighty’, ’West Gate’);

INSERT INTO Venue Values (16, ’Sam’s Hall’, ’Ontario’, ’Kingston’, ’308 Sam Street’, ’Behind the main gate’);

INSERT INTO Venue Values (17, ’Open Theatre’, ’British Columbia’, ’Vancouver’, ’54 West Gibbins’, ’Parking Lot’);

​

SELECT * FROM Venue;

​

CREATE TABLE Event

(EventID INTEGER NOT NULL,

EventName VARCHAR (50) NOT NULL,

StartDate DATE NOT NULL,

EndDate DATE NOT NULL,

Description VARCHAR (100) NOT NULL,

EventType VARCHAR (25) NOT NULL,

VenueID INTEGER,

PRIMARY KEY (EventID),

FOREIGN KEY (VenueID) REFERENCES Venue(VenueID)

ON DELETE SET NULL

ON UPDATE CASCADE);

INSERT INTO Event Values (1001, ’Bubble show’, ’2021-01-01’, ’2021-01-05’, ’Latest Products‘, ’Trade show’, 12);

INSERT INTO Event Values (1002, ’Magic Show Event’, ’2021-01-02’, ’2021-01-06’, ’Electronic Products‘, ’Street Festival’, 13);

INSERT INTO Event Values (1003, ’Christmas Event’, ’2021-01-01’, ’2021-01-05’, ’Latest Products‘, ’Music Festival’, 12);

INSERT INTO Event Values (1004, ’Handicrafts Event’, ’2021-01-04’, ’2021-01-08’, ’EcoFriendly Products‘, ’Water Front Festival’, 15);

INSERT INTO Event Values (1005, ’Drink and Dance’, ’2021-01-05’, ’2021-01-10’, ’Spinning Products‘, ’Beer Festival’, 16);

INSERT INTO Event Values (1006, ’Sports Event’, ’2021-01-11’, ’2021-01-15’, ’Sports Products‘, ’Sporting Event’, 17);

INSERT INTO Event Values (1007, ’Rib Event’, ’2021-01-11’, ’2021-01-15’, ’Latest Products‘, ’Rib Fest’, 17);

 SELECT * FROM Event;

​

CREATE TABLE Booth

(BoothID INTEGER NOT NULL,

RowNo VARCHAR (1) NOT NULL,

Section INTEGER NOT NULL,

Location VARCHAR(5) NOT NULL,

EventID INTEGER,

PRIMARY KEY (BoothID),

FOREIGN KEY(EventID) REFERENCES Event(EventID)

ON DELETE SET NULL

ON UPDATE CASCADE);

INSERT INTO Booth VALUES (1, ’A’, 11, ‘A11’, 1001);

INSERT INTO Booth VALUES (2, ’B’, 21, ‘B21’, 1001);

INSERT INTO Booth VALUES (3, ’C’, 31, ‘C11’, 1002);

INSERT INTO Booth VALUES (4, ’D’, 41, ‘D41’, 1003);

INSERT INTO Booth VALUES (5, ’E’, 51, ‘E51’, 1004);

INSERT INTO Booth VALUES (6, ’F’, 61, ‘F61’, 1005);

INSERT INTO Booth VALUES (7, ’G’, 71, ‘G71’, 1006);

​

SELECT * FROM Booth;

​

CREATE TABLE Salesperson

(SalesPersonID INTEGER NOT NULL,

FirstName VARCHAR (20) NOT NULL,

LastName VARCHAR (20) NOT NULL,

State VARCHAR(25) NOT NULL,

City VARCHAR (25) NOT NULL,

Address VARCHAR (50) NOT NULL,

PhoneNumber VARCHAR(20) NOT NULL,

PRIMARY KEY (SalesPersonID));

INSERT INTO Salesperson VALUES(6001,’Alex’,’Campbell’,’Alberta’,’Calgary’,’575 W Madison’,’872-707-0002’);

INSERT INTO Salesperson VALUES(6002,’Ken’,’Shuan’,’Ontario’,’Toronto’,’125 State Street’,’333-444-5555’);

INSERT INTO Salesperson VALUES(6003,’Harry’,’Potter’,’Ontario’,’Ottawa’,’23 Ridley Park’,’444-555-6666’);

INSERT INTO Salesperson VALUES(6004,’Sam’,’Williams’,’Ontario’,’Hamilton’,’44 East State’,’555-444-6666’);

INSERT INTO Salesperson VALUES(6005,’Mike’,’Maxwell’,’Ontario’,’Kingston’,’65 Wicker Park’,’666-777-8888’);

INSERT INTO Salesperson VALUES(6006,’William’,’Wordsworth’,’British Columbia’,’Vancouver’,’200 Desmond Street’,’312-774-4331’);

​

SELECT * FROM Salesperson;

​

CREATE TABLE Shift

(ShiftID VARCHAR(5) NOT NULL,

ShiftDate DATE NOT NULL,

StartTime TIME NOT NULL,

EndTime TIME NOT NULL,

BoothID INTEGER,

SalespersonID INTEGER,

PRIMARY KEY (ShiftID),

FOREIGN KEY(BoothID) REFERENCES Booth(BoothID) ON DELETE SET NULL ON UPDATE CASCADE,

FOREIGN KEY(SalespersonID) REFERENCES Salesperson(SalespersonID) ON DELETE SET NULL ON UPDATE CASCADE);

 INSERT INTO Shift VALUES ( ‘SI100’, ’2021-01-01’, ’8:00:00’, ’11:59:00’, 1, 6001);

INSERT INTO Shift VALUES ( ‘SI101’, ’2021-01-02’, ’12:00:00’, ’15:59:00’, 1, 6002);

INSERT INTO Shift VALUES ( ‘SI102’, ’ 2021-01-03’, ’16:00:00’, ’20:00:00’, 1, 6003);

INSERT INTO Shift VALUES ( ‘SI103’, ’ 2021-01-04’, ’8:00:00’, ’11:59:00’, 2, 6004);

INSERT INTO Shift VALUES ( ‘SI104’, ’ 2021-01-05’, ’12:00:00’, ’15:59:00’, 3, 6005);

INSERT INTO Shift VALUES ( ‘SI105’, ’ 2021-01-01’, ’16:00:00’, ’20:00:00’, 4, 6006);

INSERT INTO Shift VALUES ( ‘SI106’, ’ 2021-01-06’, ’8:00:00’, ’11:59:00’, 5, 6006);

INSERT INTO Shift VALUES ( ‘SI107’, ’ 2021-01-07’, ’16:00:00’, ’20:00:00’, 6, 6001);

SELECT * FROM Shift;

INSERT INTO Shift VALUES ( ‘SI108’, ’ 2021-01-12’, ’8:00:00’, ’11:59:00’, 7, 6005);

​

CREATE TABLE Sales

(SalesID INTEGER NOT NULL,

SaleTime TIME NOT NULL,

EventID INTEGER,

ShiftID VARCHAR(5),

PRIMARY KEY(SalesID),

FOREIGN KEY(EventID) REFERENCES Event(EventID) ON DELETE SET NULL ON UPDATE CASCADE,

FOREIGN KEY (ShiftID) REFERENCES Shift(ShiftID) ON DELETE SET NULL ON UPDATE CASCADE);

INSERT INTO Sales VALUES (1111, ’11:00:00’, 1001, ’SI100’);

INSERT INTO Sales VALUES (1112, ’12:30:00’, 1001, ’SI101’);

INSERT INTO Sales VALUES (1113, ’16:00:00’, 1003, ’SI102’);

INSERT INTO Sales VALUES (1114, ’17:00:00’, 1003, ’SI102’);

INSERT INTO Sales VALUES (1115, ’11:30:00’, 1004, ’SI100’);

INSERT INTO Sales VALUES (1116, ’11:40:00’, 1004, ’SI100’);

INSERT INTO Sales VALUES (1117, ’11:45:00’, 1004, ’SI100’);

INSERT INTO Sales VALUES (1118, ’18:00:00’, 1005, ’SI102’);

INSERT INTO Sales VALUES (1119, ’18:30:00’, 1005, ’SI102’);

INSERT INTO Sales VALUES (1120, ’19:00:00’, 1005, ’SI102’);

INSERT INTO Sales VALUES (1121, ’19:30:00’, 1005, ’SI102’);

INSERT INTO Sales VALUES (1122, ’9:00:00’, 1006, ’SI100’);

INSERT INTO Sales VALUES (1123, ’10:00:00’, 1006, ’SI100’);

INSERT INTO Sales VALUES (1124, ’11:00:00’, 1006, ’SI100’);

INSERT INTO Sales VALUES (1125, ’11:38:00’, 1006, ’SI100’);

INSERT INTO Sales VALUES (1126, ’11:49:00’, 1006, ’SI100’);

SELECT * FROM Sales;

​

Create TABLE SoldVia

(ProductID INTEGER NOT NULL,

SalesID INTEGER NOT NULL,

Quantity INTEGER NOT NULL,

SellingPrice NUMERIC(7,2) NOT NULL,

PRIMARY KEY (ProductID,SalesID),

FOREIGN KEY(ProductID) REFERENCES Product(ProductID),

FOREIGN KEY(SalesID) REFERENCES Sales(SalesID));

INSERT INTO SoldVia VALUES(111,1111,10,7);

INSERT INTO SoldVia VALUES(111,1112,8,8);

INSERT INTO SoldVia VALUES(111,1113,6,8.5);

INSERT INTO SoldVia VALUES(112,1114,10,20.5);

INSERT INTO SoldVia VALUES(112,1115,5,20.5);

​

​

​

INSERT INTO SoldVia VALUES(113,1116,4,12);

INSERT INTO SoldVia VALUES(113,1117,3,12.5);

INSERT INTO SoldVia VALUES(113,1118,1,12.5);

INSERT INTO SoldVia VALUES(113,1119,2,12);

INSERT INTO SoldVia VALUES(113,1120,4,12);

INSERT INTO SoldVia VALUES(114,1121,8,19.6);

INSERT INTO SoldVia VALUES(114,1122,6,19.6);

INSERT INTO SoldVia VALUES(115,1123,7,28.4);

INSERT INTO SoldVia VALUES(115,1124,9,28.4);

INSERT INTO SoldVia VALUES(115,1125,2,28.4);

INSERT INTO SoldVia VALUES(115,1126,1,28.4);

SELECT * FROM SoldVia;

​

SQL REPORTS & PRELIMINARY ANALYSIS

Report 1

A report which gives the details of the product, by whom it was sold at which event and venue and the date it was sold.

​

Details of the product, by whom it was sold at which event and venue and the date it was sold.

​

SELECT s.ProductID,p.ProductName,s.SalesID,s.Quantity,s.SellingPrice,sa.SaleTime,sa.ShiftID,sh.ShiftDate,

sh.BoothID,sh.SalespersonID,sp.FirstName,sp.LastName,sa.EventID,e.EventName,e.EventType,

e.VenueID,v.VenueName

FROM SoldVia s,Product p,Sales sa,Shift sh,Salesperson sp,Event e,Venue v

WHERE s.ProductID=p.ProductID AND

s.SalesID=sa.SalesID AND

sa.EventID=e.EventID AND

sa.ShiftID=sh.ShiftID AND

e.VenueID=v.VenueID AND

sh.SalespersonID=sp.SalespersonID;

​

PRELIMINARY ANALYSIS & RECOMMENDATIONS

• Remote Controlled Drones sold at sporting event had the highest selling price.
• The contribution of Alex Campbell was maximum towards generating revenue.
• Fox Core should try to allocate more sales personal at venues of Indoor Hall and Sam’s Hall to sell their products and increase sales.
• They should include more add-ons with Bubble Guns as that is the least expensive product and not profitable like Remote Controlled Drones which is a high-end product.
• All sales are from Booth 1 with 3 shifts. Hence more training should be given to salespersons at other Booths.

​

SQL REPORTS & PRELIMINARY ANALYSIS

Report 2

A report which shows total revenue by salesperson and product.

​

Details of the product, by whom it was sold at which event and venue and the date it was sold.

​

SELECT sh.SalespersonID,sp.FirstName,sp.LastName,s.ProductID,p.ProductName

SUM(s.Quantity*s.SellingPrice) AS Revenue

FROM Shift sh, Salesperson sp, SoldVia s, Sales sa,Product p

WHERE sh.SalespersonID=sp.SalespersonID AND

sa.ShiftID=sh.ShiftID AND

s.SalesID=sa.SalesID AND

s.ProductID=p.ProductID

GROUP BY sh.SalespersonID, sp.FirstName,sp.LastName;

​

SQL REPORTS & PRELIMINARY ANALYSIS

SQL Report 3

Generate a report that shows total revenue by product, total cost and Profit

​

Details of the product, by whom it was sold at which event and venue and the date it was sold.

​

SELECT s.ProductID,p.ProductName,

SUM(s.Quantity*s.SellingPrice) AS Revenue,

SUM(p.WholesaleCost*s.Quantity) AS TotalCost,

SUM(s.Quantity*s.SellingPrice) - SUM(p.WholesaleCost*s.Quantity) AS Profit

FROM SoldVia s,Product p

WHERE s.ProductID=p.ProductID

GROUP BY s.ProductID,p.ProductName;

PRELIMINARY ANALYSIS

• Emoji Pillows are least profitable. Review the offer, Sales efforts.
• Remote Controlled Drones have the highest profits. Keep Selling!
• Reduce Cost of products by sourcing from the right vendor.