Relational database concepts

The Cartesian product pairs every tuple of one relation with every tuple of the other, producing a relation with the combined attributes and a cardinality equal to the product of the inputs. A theta-join is that product filtered by a predicate; an equi-join is a theta-join whose predicate uses only equality comparisons.

Selection σ_p(R) keeps exactly the tuples of relation R that satisfy predicate p. The schema is unchanged — it filters rows, never columns. Conjunctions cascade: σ_{p∧q}(R) = σ_p(σ_q(R)) = σ_q(σ_p(R)).

The two relations must be union-compatible: same number of attributes, in corresponding positions, with compatible types. Without that, the result would have no well-defined heading, so the expression is simply invalid — not merely wrong at runtime.

A superkey is any set of attributes whose values are unique across all rows. A candidate key is a minimal superkey: drop any attribute and uniqueness breaks. The primary key is the one candidate key chosen as the official row identifier; the rest are alternate keys.

A relation is a heading plus a body. The heading is a set of named attributes, each with a domain (type); the body is a set of tuples, each mapping every attribute name to a value. Informally: table = relation, row = tuple, column = attribute.

An entity is a thing with its own identity, its own describing facts and its own lifecycle. An attribute is one fact about a single entity instance. If addresses repeat, need describing, or are referenced on their own, model an entity.

It becomes a third table holding one row per pair — student key and course key — with a foreign key to each parent and a primary key over the pair. Neither parent can hold the link because each side has many partners, and a column stores only one value.

It becomes a child table holding the owner's key plus one value per row, with a foreign key to the owner and a key over (owner key, value). Repeated columns cap the count and scatter one fact; a delimited string loses typing, constraints and lookups.

Give the table a parent_id column that is a foreign key back to its own primary key. Roots have NULL parent_id. One row per node stores one edge. To read a whole subtree you walk the parent_id chain repeatedly, which in standard SQL means a recursive query.

Use a third table holding one foreign key to each side, keyed on the pair of them. A single foreign key column stores one value per row, so it cannot record many links from the same row.

A CHECK constraint is a boolean condition attached to a table. The engine tests it for every row a statement inserts or updates; if the condition comes out false, the statement fails with a constraint-violation error and the row is not stored.

Because the application is not the only writer, and its checks are not atomic with the write. Scripts, migrations, other services and future bugs all reach the same tables. Database constraints are the last line of defense that no code path can bypass; app validation exists for fast, friendly feedback.

It guarantees every non-NULL value in the referencing (child) column exists in the referenced (parent) key, so there are no orphan rows. A NULL referencing value points at nothing, so the check is skipped and the row is accepted.

Omitting the column makes the engine apply the DEFAULT, so the insert succeeds. Explicitly writing NULL is a real value being supplied — the DEFAULT is not consulted, and the NOT NULL constraint rejects the row.

A primary key guarantees every row has a value in those columns (no NULLs) and no two rows share the same combination. The engine enforces it by marking the columns NOT NULL and maintaining a unique index it probes on every insert and key update.

Atomicity means a transaction is all-or-nothing: either every change it made becomes visible together, or none does. If it fails partway, the engine reverses the work already done, leaving the database as if the transaction never ran.

C means a transaction moves the database from one valid state to another: every declared integrity rule (primary key, unique, not null, check, foreign key) holds at commit. The engine enforces the declared rules; the developer decides which rules to declare.

It promises the committed changes survive a crash or power loss of that node: the data reached stable storage, not just memory. It does not promise the data survives losing that machine or its disk, nor that any replica already has it.

Isolation means concurrent transactions must not see each other's unfinished work: the outcome should match some order where they ran one at a time. Enforcing that fully costs locking, blocking and aborts, so engines offer weaker levels that trade specific anomalies for throughput.

A dirty read is reading a row version written by a transaction that has not committed. If that writer rolls back, the value you read never existed in any committed state, so any decision, calculation or write you based on it is derived from data the database itself disowned.

A full scan reads every page of the table in order and discards rows that do not match. An index scan descends the index to the matching keys, then follows each key's pointer to fetch that row's table page. Fewer rows touched, but scattered reads.

Deletes and updates leave dead entries and half-empty pages behind. The same live keys end up spread over more pages, so scans read more pages and each cached page carries less useful data. That is index bloat.

All entries live in leaf pages, sorted by key, and the leaves are chained to their neighbours in key order. So the engine descends once to the first matching key and then walks the chain until the range ends, reading entries already in order.

A B+tree is a balanced tree of page-sized nodes. Internal nodes store only separator keys and child pointers used to route a search downward. Leaf nodes store every indexed key in sorted order with a pointer to the row. A lookup descends root to leaf.

In clustered storage the table IS the index: rows live in the leaves of a B+tree ordered by the clustering key. In a heap, rows sit in unordered pages and are addressed by a physical row id, with every index a separate structure pointing at those ids.

A fully pipelined plan passes each row up to the client as it is produced, so the first row arrives early. If the plan contains a blocking operator — a sort, a hash build, a grouping step — nothing can be emitted until that operator has read all its input, so results appear only at the end.

For each row of the outer input, scan the entire inner input and emit the pairs that satisfy the join predicate. Comparisons are O(n*m), and the naive form re-reads the inner input once per outer row, so I/O is the real problem.

Each sort or grouping operator gets a limited memory budget. If the rows it must hold exceed that budget, the engine writes partial results into temporary files and reads them back later. Spilling replaces memory work with disk I/O, so latency jumps sharply.

