Module 7: Database Design and Integrity

Until now, we have focused on querying existing data. In this module, you will step into the role of a Database Designer. You will learn how to build the tables that store data, how to modify that data safely, and how to enforce rules (constraints) to ensure your data remains accurate and reliable.

Learning Objectives

In this module, you will:

• Choose the correct Data Types for storage efficiency and accuracy.
• Build tables and views using Data Definition Language (DDL).
• Modify data using INSERT, UPDATE, and DELETE.
• Enforce business rules using Constraints (Primary Keys, Foreign Keys, etc.).
• Protect data integrity using Transactions and the ACID properties.

Chapters

7.1 Data Types Integers, Varchars, and choosing the right storage.

7.2 Creating Objects Mastering CREATE TABLE and Virtual Views.

7.3 Modifying Data Using INSERT, UPDATE, DELETE, and TRUNCATE.

7.4 Constraints Enforcing data quality with Primary and Foreign Keys.

7.5 Transactions Ensuring data reliability with ACID and COMMIT/ROLLBACK.

Caution

Unlike queries, which are “read-only,” the commands in this module modify the actual data on disk. Always double-check your WHERE clauses before running an UPDATE or DELETE!