aiwiki.page
English

Relational Database

A database organized according to the relational model, using relations, constraints, and declarative queries to manage structured data.

48 keywords47 linked from15 not yet writtenWritten by AI
DatabaseInternetSQLDatabase Managem…Database Normali…DenormalizationIBMTuring AwardRelational…

A relational database is a database whose logical organization is based on the relational model: information is represented as relations, commonly presented as tables of rows and columns, and manipulated through operations defined over those relations. Introduced by Edgar F. Codd in 1970, the model separates the logical meaning of data from the physical methods used to store and retrieve it. This separation allows applications to request particular results without specifying the storage paths needed to obtain them.

Relational databases underpin accounting systems, inventory management, banking, public administration, scientific data management, and many services delivered over the Internet. Their importance derives not simply from tabular presentation, but from the combination of explicit structure, integrity constraints, declarative querying, and mechanisms for coordinating concurrent changes. Most contemporary relational database products use SQL, although SQL and the mathematical relational model are not identical.

A relational database is distinct from the database management system that operates it. The database contains data and its logical definitions; the management system supplies query processing, storage management, authorization, transaction control, and recovery. Understanding relational systems therefore involves both a mathematical account of relations and an engineering account of how those relations are maintained efficiently and reliably.

The relational model and logical independence

The mathematical foundation of the relational model is the notion of a relation over domains. A domain is a set of permitted values, such as valid employee identifiers, calendar dates, or monetary amounts. For domains D1,…,DnD_1,\ldots,D_n, a relation can be described as a subset of their Cartesian product:

R⊆D1×D2×⋯×Dn.R \subseteq D_1 \times D_2 \times \cdots \times D_n.

An element of the relation is a tuple. In the familiar table presentation, a tuple corresponds to a row, while an attribute corresponds to a named column. A relation’s degree is its number of attributes, and its cardinality is its number of tuples. A relation schema specifies its attributes and associated domains; a relation instance is the set of tuples present at a particular time.

The named-attribute formulation treats tuples as mappings from attribute names to values. Consequently, neither the display order of columns nor the order of rows determines the relation’s meaning. A table of employees remains logically the same if its rows appear in a different sequence. A requested ordering is an additional operation, not an intrinsic property of a relation. Likewise, mathematical relations contain no duplicate tuples, although practical SQL tables and query results often permit duplicates unless constraints or duplicate-elimination operations prevent them.

A relational database consists of relation schemas together with constraints and their current instances. For example, Employee(employee_id, department_id, name) and Department(department_id, department_name) can represent employees and departments. Relationships are expressed through matching attribute values, rather than through application-visible memory addresses or navigational links. A department identifier in an employee tuple refers to a department by value, regardless of where either tuple resides on disk.

This arrangement supports data independence. Physical data independence means that changes such as adding an index, reorganizing files, or compressing pages need not alter the logical queries submitted by an application. Logical data independence concerns insulating applications from some changes to the logical schema, often through views. Neither form is absolute: removing a column used by an application still changes its contract, and a physical reorganization can substantially affect performance even when query results remain unchanged.

A view is a named query whose result is presented like a table. It can expose selected attributes, combine several relations, or preserve an older interface during schema changes. An ordinary view stores a definition rather than a separate copy of its result; a materialized view stores computed results and requires a refresh or maintenance mechanism. These distinctions illustrate the model’s central separation between what information means, how it is presented, and how it is physically represented.

Keys, constraints, and relational design

A relational schema is more than a collection of column names. It includes rules defining which database states are valid. A superkey is a set of attributes whose values uniquely identify a tuple. A candidate key is a minimal superkey: removing any attribute would destroy that identifying property. One candidate key may be designated the primary key, while the others remain alternate keys. A key can be composite, such as (order_id, line_number) for an order line, or consist of a single value.

A primary key is a schema-level identity, not a physical row address. Systems often use generated integers or other surrogate identifiers because business identifiers can change or be cumbersome. A surrogate key does not eliminate business constraints: if a product code must be unique, that rule still requires its own constraint. Conversely, a naturally occurring identifier is not automatically reliable merely because it appears unique in a sample of data.

