BD I: Algèbre Relationnelle

Cadre formel pour la manipulation de tables

Équipe BD

LPSM Université Paris-Cité

2025-09-12

Le modèle relationnel

  • Formulé par E. Codd en 1970.

  • Fondé sur la théorie des relations (partie de la théorie naïve des ensembles).

  • Propose une solution pour

    • la modélisation,
    • l’interrogation de données;

  • Facile à traduire/mettre en œuvre dans des langages réels comme Structured Query Language (SQL) ou dans des environnements de calcul orientés données comme R/dplyr.

Un peu de formalisation

Rappel sur la notion de relation (au sens classique).

Definition : Domaine

Un ensemble d’éléments.

Exemples : entiers \(\mathbb{Z}\), flottants, chaines de caractères \(\mathcal{A}^*\), date, …

Les types d’un langage de programmation comme C, Java, …

Remarque

Un domaine peut se définir :

  • en extension (en donnant la liste de toutes les valeurs possibles) ou

  • en intention (en donnant une propriété caractéristique).

Produit cartésien

Définition : Produit cartésien d’ensembles/domaines

Le Produit cartésien d’une liste de domaines \(D_1, D_2, \dots, D_k\), noté

\[D_1 \times D_2 \times \dots \times D_k\]

est l’ensemble

\[\Bigl\{(t_1,t_2,\dots,t_k);\ t_i\in D_i \text{ pour } i=1,\dots,k\Bigr\}\]

Un élément \(t=(t_1,t_2,\dots,t_k)\in D_1 \times D_2 \times \dots \times D_k\) est appelé \(k\)-uplet.

Définition classique d’une relation

Une Relation \(R\) est un sous-ensemble de

\[D_1\times D_2\times \cdots \times D_k\]

où \(D_1,...,D_k\) sont des domaines.

On a donc :

\[R \subseteq D_1 \times D_2\times \cdots \times D_k\]

Exemple

Deux domaines

\[D_1=\left\{1,2,5\right\} \quad\text{et}\quad D_2=\left\{2,4\right\}\]

Un produit cartésien

\[D_1 \times D_2 = \left\{(1,2),(1,4),(2,2),(2,4),(5,2),(5,4)\right\}\]

Une relation

\[R =\left\{(1,2),(1,4),(5,2),(5,4)\right\}\]

Une relation est un (sous-) ensemble (d’un produit cartésien)

On peut représenter \(R\) par le tableau :

\(X_1\) \(X_2\)
1 2
1 4
5 2
5 4

Chaque ligne de la table correspond à un élément de la relation \(R\)

Relations (classiques)

Dans ce cours, toutes les relations ont un nombre fini d’éléments !

Définition : Cardinalité de la relation \(R\): \(|R|\)

On appelle cardinalité d’une relation \(R\), notée \(|R|\), le nombre d’éléments de \(R\).

Définition : Arité de la relation \(R\)

Soit \(R\subset D_1\times D_2\times \cdots \times D_k\), l’entier \(k\) est appelé arité de \(R\).

On parle parfois aussi de degré d’une relation.

Une relation binaire est une relation d’arité \(2\)

Schémas et relations

Relations et schémas

  • Une relation (classique) peut se voir comme une table à deux dimensions :
    • chaque ligne correspond alors à un \(k\)-uplet (si la relation est d’arité \(k\)),
    • chaque élément d’une colonne est à valeur dans un domaine.
    • Un domaine peut apparaître plusieurs fois dans la définition d’une relation.

En BD relationnelle, on donne un nom à chaque colonne

  • Attribut: nom de la colonne.

Ce nom est choisi de manière à être sémantiquement signifiant.

En BD on ne peut pas avoir deux attributs de même nom dans une même table.

Note

L’idée de nommer les colonnes plutôt que de les désigner par une position/un numéro se retrouve dans les sytèmes qui manipulent des données tabulaires (Pandas/Python, R, Spark, …).

Schéma d’une relation

Définition : schéma

Le Schéma d’une relation \(R\) est la donnée des attributs et domaines de la relation.

Le schéma peut se noter

\[R(A_1\! :\! D_1,A_2\! :\! D_2,\dots,A_k\! :\! D_k)\]

où \(A_i\) : attribut et \(D_i\) : domaine.

Exemple de schéma : table bebes dans babynames 

bd_2023-24> \d bebes
+--------+------------------------+-----------+
| Column | Type                   | Modifiers |
|--------+------------------------+-----------|
| sexe   | integer                |           |
| prenom | character varying(500) |           |
| annee  | integer                |           |
| nombre | integer                |           |
+--------+------------------------+-----------+

La relation bebes a donc pour schéma : \(\left((\textsf{sexe}, \mathbb{Z}), (\textsf{prenom}, \texttt{string}), (\texttt{annee}, \mathbb{Z}), (\texttt{nombre}, \mathbb{Z})\right)\)

