# Complete Optimization Problem and Solution: pilot_record

## 1. Problem Context and Goals

### Context  
The business objective is to optimize the assignment of pilots to aircraft in a manner that minimizes the total number of flights. This involves making decisions about the number of flights assigned to each pilot for specific aircraft, represented as integer values. The operational parameters are designed to align with a linear objective, focusing on minimizing the total flights across all assignments. Each pilot is required to fly a minimum number of flights annually, ensuring they maintain proficiency and meet industry standards. Additionally, each aircraft has a maximum flight capacity to balance operational efficiency and maintenance needs. These parameters are critical in forming the constraints of the optimization model, ensuring that the problem remains linear and avoids nonlinear relationships. The business configuration includes scalar parameters such as the minimum number of flights each pilot must fly and the maximum number of flights an aircraft can be assigned, which are used as lower and upper bound constraints, respectively.

### Goals  
The primary goal of this optimization problem is to minimize the total number of flights assigned to pilots. This is achieved by strategically assigning flights to pilots and aircraft while adhering to the constraints of minimum and maximum flight requirements. The success of this optimization is measured by the reduction in total flights, aligning with the expected coefficient sources. The goal is articulated in natural language to emphasize the linear nature of the optimization objective, focusing on minimizing the total flights assigned.

## 2. Constraints    

The constraints for this optimization problem are designed to ensure that each pilot meets their minimum flight requirement and that no aircraft exceeds its maximum flight capacity. Specifically, the constraints are:

- Each pilot must be assigned a number of flights that meets or exceeds their minimum required flights. This ensures that pilots maintain their proficiency and comply with industry standards.
- Each aircraft must not be assigned more flights than its maximum capacity, ensuring operational efficiency and adherence to maintenance schedules.

These constraints are expressed in business terms that naturally lead to linear mathematical forms, avoiding any nonlinear relationships such as variable products or divisions.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating new tables for decision variables and constraint bounds, and updating configuration logic for scalar parameters and formulas.

CREATE TABLE flights_assigned (
  pilot_id INTEGER,
  aircraft_id INTEGER,
  number_of_flights INTEGER
);

CREATE TABLE pilot_constraints (
  pilot_id INTEGER,
  min_flights INTEGER
);

CREATE TABLE aircraft_constraints (
  aircraft_id INTEGER,
  max_flights INTEGER
);
```

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

- **Flights Assigned Table**: This table tracks the assignment of flights to pilots and aircraft. It plays a crucial role in the optimization model as it contains the decision variables, which are the number of flights assigned to each pilot for a specific aircraft.
  - **Pilot ID**: Serves as a unique identifier for each pilot, linking them to their flight assignments.
  - **Aircraft ID**: Serves as a unique identifier for each aircraft, linking them to their flight assignments.
  - **Number of Flights**: Represents the number of flights assigned to a pilot for a specific aircraft, serving as the decision variable in the optimization model.

- **Pilot Constraints Table**: This table stores the minimum flight requirements for each pilot, serving as a lower bound constraint in the optimization model.
  - **Pilot ID**: Links to the pilot in the constraints, ensuring each pilot meets their minimum flight requirement.
  - **Minimum Flights**: Represents the minimum number of flights required for each pilot, ensuring compliance with industry standards.

- **Aircraft Constraints Table**: This table stores the maximum flight limits for each aircraft, serving as an upper bound constraint in the optimization model.
  - **Aircraft ID**: Links to the aircraft in the constraints, ensuring each aircraft does not exceed its maximum flight capacity.
  - **Maximum Flights**: Represents the maximum number of flights allowed for each aircraft, balancing operational efficiency and maintenance requirements.

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical industry standards for pilot flight assignments and aircraft utilization, ensuring a balance between pilot workload and aircraft capacity.

-- Realistic data for flights_assigned
INSERT INTO flights_assigned (pilot_id, aircraft_id, number_of_flights) VALUES (1, 101, 12);
INSERT INTO flights_assigned (pilot_id, aircraft_id, number_of_flights) VALUES (2, 102, 18);
INSERT INTO flights_assigned (pilot_id, aircraft_id, number_of_flights) VALUES (3, 103, 15);

-- Realistic data for pilot_constraints
INSERT INTO pilot_constraints (pilot_id, min_flights) VALUES (1, 10);
INSERT INTO pilot_constraints (pilot_id, min_flights) VALUES (2, 15);
INSERT INTO pilot_constraints (pilot_id, min_flights) VALUES (3, 12);

-- Realistic data for aircraft_constraints
INSERT INTO aircraft_constraints (aircraft_id, max_flights) VALUES (101, 120);
INSERT INTO aircraft_constraints (aircraft_id, max_flights) VALUES (102, 100);
INSERT INTO aircraft_constraints (aircraft_id, max_flights) VALUES (103, 110);
```