# Complete Optimization Problem and Solution: university_basketball

## 1. Problem Context and Goals

### Context  
The university basketball league is focused on optimizing the allocation of resources to its teams to enhance their performance. The primary decision involves determining the amount of resources, such as training hours, coaching staff, and budget, allocated to each team. This decision is represented by the continuous variable `resource_allocation.amount[Team_ID]`. The league's operational goal is to maximize the overall win percentage of the teams, which is directly influenced by the resources allocated. The win percentage for each team is captured in the `basketball_match.All_Games_Percent[Team_ID]`.

The league operates under specific resource constraints. The total resources available for allocation across all teams are limited, ensuring that the league remains within its financial means. Each team must receive a minimum level of resources to participate effectively, while no team should receive an excessive amount that could lead to unfair competition. These constraints are defined by the business configuration parameters: total resources available, minimum resources per team, and maximum resources per team.

### Goals  
The optimization goal is to maximize the overall performance of the basketball teams by strategically allocating resources. The metric for optimization is the sum of the product of each team's win percentage and the resources allocated to them. Success is measured by the increase in the league's overall win percentage, which is directly linked to the resources allocated to each team. The objective is to ensure that resources are distributed in a manner that maximizes this performance metric while adhering to the defined constraints.

## 2. Constraints    

The resource allocation process is governed by several linear constraints:

- The total amount of resources allocated to all teams must not exceed the total resources available. This ensures that the league operates within its budgetary limits.
- Each team must receive at least a minimum amount of resources to ensure they can compete effectively in the league.
- No team should receive more than the maximum allowable resources, preventing any single team from gaining an unfair advantage.

These constraints are designed to ensure a fair and efficient allocation of resources across all teams, promoting balanced competition and optimal performance.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating new tables for resource allocation constraints and decision variables, and updating existing tables to fill mapping gaps. Configuration logic updates include adding scalar parameters for total resources and resource allocation limits.

CREATE TABLE basketball_match (
  All_Games_Percent FLOAT,
  Resource_Allocation FLOAT
);

CREATE TABLE resource_allocation (
  Team_ID INTEGER,
  amount FLOAT
);

CREATE TABLE resource_constraints (
  Team_ID INTEGER,
  Minimum_Allocation FLOAT,
  Maximum_Allocation FLOAT
);
```

### Data Dictionary  
The data dictionary provides a business-oriented mapping of tables and columns to their purposes and roles in the optimization process:

- **basketball_match**: This table captures the performance metrics of each team, specifically their win percentage in all games. The column `All_Games_Percent` serves as a coefficient in the optimization objective, representing the impact of resource allocation on team performance.

- **resource_allocation**: This table records the amount of resources allocated to each team. The `Team_ID` column uniquely identifies each team, while the `amount` column represents the decision variable for resource allocation.

- **resource_constraints**: This table defines the constraints on resource allocation for each team. The `Team_ID` column identifies the team, while `Minimum_Allocation` and `Maximum_Allocation` specify the bounds for resource allocation, ensuring compliance with league rules.

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical resource allocation scenarios in university sports, ensuring that constraints are neither too tight nor too loose, allowing for meaningful optimization.

-- Realistic data for basketball_match
INSERT INTO basketball_match (All_Games_Percent, Resource_Allocation) VALUES (0.75, 110);
INSERT INTO basketball_match (All_Games_Percent, Resource_Allocation) VALUES (0.6, 140);
INSERT INTO basketball_match (All_Games_Percent, Resource_Allocation) VALUES (0.85, 130);

-- Realistic data for resource_allocation
INSERT INTO resource_allocation (Team_ID, amount) VALUES (1, 110);
INSERT INTO resource_allocation (Team_ID, amount) VALUES (2, 140);
INSERT INTO resource_allocation (Team_ID, amount) VALUES (3, 130);

-- Realistic data for resource_constraints
INSERT INTO resource_constraints (Team_ID, Minimum_Allocation, Maximum_Allocation) VALUES (1, 50, 200);
INSERT INTO resource_constraints (Team_ID, Minimum_Allocation, Maximum_Allocation) VALUES (2, 60, 180);
INSERT INTO resource_constraints (Team_ID, Minimum_Allocation, Maximum_Allocation) VALUES (3, 70, 190);
```