# Complete Optimization Problem and Solution: entertainment_awards

## 1. Problem Context and Goals

### Context  
The film festival aims to maximize audience engagement by selecting a subset of artworks to nominate for each festival. The decision to nominate an artwork is binary, meaning each artwork can either be nominated or not for a specific festival. The engagement score for each artwork at a festival reflects its potential to attract and engage the audience. The festival must also adhere to two key operational constraints:  
1. **Maximum Nominations per Festival**: Each festival has a predefined limit on the number of artworks that can be nominated.  
2. **Minimum Diversity Requirement**: The nominated artworks must collectively meet a minimum diversity score, ensuring a balanced representation of different artwork types.  

The business configuration includes a scalar parameter, the minimum required diversity score, which is set to 0.75. This ensures that the selection of artworks maintains a moderate level of diversity while maximizing audience engagement.  

### Goals  
The primary goal of this optimization problem is to maximize the total audience engagement across all festivals. This is achieved by selecting the optimal set of artworks to nominate, considering their engagement scores and adhering to the operational constraints. Success is measured by the sum of engagement scores for the nominated artworks, ensuring that the festival’s audience engagement is as high as possible while respecting the nomination limits and diversity requirements.  

## 2. Constraints  

The optimization problem is subject to the following constraints:  
1. **Maximum Nominations per Festival**: For each festival, the total number of nominated artworks cannot exceed the predefined maximum number of nominations allowed for that festival.  
2. **Minimum Diversity Requirement**: For each festival, the combined diversity score of the nominated artworks must meet or exceed the minimum required diversity score of 0.75. This ensures a balanced selection of artwork types.  

These constraints are designed to ensure that the festival’s operational limits and diversity goals are met while maximizing audience engagement.  

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 3 Database Schema
-- Objective: Schema changes include adding a minimum diversity score constraint to the business configuration logic and updating the data dictionary to reflect this addition. No new tables were created or deleted as the existing schema adequately supports the optimization requirements.

CREATE TABLE engagement_scores (
  Artwork_ID INTEGER,
  Festival_ID INTEGER,
  score FLOAT
);

CREATE TABLE festival_nominations (
  Festival_ID INTEGER,
  max_nominations INTEGER
);

CREATE TABLE artwork_types (
  Type STRING,
  diversity_score FLOAT
);

