relational model

Uses your system voice; voice and rate in settings.

Q755662Snapshot

≈ 7 min read

Names in other languages

  • Русскийреляционная модель данных
  • Françaismodèle relationnel
  • Portuguêsmodelo relacional
  • Englishrelational model
  • Polskimodel relacyjny
  • 한국어관계형 모델
  • 简体中文关系模型
  • 繁體中文關聯模型
  • 中文關聯模型

Article bodies are English and are not machine-translated. A name in another language is not a translation of the article.

The relational model (RM) is an approach to managing data using a structure and language consistent with first-order predicate logic, first described in 1969 by English computer scientist Edgar F. Codd, where all data are represented in terms of tuples, grouped into relations. A database organized in terms of the relational model is a relational database. The purpose of the relational model is to provide a declarative method for specifying data and queries: users directly state what information the database contains and what information they want from it, and let the database management system software take care of describing data structures for storing the data and retrieval procedures for answering queries. Most relational databases use the SQL data definition and query language; these systems implement what can be regarded as an engineering approximation to the relational model. A table in a SQL database schema corresponds to a predicate variable; the contents of a table to a relation; key constraints, other constraints, and SQL queries correspond to predicates. However, SQL databases deviate from the relational model in many details, and Codd fiercely argued against deviations that compromise the original principles.

The relational model (RM) is an approach to managing data using a structure and language consistent with first-order predicate logic, first described in 1969 by English computer scientist Edgar F. Codd, where all data are represented in terms of tuples, grouped into relations. A database organized in terms of the relational model is a relational database. The purpose of the relational model is to provide a declarative method for specifying data and queries: users directly state what information the database contains and what information they want from it, and let the database management system software take care of describing data structures for storing the data and retrieval procedures for answering queries. Most relational databases use the SQL data definition and query language; these systems implement what can be regarded as an engineering approximation to the relational model. A table in a SQL database schema corresponds to a predicate variable; the contents of a table to a relation; key constraints, other constraints, and SQL queries correspond to predicates. However, SQL databases deviate from the relational model in many details, and Codd fiercely argued against deviations that compromise the original principles. The projection of a tuple t on a finite set of attributes A is t[A] = \{ (a, v) : (a, v) \in t, a \in A \} . The next definition defines relation that formalizes the contents of a table as it is defined in the relational model. Relation A relation is a tuple (H, B) with H , the header, and B , the body, a set of tuples that all have the domain H . Such a relation closely corresponds to what is usually called the extension of a predicate in first-order logic except that here we identify the places in the predicate with attribute names. Usually in the relational model a database schema is said to consist of a set of relation names, the headers that are associated with these names and the constraints that should hold for every instance of the database schema. Relation universe A relation universe U over a header H is a non-empty set of relations with header H . Relation schema A relation schema (H, C) consists of a header H and a predicate C(R) that is defined for all relations R with header H . A relation satisfies a relation schema (H, C) if it has header H and satisfies C . there exist no two distinct tuples t_1, t_2 \in B such that t_1[K] = t_2[K] . A superkey holds in a relation universe U if it holds in all relations in U . Theorem: A superkey K holds in a relation universe U over H if and only if K \subseteq H and K \rightarrow H holds in U . Candidate key A candidate key is a superkey that cannot be further subdivided to form another superkey. A superkey K holds as a candidate key for a relation universe U if it holds as a superkey for U and there is no proper subset of K that also holds as a superkey for U . Functional dependency Functional dependency is the property that a value in a tuple may be derived from another value in that tuple. A functional dependency (FD for short) is written as X \rightarrow Y for X, Y finite sets of attribute names. A functional dependency X \rightarrow Y holds in a relation (H, B) if: X, Y \subseteq H and \forall tuples t_1, t_2 \in B , t_1[X] = t_2[X]~\Rightarrow~t_1[Y] = t_2[Y] A functional dependency X \rightarrow Y holds in a relation universe U if it holds in all relations in U . Trivial functional dependency A functional dependency is trivial under a header H if it holds in all relation universes over H . Theorem: An FD X \rightarrow Y is trivial under a header H if and only if Y \subseteq X \subseteq H . Closure Armstrong's axioms: The closure of a set of FDs S under a header H , written as S^+ , is the smallest superset of S such that: Y \subseteq X \subseteq H~\Rightarrow~X \rightarrow Y \in S^+ (reflexivity) X \rightarrow Y \in S^+ \land Y \rightarrow Z \in S^+~\Rightarrow~X \rightarrow Z \in S^+ (transitivity) and X \rightarrow Y \in S^+ \land Z \subseteq H~\Rightarrow~(X \cup Z) \rightarrow (Y \cup Z) \in S^+ (augmentation) Theorem: Armstrong's axioms are sound and complete; given a header H and a set S of FDs that only contain subsets of H , X \rightarrow Y \in S^+ if and only if X \rightarrow Y holds in all relation universes over H in which all FDs in S hold. Completion The completion of a finite set of attributes X under a finite set of FDs S , written as X^+ , is the smallest superset of X such that: Y \rightarrow Z \in S \land Y \subseteq X^+~\Rightarrow~Z \subseteq X^+ The completion of an attribute set can be used to compute if a certain dependency is in the closure of a set of FDs. Theorem: Given a set S of FDs, X \rightarrow Y \in S^+ if and only if Y \subseteq X^+ . Irreducible cover An irreducible cover of a set S of FDs is a set T of FDs such that: S^+ = T^+ there exists no U \subset T such that S^+ = U^+ X \rightarrow Y \in T~\Rightarrow Y is a singleton set and X \rightarrow Y \in T \land Z \subset X~\Rightarrow~Z \rightarrow Y \notin S^+ . C := ∅ // found candidate keys Q := { H } // superkeys that contain candidate keys while Q <> ∅ do let K be some element from Q Q := Q&nbsp;– { K } minimal := true for each X->Y in S do K' := (K&nbsp;– Y) ∪ X // derive new superkey if K' ⊂ K then minimal := false Q := Q ∪ { K' } end if end for if minimal and there is not a subset of K in C then remove all supersets of K from C C := C ∪ { K } end if end while

Text derived from https://en.wikipedia.org/w/index.php?oldid=1361474021 under CC BY-SA 4.0; modifications have been made.

Sources cited in this revision

No external citation was extracted for this revision. That is a statement about the source, not a claim that none exists.