2) (20 marks) The service organization needs to know what repairs need to be performed on...

 

 

Transcribed Image Text:

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, c. if the car has been driven 8,000 km since last maintenance, it requires maintenance, d. if alignment is off by more than 5% - maintenance is required. e. 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: 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. 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, c. if the car has been driven 8,000 km since last maintenance, it requires maintenance, d. if alignment is off by more than 5% - maintenance is required. e. 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: 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.