Overview01
This project models a multi-store dealership’s operations: conceptual modeling, schema design, analytical SQL, and reporting views. Seed data isn’t built yet, so the queries are written and structured but not yet run against data.
The schema02
The bn_motors database (MySQL 8, InnoDB) has 25 tables, documented with an ERD and a business-rules blueprint. The main entities by domain:
- Sales. Store, Employee, Customer, Vehicle, Sale, Trade-In, FinanceContract.
- Service. ServiceOrder, ServiceLineItem, Technician, Part, Supplier.
- Support. Lead, Appointment, Payment, Warranty, VehicleTransfer.
Analytics03
Ten analytical queries in three files: inventory (aging buckets, value by store, stale-and-overpriced flags), sales (gross profit per deal, salesperson ranking, month-over-month trends, finance penetration), and customer (lead-to-sale funnel, lifetime value, first-vs-repeat buyers). Three reporting views (inventory, sales with gross profit and a finance flag, and customer lifetime value) give non-technical users standard metrics without writing SQL.