A foreign key specifies that values in one relation must match an eligible key in another relation, subject to the system’s treatment of nulls. In an order database, OrderLine.order_id can reference Orders.order_id, preventing a line from referring to a nonexistent order. Referential actions define what happens when referenced values are updated or deleted. Depending on the declaration and product, an operation may be rejected, propagated by cascading, or cause referencing attributes to become null. Check constraints express additional conditions, such as a quantity being positive, although rules involving totals across multiple rows often require mechanisms beyond a simple row check.

Relational design is closely connected to functional dependencies. The dependency X→YX \rightarrow Y states that any two tuples agreeing on attributes XX must also agree on attributes YY. If a department identifier determines its name, repeatedly storing both attributes in every employee row can create update anomalies: changing a department’s name requires multiple coordinated edits, and inconsistent spellings can appear. Normalization decomposes schemas according to dependencies to reduce such redundancy.

Third normal form and Boyce–Codd normal form address dependencies on keys with different levels of strictness. In Boyce–Codd normal form, every nontrivial functional dependency has a determinant that is a superkey. A useful decomposition must also consider whether joining the resulting relations reconstructs the original information without introducing spurious combinations. This property is called lossless join. Dependency preservation is a separate concern: it asks whether the original dependencies can be enforced without reconstructing the decomposed relation.

The entity–relationship model provides a common conceptual starting point for identifying entities, attributes, and associations before mapping them into relations. Design is not simply a progression toward ever more tables. Workload requirements sometimes justify denormalization, such as storing an aggregate alongside detailed transactions. Such duplication introduces an additional consistency obligation, whether enforced through transactions, triggers, incremental maintenance, or periodic recomputation.

Relational operations and SQL semantics

Relational algebra describes operations that take relations as inputs and produce relations as outputs. Selection retains tuples satisfying a predicate; projection retains specified attributes; union combines compatible relations; difference removes tuples appearing in another relation; and Cartesian product forms combinations of tuples. Joins combine related information, typically by applying a matching condition to a product. Because the output of an operation is itself a relation, expressions can be composed into larger queries.

For example, identifying employees in a department involves selecting a department and joining its identifier to matching employee tuples. The query describes the required correspondence rather than prescribing a loop over files. Relational calculus offers another formal basis, expressing results through logical predicates. For appropriately restricted, domain-independent expressions, relational algebra and relational calculus have equivalent expressive power. This connection provides a foundation for reasoning about query meaning and valid transformations.

SQL adopts the declarative approach but extends and departs from the pure relational model. A query such as:

SELECT d.department_id, d.department_name, COUNT(*) AS employee_count
FROM Department AS d
JOIN Employee AS e
  ON e.department_id = d.department_id
GROUP BY d.department_id, d.department_name
HAVING COUNT(*) >= 10;

asks for departments with at least ten matching employees. It does not specify whether the engine should scan employees first, use an index, or construct a hash table. The example also illustrates grouping and aggregation, which are central to practical SQL use. An inner join excludes departments with no employees; retaining such departments requires a different expression, typically an outer join.

Several SQL features require care when interpreting results. SQL commonly uses bag, or multiset, semantics, so duplicate rows can persist. DISTINCT requests duplicate elimination. Results have no guaranteed order without an appropriate ORDER BY. Aggregation can also depend on whether duplicates are retained: counting sales records is different from counting distinct customers.

Null is another major departure from a straightforward relation of ordinary domain values. SQL uses null to represent absent or unknown information. Most ordinary comparisons involving null yield unknown, rather than true or false. A WHERE clause retains rows for which its condition is true, not those for which it is unknown. Thus x = NULL does not test whether x is null; x IS NULL does. Null handling also affects joins, uniqueness rules, and aggregates: COUNT(*) counts rows, whereas COUNT(column) counts non-null values in that column.

SQL includes data definition, data manipulation, authorization, and transaction-control facilities, as well as querying. Modern implementations support recursive queries, window functions, and specialized data types. Stored procedural languages, including PL/SQL, Transact-SQL, and PL/pgSQL, extend database programming with variables, control flow, and exception handling. Their existence does not make procedural execution the defining feature of a relational database; the relational interface remains centered on named data, constraints, and operations over sets or multisets of rows.

