chapter six

6 Normalization and Implementation

 

In this chapter

  • You normalize your database design.
  • You implement your database design.
  • You learn a set of important concepts such as constraints and cascade.
What you need to know

You can find the database design covered in this chapter implemented in tools commonly used by practitioners, such as dbdiagram.io, and MySQL Workbench in the GitHub repository (https://github.com/Neo-Hao/grokking-relational-database-design). You can navigate to the chapter_06 folder and follow the instructions in the README.md file to load the database design into corresponding tools.

Additionally, you can also find the SQL scripts corresponding to the almost finalized database design for different RDBMS, including MySQL, MariaDB, PostgreSQL, SQLite, SQL Server, and Oracle.

Overview

In this chapter, you will normalize and implement your database design for The Sci-Fi Collective. By doing so, you will learn about important concepts in database design, such as functional dependency, normalization, and different types of constraints.

Normalization

Before converting your database design to a SQL script that creates the corresponding database and tables, you will need to normalize your design first. This is a critical step in database design known as normalization.

There is always a primary key

There are no multi-valued columns

All columns are dependent on a key but nothing else

There is no transitive dependency

Implementation

NOT NULL: Can’t have null-thing to say

Primary key: The one and only

Foreign key: Playing cupid

Referential actions

Unique: Sorry, I’m taken

Default: Defaulting to awesome

Check: Enforcing data decorum

Summary