# Complete Optimization Problem and Solution: flight_company

## 1. Problem Context and Goals

### Context  
The flight scheduling optimization problem focuses on minimizing total fuel consumption across all flights by assigning each flight to an airport and a company. The decision involves determining whether a specific flight is assigned to a particular airport and company, represented as a binary decision variable. The fuel consumption for each flight is calculated using a predefined coefficient that reflects the fuel efficiency of the flight under specific operational conditions.  

Operational constraints include ensuring that each flight is assigned to exactly one airport and one company, respecting the capacity limits of both airports and companies. Additionally, flights must operate within predefined velocity and altitude ranges to ensure safety and efficiency. The minimum and maximum velocity constraints are set to balance speed and fuel consumption, while the altitude constraints ensure flights operate within safe and efficient airspace levels.  

The fuel consumption calculation is based on a linear relationship between the fuel consumption coefficient and the distance traveled, ensuring the objective function remains linear. This approach avoids nonlinear relationships, such as variable products or divisions, which would complicate the optimization process.  

### Goals  
The primary goal of this optimization problem is to minimize the total fuel consumption across all flights. This is achieved by optimizing the assignment of flights to airports and companies, ensuring that each flight is assigned to exactly one airport and one company while respecting operational constraints.  

Success is measured by the reduction in total fuel consumption, which is directly influenced by the fuel consumption coefficients and the assignment decisions. The optimization process ensures that the fuel consumption coefficients are used effectively to achieve the lowest possible fuel consumption while maintaining operational feasibility.  

## 2. Constraints  

The optimization problem is subject to the following constraints:  

1. **Flight Assignment Constraint**: Each flight must be assigned to exactly one airport and one company. This ensures that all flights are scheduled and no flight is left unassigned.  

2. **Airport Capacity Constraint**: The total number of flights assigned to an airport cannot exceed the airport's capacity. This ensures that airports are not overloaded and can handle the assigned flights efficiently.  

3. **Company Capacity Constraint**: The total number of flights assigned to a company cannot exceed the company's capacity. This ensures that companies can manage the assigned flights without exceeding their operational limits.  

4. **Velocity Constraint**: Each flight must operate within its predefined minimum and maximum velocity limits. This ensures that flights maintain safe and efficient speeds, balancing fuel consumption and travel time.  

5. **Altitude Constraint**: Each flight must operate within its predefined minimum and maximum altitude limits. This ensures that flights operate within safe and efficient airspace levels, avoiding conflicts with other air traffic and optimizing fuel efficiency.  

These constraints are designed to ensure that the optimization problem remains linear and feasible, avoiding any nonlinear relationships that could complicate the solution process.  

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 2 Database Schema
-- Objective: Schema changes include creating a table for flight assignments to address the missing decision variable. Configuration logic updates include scalar parameters for velocity and altitude constraints and formulas for fuel consumption calculations.

CREATE TABLE fuel_consumption_coefficients (
  flight_id INTEGER,
  coefficient FLOAT
);

CREATE TABLE velocity_constraints (
  flight_id INTEGER,
  velocity_min INTEGER,
  velocity_max INTEGER
);

CREATE TABLE altitude_constraints (
  flight_id INTEGER,
  altitude_min INTEGER,
  altitude_max INTEGER
);

CREATE TABLE airport_capacities (
  airport_id INTEGER,
  capacity INTEGER
);

CREATE TABLE company_capacities (
  company_id INTEGER,
  capacity INTEGER
);

