INFOSYS 330 – BUSAN 302

THE UNIVERSITY OF AUCKLAND

_________________________________

FINAL ASSESSMENT

SEMESTER ONE, 2020: Campus: City

_________________________________

INFORMATION SYSTEMS

Databases & Business Intelligence

BUSINESS ANALYTICS

Data Mining & Decision Support Systems

(Estimated Time: 3 Hours)

(This assessment is worth 50 points of the grade. It is marked out of 100%)

Note: Attempt all questions 

Use Figure 1 on Page 2 for Questions 1, 2 & 3 in Section A.

Read the Case Study (in the Attachment on pages 5 and 6) for Section B

Section Description Marks

A Data Warehousing and T-SQL 35

B Applications of Machine Learning in Business 65

Total Marks 100

(Available From: 1:00 pm 2nd July 2020 

Available to: 12:59 pm 3rd July 2020)

INFOSYS 330 – BUSAN 302

2 of 8 

Instructions

• This Final Assessment has been designed so that a well-prepared student could complete it

within 3 hours.

• It is your responsibility to ensure your assessment is successfully submitted on time. Please don’t

leave it to the last minute to submit your assessment.

• If any corrections are made during the 24 hours, you will be notified by a Canvas Announcement.

Please ensure your notifications are turned on during this period.

• Support:

• If you wish to raise concerns during the Final Assessment, please call the Contact Centre for

advice:

Auckland: 09 373 7513, Outside Auckland: 0800 61 62 63, International: +64 9 373 7513

• For any Canvas issues, please use 24/7 help on Canvas by chat or phone.

INFOSYS 330 – BUSAN 302

3 of 8 

Declaration of Academic Honesty 

• By completing this assessment, I agree to the following declaration:

• I understand the University expects all students to complete coursework with integrity and 

honesty. I promise to complete all online assessment with the same academic integrity standards 

and values. Any identified form of poor academic practice or academic misconduct will be 

followed up and may result in disciplinary action.

• As a member of the University’s student body, I will complete this assessment in a fair, honest, 

responsible and trustworthy manner. This means that:

• I declare that this assessment is my own work, except where acknowledged appropriately (e.g., 

use of referencing).

• I will not seek out any unauthorised help (i.e., anyone other than the course lecturer or tutor) in 

completing this assessment.

• I declare that this work has not been submitted for academic credit in another University of 

Auckland course, or elsewhere.

• I am aware the University of Auckland may use Turnitin or any other plagiarism detecting 

methods to check my content.

• I will not discuss the content of the assessment with anyone else in any form, including, Canvas, 

Piazza, Facebook, Twitter or any other social media within the assessment period.

• I will not reproduce the content of this assessment anywhere in any form.

• I declare that I composed the writing and/or translations in this assessment independently, 

using only the tools and resources defined for use in this assessment.

INFOSYS 330 – BUSAN 302

4 of 8 

SECTION A Data warehousing and T-SQL 35%

Figure 1: A small section of the ERD for the Drones New Zealand database

INFOSYS 330 – BUSAN 302

5 of 8 

1. Data Warehousing (12 Marks)

Figure 1, is a section of the database used in the organisation - Drones NZ. Assuming the section of 

the database shown in Figure 1 is the only data source that can be used, design a star schema that 

will be useful for its executives. Do not use more than four dimension tables and one fact table in 

your star schema design. Show the measures in the fact table.

You must show the relationships between the dimension tables and the fact table, the primary and 

foreign keys in all the tables, and the measure (metric) in the fact table. Do not use a surrogate key 

(for the composite primary key) in the fact table. The attribute hierarchies must be shown ordered 

clearly in the dimension table, with the most granular attribute at the top. 

2. Basic SQL (11 Marks)

Select ONLY ONE dimension table from the star schema you designed in Question 1.

First plan and then write one or more SQL statements to retrieve all the data needed to insert 

into the selected dimension table, from the tables shown in the Entity Relationship Diagram given in 

Figure 1. 

a) Firstly, using plain English, explain how you plan to retrieve the data to insert into the 

selected dimension table.

(3 Marks)

