Item 2: Information Retrieval and Use (60%)

Hand In: 24th April 2012

To be demonstrated in week 13.

NOTE: the documentation MUST be submitted on time to receive a mark. Any part of the demonstrated work which is not supported by documentary evidence will not receive a mark.

Learning Outcomes

Module Learning Outcomes / Map to Programme Learning Outcomes
1) DEMONSTRATE KNOWLEDGE AND CRITICAL UNDERSTANDING OF INFORMATICS AND THE UNDERPINNING THEORIES. / Learning
3) RETRIEVAL OF INFORMATION USING SQL. / Application

Description

This is an individual assignment. You are expected to spend between 40 and 60 hours completing the work. You are advised to spread your assignment work over weeks 6 to 12 seeking help in tutorials where necessary.

You may wish to change the database design submitted as item 1 based on feedback. If so, your changed design should be submitted with item 2 – no marks will be given for the new design but a superior design may improve the quality of the work in this item.

Undertake the following and produce relevant documentation

 Create a set of database tables

 Add sensible test data to ensure that you can demonstrate the queries and graphs required.

Documentation should include table structures and listings of all test data

Part 1 – Queries (20%)

Produce SQL queries as detailed in 1 to 5 below (each of the 5 query questions is worth 4%)

Documentation should include the query statements followed by the output.

Create queries to show

 A membership list for a user entered branch

 A stock listing for a user entered branch#

NOTE: The output from these queries will be used in the demonstration to give an idea of the test data you have entered.

Create a query to show the service#, description and customer details and revenue of all the services purchased. Group your output by branch and within each group order your output by revenue – highest first!

Create a query to show the details of the customers, who joined in the same month (not necessarily the same year) as the customer who has generated the most revenue.

Create a query to show all garments which have never been hired

Create queries to show the following

 Members who have memberships at more than one branch

 Employees who are also members

Part 2 – Forensic data (20%)

By using the flashback command set in Oracle (or equivalent) examine the history of one of the tables you have used in this assignment. (15%)

NOTE: An appropriate table will be one that you have created, amended the table structure, inserted, updated and deleted data.

Write a brief report (of circa 200 words) detailing how these commands would aid a forensic investigator in detecting fraud (5%)

Part 3 – Data mining and visualisation (20%)

Pareto’s law states that in general it can be said that

 20% of customers account for 80% of your service and supply problems

 20% of customers account for 80% of your turnover

 20% of customers account for 80% of your profits

Pareto’s rule holds true for many organisations. The first and last points will be hard to prove with the data you have stored. However, using pivot tables you can look at the turnover (revenue).

Modify your answer to Part 1, question 2 as follows

 Create a query to show the service#, description, price and customer details (including age) of all the services purchased.

 Move your output to a spreadsheet.

NOTE: If you are unable to achieve a data set from a query it will be acceptable to enter suitable data directly into the spreadsheet.

1. Create suitable graphs to show the following :

 Revenue percentage generated from each item (2%)

 Revenue per customer (2%)

2. Create a pivot table, which will enable you to

 Ascertain whether 80% of your revenue is in fact coming from 20% of your customers (5%)

 Determine whether there is any correlation between customer age and amount spent (5%)

3. Create suitable graphs to clearly show your results (6%)

NOTES:

Marks for each section are shown. This item will be marked during a 15-minute demonstration. At this time, a view will be taken as to the appropriateness of the test data.

No marks will be awarded for this item if either the documentation is missing or the demonstration does not take place. You should note that this time of year is busy if you fail to attend your scheduled demonstration, it may not be possible to reschedule within the timescale.

Before handing in your work, carefully check to ensure that it meets the assessment criteria. The work submitted and demonstrated MUST be as a result of your own individual efforts.

Assessment marking criteria for Item 2 (60%)

A demonstration marking form will be used.

Grade Point / Percent / General Comment / Detailed Criteria where relevant
13-15 / >=70 / The work greatly exceeds the requirements of the query /  A sophisticated and suitably complex database with several tables will be present
 The test data will be extensive and meaningful
 Queries will demonstrate accurate utilisation of most of the features suggested below
 All queries will be capable of producing the correct result in an appropriate format.
 The flashback command will have been used to accurately determine the complete history of use of an appropriate table
 There will be a report containing suitable well thought out comments on the use of the flashback command in a forensic investigation.
 The pivot tables will be correctly set up for the task
 Spreadsheet functions will have been used where appropriate to facilitate an elegant solution
 Care will have been taken to select the most appropriate graphs in all cases and to ensure that labels and colours provide clarity and professionalism
10-12 / 60-69.5 / The work meets and goes beyond the requirements of the query /  A sophisticated database will be present
 The test data will be meaningful and adequate for demonstration of the queries.
 Queries will accurately utilise some the features suggested below All queries will be capable of producing the correct result mostly in an appropriate format.
 The flashback command will have been used to determine the history of use of an appropriate table
 There will be a report containing suitable comments on the use of the flashback command in a forensic investigation.
 The pivot tables will be correctly set up for the task
 Spreadsheet functions will have been used where appropriate to facilitate an elegant solution
 In most cases the most appropriate graphs will have been selected and labels and colours will in most cases provide some clarity and professionalism
7-9 / 50-59.5 / The work meets the requirements of the query /  A reasonably sophisticated database will be present
 The test data will be adequate for demonstration of the queries and graphs.
 Queries will utilise some of the features suggested below
 Most queries will be capable of producing the correct result and format.
 The flashback command will have been used to determine the history of use of a table - the table chosen may not have an appropriate history
 There will be a report containing suitable comments on the use of the flashback command in a forensic investigation.
 The pivot tables will be correctly set up
 Graphs will show the information required clearly though better choices could have been made in some cases
4-6 / 40-49.5 / The work barely meets the requirements of the query /  An adequate database will be present
 A reasonable attempt will have been made for all sections and most of the work will be of a fair standard. Some minor elements may be missing or inadequate.
3 / 30-39.5 / The work fails to adequately meet some of the requirements of the query /  A database and spreadsheet will be present though they may not meet the standard required.
 Some key elements of the work will be missing or fall below the standard required.
1-2 / 10-29.5 / The work fails to meet most of the requirements of the query /  A database will be present though it will not meet the standard required. The spreadsheet may be missing or inadequate
 Most of the work will fall below the standard required
0 / 0 / Non-submission or totally inadequate. /  There will be no database or spreadsheet
Suggested features /

Parameterised query, Formatted output
Group by, Order by
Embedded Select, Outer join, Self Join.

NOTE: Depending on the structure of the database it may not be possible to utilise the all suggested features. This will not necessarily reduce the mark awarded if the database design enables you to produce the required output using an elegant solution.