CREATE TABLE flight_assignments (
  flight_id INTEGER,
  airport_id INTEGER,
  company_id INTEGER,
  assignment BOOLEAN
);
```

### Data Dictionary  
- **fuel_consumption_coefficients**: Contains the fuel consumption coefficients for each flight, which are used to calculate the total fuel consumption in the objective function.  
  - *flight_id*: Unique identifier for each flight.  
  - *coefficient*: Fuel consumption coefficient for the flight, used in the objective function.  

- **velocity_constraints**: Defines the minimum and maximum velocity constraints for each flight, ensuring flights operate within safe and efficient speed limits.  
  - *flight_id*: Unique identifier for each flight.  
  - *velocity_min*: Minimum velocity constraint for the flight.  
  - *velocity_max*: Maximum velocity constraint for the flight.  

- **altitude_constraints**: Defines the minimum and maximum altitude constraints for each flight, ensuring flights operate within safe and efficient airspace levels.  
  - *flight_id*: Unique identifier for each flight.  
  - *altitude_min*: Minimum altitude constraint for the flight.  
  - *altitude_max*: Maximum altitude constraint for the flight.  

- **airport_capacities**: Specifies the maximum number of flights each airport can handle, ensuring airports are not overloaded.  
  - *airport_id*: Unique identifier for each airport.  
  - *capacity*: Maximum number of flights the airport can handle.  

- **company_capacities**: Specifies the maximum number of flights each company can handle, ensuring companies can manage their assigned flights.  
  - *company_id*: Unique identifier for each company.  
  - *capacity*: Maximum number of flights the company can handle.  

- **flight_assignments**: Represents the binary decision variable indicating whether a flight is assigned to an airport and a company.  
  - *flight_id*: Unique identifier for each flight.  
  - *airport_id*: Unique identifier for each airport.  
  - *company_id*: Unique identifier for each company.  
  - *assignment*: Binary variable indicating if the flight is assigned to the airport and company.  

### Current Stored Values  
```sql
-- Iteration 2 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on industry standards for flight operations, ensuring realistic velocity, altitude, and capacity constraints. Fuel consumption coefficients were derived from typical aircraft performance data.

-- Realistic data for fuel_consumption_coefficients
INSERT INTO fuel_consumption_coefficients (flight_id, coefficient) VALUES (1, 0.5);
INSERT INTO fuel_consumption_coefficients (flight_id, coefficient) VALUES (2, 0.6);
INSERT INTO fuel_consumption_coefficients (flight_id, coefficient) VALUES (3, 0.7);

-- Realistic data for velocity_constraints
INSERT INTO velocity_constraints (flight_id, velocity_min, velocity_max) VALUES (1, 500, 900);
INSERT INTO velocity_constraints (flight_id, velocity_min, velocity_max) VALUES (2, 550, 950);
INSERT INTO velocity_constraints (flight_id, velocity_min, velocity_max) VALUES (3, 600, 1000);

-- Realistic data for altitude_constraints
INSERT INTO altitude_constraints (flight_id, altitude_min, altitude_max) VALUES (1, 30000, 40000);
INSERT INTO altitude_constraints (flight_id, altitude_min, altitude_max) VALUES (2, 31000, 41000);
INSERT INTO altitude_constraints (flight_id, altitude_min, altitude_max) VALUES (3, 32000, 42000);

-- Realistic data for airport_capacities
INSERT INTO airport_capacities (airport_id, capacity) VALUES (1, 10);
INSERT INTO airport_capacities (airport_id, capacity) VALUES (2, 15);
INSERT INTO airport_capacities (airport_id, capacity) VALUES (3, 20);

-- Realistic data for company_capacities
INSERT INTO company_capacities (company_id, capacity) VALUES (1, 5);
INSERT INTO company_capacities (company_id, capacity) VALUES (2, 10);
INSERT INTO company_capacities (company_id, capacity) VALUES (3, 15);

-- Realistic data for flight_assignments
INSERT INTO flight_assignments (flight_id, airport_id, company_id, assignment) VALUES (1, 1, 1, True);
INSERT INTO flight_assignments (flight_id, airport_id, company_id, assignment) VALUES (2, 2, 2, True);
INSERT INTO flight_assignments (flight_id, airport_id, company_id, assignment) VALUES (3, 3, 3, True);
```