Design And Implementation Of A Product Monitoring Service For A Retail Outlet
The PC Land Case Study
Section 1: E-R model
Normalization
First NORMAL FORM:
|
sale date |
sale number |
Sales-id |
Sales person |
Customer number |
customer name |
product type |
unit price |
sale quantity |
line total |
|
|
7-Aug-17 |
57435 |
633 |
James Hart |
238790 |
Peter Minter |
T5060 |
Laptop |
500 |
2 |
1000 |
|
7-Aug-17 |
57435 |
633 |
James Hart |
238790 |
Peter Minter |
PT42 |
printer |
250 |
1 |
250 |
|
7-Aug-17 |
57435 |
633 |
James Hart |
238790 |
Peter Minter |
QZE248 |
printer |
200 |
1 |
200 |
SECOND NORMAL FORM:
|
sales table |
|||||
|
sale date |
sale number |
Sales-id |
Sales person |
Customer number |
customer name |
|
7-Aug-17 |
57435 |
633 |
James Hart |
238790 |
Peter Minter |
|
product table |
|||||
|
model number |
product type |
unit price |
|||
|
T5060 |
Laptop |
500 |
|||
|
PT42 |
printer |
250 |
|||
|
QZE248 |
printer |
200 |
|||
|
daily sales table |
|||||
|
sale date |
model number |
sale quantity |
total |
||
|
7-Aug-17 |
T5060 |
2 |
1000 |
||
|
7-Aug-17 |
PT42 |
1 |
250 |
||
|
7-Aug-17 |
QZE248 |
1 |
200 |
THIRD NORMAL FORM:
|
sales table |
|||
|
sale date |
sale number |
Sales-id |
Customer number |
|
7-Aug-17 |
57435 |
633 |
238790 |
|
sales person |
|||
|
Sales-id |
Sales person |
||
|
633 |
James Hart |
||
|
customer table |
|||
|
Customer number |
customer name |
||
|
238790 |
Peter Minter |
||
|
product table |
|||
|
model number |
product type |
unit price |
|
|
T5060 |
Laptop |
500 |
|
|
PT42 |
printer |
250 |
|
|
QZE248 |
printer |
200 |
|
|
daily sales table |
|||
|
sale date |
model number |
sale quantity |
total |
|
7-Aug-17 |
T5060 |
2 |
1000 |
|
7-Aug-17 |
PT42 |
1 |
250 |
|
7-Aug-17 |
QZE248 |
1 |
200 |
E-R diagram:
Section 2: CREATE TABLE QUERY
categories:
CREATE TABLE “categories”
( “catID” NUMBER,
“catType” VARCHAR2(20) NOT NULL ENABLE,
CONSTRAINT “categories_PK” PRIMARY KEY (“catID”) ENABLE )
employees:
CREATE TABLE “employees”
( “empID” NUMBER NOT NULL ENABLE,
“empName” VARCHAR2(30) NOT NULL ENABLE,
“address” VARCHAR2(60) NOT NULL ENABLE,
“phoneNo” NUMBER NOT NULL ENABLE,
“DOB” DATE NOT NULL ENABLE,
“position” VARCHAR2(10) NOT NULL ENABLE,
CONSTRAINT “employees_PK” PRIMARY KEY (“empID”) ENABLE )
products:
CREATE TABLE “products”
( “proID” NUMBER NOT NULL ENABLE,
“catID” NUMBER NOT NULL ENABLE,
“modelNumber” VARCHAR2(15) NOT NULL ENABLE,
“maker” VARCHAR2(15) NOT NULL ENABLE,
“processorSpeed” NUMBER,
“memoryCapacity” NUMBER,
“sizeHDD” NUMBER,
“speedDVD” NUMBER,
“screenSize” NUMBER,
“color” VARCHAR2(15) NOT NULL ENABLE,
“cost” NUMBER NOT NULL ENABLE,
CONSTRAINT “products_pk” PRIMARY KEY (“proID”) ENABLE,
CONSTRAINT “products_fk” FOREIGN KEY (“catID”)
REFERENCES “categories” (“catID”) ON DELETE CASCADE ENABLE )
sales:
CREATE TABLE “sales”
( “invoiceNo” NUMBER,
“saleDate” DATE NOT NULL ENABLE,
“empID” NUMBER NOT NULL ENABLE,
“customerName” VARCHAR2(50) NOT NULL ENABLE,
“cost” NUMBER NOT NULL ENABLE,
CONSTRAINT “sales_PK” PRIMARY KEY (“invoiceNo”) ENABLE )
slaesProduct:
CREATE TABLE “salesProduct”
( “slNo” NUMBER NOT NULL ENABLE,
“invoiceNo” NUMBER NOT NULL ENABLE,
“modelNumber” VARCHAR2(15) NOT NULL ENABLE,
CONSTRAINT “salesProduct_PK” PRIMARY KEY (“slNo”) ENABLE,
CONSTRAINT “SALESPRODUCT_FK” FOREIGN KEY (“invoiceNo”)
REFERENCES “sales” (“invoiceNo”) ON DELETE CASCADE ENABLE )
INSERT QUERY
categories:
INSERT INTO “SYSTEM”.”categories” (“catID”,”catType”) VALUES (1,’Personal Computer”‘);
INSERT INTO “SYSTEM”.”categories” (“catID”,”catType”) VALUES (2,’Laptop’);
INSERT INTO “SYSTEM”.”categories” (“catID”,”catType”) VALUES (3,’Printer’);
INSERT INTO “SYSTEM”.”categories” (“catID”,”catType”) VALUES (4,’USB drives’);
INSERT INTO “SYSTEM”.”categories” (“catID”,”catType”) VALUES (5,’web cams’);
INSERT INTO “SYSTEM”.”categories” (“catID”,”catType”) VALUES (6,’cartridges’);
INSERT INTO “SYSTEM”.”categories” (“catID”,”catType”) VALUES (7,’cables’);
INSERT INTO “SYSTEM”.”categories” (“catID”,”catType”) VALUES (8,’plugs’);
INSERT INTO “SYSTEM”.”categories” (“catID”,”catType”) VALUES (9,’adapters’);
employees:
INSERT INTO “SYSTEM”.”employees” (“empID”,”empName”,”address”,”phoneNo”,”DOB”,”position”) VALUES (1,’Dominic Jenkins’,’93 Coast Rd KIRKLINGTON DL8 8GS’,7883012001,’12-DEC-97′,’Manager’);
INSERT INTO “SYSTEM”.”employees” (“empID”,”empName”,”address”,”phoneNo”,”DOB”,”position”) VALUES (2,’Christopher Carey’,’68 Wern Ddu Lane LUSS G83 6ZS’,7056100038,’05-MAR-84′,’Manager’);
INSERT INTO “SYSTEM”.”employees” (“empID”,”empName”,”address”,”phoneNo”,”DOB”,”position”) VALUES (3,’Alfie Connor’,’46 Sandyhill Rd GABLON IV25 6SP’,7084413919,’22-JAN-56′,’staff’);
INSERT INTO “SYSTEM”.”employees” (“empID”,”empName”,”address”,”phoneNo”,”DOB”,”position”) VALUES (4,’Elliot Atkinson’,’36 Jubilee Drive CAWTON YO6 4XP’,7703964362,’24-JAN-58′,’staff’);
INSERT INTO “SYSTEM”.”employees” (“empID”,”empName”,”address”,”phoneNo”,”DOB”,”position”) VALUES (5,’Phoebe Hale’,’20 Hendford Hill MOSELEY WR2 1NQ’,7730146399,’06-JUN-37′,’staff’);
INSERT INTO “SYSTEM”.”employees” (“empID”,”empName”,”address”,”phoneNo”,”DOB”,”position”) VALUES (6,’Imogen McKenzie’,’78 Grenoble Road BRIDGE OF BREWLANDS PH11 0ZB’,7885040773,’02-DEC-39′,’staff’);
INSERT INTO “SYSTEM”.”employees” (“empID”,”empName”,”address”,”phoneNo”,”DOB”,”position”) VALUES (7,’William
Tasks
INSERT INTO “SYSTEM”.”products” (“proID”,”catID”,”modelNumber”,”maker”,”processorSpeed”,”memoryCapacity”,”sizeHDD”,”speedDVD”,”screenSize”,”color”,”cost”) VALUES (1,1,’A315-31′,’Acer’,200,4,500,250,15,’black’,298);
INSERT INTO “SYSTEM”.”products” (“proID”,”catID”,”modelNumber”,”maker”,”processorSpeed”,”memoryCapacity”,”sizeHDD”,”speedDVD”,”screenSize”,”color”,”cost”) VALUES (2,1,’E5 – 575′,’Acer’,250,8,1024,200,15.6,’black’,350);
INSERT INTO “SYSTEM”.”products” (“proID”,”catID”,”modelNumber”,”maker”,”processorSpeed”,”memoryCapacity”,”sizeHDD”,”speedDVD”,”screenSize”,”color”,”cost”) VALUES (3,3,’HP DeskJet 3835′,’HP’,”,”,”,”,”,’black’,150);
INSERT INTO “SYSTEM”.”products” (“proID”,”catID”,”modelNumber”,”maker”,”processorSpeed”,”memoryCapacity”,”sizeHDD”,”speedDVD”,”screenSize”,”color”,”cost”) VALUES (4,2,’AN515-51′,’Acer’,500,8,1024,200,15.6,’red’,500);
INSERT INTO “SYSTEM”.”products” (“proID”,”catID”,”modelNumber”,”maker”,”processorSpeed”,”memoryCapacity”,”sizeHDD”,”speedDVD”,”screenSize”,”color”,”cost”) VALUES (5,4,’Cruzer Blade’,’Sandisk’,”,”,16,”,”,’red’,20);
INSERT INTO “SYSTEM”.”products” (“proID”,”catID”,”modelNumber”,”maker”,”processorSpeed”,”memoryCapacity”,”sizeHDD”,”speedDVD”,”screenSize”,”color”,”cost”) VALUES (6,6,’678′,’HP’,”,”,”,”,”,’tricolor’,99);
INSERT INTO “SYSTEM”.”sales” (“invoiceNo”,”saleDate”,”empID”,”customerName”,”cost”) VALUES (1,’01-JAN-18′,4,’Abigail Kent’,448);
INSERT INTO “SYSTEM”.”sales” (“invoiceNo”,”saleDate”,”empID”,”customerName”,”cost”) VALUES (2,’01-JAN-18′,6,’Christopher Marsden’,567);
INSERT INTO “SYSTEM”.”sales” (“invoiceNo”,”saleDate”,”empID”,”customerName”,”cost”) VALUES (3,’01-JAN-18′,6,’George Taylor’,469);
INSERT INTO “SYSTEM”.”sales” (“invoiceNo”,”saleDate”,”empID”,”customerName”,”cost”) VALUES (4,’01-JAN-18′,7,’Madeleine Young’,769);
INSERT INTO “SYSTEM”.”sales” (“invoiceNo”,”saleDate”,”empID”,”customerName”,”cost”) VALUES (5,’02-JAN-18′,3,’Charles Hall’,150);
INSERT INTO “SYSTEM”.”” (“slNo”,”invoiceNo”,”modelNumber”) VALUES (1,1,’A315-31′);
INSERT INTO “SYSTEM”.”” (“slNo”,”invoiceNo”,”modelNumber”) VALUES (2,1,’HP DeskJet 3835′);
INSERT INTO “SYSTEM”.”” (“slNo”,”invoiceNo”,”modelNumber”) VALUES (3,2,’A315-31′);
INSERT INTO “SYSTEM”.”” (“slNo”,”invoiceNo”,”modelNumber”) VALUES (4,2,’HP DeskJet 3835′);
INSERT INTO “SYSTEM”.”” (“slNo”,”invoiceNo”,”modelNumber”) VALUES (5,2,’Cruzer Blade’);
INSERT INTO “SYSTEM”.”” (“slNo”,”invoiceNo”,”modelNumber”) VALUES (6,2,’678′);
INSERT INTO “SYSTEM”.”” (“slNo”,”invoiceNo”,”modelNumber”) VALUES (7,3,’E5 – 575′);
INSERT INTO “SYSTEM”.”” (“slNo”,”invoiceNo”,”modelNumber”) VALUES (8,3,’Cruzer Blade’);
INSERT INTO “SYSTEM”.”” (“slNo”,”invoiceNo”,”modelNumber”) VALUES (9,3,’678′);
INSERT INTO “SYSTEM”.”” (“slNo”,”invoiceNo”,”modelNumber”) VALUES (10,4,’AN515-51′);
INSERT INTO “SYSTEM”.”” (“slNo”,”invoiceNo”,”modelNumber”) VALUES (11,4,’HP DeskJet 3835′);
INSERT INTO “SYSTEM”.”” (“slNo”,”invoiceNo”,”modelNumber”) VALUES (12,4,’678′);
INSERT INTO “SYSTEM”.”” (“slNo”,”invoiceNo”,”modelNumber”) VALUES (13,4,’Cruzer Blade’);
INSERT INTO “SYSTEM”.”” (“slNo”,”invoiceNo”,”modelNumber”) VALUES (14,5,’HP DeskJet 3835′);Q1
SELECT “modelNumber” FROM “SYSTEM”.”products” WHERE “processorSpeed” > 150Q2
SELECT “modelNumber”, “cost” FROM “SYSTEM”.”products” WHERE “maker” = ‘Acer’Q3
SELECT “modelNumber” FROM “SYSTEM”.”products” WHERE “catID” = (SELECT “catID” FROM “SYSTEM”.”categories” where “catType” = ‘Printer’)Q4
SELECT “employees”.”empName”, “sales”.”invoiceNo”, “sales”.”saleDate”, “sales”.”customerName”, “sales”.”cost” FROM “SYSTEM”.”sales”, “SYSTEM”.”employees” WHERE “sales”.”empID” = “employees”.”empID”Q5
SELECT “sales”.”saleDate”, “salesProduct”.”modelNumber”, COUNT(“salesProduct”.”modelNumber”) AS COUNT FROM “salesProduct”, “sales” WHERE “salesProduct”.”invoiceNo” = “sales”.”invoiceNo” AND “modelNumber” = (SELECT “modelNumber” FROM “products” where “catID” = (SELECT “catID” FROM “SYSTEM”.”categories” where “catType” = ‘Printer’)) group by “modelNumber”, “sales”.”saleDate”Q6
SELECT “sales”.”customerName”, “sales”.”saleDate”, “salesProduct”.”modelNumber”, “products”.”cost” FROM “sales”, “salesProduct”, “products” where “sales”.”invoiceNo” = “salesProduct”.”invoiceNo” AND “salesProduct”.”modelNumber” = “products”.”modelNumber” order by “customerName”, “modelNumber”Q7
This query would be able to display the model number of the products falling under the respective category.
SELECT “categories”.”catType”, “products”.”modelNumber” from “categories”, “products” where “products”.”catID” = “categories”.”catID”
Q8
This query would help in the understanding of the number of products of each model number that is being sold on a daily basis in the company.
SELECT “sales”.”saleDate”, “salesProduct”.”modelNumber”, COUNT(“salesProduct”.”modelNumber”) AS COUNT FROM “salesProduct”, “sales” WHERE “salesProduct”.”invoiceNo” = “sales”.”invoiceNo” group by “modelNumber”, “sales”.”saleDate” order by “saleDate”
Connolly, T. &Begg, C. Database Systems – A Practical Approach to Design, Implementation and Management (5th Ed.), Pearson Education, 2009.
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management. Cengage Learning.
Earp, R. & Bagui, S. Learning SQL A Step-by-Step Guide using Oracle, Jones and Bartlett Publishers, 2008.
Hoffer, J. A., Venkataraman, R. & Topi, H. Modern Database Management (10th Ed.) Pearson, 2011.
Kline, K., Hunt, B. &Kline, D. SQL in a Nutshell – A Desktop Quick Reference O’Reilly, 2004.
Kroenke, D. & Auer, D. Database Concepts (5th Ed.), Pearson, 2011.
Laudon, K.C. and Laudon, J.P., 2016. Management information system. Pearson Education India.
Reddy, T.B., Thomas, A.D., Stamatis, D., Bertsch, J., Isbandi, M., Jansson, J., Mallajosyula, J., Pagani, I., Lobos, E.A. and Kyrpides, N.C., 2014. The Genomes OnLine Database (GOLD) v. 5: a metadata management system based on a four level (meta) genome project classification. Nucleic acids research, 43(D1), pp.D1099-D1106.
Ritchie, C. Relational Database Principles (3rd Ed.) Cengage Learning, 2008.
Shah, N. Database Systems Using Oracle, Pearson, 2004.