# Complete Optimization Problem and Solution: ship_1

## 1. Problem Context and Goals

### Context  
The business objective is to optimize the assignment of captains to ships in a manner that minimizes the total age of the captains assigned. Each captain can only be assigned to one ship, and each ship must have exactly one captain. The decision-making process involves determining which captain is assigned to which ship, represented by a binary decision variable indicating if a captain is assigned to a specific ship. The operational parameters are aligned with the goal of minimizing the total age of captains assigned to ships, calculated by summing the ages of all assigned captains. The business configuration includes a maximum allowable age for captains, ensuring that only eligible captains are considered for assignment. This maximum age limit is set at 60, ensuring captains are experienced but not beyond typical retirement age. The calculation of the total age of assigned captains is expressed in natural language as the sum of the ages of captains assigned to ships, which forms the basis of the objective function to minimize. The data presented reflects current operational information, focusing on precise decision-making that leads to linear formulations. Resource limitations are expressed through constraints that ensure each captain is assigned to exactly one ship and each ship has exactly one captain, avoiding scenarios that require nonlinear relationships.

### Goals  
The primary goal of this optimization problem is to minimize the total age of captains assigned to ships. The metric to optimize is the total age, calculated as the sum of the ages of all captains assigned to ships. Success is measured by achieving the lowest possible total age, aligning with the expected coefficient sources, which are the ages of the captains. The optimization goal is described in natural language, focusing on minimizing the total age of captains assigned to ships, without using mathematical formulas or symbolic notation.

## 2. Constraints    

The constraints for this optimization problem are designed to ensure that each captain is assigned to exactly one ship and each ship has exactly one captain. These constraints are expressed in business terms that naturally lead to linear mathematical forms. Specifically, for each captain, the sum of assignments across all ships must equal one, ensuring that each captain is assigned to one ship. Similarly, for each ship, the sum of assignments across all captains must equal one, ensuring that each ship has exactly one captain. These constraints are critical to maintaining the integrity of the assignment process and ensuring that the optimization problem remains linear.

## 3. Available Data  

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

CREATE TABLE Captain (
  id INTEGER,
  age INTEGER,
  class STRING
);

CREATE TABLE CaptainShipAssignment (
  captain_id INTEGER,
  ship_id INTEGER,
  assignment BOOLEAN
);

CREATE TABLE ConstraintBounds (
  captain_assignment INTEGER,
  ship_assignment INTEGER
);
```

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

- **Captain Table**: This table stores information about captains, including their unique identifier, age, and class. The age of the captain serves as a coefficient in the objective function, contributing to the calculation of the total age of assigned captains.

- **CaptainShipAssignment Table**: This table represents the assignments of captains to ships. It includes the ID of the assigned captain and the ID of the ship to which a captain is assigned. The assignment column is a binary decision variable indicating whether a captain is assigned to a ship.

- **ConstraintBounds Table**: This table stores the constraint bounds for assignments, ensuring that each captain is assigned to exactly one ship and each ship has exactly one captain. The captain_assignment and ship_assignment columns ensure compliance with these constraints.

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were chosen to reflect a realistic distribution of captain ages and ship assignments, ensuring that the optimization problem remains solvable while respecting business constraints.

-- Realistic data for Captain
INSERT INTO Captain (id, age, class) VALUES (1, 45, 'A');
INSERT INTO Captain (id, age, class) VALUES (2, 50, 'B');
INSERT INTO Captain (id, age, class) VALUES (3, 55, 'C');

-- Realistic data for CaptainShipAssignment
INSERT INTO CaptainShipAssignment (captain_id, ship_id, assignment) VALUES (1, 101, True);
INSERT INTO CaptainShipAssignment (captain_id, ship_id, assignment) VALUES (2, 102, True);
INSERT INTO CaptainShipAssignment (captain_id, ship_id, assignment) VALUES (3, 103, True);

-- Realistic data for ConstraintBounds
INSERT INTO ConstraintBounds (captain_assignment, ship_assignment) VALUES (1, 1);
```