Database Foundations: Data Structures

Course details

This course focuses on seven core concepts: data types, designing a normalized database, identifying rows with primary keys, linking data with foreign keys, formalizing data relationships, indexes, and constraints. Database expert Adam Wilbert describes techniques that database developers use to protect their data. He explains why data types are important in creating a consistent data storage environment, then walks you through several common data types. Adam shows you the three normal forms of good database design.

In order to work with the data stored in each row in a table, the table must contain a primary key column, or set of columns. Foreign keys provide the glue that the entire system is built on. Adam steps you through how to incorporate primary keys and foreign keys into the design of your database tables. He walks through data relationships, indexes, and constraints, then concludes with other courses you may find useful.

Concepts

Introduction

Organizing data in a database
What you should know
Making the most of the exercise files
Set up the database servers

Data Types

Data integrity concepts
The role of data types
Numeric data types
Character data types
Date and time data types
Additional common types
Challenge - Choosing data types
Solution - Choosing data types

Design a Normalized Database

Protect data integrity with normalization
First normal form
Second normal form
Third normal form
Denormalization considerations

Identify Rows with Primary Keys

Purpose of a primary key
Establish the table's primary key
Natural keys
Composite keys
Surrogate keys
Challenge - Add a primary key to a table
Solution - Add a primary key to a table

Link Data with Foreign Keys

What are foreign keys
Add a foreign key column to a table
Create a relationship between tables
IDENTITY column sequences

Formalize Data Relationships

One-to-many relationships
One-to-one relationships
Many-to-many relationships
Self-referencing relationships
Cascade updates and deletes
Challenge - Link tables with keys
Solution - Link tables with keys

Indexes

Table indexes
Add indexes to a table
View index statistics

Constraints

Validate data with constraints
Null constraints
Unique constraints
Setting default values
Check constraints
Challenge - Add constraints to a table
Solution - Add constraints to a table

Conclusion

Next steps