# Database Optimization ## Normalization * Divides larger tables into small sets to minimize data redundancy * Prevent the problems caused by typical CRUD actions Example:

### Types of Anomalies #### Insert Anomaly If a student is not assigned to a course, and the course\_ID isn't nullable, then your insert is not possible.

#### Delete Anomaly By deleting a student you would also delete all the college related information.

#### Update Anomaly If you miss any record on the course attributes while updating, then you will have data inconsistency.

### Normal forms To prevent these types of anomalies, we can use Normal Form transformation. #### First Normal Form (1NF) Column cell of a table cannot hold multiple values

By applying the 1NF:

#### Second Normal Form (2NF) * Must be in 1NF * Data Redundancy across multiple rows * Data must be moved to separate table

By applying the 2NF:

#### Third Normal Form (3NF) * Must be in 2NF * Eliminate fields that do not depend on the primary key

By applying the 3NF:

## Denormalization * Data from multiple tables are combined into one table * Faster data retrieval * Avoid costly join in a relational database * Increased performance * Applied after normalization

## OLTP and OLAP implementations

### Normalization on OLTP * Write operation: Single update on target table * Read operation: Costly join operations ### Denormalization on OLAP * Write operation: Multiple updates on target attributes, can lead to data inconsistency * Read Operation: Minimal join operations