首页 > > 详细

Data Warehousing Assignment 2: Analytical SQL Assignment

 Data Warehousing

Assignment 2: Analytical SQL Assignment (20 marks)
Module 2, 2020
Introduction 
This is an individual assignment. Using data models (ER diagrams) developed by your syndicate group and 
submitted as part of the Assignment 1 report, you are required to design and document two analytical SQL 
queries that can help business users of a system that implements the data models to gain insights about the 
performance of their organisation and support strategic decision making. 
Case Descriptions 
Refer to Assignment 1 case description. 
Assessment and Report Requirements 
This assignment is worth 20% of your assessment for the subject. Historically, the average mark for this 
assignment has been around 15/20 marks. 
Design two analytical queries over the database schema in Third Normal Form (3NF) and Star Schema your
syndicate group has prepared and submitted as part of the Assignment 1 report. The analytical queries should 
address the data across multiple entities (tables) of your database/star schemas. For examples of analytical 
queries, please refer to the lecture slides published on Canvas: 
1) Data Warehousing - Part 03 - Lecture - Data Warehouse Design Principles - AFTER.pdf (Slide 14) 
2) Data Warehousing - Part 04 - Lecture - Dimensional Modelling (Kimball and Ross) - AFTER.pdf (Slide 8) 
Each of the two analytical queries must be supplied with: 
1) A specification of the query in English language (maximum 150 words); 
2) Two SQL queries that implement the English language specification of the query. One SQL query should 
be implemented over the database schema, while the other SQL query should implement the same logic as 
the first query but over the star schema; 
3) A discussion of decision making capabilities that the analytical query supports (maximum 250 words). 
Explain how the results of the query can provide valuable insights to an organisation that implements it. 
You should use hypothetical query results to exemplify strategic decisions and actions the organisation can 
take as a response to obtaining them. 
Finally, your report should include a discussion on the benefits and drawbacks of implementing analytical 
queries over the normalised database schema and denormalised star schema (maximum 350 words). This 
discussion should demonstrate your understanding of the role of both data designs in organisations. 
The submission should be less than 1,000 words in length (excluding figures, tables, SQL statements, and 
references) and take the form of a report describing and justifying the designs of the analytical queries. A 
suggested outline for the sections of the report is listed below1
1. Cover sheet containing the title of the assignment, an identifier of your Assignment 1 report (e.g., your 
syndicate group name or number), your name, and student id.
2. Data models captured as ER diagrams, both the database schema in Third Normal Form (3NF) and Star
Schema, that you have submitted as part of the Assignment 1 report.
3. Specification of analytical queries in English language, which presents two analytical queries that can 
be implemented over both data models presented in Section 2. 
1 Please note that this structure is a guide only and can be changed.
Data Warehousing
Assignment 2: Analytical SQL Assignment (20 marks)
Module 2, 2020
4. Specification of SQL queries, which lists SQL queried that implement the analytical queries from 
Section 3. Implement and list two SQL queries for each analytical query from Section 3, one SQL query 
over the database schema and one SQL query over the star schema, both implementing the same logic. In 
total, this section should list four SQL queries.
5. Decision making capabilities, which presents a discussion of the strategic decisions that the queries from 
Sections 3 and 4 can support in organisations that decide to implement your data models and queries. You 
should use hypothetical examples of data and/or query results to demonstrate the added value of your 
queries.
6. Discussion, which includes a summary of benefits and drawbacks implementation of analytical queries 
over normalised and denormalised data model entails. 
Remarks 
You are requested to use the data models submitted as part of your Assignment 1 report. No modifications to 
the data models are allowed. Note that the correctness of the data models submitted as part of Assignment 1 
will not influence your mark for Assignment 2. 
Note that all the word limits mentioned under the “Assessment and Report Requirements” section are strict. 
A failure to adhere to those limits will lead to reductions in your mark for Assignment 2. 
Assessment Criteria (Marking Scheme) 
Aspect of assessment Mark (%) Excellent performance 
Analytical queries 
(in English language)
 20% 
