# Complete Optimization Problem and Solution: county_public_safety

## 1. Problem Context and Goals

### Context  
The optimization problem focuses on allocating police officers across multiple counties to minimize the overall crime rate while adhering to operational and resource constraints. The key decision involves determining the number of police officers assigned to each county, which must be an integer value. The crime rate in each county serves as a critical factor in this decision, as it directly influences the effectiveness of police presence. 

The total number of police officers available for allocation is limited, ensuring that the solution remains within budget and resource capacity. Additionally, each county has specific requirements for the minimum and maximum number of police officers allowed, based on local needs and operational capacity. Furthermore, the case burden per officer is constrained to ensure that officers are not overburdened, with a maximum case burden defined for each county. These constraints ensure that the allocation is both feasible and sustainable.

The business configuration includes two key parameters: the total number of police officers available for allocation and the maximum case burden per officer in each county. These parameters are critical for defining the boundaries of the optimization problem and ensuring that the solution aligns with operational realities.

### Goals  
The primary goal of this optimization is to minimize the total crime rate across all counties by strategically allocating police officers. This is achieved by reducing the product of the crime rate and the number of police officers in each county. Success is measured by the overall reduction in crime rate, which is directly influenced by the allocation of police officers. The optimization ensures that the allocation respects all operational constraints, including the total number of police officers available, the minimum and maximum police officers per county, and the maximum case burden per officer.

## 2. Constraints  

The optimization problem is subject to the following constraints:  
1. **Total Police Officers Constraint**: The sum of police officers allocated across all counties must not exceed the total number of police officers available.  
2. **Minimum Police Officers Constraint**: Each county must have at least the minimum number of police officers required to maintain public safety.  
3. **Maximum Police Officers Constraint**: Each county cannot exceed the maximum number of police officers allowed, based on local operational capacity.  
4. **Case Burden Constraint**: The number of police officers in each county must be sufficient to handle the population, ensuring that the case burden per officer does not exceed the defined maximum.  

These constraints ensure that the allocation of police officers is both feasible and aligned with operational requirements.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating new tables for missing optimization requirements and updating business configuration logic for scalar parameters and formulas.

CREATE TABLE county_public_safety (
  County_ID INTEGER,
  Crime_rate FLOAT,
  Police_officers INTEGER,
  Population INTEGER
);

CREATE TABLE county_police_limits (
  County_ID INTEGER,
  Minimum_Police_Officers INTEGER,
  Maximum_Police_Officers INTEGER
);
```

### Data Dictionary  
- **county_public_safety**:  
  - **County_ID**: Unique identifier for the county, used to index decision variables and constraints.  
  - **Crime_rate**: The crime rate in the county, serving as the coefficient in the objective function.  
  - **Police_officers**: The number of police officers in the county, which is the decision variable to be optimized.  
  - **Population**: The population of the county, used to define the case burden constraint.  

- **county_police_limits**:  
  - **County_ID**: Unique identifier for the county, used to index constraints.  
  - **Minimum_Police_Officers**: The minimum number of police officers required in the county, defining a lower bound for the decision variable.  
  - **Maximum_Police_Officers**: The maximum number of police officers allowed in the county, defining an upper bound for the decision variable.  

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on realistic population sizes, crime rates, and police officer allocations, ensuring that constraints are feasible and the optimization problem is solvable.

-- Realistic data for county_public_safety
INSERT INTO county_public_safety (County_ID, Crime_rate, Police_officers, Population) VALUES (1, 0.05, 50, 10000);
INSERT INTO county_public_safety (County_ID, Crime_rate, Police_officers, Population) VALUES (2, 0.1, 100, 20000);
INSERT INTO county_public_safety (County_ID, Crime_rate, Police_officers, Population) VALUES (3, 0.15, 150, 30000);

-- Realistic data for county_police_limits
INSERT INTO county_police_limits (County_ID, Minimum_Police_Officers, Maximum_Police_Officers) VALUES (1, 10, 100);
INSERT INTO county_police_limits (County_ID, Minimum_Police_Officers, Maximum_Police_Officers) VALUES (2, 20, 200);
INSERT INTO county_police_limits (County_ID, Minimum_Police_Officers, Maximum_Police_Officers) VALUES (3, 30, 300);
```