IDX

LOG

Memory of Civilization

Databases, SQL, Storage & Information Management

Central Doctrine

Networks move information, but databases give information structure, persistence, and meaning. This realm explores the architecture of digital memory. We map how massive collections of raw data are modeled into relational tables, queried through SQL, protected by strict ACID transactions, and scaled across distributed systems to remember the actions of human civilization.

DATA → MODEL → SCHEMA → RELATIONSHIP → NORMALIZATION → SQL / QUERY → TRANSACTION → PERSISTENCE

The Internal Chambers

Ontology of Data Systems

CHAMBER 01

What is a Database?

👤

→

Persistent storage for structured information
Database Management System (DBMS)
Data retrieval, modification, and integrity
Concurrent access for multiple users

CHAMBER 02

Database vs. Files

Overcoming data duplication and inconsistency
Efficient searching and updating
Handling transactions and concurrent access securely

CHAMBER 03

The DBMS

QUERY ENGINE

TRANSACTION MGR

STORAGE ENGINE

Query processing and optimization
Storage and transaction management
Concurrency control, recovery, and security

CHAMBER 04

Data Models

REL

DOC

K-V

GRAPH

Relational vs. NoSQL approaches
Hierarchical, Network, Object-oriented
Document, Key-value, and Graph models

CHAMBER 05

Relational Databases

Relations (Tables), Tuples (Rows), Attributes (Columns)
Domain constraints and Schema definition
Structured records and mathematical foundation

CHAMBER 06

Tables

NAME

AGE

Rows (Records) and Columns (Fields)
Data types and constraints
The fundamental unit of relational storage

CHAMBER 08

Keys

🔑

Super key, Candidate key, Alternate key
Primary key: Uniquely identifies a record.
Foreign key: Links to a Primary key in another table.
Natural vs. Surrogate keys

CHAMBER 10

Referential Integrity

user_id

Parent (Referenced) and Child (Referencing) tables
Enforcing relationship consistency
Cascading actions (ON DELETE CASCADE)

CHAMBER 11

Relationships

1:1

1:M

M:M

One-to-one, One-to-many, Many-to-many
Cardinality and Optionality
Junction (Association) tables for M:M relationships

CHAMBER 12

Entity-Relationship

USER

POST

Entities (Rectangles), Attributes (Ovals)
Relationships (Diamonds)
Translating business rules into schema design

CHAMBER 14

Normalization

→

Reducing redundancy and preventing anomalies
1NF: Atomic values, no repeating groups.
2NF: No partial dependencies on composite keys.
3NF: No transitive dependencies (non-key relies on non-key).

CHAMBER 20

Structured Query Lang.

SELECT name

FROM users

WHERE age > 18;

Declarative language (Tell it WHAT, not HOW)
Query, Statement, Clause, Expression
The universal interface for relational databases

CHAMBER 21

SELECT (Read)

🔍

Column selection (SELECT) and Row filtering (WHERE)
Sorting results (ORDER BY)
Limiting rows and ensuring distinct values

CHAMBER 22

INSERT (Create)

Adding single or multiple records
Column specification and default values
Constraint validation upon entry

CHAMBER 23

UPDATE (Update)

Modifying existing records via SET
DANGER: Forgetting the WHERE clause updates the entire table.

CHAMBER 24

DELETE (Delete)

Removing specific records
Referential constraints may block deletion
Difference between DELETE (rows) and DROP (table structure)

CHAMBER 28

NULL

NULL means "Missing" or "Unknown"
NULL is not zero, and not an empty string.
Requires IS NULL (cannot use = NULL)
Three-valued logic (True, False, Unknown)

CHAMBER 29/30

Aggregation & Grouping

→

SUM

COUNT, SUM, AVG, MIN, MAX
GROUP BY: Rolling rows up into summary categories.
HAVING: Filtering the grouped results (WHERE happens before grouping).

CHAMBER 31-33

SQL Joins

INNER JOIN: Only matching rows.
LEFT JOIN: All from left, matches from right (NULL if no match).
Combining normalized tables back together for queries.

CHAMBER 36

Views

→

VIEW

Virtual tables generated by a stored query
Abstractions for security or simplification
Materialized views (storing the physical result)

CHAMBER 37

Indexes

B-tree and Hash indexes for search optimization
Turns O(n) Table Scans into O(log n) Index Scans
Trade-off: Faster reads, slower writes (must maintain index).

CHAMBER 38

Query Optimizer

SQL

→

OPTIMIZER

→

PLAN

Parser → Optimizer → Execution Plan
Decides the fastest access path (Scan vs. Index) and join strategy
Cost estimation based on table statistics

CHAMBER 39/40

Transactions & ACID

Atomicity

Consistency

Isolation

Durability

Atomicity: All or nothing (Commit or Rollback).
Isolation: Concurrent transactions don't interfere.
Durability: Once committed, data survives crashes.

CHAMBER 41

Concurrency Control

Managing shared data access to prevent race conditions
Shared (Read) vs. Exclusive (Write) locks
Row locks vs. Table locks
Deadlock detection and resolution

CHAMBER 44

Database Recovery

Write-Ahead Logging (WAL) ensures durability
Redo (reapply committed) and Undo (revert uncommitted)
Checkpoints to bound recovery time after a crash

CHAMBER 47

SQL Injection

SELECT * FROM users

WHERE name = 'OR 1=1;--'

Untrusted input manipulating the query logic
Prevention: Parameterized queries / Prepared statements
The most critical database security vulnerability

CHAMBER 49

Replication

PRI

Copying data to multiple nodes for High Availability
Primary (Writes) / Replica (Reads) architecture
Synchronous vs. Asynchronous replication lag

CHAMBER 52

Sharding

↓

Horizontal partitioning of data across multiple servers
Scaling Writes beyond a single machine's capacity
Requires a Shard Key to route queries correctly

CHAMBER 53

NoSQL

DOC

K-V

COL

GRAPH

Non-relational, flexible schema databases
Document: JSON-like nested data (MongoDB)
Key-Value: Fast, simple caching (Redis)
Graph: Optimized for connected relationships (Neo4j)

CHAMBER 61

Application Architecture

APP

↓ ORM ↓

DRIVER

↓ SQL ↓

DBMS

Connection pools to manage server overhead
ORM (Object-Relational Mapping): Translating OOP objects into SQL rows automatically.

CHAMBER 64/65

Data Warehouses

OLTP: Operational systems (Fast, small transactions).
OLAP: Data Warehouses (Massive historical aggregations).
ETL: Extract, Transform, Load pipelines.

CHAMBER 71/72

CAP Theorem

CAP

Consistency, Availability, Partition Tolerance
During a network partition (P), a distributed DB must choose between (C) and (A).
Eventual Consistency vs. Strong Consistency.

CHAMBER 81

SQL Master Pattern

SELECT columns

FROM table

JOIN other_table ON rel

WHERE conditions

GROUP BY columns

HAVING group_cond

ORDER BY columns