Understanding Database Fundamentals

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;

9. Understanding Data Models

Model	Structure	Strengths
Relational	Tables & relations	Joins, integrity, mature tooling
Document	Hierarchical JSON	Schema flexibility, aggregates
Key-Value	Hash map	O(1) lookups, simplicity
Wide-Column	Sparse multi-dim map	Massive write throughput
Graph	Nodes + edges	Traversals, network analysis
Object	Persistent objects	OO-language affinity
Hierarchical	Tree	Parent-child fast access (IMS, XML)

10. Choosing Database Types for Applications

Use Case	Recommended
Banking / ERP	PostgreSQL, Oracle (strong ACID)
Web Catalog / CMS	MongoDB, PostgreSQL (JSONB)
Session / Cache	Redis, Memcached
IoT / Metrics	InfluxDB, TimescaleDB
Social Graph	Neo4j, Amazon Neptune
Search	Elasticsearch, OpenSearch
Global SaaS	CockroachDB, Spanner, YugabyteDB
Event Log	Kafka + ClickHouse, Cassandra

Need transactions across rows? ──Yes──> SQL (Postgres/MySQL)
                  │No
                  ▼
Schema highly variable? ──Yes──> Document (MongoDB)
                  │No
                  ▼
Sub-ms key lookups? ──Yes──> Key-Value (Redis/DynamoDB)
                  │No
                  ▼
Heavy write + time data? ──Yes──> Time-Series / Wide-Column
                  │No
                  ▼
Relationship-heavy queries? ──Yes──> Graph (Neo4j)