Iteration final - PROBLEM_DESCRIPTION
Sequence: 5
Timestamp: 2025-07-25 22:28:37

Prompt:
You are a business analyst creating structured optimization problem documentation.

DATA SOURCES EXPLANATION:
- FINAL OR ANALYSIS: Final converged optimization problem from alternating process (iteration 1), contains business context and schema mapping evaluation
- DATABASE SCHEMA: Current database structure after iterative adjustments  
- DATA DICTIONARY: Business meanings and optimization roles of tables and columns
- CURRENT STORED VALUES: Realistic business data generated by triple expert (business + data + optimization)
- BUSINESS CONFIGURATION: Scalar parameters and business logic formulas separated from table data

CRITICAL REQUIREMENTS: 
- Ensure problem description naturally leads to LINEAR or MIXED-INTEGER optimization formulation
- Make business context consistent with the intended decision variables and objectives
- Align constraint descriptions with expected mathematical constraints
- Ensure data descriptions map clearly to expected coefficient sources
- Maintain business authenticity while fixing mathematical consistency issues
- Avoid business scenarios that would naturally require nonlinear relationships (variable products, divisions, etc.)

AUTO-EXTRACTED CONTEXT REQUIREMENTS:
- Business decisions match expected decision variables: x[i,j] = binary decision variable indicating if actor i is assigned to role j
- Operational parameters align with expected linear objective: maximize ∑(w1 * age[i] + w2 * performance_duration[i]) * x[i,j]
- Business configuration includes: weight for actor's age in the engagement metric (used for coefficient in the objective function), weight for actor's performance duration in the engagement metric (used for coefficient in the objective function), maximum allowed total duration of all performances (used for bound in the duration constraint)
- Use natural language to precisely describe linear mathematical relationships
- NO mathematical formulas, equations, or symbolic notation
- Present data as current operational information
- Focus on precise operational decision-making that leads to linear formulations
- Resource limitations match expected linear constraints
- Avoid scenarios requiring variable products, divisions, or other nonlinear relationships
- Include specific operational parameters that map to expected coefficient sources
- Reference business configuration parameters where appropriate

FINAL OR ANALYSIS:
{
  "database_id": "musical",
  "iteration": 1,
  "business_context": "A theater company wants to maximize the total audience engagement across multiple musicals by optimally assigning actors to roles based on their age and performance duration, while ensuring that each actor is assigned to at most one role and each role is filled by exactly one actor, and the total duration of all performances does not exceed a given limit.",
  "optimization_problem_description": "The objective is to maximize the total audience engagement, which is a weighted sum of the actors' ages and their performance durations. The constraints ensure that each actor is assigned to at most one role, each role is filled by exactly one actor, and the total duration of all performances does not exceed a given limit.",
  "optimization_formulation": {
    "objective": "maximize \u2211(w1 * age[i] + w2 * performance_duration[i]) * x[i,j]",
    "decision_variables": "x[i,j] = binary decision variable indicating if actor i is assigned to role j",
    "constraints": [
      "\u2211x[i,j] <= 1 for all i (each actor is assigned to at most one role)",
      "\u2211x[i,j] = 1 for all j (each role is filled by exactly one actor)",
      "\u2211(performance_duration[i] * x[i,j]) <= D (total duration of all performances does not exceed D)"
    ]
  },
  "current_optimization_to_schema_mapping": {
    "objective_coefficients": {
      "w1": {
        "currently_mapped_to": "business_configuration_logic.w1",
        "mapping_adequacy": "good",
        "description": "weight for actor's age in the engagement metric"
      },
      "w2": {
        "currently_mapped_to": "business_configuration_logic.w2",
        "mapping_adequacy": "good",
        "description": "weight for actor's performance duration in the engagement metric"
      }
    },
    "constraint_bounds": {
      "D": {
        "currently_mapped_to": "business_configuration_logic.D",
        "mapping_adequacy": "good",
        "description": "maximum allowed total duration of all performances"
      }
    },
    "decision_variables": {
      "x[i,j]": {
        "currently_mapped_to": "assignments.assignment_status",
        "mapping_adequacy": "good",
        "description": "binary decision variable indicating if actor i is assigned to role j",
        "variable_type": "binary"
      }
    }
  },
  "missing_optimization_requirements": [],
  "iteration_status": {
    "complete": true,
    "confidence": "high",
    "next_focus": "Ready for convergence"
  }
}

FINAL DATABASE SCHEMA:
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating tables for roles, actors, and assignments. Configuration logic updates include adding weights for engagement metric and maximum duration.

CREATE TABLE roles (
  role_id INTEGER,
  musical_id INTEGER,
  role_name STRING
);

CREATE TABLE actors (
  actor_id INTEGER,
  age INTEGER,
  performance_duration INTEGER
);

