# Complete Optimization Problem and Solution: university_basketball

## 1. Problem Context and Goals

### Context  
The business problem revolves around selecting basketball teams for a tournament to maximize the overall win percentage of the selected teams. The decision-making process involves choosing which teams to include in the tournament based on their performance, conference affiliation, and geographical location. Each team is represented by a unique identifier, and the decision to select a team is binary—either the team is selected or not.  

The operational parameters guiding this decision include:  
- **Win Percentage**: The win percentage of each team, which serves as the primary metric for evaluating team performance.  
- **Total Teams Selected**: The total number of teams to be selected for the tournament, ensuring a manageable and competitive tournament size.  
- **Conference Diversity**: Minimum requirements for the number of teams selected from the East, West, and South conferences to ensure fair representation across conferences.  
- **Geographical Distribution**: Maximum limits on the number of teams selected from specific locations (New York, Los Angeles, and Chicago) to promote geographical diversity.  

These parameters are derived from the business configuration, which includes scalar values such as the total number of teams to be selected, minimum team requirements per conference, and maximum team limits per location. The decision-making process is designed to be linear, avoiding complex relationships such as variable products or divisions, ensuring a straightforward and efficient optimization formulation.  

### Goals  
The primary goal of this optimization problem is to maximize the total win percentage of the selected teams for the tournament. Success is measured by the cumulative win percentage of the chosen teams, ensuring that the tournament features the highest-performing teams while adhering to the constraints on conference diversity and geographical distribution.  

The optimization process focuses on selecting the best combination of teams based on their win percentages, ensuring that the final selection aligns with the business requirements for team diversity and location limits. This goal is achieved through a linear formulation that directly maps the win percentages of teams to the decision variables, ensuring clarity and simplicity in the optimization process.  

## 2. Constraints  

The optimization problem is subject to the following constraints, which ensure that the selected teams meet the business requirements for diversity and distribution:  

1. **Total Teams Selected**: The total number of teams selected for the tournament must equal the predefined value specified in the business configuration.  
2. **East Conference Diversity**: The number of teams selected from the East conference must meet or exceed the minimum requirement set in the business configuration.  
3. **West Conference Diversity**: The number of teams selected from the West conference must meet or exceed the minimum requirement set in the business configuration.  
4. **South Conference Diversity**: The number of teams selected from the South conference must meet or exceed the minimum requirement set in the business configuration.  
5. **New York Geographical Limit**: The number of teams selected from New York must not exceed the maximum limit specified in the business configuration.  
6. **Los Angeles Geographical Limit**: The number of teams selected from Los Angeles must not exceed the maximum limit specified in the business configuration.  
7. **Chicago Geographical Limit**: The number of teams selected from Chicago must not exceed the maximum limit specified in the business configuration.  

These constraints are designed to ensure that the final selection of teams is balanced and representative, aligning with the business goals for diversity and geographical distribution.  

## 3. Available Data  

### Database Schema  
```sql
-- Iteration 3 Database Schema
-- Objective: Added missing constraint bounds to business configuration logic and updated data dictionary to reflect these changes. No new tables were created as the missing requirements were better suited for configuration logic.

CREATE TABLE win_percentage (
  Team_ID INTEGER,
  Win_Percent FLOAT
);

CREATE TABLE team_selection (
  Team_ID INTEGER,
  Team_Selection BOOLEAN
);

CREATE TABLE conference_indicator (
  Team_ID INTEGER,
  Conference_Indicator STRING
);

CREATE TABLE location_indicator (
  Team_ID INTEGER,
  Location_Indicator STRING
);
```

### Data Dictionary  
The following tables and columns are used in the optimization problem, with their business purposes and optimization roles clearly defined:  

- **win_percentage**:  
  - **Team_ID**: Unique identifier for each team.  
  - **Win_Percent**: The win percentage of the team, used as the coefficient in the objective function to maximize the total win percentage of selected teams.  

- **team_selection**:  
  - **Team_ID**: Unique identifier for each team.  
  - **Team_Selection**: Binary indicator of whether a team is selected for the tournament, serving as the decision variable in the optimization model.  

- **conference_indicator**:  
  - **Team_ID**: Unique identifier for each team.  
  - **Conference_Indicator**: The conference affiliation of the team, used to enforce constraints on conference diversity.  

- **location_indicator**:  
  - **Team_ID**: Unique identifier for each team.  
  - **Location_Indicator**: The location of the team, used to enforce constraints on geographical distribution.  

### Current Stored Values  
```sql
-- Iteration 3 Realistic Data
-- Generated by triple expert (business + data + optimization)
-- Values were determined based on realistic basketball tournament scenarios, considering team performance, conference distribution, and geographical constraints. Data was generated to ensure diversity and competitiveness while respecting business configuration parameters.

-- Realistic data for win_percentage
INSERT INTO win_percentage (Team_ID, Win_Percent) VALUES (1, 85.0);
INSERT INTO win_percentage (Team_ID, Win_Percent) VALUES (2, 78.5);
INSERT INTO win_percentage (Team_ID, Win_Percent) VALUES (3, 72.0);
INSERT INTO win_percentage (Team_ID, Win_Percent) VALUES (4, 80.5);
INSERT INTO win_percentage (Team_ID, Win_Percent) VALUES (5, 75.0);

-- Realistic data for team_selection
INSERT INTO team_selection (Team_ID, Team_Selection) VALUES (1, False);
INSERT INTO team_selection (Team_ID, Team_Selection) VALUES (2, False);
INSERT INTO team_selection (Team_ID, Team_Selection) VALUES (3, False);
INSERT INTO team_selection (Team_ID, Team_Selection) VALUES (4, False);
INSERT INTO team_selection (Team_ID, Team_Selection) VALUES (5, False);

-- Realistic data for conference_indicator
INSERT INTO conference_indicator (Team_ID, Conference_Indicator) VALUES (1, 'East');
INSERT INTO conference_indicator (Team_ID, Conference_Indicator) VALUES (2, 'West');
INSERT INTO conference_indicator (Team_ID, Conference_Indicator) VALUES (3, 'South');
INSERT INTO conference_indicator (Team_ID, Conference_Indicator) VALUES (4, 'East');
INSERT INTO conference_indicator (Team_ID, Conference_Indicator) VALUES (5, 'West');

-- Realistic data for location_indicator
INSERT INTO location_indicator (Team_ID, Location_Indicator) VALUES (1, 'New York');
INSERT INTO location_indicator (Team_ID, Location_Indicator) VALUES (2, 'Los Angeles');
INSERT INTO location_indicator (Team_ID, Location_Indicator) VALUES (3, 'Chicago');
INSERT INTO location_indicator (Team_ID, Location_Indicator) VALUES (4, 'New York');
INSERT INTO location_indicator (Team_ID, Location_Indicator) VALUES (5, 'Los Angeles');
```