# Complete Optimization Problem and Solution: school_finance

## 1. Problem Context and Goals

### Context  
A school district is tasked with allocating its budget across multiple schools to maximize the overall educational impact. The impact is measured by the sum of weighted investments, where each school has a specific weight representing the educational benefit per dollar invested. The district has a total budget of $1,000,000 available for investment across all schools, and each school must receive a minimum investment to ensure basic operational needs are met. The minimum investment required for each school varies, with values such as $60,000, $70,000, and $50,000 depending on the school. The goal is to determine the optimal amount to invest in each school, ensuring that the total investment does not exceed the available budget and that each school receives at least its minimum required investment.

### Goals  
The primary goal of this optimization problem is to maximize the total educational impact across all schools. This is achieved by allocating investments in a way that the sum of the weighted investments is as large as possible. The weights reflect the relative educational impact per dollar invested in each school, and the success of the allocation is measured by the total weighted sum of investments. The optimization process ensures that the district’s budget is used efficiently to achieve the highest possible educational benefit.

## 2. Constraints    

The optimization problem is subject to two key constraints:  
1. **Total Budget Constraint**: The sum of investments across all schools must not exceed the total available budget of $1,000,000. This ensures that the district does not overspend its allocated resources.  
2. **Minimum Investment Constraint**: Each school must receive at least its specified minimum investment. For example, School 1 must receive at least $60,000, School 2 at least $70,000, and School 3 at least $50,000. This ensures that all schools have the necessary funding to operate effectively.  

These constraints ensure that the investment decisions are both financially feasible and operationally sound.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating tables for school weights and minimum investments, updating the budget table, and adding configuration logic for scalar parameters and formulas.

CREATE TABLE school_weights (
  school_id INTEGER,
  weight FLOAT
);

CREATE TABLE school_minimum_investments (
  school_id INTEGER,
  minimum_investment FLOAT
);

CREATE TABLE budget (
  school_id INTEGER,
  Invested FLOAT,
  total_budget FLOAT
);
```

### Data Dictionary  
- **school_weights**:  
  - **Purpose**: Stores the weights representing the educational impact per dollar invested in each school.  
  - **Columns**:  
    - `school_id`: Unique identifier for each school.  
    - `weight`: The weight reflecting the educational impact per dollar invested in the school.  

- **school_minimum_investments**:  
  - **Purpose**: Stores the minimum investment required for each school to ensure basic operational needs.  
  - **Columns**:  
    - `school_id`: Unique identifier for each school.  
    - `minimum_investment`: The minimum amount of funding required for the school.  

- **budget**:  
  - **Purpose**: Tracks the current investments in each school and the total budget available for allocation.  
  - **Columns**:  
    - `school_id`: Unique identifier for each school.  
    - `Invested`: The amount currently invested in the school.  
    - `total_budget`: The total budget available for investment across all schools.  


### Retrieved Values

**Query 1: This data is crucial for determining the relative benefit of investing in each school.**

```sql
SELECT school_id, weight FROM school_weights;
```

**Results (CSV format):**
```csv
school_id,weight
1,0.6
2,0.8
3,0.5
```

**Query 2: This ensures that each school receives at least its specified minimum investment.**

```sql
SELECT school_id, minimum_investment FROM school_minimum_investments;
```

**Results (CSV format):**
```csv
school_id,minimum_investment
1,60000.0
2,70000.0
3,50000.0
```

**Query 3: This is the upper bound in the total budget constraint, ensuring that the sum of investments does not exceed the available budget.**

```sql
SELECT total_budget FROM budget LIMIT 1;
```

**Results (CSV format):**
```csv
total_budget
1000000.0
```

**Query 4: This data can be used to understand the current allocation and to ensure that the minimum investment constraints are being met.**

```sql
SELECT school_id, Invested FROM budget;
```

**Results (CSV format):**
```csv
school_id,Invested
1,120000.0
2,180000.0
3,80000.0
```

**Query 5: This helps in understanding the baseline funding needed before any additional optimization can be performed.**

```sql
SELECT SUM(minimum_investment) AS total_minimum_investment FROM school_minimum_investments;
```

**Results (CSV format):**
```csv
total_minimum_investment
180000.0
```

**Query 6: This helps in understanding how much of the budget has already been allocated.**

```sql
SELECT SUM(Invested) AS total_current_investment FROM budget;
```

**Results (CSV format):**
```csv
total_current_investment
380000.0
```

**Query 7: This is useful for determining how much additional budget is available for optimization.**

```sql
SELECT (total_budget - (SELECT SUM(minimum_investment) FROM school_minimum_investments)) AS remaining_budget FROM budget LIMIT 1;
```

**Results (CSV format):**
```csv
remaining_budget
820000.0
```

**Query 8: This is useful for understanding both the benefit and the constraints associated with each school in a single view.**

```sql
SELECT sw.school_id, sw.weight, smi.minimum_investment FROM school_weights sw JOIN school_minimum_investments smi ON sw.school_id = smi.school_id;
```

**Results (CSV format):**
```csv
school_id,weight,minimum_investment
1,0.6,60000.0
2,0.8,70000.0
3,0.5,50000.0
```

**Query 9: This provides a comprehensive view of the current state and the optimization parameters for each school.**

```sql
SELECT sw.school_id, sw.weight, smi.minimum_investment, b.Invested FROM school_weights sw JOIN school_minimum_investments smi ON sw.school_id = smi.school_id JOIN budget b ON sw.school_id = b.school_id;
```

**Results (CSV format):**
```csv
school_id,weight,minimum_investment,Invested
1,0.6,60000.0,120000.0
2,0.8,70000.0,180000.0
3,0.5,50000.0,80000.0
```

**Query 10: This is the objective function value that needs to be maximized.**

```sql
SELECT SUM(sw.weight * b.Invested) AS total_weighted_investment FROM school_weights sw JOIN budget b ON sw.school_id = b.school_id;
```

**Results (CSV format):**
```csv
total_weighted_investment
256000.0
```