CREATE TABLE assignments (
  assignment_id INTEGER,
  actor_id INTEGER,
  role_id INTEGER,
  assignment_status BOOLEAN
);


```

CURRENT STORED VALUES:
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on realistic theater company scenarios, considering typical actor ages, performance durations, and role assignments. The weights and maximum duration were set to balance the engagement metric and ensure feasible solutions.

-- Realistic data for roles
INSERT INTO roles (role_id, musical_id, role_name) VALUES (1, 1, 'Lead');
INSERT INTO roles (role_id, musical_id, role_name) VALUES (2, 1, 'Supporting');
INSERT INTO roles (role_id, musical_id, role_name) VALUES (3, 2, 'Chorus');

-- Realistic data for actors
INSERT INTO actors (actor_id, age, performance_duration) VALUES (1, 28, 15);
INSERT INTO actors (actor_id, age, performance_duration) VALUES (2, 35, 20);
INSERT INTO actors (actor_id, age, performance_duration) VALUES (3, 22, 10);

-- Realistic data for assignments
INSERT INTO assignments (assignment_id, actor_id, role_id, assignment_status) VALUES (1, 1, 1, True);
INSERT INTO assignments (assignment_id, actor_id, role_id, assignment_status) VALUES (2, 2, 2, True);
INSERT INTO assignments (assignment_id, actor_id, role_id, assignment_status) VALUES (3, 3, 3, True);


```

DATA DICTIONARY:
{
  "tables": {
    "roles": {
      "business_purpose": "List of roles and their corresponding musicals",
      "optimization_role": "business_data",
      "columns": {
        "role_id": {
          "data_type": "INTEGER",
          "business_meaning": "Unique identifier for each role",
          "optimization_purpose": "identifier for roles in assignments",
          "sample_values": "1, 2, 3"
        },
        "musical_id": {
          "data_type": "INTEGER",
          "business_meaning": "Identifier for the musical the role belongs to",
          "optimization_purpose": "grouping roles by musical",
          "sample_values": "1, 2, 3"
        },
        "role_name": {
          "data_type": "STRING",
          "business_meaning": "Name of the role",
          "optimization_purpose": "descriptive information",
          "sample_values": "Lead, Supporting, Chorus"
        }
      }
    },
    "actors": {
      "business_purpose": "List of actors with their ages and performance durations",
      "optimization_role": "business_data",
      "columns": {
        "actor_id": {
          "data_type": "INTEGER",
          "business_meaning": "Unique identifier for each actor",
          "optimization_purpose": "identifier for actors in assignments",
          "sample_values": "1, 2, 3"
        },
        "age": {
          "data_type": "INTEGER",
          "business_meaning": "Age of the actor",
          "optimization_purpose": "used in the engagement metric",
          "sample_values": "25, 30, 35"
        },
        "performance_duration": {
          "data_type": "INTEGER",
          "business_meaning": "Duration of the actor's performance",
          "optimization_purpose": "used in the engagement metric",
          "sample_values": "10, 15, 20"
        }
      }
    },
    "assignments": {
      "business_purpose": "Assignments of actors to roles",
      "optimization_role": "decision_variables",
      "columns": {
        "assignment_id": {
          "data_type": "INTEGER",
          "business_meaning": "Unique identifier for each assignment",
          "optimization_purpose": "identifier for assignments",
          "sample_values": "1, 2, 3"
        },
        "actor_id": {
          "data_type": "INTEGER",
          "business_meaning": "Identifier for the actor assigned",
          "optimization_purpose": "links to actors table",
          "sample_values": "1, 2, 3"
        },
        "role_id": {
          "data_type": "INTEGER",
          "business_meaning": "Identifier for the role assigned",
          "optimization_purpose": "links to roles table",
          "sample_values": "1, 2, 3"
        },
        "assignment_status": {
          "data_type": "BOOLEAN",
          "business_meaning": "Indicates if the actor is assigned to the role",
          "optimization_purpose": "binary decision variable",
          "sample_values": "true, false"
        }
      }
    }
  }
}


BUSINESS CONFIGURATION:

BUSINESS CONFIGURATION:
{
  "w1": {
    "data_type": "FLOAT",
    "business_meaning": "weight for actor's age in the engagement metric",
    "optimization_role": "coefficient in the objective function",
    "configuration_type": "scalar_parameter",
    "value": 0.6,
    "business_justification": "Higher weight on age to emphasize the importance of experience in engagement."
  },
  "w2": {
    "data_type": "FLOAT",
    "business_meaning": "weight for actor's performance duration in the engagement metric",
    "optimization_role": "coefficient in the objective function",
    "configuration_type": "scalar_parameter",
    "value": 0.4,
    "business_justification": "Lower weight on performance duration to balance the engagement metric."
  },
  "D": {
    "data_type": "INTEGER",
    "business_meaning": "maximum allowed total duration of all performances",
    "optimization_role": "bound in the duration constraint",
    "configuration_type": "scalar_parameter",
    "value": 100,
    "business_justification": "A realistic total duration limit for multiple performances."
  }
}

