Introduction to Data Warehousing DBW624S1A We are going to build a logical model (like you did...

   

Transcribed Image Text:

Introduction to Data Warehousing DBW624S1A We are going to build a logical model (like you did in Assignment 1) for a car rental agency, however, we'll have some enhancements which include a multi-model database and leveraging sources of data from Internet of Things. You are only building the logical model. You do not need to build the physical model. 1) (30 marks) Build your logical model to meet the following requirements: a. The FACT table contains rental facts. Each car rental by a customer is a record in the fact table. b. Each vehicle will have a Vehicle Identification Number (VIN) which uniquely identifies it c. You must have a CARS DIMENSION table. This is where you store a record for each rental car in your fleet d. You must have a CUSTOMER DIMENSION table. This is where you store information on each customer who rents a vehicle e. You must pick FOUR additional DIMENSION tables for your logical model f. You must have a REFERENCE table which contains anadian postal codes g. The actual signed rental contract will be in JSON format and must be stored with each fact. h. Some common queries that you must be able to handle from your logical model: i. The financial analysts will want to have weekly, monthly, quarterly and annual revenue reports by rental agency location. The business analysts will want to be able to calculate the utilization rate for each vehicle in the fleet - and - for the fleet as a whole by city. Note: utilization rate means what % of days is a particular car being rented versus sitting on the lot (idle). ii. The marketing team will want to be able to accomplish two things: 1. Assess the loyalty level of each customer based on the number of rental days they have paid for. This would be a no status, bronze, silver and gold status. 2. Offer promotions and specials for specific status levels for various time periods. You can assume the promotions offer a discount. iv. The legal team will want to have access to the original signed documents, requested by rental agreement number, to pull up agreements and look at insurance options chosen by customers. 11. 2) (20 marks) The service organization needs to know what repairs need to be performed on vehicles in between rental periods. Build a MAINTENANCE_EVENTS table which tracks information from the sensors within a specific vehicle. Assume the car has sensors which relay the following information. This table should track items like % of wear on break pads, tire pressure, wheel alignment, kms since last maintenance check, fluid levels. Each sensor will have a SENSOR ID. You'll need a REFERENCE table which maps SENSOR IDs to VINS. Each sensor type (breaks, alignment, fluids, tire pressure, etc) will require a threshold of when something requires maintenance - use the following: a. if break pads are down below 30% - they need to be replaced. b. If tire pressure is below 20 PSI they require maintenance, if the car has been driven 8.000 km since last maintenance, it requires maintenance, if alignment is off by more than 5% - maintenance is required. d. If fluids are below 50%, they require to be refilled 2 3) (20 marks) Build queries which map to the logical model you have already created which accomplish the following: a. Write a SQL query which will group together sensor results which require action and build out the maintenance report for a specific vehicle upon its return b. Write a SQL query which will generate financial reports on weekly, monthly, quarterly and annual revenue for each rental location c. Write a SQL query which will generate a utilization report for each vehicle and aggregate the results for the entire company d. Write a SQL query which will take the rental agreement number as input and return the actual, signed rental agreement 4) (20 marks) We now need to make some performance enhancements to our logical model. Write the command which would create the following SUMMARY TABLES a. A summary table of the utilization rates so that after each car rental is complete, the utilization rate is updated b. A summary table of the revenue by rental location for the week, month, quarter and year to date. c. A summary table which shows all of the maintenance performed on a specific vehicle 5) (10 marks) Re-write the queries in 3(b) and 3(c) now to leverage the new summary tables. Introduction to Data Warehousing DBW624S1A We are going to build a logical model (like you did in Assignment 1) for a car rental agency, however, we'll have some enhancements which include a multi-model database and leveraging sources of data from Internet of Things. You are only building the logical model. You do not need to build the physical model. 1) (30 marks) Build your logical model to meet the following requirements: a. The FACT table contains rental facts. Each car rental by a customer is a record in the fact table. b. Each vehicle will have a Vehicle Identification Number (VIN) which uniquely identifies it c. You must have a CARS DIMENSION table. This is where you store a record for each rental car in your fleet d. You must have a CUSTOMER DIMENSION table. This is where you store information on each customer who rents a vehicle e. You must pick FOUR additional DIMENSION tables for your logical model f. You must have a REFERENCE table which contains anadian postal codes g. The actual signed rental contract will be in JSON format and must be stored with each fact. h. Some common queries that you must be able to handle from your logical model: i. The financial analysts will want to have weekly, monthly, quarterly and annual revenue reports by rental agency location. The business analysts will want to be able to calculate the utilization rate for each vehicle in the fleet - and - for the fleet as a whole by city. Note: utilization rate means what % of days is a particular car being rented versus sitting on the lot (idle). ii. The marketing team will want to be able to accomplish two things: 1. Assess the loyalty level of each customer based on the number of rental days they have paid for. This would be a no status, bronze, silver and gold status. 2. Offer promotions and specials for specific status levels for various time periods. You can assume the promotions offer a discount. iv. The legal team will want to have access to the original signed documents, requested by rental agreement number, to pull up agreements and look at insurance options chosen by customers. 11. 2) (20 marks) The service organization needs to know what repairs need to be performed on vehicles in between rental periods. Build a MAINTENANCE_EVENTS table which tracks information from the sensors within a specific vehicle. Assume the car has sensors which relay the following information. This table should track items like % of wear on break pads, tire pressure, wheel alignment, kms since last maintenance check, fluid levels. Each sensor will have a SENSOR ID. You'll need a REFERENCE table which maps SENSOR IDs to VINS. Each sensor type (breaks, alignment, fluids, tire pressure, etc) will require a threshold of when something requires maintenance - use the following: a. if break pads are down below 30% - they need to be replaced. b. If tire pressure is below 20 PSI they require maintenance, if the car has been driven 8.000 km since last maintenance, it requires maintenance, if alignment is off by more than 5% - maintenance is required. d. If fluids are below 50%, they require to be refilled 2 3) (20 marks) Build queries which map to the logical model you have already created which accomplish the following: a. Write a SQL query which will group together sensor results which require action and build out the maintenance report for a specific vehicle upon its return b. Write a SQL query which will generate financial reports on weekly, monthly, quarterly and annual revenue for each rental location c. Write a SQL query which will generate a utilization report for each vehicle and aggregate the results for the entire company d. Write a SQL query which will take the rental agreement number as input and return the actual, signed rental agreement 4) (20 marks) We now need to make some performance enhancements to our logical model. Write the command which would create the following SUMMARY TABLES a. A summary table of the utilization rates so that after each car rental is complete, the utilization rate is updated b. A summary table of the revenue by rental location for the week, month, quarter and year to date. c. A summary table which shows all of the maintenance performed on a specific vehicle 5) (10 marks) Re-write the queries in 3(b) and 3(c) now to leverage the new summary tables.

Answer rating: 100% (QA)

1 Logical Model Entities and their attributes FACT table Rental Rental ID primary key Rental Date Return Date Rental Duration Rental Cost Vehicle Identification Number VIN foreign key to CARS DIMENSIO... View the full answer