A rule-based optimizer applies a fixed priority list of heuristics regardless of the data. A cost-based optimizer enumerates alternative plans, estimates the cost of each from statistics about the data, and picks the cheapest. Cost-based wins because the right plan depends on data distribution, which rules cannot see.

They are summaries of the data — row counts, distinct values per column, null fractions, common values, value distributions — that the query planner reads to guess how many rows each step will produce. Missing or stale statistics make those guesses wrong, so the planner picks a bad access path or join method and the query runs far slower.

A column the engine derives from other columns of the same row via a fixed expression; you never write to it. STORED materializes the value on disk at write time (uses space, free to read, indexable). VIRTUAL stores nothing and recomputes it on every read.

A materialized view stores the actual result rows of its query on disk, like a table. An ordinary view stores only the query text and recomputes it on every read. You trade freshness and storage for cheap reads of expensive queries.

A sequence is a standalone database object that hands out unique increasing numbers on request, independent of any table. An IDENTITY or serial column is a column wired to such a generator so inserts get a value automatically. Same machinery, different packaging.

A function returns a value and is called inside an expression or query. A procedure is invoked by a CALL statement, returns data through output parameters or result sets, and in most engines may commit or roll back. Functions compute values; procedures perform units of work.

A temporary table holds intermediate rows for one session (or one transaction). Its data is private to the creating session, it lives in a special temporary namespace, it is dropped automatically at session or transaction end, and it is typically unlogged and never crash-recovered.

A shared region of RAM holding fixed-size disk pages. All reads and writes go through it, so hot pages never touch disk. The engine caches itself because it knows access patterns and must control when a modified page may be written.

A checkpoint flushes modified in-memory pages to disk and records a position in the transaction log. Without it, crash recovery would have to replay the log from the beginning, and the log could never be truncated, so it would grow forever.

TCP connect, a protocol handshake naming the database and user, an optional TLS upgrade, then authentication. The server allocates a session (a backend process or thread with its own memory), loads role metadata, and signals it is ready for queries.

Opening a connection costs a network handshake, authentication, and server-side setup - milliseconds plus memory - and the server can only hold a limited number. A pool keeps a bounded set of authenticated connections open, lends one to each unit of work, takes it back afterwards, and makes callers wait when all are busy.

The transaction log decides. A transaction whose commit record reached durable storage before the crash is kept, and reapplied if needed. Every transaction without a durable commit record is rolled back completely. So an acknowledged commit survives; in-flight work disappears atomically, never half-applied.

It proves the job ran and wrote bytes somewhere. It does not prove the backup is complete, readable, or restorable. The only proof is restoring it onto a separate instance, starting the database, and querying the data.

RPO is how much recent data you can afford to lose, measured in time before the failure. RTO is how long the system may stay unavailable before it is serving again. RPO is about data loss; RTO is about downtime.

A full backup copies everything. An incremental copies only what changed since the previous backup of any type. A differential copies everything changed since the last full. Incrementals are smallest to take but slowest to restore, because the whole chain must be replayed.

Split-brain is two nodes both believing they are primary and accepting writes, usually after a network partition hid a still-running primary. Their histories diverge, and reconciling them means discarding writes the application already saw committed.

A switchover is a controlled role swap: writes are stopped, the replica catches up fully, then roles change with no data loss and the old primary can rejoin as a replica. A failover is reactive after a crash, so unreplicated commits can be lost.

tenant_id tags each row with its owning customer. Every read and write must filter on it, it should lead composite primary keys and indexes so a scan touches one tenant, unique constraints must include it, and foreign keys must stay inside one tenant.

On a non-partitioned table you get one physical index over all rows. On a partitioned table, most engines build one physical index per partition, each covering only that partition's rows; the index you declared on the parent is just metadata tying them together.

Pruning is the database eliminating partitions that cannot contain matching rows, using each partition's declared bounds. It needs a predicate on the partition key. An index narrows the search inside a table; pruning removes whole tables from the plan before any scan happens.

Replicas add read capacity: full copies of the data that can serve queries. They do not add write capacity — every replica must apply the same write stream as the primary — and with asynchronous replication their data is slightly behind, so reads can be stale.

Partitioning splits one logical table into physical pieces inside the same database, chosen by a partition key. RANGE assigns contiguous intervals (usually dates), LIST assigns explicit value sets (region, status), HASH spreads rows evenly by a hash of the key.

Record who, what, which object, when, from where, and the outcome: logins and failed logins, DDL, privilege changes, and DML or reads on sensitive tables. App logs miss DBAs, tools and jobs that connect directly, and can be bypassed.

Authentication proves who is connecting. It happens once, during the connection handshake, using a password, an OS identity, a Kerberos ticket, or a client certificate. Authorization decides what that identity may do, and the server re-checks it on every statement afterwards.

Dynamic masking keeps the true value stored and applies a masking function at query time based on who is asking, so privileged roles still see the original. Write-time redaction or tokenisation changes what is stored, so the original cannot leak from that table at all.

Volume or filesystem encryption protects whole disks and is invisible to the database. Engine-level Transparent Data Encryption encrypts the database's own files and backups. Application or column encryption encrypts values before storage, so even the database never sees plaintext — but indexing and range queries break.

TLS protects the wire: credentials, SQL text, parameters and result rows cannot be read or silently altered by anyone on the network path, and the client can verify it reached the real server. It does nothing for data on disk, backups, in-database authorization, or a compromised host.