Business Configuration Design: 
Our system separates business logic design from value determination:
- Configuration Logic (business_configuration_logic.json): Templates designed by data engineers with sample_value for scalars and actual formulas for business logic
- Configuration Values (business_configuration.json): Realistic values determined by domain experts for scalar parameters only
- Design Rationale: Ensures business logic consistency while allowing flexible parameter tuning


TASK: Create structured markdown documentation for SECTIONS 1-3 ONLY (Problem Description).

EXACT MARKDOWN STRUCTURE TO FOLLOW:

# Complete Optimization Problem and Solution: musical

## 1. Problem Context and Goals

### Context  
[Regenerate business context that naturally aligns with LINEAR optimization formulation. Ensure:]
- Business decisions match expected decision variables: x[i,j] = binary decision variable indicating if actor i is assigned to role j
- Operational parameters align with expected linear objective: maximize ∑(w1 * age[i] + w2 * performance_duration[i]) * x[i,j]
- Business configuration includes: weight for actor's age in the engagement metric (used for coefficient in the objective function), weight for actor's performance duration in the engagement metric (used for coefficient in the objective function), maximum allowed total duration of all performances (used for bound in the duration constraint)
- Use natural language to precisely describe linear mathematical relationships
- NO mathematical formulas, equations, or symbolic notation
- Present data as current operational information
- Focus on precise operational decision-making that leads to linear formulations
- Resource limitations match expected linear constraints
- Avoid scenarios requiring variable products, divisions, or other nonlinear relationships
- Include specific operational parameters that map to expected coefficient sources
- Reference business configuration parameters where appropriate
- CRITICAL: Include ALL business configuration information (scalar parameters AND business logic formulas) in natural business language

### Goals  
[Regenerate goals that clearly lead to LINEAR mathematical objective:]
- Optimization goal: maximize
- Metric to optimize: maximize ∑(w1 * age[i] + w2 * performance_duration[i]) * x[i,j]
- Success measurement aligned with expected coefficient sources
- Use natural language to precisely describe linear optimization goal
- NO mathematical formulas, equations, or symbolic notation

## 2. Constraints    

[Regenerate constraints that directly match expected LINEAR mathematical constraints:]
- Expected constraint: ['∑x[i,j] <= 1 for all i (each actor is assigned to at most one role)', '∑x[i,j] = 1 for all j (each role is filled by exactly one actor)', '∑(performance_duration[i] * x[i,j]) <= D (total duration of all performances does not exceed D)'] (Form: Standard constraint form based on business requirements)

[Each constraint should be described in business terms that naturally lead to LINEAR mathematical forms (no variable products or divisions)]

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating tables for roles, actors, and assignments. Configuration logic updates include adding weights for engagement metric and maximum duration.

CREATE TABLE roles (
  role_id INTEGER,
  musical_id INTEGER,
  role_name STRING
);

CREATE TABLE actors (
  actor_id INTEGER,
  age INTEGER,
  performance_duration INTEGER
);

CREATE TABLE assignments (
  assignment_id INTEGER,
  actor_id INTEGER,
  role_id INTEGER,
  assignment_status BOOLEAN
);


```

### Data Dictionary  
[Create comprehensive business-oriented data dictionary mapping tables and columns to their business purposes and optimization roles - NOT technical database terms. Base this on the data_dictionary provided but rewrite in business language that clearly connects to the expected linear mathematical formulation]

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on realistic theater company scenarios, considering typical actor ages, performance durations, and role assignments. The weights and maximum duration were set to balance the engagement metric and ensure feasible solutions.

-- Realistic data for roles
INSERT INTO roles (role_id, musical_id, role_name) VALUES (1, 1, 'Lead');
INSERT INTO roles (role_id, musical_id, role_name) VALUES (2, 1, 'Supporting');
INSERT INTO roles (role_id, musical_id, role_name) VALUES (3, 2, 'Chorus');

-- Realistic data for actors
INSERT INTO actors (actor_id, age, performance_duration) VALUES (1, 28, 15);
INSERT INTO actors (actor_id, age, performance_duration) VALUES (2, 35, 20);
INSERT INTO actors (actor_id, age, performance_duration) VALUES (3, 22, 10);

-- Realistic data for assignments
INSERT INTO assignments (assignment_id, actor_id, role_id, assignment_status) VALUES (1, 1, 1, True);
INSERT INTO assignments (assignment_id, actor_id, role_id, assignment_status) VALUES (2, 2, 2, True);
INSERT INTO assignments (assignment_id, actor_id, role_id, assignment_status) VALUES (3, 3, 3, True);


```

