TD/TP2.7 - TSI Ljf.html

Informatique TD/TP S2.7 Base de données
Lycée Jules Ferry Cannes Page 1 sur 5 TSI1
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 (à gauche) 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.
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.

Informatique TD/TP S2.7 Base de données
Lycée Jules Ferry Cannes Page 2 sur 5 TSI1
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
1
er
: liste de département répété autant de fois que le nb de station
2
e
: 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).

Informatique TD/TP S2.7 Base de données
Lycée Jules Ferry Cannes Page 3 sur 5 TSI1
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)

Informatique TD/TP S2.7 Base de données
Lycée Jules Ferry Cannes Page 4 sur 5 TSI1
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 du module SQLite.
Il faut ensuite remplir les différents champs :
Une fois la table créée on peut visualiser les attributs et le code SQL qui a permis de créer la table :

Informatique TD/TP S2.7 Base de données
Lycée Jules Ferry Cannes Page 5 sur 5 TSI1
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 :