Tutorial D et algèbre relationnelle

A propos de la figure 2.1 ci-dessous (copie de la base de données présente dans de nombreux ouvrages de Chris Date) : celui qui crée des bases de données ou qui n’en est que l’utilisateur est en terrain connu. Dans un contexte SQL, on reconnaît en effet 3 tables : celle des fournisseurs (S), celle des types de pièces (P) et celle des livraisons des pièces par les fournisseurs (SP). Ainsi, on apprend que Fernand a livré 300 écrous, 200 boulons, etc.



Fig. 2.1 - Base de données Fournisseurs et Types de pièces

Dans le cadre du Modèle Relationnel de Données (théorie relationnelle), on ne manipule que des relations, valeurs prises par des variables relationnelles (relvars). Une relation est une construction mathématique ([Date2019], page 6 ; [Date2013], page 17), dont la définition est très précise ([Date2013], page 27, [Date2015], page 89). Une relation est définie comme étant composée d’un en-tête (heading) et d’un corps (body) :

―    L’en-tête H est un ensemble d’attributs, dont le nombre n représente le degré de H (n ≥ 0).

―    Le corps B est un ensemble de n-uplets (tuples), dont le nombre représente la cardinalité de B.

La paire (H, B) constitue une valeur de relation (ou relation pour faire court).


Type d’une relation

On entend par type d’une relation l’expression :

RELATION H

Expression dans laquelle le terme « RELATION » désigne un générateur de type spécifique, en l’occurrence RELATION, utilisé pour définir une variable relationnelle ou une relation.

Un attribut est un élément d’un en-tête H, constitué d’une paire <A, T>, où A désigne un nom d’attribut et T le type de cet attribut. Deux attributs de H ne peuvent pas avoir le même nom (cf. [Date2013], page 18).

Par exemple, l’en-tête de la relation S de la figure 2.1 est composé des attributs suivants :

    <Sno, INTEGER>
    <Sname, CHARACTER>      /* pour les pressés, CHARACTER est remplaçable par CHAR */
    <Status, INTEGER>
    <City, CHARACTER>

D’où le type de relation :

RELATION {Sno INTEGER, Sname CHARACTER, Status INTEGER, City CHARACTER}

A noter que jusque dans les années quatre-vingts, on utilisait le terme domaine (dû à Codd) plutôt que celui de type (cf. [Date2004], page 111), lequel a pris progressivement le pas et du reste ne fait pas partie de la grammaire de Tutorial D (cf. [TTM2020]).

Un type scalaire n’a pas de composants visibles par l’utilisateur, contrairement à un type non scalaire. Par exemple les types INTEGER et CHARACTER sont scalaires.

Prenons le cas de la déclaration en Tutorial D de la variable relationnelle (relvar) S :

VAR S BASE RELATION
{Sno INTEGER, Sname CHAR, Status INTEGER, City CHAR}
KEY {Sno}  ;

Il y a en l’occurrence un appel au générateur de type RELATION, lequel permet donc à l’utilisateur de définir le type non scalaire :

RELATION {Sno INTEGER, Sname CHAR, Status INTEGER, City CHAR}

Les attributs Sno INTEGER, Sname CHAR, Status INTEGER, City CHAR sont les composants visibles de ce type de facto « utilisateur ».


Plus généralement, comme on l’a vu, si H symbolise l’en-tête d’une variable relationnelle R, celle-ci a pour type RELATION{H}.

De la même façon, le système propose le générateur de type TUPLE faisant que pour reprendre notre exemple, on disposera aussi du type non scalaire :

TUPLE {Sno INTEGER, Sname CHAR, Status INTEGER, City CHAR}

Plus généralement on dispose du type TUPLE{H}.

Les types définis par le système tels que INTEGER, CHARACTER, RATIONAL, BOOLEAN, sont toujours scalaires.
Par contre, le type RELATION {Sno INTEGER, Sname CHAR, Status INTEGER, City CHAR} est défini par l’utilisateur et en l’occurrence non scalaire.

Tutorial D permet de définir des types « utilisateur » (classes des systèmes orientés objets), à l’aide d’un opérateur spécifique, TYPE.

Exemple :

TYPE POINT ... POSSREP {X RATIONAL, Y RATIONAL
CONSTRAINT (X2 + Y2) ≤ 10000)} ;

A charge de l’utilisateur de fournir les opérateurs sans lesquels le type serait sans emploi...

Exemple :

OPERATOR REFLECT (POINT) RETURNS POINT ;
RETURN POINT ( - THE_X(P), - THE_Y(P)) ;
END OPERATOR ;

(Etant donné un point P de coordonnées cartésiennes (x,y), l’opérateur REFLECT produit le point « inverse » de coordonnées (-x,-y). Pour plus d’information, voir [Date2015], page 55).


A noter que Tutorial D ne propose pas le type DATE, dont la définition reste à la charge de l’utilisateur. Il s’agit là d’un exercice un peu fastidieux, rebattu, mais pas bien compliqué et j’en fournis un exemple (cf. Annexe 6.2).

Si maintenant on présente les données de la figure 2.1 sous une forme différente comme ci-dessous, l’utilisateur va être troublé... En effet, la relvar S a phagocyté la relvar SP.


 Fig. 2.2 - Base de données Fournisseurs et Types de pièces, présentation différente


Dans le cadre de la théorie relationnelle, l’attribut PnoQty est un attribut de type RELATION {Pno ,Qty} : les valeurs qu’il prend sont des relations. Ainsi, non seulement les attributs composant l’en-tête d’une relation (et celui de la variable relationnelle dont elle est une valeur) peuvent être du type INTEGER, CHARACTER BOOLEAN et autres types traditionnels ou non, mais ils peuvent aussi être du type RELATION {H}.

Dans la figure 2.2, les valeurs que prend l’attribut PnoQty sont des relations ; c’est un attribut qualifié de RVA (Relation Valued Attribute).

Cela dit, à partir d’une relvar classique (cf. figure 2.1), produire une relvar dont un ou plusieurs attributs sont des RVA est simple avec Tutorial D, au moyen des opérateurs IMAGE_IN et EXTEND (cf. paragraphes 4.3, 4.4) :

 EXTEND S : {PnoQty := IMAGE_IN (SP)} ;

Ce qui se lit ainsi : au moyen de l’opérateur EXTEND ajouter à S un attribut PnoQty qui soit l’image de la projection de SP sur les attributs Pno et Qty.

Plus formellement (Date2015] page 223) :

Cette présentation formelle mérite quelques éclaircissements, aussi dans leurs ouvrages C.J. Date et Hugh Darwen abondent en exemples et exercices et je m’y colle de façon plus modeste.

Pour reprendre le cas des relations S et SP (figure 2.1) :

Soit un n-uplet t1 {Sno 4, Sname "Antoine", Status 20, City "Londres"} de la relation r1 (valeur de S),

Soit un n-uplet t2 {Sno 4, Pno 2, Qty 200} de la relation r2 (valeur de SP) ayant en commun avec r1 l’attribut Sno,

Soit r3 la restriction de r2 contenant tous les tuples tels que t2 :

 {Sno 4, Pno 2, Qty 200},
 {Sno 4, Pno 4, Qty 300},
 {Sno 4, Pno 5, Qty 400}

r4 est la relation image, projection de r3 sur les attributs autres que Sno :

 {Pno 2, Qty 200},
 {Pno 4, Qty 300},
 {Pno 5, Qty 400}

L’expression « EXTEND S : {PnoQty := IMAGE_IN (SP)} » synthétise tout cela, merci Tutorial D !

  L’expression ci-dessus peut faire l’objet d’une vue :

VAR SV VIRTUAL (EXTEND S : {PnoQty := IMAGE_IN (SP)}) KEY {Sno} ;


Prenons une copie SB de la relvar S :

 VAR SB BASE RELATION
 {   Sno INTEGER
    , Sname CHAR
    , Status INTEGER
    , City CHAR
    , PnoQty RELATION {  Pno INTEGER
 , Qty INTEGER }}
 , KEY {Sno} ;

Avec l’instruction de mise à jour suivante :

INSERT SB (EXTEND S : {PnoQty := IMAGE_IN (SP)}) ;

Alors les contenus de SB et S sont les mêmes.

En passant, l’expression

S WHERE (IMAGE_IN (SP)) {Pno} = P {Pno}

permet de savoir quels fournisseurs fournissent tous les types de pièces.

Une clé candidate (clé pour abréger) est un sous-ensemble d’attributs K de l’en-tête H d’une variable relationnelle R, respectant les deux contraintes suivantes :

―  Unicité. Deux n-uplets distincts de R ne peuvent pas avoir simultanément même valeur de K.

―  Irréductibilité. Il n’existe pas de sous-ensemble strict de K garantissant la contrainte d’unicité.

Chaque variable relationnelle est obligatoirement dotée d’au moins une clé candidate.


Exemple avec la variable relationnelle S (cf. figure 2.1) :

VAR S BASE RELATION
 {Sno INTEGER, Sname CHAR, Status INTEGER, City CHAR}
 KEY {Sno} ;

{Sno} est clé de S.


Une variable relationnelle peut bien entendu être dotée de plus d’une clé. Supposons que la paire {Status, City} soit candidate elle aussi à être clé, dans ces conditions, on codera :

VAR S BASE RELATION
 {Sno INTEGER, Sname CHAR, Status INTEGER, City CHAR}
 KEY {Sno} KEY {Status, City} ;

Jusqu’en 2013, la déclaration des clés étrangères a fait l’objet d’une recommandation (RM Very Strong Suggestion 2), mais comme il est aussi simple de mettre en oeuvre une contrainte, exit cette recommandation (cf. [TTM2013]).

Pour plus d’information sur le sujet, se reporter au paragraphe 5.2 (Intégrité référentielle).

Par exemple, pour contraindre que l’attribut Sno de la variable relationnelle SP fasse référence à l’attribut Sno de la variable relationnelle S (de clé {Sno}) :

CONSTRAINT S_SP_FK SP{Sno} S{Sno} ;

Pour contraindre que l’attribut Pno de la variable relationnelle SP fasse référence à l’attribut Pno de la variable relationnelle P (de clé {Pno}) :

CONSTRAINT P_SP_FK SP{Sno} S{Sno} AND SP{Pno} P{Pno} ;

Des deux contraintes on peut du reste n’en faire qu’une :

 CONSTRAINT SP_FK SP{Sno} S{Sno} AND SP{Pno} P{Pno} ;

Mais en cas de viol de cette contrainte, on passera plus de temps à chercher qui de Sno et Pno lequel est l’attribut peccamineux...

3-0-1. Les opérateurs de Codd▲

3-0-2. Expression relationnelle▲

La restriction permet, à partir d’une expression relationnelle r, de produire une relation r′ ayant le même en-tête que celui de r et dont le corps en est un sous-ensemble (cardinal de r′ ≤ cardinal de r).
L’opération met en jeu une condition (expression booléenne) impliquant un ou plusieurs attributs de r et devant être vérifiée par chacun de ses n-uplets.

Syntaxe :

r WHERE condition

Par exemple, en reprenant la base de données de la figure 2.1, l’instruction :

S WHERE City = "Paris" ;

est une restriction de S sur l’attribut City, auquel on applique la condition d’égalité « City = "Paris" » :


    Fig. 3.1 - Résultat de la restriction


Traduction en SQL :

 SELECT Sno, Sname, Status, City
 FROM   S
 WHERE City = "Paris" ;

Objet de la projection

La projection permet, à partir d’une expression relationnelle r d’en-tête H, de produire une relation r′ dont l’en-tête H′ sous-ensemble de H (degré de H′ ≤ degré de H), et dont le corps est constitué de l’ensemble des n-uplets t tels qu’il existe dans r un n-uplet ayant la même valeur que celle de t pour les attributs composant H′.

Syntaxe :

r { X, Y, ..., Z }

expression dans laquelle X, Y, ..., Z sont les attributs de r qui composent H′.

Par exemple, l’instruction :

S {Sno, Status} ;

est une projection de S (cf. figure 2.1) sur les attributs Sno et Status :


 Fig. 3.2 - Résultat de la projection


Résultat que l’on peut par ailleurs obtenir ainsi (projection de tous les attributs « sauf ») :

« S {ALL BUT City, Sname} » est équivalent à « S {Sno, Status} » ;


Traduction en SQL :

SELECT DISTINCT Sno, Status
FROM   S ;


 A noter que la projection est très utilisée par la suite, notamment à l’occasion des contraintes d’intégrité des données (projection sur les attributs entrant dans la constitution des clés).


Cas de l’ensemble vide, TABLE_DEE, TABLE_DUM

L’ensemble vide ne contenant aucun élément, il est de facto un sous-ensemble de tout ensemble. Ceci vaut pour l’en-tête H de chaque relation r, puisque H est un ensemble (d’attributs) : H peut être vide, ne contenir aucun attribut (degré 0). En fait il existe deux relations dont l’en-tête est vide :

(a) Une relation contenant un seul n-uplet, elle porte le nom de TABLE_DEE (DEE pour abréger)

(b) Une relation ne contenant aucun n-uplet, elle porte le nom de TABLE_DUM (DUM pour abréger)

DEE est à interpréter comme VRAI et DUM comme FAUX. Grâce à eux, on peut vérifier qu’une relation est vide ou non.


Exemples

