# Complete Optimization Problem and Solution: pilot_record

## 1. Problem Context and Goals

### Context  
The pilot assignment problem focuses on optimizing the allocation of pilots to aircraft to minimize operational costs while adhering to critical business rules. The decision involves determining whether a specific pilot is assigned to a specific aircraft, represented as a binary choice. The operational costs are derived from factors such as pilot rank, aircraft fuel efficiency, and experience, which are captured in the cost coefficients. 

Key business rules include:  
1. **Pilot Assignment Limit**: Each pilot can be assigned to at most one aircraft per day to ensure safety and manage workload. This limit is defined by a scalar parameter in the business configuration.  
2. **Aircraft Assignment Requirement**: Each aircraft must be assigned exactly one pilot to ensure operational readiness and safety. This requirement is also defined by a scalar parameter in the business configuration.  
3. **Pilot Qualifications**: Pilots can only be assigned to aircraft they are qualified to fly based on their rank and certification. This is enforced through a qualification indicator in the data.  

The problem is designed to ensure that all relationships and constraints are linear, avoiding scenarios that would require nonlinear relationships such as variable products or divisions. The data sources, including cost coefficients, pilot qualifications, and assignment limits, are structured to support a linear optimization formulation.

### Goals  
The primary goal of this optimization problem is to minimize the total operational cost of assigning pilots to aircraft. This cost is calculated as the sum of the cost coefficients for each pilot-aircraft assignment. Success is measured by achieving the lowest possible total cost while satisfying all business constraints, including pilot assignment limits, aircraft assignment requirements, and pilot qualifications. The optimization process ensures that the solution is both cost-effective and operationally feasible.

## 2. Constraints  

The optimization problem is subject to the following constraints, which are designed to align with linear mathematical forms:  

1. **Pilot Assignment Limit**: For each pilot, the total number of aircraft they are assigned to must not exceed the maximum limit defined in the business configuration. This ensures that no pilot is overburdened.  
2. **Aircraft Assignment Requirement**: For each aircraft, exactly one pilot must be assigned. This ensures that all aircraft are operational and ready for use.  
3. **Pilot Qualifications**: A pilot can only be assigned to an aircraft if they are qualified to fly it, as indicated by the qualification data. This ensures that assignments are safe and compliant with operational standards.  

These constraints are expressed in a way that naturally leads to linear relationships, avoiding any nonlinear complexities.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 2 Database Schema
-- Objective: Schema changes include creating tables for pilot assignment limits and aircraft assignment requirements, and updating business configuration logic with scalar parameters for pilot assignment limits and aircraft assignment requirements.

CREATE TABLE cost_coefficients (
  pilot_id INTEGER,
  aircraft_id INTEGER,
  cost_value FLOAT
);

CREATE TABLE pilot_qualifications (
  pilot_id INTEGER,
  aircraft_id INTEGER,
  is_qualified BOOLEAN
);

CREATE TABLE pilot_assignments (
  pilot_id INTEGER,
  aircraft_id INTEGER,
  assignment BOOLEAN
);
```

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

- **cost_coefficients**:  
  - **pilot_id**: Unique identifier for the pilot, used to link the cost coefficient to a specific pilot.  
  - **aircraft_id**: Unique identifier for the aircraft, used to link the cost coefficient to a specific aircraft.  
  - **cost_value**: The calculated cost of assigning a pilot to an aircraft, used in the objective function to minimize total costs.  

- **pilot_qualifications**:  
  - **pilot_id**: Unique identifier for the pilot, used to link the qualification to a specific pilot.  
  - **aircraft_id**: Unique identifier for the aircraft, used to link the qualification to a specific aircraft.  
  - **is_qualified**: Indicates whether the pilot is qualified to fly the aircraft, used in the qualification constraint.  

- **pilot_assignments**:  
  - **pilot_id**: Unique identifier for the pilot, used to link the assignment to a specific pilot.  
  - **aircraft_id**: Unique identifier for the aircraft, used to link the assignment to a specific aircraft.  
  - **assignment**: Indicates whether the pilot is assigned to the aircraft, used as the decision variable in the optimization problem.  

### Current Stored Values  
```sql
-- Iteration 2 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on realistic operational scenarios, considering pilot ranks, aircraft types, and cost factors such as fuel efficiency and pilot experience.

-- Realistic data for cost_coefficients
INSERT INTO cost_coefficients (pilot_id, aircraft_id, cost_value) VALUES (1, 101, 2.55);
INSERT INTO cost_coefficients (pilot_id, aircraft_id, cost_value) VALUES (2, 102, 3.4);
INSERT INTO cost_coefficients (pilot_id, aircraft_id, cost_value) VALUES (3, 103, 4.25);

-- Realistic data for pilot_qualifications
INSERT INTO pilot_qualifications (pilot_id, aircraft_id, is_qualified) VALUES (1, 101, True);
INSERT INTO pilot_qualifications (pilot_id, aircraft_id, is_qualified) VALUES (2, 102, True);
INSERT INTO pilot_qualifications (pilot_id, aircraft_id, is_qualified) VALUES (3, 103, False);

-- Realistic data for pilot_assignments
INSERT INTO pilot_assignments (pilot_id, aircraft_id, assignment) VALUES (1, 101, True);
INSERT INTO pilot_assignments (pilot_id, aircraft_id, assignment) VALUES (2, 102, True);
INSERT INTO pilot_assignments (pilot_id, aircraft_id, assignment) VALUES (3, 103, False);
```