1. Understanding Database Management Systems (DBMS)
Component
Role
Examples
Storage Engine
Physical data storage on disk/memory
InnoDB, WiredTiger, RocksDB
Query Processor
Parses, optimizes, executes queries
SQL parser, planner, executor
Transaction Manager
ACID guarantees, concurrency control
MVCC, 2PL, WAL
Buffer Manager
Caches pages in RAM
Buffer pool, page cache
Catalog/Metadata
Schema definitions, statistics
information_schema, pg_catalog
Access Control
Authentication, authorization
Roles, GRANT/REVOKE
Recovery Manager
Crash recovery from logs
WAL, redo/undo logs
DBMS Category
Examples
Best For
RDBMS
PostgreSQL 16+, MySQL 8+, Oracle, SQL Server
OLTP, structured data, transactions
Document
MongoDB 7+, Couchbase, Firestore
Flexible schema, JSON data
Key-Value
Redis 7+, DynamoDB, etcd
Caching, sessions, leaderboards
Column-Family
Cassandra 5+, ScyllaDB, HBase
Write-heavy, time-series, large scale
Graph
Neo4j 5+, Amazon Neptune, ArangoDB
Relationships, recommendations, fraud
Time-Series
InfluxDB 3+, TimescaleDB, QuestDB
Metrics, IoT, monitoring
Search
Elasticsearch 8+, OpenSearch, Meilisearch
Full-text search, log analytics
NewSQL
CockroachDB 23+, YugabyteDB, TiDB
Distributed SQL, global scale
2. Understanding Relational Databases (SQL)
Concept
Definition
Relation
Table with named columns (attributes) and rows (tuples)
Tuple
A single row in a table
Attribute
A column with defined data type
Domain
Set of permissible values for an attribute
Primary Key
Unique identifier for each row
Foreign Key
Reference to primary key of another table
Schema
Logical structure: tables, columns, relationships
Relational Algebra
Formal basis: SELECT, PROJECT, JOIN, UNION
Example: Relational Model
CREATE TABLE customers ( id BIGSERIAL PRIMARY KEY, email VARCHAR(255) NOT NULL UNIQUE, name VARCHAR(100) NOT NULL, created_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE orders ( id BIGSERIAL PRIMARY KEY, customer_id BIGINT NOT NULL REFERENCES customers(id), total NUMERIC(10,2) NOT NULL CHECK (total >= 0), placed_at TIMESTAMPTZ NOT NULL DEFAULT NOW());
3. Understanding NoSQL Databases
Type
Data Model
Query
Example
Document
JSON/BSON documents
Field queries, aggregation
MongoDB
Key-Value
Opaque value by key
GET/SET/DEL
Redis, DynamoDB
Wide-Column
Sparse rows with column families
CQL by partition key
Cassandra
Graph
Nodes & edges with properties
Cypher, Gremlin
Neo4j
Trait
Description
Schema-less
Flexible structure per record
Horizontal Scaling
Built-in sharding/partitioning
Eventual Consistency
Trades consistency for availability (typically)
Denormalization
Data duplication for read performance
No Joins
Joins done in application or via embedding
4. Understanding ACID Properties
Property
Guarantee
Mechanism
Atomicity
All or nothing
Undo logs, rollback
Consistency
Valid state to valid state
Constraints, triggers
Isolation
Concurrent txs don't interfere
Locks, MVCC
Durability
Committed data survives failure
WAL, fsync
Example: ACID Transaction
BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2;COMMIT; -- both updates persist atomically, or neither does
5. Understanding BASE Properties
Property
Meaning
Basically Available
System always responds (may be stale)
Soft State
State may change without input (replication)
Eventual Consistency
All replicas converge given no new writes
Note: BASE favors availability and partition tolerance; ACID favors consistency. NoSQL stores typically lean BASE; RDBMS lean ACID.
6. Understanding CAP Theorem
Property
Description
Consistency (C)
Every read receives the latest write
Availability (A)
Every request gets a non-error response
Partition Tolerance (P)
System operates despite network partitions
Choice
Trade-off
Examples
CP
Reject requests during partition
MongoDB, HBase, etcd
AP
Return possibly stale data
Cassandra, DynamoDB, Couchbase
CA
Only possible without partitions (single-node)
Traditional RDBMS (non-distributed)
Warning: In distributed systems, partitions WILL happen — you must choose C or A during a partition. PACELC extends CAP for partition-free behavior (latency vs consistency).
7. Comparing SQL vs NoSQL
Aspect
SQL
NoSQL
Schema
Fixed, predefined
Flexible, dynamic
Scaling
Vertical (mostly)
Horizontal
Joins
Native, optimized
Limited or app-side
Transactions
Multi-row ACID
Often single-row only
Consistency
Strong by default
Tunable, often eventual
Query Language
Standardized SQL
Vendor-specific APIs
Best Fit
OLTP, reporting, complex relations
High-volume, semi-structured, low latency
8. Understanding Database Schemas
Level
Description
Physical Schema
How data is stored (files, blocks, indexes)
Logical Schema
Tables, columns, relationships, constraints
External Schema (View)
User/application-specific projections
Conceptual Schema
Entity-relationship model, business rules
Example: Creating a Schema
CREATE SCHEMA sales AUTHORIZATION app_user;CREATE TABLE sales.invoices ( id BIGSERIAL PRIMARY KEY, amount NUMERIC(12,2) NOT NULL);GRANT USAGE ON SCHEMA sales TO reporting_role;