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


### Retrieved Values

**Query 1: Query Description: Retrieve the cost per flight for each route. This is essential for the objective function, as it provides the coefficients needed to calculate the total operating cost.**

```sql
SELECT route, cost FROM cost_per_flight;
```

**Results (CSV format):**
```csv
route,cost
NYC-LAX,4500
CHI-MIA,3500
SEA-DEN,3000
```

**Query 2: Query Description: Retrieve the capacity of each airport. This data is crucial for the constraints, ensuring that the number of flights departing from or arriving at each airport does not exceed its capacity.**

```sql
SELECT airport, capacity FROM airport_capacity;
```

**Results (CSV format):**
```csv
airport,capacity
JFK,150
ORD,120
SEA,80
```

**Query 3: Query Description: Retrieve the current number of flights on each route. This data represents the decision variables in the optimization problem, which need to be optimized to minimize the total cost.**

```sql
SELECT route, flight_count FROM flights;
```

**Results (CSV format):**
```csv
route,flight_count
NYC-LAX,5
CHI-MIA,3
SEA-DEN,2
```

**Query 4: Query Description: Retrieve the total number of flights departing from each airport. This is necessary to enforce the airport departure capacity constraint.**

```sql
SELECT departure_airport, SUM(flight_count) AS total_departures FROM flights JOIN routes ON flights.route = routes.route GROUP BY departure_airport;
```

**Error:** Execution failed on sql 'SELECT departure_airport, SUM(flight_count) AS total_departures FROM flights JOIN routes ON flights.route = routes.route GROUP BY departure_airport;': no such table: routes

**Query 5: Query Description: Retrieve the total number of flights arriving at each airport. This is necessary to enforce the airport arrival capacity constraint.**

```sql
SELECT arrival_airport, SUM(flight_count) AS total_arrivals FROM flights JOIN routes ON flights.route = routes.route GROUP BY arrival_airport;
```

**Error:** Execution failed on sql 'SELECT arrival_airport, SUM(flight_count) AS total_arrivals FROM flights JOIN routes ON flights.route = routes.route GROUP BY arrival_airport;': no such table: routes

**Query 6: Query Description: Retrieve the total operating cost across all routes. This is the metric to be minimized in the optimization problem.**

```sql
SELECT SUM(cost * flight_count) AS total_operating_cost FROM cost_per_flight JOIN flights ON cost_per_flight.route = flights.route;
```

**Results (CSV format):**
```csv
total_operating_cost
39000
```

**Query 7: Query Description: Retrieve routes that are not currently covered by any flights. This ensures that the route coverage constraint is met by identifying routes that need at least one flight.**

```sql
SELECT route FROM cost_per_flight WHERE route NOT IN (SELECT route FROM flights WHERE flight_count > 0);
```

**Results (CSV format):**
```csv
route
```

**Query 8: Query Description: Retrieve the maximum capacity of each airport along with the current number of flights departing and arriving. This helps in identifying airports that are close to or exceeding their capacity.**

```sql
SELECT arrival_airport AS airport, SUM(flight_count) AS total_arrivals FROM flights JOIN routes ON flights.route = routes.route GROUP BY arrival_airport ) AS arrivals ON airport_capacity.airport = arrivals.airport;
```

**Error:** Execution failed on sql 'SELECT arrival_airport AS airport, SUM(flight_count) AS total_arrivals FROM flights JOIN routes ON flights.route = routes.route GROUP BY arrival_airport ) AS arrivals ON airport_capacity.airport = arrivals.airport;': near ")": syntax error

