Skip to main content
Mapagam

45 SECTIONS · 524 TOPICS

Database Fundamentals Roadmap

  1. Understanding Database Fundamentals

    1. 1. Understanding Database Management Systems (DBMS)
    2. 2. Understanding Relational Databases (SQL)
    3. Example: Relational Model
    4. 3. Understanding NoSQL Databases
    5. 4. Understanding ACID Properties
    6. Example: ACID Transaction
    7. 5. Understanding BASE Properties
    8. 6. Understanding CAP Theorem
    9. 7. Comparing SQL vs NoSQL
    10. 8. Understanding Database Schemas
    11. Example: Creating a Schema
    12. 9. Understanding Data Models
    13. 10. Choosing Database Types for Applications

  2. Designing Entity-Relationship Models

    1. 1. Identifying Entities and Attributes
    2. 2. Defining Primary Keys
    3. 3. Establishing Relationships
    4. Example: M:N via Junction Table
    5. 4. Understanding Cardinality and Participation
    6. 5. Creating ER Diagrams
    7. 6. Handling Weak Entities and Identifying Relationships
    8. Example: Weak Entity (OrderItem)
    9. 7. Modeling Recursive Relationships
    10. Example: Employee-Manager
    11. 8. Using Associative Entities
    12. 9. Defining Attribute Types
    13. 10. Modeling Generalization and Specialization
    14. Example: Class Table Inheritance

  3. Working with Normalization

    1. 1. Understanding Functional Dependencies
    2. 2. Applying First Normal Form
    3. Example: 1NF Conversion
    4. 3. Applying Second Normal Form
    5. Example: Removing Partial Dependency
    6. 4. Applying Third Normal Form
    7. Example: Removing Transitive Dependency
    8. 5. Applying Boyce-Codd Normal Form (BCNF)
    9. 6. Applying Fourth Normal Form
    10. 7. Applying Fifth Normal Form
    11. 8. Identifying Normalization Anomalies
    12. 9. Using Normalization Tools and Techniques
    13. 10. Balancing Normalization vs Performance

  4. Implementing Denormalization Strategies

    1. 1. Understanding When to Denormalize
    2. 2. Creating Redundant Columns
    3. Example: Generated Column (Postgres)
    4. 3. Combining Tables
    5. 4. Using Materialized Views
    6. Example: Materialized Sales Summary
    7. 5. Implementing Summary Tables
    8. 6. Handling Data Consistency in Denormalized Schemas
    9. 7. Creating Read-Optimized Schemas
    10. 8. Balancing Storage vs Query Performance
    11. 9. Managing Update Complexity
    12. 10. Documenting Denormalization Decisions

  5. Creating SQL Tables and Schemas

    1. 1. Creating Tables
    2. Example: Comprehensive CREATE TABLE
    3. 2. Defining Column Data Types
    4. 3. Setting Column Constraints
    5. 4. Defining Primary Keys
    6. 5. Creating Foreign Keys
    7. Example: FK with ON DELETE CASCADE
    8. 6. Adding Check Constraints
    9. 7. Creating Schemas and Namespaces
    10. 8. Altering Tables
    11. 9. Dropping Tables
    12. 10. Using Temporary Tables
    13. Example: Transaction-scoped Temp Table

  6. Working with SQL Data Types

    1. 1. Using Integer Types
    2. 2. Using Decimal Types
    3. 3. Using String Types
    4. 4. Using Date and Time Types
    5. Example: Time Zone-Safe Storage
    6. 5. Using Boolean Types
    7. 6. Using Binary Types
    8. 7. Using JSON and XML Types
    9. Example: JSONB Query
    10. 8. Using Array and Composite Types
    11. 9. Creating User-Defined Types
    12. 10. Choosing Appropriate Data Types

  7. Managing Database Constraints

    1. 1. Understanding Constraint Types
    2. 2. Creating Named Constraints
    3. 3. Implementing Referential Integrity
    4. 4. Deferring Constraint Checking
    5. Example: Circular FK with Deferral
    6. 5. Implementing Domain Constraints
    7. 6. Using Cascading Actions
    8. 7. Implementing Business Rule Constraints
    9. 8. Handling Constraint Violations
    10. Example: ON CONFLICT (Postgres UPSERT)
    11. 9. Disabling and Enabling Constraints
    12. 10. Documenting Constraint Business Rules

  8. Working with SQL Indexes

    1. 1. Creating Indexes
    2. 2. Understanding Index Types
    3. 3. Creating Composite Indexes
    4. Example: Composite Index
    5. 4. Using Covering Indexes
    6. 5. Creating Partial Indexes
    7. Example: Partial Unique Index
    8. 6. Implementing Full-Text Indexes
    9. Example: Postgres FTS
    10. 7. Analyzing Index Usage
    11. 8. Dropping Unused Indexes
    12. 9. Managing Index Maintenance
    13. 10. Balancing Index Overhead vs Query Performance

  9. Writing SQL SELECT Queries

    1. 1. Selecting Columns
    2. 2. Filtering Rows
    3. 3. Using Logical Operators
    4. 4. Filtering with Ranges
    5. 5. Matching Patterns
    6. 6. Handling NULL Values
    7. 7. Sorting Results
    8. 8. Limiting Results
    9. 9. Removing Duplicates
    10. 10. Aliasing Columns and Tables

  10. Working with SQL Joins

    1. 1. Understanding Join Types
    2. 2. Using INNER JOIN
    3. Example: INNER JOIN
    4. 3. Using LEFT JOIN
    5. Example: Find Customers With No Orders
    6. 4. Using RIGHT JOIN
    7. 5. Using FULL OUTER JOIN
    8. Example: Reconciliation Across Two Sources
    9. 6. Creating CROSS JOIN
    10. 7. Performing Self Joins
    11. Example: Employee with Manager Name
    12. 8. Using Multiple Joins
    13. Example: 3-Table Join
    14. 9. Optimizing Join Performance
    15. 10. Understanding Join Order and Execution Plans

  11. Using SQL Aggregate Functions

    1. 1. Counting Rows
    2. 2. Calculating Sums
    3. 3. Finding Averages
    4. 4. Getting Minimum Values
    5. 5. Getting Maximum Values
    6. 6. Grouping Results
    7. Example: Sales by Region and Month
    8. 7. Filtering Groups
    9. Example: HAVING
    10. 8. Using Multiple Aggregations
    11. 9. Handling NULL in Aggregations
    12. 10. Creating Rollups and Cubes

  12. Working with Subqueries

    1. 1. Creating Scalar Subqueries
    2. Example: Scalar Subquery
    3. 2. Using Subqueries in WHERE Clause
    4. 3. Using Subqueries in SELECT Clause
    5. 4. Using Subqueries in FROM Clause
    6. Example: Derived Table
    7. 5. Using Correlated Subqueries
    8. Example: Correlated Subquery
    9. 6. Testing Existence
    10. 7. Comparing with Subqueries
    11. 8. Optimizing Subquery Performance
    12. 9. Converting Subqueries to Joins
    13. Example: IN → JOIN
    14. 10. Using Common Table Expressions
    15. Example: Recursive CTE (Hierarchy)

  13. Manipulating Data with SQL DML

    1. 1. Inserting Single Rows
    2. 2. Inserting Multiple Rows
    3. Example: Multi-row Insert
    4. 3. Inserting from SELECT
    5. 4. Updating Rows
    6. 5. Updating with Joins
    7. Example: UPDATE FROM (Postgres)
    8. 6. Deleting Rows
    9. 7. Truncating Tables
    10. 8. Using MERGE/UPSERT
    11. Example: MERGE
    12. 9. Returning Affected Rows
    13. 10. Handling DML Errors

  14. Using SQL Functions

    1. 1. Using String Functions
    2. 2. Using Numeric Functions
    3. 3. Using Date Functions
    4. 4. Using Conversion Functions
    5. 5. Using Conditional Functions
    6. 6. Using Window Functions
    7. Example: Greatest-N-per-group
    8. 7. Using Aggregate Window Functions
    9. 8. Creating User-Defined Functions
    10. Example: PL/pgSQL Function
    11. 9. Creating Scalar Functions
    12. 10. Creating Table-Valued Functions
    13. Example: SETOF Function (PG)

  15. Managing SQL Transactions

    1. 1. Starting Transactions
    2. 2. Committing Changes
    3. 3. Rolling Back Changes
    4. 4. Creating Savepoints
    5. Example: SAVEPOINT
    6. 5. Understanding ACID Properties
    7. 6. Setting Isolation Levels
    8. 7. Handling Concurrency Issues
    9. 8. Using Optimistic Locking
    10. Example: Optimistic Lock
    11. 9. Using Pessimistic Locking
    12. 10. Managing Deadlocks

  16. Understanding Database Transactions

    1. 1. Understanding Transaction Lifecycle
    2. 2. Understanding Atomicity
    3. 3. Understanding Consistency
    4. 4. Understanding Isolation
    5. 5. Understanding Durability
    6. 6. Understanding Transaction Logs
    7. 7. Handling Long-Running Transactions
    8. 8. Using Distributed Transactions
    9. 9. Implementing Compensating Transactions
    10. 10. Optimizing Transaction Performance

  17. Working with SQL Views

    1. 1. Creating Views
    2. 2. Querying Views
    3. 3. Creating Updatable Views
    4. 4. Using Views for Security
    5. Example: Per-User View
    6. 5. Creating Materialized Views
    7. 6. Refreshing Materialized Views
    8. 7. Indexing Materialized Views
    9. 8. Dropping Views
    10. 9. Managing View Dependencies
    11. 10. Optimizing View Performance

  18. Working with Stored Procedures

    1. 1. Creating Stored Procedures
    2. 2. Defining Procedure Parameters
    3. 3. Using Variables in Procedures
    4. Example: PL/pgSQL Variables
    5. 4. Implementing Control Flow
    6. 5. Handling Errors in Procedures
    7. 6. Executing Stored Procedures
    8. 7. Returning Result Sets
    9. 8. Using Dynamic SQL in Procedures
    10. 9. Managing Procedure Security
    11. 10. Optimizing Procedure Performance

  19. Working with Database Triggers

    1. 1. Creating Triggers
    2. Example: Audit Trigger (PG)
    3. 2. Understanding Trigger Types
    4. 3. Implementing Row-Level Triggers
    5. 4. Implementing Statement-Level Triggers
    6. 5. Using Trigger Events
    7. 6. Accessing Old and New Values
    8. 7. Implementing Audit Trails with Triggers
    9. 8. Cascading Trigger Execution
    10. 9. Disabling and Enabling Triggers
    11. 10. Managing Trigger Performance Impact

  20. Working with SQL Partitioning

    1. 1. Understanding Partitioning Benefits
    2. 2. Creating Range Partitions
    3. Example: Postgres Range Partitioning
    4. 3. Creating List Partitions
    5. 4. Creating Hash Partitions
    6. 5. Creating Composite Partitions
    7. 6. Querying Partitioned Tables
    8. 7. Managing Partitions
    9. 8. Indexing Partitioned Tables
    10. 9. Maintaining Partition Statistics
    11. 10. Migrating Data Between Partitions

  21. Optimizing SQL Query Performance

    1. 1. Analyzing Query Execution Plans
    2. Example: EXPLAIN ANALYZE
    3. 2. Identifying Performance Bottlenecks
    4. 3. Optimizing WHERE Clause Predicates
    5. 4. Using Index Hints and Query Hints
    6. 5. Rewriting Queries for Performance
    7. 6. Optimizing Joins
    8. 7. Avoiding Full Table Scans
    9. 8. Using Query Caching
    10. 9. Optimizing Aggregate Queries
    11. 10. Profiling Query Execution Time

  22. Managing Database Performance

    1. 1. Monitoring Database Metrics
    2. 2. Analyzing Slow Queries
    3. 3. Tuning Database Configuration Parameters
    4. 4. Managing Connection Pooling
    5. 5. Implementing Query Result Caching
    6. 6. Optimizing Buffer Pool and Memory Usage
    7. 7. Managing Disk I/O Performance
    8. 8. Implementing Read Replicas for Load Distribution
    9. 9. Using Database Profiling Tools
    10. 10. Establishing Performance Baselines

  23. Implementing Database Sharding

    1. 1. Understanding Sharding Concepts
    2. 2. Designing Shard Keys
    3. 3. Implementing Range-Based Sharding
    4. 4. Implementing Hash-Based Sharding
    5. 5. Implementing Directory-Based Sharding
    6. 6. Managing Cross-Shard Queries
    7. 7. Handling Shard Rebalancing
    8. 8. Implementing Consistent Hashing
    9. 9. Managing Shard Failover
    10. 10. Designing for Shard Scalability

  24. Managing Database Connections

    1. 1. Understanding Connection Lifecycle
    2. Connection Lifecycle
    3. 2. Configuring Connection Pooling
    4. 3. Setting Pool Size Parameters
    5. 4. Implementing Connection Timeouts
    6. 5. Handling Connection Leaks
    7. 6. Using Connection Validation
    8. 7. Managing Connection Credentials
    9. 8. Implementing Connection Retry Logic
    10. 9. Monitoring Connection Pool Metrics
    11. 10. Optimizing Connection Pool Performance

  25. Implementing Database Security

    1. 1. Creating Database Users and Roles
    2. 2. Granting and Revoking Permissions
    3. 3. Implementing Row-Level Security
    4. Example: Postgres RLS
    5. 4. Implementing Column-Level Security
    6. 5. Using Database Encryption
    7. 6. Implementing SQL Injection Prevention
    8. 7. Auditing Database Access
    9. 8. Managing Database Authentication
    10. 9. Implementing Principle of Least Privilege
    11. 10. Securing Database Connections

  26. Managing Database Backup and Recovery

    1. 1. Understanding Backup Types
    2. 2. Creating Database Backups
    3. 3. Scheduling Automated Backups
    4. 4. Implementing Point-in-Time Recovery
    5. 5. Restoring Databases from Backups
    6. Restore Procedure
    7. 6. Testing Backup and Restore Procedures
    8. 7. Managing Backup Storage and Retention
    9. 8. Implementing Hot vs Cold Backups
    10. 9. Handling Database Corruption Recovery
    11. 10. Documenting Recovery Procedures

  27. Implementing Database Replication

    1. 1. Understanding Replication Types
    2. 2. Configuring Replication Topology
    3. 3. Implementing Synchronous Replication
    4. 4. Implementing Asynchronous Replication
    5. 5. Handling Replication Lag
    6. 6. Managing Failover and Switchover
    7. 7. Implementing Read Replicas
    8. 8. Resolving Replication Conflicts
    9. 9. Monitoring Replication Health
    10. 10. Testing Disaster Recovery Scenarios

  28. Implementing Database Monitoring

    1. 1. Monitoring Database Health Metrics
    2. 2. Tracking Query Performance
    3. 3. Monitoring Lock and Wait Statistics
    4. 4. Tracking Connection Utilization
    5. 5. Monitoring Disk Space Usage
    6. 6. Setting Up Alerting Thresholds
    7. 7. Using Database Dashboards
    8. 8. Implementing Log Aggregation
    9. 9. Tracking Replication Lag Metrics
    10. 10. Establishing Monitoring Baselines

  29. Implementing Data Archiving Strategies

    1. 1. Identifying Archive Candidates
    2. 2. Designing Archive Schemas
    3. 3. Implementing Tiered Storage
    4. 4. Creating Archive Policies
    5. 5. Managing Archive Retention Periods
    6. 6. Implementing Data Purging Procedures
    7. 7. Querying Archived Data
    8. 8. Handling Compliance Requirements
    9. 9. Implementing Archive Compression
    10. 10. Testing Archive Restore Procedures

  30. Modeling Hierarchical Data Structures

    1. 1. Using Adjacency List Model
    2. Example: Adjacency List
    3. 2. Using Nested Set Model
    4. 3. Using Path Enumeration Model
    5. 4. Using Closure Table Model
    6. Example: Closure Table
    7. 5. Comparing Hierarchical Models
    8. 6. Querying Hierarchical Data
    9. 7. Modeling Tree Structures
    10. 8. Modeling Graph Structures
    11. 9. Handling Hierarchical Data Updates
    12. 10. Optimizing Hierarchical Queries

  31. Modeling Temporal and Historical Data

    1. 1. Understanding Temporal Data Types
    2. 2. Implementing Bi-Temporal Tables
    3. Example: Bi-Temporal Row
    4. 3. Modeling Effective Dating
    5. 4. Designing Historical Tracking Tables
    6. 5. Implementing Event Sourcing Schemas
    7. 6. Modeling Point-in-Time Queries
    8. Example: AS OF Query (SQL:2011)
    9. 7. Designing Temporal Foreign Keys
    10. 8. Handling Temporal Data Updates
    11. 9. Implementing Slowly Changing Dimensions
    12. 10. Optimizing Temporal Query Performance

  32. Modeling Time-Series Data

    1. 1. Designing Time-Series Schemas
    2. 2. Partitioning by Time
    3. 3. Implementing Downsampling Strategies
    4. 4. Designing Data Retention Policies
    5. 5. Indexing Time-Based Queries
    6. 6. Handling Out-of-Order Data
    7. 7. Implementing Compression Strategies
    8. 8. Designing Rollup Tables
    9. 9. Managing Hot and Cold Data Storage
    10. 10. Optimizing Time-Series Query Patterns

  33. Implementing Multi-Tenancy Data Models

    1. 1. Using Separate Databases per Tenant
    2. 2. Using Separate Schemas per Tenant
    3. 3. Using Shared Schema with Tenant ID
    4. Example: Shared Schema
    5. 4. Implementing Row-Level Isolation
    6. 5. Designing Tenant-Specific Configurations
    7. 6. Modeling Shared Reference Data
    8. 7. Implementing Tenant Data Partitioning
    9. 8. Designing for Tenant Scalability
    10. 9. Handling Cross-Tenant Queries
    11. 10. Ensuring Tenant Data Security

  34. Implementing Data Modeling Patterns

    1. 1. Using Attribute Pattern
    2. 2. Using Extended Reference Pattern
    3. 3. Using Subset Pattern
    4. 4. Using Computed Pattern
    5. 5. Using Bucket Pattern
    6. 6. Using Outlier Pattern
    7. 7. Using Schema Versioning Pattern
    8. 8. Using Polymorphic Pattern
    9. 9. Using Approximation Pattern
    10. 10. Using Tree Pattern

  35. Implementing Data Validation

    1. 1. Validating Data Types
    2. 2. Enforcing Required Fields
    3. 3. Implementing Range Validation
    4. 4. Using Regular Expressions for Patterns
    5. 5. Implementing Custom Validation Rules
    6. 6. Handling Validation Errors
    7. 7. Validating Relationships and References
    8. 8. Implementing Server-Side Validation
    9. 9. Using Schema Validation
    10. 10. Balancing Validation vs Performance

  36. Implementing Schema Evolution

    1. 1. Adding Columns Safely
    2. 2. Removing Deprecated Columns
    3. Deprecation Workflow
    4. 3. Renaming Columns Without Downtime
    5. Expand-Contract Rename
    6. 4. Changing Column Data Types
    7. 5. Adding Indexes Online
    8. 6. Modifying Constraints Safely
    9. 7. Handling Backward Compatibility
    10. 8. Implementing Forward Compatibility
    11. 9. Documenting Schema Changes
    12. 10. Testing Schema Changes

  37. Implementing Database Migrations

    1. 1. Choosing Migration Tools
    2. 2. Writing Forward Migrations
    3. Example: Flyway SQL Migration
    4. 3. Implementing Reversible Migrations
    5. 4. Managing Migration Versions
    6. 5. Running Migrations in CI/CD
    7. 6. Handling Migration Failures
    8. 7. Implementing Zero-Downtime Migrations
    9. 8. Coordinating Schema and Code Changes
    10. Coordinated Release
    11. 9. Testing Migrations in Lower Environments
    12. 10. Documenting Migration Procedures

  38. Modeling NoSQL Data Structures

    1. 1. Understanding NoSQL Data Models
    2. 2. Designing Schemaless Collections
    3. 3. Modeling Embedded Documents
    4. 4. Modeling Document References
    5. 5. Implementing Denormalization
    6. 6. Designing for Access Patterns
    7. Access-Pattern-First Modeling
    8. 7. Modeling Many-to-Many Relationships
    9. 8. Handling Schema Migrations in NoSQL
    10. 9. Modeling Aggregates
    11. 10. Choosing Document Granularity

  39. Implementing Document Databases

    1. 1. Storing JSON Documents
    2. 2. Creating and Updating Documents
    3. Example: MongoDB Insert & Update
    4. 3. Indexing Document Fields
    5. 4. Implementing Document Validation
    6. Example: $jsonSchema Validator
    7. 5. Managing Document Versioning
    8. 6. Implementing Atomic Updates
    9. 7. Designing Sub-Document Structures
    10. 8. Handling Array Fields
    11. 9. Implementing Full-Text Search
    12. 10. Optimizing Document Storage

  40. Querying Document Databases

    1. 1. Using Document Query Operators
    2. 2. Filtering Nested Fields
    3. Example: Nested Filter
    4. 3. Performing Aggregation Pipelines
    5. Example: Aggregation Pipeline
    6. 4. Joining Documents
    7. Example: $lookup
    8. 5. Sorting and Pagination
    9. 6. Using Map-Reduce
    10. 7. Performing Text Search
    11. Example: Text Search
    12. 8. Using Geospatial Queries
    13. 9. Optimizing Document Queries
    14. 10. Analyzing Query Performance

  41. Implementing Key-Value Stores

    1. 1. Designing Key Naming Conventions
    2. 2. Storing Simple Values
    3. Example: Redis Strings
    4. 3. Implementing Complex Data Structures
    5. 4. Setting TTL and Expiration
    6. 5. Implementing Atomic Operations
    7. 6. Using Key-Value for Caching
    8. 7. Implementing Session Storage
    9. 8. Designing for High Throughput
    10. 9. Implementing Persistence Strategies
    11. 10. Handling Hot Keys

  42. Implementing Column-Family Stores

    1. 1. Designing Column Families
    2. Example: Cassandra Table
    3. 2. Modeling Wide Rows
    4. 3. Designing Partition Keys
    5. 4. Implementing Clustering Columns
    6. 5. Designing for Time-Series Data
    7. 6. Modeling for Write-Heavy Workloads
    8. 7. Implementing Secondary Indexes
    9. 8. Querying with CQL
    10. Example: CQL Queries
    11. 9. Managing Consistency Levels
    12. 10. Optimizing Column-Family Queries

  43. Implementing Graph Databases

    1. 1. Modeling Nodes and Relationships
    2. 2. Designing Graph Schemas
    3. 3. Using Cypher Query Language
    4. Example: Cypher
    5. 4. Implementing Graph Traversal
    6. 5. Modeling Properties on Edges
    7. 6. Implementing Path Finding Algorithms
    8. 7. Modeling Social Networks
    9. 8. Implementing Recommendation Systems
    10. 9. Querying Complex Patterns
    11. Example: Fraud Ring Detection
    12. 10. Optimizing Graph Queries

  44. Working with JSON and Semi-Structured Data

    1. 1. Storing JSON in SQL Databases
    2. 2. Querying JSON Fields
    3. Example: PG JSONB Queries
    4. 3. Indexing JSON Documents
    5. 4. Validating JSON Structure
    6. 5. Modeling Hybrid Relational-JSON
    7. 6. Handling XML Data
    8. 7. Implementing JSON Path Expressions
    9. 8. Converting Between JSON and Tables
    10. Example: jsonb_to_recordset / json_table
    11. 9. Designing Flexible Schemas
    12. 10. Optimizing JSON Query Performance

  45. Making SQL vs NoSQL Decisions

    1. 1. Analyzing Data Structure Requirements
    2. 2. Evaluating Query Complexity
    3. 3. Assessing Consistency Requirements
    4. 4. Considering Scalability Needs
    5. 5. Evaluating Transaction Requirements
    6. 6. Comparing SQL and NoSQL Performance
    7. 7. Assessing Operational Complexity
    8. 8. Evaluating Team Expertise
    9. 9. Choosing Polyglot Persistence
    10. 10. Making Database Migration Decisions
    11. Migration Decision Workflow