Etant donné qu’il n’y a aucun fournisseur à Madrid :

L’expression « (S WHERE City = "Madrid"){} » renvoie DUM,
ce que confirme l’expression « (S WHERE City = "Madrid"){} = DUM », qui renvoie TRUE,
 ou l’expression « (S WHERE City = "Madrid"){} = DEE », qui renvoie FALSE.

L’expression « (S WHERE City ≠ "Madrid"){} » renvoie DEE,
ce que confirme l’expression « (S WHERE City ≠ "Madrid"){} = DEE », qui renvoie TRUE.


Tutorial D propose un raccourci convivial, l’opérateur IS_EMPTY :

« IS_EMPTY (S WHERE City = "Madrid") » est équivalent à « (S WHERE City = "Madrid"){} = DUM »,
et renvoie TRUE, tandis que
« NOT (IS_EMPTY (S WHERE City = "Madrid")) »
renvoie évidemment FALSE.

Soit les deux expressions relationnelles :

r1 {X1, X2, ..., Xm, Y1, Y2, ..., Yn}

r2 {Y1, Y2, ..., Yn, Z1, Z2, ..., Zp}

Où Y1, Y2, ..., Yn représentent les seuls attributs ayant le même nom (et même type) dans les deux expressions. On observera qu’aucun des attributs X1, X2, ..., Xm n’a le même nom qu'un des attributs Z1, Z2, ..., Zp.

Chaque attribut Yk (k = 1, 2, ..., n) est du même type dans les deux expressions.

Résumons maintenant {X1, X2, ..., Xm}, {Y1, Y2, ..., Yn} et {Z1, Z2, ..., Zp} respectivement en X, Y, Z.
Alors, la jointure naturelle de r1 et r2 est une relation d’en-tête {X, Y, Z} et dont le corps est constitué de tous les n-uplets {X x, Y y, Z z} tels que chacun de ces n-uplets apparaît dans r1 avec X ayant la valeur x et Y ayant la valeur y d'une part, et apparaît dans r2 avec Y ayant la valeur y, et Z ayant la valeur z d’autre part.

Syntaxe

r1  JOIN  r2


Par exemple, dans le cas des relations S1 et P1 ci-dessous, X correspond à la paire {Sno, Sname}, Y au singleton {City} et Z à la paire {Pno, Pname} :


Fig. 3.3 - Relations S1 et P1 

La jointure naturelle

S1 JOIN P1 ;

produit la relation :


Fig. 3.4 - Jointure naturelle des relations S1 et P1 


Équivalents SQL :

SELECT Sno, Sname, City, Pno, Pname FROM S1 NATURAL JOIN P1 ;
SELECT Sno, Sname, S1.City, Pno, Pname FROM S1 JOIN P1 ON S1.City = P1.City ;

Rappel : les relations faisant l’objet d’une union (donc, mutatis mutandis, les expressions relationnelles) doivent être du même type : avoir exactement le même en-tête c’est-à-dire que les attributs qui composent celui-ci sont les mêmes en nom et en type.

Syntaxe

r1 UNION r2

Les expressions relationnelles r1 et r2 sont du même type.


Soit donc P1 et P2 deux relations de même type :

Fig. 3.5 - Les relations de même type, P1 et P2


L’union de P1 et P2 s’écrit simplement

P1 UNION P2 ;

et produit un résultat de même type :


 Fig. 3.6 - Relation produite par l’union des relations P1 et P2


L’opérateur UNION étant de nature ensembliste, seul un des n-uplet <1, Ecrou, Rouge,12.0, Londres> est présent dans le résultat.


Avec SQL :

SELECT Pno, Pname, Color, Weight, City FROM P1
UNION
SELECT Pno, Pname, Color, Weight, City FROM P2 ;


L’opérateur UNION est accompagné de l’opérateur XUNION (union exclusive) : ne figurent dans le résultat que les n-uplets n’appartenant pas à la fois à P1 et à P2.


Fig. 3.7 - Relation produite par l’union exclusive des relations P1 et P2


L’union étant commutative et associative, l’expression relationnelle ci-dessous peut être utilisée si plusieurs relations de même type font l’objet d’une union :

UNION {R1, R2, ... Rn}

Même principe pour l’union exclusive :

XUNION {R1, R2, ... Rn}

Autre opérateur accompagnant UNION : D_UNION (union disjointe). Cet opérateur permet au système de déclencher une erreur si R1 et R2 contiennent des n-uplets ayant même valeur.

Renommage des attributs :
les relations P1 et P2 ci-dessus étant du même type, elles sont joignables.
Supposons maintenant que dans P2 l’attribut Weight soit maintenant nommé Poids :
P1 et P2 ne sont donc plus du même type.
Pour parvenir malgré tout à joindre ces relations, on utilisera l’opérateur RENAME
(cf. le paragraphe 4-1 traitant de cet opérateur, en combinaison avec UNION).

Rappel : comme dans le cas de l’union, les expressions relationnelles faisant l’objet d’une intersection doivent être du même type : avoir exactement le même en-tête c’est-à-dire que les attributs qui composent celui-ci sont les mêmes en nom et en type.

Syntaxe

r1 INTERSECT r2

Les expressions relationnelles r1 et r2 sont du même type.


Soit donc P1 et P2 deux relations de même type :


Fig. 3.8 - Les relations de même type, P1 et P2


L’intersection de P1 et P2 s’écrit simplement

P1 INTERSECT P2 ;

et produit un résultat de même type, ne contenant que les n-uplets ayant la même valeur dans les deux relations :


Fig. 3.9 - Relation produite par l’intersection des relations P1 et P2


L’intersection étant commutative et associative, l’expression relationnelle ci-dessous peut être utilisée si plusieurs variables relationnelles de même type font l’objet d’une intersection :

INTERSECT {R1, R2, ... Rn}


Renommage des attributs si nécessaire : cf. l’opérateur UNION et voir l’opérateur RENAME.

Rappel : les expressions relationnelles faisant l’objet d’une différence doivent être du même type : avoir exactement le même en-tête c’est-à-dire que les attributs qui composent celui-ci sont les mêmes en nom et en type.

Syntaxe

r1 MINUS r2

Les expressions relationnelles r1 et r2 sont du même type.


Soit donc P1 et P2 deux relations de même type :


Fig. 3.10 - Les relations de même type, P1 et P2

La différence de P1 et P2 s’écrit simplement

P1 MINUS P2 ;

et produit une relation de même type, à savoir l’ensemble des n-uplets t tels que t appartient à P1 mais pas à P2 :


Fig. 3.11 - Relation produite par la différence des relations P1 et P2


La différence n’est pas commutative, P2 MINUS P1 produit un résultat différent :


Fig. 3.12 - Relation produite par la différence des relations P2 et P1


Renommage des attributs si nécessaire : cf. l’opérateur UNION et voir l’opérateur RENAME.


Opérateur I_MINUS (included minus)

P1 I_MINUS P2 ;

Si P2 n’est pas inclus dans P1, le système déclenchera une erreur.

Dans le 1er exemple ci-dessus, le système rejettera P1 I_MINUS P2.

Par contre il acceptera l’instruction suivante :

P1 I_MINUS (P2 WHERE Pno = 2) ;

Au résultat :


Fig. 3.13 - P1 I_MINUS (P2 WHERE Pno = 2) 

L’opérateur TIMES permet d’effectuer le produit cartésien de deux relations r1 et r2 si et seulement si elles n’ont aucun nom d’attribut en commun.

Syntaxe

r1 TIMES r2


Exemple

Soit S1 et P1 deux relations n’ayant pas de nom d’attribut en commun :


Fig. 3.14 - Deux relations n’ayant aucun nom d’attribut en commun 


Le produit cartésien de S1 et P1 s’écrit

S1 TIMES P1 ;

et produit le résultat :

Fig. 3.15 - Deux relations n’ayant aucun nom d’attribut en commun


Résultat qui est une relation contenant tous les n-uplets possibles résultant de la concaténation de deux n-uplets, le 1er appartenant à S1 et le second appartenant à P1. Contrairement au produit cartésien mathématique, l’opérateur TIMES est commutatif et associatif.

P1 et P2 n’ayant aucun attribut en commun, dans ces conditions S1 TIMES P1 et S1 JOIN P1 ont le même effet : TIMES est donc à considérer comme un cas particulier de JOIN (à savoir une jointure dégénérée, cf. [TTM2014], page 37).

TIMES ne fait donc pas partie des opérateurs qu’un système orienté D doit impérativement proposer.

La division d’un ensemble A par un ensemble B ne date pas d’aujourd’hui. A titre historique, voici ce qu’écrivait par exemple J. W. Dedekind en 1871 :

« On dit qu’un corps A est un diviseur du corps M (ou que M est un multiple de A) si tous les nombres qui appartiennent à A appartiennent aussi à M ».

Ted Codd a proposé la division relationnelle (cf. Codd1972]). Je traduis ce qu’il a écrit dans [Codd1990] :

« A certains égards, la division relationnelle est semblable à la division de l’arithmétique des entiers. Avec la division relationnelle, tout comme dans la division arithmétique des entiers, il y a un dividende, un diviseur, un quotient et même un reste. [...] Toutefois, au lieu d’être des entiers, les opérandes et les résultats sont des relations. [...] Prenons un exemple dans la division de l’arithmétique des entiers. Supposons que nous divisions 29 par 7. Ce que l’on recherche, c’est le plus grand multiplicateur qui par 7 produit un résultat inférieur ou égal à 29. Ce plus grand multiplicateur est 4 puisque 4  X 7 = 28 et 28 est inférieur à 29, tandis que 5 X 7 = 35 et 35 est supérieur à 29. »

Noter l’expression « le plus grand multiplicateur ». En première approximation, dans le contexte de l’algèbre relationnelle, disons qu’on cherchera là aussi le plus grand ensemble Q tel que le produit cartésien de Q (relation considérée comme quotient) par une relation S considérée comme diviseur soit un sous-ensemble d’une relation R (considérée comme dividende).

Ce qui précède est synthétisé dans une formule du genre de celles qu’on trouve dans [Codd1972] :

R[A ÷ B]S = R[A] ― ((R[A][B]) ― R)[A]

Où R et S sont deux relations, A est un sous-ensemble d’attributs de la relation R, et B un sous-ensemble d’attributs de la relation S, tels que A et B n’ont aucun attribut en commun. L’opérateurest le produit cartésien.

Traduction en Tutorial D :

R{A} MINUS ((R{A} TIMES S{B}) MINUS R) {A}


Application à la base de données de la figure 2.1, pour obtenir la liste des numéros des fournisseurs correspondants :

SP{Sno} MINUS (SP{Sno} TIMES P{Pno} MINUS SP{Sno, Pno}) {Sno} ;

Si on veut compléter avec les attributs des fournisseurs (variable relationnelle S) :

SP{Sno} MINUS (SP{Sno} TIMES P{Pno} MINUS SP{Sno, Pno}) {Sno} JOIN S ;


En fait, Codd utilise la division en tant que contrepartie algébrique de la quantification universelle (quantificateur « Ɐ ») du Calcul Relationnel qu’il décrit dans [Codd 1971]. Son but fut de rendre l’algèbre relationnellement complète, un algorithme de réduction permettant de traduire une expression arbitraire du calcul en une expression algébrique sémantiquement équivalente (se reporter à [TTM2013], page 190).

Citons [Codd1979] :

« Given relations R(A, B1) and S(B2) with B1 and B2 defined on the same domain(s), then, R[B1 ÷ B2]S is the maximal subset of R[A] such that its Cartesian product with S[B2] is included in R. This operator is the algebraic counterpart of the universal quantifier. »


Cela dit, Chris Date rappelle qu’il y a au moins sept opérateurs qui peuvent prétendre à l’appellation « division » ! (cf. [TTM2013] page 189). Il rappelle aussi qu’il est possible de montrer que l’algèbre relationnelle est complète sans qu’il soit nécessaire pour autant de disposer de l’opérateur de division (cf. [TTM2013], page 190), en conséquence de quoi Date propose que cet opérateur disparaisse de Tutorial D, au bénéfice de l’opérateur IMAGE_IN (cf. paragraphe 4.3).

Quoi qu’il en soit, Tutorial D propose par exemple le Small Divide (cf. [TTM2014], page 119). Appliqué à la base de données (Figure 2.1), on obtient l’ensemble des fournisseurs de tous les types de pièces), avec la syntaxe suivante :

S DIVIDEBY P PER (SP) ;

C’est-à-dire le seul fournisseur 1. Pour savoir qu’il s’agit du fournisseur nommé Fernand :

(S DIVIDEBY P PER (SP)) JOIN S ;

    Attention au piège de la division par la relation {} c’est-à-dire lorsqu’en l’occurrence P est vide...

Citons [TTM2014] :

« We offer this lamentable failure on our part as an object lesson to language designers everywhere. We spare you the details of how it happened ... Suffice it to say that language design is hard. » 

Dans le cadre du Modèle Relationnel de Données, chaque opérateur utilise en entrée une ou plusieurs expressions relationnelles et produit une relation en sortie. Il n’existe donc pas d’opérateurs de manipulation des n-uplets. Néanmoins, pour les besoins de la cause, Tutorial D permet d’extraire un n-uplet en particulier, au moyen d’un opérateur ad-hoc (tuple extractor) : TUPLE FROM.

