# 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.  


### Retrieved Values

**Query 1: Query Description: Retrieve all cost coefficients for pilot-aircraft pairs. This data is crucial for the objective function, as it represents the operational cost of assigning a specific pilot to a specific aircraft.**

```sql
SELECT pilot_id, aircraft_id, cost_value FROM cost_coefficients;
```

**Results (CSV format):**
```csv
pilot_id,aircraft_id,cost_value
1,101,2.55
2,102,3.4
3,103,4.25
```

**Query 2: Query Description: Retrieve all pilot qualifications. This data is essential for the qualification constraint, ensuring that pilots are only assigned to aircraft they are qualified to fly.**

```sql
SELECT pilot_id, aircraft_id, is_qualified FROM pilot_qualifications WHERE is_qualified = TRUE;
```

**Results (CSV format):**
```csv
pilot_id,aircraft_id,is_qualified
1,101,1
2,102,1
```

**Query 3: Query Description: Retrieve all current pilot assignments. This data is important for understanding the current state of assignments and ensuring that the optimization respects the pilot assignment limit and aircraft assignment requirement constraints.**

```sql
SELECT pilot_id, aircraft_id, assignment FROM pilot_assignments WHERE assignment = TRUE;
```

**Results (CSV format):**
```csv
pilot_id,aircraft_id,assignment
1,101,1
2,102,1
```

**Query 4: Query Description: Retrieve the total number of assignments per pilot. This aggregated data is useful for enforcing the pilot assignment limit constraint, ensuring no pilot is assigned to more than one aircraft.**

```sql
SELECT pilot_id, COUNT(aircraft_id) AS total_assignments FROM pilot_assignments WHERE assignment = TRUE GROUP BY pilot_id;
```

**Results (CSV format):**
```csv
pilot_id,total_assignments
1,1
2,1
```

**Query 5: Query Description: Retrieve the total number of assignments per aircraft. This aggregated data is useful for enforcing the aircraft assignment requirement constraint, ensuring each aircraft has exactly one pilot assigned.**

```sql
SELECT aircraft_id, COUNT(pilot_id) AS total_assignments FROM pilot_assignments WHERE assignment = TRUE GROUP BY aircraft_id;
```

**Results (CSV format):**
```csv
aircraft_id,total_assignments
101,1
102,1
```

**Query 6: Query Description: Retrieve all pilot-aircraft pairs with their cost coefficients and qualification status. This combined data is useful for understanding the cost and feasibility of each potential assignment, which is critical for the optimization process.**

```sql
SELECT cc.pilot_id, cc.aircraft_id, cc.cost_value, pq.is_qualified FROM cost_coefficients cc JOIN pilot_qualifications pq ON cc.pilot_id = pq.pilot_id AND cc.aircraft_id = pq.aircraft_id WHERE pq.is_qualified = TRUE;
```

**Results (CSV format):**
```csv
pilot_id,aircraft_id,cost_value,is_qualified
1,101,2.55,1
2,102,3.4,1
```

**Query 7: Query Description: Retrieve all pilot-aircraft pairs that are currently unassigned but qualified. This data is useful for identifying potential new assignments that could be made to optimize the total cost.**

```sql
SELECT pq.pilot_id, pq.aircraft_id FROM pilot_qualifications pq LEFT JOIN pilot_assignments pa ON pq.pilot_id = pa.pilot_id AND pq.aircraft_id = pa.aircraft_id WHERE pq.is_qualified = TRUE AND (pa.assignment IS NULL OR pa.assignment = FALSE);
```

**Results (CSV format):**
```csv
pilot_id,aircraft_id
```

**Query 8: Query Description: Retrieve the minimum cost coefficient for each aircraft. This data is useful for identifying the most cost-effective pilot for each aircraft, which can help in initializing the optimization process.**

```sql
SELECT aircraft_id, MIN(cost_value) AS min_cost FROM cost_coefficients GROUP BY aircraft_id;
```

**Results (CSV format):**
```csv
aircraft_id,min_cost
101,2.55
102,3.4
103,4.25
```

**Query 9: Query Description: Retrieve the minimum cost coefficient for each pilot. This data is useful for identifying the most cost-effective aircraft for each pilot, which can help in initializing the optimization process.**

```sql
SELECT pilot_id, MIN(cost_value) AS min_cost FROM cost_coefficients GROUP BY pilot_id;
```

**Results (CSV format):**
```csv
pilot_id,min_cost
1,2.55
2,3.4
3,4.25
```

**Query 10: Query Description: Retrieve all pilot-aircraft pairs with their cost coefficients, qualification status, and current assignment status. This comprehensive data is useful for a detailed analysis of the current state and potential optimizations.**

```sql
SELECT cc.pilot_id, cc.aircraft_id, cc.cost_value, pq.is_qualified, pa.assignment FROM cost_coefficients cc JOIN pilot_qualifications pq ON cc.pilot_id = pq.pilot_id AND cc.aircraft_id = pq.aircraft_id LEFT JOIN pilot_assignments pa ON cc.pilot_id = pa.pilot_id AND cc.aircraft_id = pa.aircraft_id WHERE pq.is_qualified = TRUE;
```

**Results (CSV format):**
```csv
pilot_id,aircraft_id,cost_value,is_qualified,assignment
1,101,2.55,1,1
2,102,3.4,1,1
```

