Database Management Systems (DBMS) Cheatsheet

1. Introduction to DBMS

Definition

• A Database Management System (DBMS) is software designed to manage, store, and retrieve data from a database.

Key Components

• Database: Collection of related data.
• DBMS Engine: Core component responsible for managing data storage, retrieval, and manipulation.
• Database Schema: Structure that defines the organization of data in the database.
• Query Language: Interface for interacting with the database (e.g., SQL).

2. Types of DBMS

Relational DBMS (RDBMS)

• Organizes data into tables with rows and columns.
• Examples: MySQL, PostgreSQL, Oracle Database.

NoSQL DBMS

• Designed for non-relational data storage and retrieval.
• Types: Document-oriented, key-value stores, column stores, graph databases.
• Examples: MongoDB, Cassandra, Redis.

Object-Oriented DBMS (OODBMS)

• Stores data as objects rather than rows and columns.
• Examples: db4o, ObjectDB.

3. Database Design

Entity-Relationship (ER) Model

• Represents entities, attributes, and relationships between entities.
• Entities: Objects or concepts about which data is stored.
• Relationships: Associations between entities.

Normalization

• Process of organizing data in a database to reduce redundancy and improve data integrity.
• Normal Forms: 1NF, 2NF, 3NF, BCNF.

Indexes

• Data structure that improves the speed of data retrieval operations on a database table.
• Types: Clustered, Non-clustered.

4. Querying and Manipulating Data

Structured Query Language (SQL)

• Standard language for relational database management and manipulation.
• Operations: SELECT, INSERT, UPDATE, DELETE.

Data Definition Language (DDL)

• SQL commands for defining and managing database structures.
• Commands: CREATE, ALTER, DROP.

Data Manipulation Language (DML)

• SQL commands for manipulating data within database tables.
• Commands: INSERT, UPDATE, DELETE.

5. Transaction Management

ACID Properties

• Atomicity: All operations in a transaction must be completed successfully, or none of them are.
• Consistency: Database remains in a consistent state before and after a transaction.
• Isolation: Transactions are isolated from each other until they are completed.
• Durability: Changes made by a transaction are permanent and survive system failures.

Concurrency Control

• Mechanisms to manage simultaneous access to data by multiple transactions.
• Techniques: Locking, timestamp-based protocols, multiversion concurrency control.

6. Data Security and Integrity

Access Control

• Regulates who can access the database and what actions they can perform.
• Techniques: Role-based access control (RBAC), discretionary access control (DAC).

Data Encryption

• Protects sensitive data by converting it into a form that cannot be easily understood without authorization.
• Techniques: Symmetric encryption, asymmetric encryption.

7. Backup and Recovery

Backup Strategies

• Full Backup: Backup of the entire database.
• Incremental Backup: Backup of changes made since the last backup.
• Differential Backup: Backup of changes made since the last full backup.

Recovery Techniques

• Rollback: Undo changes made by incomplete transactions.
• Rollforward: Apply changes from backup to restore the database to a consistent state.

8. Distributed Databases

Definition

• Database system in which data is stored and processed across multiple locations or nodes.
• Types: Homogeneous, heterogeneous.

Replication

• Copying and storing data in multiple locations to improve availability and fault tolerance.
• Types: Snapshot replication, transactional replication.

Partitioning

• Dividing a database into smaller subsets (partitions) to distribute data across multiple nodes.
• Types: Horizontal partitioning, vertical partitioning.