# 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.

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on realistic stadium capacities, singer availability, and business constraints to ensure a meaningful and solvable optimization problem.

-- Realistic data for stadium
INSERT INTO stadium (Capacity) VALUES (10000);
INSERT INTO stadium (Capacity) VALUES (20000);
INSERT INTO stadium (Capacity) VALUES (30000);

-- Realistic data for singer_concert_assignment
INSERT INTO singer_concert_assignment (assignment) VALUES (0);
INSERT INTO singer_concert_assignment (assignment) VALUES (1);
INSERT INTO singer_concert_assignment (assignment) VALUES (0);
INSERT INTO singer_concert_assignment (assignment) VALUES (1);
INSERT INTO singer_concert_assignment (assignment) VALUES (0);
```