Données INSEE. Une ligne de la table bebes nous informe qu’en France (héxagone?), pendant une annee, le nombre de naissances de sexe sexe, ayant reçu le prénom prenom est donnée par la colonne nombre.

bd_2023-24> SELECT * 
FROM bebes 
WHERE sexe=1 AND annee=2000 AND prenom='THÉO' ;

+------+--------+-------+--------+
| sexe | prenom | annee | nombre |
|------+--------+-------+--------|
| 1    | THÉO   | 2000  | 7961   |
+------+--------+-------+--------+

L’arité de bebes est \(4\), sa cardinalité est \(648 614\).

Particularité de la définition orientée BDD d’une relation

En BD, l’ordre des colonnes n’a pas d’importance car on désigne chaque composante d’un \(k\)-uplet par son nom d’attribut et non par sa position/rang.

Résumé informel

  • RELATION ↔︎ TABLE À DEUX DIMENSIONS

  • (NOM DE) COLONNE ↔︎ ATTRIBUT

  • EN-TÊTE DU TABLEAU ↔︎ SCHEMA DE LA RELATION

  • LIGNE ↔︎ TUPLE

  • ENSEMBLE DES LIGNES ↔︎ CONTENU DE LA RELATION

LMD : Opérateurs

Langage de manipulation de données

L’algèbre relationnelle est un système de calcul sur des tables.

Elle est formée d’une collection d’opérateurs qui prennent en argument des tables et retournent des tables.

Remarque :

Les opérateurs prennent en général des arguments supplémentaires qui ne sont pas des tables. La notion d’algèbre relationnelle est inspirée par les structures algébriques comme les groupes, les anneaux, les corps où des opérations internes opèrent sur un ensemble (par exemple \((\mathbb{R}, +, \times)\)), mais elle ne rentre pas exactement dans le cadre.

L’algèbre n’est pas aussi expressive qu’un langage de programmation classique (comme Python). C’est cela qui rend ce modèle de calcul intéressant : il permet de faire des choses pas triviales, mais il est plus facile à utiliser qu’un langage de programmation.

Opérateurs de base

Liste des opérateurs

L’algèbre relationnelle est d’abord un Langage de Manipulation de Données (LMD).

  • Union: \(\Large{\cup}\)

  • Intersection: \(\Large{\cap}\)

  • Différence: \(\Large{\backslash}\)

  • Projection: \(\Large{\Pi}\)

  • Sélection: \(\Large{\sigma}\)

  • Produit cartésien: \(\Large{\times}\)

  • Renommage: \(\Large{\rho}\)

Ces opérateurs s’appliquent à des relations pour produire d’autres relations (le résultat).

Union \(\cup\) et intersection \(\cap\)

Définition

  • L’union et l’intersection sont des opérations portant sur deux relations \(R_1\) et \(R_2\) de même schéma,

  • \(T=R_1\cup R_2\) est constituée des tuples appartenant à \(R_1\) ou à \(R_2\),

  • \(T=R_1\cap R_2\) est constituée des tuples appartenant à \(R_1\) et à \(R_2\).

  • Les schémas de \(R_1\cup R_2\), \(R_1\cap R_2\) sont les mêmes que ceux de \(R_1\) et \(R_2\).

Union : exemple

-- THEO_1900

+------+-----------+-------+--------+
| sexe | prenom    | annee | nombre |
|------+-----------+-------+--------|
| 1    | THEOBALD  | 1900  | 6      |
| 1    | THEODORE  | 1900  | 227    |
| 1    | THEOPHILE | 1900  | 309    |
| 1    | THEODOSE  | 1900  | 3      |
| 1    | THEODULE  | 1900  | 39     |
| 1    | THEOPHANE | 1900  | 3      |
+------+-----------+-------+--------+

-- THEO_2000

+------+-----------+-------+--------+
| sexe | prenom    | annee | nombre |
|------+-----------+-------+--------|
| 1    | THEO      | 2000  | 6      |
| 1    | THAO      | 2000  | 4      |
| 1    | THÉO      | 2000  | 7961   |
| 1    | THÉO-PAUL | 2000  | 3      |
| 1    | THEODOR   | 2000  | 3      |
| 1    | THEOPHANE | 2000  | 47     |
| 1    | THEODORE  | 2000  | 149    |
| 1    | THEOPHILE | 2000  | 336    |
| 1    | THEOPHYLE | 2000  | 4      |
| 1    | THEOS     | 2000  | 3      |
| 1    | THEOTIM   | 2000  | 4       |
| 1    | THEOTIME  | 2000  | 73     |
+------+-----------+-------+--------+

THEO_1900 ∪ THEO_2000

