Web DevelopmentData Science

DBMS Cheat Sheet for Interview 2025

Last Updated: 19th March, 2025

Jay Abhani

Senior Web Development Instructor at almaBetter

A complete DBMS cheat sheet covering key concepts, SQL commands, normalization, indexing and transactions. Perfect for interview preparation and quick reference.

A Database Management System (DBMS) is essential for efficiently storing, retrieving, and managing data in applications. Whether you are preparing for an interview or need a quick reference guide, this DBMS cheat sheet covers key concepts, terminology, and SQL commands in a structured way.

This database management system cheat sheet provides a concise yet detailed overview of DBMS principles, architectures, normalization, indexing, transactions, and SQL queries to help you master DBMS for interviews and real-world applications.

What is DBMS?

A Database Management System (DBMS) is software that allows users to create, read, update, and delete (CRUD) data efficiently. It acts as an intermediary between the user and the database.

Key Functions of DBMS:

Hrere are the keyw features of DBMS (Database Management System):

Data Definition – Creating and modifying schema.
Data Manipulation – Querying and updating records.
Data Security – Ensuring authorized access.
Concurrency Control – Managing multiple users.
Transaction Management – Maintaining consistency.
Backup & Recovery – Preventing data loss.

Types of Databases

DBMS can be classified into various types based on structure and data storage:

Based on Data Model

Type	Description	Example
Relational DBMS (RDBMS)	Data stored in tables with relationships	MySQL, PostgreSQL, Oracle
NoSQL DBMS	Schema-less, suitable for big data	MongoDB, Cassandra
Hierarchical DBMS	Data arranged in a tree structure	IBM Information Management System
Network DBMS	Complex relationships using graph structure	IDS (Integrated Data Store)
Object-Oriented DBMS	Stores objects rather than tables	db4o, ObjectDB

DBMS Architecture

1-Tier, 2-Tier, 3-Tier Architecture

Architecture	Description
1-Tier	Direct access between the user and database (e.g., local storage).
2-Tier	Client connects to the database via an application (e.g., JDBC).
3-Tier	Client → Application Server → Database (e.g., Web Applications).

DBMS Components

Database Engine – Manages data storage and retrieval.
Query Processor – Interprets and executes SQL queries.
Transaction Manager – Handles concurrent access.
Storage Manager – Controls physical storage.
Metadata Catalog – Stores schema information.

Learn more with these related lessons: Types of Databases, Integrity Constraints in DBMS and Data Models in DBMS

Relational Database Model

A Relational DBMS (RDBMS) organizes data into tables (relations) consisting of rows and columns.

Key Terminologies

Term	Definition
Relation (Table)	A collection of rows and columns.
Tuple (Row)	A single record in a table.
Attribute (Column)	A property of an entity.
Primary Key	A unique identifier for a row.
Foreign Key	A reference to a primary key in another table.
Candidate Key	A set of attributes that uniquely identify a record.
Super Key	A set of attributes that uniquely identify records (includes candidate keys).
Composite Key	A key consisting of multiple attributes.

Relational Integrity Constraints

Primary Key Constraint – Ensures uniqueness.
Foreign Key Constraint – Ensures referential integrity.
Not Null Constraint – Prevents null values.
Unique Constraint – Ensures uniqueness for non-primary key columns.
Check Constraint – Defines rules for column values.

Normalization in DBMS

Normalization in DBMS is the process of organizing data to reduce redundancy and improve data integrity.

Normal Forms

Normal Form	Description
1NF (First Normal Form)	Removes duplicate columns; ensures atomicity.
2NF (Second Normal Form)	Removes partial dependencies (if primary key is composite).
3NF (Third Normal Form)	Removes transitive dependencies.
BCNF (Boyce-Codd Normal Form)	Ensures no redundant dependencies.
4NF (Fourth Normal Form)	Removes multi-valued dependencies.
5NF (Fifth Normal Form)	Ensures lossless decomposition of tables.

Transactions and Concurrency Control

What is a Transaction?

A transaction is a sequence of database operations that must be executed as a single unit.

ACID Properties

Property	Description
Atomicity	Ensures all operations are completed or none at all.
Consistency	Ensures database integrity after the transaction.
Isolation	Ensures transactions execute independently.
Durability	Ensures completed transactions persist even after failures.

Concurrency Control Techniques

Lock-Based Protocols – Shared & Exclusive Locks.
Timestamp Ordering – Transactions ordered by timestamps.
Optimistic Concurrency Control – Validation phase before commit.

Indexing in DBMS

Indexes improve query performance by reducing search time.

Types of Indexes

Type	Description
Primary Index	Automatically created for the primary key.
Secondary Index	Created for non-primary key attributes.
Clustered Index	Determines the physical order of data.
Non-Clustered Index	Creates a separate structure for quick lookups.
Hash Index	Uses hashing for direct access.

SQL Cheat Sheet

Data Definition Language (DDL)

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT NOT NULL
);

ALTER TABLE students ADD COLUMN email VARCHAR(100);

DROP TABLE students;

Data Manipulation Language (DML)

INSERT INTO students (id, name, age) VALUES (1, 'John', 22);

UPDATE students SET age = 23 WHERE id = 1;

DELETE FROM students WHERE id = 1;

Data Query Language (DQL)

SELECT * FROM students WHERE age > 20;

Data Control Language (DCL)

GRANT SELECT, INSERT ON students TO user1;

REVOKE INSERT ON students FROM user1;

Transaction Control Language (TCL)

BEGIN TRANSACTION;
UPDATE students SET age = 24 WHERE id = 2;
COMMIT;

ROLLBACK;

Learn more with these related lessons: DML and DDL in SQL & SQL Interview Questions

DBMS Interview Cheat Sheet

Common Interview Questions

Q1. What is the difference between DBMS and RDBMS?

Ans: RDBMS stores data in tables with relationships, while DBMS may use various structures.

Q2. What are the differences between SQL and NoSQL?

Ans: SQL uses structured tables, while NoSQL supports flexible document-based storage.

Q3. Explain normalization and denormalization.

Ans: Normalization removes redundancy; denormalization increases performance by allowing duplicates.

Q4. What is indexing, and why is it used?

Ans: Indexing speeds up data retrieval by reducing search operations.

Q5. What are ACID properties in DBMS?

Ans: Atomicity, Consistency, Isolation, and Durability ensure reliable transactions.

Cheat Sheet for DBMS - Quick Guide

Topic	Quick Summary
Normalization	Reduces redundancy and improves integrity.
ACID Properties	Ensure reliable transactions.
Indexes	Improve search speed.
SQL Commands	DDL, DML, DQL, DCL, TCL.
Joins in SQL	INNER, LEFT, RIGHT, FULL OUTER.
Locks	Prevent data conflicts in concurrent transactions.

Conclusion

This DBMS cheat sheet for interviews provides a structured quick reference guide covering core DBMS concepts, SQL commands, normalization, transactions, indexing, and interview FAQs. Mastering these topics will help you ace your DBMS interviews and efficiently work with databases in real-world applications.

More Cheat Sheets and Top Picks