# 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.  

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical university course and teacher capacities, ensuring realistic teaching loads and quality grades.

-- Realistic data for teacher_course_assignment
INSERT INTO teacher_course_assignment (assignment_status) VALUES (True);
INSERT INTO teacher_course_assignment (assignment_status) VALUES (False);
INSERT INTO teacher_course_assignment (assignment_status) VALUES (True);

-- Realistic data for teacher_max_courses
INSERT INTO teacher_max_courses (max_courses) VALUES (4);
INSERT INTO teacher_max_courses (max_courses) VALUES (3);
INSERT INTO teacher_max_courses (max_courses) VALUES (5);

-- Realistic data for course_arrange
INSERT INTO course_arrange (Grade) VALUES (4.5);
INSERT INTO course_arrange (Grade) VALUES (3.7);
INSERT INTO course_arrange (Grade) VALUES (4.0);
INSERT INTO course_arrange (Grade) VALUES (3.5);
INSERT INTO course_arrange (Grade) VALUES (4.2);
```