+------+-----------+-------+--------+
| sexe | prenom    | annee | nombre |
|------+-----------+-------+--------|
| 1    | THEODOR   | 2000  | 3      |
| 1    | THAO      | 2000  | 4      |
| 1    | THÉO      | 2000  | 7961   |
| 1    | THÉO-PAUL | 2000  | 3      |
| 1    | THEOBALD  | 1900  | 6      |
| 1    | THEO      | 2000  | 6      |
| 1    | THEODORE  | 1900  | 227    |
| 1    | THEODOSE  | 1900  | 3      |
| 1    | THEODULE  | 1900  | 39     |
| 1    | THEODORE  | 2000  | 149    |
| 1    | THEOPHANE | 1900  | 3      |
| 1    | THEOPHANE | 2000  | 47     |
| 1    | THEOPHILE | 1900  | 309    |
| 1    | THEOTIME  | 2000  | 73     |
| 1    | THEOPHILE | 2000  | 336    |
| 1    | THEOPHYLE | 2000  | 4      |
| 1    | THEOS     | 2000  | 3      |
| 1    | THEOTIM   | 2000  | 4      |
+------+-----------+-------+--------+

Intersection : exemple

-- THEO_1900

+------+-----------+-------+--------+
| sexe | prenom    | annee | nombre |
|------+-----------+-------+--------|
| 1    | THEOBALD  | 1900  | 6      |
| 1    | THEODORE  | 1900  | 227    |
| 1    | THEOPHILE | 1900  | 309    |
| 1    | THEODOSE  | 1900  | 3      |
| 1    | THEODULE  | 1900  | 39     |
| 1    | THEOPHANE | 1900  | 3      |
+------+-----------+-------+--------+

-- THEOD_1900

+------+-----------+-------+--------+
| sexe | prenom    | annee | nombre |
|------+-----------+-------+--------|
| 1    | THEODORE  | 1900  | 227    |
| 1    | THEODOSE  | 1900  | 3      |
| 1    | THEODULE  | 1900  | 39     |
+------+-----------+-------+--------+

THEO_1900 ∩ THEOD_1900

+------+-----------+-------+--------+
| sexe | prenom    | annee | nombre |
|------+-----------+-------+--------|
| 1    | THEODORE  | 1900  | 227    |
| 1    | THEODOSE  | 1900  | 3      |
| 1    | THEODULE  | 1900  | 39     |
+------+-----------+-------+--------+

car THEOD_1900 ⊂ THEO_1900

Différence

Définition

  • La différence de deux relations \(R_1\) et \(R_2\) (de même schéma) est une relation \(T\),

  • de même schéma que \(R_1\) et \(R_2\),

  • constituée des tuples appartenant à \(R_1\) et n’appartenant pas à \(R_2\).

  • On note \(T = R_1 - R_2\) ou \(T = R_1 \setminus R_2\).

Opération non commutative !

\(R_1 - R_2\neq R_2 - R_1\) (en général).

Différence : exemple

-- THEO_1900

+------+-----------+-------+--------+
| sexe | prenom    | annee | nombre |
|------+-----------+-------+--------|
| 1    | THEOBALD  | 1900  | 6      |
| 1    | THEODORE  | 1900  | 227    |
| 1    | THEOPHILE | 1900  | 309    |
| 1    | THEODOSE  | 1900  | 3      |
| 1    | THEODULE  | 1900  | 39     |
| 1    | THEOPHANE | 1900  | 3      |
+------+-----------+-------+--------+

-- THEOD_1900

+------+-----------+-------+--------+
| sexe | prenom    | annee | nombre |
|------+-----------+-------+--------|
| 1    | THEODORE  | 1900  | 227    |
| 1    | THEODOSE  | 1900  | 3      |
| 1    | THEODULE  | 1900  | 39     |
+------+-----------+-------+--------+

THEO_1900 ∖ THEOD_1900

+------+-----------+-------+--------+
| sexe | prenom    | annee | nombre |
|------+-----------+-------+--------|
| 1    | THEOBALD  | 1900  | 6      |
| 1    | THEOPHILE | 1900  | 309    |
| 1    | THEOPHANE | 1900  | 3      |
+------+-----------+-------+--------+

Produit Cartésien

Définition

Soient \(R_1\) et \(R_2\) dont les schémas \(R_1(A_1,A_2,...,A_k)\) et \(R_2(B_1,B_2,...,B_\ell)\) n’ont pas d’attributs communs.

  • Dans le contexte “classique” : \(R_1 \times R_2= \{(e_1,e_2): e_1\in R_1, e_2\in R_2\}\).

  • Dans le contexte BDD, \(R_1 \times R_2\) contient tous les tuples formés par concaténation d’un tuple de \(R_1\) et d’un tuple de \(R_2\).

  • Le schéma de \(R=R_1 \times R_2\) est \(R(A_1,A_2,...,A_k,B_1,B_2,...,B_\ell)\) obtenu par concaténation des schémas de \(R_1\) et \(R_2\).

La condition sur les schémas est indispensable pour que le schéma du résultat n’ait pas deux attributs identiques. On peut contourner ce problème avec un renommage (voir plus loin).

Attention

