EXAM CODES: TITLE OF PAPER: EXAM DURATION:
Semester Two 2021 Examination Period
Faculty of Information Technology
Business Intelligence and Data Warehousing – SAMPLE 3
2 hours 10 minutes or 130 minutes
THIS PAPER IS FOR STUDENTS STUDYING AT: (tick where applicable)
Caulfield Clayton Parkville Peninsula
Monash Extension Off Campus Learning Malaysia Other (specify)
Sth Africa
Office Use Only
During an exam, you must not have in your possession any item/material that has not been authorised for your exam. This includes books, notes, paper, electronic device/s, mobile phone, smart watch/device, calculator, pencil case, or writing on any part of your body. Any authorised items are listed below. Items/materials on your desk, chair, in your clothing or otherwise on your person will be deemed to be in your possession.
No examination materials are to be removed from the room. This includes retaining, copying, memorising or noting down content of exam material for personal use or to share with any other person by any means following your exam.
Failure to comply with the above instructions, or attempting to cheat or cheating in an exam is a discipline offence under Part 7 of the Monash University (Council) Regulations, or a breach of instructions under Part 3 of the Monash University (Academic Board) Regulations.
AUTHORISED MATERIALS
CALCULATORS
SPECIFICALLY PERMITTED ITEMS if yes, items permitted are:
YES YES YES
NO NO NO
Candidates must complete this section if required to write answers within this paper
STUDENT ID: __ __ __ __ __ __ __ __ DESK NUMBER: __ __ __ __ __
Page 1 of 35
Question 1:
Consider the following Student Enrolment star schemas: Star Schema Version-1 does not have a dimension hierarchy, whereas Star Schema Version-2 has a dimension hierarchy: from country to state, and to campus.
Star Schema Version-1
Star Schema Version-2
Page 2 of 35
Questions:
a. In contrasting both star schemas, is there any mistake in any of the two star schemas (Note that Star Schema Version-1 does not have a hierarchy, and Star Schema Version-2 does have)?
• If yes, state which star schema, and explain your reason.
• If no, also explain your reason.
b. Compare both star schemas.
• If there are mistakes in any (or both) star schemas, you need to draw the
correct schema(s) first before comparing between each other.
• If there are no mistakes in both star schemas, you can immediately compare
the two star schemas.
Also, when you compare the two star schemas, you need to use some sample data (in the fact and in certain dimensions) to support your arguments
Write your answers here:
Page 3 of 35
Continue your answers here:
Page 4 of 35
Continue your answers here:
Page 5 of 35
Question 2
Page 6 of 35
This question is taken from the Bookshop Case Study on Temporal Data Warehousing. The following shows a star schema shows a fact table (number of books sold) and three dimensions (e.g. Month, Branch, and Book). The Book dimension is temporal dimension, which contains a temporal attribute, called Price, which is book price.
Branch_DIM
Branch_ID Branch_Address
BOOKSALES_FACT
Branch_ID Month_ID Book_ID Number_Books_Sold
Book_ID Book_Title Author
Book_Price_DIM
Book_ID Start_Date End_Date Price Remarks
The tables for this star schema have been created and populated from the operational database. The sample data is as follows:
Month_DIM Table
201503 201502 201501 201412 etc
Branch_DIM Table
Branch_Address
City etc
Melbourne Central Shopping Centre, Melbourne 285 Dandenong Road, Chadstone
199 Burke Road, Camberwell
Book_DIM Table
Page 7 of 35
Book_Title
CSIRO Team Rowling
C1 201401 201407 $45.95 C1 201408 201410 $36.75 C1 201411 201501 $23.00 C1 201502 201512 $45.95 H6 201401 201403 $21.95 H6 201404 201501 $30.95 H6 201502 201512 $10.00 DV 201401 201512 $27.95 …………
BookSales_Fact Table
C1 CSIRO Diet
H6 6
DV Da ………
Book_Price_DIM Table
Start_Date
Full Price
20% Discount
Half Price
Full Price Launching
Full Price
End of Product Sale Full Price
Number_Books_Sold
201503 City
201503 City
201503 City
201503 City …
C1 5 H6 15 DV 23
201503 Chadstone
201503 Chadstone
201503 Chadstone
201503 Chadstone …
C1 15 H6 3 DV 2
201503 Camberwell 201503 Camberwell 201503 Camberwell 201503 Camberwell … 201503 … … ………
C1 1 H6 1 DV 2
201412 City
201412 City
201412 City
201412 City …
C1 15 H6 6 DV 6
201412 Chadstone
201412 Chadstone
201412 Chadstone
201412 Chadstone …
C1 10 H6 8 DV 1
201412 Camberwell 201412 Camberwell 201412 Camberwell 201412 Camberwell … 201412 … … ………
C1 18 H6 3 DV 2
Write the SQL command to produce the following report (10 marks):
201503 City
201503 City
201503 City
201503 City
201503 Chadstone
201503 Chadstone
201503 Chadstone
201503 Chadstone
201503 Camberwell
201503 Camberwell
201503 Camberwell
201503 Camberwell
201503 … ……… ………
201412 City
201412 City
201412 City
201412 City
201412 Chadstone
201412 Chadstone
201412 Chadstone
201412 Chadstone
201412 Camberwell
201412 Camberwell
201412 Camberwell
201412 Camberwell
201412 … ………
The structures of the above tables are as follows:
SQL> desc Month_DIM; Name
SQL> desc Branch_DIM;
BRANCH_ID BRANCH_ADDRESS
CSIRO Team Rowling
CSIRO Team Rowling
CSIRO Team Rowling
CSIRO Team Rowling
CSIRO Team Rowling
CSIRO Team Rowling
$45.95 5 $10.00 15 $27.95 23
$45.95 15 $10.00 3 $27.95 2
$45.95 1 $10.00 1 $27.95 2
$23.00 15 $30.95 6 $27.95 6
$23.00 10 $30.95 8 $27.95 1
$23.00 18 $30.95 3 $27.95 2
C1 CSIRO Diet H6 6 DV Da
Page 8 of 35
Book_Title
Number_Books _Sold
C1 CSIRO Diet
H6 6 DV Da …
C1 CSIRO Diet H6 6 DV Da
C1 CSIRO Diet H6 6 DV Da
C1 CSIRO Diet
H6 6 DV Da …
C1 CSIRO Diet H6 6 DV Da
Type VARCHAR2(6)
VARCHAR2(15) VARCHAR2(50)
SQL> desc Book_DIM; Name
BOOK_ID BOOK_TITLE AUTHOR
SQL> desc Book_Price_DIM; Name
BOOK_ID START_DATE END_DATE PRICE REMARKS
SQL> desc BookSales_Fact; Name
MONTH_ID BRANCH_ID
BOOK_ID NUMBER_BOOKS_SOLD
VARCHAR2(5) VARCHAR2(20) VARCHAR2(20)
VARCHAR2(5) VARCHAR2(6) VARCHAR2(6) NUMBER(6,2) VARCHAR2(20)
VARCHAR2(6) VARCHAR2(15) VARCHAR2(5) NUMBER
Page 9 of 35
Write your answer here:
Page 10 of 35
Question 3
Page 11 of 35
This question is taken from the Product-Sales-Supplier Case Study.
The director of a company is interested in analyzing the statistics of its product sales history. The analysis is needed for identifying which products are popular, which suppliers supply those products, when is the best time to purchase more stock, etc. You are required to design a small Data Warehouse to keep track of the statistics.
The director is particularly interested in analyzing the total sales (Quantity * Price) by product, customer locations (suburbs and postcodes), sales time periods (monthly and yearly), and supplier.
The operational database currently has the following tables:
Your snowflake schema will have a Bridge Table connecting Product Dimension and Supplier Dimension. A snowflake schema with a Bridge Table as shown below:
Page 12 of 35
Year Month
PRODUCTSALES FACT
TimeID ProductNo CustLoc
TotalSales
PRODUCTDIM
ProductType
CUSTLOCDIM
Suburb Postcode
PRODSUP-BRIDGE
ProductNo SupplierID
SUPPLIERDIM
SupplierID
SupplierName
The above snowflake schema is missing two attributes: WeightFactor attribute, and ListAGG attribute.
Questions:
a. Draw a new snowflake schema (call it Snowflake Schema version 2) for the above case study, but this new snowflake schema must use a WeightFactor attribute (without ListAGG attribute). You also need to show sample records in the Product Dimension, the Bridge Table, and the Supplier Dimension. The sample data must show the correct values for the Weight attribute. Make sure that in your snowflake schema, the attributes are clearly shown.
b. Draw another snowflake schema (call it Star Schema version 3), which also has a Bridge Table and a WeightFactor attribute. But version-3 snowflake schema has the ListAGG attribute. You also need to show sample records in the Product Dimension, the Bridge Table, and the Supplier Dimension. The sample data must show the correct values for the Weight and ListAGG attributes.
c. Write the SQL query to create the ProductDim table for the Star Schema version 3.
Write your answer here:
Page 13 of 35
Continue your answer here:
Page 14 of 35
Continue your answer here:
Page 15 of 35
Question 4
This question is based on the Robcor case study. The following is the E/R diagram of the operational database in the Robcor case study:
A star schema for the above operational database is shown as follows:
Robcor Star Schema-1
Page 16 of 35
Questions:
a. Is it possible to determine which level Robcor Star Schema-1 is? If it is possible, state the level and also give the reason. If it is not possible to state the level, then give the reason.
b. Let’s have a look at the following star schema (Robcor Star Schema-2). Between the two star schemas (Robcor Star Schema-1 and Robcor Star Schema-2), which one has a higher level of aggregation? State the name of the star schema, whether it is Robcor Star Schema-1 or Robcor Star Schema-2, and explain the reason.
Robcor Star Schema-2
Page 17 of 35
Write your answers here:
Page 18 of 35
Question 5
Page 19 of 35
Given the following star schema:
The tables (e.g. Fact and three dimensions) have been created and have also been populated with an adequate number of records. The table names and attributes are shown in the star schema above.
Write the SQL for the following OLAP queries:
a. Display the top 10 average prices by suburb of property
b. Display the average price of properties by property type description and suburb. It is
not required to show the subtotals or group totals or grand total
Write your answer here:
Page 20 of 35
Question 6
This question is about Top n% and Top k (such as Top 10% and Top 3) in OLAP. The tables are based on the ROBCOR data warehouse case study, which consists of one fact and three dimension tables: charter_fact, time, pilot, and model.
SQL> desc charter_fact; Name
TIME_ID MOD_CODE EMP_NUM TOT_CHAR_HOURS TOT_FUEL REVENUE
SQL> desc time; Name
TIME_ID TIME_YEAR TIME_MONTH
SQL> desc pilot; Name
EMP_NUM PIL_LICENSE PIL_RATINGS PIL_MED_TYPE PIL_MED_DATE PIL_PT135_DATE
SQL> desc model; Name
MOD_CODE MOD_MANUFACTURER MOD_NAME MOD_SEATS MOD_CHG_MILE MOD_CRUISE MOD_FUEL
VARCHAR2(6) CHAR(10) NUMBER(10) NUMBER NUMBER NUMBER
CHAR(6) CHAR(4) CHAR(2)
NUMBER(10) CHAR(25) CHAR(25) CHAR(1) DATE
CHAR(10) CHAR(15) CHAR(20) FLOAT(126) NUMBER(19,4) FLOAT(126) FLOAT(126)
Page 21 of 35
Questions:
a. Write the SQL command to display the time periods which had the revenue in the top 10% of the months.
The result should be like this:
TIME_ID TOTAL
199503 51144.16 199408 49775.51 199510 48538.01 199409 47647.75 199703 45872.32
PERCENT_RANK
1 .975609756 .951219512 .926829268 .902439024
b. Write the SQL command to display the mod_code and mod_name of the two airplanes that have the largest total fuel used.
The result should look like this:
MOD_CODE MOD_NAME
PA31-350 Navajo Chieftain C-90A KingAir
TOTAL MYRANK
83790.5 1 61708.4 2
Page 22 of 35
Write your answer here:
Page 23 of 35