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

### Current Stored Values  
```sql
-- Iteration 2 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on industry standards, realistic operational costs, and airline capacities. The data ensures that the optimization problem is meaningful and solvable by maintaining logical relationships between tables and respecting business configuration logic.

-- Realistic data for cost_airline_route
INSERT INTO cost_airline_route (alid, rid, cost) VALUES (1, 1, 1200.0);
INSERT INTO cost_airline_route (alid, rid, cost) VALUES (1, 2, 1800.0);
INSERT INTO cost_airline_route (alid, rid, cost) VALUES (1, 3, 2500.0);

-- Realistic data for capacity_airline
INSERT INTO capacity_airline (alid, capacity) VALUES (1, 8);
INSERT INTO capacity_airline (alid, capacity) VALUES (2, 12);
INSERT INTO capacity_airline (alid, capacity) VALUES (3, 6);

-- Realistic data for assign_airline_route
INSERT INTO assign_airline_route (alid, rid, assign) VALUES (1, 1, False);
INSERT INTO assign_airline_route (alid, rid, assign) VALUES (1, 2, False);
INSERT INTO assign_airline_route (alid, rid, assign) VALUES (1, 3, False);
```