# Complete Optimization Problem and Solution: course_teach

## 1. Problem Context and Goals

### Context  
A university is focused on optimizing the assignment of teachers to courses to maximize the overall teaching quality. The teaching quality for each course-teacher pair is measured by a grade, which reflects the effectiveness of the teacher in delivering the course content. The university must ensure that each course is assigned exactly one teacher and that no teacher is assigned more courses than they can handle, as defined by a maximum course limit per teacher.  

The decision-making process involves determining whether a specific teacher is assigned to a specific course, represented as a binary choice. The teaching quality grade for each teacher-course pair serves as the basis for evaluating the effectiveness of the assignment. The maximum number of courses a teacher can handle is a key operational parameter, ensuring that teachers are not overburdened while maintaining teaching quality.  

The business logic calculates the teaching quality for each teacher-course pair by multiplying the grade for that pair by the binary decision variable indicating the assignment. This ensures that the total teaching quality is a linear combination of the grades for all assigned pairs.  

### Goals  
The primary goal of this optimization problem is to maximize the total teaching quality across all course-teacher assignments. This is achieved by assigning teachers to courses in a way that ensures the highest possible sum of teaching quality grades, while respecting the constraints on teacher availability and course requirements. Success is measured by the total teaching quality score, which is directly derived from the grades of the assigned teacher-course pairs.  

## 2. Constraints  

The optimization problem must adhere to the following constraints:  
1. **Course Assignment Constraint**: Each course must be assigned exactly one teacher. This ensures that all courses are covered without overlap.  
2. **Teacher Capacity Constraint**: Each teacher can be assigned to no more than their maximum allowable number of courses. This prevents overloading teachers and maintains a balanced workload.  

These constraints are designed to ensure that the assignments are feasible and align with the operational capabilities of the university.  

## 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 teacher_course_assignment (
  assignment_status BOOLEAN
);

CREATE TABLE teacher_max_courses (
  max_courses INTEGER
);

