# Complete Optimization Problem and Solution: book_2

## 1. Problem Context and Goals

### Context  
A publishing company is focused on maximizing its revenue by determining the optimal number of issues to publish for each book. The decision-making process involves selecting the number of issues for each book, represented as integer variables. The primary objective is to maximize the total revenue, which is calculated by multiplying the price of each publication by the number of issues published for each book. The company operates under specific constraints: the total number of issues that can be published is capped, and there is a budget limit for publication costs. These constraints ensure that the company's operations remain within feasible production and financial limits. The business configuration includes a maximum number of issues that can be published, which serves as a constraint on total issues, and a total budget available for publication costs, which serves as a constraint on budget. The operational parameters are aligned with these constraints to ensure a linear optimization formulation, avoiding any nonlinear relationships such as variable products or divisions.

### Goals  
The primary goal of the optimization process is to maximize the company's revenue from book publications. This involves determining the optimal number of issues to publish for each book to achieve the highest possible total revenue. The success of this optimization is measured by the total revenue, which is calculated by summing the product of the price of each publication and the number of issues published for each book. The optimization goal is clearly defined in linear terms, focusing on maximizing revenue while adhering to the constraints of total issues and budget.

## 2. Constraints    

The optimization process is subject to two main constraints. First, the total number of issues published across all books must not exceed the maximum number of issues allowed. This constraint ensures that the company's production capacity is not overstretched. Second, the total cost of publishing, calculated by summing the product of the price of each publication and the number of issues published, must not exceed the available budget. This financial constraint ensures that the company operates within its financial means. Both constraints are expressed in linear terms, aligning with the company's operational parameters and business configuration.

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 1 Database Schema
-- Objective: Schema changes include adding missing constraint bounds and moving scalar parameters to configuration logic. Adjustments ensure all optimization requirements are met and business logic is preserved.

CREATE TABLE book (
  Book_ID INTEGER,
  Issues INTEGER
);

CREATE TABLE publication (
  Publication_ID INTEGER,
  Price FLOAT
);
```

### Data Dictionary  
The data dictionary provides a comprehensive overview of the tables and columns used in the optimization process, highlighting their business purposes and roles in the optimization:

- **Book Table**: This table stores information about each book and the number of issues to be published. 
  - **Book_ID**: Serves as a unique identifier for each book, linking it to the decision variable for the number of issues.
  - **Issues**: Represents the number of issues to be published for each book, serving as the decision variable in the optimization process.

- **Publication Table**: This table contains information about each publication and its price.
  - **Publication_ID**: Acts as a unique identifier for each publication, linking it to the price coefficient.
  - **Price**: Indicates the price of each publication, used as a coefficient in the revenue calculation.

### Current Stored Values  
```sql
-- Iteration 1 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on typical publishing industry standards, ensuring that the number of issues and prices are realistic and align with the given budget and issue constraints.

-- Realistic data for book
INSERT INTO book (Book_ID, Issues) VALUES (1, 15);
INSERT INTO book (Book_ID, Issues) VALUES (2, 25);
INSERT INTO book (Book_ID, Issues) VALUES (3, 10);

-- Realistic data for publication
INSERT INTO publication (Publication_ID, Price) VALUES (1, 18.99);
INSERT INTO publication (Publication_ID, Price) VALUES (2, 22.5);
INSERT INTO publication (Publication_ID, Price) VALUES (3, 14.75);
```