BUSA20001
Visualisation and Data Wrangling

Sample Exam


BUSA20001 1 Sample Exam
Overview and Instructions
For the actual exam instructions, please refer to the university LMS.
These sample questions are to give you a sense of the STYLE of questions to expect on the exam.
Marks per question and sub-question are INDICATIVE only and will be different for your actual exam.
The general structure of the exam will be the same:
Question 1 – ERD and SQL
Question 2 – Python
Question 3 – Visualisation critical analysis and recommendations, reporting architecture / process
recommendations.
Question 4 – Discussion around subject concepts
You can generally expect Questions 1 and 3 to be worth the most marks.

BUSA20001
Visualisation and Data Wrangling

Sample Exam


BUSA20001 2 Sample Exam
Question 1 [2+2+2+4+4+2+2+2+2+2][24]
UltraLux Retreats (ULR) offers exclusive private vacation villas to rent for holidays or functions. When
making a reservation, clients can also request extra services such as airport transfers, spa treatments, car
and driver, or even a chef to prepare meals. Payment must be made one month prior to reservation start, or
the reservation is cancelled.
The following Entity-Relationship Diagram documents part of the database that supports their website and
online reservations system.


BUSA20001
Visualisation and Data Wrangling

Sample Exam


BUSA20001 3 Sample Exam
(a) For each of the assertions (i) through (iii) below, discuss whether the database structure forces the
assertion to be true or not. Make reference to specific parts of the ERD to support your answers.
(i) When a client makes a booking, the reservation must be for one continuous series of days.
[2 marks]
(ii) A service may be available across an entire country, irrespective of city in that country.
[2 marks]
(iii) Property features are generic. The features available at any property can only be displayed as a
simple list.
[2 marks]
(b) Can the total price for a reservation, including charges for all services, be calculated automatically by the
system? Explain with reference to the diagram.
[4 marks]
(c) After a recent increase in the number of late cancellations, ULR wants to institute a policy that customers
must pay a deposit of 50% at the time of booking. The remaining balance would be due for payment 30
days prior to reservation start.

