# Complete Optimization Problem and Solution: flight_2

## 1. Problem Context and Goals

### Context  
An airline company is focused on optimizing its flight operations to minimize costs while ensuring efficient use of airport resources. The company operates flights on specific routes, each with an associated cost of operation. The primary decision involves determining the number of flights to operate on each route, which must be an integer value. The cost of operating a flight on a specific route is a key operational parameter, directly influencing the total operating cost. Additionally, each airport has a maximum capacity for handling flights, which limits the number of flights that can depart from or arrive at that airport. The business logic for calculating the total operating cost is straightforward: it is the sum of the cost of operating each flight across all routes. This scenario naturally aligns with a linear optimization formulation, as the relationships between costs, flight counts, and airport capacities are proportional and additive, without any nonlinear interactions such as variable products or divisions.

### Goals  
The primary goal of this optimization problem is to minimize the total cost of operating all flights. This is achieved by determining the optimal number of flights to operate on each route, ensuring that the sum of the costs for all flights is as low as possible. Success is measured by achieving the lowest possible total operating cost while adhering to the constraints of airport capacities and ensuring that each route is covered by at least one flight. The metric to optimize is the total cost, which is calculated by multiplying the cost of operating a flight on each route by the number of flights on that route and summing these values across all routes.

## 2. Constraints    

The optimization problem is subject to the following constraints:  
1. **Route Coverage**: Each route must be covered by at least one flight. This ensures that all routes are operational and no route is left unserved.  
2. **Airport Departure Capacity**: The total number of flights departing from any given airport must not exceed the airport's capacity. This ensures that the airport's resources are not overburdened.  
3. **Airport Arrival Capacity**: The total number of flights arriving at any given airport must not exceed the airport's capacity. This ensures that the airport's resources are not overburdened from incoming flights.  

These constraints are expressed in business terms that naturally lead to linear mathematical forms, ensuring that the optimization problem remains linear and tractable.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating tables for cost_per_flight and airport_capacity, modifying the flights table to better map decision variables, and adding business configuration logic for scalar parameters and formulas.

CREATE TABLE cost_per_flight (
  route STRING,
  cost INTEGER
);

CREATE TABLE airport_capacity (
  airport STRING,
  capacity INTEGER
);

CREATE TABLE flights (
  route STRING,
  flight_count INTEGER
);
```

### Data Dictionary  
- **cost_per_flight**: This table contains the cost of operating a flight on each specific route. The `route` column identifies the flight route, and the `cost` column specifies the cost of operating a flight on that route. This data is used as coefficients in the objective function to calculate the total operating cost.  
- **airport_capacity**: This table defines the maximum number of flights that each airport can handle. The `airport` column identifies the airport, and the `capacity` column specifies the maximum number of flights. This data is used as bounds in the constraints to ensure that airport capacities are not exceeded.  
- **flights**: This table represents the decision variables in the optimization problem. The `route` column identifies the flight route, and the `flight_count` column specifies the number of flights to operate on that route. This data is used to determine the optimal number of flights for each route.  

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on industry standards, realistic flight costs, airport capacities, and flight frequencies. The data ensures that the optimization problem is meaningful and solvable by providing a balance between cost, capacity, and flight coverage.

-- Realistic data for cost_per_flight
INSERT INTO cost_per_flight (route, cost) VALUES ('NYC-LAX', 4500);
INSERT INTO cost_per_flight (route, cost) VALUES ('CHI-MIA', 3500);
INSERT INTO cost_per_flight (route, cost) VALUES ('SEA-DEN', 3000);

-- Realistic data for airport_capacity
INSERT INTO airport_capacity (airport, capacity) VALUES ('JFK', 150);
INSERT INTO airport_capacity (airport, capacity) VALUES ('ORD', 120);
INSERT INTO airport_capacity (airport, capacity) VALUES ('SEA', 80);

-- Realistic data for flights
INSERT INTO flights (route, flight_count) VALUES ('NYC-LAX', 5);
INSERT INTO flights (route, flight_count) VALUES ('CHI-MIA', 3);
INSERT INTO flights (route, flight_count) VALUES ('SEA-DEN', 2);
```