Syntaxe

TUPLE FROM exp

Où exp représente une expression relationnelle.

De la même façon, il est possible d’extraire d’un n-uplet une valeur d’attribut (encore par FROM) :

Syntaxe

attr FROM tup

Où attr est une référence à un attribut et tup une expression de tuple.


Exemple :

Obtenir les numéros des fournisseurs et leur statut, tels que leur statut soit inférieur à celui du fournisseur Paul (Sno = 3).

Solution proposée dans [Date2013] page 62 :

S{Sno, Status} WHERE Status < (Status FROM (TUPLE FROM (S WHERE Sno = 3))) ;

Au résultat :

RELATION {Sno INTEGER, Status INTEGER}
{
TUPLE {Sno 1, Status 20},
TUPLE {Sno2 , Status 10},
TUPLE {Sno 4, Status 20}
}

TUPLE FROM n’est pas indispensable, on obtient le même résultat avec l’instruction suivante (cf. [Date2013] page 69) :

((S{Sno, Status} TIMES ((S WHERE Sno = 3){Status} RENAME {Status AS x})) WHERE Status < x) {Sno, Status} ;

Il faut quand même reconnaître que cette dernière solution n’est pas très intuitive...

Au contraire de la solution SQL :

SELECT Sno, Status
FROM    S
WHERE  Status < (SELECT Status FROM S WHERE Sno = 3) ;

L'opérateur RENAME permet, à partir d’une expression relationnelle r, de produire une relation égale en valeur (même corps), mais avec tout ou partie des noms des attributs ayant été renommés.

Soit A1, A2, ..., An un ensemble de noms d’attributs de r à renommer respectivement en B1, B2, ..., Bn, on écrira :

Syntaxe

r  RENAME {A1 AS B1, A2 AS B2, ..., An AS Bn}

Par exemple, pour produire (au moins conceptuellement) une relation P’ comparable à la relation P de la figure 2.1, tout en y renommant l’attribut Weight en Poids, on utilise l’instruction suivante impliquant la variable relationnelle P :

P RENAME {Weight AS Poids} ;

=>

Fig. 4.1 - Relation P’ résultant du renommage de Weight en Poids


Rappel - RENAME est un opérateur en lecture seule : la relation P est inchangée (en effet, ce n’est qu’une valeur).

Maintenant, si les relations sont des valeurs de variables relationnelles distinctes P1 et P2 joignables, mais dont les attributs Weight et Poids n’ont donc pas le même nom, l’opérateur UNION ne peut pas être utilisé pour ces variables. On va donc faire appel à RENAME, rendant ainsi possible l’union :

P1 UNION (P2 RENAME {Poids AS Weight}) ;

RENAME est bien entendu disponible pour les autres opérateurs, MINUS, INTERSECT, etc.

RENAME n’est pas un opérateur primitif. Exemple :

P RENAME {City AS Patelin} ;

est équivalent à

(EXTEND P : {Patelin := City}) {ALL BUT City} ;

Tout en sachant que EXTEND n’est pas non plus un opérateur primitif (cf. cet opérateur).

Soit les deux expressions relationnelles joignables r1 et r2 (c’est-à-dire ayant un ou plusieurs attributs de même nom et de même type). L’en-tête de r1 étant composé des attributs A1, A2, ..., An, l’expression r1 MATCHING r2 représente la semi-jointure (semijoin) de r1 et de r2, et produit une relation qui est la projection sur {A1, A2, ..., An} de la jointure de r1 et r2.

Syntaxe

r1 MATCHING r2

Avec l’exemple de la figure 2.1, la semi-jointure :

S MATCHING SP ;

est un raccourci pour :

(S JOIN SP) {Sno, Sname, Status, City} ;

Au résultat :


Fig. 4.2 - S MATCHING SP 

Le fournisseur 5 (Patricia) ne figure pas dans le résultat puisqu’absent dans SP.
En revanche, ce fournisseur est présent dans la relation produite par :

S NOT MATCHING SP ;

Raccourci pour l’expression :

S MINUS (S MATCHING SP) ;

(d’où le nom de de semi-différence donné à NOT MATCHING).

Au résultat :


Fig. 4.3 - S NOT MATCHING SP

L’opérateur IMAGE_IN permet de produire ce qu’il est convenu d’appeler une relation image (image relation).

Syntaxe :

IMAGE_IN ( relation expression [tuple expression] )

Définition :

Soit les relations joignables r1 et r2 (c’est-à-dire ayant des attributs de même nom et de même type), soit t1 un n-uplet de r1 et soit t2 un n-uplet de r2, ayant les mêmes valeurs que t1 pour les attributs communs à r1 et r2 ; soit r3 la relation qui est la restriction de r2, contenant tous les n-uplets tels que t2 (c’est-à-dire ayant les mêmes valeurs que t1 pour les attributs communs à r1 et r2), et soit r4 la relation qui est la projection de r3 sur les attributs qui ne sont pas communs à r1 et r2.
Alors r4 est la relation image (par rapport à r2) correspondant à t1.

Exemples :

(a) Pour reprendre le cas de S et SP (cf. figure 2.1) :


Fig. 4.4 - Base de données Fournisseurs et Types de pièces


Soit le n-uplet t1 {Sno 4, Sname "Antoine", Status 20, City "Londres"} de la relation r1 (valeur de la variable S),

Soit le n-uplet t2 {Sno 4, Pno 2, Qty 200} de la relation r2 (valeur de la variable SP) ayant en commun avec r1 l’attribut Sno,

Soit r3 la restriction de r2 contenant tous les n-uplets tels que t2 (c’est-à-dire ayant en commun avec r1 l’attribut Sno) :

{Sno 4, Pno 2, Qty 200},
{Sno 4, Pno 4, Qty 300}, 
{Sno 4, Pno 5, Qty 400} 

r4 est la relation image, projection de r3 sur les attributs autres que Sno :

{Pno 2, Qty 200},
{Pno 4, Qty 300}, 
{Pno 5, Qty 400} 


(b) Application. Quels fournisseurs ont livré moins de trois types de pièces :

S WHERE COUNT (IMAGE_IN (SP)) < 3 ;

Au résultat :


Fig. 4.5 - Fournisseurs ayant livré moins de trois types de pièces


Bien observer que le fournisseur Patricia fait partie du lot bien que n’ayant rien livré, ce que confirme l’instruction suivante :

S WHERE COUNT (IMAGE_IN (SP)) = 0 ;

Avec le résultat :


Fig. 4.6 - Fournisseurs n’ayant livré aucun type de pièces


  Dans les versions de Tutorial D datées d’avant le 23 septembre 2016, IMAGE_IN était présent sous une forme différente mais un tantinet curieuse, sinon déroutante :

S WHERE COUNT (!!SP) < 3 ;

Expression dans laquelle « !!SP » (prononcer « bang bang SP ») est une référence aux relations images concernées.

En tout cas, IMAGE_IN intervient désormais dans les opérations d’agrégation, il remplace avantageusement la division, il permet la mise en oeuvre d’attributs dont les valeurs sont des relations (RVA, Relation Value Attributes), il est une sorte de couteau suisse relationnel...

Exemple : Quels fournisseurs ont livré tous les types de pièces ?

Avec DIVIDEBY :

(S DIVIDEBY P PER (SP)) JOIN S ;

Avec IMAGE_IN et comparaison de relations :

S WHERE (IMAGE_IN (SP)){Pno} = P{Pno} ;

Quel que soit l’opérateur, au résultat il n’y a que Fernand :

RELATION {Sno INTEGER, Sname CHARACTER, Status INTEGER, City CHARACTER}
{TUPLE {Sno 1, Sname "Fernand", Status 20, City "Londres"}}


Fig. 4.7 - Fournisseurs ayant livré tous les types de pièces

Pour obtenir le même résultat avec SQL : quels sont les fournisseurs pour lesquels il n’existe pas de type de pièce qu’ils ne fournissent pas ; exemple :

SELECT DISTINCT Sno, Sname, Status, City
FROM S
WHERE Sno IN
  (SELECT Sno
   FROM SP A
   WHERE NOT EXISTS
      (SELECT *
       FROM P B
	   WHERE NOT EXISTS
            (SELECT *
             FROM SP C
             WHERE A.Sno = C.Sno
               AND B.Pno = C.Pno))) ;

Bien entendu, autant d’experts de la division en SQL, autant de solutions plus astucieuses les unes que les autres. 

L’opérateur EXTEND a pour objet de permettre la production de relations dotées d’attributs supplémentaires, dont les valeurs résultent par exemple d’opérations arithmétiques telles que l’addition la soustraction, la multiplication et la division, portant sur des attributs d’une expression relationnelle r. Associé à l’opérateur IMAGE_IN, EXTEND permet surtout de réaliser des opérations d’agrégation : COUNT, SUM, AVG, MAX, MIN, AND, OR, XOR, et aussi de produire des relations dont l’en-tête comporte des attributs de type relation (RVA).

Syntaxe ([TTM2013], page 148) :

EXTEND r : {liste d’affectations d’attributs}

r est une expression relationnelle. Une affectation d’attribut est de la forme « A := exp », où A est un nouvel attribut (c’est-à-dire absent de l’en-tête de r), et exp la valeur à affecter à cet attribut.

Ainsi, par extension de r, on produit une relation dont l’en-tête est celui de r complété par la liste des attributs tels que A. Le corps de cette relation est composé de l’ensemble des n-uplets t de r, chacun d’eux étant complété par les valeurs affectées à chaque attribut tel que A.

C. J. Date donne l’exemple simple suivant (cf. [Date2015], page 220) :

Dans la relvar P, supposons que le poids des pièces (attribut Weight) soit donné en livres, mais on souhaiterait en plus avoir ce poids en grammes. A cet effet, on utilise EXTEND :

EXTEND P : {Grammes := Weight * 454.0} ;

D’où le résultat :


        Fig. 4.8 -  Poids en livres et en grammes

Rappelons que la relation produite n’est que le résultat d’une expression relationnelle, la valeur de P n’est pas remplacée puisque les opérateurs relationnels sont en lecture seule.

Bien entendu, comme toute expression relationnelle, l’expression ci-dessus peut être emboîtée dans une autre expression. Par exemple, pour obtenir les numéros des types de pièces, leur nom et leur poids en grammes, dont le poids excède 7 kilos :

((EXTEND P : {Grammes := Weight * 454.0}) WHERE Grammes > 7000.0) {Pno, Pname, Grammes} ;

Ou de façon équivalente :

(EXTEND (P WHERE Weight * 454.0 > 7000.0) : {Grammes := Weight * 454.0}) {Pno, Pname, Grammes} ;


Un deuxième exemple, celui de l’extension au moyen de plusieurs attributs (affectation multiple) :

Pour chaque type de pièce ayant fait l’objet de livraisons, donner le détail des informations connues ainsi que le total des quantités livrées, le minimum et le maximum correspondants.

EXTEND (P MATCHING SP) :
{
  QtePieces := SUM (IMAGE_IN (SP), Qty)
, QteMin := MIN (IMAGE_IN (SP), Qty)
, QteMax := MAX (IMAGE_IN (SP), Qty)
} ;

Pour cet exemple, se reporter à l’exemple 7 du paragraphe 4.5 (Opérateurs scalaires d’agrégation).


Troisième exemple : production d’une relation dotée d’un attribut à valeur relation (RVA), nommé PnoQty :

EXTEND S : {PnoQty := IMAGE_IN(SP)} ;


Fig. 4.9 - une valeur de S étendue d’un attribut RVA

   Le fournisseur Patricia n’ayant rien livré, la valeur prise par l’attribut PnoQty est en l’occurrence l’ensemble vide {}.

Pour obtenir les fournisseurs qui n’ont rien livré (pour DEE et DUM, se reporter à l’opérateur de projection) :

(EXTEND S : {PnoQty := IMAGE_IN(SP)}) WHERE PnoQty{} = DUM ;

De façon équivalente :

(EXTEND S : {PnoQty := IMAGE_IN(SP)}) WHERE IS_EMPTY (PnoQty) ;

Les fournisseurs qui ont effectué des livraisons :

(EXTEND S : {PnoQty := IMAGE_IN(SP)}) WHERE PnoQty{} = DEE ;

De façon équivalente :

(EXTEND S : {PnoQty := IMAGE_IN(SP)}) WHERE NOT(IS_EMPTY (PnoQty)) ;

Retour sur la relation image. Reprenons la définition donnée dans le paragraphe 4.3, traitant de l’opérateur IMAGE_IN :

Soit les relations joignables r1 et r2 (c’est-à-dire ayant des attributs de même nom et de même type), soit t1 un n-uplet de r1 et soit t2 un n-uplet de r2, ayant les mêmes valeurs que t1 pour les attributs communs à r1 et r2, soit r3 la relation qui est la restriction de r2, contenant tous les n-uplets tels que t2 (c’est-à-dire ayant les mêmes valeurs que t1 pour les attributs communs à r1 et r2), et soit r4 la relation qui est la projection de r3 sur les attributs qui ne sont pas communs à r1 et r2.
Alors r4  est la relation image (par rapport à r2) correspondant à t1.

Descendons dans la soute. Pour reprendre le cas des relations S et SP (figure 2.1) :

Soit un n-uplet t1 {Sno 4, Sname "Antoine", Status 20, City "Londres"} de la relation r1 (valeur de S),

