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 !