b) Secondly, write the SQL query (or queries) needed as planned in Question 2a).

(8 Marks)

3. Advanced T-SQL (12 marks)

Write a T-SQL Procedure to calculate ONE measure in the fact table.

INFOSYS 330 – BUSAN 302

6 of 8

 ATTACHMENT FOLLOWS

SECTION B – Applications of Machine Learning in Business – 65%

(Attempt all questions in this section, based on the attached Case Study at the end.)

4. Discuss a critical business problem faced by ‘Drones NZ, which needs a solution incorporating 

a Machine Learning Algorithm.

 (12 Marks)

5. Decompose the problem suggested in Question 4, to manageable parts and list them with a 

brief explanation.

 (12 Marks)

6. Select the most complicated decomposed part listed in your solution for Question 5, and 

explain in detail how you propose to solve it. Your answer must include how you selected the 

Machine Learning Algorithm and how you propose to design and implement it, in detail.

(30 Marks)

7. DSS (11Marks)

We had discussed different types of Decision Support Systems (DSS) in class. 

 

a. What type of DSS is the system you designed for Question 6? 

(3 Marks)

b. Describe the different components of a DSS. Use the system you designed to give an 

example for each DSS component. 

 (8 Marks)

ATTACHMENT INFOSYS 330 – BUSAN 302

7 of 8

The Case Study – Drones New Zealand (Drones NZ)

Jillian and James Ciao set up in business, in January 2020 selling customised drones to organisations 

and individuals. They named their business Drones New Zealand (Drones NZ). The purpose of the 

organisation is to customize sales of drones for a plethora of purposes that individuals and organisations 

use drones for in New Zealand, and provide after sales maintenance.

Examples of customers include: individuals and organisations in sports and recreation;

restaurants delivering cooked food that must be kept warm or cold;shops,such as DIY stores delivering 

small items; farms and conservation groups for spraying fertilisers, weed-killers etc.; organizations 

taking aerial photographs of animals, pests, vegetations, etc.; and NGOs for delivering dry goods in 

disaster struck areas.

Drones NZ not only sells drones but also provides after-sales service. There are many types of 

drones and the knowledge needed to service them is wide and varied. Expert knowledge is very 

expensive, and Drones NZ cannot afford to call experts each time a service needs to be done. A wide 

area of knowledge, both mechanical and IT-based, is needed because there are many different types of 

drones which uses different types of software. Hence, the knowledge required by maintenance 

engineers must span the range of products. The costs incurred in expert services are therefore also very 

high. Jillian who is a software engineer and James a mechanical engineer, have decided to utilize the 

capabilities of machine learning algorithms/data mining to solve this problem, of needing a wide 

variety of skills. Their immediate attention on this has an impact on other matters. These include hiring 

staff, reducing expenses of consulting experts, and demanding and utilising service contracts for 

products bought from suppliers.

Drones NZ stock drones and parts needed on a just-in-time basis, to reduce on the amount spent in 

holding stocks. They have to have a network of suppliers and information about the products and 

services suppliers provide, such as costs of the various items and the average time taken for delivery. 

This information must be updated as changes happen as information on price etc. changes frequently. 

Such changes when they happen must be communicated to Drones NZ by the suppliers.

ATTACHMENT INFOSYS 330 – BUSAN 302

8 of 8

Drones should be GPS enabled when they are sold to customers to enable them avoid accidents 

with obstacles such as traffic, animals and humans. Flight paths must be incorporated with additional 

constraints: examples include, height suited in the air apace they occupy; and the radius within which 

the drone can travel. 

Automated pilots are a new venture of research that Jillian is keen on and her research includes

exploring the appropriateness of selected machine learning algorithms for this purpose. The drones 

must maintain a log of delivery, time of start and finish and photographic record at the delivery end. 

James is very interested in maximising the efficiency of the flight path and the ability of a drone 

to effectively carry out the functions for which they have been designed.

Described above are the main operations of Drones NZ. Please feel free to extend 

this case study as and if needed, to suit a business problem you identify. Any 

extensions made to the case study or any assumptions made by you in interpreting 

a situation must be explained.