STOP HERE - Do not include section 4 (Mathematical Optimization Formulation).

Response:
# Complete Optimization Problem and Solution: musical

## 1. Problem Context and Goals

### Context  
A theater company is planning multiple musical performances and aims to maximize audience engagement by optimally assigning actors to roles. The engagement metric is calculated as a weighted combination of each actor's age and their performance duration. The company has a set of actors, each with a specific age and performance duration, and a set of roles that need to be filled. The decision to assign an actor to a role is binary, meaning an actor is either assigned to a role or not. 

The company has established two weights to balance the importance of age and performance duration in the engagement metric. The weight for age is set to 0.6, emphasizing the value of experience, while the weight for performance duration is set to 0.4, ensuring a balanced consideration of performance length. Additionally, the company has set a maximum total duration limit of 100 minutes for all performances combined to ensure the event remains within a feasible timeframe.

The goal is to assign actors to roles in such a way that the total engagement is maximized, while ensuring that each actor is assigned to at most one role, each role is filled by exactly one actor, and the total duration of all performances does not exceed the allowed limit.

### Goals  
The primary optimization goal is to maximize the total audience engagement across all performances. This is achieved by assigning actors to roles in a way that maximizes the weighted sum of their ages and performance durations. Success is measured by the total engagement score, which is calculated using the predefined weights for age and performance duration. The solution must also respect the constraints on actor assignments and the total performance duration.

## 2. Constraints    

The optimization problem is subject to the following constraints:

1. **Actor Assignment Limit**: Each actor can be assigned to at most one role. This ensures that no actor is overcommitted and can focus on a single performance.
   
2. **Role Fulfillment**: Each role must be filled by exactly one actor. This ensures that all roles are covered and no role is left unassigned.

3. **Total Duration Limit**: The combined duration of all performances must not exceed the maximum allowed total duration of 100 minutes. This ensures that the event remains within the planned timeframe.

These constraints ensure that the assignment of actors to roles is both feasible and aligned with the company's operational requirements.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating tables for roles, actors, and assignments. Configuration logic updates include adding weights for engagement metric and maximum duration.

CREATE TABLE roles (
  role_id INTEGER,
  musical_id INTEGER,
  role_name STRING
);

CREATE TABLE actors (
  actor_id INTEGER,
  age INTEGER,
  performance_duration INTEGER
);

CREATE TABLE assignments (
  assignment_id INTEGER,
  actor_id INTEGER,
  role_id INTEGER,
  assignment_status BOOLEAN
);
```

### Data Dictionary  
- **Roles Table**: Contains information about the roles available in different musicals. Each role has a unique identifier, a musical identifier, and a role name.
  - **role_id**: Unique identifier for each role.
  - **musical_id**: Identifier for the musical the role belongs to.
  - **role_name**: Name of the role.

- **Actors Table**: Contains information about the actors, including their age and performance duration.
  - **actor_id**: Unique identifier for each actor.
  - **age**: Age of the actor, used in the engagement metric.
  - **performance_duration**: Duration of the actor's performance, used in the engagement metric.

- **Assignments Table**: Tracks the assignments of actors to roles. Each assignment has a unique identifier, an actor identifier, a role identifier, and a status indicating whether the actor is assigned to the role.
  - **assignment_id**: Unique identifier for each assignment.
  - **actor_id**: Identifier for the actor assigned.
  - **role_id**: Identifier for the role assigned.
  - **assignment_status**: Indicates if the actor is assigned to the role (True/False).

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on realistic theater company scenarios, considering typical actor ages, performance durations, and role assignments. The weights and maximum duration were set to balance the engagement metric and ensure feasible solutions.

-- Realistic data for roles
INSERT INTO roles (role_id, musical_id, role_name) VALUES (1, 1, 'Lead');
INSERT INTO roles (role_id, musical_id, role_name) VALUES (2, 1, 'Supporting');
INSERT INTO roles (role_id, musical_id, role_name) VALUES (3, 2, 'Chorus');

-- Realistic data for actors
INSERT INTO actors (actor_id, age, performance_duration) VALUES (1, 28, 15);
INSERT INTO actors (actor_id, age, performance_duration) VALUES (2, 35, 20);
INSERT INTO actors (actor_id, age, performance_duration) VALUES (3, 22, 10);

-- Realistic data for assignments
INSERT INTO assignments (assignment_id, actor_id, role_id, assignment_status) VALUES (1, 1, 1, True);
INSERT INTO assignments (assignment_id, actor_id, role_id, assignment_status) VALUES (2, 2, 2, True);
INSERT INTO assignments (assignment_id, actor_id, role_id, assignment_status) VALUES (3, 3, 3, True);
```
