Pedal Works Bicycle Shops ========================= Database schema: bikeShop Platform: mariadb Tables: 9 Total rows: 4,664 ABOUT THE BUSINESS ------------------ Pedal Works runs a small chain of neighbourhood bicycle shops. Each store sells new and refurbished bikes and runs a service counter where customers drop bikes off for repair. Technicians log the services they perform and the parts they consume on every job. Repairs are modelled as an order header plus line items so a single visit can cover several services, and parts are attached to the line item rather than the order so the cost of a job breaks down cleanly. WHAT EACH TABLE HOLDS --------------------- stores (12 rows) One row per physical shop location. customers (260 rows) Everyone who has bought or serviced a bike here. employees (260 rows) Sales staff and service technicians. bikes (260 rows) Individual bicycles, each owned by one customer. services (12 rows) The catalogue of services the shop offers. parts (260 rows) Inventory of parts consumed during repairs. repairOrders (1,200 rows) One row per bike brought in for service. repairItems (1,200 rows) The individual services performed on a repair order. partUsages (1,200 rows) Which parts were consumed on which repair item. HOW THE TABLES RELATE --------------------- The core entities are customers, employees, bikes, and parts. Day-to-day activity is recorded in repairOrders, repairItems, and partUsages. Stores and services hold the reference values the rest of the schema draws on. - Each employees row belongs to one stores, through employees.storeId → stores.storeId. - Each bikes row belongs to one customers, through bikes.customerId → customers.customerId. - Each repairOrders row belongs to one bikes, through repairOrders.bikeId → bikes.bikeId. - Each repairOrders row belongs to one employees, through repairOrders.employeeId → employees.employeeId. - Each repairOrders row belongs to one stores, through repairOrders.storeId → stores.storeId. - Each repairItems row belongs to one repairOrders, through repairItems.repairOrderId → repairOrders.repairOrderId. - Each repairItems row belongs to one services, through repairItems.serviceId → services.serviceId. - partUsages resolves a many-to-many relationship: each row pairs one repairItems with one other party via partUsages.repairItemId. - partUsages resolves a many-to-many relationship: each row pairs one parts with one other party via partUsages.partId. DATA DICTIONARY --------------- stores storeId INT e.g. 1 Unique identifier for each stores row. storeName VARCHAR(120) e.g. Lakebound Holdings city VARCHAR(60) e.g. Green Bay state VARCHAR(2) e.g. MI openedDate DATE e.g. 2017-11-22 customers customerId INT e.g. 1 Unique identifier for each customers row. firstName VARCHAR(50) e.g. Priya lastName VARCHAR(50) e.g. Silva email VARCHAR(120) e.g. priya.silva1@example.com phone VARCHAR(20) e.g. (273) 823-2565 city VARCHAR(60) e.g. Traverse City joinDate DATE e.g. 2024-07-10 employees employeeId INT e.g. 1 Unique identifier for each employees row. firstName VARCHAR(50) e.g. Gustav lastName VARCHAR(50) e.g. Bhatt storeId INT e.g. 8 Links this row to stores.storeId. role VARCHAR(60) e.g. Sales Associate One of: Technician, Sales Associate, Service Manager, Store Manager. hireDate DATE e.g. 2025-03-13 hourlyRate DECIMAL(12,2) e.g. 32.27 bikes bikeId INT e.g. 1 Unique identifier for each bikes row. customerId INT e.g. 94 Links this row to customers.customerId. brand VARCHAR(60) e.g. Giant One of: Trek, Specialized, Giant, Cannondale, …. model VARCHAR(30) e.g. RX-8455 bikeType VARCHAR(60) e.g. Mountain One of: Road, Mountain, Hybrid, Gravel, …. purchaseYear INT e.g. 2016 purchasePrice DECIMAL(12,2) e.g. 498.53 services serviceId INT e.g. 1 Unique identifier for each services row. serviceName VARCHAR(60) e.g. Drivetrain Clean One of: Wheel True, Wheel Rebuild, Brake Bleed, Brake Pad Replacement, …. standardHours DECIMAL(12,2) e.g. 5.85 flatRate DECIMAL(12,2) e.g. 50.33 parts partId INT e.g. 1 Unique identifier for each parts row. partName VARCHAR(120) e.g. Backordered unit partNumber VARCHAR(30) e.g. EZC-28234 category VARCHAR(60) e.g. Tyres One of: Drivetrain, Braking, Wheels, Tyres, …. unitCost DECIMAL(12,2) e.g. 8.37 onHandQuantity INT e.g. 3 repairOrders repairOrderId INT e.g. 1 Unique identifier for each repairOrders row. bikeId INT e.g. 1 Links this row to bikes.bikeId. employeeId INT e.g. 70 Links this row to employees.employeeId. storeId INT e.g. 2 Links this row to stores.storeId. dropOffDate DATE e.g. 2025-03-05 status VARCHAR(60) e.g. Completed One of: Completed, In Progress, Waiting On Parts, Ready For Pickup, …. laborHours DECIMAL(12,2) e.g. 1.09 repairItems repairItemId INT e.g. 1 Unique identifier for each repairItems row. repairOrderId INT e.g. 1111 Links this row to repairOrders.repairOrderId. serviceId INT e.g. 4 Links this row to services.serviceId. quantity INT e.g. 1 itemCost DECIMAL(12,2) e.g. 76.8 lineTotal DECIMAL(12,2) e.g. 76.8 Calculated as quantity * itemCost. partUsages partUsageId INT e.g. 1 Unique identifier for each partUsages row. repairItemId INT e.g. 146 Links this row to repairItems.repairItemId. partId INT e.g. 3 Links this row to parts.partId. quantityUsed INT e.g. 1 PROVENANCE ---------- Every value in this dataset is synthetic. No real person, company, or record is represented. Generated by the Agoge dataset generator 1.0.0 with seed 42. The same brief and seed reproduce this data exactly.