# Complete Optimization Problem and Solution: Climbing

## 1. Problem Context and Goals

### Context  
In the context of a climbing competition, the organizer aims to strategically assign climbers to various mountains to maximize the overall points scored. Each climber can be assigned to only one mountain, and every mountain must have at least one climber assigned to it. The decision-making process involves determining whether a climber is assigned to a specific mountain, represented by a binary decision variable. The objective is to maximize the total points, which is calculated by summing the points scored by each climber assigned to a mountain. This linear optimization problem is driven by the need to efficiently allocate resources (climbers) to meet the competition's requirements while maximizing the scoring potential. The operational parameters include the points each climber can score, which serve as coefficients in the objective function. The business configuration involves ensuring that each climber is assigned to one mountain and that each mountain is climbed by at least one climber, aligning with the linear constraints of the problem.

### Goals  
The primary goal of this optimization problem is to maximize the total points scored in the climbing competition. This is achieved by optimizing the assignment of climbers to mountains, ensuring that the total points, calculated as the sum of the points scored by each climber assigned to a mountain, is maximized. Success is measured by the total points achieved, which directly correlates with the effective assignment of climbers based on their scoring potential. The focus is on a linear optimization goal, where the objective is to maximize the sum of the points associated with each climber's assignment.

## 2. Constraints    

The constraints for this optimization problem are designed to ensure fair and efficient allocation of climbers to mountains:

- Each climber must be assigned to exactly one mountain. This constraint ensures that no climber is left unassigned and that their scoring potential is fully utilized.
- Each mountain must have at least one climber assigned to it. This ensures that all mountains are part of the competition and contribute to the overall scoring.

These constraints are expressed in business terms that naturally lead to linear mathematical forms, ensuring that the problem remains within the realm of linear optimization.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include the creation of a decision_variables table to address missing binary variable mapping, and updates to existing tables to ensure alignment with optimization requirements.

CREATE TABLE climber (
  Climber_ID INTEGER,
  Points INTEGER
);

CREATE TABLE mountain (
  Mountain_ID INTEGER
);

CREATE TABLE decision_variables (
  Climber_ID INTEGER,
  Mountain_ID INTEGER,
  assignment BOOLEAN
);
```

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

- **Climber Table**: This table stores information about each climber, including their unique identifier and the points they can score. The points serve as coefficients in the objective function, representing the scoring potential of each climber.

- **Mountain Table**: This table contains information about each mountain, identified by a unique identifier. The mountains are the resources to which climbers are assigned, ensuring that each mountain is climbed by at least one climber.

- **Decision Variables Table**: This table captures the binary decision variables that indicate whether a climber is assigned to a specific mountain. It links climbers to mountains and plays a crucial role in the optimization process by determining the assignment of climbers.

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical climbing competition scenarios, ensuring a diverse range of points and assignments to create a meaningful optimization problem.

-- Realistic data for climber
INSERT INTO climber (Climber_ID, Points) VALUES (1, 15);
INSERT INTO climber (Climber_ID, Points) VALUES (2, 25);
INSERT INTO climber (Climber_ID, Points) VALUES (3, 10);

-- Realistic data for mountain
INSERT INTO mountain (Mountain_ID) VALUES (1);
INSERT INTO mountain (Mountain_ID) VALUES (2);
INSERT INTO mountain (Mountain_ID) VALUES (3);

-- Realistic data for decision_variables
INSERT INTO decision_variables (Climber_ID, Mountain_ID, assignment) VALUES (1, 1, True);
INSERT INTO decision_variables (Climber_ID, Mountain_ID, assignment) VALUES (2, 2, True);
INSERT INTO decision_variables (Climber_ID, Mountain_ID, assignment) VALUES (3, 3, True);
```