# Complete Optimization Problem and Solution: bike_1

## 1. Problem Context and Goals

### Context  
The bike-sharing system operates across multiple stations, each with a limited capacity to hold bikes. The system faces challenges in meeting trip demands due to uneven bike distribution across stations. To address this, the business aims to optimize bike redistribution by moving bikes between stations. The key decisions involve determining the number of bikes to move from one station to another and identifying the number of unmet trip demands at each station.  

The operational parameters include the cost incurred per bike moved between stations, which is set at $3.00 per bike. This cost reflects realistic expenses for labor, fuel, and maintenance. The system must also respect the dock capacity of each station, which limits the number of bikes a station can hold, and the initial bike availability at each station, which determines how many bikes can be moved from a station.  

The goal is to minimize the total number of unmet trip demands and the total cost of bike movements while ensuring that station capacities and initial bike counts are not exceeded. This problem naturally aligns with a linear optimization formulation, as all relationships and constraints are additive and proportional, avoiding nonlinear complexities such as variable products or divisions.

### Goals  
The primary optimization goal is to minimize the combined impact of unmet trip demands and the cost of bike movements. Specifically, the system aims to reduce the total number of unmet trip demands across all stations and the total cost incurred from moving bikes between stations. Success is measured by achieving the lowest possible sum of unmet demands and movement costs, ensuring that operational constraints are respected.  

This goal aligns with the available data, such as the cost per bike movement and station capacities, which serve as coefficients in the optimization process. The focus is on making precise operational decisions that lead to a linear and scalable solution.

## 2. Constraints  

The optimization problem is subject to the following constraints:  
1. **Station Capacity Constraint**: The total number of bikes moved into any station must not exceed its dock capacity. This ensures that no station is overloaded with bikes beyond its physical limits.  
2. **Initial Bike Availability Constraint**: The total number of bikes moved out of any station must not exceed the initial number of bikes available at that station. This ensures that bike movements are feasible based on the current inventory at each station.  

These constraints are naturally linear, as they involve simple sums and comparisons without requiring variable products or divisions.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 3 Database Schema
-- Objective: Added dock_capacity table to address missing optimization requirement, updated business configuration logic to include cost_per_bike_movement as a scalar parameter, and ensured all mappings are complete and consistent with OR expert's analysis.

CREATE TABLE unmet_demand (
  station_id INTEGER,
  demand_count INTEGER
);

CREATE TABLE bike_movements (
  from_station_id INTEGER,
  to_station_id INTEGER,
  movement_count INTEGER
);

CREATE TABLE dock_capacity (
  station_id INTEGER,
  capacity INTEGER
);
```

### Data Dictionary  
- **unmet_demand**: Tracks the number of unmet trip demands at each station.  
  - *station_id*: Unique identifier for the station.  
  - *demand_count*: Number of unmet trip demands at the station.  

- **bike_movements**: Tracks the number of bikes moved between stations.  
  - *from_station_id*: Station where bikes are moved from.  
  - *to_station_id*: Station where bikes are moved to.  
  - *movement_count*: Number of bikes moved between the two stations.  

- **dock_capacity**: Tracks the maximum number of bikes each station can hold.  
  - *station_id*: Unique identifier for the station.  
  - *capacity*: Maximum number of bikes the station can hold.  

### Current Stored Values  
```sql
-- Iteration 3 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on realistic bike-sharing system operations, considering typical station capacities, demand patterns, and movement costs.

-- Realistic data for unmet_demand
INSERT INTO unmet_demand (station_id, demand_count) VALUES (1, 2);
INSERT INTO unmet_demand (station_id, demand_count) VALUES (2, 0);
INSERT INTO unmet_demand (station_id, demand_count) VALUES (3, 1);

-- Realistic data for bike_movements
INSERT INTO bike_movements (from_station_id, to_station_id, movement_count) VALUES (1, 2, 3);
INSERT INTO bike_movements (from_station_id, to_station_id, movement_count) VALUES (2, 3, 2);
INSERT INTO bike_movements (from_station_id, to_station_id, movement_count) VALUES (3, 1, 1);

-- Realistic data for dock_capacity
INSERT INTO dock_capacity (station_id, capacity) VALUES (1, 15);
INSERT INTO dock_capacity (station_id, capacity) VALUES (2, 20);
INSERT INTO dock_capacity (station_id, capacity) VALUES (3, 10);
```