# Complete Optimization Problem and Solution: decoration_competition

## 1. Problem Context and Goals

### Context  
In a competitive decoration event, colleges send their members to participate in various themed rounds. The primary decision involves determining which members should participate in which rounds. Each member's participation is represented as a binary decision, indicating whether they are involved in a particular round. The objective is to maximize the total rank points accumulated by the college, which is calculated by summing the rank points earned by each participating member across all rounds. The rank points are derived from a formula that converts a member's rank position into rank points, ensuring a linear relationship. Each member is allowed to participate in a maximum of three rounds, providing a constraint that aligns with the operational parameters. This setup ensures that the problem is structured for linear optimization, focusing on maximizing the college's total rank performance.

### Goals  
The optimization goal is to maximize the total rank points earned by the college's members. This involves selecting the optimal set of member participations across rounds to achieve the highest possible sum of rank points. The success of this optimization is measured by the total rank points, which are calculated by multiplying the rank points associated with each member's participation in a round. The aim is to clearly articulate this linear optimization goal in natural language, focusing on maximizing the college's overall performance in the competition.

## 2. Constraints    

The constraints for this optimization problem are straightforward and align with linear mathematical forms. Each member can participate in a limited number of rounds, specifically up to three, ensuring that the total participation for each member does not exceed this limit. Additionally, the participation decision for each member in a round is binary, meaning a member either participates or does not participate in a given round. These constraints are expressed in business terms that naturally lead to linear mathematical formulations, avoiding any nonlinear relationships.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating new tables for decision variables and objective coefficients, modifying existing tables to improve mapping adequacy, and updating business configuration logic for scalar parameters and formulas.

CREATE TABLE DecisionVariables (
  round_id INTEGER,
  member_id INTEGER,
  participation BOOLEAN
);

CREATE TABLE ObjectiveCoefficients (
  round_id INTEGER,
  member_id INTEGER,
  rank_points FLOAT
);
```

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

- **DecisionVariables Table**: This table tracks the participation of members in various rounds. Each entry indicates whether a member is participating in a specific round, serving as a binary decision variable in the optimization process.
  - **round_id**: Represents the identifier for each round, used as an index for decision variables.
  - **member_id**: Represents the identifier for each member, used as an index for decision variables.
  - **participation**: A binary indicator showing if a member is participating in a round, crucial for decision-making in the optimization.

- **ObjectiveCoefficients Table**: This table stores the rank points associated with each member's participation in a round, which are used as coefficients in the optimization objective.
  - **round_id**: Represents the identifier for each round, used as an index for objective coefficients.
  - **member_id**: Represents the identifier for each member, used as an index for objective coefficients.
  - **rank_points**: The rank points earned by a member in a round, serving as a coefficient in the objective function to maximize total rank points.

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical competition structures, ensuring a variety of participation and rank points to reflect realistic scenarios.

-- Realistic data for DecisionVariables
INSERT INTO DecisionVariables (round_id, member_id, participation) VALUES (1, 101, True);
INSERT INTO DecisionVariables (round_id, member_id, participation) VALUES (2, 102, True);
INSERT INTO DecisionVariables (round_id, member_id, participation) VALUES (3, 103, False);
INSERT INTO DecisionVariables (round_id, member_id, participation) VALUES (1, 102, True);
INSERT INTO DecisionVariables (round_id, member_id, participation) VALUES (2, 101, False);

-- Realistic data for ObjectiveCoefficients
INSERT INTO ObjectiveCoefficients (round_id, member_id, rank_points) VALUES (1, 101, 25.0);
INSERT INTO ObjectiveCoefficients (round_id, member_id, rank_points) VALUES (2, 102, 30.0);
INSERT INTO ObjectiveCoefficients (round_id, member_id, rank_points) VALUES (3, 103, 0.0);
INSERT INTO ObjectiveCoefficients (round_id, member_id, rank_points) VALUES (1, 102, 20.0);
INSERT INTO ObjectiveCoefficients (round_id, member_id, rank_points) VALUES (2, 101, 0.0);
```