# Complete Optimization Problem and Solution: world_1

## 1. Problem Context and Goals

### Context  
A government agency is tasked with allocating a fixed budget across multiple cities to maximize the total population served. The allocation must ensure equitable distribution across districts while adhering to budget constraints. Each city has a specific population size, and the budget allocated to a city directly impacts the number of people served. The agency has established operational parameters to guide the allocation process:  
- **Total Budget Available for Allocation**: The agency has a fixed total budget of 1,000,000 units, which cannot be exceeded.  
- **Minimum Budget Allocation per City**: Each city must receive at least 50,000 units to ensure even smaller cities receive a fair share.  
- **Minimum Budget Allocation per District**: Each district must receive a minimum of 200,000 units to ensure equitable distribution across regions.  

The decision variables in this problem are the budget allocations to each city, represented as continuous values. The objective is to maximize the total population served by allocating the budget in a way that respects these constraints.  

### Goals  
The primary goal of this optimization problem is to maximize the total population served by the budget allocation. This is achieved by prioritizing cities with larger populations while ensuring that all cities and districts receive their minimum required allocations. Success is measured by the total population served, which is directly proportional to the budget allocated to each city. The optimization process ensures that the allocation is both efficient and equitable, aligning with the agency’s operational and policy objectives.  

## 2. Constraints  

The budget allocation process is subject to the following constraints:  
1. **Total Budget Limit**: The sum of all budget allocations across cities must not exceed the total available budget of 1,000,000 units.  
2. **Minimum Allocation per City**: Each city must receive at least 50,000 units of the budget to ensure a fair distribution.  
3. **Minimum Allocation per District**: The total budget allocated to all cities within a district must be at least 200,000 units to maintain equity across regions.  

These constraints ensure that the budget allocation is both feasible and aligned with the agency’s operational requirements.  

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating tables for budget allocation, districts, and cities. Configuration logic updates include adding scalar parameters for total budget, minimum allocation per city, and minimum allocation per district.

CREATE TABLE city (
  CityID INTEGER,
  Population INTEGER,
  DistrictID INTEGER
);

CREATE TABLE district (
  DistrictID INTEGER,
  DistrictName STRING
);

CREATE TABLE budget_allocation (
  CityID INTEGER,
  Allocation FLOAT
);
```

### Data Dictionary  
- **city**: Stores city-specific data, including population and district affiliation.  
  - **CityID**: Unique identifier for each city.  
  - **Population**: Population of the city, used as the coefficient in the objective function.  
  - **DistrictID**: District to which the city belongs, used for grouping in district-level constraints.  
- **district**: Stores district-specific data, including district names.  
  - **DistrictID**: Unique identifier for each district.  
  - **DistrictName**: Name of the district, used for business context.  
- **budget_allocation**: Stores the budget allocated to each city.  
  - **CityID**: City to which the budget is allocated.  
  - **Allocation**: Budget allocated to the city, representing the decision variable in the optimization problem.  


### Retrieved Values

**Query 1: The population acts as the coefficient in the optimization problem.**

```sql
SELECT CityID, Population, DistrictID FROM city;
```

**Results (CSV format):**
```csv
CityID,Population,DistrictID
1,150000,1
2,250000,2
3,100000,3
```

**Query 2: This data is useful for understanding the distribution of population across districts, which can influence the allocation strategy.**

```sql
SELECT DistrictID, SUM(Population) AS TotalPopulation FROM city GROUP BY DistrictID;
```

**Results (CSV format):**
```csv
DistrictID,TotalPopulation
1,150000
2,250000
3,100000
```

**Query 3: This data represents the decision variables in the optimization problem and is necessary to ensure that the allocations meet the constraints.**

```sql
SELECT CityID, Allocation FROM budget_allocation;
```

**Results (CSV format):**
```csv
CityID,Allocation
1,60000.0
2,80000.0
3,50000.0
```

**Query 4: This data is important for enforcing the district-level minimum allocation constraint.**

```sql
SELECT DistrictID, SUM(Allocation) AS TotalAllocation FROM budget_allocation JOIN city ON budget_allocation.CityID = city.CityID GROUP BY DistrictID;
```

**Results (CSV format):**
```csv
DistrictID,TotalAllocation
1,60000.0
2,80000.0
3,50000.0
```

**Query 5: This data is necessary to ensure that the total budget constraint is not exceeded.**

```sql
SELECT SUM(Allocation) AS TotalBudgetAllocated FROM budget_allocation;
```

**Results (CSV format):**
```csv
TotalBudgetAllocated
190000.0
```

**Query 6: This data is useful for verifying that each city meets the minimum allocation requirement and for understanding the impact of allocations on population served.**

```sql
SELECT city.CityID, city.Population, budget_allocation.Allocation FROM city LEFT JOIN budget_allocation ON city.CityID = budget_allocation.CityID;
```

**Results (CSV format):**
```csv
CityID,Population,Allocation
1,150000,60000.0
2,250000,80000.0
3,100000,50000.0
```

**Query 7: This data is useful for providing context and ensuring that district-level constraints are applied correctly.**

```sql
SELECT DistrictID, DistrictName FROM district;
```

**Results (CSV format):**
```csv
DistrictID,DistrictName
1,North
2,South
3,East
```

**Query 8: This data is useful for understanding the distribution of cities across districts, which can influence the allocation strategy.**

```sql
SELECT DistrictID, COUNT(CityID) AS NumberOfCities FROM city GROUP BY DistrictID;
```

**Results (CSV format):**
```csv
DistrictID,NumberOfCities
1,1
2,1
3,1
```

**Query 9: This data is important for identifying cities that need adjustments to meet the minimum allocation constraint.**

```sql
SELECT city.CityID, city.Population, budget_allocation.Allocation FROM city LEFT JOIN budget_allocation ON city.CityID = budget_allocation.CityID WHERE budget_allocation.Allocation < 50000 OR budget_allocation.Allocation IS NULL;
```

**Results (CSV format):**
```csv
CityID,Population,Allocation
```

**Query 10: This data is important for identifying districts that need adjustments to meet the minimum allocation constraint.**

```sql
SELECT city.DistrictID, SUM(budget_allocation.Allocation) AS TotalAllocation FROM city LEFT JOIN budget_allocation ON city.CityID = budget_allocation.CityID GROUP BY city.DistrictID HAVING SUM(budget_allocation.Allocation) < 200000 OR SUM(budget_allocation.Allocation) IS NULL;
```

**Results (CSV format):**
```csv
DistrictID,TotalAllocation
1,60000.0
2,80000.0
3,50000.0
```

