# Complete Optimization Problem and Solution: book_2

## 1. Problem Context and Goals

### Context  
A publisher is tasked with maximizing revenue from book sales while adhering to strict operational constraints. The key decision involves determining the number of copies to produce for each book, considering factors such as the book's price, production costs, and market demand. The publisher operates within a fixed total production budget, ensuring that the combined cost of producing all books does not exceed this limit. Additionally, each book has a maximum number of issues that can be produced, reflecting production capacity and market saturation. The publisher must also ensure that the number of copies produced for each book meets the minimum demand required to satisfy market needs. These constraints are designed to balance financial prudence with operational feasibility, ensuring that the publisher maximizes revenue without overextending resources.

The business configuration includes a total production budget of 150,000 units and a maximum of 6,000 issues per book. These parameters are critical in guiding the decision-making process, ensuring that the publisher operates within realistic financial and production limits.

### Goals  
The primary goal of this optimization problem is to maximize the total revenue generated from book sales. Revenue is calculated by multiplying the price of each book by the number of copies produced and summing this across all books. Success is measured by achieving the highest possible revenue while staying within the constraints of the total production budget, the maximum number of issues per book, and the minimum demand requirements. This goal aligns with the publisher's objective of maximizing profitability while maintaining operational efficiency and market responsiveness.

## 2. Constraints  

The optimization problem is subject to the following constraints:  
1. **Total Production Budget**: The combined cost of producing all books must not exceed the total production budget of 150,000 units. This ensures that the publisher remains financially viable and does not overspend on production.  
2. **Maximum Issues per Book**: The number of copies produced for each book must not exceed the maximum number of issues allowed, which is 6,000 per book. This constraint reflects production capacity and market demand limits.  
3. **Minimum Demand**: The number of copies produced for each book must meet or exceed the minimum demand required to satisfy market needs. This ensures that the publisher fulfills customer expectations and maintains market presence.  

These constraints are designed to ensure that the publisher's decisions are both financially sustainable and operationally feasible, aligning with the broader business objectives.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 3 Database Schema
-- Objective: Added tables for Total_Budget and Max_Issues_i, updated business configuration logic to include scalar parameters and formulas, and ensured schema aligns with OR expert's requirements.

CREATE TABLE production_budget (
  Copies INTEGER
);

CREATE TABLE minimum_demand (
  Min_Demand INTEGER
);

CREATE TABLE production_costs (
  Cost FLOAT
);

CREATE TABLE book_prices (
  Price FLOAT
);

CREATE TABLE total_budget (
  Total_Budget INTEGER
);

