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.
© Copyright 2024