Soit un n-uplet t2 {Sno 4, Pno 2, Qty 200} de la relation r2 (valeur de SP) ayant en commun avec r1 l’attribut Sno,

Soit r3 la restriction de r2 contenant tous les n-uplets tels que t2 :

{Sno 4, Pno 2, Qty 200},
{Sno 4, Pno 4, Qty 300}, 
{Sno 4, Pno 5, Qty 400} 

r4 est la relation image, projection de r3 sur les attributs autres que Sno :

{Pno 2, Qty 200},
{Pno 4, Qty 300}, 
{Pno 5, Qty 400} 

L’expression « EXTEND S : {PnoQty := IMAGE_IN (SP)} » synthétise tout cela, merci Tutorial D !

   Une fois encore il est à noter que dans cette opération, il n’y a aucune mise à jour de la relvar S :
 Comme tous les opérateurs relationnels, EXTEND opère en lecture seule.
  Rappel : les seuls opérateurs dédiés à la mise à jour des variables relationnelles sont INSERT, DELETE, UPDATE.


L’expression précédente peut faire l’objet d’une vue :

VAR SV VIRTUAL (EXTEND S : {PnoQty := IMAGE_IN (SP)}) KEY {Sno} ;

Prenons par ailleurs une copie SB de la relvar S étendue :

VAR SB BASE RELATION
{  Sno INTEGER
  , Sname CHAR
  , Status INTEGER
  , City CHAR
  , PnoQty RELATION {  Pno INTEGER
  Qty INTEGER }
}
    KEY {Sno} ;

Avec l’instruction de mise à jour suivante :

INSERT SB (EXTEND S : {PnoQty := IMAGE_IN (SP)}) ;

Alors les relations SB et SV ont la même valeur.

Rappel. Dans les ouvrages de Date et Darwen édités avant septembre 2016, IMAGE_IN était présent sous une forme différente.
L’expression « EXTEND S : {PnoQty := IMAGE_IN (SP)} » y est présentée sous la forme équivalente suivante (cf. paragraphe 4.3 (IMAGE_IN)) :

EXTEND S : {PnoQty := !!SP} ;

L’expression « !!SP » étant en l’occurrence une référence à l’image relation.

Ou encore, pour reprendre l’exemple initial "EXTEND P : {Grammes := Weight * 454.0}", en remontant encore dans le passé, on écrivait :

EXTEND P ADD (Weight * 454.0 AS Grammes) ;

En passant, l’expression

S WHERE (IMAGE_IN (SP)) {Pno} = P {Pno} ;

permet de savoir quels fournisseurs fournissent tous les types de pièces. On fait ainsi l’économie de l’opérateur DIVIDEBY.

Au résultat :


Fig. 4.10 - Fournisseurs ayant livré tous les types de pièces


Un autre exemple d’extension :

Donner le détail des informations pour chaque fournisseur, ainsi que les quantités de types de pièces qu’il a livrées :

EXTEND S : {QtePieces := SUM (IMAGE_IN (SP), Qty)} ;


Fig. 4.11 - Total des types de pièces livrées par chaque fournisseur

   Pour Le fournisseur Patricia qui n’a rien livré, l’attribut QtePieces prend la valeur 0.

Les opérateurs relationnels produisent des relations. Mais il est aussi nécessaire qu’une opération puisse produire un résultat scalaire c’est-à-dire dont le type est lui-même scalaire (INTEGER, RATIONAL, CHARACTER, BOOLEAN, ou défini par l’utilisateur). A cet effet, Tutorial D propose les opérateurs suivants et dont les noms parlent d’eux-mêmes :

COUNT, SUM, AVG, MAX, MIN

AND, OR, XOR (qui produisent des résultats de type BOOLEAN).

Syntaxe :

opérateur (exp1 [, exp2])

opérateur est un des opérateurs cités ci-dessus ;

exp1 est une expression de type relation, par exemple

« S »

« S WHERE City = "Paris" »

exp2 est une référence à un attribut, il s’agit ici de son nom.

exp2 doit être omis dans le cas de COUNT et peut l’être si exp1 représente une relation de degré 1, c’est-à-dire ne comportant qu’un seul attribut.

Exemple 1

Le nom de fournisseur alphabétiquement le dernier :

MAX (S, Sname) ;

Au résultat : "Raoul".

Exemple 2

Obtenir la somme de toutes les quantités présentes dans la variable relationnelle SP :

SUM (SP, Qty) ;

Au résultat : 3100.

Exemple 3

Obtenir la somme des quantités distinctes présentes dans la variable relationnelle SP :

SUM (SP{Qty}, Qty) ;

Au résultat : 1000.

(Noter la projection SP{Qty}) ;

Exemple 4

Obtenir la moyenne de toutes les quantités présentes dans la variable relationnelle SP :

AVG (SP, Qty) ;

Au résultat : 258.

Exemple 5

Obtenir le nombre de n-uplets dans la variable relationnelle S :

COUNT (S) ;

Au résultat : 5.

Exemple 6

Obtenir le nombre de n-uplets dans la variable relationnelle SP où Qty > 300 :

COUNT (SP WHERE Qty > 300) ;

Au résultat : 3.

Exemple 7

Extension multiple : pour chaque type de pièce ayant fait l’objet de livraisons,

―  donner le détail des informations connues
―  le total des quantités livrées
―  le minimum et le maximum correspondants

EXTEND (P MATCHING SP) :
{
  QtePieces := SUM (IMAGE_IN (SP), Qty)
, QteMin := MIN (IMAGE_IN (SP), Qty)
, QteMax := MAX (IMAGE_IN (SP), Qty)
} ;


     Fig. 4.12 - Quantités par type de pièce

Exemple 8

Soit la relation suivante, valeur de la variable relationnelle S :


Fig. 4.13 - Une valeur de la variable relationnelle S

Y a-t-il des fournisseurs dont le statut est égal à 20, sinon qui ne résident pas à Londres ?

Avec l’opérateur AND :

AND (S, Status = 20 OR City ≠ "Londres") ;

Le résultat est égal à true, car bien que Fernand et Antoine résident à Londres, ils ont néanmoins un statut égal à 20, quant aux autres fournisseurs, aucun ne réside à Londres.

Suivant l’humeur, on peut encore coder de la façon suivante :

NOT IS_EMPTY (S WHERE Status = 20 OR City ≠ "Londres") ;

Ou plus simplement :

IS_EMPTY (S WHERE Status ≠ 20 AND City = "Londres") ;

Avec l’opérateur XOR. Y a-t-il des fournisseurs, soit dont le statut est égal à 30, soit qui ne résident pas à Londres :

XOR (S, Status = 30 AND City ≠ "Londres") ;

Le résultat est égal à false, à cause de Raoul, Albert et Edmond qui ont un statut différent de 30 alors qu’ils s’ils ne résident pas à Londres.

On peut se poser la question de l’intérêt des opérateurs d’intégration AND, OR, XOR. Ils peuvent être intéressants à l’occasion de la mise en oeuvre des contraintes d’intégrité, lesquelles doivent satisfaire à des expressions booléennes, dont le résultat doit donc être vrai (true), faux (false).

Cela dit, pour revenir sur l’exemple 8, il est bien difficile de trouver des arguments en faveur de

soit

(a) AND (S, Status = 20 OR City ≠ "Londres") ;

soit

(b) IS_EMPTY (S WHERE Status ≠ 20 AND City = "Londres") ;


Remarque concernant l’ensemble vide

En l’occurrence le résultat de COUNT et SUM doit être égal à 0 :

DELETE SP ;

COUNT (SP) ;        /* résultat = 0 */

SUM (SP, Qty) ;     /* résultat = 0 */

AVG donne lieu à une division par 0, donc provoque une erreur :

AVG (SP, Qty) ;     /* Result of AVG on no values is undefined. */

MIN et MAX donnent lieu à un résultat indéfini, donc provoquent une erreur :

MAX (SP, Qty) ;     /* Result of MAX on no values is undefined. */

L’opérateur SUMMARIZE permet de produire une relation comportant le résultat d’opérations scalaires telles que définies dans le paragraphe 4.5 (Opérateurs scalaires d’agrégation), venant compléter une relation donnée.
Pour reprendre l’exemple de la base de données Fournisseurs et Types de pièces (figure 2.1) on peut produire une synthèse, un récapitulatif des pièces par fournisseur : nombre de pièces, quantité totale, etc.

Syntaxe

SUMMARIZE r1 PER (r2) : {liste d’affectations d’attributs}

―   r1 est une expression relationnelle (par exemple SP) contenant les attributs faisant l’objet de SUMMARIZE (en l’occurrence l’attribut Qty de SP) ;

―  r2 est une expression relationnelle (par exemple P) permettant de dire pour quel(s) attribut(s) commun(s) à r1 et r2, donc en fait pour le compte de r2, on effectue l’opération ;

―  liste d’affectations d’attributs nomme les nouveaux attributs devant contenir les résultats de l’opération.

Une affectation d’attribut est de la forme « B := exp », où B est un nouvel attribut (c’est-à-dire absent de r1) et exp la valeur à affecter à cet attribut.


Définition de l’opérateur ([Date2015] page 237) :

Soit les relations r1 et r2 où l’en-tête H2 de r2 est un sous-ensemble de l’en-tête H1 de r1.
Soit A1, A2, ..., An, les attributs appartenant à H2.
Alors l’expression « SUMMARIZE r1 PER (r2) : {B := exp} » représente une extension de r1 en fonction de r2 et produit une relation,
(a)  dont l’en-tête est celui de r2 complété par un attribut B,
(b) dont le corps est composé de l’ensemble des n-uplets t tels que t est un n-uplet de r2 complété par la valeur b de l’attribut B.
Cette valeur b résulte de l’évaluation de l’expression exp pour tous les n-uplets de r1 dont la valeur est égale à celle de t pour les attributs A1, A2, ..., An.


Considérons l’expression

SUMMARIZE SP PER (S{Sno}) : {nbPno := COUNT( )}

Et appliquons la définition :

Soit les relations SP et S{Sno} où l’en-tête {Sno} de S{Sno} est un sous-ensemble de l’en-tête {Sno, Pno, Qty} de SP.
Soit Sno l’attribut appartenant à {Sno}.
Alors l’expression « SUMMARIZE SP PER (S{Sno}) : {nbPno := COUNT( )} » représente le résultat d’un calcul appliqué à SP en fonction de S{Sno} et produit une relation,
(a) dont l’en-tête est celui de S{Sno} complété par un attribut nbPno,
(b)  dont le corps est composé de l’ensemble des n-uplets t tels que t est un n-uplet de S{Sno}, complété par la valeur nbPno de l’attribut nbPno.
Cette valeur nbPno résulte ici de l’évaluation de l’expression COUNT( ) pour tous les n-uplets de SP dont la valeur est égale à celle de t pour l’attribut Sno.
Noter que pour S{Sno}, quand l’attribut Sno prend la valeur 5, l’attribut nbPno prend la valeur 0, puisque la valeur 5 n’existe pas pour l’attribut Sno de SP (tandis que COUNT doit produire un nombre).

Au résultat


Fig. 4.14. Summarize - Nombre de pièces par fournisseur


Avec une liste d’attributs (affectation multiple) :


SUMMARIZE SP PER P{Pno} : {
sumQty := SUM (Qty),
maxQty := MAX (Qty),
minQty := MIN (Qty)
} ;

Au résultat


 Fig. 4.15. Summarize - affectation multiple


Tutorial D recommande d’utiliser EXTEND plutôt que SUMMARIZE. Exemple donné dans [TTM2013], page 249 :

Enrichissons d’abord la base de données des fournisseurs et types de pièces (figure 2.1), par l’adjonction d’une variable relationnelle J des projets ainsi que d’une variable relationnelle SJ associant les fournisseurs et les projets :



Fig. 4.16. Summarize - relations en jeu

En abrégeant la liste des attributs dans les en-têtes :

S {Sno}
SP {Sno, Pno}
SJ {Sno, Jno}
J {Jno}

Considérons la requête :

S JOIN (SUMMARIZE SP PER (S{Sno}) : {NP := COUNT( )})
   JOIN (SUMMARIZE SJ PER (S{Sno}) : {NJ := COUNT( )}) ;

Au résultat :


Fig. 4.17. Summarize - résultat


On produit le même résultat, de façon plus simple, avec l’opérateur EXTEND :

EXTEND S : {NP := COUNT(IMAGE_IN(SP)), NJ := COUNT(IMAGE_IN(SJ))}

Suivons Tutorial D quand il recommande d’utiliser EXTEND plutôt que SUMMARIZE.  

Soit une expression relationnelle r1. En remplacement d’un ensemble d’attributs de type non RVA de cette expression, l’opérateur GROUP permet de produire une relation r2 dotée d’un attribut de type relation (RVA).
r1 et r2 contiennent la même information, seul le mode de représentation change.

L’opérateur UNGROUP permet l’opération inverse : à partir d’une expression relationnelle dotée d’un attribut de type relation (RVA), produire une relation où cet attribut est remplacé par un ensemble attributs non RVA.

Syntaxe

r GROUP {exp} AS att ;

exp représente une liste de noms d’attributs de l’expression relationnelle r et att le nom à donner à un attribut de type relation (RVA) en fonction de exp.

r UNGROUP att ;

