# Complete Optimization Problem and Solution: flight_1

## 1. Problem Context and Goals

### Context  
An airline is tasked with optimizing its flight operations to minimize costs while ensuring operational efficiency. The airline must decide which flights to operate and which employees to assign to those flights, considering aircraft availability and employee certifications. Each flight has an associated operational cost, and each employee has a fixed salary. The goal is to minimize the total cost, which includes the sum of flight operational costs and employee salaries.  

The airline operates flights between specific origin and destination pairs, with a maximum number of flights allowed for each pair based on aircraft capacity. Additionally, every flight that is operated must be staffed with certified employees. The operational cost of each flight is determined by factors such as fuel, maintenance, and other expenses, while employee salaries are based on industry standards for pilots and cabin crew.  

The problem is formulated as a linear optimization model to ensure computational efficiency and scalability. The decision variables include whether to operate a specific flight and whether to assign a specific employee to a flight. The objective is to minimize the total cost, which is a linear combination of flight operational costs and employee salaries.  

### Goals  
The primary goal of this optimization problem is to minimize the total operational cost for the airline. This cost includes the sum of the operational costs for all flights that are operated and the salaries of all employees assigned to those flights. Success is measured by achieving the lowest possible total cost while ensuring that all operational constraints, such as aircraft capacity and employee staffing requirements, are satisfied.  

## 2. Constraints  

1. **Flight Capacity Constraint**: The number of flights operated between any origin and destination pair must not exceed the maximum number of flights allowed for that pair. This ensures that aircraft are not overutilized and that operational limits are respected.  

2. **Employee Assignment Constraint**: Every flight that is operated must be staffed with at least one certified employee. This ensures that all flights have the necessary personnel to operate safely and efficiently.  

These constraints are designed to ensure that the airline's operations remain within feasible limits while minimizing costs.  

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 2 Database Schema
-- Objective: Schema changes include creating tables for flight costs and employee salaries to address missing optimization requirements. Business configuration logic updated to include scalar parameters for flight costs and employee salaries.

CREATE TABLE aircraft_capacity (
  origin STRING,
  destination STRING,
  max_flights INTEGER
);

CREATE TABLE employee_assignment (
  eid INTEGER,
  flno INTEGER
);

CREATE TABLE flight_operation (
  flno INTEGER
);
```

### Data Dictionary  
- **aircraft_capacity**:  
  - **Business Purpose**: Specifies the maximum number of flights allowed between origin and destination pairs based on aircraft availability.  
  - **Columns**:  
    - **origin**: The airport code for the origin of the flight.  
    - **destination**: The airport code for the destination of the flight.  
    - **max_flights**: The maximum number of flights allowed between the origin and destination.  

- **employee_assignment**:  
  - **Business Purpose**: Tracks which employees are assigned to which flights.  
  - **Columns**:  
    - **eid**: The unique identifier for an employee.  
    - **flno**: The flight number to which the employee is assigned.  

- **flight_operation**:  
  - **Business Purpose**: Tracks which flights are operated.  
  - **Columns**:  
    - **flno**: The unique identifier for a flight.  

### Current Stored Values  
```sql
-- Iteration 2 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on industry standards for flight costs and employee salaries, ensuring they align with typical airline operations. Flight numbers and employee IDs were generated sequentially to maintain consistency.

-- Realistic data for aircraft_capacity
INSERT INTO aircraft_capacity (origin, destination, max_flights) VALUES ('JFK', 'LAX', 8);
INSERT INTO aircraft_capacity (origin, destination, max_flights) VALUES ('LAX', 'JFK', 8);
INSERT INTO aircraft_capacity (origin, destination, max_flights) VALUES ('ORD', 'DFW', 5);

-- Realistic data for employee_assignment
INSERT INTO employee_assignment (eid, flno) VALUES (1, 101);
INSERT INTO employee_assignment (eid, flno) VALUES (2, 102);
INSERT INTO employee_assignment (eid, flno) VALUES (3, 103);

-- Realistic data for flight_operation
INSERT INTO flight_operation (flno) VALUES (101);
INSERT INTO flight_operation (flno) VALUES (102);
INSERT INTO flight_operation (flno) VALUES (103);
```