# 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.  


### Retrieved Values

**Query 1: This data is crucial for the objective function, as minimizing unmet demands is a primary goal.**

```sql
SELECT station_id, demand_count FROM unmet_demand;
```

**Results (CSV format):**
```csv
station_id,demand_count
1,2
2,0
3,1
```

**Query 2: This data is essential for calculating the total cost of bike movements, which is part of the objective function.**

```sql
SELECT from_station_id, to_station_id, movement_count FROM bike_movements;
```

**Results (CSV format):**
```csv
from_station_id,to_station_id,movement_count
1,2,3
2,3,2
3,1,1
```

**Query 3: This data is necessary for the station capacity constraint, ensuring that the number of bikes moved into a station does not exceed its capacity.**

```sql
SELECT station_id, capacity FROM dock_capacity;
```

**Results (CSV format):**
```csv
station_id,capacity
1,15
2,20
3,10
```

**Query 4: This data is needed for the initial bike availability constraint, ensuring that the number of bikes moved out of a station does not exceed its initial bike count.**

```sql
SELECT from_station_id, SUM(movement_count) AS total_bikes_moved_out FROM bike_movements GROUP BY from_station_id;
```

**Results (CSV format):**
```csv
from_station_id,total_bikes_moved_out
1,3
2,2
3,1
```

**Query 5: This data is needed for the station capacity constraint, ensuring that the number of bikes moved into a station does not exceed its capacity.**

```sql
SELECT to_station_id, SUM(movement_count) AS total_bikes_moved_in FROM bike_movements GROUP BY to_station_id;
```

**Results (CSV format):**
```csv
to_station_id,total_bikes_moved_in
1,1
2,3
3,2
```

**Query 6: This summary statistic is useful for evaluating the overall performance of the optimization solution.**

```sql
SELECT SUM(demand_count) AS total_unmet_demand FROM unmet_demand;
```

**Results (CSV format):**
```csv
total_unmet_demand
3
```

**Query 7: This summary statistic is crucial for evaluating the cost component of the objective function.**

```sql
SELECT SUM(movement_count) * 3.00 AS total_movement_cost FROM bike_movements;
```

**Results (CSV format):**
```csv
total_movement_cost
18.0
```

**Query 8: Note: This assumes that the initial bike availability is derived from the sum of bikes moved out and the current bike count, which may require additional data or assumptions.**

```sql
SELECT from_station_id, SUM(movement_count) AS initial_bike_availability FROM bike_movements GROUP BY from_station_id;
```

**Results (CSV format):**
```csv
from_station_id,initial_bike_availability
1,3
2,2
3,1
```

**Query 9: This summary statistic is useful for understanding the overall scale of bike movements in the system.**

```sql
SELECT SUM(movement_count) AS total_bikes_moved FROM bike_movements;
```

**Results (CSV format):**
```csv
total_bikes_moved
6
```

**Query 10: This data is useful for verifying the station capacity constraint, ensuring that the number of bikes moved into a station does not exceed its capacity.**

```sql
SELECT dc.station_id, dc.capacity, COALESCE(SUM(bm.movement_count), 0) AS total_bikes_moved_in FROM dock_capacity dc LEFT JOIN bike_movements bm ON dc.station_id = bm.to_station_id GROUP BY dc.station_id, dc.capacity;
```

**Results (CSV format):**
```csv
station_id,capacity,total_bikes_moved_in
1,15,1
2,20,3
3,10,2
```