En algèbre relationnelle, on ne tient pas compte de l’ordre des attributs dans le schéma d’une relation puisque que les attributs ont des noms deux à deux distincts. De ce fait, le produit cartésien est commutatif ( \(R_1\times R_2 = R_2\times R_1\) ce qui n’est pas vrai classiquement) et associatif.

Produit cartésien

Définition formelle

\(R=R_1\times R_2\) est la relation de schéma \(R(A_1,A_2,...,A_k,B_1,B_2,...,B_\ell)\) vérifiant :

  • Pour tout \(t\in R\), il existe \(t_1\in R_1\), \(t_2\in R_2\) tels que :

\[t.A_1=t_1.A_1, \ldots, t.A_k=t_1.A_k,\quad t.B_1=t_2.B_1, \ldots, t.B_\ell=t_2.B_\ell\]

  • Réciproquement, pour tout \(t_1\in R_1\), \(t_2\in R_2\) , il existe \(t\in R\) tels que :

\[t.A_1=t_1.A_1, \ldots, t.A_k=t_1.A_k,\quad \text{et}\quad t.B_1=t_2.B_1, \ldots, t.B_\ell=t_2.B_\ell\quad \text{et}\]

\[$\forall t_1 \in R_1, \forall t_2 \in R_2, \exists t \in R, \qquad t.A_1=t_1.A_1, \ldots, t.A_k=t_1.A_k,\quad \text{et} \quad t.B_1=t_2.B_1, \ldots, t.B_\ell=t_2.B_\ell \qquad\qquad\qquad\]

Projection ( \(\Large{\pi}\) )

Définition

  • La projection d’une relation \(R\) de schéma \(R(A_1,\dots, A_k)\) sur les attributs \(A_{i_1}, \dots, A_{i_p}\), avec \(i_1,..., i_p\in \{1,...,k\}\), est la relation \(S\)
    • de schéma \(S(A_{i_1}, \dots,A_{i_p})\)
    • dont les tuples sont obtenus par élimination des attributs non mentionnés dans \(A_{i_1}, \dots, A_{i_p}\) (et par élimination des doublons).
    • On note \(S = \pi_{A_{i_1}, \dots, A_{i_p}} (R)\).
  • Définition formelle : \(s\in S\quad \iff \quad \exists t\in R, \forall k\in\left\{1,\dots,p\right\}\qquad s.A_{i_k}=t.A_{i_k}\)

Remarque

Implicitement, on a élimination des doublons car une projection peut produire plusieurs fois le même tuple. Or une relation est un ensemble de tuples, et un ensemble ne peut pas contenir plusieurs fois le même élément.

Remarque

À la différence des opérations ensemblistes ∩, ∪, ∖, ×, la projection ne fait pas intervenir que des tables.

On peut considérer la projection comme une fonction à deux arguments, avec un premier argument de type table, et un second argument constitué par une liste d’attributs.

On peut aussi considérer la projection comme une fonction avec un nombre variable d’arguments. Un premier argument de type table, puis des arguments qui désignent des attributs de la table. Si on accepte ce point de vue, on peut adopter la notation :

\[S = \pi(R, {A_{i_1}, \dots, A_{i_p}})\quad \text{ ou bien } \quad S = \textrm{PROJECTION}(R, {A_{i_1}, \dots, A_{i_p}})\] au lieu de \(\quad S = \pi_{A_{i_1}, \dots, A_{i_p}} (R)\)

Dans la suite, nous utiliserons ces notations.

Projection : Exemple

PROJECTION(THEOD_1900, prenom, nombre)

+-----------+---------+
| prenom    |  nombre |
|-----------+---------+
| THEODORE  |  227    |
| THEODOSE  |  3      |
| THEODULE  |  39     |
+-----------+---------+

Ici, aucune éliminitation de doublons n’a été nécessaire

Sélection ( \(\Large{\sigma}\) )

Définition

  • La sélection d’une relation \(R\) par une condition \(C\) est une relation \(S\) :

    • de même schéma que \(R\),

    • dont les tuples sont ceux de \(R\) qui satisfont la condition \(C\).

    • On note \(S = \sigma_C (R)\).

  • La condition \(C\) :

    • s’exprime à l’aide des noms d’attributs de la relation et de constantes (pour les opérandes),

    • on peut utiliser des opérateurs arithmétiques de comparaison ( \(=, \neq, \leq, \geq, <, >\) ) ainsi que des connecteurs logiques ( \(\lnot, \land, \lor\) ).

Mise en garde

On peut utiliser le terme Restriction à la place de Sélection.

Remarque

À la différence des opérations ensemblistes ∩, ∪, ∖, ×, , la sélection comme la projection ne font pas intervenir que des tables.

On peut considérer la sélection comme une fonction à deux arguments, avec un premier argument de type table, et un second argument constitué par une condition (une expression dont l’évaluation sur chaque ligne de la table retourne Vrai, Faux, ou Indéterminé).