att est le nom dans l’expression r d’un attribut de type relation (RVA) devant donner lieu à attributs non RVA.


Exemple. Soit les deux relations R1 (restriction de la relation SP, cf. figure 2.1) et R4 :


Fig. 4.16.  GROUP / UNGROUP

Les relations R1 et R4 représentent la même information. La différence tient en ce que l’en-tête de R4 est doté d’un attribut dont la valeur est une relation (RVA). L’opérateur GROUP permet de produire R4 à partir de R1 et l’opérateur UNGROUP permet de produire R1 à partir de R4.


Application - production de la relation R4 à partir de la relation R1 et inversement

(a)    Déclaration de la variable R1 :

VAR R1 BASE RELATION {Sno INTEGER, Pno INTEGER} KEY {Sno, Pno} ;

Valorisation de R1 (pour l’opérateur INSERT, cf. paragraphe 4.8.3) :

INSERT R1 RELATION
{  TUPLE {Sno 2, Pno 1}
  , TUPLE {Sno 2, Pno 2}
  , TUPLE {Sno 3, Pno 2}
  , TUPLE {Sno 4, Pno 2}
  , TUPLE {Sno 4, Pno 4}
  , TUPLE {Sno 4, Pno 5}} ;

Production de R4 :

R1 GROUP {Pno} AS PnoRel ;

Au résultat :

RELATION {Sno INTEGER, PnoRel RELATION {Pno INTEGER}}
{ TUPLE {Sno 2, PnoRel RELATION {Pno INTEGER} {TUPLE {Pno 1}, TUPLE {Pno 2}}}
 , TUPLE {Sno 3, PnoRel RELATION {Pno INTEGER} {TUPLE {Pno 2}}}
 , TUPLE {Sno 4, PnoRel RELATION {Pno INTEGER} {TUPLE {Pno 2}, TUPLE {Pno 4}, TUPLE {Pno 5}}}}


(b)    A l’inverse, déclaration de la variable R4 :

VAR R4 BASE RELATION {Sno INTEGER, PnoRel RELATION {Pno INTEGER}} KEY {Sno} ;

Valorisation de R4 :

INSERT R4 RELATION
{  TUPLE {Sno 2, PnoRel RELATION {TUPLE {Pno 1}, TUPLE {Pno 2}}}
 ,  TUPLE {Sno 3, PnoRel RELATION {TUPLE {Pno 2}}}
 ,  TUPLE {Sno 4, PnoRel RELATION {TUPLE {Pno 2}, TUPLE {Pno 4}, TUPLE {Pno 5}}}}

Production de R1 :

R4 UNGROUP PnoRel ;

Au résultat :

RELATION {Sno INTEGER, Pno INTEGER}
 { TUPLE {Sno 2, Pno 1}
  , TUPLE {Sno 2, Pno 2}
  , TUPLE {Sno 3, Pno 2}
  , TUPLE {Sno 4, Pno 2}
  , TUPLE {Sno 4, Pno 4}
  , TUPLE {Sno 4, Pno 5}}


  Suivons Tutorial D quand il recommande d’utiliser EXTEND plutôt que GROUP.

Remplaçons en effet :

R1 GROUP {Pno} AS PnoRel ;

Par :

EXTEND R1{Sno} : {PnoRel := IMAGE_IN (R1)} ;

Le résultat est le même. Certes, la 1re expression est plus dense et a priori plu lisible que la 2e. Mais remplaçons l’expression de gauche R1{Sno} par S (cf. la base de données de la figure 2.1), tout en conservant l’expression de droite :

EXTEND S : {PnoRel := IMAGE_IN (R1)} ;

Cette fois-ci, les fournisseurs 1 (Fernand) et 5 (Patricia) sont pris en compte, ce qui ne peut pas être le cas avec GROUP :

RELATION {Sno INTEGER, Sname CHARACTER, Status INTEGER, City CHARACTER, PnoRel RELATION {Pno INTEGER}}
 {
    TUPLE {Sno 1, Sname "Fernand", Status 20, City "Londres", PnoRel RELATION {Pno INTEGER} {}}
   , TUPLE {Sno 2, Sname "Raoul", Status 10, City "Paris", PnoRel RELATION {Pno INTEGER} {TUPLE {Pno 1}, TUPLE {Pno 2}}}
   , TUPLE {Sno 3, Sname "Paul", Status 30, City "Paris", PnoRel RELATION {Pno INTEGER} {TUPLE {Pno 2}}}
   , TUPLE {Sno 4, Sname "Antoine", Status 20, City "Londres", PnoRel RELATION {Pno INTEGER} {TUPLE {Pno 2}, TUPLE {Pno 4}, TUPLE {Pno 5}}}
   , TUPLE {Sno 5, Sname "Patricia", Status 30, City "Arles", PnoRel RELATION {Pno INTEGER}  {}}
 }

  On peut aussi se dispenser de UNGROUP, mais ça n’est pas conseillé !

En effet, en partant d’un exemple proposé par [TTM2013], soit la variable relationnelle z :

VAR z BASE RELATION {sno INT, pno INT, zqty RELATION {qty INT}} KEY {sno, pno} ;

Pour obtenir les fournisseurs dont les quantités de pièces livrées son supérieures à 200,

Avec UNGROUP :

z UNGROUP zqty WHERE qty > 200 ;

Sans UNGROUP :

(EXTEND z WHERE NOT (IS_EMPTY (zqty)) :
   {qty := qty FROM TUPLE FROM zqty}
       WHERE qty > 200) {ALL BUT zqty} ;

Expression dans laquelle on doit récupérer la valeur de l’attribut qty par son extraction dans le n-uplet dont l’en-tête contient l’attribut ayant cette valeur... A vos souhaits !

En aparté : je virerais volontiers GROUP et remplacerais UNGROUP par un hypothétique IMAGE_OUT (qui n’existe pas dans Tutorial D, mais seulement dans mon imagination)...

4-8-1. Cas général / affectation multiple ▲

4-8-2. Les raccourcis : INSERT, UPDATE, DELETE ▲

4-8-3. Opérateur INSERT ▲

4-8-4. Opérateur D_INSERT ▲

4-8-5. Opérateur DELETE ▲

4-8-6. Opérateur I_DELETE ▲

4-8-7. Opérateur UPDATE ▲

Pour ne pas contenir de doublons, c’est-à-dire être réduite à un sac inexploitable par les opérateurs relationnels, les n-uplets composant le corps de chaque relation doivent être dotés d’une clé (key), garantissant leur unicité. Ainsi dans le cas de la variable relationnelle S des fournisseurs (cf. figure 2.1), l’attribut Sno donnera lieu à une clé :

VAR S BASE RELATION
 {Sno INTEGER, Sname CHAR, Status INTEGER, City CHAR}
 KEY {Sno} ;

Une variable relationnelle peut évidemment comporter plus d’une clé. Par exemple, en supposant que la paire {Status, City} soit candidate elle aussi à être clé, on codera :

VAR S BASE RELATION
  {Sno INTEGER, Sname CHAR, Status INTEGER, City CHAR}
  KEY {Sno} KEY {Status, City} ;

Si initialement la paire {Status, City} n’est pas candidate à être clé, mais doit l’être plus tard, Tutorial D ne prévoit pas d’instruction permettant d’ajouter cette clé : à charge du SGBD d’offrir cette possibilité.

Ainsi avec Rel ([Voorhis2004]) :

ALTER VAR S KEY {Sno} KEY {Status, City, Sname} ;

A défaut, on pourrait néanmoins mettre en oeuvre une contrainte :

CONSTRAINT C1 COUNT (S{Status, City}) = COUNT (S) ;

C’est-à-dire que le nombre de paires (Status, City) contenues dans la relation (valeur de la variable relationnelle S) doit être égal au nombre de n-uplets contenus dans cette relation.

L’intégrité référentielle a été prise en compte par Codd dès l’origine, dans son article de 1970 ([Codd1970]). Les clés étrangères constituent en quelque sorte le mortier reliant les relations de façon cohérente, garantissant l’absence de « failles » dans l’édifice. En effet, que penser d’une ligne de facture ne référençant pas « sa » facture ? d’un contrat sans titulaire ? Bref que dire de ces orphelins ? Pour une description complète de l’intégrité référentielle et des clés étrangères se reporter par exemple à [Date2004].

Tutorial D a bien entendu traité à fond du sujet, pour aboutir à la conclusion que l’intégrité référentielle n’est jamais qu’un cas particulier de la dépendance d’inclusion (paragraphe 5.3), laquelle est une contrainte d’intégrité voulant qu’une projection r2{X} d’une relation r2 soit un sous-ensemble d’une projection r1{X} d’une relation r1 :

r2{X}  ⊆  r1{X} /* r1 et r2 ne sont pas nécessairement distinctes */

Ceci s’applique dans le cas de la contrainte d’intégrité référentielle à établir entre les variables relationnelles S (fournisseurs) et SP (livraisons) :

CONSTRAINT C1 SP{Sno} ⊆ S{Sno} ;

Cela vaut évidemment dans le cas des types de pièces (variable relationnelle P) :

CONSTRAINT C2 SP{Pno} ⊆ P{Pno} ;

Ainsi, il ne sera pas possible de créer la moindre livraison orpheline. Qu’en est-il dans le cas de la suppression d’un fournisseur ? Quelle conséquence pour ses livraisons ? Pour reprendre l’exemple de la suppression du fournisseur Fernand (Sno = 1) et de ses livraisons (paragraphe 4.8.5, Opérateur DELETE) :

DELETE S WHERE Sno = 1 ,
DELETE SP WHERE Sno = 1 ;

On est donc dans le contexte d’une affectation multiple, (en l’occurrence double) où les expressions d’affectation (DELETE) sont séparées par une virgule. Certes, il existe la contrainte d’intégrité C1, mais le système ne déclenche celle-ci qu’à la détection du point-virgule, une fois les deux affectations effectuées : le résultat étant cohérent, la suppression de Fernand et de ses livraisons devient effective.

Ce qui vaut pour DELETE vaut pour UPDATE. Même si cela ne paraît pas bien raisonnable, à supposer qu’il faille remplacer la valeur de Sno pour Fernand, qu’à cela ne tienne :

UPDATE S WHERE Sno = 1 : {Sno := 314} ,
UPDATE SP WHERE Sno = 1 : {Sno := 314} ;

En aparté, à propos de DB2 (cf. [FSM1998]) :

• En 1988, avec DB2 V2 on a pu mettre en oeuvre l’intégrité d’entité (clés primaires) et l’intégrité référentielle (clés étrangères), mais avec interdiction de la modification d’une clé primaire référencée par une clé étrangère (option ON UPDATE CASCADE non proposée). Motif ? « The reason for this restriction is philosophical ». Néanmoins, dans la série « faites ce que je dis, mais pas ce que je fais », IBM contrevient allègrement à cette interdiction quant à la mise à jour des tables du catalogue...
  
  En 2025, 37 ans plus tard , selon la référence Db2 13 for z/OS, Administration Guide (SC28-2761-00, page 137) : « You cannot change a parent key column of a row that has a dependent row. »). Na ! la position d’IBM n’a pas varié d’un iota...
  
  
  

Revenons sur l’expression utilisée dans le paragraphe 5.2 (Intégrité référentielle) :

r2{X}  ⊆  r1{X}

Où le symbole « ⊆ » dénote l’inclusion de la relation r2{X} dans la relation r1{X}. r2{X} est un sous-ensemble de r1{X} (r1 et r2 ne sont pas nécessairement distincts). Se reporter au chapitre 13 dans [TTM2013].


Dans l’exemple Entité/Association ci-dessous, un assuré détient au moins un contrat (police d’assurance), et un contrat est détenu par au moins et au plus un assuré :



     Fig. 5.1 - Contrainte d’inclusion


Les variables relationnelles correspondantes sont les suivantes :

VAR Assure BASE RELATION {AssNo INT, AssNom CHAR} KEY {AssNo} ;
VAR Contrat BASE RELATION {CtrNo INT, CtrDate CHAR, AssNo INT} KEY {CtrNo} ;

Dans le contexte du Modèle Relationnel de Données, la détention d’un contrat par un assuré fait bien entendu l’objet d’une contrainte d’intégrité référentielle :

CONSTRAINT C1 Contrat{AssNo} ⊆ Assure{AssNo} ;

Mais qu’en est-il de la règle selon laquelle un assuré doit détenir au moins un contrat ? En l’occurrence on utilise une contrainte d’égalité, cas particulier de la contrainte d’inclusion :

CONSTRAINT C2  Contrat{AssNo} = Assure{AssNo} ;

Ce qui fait que pour créer un assuré, il faut en même temps créer au moins un contrat ; à cet effet, on utilisera donc l’affectation multiple  décrite dans le paragraphe 4.8.1 (noter la virgule séparant le deux INSERT ci-dessous), ainsi la contrainte sera respectée :

INSERT Assure RELATION {TUPLE {AssNo 1, AssNom "Fernand"}} ,
INSERT Contrat RELATION {TUPLE {CtrNo 1, CtrDate "d01"}} ;

Mutatis mutandis, pour supprimer un assuré, il faut en même temps supprimer ses contrats ; à cet effet, on utilisera à nouveau l’affectation multiple :

DELETE Assure WHERE AssNo = 1 ,
DELETE Contrat WHERE AssNo = 1 ;


