Iteration final - PROBLEM_DESCRIPTION
Sequence: 9
Timestamp: 2025-07-25 22:51:52

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 3), 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[Channel_ID, Program_ID, Time_Slot_ID] (binary)
- Operational parameters align with expected linear objective: maximize ∑(Share_in_percent[Channel_ID, Program_ID] × Rating_in_percent[Channel_ID] × x[Channel_ID, Program_ID, Time_Slot_ID])
- Business configuration includes: Maximum number of time slots per channel (used for Used in constraints), Minimum number of local programs per channel (used for Used in constraints)
- 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": "program_share",
  "iteration": 3,
  "business_context": "A media company aims to maximize the total viewership share across its channels by optimally scheduling programs. The company needs to decide which programs to broadcast on which channels and at what times of day, considering channel ratings and program shares, while ensuring linearity in the optimization model.",
  "optimization_problem_description": "The goal is to maximize the total viewership share by selecting the best combination of programs, channels, and times of day. The objective is to maximize the sum of the products of program shares and channel ratings, ensuring each program is broadcast at most once, each channel has a limited number of time slots, and programs from certain origins are prioritized.",
  "optimization_formulation": {
    "objective": "maximize \u2211(Share_in_percent[Channel_ID, Program_ID] \u00d7 Rating_in_percent[Channel_ID] \u00d7 x[Channel_ID, Program_ID, Time_Slot_ID])",
    "decision_variables": "x[Channel_ID, Program_ID, Time_Slot_ID] (binary)",
    "constraints": [
      "\u2211(x[Channel_ID, Program_ID, Time_Slot_ID]) \u2264 1 for all Program_ID (each program is broadcast at most once)",
      "\u2211(x[Channel_ID, Program_ID, Time_Slot_ID]) \u2264 Max_Time_Slots for all Channel_ID (each channel has a limited number of time slots)",
      "\u2211(x[Channel_ID, Program_ID, Time_Slot_ID] \u00d7 I[Program_ID, 'Local']) \u2265 Min_Local_Programs for all Channel_ID (each channel has a minimum number of local programs)"
    ]
  },
  "current_optimization_to_schema_mapping": {
    "objective_coefficients": {
      "Share_in_percent[Channel_ID, Program_ID]": {
        "currently_mapped_to": "program_shares.share_in_percent",
        "mapping_adequacy": "good",
        "description": "Share of viewership for a program on a channel"
      },
      "Rating_in_percent[Channel_ID]": {
        "currently_mapped_to": "channel_ratings.rating_in_percent",
        "mapping_adequacy": "good",
        "description": "Rating of a channel"
      }
    },
    "constraint_bounds": {
      "Max_Time_Slots[Channel_ID]": {
        "currently_mapped_to": "business_configuration_logic.Max_Time_Slots",
        "mapping_adequacy": "good",
        "description": "Maximum number of time slots per channel"
      },
      "Min_Local_Programs[Channel_ID]": {
        "currently_mapped_to": "business_configuration_logic.Min_Local_Programs",
        "mapping_adequacy": "good",
        "description": "Minimum number of local programs per channel"
      }
    },
    "decision_variables": {
      "x[Channel_ID, Program_ID, Time_Slot_ID]": {
        "currently_mapped_to": "broadcast_decisions.x",
        "mapping_adequacy": "good",
        "description": "Binary decision variable indicating if a program is broadcast on a channel at a specific time",
        "variable_type": "binary"
      }
    }
  },
  "missing_optimization_requirements": [],
  "iteration_status": {
    "complete": true,
    "confidence": "high",
    "next_focus": "Ready for convergence"
  }
}

FINAL DATABASE SCHEMA:
```sql
-- Iteration 3 Database Schema
-- Objective: Schema changes include adding tables for Max_Time_Slots and Min_Local_Programs to address missing optimization requirements. Configuration logic updates include scalar parameters for Max_Time_Slots and Min_Local_Programs.

CREATE TABLE time_slots (
  time_slot_id INTEGER,
  time_of_day STRING
);

CREATE TABLE program_origins (
  program_id INTEGER,
  origin STRING
);

CREATE TABLE broadcast_decisions (
  channel_id INTEGER,
  program_id INTEGER,
  time_slot_id INTEGER,
  x BOOLEAN
);

CREATE TABLE program_shares (
  channel_id INTEGER,
  program_id INTEGER,
  share_in_percent INTEGER
);

CREATE TABLE channel_ratings (
  channel_id INTEGER,
  rating_in_percent INTEGER
);


```

