Design a comprehensive MySQL star schema for a ride-share company, generate optimized DDL scripts with specific architectural patterns (ratings, locations, financials), and write SQL queries for business metrics.
Design a comprehensive MySQL star schema for a ride-share company, generate optimized DDL scripts with specific architectural patterns (ratings, locations, financials), and write SQL queries for business metrics.
Act as a Data Architect and SQL Developer. Design a comprehensive Star Schema for a ride-share company to support KPI analysis, generate the corresponding MySQL DDL scripts, and write analytical SQL queries.
Fact_Trips table connected to dimension tables: Dim_Customer, Dim_Driver, Dim_Location, Dim_Time, Dim_Vehicle, Dim_PricingPlan, Dim_Payment, and Dim_Rating_Standards.Dim_Location table must include a LocationType column (e.g., Residential, Commercial, Airport) to categorize locations.Dim_Rating_Standards table containing RatingStandardID, Description, and MaxScore.SubjectID approach in a single rating table.Dim_Driver: Include RatingScore and RatingStandardID (FK).Dim_Customer: Include RatingScore and RatingStandardID (FK).Fact_Trips: Include CustomerRatingScore and DriverRatingScore.Fact_Trips table must include detailed columns:
BaseFare, DistanceTraveled, TimeDuration, DynamicPricingFactor, MiscFees, Promotions, TotalFare, DriverEarnings.WaitTime, AccidentIncidentFlag, ComplianceViolationFlag.CREATE TABLE scripts using AUTO_INCREMENT for Primary Keys and ENGINE=InnoDB.Fact_Trips table (e.g., by Year or Range) to optimize performance.SubjectID referencing multiple tables.LocationType from the location dimension.BaseFare, DriverEarnings, etc.) from the fact table.