Goals

Learn about the process of structuring a database schema to reduce redundancy and fix logically incorrect designs through normalization. Identify functional dependencies, superkeys, and subkeys to create a data model without anomalies. Design normalized data models for a university and the Tetris game.

Examples

• Normalizing the suppliers and parts data model
• Extract functional dependencies from a game like Mario Kart

Exercises

• Find functional dependencies and normalize a Mario Kart game
• Extract functional dependencies and normalize a device rental platform
• Create a database for a university based on a spreadsheet
• Design a data model for the game tetris in the BCNF

Introduction

Context

What is Normalization?

Normalization is the process of structuring a database schema to reduce redundency and fix a logically incorrect design. Based on constraints we will discuss in this topic, a database schema can be assigned to a normal form (codd70 ).

Normal Form Hierarchy

Normal forms are defined in an ascending order, where each step adds further constraints to the schema. By further normalizing the schema, we can bring it into a higher normal form.

Typically, we want our data model to be in the Boyce/Codd normal form (BCNF).

Normalization Sequence

When our data model is in a lower normal form, it is uncommon to normalize to all intermediate forms:

$$ 1NF → 2NF → 3NF → BCNF $$

Instead, we typically try to normalize the schema directly to the desired target form:

$$ 1NF → BCNF $$

$$ 2NF → BCNF $$

$$ 3NF → BCNF $$

Redundancy