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.
