# Complete Optimization Problem and Solution: battle_death

## 1. Problem Context and Goals

### Context  
In the context of naval operations, the strategic allocation of ships to battles is crucial to minimizing casualties. The decision-making process involves determining whether a specific ship should be assigned to a particular battle. This decision is represented by a binary choice, where each ship can either be assigned to a battle or not. The primary objective is to minimize the total number of deaths resulting from these assignments. This is achieved by considering the number of deaths each ship could potentially cause in a battle and making assignments that minimize this total.

Operational parameters include a maximum limit on the number of ships available for assignment, ensuring that the fleet's resources are not overextended. The business logic involves calculating the total deaths caused by ship assignments, which serves as the objective function to minimize. This calculation is straightforward and linear, focusing on the sum of potential deaths weighted by the assignment decisions.

The data used in this process reflects current operational scenarios, ensuring that decisions are based on realistic and historical naval battle data. The constraints are linear, focusing on resource limitations and ensuring that each ship is assigned to at most one battle, while each battle receives the required number of ships. The business configuration includes both scalar parameters, such as the maximum number of ships available, and business logic formulas that guide the decision-making process.

### Goals  
The primary goal of this optimization problem is to minimize the total number of deaths resulting from ship assignments to battles. This is achieved by strategically assigning ships in a way that reduces the potential casualties. The metric used to measure success is the total number of deaths, calculated as the sum of deaths caused by each ship in each battle, weighted by the assignment decisions. The optimization goal is clearly defined and aligns with the linear nature of the problem, focusing on minimizing this total in a straightforward manner.

## 2. Constraints    

The constraints in this optimization problem are designed to ensure that the ship assignments are both feasible and optimal:

- Each ship can be assigned to at most one battle. This constraint ensures that no ship is overcommitted, reflecting the operational reality that a ship cannot be in two places at once.
- Each battle must receive at least the minimum required number of ships. This ensures that the battles are adequately supported, aligning with strategic requirements.
- The total number of ships assigned cannot exceed the maximum number available. This constraint reflects the resource limitations of the fleet, ensuring that assignments do not exceed the available resources.

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 2 Database Schema
-- Objective: Incorporated missing data for deaths caused by ships in battles, adjusted schema to include objective coefficients, and updated business configuration logic for scalar parameters and formulas.

CREATE TABLE ship_battle_assignment (
  ship_id INTEGER,
  battle_id INTEGER,
  assignment BOOLEAN
);

CREATE TABLE battle_requirements (
  battle_id INTEGER,
  min_ships INTEGER
);

CREATE TABLE deaths_by_ship_battle (
  ship_id INTEGER,
  battle_id INTEGER,
  deaths INTEGER
);
```

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

- **Ship Battle Assignment Table**: This table tracks which ships are assigned to which battles. It plays a critical role in decision-making, with each assignment represented as a binary decision variable.
  - **Ship ID**: Identifies each ship, used to track assignments.
  - **Battle ID**: Identifies each battle, used to track assignments.
  - **Assignment**: Indicates whether a ship is assigned to a battle, serving as the binary decision variable.

- **Battle Requirements Table**: This table stores the minimum number of ships required for each battle, serving as a constraint bound in the optimization process.
  - **Battle ID**: Identifies each battle, linking to requirement checks.
  - **Minimum Ships**: Specifies the minimum number of ships needed for a battle, acting as a constraint bound.

- **Deaths by Ship Battle Table**: This table records the number of deaths caused by each ship in each battle, serving as the objective coefficients in the optimization problem.
  - **Ship ID**: Identifies each ship, used in death calculations.
  - **Battle ID**: Identifies each battle, used in death calculations.
  - **Deaths**: Represents the number of deaths caused by a ship in a battle, acting as a coefficient in the objective function.

### Current Stored Values  
```sql
-- Iteration 2 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical naval battle scenarios, ensuring that ship assignments and battle requirements are realistic and align with historical data on ship deployments and battle outcomes.

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

-- Realistic data for battle_requirements
INSERT INTO battle_requirements (battle_id, min_ships) VALUES (101, 3);
INSERT INTO battle_requirements (battle_id, min_ships) VALUES (102, 5);
INSERT INTO battle_requirements (battle_id, min_ships) VALUES (103, 2);

-- Realistic data for deaths_by_ship_battle
INSERT INTO deaths_by_ship_battle (ship_id, battle_id, deaths) VALUES (1, 101, 15);
INSERT INTO deaths_by_ship_battle (ship_id, battle_id, deaths) VALUES (2, 102, 25);
INSERT INTO deaths_by_ship_battle (ship_id, battle_id, deaths) VALUES (3, 103, 5);
```