# Complete Optimization Problem and Solution: scientist_1

## 1. Problem Context and Goals

### Context  
The research organization is focused on optimizing the allocation of its scientists to various projects. The primary decision involves determining which scientists should be assigned to which projects. This decision is represented by binary variables, where each variable indicates whether a specific scientist is assigned to a particular project. The organization aims to maximize the total hours worked on projects, which is a linear objective. This involves summing the hours associated with each project, weighted by the assignment of scientists to those projects.

The business configuration includes specific operational parameters: each scientist must be assigned to a minimum number of projects, and each project must have a minimum number of scientists assigned. These parameters are critical as they ensure that scientists are engaged in multiple projects, promoting collaboration and efficient use of resources, while also guaranteeing that each project receives adequate attention.

The data used in this optimization process reflects current operational information, ensuring that the decision-making process is grounded in realistic scenarios. The constraints and objectives are designed to be linear, avoiding any nonlinear relationships such as variable products or divisions. The operational parameters are directly mapped to expected coefficient sources, ensuring a clear and consistent optimization framework.

### Goals  
The primary goal of this optimization problem is to maximize the total hours worked on projects. This is achieved by optimally assigning scientists to projects in a way that maximizes the sum of the hours associated with each project, weighted by the assignment of scientists. The success of this optimization is measured by the total hours worked, which directly aligns with the expected coefficient sources. The goal is articulated in natural language, focusing on the linear optimization objective without the use of mathematical formulas or symbolic notation.

## 2. Constraints    

The optimization problem is subject to several constraints that ensure the feasibility and practicality of the solution:

- Each scientist must be assigned to at least a minimum number of projects. This constraint ensures that scientists are actively engaged and contributing to multiple projects, fostering a collaborative environment.
  
- Each project must have at least a minimum number of scientists assigned. This constraint guarantees that every project receives the necessary attention and resources to be successful.

These constraints are described in business terms that naturally lead to linear mathematical forms, avoiding any nonlinear relationships such as variable products or divisions.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema adjustments include creating new tables for constraint bounds, modifying existing tables to improve mapping adequacy, and updating business configuration logic for scalar parameters and formulas.

CREATE TABLE Projects (
  ProjectID INTEGER,
  Hours FLOAT
);

CREATE TABLE AssignedTo (
  ScientistID INTEGER,
  ProjectID INTEGER,
  binary_column BOOLEAN
);

CREATE TABLE ConstraintBounds (
  ConstraintType STRING,
  MinAssignments INTEGER
);
```

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

- **Projects Table**: This table stores information about each project, including the number of hours associated with it. The hours serve as coefficients in the optimization objective, representing the value of assigning scientists to projects.

- **AssignedTo Table**: This table tracks the assignment of scientists to projects. It includes a binary column that indicates whether a scientist is assigned to a project. This information is crucial for defining the decision variables in the optimization model.

- **ConstraintBounds Table**: This table stores the bounds for assignment constraints. It specifies the minimum number of assignments required for both scientists and projects, ensuring that the optimization model adheres to business requirements.

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical project hours and realistic minimum assignment constraints to ensure a balanced workload among scientists and projects.

-- Realistic data for Projects
INSERT INTO Projects (ProjectID, Hours) VALUES (1, 15.0);
INSERT INTO Projects (ProjectID, Hours) VALUES (2, 25.0);
INSERT INTO Projects (ProjectID, Hours) VALUES (3, 35.0);

-- Realistic data for AssignedTo
INSERT INTO AssignedTo (ScientistID, ProjectID, binary_column) VALUES (101, 1, True);
INSERT INTO AssignedTo (ScientistID, ProjectID, binary_column) VALUES (101, 2, True);
INSERT INTO AssignedTo (ScientistID, ProjectID, binary_column) VALUES (102, 2, True);
INSERT INTO AssignedTo (ScientistID, ProjectID, binary_column) VALUES (102, 3, True);
INSERT INTO AssignedTo (ScientistID, ProjectID, binary_column) VALUES (103, 3, True);

-- Realistic data for ConstraintBounds
INSERT INTO ConstraintBounds (ConstraintType, MinAssignments) VALUES ('scientist', 2);
INSERT INTO ConstraintBounds (ConstraintType, MinAssignments) VALUES ('project', 1);
```