# Complete Optimization Problem and Solution: soccer_2

## 1. Problem Context and Goals

### Context  
The soccer league is organizing tryouts to select players from various colleges for different positions on a team. The primary decision involves determining whether each player should be selected, represented by a binary decision variable for each player. The objective is to minimize the total number of yellow cards among the selected players. This selection process must ensure that each position on the team is filled with the required number of players. Additionally, to promote diversity, there is a limit on the number of players that can be selected from any single college. The maximum number of players allowed from a single college is a critical parameter in this process. The number of players required for each position is specified to ensure that all positions are adequately filled. These requirements are expressed in a way that naturally aligns with linear optimization, focusing on precise decision-making without involving complex mathematical operations like products or divisions.

### Goals  
The goal of this optimization problem is to minimize the total number of yellow cards among the selected players. The metric used to achieve this goal is the sum of yellow cards for all selected players. Success in this context is measured by achieving the lowest possible total of yellow cards while adhering to the constraints of filling each position and maintaining college diversity. The optimization goal is clearly defined in linear terms, focusing on minimizing the sum of yellow cards associated with the selected players.

## 2. Constraints    

The constraints for this optimization problem are designed to ensure that the team composition meets specific requirements:

- For each position on the team, the number of selected players must match the required number of players for that position. This ensures that all positions are adequately filled.
- To maintain diversity, the number of players selected from any single college must not exceed the maximum allowed. This constraint ensures that no single college is over-represented in the team selection.

These constraints are expressed in business terms that naturally lead to linear mathematical forms, avoiding complex operations like variable products or divisions.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 2 Database Schema
-- Objective: Schema adjustments were made to incorporate missing yellow card data and improve optimization mapping. Configuration logic was updated to include scalar parameters and formulas.

CREATE TABLE Tryout (
  pID INTEGER,
  pPos STRING,
  cName STRING,
  yCard INTEGER
);

CREATE TABLE PositionRequirements (
  position STRING,
  required_players INTEGER
);

CREATE TABLE CollegeLimits (
  college STRING,
  max_players INTEGER
);

CREATE TABLE PlayerYellowCards (
  pID INTEGER,
  yCard INTEGER
);
```

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

- **Tryout Table**: This table stores data about players participating in the tryouts. Each player is identified by a unique ID, and the table includes information about the position they are trying out for, the college they represent, and the number of yellow cards they have received. This table plays a crucial role in defining the decision variables for the optimization problem.

- **PositionRequirements Table**: This table defines the number of players required for each position on the team. It ensures that the constraints related to filling each position are met.

- **CollegeLimits Table**: This table specifies the maximum number of players that can be selected from each college. It is used to enforce diversity in player selection by limiting the representation from any single college.

- **PlayerYellowCards Table**: This table records the number of yellow cards for each player, linking this data to the players' unique IDs. It is used in the objective function to minimize the total number of yellow cards among selected players.

### Current Stored Values  
```sql
-- Iteration 2 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical soccer team compositions and college diversity requirements, ensuring a balance between player skill (yellow cards) and team needs (positions and college representation).

-- Realistic data for Tryout
INSERT INTO Tryout (pID, pPos, cName, yCard) VALUES (1, 'Goalkeeper', 'College A', 0);
INSERT INTO Tryout (pID, pPos, cName, yCard) VALUES (2, 'Defender', 'College B', 1);
INSERT INTO Tryout (pID, pPos, cName, yCard) VALUES (3, 'Midfielder', 'College C', 2);
INSERT INTO Tryout (pID, pPos, cName, yCard) VALUES (4, 'Forward', 'College A', 1);
INSERT INTO Tryout (pID, pPos, cName, yCard) VALUES (5, 'Defender', 'College B', 0);

-- Realistic data for PositionRequirements
INSERT INTO PositionRequirements (position, required_players) VALUES ('Goalkeeper', 1);
INSERT INTO PositionRequirements (position, required_players) VALUES ('Defender', 4);
INSERT INTO PositionRequirements (position, required_players) VALUES ('Midfielder', 4);
INSERT INTO PositionRequirements (position, required_players) VALUES ('Forward', 2);

-- Realistic data for CollegeLimits
INSERT INTO CollegeLimits (college, max_players) VALUES ('College A', 3);
INSERT INTO CollegeLimits (college, max_players) VALUES ('College B', 3);
INSERT INTO CollegeLimits (college, max_players) VALUES ('College C', 3);

-- Realistic data for PlayerYellowCards
INSERT INTO PlayerYellowCards (pID, yCard) VALUES (1, 0);
INSERT INTO PlayerYellowCards (pID, yCard) VALUES (2, 1);
INSERT INTO PlayerYellowCards (pID, yCard) VALUES (3, 2);
```