FIT5137 Assignment 2 -S2 2025
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
FIT5137 Assignment 2 -S2 2025 (Weight = 40%)
Due - Friday, 26 September 2025, 11:55 PM
General Information and Submission
o This is an individual assignment.
o This assignment requires the Oracle database.You can connect using Either the Oracle service provided by Monash (Week 1 setup) OR the local Docker version of Oracle (Week 2 setup). Please ensure one of these is set up and running to access the required data.
o Submission method: Submission is online through Moodle.
o Penalty for late submission: 5% deduction for each day.
o Assignment FAQ: There is an Assignment Frequently Asked Questions page set up for the Assignment 2 on EdStem Forum.
o A marking rubric has been provided for Assignment 2: A2 Marking guide
Problem Description
Monash Equipment Centre (MonEquip) is an Australian-based company that provides a wide range of equipment for civil infrastructure and commercial construction projects. The company has two main business functions:
● selling equipment along with excellent after-sales service,
● providing equipment for hire while ensuring a seamless hiring experience.
MonEquip has different branches throughout the state of Victoria, in which the management team frequently generates reports to keep track of the business (e.g. calculating the revenues from hiring and selling equipment). The reports are then used for forecasting various trends and making predictions about the business.
MonEquip currently has an existing operational database which maintains and stores all of the information of their business transactions (e.g. product hiring, product sales, equipment, etc.) required for the management's daily operation. The operational database records the transaction from April 2018 to December 2020. However, since the staff at MonEquip has limited database knowledge and the operational database is quite large, the management team has decided to hire you as a Data Warehouse Engineer to design, develop, and quickly generate Business Intelligence reports from a new Data Warehouse.
As you are required to design a Data Warehouse for MonEquip, the Operational Database tables can be accessed through the MonEquip account. You can, for example, execute the following query:
Select * From MonEquip.
|
Please use the following statement to copy tables from the MonEquip account to your own schema for the exercises.
Create Table As select *
From MonEquip. If you are using the Docker version of Oracle, do not modify tables directly in the MonEquip account, as this may lead to inaccurate/incorrect query results. |
For the daily operation, MonEquip normally purchases equipment from its suppliers. The company then resells the equipment to customers while also providing a hiring service.
When an equipment is purchased, the price of the equipment is kept in the Equipment table as EquipmentPrice.
Meanwhile, the hiring rate is calculated as follows:
● The unit hire price is the hiring rate per day.
● If the customer returns the equipment within the same day, they only need to pay for 50% of the Unit_Hire_Price.
● Otherwise, Total_hire_price is calculated as (End_Date - Start_Date) * Unit_Hire_Price * Quantity.
A. Transformation Stage
The first stage of this assignment is divided into TWO main tasks:
1. Design a data warehouse for the above MonEquip database.
You are required to create a data warehouse for the MonEquip database. The management is especially interested in the following indicators :
● Average hire revenue per hire transaction
● Average sales revenue per sale transaction
● Total revenue for equipment hiring
● Total revenue for sales
● Number of equipment hired
● Number of equipment sold
|
Note: In the MonEquip database, the Total Sales Price and Total Hire Price fields represent the total amount received from each sales or hire transaction. For this assignment, you can use these fields to calculate revenue values when designing your data warehouse. While the database uses the term price, it's common in real-world systems for attribute names in operational databases to differ from the terminology used in business reporting or analytics. Operational systems may use technical or transactional labels, whereas analytical systems often adopt terms that reflect business meaning more clearly—such as referring to total prices as forms of revenue. As part of the warehouse design process, you are expected to make informed decisions about how to interpret and organize such fields to support business-focused analysis. |
The following shows some possible dimension attributes that you may need in your data warehouse:
● Time: Month, Year (derived from the Start Date of Hire for hire transactions and the Sale Date for sales transactions)
● Season [Spring:9 to 11, Summer: 12 to 2, Autumn: 3 to 5 and Winter: 6 to 8]
● Customer type
● Category
● Company branch
● Sales price scale: low sales <$5,000; medium sales between $5,000 and $10,000; high sales > $10,000
For the attribute, ensure that it meets the requirements of the range or group specified in your submission, if required in the specification.
- Preparation stage.
Before you start designing the data warehouse, you have to ensure that you have explored the operational database and have done sufficient data cleaning. Once you have done the data cleaning process, you are required to explain what strategies you have taken to explore and clean the data.
The outputs of this task for Report are:
a) If you have done the data cleaning process, explain the strategies you used in this process (you need to show the SQL to explore the operational database and SQL of the data cleaning, as well as the screenshot of data before and after data cleaning).
- Designing the data warehouse by drawing star/snowflake schema.
Design task A:
The star schema for this data warehouse may contain multi-facts. You need to identify the fact measures, dimensions, and attributes of the star/snowflake schema. The following queries might help you to determine the fact measures and dimensions:
● What was the total sales revenue in January, 2020?
● How many pieces of equipment were sold in Winter, 2018?
● How many equipments was hired by business customers?
● What was the total hire revenue in Clayton branch?
● How many trailers were hired by individual customers in Summer?
● What is the average sale revenue for Lighting equipment in 2019?
● What is the average hire revenue for Vehicles by individual customers?
● How much sales revenue was generated from a high sale in Summer?
Note:
● The star schema you created in Design Task A as the highest level of aggregation.
● The star or snowflake schema you design may or may not require a bridge table. If a bridge table is necessary, make sure to include the attributes GroupList and WeightFactor in the relevant dimension table.
Design task B:
In this assignment, consider the star schema you created in Design Task A as the highest level of aggregation. The MonEquip company manager wants to implement a drill-down function to explore more detailed information. Your task is to suggest several ways to increase the granularity of your fact tables from Design Task A. In other words, the manager wants to decrease the aggregation level of the fact tables you created in Design Task A.
The outputs of task A & B for Report are:
b) A star/snowflake schema diagrams for design task A. (You can use Lucidchart to draw the star schema.)
c) List suggestion of increase the granularity of your fact tables for design task B
2025-09-11