CREATE TABLE nomination_decisions (
  Artwork_ID INTEGER,
  Festival_ID INTEGER,
  decision BOOLEAN
);
```

### Data Dictionary  
- **engagement_scores**: Contains the engagement scores for artworks at specific festivals.  
  - *Artwork_ID*: Unique identifier for the artwork.  
  - *Festival_ID*: Unique identifier for the festival.  
  - *score*: Engagement score for the artwork at the festival, used as the coefficient in the optimization objective.  

- **festival_nominations**: Defines the maximum number of nominations allowed per festival.  
  - *Festival_ID*: Unique identifier for the festival.  
  - *max_nominations*: Maximum number of nominations allowed for the festival, used as a constraint bound.  

- **artwork_types**: Lists the types of artworks and their associated diversity scores.  
  - *Type*: Type of artwork (e.g., Film, Short, Documentary).  
  - *diversity_score*: Diversity score for the artwork type, used in the diversity constraint.  

- **nomination_decisions**: Represents the binary decision variable indicating whether an artwork is nominated at a festival.  
  - *Artwork_ID*: Unique identifier for the artwork.  
  - *Festival_ID*: Unique identifier for the festival.  
  - *decision*: Binary decision (True/False) indicating whether the artwork is nominated.  


### Retrieved Values

**Query 1: This data is crucial for the objective function, as it represents the coefficients to maximize.**

```sql
SELECT Artwork_ID, Festival_ID, score FROM engagement_scores;
```

**Results (CSV format):**
```csv
Artwork_ID,Festival_ID,score
1,1,0.85
2,1,0.75
3,1,0.65
```

**Query 2: This data is essential for the constraint on the number of nominations per festival.**

```sql
SELECT Festival_ID, max_nominations FROM festival_nominations;
```

**Results (CSV format):**
```csv
Festival_ID,max_nominations
1,5
2,6
3,4
```

**Query 3: This data is necessary for calculating the diversity constraint.**

```sql
SELECT Type, diversity_score FROM artwork_types;
```

**Results (CSV format):**
```csv
Type,diversity_score
Film,0.9
Short,0.8
Documentary,0.7
```

**Query 4: This data represents the decision variables in the optimization problem.**

```sql
SELECT Artwork_ID, Festival_ID, decision FROM nomination_decisions;
```

**Results (CSV format):**
```csv
Artwork_ID,Festival_ID,decision
1,1,1
2,1,0
3,1,1
```

**Query 5: This summary data helps in understanding the total potential engagement per festival.**

```sql
SELECT Festival_ID, SUM(score) AS total_engagement FROM engagement_scores GROUP BY Festival_ID;
```

**Results (CSV format):**
```csv
Festival_ID,total_engagement
1,2.25
```

**Query 6: This data is useful for ensuring the nomination limit constraint is not exceeded.**

```sql
SELECT Festival_ID, COUNT(Artwork_ID) AS num_nominations FROM nomination_decisions WHERE decision = TRUE GROUP BY Festival_ID;
```

**Results (CSV format):**
```csv
Festival_ID,num_nominations
1,2
```

**Query 7: This data helps in verifying the diversity constraint.**

```sql
SELECT nd.Festival_ID, AVG(at.diversity_score) AS avg_diversity_score FROM nomination_decisions nd JOIN artwork_types at ON nd.Artwork_ID = at.Type WHERE nd.decision = TRUE GROUP BY nd.Festival_ID;
```

**Results (CSV format):**
```csv
Festival_ID,avg_diversity_score
```

**Query 8: This combined data is useful for evaluating both engagement and diversity simultaneously.**

```sql
SELECT es.Artwork_ID, es.Festival_ID, es.score, at.diversity_score FROM engagement_scores es JOIN artwork_types at ON es.Artwork_ID = at.Type;
```

**Results (CSV format):**
```csv
Artwork_ID,Festival_ID,score,diversity_score
```

**Query 9: This data helps in monitoring the nomination limit constraint.**

```sql
SELECT fn.Festival_ID, fn.max_nominations, COUNT(nd.Artwork_ID) AS current_nominations FROM festival_nominations fn LEFT JOIN nomination_decisions nd ON fn.Festival_ID = nd.Festival_ID AND nd.decision = TRUE GROUP BY fn.Festival_ID, fn.max_nominations;
```

**Results (CSV format):**
```csv
Festival_ID,max_nominations,current_nominations
1,5,2
2,6,0
3,4,0
```

**Query 10: This data is critical for identifying constraint violations.**

```sql
SELECT fn.Festival_ID, fn.max_nominations, COUNT(nd.Artwork_ID) AS current_nominations FROM festival_nominations fn JOIN nomination_decisions nd ON fn.Festival_ID = nd.Festival_ID AND nd.decision = TRUE GROUP BY fn.Festival_ID, fn.max_nominations HAVING COUNT(nd.Artwork_ID) > fn.max_nominations;
```

**Results (CSV format):**
```csv
Festival_ID,max_nominations,current_nominations
```

**Query 11: This data helps in identifying diversity constraint violations.**

```sql
SELECT nd.Festival_ID, AVG(at.diversity_score) AS avg_diversity_score FROM nomination_decisions nd JOIN artwork_types at ON nd.Artwork_ID = at.Type WHERE nd.decision = TRUE GROUP BY nd.Festival_ID HAVING AVG(at.diversity_score) < 0.75;
```

**Results (CSV format):**
```csv
Festival_ID,avg_diversity_score
```

