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:
- Draw an initial Entity-Relationship model, stating any assumptions you make.. (20%)
- 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%)
- Give the Relational Schema (set of tables) that you will create. (10%)
- 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 QUERYcategories:
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.
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.