# Complete Optimization Problem and Solution: bike_1

## 1. Problem Context and Goals

### Context  
In the urban bike-sharing system, the primary business decision involves determining the optimal allocation of bikes across various stations to ensure efficient operation. The decision variables are the number of bikes in shortage and excess at each station. The operational parameters are designed to align with the objective of minimizing the total penalty costs associated with bike shortages and excesses. The business configuration includes a penalty cost for each bike shortage at a station, which is used to minimize shortage costs, and a penalty cost for each excess bike at a station, which is used to minimize excess costs. Additionally, the total number of bikes available in the system serves as a constraint bound in the optimization model. The relationships between these elements are linear, ensuring that the optimization problem can be solved using linear programming techniques. The data presented reflects current operational information, focusing on precise decision-making that leads to linear formulations. Resource limitations are expressed through linear constraints, avoiding scenarios that require nonlinear relationships. Specific operational parameters are mapped to expected coefficient sources, with business configuration parameters referenced appropriately.

### Goals  
The goal of the optimization is to minimize the total penalty costs associated with bike shortages and excesses across all stations. This is achieved by optimizing the metric that sums the product of the shortage penalty and the number of bikes in shortage, and the product of the excess penalty and the number of bikes in excess. Success is measured by the reduction in these penalty costs, aligning with the expected coefficient sources. The optimization goal is described in natural language, focusing on minimizing the total penalty costs without using mathematical formulas or symbolic notation.

## 2. Constraints    

The constraints for this optimization problem are designed to ensure that the allocation of bikes adheres to the system's operational limits. The total number of bikes allocated as shortages and excesses across all stations must equal the total number of bikes available in the system. Each station must have a non-negative number of bikes in shortage and excess, ensuring that no station reports a negative value for these variables. Additionally, the sum of the number of bikes in shortage and the expected demand at each station must not exceed the station's dock capacity. These constraints are expressed in business terms that naturally lead to linear mathematical forms, avoiding any variable products or divisions.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating new tables for penalty costs and expected demand, modifying existing tables to include missing data, and updating configuration logic for scalar parameters and formulas.

CREATE TABLE station (
  station_id INTEGER,
  dock_count INTEGER,
  shortage FLOAT,
  excess FLOAT
);

CREATE TABLE expected_demand (
  station_id INTEGER,
  expected_demand INTEGER
);
```

### Data Dictionary  
The data dictionary provides a comprehensive mapping of tables and columns to their business purposes and optimization roles. The "station" table stores information about each bike station, with columns for the station ID, dock count, shortage, and excess. The station ID uniquely identifies each station and links to optimization processes. The dock count represents the maximum number of bikes that can be docked at the station, serving as a constraint bound for bike allocation. The shortage and excess columns represent the decision variables for the number of bikes short or in excess at each station. The "expected_demand" table stores the expected demand for bikes at each station, with columns for the station ID and expected demand. The expected demand is used to forecast demand in the optimization process, linking demand data to stations.

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical urban bike-sharing systems, considering average station sizes, expected demand patterns, and penalty costs that reflect operational priorities.

-- Realistic data for station
INSERT INTO station (station_id, dock_count, shortage, excess) VALUES (1, 15, 0, 2);
INSERT INTO station (station_id, dock_count, shortage, excess) VALUES (2, 20, 1, 0);
INSERT INTO station (station_id, dock_count, shortage, excess) VALUES (3, 10, 0, 1);

-- Realistic data for expected_demand
INSERT INTO expected_demand (station_id, expected_demand) VALUES (1, 12);
INSERT INTO expected_demand (station_id, expected_demand) VALUES (2, 18);
INSERT INTO expected_demand (station_id, expected_demand) VALUES (3, 8);
```