# Complete Optimization Problem and Solution: railway

## 1. Problem Context and Goals

### Context  
In the railway management system, the primary challenge is to efficiently allocate managers to railways to minimize the total cost of management while ensuring operational effectiveness. Each manager has a specific cost associated with their level, which directly impacts the total management cost. Additionally, each manager has a maximum capacity for the number of railways they can manage, ensuring that no manager is overburdened. The decision to assign a manager to a railway is binary—either a manager is assigned to a railway or they are not. The goal is to make these assignments in a way that minimizes the total cost while ensuring that every railway is managed by at least one manager and no manager exceeds their capacity.

The business configuration includes two key parameters: the cost associated with a manager's level, which is used to calculate the total cost in the objective function, and the maximum number of railways a manager can manage, which is used to enforce capacity constraints. These parameters are critical in ensuring that the optimization model aligns with real-world operational requirements.

### Goals  
The primary goal of this optimization problem is to minimize the total cost of assigning managers to railways. This cost is calculated based on the cost associated with each manager's level and the number of railways they are assigned to manage. Success is measured by achieving the lowest possible total cost while ensuring that all railways are managed and no manager exceeds their capacity. The optimization process will use the current operational data, including manager costs and capacities, to determine the most cost-effective assignment of managers to railways.

## 2. Constraints    

The optimization problem is subject to two key constraints:

1. **Railway Management Constraint**: Each railway must be managed by at least one manager. This ensures that all railways have the necessary oversight and operational support.

2. **Manager Capacity Constraint**: No manager can be assigned to more railways than their specified capacity. This prevents overburdening managers and ensures that they can effectively manage their assigned railways.

These constraints are designed to ensure that the solution is both operationally feasible and aligned with the business requirements. They are expressed in a way that naturally leads to a linear mathematical formulation, avoiding any nonlinear relationships such as variable products or divisions.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating tables for manager capacity and manager-railway assignments, modifying the manager table to include cost per level, and adding business configuration logic for scalar parameters and formulas.

CREATE TABLE manager (
  manager_id INTEGER,
  cost_per_level INTEGER
);

CREATE TABLE manager_capacity (
  manager_id INTEGER,
  capacity INTEGER
);

CREATE TABLE manager_railway_assignment (
  manager_id INTEGER,
  railway_id INTEGER,
  assignment_status BOOLEAN
);
```

### Data Dictionary  
- **manager**: This table stores information about managers, including their unique identifier and the cost associated with their level. The cost per level is used in the objective function to calculate the total cost of management.
  - **manager_id**: A unique identifier for each manager.
  - **cost_per_level**: The cost associated with a manager's level, used to calculate the total cost in the objective function.

- **manager_capacity**: This table stores the maximum number of railways each manager can manage. This information is used to enforce the capacity constraint in the optimization model.
  - **manager_id**: A unique identifier for each manager.
  - **capacity**: The maximum number of railways a manager can manage, used in the capacity constraint.

- **manager_railway_assignment**: This table stores the binary decision variables indicating whether a manager is assigned to a railway. These variables are used in the optimization model to determine the optimal assignment of managers to railways.
  - **manager_id**: A unique identifier for each manager.
  - **railway_id**: A unique identifier for each railway.
  - **assignment_status**: A binary variable indicating whether a manager is assigned to a railway (true) or not (false).

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical railway management scenarios, ensuring realistic costs, capacities, and assignments that align with business logic and optimization requirements.

-- Realistic data for manager
INSERT INTO manager (manager_id, cost_per_level) VALUES (1, 120);
INSERT INTO manager (manager_id, cost_per_level) VALUES (2, 150);
INSERT INTO manager (manager_id, cost_per_level) VALUES (3, 200);

-- Realistic data for manager_capacity
INSERT INTO manager_capacity (manager_id, capacity) VALUES (1, 4);
INSERT INTO manager_capacity (manager_id, capacity) VALUES (2, 6);
INSERT INTO manager_capacity (manager_id, capacity) VALUES (3, 8);

-- Realistic data for manager_railway_assignment
INSERT INTO manager_railway_assignment (manager_id, railway_id, assignment_status) VALUES (1, 1, True);
INSERT INTO manager_railway_assignment (manager_id, railway_id, assignment_status) VALUES (2, 2, True);
INSERT INTO manager_railway_assignment (manager_id, railway_id, assignment_status) VALUES (3, 3, True);
```