relational algebra

relational algebra
Name	relational algebra
Field	Computer science
Introduced	1970s
Founders	Edgar F. Codd
Related	Relational model, Relational calculus, Structured Query Language

relational algebra

Relational algebra is a formal system for manipulating tables of data introduced as part of the Relational model by Edgar F. Codd and developed in the context of IBM research during the 1970s; it underpins query processing in System R, influenced the design of Structured Query Language, and interacts with formal logics such as predicate logic and first-order logic. The formalism provided a foundation for academic work at institutions such as University of California, Berkeley, Massachusetts Institute of Technology, and practical systems at companies including Oracle Corporation and Microsoft. It has been central to standardization efforts led by ANSI and ISO and has influenced textbook treatments at publishers like Addison-Wesley and MIT Press.

Introduction

Relational algebra was proposed to give a set of operations that take one or more relations and produce a relation, serving as an algebraic counterpart to the Relational model of database management. Early foundational publications appeared in journals associated with ACM and IEEE, and the topic was taught in courses at universities such as Stanford University and Carnegie Mellon University. Work on implementation and optimization connected it to research on query optimization at projects like System R and influenced commercial products including Ingres and DB2.

Formal Definition and Notation

Formally, a relation is a finite set of tuples over a scheme; the algebra defines operators that map relations to relations using typed schemas and attribute names. The notation used in canonical texts by Edgar F. Codd and later authors such as Chris Date and Hector Garcia-Molina borrows from set theory and tuple relational calculus; universities like Princeton University and University of Washington present signature-based typing and operators with arity and attribute lists. Mathematical tools from set theory, first-order logic, and predicate logic provide proofs of closure, equivalence, and expressive power; these properties were formalized in research by scholars associated with Bell Labs and groups at AT&T.

Fundamental Operators

The core unary and binary operators include selection, projection, union, set difference, Cartesian product, and rename, while derived operators include natural join, intersection, division, and theta-join. Canonical presentations attribute the algebraic formulation to work at IBM and exposition by Edgar F. Codd, with formal proofs appearing in proceedings of SIGMOD and VLDB. Implementations in Oracle Corporation's products, Microsoft SQL Server, and PostgreSQL map these operators to execution plans with scans, joins, and sorts; database textbooks from Addison-Wesley publishers show algebra-to-plan transformations studied in courses at MIT. Advanced operators such as outer join and semi-join were introduced in research at University of California, Berkeley and incorporated into industrial systems like Ingres and Informix.

Relational Algebra vs. Relational Calculus and SQL

Relational algebra is equivalent in expressive power to tuple relational calculus and domain relational calculus under suitable safety restrictions, a fact established in theoretical work connected to Edgar F. Codd and others and presented at venues like PODS and ICDT. The translation between algebra and declarative languages such as Structured Query Language is central to query rewriting in engines developed by teams at Google and Amazon Web Services; academic comparisons appear in courses at Harvard University and research by scholars affiliated with University of Toronto. While algebra provides procedural operators, calculus and SQL provide declarative syntaxes; mapping optimizers in systems like System R and projects at IBM Research perform algebraic rewrites guided by cost models and rules from ACM SIGMOD literature.

Algebraic Properties and Optimization

Properties such as commutativity, associativity, distributivity, idempotence, and laws for selection and projection enable algebraic equivalences used in query optimization. Research on cost-based and rule-based optimizers emerged from System R and was extended in academica at University of Wisconsin–Madison and industrial labs including Bell Labs and Microsoft Research. Techniques such as join ordering, predicate pushdown, and query rewriting exploit these algebraic laws and are implemented in engines like DB2, PostgreSQL, and MySQL; evaluation strategies draw on complexity results from NP-completeness literature and combinatorial optimization studies at Princeton University. Formal verification of optimizer transformations has been pursued at institutions including Carnegie Mellon University and companies such as Google.

Extensions and Variants

Extensions to the classical algebra include bag (multiset) semantics, temporal and probabilistic relational algebras, nested relations, object-relational operators, and extensions supporting analytics and streaming. Work on bags influenced SQL:1999 and implementations in Oracle Corporation and Microsoft SQL Server; temporal models were advanced by researchers at University of Florida and TU Berlin while probabilistic databases were developed in groups at Trinity College Dublin and University of Washington. Nested and object-relational models were explored in projects like POSTGRES and companies such as Informix; streaming and continuous query variants appeared in systems research at Stanford University and IBM Research and standards efforts at W3C.

Category:Database theory