ENGIMY.IO - CHEATSHEET
DBMS × QUICK REFERENCE
DBMS
CORE SUBJECT REFERENCE

DATABASE
MANAGEMENT CHEATSHEET.

A comprehensive reference for Database Management Systems – ER models, normalization, SQL, transactions, indexing, and more. All in one place.

REFERENCE v1.0

DBMS Quick Reference

Everything you need day‑to‑day – design, queries, transactions, and optimisation.

Database Basics

Database System Components
  • DBMS – software (MySQL, PostgreSQL, Oracle)
  • Database – collection of data
  • Schema – logical structure
  • Instance – actual data at a time
  • Data Dictionary – metadata
Database Users
  • DBA – Database Administrator
  • Designer – schema design
  • Developer – application programming
  • End User – queries and reports

Database Languages

  • DDL – Data Definition Language (CREATE, ALTER, DROP)
  • DML – Data Manipulation Language (INSERT, UPDATE, DELETE)
  • DCL – Data Control Language (GRANT, REVOKE)
  • TCL – Transaction Control Language (COMMIT, ROLLBACK, SAVEPOINT)

ER Model

Components
  • Entity – real‑world object (rectangle)
  • Attribute – property (oval)
  • Relationship – association (diamond)
  • Key – unique identifier (underline)
Types of Attributes
  • Simple – atomic
  • Composite – can be divided
  • Single‑valued – one value
  • Multi‑valued – set of values
  • Derived – computed from others

Keys

Types of Keys
  • Super Key – set of attributes that uniquely identify
  • Candidate Key – minimal super key
  • Primary Key – chosen candidate key
  • Alternate Key – candidate keys not chosen
  • Foreign Key – references primary key of another table
  • Composite Key – multiple attributes
  • Surrogate Key – artificial (auto‑increment)
Relationship Types
  • One‑to‑One (1:1)
  • One‑to‑Many (1:N)
  • Many‑to‑Many (M:N)
Participation
  • Total – every entity participates
  • Partial – some entities participate

Normalization

Normal Forms
  • 1NF – atomic values, no repeating groups
  • 2NF – 1NF + no partial dependency on candidate key
  • 3NF – 2NF + no transitive dependency
  • BCNF – every determinant is a candidate key
  • 4NF – BCNF + no multi‑valued dependency
  • 5NF – no join dependency (lossless join)
Dependencies
  • Functional Dependency – X → Y (X determines Y)
  • Partial Dependency – non‑key depends on part of candidate key
  • Transitive Dependency – A → B → C (non‑key → non‑key)
  • Multi‑valued Dependency – X →→ Y (independent multiple values)
  • Closure – set of all attributes determined
  • Armstrong's Axioms – reflexivity, augmentation, transitivity

Normalization Steps

1NF: Atomic values, no repeating groups
2NF: 1NF + remove partial dependencies
3NF: 2NF + remove transitive dependencies
BCNF: All determinants are candidate keys

SQL (Structured Query Language)

DDL Commands