CURRENT STORED VALUES:
```sql
-- Iteration 3 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical media industry standards, ensuring realistic viewership shares, channel ratings, and program scheduling constraints. The data was designed to reflect a balanced mix of local and international programs, with varying time slots to maximize viewership.

-- Realistic data for time_slots
INSERT INTO time_slots (time_slot_id, time_of_day) VALUES (1, 'Morning');
INSERT INTO time_slots (time_slot_id, time_of_day) VALUES (2, 'Afternoon');
INSERT INTO time_slots (time_slot_id, time_of_day) VALUES (3, 'Evening');

-- Realistic data for program_origins
INSERT INTO program_origins (program_id, origin) VALUES (1, 'Local');
INSERT INTO program_origins (program_id, origin) VALUES (2, 'International');
INSERT INTO program_origins (program_id, origin) VALUES (3, 'Local');

-- Realistic data for broadcast_decisions
INSERT INTO broadcast_decisions (channel_id, program_id, time_slot_id, x) VALUES (1, 1, 1, 1);
INSERT INTO broadcast_decisions (channel_id, program_id, time_slot_id, x) VALUES (2, 2, 3, 1);
INSERT INTO broadcast_decisions (channel_id, program_id, time_slot_id, x) VALUES (3, 3, 2, 1);

-- Realistic data for program_shares
INSERT INTO program_shares (channel_id, program_id, share_in_percent) VALUES (1, 1, 60);
INSERT INTO program_shares (channel_id, program_id, share_in_percent) VALUES (2, 2, 70);
INSERT INTO program_shares (channel_id, program_id, share_in_percent) VALUES (3, 3, 50);

-- Realistic data for channel_ratings
INSERT INTO channel_ratings (channel_id, rating_in_percent) VALUES (1, 75);
INSERT INTO channel_ratings (channel_id, rating_in_percent) VALUES (2, 85);
INSERT INTO channel_ratings (channel_id, rating_in_percent) VALUES (3, 80);


```

DATA DICTIONARY:
{
  "tables": {
    "time_slots": {
      "business_purpose": "Available time slots for broadcasting programs",
      "optimization_role": "business_data",
      "columns": {
        "time_slot_id": {
          "data_type": "INTEGER",
          "business_meaning": "Unique identifier for a time slot",
          "optimization_purpose": "Used in decision variables",
          "sample_values": "1, 2, 3"
        },
        "time_of_day": {
          "data_type": "STRING",
          "business_meaning": "Time of day for broadcasting",
          "optimization_purpose": "Used in decision variables",
          "sample_values": "Morning, Afternoon, Evening"
        }
      }
    },
    "program_origins": {
      "business_purpose": "Origin of programs (e.g., local, international)",
      "optimization_role": "business_data",
      "columns": {
        "program_id": {
          "data_type": "INTEGER",
          "business_meaning": "Unique identifier for a program",
          "optimization_purpose": "Used in decision variables",
          "sample_values": "1, 2, 3"
        },
        "origin": {
          "data_type": "STRING",
          "business_meaning": "Origin of the program",
          "optimization_purpose": "Used in constraints",
          "sample_values": "Local, International"
        }
      }
    },
    "broadcast_decisions": {
      "business_purpose": "Binary decisions indicating if a program is broadcast on a channel at a specific time",
      "optimization_role": "decision_variables",
      "columns": {
        "channel_id": {
          "data_type": "INTEGER",
          "business_meaning": "Unique identifier for a channel",
          "optimization_purpose": "Used in decision variables",
          "sample_values": "1, 2, 3"
        },
        "program_id": {
          "data_type": "INTEGER",
          "business_meaning": "Unique identifier for a program",
          "optimization_purpose": "Used in decision variables",
          "sample_values": "1, 2, 3"
        },
        "time_slot_id": {
          "data_type": "INTEGER",
          "business_meaning": "Unique identifier for a time slot",
          "optimization_purpose": "Used in decision variables",
          "sample_values": "1, 2, 3"
        },
        "x": {
          "data_type": "BOOLEAN",
          "business_meaning": "Binary decision variable",
          "optimization_purpose": "Used in decision variables",
          "sample_values": "0, 1"
        }
      }
    },
    "program_shares": {
      "business_purpose": "Share of viewership for a program on a channel",
      "optimization_role": "objective_coefficients",
      "columns": {
        "channel_id": {
          "data_type": "INTEGER",
          "business_meaning": "Unique identifier for a channel",
          "optimization_purpose": "Used in objective coefficients",
          "sample_values": "1, 2, 3"
        },
        "program_id": {
          "data_type": "INTEGER",
          "business_meaning": "Unique identifier for a program",
          "optimization_purpose": "Used in objective coefficients",
          "sample_values": "1, 2, 3"
        },
        "share_in_percent": {
          "data_type": "INTEGER",
          "business_meaning": "Share of viewership for a program on a channel",
          "optimization_purpose": "Used in objective coefficients",
          "sample_values": "50, 60, 70"
        }
      }
    },
    "channel_ratings": {
      "business_purpose": "Rating of a channel",
      "optimization_role": "objective_coefficients",
      "columns": {
        "channel_id": {
          "data_type": "INTEGER",
          "business_meaning": "Unique identifier for a channel",
          "optimization_purpose": "Used in objective coefficients",
          "sample_values": "1, 2, 3"
        },
        "rating_in_percent": {
          "data_type": "INTEGER",
          "business_meaning": "Rating of a channel",
          "optimization_purpose": "Used in objective coefficients",
          "sample_values": "75, 80, 85"
        }
      }
    }
  }
}


