Database Management Systems

Data and Information

File System vs DBMS: Understanding the Evolution of Data Management

Understanding DBMS Concepts

DBMS Advantages: Why Database Systems Excel

Database Users

DBMS Applications Across Industries

Three-Level Architecture

Database Schemas and Instances

Data Independence: The Foundation of Database Flexibility

DBMS Components: The Internal Architecture

Database Languages: The Complete Language Toolkit

Client-Server Architecture in Database Systems

Data Model Concepts

The Hierarchical Data Model

The Network Data Model

The Relational Model

Object-Oriented Database Model

Document and Other Data Models

Design Phases

Requirements Gathering for Database Design

Conceptual Design: From Requirements to Data Models

Logical Design: From Concepts to Schemas

Physical Design

Database Design Methodologies

Relations and Tables: The Foundation of Relational Databases

Attributes and Domains in the Relational Model

Tuples and Rows: The Atomic Units of Relational Data

Keys in the Relational Model

Integrity Constraints in the Relational Model

Relational Model Notation

Relational Algebra Overview

Selection and Projection

Set Operations in Relational Algebra

Cartesian Product and Rename Operations

Join Operations in Relational Algebra

Division Operation in Relational Algebra

Calculus vs Algebra: Two Paths to Query Power

Tuple Relational Calculus

Quantifiers in Relational Calculus

Safe Queries in Relational Calculus

Domain Relational Calculus

Expressive Power of Relational Languages

Mapping Overview

Entity Mapping

Mapping One-to-One Relationships

Mapping One-to-Many Relationships to Relations

Relationship Mapping (M:N)

Specialization Mapping: ER to Relational

ER Model Overview

Entities and Entity Sets

Attributes in ER Modeling

Relationships in ER Modeling

Cardinality in ER Modeling

Participation Constraints in ER Modeling

Weak Entities in ER Modeling

Identifying Relationships in ER Modeling

Ternary Relationships

Self-Referential Relationships

Aggregation

ER Diagram Best Practices

Specialization in Enhanced ER Modeling

Inheritance in Enhanced ER Modeling

Enhanced Entity-Relationship Model Overview

Generalization in EER Modeling

Constraints on Specialization

Categories and Union Types in EER Modeling

SQL Overview

Data Definition Language (DDL)

SQL Data Types

Constraints in DDL

Data Manipulation Language (DML)

SQL Execution Flow

SELECT Basics: Mastering Data Retrieval

The WHERE Clause: Filtering Data with Precision

NULL Handling in SQL

Logical Operators

ORDER BY: Mastering Result Set Ordering

DISTINCT and LIMIT: Controlling Result Sets

Category Discounted Product Pricing

Customer Referral Eligibility

Employee Low Bonus Audit

Large-Country Screening Report

Join Concepts

Mastering Inner Joins in SQL

Outer Joins: Preserving Unmatched Data

Other Join Types: Beyond Basic Joins

SQL Subqueries: Nested Query Power

Correlated Subqueries

Adjacent Free Seat Finder

Binary Hierarchy Node Role Tagging

Double Round-Robin League Fixtures

Employee Identity Bridge Report

SQL Aggregate Functions: Summarizing Data with Precision

GROUP BY: Categorizing and Summarizing Data

HAVING Clause: Filtering Grouped Data

Window Functions Basics

SQL Ranking Functions

Analytic Functions

Accounts Above Net Balance Threshold

Ad Campaign Click-Through Rate Ranking

Artist Presence Frequency on Streaming Chart

Average Process Duration by Machine

SQL Views: Virtual Tables and Data Abstraction

Common Table Expressions (CTEs)

Stored Procedures

SQL Functions

Triggers

Cursors in SQL

Latest Employee Compensation Snapshot

Minimum Coordinate Gap on a Line

Rapid Repeat Confirmation Requesters

State City Directory Rollup

Query Performance Basics

Understanding EXPLAIN Plans

Index Usage for Query Optimization

Query Optimization Tips

Common SQL Performance Issues

Query Profiling

Functional Dependency Concept

Armstrong's Axioms

Attribute Closure

Equivalence of Functional Dependency Sets

Canonical Cover: Minimizing Functional Dependencies

Functional Dependencies and Keys

Second Normal Form: Eliminating Partial Dependencies

Third Normal Form (3NF)

Understanding Database Normalization

First Normal Form (1NF): Foundation of Normalization

Lossless Join Decomposition

Dependency Preserving Decomposition

Boyce-Codd Normal Form (BCNF)

BCNF Decomposition

Multivalued Dependencies

Fourth Normal Form (4NF)

Join Dependencies and Fifth Normal Form

Normal Form Comparison

Understanding Denormalization

Denormalization Techniques

Performance Considerations in Denormalization

Data Integrity Challenges in Denormalization

OLTP vs OLAP Considerations

