# Complete Optimization Problem and Solution: swimming

## 1. Problem Context and Goals

### Context  
The swimming federation is tasked with assigning swimmers to events in a way that maximizes the total performance score across all events. Each swimmer has a performance score for specific events, which is calculated based on their recorded time and the difficulty of the event. The federation has set a minimum performance score threshold of 80, ensuring that only swimmers who meet or exceed this score are considered for event assignments. This threshold aligns with the federation's goal of maintaining high standards and maximizing overall performance.

The assignment process must also respect the capacity constraints of the stadiums hosting the events. Each stadium has a maximum number of swimmers it can accommodate for a given event, and the federation must ensure that the number of swimmers assigned to an event does not exceed this capacity. Additionally, each swimmer can be assigned to at most one event, ensuring fair participation across the competition.

The performance score for each swimmer in an event is calculated using a formula that multiplies the swimmer's recorded time by a difficulty factor specific to the event. This formula ensures that the performance score reflects both the swimmer's speed and the challenge posed by the event. The federation uses these scores to make informed decisions about event assignments, aiming to optimize the overall performance of the team.

### Goals  
The primary goal of the optimization problem is to maximize the total performance score across all events by assigning swimmers to events in the most effective way. This involves selecting the best possible combination of swimmers for each event, considering their individual performance scores and the constraints imposed by stadium capacities and swimmer participation limits. Success is measured by the total performance score achieved, which directly reflects the quality of the assignments made. The federation aims to ensure that every assignment contributes positively to the overall performance, while adhering to the operational constraints.

## 2. Constraints    

The optimization problem must adhere to the following constraints:

1. **Swimmer Assignment Limit**: Each swimmer can be assigned to at most one event. This ensures that no swimmer is overburdened and that participation is fair across the competition.  
2. **Event Capacity Constraint**: The number of swimmers assigned to each event must not exceed the capacity of the stadium hosting the event. This ensures that the physical limitations of the venues are respected and that the events can be conducted smoothly.  

These constraints are designed to ensure that the assignments are both feasible and fair, while still allowing the federation to maximize the total performance score.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating tables for performance scores and event-stadium mappings, modifying the stadium table to better represent capacity constraints, and adding business configuration logic for scalar parameters and formulas.

CREATE TABLE swimmer_performance (
  swimmer_id INTEGER,
  event_id INTEGER,
  score FLOAT,
  assigned BOOLEAN
);

CREATE TABLE event_stadium (
  event_id INTEGER,
  stadium_id INTEGER
);

CREATE TABLE stadium (
  stadium_id INTEGER,
  capacity INTEGER,
  event_id INTEGER
);
```

### Data Dictionary  
- **swimmer_performance**:  
  - **swimmer_id**: Unique identifier for a swimmer. Links the swimmer to their performance score in specific events.  
  - **event_id**: Unique identifier for an event. Links the event to the swimmer's performance score.  
  - **score**: Performance score of the swimmer in the event. Used in the objective function to determine the total performance score.  
  - **assigned**: Indicates whether the swimmer is assigned to the event. Represents the decision variable in the optimization problem.  

- **event_stadium**:  
  - **event_id**: Unique identifier for an event. Links the event to the stadium where it will be held.  
  - **stadium_id**: Unique identifier for a stadium. Links the stadium to the event it will host.  

- **stadium**:  
  - **stadium_id**: Unique identifier for a stadium. Links the stadium to its capacity for specific events.  
  - **capacity**: Maximum number of swimmers allowed in the stadium for an event. Used in the capacity constraints.  
  - **event_id**: Unique identifier for an event. Links the stadium to the specific event it will host.  


### Retrieved Values

**Query 1: This data is crucial for the objective function as it provides the performance scores that need to be maximized.**

```sql
SELECT swimmer_id, event_id, score FROM swimmer_performance WHERE score >= 80;
```

**Results (CSV format):**
```csv
swimmer_id,event_id,score
1,1,85.5
3,1,90.0
```

**Query 2: This data is essential for the capacity constraint to ensure the number of swimmers assigned to an event does not exceed the stadium's capacity.**

```sql
SELECT s.stadium_id, s.event_id, s.capacity FROM stadium s;
```

**Results (CSV format):**
```csv
stadium_id,event_id,capacity
1,1,100
2,2,150
3,3,200
```

**Query 3: This data is important to understand which events are held in which stadiums, which is necessary for applying the capacity constraints.**

```sql
SELECT es.event_id, es.stadium_id FROM event_stadium es;
```

**Results (CSV format):**
```csv
event_id,stadium_id
1,1
2,2
3,3
```

**Query 4: This data helps in monitoring the current assignments and ensuring that the capacity constraints are not violated.**

```sql
SELECT event_id, COUNT(swimmer_id) AS assigned_swimmers FROM swimmer_performance WHERE assigned = TRUE GROUP BY event_id;
```

**Results (CSV format):**
```csv
event_id,assigned_swimmers
```

**Query 5: This data is important for identifying available swimmers who can be assigned to events to maximize the total performance score.**

```sql
SELECT swimmer_id FROM swimmer_performance WHERE assigned = FALSE GROUP BY swimmer_id;
```

**Results (CSV format):**
```csv
swimmer_id
1
2
3
```

**Query 6: This data can be useful for identifying the top-performing swimmers in each event, which can help in making optimal assignments.**

```sql
SELECT event_id, MAX(score) AS max_score FROM swimmer_performance GROUP BY event_id;
```

**Results (CSV format):**
```csv
event_id,max_score
1,90.0
```

**Query 7: This data helps in evaluating the current total performance score and identifying areas for improvement.**

```sql
SELECT event_id, SUM(score) AS total_score FROM swimmer_performance WHERE assigned = TRUE GROUP BY event_id;
```

**Results (CSV format):**
```csv
event_id,total_score
```

**Query 8: This data is important for identifying events that can still accommodate more swimmers, which is crucial for making new assignments.**

```sql
SELECT event_id, COUNT(swimmer_id) AS assigned_swimmers FROM swimmer_performance WHERE assigned = TRUE GROUP BY event_id ) a ON s.event_id = a.event_id WHERE s.capacity - COALESCE(a.assigned_swimmers, 0) > 0;
```

**Error:** Execution failed on sql 'SELECT event_id, COUNT(swimmer_id) AS assigned_swimmers FROM swimmer_performance WHERE assigned = TRUE GROUP BY event_id ) a ON s.event_id = a.event_id WHERE s.capacity - COALESCE(a.assigned_swimmers, 0) > 0;': near ")": syntax error

**Query 9: This data is crucial for making new assignments to maximize the total performance score while respecting the capacity constraints.**

```sql
SELECT event_id, COUNT(swimmer_id) AS assigned_swimmers FROM swimmer_performance WHERE assigned = TRUE GROUP BY event_id ) a ON s.event_id = a.event_id WHERE s.capacity - COALESCE(a.assigned_swimmers, 0) > 0 ) available_events ON sp.event_id = available_events.event_id WHERE sp.assigned = FALSE AND sp.score >= 80;
```

**Error:** Execution failed on sql 'SELECT event_id, COUNT(swimmer_id) AS assigned_swimmers FROM swimmer_performance WHERE assigned = TRUE GROUP BY event_id ) a ON s.event_id = a.event_id WHERE s.capacity - COALESCE(a.assigned_swimmers, 0) > 0 ) available_events ON sp.event_id = available_events.event_id WHERE sp.assigned = FALSE AND sp.score >= 80;': near ")": syntax error

