Database Foundations: Data Structures

Introduction

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

1. 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
• Solution: Store data in a table

2. Design a Normalized Database

• Protect data integrity with normalization
• First normal form
• Second normal form
• Third normal form
• Denormalization considerations
• Solution: Normalize a data table

3. 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
• Solution: Alter a table's design

4. 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
• Solution: Create a table constraint

5. 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
• Solution: Build a relationship between tables

6. Indexes

• Table indexes
• Add indexes to a table
• View index statistics

7. 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
• Solution: Set additional constraints on the data

Conclusion

• Next steps