Contraintes et SQL

  Avec SQL, la mise en oeuvre de la contrainte d’inclusion est à effectuer au moyen d’une instruction CREATE ASSERTION, mais comme les éditeurs des SGBD du marché (DB2, Oracle, SQL Server, PostgreSQL, etc.) ne proposent pas cette instruction (qui leur flanque la frousse, cf. [Date2015], page 529), le développeur en est donc réduit à galérer avec des triggers, dont - en passant - la syntaxe diffère allègrement d’un SGBD à l’autre...

Un bémol pour SQL Server, lequel permet de créer une fonction, ainsi qu’une contrainte de table faisant référence à cette fonction.

Exemple avec SQL Server :

Chez Dubicobit, les chauffeurs sont soit des occasionnels, soit ceux de la société.


Fig. 5.2 - Société Dubicobit - chauffeurs


Les tables :

 CREATE TABLE Chauffeur(
   ChauffeurId INT,
   ChauffeurNom VARCHAR(50),
   CONSTRAINT Chauffeur_PK PRIMARY KEY(ChauffeurId));

CREATE TABLE ChauffeurOccasionnel(
   ChauffeurId INT,
   CONSTRAINT ChauffeurOccasionnel_PK PRIMARY KEY(ChauffeurId),
   CONSTRAINT ChauffeurOccasionnel_FK FOREIGN KEY(ChauffeurId) REFERENCES Chauffeur(ChauffeurId)
       ON DELETE CASCADE);

CREATE TABLE ChauffeurSociete(
   ChauffeurId INT,
   CONSTRAINT ChauffeurSociete_PK PRIMARY KEY(ChauffeurId),
   CONSTRAINT ChauffeurSociete__FK FOREIGN KEY(ChauffeurId) REFERENCES Chauffeur(ChauffeurId)
   ON DELETE CASCADE);

Pour s’assurer qu’un chauffeur occasionnel ne soit pas en même temps un chauffeur société :

 CREATE FUNCTION Exclusion_ChauffeurSociete_fonction(@ChauffeurId INT)
    RETURNS CHAR(2)
  AS BEGIN
      RETURN CASE WHEN NOT EXISTS
        (SELECT *
         FROM ChauffeurSociete
         WHERE ChauffeurId = @ChauffeurId) 
      THEN 'ok'
      ELSE 'ko'
     END ;
END ;

GO

ALTER TABLE ChauffeurOccasionnel
  ADD CONSTRAINT Exclusion_ChauffeurSociete 
     CHECK (dbo.Exclusion_ChauffeurSociete_fonction(ChauffeurId) = 'ok') ;

Bien entendu, pas de trou dans la raquette ! Il faudra s’assurer de la même façon qu’un chauffeur société ne puisse devenir en même temps chauffeur occasionnel...

Repris chez [Nanci2001] (chapitre 13, figure 13.38), voici le MCD d’un exemple d’inclusion dite de participation
(MCD représenté ici au moyen de Looping, cf. [Bergougnoux2019]) :


Fig. 5.3 - Contrainte d’inclusion (participation)

On ne peut effectuer un prêt [d’un ouvrage] qu’à la condition d’avoir souscrit un abonnement.

NULL étant interdit de séjour, l’association Souscrire fait l’objet d’une variable relationnelle.

Ajoutons pour la forme quelques attributs : abtitre (titre d’un abonnement), abdate (date d’un abonnement), psnom (nom de la personne), prdate (date d’un prêt).

Les variables relationnelles correspondantes sont les suivantes :

VAR Abonnement BASE RELATION {abno INT, abtitre CHAR, abdate CHAR} KEY {abno} ;
VAR Personne BASE RELATION {psno INT, psnom CHAR} KEY {psno} ;
VAR Souscrire BASE RELATION {psno INT, abno INT} KEY {psno} ;
VAR Pret BASE RELATION {prno INT, psno INT, prdate CHAR} KEY {prno} ;

Les contraintes d’intégrité référentielle :

CONSTRAINT C1  Souscrire{psno} ⊆ Personne{psno} AND Souscrire{abno} ⊆ Abonnement{abno} ;
CONSTRAINT C2  Pret{psno} ⊆ Personne{psno} ;

La contrainte d’inclusion :

CONSTRAINT C3  Pret{psno} ⊆ Souscrire{psno} ;

Et tant qu’à faire, une contrainte pour s’assurer qu’un prêt n’est pas antérieur à l’abonnement auquel il fait référence via la souscription de cet abonnement :

CONSTRAINT C4  IS_EMPTY ((Pret JOIN Souscrire JOIN Abonnement) WHERE prdate < abdate) ;

 Concernant la mise en oeuvre des contraintes C3 et C4 avec SQL, voir la remarque faite dans le paragraphe 5.3 (Contrainte d’inclusion).

Repris chez [Nanci2001] (chapitre 7, figure 7.30), voici le MCD d’un exemple de contrainte de simultanéité
(MCD représenté ici au moyen de Looping, cf. [Bergougnoux2019]) :


Fig. 5.4 - Contrainte de simultanéité

Simultanéité : si une commande porte sur un article, cette commande doit être passée par un client, et si un client passe une commande, celle-ci doit aussi porter sur un article.

NULL étant interdit de séjour, l’association Passer fait l’objet d’une variable relationnelle.

Les variables relationnelles sont les suivantes :

VAR Client BASE RELATION {clino INT, clinom CHAR} KEY {clino} ;
VAR Article BASE RELATION {artno INT, artnom CHAR} KEY {artno} ;
VAR Commande BASE RELATION {cdeno INT, cdedate CHAR} KEY {cdeno} ;
VAR Passer BASE RELATION {cdeno INT, clino INT} KEY {cdeno} ;
VAR Porter BASE RELATION {cdeno INT, artno INT} KEY {cdeno, artno} ;

Les contraintes d’intégrité référentielle :

CONSTRAINT C1  Passer{cdeno} ⊆ Commande{cdeno} AND Passer{clino} ⊆ Client{clino} ;
CONSTRAINT C2  Porter{cdeno} ⊆ Commande{cdeno} AND Porter{artno} ⊆ Article{artno} ;

La contrainte de simultanéité (c’est-à-dire d’égalité, cas particulier de la contrainte d’inclusion) :

CONSTRAINT C3  Passer{cdeno} = Porter{cdeno} ;

 Concernant la mise en oeuvre de la contrainte de simultanéité avec SQL, voir la remarque faite dans le paragraphe 5.3 (Contrainte d’inclusion).

Repris chez [Nanci2001] (chapitre 7, figure 7.31), voici le MCD d’un exemple de contrainte de totalité de participation
(MCD représenté ici au moyen de Looping, cf. [Bergougnoux2019]) :


 Fig. 5.5 - Contrainte de totalité

Totalité : tout véhicule doit être couvert par un contrat d’assurance et/ou impliqué dans un sinistre (en passant, si un véhicule n’est pas couvert mais est impliqué dans un sinistre, le propriétaire du véhicule risquera (sic) de passer de mauvaises nuits...)

NULL étant interdit de séjour, l’association Couvrir fait l’objet d’une variable relationnelle.

Les variables relationnelles sont les suivantes :

VAR Vehicule BASE RELATION {vehno INT, vehmarque CHAR} KEY {vehno} ;
VAR Contrat BASE RELATION {ctrno INT, ctrass CHAR} KEY {ctrno} ;
VAR Sinistre BASE RELATION {sinno INT, sindate CHAR} KEY {sinno} ;
VAR Couvrir BASE RELATION {vehno INT, ctrno INT} KEY {vehno} ;
VAR Impliquer BASE RELATION {vehno INT, sinno INT} KEY {vehno, sinno} ;

Les contraintes d’intégrité référentielle :

CONSTRAINT C1  Couvrir{vehno} ⊆ Vehicule{vehno} AND Couvrir{ctrno} ⊆ Contrat{ctrno} ;
CONSTRAINT C2  Impliquer{vehno} ⊆ Vehicule {vehno} AND Impliquer{sinno} ⊆ Sinistre{sinno} ;

La contrainte de totalité :

CONSTRAINT C3  Vehicule{vehno} = UNION {Couvrir{vehno}, Impliquer{vehno}} ;

 Concernant la mise en oeuvre de la contrainte de totalité avec SQL, voir la remarque faite dans le paragraphe 5.3 (Contrainte d’inclusion).

Repris chez [Nanci2001] (chapitre 13, figure 13.34), voici le MCD d’un exemple de contrainte d’exclusion sur participation
(MCD représenté ici au moyen de Looping, cf. [Bergougnoux2019]) :


Fig. 5.6 -   contrainte d’exclusion

Exclusion : un article ne peut pas être à la fois acheté à un fournisseur et approvisionné auprès d’une unité d’approvisionnement, c’est soit l’un soit l’autre.

Les variables relationnelles sont les suivantes :

VAR Article BASE RELATION {artno INT, artnom CHAR} KEY {artno} ;
VAR Fournisseur BASE RELATION {fourno INT, fournom CHAR} KEY {fourno} ;
VAR UniteProduction BASE RELATION {unino INT, uninom CHAR} KEY {unino} ;
VAR Acheter BASE RELATION {artno INT, fourno CHAR} KEY {artno, fourno} ;
VAR Approvisionner BASE RELATION {artno INT, unino INT} KEY {artno, unino} ;

Les contraintes d’intégrité référentielle :

CONSTRAINT C1  Approvisionner{artno} ⊆ Article{artno} AND Approvisionner{unino} ⊆ UniteProduction{unino} ;
CONSTRAINT C2  Acheter{artno} ⊆ Article{artno} AND Acheter{fourno} ⊆ Fournisseur{fourno} ;

La contrainte d’exclusion :

CONSTRAINT C3 IS_EMPTY (Acheter{artno} INTERSECT Approvisionner{artno}) ;

 Concernant la mise en oeuvre de la contrainte d’exclusion avec SQL, voir la remarque faite dans le paragraphe 5.3 (Contrainte d’inclusion).

Je fais ici référence à un article publié en janvier 2014, traitant de la généralisation/spécialisation (cf. [FSM2014]).

Le MCD ci-dessous, présente un aperçu du référentiel PERSONNES de la société Dubicobit, selon lequel :

— Une personne peut avoir plusieurs adresses

— Une personne peut être un collaborateur Dubicobit ou un tiers

— Un collaborateur peut être un directeur ou un employé

— Un tiers peut être un client ou un fournisseur de Dubicobit.



Fig. 5.7 -   Contrainte de partitionnement


Partitionnement : exclusion et totalité (XT). Une personne est soit un collaborateur, soit un tiers ; un collaborateur est soit un employé, soit un directeur ; un tiers est soit un client, soit un fournisseur.

Dans ce qui suit, certains noms d’entités-types et d’attributs ont été abrégés.

Les variables relationnelles correspondantes sont les suivantes :

VAR PSN BASE RELATION {psnid INT}
    KEY {psnid} ;

VAR ADR BASE RELATION {psnid INT, adrid INT, adrlign2 CHAR, adrlign3 CHAR, adrlign4 CHAR, cp CHAR}
    KEY {psnid, adrid} ;

VAR COLLAB BASE RELATION {psnid INT, matricule CHAR, nir CHAR, nom CHAR, prenom CHAR, datemb CHAR, salaire INT}
    KEY {psnid} KEY {matricule} KEY {nir} ;

VAR EMP BASE RELATION {psnid INT, profil CHAR}
KEY {psnid} ;

VAR DIR BASE RELATION {psnid INT, prime INT}
KEY {psnid} ;

VAR TIERS BASE RELATION {psnid INT, siret CHAR, raisoc CHAR}
KEY {psnid} KEY {siret} ;

VAR CLI BASE RELATION {psnid INT, condtar CHAR}
KEY {psnid} ;

VAR FOUR BASE RELATION {psnid INT, tauxrem INT}
KEY {psnid} ;

VAR ART BASE RELATION {artno INT, artnom CHAR}
KEY {artno} ;

VAR ARTFOU BASE RELATION {artno INT, psnid INT, qte INT}
KEY {artno, psnid} ;

Les contraintes d’intégrité référentielle :

CONSTRAINT adr_fk ADR{psnid} ⊆ PSN{psnid} ;

CONSTRAINT collab_fk COLLAB{psnid} ⊆ PSN{psnid} ;

CONSTRAINT emp_fk EMP{psnid} ⊆ COLLAB{psnid} ;

CONSTRAINT dir_fk DIR{psnid} ⊆ COLLAB{psnid} ;

CONSTRAINT tiers_fk TIERS{psnid} ⊆ PSN{psnid} ;

CONSTRAINT cli_fk CLI{psnid} ⊆ TIERS{psnid} ;

CONSTRAINT four_fk FOUR{psnid} ⊆ TIERS{psnid} ;

CONSTRAINT artfou_fk ARTFOU{psnid} ⊆ FOUR{psnid} AND ARTFOU{artno} ⊆ ART{artno} ;

Les contraintes de partitionnement :

CONSTRAINT colltiers_part (PSN{psnid} = UNION {COLLAB{psnid}, TIERS{psnid}}
AND PSN{psnid} = XUNION {COLLAB{psnid}, TIERS{psnid}}) ;