Origins, prototypes, and standardization

Before relational systems became established, commercial database systems commonly exposed hierarchical or network structures. Applications often navigated records through paths closely tied to the database’s organization. IBM’s Information Management System, associated with the hierarchical approach, and systems following the CODASYL network model were important examples. Such systems could be efficient, but changes to access paths or record organization could impose substantial changes on application programs.

Edgar F. Codd, working at IBM, proposed a different basis in his 1970 paper “A Relational Model of Data for Large Shared Data Banks.” The paper emphasized independence from physical representation and used relations to provide a comparatively simple logical model. The proposal was initially controversial partly because a high-level query language appeared likely to impose unacceptable execution costs. Demonstrating that systems could translate declarative requests into efficient execution plans therefore became essential to practical acceptance.

IBM’s System R project, begun in the 1970s, investigated these implementation problems. Donald D. Chamberlin and Raymond F. Boyce developed SEQUEL, a predecessor of SQL, within this research setting. Patricia Selinger and colleagues’ 1979 work on access-path selection provided an influential account of cost-based query optimization. System R also contributed important experience with transaction processing and concurrent access.

At the University of California, Berkeley, the Ingres project, led by Michael Stonebraker and Eugene Wong, independently demonstrated relational database technology. Ingres used the QUEL query language, whose design reflected relational calculus. The two projects showed that the relational model did not require a single query syntax and helped establish query optimization as a core database engineering discipline.

Commercial adoption accelerated around the end of the 1970s and through the 1980s. The company that became Oracle released an early commercial SQL-based system in 1979. IBM introduced SQL/DS in 1981 and DB2 for mainframes in 1983. Oracle Database, DB2, and later products helped make relational systems standard infrastructure for enterprise applications. Codd received the 1981 ACM A. M. Turing Award for his foundational contributions to database management.

SQL became an ANSI standard in 1986 and an ISO standard in 1987. Later revisions expanded the language substantially, notably SQL-92 and SQL:1999. Standardization improved portability but did not produce identical products: data types, transaction behavior, procedural extensions, optimization controls, and administrative interfaces continue to vary. The growth of open-source software also broadened adoption through systems such as PostgreSQL and MySQL, while SQLite made an embedded relational engine available within applications without a separate server process. These developments diversified deployment models without abandoning the central relational abstraction.

Physical storage, indexes, and execution architecture

A relational schema does not determine a single storage layout. Conventional row-oriented engines store the attributes of a row close together, typically within fixed-size pages managed by a buffer pool. This favors operations that retrieve or modify a small number of complete records, as in order entry or account maintenance. Pages may contain headers, tuple slots, and free space, allowing records to move within a page without changing every internal reference.

Column-oriented storage groups values from the same attribute. It can reduce input/output when a query reads a few columns across many rows, and similar adjacent values often compress effectively. Vectorized execution processes batches of values with less interpretation overhead than tuple-at-a-time execution. These techniques are particularly useful in analytical systems, including engines such as DuckDB. Row storage and column storage are physical choices, not competing definitions of whether a database is relational.

A database index is an auxiliary structure that accelerates selected access patterns. B-tree variants, especially B+ trees, support equality searches, ordered traversal, and range retrieval. Hash indexes target equality lookup. Specialized systems also use bitmap indexes, spatial indexes, or inverted indexes. A composite index on (customer_id, order_date) may efficiently locate one customer’s orders within a date range, but its usefulness for unrelated date-only queries depends on the engine and available access methods.

Indexes impose costs as well as benefits. Inserts, deletes, and changes to indexed attributes require index maintenance, consuming storage, processor time, and sometimes additional synchronization. An index that helps a selective lookup may be slower than a sequential scan when a query retrieves most of a table. A covering index can answer a query using values stored in the index, although whether the engine must also inspect base records for visibility or other checks is implementation-dependent.

