# Complete Optimization Problem and Solution: soccer_2

## 1. Problem Context and Goals

### Context  
A college soccer team is preparing for tryouts to select players for the upcoming season. The goal is to maximize the overall skill level of the selected players while adhering to specific constraints on the number of players per position and the total number of players. The decision-making process involves determining whether each player should be selected for tryouts, represented by a binary decision variable. The skill level of each player is a key factor in this decision, as it directly contributes to the overall quality of the team.  

The team must respect the following operational limits:  
- **Total Players Limit**: No more than 20 players can be selected for tryouts, ensuring a manageable team size.  
- **Forwards Limit**: A maximum of 5 forwards can be selected to maintain a balanced offensive lineup.  
- **Midfielders Limit**: Up to 7 midfielders can be chosen to cover various roles in the midfield.  
- **Defenders Limit**: No more than 6 defenders can be selected to ensure a strong defensive setup.  
- **Goalkeepers Limit**: A maximum of 2 goalkeepers can be chosen, which is standard for tryouts.  

These limits are based on typical college soccer team sizes and position distributions, ensuring realistic and practical constraints. The decision to select a player is directly tied to their skill level, and the overall objective is to maximize the sum of the skill levels of the selected players.

### Goals  
The primary goal of this optimization problem is to maximize the total skill level of the players selected for tryouts. This is achieved by selecting players whose combined skill levels are as high as possible, while ensuring that the selection adheres to the constraints on the number of players per position and the total number of players. Success is measured by the overall skill level of the selected team, which directly impacts the team's performance potential.  

## 2. Constraints  

The selection of players for tryouts is subject to the following constraints:  
1. **Total Players Constraint**: The total number of players selected for tryouts cannot exceed 20. This ensures the team size remains manageable and within operational limits.  
2. **Forwards Constraint**: The number of forwards selected must not exceed 5. This maintains a balanced offensive lineup and prevents overstaffing in this position.  
3. **Midfielders Constraint**: The number of midfielders selected must not exceed 7. This ensures sufficient coverage of various midfield roles without overloading the team.  
4. **Defenders Constraint**: The number of defenders selected must not exceed 6. This ensures a strong defensive setup while avoiding redundancy.  
5. **Goalkeepers Constraint**: The number of goalkeepers selected must not exceed 2. This aligns with standard practices for tryouts and ensures adequate coverage for this specialized position.  

These constraints are designed to ensure a balanced and effective team composition while respecting operational and practical limitations.  

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating tables for player skills and position limits, and moving scalar parameters to business configuration logic.

CREATE TABLE player_skills (
  player_id INTEGER,
  skill_level FLOAT,
  select BOOLEAN
);
```

### Data Dictionary  
The **player_skills** table contains the following columns, each with a specific business purpose and optimization role:  
- **player_id**: A unique identifier for each player. This serves as the index for decision variables in the optimization model.  
- **skill_level**: Represents the skill level of the player. This value is used as the coefficient in the objective function, directly contributing to the total skill level of the selected team.  
- **select**: A binary indicator of whether the player is selected for tryouts. This is the decision variable in the optimization model, determining the final team composition.  

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical college soccer team sizes and position distributions, ensuring realistic constraints and skill levels.

-- Realistic data for player_skills
INSERT INTO player_skills (player_id, skill_level, select) VALUES (1, 8.5, False);
INSERT INTO player_skills (player_id, skill_level, select) VALUES (2, 7.2, False);
INSERT INTO player_skills (player_id, skill_level, select) VALUES (3, 9.0, False);
INSERT INTO player_skills (player_id, skill_level, select) VALUES (4, 6.8, False);
INSERT INTO player_skills (player_id, skill_level, select) VALUES (5, 7.9, False);
```