2023年11月25日

数据库代写 | FIT3003 S2 2020 Take Home Test – Part 2 (5%)

本次数据库代写是根据已有房地产公司数据进行查询、维护、设计数据仓库

FIT3003 S2 2020 Take Home Test – Part 2 (5%)

The E/R diagram of MRECompany’s existing operational database is shown below:
Figure 1 : E/R diagram of MREComany’s existing operational database
Since the staff have limited database knowledge and the operational database can be quite
complicated and large, MRECompany has decided to hire you as a Data Warehouse Engineer to
design and develop a Data Warehouse to keep track of the statistics and can effectively generate
reports.
The data warehouse must be able to answer at least the following questions:
a) The total leasing revenues according to different client companies’ sizes in 2019.
b) The total service charged MRECompany gets from charging water to clients in Quarter 1,
2016.
c) The number of contracts according to different leasing duration.
d) The number of contracts according to different client companies’ size and year.
Note that the manager wants to be able to drill down some information in the Data Warehouse in
these formats:
● Company size: Small company has less than 20 employees, Medium size company has
between 20-100 employees and Large size company has more than 100 employees
● Lease duration:
○ Short-term lease: less than 1 year
○ Medium-term lease: between 1 to 5 years
○ Long-term lease: more than 5 years
As you are required to design a Data Warehouse for Melbourne Real Estate company, its
Operational Database tables can be accessed through MREComany account. You can, for example,
execute the following query:
select * from MREComany.<table_name>;
Tasks:
[1]. Develop a star schema for MRECompany. Identify the fact table, dimensions and attributes
required to support the schema.
The result of this task is a star schema diagram. You can use any drawing tool, such as
Lucidchart, to draw the star schema.
[2]. Validate your star schema using the Two-Column Table Methodology. You are required to
illustrate the two-column tables for all fact measures and dimensions in your star schema
design.
[3]. Write the SQL commands to create the fact and dimension tables. You need to create a script
file containing the appropriate SQL commands to create the fact and dimension tables. The
operational tables are accessible from the MREComany account.
The result of this task is the SQL commands. You will also need to show the contents of the
tables that you have created.
[4]. Write the SQL commands to answer the following queries: (you need to make sure that there
are records in your fact and dimensions tables. For each of the following queries, write the
SQL and show the results):
a) The total leasing revenues according to different client companies’ sizes in 2019.
b) The total service charged MRECompany gets from charging water to clients in Quarter 1,
2016.
c) The total number of contracts signed with large-size companies according to different
leasing durations.
d) The number of contracts according to year and different client company’s sizes in
descending order.