Semester 1 2022 Examination Period Faculty of Information Technology EXAM CODES: FIT5195 TITLE OF PAPER: Business Intelligence and Data Warehousing - SAMPLE1 EXAM DURATION: 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 □ Sth Africa □ Other (specify) 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 OPEN BOOK □ YES ⌧ NO CALCULATORS □ YES ⌧ NO SPECIFICALLY PERMITTED ITEMS □ YES ⌧ NO if yes, items permitted are: Candidates must complete this section if required to write answers within this paper STUDENT ID: __ __ __ __ __ __ __ __ DESK NUMBER: __ __ __ __ __ Question 1:total 10 Marks 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. Question: a. Write the SQL command to display the time periods which had the revenue in the top 10% of the months. (5 marks) The result should be like this: b. Write the SQL command to display the mod_code and mod_name of the two airplanes that have the largest total fuel used. (5 marks) The result should look like this: Question 2:total 10 Marks A data warehouse for this Truck Delivery case study has been created, and the snowflake schema is shown as follows: Snowflake schema The dimension and fact tables for this snowflake schema have been created, and the contents of these tables are shown as follows: The structures of these tables are as follows: Questions: (a) Write the SQL statement to display the shipment cost for each trip. Write down the output of this query as well. (5 marks) (b) Write the SQL statement to display the shipment cost for each store. Write down the output of this query as well.(5 marks) Question 3:total 5 Marks Let’s consider the Clothing case study. The operational database consists of the following tables: A star schema has been created: The fact measures included in the above star schema are TotalOrderQuantity, which is taken from the Quantity attribute from table Inventory, and TotalOrderCost, which is the sum of order price x quantity. The above star schema contains highly aggregated data, and therefore this star schema is at level-2 in the data warehouse architecture. Questions: Draw level-1 and level-0 star schemas for the above clothing data warehouse case study. (5 marks = 2 * 2.5 marks for each level) Question 4: total 15 Marks Data cleaning is an important part in building a clean and correct data warehouse. Data cleaning is often needed, because there are mistakes and inconsistencies in the operational database. Before data cleaning is done, we need to do data exploration on the operational database in order to find out if there are any mistakes and inconsistencies in the operational database. The following are the six tables in the operational database: Questions: a) Write the SQL command to find out if there are duplicate transaction records and provide the solution for this issue. (5 marks) b) Write the SQL command to find out if there are illegal records in the transaction table (e.g. a record in transaction table that are actually not exist in other tables). (5 marks) c) What other issue that may need to be cleaned based on the above operational database? You can provide three more issues. (5 marks) Question 5:total 10 Marks Questions: (a) (2 marks) What is one-attribute dimension? (b) (8 marks) Describe four ways to deal with one-attribute dimensions Question 6: total 15 Marks The MonMusicis a mobile app that allows its users to download songs. When an individual joins MonMusicas anewuser, they are given a unique userid and their details (such as name, email,phone number, and country) are recorded. After joining,members can start downloading songs and MonMusicrecords the date and time of when the member download the song.The download fee is also recorded to the system depending on the song the user chose. Each songin MonMusichas a unique songid. The genre (e.g. Pop, Classic, Rock,etc.) and the details of the artists of each song are also recorded in the system. Each song may involve several artists and an artist can appear in several songs.Finally, the MonMusic users can provide some reviews to the songs.Each review contains the song id, user id, review date, content, and the rating(within 5 stars). The E/R diagram of the operational database is shown as follows: MonMusic requires a data warehouse for analysis of number of downloaded songs and the total download revenue. Particularly, data analysis is needed for answering the following questions: ● How much is the total download revenuein Summer? ● How many songs are downloadedby users located in Australia? ● How much is the total download revenuefor each artist? ● How much is the total download revenue for Classic songs during Winter 2020? Questions: a.Based on the above requirements, develop a star/snowflake schema. If you are using a Bridge Table, make sure to include a Weight Factorattribute and a ListAGGattribute. (10marks) b.Write the queries to create (and populate) the dimension tables, and the fact table.You do not need to write the SQL to answer the requirement questions above. (5marks) Question 7: total 15 Marks Marina Resorts is a luxury hotel chain that has branches at several beaches in West Australia. It provides different choices for booking, including hotel rooms, private villas, apartments and bungalows. Customers have to first register as membersin order to book their vacation rentals. The following entity relationship diagram shows the operational database of Marina Resorts: Members can make bookings online or by phone. Members can use the accumulated loyalty points to redeem free stays. There are two payment methods provided for the customers, which include credit card for online booking, or pay via cash over the counter upon checking in. After operating for more than 10 years, Marina Resorts would like to build a data warehouse that analyses its sales based on member, season and room type. The following diagram shows a Level-1 star schema of the data warehouse. Question: (a)Based on the star schema above, show how a Level-0 star schema can be produced by changing the granularity of a dimension. The fact measure:TotalNights and TotalPrice should remain the same. You may choose any of the dimensions.(5 marks) (b)Now, draw a star schema for Marina Resorts that has the highest level of aggregation. The star schema should be focusing on the same set of fact measures.(5 marks) (c)Assume that the room charges -PricePerNight changes from time to time. Show how SCD type-4 can be applied to star schema. You can choose to draw any level of the star schema. (5 marks) Question 8: total 20 Marks Monash Roads and Safety (MonR&S) is in charge of taking care and recording accident-related events within the Monash municipality. When a road accident happens, the details of the accident will be recorded into MonR&S database system. The details that are recorded consists of the location, date, time, and the event (e.g. what exactly happened during the accident). MonR&S also records the information of the police officer who is in charge of overseeing the events of the accident (e.g finding out who/what caused the accident). In addition, to identify a particular accident, each accident is given a unique accident number. On the other hand, one accident may be caused by several vehicles. For example, an accident event ofa collision between twovehicles. All vehicles have owners who may or may not be the same as the vehicle driver during the accident. Therefore, the MonR&S database stores information about vehiclesthat wereinvolved in the accident, the information about the owners of the vehicles,and the information about the drivers at the time of the accident. Furthermore, for each accident, the severity of damage incurred by each vehicle is recorded. The operational database for MonR&Sis shown by the following E/R diagram. You are required to build a data warehousefor MonR&Sto analyse the following: ● The number of accidents happened according tosuburbsand by different lighting periods (daytime: 6:00AM -5:59PM and nighttime 6:00PM -5:59AM). ● The number of vehiclesbased on thevehicle modelsand the severity of damage. ● The number of accidents taken care of different police officer branches. Question: (a) Draw the star schema for this system. If you are using a Bridge Table, make sure to include a Weight Factorattribute and a ListAGGattribute. (10marks). (b) Write the SQL queries to create the fact and dimensiontables. (10marks)