# Complete Optimization Problem and Solution: pets_1

## 1. Problem Context and Goals

### Context  
The university is focused on optimizing the allocation of student advisors to students who own pets. The primary aim is to ensure that each advisor's workload is balanced, considering both the number of students and the total weight of pets assigned to them. The decision-making process involves assigning students to advisors, where the number of students assigned is an integer value, and the total pet weight assigned is a continuous value. The operational goal is to minimize the maximum workload of any advisor, which is determined by the sum of the number of students and the total pet weight assigned to each advisor. The business configuration includes a workload coefficient, set at 1.0, which ensures that both student numbers and pet weights are equally considered in workload calculations. This setup allows for precise operational decision-making that aligns with linear optimization formulations, avoiding any nonlinear relationships such as variable products or divisions. The constraints are based on the maximum number of students and total pet weight each advisor can handle, ensuring resource limitations are respected.

### Goals  
The optimization goal is to minimize the maximum workload of any advisor. This involves reducing the highest sum of students and pet weight assigned to any single advisor. Success in this optimization is measured by achieving a balanced distribution of workloads across all advisors, ensuring no advisor is overburdened. The linear optimization goal is clearly defined by focusing on minimizing the maximum workload, which is a straightforward linear objective.

## 2. Constraints    

The constraints for this optimization problem are designed to ensure that each advisor's workload remains within manageable limits. Specifically, the constraints include:

- Each advisor can handle a maximum number of students, which is defined by the advisor's capacity.
- Each advisor can also handle a maximum total pet weight, ensuring that the pet weight assigned does not exceed this limit.
- Each student must be assigned to exactly one advisor, ensuring that all students are accounted for in the allocation process.

These constraints are expressed in business terms that naturally lead to linear mathematical forms, ensuring that the optimization problem remains linear and manageable.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 2 Database Schema
-- Objective: Schema changes include creating new tables for missing optimization data, modifying existing tables to improve mapping adequacy, and updating business configuration logic for scalar parameters and formulas.

CREATE TABLE Has_Pet (
  StuID INTEGER,
  PetID INTEGER,
  AdvisorID INTEGER
);

CREATE TABLE AdvisorConstraints (
  AdvisorID INTEGER,
  MaxStudents INTEGER,
  MaxWeight FLOAT
);

CREATE TABLE PetWeightAssignment (
  AdvisorID INTEGER,
  TotalPetWeight FLOAT
);
```

### Data Dictionary  
The data dictionary provides a comprehensive mapping of tables and columns to their business purposes and optimization roles:

- **Has_Pet**: This table maps students to their pets and advisors. It plays a crucial role in decision variables, where each student (StuID) is uniquely identified and linked to their respective pet (PetID) and advisor (AdvisorID).

- **AdvisorConstraints**: This table stores the constraints for each advisor, detailing the maximum number of students (MaxStudents) and the maximum total pet weight (MaxWeight) that each advisor can handle. These constraints are critical for ensuring that the optimization respects the resource limitations of each advisor.

- **PetWeightAssignment**: This table records the total weight of pets assigned to each advisor. It is used to calculate the advisor's workload, ensuring that the total pet weight assigned does not exceed the advisor's capacity.

### Current Stored Values  
```sql
-- Iteration 2 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical university advisor workloads and average pet weights, ensuring that constraints are neither too tight nor too loose, allowing for a balanced distribution of students and pets.

-- Realistic data for Has_Pet
INSERT INTO Has_Pet (StuID, PetID, AdvisorID) VALUES (1, 101, 201);
INSERT INTO Has_Pet (StuID, PetID, AdvisorID) VALUES (2, 102, 202);
INSERT INTO Has_Pet (StuID, PetID, AdvisorID) VALUES (3, 103, 203);

-- Realistic data for AdvisorConstraints
INSERT INTO AdvisorConstraints (AdvisorID, MaxStudents, MaxWeight) VALUES (201, 5, 100.0);
INSERT INTO AdvisorConstraints (AdvisorID, MaxStudents, MaxWeight) VALUES (202, 6, 120.0);
INSERT INTO AdvisorConstraints (AdvisorID, MaxStudents, MaxWeight) VALUES (203, 7, 150.0);

-- Realistic data for PetWeightAssignment
INSERT INTO PetWeightAssignment (AdvisorID, TotalPetWeight) VALUES (201, 50.0);
INSERT INTO PetWeightAssignment (AdvisorID, TotalPetWeight) VALUES (202, 60.0);
INSERT INTO PetWeightAssignment (AdvisorID, TotalPetWeight) VALUES (203, 70.0);
```