Get Instant Help From 5000+ Experts For
question

Writing: Get your essay and assignment written from scratch by PhD expert

Rewriting: Paraphrase or rewrite your friend's essay with similar meaning at reduced cost

Editing:Proofread your work by experts and improve grade at Lowest cost

And Improve Your Grades
myassignmenthelp.com
loader
Phone no. Missing!

Enter phone no. to receive critical updates and urgent messages !

Attach file

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

Guaranteed Higher Grade!
Free Quote
wave

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%)                                         
  1. 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%)                                                                            
  1. Give the Relational Schema (set of tables) that you will create.  (10%)                 
  1. 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

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

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.

Cite This Work

To export a reference to this article please select a referencing stye below:

My Assignment Help. (2020). Design And Implementation Of A Product Monitoring Service Essay For A Retail Outlet.. Retrieved from https://myassignmenthelp.com/free-samples/cc5051-databases.

"Design And Implementation Of A Product Monitoring Service Essay For A Retail Outlet.." My Assignment Help, 2020, https://myassignmenthelp.com/free-samples/cc5051-databases.

My Assignment Help (2020) Design And Implementation Of A Product Monitoring Service Essay For A Retail Outlet. [Online]. Available from: https://myassignmenthelp.com/free-samples/cc5051-databases
[Accessed 21 November 2024].

My Assignment Help. 'Design And Implementation Of A Product Monitoring Service Essay For A Retail Outlet.' (My Assignment Help, 2020) <https://myassignmenthelp.com/free-samples/cc5051-databases> accessed 21 November 2024.

My Assignment Help. Design And Implementation Of A Product Monitoring Service Essay For A Retail Outlet. [Internet]. My Assignment Help. 2020 [cited 21 November 2024]. Available from: https://myassignmenthelp.com/free-samples/cc5051-databases.

Get instant help from 5000+ experts for
question

Writing: Get your essay and assignment written from scratch by PhD expert

Rewriting: Paraphrase or rewrite your friend's essay with similar meaning at reduced cost

Editing: Proofread your work by experts and improve grade at Lowest cost

loader
250 words
Phone no. Missing!

Enter phone no. to receive critical updates and urgent messages !

Attach file

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

Plagiarism checker
Verify originality of an essay
essay
Generate unique essays in a jiffy
Plagiarism checker
Cite sources with ease
support
close