# Complete Optimization Problem and Solution: club_1

## 1. Problem Context and Goals

### Context  
The problem revolves around optimizing the allocation of students to clubs to maximize overall student engagement. Each student can be assigned to at most one club, and each club has a maximum capacity that cannot be exceeded. The engagement score for a student in a club is calculated as a weighted combination of their participation level and the importance of the club. This score is used to measure the effectiveness of the assignment. The default maximum number of students allowed in a club is set to 20, ensuring a realistic starting point for capacity constraints. The engagement score formula, which combines participation and club importance, is used to compute the coefficients for the optimization objective. The decision variables are binary, indicating whether a student is assigned to a specific club. The goal is to make these assignments in a way that maximizes the total engagement score across all students and clubs, while respecting the capacity limits of each club and ensuring each student is assigned to no more than one club.

### Goals  
The primary goal of this optimization problem is to maximize the total engagement score of students in their assigned clubs. This is achieved by assigning students to clubs in a way that leverages their individual engagement scores, which are derived from their participation levels and the importance of the clubs. Success is measured by the sum of these engagement scores, ensuring that the assignments are both effective and efficient. The optimization process ensures that the total engagement score is as high as possible, while adhering to the constraints of club capacities and student assignment limits.

## 2. Constraints    

The optimization problem is subject to two key constraints:  
1. **Club Capacity Constraint**: The total number of students assigned to any club must not exceed the club's maximum capacity. This ensures that clubs do not become overcrowded and can operate effectively within their defined limits.  
2. **Student Assignment Constraint**: Each student can be assigned to at most one club. This ensures that students are not overcommitted and can fully engage with their chosen club.  

These constraints are designed to reflect real-world operational limitations and ensure that the optimization solution is both practical and feasible.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating tables for engagement scores and club capacities, and updating configuration logic for scalar parameters and formulas.

CREATE TABLE Engagement_Scores (
  StuID INTEGER,
  ClubID INTEGER,
  score FLOAT
);

CREATE TABLE Club_Capacities (
  ClubID INTEGER,
  capacity INTEGER
);

CREATE TABLE Member_of_club (
  StuID INTEGER,
  ClubID INTEGER
);
```

### Data Dictionary  
- **Engagement_Scores**:  
  - **Purpose**: Stores the engagement scores for each student-club pair.  
  - **Columns**:  
    - **StuID**: Identifies the student.  
    - **ClubID**: Identifies the club.  
    - **score**: Represents the engagement score of the student in the club, used as a coefficient in the objective function.  

- **Club_Capacities**:  
  - **Purpose**: Stores the maximum number of students allowed in each club.  
  - **Columns**:  
    - **ClubID**: Identifies the club.  
    - **capacity**: Represents the maximum number of students allowed in the club, used as a bound in the capacity constraint.  

- **Member_of_club**:  
  - **Purpose**: Stores which students are members of which clubs.  
  - **Columns**:  
    - **StuID**: Identifies the student.  
    - **ClubID**: Identifies the club.  


### Retrieved Values

**Query 1: This data is crucial for the objective function, as it represents the coefficients to be maximized.**

```sql
SELECT StuID, ClubID, score FROM Engagement_Scores;
```

**Results (CSV format):**
```csv
StuID,ClubID,score
1,101,0.6
2,102,0.8
3,103,0.4
```

**Query 2: This data is essential for the club capacity constraint, ensuring that the number of students assigned to a club does not exceed its capacity.**

```sql
SELECT ClubID, capacity FROM Club_Capacities;
```

**Results (CSV format):**
```csv
ClubID,capacity
101,20
102,25
103,15
```

**Query 3: This data helps in understanding the current state of assignments and can be used to ensure that the student assignment constraint is respected.**

```sql
SELECT StuID, ClubID FROM Member_of_club;
```

**Results (CSV format):**
```csv
StuID,ClubID
1,101
2,102
3,103
```

**Query 4: This aggregated data is useful for checking the current load on each club against its capacity.**

```sql
SELECT ClubID, COUNT(StuID) AS current_members FROM Member_of_club GROUP BY ClubID;
```

**Results (CSV format):**
```csv
ClubID,current_members
101,1
102,1
103,1
```

**Query 5: This joined data is useful for understanding the relationship between engagement scores and club capacities, which can inform the optimization process.**

```sql
SELECT es.StuID, es.ClubID, es.score, cc.capacity FROM Engagement_Scores es JOIN Club_Capacities cc ON es.ClubID = cc.ClubID;
```

**Results (CSV format):**
```csv
StuID,ClubID,score,capacity
1,101,0.6,20
2,102,0.8,25
3,103,0.4,15
```

**Query 6: This data is important for identifying potential new assignments that could increase the total engagement score.**

```sql
SELECT es.StuID, es.ClubID, es.score FROM Engagement_Scores es LEFT JOIN Member_of_club moc ON es.StuID = moc.StuID AND es.ClubID = moc.ClubID WHERE moc.StuID IS NULL;
```

**Results (CSV format):**
```csv
StuID,ClubID,score
```

**Query 7: This data can be used to identify the best possible club assignment for each student, which can be a starting point for the optimization.**

```sql
SELECT StuID, MAX(score) AS max_score FROM Engagement_Scores GROUP BY StuID;
```

**Results (CSV format):**
```csv
StuID,max_score
1,0.6
2,0.8
3,0.4
```

**Query 8: This data is useful for identifying clubs that can still accept more students, which is important for the capacity constraint.**

```sql
SELECT cc.ClubID, cc.capacity, COUNT(moc.StuID) AS current_members FROM Club_Capacities cc LEFT JOIN Member_of_club moc ON cc.ClubID = moc.ClubID GROUP BY cc.ClubID, cc.capacity HAVING COUNT(moc.StuID) < cc.capacity;
```

**Results (CSV format):**
```csv
ClubID,capacity,current_members
101,20,1
102,25,1
103,15,1
```

**Query 9: This data is important for identifying potential reassignments to alleviate overcapacity issues.**

```sql
SELECT capacity FROM Club_Capacities WHERE Club_Capacities.ClubID = Member_of_club.ClubID ) ) over_capacity_clubs ON es.ClubID = over_capacity_clubs.ClubID;
```

**Error:** Execution failed on sql 'SELECT capacity FROM Club_Capacities WHERE Club_Capacities.ClubID = Member_of_club.ClubID ) ) over_capacity_clubs ON es.ClubID = over_capacity_clubs.ClubID;': near ")": syntax error

