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 oneweek 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).