# Complete Optimization Problem and Solution: battle_death

## 1. Problem Context and Goals

### Context  
In military operations, the allocation of ships to battles is a critical decision that directly impacts the outcome of engagements. The goal is to minimize the total number of casualties (killed and injured) across all battles by optimally deploying ships, considering their tonnage and the constraints of each battle. Each ship can only be deployed to one battle, and the total tonnage of ships deployed to a battle must not exceed the maximum tonnage allowed for that battle. The decision to deploy a ship to a specific battle is represented as a binary choice: a ship is either deployed or not. The total casualties in a battle are calculated as the sum of killed and injured personnel, which serves as the primary metric for optimization. The maximum tonnage allowed per battle is a key operational parameter that ensures realistic and feasible deployment scenarios.

### Goals  
The primary goal of this optimization problem is to minimize the total number of casualties across all battles. This is achieved by strategically deciding which ships to deploy to each battle, ensuring that the total tonnage of deployed ships does not exceed the maximum allowed for each battle. Success is measured by the reduction in the sum of killed and injured personnel across all battles, aligning with the operational objective of minimizing human losses.

## 2. Constraints    

1. **Tonnage Constraint**: For each battle, the total tonnage of all ships deployed to that battle must not exceed the maximum tonnage allowed for that battle. This ensures that the deployment of ships is within the operational capacity of each battle.

2. **Single Deployment Constraint**: Each ship can only be deployed to one battle. This ensures that ships are not overcommitted and are available for their assigned engagements.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating tables for ship tonnage, battle constraints, and ship-to-battle deployment decisions. Configuration logic updates include scalar parameters for maximum tonnage and formulas for casualty calculations.

CREATE TABLE ship_tonnage (
  ship_id INTEGER,
  tonnage INTEGER
);

CREATE TABLE battle_constraints (
  battle_id INTEGER,
  max_tonnage INTEGER
);

CREATE TABLE ship_deployment (
  ship_id INTEGER,
  battle_id INTEGER,
  deployed BOOLEAN
);

CREATE TABLE death (
  battle_id INTEGER,
  ship_id INTEGER,
  killed INTEGER,
  injured INTEGER
);


```

### Data Dictionary  
- **ship_tonnage**: Contains the tonnage of each ship available for deployment.  
  - *ship_id*: Unique identifier for each ship.  
  - *tonnage*: Tonnage of the ship, used in the tonnage constraint.  

- **battle_constraints**: Specifies the maximum tonnage allowed per battle.  
  - *battle_id*: Unique identifier for each battle.  
  - *max_tonnage*: Maximum tonnage allowed in the battle, used as a constraint bound in the optimization model.  

- **ship_deployment**: Represents the binary decision variable indicating if a ship is deployed to a battle.  
  - *ship_id*: Unique identifier for each ship.  
  - *battle_id*: Unique identifier for each battle.  
  - *deployed*: Indicates if the ship is deployed to the battle, used as a decision variable in the optimization model.  

- **death**: Records the casualties in each battle.  
  - *battle_id*: Unique identifier for each battle.  
  - *ship_id*: Unique identifier for each ship.  
  - *killed*: Number of killed in the battle, used as an objective coefficient in the optimization model.  
  - *injured*: Number of injured in the battle, used as an objective coefficient in the optimization model.  

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on realistic military ship tonnages, battle constraints, and casualty figures, ensuring a meaningful and solvable optimization problem.

-- Realistic data for ship_tonnage
INSERT INTO ship_tonnage (ship_id, tonnage) VALUES (1, 5000);
INSERT INTO ship_tonnage (ship_id, tonnage) VALUES (2, 7000);
INSERT INTO ship_tonnage (ship_id, tonnage) VALUES (3, 9000);

-- Realistic data for battle_constraints
INSERT INTO battle_constraints (battle_id, max_tonnage) VALUES (1, 10000);
INSERT INTO battle_constraints (battle_id, max_tonnage) VALUES (2, 12000);
INSERT INTO battle_constraints (battle_id, max_tonnage) VALUES (3, 15000);

-- Realistic data for ship_deployment
INSERT INTO ship_deployment (ship_id, battle_id, deployed) VALUES (1, 1, True);
INSERT INTO ship_deployment (ship_id, battle_id, deployed) VALUES (2, 2, True);
INSERT INTO ship_deployment (ship_id, battle_id, deployed) VALUES (3, 3, True);

-- Realistic data for death
INSERT INTO death (battle_id, ship_id, killed, injured) VALUES (1, 1, 10, 15);
INSERT INTO death (battle_id, ship_id, killed, injured) VALUES (2, 2, 20, 25);
INSERT INTO death (battle_id, ship_id, killed, injured) VALUES (3, 3, 30, 35);


```