# Complete Optimization Problem and Solution: gymnast

## 1. Problem Context and Goals

### Context  
In a gymnastics competition, the goal is to select a team of gymnasts that maximizes the total points scored across all events. Each gymnast has a specific performance score for each event, which represents their potential contribution to the team’s total score. The decision-making process involves determining which gymnasts should participate in which events, based on their performance scores.  

The operational parameters include:  
- **Performance Scores**: Each gymnast has a score for the floor exercise, pommel horse, rings, vault, parallel bars, and horizontal bar. These scores are used to calculate the total points the team can achieve.  
- **Event Participation**: Each gymnast can participate in a maximum of 3 events, as defined by the business configuration. This ensures that gymnasts are not overburdened and can perform at their best.  
- **Team Size**: The total number of gymnasts on the team cannot exceed 10, as specified in the business configuration. This limit ensures the team is manageable and diverse in skill sets.  

The decision variables are binary choices indicating whether a gymnast participates in a specific event. The objective is to maximize the total points scored by the team across all events, while adhering to the constraints on event participation and team size.  

### Goals  
The primary goal of this optimization problem is to maximize the total points scored by the gymnastics team across all events. This is achieved by strategically selecting which gymnasts participate in which events, based on their performance scores. Success is measured by the total points accumulated, which is the sum of the points contributed by each gymnast in the events they participate in.  

The optimization process ensures that the team’s total score is as high as possible, while respecting the operational constraints of event participation limits and team size.  

## 2. Constraints  

The optimization problem is subject to the following constraints:  
1. **Event Participation Limit**: Each gymnast can participate in a maximum of 3 events. This ensures that no gymnast is overcommitted and can perform optimally in the events they are assigned.  
2. **Team Size Limit**: The total number of gymnasts on the team cannot exceed 10. This ensures the team remains manageable and diverse in skill sets.  

These constraints are designed to align with the operational realities of the competition and ensure the optimization solution is both practical and effective.  

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating tables for decision variables and constraint bounds, and updating business configuration logic to handle scalar parameters and formulas.

CREATE TABLE gymnast (
  Floor_Exercise_Points FLOAT,
  Pommel_Horse_Points FLOAT,
  Rings_Points FLOAT,
  Vault_Points FLOAT,
  Parallel_Bars_Points FLOAT,
  Horizontal_Bar_Points FLOAT
);

CREATE TABLE gymnast_event_participation (
  floor_exercise BOOLEAN,
  pommel_horse BOOLEAN,
  rings BOOLEAN,
  vault BOOLEAN,
  parallel_bars BOOLEAN,
  horizontal_bar BOOLEAN
);

