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.

Question 1 [22 marks]

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.

 

(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 and can only be displayed on the website as a simple list of features available at each property. [2 marks]

(b)  When saving a new reservation, can the total price including charges for all services, be calculated

automatically by the system? Explain with reference to the diagram. [2 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. If a cancellation is made more than a month before the reservation starts, it will be refunded. 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 <field> is <some criteria> and sorts by <field>).

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 DATE_TRUNC(Date, 'month') = DATE_TRUNC(GETDATE(), 'month')

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

AND DATE_DIFF(GETDATE(), Start_Date, 'day') BETWEEN 21 and 42 [2 marks]

(iii) SELECT Country, City, Address,

SUM(

DATE_DIFF(

GREATEST(r .start_date, DATE_PARSE('2022-01-01', 'yyyy-MM-dd')),

LEAST(r .end_date, DATE_PARSE('2022-12-31', 'yyyy-MM-dd')),

'day'

)

) / 365 * 100 as Rate

FROM Property pr

LEFT JOIN Reservation r ON r .Property_ID = pr .Property_ID

AND (YEAR(r .End_Date) = 2022 OR 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

-- Change Property and Date filters as needed

WHERE rt .Property_ID = 1234

AND Effective_Date <= DATE_PARSE('2022-07-02', 'yyyy-MM-dd')

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]

Question 2 [16 marks]

Consider the following Python program, used by XYZ Bank Ltd:

 

(a)  What is the main intent of this program – why would someone have written it? [1 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, r3 (i.e. can one on them be edited without giving consideration whether the others might also need to be edited)? [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. Document the algorithm implemented by this function, i.e.,

summarize its logic using everyday business language so that a business person who knows nothing about programming could understand what it does. [4 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]

Question 3 [20 marks]

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.

 

(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 TWO        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? [6 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]