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

### Current Stored Values  
```sql
-- Iteration 3 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on industry standards for book publishing, ensuring realistic production costs, prices, and demand levels. Budget and issue limits were set to reflect typical constraints faced by publishers.

-- Realistic data for production_budget
INSERT INTO production_budget (Copies) VALUES (1000);
INSERT INTO production_budget (Copies) VALUES (1500);
INSERT INTO production_budget (Copies) VALUES (2000);

-- Realistic data for minimum_demand
INSERT INTO minimum_demand (Min_Demand) VALUES (500);
INSERT INTO minimum_demand (Min_Demand) VALUES (600);
INSERT INTO minimum_demand (Min_Demand) VALUES (700);

-- Realistic data for production_costs
INSERT INTO production_costs (Cost) VALUES (10.0);
INSERT INTO production_costs (Cost) VALUES (12.0);
INSERT INTO production_costs (Cost) VALUES (15.0);

-- Realistic data for book_prices
INSERT INTO book_prices (Price) VALUES (20.0);
INSERT INTO book_prices (Price) VALUES (25.0);
INSERT INTO book_prices (Price) VALUES (30.0);

-- Realistic data for total_budget
INSERT INTO total_budget (Total_Budget) VALUES (100000);
INSERT INTO total_budget (Total_Budget) VALUES (150000);
INSERT INTO total_budget (Total_Budget) VALUES (200000);

-- Realistic data for max_issues
INSERT INTO max_issues (Max_Issues) VALUES (5000);
INSERT INTO max_issues (Max_Issues) VALUES (6000);
INSERT INTO max_issues (Max_Issues) VALUES (7000);
```