Algèbre relationnelle

Algèbre relationnelle Dominique Laurent Université de Tours laurent@univ-tours.fr

Eléments de base • Univers : ensemble fini d ’attributs, U • Attribut : associé à un ensemble de valeurs appelé domaine, dom(A) • Schéma relationnel : sous-ensemble non vide de l ’univers • n-uplet sur le schéma R : application de R dans l ’union des domaines des attributs de R

Eléments de base (exemple) • U = {num_et, nom_et, adr_et, num_p, nom_p, adr_p, num_c, nom_c} • R= {num_et, num_c} • t : R  dom(num_et)  dom(num_c) num_et  n1 num_c  n2 • Notation : t = (n1, n2)

Eléments de base (suite) • Relation sur R : ensemble fini de n-uplets définis sur R • Base de données sur U : ensemble de relations définies sur des schémas de U • Remarque : plusieurs relations d ’une même base peuvent avoir le même schéma

Exemple • BD = {etud, prof, cours, inscrit} • Avec : • etud[num_et, nom_et, adr_et] • prof[num_p, nom_p, adr_p] • cours[num_c, nom_c, num_p] • inscrit[num_et, num_c] • Remarque : on peut ajouter • anc_etud[num_et, nom_et, adr_et]

Algèbre relationnelle • Combine les relations pour exprimer les requêtes • Formalisme rigoureux dont le pouvoir d ’expression est « fort » : ~ logique du 1er ordre • Propriétés formelles pour optimiser le calcul des réponses

Opérations de base • Opérations ensemblistes : • union, intersection, différence • Opérations relationnelles : • projection • sélection • jointure • renommage

Projection • r[R] et X sous-ensemble de R • Projection de r sur X : • relation définie sur X contenant les restrictions sur X des n-uplets de r • notation : x(r) • x(r) = {u |  t dans r tel que u=t.R} • Remarque : attention à l ’écriture • x(r) = {t.R | t dans r}

Projection - Exemple • etud[num_et, nom_et, adr_et] • X = nom_et, adr_et • x(etud) • liste des noms et adresses des étudiants • Attention : si Y = nom_et, adr_p • y(etud) n ’est pas défini

Sélection • r[R] relation définie sur R • C condition de sélection de la forme • C = (A comp a), ou C = (A comp B) • C combinaison par les connecteurs logiques • Sélection de r selon C • relation définie sur R • notation : C(r) • C(r) est l ’ensemble des n-uplets de r qui satisfont C

Sélection - Exemple • etud[num_et, nom_et, adr_et] • C = (adr_et = Casa ou num_et > 100) • C(etud) • liste des étudiants dont l ’adresse est Casa ou dont le numéro est supérieur à 100 • nom_et(C(etud)) • liste des noms de ces étudiants • Attention : si C = (nom_p = nom_et) • C(etud)n ’est pas défini

Jointure • r[R] et s[S] deux relations • Jointure de r par s • relation définie sur R  S • notation : r s • r s = {t | t.R  r et t.S  s} • Remarques • une jointure est toujours définie • opération coûteuse à calculer

Jointure - Exemple • etud[num_et, nom_et, adr_et] inscrit[num_et, num_c] • etud inscrit • définie sur num_et, nom_et, adr_et, num_c • liste des inscriptions des étudiants • nom_et,num_c(etud inscrit) • liste des noms des étudiants et des numéros des cours auxquels ils sont inscrits

Jointure - Exemple (suite) • etud prof • définie? Sur quel schéma? • Signification intuitive? • etud etud • définie? Sur quel schéma? • Signification intuitive?

Jointure - Propriétés • R(r s) = r ? • R(r s) S(r s) = r s ? • r[RS], R(r) S(r) = r ? • C(r s) = C(r) s ?

Renommage • r[R] , A attribut de R , A1 nouveau nom pour A • Renommage de A en A1 dans r • relation définie sur (R - {A})  {A1} • notation : A1A(r) • A1A(r) contient les mêmes n-uplets que r • Utilisation : avoir deux versions distinctes d ’une même relation

