# Complete Optimization Problem and Solution: flight_2

## 1. Problem Context and Goals

### Context  
An airline company is focused on optimizing its flight schedule to minimize operational costs. The decision-making process involves determining which flights to operate, represented by a binary decision variable for each flight. The operational parameters are designed to align with a linear objective of minimizing total operational costs. This involves summing the costs associated with each flight that is operated. The business configuration includes several key constraints: the maximum number of departures allowed from an airport, the maximum number of arrivals allowed at an airport, and the maximum number of flights the airline can operate. These constraints are critical in ensuring that the optimization problem remains linear and solvable. The data used reflects current operational information, ensuring that decisions are based on realistic scenarios. The business configuration parameters are integral to the optimization model, providing bounds that guide the decision-making process.

### Goals  
The primary goal of the optimization is to minimize the total operational cost associated with the airline's flight schedule. This involves selecting the optimal set of flights to operate, with the aim of reducing costs while adhering to operational constraints. The success of this optimization is measured by the reduction in total operational costs, achieved by carefully selecting which flights to operate based on their associated costs. The optimization goal is clearly defined in natural language, focusing on minimizing costs without the use of mathematical formulas or symbolic notation.

## 2. Constraints    

The optimization problem is subject to several constraints that ensure the solution is feasible and aligns with business requirements:

- The total number of flights operated by the airline must not exceed the maximum number of flights the airline can operate. This constraint ensures that the airline operates within its capacity limits.
- All mandatory flights must be operated. This constraint ensures that certain critical flights are always included in the schedule.
- The number of flights departing from any given airport must not exceed the maximum number of departures allowed. This constraint reflects the airport's capacity to handle departures.
- Similarly, the number of flights arriving at any given airport must not exceed the maximum number of arrivals allowed. This ensures that the airport's arrival capacity is not exceeded.

These constraints are described in business terms that naturally lead to linear mathematical forms, avoiding any nonlinear relationships such as variable products or divisions.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating new tables for missing optimization data, modifying existing tables to fill mapping gaps, and updating business configuration logic for scalar parameters and formulas.

CREATE TABLE FlightCosts (
  FlightNo STRING,
  Cost FLOAT
);

CREATE TABLE MandatoryFlights (
  FlightNo STRING
);
```

### Data Dictionary  
The data dictionary provides a comprehensive mapping of tables and columns to their business purposes and optimization roles:

- **FlightCosts Table**: This table stores the operational cost data for each flight. The primary purpose is to provide the cost coefficients used in the objective function of the optimization model. Each flight is uniquely identified by a flight number, which links the cost to specific flights.

  - **FlightNo**: A unique identifier for each flight, serving as a link between the flight and its associated cost.
  - **Cost**: Represents the operational cost of operating the flight, used as a coefficient in the objective function to calculate total operational costs.

- **MandatoryFlights Table**: This table lists the flights that must be operated, serving as a constraint in the optimization model to ensure these flights are always included in the schedule.

  - **FlightNo**: A unique identifier for each mandatory flight, ensuring these flights are operated as required by the business constraints.

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical operational costs for flights, realistic airport capacity constraints, and airline operational limits to ensure a balanced and solvable optimization problem.

-- Realistic data for FlightCosts
INSERT INTO FlightCosts (FlightNo, Cost) VALUES ('FL123', 1200.0);
INSERT INTO FlightCosts (FlightNo, Cost) VALUES ('FL456', 2500.0);
INSERT INTO FlightCosts (FlightNo, Cost) VALUES ('FL789', 1800.0);

-- Realistic data for MandatoryFlights
INSERT INTO MandatoryFlights (FlightNo) VALUES ('FL789');
INSERT INTO MandatoryFlights (FlightNo) VALUES ('FL101');
INSERT INTO MandatoryFlights (FlightNo) VALUES ('FL202');
```