CONSTRAINT empdir_part (COLLAB{psnid} = UNION {EMP{psnid}, DIR{psnid}}
AND COLLAB{psnid} = XUNION {EMP{psnid}, DIR{psnid}}) ;

CONSTRAINT clifour_part (TIERS{psnid} = UNION {CLI{psnid}, FOUR{psnid}}
AND TIERS{psnid} = XUNION {CLI{psnid}, FOUR{psnid}}) ;


Rappel. Comme précisé au paragraphe 3.4 traitant de l’opérateur UNION, celui-ci étant commutatif et associatif, l’expression relationnelle ci-dessous peut être utilisée si plusieurs relations de même type font l’objet d’une union :

UNION {R1, R2, ... Rn}

Même principe pour l’union exclusive :

XUNION {R1, R2, ... Rn}


Concernant la mise en oeuvre des contraintes de partitionnement avec SQL, voir la remarque faite dans le paragraphe 5.3 (Contrainte d’inclusion).

Le professeur Bergougnoux propose l’exemple très intéressant d’un modèle conceptuel des données, celui de l’emprunt par des étudiants d’ouvrages fournis par leur bibliothèque (cf.[Bergougnoux2024]).

Les variables relationnelles correspondantes sont en l’occurrence les suivantes :

VAR Etudiant BASE RELATION {NumEtu INT, Nom CHAR, Prenom CHAR} KEY {NumEtu} ;
VAR Ouvrage BASE RELATION {CodeOuv INT, Titre CHAR, Auteur CHAR, NbExemplaires INT} KEY {CodeOuv} ;
VAR Emprunt BASE RELATION {NumEtu INT, CodeOuv INT} KEY {NumEtu, CodeOuv} ;

Les contraintes :

―  Un étudiant ne peut pas avoir emprunté plus de 10 ouvrages à la fois

―  Un ouvrage donné possède un nombre d'exemplaires limité.


Afin de savoir pour chaque étudiant le nombre d’exemplaires empruntés par ouvrage, on peut utiliser l’expression :

EXTEND Etudiant {NumEtu} : {NbEmprunts := COUNT (IMAGE_IN(Emprunt))}

Et la première contrainte s’exprime ainsi :

CONSTRAINT MAX_EMPRUNTS_PAR_ETUDIANT
IS_EMPTY ((EXTEND Etudiant {NumEtu} :
 {NbEmprunts := COUNT (IMAGE_IN(Emprunt))}) WHERE NbEmprunts > 10) ;

Rappel : l’opérateur EXTEND est en lecture seule, la variable relationnelle Etudiant n’est pas affectée !

Empêcher que soit emprunté tout ouvrage dont tous les exemplaires sont actuellement en cours de prêt :

CONSTRAINT MAX_EMPRUNTS_PAR_OUVRAGE
IS_EMPTY (EXTEND Ouvrage {CodeOuv, NbExemplaires} :
 {NbEmprunts := COUNT (IMAGE_IN(Emprunt))} WHERE NbEmprunts > NbExemplaires) ;

Là encore, l’opérateur EXTEND étant en lecture seule, la variable relationnelle Ouvrage n’est pas affectée.

Revenons sur la base de données initiale (cf. figure 2.1) et reprenons l’expression suivante qui effectue une extension de la variable relationnelle S, avec un attribut PnoQty de type RELATION {Pno ,Qty} (cf. le paragraphe 4.4 Extension (Extend) :

  EXTEND S : {PnoQty := IMAGE_IN(SP)}



 Fig. 6.1 - Une valeur de S étendue d’un attribut RVA


Considérons maintenant le résultat de la requête SQL suivante (SGBD : SQL Server) :

SELECT S.Sno, Sname, Status, City, Pno, Qty
FROM S LEFT OUTER JOIN SP ON S.Sno = SP.Sno ;


Fig. 6.2 - S LEFT OUTER JOIN SP


- Dans la figure 6.1, l’absence de livraison est représentée par l’ensemble vide.

- Dans la figure 6.2, l’absence de livraison est représentée par NULL.


 NULL est censé représenter une valeur inconnue, ce qui dans l’exemple est manifestement un contre-sens sémantique... Il sera prudent d’utiliser la fonction COALESCE pour remplacer NULL par exemple par "néant", ou "sans objet", etc. :

SELECT S.Sno, Sname, Status, City
, COALESCE(CAST(Pno AS VARCHAR(5)), 'sans objet') AS Pno
, COALESCE(CAST(Qty AS VARCHAR(5)), 'sans objet') AS Qty
FROM S LEFT OUTER JOIN SP ON S.Sno = SP.Sno ;

Reprenons ce qui est écrit dans le paragraphe 2.5 :

Tutorial D permet de définir des types « utilisateur » (classes des systèmes orientés objets), à l’aide d’un opérateur spécifique, TYPE.

Exemple :

TYPE POINT ... POSSREP {X RATIONAL, Y RATIONAL
CONSTRAINT (X2 + Y2) ≤ 10000)} ;

A charge de l’utilisateur de fournir les opérateurs sans lesquels le type serait sans emploi...

Exemple :

OPERATOR REFLECT (POINT) RETURNS POINT ;
RETURN POINT ( - THE_X(P), - THE_Y(P)) ;
END OPERATOR ;

(Etant donné un point P de coordonnées cartésiennes (x,y), l’opérateur REFLECT produit le point « inverse » de coordonnées (-x,-y). Pour plus d’information, voir [Date2015], page 55).


Comme je l’ai écrit dans le paragraphe 2.5, Tutorial D ne proposant pas le type DATE, sa définition reste à la charge de l’utilisateur. Il s’agit là d’un exercice un peu fastidieux, rebattu, mais pas bien compliqué.

Pour ma part, j’ai programmé l’équivalent dans les années soixante-dix, en assembleur IBM/370, sous la forme d’un « sous-programme » appelable par CALL, mais je n’ai pas l’intention de récidiver ! Tout au plus je proposerai une esquisse en Tutorial D.

Création du type DATE :

1.   TYPE DATE
2.  {madate CHARACTER
3.  CONSTRAINT DATE_VALIDE(madate)} ;

Ligne 1 : on nomme le type.

Ligne 2 : le type est représenté par un composant nommé madate, de type CHARACTER.

Ligne 3 : le composant madate voit satisfaire à une contrainte nommée DATE_VALIDE.

Avant de créer le type DATE, il aura fallu créer la contrainte DATE_VALIDE à laquelle fait référence le type DATE.

Bien entendu, cette contrainte a pour objet de s’assurer que la structure de la date madate est valide. A cet effet, je propose le code ci-dessous, créé avec Rel (cf. [Voorhis2004]). On y trouve certains opérateurs non prévus par Tutorial D, mais fournis par Rel et bien connus des développeurs dans d’autres langages, tels que LENGTH, TRIM, SUBSTRING, IS_DIGITS, CAST_AS_INTEGER, CAST_AS_RATIONAL, REMAINDER, Writeln.

D’autres opérateurs seraient évidemment à prévoir, permettant par exemple le calcul du nombre de jours écoulés entre deux dates, l’ajout ou le retrait d’un un nombre de jours une date, etc.

Je me limiterai ici à des contraintes impliquant des dates du genre « la date de fin ne peut pas être antérieure à la date de début ».


L’opérateur permettant de valider la structure d’une date :

OPERATOR DATE_VALIDE (dateCarIn CHAR) RETURNS BOOLEAN ;

VAR dateCar CHARACTER ;
dateCar := dateCarIn ;

VAR anneeCar CHARACTER ; VAR moisCar CHARACTER ; VAR jourCar CHARACTER ;
VAR tiret1Car CHARACTER ; VAR tiret2Car CHARACTER ;

VAR ok BOOLEAN ; 

ok := TRUE ;
IF NOT (LENGTH(TRIM(dateCar)) = 10) 
  THEN 
    ok := FALSE ;
    Writeln "Erreur. Date = " || dateCar || " ; Format date attendu : aaaa-mm-jj" ;
END IF ;

IF ok 
  THEN 
     dateCar := TRIM(dateCar) ;
     anneeCar := SUBSTRING (dateCar, 0, 4) ;
     tiret1Car := SUBSTRING (dateCar, 4, 5) ;
     moisCar := SUBSTRING (dateCar, 5, 7) ;
    tiret2Car := SUBSTRING (dateCar, 7, 8) ;
    jourCar := SUBSTRING (dateCar, 8, 10) ;
END IF ;

IF ok AND NOT (IS_DIGITS (anneeCar) AND tiret1Car = "-" AND IS_DIGITS(moisCar) AND  tiret2Car = "-" AND IS_DIGITS(jourCar))
  THEN 
    ok := FALSE ;
    Writeln "erreur de structure de la date" ;
END IF ;

VAR annee INT ;
VAR mois INT ;
VAR jour INT ;
annee := CAST_AS_INTEGER(anneeCar) ;
mois := CAST_AS_INTEGER(moisCar) ;
jour := CAST_AS_INTEGER(jourCar) ;

IF ok AND NOT (annee ≥ 1600 AND annee < 2200) 
  THEN 
    ok := false ;
    Writeln "l'année doit être  ≥ 1600 et < 2200" ;
END IF ;
    
IF ok AND NOT (mois > 0 AND mois < 13) 
  THEN 
    ok := FALSE ;
    Writeln "le mois doit être compris entre 01 et 12" ;
END IF ;

IF ok AND NOT (jour > 0 AND jour < 32) 
  THEN 
    ok := FALSE ;
    Writeln "le jour n'est pas valide" ;
END IF ;

VAR anneeRat RATIONAL ;
VAR reste400 RATIONAL ; VAR reste100 RATIONAL ; VAR reste4 RATIONAL ;
anneeRat := CAST_AS_RATIONAL(annee) ;

IF ok
    THEN 
        IF mois = 2    /* février : contrôle de la bissextilité */
            THEN
                reste400 := REMAINDER (anneeRat, 400.0) ;
                reste100 := REMAINDER (anneeRat, 100.0) ;
                reste4 := REMAINDER (anneeRat, 4.0) ;

                IF reste400 ≠ 0.0 AND reste100 ≠ 0.0 AND reste4 ≠ 0.0
                    THEN 
                        IF NOT (jour < 29)
                            THEN 
                                  ok := FALSE ; 
                                  Writeln annee || " : année non bissextile, le jour doit être < 29" ;
                        END IF ;
                END IF ;

                IF reste400 = 0.0
                    THEN 
                        IF NOT (jour < 30)
                            THEN 
                                ok := FALSE ; 
                                Writeln annee || " : année bissextile, le jour doit être < 30" ;
                        END IF ;
                ELSE
                    IF reste100 = 0.0
                        THEN 
                            IF NOT (jour < 29)
                                THEN 
                                    ok := FALSE ; 
                                    Writeln annee || " : année non bissextile, le jour doit être < 29" ;
                            END IF ;
                    ELSE
                        IF NOT (jour < 30)
                            THEN 
                                ok := FALSE ; 
                                Writeln annee || " : année bissextile, le jour doit être < 30" ;
                        END IF ;
                    END IF ;
                END IF ;
        END IF ;    /* fin contrôle février */

        IF mois = 4 or mois = 6 or mois = 9 or mois = 11 
            THEN 
                IF NOT (jour < 31)
                    THEN 
                        ok := FALSE ; 
                        Writeln "Le jour doit être < 31" ;
                END IF ;
        END IF ;
END IF ;

RETURN (ok) ;

END OPERATOR ;

Pour tester :

VAR d BASE RELATION {eno INTEGER, datedebut DATE, datefin DATE} KEY {eno} ;

CONSTRAINT CXD IS_EMPTY (d where datedebut > datefin) ;

INSERT d RELATION
{  TUPLE {eno 1, datedebut DATE("1985-09-30"), datefin DATE("1988-02-29")}
 ,  TUPLE {eno 2, datedebut DATE("1991-02-28"), datefin DATE("1984-02-28")} } ;

Le 1er n-uplet est valide, mais le deuxième viole la contrainte CXD. Rel confirme :

ERROR: RS0215: Constraint CXD returns false

Que les choses soient claires, le Modèle Relationnel de Données n’est évidemment pas partie prenante dans la performance des applications, celle-ci est du ressort du SGBD. Bien entendu, le développeur n’est pas étranger à la performance de ses requêtes et pour en prendre la mesure, lui (ou son DBA), doit savoir utiliser la commande EXPLAIN, disponible avec tous les SGBD SQL.

Nous sommes en 1993, à la SNCF, et un collègue, Albert, se propose d’exécuter une requête SQL dont la structure est la suivante  :

  SELECT ...
  FROM ...
  WHERE ...
  UNION ALL
  SELECT ...
  FROM ...
  WHERE ...
  ;

Le SGBD utilisé est une machine Base de données Teradata. Testés isolément, les deux SELECT durent respectivement 5 minutes et 2 minutes. Puisqu’il s’agit d’effectuer un UNION ALL, la requête elle-même devrait durer entre 7 et 8 minutes. Léger problème : Teradata ne connaît pas l’opérateur UNION (nous sommes pourtant en 1993) ! 
Albert se propose de modifier sa requête, en y remplaçant UNION par OR. Mais, si vous avez essayé ce genre d’astuce, vous savez qu’au bout du compte on a droit à un produit cartésien...