CREATE TABLE team_constraints (
  max_events_per_gymnast INTEGER,
  max_gymnasts_on_team INTEGER
);
```

### Data Dictionary  
- **gymnast**: Stores the performance scores of each gymnast across all events. These scores are used as coefficients in the objective function to calculate the total points.  
  - **Floor_Exercise_Points**: Points scored by a gymnast in the floor exercise.  
  - **Pommel_Horse_Points**: Points scored by a gymnast in the pommel horse event.  
  - **Rings_Points**: Points scored by a gymnast in the rings event.  
  - **Vault_Points**: Points scored by a gymnast in the vault event.  
  - **Parallel_Bars_Points**: Points scored by a gymnast in the parallel bars event.  
  - **Horizontal_Bar_Points**: Points scored by a gymnast in the horizontal bar event.  

- **gymnast_event_participation**: Represents the decision variables for event participation. Each column indicates whether a gymnast participates in a specific event.  
  - **floor_exercise**: Whether the gymnast participates in the floor exercise.  
  - **pommel_horse**: Whether the gymnast participates in the pommel horse event.  
  - **rings**: Whether the gymnast participates in the rings event.  
  - **vault**: Whether the gymnast participates in the vault event.  
  - **parallel_bars**: Whether the gymnast participates in the parallel bars event.  
  - **horizontal_bar**: Whether the gymnast participates in the horizontal bar event.  

- **team_constraints**: Stores the operational constraints for the team.  
  - **max_events_per_gymnast**: The maximum number of events a gymnast can participate in.  
  - **max_gymnasts_on_team**: The maximum number of gymnasts allowed on the team.  


### Retrieved Values

**Query 1: This data is crucial for the objective function coefficients, as it represents the potential contribution of each gymnast to the team's total score.**

```sql
SELECT Floor_Exercise_Points, Pommel_Horse_Points, Rings_Points, Vault_Points, Parallel_Bars_Points, Horizontal_Bar_Points FROM gymnast;
```

**Results (CSV format):**
```csv
Floor_Exercise_Points,Pommel_Horse_Points,Rings_Points,Vault_Points,Parallel_Bars_Points,Horizontal_Bar_Points
9.5,8.7,9.0,9.2,8.9,9.1
8.8,9.3,8.7,8.9,9.0,8.6
9.0,8.5,9.2,9.1,8.8,9.3
```

**Query 2: This data represents the decision variables that need to be optimized, indicating whether a gymnast participates in a specific event.**

```sql
SELECT floor_exercise, pommel_horse, rings, vault, parallel_bars, horizontal_bar FROM gymnast_event_participation;
```

**Results (CSV format):**
```csv
floor_exercise,pommel_horse,rings,vault,parallel_bars,horizontal_bar
1,0,1,0,1,0
0,1,0,1,1,0
1,0,1,1,0,1
```

**Query 3: This data is essential for ensuring that the optimization solution adheres to the constraints on event participation limits and team size.**

```sql
SELECT max_events_per_gymnast, max_gymnasts_on_team FROM team_constraints;
```

**Results (CSV format):**
```csv
max_events_per_gymnast,max_gymnasts_on_team
3,10
```

**Query 4: This helps in enforcing the constraint that each gymnast can participate in a maximum of 3 events.**

```sql
SELECT (CASE WHEN floor_exercise THEN 1 ELSE 0 END + CASE WHEN pommel_horse THEN 1 ELSE 0 END + CASE WHEN rings THEN 1 ELSE 0 END + CASE WHEN vault THEN 1 ELSE 0 END + CASE WHEN parallel_bars THEN 1 ELSE 0 END + CASE WHEN horizontal_bar THEN 1 ELSE 0 END) AS total_events_participated FROM gymnast_event_participation;
```

**Results (CSV format):**
```csv
total_events_participated
3
3
4
```

**Query 5: This helps in enforcing the constraint that the team size cannot exceed 10.**

```sql
SELECT COUNT(*) AS total_gymnasts_on_team FROM gymnast_event_participation;
```

**Results (CSV format):**
```csv
total_gymnasts_on_team
3
```

**Query 6: This data can be used to prioritize gymnasts who have the highest potential contribution to the team's total score.**

```sql
SELECT GREATEST(Floor_Exercise_Points, Pommel_Horse_Points, Rings_Points, Vault_Points, Parallel_Bars_Points, Horizontal_Bar_Points) AS max_points FROM gymnast;
```

**Error:** Execution failed on sql 'SELECT GREATEST(Floor_Exercise_Points, Pommel_Horse_Points, Rings_Points, Vault_Points, Parallel_Bars_Points, Horizontal_Bar_Points) AS max_points FROM gymnast;': no such function: GREATEST

**Query 7: This data can be used to identify which gymnasts are the best candidates for each event, helping to maximize the team's total score.**

```sql
SELECT 'Horizontal_Bar' AS event, MAX(Horizontal_Bar_Points) AS max_points FROM gymnast;
```

**Results (CSV format):**
```csv
event,max_points
Horizontal_Bar,9.3
```

**Query 8: This data can be used to assess the overall potential of each gymnast and prioritize their inclusion in the team.**

```sql
SELECT (Floor_Exercise_Points + Pommel_Horse_Points + Rings_Points + Vault_Points + Parallel_Bars_Points + Horizontal_Bar_Points) AS total_potential_points FROM gymnast;
```

**Results (CSV format):**
```csv
total_potential_points
54.4
53.300000000000004
53.89999999999999
```

**Query 9: This data provides an upper bound on the team's total score, which can be useful for benchmarking the optimization solution.**

```sql
SELECT SUM(Floor_Exercise_Points + Pommel_Horse_Points + Rings_Points + Vault_Points + Parallel_Bars_Points + Horizontal_Bar_Points) AS total_team_potential_points FROM gymnast;
```

**Results (CSV format):**
```csv
total_team_potential_points
161.6
```