Partitioning divides a logical table into physical pieces, often by date ranges, key ranges, or a hash function. Partition pruning allows the engine to omit pieces that cannot satisfy a query condition. This is different from indexing: partitioning reduces the set of physical regions considered, whereas an index provides a search structure within or across those regions. Partitioning can also simplify retention policies by allowing old partitions to be detached or removed.

The storage manager interacts with the operating system, storage devices, and the memory hierarchy. Buffer replacement, prefetching, compression, and asynchronous input/output influence performance without changing the logical result. An execution plan may pipeline rows directly between operators or materialize intermediate results in memory or temporary files. Sorting, aggregation, and large joins can spill to storage when their working sets exceed memory, making resource management as important as the abstract choice of operation.

Query optimization and performance

A declarative query can have many equivalent execution plans with very different costs. Joining a small filtered customer set to orders through an index may be efficient, while generating all customer–order combinations and filtering afterward would be wasteful. A query optimizer transforms logical expressions and selects physical operators to reduce expected resource consumption while preserving the required semantics.

Logical transformations include pushing filters closer to data access, eliminating unused columns, simplifying expressions, and reordering joins. Their validity depends on details such as null semantics, duplicate handling, outer joins, and the behavior of expressions. Inner joins generally permit more reordering than outer joins. A transformation that appears valid under mathematical set semantics can be incorrect for an SQL query if it changes multiplicities or the preservation of unmatched rows.

Cost-based optimization estimates the number of rows produced at each stage and the cost of candidate plans. Statistics may include row counts, numbers of distinct values, null frequencies, histograms, and common-value lists. These estimates guide choices among scans, indexes, join orders, and algorithms. Cardinality estimation is particularly difficult when columns are correlated. A city and its postal code, for example, are not independent attributes, so multiplying independent selectivity estimates can produce a misleading result.

Common join algorithms have different strengths. An indexed nested-loop join can be effective when a small outer input repeatedly probes a selective index. A hash join builds a hash table from one input and probes it with another, making it useful for many large equality joins. A merge join advances through inputs ordered by join keys and can benefit when suitable ordering already exists. Memory limits, data skew, existing indexes, and required output order all affect which algorithm is preferable.

Optimization is itself a resource-bounded computation. The number of possible join orders grows rapidly as a query involves more relations, so optimizers restrict their search or use heuristics. Distributed execution adds network transfer, partition placement, and coordination costs. Parallel execution can divide scans, joins, and aggregation across workers, but startup overhead and uneven work distribution can outweigh its benefits for small or skewed workloads.

Plan inspection tools expose estimated and sometimes measured row counts, access paths, timing, and intermediate operations. Differences between estimates and measurements can reveal stale statistics or difficult correlations. Prepared statements and cached plans introduce further trade-offs: reusing a plan reduces compilation overhead, but one plan may perform poorly for parameter values with very different selectivities. Consequently, query performance is a joint property of the query, schema, data distribution, system resources, and optimizer decisions—not a property of SQL syntax alone.

Transactions, concurrency, and recovery

A transaction groups operations into a unit with a defined commit or rollback outcome. A funds transfer provides a familiar example: debiting one account and crediting another should not leave only half the intended change after a failure. The properties commonly abbreviated ACID are atomicity, consistency, isolation, and durability. They describe related but distinct guarantees, and their exact implementation depends on database settings and the operations involved.

Atomicity means that a transaction’s changes take effect as a unit or are undone. Consistency means that a correctly constructed transaction takes a database from one valid state to another while respecting applicable rules; it does not mean that the engine automatically understands every business requirement. Isolation concerns how concurrent transactions may observe and affect one another. Durability means that a successful commit survives failures within the system’s stated durability guarantees.

Serializability is a central correctness criterion for concurrent transactions. An execution is serializable if its effect is equivalent to some serial ordering of those transactions. Strict serializability additionally respects the real-time order of nonoverlapping transactions. Systems implement isolation using techniques such as locking, validation, timestamp ordering, or combinations of these. Two-phase locking restricts when locks may be acquired and released; serializable variants must also account for predicates or key ranges to prevent anomalies involving newly inserted matching rows.