En tout cas, ignorant l’existence d’EXPLAIN et ayant peur de faire une bêtise, bien inspiré, Albert a préféré me demander mon avis, en conséquence de quoi, au moyen de cette commande, j’ai demandé à la machine Base de données ce qu’elle en pensait, et Albert n’a pas été déçu ! La requête soumise à EXPLAIN (enrichie d’un SELECT COUNT (*) mais l’important est bien de repérer le OR) :

SELECT COUNT (*)
FROM
      B4AEBOJ.TAEIG05 A                  
     ,B4AEBOJ.TAETG17 B                  
     ,B4AEBOJ.TAESG04 C                  
WHERE
      B.I01_C_NUMER_ID = A.I01_C_NUMER_ID
  AND A.I05_B_ANNUL IS NOT NULL
  AND A.I05_B_ANNUL = '0'
  AND B.I01_C_NUMER_ID NOT IN
    ( SELECT
            D.I01_C_NUMER_ID
      FROM
            B4AEBOJ.TAETG17 D               
           ,BOAE9Y.TAEIG12 E               
      WHERE
            D.I01_C_NUMER_ID = E.I01_C_NUMER_ID )
AND B.I01_C_NUMER_ID NOT IN
    ( SELECT
            G.I01_C_NUMER_ID
      FROM
            B4AEBOJ.TAETG17 G               
           ,BFRU007.TAES1Y121 H             
      WHERE
            G.I01_C_NUMER_ID = H.I01_C_NUMER_ID )
 AND ( (
           B.I01_C_NUMER_ID = C.I01_C_NUMER_ID
       AND A.I01_C_NUMER_ID = C.I01_C_NUMER_ID
       AND B.R10_C_CODE_R10 IS NOT NULL   
       AND B.T59_C_CODE_T59 IS NOT NULL   
       AND ( B.T59_C_CODE_T59 = '1292'
           OR B.T59_C_CODE_T59 = '1293' )
)
OR     /* le OR est ici */
(
         B.I01_C_NUMER_ID NOT IN
         ( SELECT
                 F.I01_C_NUMER_ID
           FROM
                 B4AEBOJ.TAETG17 F
                ,B4AEBOJ.TAETG20 K
           WHERE
                 F.I01_C_NUMER_ID = K.I01_C_NUMER_ID )

    AND   B.I01_C_NUMER_ID NOT IN
         ( SELECT
                 L.I01_C_NUMER_ID
           FROM
                 B4AEBOJ.TAETG17 L          /* TITRE-EMIS */
                ,BFRU007.TAES1Y131 I        /* IT-NOEUD-PRESTATION */
           WHERE
                 L.I01_C_NUMER_ID = I.I01_C_NUMER_ID )

) ) ;

La réponse de la machine Base de données (de test), composée modestement ici d’une dizaine d’AMPs (Access Module Processors), processeurs composant la machine (plus d’une centaine pour la machine de production de l'appication concernée, ARISTOTE) :

Explanation

1) First, we lock BFRU007.I for read, we lock BFRU007.H for read, we lock BOAE9Y.E for read, we lock B4AEBOJ.K for read, we lock B4AEBOJ.D for read, we lock B4AEBOJ.C for read, and we lock B4AEBOJ.A for read.

2) Next, we execute the following steps in parallel.

• 21) We do an all-AMPs JOIN step from BOAE9Y.E by way of a RowHash match scan with no residual conditions, which is joined to B4AEBOJ.D. BOAE9Y.E and B4AEBOJ.D are joined using a merge join, with a join condition of ("B4AEBOJ.D.I01_C_NUMER_ID = BOAE9Y.E.I01_C_NUMER_ID").
  
  The result goes into Spool 3, which is built locally on the AMPs. Then we do a SORT to order Spool 3 by row hash and the sort key in spool field1 eliminating duplicate rows. The size of Spool 3 is estimated to be 260 rows. The estimated time for this step is 0.26 seconds.
  
  22) We do an all-AMPs RETRIEVE step from BFRU007.H by way of an all-rows scan with no residual conditions into Spool 5, which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 5 by row hash. The size of Spool 5 is estimated to be 9,500 rows. The estimated time for this step is 17.85 seconds.

3) We execute the following steps in parallel.

• 31) We do an all-AMPs JOIN step from Spool 5 (Last Use) by way of a RowHash match scan, which is joined to B4AEBOJ.G. Spool 5 and B4AEBOJ.G are joined using a merge join, with a join condition of ("B4AEBOJ.G.I01_C_NUMER_ID = Spool_5.I01_C_NUMER_ID"). The result goes into Spool 4, which is built locally on the AMPs. Then we do a SORT to order Spool 4 by row hash and the sort key in spool field1 eliminating duplicate rows. The size of Spool 4 is estimated to be 123,498 rows. The estimated time for this step is 2 minutes and 21 seconds.
  
  32) We do an all-AMPs JOIN step from B4AEBOJ.K by way of a RowHash match scan with no residual conditions, which is joined to B4AEBOJ.F. B4AEBOJ.K and B4AEBOJ.F are joined using a merge join, with a join condition of ("B4AEBOJ.F.I01_C_NUMER_ID = B4AEBOJ.K.I01_C_NUMER_ID"). The result goes into Spool 6, which is built locally on the AMPs. Then we do a SORT to order Spool 6 by row hash and the sort key in spool field1 eliminating duplicate rows. The size of Spool 6 is estimated to be 6,002,529 rows. The estimated time for this step is 1 hour and 58 minutes.
  
  33) We do an all-AMPs RETRIEVE step from BFRU007.I by way of an all-rows scan with no residual conditions into Spool 8, which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 8 by row hash. The size of Spool 8 is estimated to be 20 rows. The estimated time for this step is 0.07 seconds.

4) We execute the following steps in parallel.

• 41) We do an all-AMPs JOIN step from Spool 8 (Last Use) by way of a RowHash match scan, which is joined to B4AEBOJ.L. Spool 8 and B4AEBOJ.L are joined using a merge join, with a join condition of ("B4AEBOJ.L.I01_C_NUMER_ID = Spool_8.I01_C_NUMER_ID"). The result goes into Spool 7, which is built locally on the AMPs. Then we do a SORT to order Spool 7 by row hash and the sort key in spool field1 eliminating duplicate rows. The size of Spool 7 is estimated to be 260 rows. The estimated time for this step is 0.27 seconds.
  
  42) We do an all-AMPs JOIN step from B4AEBOJ.B by way of an all-rows scan with no residual conditions, which is joined to Spool 3 (Last Use). B4AEBOJ.B and Spool 3 are joined using an exclusion merge join, with a join condition of ( "B4AEBOJ.B.I01_C_NUMER_ID = Spool_3.I01_C_NUMER_ID"). The result goes into Spool 9, which is built locally on the AMPs. Then we do a SORT to order Spool 9 by row hash. The size of Spool 9 is estimated to be 461,740 rows. The estimated time for this step is 5 minutes and 32 seconds.

5) We execute the following steps in parallel.

• 51) We do an all-AMPs JOIN step from Spool 9 (Last Use) by way of an all-rows scan, which is joined to Spool 4 (Last Use). Spool 9 and Spool 4 are joined using an exclusion merge join, with a join condition of ("Spool_9.I01_C_NUMER_ID = Spool_4.I01_C_NUMER_ID"). The result goes into Spool 10, which is built locally on the AMPs. Then we do a SORT to order Spool 10 by row hash. The size of Spool 10 is estimated to be 461,740 rows. The estimated time for this step is 5 minutes and 35 seconds.
  
  52) We do an all-AMPs RETRIEVE step from B4AEBOJ.A by way of an all-rows scan with a condition of ("(B4AEBOJ.A.I05_B_ANNUL = '0') and (NOT (B4AEBOJ.A.I05_B_ANNUL is null))") into Spool 11, which is built locally on the AMPs. Then we do a SORT to order Spool 11 by row hash. The size of Spool 11 is estimated to be 30,710 rows. The estimated time for this step is 1 minute and 55 seconds.

6) We execute the following steps in parallel.

• 61) We do an all-AMPs JOIN step from Spool 10 (Last Use) by way of a RowHash match scan, which is joined to Spool 11 (Last Use). Spool 10 and Spool 11 are joined using a merge join, with a join condition of ("Spool_10.I01_C_NUMER_ID = Spool_11.I01_C_NUMER_ID"). The result goes into Spool 12, which is built locally on the AMPs. The size of Spool 12 is estimated to be 399,224 rows. The estimated time for this step is 6 minutes and 31 seconds.
  
  62) We do an all-AMPs RETRIEVE step from B4AEBOJ.C by way of an all-rows scan with no residual conditions into Spool 13, which is duplicated on all AMPs. The size of Spool 13 is estimated to be 11,598,400 rows. The estimated time for this step is 1 hour and 49 minutes.

7) We do an all-AMPs JOIN step from Spool 12 (Last Use) by way of an all-rows scan, which is joined to Spool 13 (Last Use). Spool 12 and Spool 13 are joined using a product join. The result goes into Spool 14, which is built locally on the AMPs. The size of Spool 14 is estimated to be 231 517 982 080 rows. The estimated time for this step is 17 479 hours and 42 minutes.

8) We do an all-AMPs RETRIEVE step from Spool 14 by way of an all-rows scan with a condition of ("(Spool_14.I01_C_NUMER_ID = Spool_14.I01_C_NUMER_ID) AND ((Spool_14.I01_C_NUMER_ID = Spool_14.I01_C_NUMER_ID) AND (((Spool_14.T59_C_CODE_T59 = 1292) OR (Spool_14.T59_C_CODE_T59 = 1293 )) AND ((NOT (Spool_14.T59_C_CODE_T59 IS NULL )) AND (NOT (Spool_14") into Spool 15, which is redistributed by hash code to all AMPs. The size of Spool 15 is estimated to be 231 517 982 080 rows. The estimated time for this step is 6 794 hours and 5 minutes.

9) We do an all-AMPs RETRIEVE step from Spool 14 (Last Use) by way of an all-rows scan with a condition of ("(Spool_14.I01_C_NUMER_ID <> Spool_14.I01_C_NUMER_ID) or ((Spool_14.I01_C_NUMER_ID <> Spool_14.I01_C_NUMER_ID) or (((Spool_14.T59_C_CODE_T59 <> 1292) AND (Spool_14.T59_C_CODE_T59 <> 1293 )) OR ((Spool_14.T59_C_CODE_T59 IS NULL) OR (Spool_14.R10_C_COD") into Spool 16, which is built locally on the AMPs. Then we do a SORT to order Spool 16 by row hash. The size of Spool 16 is estimated to be 231 517 982 080 rows. The estimated time is 3 243 hours and 15 minutes.

10) We do an all-AMPs JOIN step from Spool 16 (Last Use) by way of an all-rows scan, which is joined to Spool 7 (Last Use). Spool 16 and Spool 7 are joined using an exclusion merge join, with a join condition of ("Spool_16.I01_C_NUMER_ID = Spool_7.I01_C_NUMER_ID"). The result goes into Spool 17, which is built locally on the AMPs. Then we do a SORT to order Spool 17 by row hash. The size of Spool 17 is estimated to be 231 517 982 080 rows. The estimated time for this step is 3 243 hours and 15 minutes.

11) We do an all-AMPs JOIN step from Spool 17 (Last Use) by way of an all-rows scan, which is joined to Spool 6 (Last Use). Spool 17 and Spool 6 are joined using an exclusion merge join, with a join condition of ("Spool_17.I01_C_NUMER_ID = Spool_6.I01_C_NUMER_ID"). The result goes into Spool 15, which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 15 by the sort key in spool field1 eliminating duplicate rows. The size of Spool 15 is estimated to be 231 517 982 080 rows. The estimated time for this step is 5 092 hours and 46 minutes.

12) We do an all-AMPs RETRIEVE step from Spool 15 (Last Use) by way of an all-rows scan into Spool 2, which is built locally on the AMPs. The size of Spool 2 is estimated to be 173 638 486 560 rows. The estimated time for this step is 1 491 hours and 31 minutes.

13) We do a SUM step to aggregate from Spool 2 (Last Use) by way of an all-rows scan. Aggregate Intermediate Results are computed globally, then placed in Spool 18.

14) We do an all-AMPs RETRIEVE step from Spool 18 (Last Use) by way of an all-rows scan into Spool 1, which is built locally on the AMPs. The size of Spool 1 is estimated to be 416 700 rows. The estimated time for this step is 5 minutes and 2 seconds.

15) Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.

-> The contents of Spool 1 are sent back to the user as the result of statement 1.

-----------------------------------------------------------------

Conclusion du DBA : Il faudrait attendre au moins deux ans et demi pour obtenir le résultat...

L’alternative : on va exporter vers MVS (le système d’exploitation) le résultat de chacun des deux SELECT à unir (opération de quelques minutes), on concaténera les fichiers correspondants (concaténation des deux cartes DD), coût = 0 seconde, et on importera cela dans la machine Base de données, pour encore un coût de quelques minutes.

Quant à attendre deux ans et demi, au vu du step 7 de l’EXPLAIN, la machine annonçant un produit cartésien pondant 231 517 982 080 lignes, l’espace spool14 affecté aura explosé bien avant...

D’aucuns prétendraient que la mauvaise performance est, d’une façon générale, serait autant imputable à Tutorial D qu’au système, mais Tutorial D sait quand même ce qu’est un produit cartésien, son rôle n’étant que de le présenter et éventuellement alerter...