THE UNIVERSITY OF NEW SOUTH WALES
SCHOOL OF INFORMATION SYSTEMS AND TECHNOLOGY MANAGEMENT
TERM 1 2023
COMM2822: INTRODUCTION TO DATABASE FOR BUSINESS ANALYTICS
FINAL EXAMINATION
1. Writing Time: 3 Hours.
2. Reading and Submission Allowance Time: 1 Hour.
3. This is an Online Open-Book Exam, your responses must be your
original work. You must attempt this Final Exam by yourself without any
help from others. Thus, you have NOT worked, collaborated or colluded
with any other persons in formulating your responses. The work that
you are submitting for your Final Exam is your OWN work.
4. Release date/time (via Moodle): Wednesday, 10 May 2023, 8:00am
(Australian Eastern Time Zone)
5. Submission date/time (Via Turnitin): Wednesday, 10 May 2023,
12:00pm (Australian Eastern Time Zone)
6. Failure to upload the exam by the submission time will result in a
penalty of 15% of the available marks per hour of lateness.
7. This Examination Paper has 7 pages, including the cover page.
8. Total number of Questions: 3 Questions.
9. Answer all Questions.
10. Total marks available: 100 marks. This examination is worth 50% of the
total marks for the course.
11. Questions are not of equal value. Marks available for question sub-parts
are shown on this examination paper.
12. Some questions have word limits as indicated on the question. These
word limits must be adhered to. Text in excess of the specified word
limit(s) may not be considered in the marking process.
13. Answers to questions are to be written in the exam answer sheet
template provided. Please ensure that you provide all details required on
the cover sheet of your Final Exam answer sheet.
PAGE 2 OF 7
14. Failure to submit exam answers with the correct exam answer sheet will
result in 10% penalty of your overall exam marks (i.e. 5 marks).
15. Students are reminded of UNSW’s rules regarding Academic Integrity
and Plagiarism. Plagiarism is a serious breach of ethics at UNSW and is
not taken lightly. For details, see Examples of plagiarism.
16. The use of any generative AI tools or services to search for or generate
information or answers is prohibited. If its use is detected, it will be
regarded as serious academic misconduct and subject to the standard
penalties, which may include 00FL, suspension and exclusion.
17. This Final Exam is an open book/open web, and further information is
available “Here”.
• You are permitted to refer to your course notes, any materials
provided by the course convenor or lecturer, books, journal articles,
or tutorial materials.
• It is sufficient to use in-text citations that include the following
information: the name of the author or authors; the year of
publication; the page number (where the information/idea can be
located on a particular page when directly quoted), For example,
(McConville, 2011, p.188).
• You are required to cite your sources and attribute direct quotes
appropriately when using external sources (other than your course
materials).
• When citing Internet sources, please use the following format:
website/page title and date.
• If you provide in-text citations, you MUST provide a Reference List.
The Reference list will NOT BE counted towards your word limit.
18. Students are advised to read the Final Exam paper thoroughly before
commencing.
19. The Lecturer-in-Charge (LiC) / Exam Referee will be available online (via
Moodle) after the Open-book Exam paper is released for a period of one
hour.
PAGE 3 OF 7
QUESTION 1 45 MARKS
The local shop in your town has hired you to design and implement a database for their
online orders using Oracle. They have provided you with the below sample report.
Note:
Most of the columns are self-explanatory, and the below columns provide further
explanations:
(i) RRP/Item: Recommended Retail Price is the price for an item without any
discount.
(ii) Cost/Item: This is the cost price per item.
(iii) Discount/Item: This is the discount per item for a customer in a promotion
period (a promotion period has a start date and an end date).
(iv) OrderAmount: (RRP/Item – Discount/Item ) * (Quantity Ordered)
(v) Profit: (RRP/Item – Cost/Item – Discount/Item) * (Quantity Ordered)
Moreover,
(vi) For all Oracle SQL queries, you must use ANSI JOIN Method (i.e., you must
use inner join, outer join, left join or right join) where applicable.
(vii) You do not need to run the SQL queries in Oracle SQL Developer, but the SQL
queries must follow the Oracle SQL syntax.
Order
No Order Date
Cust.
ID
Customer
Name Item ID Item Desciption
RRP
/Item
Cost
/Item
Discount/
Item
Quantity
Ordered
Order
Amount Profit
Expected
Delivery
85750 1/02/2023 10001 Edward Smith H-123 Hardware 123 985$ 700$ 1 985$ 285$ 9/02/2023
85750 1/02/2023 10001 Edward Smith H-976 Hardware 976 950$ 425$ 2 1,900$ 1,050$ 9/02/2023
85750 1/02/2023 10001 Edward Smith S-765 Software 765 150$ 98$ 5 3 435$ 141$ 7/02/2023
85750 1/02/2023 10001 Edward Smith S-766 Software 766 750$ 250$ 3 2,250$ 1,500$ 7/02/2023
85751 2/02/2023 10983 Ben Wu H-123 Hardware 123 985$ 700$ 2 1,970$ 570$ 3/02/2023
85751 2/02/2023 10983 Ben Wu N-710 Network 710 80$ 25$ 10 800$ 550$ 3/02/2023
85752 3/02/2023 10082 Dylan Duan N-710 Network 710 80$ 25$ 5 400$ 275$ 4/02/2023
85753 4/02/2023 10983 Ben Wu H-123 Hardware 123 985$ 700$ 2 1,970$ 570$ 8/02/2023
85753 4/02/2023 10983 Ben Wu N-710 Network 710 80$ 25$ 2 160$ 110$ 8/02/2023
85753 4/02/2023 10983 Ben Wu S-766 Software 766 750$ 250$ 2 1,500$ 1,000$ 8/02/2023
85754 4/02/2023 10001 Edward Smith H-976 Hardware 976 950$ 425$ 2 1,900$ 1,050$ 9/02/2023
85754 4/02/2023 10001 Edward Smith S-765 Software 765 150$ 98$ 3 450$ 156$ 7/02/2023
PAGE 4 OF 7
Required:
With reference to the above scenario, answer the following three questions:
(a) Using the above table structure, draw the functional dependency diagram in the
First Normal Form (1NF). Identify, label, and explain the selection of Primary
Key(s), all transitive and partial dependencies. Write the relational schemas and
show all the primary keys and foreign keys.
Hint: you have to remove the derived attributes: order_amount and profit.
(15 Marks) [No word limit]
(b) Create 2NF, 3NF, and BCNF (if necessary), showing all intermediate steps in the
normalisation process. Write the relational schemas and show all the primary
keys and foreign keys.
(20 marks) [No word limit]
(c) Assuming your relational schemas in Question 1 (b) are now converted into
tables. Write an SQL query (based on Oracle SQL syntax) using the SQL Join
clause on these tables to create the sample report.
(10 Marks) [No word limit]
PAGE 5 OF 7
QUESTION 2 30 MARKS
PAGE 6 OF 7
Note:
(i) For all Oracle SQL queries, you must use ANSI JOIN Method (i.e., you must
use inner join, outer join, left join or right join) where applicable.
(ii) You do not need to run the SQL queries in Oracle SQL Developer, but the
SQL queries must follow the Oracle SQL syntax.
Required:
With reference to the scenario, answer the following five questions:
(a) Write or draw the most appropriate relational tables (note: any supertypes and
subtypes should be resolved as a relational table(s)).
(5 marks) [No word limit]
(b) Write a SQL query to list all the sections and names of the section managers.
(2 marks) [No word limit]
(c) Write a SQL query to find the maximum salary of each section (list section
name).
(3 marks) [No word limit]
(d) Write a SQL query to retrieve the nurses who earn at least $15,000 more than
the nurses who earn the least amount in the same section. List the nurses’ ID,
first name, last name, and salary.
(5 marks) [No word limit]
(e) For each section that has more (job class = 1) than (job class = 2), write an SQL
query to retrieve the section name and total average salary of the nurses
working for that section in Sydney.
For the SQL query, you must satisfy two conditions:
(i) You must have at least two sub-queries clauses in the FROM clause
(sub-queries defined as part of the joins (inner, outer, left or right)
statements are acceptable).
(ii) You must have at least one clause using Group By, but you cannot use
a HAVING clause to test any condition.
(15 marks) [No word limit]
PAGE 7 OF 7
QUESTION 3 25 MARKS
Facebook owes us $725 million: here’s how to get your cut
By Sheena Vasani on 20 April, 2023, 5:22 AM
Source: Facebook owes us $725 million: here’s how to get your cut (msn.com)
Case Study:
“Facebook parent company Meta agreed to pay US$725 million to resolve a lawsuit
accusing it of granting Cambridge Analytica and other third parties access to private
user data, which a judge has tentatively approved. The tech giant also faced
accusations it misled users about how much control they had over their private
information. Meta, however, denies it did anything wrong.
In 2018, Cambridge Analytica — a now-defunct consulting firm that worked with former
President Donald Trump’s campaign — accessed the private data of as many as 87
million Facebook users, thus prompting the lawsuit. A researcher who was granted
permission by Facebook to deploy an app harvesting user data gave Cambridge
Analytica the information.
That lawsuit eventually expanded to cover other cases where Facebook was accused
of granting access to user data without their consent to other third parties.
According to lawyers for the plaintiff, the proposed $725 million settlement is the largest
ever achieved in a US data privacy class action case. It’s also the most Meta’s ever
paid to settle a class action lawsuit.”
Required:
With reference to the above case study, answer the following two questions:
(a) Analyse and recommend how Meta (Facebook) should address their data ethics
issues using the 7-step Ethical Decision Making framework.
(15 Marks) [450 words limit]
(b) Justify a type of data governance model you would recommend Meta
(Facebook) to implement.
(10 Marks) [300 words limit]
— END OF EXAMINATION PAPER —