Algèbre relationnelle Stéphane Gonnord, Laurent Jouhet

SQL vs Maths Projections et sélections Jointures Agrégation Autres opérateurs

Bases de données (3)Algèbre relationnelle

Stéphane Gonnord, Laurent Jouhet

Lycée du parc - Lyon


Plan

SQL vs Maths

Projections et sélections

Jointures

Agrégation

Autres opérateurs


Vous comprenez ça ?

γMAX(c)(

depγCOMPTAGE :c(departements onJCD

(σpop≥104communes

)))

SELECT max(c)FROM

(SELECT dep, COUNT(*) AS cFROM departements JOIN communesON departements.id=communes.depWHERE pop>=10000GROUP BY dep)

Quel est le nombre maximal de communes de plus de 10000 habitantspar département ?



γMAX(c)(



)))SELECT max(c)FROM





γMAX(c)(



)))SELECT max(c)FROM




L’objet de l’algèbre relationnelle

• Manipuler des relations (ensembles de tuples typés).

• Des opérateurs pour construire des relations à partir d’autresrelations.

• Binaires : union, intersection, ... produit, jointure (et division).

• Unaires : projections, sélections.

• Des opérateurs d’agrégation.


ExemplesTrois relations différentes

communesid dep nom pop

. . . . . . . . . . . .69023 69 Lyon 484344

. . . . . . . . . . . .2B050 2B Calvi 5394

. . . . . . . . . . . .

departementsid reg nom

. . . . . . . . .69 82 Rhône. . . . . . . . .2B 94 Haute-Corse. . . . . . . . .

regionsid nom

. . . . . .82 Rhône-Alpes. . . . . .94 Corse. . . . . .


ExemplesEt trois de plus

eleveside nom prenom

0 Lions Jacques-Louis1 Laurent Jean

. . . . . . . . .

profsidp nom prenom

0 Théron Pierre1 Brun Jules

. . . . . . . . .

collesprof eleve semaine note

2 8 1 161 0 6 19

. . . . . . . . . . . .


Projections

• Permet de choisir des colonnes

• Analogue de SELECT, attention !

• πA1,...,Ak R ou πA1,...,Ak (R) : on ne prend que les attributs A1, ...,Ak

• Exemple :• En français : «Donner les élèves (identifiants) et les notes pour

toutes les colles.»• En SQL : SELECT eleve,note FROM colles.• En algèbre relationnelle : πeleve,note(colles).


2 8 1 161 0 6 19

. . . . . . . . . . . .

→

πeleve,note(colles)eleve note

8 160 19

. . . . . .


Projections

• Permet de choisir des colonnes

• Analogue de SELECT, attention !

• πA1,...,Ak R ou πA1,...,Ak (R) : on ne prend que les attributs A1, ...,Ak

• Exemple :• En français : «Donner les élèves (identifiants) et les notes pour

toutes les colles.»• En SQL : SELECT eleve,note FROM colles.• En algèbre relationnelle : πeleve,note(colles).


2 8 1 161 0 6 19

. . . . . . . . . . . .

→

πeleve,note(colles)eleve note

8 160 19

. . . . . .


Sélections/restrictions

• Permet de choisir des lignes

• Conditions de type WHERE ...

• σCR ou σC(R), avec C la condition (formule logique portant surles attributs)

• Exemple :• En français : «Quelles sont les villes de plus de 10000

habitants ?»• En SQL : SELECT * FROM communes WHERE pop>=10000• En algèbre relationnelle : σpop≥10000(communes).


69023 69 Lyon 4843442B050 2B Calvi 5394

. . . . . . . . . . . .

σpop≥10000(communes)id dep nom pop

69023 69 Lyon 48434479049 79 Bressuire 18615

. . . . . . . . . . . .


Sélections/restrictions

• Permet de choisir des lignes

• Conditions de type WHERE ...

• σCR ou σC(R), avec C la condition (formule logique portant surles attributs)

• Exemple :• En français : «Quelles sont les villes de plus de 10000

habitants ?»• En SQL : SELECT * FROM communes WHERE pop>=10000• En algèbre relationnelle : σpop≥10000(communes).


69023 69 Lyon 4843442B050 2B Calvi 5394

. . . . . . . . . . . .

σpop≥10000(communes)id dep nom pop

69023 69 Lyon 48434479049 79 Bressuire 18615

. . . . . . . . . . . .


On peut composer !

• Exemple : nom des villes de plus de 100000 habitants : traduireen SQL et algèbre relationnelle.

• SELECT nom FROM communes WHERE pop>=100000

• πnomσpop≥105(communes).

ExerciceEst-ce que ça commute ?

πnom(σpop≥105(communes)) =?σpop≥105(πnom(communes))

πeleve,note(σnote≥19(colles)) =?σnote≥19(πeleve,note(colles))


On peut composer !








On peut composer !








Produit : beurk

• Deux tables :communesnom dep

Lyon 69Calvi 2BCorte 2B

departementsid nom

69 Rhône2B Haute-Corse

• Et leur produit :communes×departements

nom dep id nom

Lyon 69 69 RhôneLyon 69 2B Haute-CorseCalve 2B 69 RhôneCalvi 2B 2B Haute-CorseCorte 2B 69 RhôneCorte 2B 2B Haute-Corse


Produit : beurk

• Deux tables :communesnom dep

Lyon 69Calvi 2BCorte 2B

departementsid nom

69 Rhône2B Haute-Corse

• Et leur produit :communes×departements

nom dep id nom

Lyon 69 69 RhôneLyon 69 2B Haute-CorseCalve 2B 69 RhôneCalvi 2B 2B Haute-CorseCorte 2B 69 RhôneCorte 2B 2B Haute-Corse


Jointure : produit avec sélection

• Formellement :R1on

CR2 = σC(R1×R2)

• t1 JOIN t2 ON (C) et t1,t2 WHERE (C) sont proches... maisdifférentes ? (Boîte noire) !

• Par exemple : C = (dep = id)

communes onCdepartements

nom dep id nom

Lyon 69 69 RhôneCalvi 2B 2B Haute-CorseCorte 2B 2B Haute-Corse




CR2 = σC(R1×R2)




nom dep id nom





CR2 = σC(R1×R2)




nom dep id nom



Fonctions d’agrégation

• But : regrouper des lignes, et évaluer une fonction sur cesregroupements.

• Syntaxe strange :

A1,...,Ak γf1(B1),...,fi(Bi)table

• « Regroupe selon les attributs Ai , et calcule les valeurs fi sur lesattributs Bi »

• SQL :

SELECT A1,..,Ak,f1(B1),...,fi(Bi)FROM tableGROUP BY A1,...,Ak

• Attention, les autres champs sont perdus ! En particulier lors ducalcul du maximum !







• SQL :









• SQL :









• SQL :




Exemples

• Population des différents départements :

depγSOMME(pop)(communes)

• Avec le nom du département ?

σn(

dep,departements.nom:nγSOMME(pop)(R)),

où R = communes on departements

• Moyenne des différents élèves ? (sans les noms)

eleveγMOYENNE(note)colles

• Et enfin : γMAX(c)(

depγCOMPTAGE:c(departements onJCD

(σpop≥104 communes

)))


Exemples




σn(








)))


Exemples




σn(








)))


Exemples




σn(








)))


Exemples




σn(








)))


Exemples




σn(








)))


Exemples




σn(








)))


• Union

• Intersection

• Différence

Uniquement quand ça a du sens !

Documents

Algèbre relationnelle Stéphane Gonnord, Laurent Jouhet