# Complete Optimization Problem and Solution: school_player

## 1. Problem Context and Goals

### Context  
The school district is focused on optimizing the allocation of players to various school teams to enhance overall team performance. The decision-making process involves assigning players to teams, where each assignment is represented by a binary decision variable indicating whether a player is assigned to a specific team. The primary objective is to maximize the total performance score of all teams, which is calculated based on the performance contributions of individual players. 

Operational parameters include constraints such as the maximum number of players allowed in each team and the total age limit for players within a team. These constraints ensure that teams are balanced and adhere to school policies. The maximum team size is set to allow flexibility in player assignments while maintaining a manageable team size, and the age limit ensures a mix of younger and older players, balancing experience and potential.

### Goals  
The optimization goal is to maximize the total performance score of the school teams. This score is determined by summing the performance contributions of players assigned to teams. The success of this optimization is measured by the total performance score, which reflects the effectiveness of player assignments in enhancing team performance. The goal is articulated in natural language to emphasize the linear nature of the optimization objective, focusing on maximizing the sum of performance contributions from assigned players.

## 2. Constraints    

The optimization problem is subject to several constraints that ensure feasible and balanced team compositions:

- Each team has a maximum number of players that can be assigned, ensuring that team sizes remain manageable and within school policy limits.
- The total age of players assigned to each team must not exceed a specified limit, promoting a balanced mix of player ages within teams.

These constraints are described in business terms that naturally align with linear mathematical formulations, ensuring that the optimization problem remains linear and solvable.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 2 Database Schema
-- Objective: Schema changes include creating a new table for decision variables, modifying existing tables to include missing data, and updating business configuration logic for scalar parameters and formulas.

CREATE TABLE PlayerPerformance (
  player_id INTEGER,
  performance_coefficient FLOAT,
  age INTEGER
);

CREATE TABLE TeamConstraints (
  team_id INTEGER,
  max_team_size INTEGER,
  max_age_limit INTEGER
);

CREATE TABLE PlayerAssignment (
  player_id INTEGER,
  team_id INTEGER,
  assignment BOOLEAN
);

CREATE TABLE PlayerAge (
  player_id INTEGER,
  age INTEGER
);
```

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

- **PlayerPerformance**: This table captures the performance contributions and ages of players. The performance coefficient represents each player's contribution to the team's performance, which is used in calculating the total performance score.
  
- **TeamConstraints**: This table defines the constraints for each team, including the maximum number of players allowed and the total age limit. These constraints ensure that teams are balanced and adhere to school policies.

- **PlayerAssignment**: This table records the binary decision variables indicating player assignments to teams. Each entry specifies whether a player is assigned to a particular team, forming the basis for the optimization decision-making process.

- **PlayerAge**: This table stores the age information for each player, which is used in age-based constraints to ensure a balanced mix of player ages within teams.

### Current Stored Values  
```sql
-- Iteration 2 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical school team sizes and age distributions, ensuring that constraints are realistic and the optimization problem is solvable.

-- Realistic data for PlayerPerformance
INSERT INTO PlayerPerformance (player_id, performance_coefficient, age) VALUES (1, 1.8, 16);
INSERT INTO PlayerPerformance (player_id, performance_coefficient, age) VALUES (2, 2.3, 17);
INSERT INTO PlayerPerformance (player_id, performance_coefficient, age) VALUES (3, 2.7, 18);

-- Realistic data for TeamConstraints
INSERT INTO TeamConstraints (team_id, max_team_size, max_age_limit) VALUES (1, 15, 100);
INSERT INTO TeamConstraints (team_id, max_team_size, max_age_limit) VALUES (2, 18, 110);
INSERT INTO TeamConstraints (team_id, max_team_size, max_age_limit) VALUES (3, 20, 120);

-- Realistic data for PlayerAssignment
INSERT INTO PlayerAssignment (player_id, team_id, assignment) VALUES (1, 1, True);
INSERT INTO PlayerAssignment (player_id, team_id, assignment) VALUES (2, 2, True);
INSERT INTO PlayerAssignment (player_id, team_id, assignment) VALUES (3, 3, True);

-- Realistic data for PlayerAge
INSERT INTO PlayerAge (player_id, age) VALUES (1, 16);
INSERT INTO PlayerAge (player_id, age) VALUES (2, 17);
INSERT INTO PlayerAge (player_id, age) VALUES (3, 18);
```