# 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.  

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on realistic school district budgets, typical minimum investment requirements, and weights reflecting varying educational impact per dollar invested across schools.

-- Realistic data for school_weights
INSERT INTO school_weights (school_id, weight) VALUES (1, 0.6);
INSERT INTO school_weights (school_id, weight) VALUES (2, 0.8);
INSERT INTO school_weights (school_id, weight) VALUES (3, 0.5);

-- Realistic data for school_minimum_investments
INSERT INTO school_minimum_investments (school_id, minimum_investment) VALUES (1, 60000);
INSERT INTO school_minimum_investments (school_id, minimum_investment) VALUES (2, 70000);
INSERT INTO school_minimum_investments (school_id, minimum_investment) VALUES (3, 50000);

-- Realistic data for budget
INSERT INTO budget (school_id, Invested, total_budget) VALUES (1, 120000, 1000000);
INSERT INTO budget (school_id, Invested, total_budget) VALUES (2, 180000, 1000000);
INSERT INTO budget (school_id, Invested, total_budget) VALUES (3, 80000, 1000000);
```