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.

6.1 Overview

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

6.2 Normalization

Before converting your database design to a SQL script that creates the corresponding database and tables, you will need to take a critical step called normalization.

6.2.1 There is always a primary key

6.2.2 There are no multi-valued columns

6.2.3 All columns are dependent on a key but nothing else

6.2.4 There is no transitive dependency

6.3 Implementation

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

6.3.2 Primary key: The one and only

6.3.3 Foreign key: Playing cupid

6.3.4 Referential actions

6.3.5 Unique: Sorry, I’m taken

6.3.6 Default: Defaulting to awesome

6.3.7 Check: Enforcing data decorum

6.4 Summary