Renommage - Exemple • Numéros des étudiants inscrits à plus d ’un cours ? • inscrit[num_et, num_c] (1ère version) • nnum_c(inscrit) (2ème version) • nnum_c(inscrit) inscrit (jointure) • num_cn(nnum_c(inscrit) inscrit) (sélection) • num_et(num_cn(nnum_c(inscrit) inscrit))

Opérations ensemblistes • r[R] et s[R] définies sur le même schéma R • r  s , r  s , r - s • définies sur R • sémantique habituelle • Remarque : r  s = r s

Opérations ensemblistes - Exemples • Liste de tous les noms, prof et étudiants • nom_p(prof)  nom_et(etud) non défini • nnom_p(nom_p(prof))  nnom_et(nom_et(etud)) • Numéros des étudiants inscrits à un seul cours • num_et(inscrit) - num_et(num_cn(nnum_c(inscrit) inscrit))

Opérations ensemblistes - Propriétés • Sélection • C(r  s) = C(r) C(s) ? • Projection • x(r  s) = x(r)  x(s) ? • x(r  s) = x(r)  x(s) ? • x(r - s) = x(r) - x(s) ? • Jointure • (r  r’) s = (r  s) (r’  s) ?

Extension : quotient relationnel • Liste des numéros des étudiants inscrits à tous les cours ? • n est dans la réponse si pour tout n_c dans cours, (n, n_c) est dans inscrit • D ’où la formule pour la réponse : • {n | ( (n_c,x)  cours), (n, n_c)  inscrit} • Ecriture algébrique : • inscrit  num_c(cours)

Extension : quotient relationnel • r[R] et s[S] telles que S  R • Quotient de r par s • relation définie sur R - S • notation : r  s • r  s = {t | (us)(v r)(v.R=t et v.S=u)} • Propriété : • r  s = R-S(r) - R-S ((R-S(r) s) - r)

Algèbre relationnelle et SQL • Projection, sélection, jointure : • SELECT <attributs de la projection> • FROM <relations de l ’expression> • WHERE <condition de sélection> AND<conditions de jointure> • Renommage : • au niveau des attributs • au niveau des relations

Algèbre relationnelle et SQL - Exemples • nom_et,adr_et(num_c123(etud inscrit)) • SELECT nom_et, adr_et • FROM etud, inscrit • WHERE num_c  123 AND etud.num_et = inscrit.num_et

Algèbre relationnelle et SQL - Exemples • num_et(num_cn(nnum_c(inscrit) inscrit)) • SELECT inscrit.num_et • FROM inscrit, inscrit inscr • WHERE inscrit.num_c  inscr.num_c AND inscrit.num_et = inscr.num_et

Algèbre relationnelle et SQL • Opérations ensemblistes • union, intersect, minus • entre deux clauses SELECT FROM WHERE • Liste des noms et adresses des étudiants inscrits seulement au cours de numéro 123 • Algèbre relationnelle ? • SQL ?

Quotient relationnel et SQL • r[A, B] , s[B] • r  s défini sur A par • SELECT distinct A FROM r WHERE NOT EXISTS ((SELECT r’.A, s.B FROM r r’ , s WHERE r.A = r’.A) MINUS (SELECT * FROM r))

Quotient relationnel et SQL • Utilisation des vues • CREATE VIEW <nom_de_vue> AS (<requête SQL>) • Liste des noms des étudiants habitant Casa et inscrits à tous les cours assurés par un prof habitant Tours • Créer une vue pour chaque relation r et s • Appliquer la requête précédente

Fonctions en SQL • Fonctions classiques : • min, max, avg, count, sum • Exemple : inscrit[num_et, num_c] • Nombre de cours où l ’etudiant de num 123 est inscrit • SELECT count(distinct num_c) FROM inscrit WHERE num_et = 123

Fonctions en SQL et groupements • Généraliser la requête précédente pour chaque étudiant • Utilisation de GROUP BY • SELECT num_et, count(distinct num_c) FROM inscrit GROUP BY num_et

Fonctions en SQL et groupements - Exemple • result[num_et, num_c, note] • Liste des numéros des étudiants inscrits à plus de 3 cours, avec leur moyenne générale • SELECT num_et, avg(note) FROM result GROUP BY num_et HAVING count(distinct num_c) > 3

Algèbre relationnelle