Informatique TD 0.7 Base de données Objectif du TD On se propose dans cette activité de révisions 0.7 de revoir les requêtes concernant les bases de données. Les principales capacités développées dans cette partie de la formation sont : • utiliser une application offrant une interface graphique pour créer une base de données et l’alimenter, • utiliser une application offrant une interface graphique pour lancer des requêtes sur une base de données, • distinguer les rôles respectifs des machines client, serveur, et éventuellement serveur de données, • traduire dans le langage de l’algèbre relationnelle des requêtes écrites en langage courant, • concevoir une base constituée de plusieurs tables, et utiliser les jointures symétriques pour effectuer des requêtes croisées. Client : lance les requêtes Serveur : stocke les informations et transmet les réponses aux requêtes [1] Figure 1 : Relation client-serveur pour une base de données locale (serveur de fichiers iaca du lycée par exemple) Figure 2 : Relation client-serveur pour des requêtes par internet (demande d'information météo par exemple) Lycée Jules Ferry Cannes Page 1 sur 4 TSI2 Informatique TD 0.7 Base de données Requêtes sur base de données Table RADOME Table SYNOP 1 Opérateurs relationnels Projection : Sélection de certaines colonnes (ou élimination d’attributs). - algèbre relationnel : ← , ,… ( 1 ) - requête SQL : ←SELECT , ,. .. FROM Exemple : « Quels sont les numéros de station et les villes correspondantes ? » o Algebre relationnelle : o SQL : SELECT _!"#,$% & ('()*+,) num_sta, commune FROM RADOME ; Sélection (restriction) : Sélection de certaines lignes qui vérifient une condition. - algèbre relationnel : ←./01234.412( 1 ) - requête SQL : ←SELECT ∗FROM WHERE attribute = condition ; Exemple : « Quels sont les numéros de station et les villes correspondantes situées dans le var ? » & 678é:#;"& & " /<= (>?@ABC)D o algèbre relationnel : _!"#,$% o requête SQL : SELECT num_sta, commune FROM RADOME WHERE département = 83 ; Lycée Jules Ferry Cannes Page 2 sur 4 TSI2 Informatique TD 0.7 Base de données Jointure symétrique : Combiner deux tables en une seule table suivant un attribut commun. - algèbre relationnel : E ← -F . ./G . . ( 1 × R2 ) - requête SQL : ←SELECT ∗FROM JOIN ON R1.attribute1 = R2.attribute2 ; Exemple : « Quelles sont les villes où la vitesse du vent moyen a dépassé les 20 m/s ? » o algèbre relationnel : $% & 67'K. _!"#/>L.MNO_PQR (>?@ABC × STT>20(WXYAZ)) o requête SQL : SELECT RADOME.commune FROM RADOME JOIN SYNOP ON RADOME.num_sta=SYNOP_sta WHERE SYNOP.ff >20 ; Fonctions d'agrégation : Ces fonctions qui ne sont pas couvertes par l’algèbre relationnelle classique, permettent d’effectuer des calculs statistiques basiques sur les valeurs : MIN, MAX, SUM (somme), AVG(moyenne), COUNT(comptage du nombre de lignes). Exemple : « Quelle est la ville et la valeur de la pression où la plus haute valeur a été mesurée ? » o algèbre relationnel : $% o & ,[+(\(: &;) 67'K. _!"#/>L.MNO_PQR (>?@ABC × WXYAZ)D requête SQL : SELECT RADOME.commune , MAX(SYNOP.pmer) FROM RADOME JOIN SYNOP ON RADOME.num_sta=SYNOP_sta GROUP BY RADOME.commune, SYNOP.pmer NOTA : Formalisme de générale l’instruction SQL « SELECT » SELECT <liste d’expressions> FROM <liste de tables> WHERE <conditions> GROUP BY <liste d’attributs> HAVING <conditions> ORDER BY <liste d’attributs> 2 Opérateurs ensemblistes Union : Relation contenant les attributs appartenant à R1 ou à R2. Il y a suppression des lignes identiques. - algèbre relationnel : R3 ← R1 ⋃ R2 : - Syntaxe SQL : R3 ←R1 UNION R2 Exemple : « Quels sont les numéros de station situés dans le var et celles situées dans les alpesmaritimes ? » o algèbre relationnel : _!"# 678é:#;"& & "/<= (>?@ABC) ⋃ 78é:#;"& & " /_` (>?@ABC)D o requête SQL : SELECT num_sta FROM RADOME WHERE department=83 UNION SELECT num_sta FROM RADOME WHERE department=06 Lycée Jules Ferry Cannes Page 3 sur 4 TSI2 Informatique TD 0.7 Base de données Intersection : Relation contenant les lignes appartenant à R1 et à R2. - algèbre relationnel : R3 ← R1 ⋂ R2 : - Syntaxe SQL : R3 ←R1 INTERSECT R2 Exemple : « Quels sont les numéros de station situés dans le var et celles situées dans les alpesmaritimes ? » o algèbre relationnel : _!"# 678é:#;"& & "/<= (>?@ABC) ⋃ 78é:#;"& & " /_` (>?@ABC)D o requête SQL : SELECT num_sta FROM RADOME WHERE department=83 UNION SELECT num_sta FROM RADOME WHERE department=06 Différence : Relation contenant les lignes appartenant à R1 moins celles appartenant à R2. - algèbre relationnel : R3 ← R1 − R2 : - Syntaxe SQL : R3 ←R1 EXCEPT R2 Exemple : « Quels sont les numéros de station situés dans le var et qui ne sont pas à une altitude supérieure à 70 m ? » o algèbre relationnel : o _!"# 678é:#;"& & "=<= (>?@ABC) requête SQL : − 7#d"e" 8&>70 (>?@ABC) D SELECT num_sta FROM RADOME WHERE départment=83 EXCEPT SELECT num_sta FROM RADOME WHERE altitude>70 Produit cartésien : R3 ← R1 × R2 : Relation contenant l’ensemble des possibilités d’association entre une valeur de R1 et une valeur de R2. On l’utilise notamment pour définir l’opérateur relationnel de type ≪ jointure symétrique≫ (voir ci-avant). Division cartésienne : R3 ← R1 ÷ R2 : c’est la plus grande relation vis-à-vis de l’inclusion. La combinaison de chaque ligne de R3 avec les lignes de R2 est contenue dans R1. En pratique, on peut l’exprimer à partir des autres opérateurs. Elle n’est pas présente directement dans les langages de requêtes de type SQL. Références : [1] P. Beynet, « Informatique ». UPSTI. Lycée Jules Ferry Cannes Page 4 sur 4 TSI2