TD/TP2.7 - TSI Ljf.html
publicité
Informatique TD/TP S2.7 Base de données Les principales capacités développées 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. En travaux pratiques, nous allons utiliser l'extension SQLite du navigateur FireFox. Il s'agit d'une interface graphique permettant de créer une base de données, de l'alimenter et de lancer des requêtes SQL. L'avantage de cette solution est qu'elle ne nécessite pas d'installation particulière et permet de travailler sans nécessité de paramétrer la connexion à la base de données. Structure logicielle Figure 1 : Structure logicielle pour internet (à ga uche) et locale (à droite). 1) Comment s'appelle la structure de gauche de la Figure 1. Quel est l'intérêt de cette structure. Structure client serveur 2) Nous allons travailler en TP avec la structure de droite de la Figure 1. Quel est l'intérêt et l'inconvénient de cette structure ? Avantage : pas besoin de connection. Inconvénient : base de données non partagée; pas de contrôle d'accès Structure de la base de données Les tables sur lesquels porte cette activité sont celles du cours : La table RADOME contient la liste des stations de météoFrance et leur caractéristiques géographiques. La table SYNOP contient les enregistrements météorologiques des différentes stations. Lycée Jules Ferry Cannes Page 1 sur 5 TSI1 Informatique TD/TP S2.7 Base de données 3) Pourquoi la clef étrangère RADOME_num_sta ne convient pas comme clef primaire de la table SYNOP ? Chaque station va avoir plusieurs enregistrements météo (pas unique) Requête sur une seule table 4) Quelle sera la différence d'affichage entre les 2 instructions suivantes : - SELECT département FROM RADOME; - SELECT DISTINCT département FROM RADOME 1er : liste de département répété autant de fois que le nb de station 2e : liste des départements possédant au moins une station 5) Ecrire la requête permettant d'obtenir la liste des différentes altitudes des stations météorologiques (en algèbre relationnel puis en SQL). requête SQL : SELECT altitude FROM RADOME 6) Ecrire une requête permettant d'afficher la liste des stations des alpes-maritimes (en algèbre relationnel puis en SQL). _ é requête SQL : SELECT num_sta FROM RADOME WHERE département == 06 ; 7) Ecrire une requête permettant d'afficher la liste des pressions enregistrées (en algèbre relationnel puis en SQL). !" # requête SQL : SELECT pression FROM SYNOP Requêtes croisées 8) Ecrire une requête croisée permettant de lister les noms usuels des stations météorologiques, leur altitude et les pressions enregistrées (en algèbre relationnel puis en SQL). _ , , _ _%&'_()* × !" # SELECT nom_usuel, altitude,pression FROM RADOME JOIN SYNOP ON RADOME.num_sta==SYNOP.RADOME_num_sta ; 9) Ecrire une requête croisée en SQL permettant de lister les noms usuels des stations météorologiques, leur altitude et les pressions enregistrées le 2016-05-01 à 12:00:00. (on note cette date sous la forme d'une chaine de caractère '2016-05-01 12:00:00'). SELECT nom_usuel, altitude,pression FROM RADOME JOIN SYNOP ON num_sta==RADOME_num_sta WHERE date=="2016-04-31 12:00:00"; 10) Ecrire une requête croisée en SQL permettant de lister les noms usuels des stations, la pression pmer ramenée au niveau de la mer inférieur ainsi que les dates des enregistrements. La pression au niveau de la mer s'obtient par l'opération pression + altitude/100 (en atmosphère standard la pression augmente de 1hPa tous les 100m lorsque l'on se rapproche de la mer). Lycée Jules Ferry Cannes Page 2 sur 5 TSI1 Informatique TD/TP S2.7 Base de données SELECT nom_usuel, pression+altitude/100 as pmer , date FROM RADOME JOIN SYNOP ON num_sta==RADOME_num_sta; 11) Ecrire une requête croisée en SQL permettant de lister les noms usuels des stations météorologiques et la pression moyenne pmer. SELECT nom_usuel, pression+altitude/100, AVG(pression+altitude/100) FROM RADOME JOIN SYNOP ON num_sta==RADOME_num_sta GROUP BY nom_usuel; 12) Ecrire une requête croisée en SQL permettant de lister les noms usuels des stations météorologiques et le nombre de relevés où la pression pmer a été inférieure à 1013hPa (situation climatique de dépression) pour chaque station. SELECT nom_usuel, pression+altitude/100 as pmer , COUNT(*), FROM RADOME JOIN SYNOP ON num_sta==RADOME_num_sta WHERE pmer>1013 GROUP BY nom_usuel; 13) Ecrire une requête croisée en SQL permettant de lister les noms usuels des stations météorologiques et le nombre de relevés des stations ayant plus de 2 relevés. SELECT nom_usuel, COUNT(*) as nb, FROM RADOME JOIN SYNOP ON num_sta==RADOME_num_sta GROUP BY nom_usuel HAVING nb>2 ; Références : [1] « Carte Météo des stations météo en France - Station-meteo.com ». P. Beynet (UPSTI) Lycée Jules Ferry Cannes Page 3 sur 5 TSI1 Informatique TD/TP S2.7 Base de données Tutoriel SQLite / FireFox (1/2) Installation de l'extension Si l'extension est installée, elle est accessible par le menu FireFox : Outils/SQLite Manager Si l'extension n'est pas installée, choisir le menu FireFox : Outils / Modules complémentaires, taper sql dans le champs de recherche puis installer SQLite. Il peut être nécessaire de redémarrer FireFox. Création de la base de données Créer la base de données en cliquant sur l'icône du module SQLite. Taper le nom de la base puis choisir un dossier où vous avez le droit d'écriture (Mes documents par exemple). Création de la table RADOME La création des tables est accessible par l'icône Il faut ensuite remplir les différents champs : du module SQLite. Une fois la table créée on peut visualiser les attributs et le code SQL qui a permis de créer la table : Lycée Jules Ferry Cannes Page 4 sur 5 TSI1 Informatique TD/TP S2.7 Base de données Tutoriel SQLite / FireFox (2/2) Requête sur la table RADOME Pour pouvoir tester les requêtes sur la table RADOME, il convient de rentrer quelques enregistrements concernant les stations [1] en cliquant sur l'onglet "Parcourir&rechercher" en sélectionnant la table dans l'arbre de construction puis en cliquant sur "Ajouter une nouvelle entrée". Création de la table SYNOP La table SYNOP est créée graphiquement de la même façon (des valeurs par défaut peuvent être saisie ou choisi dans les menus déroulant dans l'interface de création). On obtient ainsi le code SQL suivant: CREATE TABLE "SYNOP" ( "num_mesure" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE , "date" TEXT DEFAULT CURRENT_TIMESTAMP, "pression" INTEGER DEFAULT 1013, "vitesse_vent" INTEGER, "temperature" INTEGER, "RADOME_num_sta" INTEGER) Déclaration de l'association entre les tables RADOME et SYNOP Une fois les différents attributs définis, il convient de définir la clef étrangère (FOREIGN KEY) indisponible depuis l'interface graphique : - - Copier la requête SQL qui permet de définir la table SYNOP puis supprimer la table (ATTENTION l'opération n'est pas réversible et les changements automatiques dans la base). Ouvrir l'onglet Exécuter le SQL puis coller la requête de création de la table SYNOP. Ajouter un argument supplémentaire à la fin de la requête pour créer le lien entre les attributs des 2 tables : CREATE TABLE "SYNOP" ("num_mesure" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE , "date" TEXT DEFAULT CURRENT_TIMESTAMP, "pression" INTEGER DEFAULT 1013, "cap_vent" INTEGER, "vitesse_vent" INTEGER, "temperature" INTEGER, "RADOME_num_sta" INTEGER, FOREIGN KEY ("RADOME_num_sta") REFERENCES "RADOME" ("num_sta") ) Exécuter la commande SQL, pour que ce lien à partir de la clef étrangère fonctionne correctement il convient d'exécuter la requête SQL suivante en remplacement de l'instruction précédente (active la vérification lors de la saisie des enregistrements) : PRAGMA foreign_keys = ON; Requêtes croisées avec la table SYNOP Pour observer l'effet des requêtes croisées, il faut ajouter des enregistrements sur la table SYNOP : Lycée Jules Ferry Cannes Page 5 sur 5 TSI1
