TD8

TD – Informatique – PSI
Base de données
1. Traduction en algèbre relationnelle de plusieurs requêtes
Q1. Traduire en algèbre relationnelle les requêtes SQL suivantes issues d'une base de données de notes d'un lycée.
SELECT Filiere FROM classe
SELECT ProfMath FROM classe WHERE Filiere=”MP”
SELECT * FROM classe WHERE Filiere=”PSI”
SELECT Prenom FROM eleve WHERE classe=1 OR classe=3
SELECT Nom, Moyenne FROM eleve WHERE Moyenne<10
SELECT ProfPhys FROM classe JOIN eleves ON eleves.Classe=classe.Numero
SELECT MAX(Moyenne) FROM Eleve WHERE Classe=3
2. Interrogation d’une base de données de Cinéclub
La base de données du vidéo club permet de satisfaire les conditions suivantes :
- Les films peuvent être sur différents supports (DVD, Blu-Ray, etc.), et le tarif de la location est fonction du support ;
- Un même film peut être présent en plusieurs exemplaires ;
- Chaque exemplaire d'un film à un numéro d'inventaire ;
- Les clients connaissent parfois le titre du film qu'ils veulent emprunter ;
- Les clients demandent souvent les films disponibles de tel acteur ou tel réalisateur ;
- Les clients les plus passionnés recherchent des films avec « tel titre » et « telle date de sortie » (pour différencier les
remakes) ;
- Les clients sont connus par leur nom, prénom, adresse ;
- Le vidéo club a besoin de connaître les emprunts en cours.
Copier la base de données « Videoclub.db3 » qui se trouve sur le répertoire de partage habituel et le copier dans un dossier libre
d’accès en écriture renommé avec votre nom. Ensuite, la charger en lançant Firefox, puis l’extension « SQLiteManager » (Menu «
Outils », puis SQLiteManager). Si l’extension ne s’y trouve pas, il va falloir l’installer : depuis MozillaFirefox : Ouvrir le menu Modules – chercher SQLiteManager et l’installer. Il faudra surement redémarrer Firefox. Ensuite, on pourra l’utiliser.
Q.1. Observer cette base : Combien de tables ? Quels attributs ? Clés primaires ?
Pour les questions suivantes, il est demandé de transformer la question en requête SQL.
Q.2. Quel est le titre (VO) des films plus récents que 2008 ?
Q.3. Quel est le titre (VO) des films disponibles en Blu-ray ?
Q.4. Quel est le titre (VO) des films disponibles en Blu-ray et antérieurs à 1950 ?
Q.5. Quels sont les films dans lesquels joue Jean-Paul Belmondo ?
Q.6. Quel est le réalisateur du film Gran Torino ?
Q.7. Quels sont les films réalisés par Clint Eastwood ?
Q.8. Quels sont les films empruntés par Pauline SCHMITT?
TD – Informatique – PSI
Q.9. Quel est le nombre d'exemplaires en stock de chaque film par titre et par format ?
Q.10. Quel est le nombre d'emprunts effectués en moyenne par les clients ? On pourra utiliser la fonction AVG qui calcule une
moyenne.
Q.11. Quelle est la liste des sommes déjà dépensées par client ?
Q.12. Quelles personnes ont été à la fois acteur(-trices) et réalisateur(-trices) ?
Q.13. Quels sont les films possédant le plus d'exemplaires ?
Q.14. Dans quels films peut-on voir Alain Delon et Simone Signoret jouer ensemble ?
Q.15. Qui est l'acteur qui a joué dans le plus grand nombre de films ?
3. Exploitation d’une base de données météo sous Python
La NOAA (National Oceanic and Atmospheric Administration), une agence des Etats-Unis, diffuse des données météorologiques
relatives au territoire américain. Il s'agit de relevés journaliers, effectués en général dans les aéroports. Le fichier « noaa.db3 »
fourni contient les données journalières pour une année. La base de données est constituée de deux tables :
- « Station » qui modélise les stations de relevés :
o StationId : identifiant unique à usage interne ;
o CallSign : indicatif, unique également, en général un code d'aéroport ;
o Name : nom ;
o State : code de l'état (deux lettres) ;
o Location : emplacement, en langage naturel ;
o Latitude, Longitude : coordonnées géographiques (degrés) ;
o GroundHeight, StationHeight.
- « Weather » qui modélise un relevé journalier :
o StationId : identifiant de la station ;
o Date : date sous la forme AAAAMMJJ ;
o Tmax, Tmin, Tavg : températures maximale, minimale, moyenne (certaines valeurs peuvent valoir Null si les
données sont indisponibles) ;
o …
On peut toujours utiliser le module Sqlite Manager comme précédemment, mais comme on souhaite récupérer des valeurs et les
analyser, on préfèrera le langage Python. Le langage Python permet d'accéder facilement à des bases de données SQLite, grâce au
module sqlite3. Le schéma général d'un programme Python utilisant une base SQLite est le suivant :
Par exemple, si l’objectif est d’obtenir le nom, l’état et les coordonnées GPS de la station qui est située la plus au nord, on saisira
dans un script les lignes de code suivantes :
import sqlite3 as sql
bdd=sql.connect("noaa.db3")
requete='SELECT Name, State, Latitude, Longitude \
FROM station \
WHERE Latitude= \
(SELECT MAX(Latitude) FROM station)'
reponse=bdd.execute(requete)
print(reponse)
La dernière ligne affichera le résultat suivant dans la console : <sqlite3.Cursor object at 0x035A6360>
Il s’agit d’un objet curseur qu’on ne peut pas afficher directement. Par contre, cet objet est itérable. On récupère ainsi les lignes de
la réponse sous la forme de tuples. Ainsi, si l’on intègre les lignes de codes suivantes au script :
for li in reponse:
print(li)
TD – Informatique – PSI
On obtient au final :
('BARROW', 'AK', 71.3213, -156.611)
Tester ces lignes dans un script Python et les comparer aux résultats d’une requête directement saisie sur SQLite Manager. Le
résultat est évidemment le même.
Q.1. En utilisant Python, donner les noms, les états, les altitudes au sol et les températures extrêmes des stations où l’écart entre la
température minimale et la température maximale au cours de l’année a dépassé 75 degrés.
Q.2. Obtenir un graphe des températures journalières minimales, maximales et moyennes à l'aéroport JFK, sur l'année.