TD INFORMATIQUE N°9 PC* 15-16

TD INFORMATIQUE N°9 PC* 15-16
INTERROGATION D’UNE BASE DE DONNEES RELATIONNELLE EN LANGAGE SQL
Pour réaliser les exercices proposés dans ce TD, vous utiliserez le module SQLite Manager de FireFox.
Vous enregistrez les réponses aux différentes questions dans un fichier .rtf (wordpad).
1. Base de données "Astrophysique"
a) Mise en place de la base de données
Cette base de données comporte 5 tables (ou relations) : asteroide, astre, astrophysicien, collision, planete. Les
attributs sont indiqués dans le schéma relationnel ci-dessous.
asteroide
astre
nomAsteroide
Ceres
Pluton
Fictive120
Fictive3456
(4769) Castalia
diametre
0.076354
0.1807
0.0081
0.004
0.00003
nom
Piazzi
Tombaugh
Tournesol
Tournesol
Helin
prenom
Giuseppe
Clyde
Tryphon
Tryphon
Eleanor
nomAstre
Soleil
Alpha Tauri
Alpha Canis Majoris
Fictive
diametre
1
44.2
1.711
3
collision
nomAstre
Fictive
Fictive
Soleil
nomPlanete
FictiveA
FictiveB
Terre
nomAsteroide
date
Fictive120
30/05/2014
Fictive3456
07/09/2021
(4769) Castalia 00/00/0000
planete
nomAstre
Soleil
Soleil
Soleil
Soleil
Soleil
Soleil
Soleil
Fictive
Fictive
astrophysicien
nomPlanete
Mercure
Terre
Uranus
Mars
Venus
Jupiter
Saturne
FictiveA
FictiveB
diametre
0.384
12456.3
4.007
0.533
0.949
11.209
9.4492
1.8
2
masse
0.055
1
14.536
0.107
0.815
317.8
95.152
7
6
tempsRevolution
58
365.257
30799.1
669
224.701
4335.35
10757.7
678
456
nom
Piazzi
Herschel
Tombaugh
Helin
Tournesol
prenom
Giuseppe
William
Clyde
Eleanor
Tryphon
pays
Italie
Britannique
USA
USA
Belgique
Le fichier fourni (Astrophysique.sqlite) possède déjà les tables asteroide, astrophysicien, collision, planete. Il reste
à créer la table astre, et à saisir les 4 tuples de la table.
Ouvrir la base de données Astrophysique.sqlite avec le module SQLite Manager de FireFox.
Pour créer la table astre :
- on peut travailler en mode graphique : Menu Table - Créer une table, etc…
- ou utiliser la commande SQL (onglet « exécuter le SQL ») :
CREATE TABLE "astre" ("nomAstre" TEXT, "diametre" FLOAT)
Puis cliquer exécuter les commandes SQL.
Rem : En SQL, une ligne est ignorée (ou considérée comme commentaire) si elle commence par : -- (double tiret)
Pour saisir les tuples de la table :
- on peut opérer en mode graphique : Cliquer dans la zone à gauche sur la table à modifier, se placer à droite dans
l’onglet parcourir&rechercher, cliquer le bouton 'Ajouter une nouvelle entrée', etc…
- ou bien saisir et exécuter les commandes SQL :
INSERT INTO "astre" VALUES ("Soleil",1)
etc…
b) Questions préliminaires
Q1. Pour chaque table, préciser une clé primaire.
Q2. Pour chaque table, préciser les clés étrangères.
c) Requêtes SQL à formuler sur cette base de données
Formuler les requêtes SQL permettant de répondre aux questions suivantes.
Si la question semble compliquée, décomposer en plusieurs requêtes, et tester chaque sous-requête.
Q3. Quels sont les astres dont le diamètre dépasse 106 km ? On rappelle que le rayon solaire vaut 6,96 ⋅ 105 km .
Q4. Quelles sont les planètes qui tournent autour du soleil et dont le temps de révolution dépasse 500 jours ?
Q5. Quels sont les astrophysiciens qui ont découvert au moins un astéroïde ? On donnera une requête simple qui
renvoie seulement le nom et le prénom, et une autre qui renvoie le nom, le prénom et le pays.
Q6. Quels sont les astrophysiciens qui n’ont découvert aucun astéroïde ?
Q7. Quels sont les astres dont toutes les planètes sont concernées par des collisions ?
Pour cette question on procédera par étapes en écrivant les requêtes suivantes :
- planètes avec collision. On donnera une requête simple qui renvoie seulement le nom, et une autre qui
renvoie le nom et la masse.
- planètes sans collision. On donnera une requête simple qui renvoie seulement le nom, et une autre qui
renvoie le nom et la masse.
- astres dont au moins une planète est sans collision. On donnera une requête simple qui renvoie seulement le
nom, et une autre qui renvoie le nom et le diamètre.
- astres dont toutes les planètes ont une collision. On donnera une requête simple qui renvoie seulement le
nom, et une autre qui renvoie le nom et le diamètre.
- astres dont aucune planète n’est sans collision. On donnera une requête simple qui renvoie seulement le
nom, et une autre qui renvoie le nom et le diamètre.
Q8. Pour chaque astrophysicien, donner son nom, son prénom et le nombre d’astéroïdes qu’il a découvert.
Q9. Quels sont les astres possédant le maximum de planètes ?
Commencer par chercher les astres avec le nombre de leurs planètes. Donner une requête tenant compte du fait
que plusieurs astres peuvent avoir le même nombre de planètes.
Q10. Quelles sont les planètes qui sont concernées par le maximum de collisions possibles ?