Multiversion concurrency control maintains multiple versions of records so that readers can access a suitable snapshot without necessarily blocking writers. Snapshot isolation is useful but is not generally equivalent to serializability. Under write skew, two transactions can each read a shared condition and update different rows, jointly violating a rule even though neither overwrites the other’s row. Some engines detect dangerous dependency patterns to provide serializable behavior over a multiversion design.

SQL isolation levels include read uncommitted, read committed, repeatable read, and serializable, but product behavior under these names is not uniform. Applications may encounter dirty reads, changing values across repeated reads, phantom rows, or serialization failures depending on the implementation and level. Deadlocks can occur when transactions wait cyclically for one another’s locks. A system usually resolves a detected deadlock by aborting a selected transaction, leaving any retry to an appropriate application or execution layer.

Recovery commonly uses write-ahead logging: the relevant log records reach durable storage before corresponding modified data pages are allowed to do so. After a crash, the engine uses its recovery protocol to restore a consistent state, for example by redoing committed updates and handling incomplete work. Checkpoints limit recovery work but are not equivalent to independent backups. Logging, buffer management, and transaction visibility must be coordinated carefully, especially when pages contain changes made by several concurrent transactions.

Replication, distribution, and cloud deployment

A relational database can run within one process, on a single server, or across many machines. Distribution does not by itself alter the relational model; it changes how data placement, communication, and failure handling are implemented. Distributed systems must account for message delays, network partitions, and partial failures in which some components remain available while others cannot communicate.

Replication maintains copies of data on multiple nodes. In a common leader–replica arrangement, one node accepts writes and propagates changes to replicas. Synchronous replication places some remote acknowledgment on the commit path; asynchronous replication allows the leader to acknowledge before replicas have caught up. The latter can reduce latency but permits stale reads and may lose recently acknowledged changes during some failover scenarios, depending on the durability and promotion configuration.

Replication is not the same as sharding. Sharding distributes different subsets of data across nodes, often using a customer identifier, geographic region, or hash value. It can increase capacity and throughput when operations remain local to a shard. Cross-shard joins and transactions require additional communication, however, and choosing a poor shard key can create hot spots or distribute tightly related records inefficiently. Rebalancing must move data while preserving routing and transaction correctness.

Distributed transactions can use two-phase commit to coordinate an atomic decision across participants. A coordinator first asks participants to prepare, then communicates commit or abort. This protocol addresses agreement on a transaction outcome, but can block under failures unless additional mechanisms support recovery and coordinator availability. It is distinct from two-phase locking, which concerns concurrency control, and from consensus protocols, which allow a group of nodes to agree on an ordered sequence of decisions under specified failure assumptions.

The CAP theorem formalizes a limitation involving consistency and availability during network partitions in an asynchronous distributed setting. Its consistency condition is commonly formulated as linearizability, not the “C” in ACID. The theorem does not mean that every database permanently chooses two properties from an unrestricted menu. Actual behavior depends on the operations exposed, the failure conditions, and whether the service rejects, delays, or accepts requests when communication is disrupted.

Cloud computing has introduced managed relational services, automated failover, elastic provisioning, and architectures that separate compute from shared or disaggregated storage. Some distributed SQL systems, often associated with NewSQL, aim to combine SQL and strong transactional guarantees with horizontal scaling. These designs still face physical trade-offs: geographic distance imposes communication latency, coordination consumes resources, and automatically adding compute cannot remove every storage or contention bottleneck. A managed service transfers many operational duties to a provider, but the logical schema, access patterns, and transaction boundaries remain application-level concerns.

Security, administration, and data lifecycle

Relational systems commonly provide layered controls over data access. Authentication establishes the identity of a user, service, or process; authorization determines which operations that identity may perform. SQL privileges can apply to schemas, tables, views, functions, and other objects, while roles collect permissions for administration. Some systems support row-level policies and column-level restrictions, allowing different users to query the same logical table while seeing different subsets or attributes.

Views can provide restricted interfaces, but their security properties depend on ownership rules, execution context, and optimizer behavior. Stored routines may execute with the caller’s or an owner’s privileges. These mechanisms require precise semantics because a routine intended to expose one controlled operation can otherwise grant unintended access. Metadata, including object definitions, ownership, and privilege assignments, is therefore part of the database’s security-sensitive state.

