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.
a
steroide
nomAsteroide diametre nom prenom
Ceres 0.076354 Piazzi Giuseppe
Pluton 0.1807 Tombaugh Clyde
Fictive120 0.0081 Tournesol Tryphon
Fictive3456 0.004 Tournesol Tryphon
(4769) Castalia 0.00003 Helin Eleanor
astre
nomAstre diametre
Soleil 1
Alpha Tauri 44.2
Alpha Canis Majoris 1.711
Fictive 3
collision
nomAstre nomPlanete nomAsteroide date
Fictive FictiveA Fictive120 30/05/2014
Fictive FictiveB Fictive3456 07/09/2021
Soleil Terre (4769) Castalia 00/00/0000
planete
nomAstre nomPlanete diametre masse tempsRevolution
Soleil Mercure 0.384 0.055 58
Soleil Terre 12456.3 1 365.257
Soleil Uranus 4.007 14.536 30799.1
Soleil Mars 0.533 0.107 669
Soleil Venus 0.949 0.815 224.701
Soleil Jupiter 11.209 317.8 4335.35
Soleil Saturne 9.4492 95.152 10757.7
Fictive FictiveA 1.8 7 678
Fictive FictiveB 2 6 456
astrophysicien
nom prenom pays
Piazzi Giuseppe Italie
Herschel William Britannique
Tombaugh Clyde USA
Helin Eleanor USA
Tournesol Tryphon 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 6
10 km
? On rappelle que le rayon solaire vaut
5
6,96 10 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 ?