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