Set-Theoretic Foundations of Relational Data Model

• Set-Theoretic Foundations of the Relational Data Model is a framework that represents database tables as sets of attribute-indexed functions, enabling non-positional, many-sorted data structures.
• It defines relational algebra operations—such as selection, projection, and join—as standard set-theoretic operations, ensuring closure and logical consistency.
• Extensions incorporating bags, nulls, and formal verification illustrate the practical applicability of set theory to modern SQL semantics and schema constraints.

The set-theoretic foundations of the relational data model provide the formal apparatus for representing and manipulating database tables as mathematical relations. In these foundations, a relational table is interpreted as a finite set of tuples, each modeled as a total function from attribute names (or roles) to values in their associated domains. This approach generalizes the classical mathematical notion of n-ary (often positional) relations to many-sorted, attribute-indexed structures, giving rise to the algebraic and logical operations central to database theory, query languages, and formal verification. Recent work augments this basis with formal treatments of bags (multisets), nulls, and logic-based query semantics, all firmly rooted in set-theoretic and first-order axioms [0607039], (Mitroshin, 2012, Kelly et al., 2012, Mohamed et al., 2024).

1. Set-Theoretic Primitives for Relations

At its core, the relational model is built upon the basic primitives of set theory:

• Set: A collection of distinct values, with membership denoted $x \in S$.
• Cartesian Product: For sets $X$ and $Y$, $X \times Y = \{ (x, y) \mid x \in X, y \in Y \}$, and generalized to $I$-indexed families as $\prod_{i \in I} X_i$.
• Function: $f: X \to Y$ is a set of ordered pairs, uniquely associating each $x \in X$ to $f(x) \in Y$.
• Tuple as Function: A tuple $t$ over attributes $A$ is modeled as $t: A \to \bigsqcup_{a \in A} \mathrm{Dom}(a)$ with $t(a) \in \mathrm{Dom}(a)$—where $\bigsqcup$ denotes the disjoint union.

An n-ary relation with attributes $A = \{a_1, ..., a_n\}$ is any subset $R \subseteq T_A$, where $T_A$ is the tuple-space over $A$. This formulation enables referring to tuple components by attribute name, supporting unrestricted arities, heterogeneous domains, and non-positional indexing [0607039], (Kelly et al., 2012).

2. Operations of Relational Algebra as Set Operations

The classic operations of the relational algebra are interpreted as set-theoretic operators on these sets of attribute-indexed tuples. Fundamental operations are defined as follows [0607039], (Mitroshin, 2012, Kelly et al., 2012, Mohamed et al., 2024):

• Selection $\sigma_{\theta}(R)$: Subset of $R$ where predicate $\theta$ holds.

$\sigma_{\theta}(R) = \{ t \in R \mid \theta(t) \}$

• Projection $\pi_B(R)$: Restricts tuples to attributes $B \subseteq A$.

$\pi_B(R) = \{ t\restriction_B \mid t \in R \}$

• Cartesian Product $R \times S$: Combine tuples over disjoint attribute sets $A$, $B$.

$$R \times S = \{ u : A \cup B \to \bigsqcup \mathrm{Dom}(c) \mid u\restriction_A \in R,\, u\restriction_B \in S \}$$

• Set Union, Intersection, Difference: Standard set operations, requiring union-compatible schemas.

$R \cup S, \quad R \cap S, \quad R - S$

• Renaming $\rho_{\phi}(R)$: Apply attribute renaming $\phi: A \to C$.

$$\rho_{\phi}(R) = \{ t \circ \phi^{-1} \mid t \in R \}$$

• Natural Join $R \Join S$: Combine tuples from $R \subseteq T_A$ and $S \subseteq T_B$ on shared attributes $C = A \cap B$.

$$R \Join S = \{ u : A \cup B \to \bigsqcup \mathrm{Dom}(c) \mid u\restriction_A \in R,\, u\restriction_B \in S,\, u\restriction_C \text{ agrees} \}$$

• Theta-Join, Equi-Join: Apply selection $\theta$ over $R \times S$.

These operations compose into expressions (queries), preserving closure: every operation yields a relation over a well-defined set of attribute names and domains.

3. Algebraic Laws and Closure Properties

The set-theoretic nature directly enforces key algebraic identities and properties:

