A database is an organized collection of data maintained for queries and updates. A database management system manages that collection, enforcing access rules and database integrity constraints. A relational database represents it through database relations, rather than exposing only an arbitrary collection of bytes in files.
For example, a laboratory can store biological sample identifiers, measurement values and scientific instrument identifiers as database tuples. A database query can select measurements above a threshold and join them to scientific instrument records. A foreign key can require every recorded scientific instrument identifier to name an existing scientific instrument. This makes the connection a maintained rule rather than a convention that every application must remember.
The logical relational schema and actual storage are distinct. A database query plan may use an index or scan database pages while returning the same specified result. Database transactions coordinate related updates, and database integrity constraints restrict the permitted states. PostgreSQL's documentation gives a concrete implementation in which named tables contain rows with named, typed columns and databases group tables.[ref]
A database view is a named database query that can be used much like a database relation. An ordinary view stores the query definition rather than a separate persistent copy of its result. Referring to the view evaluates that definition as part of the surrounding database query; changes in the underlying data can therefore change the view's result.[ref]
For example, an orders table might contain identifiers, totals and payment flags. The definition CREATE VIEW unpaid_orders AS SELECT id, total FROM orders WHERE paid = false; exposes the unpaid orders without requiring every application query to repeat the condition. If order 17 is marked paid, the next query of the ordinary view no longer includes that order, subject to the query's database transaction snapshot. Creating the view did not create another independent copy of order 17.
A view can hide storage and schema details behind a stable interface, supporting data independence. A materialized view instead stores results that require a refresh policy. Some ordinary views permit updates, but a view containing aggregation or certain joins may not identify a unique underlying row to change. Update behavior and access control are separate specifications; the word view alone does not guarantee that an arbitrary definition is writable or supplies a security boundary.[ref]
A materialized view stores the result of a defining database query, allowing later queries to read the stored result. An ordinary database view retains the definition and evaluates it against the relevant underlying data when queried. A materialized view trades storage and maintenance work for potentially cheaper repeated reads; its freshness depends on when and how the result is maintained.[ref]
Suppose an orders table contains totals 10 and 20 for one customer. A materialized view defined by SELECT customer_id, SUM(total) AS total FROM orders GROUP BY customer_id stores 30 for that customer. Inserting a third order worth 7 changes the underlying sum to 37. In PostgreSQL, the previously stored 30 remains until a refresh replaces the result, for example with REFRESH MATERIALIZED VIEW customer_totals;. The stored answer can therefore be internally consistent yet out of date.
A dashboard that tolerates nightly updates can benefit from this arrangement, especially when the defining database query combines many rows. An application requiring the latest committed totals needs a suitable maintenance policy and database transaction semantics. The term materialized view alone does not promise automatic updates or an always faster query.[ref]
Data independence separates the logical meaning of stored data from details that an application need not know. Physical independence permits changing indexes, page layout or access paths without changing a query's meaning. Logical independence permits some changes in the database's logical organization while preserving the interface exposed to existing applications, often through views.
For example, a query asking for an employee's department should return the same answer after the database replaces a table scan with a B-tree index. An application that follows stored record addresses directly would instead depend on that representation. E. F. Codd's1970 relational model explicitly argued against dependencies on ordering, indexes and access paths.[ref] Independence does not mean every schema change is invisible: removing a field an application needs changes its contract unless another representation supplies equivalent information.
A database management system is software that defines, stores, queries and updates databases while managing access, integrity and concurrent use. Its components can include a query parser, optimizer, storage manager, transaction manager and recovery mechanism. A database is the managed collection; the database management system supplies the mechanisms and interfaces used to operate on it.
For example, evaluating SELECT name FROM Person WHERE id = 7 requires interpreting the query, choosing an access path, retrieving visible records and returning the selected field. An index can speed the lookup without changing its logical result. Concurrent updates may require locks or versions, and a crash may require log replay. Merely writing records to a file does not supply those guarantees. PostgreSQL's tutorial illustrates the distinction between client applications, the server process managing database files and connections, and the databases accessed through that server.[ref]
A database index is an auxiliary data structure that helps a database management system locate records satisfying selected conditions. It stores information organized for an access pattern, such as searching a column value, without requiring every database tuple to be inspected. The database query still specifies the result; the index supplies a possible route to it.[ref]
For example, a database relation with a million samples can have an index on sample_id. Looking for one identifier can follow a search structure instead of scanning all rows. A condition matching most rows may still favor a scan, since many separate lookups and row fetches can cost more. The index occupies storage and must be maintained on relevant updates. These costs explain why indexing every column is not automatically the best design.
PostgreSQL is an open-source object-relational database management system supporting SQL, database transactions, constraints and extensible data types. It manages stored relations and concurrent access through a server, rather than merely defining a database query language. The project descends from the Berkeley POSTGRES system.[ref]
For example, SELECT name FROM samples WHERE reading > 5 requests selected rows without prescribing their physical access path. A database index can accelerate retrieval, while database transactions govern the visibility and consistency of related updates. A database relation has no implicit presentation order: a caller needing sorted rows must request an ordering.[ref] These distinctions separate logical results from storage organization and display.
An entity-relationship model describes identifiable entities, their database attributes and the relationships between them before committing to a physical database layout. Entity types classify the objects being recorded; relationship types describe associations, possibly with their own database attributes. Peter Pin-Shan Chen developed the model as a way to retain the meaning of data while relating several existing database models.[ref]
For example, a laboratory records scientific instrument entities and measurement entities, with a relationship saying which scientific instrument produced each measurement. An scientific instrument's serial number is an identifying database attribute. A calibration date can describe an scientific instrument, while a usage duration can describe a particular relationship occurrence. Distinguishing these placements prevents unrelated facts from being conflated.
Cardinality and participation constraints add rules: every measurement might require exactly one scientific instrument, while an scientific instrument can produce zero or many measurements. An entity-relationship diagram records these choices. Translating the design to a relational schema introduces database relations, identifiers and appropriate foreign keys; the diagram itself does not require a particular index or file structure.
An entity type whose instances inherit the identifier, database attributes and relationships of a more general entity type. A total disjoint specialization requires every parent instance to belong to exactly one listed subtype.
A diagram of an entity-relationship model, including relationship cardinalities and, when needed, subtype and identification constraints.
A database transaction groups database operations into a unit that can be committed or rolled back. Transaction atomicity means that its updates take effect together or not at all. Transaction durability concerns retaining an acknowledged committed result after a failure; transaction isolation controls which effects concurrent database transactions may observe.[ref]
For example, transferring seven units from an account with balance twenty to one with balance five should produce balances thirteen and twelve. A failure after only the debit must not leave thirteen and five as a completed transfer. In SQL, a transaction block uses BEGIN, then the related updates, then COMMIT; ROLLBACK abandons the uncommitted changes.[ref]
Isolation is a separate question from grouping statements. Two concurrent transfers can each read an old balance before either writes. A correct application must select suitable transaction isolation or locking and handle possible conflicts; merely putting the statements in a database transaction does not establish every desired invariant. The corresponding database schedule records their interleaving.
Transaction isolation specifies which effects concurrent database transactions may observe and which interleavings are permitted to commit. Serializable isolation requires the combined result to be equivalent to some serial ordering, even when execution actually overlaps. Weaker levels admit particular anomalies; their names require implementation-specific interpretation.[ref]
For example, two doctors are on call. Each transaction reads that both are available and removes its own doctor, intending to leave someone on call. If each sees an old snapshot and they update different rows, both may commit under a snapshot-based level that does not prevent this write skew. No serial ordering would let both checks pass. PostgreSQL Serializable can reject such conflicting executions, so the application must be prepared to retry a whole transaction. Isolation protects a stated concurrency model; it does not repair a faulty business rule within one transaction.
Transaction durability means that an acknowledged committed transaction survives failures covered by the database's durability model. A successful statement that has not committed is not yet such a promise. The failure model and storage configuration matter: surviving a process crash does not automatically mean surviving destruction of every storage replica.
Write-ahead logging provides one mechanism. Before reporting a durable commit, persist the required log records. Modified data pages can be written later because recovery can replay their committed changes from the log. The log-before-data rule also prevents a page from reaching persistent storage without the recovery information needed to interpret it.[ref] Durability therefore concerns persistence of the committed result, whereas transaction atomicity concerns which parts of a transaction may take effect. Faster asynchronous commit settings can deliberately weaken the interval covered by the acknowledgment.
Transaction atomicity gives a database transaction an all-or-nothing effect: either its committed updates take effect together or its partial updates do not remain as a completed result. This can be implemented with undo information, version visibility, recovery logging or combinations of these mechanisms. It does not require every physical write to happen at the same instant.
For example, transferring five units between accounts requires a debit and a credit. If an error occurs after the debit, aborting the transaction must not leave only that debit as the final database result. SQL transaction blocks use BEGIN, COMMIT and ROLLBACK to express the boundary.[ref] Atomicity alone does not say how transactions interact before completion; transaction isolation governs that. Nor does it make a committed result survive a crash; that is transaction durability. External effects such as a sent email require coordination beyond rolling back the database changes.
An ordering of operations of several database transactions that preserves the operation order within each database transaction.
A database schedule that can be turned into a serial database schedule by swapping adjacent operations of different database transactions that do not conflict. Reads and writes of the same object conflict when at least one is a write.
A directed graph with a vertex for each database transaction and an edge from the earlier to the later participant in each conflicting pair of operations. A database schedule is a conflict-serializable database schedule exactly when this directed graph has no directed cycle.
A database schedule in which every database transaction runs as one contiguous group, without interleaving another database transaction.
A block transferred between storage and memory; database query plan costs are often estimated by the number of such transfers.
A database query requests information computed from stored data. It can filter database tuples, project selected database attributes, combine database relations or aggregate groups. For example, a query can return the count of orders per customer rather than retrieving every order separately. The query specifies a result; a database query plan chooses operations for computing it. Equivalent result specifications can have very different costs under different indexes and data sizes. In SQL, duplicate rows and null values require conventions beyond the simplest mathematical relational algebra.
Database query optimization chooses a database query plan for a database query by comparing ways to obtain the required result. The choice includes the order of relational joins, use of a database index, and the algorithms implementing those relational joins. A cost model estimates work from quantities such as input sizes and storage accesses. The selected plan minimizes estimated cost among considered alternatives; this need not be the fastest possible plan in reality.[ref]
Consider a relational join of one million orders with customers, followed by a condition selecting ten customers. Applying the customer condition first can greatly shrink a join input. If a suitable database index locates those customers' orders, the engine may avoid scanning all orders. If almost all customers qualify, a sequential scan can instead be cheaper than many scattered index accesses. These are alternative ways of computing the same answer, not different meanings of the query.
Join order also matters with three or more inputs: an early join that creates a huge intermediate result can dominate the total work. Exhaustive search itself becomes expensive as the number of inputs grows. PostgreSQL therefore switches to a heuristic search above a configurable threshold. Inaccurate size estimates can favor a poor plan even when the search algorithm correctly minimizes its cost model.[ref]
Database query processing is the sequence that turns a database query into returned results. In a typical SQL system, it includes parsing the text, checking names and types, rewriting the query when appropriate, database query optimization, and executing the chosen database query plan. Planning chooses operations; execution actually performs storage accesses, relational joins, filtering and aggregation.[ref]
For a request for each customer's total order value, the engine might scan orders, group their database tuples by customer identifier, sum the values and return one result per customer. An alternative plan can first restrict the orders using a database index. The result specification alone does not prescribe which access method performs the work.
A query against a stored database view illustrates why processing has more than a text-to-loop step. PostgreSQL can rewrite the reference into a query on the underlying tables before planning. The executor then walks the resulting plan and obtains rows from its operations. The distinction is useful when diagnosing a slow query: excessive planning work, a poor plan, and unexpectedly expensive execution are different causes even if the same statement triggered them.[ref]
A physical recipe for evaluating a database query, specifying scans, index accesses and implementations of relational joins and duplicate elimination.
A relational join implementation that retains a block of outer records in memory while scanning the inner input. With outer pages per block, outer pages and inner pages, its data-read cost is , before temporary output costs.
An index whose search order determines, or closely follows, the physical placement of the data records. Clustering by one database attribute does not give indexed access by an unrelated database attribute.
Computes a summary such as a count, sum or arithmetic mean over a collection of records.
Database query results may contain repeated records, with their multiplicities retained.
Database query results contain no repeated database tuples. SQL usually uses bag semantics unless duplicate elimination is requested.
A database query language expresses requests over a database using a defined syntax and semantics. A declarative language describes the desired result while allowing a query optimizer to choose its evaluation. SQL is a prominent example for relational databases; relational algebra supplies operations useful for representing and transforming query plans. Expressive power, treatment of duplicates and null values, and the ability to update data vary by language. A query that is syntactically accepted is not automatically safe or efficient on arbitrary datasets.
Tuple relational calculus describes query answers by predicates over database tuples rather than a sequence of storage operations. A set-builder expression returns tuples satisfying a formula built from relation membership, attribute comparisons, logical connectives and quantifiers. For example,
selects employee tuples whose salary exceeds100. A join can be expressed using existentially quantified tuples from two relations and equality of their shared identifying attribute.
The formula needs a safety restriction. Over an unrestricted infinite value universe, a query such as can demand infinitely many absent tuples. Safe tuple relational calculus restricts results to information grounded in the input and explicit constants. Under the usual finite-relation assumptions, its expressive power agrees with relational algebra. This gives a declarative query language while leaving the database system free to choose an efficient query plan.
Safe tuple relational calculus restricts tuple relational calculus so that output values come from the finite input relations or explicit constants, with suitable restrictions on quantification. This prevents an apparently simple database query from demanding an infinite result over an unrestricted value universe. Database query safety is a semantic requirement; merely writing a familiar-looking formula does not ensure it.[ref]
For a finite unary relation , the database query safely returns . In contrast, over all integers requests every other unary database tuple, an infinite collection. Restricting negation to tuples already drawn from another finite relation gives an expressible finite set difference. Under the usual finite relational model, the safe calculus and relational algebra express the same queries.
Domain relational calculus describes a database query using first-order logic variables ranging over individual attribute values. A formula specifies which output tuples qualify, instead of prescribing a sequence of physical scans or joins. This differs from tuple relational calculus, whose variables range over whole database tuples.
For example, if records an scientific instrument identifier and a measurement value, then
returns identifiers with some recorded value above five. The existential quantifier binds , while is the output variable. Each returned identifier occurs in a stored database tuple, so extending the ambient domain by unused values cannot change the result.
The apparently simple query behaves differently: with unrestricted quantification, it can return every unused domain value as well as absent recorded values. Database query safety and domain independence of a database query formalize why a finite input must not silently turn such a query into an unbounded enumeration. A finite candidate relation gives the safer intended query .
The requirement that a database query have a finite, appropriately range-restricted answer on finite input. In the usual domain-independent interpretation, extending the ambient value domain with unused values must not change the answer. The absence of a negation symbol alone does not ensure safety.
A database query has the same result when its ambient domain is extended while all input database relations are unchanged.
SQL is a language family for defining and querying relational databases and modifying their contents. A query such as SELECT customer, COUNT(*) FROM orders GROUP BY customer groups rows by customer and counts each group. A WHERE condition filters rows before grouping; HAVING filters groups afterward. Implementations usually use bag semantics, retaining duplicates unless removed explicitly. Null values also introduce three-valued condition evaluation, so ordinary two-valued logical rewrites need care. Standard features and vendor extensions must be distinguished when portability matters.[ref]
SQL NULL marks a missing or unknown value. Ordinary comparisons involving SQL NULL, including NULL = NULL, produce the truth value unknown. To test a scalar value for absence, use IS NULL; to compare while treating two missing values alike, use IS NOT DISTINCT FROM.[ref]
This three-valued logic changes query reasoning. 5 NOT IN (2, NULL) is unknown: five differs from two, but the missing value might be five. A relational selection retains true conditions, so unknown does not pass the filter.[ref]
Database aggregation has operation-specific rules. On values 2, NULL, 4, COUNT(*) counts three rows, COUNT(value) counts two non-null values and AVG(value) is three. With no input rows, COUNT returns zero while AVG and SUM return SQL NULL. Therefore a blanket rule that all database aggregation ignores SQL NULL is insufficient.[ref]
Relational division expresses an all-required-matches query. Let be a database relation, the required values and an explicit candidate database relation. The desired result is
It can be constructed using Cartesian product, relational projection and relational difference:
The inner relational difference contains missing required pairs. Its relational projection identifies failing candidates; removing them from proves the formula.
For example, can record scientific instrument/test pairs and the required tests. An scientific instrument with both pairs passes; one with only the heat pair does not. This is a universal quantifier requirement, unlike an ordinary relational join asking for some match.
When is empty, vacuous truth gives . Choosing is conventional but cannot recover candidates absent from ; use an independently stored scientific instrument database relation if those candidates must be included. Stating the candidate universe makes this edge case unambiguous.
For two database relations with compatible database attributes, retains the database tuples in the first and absent from the second.
Combines database tuples satisfying a matching condition. A natural join uses equality of all shared database attribute names.
A natural join combines two database relations by equating every identically named attribute and retaining one copy of each common attribute. If people(id,name) contains (7,Ada) and scores(id,mark) contains (7,90), their natural join contains (7,Ada,90). With no common names it becomes a Cartesian product. Matching several common columns requires all their equalities, so adding a same-named column can silently change the result. An explicit join condition is often clearer when schemas evolve. In SQL, duplicates and null values follow the implementation's join semantics.[ref]
Retains specified database attributes, eliminating repeated database tuples under set semantics.
Retains the database tuples satisfying a condition.
A database organized using the relational model.
The relational model represents data as database relations, which are sets of database tuples over specified value domains. A relational schema names the database attributes and states database integrity constraints; an instance supplies their current values. Relational algebra describes operations on this logical data, while storage order, indexes and access paths belong to its implementation.
For example, the database relation Reading(sample, value) can contain . A relational selection for values above five followed by relational projection onto sample identifiers returns . Changing the underlying file layout or adding an index need not change that query's meaning. Mathematical database relations have set semantics; SQL often uses bag semantics and has additional SQL NULL behavior.
In his 1970 paper, E. F. Codd argued for data independence: application logic should not rely on particular stored orderings, indexes or navigational paths. His relational formulation made that separation central to database design.[ref]
Names of database relations, their database attributes and value domains, together with applicable database integrity constraints.
Database normalization organizes relational schemas using their dependencies to reduce redundant facts and update anomalies. If a table stores (student,course,instructor,office) and each instructor has one office, repeating the office in every enrolment creates multiple places to update. Splitting instructor-office facts into a separate relation can remove that redundancy. A decomposition must be checked for lossless join and, when needed, dependency preservation; merely creating more tables is not a correctness argument. Different normal forms impose different conditions on functional dependencies and other dependency types.
A decomposition has a lossless join when projecting any permitted Database relation onto the component schemas and taking their natural join reconstructs exactly the original relation. Every original tuple reappears; the substantive requirement is that no spurious tuples are introduced. The dependencies defining permitted instances are essential to the claim.
For example, decompose into and . If is a functional dependency, this is lossless. Any joined tuple combines and from original rows; the dependency forces every row with that to have the same , so the combined tuple was already present. Without that dependency, original rows and yield two additional crossed combinations on rejoining. Removing redundancy is therefore insufficient: the decomposition must preserve which facts belonged together. A binary decomposition is lossless under functional dependencies when the common attributes determine one of its component schemas.
A decomposition in which the database integrity constraints can be enforced using constraints on individual component database relations, without computing their relational join.
A decomposition of a relational schema whose component relational projections always reconstruct the original database relation by natural join, without extra database tuples.
For database tuples agreeing on , requires the combinations obtained by swapping their values on and on the remaining database attributes to occur too. A functional dependency implies the corresponding multivalued dependency; complementation exchanges with the remaining database attributes. A multivalued dependency is trivial if or is the full relational schema.
The set of database attributes determined by under given functional dependencies. Start from and repeatedly add the right-hand side of each functional dependency whose left-hand side is already included.
A foreign key is a database integrity constraint requiring designated database attributes to match a referenced identifier in a database relation. The referenced database relation may be the same one: a self-reference can represent each employee's manager. The identifier being referenced must have the required uniqueness, but the referencing database attributes need not be unique; several employees can have the same manager.[ref]
For a simple example, let Instrument(id) contain identifiers . A measurement carrying instrument_id=4 has a matching scientific instrument; one carrying instrument_id=7 violates the foreign key. Deleting scientific instrument four while measurements still refer to it requires a stated policy: reject the deletion, cascade a suitable deletion, or apply another permitted referential action.[ref]
SQL NULL and compound foreign keys require an explicit matching policy. A nullable simple reference can represent no parent, whereas NOT NULL can require a reference. A foreign key enforces reference existence; it does not by itself prove that a self-referencing structure has no directed cycles.
An inclusion-minimal superkey: removing any of its database attributes destroys the superkey property. Minimal means inclusion-minimal, not necessarily smallest in size.
The candidate key selected as the principal identifier for database tuples of a database relation.
A condition required of every legal instance of a relational schema.
A named component of a database tuple.
One record of a database relation, assigning a value to each database attribute.
A set of database tuples with the same named database attributes; unlike an SQL result without duplicate elimination, a mathematical database relation has no repeated database tuples.

Articles by others on the same topic (1)