# Database Management Systems (DBMS) A Database Management System (DBMS) is software that manages [[Database|databases]], providing an interface between users/applications and the data. It handles storage, retrieval, security, backup, and concurrent access to data. The DBMS abstracts the physical storage details, allowing users to interact with data through high-level languages like [[SQL]] without needing to understand how data is stored on disk. ## Core Functions - **Data storage management**: Efficiently store and organize data on disk - **Query processing**: Parse, optimize, and execute queries - **Transaction management**: Ensure ACID properties for data integrity - **Concurrency control**: Handle multiple simultaneous users safely - **Security**: Authentication, authorization, and access control - **Backup and recovery**: Protect against data loss and corruption - **Data integrity**: Enforce constraints and relationships ## DBMS Architecture ### Three-Schema Architecture 1. **External level**: User views of the data (what users see) 2. **Conceptual level**: Logical structure of the entire database 3. **Internal level**: Physical storage details ### Components ``` ┌─────────────────────────────────────┐ │ Application/User │ └─────────────────┬───────────────────┘ │ ┌─────────────────▼───────────────────┐ │ Query Processor │ │ (Parser, Optimizer, Executor) │ └─────────────────┬───────────────────┘ │ ┌─────────────────▼───────────────────┐ │ Transaction Manager │ │ (Concurrency, Recovery) │ └─────────────────┬───────────────────┘ │ ┌─────────────────▼───────────────────┐ │ Storage Manager │ │ (Buffer, File, Disk Management) │ └─────────────────┬───────────────────┘ │ ┌─────────────────▼───────────────────┐ │ Data Files │ └─────────────────────────────────────┘ ``` ## Types of DBMS ### By Data Model | Type | Description | Examples | |------|-------------|----------| | Relational (RDBMS) | Tables with rows and columns | [[PostgreSQL]], [[SQLite]], MySQL | | Document | JSON/BSON documents | MongoDB, CouchDB | | Key-Value | Simple key-value pairs | Redis, DynamoDB | | Graph | Nodes and relationships | Neo4j, Amazon Neptune | | Wide-Column | Column families | Cassandra, HBase | | Time-Series | Optimized for time-stamped data | InfluxDB, TimescaleDB | ### By Architecture - **Centralized**: Single server (traditional) - **Distributed**: Data spread across multiple nodes - **Federated**: Unified view of multiple independent databases - **Cloud-native**: Designed for cloud deployment (Spanner, Aurora) ### By Use Case - **OLTP**: Online Transaction Processing (fast writes, many users) - **OLAP**: Online Analytical Processing (complex queries, reporting) - **HTAP**: Hybrid Transactional/Analytical Processing ## Key Concepts ### Query Optimization The DBMS query optimizer chooses the most efficient execution plan: 1. Parse query into syntax tree 2. Generate possible execution plans 3. Estimate cost of each plan 4. Select lowest-cost plan 5. Execute and return results ### Buffer Management Caches frequently accessed data in memory to reduce disk I/O: - Page replacement policies (LRU, Clock) - Write-ahead logging (WAL) - Dirty page management ### Locking and Concurrency Mechanisms to handle simultaneous access: - **Pessimistic locking**: Lock before access - **Optimistic locking**: Check for conflicts at commit - **MVCC**: Multi-Version Concurrency Control (used by PostgreSQL) ### Recovery Techniques to restore data after failures: - **[[Write-Ahead Logging (WAL)]]**: Log changes before applying - **Checkpointing**: Periodic snapshots of database state - **ARIES**: Algorithm for Recovery and Isolation ## Popular DBMS Software ### Open Source - [[PostgreSQL]]: Feature-rich, standards-compliant - [[SQLite]]: Embedded, serverless - MySQL/MariaDB: Popular for web applications - MongoDB: Document database - Redis: In-memory key-value store ### Commercial - Oracle Database: Enterprise standard - Microsoft SQL Server: Windows ecosystem - IBM Db2: Mainframe and enterprise ### Cloud-Native - Amazon Aurora: MySQL/PostgreSQL compatible - Google Spanner: Globally distributed - CockroachDB: Distributed SQL - PlanetScale: Serverless MySQL ## References - https://en.wikipedia.org/wiki/Database#Database_management_system ## Related - [[Database]] - [[Relational Databases (RDBMS)]] - [[SQL]] - [[SQLite]] - [[PostgreSQL]] - [[DuckDB]] - [[Dolt]] - [[ACID]] - [[Write-Ahead Logging (WAL)]] - [[Atomicity]]