# Complete Optimization Problem and Solution: roller_coaster

## 1. Problem Context and Goals

### Context  
A global theme park chain is focused on enhancing visitor satisfaction by strategically distributing roller coasters across its parks. The decision involves determining the number of roller coasters of each type to install in each park, considering visitor satisfaction scores, budget limits, available space, and maximum roller coaster capacity per park. Each roller coaster type has a fixed cost and space requirement, which are critical for ensuring that the installation plans are financially viable and physically feasible. The goal is to make operational decisions that maximize visitor satisfaction while adhering to these constraints.  

The business configuration includes the cost of each roller coaster type, set at $500,000, and the space required for each roller coaster type, set at 2,000 square units. These parameters are used to calculate budget and space constraints, ensuring that the optimization problem remains linear and aligned with real-world operational limitations.  

### Goals  
The primary goal of this optimization problem is to maximize the total visitor satisfaction across all parks. This is achieved by determining the optimal number of roller coasters of each type to install in each park, weighted by their respective visitor satisfaction scores. Success is measured by the overall increase in visitor satisfaction, which is directly influenced by the number and type of roller coasters installed. The optimization ensures that the decisions are made within the bounds of budget, space, and maximum roller coaster capacity for each park.  

## 2. Constraints  

The optimization problem is subject to the following constraints:  
1. **Budget Constraint**: The total cost of installing roller coasters in each park must not exceed the park’s allocated budget. The cost of each roller coaster type is fixed at $500,000, and the total cost is calculated by multiplying this cost by the number of roller coasters of each type installed in the park.  
2. **Space Constraint**: The total space occupied by roller coasters in each park must not exceed the park’s available space. Each roller coaster type requires 2,000 square units of space, and the total space is calculated by multiplying this requirement by the number of roller coasters of each type installed in the park.  
3. **Maximum Roller Coasters Constraint**: The total number of roller coasters installed in each park must not exceed the park’s maximum allowed capacity. This ensures that the park’s infrastructure and operational capabilities are not overwhelmed.  

These constraints ensure that the optimization problem remains linear and aligned with the operational realities of the theme parks.  

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include creating new tables for visitor satisfaction scores, budget limits, available space, and maximum roller coasters per park. Configuration logic updates include scalar parameters for cost and space requirements of roller coaster types.

CREATE TABLE visitor_satisfaction_scores (
  park_id INTEGER,
  coaster_type STRING,
  score FLOAT,
  num_coasters INTEGER
);

CREATE TABLE park_budgets (
  park_id INTEGER,
  budget INTEGER
);

CREATE TABLE park_available_space (
  park_id INTEGER,
  space INTEGER
);

CREATE TABLE park_max_coasters (
  park_id INTEGER,
  max_coasters INTEGER
);
```

### Data Dictionary  
- **visitor_satisfaction_scores**:  
  - **park_id**: Unique identifier for each park.  
  - **coaster_type**: Type of roller coaster (e.g., Wooden, Steel, Inverted).  
  - **score**: Visitor satisfaction score for the roller coaster type in the park.  
  - **num_coasters**: Number of roller coasters of this type in the park.  

- **park_budgets**:  
  - **park_id**: Unique identifier for each park.  
  - **budget**: Budget limit for the park.  

- **park_available_space**:  
  - **park_id**: Unique identifier for each park.  
  - **space**: Available space for roller coasters in the park.  

- **park_max_coasters**:  
  - **park_id**: Unique identifier for each park.  
  - **max_coasters**: Maximum number of roller coasters allowed in the park.  

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on industry standards, realistic theme park budgets, and typical roller coaster costs and space requirements. The data ensures that the optimization problem is meaningful and solvable by providing a balance between visitor satisfaction, budget constraints, and space limitations.

-- Realistic data for visitor_satisfaction_scores
INSERT INTO visitor_satisfaction_scores (park_id, coaster_type, score, num_coasters) VALUES (1, 'Wooden', 8.5, 2);
INSERT INTO visitor_satisfaction_scores (park_id, coaster_type, score, num_coasters) VALUES (1, 'Steel', 9.0, 3);
INSERT INTO visitor_satisfaction_scores (park_id, coaster_type, score, num_coasters) VALUES (1, 'Inverted', 7.5, 1);
INSERT INTO visitor_satisfaction_scores (park_id, coaster_type, score, num_coasters) VALUES (2, 'Wooden', 8.0, 1);
INSERT INTO visitor_satisfaction_scores (park_id, coaster_type, score, num_coasters) VALUES (2, 'Steel', 9.5, 4);
INSERT INTO visitor_satisfaction_scores (park_id, coaster_type, score, num_coasters) VALUES (2, 'Inverted', 8.0, 2);
INSERT INTO visitor_satisfaction_scores (park_id, coaster_type, score, num_coasters) VALUES (3, 'Wooden', 7.0, 1);
INSERT INTO visitor_satisfaction_scores (park_id, coaster_type, score, num_coasters) VALUES (3, 'Steel', 9.0, 5);
INSERT INTO visitor_satisfaction_scores (park_id, coaster_type, score, num_coasters) VALUES (3, 'Inverted', 8.5, 3);

-- Realistic data for park_budgets
INSERT INTO park_budgets (park_id, budget) VALUES (1, 1000000);
INSERT INTO park_budgets (park_id, budget) VALUES (2, 1500000);
INSERT INTO park_budgets (park_id, budget) VALUES (3, 2000000);

-- Realistic data for park_available_space
INSERT INTO park_available_space (park_id, space) VALUES (1, 10000);
INSERT INTO park_available_space (park_id, space) VALUES (2, 15000);
INSERT INTO park_available_space (park_id, space) VALUES (3, 20000);

-- Realistic data for park_max_coasters
INSERT INTO park_max_coasters (park_id, max_coasters) VALUES (1, 5);
INSERT INTO park_max_coasters (park_id, max_coasters) VALUES (2, 7);
INSERT INTO park_max_coasters (park_id, max_coasters) VALUES (3, 10);
```