# Complete Optimization Problem and Solution: theme_gallery

## 1. Problem Context and Goals

### Context  
The theme gallery operates with the goal of maximizing its annual revenue from exhibitions. To achieve this, the gallery must strategically allocate artists to exhibitions based on their popularity and the ticket prices of the exhibitions. Each artist has a limited availability, and each exhibition requires a minimum number of artists to ensure its success. The gallery’s decision-making process involves determining the number of exhibitions each artist should participate in during the year. This decision is influenced by two key factors: the ticket price of each exhibition and the expected attendance for each artist in each exhibition. The ticket price for each exhibition is a fixed value, and the expected attendance is based on historical data and artist popularity. The gallery must ensure that no artist is overbooked and that each exhibition meets its minimum artist requirement. These operational parameters are designed to align with a linear optimization framework, ensuring that the relationships between variables are straightforward and avoid any nonlinear complexities such as variable products or divisions.

### Goals  
The primary goal of the optimization is to maximize the gallery’s total annual revenue from exhibitions. This revenue is calculated by multiplying the ticket price of each exhibition by the expected attendance for each artist in that exhibition and then summing these values across all artists and exhibitions. Success is measured by the total revenue generated, which directly depends on the strategic allocation of artists to exhibitions. The optimization process ensures that this allocation respects the constraints of artist availability and exhibition requirements, leading to a feasible and profitable solution.

## 2. Constraints  

The optimization problem is subject to the following constraints:  
1. **Artist Availability Constraint**: Each artist can participate in a limited number of exhibitions per year. The total number of exhibitions an artist is assigned to must not exceed their maximum allowed number of exhibitions.  
2. **Exhibition Requirement Constraint**: Each exhibition must have a minimum number of artists participating to ensure its success. The total number of artists assigned to an exhibition must meet or exceed this minimum requirement.  
3. **Non-Negativity Constraint**: The number of exhibitions an artist participates in must be a non-negative integer, ensuring that the solution is realistic and implementable.  

These constraints are designed to reflect real-world operational limitations and are expressed in a way that naturally leads to a linear mathematical formulation.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 2 Database Schema
-- Objective: Schema changes include creating tables for missing optimization requirements (Ticket_Price_j and Attendance_ij). Business configuration logic updated with scalar parameters and formulas for optimization constraints and calculations.

CREATE TABLE artist_exhibition_limits (
  Artist_ID INTEGER,
  Max_Exhibitions INTEGER
);

CREATE TABLE exhibition_artist_requirements (
  Exhibition_ID INTEGER,
  Min_Artists INTEGER
);

CREATE TABLE artist_exhibition_assignments (
  Artist_ID INTEGER,
  Exhibition_ID INTEGER,
  Num_Exhibitions INTEGER
);

CREATE TABLE exhibition_ticket_prices (
  Exhibition_ID INTEGER,
  Ticket_Price INTEGER
);

CREATE TABLE artist_exhibition_attendance (
  Artist_ID INTEGER,
  Exhibition_ID INTEGER,
  Attendance INTEGER
);
```

### Data Dictionary  
- **artist_exhibition_limits**: Tracks the maximum number of exhibitions each artist can participate in.  
  - *Artist_ID*: Unique identifier for each artist.  
  - *Max_Exhibitions*: Maximum number of exhibitions the artist can participate in.  

- **exhibition_artist_requirements**: Specifies the minimum number of artists required for each exhibition.  
  - *Exhibition_ID*: Unique identifier for each exhibition.  
  - *Min_Artists*: Minimum number of artists required for the exhibition.  

- **artist_exhibition_assignments**: Records the number of exhibitions each artist participates in per year.  
  - *Artist_ID*: Unique identifier for each artist.  
  - *Exhibition_ID*: Unique identifier for each exhibition.  
  - *Num_Exhibitions*: Number of exhibitions the artist participates in.  

- **exhibition_ticket_prices**: Stores the ticket price for each exhibition.  
  - *Exhibition_ID*: Unique identifier for each exhibition.  
  - *Ticket_Price*: Ticket price for the exhibition.  

- **artist_exhibition_attendance**: Captures the expected attendance for each artist in each exhibition.  
  - *Artist_ID*: Unique identifier for each artist.  
  - *Exhibition_ID*: Unique identifier for each exhibition.  
  - *Attendance*: Expected attendance for the artist in the exhibition.  

### Current Stored Values  
```sql
-- Iteration 2 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on realistic business scenarios, considering typical ticket prices, artist popularity, and exhibition capacity. Data was generated to ensure a balanced optimization problem with feasible constraints.

-- Realistic data for artist_exhibition_limits
INSERT INTO artist_exhibition_limits (Artist_ID, Max_Exhibitions) VALUES (1, 5);
INSERT INTO artist_exhibition_limits (Artist_ID, Max_Exhibitions) VALUES (2, 6);
INSERT INTO artist_exhibition_limits (Artist_ID, Max_Exhibitions) VALUES (3, 4);

-- Realistic data for exhibition_artist_requirements
INSERT INTO exhibition_artist_requirements (Exhibition_ID, Min_Artists) VALUES (1, 3);
INSERT INTO exhibition_artist_requirements (Exhibition_ID, Min_Artists) VALUES (2, 4);
INSERT INTO exhibition_artist_requirements (Exhibition_ID, Min_Artists) VALUES (3, 3);

-- Realistic data for artist_exhibition_assignments
INSERT INTO artist_exhibition_assignments (Artist_ID, Exhibition_ID, Num_Exhibitions) VALUES (1, 1, 1);
INSERT INTO artist_exhibition_assignments (Artist_ID, Exhibition_ID, Num_Exhibitions) VALUES (2, 2, 2);
INSERT INTO artist_exhibition_assignments (Artist_ID, Exhibition_ID, Num_Exhibitions) VALUES (3, 3, 1);

-- Realistic data for exhibition_ticket_prices
INSERT INTO exhibition_ticket_prices (Exhibition_ID, Ticket_Price) VALUES (1, 50);
INSERT INTO exhibition_ticket_prices (Exhibition_ID, Ticket_Price) VALUES (2, 70);
INSERT INTO exhibition_ticket_prices (Exhibition_ID, Ticket_Price) VALUES (3, 60);

-- Realistic data for artist_exhibition_attendance
INSERT INTO artist_exhibition_attendance (Artist_ID, Exhibition_ID, Attendance) VALUES (1, 1, 100);
INSERT INTO artist_exhibition_attendance (Artist_ID, Exhibition_ID, Attendance) VALUES (2, 2, 200);
INSERT INTO artist_exhibition_attendance (Artist_ID, Exhibition_ID, Attendance) VALUES (3, 3, 150);
```