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
INFOSYS 330 – BUSAN 302
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
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.
2 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.
3 of 8
INFOSYS 330 – BUSAN 302
SECTION A Data warehousing and T-SQL 35%
INFOSYS 330 – BUSAN 302
Figure 1: A small section of the ERD for the Drones New Zealand database
4 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.
b) Secondly, write the SQL query (or queries) needed as planned in Question 2a).
3. Advanced T-SQL
Write a T-SQL Procedure to calculate ONE measure in the fact table.
(3 Marks) (8 Marks)
(12 marks)
5 of 8
INFOSYS 330 – BUSAN 302
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.
6 of 8
(8 Marks)
ATTACHMENT FOLLOWS
INFOSYS 330 – BUSAN 302
ATTACHMENT INFOSYS 330 – BUSAN 302
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.
7of 8
ATTACHMENT INFOSYS 330 – BUSAN 302
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.
8of 8