CREATE TABLE course_arrange (
  Grade FLOAT
);
```

### Data Dictionary  
- **teacher_course_assignment**:  
  - **Business Purpose**: Represents the assignment of teachers to courses.  
  - **Optimization Role**: Decision variables indicating whether a teacher is assigned to a course.  
  - **Columns**:  
    - **assignment_status**: A binary value (true/false) indicating whether a teacher is assigned to a course.  

- **teacher_max_courses**:  
  - **Business Purpose**: Represents the maximum number of courses each teacher can handle.  
  - **Optimization Role**: Constraint bounds limiting the number of courses per teacher.  
  - **Columns**:  
    - **max_courses**: An integer value representing the maximum number of courses a teacher can handle.  

- **course_arrange**:  
  - **Business Purpose**: Represents the arrangement of courses and their associated teaching quality grades.  
  - **Optimization Role**: Objective coefficients used to calculate the total teaching quality.  
  - **Columns**:  
    - **Grade**: A floating-point value representing the teaching quality grade for a teacher-course pair.  


### Retrieved Values

**Query 1: This is crucial for the objective function, as it provides the grades that will be maximized.**

```sql
SELECT tca.assignment_status, ca.Grade FROM teacher_course_assignment tca JOIN course_arrange ca ON tca.assignment_id = ca.assignment_id;
```

**Error:** Execution failed on sql 'SELECT tca.assignment_status, ca.Grade FROM teacher_course_assignment tca JOIN course_arrange ca ON tca.assignment_id = ca.assignment_id;': no such column: tca.assignment_id

**Query 2: This is essential for the teacher capacity constraint, ensuring no teacher is overburdened.**

```sql
SELECT tmc.teacher_id, tmc.max_courses FROM teacher_max_courses tmc;
```

**Error:** Execution failed on sql 'SELECT tmc.teacher_id, tmc.max_courses FROM teacher_max_courses tmc;': no such column: tmc.teacher_id

**Query 3: This helps in enforcing the teacher capacity constraint by comparing with max_courses.**

```sql
SELECT tca.teacher_id, COUNT(tca.assignment_status) AS total_assigned_courses FROM teacher_course_assignment tca WHERE tca.assignment_status = TRUE GROUP BY tca.teacher_id;
```

**Error:** Execution failed on sql 'SELECT tca.teacher_id, COUNT(tca.assignment_status) AS total_assigned_courses FROM teacher_course_assignment tca WHERE tca.assignment_status = TRUE GROUP BY tca.teacher_id;': no such column: tca.teacher_id

**Query 4: This is useful for evaluating the effectiveness of the current assignments.**

```sql
SELECT tca.teacher_id, SUM(ca.Grade) AS total_teaching_quality FROM teacher_course_assignment tca JOIN course_arrange ca ON tca.assignment_id = ca.assignment_id WHERE tca.assignment_status = TRUE GROUP BY tca.teacher_id;
```

**Error:** Execution failed on sql 'SELECT tca.teacher_id, SUM(ca.Grade) AS total_teaching_quality FROM teacher_course_assignment tca JOIN course_arrange ca ON tca.assignment_id = ca.assignment_id WHERE tca.assignment_status = TRUE GROUP BY tca.teacher_id;': no such column: tca.teacher_id

**Query 5: This helps in ensuring that all courses are covered, as per the course assignment constraint.**

```sql
SELECT ca.course_id FROM course_arrange ca LEFT JOIN teacher_course_assignment tca ON ca.assignment_id = tca.assignment_id WHERE tca.assignment_status IS NULL OR tca.assignment_status = FALSE;
```

**Error:** Execution failed on sql 'SELECT ca.course_id FROM course_arrange ca LEFT JOIN teacher_course_assignment tca ON ca.assignment_id = tca.assignment_id WHERE tca.assignment_status IS NULL OR tca.assignment_status = FALSE;': no such column: ca.course_id

**Query 6: This is useful for identifying the best possible assignments to maximize teaching quality.**

```sql
SELECT t.teacher_id, c.course_id, ca.Grade FROM teacher t, course c LEFT JOIN course_arrange ca ON c.course_id = ca.course_id AND t.teacher_id = ca.teacher_id;
```

**Error:** Execution failed on sql 'SELECT t.teacher_id, c.course_id, ca.Grade FROM teacher t, course c LEFT JOIN course_arrange ca ON c.course_id = ca.course_id AND t.teacher_id = ca.teacher_id;': no such table: teacher

**Query 7: This can be used to identify teachers who consistently deliver high teaching quality.**

```sql
SELECT tca.teacher_id, AVG(ca.Grade) AS avg_teaching_quality FROM teacher_course_assignment tca JOIN course_arrange ca ON tca.assignment_id = ca.assignment_id WHERE tca.assignment_status = TRUE GROUP BY tca.teacher_id;
```

**Error:** Execution failed on sql 'SELECT tca.teacher_id, AVG(ca.Grade) AS avg_teaching_quality FROM teacher_course_assignment tca JOIN course_arrange ca ON tca.assignment_id = ca.assignment_id WHERE tca.assignment_status = TRUE GROUP BY tca.teacher_id;': no such column: tca.teacher_id

**Query 8: This helps in monitoring the current workload of each teacher.**

```sql
SELECT tca.teacher_id, COUNT(tca.assignment_status) AS current_assigned_courses FROM teacher_course_assignment tca WHERE tca.assignment_status = TRUE GROUP BY tca.teacher_id;
```

**Error:** Execution failed on sql 'SELECT tca.teacher_id, COUNT(tca.assignment_status) AS current_assigned_courses FROM teacher_course_assignment tca WHERE tca.assignment_status = TRUE GROUP BY tca.teacher_id;': no such column: tca.teacher_id

**Query 9: This helps in prioritizing the assignment of high-quality teachers to unassigned courses.**

```sql
SELECT ca.course_id, ca.Grade FROM course_arrange ca LEFT JOIN teacher_course_assignment tca ON ca.assignment_id = tca.assignment_id WHERE tca.assignment_status IS NULL OR tca.assignment_status = FALSE;
```

**Error:** Execution failed on sql 'SELECT ca.course_id, ca.Grade FROM course_arrange ca LEFT JOIN teacher_course_assignment tca ON ca.assignment_id = tca.assignment_id WHERE tca.assignment_status IS NULL OR tca.assignment_status = FALSE;': no such column: ca.course_id

