# Complete Optimization Problem and Solution: voter_2

## 1. Problem Context and Goals

### Context  
A university is focused on maximizing student participation in elections by strategically allocating resources such as campaign materials and reminders to different majors. The allocation is based on historical voting patterns and student demographics. The university aims to ensure that resource distribution respects the total budget, provides a minimum level of support to each major, avoids excessive allocation to any single major, and is proportional to the number of students in each major.  

The decision variables represent the amount of resources allocated to each major, which must be integer values. The objective is to maximize the total expected votes across all election positions by leveraging the relationship between resource allocation and expected votes.  

The business configuration includes:  
- A total budget of 50,000 units available for resource allocation, which serves as the upper limit for the sum of all resource allocations.  
- A minimum resource allocation of 500 units per major, ensuring each major receives a baseline level of support.  
- A maximum resource allocation of 5,000 units per major, preventing any single major from consuming an excessive share of the budget.  
- A resource allocation of 10 units per student, ensuring proportional distribution based on the number of students in each major.  

The expected votes per major are calculated using a formula that incorporates historical voting data and a student engagement factor. This calculation provides the coefficients used in the objective function to measure the impact of resource allocation on expected votes.  

### Goals  
The primary goal of this optimization problem is to maximize the total expected votes across all majors by allocating resources effectively. Success is measured by the sum of the expected votes per major multiplied by the corresponding resource allocation. This linear relationship ensures that the optimization goal is directly tied to the operational decision of resource distribution.  

## 2. Constraints  

The resource allocation must adhere to the following constraints:  
1. The total amount of resources allocated across all majors must not exceed the total budget of 50,000 units.  
2. Each major must receive at least 500 units of resources to ensure a minimum level of support.  
3. No major can receive more than 5,000 units of resources to prevent excessive allocation to any single major.  
4. The resource allocation for each major must be proportional to the number of students in that major, with a maximum of 10 units allocated per student.  

These constraints ensure that the resource allocation is both fair and feasible, aligning with the university’s operational and budgetary requirements.  

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating tables for expected votes, resource allocations, and budget constraints. Configuration logic updates include scalar parameters for budget and resource limits, and formulas for expected votes calculation.

CREATE TABLE ExpectedVotesPerMajor (
  major STRING,
  expected_votes INTEGER
);

CREATE TABLE ResourceAllocationPerMajor (
  major STRING,
  resource_allocation INTEGER
);

CREATE TABLE Student (
  major STRING,
  number_of_students_in_major INTEGER
);
```

### Data Dictionary  
- **ExpectedVotesPerMajor**:  
  - **Business Purpose**: Stores the expected number of votes per major based on historical voting data.  
  - **Optimization Role**: Provides the coefficients used in the objective function to measure the impact of resource allocation on expected votes.  
  - **Columns**:  
    - `major`: The name of the major (e.g., Computer Science, Biology).  
    - `expected_votes`: The expected number of votes for the major, derived from historical data and engagement factors.  

- **ResourceAllocationPerMajor**:  
  - **Business Purpose**: Tracks the amount of resources allocated to each major.  
  - **Optimization Role**: Represents the decision variables in the optimization model.  
  - **Columns**:  
    - `major`: The name of the major (e.g., Computer Science, Biology).  
    - `resource_allocation`: The amount of resources allocated to the major, which must be an integer value.  

- **Student**:  
  - **Business Purpose**: Contains demographic information about students, including the number of students in each major.  
  - **Optimization Role**: Provides data used to calculate proportional resource allocation based on student numbers.  
  - **Columns**:  
    - `major`: The name of the major (e.g., Computer Science, Biology).  
    - `number_of_students_in_major`: The number of students enrolled in the major, used to determine proportional resource allocation.  


### Retrieved Values

**Query 1: This data is crucial for calculating the impact of resource allocation on expected votes.**

```sql
SELECT major, expected_votes FROM ExpectedVotesPerMajor;
```

**Results (CSV format):**
```csv
major,expected_votes
Computer Science,150
Biology,100
History,80
```

**Query 2: This data is essential for calculating the maximum resource allocation per major based on the number of students.**

```sql
SELECT major, number_of_students_in_major FROM Student;
```

**Results (CSV format):**
```csv
major,number_of_students_in_major
Computer Science,200
Biology,150
History,100
```

**Query 3: This data is necessary to understand the current state of resource distribution and to apply constraints.**

```sql
SELECT major, resource_allocation FROM ResourceAllocationPerMajor;
```

**Results (CSV format):**
```csv
major,resource_allocation
Computer Science,2000
Biology,1500
History,1000
```

**Query 4: This query ensures that the resource allocation does not exceed 10 units per student, as per the constraint.**

```sql
SELECT s.major, s.number_of_students_in_major * 10 AS max_resource_allocation FROM Student s;
```

**Results (CSV format):**
```csv
major,max_resource_allocation
Computer Science,2000
Biology,1500
History,1000
```

**Query 5: This data is useful for understanding the overall student population and for calculating proportional allocations.**

```sql
SELECT SUM(number_of_students_in_major) AS total_students FROM Student;
```

**Results (CSV format):**
```csv
total_students
450
```

**Query 6: This data is necessary to ensure that the total allocation does not exceed the budget constraint of 50,000 units.**

```sql
SELECT SUM(resource_allocation) AS total_resource_allocation FROM ResourceAllocationPerMajor;
```

**Results (CSV format):**
```csv
total_resource_allocation
4500
```

**Query 7: This data is useful for understanding the relationship between student population and expected votes, which can inform resource allocation strategies.**

```sql
SELECT ev.major, ev.expected_votes, s.number_of_students_in_major FROM ExpectedVotesPerMajor ev JOIN Student s ON ev.major = s.major;
```

**Results (CSV format):**
```csv
major,expected_votes,number_of_students_in_major
Computer Science,150,200
Biology,100,150
History,80,100
```

**Query 8: This data is crucial for ensuring that the current allocations do not exceed the maximum allowable limits based on student numbers.**

```sql
SELECT ra.major, ra.resource_allocation, s.number_of_students_in_major * 10 AS max_resource_allocation FROM ResourceAllocationPerMajor ra JOIN Student s ON ra.major = s.major;
```

**Results (CSV format):**
```csv
major,resource_allocation,max_resource_allocation
Computer Science,2000,2000
Biology,1500,1500
History,1000,1000
```

**Query 9: This comprehensive view is useful for evaluating the effectiveness of current allocations in terms of expected votes and student population.**

```sql
SELECT ev.major, ev.expected_votes, ra.resource_allocation, s.number_of_students_in_major FROM ExpectedVotesPerMajor ev JOIN ResourceAllocationPerMajor ra ON ev.major = ra.major JOIN Student s ON ev.major = s.major;
```

**Results (CSV format):**
```csv
major,expected_votes,resource_allocation,number_of_students_in_major
Computer Science,150,2000,200
Biology,100,1500,150
History,80,1000,100
```

**Query 10: This data is essential for evaluating the current effectiveness of resource allocations in terms of expected votes.**

```sql
SELECT SUM(ev.expected_votes * ra.resource_allocation) AS total_expected_votes FROM ExpectedVotesPerMajor ev JOIN ResourceAllocationPerMajor ra ON ev.major = ra.major;
```

**Results (CSV format):**
```csv
total_expected_votes
530000
```

