Reference no: EM132360882

Assessment - A formal business report

Unit Learning Outcome (ULO)
ULO 1: Understand and apply current information management and security approaches in business contexts.
ULO 2: Learn about the technologies methodologies and concepts surrounding different types of data management and information security.
ULO 3: Be able to use data management technologies to create and critically evaluate data management solutions.

This is an individual assignment. The aim of the assignment is for you to develop understanding of information management and security concepts and approaches and apply them to a realistic business context (ULO1, and GLO1) by:
• Designing a simple database the business can use to manage information, which includes basic security controls to protect access to the information (ULO1/ULO2, and GLO1/GLO3);
• Designing SQL queries capable of producing specific reports required by the business relating to information management and information security (ULO3, and GLO3); and
• Evaluating critically the security threats posed by the proposed database (ULO3 and GLO3).

Starting the Assignment

You are strongly encouraged to make a start on this assignment by the end of the 5th week of trimester, and you should progress thoughtfully through the steps. Hasty decisions made early in the design process may result in much more work later.
Feel free to discuss concepts and ideas with peers but remember your submission must be your own work. Be careful not to allow others to copy your work.

Task 1: SQL Queries

Include the following SQL queries that were completed as part of the weekly workshops. These were labelled as Assignment Question within each of the workshops.

a) Assignment Question (Your tutor cannot help with this question):

For each customer list the name, city and state in that order. The results should be ordered by the city in the reverse alphabetical order (Z to A).

b) 6. Assignment Question (Your tutor cannot help with this question):
List the product description and the price of all products that are made out of natural ash and cost less than $500.

c) 8. Average price of products that have the word "Natural" in the product finish.

d) 4. List the order ID, order quantity, product description and the price of all orders containing desks (results should be alphabetically ordered by product description)

e) 5. Assignment Question (Your tutor cannot help with this question):

List the product description, quantity ordered and the price for all items included in the order number 1006. We would also like to have a subtotal column added as the last column - see below. (Subtotal = price times quantity)

For each of the above SQL queries include the following:
i. Actual textual SQL query (e.g. SELECT column FROM table;)
ii. Screenshot of the SQL query being executed on your university database account and the resulting table.
Make sure the SQL query and the resulting table are captured in full in the screenshot. The table should be formatted with no lines wrapping around. Use set linesize command (see Workshop Week 2) to increase line length if necessary.
iii. Explanation of each of the SQL queries in your own words (1 or 2 sentences max).

PRODUCT_DESCRIPTION    ORDERED_QUANTITY             STANDARD_PRICE      SUBTOTAL
------------------------- ---------------- -------------- ------------------------------------------------------------
Entertainment Center                    1                                    650                   650
Writers Desk                                2                                    325                   650

Business Brief for Tasks 2 & 3

Fix My Sink Online is a small, but rapidly growing business operating out of Geelong. The owner, Phillip Lancashire who is a plumber by trade, started this business to sell various plumbing products online and book household plumbing services with his customer base. Selling plumbing parts alone was not a profitable occupation, so Phillip also branched off into selling other types of physical goods such as tools, pot plants and animal feed to the local community in the greater Geelong.

Within a year of beginning his operation Phillip was approached by his friends who were also interested in selling various specialised goods that typical shops were not interested in selling (e.g. herbal remedies, organic fertilizers, homemade spice concoctions etc). At the same time, he is approached by others (handymen, plumbers, electricians, babysitters, mechanics etc.) seeking his help in getting their services booked with customers. All service bookings must specify a venue or a job site where the work is to be carried out. While most clients are from Victoria, the service bookings originate from all Australian states and territories.

Three years later, Phillip finds that he has become an agent, the go-between for over 500 buyers and sellers. He is generating a reasonable income from the commissions charged (5% of total transaction value). The household service bookings business has also expanded and he has over 50 service providers depending on him for finding work, and the demand is still growing.

So far, his business has been operating with temporary notes scribbled on notebooks in combination with a series of disorganised spreadsheets. But these tools are becoming inefficient and increasingly inapt at managing the growing demands of the business. A transition to an information system is urgently needed.

The proposed business should support the following two operational areas:

• Interaction between buyers and sellers

o Buyers purchase products from sellers
o Sellers sell products to buyers

• Interaction between service providers and their clients (buyers)

o Service providers offer various services to clients
o Clients get service providers booked for services

For the business to operate effectively both of these operational areas should be integrated into your EER model.

Task 2: EER Model

You are employed as a business analyst at Deakin Innovative Solutions Ltd, a business-consulting firm. You are to investigate and develop a data model (an EER model) for the newly proposed system.

Note: The design of payment systems is outside the scope of this project. Assume it is outsourced.

Tasks 3: Security Research Report

After being alarmed by the recent security incidents reported in the media, Phillip decides to employ a part-time System Administrator to manage system security.

a) Identify 6 security threats to the proposed system (Choose a mix of internal/external, deliberate/unintentional threats).

b) Classify each threat on a probability-impact matrix and explain your reasoning for the classification.

c) For each threat which of the risk controls would you recommend? Justify your choice. If you have opted for avoidance or mitigation of risk, clearly explain the policies, measures or strategies that need to be put in place to achieve the desired outcomes.