# Complete Optimization Problem and Solution: concert_singer

## 1. Problem Context and Goals

### Context  
The business problem revolves around optimizing the allocation of singers to concerts held in various stadiums. The primary objective is to maximize the total audience capacity across all concerts while ensuring that singers are not overbooked and that each concert does not exceed the stadium's capacity. 

Key operational decisions involve determining whether a specific singer is assigned to a concert in a particular stadium. This decision is represented by a binary variable, where a value of 1 indicates an assignment and 0 indicates no assignment. The total audience capacity is calculated by summing the capacities of all stadiums where concerts are assigned to singers.

Business configuration includes a scalar parameter that limits the maximum number of concerts a singer can participate in, ensuring that singers are not overbooked. Additionally, the business logic formula for calculating the total audience capacity is based on the sum of the capacities of all stadiums where concerts are assigned to singers.

### Goals  
The optimization goal is to maximize the total audience capacity across all concerts. This is achieved by strategically assigning singers to concerts in stadiums, ensuring that the sum of the capacities of all assigned concerts is as large as possible. Success is measured by the total audience capacity achieved, which directly depends on the capacities of the stadiums and the assignments of singers to concerts.

## 2. Constraints    

The optimization problem is subject to the following constraints:

1. **Singer Assignment Limit**: Each singer can participate in a maximum number of concerts, as defined by the business configuration parameter. This ensures that singers are not overbooked and can manage their schedules effectively.

2. **Stadium Capacity Limit**: Each concert in a stadium cannot exceed the stadium's capacity. This ensures that the number of singers assigned to a concert does not surpass the stadium's ability to accommodate them.

These constraints are designed to ensure that the assignments are feasible and respect both singer availability and stadium limitations.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating a singer_concert_assignment table for decision variables, adding singer_limit to business_configuration_logic, and ensuring stadium capacity is correctly mapped. Configuration logic updates include scalar parameters for singer limits and formulas for audience capacity calculations.

CREATE TABLE stadium (
  Capacity INTEGER
);

CREATE TABLE singer_concert_assignment (
  assignment BOOLEAN
);
```

### Data Dictionary  
- **stadium**: This table contains details about the stadiums, including their capacities.
  - **Capacity**: The maximum number of singers a stadium can accommodate. This value is used as a coefficient in the objective function to calculate the total audience capacity.

- **singer_concert_assignment**: This table records the assignments of singers to concerts in stadiums.
  - **assignment**: A binary variable indicating whether a singer is assigned to a concert in a stadium. This variable is used as a decision variable in the optimization model.


### Retrieved Values

**Query 1: Query Description: Retrieve the capacities of all stadiums. This is crucial for the objective function, as the total audience capacity is calculated based on the sum of the capacities of all stadiums where concerts are assigned to singers.**

```sql
SELECT Capacity FROM stadium;
```

**Results (CSV format):**
```csv
Capacity
10000
20000
30000
```

**Query 2: Query Description: Retrieve all singer-concert assignments. This data is essential for the decision variables in the optimization model, as it indicates whether a singer is assigned to a concert in a stadium.**

```sql
SELECT assignment FROM singer_concert_assignment;
```

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

**Query 3: Query Description: Count the number of assignments per singer. This is important for enforcing the singer assignment limit constraint, ensuring that no singer is overbooked.**

```sql
SELECT singer_id, COUNT(*) AS assignment_count FROM singer_concert_assignment GROUP BY singer_id;
```

**Error:** Execution failed on sql 'SELECT singer_id, COUNT(*) AS assignment_count FROM singer_concert_assignment GROUP BY singer_id;': no such column: singer_id

**Query 4: Note: Assuming there is a table or configuration that stores this parameter. Since it's not explicitly defined in the schema, this is a placeholder query.**

```sql
SELECT singer_limit FROM business_configuration_logic;
```

**Error:** Execution failed on sql 'SELECT singer_limit FROM business_configuration_logic;': no such table: business_configuration_logic

**Query 5: Query Description: Retrieve the total capacity of all stadiums. This can be useful for understanding the upper bound of the total audience capacity that can be achieved.**

```sql
SELECT SUM(Capacity) AS total_capacity FROM stadium;
```

**Results (CSV format):**
```csv
total_capacity
60000
```

**Query 6: Query Description: Retrieve the total number of assignments across all singers. This can help in understanding the overall assignment load and whether it aligns with the constraints.**

```sql
SELECT COUNT(*) AS total_assignments FROM singer_concert_assignment;
```

**Results (CSV format):**
```csv
total_assignments
5
```

**Query 7: Note: Assuming there is a way to link assignments to specific stadiums. Since it's not explicitly defined in the schema, this is a placeholder query.**

```sql
SELECT stadium_id, COUNT(*) AS assignment_count FROM singer_concert_assignment GROUP BY stadium_id;
```

**Error:** Execution failed on sql 'SELECT stadium_id, COUNT(*) AS assignment_count FROM singer_concert_assignment GROUP BY stadium_id;': no such column: stadium_id

**Query 8: Note: Assuming there is a way to link assignments to specific stadiums. Since it's not explicitly defined in the schema, this is a placeholder query.**

```sql
SELECT SUM(s.Capacity) AS total_audience_capacity FROM stadium s JOIN singer_concert_assignment sca ON s.stadium_id = sca.stadium_id WHERE sca.assignment = 1;
```

**Error:** Execution failed on sql 'SELECT SUM(s.Capacity) AS total_audience_capacity FROM stadium s JOIN singer_concert_assignment sca ON s.stadium_id = sca.stadium_id WHERE sca.assignment = 1;': no such column: s.stadium_id

