# Complete Optimization Problem and Solution: decoration_competition

## 1. Problem Context and Goals

### Context  
In a college decoration competition, each college sends members to participate in rounds with different themes. The goal is to maximize the overall ranking of a college by strategically assigning members to rounds based on their strengths in specific themes. The decision to assign a member to a round is represented by a binary indicator, which is either true (assigned) or false (not assigned).  

The ranking points a member achieves in a specific round are determined by their performance in that round. These ranking points are used to calculate the total ranking points for the college, which is the metric to be maximized.  

Operational parameters include:  
- **Member Limit**: Each member can participate in a maximum of 2 rounds to ensure balanced participation and prevent overcommitment.  
- **Round Requirement**: Each round requires exactly 2 participants to ensure healthy competition and engagement.  

The business logic for calculating ranking points is straightforward: the ranking points a member achieves in a specific round are directly used in the objective function to maximize the total ranking points.  

### Goals  
The optimization goal is to maximize the total ranking points of the college by assigning members to rounds in a way that leverages their strengths in specific decoration themes. Success is measured by the sum of the ranking points achieved by all assigned members across all rounds.  

## 2. Constraints  

The optimization problem is subject to the following constraints:  
1. **Member Participation Limit**: Each member can be assigned to no more than 2 rounds. This ensures that no member is overcommitted and participation is balanced across all members.  
2. **Round Participant Requirement**: Each round must have exactly 2 participants. This ensures that every round has the required number of participants to maintain competition and engagement.  

These constraints are designed to ensure fair and balanced participation while achieving the optimization goal.  

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 2 Database Schema
-- Objective: Added a new table for ranking points to address the missing optimization requirement. Updated business configuration logic to include scalar parameters and formulas. Ensured all tables meet the 3-row minimum rule.

CREATE TABLE assignment_indicator (
  Member_ID INTEGER,
  Round_ID INTEGER,
  Assignment_Indicator BOOLEAN
);

CREATE TABLE member_limit (
  Member_ID INTEGER,
  Member_Limit INTEGER
);

CREATE TABLE round_requirement (
  Round_ID INTEGER,
  Round_Requirement INTEGER
);

CREATE TABLE rank_in_round (
  Member_ID INTEGER,
  Round_ID INTEGER,
  Rank_in_Round INTEGER
);
```

### Data Dictionary  
- **assignment_indicator**: Tracks which members are assigned to which rounds.  
  - **Member_ID**: Unique identifier for a member.  
  - **Round_ID**: Unique identifier for a round.  
  - **Assignment_Indicator**: Indicates if the member is assigned to the round (true or false).  

- **member_limit**: Stores the maximum number of rounds each member can participate in.  
  - **Member_ID**: Unique identifier for a member.  
  - **Member_Limit**: The maximum number of rounds the member can participate in.  

- **round_requirement**: Stores the number of participants required for each round.  
  - **Round_ID**: Unique identifier for a round.  
  - **Round_Requirement**: The number of participants required for the round.  

- **rank_in_round**: Stores the ranking points a member achieves in a specific round.  
  - **Member_ID**: Unique identifier for a member.  
  - **Round_ID**: Unique identifier for a round.  
  - **Rank_in_Round**: The ranking points a member achieves in a specific round.  

### Current Stored Values  
```sql
-- Iteration 2 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on realistic scenarios in a college decoration competition, considering the strengths of members in specific themes and the constraints on participation.

-- Realistic data for assignment_indicator
INSERT INTO assignment_indicator (Member_ID, Round_ID, Assignment_Indicator) VALUES (1, 1, True);
INSERT INTO assignment_indicator (Member_ID, Round_ID, Assignment_Indicator) VALUES (2, 2, True);
INSERT INTO assignment_indicator (Member_ID, Round_ID, Assignment_Indicator) VALUES (3, 3, True);

-- Realistic data for member_limit
INSERT INTO member_limit (Member_ID, Member_Limit) VALUES (1, 2);
INSERT INTO member_limit (Member_ID, Member_Limit) VALUES (2, 2);
INSERT INTO member_limit (Member_ID, Member_Limit) VALUES (3, 2);

-- Realistic data for round_requirement
INSERT INTO round_requirement (Round_ID, Round_Requirement) VALUES (1, 2);
INSERT INTO round_requirement (Round_ID, Round_Requirement) VALUES (2, 2);
INSERT INTO round_requirement (Round_ID, Round_Requirement) VALUES (3, 2);

-- Realistic data for rank_in_round
INSERT INTO rank_in_round (Member_ID, Round_ID, Rank_in_Round) VALUES (1, 1, 15);
INSERT INTO rank_in_round (Member_ID, Round_ID, Rank_in_Round) VALUES (2, 2, 20);
INSERT INTO rank_in_round (Member_ID, Round_ID, Rank_in_Round) VALUES (3, 3, 25);
```