(10% per 
query) 
Two proposed queries have analytical components and, thus, can 
provide insights on the performance of organisations that 
implement them. The queries address the facts stored in the fact 
tables of the star schema. The queries are presented using a proper 
English language, i.e., can be understood easily. The queries 
address the entities of the database and star schema and can be 
implemented using only the entities and relationships included in 
the schemas.
Decision making 
capabilities 
20% 
(10% per 
query) 
An excellent discussion of at least one comprehensive decision 
making capability enabled by the results of each of the analytical 
queries. The discussion includes examples based on the 
hypothetical query results. The discussion suggests logical 
decisions and/or actions the organisation can take in response to 
obtaining the proposed hypothetical results of the analytical queries.
Analytical queries 
(in SQL) 
40% 
(10% per 
query) 
Two proposed SQL queries implement the analytical queries 
correctly over the normalised database schema. Two proposed SQL 
queries implement the analytical queries correctly over the 
denormalised star schema. The queries are non-trivial, i.e., each 
query uses at least two SQL statements beyond the basic SELECT, 
FROM, and WHERE statements. The presentation of the queries 
(layout, font, capitalization) supports their comprehension.
Discussion 20% An excellent discussion of the main benefits and drawbacks of 
implementing analytical queries over the normalised database 
schemas and denormalised star schemas. The discussion includes 
references to concrete examples that use the analytical queries 
prepared as part of this assignment. The discussion demonstrates 
the role of both data designs for organisations.
Data Warehousing
Assignment 2: Analytical SQL Assignment (20 marks)
Module 2, 2020
Submissions 
This assignment is due at 11:59pm on Friday, 24th April.
You will submit only an electronic version. We will endeavour to get your assignment marked within a one￾week period. You will receive feedback on your assignment via the marking scheme. The marker will also 
give written feedback in the form of comments on the marking sheet. 
You are required to submit an electronic version of your work through the LMS. You will be submitting this 
version of your work through TurnItIn (this is a plagiarism checking platform) and will be able to submit more 
than once up to the due date so please ensure that your assignment is plagiarism free. By submitting your 
work in this manner plagiarism is checked for you and you will be able to see if you have any issues with this 
in your work and will be able to fix these issues before the due date. Also, by submitting the Assignment via 
the LMS you are agreeing that the Assignment is your own work. 
Academic Honesty 
Academic misconduct occurs when students portray someone else's work as their own. There are many ways
in which Academic misconduct can occur. Some of these are: 
• Sham Paraphrasing: Material copied verbatim from text and source acknowledged in-line but 
represented as paraphrased. 
• Illicit Paraphrasing: Material paraphrased from text without in-line acknowledgement of source. 
• Other Plagiarism: Material copied from another student's assignment with the knowledge of the other 
student. 
• Verbatim Copying: Material copied verbatim from text without in-line acknowledgement of the source. 
• Recycling: Same assignment submitted more than once for different subjects. 
• Ghost Writing: Assignment written by third party and represented as own work. 
• Purloining: Assignment copied from another student's assignment or other person's papers without that 
persons knowledge. 
The university is committed to graduating students with “a profound respect for truth, and for the ethics of 
scholarship... we want our graduates to be capable of independent thought, to be able to do their own work, 
and to know how to acknowledge the work of others” (Prof. Peter McPhee). As such, the university takes a 
dim view of students who are not able to correctly acknowledge the work of others, or who try to pass this 
work off as their own. 
All students should be aware of the following web site: http://academichonesty.unimelb.edu.au/advice.html, 
which provides practical advice to students about how not to be involved in academic misconduct. In particular 
look at the Quick Checklist section (paraphrased below). 
Quick checklist 
(Reproduced with permission of Dr Stephen Morgan, Faculty of Economics and Commerce) 
To be certain to acknowledge sources fairly and avoid plagiarising, review this checklist before beginning to 
write your essay and again after you have completed your first draft. 
Data Warehousing
Assignment 2: Analytical SQL Assignment (20 marks)
Module 2, 2020
1. What type of source are you using: your own independent material, common knowledge, or someone 
else's independent material? 
2. If you are quoting someone else's material, is the quotation exact? Have you used quotation marks for 
quotations run into the text? Have you set off block quotes with an extra space before and after the quote, 
single spacing within the quote, and left indenting of all lines of the block quote? Are omissions shown 
with ellipses and additions with square brackets? 
3. If you are paraphrasing someone else's material, have you rewritten it in your own words and sentence 
structures? Does your paraphrase employ quotation marks when you resort to the author's exact 
language? Have you represented the author's meaning without distortion? 
4. Have you acknowledged each use of someone else's material? 
5. Do all references contain complete and accurate information on the sources you have cited? 
Late Submissions 
Assignment submissions past the deadline will NOT be marked and will result in the final mark of 0/20. 
Extensions 
Students seeking extensions for medical or other “outside of their control” reasons should email as soon as 
possible after those circumstances arise to the subject coordinator (artem.polyvyanyy@unimelb.edu.au). 
联系我们 - QQ: 99515681 微信:codinghelp2
© 2014 www.7daixie.com
程序代写网!