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

0. Introduction

01 - Organizing data in a database
02 - What you should know
03 - Making the most of the exercise files
04 - Set up the database servers

1. Data Types

05 - Data integrity concepts
06 - The role of data types
07 - Numeric data types
08 - Character data types
09 - Date and time data types
10 - Additional common types
11 - Challenge - Choosing data types
12 - Solution - Choosing data types

2. Design a Normalized Database

13 - Protect data integrity with normalization
14 - First normal form
15 - Second normal form
16 - Third normal form
17 - Denormalization considerations

3. Identify Rows with Primary Keys

18 - Purpose of a primary key
19 - Establish the table's primary key
20 - Natural keys
21 - Composite keys
22 - Surrogate keys
23 - Challenge - Add a primary key to a table
24 - Solution - Add a primary key to a table

4. Link Data with Foreign Keys

25 - What are foreign keys
26 - Add a foreign key column to a table
27 - Create a relationship between tables
28 - IDENTITY column sequences

5. Formalize Data Relationships

29 - One-to-many relationships
30 - One-to-one relationships
31 - Many-to-many relationships
32 - Self-referencing relationships
33 - Cascade updates and deletes
34 - Challenge - Link tables with keys
35 - Solution - Link tables with keys

6. Indexes

36 - Table indexes
37 - Add indexes to a table
38 - View index statistics

7. Constraints

39 - Validate data with constraints
40 - Null constraints
41 - Unique constraints
42 - Setting default values
43 - Check constraints
44 - Challenge - Add constraints to a table
45 - Solution - Add constraints to a table

Conclusion

46 - Next steps