On note \[S = \sigma(R, C) \quad \text{ ou bien } \quad S= \textrm{SELECTION}(R, C)\] au lieu de \(\quad S = \sigma_{C} (R)\)

Dans la suite, nous utiliserons ces notations.

Note

Notons que dans un langage comme Python, nous ne disposons pas d’emblée d’un type expression et que la notation proposée ici ne se code pas trivialement en Python.

En langage R, on dispose d’un type expression, la notation proposée ici est mise en œuvre dans le package dplyr.

Sélection : Exemple

SELECTION(THEO_1900, ¬ prenom LIKE ‘THEOD%’)

+------+-----------+-------+--------+
| sexe | prenom    | annee | nombre |
|------+-----------+-------+--------|
| 1    | THEOBALD  | 1900  | 6      |
| 1    | THEOPHILE | 1900  | 309    |
| 1    | THEOPHANE | 1900  | 3      |
+------+-----------+-------+--------+

Renommage

Définition

  • Soit \(R\) de schéma \(R(A_1,\dots, A_k)\), le renommage d’un attribut \(A_i\), \(i\leq k\), en \(B\) est une relation \(S\) :
    • de même contenu (mêmes lignes)
    • de schéma \(S(A_1,...,A_{i-1},B,A_{i+1},..., A_k)\)
    • On le note \(S=\rho_{A_i\mapsto B}(R)\)

Remarque

Comme la projection et la sélection, le renommage ne fait pas intervenir que des tables.

On peut considérer le renommage comme une fonction avec un nombre variable d’arguments. Un premier argument de type table, puis des arguments qui représentent des expressions indiquant comment doivent être renommées certains attributs.

On note \[S = \rho(R, A_{i_1}\mapsto B_1,\ldots, A_{i_p}\mapsto B_p)\quad \text{ ou bien } \quad S = RENOMMAGE(R, A_{i_1}\mapsto B_1,\ldots, A_{i_p}\mapsto B_p)\]

au lieu de \(\quad S=\rho_{A_{i_1}\mapsto B_1,\ldots, A_{i_p}\mapsto B_p}(R)\)

Dans la suite, nous utiliserons ces notations.

Renommage : Exemple

-- THEOD_1900

+------+-----------+-------+--------+
| sexe | prenom    | annee | nombre |
|------+-----------+-------+--------|
| 1    | THEODORE  | 1900  | 227    |
| 1    | THEODOSE  | 1900  | 3      |
| 1    | THEODULE  | 1900  | 39     |
+------+-----------+-------+--------+

ρ(THEOD_1900, sexe→sex, prenom→name, annee→year, nombre→count)

-- THEOD_1900

+------+-----------+-------+--------+
| sex  | name      | year  | count  |
|------+-----------+-------+--------|
| 1    | THEODORE  | 1900  | 227    |
| 1    | THEODOSE  | 1900  | 3      |
| 1    | THEODULE  | 1900  | 39     |
+------+-----------+-------+--------+

Utilité : le renommage permet d’étendre certains opérateurs à des relations de schémas non-disjoints (c.a.d. ayant des noms d’attributs communs), par exemple le produit cartésien.

Résumé

Algèbre relationnelle

Pour interroger une BD, on compose les opérateurs :

  • Opérations ensemblistes classiques : \(\cup\), \(\cap\), \(\setminus\), \(\times\).

  • Projection : élimine des colonnes,

  • Sélection : élimine des lignes.

Exemple

π(σ(THEO_1900, prenom LIKE ‘THEOD%’), prenom, nombre)

ρ(π(σ(THEO_1900, prenom LIKE ‘THEOD%’), prenom, nombre), prenom→name, nombre→count)

De la composition aux tuyaux (pipelines)

On peut rendre la composition de fonctions plus lisible (pour l’humain) en reprenant un mécanisme d’Unix : le pipe (tuyau) |> (notation de )

Plutôt qu’écrire \(g(f(x,y), z, t)\), on convient d’écrire f(x,y) |> g(z, t) voire x |> f(y) |> g(z,t)

Et pour rendre l’expression encore plus lisible on peut écrire

x |> 
  f(y) |> 
  g(z,t)

De la composition aux tuyaux (suite)

On peut ainsi réécrire

ρ(π(σ(THEO_1900, prenom LIKE ‘THEOD%’), prenom, nombre), prenom→name, nombre→count)

en

THEO_1900  |>
  σ(prenom LIKE 'THEOD%') |>
  π(prenom, nombre) |>
  ρ(prenom→name, nombre→count)

Opérations complémentaires : les jointures

Les jointures

On va définir des opérations pratiques pour la manipulation de données : les jointures

  • Jointure

  • Jointure naturelle

  • \(\theta\)-jointure et équi-jointure

Jointure

Définition

Considérons deux relations \(R_1\) et \(R_2\) de schémas disjoints.

