TP BD 2a [PDF]

Zitiervorschau

T. P. Bases de donn´ees. ESIL. 2i`eme ann´ee

´ DE LA MEDITERRAN ´ ´ UNIVERSITE EE

Enseignants : Nicolas Durand & Odile Papini

Ann´ee universitaire 2011/2012

Feuille de T. P. 2 : Interrogation de BD en SQL Oracle Pr´ eliminaires Se connecter sous Windows Pour se connecter au serveur Oracle version Oracle Database 10g Express:

Toutes les documentations sont consultables partir du site d’Oracle : http://www.oracle.com/technology/products/database/sql developer/ http://www.oracle.com/technology/products/database/oracle10g/ Exercice 1 : Interrogation de base de donn´ ees en SQL Oracle Soit la BD vols-r´eservations d´ecrite par les 6 sch´emas de relations : AVIONS(NumAv, NomAv, CapAv, VilleAv ) PILOTES(NumPil, NomPil, NaisPil, VillePil)

• cliquer sur l’icˆone SQLdeveloper

VOLS(NumVol,VilleD, VilleA, DateD, DateA, NumPil, NumAv, CoutVol)

• selectionner : new connexion

CLIENTS(NumCl, NomCl, NumRueCl, NomRueCl, CodePosteCl, VilleCl)

• donner un nom `a la connexion : choisir un nom de connexion

DEFCLASSES(NumVol, Classe, CoeffPlace, CoeffPrix )

• donner un nom d’utilisateur : compten (consulter la liste des num´eros attribu´es : 1 par personne)

RESERVATIONS(NumCl, NumVol, Classe, NbPlaces )

