# Complete Optimization Problem and Solution: student_1

## 1. Problem Context and Goals

### Context  
A school district is tasked with assigning students to classrooms in a way that balances the workload among teachers while ensuring that no classroom exceeds its capacity. The primary decision involves determining whether a student is assigned to a specific classroom, represented as a binary choice. The operational goal is to minimize the maximum number of students in any classroom, ensuring that the distribution of students is as even as possible across all classrooms.  

The business configuration includes a predefined maximum number of students allowed in any classroom, which is used to guide the optimization process. Additionally, a business logic formula calculates the maximum number of students in any classroom by summing the assignments for each classroom and identifying the highest value. This calculation directly supports the optimization objective of minimizing the maximum number of students in any classroom.  

The problem is designed to ensure that each student is assigned to exactly one classroom and that the total number of students in each classroom does not exceed its capacity. These requirements are expressed as linear relationships, avoiding any nonlinear scenarios such as variable products or divisions.  

### Goals  
The optimization goal is to minimize the maximum number of students in any classroom. Success is measured by achieving the smallest possible value for this maximum, ensuring that classroom assignments are balanced and within capacity limits. This goal aligns with the operational parameters and business configuration, which provide the necessary data and constraints to guide the optimization process.  

## 2. Constraints  

The problem is subject to the following constraints:  
1. **Student Assignment Constraint**: Each student must be assigned to exactly one classroom. This ensures that all students are placed and no student is left unassigned.  
2. **Classroom Capacity Constraint**: The total number of students assigned to each classroom must not exceed its predefined capacity. This ensures that classroom sizes remain within acceptable limits.  
3. **Maximum Students Constraint**: The total number of students assigned to any classroom must be less than or equal to the maximum number of students allowed in any classroom. This directly supports the optimization objective of minimizing the maximum number of students in any classroom.  

These constraints are expressed in linear terms, ensuring that the optimization problem remains straightforward and avoids any nonlinear complexities.  

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 2 Database Schema
-- Objective: Added a new table for maximum students per classroom and updated business configuration logic to include a formula for calculating the maximum number of students in any classroom.

CREATE TABLE classroom_capacities (
  classroom_id INTEGER,
  capacity INTEGER
);

CREATE TABLE student_assignments (
  student_id INTEGER,
  classroom_id INTEGER,
  assignment BOOLEAN
);

CREATE TABLE max_students_per_classroom (
  classroom_id INTEGER,
  max_students INTEGER
);
```

### Data Dictionary  
- **classroom_capacities**:  
  - **Purpose**: Stores the maximum number of students allowed in each classroom.  
  - **Columns**:  
    - `classroom_id`: Unique identifier for each classroom.  
    - `capacity`: The maximum number of students allowed in the classroom.  

- **student_assignments**:  
  - **Purpose**: Tracks the assignment of students to classrooms.  
  - **Columns**:  
    - `student_id`: Unique identifier for each student.  
    - `classroom_id`: Unique identifier for each classroom.  
    - `assignment`: Indicates whether the student is assigned to the classroom (True or False).  

- **max_students_per_classroom**:  
  - **Purpose**: Represents the maximum number of students in any classroom.  
  - **Columns**:  
    - `classroom_id`: Unique identifier for each classroom.  
    - `max_students`: The maximum number of students in the classroom.  


### Retrieved Values

**Query 1: This is important for understanding the current distribution of students across classrooms and identifying which classrooms are over or under capacity.**

```sql
SELECT sa.classroom_id, COUNT(sa.student_id) AS current_students FROM student_assignments sa WHERE sa.assignment = TRUE GROUP BY sa.classroom_id;
```

**Results (CSV format):**
```csv
classroom_id,current_students
1,1
2,1
3,1
```

**Query 2: This is crucial for ensuring that the number of students assigned to each classroom does not exceed its capacity.**

```sql
SELECT cc.classroom_id, cc.capacity FROM classroom_capacities cc;
```

**Results (CSV format):**
```csv
classroom_id,capacity
1,25
2,30
3,35
```

**Query 3: This is essential for the optimization objective of minimizing the maximum number of students in any classroom.**

```sql
SELECT mspc.classroom_id, mspc.max_students FROM max_students_per_classroom mspc;
```

**Results (CSV format):**
```csv
classroom_id,max_students
1,25
2,30
3,35
```

**Query 4: This helps in identifying students who need to be assigned, ensuring that the student assignment constraint is met.**

```sql
SELECT sa.student_id FROM student_assignments sa WHERE sa.assignment = FALSE;
```

**Results (CSV format):**
```csv
student_id
104
105
```

**Query 5: This provides a comprehensive view of how many students are in each classroom relative to its capacity, which is useful for balancing the workload.**

```sql
SELECT sa.classroom_id, COUNT(sa.student_id) AS current_students, cc.capacity FROM student_assignments sa JOIN classroom_capacities cc ON sa.classroom_id = cc.classroom_id WHERE sa.assignment = TRUE GROUP BY sa.classroom_id, cc.capacity;
```

**Results (CSV format):**
```csv
classroom_id,current_students,capacity
1,1,25
2,1,30
3,1,35
```

**Query 6: This is directly related to the optimization goal of minimizing the maximum number of students in any classroom.**

```sql
SELECT sa.classroom_id, COUNT(sa.student_id) AS current_students FROM student_assignments sa WHERE sa.assignment = TRUE GROUP BY sa.classroom_id ) AS classroom_counts;
```

**Error:** Execution failed on sql 'SELECT sa.classroom_id, COUNT(sa.student_id) AS current_students FROM student_assignments sa WHERE sa.assignment = TRUE GROUP BY sa.classroom_id ) AS classroom_counts;': near ")": syntax error

**Query 7: This is important for identifying classrooms that need to have students reassigned to meet the capacity constraint.**

```sql
SELECT sa.classroom_id, COUNT(sa.student_id) AS current_students, cc.capacity FROM student_assignments sa JOIN classroom_capacities cc ON sa.classroom_id = cc.classroom_id WHERE sa.assignment = TRUE GROUP BY sa.classroom_id, cc.capacity HAVING COUNT(sa.student_id) > cc.capacity;
```

**Results (CSV format):**
```csv
classroom_id,current_students,capacity
```

**Query 8: This helps in identifying classrooms that can accommodate more students, aiding in the redistribution process.**

```sql
SELECT sa.classroom_id, COUNT(sa.student_id) AS current_students, cc.capacity FROM student_assignments sa JOIN classroom_capacities cc ON sa.classroom_id = cc.classroom_id WHERE sa.assignment = TRUE GROUP BY sa.classroom_id, cc.capacity HAVING COUNT(sa.student_id) < cc.capacity;
```

**Results (CSV format):**
```csv
classroom_id,current_students,capacity
1,1,25
2,1,30
3,1,35
```

