Database Foundations: Data Structures

Learn how to properly structure your data tables to develop well-formed databases that adhere to time-tested standards.

Introduction

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

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

2. Design a Normalized Database

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

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

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

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

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

Conclusion

• Next steps