• donner un mot de passe : compten (la premi`ere fois) • changer le nom du host, remplacer localhost par : pedaserv1 Des instructions SQL utiles : • instruction SQL pour changer de mot de passe ALTER USER username identified BY new password; • (en ligne de commande) instruction SQL pour afficher une date avec jour, mois, anne, heure et minutes ALTER SESSION SET nls date format=’dd/mm/rrrr hh24:mi’; • (ou en mode graphique) : selectionner Tools, puis Preference, puis Database puis NLS FORMAT changer le format de la date

Les attributs soulign´es forment la clef primaire de la relation o` u ils se trouvent. Les attributs ayant mˆeme nom qu’une clef primaire r´ef´erencent cette clef. Le sens de chaque attribut est exprim´e par son nom. Les types des attributs, autres que chaˆıne de caract`eres, sont donn´es ici : Les num´eros servant de clefs sont des entiers, `a l’exception du num´ero de vol NumVol qui est une chaˆıne de caract`eres commenant par la lettre V. CapAv, NaisPil, NbPlaces, NumRueCl, CodePosteCl sont des entiers. CoutVol, CoeffPlace et CoeffPrix sont des nombres d´ecimaux : CoeffPlace, dans l’intervalle [0,1], donne le pourcentage de places existant dans la Classe concern´ee, pourcentage relatif `a CapAv, capacit´e totale de l’avion. CoeffPrix, sup´erieur ou ´egal `a 1, donne le coefficient multiplicatif a` appliquer `a CoutVol pour obtenir le prix r´eel d’un voyage dans la classe voulue. CoutVol est donc le prix minimal d’une place, sur le vol concern´e. DateD et DateA sont des dates, comportant le jour et l’heure voulue. Requˆ etes ` a formuler sur la base vols-r´ eservations en SQL Oracle

1

2

1 Num´eros des pilotes qui assurent au moins un trajet autre que Paris-Marseille.

19 Num´eros des vols qui assurent le mˆeme jour le trajet inverse du vol V101.

2 Num´eros des vols dont la dur´ee est comprise entre 2h et 4h.

20 Num´eros et noms des clients qui ont r´eserv´e sur un vol transportant Mr Lorentz

3 Num´eros et noms des avions dont le nom ne contient pas la lettre ”a”. 4 Noms et aˆges des pilotes habitant Nice et aˆg´es de plus de 35 ans 5 Num´eros des vols auxquels ni l’avion 101 ni l’avion 401 n’ont ´et´e affect´es. (Les vols auxquels aucun avion n’a ´et´e affect´e doivent ˆetre s´electionn´es). 6 Num´ero et ville de r´esidence de chaque pilote. Lorsque la ville est inconnue, afficher ”Ville inconnue” comme valeur. 7 Trier les vols a` destination de Marseille, par ordre croissant sur les dates de d´epart et par ordre d´ecroissant sur les heures de d´epart. Afficher le num´ero de vol, la ville, la date et l’heure de d´epart. 8 Afficher les noms d’avions en supprimant la r´ef´erence du mod`ele. Par exemple, ”Boeing 747” deviendra ”Boeing”, de mˆeme ”Airbus A310” sera transform´e en ”Airbus”. 9 Nombre de villes desservies (villes d’arriv´ee) par la compagnie 10 Nombre d’avions qui ont une capacit´e inconnue, et aussi la plus petite des capacit´es connues. 11 Nombre de places r´eserv´ees sur le vol V101 (idem avec V222) 12 Num´eros des clients ayant effectu´e plus de 3 r´eservations 13 Num´eros des pilotes qui assurent en avril un nombre d’heures de vol sup´erieur a` 170. 14 Num´eros des clients ayant effectu´e au moins une r´eservation et coˆ ut total de toutes ses r´eservations 15 Horaire du premier vol, pour chaque jour de vol et chaque trajet. 16 Nombre de vols assur´es au d´epart de Paris pour chaque jour de vol du mois d’avril. 17 Pourcentage de vols assur´es au d´epart de Paris pour le mois davril. 18 Y a-t-il au moins un vol au d´epart de Marseille vers Amsterdam tous les jours du mois d’avril ? (Donner une r´eponse en oui ou non)

3

21 Num´eros et noms des pilotes qui n’effectuent aucun vol au d´epart de Paris. 22 Num´ero de chaque pilote et nombre d’avions diff´erents qu’il pilote 23 Pour chaque vol `a destination de Marseille, num´ero du vol et nom de l’avion qui lui est affect´e 24 Nombre de places existant dans chaque classe du vol V240 (idem avec V590 et V650) 25 Nombre de places r´eserv´ees dans chaque classe du vol V101 (idem avec V240 et V590) 26 Nombre de places disponibles (non r´eserv´es) dans chaque classe du vol V101 (idem avec V240 et V590) 27 Num´eros des pilotes qui effectuent le plus de vols 28 Pour chaque vol r´eserv´e, donner le num´ero des clients qui ont r´eserv´e le plus grand nombre de places. 29 Num´eros des pilotes qui conduisent tous les Airbus A340. Exercice 2 : Cr´ eation de base de donn´ ees en SQL Oracle R´ediger les triggers qui expriment les contraintes suivantes : • contrainte 1 : une r´eservation ne peut pas ˆetre pass´ee sur un vol dont le d´epart a d´ej` a eu lieu. • contrainte 2 : il est impossible de supprimer une r´eservation relative `a un vol en cours. • contrainte 3 : pour chaque vol, la somme des coefficients CoeffPlace de chaque classe doit ˆetre inf´erieure ou ´egale `a 1. • contrainte 4 et 5 : `a un instant donn´e, un pilote assure au plus un vol. De mˆeme pour un avion.

4

• contrainte 6 : le nombre de places r´eserv´ees, pour une classe et un vol donn´es, est inf´erieur ou ´egal au nombre de places existant dans cette classe et ce vol. La base de donn´ees restera coh´erente si sa r´ealisation courante v´erifie ces six contraintes. Seules les instructions : insert delete update peuvent transformer une r´ealisation existante et coh´erente en r´ealisation incoh´erente. Pour chaque contrainte, indiquez quelles sont les tables et les instructions qu’il faut contrˆoler. Quel type de contrˆole faut-il faire : imm´ediat ou diff´er´e ?

5