# Complete Optimization Problem and Solution: storm_record

## 1. Problem Context and Goals

### Context  
The disaster management agency is tasked with efficiently distributing resources to minimize the financial impact of storms across various regions. The agency's primary decision involves determining the quantity of resources to allocate to each region, with the goal of minimizing the total damage cost. This decision-making process is guided by the need to allocate resources in a way that aligns with the available budget and the severity of storm impacts, as indicated by the number of affected cities and the maximum speed of storms. The agency has a fixed amount of resources available for allocation, which serves as a constraint in their planning. The operational parameters are structured to ensure that the decision-making process is linear, focusing on straightforward allocation without complex interactions between variables. The business configuration includes a total resource availability parameter, which is crucial for ensuring that resource distribution remains within feasible limits.

### Goals  
The primary goal of the optimization process is to minimize the total damage cost incurred by storms. This is achieved by strategically allocating resources to regions in a manner that reduces the overall financial impact. The metric for optimization is the total damage cost, calculated as the sum of the product of the damage cost per storm and the resources allocated to each region. Success in this context is measured by the ability to effectively reduce the total damage cost while adhering to the constraints of resource availability and storm severity. The optimization goal is clearly defined in linear terms, focusing on minimizing the financial impact through efficient resource distribution.

## 2. Constraints    

The resource allocation process is subject to several constraints that ensure feasibility and effectiveness. Firstly, the total amount of resources allocated across all regions must not exceed the total resources available. This constraint ensures that the agency operates within its budgetary limits. Additionally, the resources allocated to each region must not surpass the product of the number of cities affected and the maximum speed of storms in that region. This constraint reflects the practical limitations imposed by the severity of storm impacts, ensuring that resource distribution is aligned with the actual needs of each region. These constraints are articulated in business terms that naturally translate into linear mathematical forms, avoiding complex interactions such as variable products or divisions.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating a new table for decision variables and updating the business configuration logic to include total available resources as a scalar parameter. Adjustments ensure all optimization requirements are mapped and business logic is preserved.

CREATE TABLE storm (
  Damage_millions_USD FLOAT,
  Max_speed FLOAT
);

CREATE TABLE affected_region (
  Number_city_affected INTEGER
);

CREATE TABLE resource_allocation (
  resources_allocated FLOAT
);
```

### Data Dictionary  
The data dictionary provides a comprehensive overview of the tables and columns used in the optimization process, highlighting their business purposes and roles in the optimization model:

- **Storm Table**: This table captures data related to storms impacting various regions. It includes:
  - **Damage Cost (in millions USD)**: Represents the financial impact of each storm, serving as a coefficient in the objective function to minimize total damage cost.
  - **Maximum Speed**: Indicates the maximum speed of storms affecting each region, used as a constraint bound for resource allocation.

- **Affected Region Table**: This table contains information about regions impacted by storms. It includes:
  - **Number of Cities Affected**: Reflects the number of cities impacted in each region, serving as a constraint bound for resource allocation.

- **Resource Allocation Table**: This table records the allocation of resources to each region. It includes:
  - **Resources Allocated**: Represents the amount of resources distributed to each region, acting as a decision variable in the optimization model.

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on historical storm data and typical resource allocation scenarios in disaster management. The approach ensures that the data reflects realistic storm impacts and resource constraints.

-- Realistic data for storm
INSERT INTO storm (Damage_millions_USD, Max_speed) VALUES (12.0, 110.0);
INSERT INTO storm (Damage_millions_USD, Max_speed) VALUES (25.0, 140.0);
INSERT INTO storm (Damage_millions_USD, Max_speed) VALUES (18.0, 130.0);

-- Realistic data for affected_region
INSERT INTO affected_region (Number_city_affected) VALUES (4);
INSERT INTO affected_region (Number_city_affected) VALUES (6);
INSERT INTO affected_region (Number_city_affected) VALUES (5);

-- Realistic data for resource_allocation
INSERT INTO resource_allocation (resources_allocated) VALUES (150.0);
INSERT INTO resource_allocation (resources_allocated) VALUES (250.0);
INSERT INTO resource_allocation (resources_allocated) VALUES (200.0);
```