Database Fundamentals Roadmap
45 sections • 524 topics
- 1. Understanding Database Management Systems (DBMS)
- 2. Understanding Relational Databases (SQL)
- Example: Relational Model
- 3. Understanding NoSQL Databases
- 4. Understanding ACID Properties
- Example: ACID Transaction
- 5. Understanding BASE Properties
- 6. Understanding CAP Theorem
- 7. Comparing SQL vs NoSQL
- 8. Understanding Database Schemas
- Example: Creating a Schema
- 9. Understanding Data Models
- 10. Choosing Database Types for Applications
- 1. Identifying Entities and Attributes
- 2. Defining Primary Keys
- 3. Establishing Relationships
- Example: M:N via Junction Table
- 4. Understanding Cardinality and Participation
- 5. Creating ER Diagrams
- 6. Handling Weak Entities and Identifying Relationships
- Example: Weak Entity (OrderItem)
- 7. Modeling Recursive Relationships
- Example: Employee-Manager
- 8. Using Associative Entities
- 9. Defining Attribute Types
- 10. Modeling Generalization and Specialization
- Example: Class Table Inheritance
- 1. Understanding Functional Dependencies
- 2. Applying First Normal Form
- Example: 1NF Conversion
- 3. Applying Second Normal Form
- Example: Removing Partial Dependency
- 4. Applying Third Normal Form
- Example: Removing Transitive Dependency
- 5. Applying Boyce-Codd Normal Form (BCNF)
- 6. Applying Fourth Normal Form
- 7. Applying Fifth Normal Form
- 8. Identifying Normalization Anomalies
- 9. Using Normalization Tools and Techniques
- 10. Balancing Normalization vs Performance
- 1. Understanding When to Denormalize
- 2. Creating Redundant Columns
- Example: Generated Column (Postgres)
- 3. Combining Tables
- 4. Using Materialized Views
- Example: Materialized Sales Summary
- 5. Implementing Summary Tables
- 6. Handling Data Consistency in Denormalized Schemas
- 7. Creating Read-Optimized Schemas
- 8. Balancing Storage vs Query Performance
- 9. Managing Update Complexity
- 10. Documenting Denormalization Decisions
- 1. Creating Tables
- Example: Comprehensive CREATE TABLE
- 2. Defining Column Data Types
- 3. Setting Column Constraints
- 4. Defining Primary Keys
- 5. Creating Foreign Keys
- Example: FK with ON DELETE CASCADE
- 6. Adding Check Constraints
- 7. Creating Schemas and Namespaces
- 8. Altering Tables
- 9. Dropping Tables
- 10. Using Temporary Tables
- Example: Transaction-scoped Temp Table
- 1. Using Integer Types
- 2. Using Decimal Types
- 3. Using String Types
- 4. Using Date and Time Types
- Example: Time Zone-Safe Storage
- 5. Using Boolean Types
- 6. Using Binary Types
- 7. Using JSON and XML Types
- Example: JSONB Query
- 8. Using Array and Composite Types
- 9. Creating User-Defined Types
- 10. Choosing Appropriate Data Types
- 1. Understanding Constraint Types
- 2. Creating Named Constraints
- 3. Implementing Referential Integrity
- 4. Deferring Constraint Checking
- Example: Circular FK with Deferral
- 5. Implementing Domain Constraints
- 6. Using Cascading Actions
- 7. Implementing Business Rule Constraints
- 8. Handling Constraint Violations
- Example: ON CONFLICT (Postgres UPSERT)
- 9. Disabling and Enabling Constraints
- 10. Documenting Constraint Business Rules
- 1. Creating Indexes
- 2. Understanding Index Types
- 3. Creating Composite Indexes
- Example: Composite Index
- 4. Using Covering Indexes
- 5. Creating Partial Indexes
- Example: Partial Unique Index
- 6. Implementing Full-Text Indexes
- Example: Postgres FTS
- 7. Analyzing Index Usage
- 8. Dropping Unused Indexes
- 9. Managing Index Maintenance
- 10. Balancing Index Overhead vs Query Performance
- 1. Understanding Join Types
- 2. Using INNER JOIN
- Example: INNER JOIN
- 3. Using LEFT JOIN
- Example: Find Customers With No Orders
- 4. Using RIGHT JOIN
- 5. Using FULL OUTER JOIN
- Example: Reconciliation Across Two Sources
- 6. Creating CROSS JOIN
- 7. Performing Self Joins
- Example: Employee with Manager Name
- 8. Using Multiple Joins
- Example: 3-Table Join
- 9. Optimizing Join Performance
- 10. Understanding Join Order and Execution Plans
- 1. Creating Scalar Subqueries
- Example: Scalar Subquery
- 2. Using Subqueries in WHERE Clause
- 3. Using Subqueries in SELECT Clause
- 4. Using Subqueries in FROM Clause
- Example: Derived Table
- 5. Using Correlated Subqueries
- Example: Correlated Subquery
- 6. Testing Existence
- 7. Comparing with Subqueries
- 8. Optimizing Subquery Performance
- 9. Converting Subqueries to Joins
- Example: IN → JOIN
- 10. Using Common Table Expressions
- Example: Recursive CTE (Hierarchy)
- 1. Using String Functions
- 2. Using Numeric Functions
- 3. Using Date Functions
- 4. Using Conversion Functions
- 5. Using Conditional Functions
- 6. Using Window Functions
- Example: Greatest-N-per-group
- 7. Using Aggregate Window Functions
- 8. Creating User-Defined Functions
- Example: PL/pgSQL Function
- 9. Creating Scalar Functions
- 10. Creating Table-Valued Functions
- Example: SETOF Function (PG)
- 1. Understanding Transaction Lifecycle
- 2. Understanding Atomicity
- 3. Understanding Consistency
- 4. Understanding Isolation
- 5. Understanding Durability
- 6. Understanding Transaction Logs
- 7. Handling Long-Running Transactions
- 8. Using Distributed Transactions
- 9. Implementing Compensating Transactions
- 10. Optimizing Transaction Performance
- 1. Creating Stored Procedures
- 2. Defining Procedure Parameters
- 3. Using Variables in Procedures
- Example: PL/pgSQL Variables
- 4. Implementing Control Flow
- 5. Handling Errors in Procedures
- 6. Executing Stored Procedures
- 7. Returning Result Sets
- 8. Using Dynamic SQL in Procedures
- 9. Managing Procedure Security
- 10. Optimizing Procedure Performance
- 1. Creating Triggers
- Example: Audit Trigger (PG)
- 2. Understanding Trigger Types
- 3. Implementing Row-Level Triggers
- 4. Implementing Statement-Level Triggers
- 5. Using Trigger Events
- 6. Accessing Old and New Values
- 7. Implementing Audit Trails with Triggers
- 8. Cascading Trigger Execution
- 9. Disabling and Enabling Triggers
- 10. Managing Trigger Performance Impact
- 1. Understanding Partitioning Benefits
- 2. Creating Range Partitions
- Example: Postgres Range Partitioning
- 3. Creating List Partitions
- 4. Creating Hash Partitions
- 5. Creating Composite Partitions
- 6. Querying Partitioned Tables
- 7. Managing Partitions
- 8. Indexing Partitioned Tables
- 9. Maintaining Partition Statistics
- 10. Migrating Data Between Partitions
- 1. Analyzing Query Execution Plans
- Example: EXPLAIN ANALYZE
- 2. Identifying Performance Bottlenecks
- 3. Optimizing WHERE Clause Predicates
- 4. Using Index Hints and Query Hints
- 5. Rewriting Queries for Performance
- 6. Optimizing Joins
- 7. Avoiding Full Table Scans
- 8. Using Query Caching
- 9. Optimizing Aggregate Queries
- 10. Profiling Query Execution Time
- 1. Monitoring Database Metrics
- 2. Analyzing Slow Queries
- 3. Tuning Database Configuration Parameters
- 4. Managing Connection Pooling
- 5. Implementing Query Result Caching
- 6. Optimizing Buffer Pool and Memory Usage
- 7. Managing Disk I/O Performance
- 8. Implementing Read Replicas for Load Distribution
- 9. Using Database Profiling Tools
- 10. Establishing Performance Baselines
- 1. Understanding Sharding Concepts
- 2. Designing Shard Keys
- 3. Implementing Range-Based Sharding
- 4. Implementing Hash-Based Sharding
- 5. Implementing Directory-Based Sharding
- 6. Managing Cross-Shard Queries
- 7. Handling Shard Rebalancing
- 8. Implementing Consistent Hashing
- 9. Managing Shard Failover
- 10. Designing for Shard Scalability
- 1. Understanding Connection Lifecycle
- Connection Lifecycle
- 2. Configuring Connection Pooling
- 3. Setting Pool Size Parameters
- 4. Implementing Connection Timeouts
- 5. Handling Connection Leaks
- 6. Using Connection Validation
- 7. Managing Connection Credentials
- 8. Implementing Connection Retry Logic
- 9. Monitoring Connection Pool Metrics
- 10. Optimizing Connection Pool Performance
- 1. Creating Database Users and Roles
- 2. Granting and Revoking Permissions
- 3. Implementing Row-Level Security
- Example: Postgres RLS
- 4. Implementing Column-Level Security
- 5. Using Database Encryption
- 6. Implementing SQL Injection Prevention
- 7. Auditing Database Access
- 8. Managing Database Authentication
- 9. Implementing Principle of Least Privilege
- 10. Securing Database Connections
- 1. Understanding Backup Types
- 2. Creating Database Backups
- 3. Scheduling Automated Backups
- 4. Implementing Point-in-Time Recovery
- 5. Restoring Databases from Backups
- Restore Procedure
- 6. Testing Backup and Restore Procedures
- 7. Managing Backup Storage and Retention
- 8. Implementing Hot vs Cold Backups
- 9. Handling Database Corruption Recovery
- 10. Documenting Recovery Procedures
- 1. Understanding Replication Types
- 2. Configuring Replication Topology
- 3. Implementing Synchronous Replication
- 4. Implementing Asynchronous Replication
- 5. Handling Replication Lag
- 6. Managing Failover and Switchover
- 7. Implementing Read Replicas
- 8. Resolving Replication Conflicts
- 9. Monitoring Replication Health
- 10. Testing Disaster Recovery Scenarios
- 1. Monitoring Database Health Metrics
- 2. Tracking Query Performance
- 3. Monitoring Lock and Wait Statistics
- 4. Tracking Connection Utilization
- 5. Monitoring Disk Space Usage
- 6. Setting Up Alerting Thresholds
- 7. Using Database Dashboards
- 8. Implementing Log Aggregation
- 9. Tracking Replication Lag Metrics
- 10. Establishing Monitoring Baselines
- 1. Identifying Archive Candidates
- 2. Designing Archive Schemas
- 3. Implementing Tiered Storage
- 4. Creating Archive Policies
- 5. Managing Archive Retention Periods
- 6. Implementing Data Purging Procedures
- 7. Querying Archived Data
- 8. Handling Compliance Requirements
- 9. Implementing Archive Compression
- 10. Testing Archive Restore Procedures
- 1. Using Adjacency List Model
- Example: Adjacency List
- 2. Using Nested Set Model
- 3. Using Path Enumeration Model
- 4. Using Closure Table Model
- Example: Closure Table
- 5. Comparing Hierarchical Models
- 6. Querying Hierarchical Data
- 7. Modeling Tree Structures
- 8. Modeling Graph Structures
- 9. Handling Hierarchical Data Updates
- 10. Optimizing Hierarchical Queries
- 1. Understanding Temporal Data Types
- 2. Implementing Bi-Temporal Tables
- Example: Bi-Temporal Row
- 3. Modeling Effective Dating
- 4. Designing Historical Tracking Tables
- 5. Implementing Event Sourcing Schemas
- 6. Modeling Point-in-Time Queries
- Example: AS OF Query (SQL:2011)
- 7. Designing Temporal Foreign Keys
- 8. Handling Temporal Data Updates
- 9. Implementing Slowly Changing Dimensions
- 10. Optimizing Temporal Query Performance
- 1. Designing Time-Series Schemas
- 2. Partitioning by Time
- 3. Implementing Downsampling Strategies
- 4. Designing Data Retention Policies
- 5. Indexing Time-Based Queries
- 6. Handling Out-of-Order Data
- 7. Implementing Compression Strategies
- 8. Designing Rollup Tables
- 9. Managing Hot and Cold Data Storage
- 10. Optimizing Time-Series Query Patterns
- 1. Using Separate Databases per Tenant
- 2. Using Separate Schemas per Tenant
- 3. Using Shared Schema with Tenant ID
- Example: Shared Schema
- 4. Implementing Row-Level Isolation
- 5. Designing Tenant-Specific Configurations
- 6. Modeling Shared Reference Data
- 7. Implementing Tenant Data Partitioning
- 8. Designing for Tenant Scalability
- 9. Handling Cross-Tenant Queries
- 10. Ensuring Tenant Data Security
- 1. Validating Data Types
- 2. Enforcing Required Fields
- 3. Implementing Range Validation
- 4. Using Regular Expressions for Patterns
- 5. Implementing Custom Validation Rules
- 6. Handling Validation Errors
- 7. Validating Relationships and References
- 8. Implementing Server-Side Validation
- 9. Using Schema Validation
- 10. Balancing Validation vs Performance
- 1. Adding Columns Safely
- 2. Removing Deprecated Columns
- Deprecation Workflow
- 3. Renaming Columns Without Downtime
- Expand-Contract Rename
- 4. Changing Column Data Types
- 5. Adding Indexes Online
- 6. Modifying Constraints Safely
- 7. Handling Backward Compatibility
- 8. Implementing Forward Compatibility
- 9. Documenting Schema Changes
- 10. Testing Schema Changes
- 1. Choosing Migration Tools
- 2. Writing Forward Migrations
- Example: Flyway SQL Migration
- 3. Implementing Reversible Migrations
- 4. Managing Migration Versions
- 5. Running Migrations in CI/CD
- 6. Handling Migration Failures
- 7. Implementing Zero-Downtime Migrations
- 8. Coordinating Schema and Code Changes
- Coordinated Release
- 9. Testing Migrations in Lower Environments
- 10. Documenting Migration Procedures
- 1. Understanding NoSQL Data Models
- 2. Designing Schemaless Collections
- 3. Modeling Embedded Documents
- 4. Modeling Document References
- 5. Implementing Denormalization
- 6. Designing for Access Patterns
- Access-Pattern-First Modeling
- 7. Modeling Many-to-Many Relationships
- 8. Handling Schema Migrations in NoSQL
- 9. Modeling Aggregates
- 10. Choosing Document Granularity
- 1. Storing JSON Documents
- 2. Creating and Updating Documents
- Example: MongoDB Insert & Update
- 3. Indexing Document Fields
- 4. Implementing Document Validation
- Example: $jsonSchema Validator
- 5. Managing Document Versioning
- 6. Implementing Atomic Updates
- 7. Designing Sub-Document Structures
- 8. Handling Array Fields
- 9. Implementing Full-Text Search
- 10. Optimizing Document Storage
- 1. Using Document Query Operators
- 2. Filtering Nested Fields
- Example: Nested Filter
- 3. Performing Aggregation Pipelines
- Example: Aggregation Pipeline
- 4. Joining Documents
- Example: $lookup
- 5. Sorting and Pagination
- 6. Using Map-Reduce
- 7. Performing Text Search
- Example: Text Search
- 8. Using Geospatial Queries
- 9. Optimizing Document Queries
- 10. Analyzing Query Performance
- 1. Designing Key Naming Conventions
- 2. Storing Simple Values
- Example: Redis Strings
- 3. Implementing Complex Data Structures
- 4. Setting TTL and Expiration
- 5. Implementing Atomic Operations
- 6. Using Key-Value for Caching
- 7. Implementing Session Storage
- 8. Designing for High Throughput
- 9. Implementing Persistence Strategies
- 10. Handling Hot Keys
- 1. Designing Column Families
- Example: Cassandra Table
- 2. Modeling Wide Rows
- 3. Designing Partition Keys
- 4. Implementing Clustering Columns
- 5. Designing for Time-Series Data
- 6. Modeling for Write-Heavy Workloads
- 7. Implementing Secondary Indexes
- 8. Querying with CQL
- Example: CQL Queries
- 9. Managing Consistency Levels
- 10. Optimizing Column-Family Queries
- 1. Modeling Nodes and Relationships
- 2. Designing Graph Schemas
- 3. Using Cypher Query Language
- Example: Cypher
- 4. Implementing Graph Traversal
- 5. Modeling Properties on Edges
- 6. Implementing Path Finding Algorithms
- 7. Modeling Social Networks
- 8. Implementing Recommendation Systems
- 9. Querying Complex Patterns
- Example: Fraud Ring Detection
- 10. Optimizing Graph Queries
- 1. Storing JSON in SQL Databases
- 2. Querying JSON Fields
- Example: PG JSONB Queries
- 3. Indexing JSON Documents
- 4. Validating JSON Structure
- 5. Modeling Hybrid Relational-JSON
- 6. Handling XML Data
- 7. Implementing JSON Path Expressions
- 8. Converting Between JSON and Tables
- Example: jsonb_to_recordset / json_table
- 9. Designing Flexible Schemas
- 10. Optimizing JSON Query Performance
- 1. Analyzing Data Structure Requirements
- 2. Evaluating Query Complexity
- 3. Assessing Consistency Requirements
- 4. Considering Scalability Needs
- 5. Evaluating Transaction Requirements
- 6. Comparing SQL and NoSQL Performance
- 7. Assessing Operational Complexity
- 8. Evaluating Team Expertise
- 9. Choosing Polyglot Persistence
- 10. Making Database Migration Decisions
- Migration Decision Workflow