Design And Implementation Of A Product Monitoring Service For A Retail Outlet

The PC Land Case Study

Section 1:  E-R model

Save Time On Research and Writing
Hire a Pro to Write You a 100% Plagiarism-Free Paper.
Get My Paper

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.