La jointure de \(R_1\) et \(R_2\) sous la condition \(C\) est la relation \(T\) :

  • de schéma la concaténation des schémas de \(R_1\) et \(R_2\),

  • formée des tuples du produit cartésien \(R_1 \times R_2\) qui satisfont la condition \(C\),

  • on note \(T=R_1\bowtie_C R_2\).

Règles de formation de la condition de jointure : comme pour la sélection.

Définition formelle

\[R_1 \bowtie_C R_2 = \sigma_C(R_1\times R_2)\]

Remarque

Comme la projection, la sélection et le renommage, la jointure ne fait pas intervenir que des tables.

On peut considérer la jointure comme une fonction avec un nombre variable d’arguments. Deux premiers arguments de type table, puis une expression dont l’évaluation permet de déterminer quels couples de lignes doivent figurer dans la table résultat

On note \[T =\ \bowtie(R, S, C)\quad \text{ou} \quad S= \textrm{JOINTURE}(R, S, C)\] au lieu de \(\quad T=R \bowtie_C C\)

Dans la suite, nous utiliserons ces notations.

Note

Comme dit précédemment, dans un langage comme Python, nous ne disposons pas d’emblée d’un type expression et la notation proposée ici ne se code pas trivialement en Python.

En langage R, on dispose d’un type expression, la notation proposée ici est mise en œuvre dans le package dplyr avec la fonction inner_join().

Exemple sur le schéma world

Lier les pays (les lignes de country) à leur capitale, c’est à dire les couples de lignes de country et de city qui satisfont capital = id

Exemple sur le schéma world (suite)

T =  JOINTURE(country, city, country.capital = city.id)
S =  PROJECTION(T, name_country, name)

+----------------------+------------------+
| name_country         | name             |
|----------------------+------------------|
| Afghanistan          | Kabul            |
| Netherlands          | Amsterdam        |
| Netherlands Antilles | Willemstad       |
| Albania              | Tirana           |
| Algeria              | Alger            |
...
+----------------------+------------------+

Différentes variétés de jointures

Autour de la jointure ⋈(R, S, C) on a pris l’habitude de distinguer des types de jointure selon la forme de l’expression de jointure (la condition C)

On distingue

  • les équi-jointures,
  • les θ-jointures,
  • les jointures naturelles.

Note

Ces distinctions portent sur la condition C. Elles ne sont pas de même nature que les distinctions qui portent sur la manière dont le résultat final est calculé et qui définissent les jointures internes, externes (voir plus loin).

Équi-jointure

Définition

Jointure où la condition est une égalité entre des attributs de types comparables

JOINTURE(country, city, country.capital = city.id)

\(\theta\)–jointure

Définition

Condition de jointure entre attributs de types comparables et comportant au moins un opérateur différent de l’égalité, c’est-à-dire dans \(\left\{<,>,\leq,\geq, \neq\right\}\).

JOINTURE(country, city, country.capital = city.id  ∧ city.population > .5 * country.population_country)

Jointure naturelle

Définition

C’est une équi-jointure où la condition portent implicitement sur tous les attributs communs (de même nom) aux schémas des deux relations.

Dans le résultat, les attributs communs n’apparaissent qu’une seule fois puisque la relation résultat ne peut pas avoir deux attributs de même nom.

On note cette opération : \(R_1 \bowtie R_2\).

On peut écrire la jointure naturelle comme une équi-jointure sur tous les attributs communs suivie d’une projection pour éliminer les attributs doublons.

Jointure naturelle (formalisation)

Considérons deux relations \(R_1(A_1,..,A_k,B_1,...,B_h)\) et \(R_2(A_1,..,A_k,B_{h+1},...,B_\ell)\) de schémas non disjoints avec :

  • \(A_1,..., A_k\) : attributs communs,

  • \(\{B_1,...,B_h\}\cap \{ B_{h+1},...,B_\ell \}=\emptyset\),

Soit \(A'_1, ...., A'_k\) des noms tels que \(\{A_1,..., A_k\}\cap \{ A'_1, ...., A'_k \}=\emptyset\).

