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 Save Time On Research and Writing Hire a Pro to Write You a 100% Plagiarism-Free Paper. Get My Paper	Sales-id	Sales person	Customer number	customer name	model number	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.