# Complete Optimization Problem and Solution: scientist_1

## 1. Problem Context and Goals

### Context  
A research institute is tasked with efficiently assigning scientists to projects to optimize resource utilization. The primary business decision involves determining whether a scientist is assigned to a specific project, represented as a binary choice. The operational parameters include the hours required for each project, which are used to calculate the total project hours. The goal is to minimize the total project hours while ensuring that each scientist is assigned to at least one project and that no project exceeds its maximum allowed hours.  

The business configuration includes scalar parameters such as the hours required for a project, which directly influence the objective function. Additionally, a business logic formula calculates the average hours per scientist, providing a performance metric to evaluate the efficiency of the assignment process. The problem is designed to avoid nonlinear relationships, ensuring that all decisions and constraints are linear in nature.  

### Goals  
The optimization goal is to minimize the total project hours by optimally assigning scientists to projects. Success is measured by achieving the lowest possible total project hours while adhering to the constraints. The metric to optimize is the sum of the hours required for each project multiplied by the binary decision variable indicating whether a scientist is assigned to that project. This goal aligns with the operational parameters and ensures efficient resource allocation.  

## 2. Constraints  

The problem is subject to the following constraints:  
1. **Scientist Assignment Constraint**: Each scientist must be assigned to at least one project. This ensures that all scientists are actively contributing to the research efforts.  
2. **Project Hours Constraint**: The total hours assigned to a project must not exceed its maximum allowed hours. This ensures that projects are completed within their resource limits.  
3. **Project Assignment Constraint**: Each project must have at least one scientist assigned to it. This ensures that all projects are staffed and progress is made.  

These constraints are designed to align with 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 changes include adding a table for maximum allowed project hours and refining the assignment table to fully map binary decision variables. Configuration logic updated to include scalar parameters for project hours and formulas for performance metrics.

CREATE TABLE Projects (
  Hours INTEGER
);

CREATE TABLE ProjectMaxHours (
  MaxHours INTEGER
);

CREATE TABLE AssignedTo (
  is_assigned BOOLEAN
);
```

### Data Dictionary  
- **Projects**:  
  - **Business Purpose**: Details of each project, including the hours required for completion.  
  - **Optimization Role**: Provides the coefficients for the objective function, which aims to minimize total project hours.  
  - **Columns**:  
    - **Hours**: Represents the hours required for the project. This value is used in the objective function to calculate total project hours.  

- **ProjectMaxHours**:  
  - **Business Purpose**: Specifies the maximum allowed hours for each project.  
  - **Optimization Role**: Provides the bounds for the constraint that ensures no project exceeds its maximum allowed hours.  
  - **Columns**:  
    - **MaxHours**: Represents the maximum allowed hours for the project. This value is used in the constraint to limit project hours.  

- **AssignedTo**:  
  - **Business Purpose**: Tracks the assignment of scientists to projects.  
  - **Optimization Role**: Represents the binary decision variables in the optimization model.  
  - **Columns**:  
    - **is_assigned**: Indicates whether a scientist is assigned to a project. This binary variable is used in the optimization model to determine assignments.  


### Retrieved Values

**Query 1: This data is crucial for calculating the total project hours to be minimized.**

```sql
SELECT Hours FROM Projects;
```

**Results (CSV format):**
```csv
Hours
120
150
100
```

**Query 2: This data ensures that no project exceeds its maximum allowed hours.**

```sql
SELECT MaxHours FROM ProjectMaxHours;
```

**Results (CSV format):**
```csv
MaxHours
200
250
180
```

**Query 3: This data is essential for determining the optimal assignment of scientists to projects.**

```sql
SELECT is_assigned FROM AssignedTo;
```

**Results (CSV format):**
```csv
is_assigned
1
0
1
1
0
```

**Query 4: This aggregated data helps in understanding the overall resource requirement and is directly related to the objective function.**

```sql
SELECT SUM(Hours) AS TotalProjectHours FROM Projects;
```

**Results (CSV format):**
```csv
TotalProjectHours
370
```

**Query 5: This summary statistic helps in evaluating the efficiency of the assignment process.**

```sql
SELECT AVG(Hours) AS AverageHoursPerProject FROM Projects;
```

**Results (CSV format):**
```csv
AverageHoursPerProject
123.33333333333333
```

**Query 6: This data helps in verifying that the total hours assigned to a project do not exceed its maximum allowed hours.**

```sql
SELECT SUM(Hours) AS TotalAssignedHoursPerProject FROM Projects JOIN AssignedTo ON Projects.project_id = AssignedTo.project_id  -- Assuming there is a project_id column in both tables WHERE is_assigned = TRUE GROUP BY Projects.project_id;
```

**Error:** Execution failed on sql 'SELECT SUM(Hours) AS TotalAssignedHoursPerProject FROM Projects JOIN AssignedTo ON Projects.project_id = AssignedTo.project_id  -- Assuming there is a project_id column in both tables WHERE is_assigned = TRUE GROUP BY Projects.project_id;': no such column: Projects.project_id

**Query 7: This data ensures that each project has at least one scientist assigned to it.**

```sql
SELECT COUNT(is_assigned) AS NumberOfScientistsPerProject FROM AssignedTo WHERE is_assigned = TRUE GROUP BY project_id;  -- Assuming there is a project_id column in AssignedTo;
```

**Error:** Execution failed on sql 'SELECT COUNT(is_assigned) AS NumberOfScientistsPerProject FROM AssignedTo WHERE is_assigned = TRUE GROUP BY project_id;  -- Assuming there is a project_id column in AssignedTo;': no such column: project_id

