Figure below shows a star schema designed for Uber/Lyft riding process. 

 

Explanation of some attributes are:

PassengerFare: the amount of money passenger paid in the app

DriverEarning: the amount of money driver earned from the trip

TripDuration: the amount of time the trip took

Miles: the miles of driving from where passenger was picked and to destination

AvgSpeed: the average speed of the vehicle for the trip, which equals to TripDuration/Miles

ProfilePciture: a binary variable if the passenger/driver has a profile picture uploaded

Rating: the average star rating the passenger/driver has received, ranging from 0 to 5.

PlateNumber: The registration number given by RMV

VehicleType: we consider three types: regular, XL (large size cars), or luxury (expensive cars)

 

3.5 Pick any one (1) addressable question/requirement from above SQL query to answer the question. (0.8 point)

3.6 For questions or requirements that cannot be addressed by the current design, how do you modify the star schema to address it? Specifically:

1) Identify new attributes/dimensions to be added, and add them to the figure above. (0.3 point)

2) Briefly explain how the question or requirement can be addressed after your proposed star-schema change. You can use wither narratives or SQL.

Transcribed Image Text:

Figure below shows a star schema designed for Uber/Lyft riding process. Dimension Passenger (PK) Passenes. Name ProfilePicutre Rating Dimension Driver (PK) DriverD Name ProfilePicutre Rating (PK) Bids... Fact Ride Passenger Driverkex VehicleKey. Measure PassengerEace DriverEarning TripDuration Miles AvaSpeed Dimension Vehicle (PK) PlateNumber Vebilcelyne Figure below shows a star schema designed for Uber/Lyft riding process. Dimension Passenger (PK) Passenes. Name ProfilePicutre Rating Dimension Driver (PK) DriverD Name ProfilePicutre Rating (PK) Bids... Fact Ride Passenger Driverkex VehicleKey. Measure PassengerEace DriverEarning TripDuration Miles AvaSpeed Dimension Vehicle (PK) PlateNumber Vebilcelyne