BUSINESS CONFIGURATION:

BUSINESS CONFIGURATION:
{
  "Max_Time_Slots": {
    "data_type": "INTEGER",
    "business_meaning": "Maximum number of time slots per channel",
    "optimization_role": "Used in constraints",
    "configuration_type": "scalar_parameter",
    "value": 5,
    "business_justification": "This value allows each channel to have a reasonable number of time slots for broadcasting, ensuring flexibility in scheduling."
  },
  "Min_Local_Programs": {
    "data_type": "INTEGER",
    "business_meaning": "Minimum number of local programs per channel",
    "optimization_role": "Used in constraints",
    "configuration_type": "scalar_parameter",
    "value": 2,
    "business_justification": "This value ensures that each channel meets regulatory requirements and caters to local audiences, maintaining a balance with international content."
  }
}

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: program_share

## 1. Problem Context and Goals

### Context  
[Regenerate business context that naturally aligns with LINEAR optimization formulation. Ensure:]
- Business decisions match expected decision variables: x[Channel_ID, Program_ID, Time_Slot_ID] (binary)
- Operational parameters align with expected linear objective: maximize ∑(Share_in_percent[Channel_ID, Program_ID] × Rating_in_percent[Channel_ID] × x[Channel_ID, Program_ID, Time_Slot_ID])
- Business configuration includes: Maximum number of time slots per channel (used for Used in constraints), Minimum number of local programs per channel (used for Used in constraints)
- 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 ∑(Share_in_percent[Channel_ID, Program_ID] × Rating_in_percent[Channel_ID] × x[Channel_ID, Program_ID, Time_Slot_ID])
- 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[Channel_ID, Program_ID, Time_Slot_ID]) ≤ 1 for all Program_ID (each program is broadcast at most once)', '∑(x[Channel_ID, Program_ID, Time_Slot_ID]) ≤ Max_Time_Slots for all Channel_ID (each channel has a limited number of time slots)', "∑(x[Channel_ID, Program_ID, Time_Slot_ID] × I[Program_ID, 'Local']) ≥ Min_Local_Programs for all Channel_ID (each channel has a minimum number of local programs)"] (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 3 Database Schema
-- Objective: Schema changes include adding tables for Max_Time_Slots and Min_Local_Programs to address missing optimization requirements. Configuration logic updates include scalar parameters for Max_Time_Slots and Min_Local_Programs.

CREATE TABLE time_slots (
  time_slot_id INTEGER,
  time_of_day STRING
);

CREATE TABLE program_origins (
  program_id INTEGER,
  origin STRING
);

