# Complete Optimization Problem and Solution: school_bus

## 1. Problem Context and Goals

### Context  
The business problem involves optimizing the assignment of drivers to schools to minimize the total travel distance while ensuring operational efficiency. Each driver resides in a specific home city, and each school is located in a distinct location. The travel distance between a driver's home city and a school's location is calculated using the Euclidean distance formula, which measures the straight-line distance between two points based on their coordinates. 

The decision to assign a driver to a school is represented as a binary choice: a driver is either assigned to a school (1) or not (0). The business configuration includes two key parameters:  
1. **Maximum number of drivers per school**: Each school must be assigned exactly one driver to ensure efficient operations.  
2. **Maximum number of schools per driver**: Each driver can be assigned to at most one school to avoid overburdening drivers.  

The goal is to make these assignments in a way that minimizes the total travel distance across all driver-school pairs, ensuring that the constraints are satisfied. This problem is naturally suited for a linear optimization formulation, as it involves straightforward relationships between decision variables, coefficients, and constraints without requiring nonlinear operations like multiplication or division of variables.

### Goals  
The primary optimization goal is to minimize the total travel distance incurred by assigning drivers to schools. This is achieved by summing the distances for all driver-school pairs where an assignment is made. Success is measured by the total distance value, which is directly derived from the distances stored in the distance matrix. The objective is to find the optimal set of assignments that satisfies the constraints while achieving the lowest possible total travel distance.

## 2. Constraints  

The problem is subject to the following constraints:  
1. **Each school must be assigned exactly one driver**: This ensures that every school has a dedicated driver for its operations.  
2. **Each driver can be assigned to at most one school**: This prevents drivers from being overburdened by multiple assignments.  

These constraints are expressed in terms of the binary assignment decisions, ensuring that the solution adheres to the operational requirements of the business. The constraints are linear in nature, as they involve simple sums of binary variables without any nonlinear relationships.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating tables for drivers, schools, and distance matrix. Configuration logic updates include scalar parameters for distance calculation and business logic formulas for assignment constraints.

CREATE TABLE drivers (
  driver_id INTEGER,
  home_city STRING
);

CREATE TABLE schools (
  school_id INTEGER,
  location STRING
);

CREATE TABLE distance_matrix (
  driver_id INTEGER,
  school_id INTEGER,
  distance FLOAT,
  assign BOOLEAN
);
```

### Data Dictionary  
- **Drivers Table**: Contains information about drivers, including their unique identifiers and home cities.  
  - `driver_id`: Unique identifier for each driver, used to identify drivers in the optimization model.  
  - `home_city`: City where the driver resides, used to calculate the distance to schools.  

- **Schools Table**: Contains information about schools, including their unique identifiers and locations.  
  - `school_id`: Unique identifier for each school, used to identify schools in the optimization model.  
  - `location`: Location of the school, used to calculate the distance from drivers' home cities.  

- **Distance Matrix Table**: Contains the travel distances between drivers' home cities and schools' locations, as well as the binary assignment decisions.  
  - `driver_id`: Unique identifier for each driver, used to identify drivers in the optimization model.  
  - `school_id`: Unique identifier for each school, used to identify schools in the optimization model.  
  - `distance`: Travel distance between a driver's home city and a school's location, used as a coefficient in the objective function.  
  - `assign`: Binary decision variable indicating whether a driver is assigned to a school, used as a decision variable in the optimization model.  

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on realistic geographic distances between cities and school locations, ensuring that the optimization problem remains meaningful and solvable. Driver and school IDs were sequentially assigned to maintain uniqueness.

-- Realistic data for drivers
INSERT INTO drivers (driver_id, home_city) VALUES (1, 'CityA');
INSERT INTO drivers (driver_id, home_city) VALUES (2, 'CityB');
INSERT INTO drivers (driver_id, home_city) VALUES (3, 'CityC');

-- Realistic data for schools
INSERT INTO schools (school_id, location) VALUES (1, 'LocationX');
INSERT INTO schools (school_id, location) VALUES (2, 'LocationY');
INSERT INTO schools (school_id, location) VALUES (3, 'LocationZ');

-- Realistic data for distance_matrix
INSERT INTO distance_matrix (driver_id, school_id, distance, assign) VALUES (1, 1, 10.5, 0);
INSERT INTO distance_matrix (driver_id, school_id, distance, assign) VALUES (1, 2, 15.3, 0);
INSERT INTO distance_matrix (driver_id, school_id, distance, assign) VALUES (1, 3, 20.1, 0);
INSERT INTO distance_matrix (driver_id, school_id, distance, assign) VALUES (2, 1, 18.2, 0);
INSERT INTO distance_matrix (driver_id, school_id, distance, assign) VALUES (2, 2, 8.7, 0);
INSERT INTO distance_matrix (driver_id, school_id, distance, assign) VALUES (2, 3, 22.4, 0);
INSERT INTO distance_matrix (driver_id, school_id, distance, assign) VALUES (3, 1, 19.8, 0);
INSERT INTO distance_matrix (driver_id, school_id, distance, assign) VALUES (3, 2, 21.3, 0);
INSERT INTO distance_matrix (driver_id, school_id, distance, assign) VALUES (3, 3, 7.5, 0);
```