# Complete Optimization Problem and Solution: school_bus

## 1. Problem Context and Goals

### Context  
The school bus operation aims to efficiently allocate full-time drivers to various schools. The primary decision involves determining which drivers are assigned to which schools. Each driver can be assigned to only one school, and the assignment is represented as a binary decision variable. The operational goal is to minimize the mismatch in years of experience between drivers and the schools they are assigned to. This mismatch is quantified and used as a coefficient in the optimization process. 

Each school has a specific requirement for the number of drivers, which must be met. Additionally, only full-time drivers are considered for assignments to ensure that the schools' needs are met consistently. The business configuration includes scalar parameters such as the number of drivers required by each school and whether a driver is full-time, which are crucial for forming the constraints in the optimization model. These parameters are set to reflect typical operational needs and ensure the problem remains challenging yet solvable.

### Goals  
The primary goal of this optimization problem is to minimize the total mismatch in years of experience between the drivers and the schools they are assigned to. This is achieved by carefully selecting driver assignments that reduce the overall experience mismatch. The success of this optimization is measured by how well the assignments minimize the total experience mismatch, aligning with the coefficients derived from the experience mismatch data. The objective is clearly defined in linear terms, focusing on reducing the sum of mismatches across all driver-school assignments.

## 2. Constraints    

The optimization problem is subject to several constraints that ensure the solution is feasible and meets the operational requirements:

- Each driver can be assigned to at most one school, and this constraint applies only to full-time drivers. This ensures that drivers are not over-allocated and that only eligible drivers are considered for assignments.
  
- Each school must receive the exact number of drivers it requires. This constraint ensures that the operational needs of each school are met, maintaining the balance between supply and demand.

These constraints are expressed in linear terms, focusing on the allocation of drivers to schools without involving any nonlinear relationships.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 2 Database Schema
-- Objective: Schema changes include creating a new table for years of experience mismatch and updating existing tables to fill mapping gaps. Business configuration logic is updated to handle scalar parameters and formulas.

CREATE TABLE DriverAssignments (
  DriverID INTEGER,
  SchoolID INTEGER,
  Assigned BOOLEAN
);

CREATE TABLE SchoolRequirements (
  SchoolID INTEGER,
  RequiredDrivers INTEGER
);

CREATE TABLE Drivers (
  DriverID INTEGER,
  IsFullTime BOOLEAN
);

CREATE TABLE ExperienceMismatch (
  DriverID INTEGER,
  SchoolID INTEGER,
  YearsMismatch INTEGER
);
```

### Data Dictionary  
The data dictionary provides a comprehensive mapping of tables and columns to their business purposes and roles in the optimization process:

- **DriverAssignments**: This table tracks which drivers are assigned to which schools. It plays a crucial role in the decision-making process, with each assignment represented as a binary variable indicating whether a driver is assigned to a school.

- **SchoolRequirements**: This table stores the number of drivers required by each school. It is essential for defining the constraints that ensure each school receives the necessary number of drivers.

- **Drivers**: This table contains information about the drivers, specifically whether they are full-time. This information is used to filter eligible drivers for assignments, ensuring that only full-time drivers are considered.

- **ExperienceMismatch**: This table records the years of experience mismatch between drivers and schools. It provides the coefficients used in the objective function to minimize the total experience mismatch.

### Current Stored Values  
```sql
-- Iteration 2 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical school bus operations, ensuring a mix of driver experience levels and school requirements to create a realistic and challenging optimization problem.

-- Realistic data for DriverAssignments
INSERT INTO DriverAssignments (DriverID, SchoolID, Assigned) VALUES (1, 101, False);
INSERT INTO DriverAssignments (DriverID, SchoolID, Assigned) VALUES (2, 102, True);
INSERT INTO DriverAssignments (DriverID, SchoolID, Assigned) VALUES (3, 103, True);

-- Realistic data for SchoolRequirements
INSERT INTO SchoolRequirements (SchoolID, RequiredDrivers) VALUES (101, 3);
INSERT INTO SchoolRequirements (SchoolID, RequiredDrivers) VALUES (102, 4);
INSERT INTO SchoolRequirements (SchoolID, RequiredDrivers) VALUES (103, 5);

-- Realistic data for Drivers
INSERT INTO Drivers (DriverID, IsFullTime) VALUES (1, True);
INSERT INTO Drivers (DriverID, IsFullTime) VALUES (2, True);
INSERT INTO Drivers (DriverID, IsFullTime) VALUES (3, True);

-- Realistic data for ExperienceMismatch
INSERT INTO ExperienceMismatch (DriverID, SchoolID, YearsMismatch) VALUES (1, 101, 2);
INSERT INTO ExperienceMismatch (DriverID, SchoolID, YearsMismatch) VALUES (2, 102, 1);
INSERT INTO ExperienceMismatch (DriverID, SchoolID, YearsMismatch) VALUES (3, 103, 0);
```