Describe why the existing database structure cannot support this new policy.
Describe the changes that would need to be made to the database to support this policy.
[4 marks]
(d) For each SQL statement (i) through (v) below, consider the information returned by each query. Using
everyday business language, describe the nature or business intent, and usefulness, of that information
to ULR staff or customers.
DO NOT rephrase the query (e.g., selects all records where is and sorts by
), or describe the logic of the query (e.g., returns , , and where
is less than 7.
AVOID making assumptions about the way you think ULR would/should typically keep records. Answer
only with reference to information as it is recorded in the database.
(i) SELECT Country, City, SUM(Amount) as Total
FROM Payment py
INNER JOIN Reservation r ON r.Payment_ID = py.Payment_ID
INNER JOIN Property pr ON pr.Property_ID = r.Property_ID
WHERE DATETRUNC(month, Date) = DATETRUNC(month, GETDATE())
GROUP BY Country, City
ORDER BY Total DESC
[2 marks]
(ii) SELECT
c.Name, c.Phone, c.eMail,
r.Total_Price, r.Start_Date, r.End_Date,
p.Address, p.City, p.Country
FROM Reservation r
INNER JOIN Property p ON p.Property_ID = r.Property_ID
INNER JOIN Client c ON c.Client_ID = r.Client_ID
WHERE Payment_ID IS NULL
AND DATEDIFF(day, GETDATE(), Start_Date,) BETWEEN 21 and 42
[2 marks]

BUSA20001
Visualisation and Data Wrangling

Sample Exam


BUSA20001 4 Sample Exam
(iii) SELECT Country, City, Address,
SUM(
DATE_DIFF(
day,
GREATEST(r.start_date, DATEFROMPARTS(2022, 1, 1)),
LEAST(r.end_date, DATEFROMPARTS(2022, 12, 31))
)
) / 365 * 100 as Rate
FROM Property pr
LEFT JOIN Reservation r ON r.Property_ID = pr.Property_ID
AND (DATEPART(year, r.End_Date) = 2022 OR DATEPART(year, r.Start_Date) = 2022)
GROUP BY Country, City, Address
ORDER BY Rate
[2 marks]
(iv) WITH rms AS (
SELECT Property_ID,
SUM(Max_Guests) as Guests,
SUM(CASE WHEN Room_Type = 'Bedroom' THEN 1 END) as Bedrooms,
SUM(CASE WHEN Room_Type = 'Bathroom' THEN 1 END) as Bathrooms
FROM Property_Room rm
GROUP BY Property_ID
)
SELECT TOP 1
Guests, Bedrooms, Bathrooms, Min_Night_Stay, Nightly_Rate
FROM Property_Rate rt
INNER JOIN rms ON rms.Property_ID = rt.Property_ID
-- Property and Date filters are arbitrary, change as needed
WHERE rt.Property_ID = 1234
AND Effective_Date <= '2023-03-25'
ORDER BY Effective_Date DESC

[2 marks]
(v) WITH ld AS (
SELECT Property_ID, MAX(End_Date) AS Av_From FROM Reservation
WHERE End_Date < DATEADD(week, 2, GETDATE())
GROUP BY Property_ID
),
nd AS (
SELECT Property_ID, MIN(Start_Date) AS Av_To FROM Reservation
WHERE Start_Date > DATEADD(week, 2, GETDATE())
GROUP BY Property_ID
)
SELECT p.*, ld.Av_From, nd.Av_To
FROM Property p
LEFT JOIN ld USING (Property_ID)
LEFT JOIN nd USING (Property_ID)
WHERE p.Property_ID NOT IN (
SELECT Property_ID FROM Reservation r
WHERE DATEADD(week, 2, GETDATE()) BETWEEN r.Start_Date AND r.End_Date
)
[2 marks]

BUSA20001
Visualisation and Data Wrangling

Sample Exam


BUSA20001 5 Sample Exam
Question 2 [2+3+2+2+2+3+2][16]
Consider the following Python program, used by XYZ Bank Ltd:


BUSA20001
Visualisation and Data Wrangling

Sample Exam


BUSA20001 6 Sample Exam
(a) What is the main intent of this program – why would someone have written it?
[2 marks]
(b) By examining the logic of this code, describe the incentives that XYZ Bank Ltd offers customers to keep
their money in XYZ bank accounts.
[3 marks]
(c) Give better names to the two control parameters, date1 and date2. How does setting different values for
these control the scope and logic of the program?
[2 marks]
(d) Describe the purpose of parameters, b, r1, r2 and r3. What is the interdependence between b and r1, r2,
and r3 (i.e., can one on them be modified without needing to give any consideration to whether the other
also needs to be modified)?
[2 marks]
(e) Give a better name to the control parameter g. Describe the purpose of this parameter.
[2 marks]
(f) Give a better name to the function somefunc. Summarize its logic using everyday business language so
that a business person who knows nothing about programming could understand what it does.
[3 marks]
(g) Consider the rather generic column headers Col1 and Col2, and associated Description 1 and
Description 2. What is the difference between Col1 and Col2? Assuming that the two descriptions would
end up on a customer’s transaction statement, what would we expect these two descriptions to typically
say?
[2 marks]


BUSA20001
Visualisation and Data Wrangling

Sample Exam


BUSA20001 7 Sample Exam
Question 3 [2+8+9+4][23]
You have recently been hired as a consultant to review reporting practices and offer advice to a large
government agency, specifically the central accounting & finance (A&F) department who is responsible for
tracking and reporting the agency’s various annual operating and projects budgets. There is a monthly
review meeting held between A&F and the managers responsible for each budget.
The Head of A&F tells you that it is critical to be able to identify as early as possible any major budget
overruns. The process to approve increases to budgets is a long one and requires many approvals. In the
case where approval is not granted, it is important that other departments are advised to prioritize only
essential expenditures and defer discretionary ones.
You have been provided a screenshot of the current reporting dashboard, implemented with Microsoft
PowerBI software. You have also come to learn that the current process to prepare for the monthly meeting
has several problems. Updating the dashboard is time consuming and error prone. First, reports from the
accounting system (Oracle Financials, a web browser-based application) are generated for each budget.
These are then exported into Excel files. The exports must then be edited (there are many lines with extra
headings, empty rows, sub-totals, and totals that must be deleted), then combined manually (copy/paste with
additional columns added to identify which budget etc.) into a single Excel file. Several embarrassing
mistakes have been made in the past where data was missing or misaligned to the wrong budget. To make
matters worse the key analyst who was responsible for doing this each month has resigned and moved on.
However, the Head of A&F sees this as something of an opportunity to perhaps rethink the reporting process
and hire somebody with relevant skills to implement and maintain the changed process. Ideally, they’d like
the new process to be as automated as possible to prevent mistakes, but also to save time.




BUSA20001
Visualisation and Data Wrangling

Sample Exam


BUSA20001 8 Sample Exam
(a) Briefly describe the strengths of the design of this dashboard.
[2 marks]
(b) Critically assess the weaknesses of the design of this dashboard and suggest ways to improve the
design.
[8 marks]
(c) To address the problems with the current reporting architecture and manual process, consider 3
alternative ways there might be to wangle the data out of Oracle Financials into the dashboard. What are
the potential issues, limitations, and benefits of each of these alternatives?
[9 marks]
(d) What advice would you give the Head of A&F about selecting good candidates to interview to replace the
previous analysts - what skills and experience would the best candidates have?
[4 marks]




BUSA20001
Visualisation and Data Wrangling

Sample Exam


BUSA20001 9 Sample Exam
Question 4 [7]
Please note this question has optional components.
You do not need to answer all questions. You need to answer ONLY TWO (2) of the following three
questions. If you answer all three questions, you will score marks for (a) and (b) only.
CHOOSE to answer ANY ONE (1) of (a), (b) and (c)
a) “The better the question, the better the answer”.

Critically discuss this statement as it relates to data analysis. Use two examples from any or all the three
assignment projects you completed in this subject, or your own professional experience.
[7 marks]
b) “Having a good design concept is important at the start of a project.”

Critically discuss this statement as it relates to constructing dashboards and reports. Use two examples
from any or all the three assignment projects you completed in this subject, or your own professional
experience.
[7 marks]
c) “Clear documentation about the database structure and content, such as an Entity-Relationship Diagram
and list of field codes and meanings, is essential to successful data analysis.”

Discuss this statement. Use two examples from any or all the three assignment projects you completed
in this subject, or your own professional experience.
[7 marks]