Sample Paper 2
docx
keyboard_arrow_up
School
University of Melbourne *
*We aren’t endorsed by this school
Course
90520
Subject
Information Systems
Date
Feb 20, 2024
Type
docx
Pages
4
Uploaded by DeaconMetal12261
ERD
Look at keywords!
-
Time
-
Different
-
Same
-
Multiple
a)
Customers can pay for multiple orders
at the same time. Customers and Receipts have a required one to many relationship, which means one
customer can be issued multiple receipts however a receipt can only be assigned to
one customer. With Customer ID as FK in the Receipt table, it allows the customer to
pay for multiple orders i.e. multiple receipts at the same time.
Receipt id receipt and job table
b)
If a customer orders multiples of the same product, all items of that product must
be customised the same way. x
c)
Each product that TH on sells is only provided by a single supplier. If a supplier
discontinues a product, TH must also discontinue that product.
The supplier and Inventory don’t have a direct relationship with each other that’s
why there is an associative entity called Supplier Catalogue. This means that supplier
and Inventory have a many-to-many relation meaning that TH could get supply from
a different supplier for the same product.
d)
Employees have some discretion to offer discounts when taking orders from
customers, and the company offers seasonal promotions from time to time. The
owner has become concerned however, that some employees may have been
giving overly generous discounts. Is there sufficient information in the database to
support automatic calculation of what discount amounts (absolute and/or
percentage) have been given to customers?
No, there is no mention of employees and discounts in the ERD.
What is the overall purpose of the query – how is the information returned useful to TH?
What actions or decisions do you think an employee might make in response to the
information returned?
The following query is helping TH determine customer specific Job IDs where they are yet to
assign a pickup date for completed production. This helps in prioritising orders which are yet
to be completed and are running on a schedule due to the assigned date. This helps the
employees in keeping a track and making sure that they update the customers timely about
their orders.
The following query is helping TH determine customer specific Job IDs where they are yet to
assign a pickup date and orders are yet to be completed. This helps in prioritising orders
which are yet to start. This helps the employees in keeping a track and making sure that they
update the customers timely about their orders.
What is the business meaning of the filter condition found on line 4?
It is filtering out Job IDs where they haven’t given an update to customers about their order
pick-ups.
What is the business meaning of the filter condition found in lines 5 to 9?
It is filtering out Job IDs which are yet to have a completion date. This in turn helps TH to
prioritise orders which are yet to start.
What is the overall purpose of the query – how is the information returned useful to TH?
What actions or decisions do you think an employee might make in response to the
information returned?
The following query is trying to figure out the days inventory will last for each product for TF
depending on the historical orders received in the past quarter. This helps them in inventory
planning and restocking depending on the demand for each product.
This query has an error in the calculation of the time_period column. What is the error,
and how should it be corrected?
Company doesn’t operate on Sundays, it should be 91-12 (excluding Sundays)
Python
Consider the two control parameters, control_1
and control_2.
a)
How does setting different values for them control the scope and logic of the
program? The following parameters are helping in filtering rental data for getting information 6
months prior to (including) 31
st
of December 2023.
Dynamic filtering helps the end user to directly filter the dates without editing the
code or query inside.
b)
Considering the logic of the rest of the program, what advice would you give to an
end user about valid values that can be set for these parameters?
It can have any date for the month Instead of having control 2 parameter as 6 it could
have it as 12 to get the whole year monthly data, to compare it with previous year’s
data.
Converting KPI to % totals seconds in a months
is converting how many s
What is the main intent of this program? (i.e., why did someone write it, how might the
output(s) be relevant to the general manager?) The intent of this program is to get past 6-month information about rental vehicles, and the
average time they were rented for to understand the demand for vehicles and identify
outliers. This will also help the business in identifying vehicles which are scheduled to
service and cleaning due to higher rental duration.
The filename and column names used in the first ‘output_1.csv’ file produced are very
generic. This might lead to some misinterpretation of the data. Suggest better names for
the file as well as the Date and KPI columns so that the content of the file is more likely to
be reliably interpreted. Note that it's fine
to suggest names that would require some
additional code to implement. You may assume the code can
be changed to rename these.
Registration should be renamed as Vehicle Registration, Date as Rented Date and KPI as
Rental Duration (in months).
CSV file can be renamed as Rental Duration Info.
The filename and column names used in the second ‘output_2.csv’ file produced are also
very generic. This might lead to some misinterpretation of the data. Suggest better names
for the file as well as the KPI column so that the content of the file is more likely to be
reliably interpreted. Note that it's fine to
suggest names that would require some
additional code to implement. You may assume the code can
be changed to rename these.
Registration should be renamed as Vehicle Registration, Date as Rented Date and KPI as
Average Rented Duration.
CSV file can be renamed as December Rental Information.
Average usage of the cars in the month
Explain how values in the two KPI columns should be interpreted, e.g., the expected range
of values, where within this range unfavourable values might be, or the implication of
changing values over time.
KPIs range would be from 0-100 low values are bad, since its only 6 months information that
we are fetching. As the parameter 2 changes the range will also change e.g., if we want
information of past 12 months the parameter would be 12 and the KPI range will be 0-12.
The other KPI is just the average duration for which a vehicle is being rented.
Tableau
Briefly describe the strengths of the design of this dashboard.
The dashboard has overall good font sizes and is able to tell the business’s story and gives
the user more freedom to get a conclusion and develop their own insights.
Majority of the graphs are well labelled and has tried to maintain a similar colour scheme for
similar factors throughout.
Critically assess the weaknesses of the design of this dashboard, then suggest ways to
improve the design.
Dashboard lacks an appropriate title like “Month’s Budget Review”. The KPIs should be on top of the dashboard, and the font sizes for the label should be
increased and their label names need to be reviewed as well.
Have 2 separate graphs for Budget Comparison is redundant and wastage of space, instead it
should have a combined graphs displaying each department’s budget by location. Instead of
having labels for each point just have labels for minimum and maximum expenditure and
forecast %, this would make the dashboard look cleaner.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Due to lack of space on the dashboard the labels are not fully visible for the pie charts.
Therefore, instead of having 3 separate pie charts it should have 3 bar graphs on one axis
representing each department’s expenditures, obligations, and equipment for better
visualisation.
For the Change in Expenditure and Total Expenditure chart, even though the total
expenditure chart looks clean and fancy it is not giving us important information like what
share of expenditure belongs to each department, therefore it should also highlight each
department’s expenditure.
As for change in expenditure first-hand these no.s need not be this precise and should be
replaced with M or K, additionally instead of throwing these vague no.s which are of no use
it should have a comparison bullet chart comparing both year’s expenditure by department
side by side.
Additionally, even though O&M department has had the highest expenditure, I see no point
in having a monthly chart for it, which is anyways too complicated to read.
To address the problems with the current manual data wrangling process, proposes 2
alternative ways there might be to wrangle the data between Oracle Financials and the
dashboard. What are the potential issues, limitations, and benefits of each of these 2
alternatives? Limitation- can’t automate things if there are no clear rules and logic, database needs to
exist. Needs to have same structure across the data.
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?
While interviewing, a future analyst can be given a data and asked to create a rough sketch
of the dashboard and discuss the thinking behind that sketch to filter good candidates.
Additionally,