CREATE TABLE broadcast_decisions (
  channel_id INTEGER,
  program_id INTEGER,
  time_slot_id INTEGER,
  x BOOLEAN
);

CREATE TABLE program_shares (
  channel_id INTEGER,
  program_id INTEGER,
  share_in_percent INTEGER
);

CREATE TABLE channel_ratings (
  channel_id INTEGER,
  rating_in_percent INTEGER
);


```

### 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 3 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical media industry standards, ensuring realistic viewership shares, channel ratings, and program scheduling constraints. The data was designed to reflect a balanced mix of local and international programs, with varying time slots to maximize viewership.

-- Realistic data for time_slots
INSERT INTO time_slots (time_slot_id, time_of_day) VALUES (1, 'Morning');
INSERT INTO time_slots (time_slot_id, time_of_day) VALUES (2, 'Afternoon');
INSERT INTO time_slots (time_slot_id, time_of_day) VALUES (3, 'Evening');

-- Realistic data for program_origins
INSERT INTO program_origins (program_id, origin) VALUES (1, 'Local');
INSERT INTO program_origins (program_id, origin) VALUES (2, 'International');
INSERT INTO program_origins (program_id, origin) VALUES (3, 'Local');

-- Realistic data for broadcast_decisions
INSERT INTO broadcast_decisions (channel_id, program_id, time_slot_id, x) VALUES (1, 1, 1, 1);
INSERT INTO broadcast_decisions (channel_id, program_id, time_slot_id, x) VALUES (2, 2, 3, 1);
INSERT INTO broadcast_decisions (channel_id, program_id, time_slot_id, x) VALUES (3, 3, 2, 1);

-- Realistic data for program_shares
INSERT INTO program_shares (channel_id, program_id, share_in_percent) VALUES (1, 1, 60);
INSERT INTO program_shares (channel_id, program_id, share_in_percent) VALUES (2, 2, 70);
INSERT INTO program_shares (channel_id, program_id, share_in_percent) VALUES (3, 3, 50);

-- Realistic data for channel_ratings
INSERT INTO channel_ratings (channel_id, rating_in_percent) VALUES (1, 75);
INSERT INTO channel_ratings (channel_id, rating_in_percent) VALUES (2, 85);
INSERT INTO channel_ratings (channel_id, rating_in_percent) VALUES (3, 80);


```

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

Response:
# Complete Optimization Problem and Solution: program_share

## 1. Problem Context and Goals

### Context  
A media company is focused on maximizing its total viewership share across multiple channels by strategically scheduling programs. The company must decide which programs to broadcast on which channels and at what times of day. This decision is guided by two key operational metrics: the share of viewership each program attracts on a specific channel and the overall rating of the channel itself. The goal is to ensure that the scheduling decisions are made in a way that maximizes the combined impact of these metrics.

The company operates under specific business rules:  
1. Each program can be broadcast at most once across all channels and time slots.  
2. Each channel has a maximum number of time slots available for broadcasting, currently set to 5.  
3. Each channel must broadcast a minimum number of local programs, currently set to 2, to comply with regulatory requirements and cater to local audiences.  

These rules ensure that the scheduling process remains balanced and adheres to both operational and regulatory constraints. The decision variables are binary, indicating whether a specific program is scheduled on a particular channel at a given time slot. The objective is to maximize the total viewership share, calculated as the sum of the products of program shares and channel ratings for all scheduled programs.

### Goals  
The primary goal of this optimization problem is to maximize the total viewership share across all channels. This is achieved by selecting the best combination of programs, channels, and time slots, weighted by the program's share of viewership on the channel and the channel's overall rating. Success is measured by the total viewership share generated from the scheduled programs, ensuring that the company's content reaches the largest possible audience while adhering to operational and regulatory constraints.

## 2. Constraints  

The optimization problem is subject to the following constraints:  
1. **Program Uniqueness**: Each program can be broadcast at most once across all channels and time slots. This ensures that no program is over-scheduled, maintaining variety in the programming lineup.  
2. **Channel Time Slot Limit**: Each channel can only use a limited number of time slots for broadcasting, currently capped at 5. This ensures that no channel is overburdened with too many programs, maintaining a balanced schedule.  
3. **Local Program Requirement**: Each channel must broadcast a minimum number of local programs, currently set to 2. This ensures compliance with regulatory requirements and caters to the preferences of local audiences.  

