# Complete Optimization Problem and Solution: ship_mission

## 1. Problem Context and Goals

### Context  
The primary objective is to optimize the allocation of ships to various missions in a manner that minimizes the total operational costs. Each ship can be assigned to only one mission at a time, and each mission must be covered by exactly one ship. The decision-making process involves determining whether a ship is assigned to a mission, represented by a binary decision variable. The operational costs are influenced by two main factors: the cost associated with each knot of speed for a ship and the cost associated with each ton of the ship's capacity. These costs are crucial in calculating the total operational cost, which is the sum of the speed-related and capacity-related expenses for all ships. The business configuration includes these cost parameters, ensuring that the optimization aligns with the operational realities of the fleet. The formula to calculate the total operational cost is expressed in natural language as the sum of the costs per knot of speed and per ton of capacity for each ship, reflecting the objective of cost minimization. The data presented reflects current operational information, focusing on precise decision-making that leads to linear formulations. Resource limitations are considered to ensure that each mission is adequately covered without exceeding the capacity of any ship.

### Goals  
The goal of this optimization problem is to minimize the total operational cost associated with assigning ships to missions. The metric to optimize is the total operational cost, which is calculated as the sum of the costs per knot of speed and per ton of capacity for each ship. Success in this optimization is measured by achieving the lowest possible operational cost while ensuring that all missions are covered and no ship is over-assigned. The optimization goal is clearly defined in natural language, emphasizing the linear nature of the cost minimization objective.

## 2. Constraints    

The constraints for this optimization problem are designed to ensure that each mission is covered by exactly one ship and that each ship is assigned to at most one mission. Specifically, the first constraint requires that for every mission, there is exactly one ship assigned, ensuring complete mission coverage. The second constraint ensures that no ship is assigned to more than one mission at a time, maintaining operational feasibility. These constraints are expressed 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 adjustments and configuration logic updates were made to address the OR expert's mapping gaps and missing requirements, ensuring a comprehensive and optimized database structure.

CREATE TABLE ship (
  Ship_ID INTEGER,
  Tonnage FLOAT,
  cost_per_knot FLOAT,
  cost_per_ton FLOAT
);

CREATE TABLE ship_mission_assignment (
  Ship_ID INTEGER,
  Mission_ID INTEGER
);
```

### Data Dictionary  
The data dictionary provides a comprehensive mapping of tables and columns to their business purposes and optimization roles. The "ship" table stores information about each ship, including its unique identifier, capacity in tons, and costs associated with speed and capacity. These attributes play a crucial role in calculating the operational costs and are integral to the optimization process. The "ship_mission_assignment" table represents the assignment of ships to missions, with each entry indicating which ship is assigned to which mission. This table is central to the decision variable mapping, ensuring that the optimization aligns with the business requirement of mission coverage.

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical operational costs and capacities for ships, ensuring a balance between cost efficiency and mission requirements.

-- Realistic data for ship
INSERT INTO ship (Ship_ID, Tonnage, cost_per_knot, cost_per_ton) VALUES (1, 1500.0, 11.0, 5.5);
INSERT INTO ship (Ship_ID, Tonnage, cost_per_knot, cost_per_ton) VALUES (2, 2500.0, 13.0, 6.0);
INSERT INTO ship (Ship_ID, Tonnage, cost_per_knot, cost_per_ton) VALUES (3, 1800.0, 12.0, 5.8);

-- Realistic data for ship_mission_assignment
INSERT INTO ship_mission_assignment (Ship_ID, Mission_ID) VALUES (1, 101);
INSERT INTO ship_mission_assignment (Ship_ID, Mission_ID) VALUES (2, 102);
INSERT INTO ship_mission_assignment (Ship_ID, Mission_ID) VALUES (3, 103);
```