# Complete Optimization Problem and Solution: restaurant_1

## 1. Problem Context and Goals

### Context  
A university is focused on optimizing the allocation of students to restaurants based on their preferences and spending habits. The goal is to maximize student satisfaction while ensuring that the total spending across all students does not exceed a predefined budget. Student satisfaction is modeled as a linear function of restaurant ratings and the time spent at the restaurant. The university has a total budget of $15,000 allocated for student spending across all restaurants. Satisfaction scores are calculated by multiplying the time spent at a restaurant by its rating, ensuring a linear relationship that aligns with the optimization framework. The decision to allocate a student to a specific restaurant is represented as a binary choice, where each student is assigned to exactly one restaurant. The problem is designed to avoid any nonlinear relationships, such as variable products or divisions, ensuring a linear optimization formulation.

### Goals  
The primary goal of this optimization problem is to maximize the overall student satisfaction across all restaurant visits. This is achieved by strategically allocating students to restaurants in a way that their individual satisfaction scores are maximized. Success is measured by the sum of satisfaction scores for all student-restaurant pairs, ensuring that the allocation adheres to the university’s budget and respects the capacity limits of each restaurant. The optimization process ensures that each student is assigned to exactly one restaurant, and the total spending across all students does not exceed the allocated budget.

## 2. Constraints  

The optimization problem is subject to the following constraints:  
1. **Total Spending Constraint**: The total spending across all student-restaurant pairs must not exceed the university’s budget of $15,000. This ensures that the allocation remains financially feasible.  
2. **Restaurant Capacity Constraint**: The number of students assigned to each restaurant must not exceed its maximum capacity. This ensures that no restaurant is overbooked.  
3. **Student Assignment Constraint**: Each student must be assigned to exactly one restaurant. This ensures that all students are included in the allocation without duplication.  

These constraints are designed to align with linear mathematical relationships, avoiding any nonlinear complexities such as variable products or divisions.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating tables for satisfaction scores and restaurant capacities, modifying the Visits_Restaurant table to better map decision variables, and adding business configuration logic for budget and satisfaction score calculation.

CREATE TABLE Satisfaction_Scores (
  StuID INTEGER,
  ResID INTEGER,
  score FLOAT
);

CREATE TABLE Restaurant_Capacities (
  ResID INTEGER,
  capacity INTEGER
);

CREATE TABLE Visits_Restaurant (
  StuID INTEGER,
  ResID INTEGER,
  visited BOOLEAN
);
```

### Data Dictionary  
- **Satisfaction_Scores**:  
  - **Business Purpose**: Stores the satisfaction scores for each student-restaurant pair, calculated based on time spent and restaurant rating.  
  - **Optimization Role**: Provides the coefficients for the objective function, representing the satisfaction score for each student-restaurant pair.  
  - **Columns**:  
    - **StuID**: Identifies the student.  
    - **ResID**: Identifies the restaurant.  
    - **score**: Represents the satisfaction score, used in the objective function.  

- **Restaurant_Capacities**:  
  - **Business Purpose**: Stores the maximum number of students each restaurant can accommodate.  
  - **Optimization Role**: Provides the bounds for the restaurant capacity constraints.  
  - **Columns**:  
    - **ResID**: Identifies the restaurant.  
    - **capacity**: Represents the maximum capacity of the restaurant, used in the capacity constraint.  

- **Visits_Restaurant**:  
  - **Business Purpose**: Tracks the allocation of students to restaurants.  
  - **Optimization Role**: Represents the binary decision variables indicating whether a student visits a specific restaurant.  
  - **Columns**:  
    - **StuID**: Identifies the student.  
    - **ResID**: Identifies the restaurant.  
    - **visited**: A binary value indicating whether the student visits the restaurant, used as the decision variable.  


### Retrieved Values

**Query 1: Importance: These scores are the coefficients for the objective function, which aims to maximize overall student satisfaction.**

```sql
SELECT StuID, ResID, score FROM Satisfaction_Scores;
```

**Results (CSV format):**
```csv
StuID,ResID,score
1,1,4.5
1,2,3.0
2,1,3.8
```

**Query 2: Importance: These capacities are used in the restaurant capacity constraint to ensure no restaurant is overbooked.**

```sql
SELECT ResID, capacity FROM Restaurant_Capacities;
```

**Results (CSV format):**
```csv
ResID,capacity
1,50
2,75
3,100
```

**Query 3: Importance: This data represents the binary decision variables indicating whether a student visits a specific restaurant.**

```sql
SELECT StuID, ResID, visited FROM Visits_Restaurant;
```

**Results (CSV format):**
```csv
StuID,ResID,visited
1,1,1
2,2,1
3,3,1
```

**Query 4: Importance: This aggregated data helps in enforcing the restaurant capacity constraint by comparing the number of assigned students to the restaurant's capacity.**

```sql
SELECT ResID, COUNT(StuID) AS assigned_students FROM Visits_Restaurant WHERE visited = TRUE GROUP BY ResID;
```

**Results (CSV format):**
```csv
ResID,assigned_students
1,1
2,1
3,1
```

**Query 5: Importance: This helps in understanding the current satisfaction level of each student, which is crucial for the objective function.**

```sql
SELECT v.StuID, s.score FROM Visits_Restaurant v JOIN Satisfaction_Scores s ON v.StuID = s.StuID AND v.ResID = s.ResID WHERE v.visited = TRUE;
```

**Results (CSV format):**
```csv
StuID,score
1,4.5
```

**Query 6: Importance: This aggregated data is essential for enforcing the total spending constraint, ensuring it does not exceed the university’s budget.**

```sql
SELECT SUM(s.score) AS total_spending FROM Visits_Restaurant v JOIN Satisfaction_Scores s ON v.StuID = s.StuID AND v.ResID = s.ResID WHERE v.visited = TRUE;
```

**Results (CSV format):**
```csv
total_spending
4.5
```

**Query 7: Importance: This helps in ensuring that all students are included in the allocation, adhering to the student assignment constraint.**

```sql
SELECT StuID FROM Visits_Restaurant WHERE visited = FALSE;
```

**Results (CSV format):**
```csv
StuID
```

**Query 8: Importance: This helps in identifying available options for assigning students, ensuring the restaurant capacity constraint is respected.**

```sql
SELECT r.ResID, r.capacity, COUNT(v.StuID) AS assigned_students FROM Restaurant_Capacities r LEFT JOIN Visits_Restaurant v ON r.ResID = v.ResID AND v.visited = TRUE GROUP BY r.ResID, r.capacity HAVING COUNT(v.StuID) < r.capacity;
```

**Results (CSV format):**
```csv
ResID,capacity,assigned_students
1,50,1
2,75,1
3,100,1
```

**Query 9: Importance: This comprehensive view helps in evaluating all potential assignments to maximize overall satisfaction.**

```sql
SELECT s.StuID, s.ResID, s.score FROM Satisfaction_Scores s LEFT JOIN Visits_Restaurant v ON s.StuID = v.StuID AND s.ResID = v.ResID WHERE v.visited IS NULL OR v.visited = FALSE;
```

**Results (CSV format):**
```csv
StuID,ResID,score
1,2,3.0
2,1,3.8
```

