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

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

Plan

SQL vs Maths

Projections et sélections

Jointures

Agrégation

Autres opérateurs

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 ?

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 ?

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 ?

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

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.

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

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. . . . . .

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

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

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

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

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).

collesprof eleve semaine note

2 8 1 161 0 6 19

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

→

πeleve,note(colles)eleve note

8 160 19

. . . . . .

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

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).

collesprof eleve semaine note

2 8 1 161 0 6 19

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

→

πeleve,note(colles)eleve note

8 160 19

. . . . . .

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

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).

communesid dep nom pop

69023 69 Lyon 4843442B050 2B Calvi 5394

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

σpop≥10000(communes)id dep nom pop

69023 69 Lyon 48434479049 79 Bressuire 18615

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

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

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).

communesid dep nom pop

69023 69 Lyon 4843442B050 2B Calvi 5394

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

σpop≥10000(communes)id dep nom pop

69023 69 Lyon 48434479049 79 Bressuire 18615

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

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

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))

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

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))

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

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))

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

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

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

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

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

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

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

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

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

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

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

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 vs Maths Projections et sélections Jointures Agrégation Autres opérateurs

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 vs Maths Projections et sélections Jointures Agrégation Autres opérateurs

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 vs Maths Projections et sélections Jointures Agrégation Autres opérateurs

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 vs Maths Projections et sélections Jointures Agrégation Autres opérateurs

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

)))

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

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

)))

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

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

)))

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

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

)))

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

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

)))

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

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

)))

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

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

)))

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

• Union

• Intersection

• Différence

Uniquement quand ça a du sens !