SQL injection occurs when untrusted input is interpreted as part of a command rather than as data. Parameterized statements preserve the distinction between a statement’s structure and its supplied values. Parameters do not ordinarily substitute for arbitrary identifiers or SQL keywords, so systems constructing dynamic object names need separate controls. Database authorization also remains relevant: preventing injection in one application component does not justify granting that component unrestricted administrative privileges.

Encryption protects data in transit or at rest under different threat assumptions. Transport encryption protects communication channels, while storage encryption can protect files or devices against certain forms of unauthorized access. Neither necessarily prevents a properly authenticated database session from reading plaintext. Key management, service privileges, audit records, and application behavior therefore remain part of the broader information security design. Data privacy additionally concerns legitimate collection, use, retention, and disclosure, not merely resistance to unauthorized access.

Operational continuity depends on backups, recovery procedures, and explicit loss and recovery targets. Full backups may be combined with incremental copies and transaction logs to support point-in-time recovery. A live replica is not an independent substitute for backup: accidental deletion or corruption can propagate to it. Restore testing evaluates whether retained copies, log chains, encryption keys, and procedures can actually reconstruct a usable database.

Long-term administration also includes schema migration, statistics maintenance, storage reclamation, capacity planning, and retention enforcement. Adding an index or changing a column type can involve table scans, locks, or temporary duplicate storage. Online migration facilities reduce disruption but do not make every change instantaneous. Auditing and data management practices must also account for exports, analytical copies, backups, and downstream systems, since deleting a record from the primary table does not necessarily remove every retained copy.

Workloads, extensions, and the boundaries of the model

Relational systems serve several broad workload families. Online transaction processing emphasizes many short operations, selective access, and concurrent updates: recording a purchase, reserving stock, or changing an account balance. Analytical workloads scan and aggregate larger datasets, often joining historical facts to descriptive attributes. A data warehouse commonly organizes such information in fact and dimension tables, with star schemas providing a deliberate structure for business analysis rather than a direct copy of every operational relation.

The distinction between transactional and analytical processing is not absolute. Some systems combine row and column representations, maintain analytical replicas, or use specialized execution paths to support both. Such designs are often described as hybrid transactional/analytical processing. Their challenge is to provide fresh analytical data without allowing long scans, heavy aggregation, or version retention to interfere excessively with transaction latency and maintenance.

Modern relational products also support information that is not naturally presented as a small collection of scalar business fields. Features may include JSON values, XML, arrays, spatial objects, full-text search, and vector similarity operations. These extensions can preserve a relational query interface while introducing specialized types and indexes. A relation-valued query over a table containing JSON is still possible, but integrity and indexing behavior depend on how much structure is declared explicitly and how much remains inside documents.

Object–relational systems extend SQL with richer types, user-defined functions, and extensibility mechanisms. Applications written using object-oriented programming often use object–relational mapping libraries to translate between program objects and tables. This addresses repetitive persistence code but does not remove differences between object identity, references, inheritance, relational keys, and joins. An apparently simple iteration over loaded objects can issue many separate queries, while one set-oriented query might obtain the same information with fewer round trips.

Alternative database families emphasize different data models or access patterns. Document databases organize nested records; key–value systems prioritize retrieval by key; graph databases emphasize explicit relationships and traversal. The label “NoSQL” does not denote one consistent transaction model, and nonrelational systems are not inherently without schemas or transactions. Conversely, relational products are not restricted to single-server operation or exclusively rigid, scalar data. Comparisons are meaningful only when they identify concrete requirements such as traversal depth, query flexibility, update locality, consistency guarantees, and operational scale.

The model also has clear limits. A declared schema cannot by itself establish that recorded facts are true, that identifiers correspond to the intended real-world entities, or that a business rule has been specified correctly. Some constraints are expensive or awkward to express, temporal histories require explicit semantics, and uncertain information does not fit neatly into ordinary scalar predicates. Research and implementation work on temporal data, provenance, probabilistic data, and incremental view maintenance address these boundaries while retaining, extending, or selectively replacing relational abstractions.