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

The PC Land Case Study

This coursework is about the design and implementation of a product monitoring service for a retail outlet.

Study the ‘PC Land’ case study carefully and then submit the following:

1. Draw an initial Entity-Relationship model, stating any assumptions you make.. (20%)                                         

2. Normalise (to third normal form) the Sales Form. Show all the steps of normalisation clearly. State any assumptions. Now draw an Entity-Relationship diagram for these normalised entities. (15%)                                                                            

3. Give the Relational Schema (set of tables) that you will create.  (10%)                 

4. Implement and submit printed documentation for the following:

   (a) Create the tables you have identified for the PC Land database and populate them with appropriate test data of your choice.

   (b) Implement all the product information queries and all the sales process queries. You    should provide, for each query, a separate Oracle script AND the resulting output. Number      the queries Q1, Q2, Q3, Q4, Q5, Q6.

   (c) Now produce two additional queries (Q7 and Q8) which you believe will be useful to the company.

   Consider extending your schema with either extra data columns or tables for this purpose. Give a clear textual description of what each of your two queries is supposed to show. Use your judgement as to what information the queries should contain and how they should be formatted. (55%)  You need to upload one Word document to Weblearn 

Your work must be in a Word document named CC5051-yourname. This document must contain the following:

Section 1:

• The E-R model with attributes and relationships. You can draw the model using any tool you wish, including hand-drawn.
• The results of your normalisation – show all normal form stages.
• Include any supplementary descriptions/notes/assumptions/discussion to strengthen your proposed design of the system.

Section 2:

• Your CREATE TABLE commands for the tables in your system.
• All the INSERT commands you have used to place data into those tables.
• SQL code for Q1 and output
• SQL code for Q2 and output
• SQL code for Q3 and output
• SQL code for Q4 and output
• SQL code for Q5 and output
• SQL code for Q6 and output
• SQL code for Q7 with output and rationale
• SQL code for Q8 with output and rationale.
• Include any supplementary descriptions/notes/assumptions/discussion to strengthen your implementation of the system. 

The PC Land Case Study  

The PC Land has been in operation for just six months and looks set to be a successful business. PC Land is a retail outlet which offers computer equipment at very competitive prices. The shop is sited in a locality which ensures consistently high sales. 

Products fall into three categories: Personal Computer, Laptop and Printer. The shop also stocks accessory items such as USB drives, web cams, printer cartridges, cables, plugs, adapters etc. Products can be described in terms of processor speed, memory capacity, size of hard disk, price, speed of DVD drive, screen size and colour. 

Since the shop is small - it is staffed by five sales staff and has two managers - it is crucial that only those products which can be sold quickly are stocked. It is the job of one of the managers, Dave Tindall, to oversee the ordering and stocking of product items. What Dave requires is the ability to monitor the sales of given products. 

The sales staff work on a commission basis which is calculated by adding the number of sales made for the day and multiplying by two. 

In view of the fact that the shop has been so busy meeting the high level of sales, as this is what earns money, they have neglected to develop an adequate internal system for monitoring the products. Ideally Dave would like to be able to have a system which could very easily provide him with answers to both product information queries and also queries about the sales process itself.

Some of the product information queries could be as follows:

• what PC models have a speed of at least 150.
• find the model number and price of all products (of any type) made by ‘Acer’.
• find the model numbers of all colour laser printers. 

Some of the sales process queries could be:

• details of sales that are handled by an individual sales person.
• list of printers which have been sold on a particular day.
• details of the purchases a particular customer has made.

Customer number:    238790                        Sales-id:          633 Customer name:        Peter Minter              Sales person:  James Hart

Since the shop has not been running long, there are only a few mechanisms in place. The Sales Form shown below is one of them.

Sale total:                   £1450

Model         Product           Unit                 Sale                 Line number       type                 price                quantity        total   T5060         Laptop               500                    2                  £1000 PT42           printer                250                    1                  £250           QZE248      printer               200                    1                  £200

Sale number:  0057435 Sale date:        07-August-2017

PC Land

Section 1:  E-R model

Normalization

First NORMAL FORM:

sale date	sale number	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.