# Complete Optimization Problem and Solution: flight_1

## 1. Problem Context and Goals

### Context  
An airline company is focused on optimizing its flight scheduling to minimize operational costs while ensuring that all flights are adequately covered by available aircraft and certified employees. The decision-making process involves assigning each flight to a specific aircraft and employee, represented by a binary decision variable indicating whether a flight is assigned to a particular aircraft and employee. The operational parameters are aligned with the objective of minimizing the total cost, which includes the cost of operating flights and employee salaries. The business configuration includes a maximum allowable budget for employee salaries to ensure that salary costs do not exceed the budget. The optimization problem is structured to adhere to linear relationships, avoiding any nonlinear scenarios such as variable products or divisions. The constraints are based on the aircraft's distance capabilities and budget limits, ensuring that the optimization remains feasible and realistic.

### Goals  
The primary goal of the optimization is to minimize the total operational cost associated with flight scheduling. This involves reducing the combined cost of operating flights and the salaries of employees assigned to these flights. Success is measured by achieving the lowest possible total cost while adhering to the constraints of aircraft capabilities and budget limits. The optimization goal is clearly defined in linear terms, focusing on minimizing the sum of flight operation costs and employee salaries.

## 2. Constraints    

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

- Each flight must be assigned to exactly one aircraft and one employee. This ensures that all flights are covered without overlap or omission.
- The total distance covered by the assigned aircraft for each flight must not exceed the aircraft's maximum distance capability. This constraint ensures that aircraft are not overburdened beyond their operational limits.
- The total salary cost of employees assigned to flights must not exceed the predefined budget constraint. This ensures that the airline operates within its financial limits.
- The decision variable indicating flight assignments is binary, meaning it can only take values of true or false, ensuring clear and unambiguous assignment decisions.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating a new table for decision variables and updating business configuration logic for budget constraints. Adjustments ensure all optimization requirements are met and data is organized according to best practices.

CREATE TABLE flight (
  flno INTEGER,
  price FLOAT,
  distance FLOAT
);

CREATE TABLE aircraft (
  aid INTEGER,
  distance FLOAT
);

CREATE TABLE employee (
  eid INTEGER,
  salary FLOAT
);

CREATE TABLE flight_assignment (
  flno INTEGER,
  aid INTEGER,
  eid INTEGER,
  binary_decision BOOLEAN
);
```

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

- **Flight Table**: Contains information about each flight, including a unique identifier (flno), the cost of operating the flight (price), and the distance that needs to be covered. This data is crucial for determining the cost coefficients in the optimization objective and setting constraints on aircraft assignments.

- **Aircraft Table**: Stores details about each aircraft, including a unique identifier (aid) and the maximum distance it can cover. This information is used to set constraints ensuring that aircraft are not assigned to flights beyond their capabilities.

- **Employee Table**: Includes data on employees, with a unique identifier (eid) and their salary. Employee salary data is used to calculate the cost component of the optimization objective and to enforce budget constraints.

- **Flight Assignment Table**: Tracks the assignment of flights to aircraft and employees, using a binary decision variable to indicate whether a specific flight is assigned to a particular aircraft and employee. This table is central to the decision-making process in the optimization model.

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical airline operational costs, aircraft capabilities, and employee salary ranges to ensure a realistic and solvable optimization problem.

-- Realistic data for flight
INSERT INTO flight (flno, price, distance) VALUES (101, 5000.0, 300.0);
INSERT INTO flight (flno, price, distance) VALUES (102, 7500.0, 450.0);
INSERT INTO flight (flno, price, distance) VALUES (103, 10000.0, 600.0);

-- Realistic data for aircraft
INSERT INTO aircraft (aid, distance) VALUES (1, 500.0);
INSERT INTO aircraft (aid, distance) VALUES (2, 700.0);
INSERT INTO aircraft (aid, distance) VALUES (3, 1000.0);

-- Realistic data for employee
INSERT INTO employee (eid, salary) VALUES (10, 3000.0);
INSERT INTO employee (eid, salary) VALUES (20, 4000.0);
INSERT INTO employee (eid, salary) VALUES (30, 5000.0);

-- Realistic data for flight_assignment
INSERT INTO flight_assignment (flno, aid, eid, binary_decision) VALUES (101, 1, 10, True);
INSERT INTO flight_assignment (flno, aid, eid, binary_decision) VALUES (102, 2, 20, True);
INSERT INTO flight_assignment (flno, aid, eid, binary_decision) VALUES (103, 3, 30, True);
```