These constraints are designed to ensure that the scheduling decisions are both operationally feasible and aligned with the company's strategic and regulatory goals.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 3 Database Schema
-- Objective: Schema changes include adding tables for Max_Time_Slots and Min_Local_Programs to address missing optimization requirements. Configuration logic updates include scalar parameters for Max_Time_Slots and Min_Local_Programs.

CREATE TABLE time_slots (
  time_slot_id INTEGER,
  time_of_day STRING
);

CREATE TABLE program_origins (
  program_id INTEGER,
  origin STRING
);

CREATE TABLE broadcast_decisions (
  channel_id INTEGER,
  program_id INTEGER,
  time_slot_id INTEGER,
  x BOOLEAN
);

CREATE TABLE program_shares (
  channel_id INTEGER,
  program_id INTEGER,
  share_in_percent INTEGER
);

CREATE TABLE channel_ratings (
  channel_id INTEGER,
  rating_in_percent INTEGER
);
```

### Data Dictionary  
- **time_slots**: Contains available time slots for broadcasting programs.  
  - `time_slot_id`: Unique identifier for a time slot.  
  - `time_of_day`: Time of day for broadcasting (e.g., Morning, Afternoon, Evening).  

- **program_origins**: Indicates the origin of programs (e.g., local, international).  
  - `program_id`: Unique identifier for a program.  
  - `origin`: Origin of the program (e.g., Local, International).  

- **broadcast_decisions**: Represents binary decisions indicating whether a program is broadcast on a channel at a specific time.  
  - `channel_id`: Unique identifier for a channel.  
  - `program_id`: Unique identifier for a program.  
  - `time_slot_id`: Unique identifier for a time slot.  
  - `x`: Binary decision variable (0 or 1).  

- **program_shares**: Contains the share of viewership for a program on a channel.  
  - `channel_id`: Unique identifier for a channel.  
  - `program_id`: Unique identifier for a program.  
  - `share_in_percent`: Share of viewership for the program on the channel.  

- **channel_ratings**: Contains the rating of a channel.  
  - `channel_id`: Unique identifier for a channel.  
  - `rating_in_percent`: Rating of the channel.  

### Current Stored Values  
```sql
-- Iteration 3 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical media industry standards, ensuring realistic viewership shares, channel ratings, and program scheduling constraints. The data was designed to reflect a balanced mix of local and international programs, with varying time slots to maximize viewership.

-- Realistic data for time_slots
INSERT INTO time_slots (time_slot_id, time_of_day) VALUES (1, 'Morning');
INSERT INTO time_slots (time_slot_id, time_of_day) VALUES (2, 'Afternoon');
INSERT INTO time_slots (time_slot_id, time_of_day) VALUES (3, 'Evening');

-- Realistic data for program_origins
INSERT INTO program_origins (program_id, origin) VALUES (1, 'Local');
INSERT INTO program_origins (program_id, origin) VALUES (2, 'International');
INSERT INTO program_origins (program_id, origin) VALUES (3, 'Local');

-- Realistic data for broadcast_decisions
INSERT INTO broadcast_decisions (channel_id, program_id, time_slot_id, x) VALUES (1, 1, 1, 1);
INSERT INTO broadcast_decisions (channel_id, program_id, time_slot_id, x) VALUES (2, 2, 3, 1);
INSERT INTO broadcast_decisions (channel_id, program_id, time_slot_id, x) VALUES (3, 3, 2, 1);

-- Realistic data for program_shares
INSERT INTO program_shares (channel_id, program_id, share_in_percent) VALUES (1, 1, 60);
INSERT INTO program_shares (channel_id, program_id, share_in_percent) VALUES (2, 2, 70);
INSERT INTO program_shares (channel_id, program_id, share_in_percent) VALUES (3, 3, 50);

-- Realistic data for channel_ratings
INSERT INTO channel_ratings (channel_id, rating_in_percent) VALUES (1, 75);
INSERT INTO channel_ratings (channel_id, rating_in_percent) VALUES (2, 85);
INSERT INTO channel_ratings (channel_id, rating_in_percent) VALUES (3, 80);
```
