# Complete Optimization Problem and Solution: tvshow

## 1. Problem Context and Goals

### Context  
A TV network is tasked with maximizing viewer engagement by strategically allocating airtime to its series and cartoons. The network must decide how many episodes of each show to air, considering the show's viewer ratings, the channel's total available airtime, and the need for content diversity. Each show has a minimum and maximum number of episodes that must be aired to ensure visibility and avoid over-saturation. Additionally, the network must maintain a minimum level of diversity across the aired shows to cater to a broad audience. The total airtime allocated to all shows combined cannot exceed the channel's capacity, which is set at 1200 units. The network aims to balance these factors to achieve the highest possible viewer ratings while adhering to operational constraints.

### Goals  
The primary goal is to maximize the total viewer ratings across all aired episodes. This is achieved by determining the optimal number of episodes to air for each show, weighted by its individual rating. Success is measured by the sum of the ratings of all aired episodes, ensuring that the network's programming appeals to the largest possible audience. The decision-making process is guided by linear relationships, ensuring that the solution is both practical and efficient.

## 2. Constraints    

1. **Total Airtime Constraint**: The combined airtime of all aired episodes must not exceed the channel's total available airtime of 1200 units. This ensures that the network operates within its capacity.  
2. **Episode Bounds Constraint**: Each show must air a minimum of 1 episode and a maximum of 10 episodes. This ensures that no show is overlooked or overexposed.  
3. **Diversity Constraint**: The total diversity score of all aired shows must meet or exceed the minimum required diversity score of 50. This ensures a balanced mix of content that appeals to a wide audience.  

These constraints are designed to align with linear mathematical relationships, ensuring that the optimization problem remains straightforward and solvable using linear methods.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating new tables for missing optimization requirements, modifying existing tables to better map to optimization variables, and adding business configuration logic for scalar parameters and formulas.

CREATE TABLE TV_series (
  Rating FLOAT,
  Min_Episodes INTEGER,
  Max_Episodes INTEGER,
  Episodes_Aired INTEGER
);

CREATE TABLE Cartoon (
  Rating FLOAT,
  Min_Episodes INTEGER,
  Max_Episodes INTEGER,
  Episodes_Aired INTEGER
);

CREATE TABLE Show_Diversity (
  Diversity_Score INTEGER
);


```

### Data Dictionary  
- **TV_series**: Stores information about TV series, including their ratings, minimum and maximum episode limits, and the number of episodes aired.  
  - **Rating**: The viewer rating of the TV series, used to determine its contribution to the total viewer engagement.  
  - **Min_Episodes**: The minimum number of episodes that must be aired for the series to maintain visibility.  
  - **Max_Episodes**: The maximum number of episodes that can be aired to avoid over-saturation.  
  - **Episodes_Aired**: The decision variable representing the number of episodes aired for the series.  

- **Cartoon**: Stores information about cartoons, including their ratings, minimum and maximum episode limits, and the number of episodes aired.  
  - **Rating**: The viewer rating of the cartoon, used to determine its contribution to the total viewer engagement.  
  - **Min_Episodes**: The minimum number of episodes that must be aired for the cartoon to maintain visibility.  
  - **Max_Episodes**: The maximum number of episodes that can be aired to avoid over-saturation.  
  - **Episodes_Aired**: The decision variable representing the number of episodes aired for the cartoon.  

- **Show_Diversity**: Stores diversity scores for shows, ensuring a balanced mix of content.  
  - **Diversity_Score**: The diversity score of the show, used to ensure that the total diversity of aired shows meets the minimum requirement.  


### Retrieved Values

**Query 1: This data is crucial for determining the optimal number of episodes to air for each TV series, considering their ratings and constraints.**

```sql
SELECT Rating, Min_Episodes, Max_Episodes, Episodes_Aired FROM TV_series;
```

**Results (CSV format):**
```csv
Rating,Min_Episodes,Max_Episodes,Episodes_Aired
4.8,2,8,5
4.3,1,6,3
4.6,3,7,4
```

**Query 2: This data is crucial for determining the optimal number of episodes to air for each cartoon, considering their ratings and constraints.**

```sql
SELECT Rating, Min_Episodes, Max_Episodes, Episodes_Aired FROM Cartoon;
```

**Results (CSV format):**
```csv
Rating,Min_Episodes,Max_Episodes,Episodes_Aired
4.5,1,5,3
4.1,2,4,2
4.4,1,6,4
```

**Query 3: This data is necessary to ensure that the total diversity of aired shows meets the minimum required diversity score of 50.**

```sql
SELECT SUM(Diversity_Score) AS Total_Diversity_Score FROM Show_Diversity;
```

**Results (CSV format):**
```csv
Total_Diversity_Score
45
```

**Query 4: This data is essential to ensure that the combined airtime of all aired episodes does not exceed the channel's total available airtime of 1200 units.**

```sql
SELECT Episodes_Aired FROM Cartoon ) AS All_Shows;
```

**Error:** Execution failed on sql 'SELECT Episodes_Aired FROM Cartoon ) AS All_Shows;': near ")": syntax error

**Query 5: This data is important for calculating the total viewer engagement contributed by TV series.**

```sql
SELECT SUM(Rating * Episodes_Aired) AS Total_TV_Series_Ratings FROM TV_series;
```

**Results (CSV format):**
```csv
Total_TV_Series_Ratings
55.3
```

**Query 6: This data is important for calculating the total viewer engagement contributed by cartoons.**

```sql
SELECT SUM(Rating * Episodes_Aired) AS Total_Cartoon_Ratings FROM Cartoon;
```

**Results (CSV format):**
```csv
Total_Cartoon_Ratings
39.3
```

**Query 7: This data is crucial for the objective function, which aims to maximize the total viewer ratings.**

```sql
SELECT Rating, Episodes_Aired FROM Cartoon ) AS All_Shows;
```

**Error:** Execution failed on sql 'SELECT Rating, Episodes_Aired FROM Cartoon ) AS All_Shows;': near ")": syntax error

**Query 8: This data is necessary to ensure that each show airs within its specified episode bounds.**

```sql
SELECT 'Cartoon' AS Show_Type, Min_Episodes, Max_Episodes FROM Cartoon;
```

**Results (CSV format):**
```csv
Show_Type,Min_Episodes,Max_Episodes
Cartoon,1,5
Cartoon,2,4
Cartoon,1,6
```

**Query 9: This data helps in understanding the current distribution of aired episodes across different show types.**

```sql
SELECT 'Cartoon' AS Show_Type, SUM(Episodes_Aired) AS Total_Episodes_Aired FROM Cartoon;
```

**Results (CSV format):**
```csv
Show_Type,Total_Episodes_Aired
Cartoon,9
```

**Query 10: This data is useful for understanding the scale of the optimization problem.**

```sql
SELECT 'Cartoon' AS Show_Type, COUNT(*) AS Total_Shows FROM Cartoon;
```

**Results (CSV format):**
```csv
Show_Type,Total_Shows
Cartoon,3
```

