Database

The relational model comes from E. F. Codd's 1970 paper: data as relations (tables) of tuples, identified by keys and manipulated with a closed algebra, independent of physical storage. SQL, standardised as ISO/IEC 9075 since 1987, is the practical realisation. Inside a relational DBMS a query passes through a parser, a cost-based optimiser that uses table statistics to choose access paths and join order, and an execution engine. The storage engine keeps fixed-size pages in a buffer pool and indexes them with B+-trees; log-structured merge trees (RocksDB, Cassandra) trade read cost for much cheaper writes.

Transactions give the ACID guarantees. Atomicity and durability are implemented with write-ahead logging: log records describing a change must reach stable storage before the modified data page does, and a commit is durable once its commit record is flushed; the ARIES algorithm (Mohan et al., 1992) defines the redo and undo passes used for crash recovery. Isolation is weaker than many assume. SQL-92 defines READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE by which anomalies they forbid (dirty reads, non-repeatable reads, phantoms), and Berenson et al. showed in 1995 that this phenomenon-based definition misses cases such as write skew, which snapshot isolation permits. Engines implement isolation with two-phase locking or multiversion concurrency control; defaults differ (READ COMMITTED in PostgreSQL and SQL Server, REPEATABLE READ in MySQL InnoDB), and Oracle's "serializable" is really snapshot isolation.

Scaling out introduces replication (synchronous, at the cost of latency, or asynchronous, at the risk of losing recent commits on failover and of stale reads from replicas) and sharding. The CAP theorem (Brewer's conjecture of 2000, proved by Gilbert and Lynch in 2002) states that during a network partition a system must choose between consistency and availability. Non-relational families, namely key-value, document, wide-column and graph stores, relax schema or transactional guarantees for scale or flexibility, while systems such as Google Spanner provide distributed serializable transactions using tightly bounded clock uncertainty.

Security failures cluster around a few points. SQL injection (CWE-89, part of A05:2025 Injection in the OWASP Top 10) is prevented structurally by parameterised queries, not by escaping. Applications should connect with least-privilege accounts, never as the database owner. Transparent data encryption protects stolen disks and backups, not data read through a compromised application. Databases exposed directly to the internet without authentication were mass-wiped and held for ransom in the MongoDB and Elasticsearch campaigns of 2017, and point-in-time recovery from archived logs is only as good as the last tested restore.