• Closure: All operations (selection, projection, join, product, union, difference, renaming) yield relations.
• Associativity and Commutativity: For union, intersection, and natural join (when defined).
• Distributivity: Selection and projection distribute over union; selection distributes over projection if predicates are attribute-independent.
• Idempotence: For suitable domains, $\sigma_{\text{true}}(R) = R$, $\pi_A(R) = R$, and, for join, $R \Join R \supseteq R$.
• Completeness and Consistency: All constructions are finite or first-order in nature; paradoxes such as Russell's do not arise within these operationally closed fragments (Mitroshin, 2012).

4. Role of Attribute Indexing and Non-Positional Tuples

The principal set-theoretic innovation over classical (positional, binary) relations is the indexing of tuple entries by attribute, not by position. This enables:

• Attribute Name Matching: Joins and selections on common attribute names, not just positional alignment, thereby facilitating schema evolution and query formulation.
• Many-Sorted Relations: Each attribute $a$ maps to a separate domain $\mathrm{Dom}(a)$, supporting heterogeneous schemas.
• Formal Account of Renaming: Attribute renaming is purely a bijection on the indexing set, with total preservation of tuple structure [0607039], (Kelly et al., 2012).

Codd’s original model relied on positional $n$-ary tuples; subsequent set-theoretic reconstructions (e.g., Kelly & van Emden's ETR) formalize relations as sets of many-sorted, attribute-indexed functions (Kelly et al., 2012).

5. Logical Foundations and Predicate Calculus Semantics

Set-theoretic relational algebra operations correspond to logical formulas in first-order predicate calculus. Under denotational semantics:

• Atomic Formulas: Correspond to relations.
• Conjunction: Implemented as natural join.
• Existential Quantification: Realized as projection.
• Negation and Disjunction: Respect Boolean operations over relation-sets.

By interpreting open formulas as sets of tuples (assignment maps from variable names), query evaluation becomes a composition of set-theoretic operations, not a satisfaction relation on ground terms. This yields a sound and complete operational semantics for database queries as compositions of relational/algebraic primitives (Kelly et al., 2012).

6. Extensions: Bags, Nulls, and Formal Verification

Recent developments articulate set-theoretic relational foundations in the context of actual database features and verification requirements (Mohamed et al., 2024):

• Bags (Multisets): Finite “bag” sorts $\mathsf{Bag}(\alpha)$ introduce element multiplicities $m_\alpha(a, B)$; set-operations are extended to multiset-union, etc., with set semantics recovered by restricting $m_\alpha(x, B) \leq 1$.
• Nullable Sorts: Model SQL NULL with $\mathsf{Nullable}(\alpha)$ sorts, equipped with constructors $\mathsf{some}_\alpha$, $\mathsf{null}_\alpha$, and testers $\mathsf{isNull}_\alpha$; all query operators are lifted to operate over nullable values, with SQL’s 3-valued logic encoded axiomatically.
• Formal Verification: Modern SMT-based tools (such as those in cvc5) encode SQL/Table semantics as first-order theories over set/bag/nullable operations, enabling automated verification of relational query equivalences and schema properties within the set-theoretic framework.

7. Relational Schema, Keys, and Constraints in Set Theory

A relational schema is formalized as a finite set $\{A_1:D_1, ..., A_k:D_k\}$ assigning attribute names to domains. Constraints are expressed as set-theoretic properties:

• Primary Keys: $K \subseteq \{A_1,...,A_k\}$ is a key iff $\pi_K: R \to \prod_{A \in K} D(A)$ is injective.
• Foreign Keys: Refer to attributes $K$ in $R$, $L$ in $S$, for relations $R$ and $S$, as $\pi_K(R) \subseteq \pi_L(S)$.

All such constraints are first-order, guaranteeing that schema integrity constraints remain both expressible and verifiable in the pure set-theoretic and first-order logical framework (Mitroshin, 2012).

---

The set-theoretic foundation thus provides a rigorous, uniform, and extensible base for the relational data model: relations are sets of attribute-indexed functions; all relational algebra operations are set-theoretic; logical semantics and schema constraints are reduced to set and function theory; and extensions to bags, nulls, and verification follow naturally from these primitives [0607039], (Mitroshin, 2012, Kelly et al., 2012, Mohamed et al., 2024).