# Complete Optimization Problem and Solution: concert_singer

## 1. Problem Context and Goals

### Context  
The concert organizer is tasked with maximizing attendance across various concerts by strategically selecting which concerts to hold at specific stadiums. Each decision involves determining whether a concert should be held at a particular stadium, represented by a binary decision variable. The primary objective is to maximize the total expected attendance, which is calculated by summing the expected attendance for each concert-stadium pairing. This expected attendance is derived from predefined coefficients that estimate the number of attendees if a concert is held at a specific stadium. In cases where specific attendance data is unavailable, a default attendance coefficient is used as a fallback to ensure continuity in the optimization model.

The operational parameters include the stadium capacities, which act as constraints to ensure that the number of attendees does not exceed the maximum capacity of each stadium. Additionally, it is essential that each concert is paired with at least one stadium to ensure that every concert has a venue. The business configuration includes a default attendance coefficient, which is set at a realistic value of 150, representing a moderate draw for fallback scenarios.

### Goals  
The primary goal of the optimization is to maximize the total attendance across all concerts. This is achieved by selecting the optimal concert-stadium pairings that yield the highest expected attendance, based on the attendance coefficients. The success of this optimization is measured by the total expected attendance, which is calculated by summing the expected attendance for each selected concert-stadium pairing. The optimization process focuses on maximizing this metric while adhering to the constraints of stadium capacities and ensuring each concert is held at a venue.

## 2. Constraints    

The optimization problem is subject to several constraints that ensure feasibility and alignment with business requirements:

- Each concert must be held at least once, meaning that for every concert, there should be at least one stadium selected to host it. This ensures that no concert is left without a venue.
  
- The total expected attendance for a concert at a particular stadium must not exceed the stadium's capacity. This constraint ensures that the number of attendees does not surpass the maximum number that the stadium can accommodate, maintaining safety and logistical feasibility.

These constraints are expressed in business terms that naturally lead to linear mathematical forms, ensuring that the optimization problem remains linear and solvable using standard linear programming techniques.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 2 Database Schema
-- Objective: Incorporated stadium capacity data into the schema, addressed mapping gaps, and updated business configuration logic for scalar parameters and formulas.

CREATE TABLE concert_stadium_mapping (
  concert_ID INTEGER,
  stadium_ID INTEGER
);

CREATE TABLE attendance_coefficients (
  concert_ID INTEGER,
  stadium_ID INTEGER,
  coefficient FLOAT
);

CREATE TABLE stadium_capacity (
  stadium_ID INTEGER,
  capacity INTEGER
);
```

### Data Dictionary  
The data dictionary provides a comprehensive mapping of tables and columns to their business purposes and roles in the optimization model:

- **Concert-Stadium Mapping Table**: This table maps concerts to specific stadiums for planning purposes. It plays a crucial role in linking concerts to stadiums, allowing the optimization model to determine which concerts are held at which venues.

  - **concert_ID**: A unique identifier for each concert, used to link concerts to stadiums.
  - **stadium_ID**: A unique identifier for each stadium, used to link stadiums to concerts.

- **Attendance Coefficients Table**: This table stores the expected attendance coefficients for each concert-stadium combination. These coefficients are used as objective coefficients in the optimization model to calculate the expected attendance.

  - **concert_ID**: Part of the composite key for attendance coefficients, linking to the concert.
  - **stadium_ID**: Part of the composite key for attendance coefficients, linking to the stadium.
  - **coefficient**: Represents the expected attendance if the concert is held at the stadium, serving as the objective coefficient in the optimization model.

- **Stadium Capacity Table**: This table stores the maximum capacity for each stadium, serving as constraint bounds in the optimization model to ensure that attendance does not exceed stadium capacities.

  - **stadium_ID**: Links capacity to stadiums, ensuring that each stadium's capacity is correctly applied in the optimization model.
  - **capacity**: Represents the maximum number of attendees a stadium can accommodate, serving as a constraint bound in the optimization model.

### Current Stored Values  
```sql
-- Iteration 2 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical stadium capacities and expected attendance for concerts of varying popularity, ensuring a mix of small, medium, and large events.

-- Realistic data for concert_stadium_mapping
INSERT INTO concert_stadium_mapping (concert_ID, stadium_ID) VALUES (1, 101);
INSERT INTO concert_stadium_mapping (concert_ID, stadium_ID) VALUES (2, 102);
INSERT INTO concert_stadium_mapping (concert_ID, stadium_ID) VALUES (3, 103);

-- Realistic data for attendance_coefficients
INSERT INTO attendance_coefficients (concert_ID, stadium_ID, coefficient) VALUES (1, 101, 120.0);
INSERT INTO attendance_coefficients (concert_ID, stadium_ID, coefficient) VALUES (1, 102, 180.0);
INSERT INTO attendance_coefficients (concert_ID, stadium_ID, coefficient) VALUES (2, 102, 250.0);
INSERT INTO attendance_coefficients (concert_ID, stadium_ID, coefficient) VALUES (2, 103, 300.0);
INSERT INTO attendance_coefficients (concert_ID, stadium_ID, coefficient) VALUES (3, 103, 400.0);

-- Realistic data for stadium_capacity
INSERT INTO stadium_capacity (stadium_ID, capacity) VALUES (101, 5000);
INSERT INTO stadium_capacity (stadium_ID, capacity) VALUES (102, 10000);
INSERT INTO stadium_capacity (stadium_ID, capacity) VALUES (103, 15000);
```