CREATE TABLE max_issues (
  Max_Issues INTEGER
);
```

### Data Dictionary  
- **production_budget**: Tracks the number of copies produced for each book. This table is central to the decision-making process, as it represents the key decision variable in the optimization model.  
- **minimum_demand**: Specifies the minimum number of copies required for each book to meet market demand. This data ensures that production decisions align with customer needs.  
- **production_costs**: Contains the production cost per book, which is used to calculate the total production cost and ensure it stays within the budget.  
- **book_prices**: Stores the price of each book, which is used to calculate the total revenue generated from sales.  
- **total_budget**: Defines the total production budget available for book production, serving as a critical constraint in the optimization model.  
- **max_issues**: Specifies the maximum number of issues allowed per book, reflecting production capacity and market limits.  


### Retrieved Values

**Query 1: This is a critical constraint parameter for the optimization problem**

```sql
SELECT Total_Budget FROM total_budget;
```

**Results (CSV format):**
```csv
Total_Budget
100000
150000
200000
```

**Query 2: This is another critical constraint parameter for the optimization problem**

```sql
SELECT Max_Issues FROM max_issues;
```

**Results (CSV format):**
```csv
Max_Issues
5000
6000
7000
```

**Query 3: This is a constraint parameter that ensures the publisher fulfills customer expectations**

```sql
SELECT Min_Demand FROM minimum_demand;
```

**Results (CSV format):**
```csv
Min_Demand
500
600
700
```

**Query 4: This data is essential for ensuring the total production cost does not exceed the budget**

```sql
SELECT Cost FROM production_costs;
```

**Results (CSV format):**
```csv
Cost
10.0
12.0
15.0
```

**Query 5: This data is crucial for the objective function, which aims to maximize revenue**

```sql
SELECT Price FROM book_prices;
```

**Results (CSV format):**
```csv
Price
20.0
25.0
30.0
```

**Query 6: This data represents the decision variable in the optimization model**

```sql
SELECT Copies FROM production_budget;
```

**Results (CSV format):**
```csv
Copies
1000
1500
2000
```

**Query 7: This aggregated data helps ensure the total production cost stays within the budget**

```sql
SELECT SUM(pb.Copies * pc.Cost) AS Total_Production_Cost FROM production_budget pb JOIN production_costs pc ON pb.Copies IS NOT NULL;
```

**Results (CSV format):**
```csv
Total_Production_Cost
166500.0
```

**Query 8: This aggregated data is essential for the objective function, which aims to maximize revenue**

```sql
SELECT SUM(pb.Copies * bp.Price) AS Total_Revenue FROM production_budget pb JOIN book_prices bp ON pb.Copies IS NOT NULL;
```

**Results (CSV format):**
```csv
Total_Revenue
337500.0
```

**Query 9: This helps ensure that the production does not exceed the maximum issues per book**

```sql
SELECT pb.Copies, mi.Max_Issues FROM production_budget pb JOIN max_issues mi ON pb.Copies IS NOT NULL;
```

**Results (CSV format):**
```csv
Copies,Max_Issues
1000,5000
1000,6000
1000,7000
1500,5000
1500,6000
1500,7000
2000,5000
2000,6000
2000,7000
```

**Query 10: This ensures that the production meets or exceeds the minimum demand for each book**

```sql
SELECT pb.Copies, md.Min_Demand FROM production_budget pb JOIN minimum_demand md ON pb.Copies IS NOT NULL;
```

**Results (CSV format):**
```csv
Copies,Min_Demand
1000,500
1000,600
1000,700
1500,500
1500,600
1500,700
2000,500
2000,600
2000,700
```

**Query 11: This data is useful for calculating both the total production cost and total revenue**

```sql
SELECT pb.Copies, pc.Cost, bp.Price FROM production_budget pb JOIN production_costs pc ON pb.Copies IS NOT NULL JOIN book_prices bp ON pb.Copies IS NOT NULL;
```

**Results (CSV format):**
```csv
Copies,Cost,Price
1000,10.0,20.0
1000,10.0,25.0
1000,10.0,30.0
1000,12.0,20.0
1000,12.0,25.0
1000,12.0,30.0
1000,15.0,20.0
1000,15.0,25.0
1000,15.0,30.0
1500,10.0,20.0
1500,10.0,25.0
1500,10.0,30.0
1500,12.0,20.0
1500,12.0,25.0
1500,12.0,30.0
1500,15.0,20.0
1500,15.0,25.0
1500,15.0,30.0
2000,10.0,20.0
2000,10.0,25.0
2000,10.0,30.0
2000,12.0,20.0
2000,12.0,25.0
2000,12.0,30.0
2000,15.0,20.0
2000,15.0,25.0
2000,15.0,30.0
```

**Query 12: This data helps ensure that production stays within both the maximum issues and minimum demand constraints**

```sql
SELECT pb.Copies, mi.Max_Issues, md.Min_Demand FROM production_budget pb JOIN max_issues mi ON pb.Copies IS NOT NULL JOIN minimum_demand md ON pb.Copies IS NOT NULL;
```

**Results (CSV format):**
```csv
Copies,Max_Issues,Min_Demand
1000,5000,500
1000,5000,600
1000,5000,700
1000,6000,500
1000,6000,600
1000,6000,700
1000,7000,500
1000,7000,600
1000,7000,700
1500,5000,500
1500,5000,600
1500,5000,700
1500,6000,500
1500,6000,600
1500,6000,700
1500,7000,500
1500,7000,600
1500,7000,700
2000,5000,500
2000,5000,600
2000,5000,700
2000,6000,500
2000,6000,600
2000,6000,700
2000,7000,500
2000,7000,600
2000,7000,700
```