Denormalization Patterns

Transaction Definition

ACID Properties: The Foundation of Reliable Transactions

Transaction States

Conflict Serializability

Recoverability

View Serializability

An unsuccessful credit capture changes account balances

Capture reserved credit only once

Understanding Concurrency in Database Systems

Understanding the Lost Update Problem

The Dirty Read Problem: Reading Uncommitted Data

Unrepeatable Read Problem

The Phantom Read Problem

Isolation Levels

Lock Concepts

Two-Phase Locking Protocol

Two-Phase Locking Variants

Lock Granularity

Deadlock Handling in Database Systems

Starvation in Concurrency Control

Basic Timestamp Protocol

Timestamp-Based vs Lock-Based Concurrency Control

Thomas Write Rule

Snapshot Isolation: Consistent Views for Concurrent Transactions

Timestamp Ordering Protocol

Multi-Version Concurrency Control (MVCC)

Recovery Concepts

Write-Ahead Logging (WAL)

Understanding Database Failure Types

Log-Based Recovery

Checkpoints in Database Recovery

Stable Storage: Foundations of Durability

Recovery Algorithm Types

ARIES Recovery Algorithm Overview

ARIES Analysis Phase: Reconstructing System State

ARIES Redo Phase: Reconstructing Consistency

Undo Phase in ARIES Recovery

ARIES Advanced Features and Optimizations

Failed release journals leave a partially published room

Isolate the editorial transaction draft

Understanding Database Backup Types

Backup Implementation Strategies

Point-in-Time Recovery

Backup Verification

Disaster Recovery

Backup Best Practices

Storage Hierarchy in Database Systems

Disk Structure: Understanding Physical Storage

RAID Storage Technology

Buffer Management in Database Systems

File Organization Methods

Page Layout and Organization

Understanding Database Indexes

Index Types: Primary, Secondary, Unique, and Composite Indexes

Dense vs Sparse Indexes

Clustered vs Non-Clustered Indexes

Multi-Level Index Structures

Index Terminology: Essential Concepts and Metrics

B-Tree Concept

B-Tree Operations: Search, Insert, and Delete

B+-Tree Structure

B+-Tree Operations

B+-Tree Performance Analysis

B+-Tree Variants and Optimizations

Hash Index Concept

Static Hashing: Fundamentals and Implementation

Dynamic Hashing

Extendible Hashing

Linear Hashing

Hash vs Tree Indexes: Comparative Analysis

Bitmap Indexes: Specialized Indexing for Analytical Workloads

Spatial Indexes: Indexing Multi-Dimensional Data

Full-Text Indexing and Search

Covering Indexes: Optimizing Query Performance Through Index Design

Multi-Column Indexes

Index Selection: The Art and Science of Strategic Indexing

Query Processing Steps

Parsing and Validation

Query Representation

Cost Models in Query Processing

Query Execution Engine

Query Compilation

Optimization Overview

Equivalence Rules

Heuristic Optimization

Cost-Based Query Optimization

Join Ordering in Query Optimization

Statistics and Histograms

Access Methods: How Databases Retrieve Data

Selection Implementation

Nested Loop Join Algorithms

Sort-Merge Join: Algorithm, Implementation, and Optimization

Hash Join Algorithms

Other Physical Operators

Distributed Database Concepts

Data Fragmentation in Distributed Databases

Replication in Distributed Databases

Distributed Transactions

CAP Theorem: Fundamental Trade-offs in Distributed Systems

Sharding: Horizontal Scaling for Distributed Databases

NoSQL Overview: Understanding the NoSQL Paradigm

Key-Value Stores

Document Databases: The Schema-Flexible NoSQL Paradigm

Column-Family Databases

Graph Databases: Modeling Connected Data

NoSQL vs SQL: A Comprehensive Comparison

OLTP vs OLAP: Understanding Transaction and Analytical Processing

Data Warehouse Concepts

Star Schema: The Foundation of Dimensional Modeling

Snowflake Schema Design

ETL Process: Extract, Transform, Load

OLAP Operations: Multidimensional Data Analysis

Bike Fleet Last Usage Timestamp

Buyer Yearly Order Volume Report

Calendar Date Long-Format Renderer

Country November Climate Banding

Implement grouped research-window reporting with Spark

Aggregate event counts and subject diversity in separate columns

NewSQL Databases

In-Memory Databases

Time-Series Databases

Cloud Databases

Multi-Model Databases: Unified Data Management

Database Trends: The Future of Data Management

Database Administrator Responsibilities

Performance Tuning

Database Security

Monitoring and Alerting

Capacity Planning

Database Maintenance Tasks

SQL Query Writing for Interviews

Mastering DBMS Interview Strategies

Schema Design Problems

Normalization Problems: Mastering Database Design Challenges

Solving Transaction Problems in DBMS Interviews

System Design Questions for Database Systems