# Complete Optimization Problem and Solution: school_finance

## 1. Problem Context and Goals

### Context  
The school district is focused on optimizing its budget allocation across various schools to ensure the most effective use of resources. The primary decision involves determining the amount to be invested in each school, denoted as Invested_i, which is a continuous variable. The operational goal is to maximize the total amount invested across all schools. This is achieved by ensuring that each school receives at least a minimum percentage of its budgeted amount, as specified in the business configuration. The business configuration includes a scalar parameter that dictates the minimum percentage of the budget that must be invested in each school, serving as a constraint in the optimization model. The decision-making process is linear, focusing on straightforward allocation without involving complex interactions such as variable products or divisions. The operational parameters are clearly defined, aligning with the expected coefficient sources, and the business configuration parameters are referenced appropriately to maintain consistency and clarity.

### Goals  
The optimization goal is to maximize the total amount invested across all schools. The metric for optimization is the total invested amount, which is the sum of investments in each school. Success is measured by the alignment of investments with the expected coefficient sources, ensuring that the total invested amount is maximized. The goal is articulated in natural language, focusing on the linear optimization objective without resorting to mathematical formulas or symbolic notation.

## 2. Constraints    

The constraints are designed to ensure that the investment decisions adhere to the budgetary limits and minimum investment requirements. Each school must receive an investment amount that does not exceed its budgeted amount. Additionally, each school must receive at least a specified minimum percentage of its budgeted amount. These constraints are expressed in business terms that naturally lead to linear mathematical forms, avoiding any complex interactions such as variable products or divisions. The constraints are as follows:
- The investment in each school should not exceed the budgeted amount for that school.
- The investment in each school should be at least the minimum percentage of the budgeted amount, as specified in the business configuration.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema adjustments include adding missing configuration logic for minimum investment percentage and ensuring all optimization requirements are mapped correctly. Business configuration logic is updated to include scalar parameters and formulas.

CREATE TABLE budget (
  Invested FLOAT,
  Budgeted FLOAT,
  min_investment_percent FLOAT
);
```

### Data Dictionary  
The data dictionary provides a comprehensive mapping of tables and columns to their business purposes and optimization roles. The "budget" table stores essential data for each school, including the amount invested, the total budgeted amount, and the minimum investment percentage. Each column serves a specific role in the optimization process:
- **Invested**: Represents the amount invested in each school. It is a decision variable in the optimization model, indicating the investment amount.
- **Budgeted**: Indicates the total budgeted amount for each school. It serves as a constraint bound, ensuring that investments do not exceed this amount.
- **min_investment_percent**: Specifies the minimum percentage of the budget that must be invested in each school. It acts as a constraint bound, ensuring that investments meet the minimum requirement.

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical budget allocations for schools, ensuring that the minimum investment percentages are realistic and allow for a meaningful optimization problem.

-- Realistic data for budget
INSERT INTO budget (Invested, Budgeted, min_investment_percent) VALUES (4500.0, 5000.0, 0.1);
INSERT INTO budget (Invested, Budgeted, min_investment_percent) VALUES (5400.0, 6000.0, 0.15);
INSERT INTO budget (Invested, Budgeted, min_investment_percent) VALUES (6300.0, 7000.0, 0.2);
```