Considérons la relation \(S(A_1',..,A_k',B_{h+1},...,B_l)\) définie par : \(\quad S=\rho_{A_1\mapsto A_1'}(\rho_{A_2\mapsto A_2'}(\cdots (\rho_{A_k\mapsto A_k'}(R_2)\cdots ))\)

La jointure naturelle sur les relations \(R_1\) et \(R_2\) est la relation \(R\)

  • de schéma \(R(A_1,..,A_k,B_1,...,B_h, B_{h+1},...,B_\ell)\)

  • définie par : \(\quad R = \pi_{A_1,..,A_k,B_1,...,B_h, B_{h+1},...,B_\ell}(R_1\bowtie_{C} S)\quad\) où \(C\) est \((A_1=A_1') \wedge (A_2=A'_2) \wedge \ldots \wedge (A_k=A_k')\)

Exemple de Jointure naturelle

Afficher la liste des langues parlées en France (schéma world).

bd_2023-24> \d countrylanguage
+-------------+--------------+-----------+
| Column      | Type         | Modifiers |
|-------------+--------------+-----------|
| countrycode | character(3) |  not null |
| language    | text         |  not null |
| isofficial  | boolean      |  not null |
| percentage  | real         |  not null |
+-------------+--------------+-----------+
bd_2023-24> \d city
+-------------+--------------+-----------+
| Column      | Type         | Modifiers |
|-------------+--------------+-----------|
| id          | integer      |  not null |
| name        | text         |  not null |
| countrycode | character(3) |  not null |
| district    | text         |  not null |
| population  | integer      |  not null |
+-------------+--------------+-----------+
bd_2023-24> \d country
+--------------------+---------------+-----------+
| Column             | Type          | Modifiers |
|--------------------+---------------+-----------|
| countrycode        | character(3)  |  not null |
| name_country       | text          |  not null |
| continent          | text          |  not null |
| region             | text          |  not null |
| surfacearea        | real          |  not null |
| indepyear          | smallint      |           |
| population_country | integer       |  not null |
| lifeexpectancy     | real          |           |
| gnp                | numeric(10,2) |           |
| gnpold             | numeric(10,2) |           |
| localname          | text          |  not null |
| governmentform     | text          |  not null |
| headofstate        | text          |           |
| capital            | integer       |           |
| code2              | character(2)  |  not null |
+--------------------+---------------+-----------+

Exemple de Jointure naturelle (suite)

T = JOINTURE(country, countrylanguage)
S = SELECTION(T, name_country='France')
R = PROJECTION(S, name_country, language)
+--------------+------------+
| name_country | language   |
|--------------+------------|
| France       | French     |
| France       | Arabic     |
| France       | Portuguese |
| France       | Italian    |
| France       | Spanish    |
| France       | Turkish    |
+--------------+------------+

Note

Du point de la complexité des opérations à faire par le serveur, il vaut mieux faire le plus en amont possible les sélections puis les projections et enfin les jointures qui sont plus coûteuses en temps de calcul.

On écrira donc plutôt :

S = SELECTION(country, name_country='France')
T = JOINTURE(S, countrylanguage)
R = PROJECTION(T, name_country, language)

Exemples de requêtes

  • Déterminer les noms des capitales des pays situés en Asie.
S =  SELECTION(country, continent='Asia')
T =  JOINTURE(S, city, S.capital=city.id)
R =  PROJECTION(T, name)

Le début de R est

+-------------+
| name        |
|-------------+
| Kabul       |
| Abu Dhabi   |
| Yerevan     | 
| al-Manama   |

Exemples de requêtes (suite)

  • Déterminer les langues parlées dans les pays d’Asie.
S = SELECTION(country, continent='Asia')
T = JOINTURE(R1, countrylanguage)
R = PROJECTION(R2, language)

Le début de R est

+-------------+
| name        |
|-------------+
| Pashto      |
| Arabic      |
| Armenian    | 
| Azerbaijani |

Encore des opérations…

Jointure externe

Perte d’information dans les jointures internes

Dans les jointures que nous avons vues, les tuples du produit cartésien ne satisfaisant pas la condition C (non appariés) disparaissent.

On appelle ces jointures des jointures internes.

On définit aussi des jointures externes qui n’adoptent pas ce comportement.

Définition

La jointure externe entre \(R_1\) et \(R_2\) est une jointure qui produit une relation R3 à laquelle on ajoute les tuples de R1 et de R2 exclus par la jointure, en complétant avec des valeurs nulles pour les attributs de l’autre relation.

Notation : \(R_1 {}^+\!\bowtie^+ R_2\).

Jointure externe gauche

Définition

La jointure externe gauche entre R1 et R2 est une jointure externe pour laquelle on ajoute seulement les tuples de R1 (c’est à dire la relation de gauche) ayant été exclus.

Synonyme : Jointure gauche

Notation : \(R_1 {}^+\!\bowtie R_2\)

Note

Le + est du côté où on ajoute les tupes manquants.

Jointure externe droite

Définition

La jointure externe droite entre R1 et R2 est une jointure externe pour laquelle on ajoute seulement les tuples de R2 (c’est à dire la relation de droite) ayant été exclus.

Synonyme : Jointure droite

Notation : \(R_1 \bowtie^+ R_2\)

Note

Une jointure externe droite peut être réécrite par une jointure externe gauche (et réciproquement) en échangeant l’ordre de R1 et R2.

Note

On peut aussi utiliser une notation fonctionnelle et nommer ces jointures par des mots par exemple OUTER JOIN, LEFT JOIN, RIGHT JOIN.

Jointure externe (exemple)

On veut une table formée par les codes ISO, les noms des pays et (si possible) les noms des capitales. Les territoires sans capitale doivent apparaître en fin de table, avec une colonne nom de capitale nulle.

Obtenir la première partie de la réponse avec une jointure est facile. Pour trouver les territoires sans capitale, il faudrait faire une sélection dans country sur le critère capital IS NULL puis une UNION des deux relations.

Avec une jointure droite, on peut obtenir le résultat en une opération :

S = RIGHT JOIN(city, country, city.id = country.capital) 
R = PROJECTION(S, countrycode, name_country, name)

Division

Définition

La division ou quotient

  • d’une relation \(R\) de schéma \(R(A_1,A_2,\dots,A_k)\)

  • par une relation \(S\) de schéma \(S(A_{p+1},\dots ,A_k)\)

est la relation \(T\) de schéma \(T(A_1, \dots, A_p)\) formée des tuples qui, complétés par chaque tuple de \(S\), donnent un tuple de \(R\).

Autrement dit

\[\begin{array}{l} t\in T(A_1,\dots,A_p) \quad \text{ si et seulement si } \\ \forall s\in S(A_{p+1},\dots ,A_k) \quad \exists r\in R \quad \begin{cases} t.A_1=r.A_1, \dots, t.A_p=r.A_p\\ s.A_{p+1}=r.A_{p+1},\dots, s.A_{k}=r.A_{k} \end{cases} \end{array}\]

On note : \(\quad T = R \div S\)

Division (exemple)

La division permet de répondre à des questions du type :

“Donner tous les X qui ont toutes les valeurs existantes d’une propriété Y”.

Exemple : lister les langues parlées sur tous les continents.

J = JOINTURE(country, countrylanguage)
CL =  PROJECTION(J, continent, language)
C = PROJECTION(CL, continent)

CL ÷ C

Autre possibilité sans la division

J = JOINTURE(country, countrylanguage)
CL =  PROJECTION(J, continent, language)

C = PROJECTION(CL, continent)
L = PROJECTION(CL, language)
--- LPPTC Langues Pas Parlées dans Tous les Continents
LPPTC = PROJECTION((C × L  ∖  CL ), language)

R = L  ∖  LPPTC

Interdéfinissabilité des opérateurs

  • L’union, la différence, le produit cartésien, la sélection et la projection et le renommage sont suffisants pour définir tous les opérateurs que l’on a vus.

  • Mais avoir un panier plus large d’opérateurs simplifie l’écriture des requêtes.

Définitions des autres opérateurs à partir de cet ensemble minimal

  • Pour la jointure : \(R \bowtie_C S = \sigma_C (R \times S)\)
  • Pour l’intersection : \(A \cap B = A \cup B - ((B - A) \cup (A - B))\)
  • Pour la division :

soient \(R(A_1,A_2,\dots,A_k)\) et \(S(A_{p+1},\dots ,A_k)\),

\(T= R ÷ S\quad\) a pour schéma \(\quad T(A_1, \dots, A_p)\) et

\[T = {\Pi}(R, A) \setminus {\Pi}\left(\left(\left({\Pi}\left(R, A\right) × S\right) \setminus R\right), A\right)\]

où \(A = (A_1, \dots, A_p)\).

Petite histoire

L’algèbre relationnelle est la définition d’un modèle de calculabilité restreint.

Comme les modèles de calcul classiques (les langages de programmation comme Python), elle est formée de briques de base (les opérateurs) et de mécanismes d’assemblage (la composition de fonction).

Le modèle de calcul relationnel est bien plus simple qu celui d’un langage de programmation (pas de boucles while, for). Il est aussi moins expressif. On peut montrer qu’il existe de questions qui sont solubles avec un langage de programmation mais qui ne le sont pas avec l’algèbre relationnelle.

L’histoire de l’algèbre relationnelle éclaire le développement des bases de données.

Depuis Wikipedia

In the 1960s and 1970s he [Codd] worked out his theories of data arrangement, issuing his paper “A Relational Model of Data for Large Shared Data Banks” in 1970.[ To his disappointment, IBM proved slow to exploit his suggestions until commercial rivals started implementing them.

Initially, IBM refused to implement the relational model to preserve revenue from IMS/DB. Codd then showed IBM customers the potential of the implementation of its model, and they in turn pressured IBM. Then IBM included in its Future Systems project a System R subproject – but put in charge of it developers who were not thoroughly familiar with Codd’s ideas, and isolated the team from Codd. As a result, they did not use Codd’s own Alpha language but created a non-relational one, SEQUEL. Even so, SEQUEL was so superior to pre-relational systems that it was copied, in 1979, based on pre-launch papers presented at conferences, by Larry Ellison, of Relational Software Inc, in his Oracle Database, which actually reached market before SQL/DS – because of the then-already proprietary status of the original name, SEQUEL had been renamed SQL.

Codd continued to develop and extend his relational model, sometimes in collaboration with Christopher J. Date. One of the normalised forms, the Boyce–Codd normal form, is named after him.

Codd’s theorem, a result proven in his seminal work on the relational model, equates the expressive power of relational algebra and relational calculus.

Fin

Algèbre Relationnelle