# Complete Optimization Problem and Solution: musical

## 1. Problem Context and Goals

### Context  
The theater company is focused on optimizing the casting process for its musicals. The primary decision involves assigning actors to musicals, where each assignment is represented by a binary decision variable indicating whether a specific actor is cast in a particular musical. The objective is to minimize the total age of the cast, which is achieved by summing the ages of all actors assigned to musicals. Each musical has a predefined number of actors required, and each actor can only be assigned to one musical. This setup ensures that the optimization problem remains linear, as it involves straightforward summation and assignment without any nonlinear operations like multiplication or division of variables. The operational parameters, such as the age of actors and the number of actors required for each musical, are clearly defined and mapped to the relevant data sources. The business configuration includes these parameters to guide the optimization process effectively.

### Goals  
The primary goal of the optimization is to minimize the total age of the actors assigned to the musicals. This involves selecting actors in such a way that the sum of their ages is as low as possible while still meeting the casting requirements of each musical. Success in this optimization is measured by achieving the lowest possible total age, using the ages of actors as coefficients in the objective function. The goal is articulated in natural language to ensure clarity and alignment with the linear optimization framework.

## 2. Constraints    

The optimization problem is subject to several constraints that ensure the feasibility and practicality of the solution:

- Each musical must have exactly the number of actors it requires. This means that the sum of assignments for each musical should equal the number of actors needed for that musical.
- An actor can only be assigned to one musical. This constraint ensures that the sum of assignments for each actor across all musicals does not exceed one.

These constraints are expressed in business terms that naturally translate into linear mathematical forms, ensuring that the optimization remains within the linear framework.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 2 Database Schema
-- Objective: Incorporated actor ages into the schema to complete the linear formulation and updated business configuration logic for scalar parameters.

CREATE TABLE musical_requirements (
  musical_id INTEGER,
  required_actors INTEGER
);

CREATE TABLE actor_musical_assignment (
  actor_id INTEGER,
  musical_id INTEGER,
  assignment BOOLEAN
);

CREATE TABLE actor_details (
  actor_id INTEGER,
  age INTEGER
);
```

### Data Dictionary  
The data dictionary provides a comprehensive overview of the tables and columns used in the optimization process, highlighting their business purposes and roles in the optimization:

- **Musical Requirements Table**: This table defines the number of actors required for each musical. It plays a crucial role in setting the constraints for the optimization problem, ensuring that each musical has the necessary number of actors.
  - **musical_id**: Serves as a unique identifier for each musical, linking it to the required number of actors.
  - **required_actors**: Specifies the exact number of actors needed for each musical, forming the basis for the constraint on actor assignments.

- **Actor Musical Assignment Table**: This table tracks which actors are assigned to which musicals. It is central to the decision-making process in the optimization, representing the binary decision variables.
  - **actor_id**: A unique identifier for each actor, linking them to their potential assignments.
  - **musical_id**: Connects each assignment to a specific musical, ensuring that the assignment decisions are correctly applied.
  - **assignment**: A binary indicator showing whether an actor is assigned to a musical, forming the core of the decision variables in the optimization.

- **Actor Details Table**: This table stores information about the actors, including their ages, which are used as coefficients in the objective function.
  - **actor_id**: Links each actor to their details, ensuring that age information is correctly applied in the optimization.
  - **age**: Represents the age of each actor, used as a coefficient in the objective function to minimize the total age of the cast.

### Current Stored Values  
```sql
-- Iteration 2 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical age ranges for actors and realistic casting requirements for musicals, ensuring a diverse age distribution and varied musical requirements.

-- Realistic data for musical_requirements
INSERT INTO musical_requirements (musical_id, required_actors) VALUES (1, 3);
INSERT INTO musical_requirements (musical_id, required_actors) VALUES (2, 4);
INSERT INTO musical_requirements (musical_id, required_actors) VALUES (3, 5);

-- Realistic data for actor_musical_assignment
INSERT INTO actor_musical_assignment (actor_id, musical_id, assignment) VALUES (101, 1, True);
INSERT INTO actor_musical_assignment (actor_id, musical_id, assignment) VALUES (102, 2, True);
INSERT INTO actor_musical_assignment (actor_id, musical_id, assignment) VALUES (103, 3, True);
INSERT INTO actor_musical_assignment (actor_id, musical_id, assignment) VALUES (104, 2, False);
INSERT INTO actor_musical_assignment (actor_id, musical_id, assignment) VALUES (105, 3, False);

-- Realistic data for actor_details
INSERT INTO actor_details (actor_id, age) VALUES (101, 25);
INSERT INTO actor_details (actor_id, age) VALUES (102, 30);
INSERT INTO actor_details (actor_id, age) VALUES (103, 35);
```