# Complete Optimization Problem and Solution: flight_4

## 1. Problem Context and Goals

### Context  
The business problem revolves around optimizing the assignment of airlines to specific routes to minimize total operational costs while ensuring all required destinations are covered and no airline exceeds its capacity. The decision to assign an airline to a route is represented as a binary choice, where each route must be assigned to exactly one airline. The operational cost of assigning an airline to a route is a key factor in this decision, and it is calculated using a formula that includes base operational costs, distance-based costs, fuel costs, and fuel efficiency. Additionally, a threshold for operational efficiency is set to ensure that airlines maintain high performance standards. This threshold is used to evaluate airline performance but does not directly influence the optimization constraints. The problem is designed to avoid nonlinear relationships, ensuring that all calculations and constraints remain linear and straightforward.

### Goals  
The primary goal of this optimization is to minimize the total operational cost associated with assigning airlines to routes. This is achieved by summing the costs of all assignments, where each assignment is weighted by its corresponding operational cost. Success is measured by achieving the lowest possible total cost while ensuring that all routes are covered and no airline is assigned more routes than it can handle. The optimization process relies on precise operational data, including the costs of assignments and the capacities of airlines, to make informed decisions that align with business objectives.

## 2. Constraints    

The optimization problem is subject to two key constraints:  
1. **Route Coverage**: Each route must be assigned to exactly one airline. This ensures that all required destinations are covered without redundancy.  
2. **Airline Capacity**: No airline can be assigned more routes than its specified capacity. This ensures that airlines operate within their operational limits and maintain efficiency.  

These constraints are designed to be linear and straightforward, avoiding any complex relationships or nonlinear calculations. They directly align with the business requirements and ensure that the optimization problem remains solvable and meaningful.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 2 Database Schema
-- Objective: Added decision variable table assign_airline_route to address missing optimization requirement. Updated business configuration logic with additional scalar parameters and formulas.

CREATE TABLE cost_airline_route (
  alid INTEGER,
  rid INTEGER,
  cost FLOAT
);

CREATE TABLE capacity_airline (
  alid INTEGER,
  capacity INTEGER
);

CREATE TABLE assign_airline_route (
  alid INTEGER,
  rid INTEGER,
  assign BOOLEAN
);
```

### Data Dictionary  
- **cost_airline_route**: This table contains the operational costs associated with assigning specific airlines to specific routes. Each record includes the airline ID, route ID, and the cost of the assignment. The cost is used as a coefficient in the optimization objective function.  
- **capacity_airline**: This table defines the maximum number of routes each airline can handle. It includes the airline ID and its corresponding capacity, which serves as an upper bound in the capacity constraint.  
- **assign_airline_route**: This table represents the decision variables in the optimization problem. Each record indicates whether a specific airline is assigned to a specific route, using a binary value (true or false).  


### Retrieved Values

**Query 1: This data is crucial for the objective function, as it provides the coefficients for minimizing total operational costs.**

```sql
SELECT alid, rid, cost FROM cost_airline_route;
```

**Results (CSV format):**
```csv
alid,rid,cost
1,1,1200.0
1,2,1800.0
1,3,2500.0
```

**Query 2: This data is essential for the capacity constraint, ensuring no airline is assigned more routes than it can handle.**

```sql
SELECT alid, capacity FROM capacity_airline;
```

**Results (CSV format):**
```csv
alid,capacity
1,8
2,12
3,6
```

**Query 3: This data represents the decision variables in the optimization problem, indicating whether an airline is assigned to a route.**

```sql
SELECT alid, rid, assign FROM assign_airline_route;
```

**Results (CSV format):**
```csv
alid,rid,assign
1,1,0
1,2,0
1,3,0
```

**Query 4: This aggregated data helps in evaluating the capacity constraint by comparing the number of assigned routes to the airline's capacity.**

```sql
SELECT alid, COUNT(rid) AS assigned_routes FROM assign_airline_route WHERE assign = TRUE GROUP BY alid;
```

**Results (CSV format):**
```csv
alid,assigned_routes
```

**Query 5: This aggregated data helps in understanding the current cost distribution among airlines, which is useful for optimization.**

```sql
SELECT aar.alid, SUM(car.cost) AS total_cost FROM assign_airline_route aar JOIN cost_airline_route car ON aar.alid = car.alid AND aar.rid = car.rid WHERE aar.assign = TRUE GROUP BY aar.alid;
```

**Results (CSV format):**
```csv
alid,total_cost
```

**Query 6: This data is important for ensuring the route coverage constraint, as it identifies routes that need to be assigned.**

```sql
SELECT rid FROM cost_airline_route WHERE rid NOT IN (SELECT rid FROM assign_airline_route WHERE assign = TRUE);
```

**Results (CSV format):**
```csv
rid
1
2
3
```

**Query 7: This combined data helps in evaluating both the cost and capacity constraints simultaneously.**

```sql
SELECT car.alid, car.rid, car.cost, ca.capacity FROM cost_airline_route car JOIN capacity_airline ca ON car.alid = ca.alid;
```

**Results (CSV format):**
```csv
alid,rid,cost,capacity
1,1,1200.0,8
1,2,1800.0,8
1,3,2500.0,8
```

**Query 8: This aggregated data helps in understanding the cost impact of each route, which is useful for optimization.**

```sql
SELECT aar.rid, SUM(car.cost) AS total_cost FROM assign_airline_route aar JOIN cost_airline_route car ON aar.alid = car.alid AND aar.rid = car.rid WHERE aar.assign = TRUE GROUP BY aar.rid;
```

**Results (CSV format):**
```csv
rid,total_cost
```

**Query 9: This data helps in identifying airlines that can take on additional routes, which is useful for optimization.**

```sql
SELECT ca.alid, ca.capacity, COUNT(aar.rid) AS assigned_routes FROM capacity_airline ca LEFT JOIN assign_airline_route aar ON ca.alid = aar.alid AND aar.assign = TRUE GROUP BY ca.alid, ca.capacity HAVING COUNT(aar.rid) < ca.capacity;
```

**Results (CSV format):**
```csv
alid,capacity,assigned_routes
1,8,0
2,12,0
3,6,0
```

**Query 10: This data helps in identifying airlines that cannot take on additional routes, which is useful for optimization.**

```sql
SELECT ca.alid, ca.capacity, COUNT(aar.rid) AS assigned_routes FROM capacity_airline ca LEFT JOIN assign_airline_route aar ON ca.alid = aar.alid AND aar.assign = TRUE GROUP BY ca.alid, ca.capacity HAVING COUNT(aar.rid) = ca.capacity;
```

**Results (CSV format):**
```csv
alid,capacity,assigned_routes
```

