TSI2 – Année 2014-2015 – G.Platey
Modèle relationnel – Séance 4
I)
Algèbre relationnelle
a. Vocabulaire
Pour la suite du TP, on se donne la base de données suivante :
élève
classe
prénom
année
age
filière
sexe
professeur_id
classe_id
id_classe
note
id_élève
classe
professeur
nom
sexe
age
id_professeur
élève
professeur
•
Un attribut est un critère.
o Ici, on a par exemple l’âge, le sexe, la
classe, …
• Le domaine est l’ensemble des valeurs
possible de l’attribut.
o Par exemple ici, le domaine de ‘sexe’
est (‘homme’,’femme’). Celui de
‘note’ est
• Un schéma relationnel est un -uplet avec
les attributs et leurs domaines respectifs.
o Dans notre exemple, on a 3 schémas
dont (‘nom’ ; chaine de caractère) ; (’sexe’ ; (‘homme’,’femme’)) ; (‘age’ ;ℕ) ; (id_professeur ; ℕ) )
• Une valeur ou enregistrement est un élément du schéma, composé de composantes liés au
attributs de ce schéma.
o Ici, (Platey,homme,31,1) est une valeur du schéma précédent
• L’ensemble de ces valeurs constitue la table (ou relation)
o On a donc 3 tables dans notre exemple, les tables ‘élève’ ‘professeur’ et ‘classe’
o On dit que ‘Samuel’ est une valeur de la table ‘élève’
• Une base de données est un ensemble de tables (mises en relation ou non)
Exercice 1 : Ouvrir wampserver et importer la base de données tp4.sql disponible à l’adresse
http://maths.platey.pagesperso-orange.fr/tsi2.html
1) donner le domaine de ‘filière’
2) Rajouter un nouvel élève : Gauderic, jeune homme de 19ans qui a 16 en maths dans la classe de TSI2.
3) Changer l’âge de Joe qui a en fait 16 ans.
b. Requêtes simple et syntaxe de l’algèbre relationnelle
On appelle requête simple, une requête dans laquelle, on n’a pas besoin de relier les tables de la base
entre elles. Par exemple, si on recherche les prénoms de tous les garçons, en langage SQL, cela donne
SELECT `prénom` FROM `élève` WHERE `sexe`= "homme"
Dans le langage relationnel, cela revient à faire une PROJECTION (notée ) sur prénom avec la SELECTION
(notée ) `sexe`= "homme"
Cela s’écrit
éè )
é
Exercice 2 : En s’aidant de cet exemple, compléter le tableau ci-dessous
La requête
Les filières proposées
au lycée
Langage SQL
Algèbre relationnelle
)
è
é
!"#
éè
)
Les prénoms ou noms
des femmes du lycée
Le prénom et l’âge
des professeurs
hommes.
é
,%&
'%
∪
() *
'%
éè
() ,
)
éè
)
SELECT `prénom`,`note` FROM `élève`
WHERE `note`>=10 AND
`sexe`="femme"
Exercice 3 : Les deux requêtes suivantes affichent-elles le même résultat ? Laquelle sera la plus rapide ?
,
-"*
éè
)
et
-"* .
,
éè
)/
c. Jointures
Dans une même base de données, lorsqu’on veut lier deux tables qui ont un « même attribut », on utilise
une jointure. Si T et T’ sont les tables avec A et A’ les attributs identiques, on note T[A=A’]T’ la table
obtenue après jointure.
Par exemple, on écrit classe[professeur_id = id_professeur]professeur pour relier les professeurs à leurs
classes. En langage SQL, la syntaxe est : SELECT * FROM `classe` JOIN `professeur` ON
`professeur_id`=`id_professeur`
On obtient la table
Les attributs qui permettent d’identifier les éléments d’une table sont appelés « clé » . Lorsqu’un seul
attribut suffit pour différencier les éléments d’une table, on l’appelle « clé primaire »
En général les deux attributs « nom » « prenom » ne suffisent pas pour différencier les individus d’une
population. On choisit alors « l’adresse mail » ou « le numéro de sécurité sociale » ou encore « le numéro
de CNI » Dans notre exemple, on a choisi « id » comme identifiant et l’incrémentation se fait
automatiquement à chaque nouvelle saisie d’élément dans chaque table. Ces clés primaires permettent de
relier (joindre) facilement deux tables d’une même base.
Exercice 4 : Comparer cette requête avec celle-ci : SELECT * FROM `classe`,`professeur`
A quoi correspond cette dernière ?
Exercice 5 : Quelle autre jointure peut-on faire dans cette base tp4 ? Afficher la dans phpmyadmin. Vous
admettrez que cela donne vite des tables de taille considérable.
Exercice 6 : A présent, nous pouvons faire des requêtes dans ces tables plus grandes. Compléter le tableau
ci-dessous en suivant le premier exemple
La requête
Langage SQL //
Algèbre relationnelle
SELECT `prénom` FROM `élève` JOIN `classe` ON
Les prénoms des
`classe_id`=`id_classe` WHERE `professeur_id`=1
élèves de
Monsieur Platey
6
)/
.
− 34 = 34 −
é
0 () " é è 1
Les classes (année
et filière) de Gauss
Les élèves des
classes de
première
SELECT `prénom`,`note` FROM `classe` JOIN `élève` ON `id_classe`=`classe_id`WHERE
`professeur_id`=1
é
Les prénoms des
élèves qui ont la
moyenne avec
Gauss
Les élèves garçons
de Madame
Shaparova
(difficile)
,
.
7"*,
è
89:
éè
1
− 34 = 34 −
6
)/
d. agrégations
L’agrégation va servir à regrouper les valeurs (par exemple ici des élèves) qui ont un (ou plusieurs)
attribut(s) en commun (âge, ou classe, ou sexe ?) et à effectuer une opération sur ce groupe.
On peut par exemple calculer l’âge moyen des élèves de TSI2. Cela s’écrit
;
<
%& )
.
'%
=>
?
éè
)/ ou encore
'%
() ?.
SELECT AVG(`age`) FROM `élève` WHERE `classe_id`=6
'%
=>
;
<
%& )
éè
)/ et
en SQL
Pour afficher la note minimale de chaque classe, on écrira
6
− 34 = 34 −
) é è 1
% é ,
è ;@AB
) et SELECT
`année`,`filière`,MIN(`note`) FROM `élève`JOIN `classe` ON `classe_id`=`id_classe`
GROUP BY `classe_id`
en SQL
En langage relationnel, les agrégations sont comptage, max, min, somme, moyenne
En SQL, on les écrira respectivement COUNT ; MAX ; MIN ; SUM ; AVG
Exercice 7 : Compléter le tableau
Langage SQL //
La requête
Algèbre relationnelle
Déterminer l’âge
maximal des élèves
;@CD
%& )
éè
)
SELECT `sexe`,avg(`note`) FROM `élève` GROUP BY `sexe`
Calculer les
moyennes de toutes
les classes (avec
l’identifiants classe)
%
é ,
è
;
<
)
éè
1
_34 = 34_
6
)
SELECT `année`,`filière`,avg(`note`) FROM `classe` JOIN
`élève` ON `id_classe`=`classe_id` GROUP BY `id_classe`
HAVING avg(`note`)>=12
_34
)7"*
) é è 1
<
'%
( );
<
= 34_
6
))
Afficher chaque
classe avec le nombre
d’élèves
Afficher chaque
professeur avec le
nombre d’élèves
Trouver le nom du
prof qui a la moyenne
la plus grande
Exercice 8 : Ajouter une relation ‘salle’ à cette base tp4.sql avec au moins les attributs ‘ étage’ ‘numéro’
‘capacité’ et une mise en relation avec la relation ‘classe’. Puis compléter la base et imaginer des requêtes.