CREATE TABLE table_name (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT CHECK (age >= 0),
    email VARCHAR(255) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE table_name ADD COLUMN phone VARCHAR(20);
ALTER TABLE table_name MODIFY COLUMN name VARCHAR(200);
ALTER TABLE table_name DROP COLUMN phone;

DROP TABLE table_name;
TRUNCATE TABLE table_name;

DML Commands

INSERT INTO table_name (id, name, age) VALUES (1, 'Alice', 25);

UPDATE table_name SET age = 26 WHERE id = 1;

DELETE FROM table_name WHERE id = 1;

DQL (SELECT)

SELECT * FROM employees;
SELECT name, salary FROM employees WHERE department = 'Engineering';
SELECT DISTINCT department FROM employees;

-- Ordering
SELECT * FROM employees ORDER BY salary DESC;

-- Aggregate
SELECT department, COUNT(*), AVG(salary)
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

-- Joins
SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

-- Subqueries
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Views
CREATE VIEW high_salary AS
SELECT name, salary FROM employees WHERE salary > 70000;

Transactions & Concurrency

ACID Properties

  • Atomicity – all or nothing
  • Consistency – valid state before and after
  • Isolation – transactions independent
  • Durability – changes persist after commit
Transaction States
  • Active
  • Partially Committed
  • Committed
  • Aborted
  • Failed

Isolation Levels

Isolation Level Dirty Read Non‑Repeatable Read Phantom Read
Read Uncommitted ✕ ✕ ✕
Read Committed ✓ ✕ ✕
Repeatable Read ✓ ✓ ✕
Serializable ✓ ✓ ✓

Concurrency Issues

  • Dirty Read – reading uncommitted data
  • Lost Update – overwritten by another transaction
  • Non‑Repeatable Read – different results on re‑read
  • Phantom Read – new rows appear on re‑read

Concurrency Control Techniques

  • Locking – shared/exclusive locks, 2‑phase locking
  • Timestamp Ordering – older transactions have priority
  • Optimistic Concurrency Control – validate before commit
  • Multi‑version Concurrency Control (MVCC) – used in PostgreSQL, MySQL

Indexing

Types of Indexes
  • B‑Tree – balanced tree (default)
  • Hash – exact matches only
  • Bitmap – low cardinality
  • Full‑text – text search
  • Clustered – data stored in index order
  • Non‑Clustered – separate structure
When to Use Indexes
  • Primary key (automatically indexed)
  • Foreign key columns
  • Frequently queried columns (WHERE, JOIN)
  • Columns used in ORDER BY, GROUP BY
  • Large tables
  • Avoid: small tables, frequently updated columns

Index Performance

  • Clustered Index – faster for range queries
  • Composite Index – on multiple columns (order matters)
  • Index Scan – reads whole index
  • Index Seek – finds specific rows
  • Covering Index – includes all needed columns
  • Selectivity – more selective = better

Query Optimization

  • EXPLAIN – shows query execution plan
  • Indexes – use appropriate indexes
  • Avoid SELECT * – only select needed columns
  • Avoid functions on indexed columns – WHERE YEAR(date) = 2024
  • Join order – smaller tables first
  • Use EXISTS over IN for subqueries
  • Use UNION ALL over UNION if duplicates not an issue
  • Limit result set – LIMIT, TOP, ROWNUM
  • Analyze and update statistics – helps query planner

NoSQL vs SQL

Feature SQL (RDBMS) NoSQL
Data Model Tables (relations) Document, Key‑Value, Graph, Column
Schema Fixed, predefined Flexible, dynamic
ACID Strong support BASE (eventual consistency)
Scalability Vertical Horizontal (sharding)
Query Language SQL API, MapReduce, custom
Examples MySQL, PostgreSQL, Oracle MongoDB, Cassandra, Redis, Neo4j
Use Cases Banking, ERP, e‑commerce Real‑time, IoT, social media

Types of Databases

Relational
  • MySQL
  • PostgreSQL
  • Oracle
  • SQL Server
Document (NoSQL)
  • MongoDB
  • Firebase
  • Cosmos DB
  • Couchbase
Key‑Value
  • Redis
  • Memcached
  • DynamoDB
  • Riak
Graph
  • Neo4j
  • Amazon Neptune
  • ArangoDB
  • JanusGraph
Column‑Family
  • Cassandra
  • HBase
  • BigTable
Time‑Series
  • InfluxDB
  • Prometheus
  • TimescaleDB

Database Design Best Practices

  • Normalize to 3NF as a starting point (denormalize for performance)
  • Choose appropriate data types – smallest sufficient
  • Use primary keys – always have a primary key
  • Use foreign keys – maintain referential integrity
  • Index strategically – based on query patterns
  • Avoid over‑indexing – slows down writes
  • Use constraints – NOT NULL, UNIQUE, CHECK
  • Use views – for commonly used queries
  • Use stored procedures – for complex logic (if needed)
  • Partition large tables – horizontal or vertical
  • Backup regularly – and test restore
  • Monitor performance – slow query logs
📌 Quick Reference
Normal Forms: 1NF (atomic) → 2NF (no partial dependency) → 3NF (no transitive) → BCNF (all determinants are keys)
ACID: Atomicity, Consistency, Isolation, Durability
SQL Types: DDL (CREATE, ALTER, DROP), DML (INSERT, UPDATE, DELETE), DCL (GRANT, REVOKE)
Joins: INNER, LEFT, RIGHT, FULL, CROSS, SELF
Index Types: B‑Tree, Hash, Bitmap, Full‑text, Clustered, Non‑Clustered
← Back to All Cheatsheets