Dans la seconde partie, Il vous sera founit, au bout d'1h30 de travail, des fichiers à télécharger afin d'homogénéiser les groupes. Ces fichiers contiennent les définitions des tables et des données insérées. Cela fera office de corrigé (c'est pour cela que nous vous demandons une première copie au bout d'1h30). Cette seconde partie concerne l'interrogation de ces données. Vous aurez un ensemble de requêtes écritent en français qu'il faudra traduire en SQL.Le rapport est un ensemble commenté des questions de chaques parties. C'est à dire que nous voulons les requêtes à définir, ainsi qu'un commentaire pour chacun (même succint). Si des questions de cours sont demandées, veuillez reporter la question, et votre réponse commentée.Pour vous aider, vous trouverez la syntaxe pour SQL sur internet. Toute commande SQL se termine par un ';'. Par exemple, vous pourrez consulter la syntaxe sur : http://wwwlsi.supelec.fr/www/yb/poly_bd/sql/poly_59.html.PRELIMINAIRE : Connectez-vous sur jungle et tapez : jungle$ . /usr/local/oracle/etc/oracle-env (attention au <point> <espace> au début)Démarrez SQL*Plus par la commande : jungle$ sqlplus / (attention sqlplus <espace> <slash>). Vous êtes prêts à travailler.PARTIE 1 : Définition de donnéesLe langage SQL offre un certain nombre de commandes dites de définition qui permettent de définir et gérer une base de données. Cette première partie a pour objectif de présenter et manipuler cet ensemble de commandes. Pour cela, un ensemble de directives vous est proposé. Dans le cadre de notre TP, nous considérerons une base données gérant l'aéroport Charles De Gaulle.
Cette Base de Données contient: •la liste du personnel (pilote/hotesse/stewart) •la liste des destinations : villes d'europe, avec distance et décalage horaire •la liste des compagnie de vol •la liste des avions (type/compagnie/vitesse/capacité/équipage nécessaire) •la liste des vols (avion/destination/jour) : pour plus de facilité, nous n'avons considérons que 3 jours de vols. Soit 40 vols différents•la liste des réservations (vol/nom/prix) : le nombre de réservations possible en 40 vols différents en fonction de la capacité de chaque avion donnant des chiffres énormes (de l'ordre de 6000 reservations), nous ne considérerons que 1124 réservations réparties dans les vols (soit 1/6°) •La liste des équipages par vol : la répartition de l'équipage à été faite équitablement en fonction de la capacité d'équipage de chaque avion. (min 2 pilotes - 3 pour une capacité de 11 -, le reste en stewart et hotesse) .
Voici le schéma correspondant de votre base :
Personnel(PersonnelId, Nom, Naissance, HeureDeVol, Type)
Destination(DestinationId, Ville, Pays, Distance)
Compagnie(CompagnieId, Nom)
Avion(AvionId, CompagnieId, Nom, Vitesse, Equipage)
Vol(VolId, AvionId, DestinationId, Depart (date))
Reservation(ReservationId, VolId, Nom, Prix, Reduction)
Equipage(VolId, PersonnelId)
Tableau 1 : Base Aeroport1.Création des relations :
a) Créer quatre des septs relations de la base ci-dessus :
Il vous est demandé de créer les relations suivantes :•Personnel•Equipages•Avion•Vol•DestinationLes types disponibles sont : number(n), char(n), date, number(m,n)Il ne vous est pas demandé de mettre les clés primaires, les indexes, ni les clés étrangères.b) Vérifiez vos créations :DESC <nom_table>
2.Modification du schéma de la base de données
a) Modifier le type des attributs
:Il vous est demandé de modifier les types précédemment définis : Vitesse devient number(6) , Nom devient char(10)Peut-on changer un type en diminuant sa taille ?b) Ajouter un attribut à une relation :
En particulier, il vous est demandé:(i)d'ajouter un attribut Capacite pour les avions;(ii)d'ajouter l'attribut Decalage pour les destinations (correspondant au décalage horaire avec la france.c) Problème des Valeurs Nulles : not null/ null
Un attribut d'une table déclaré "not null" doit nécessairement être renseigné lors de l'insertion d'un tuple. Il vous est demandé d'identifier les attributs répondant à ce critère et de modifier votre schéma en conséquence. Utiliser la commande ALTER TABLE.3.DEFINITION DES CLéS :
1.Clés primaires :
Chacune des relations de votre base de données doit contenir des informations définies de façon unique. A cette fin, les clés primaires doivent être définies.Définissez les clés primaires des relations de la question 1.a.2.Clés étrangères :
Des attributs d'une relation font parfois référence à une valeur particulière d'une autre relation, nous appelons ces attributs des clés étrangères. (ex : l'identifiant d'un vol contient l'identifiant de l'avion qui est une clé étrangère pour la relation vol, vers la relation Avion). Précisez pour l'ensemble des relations de la question 1.a. les clés étrangères possibles.4.Insertion de données
Il vous est demandé d'insérer un jeu de données cohérent dans vos relations (un ou deux tuples par relation)Exemple de données insérées dans la relation Avion: (1, 1, 'A320', 150, 864, 7);Est-il possible d'ajouter un attribut à une relation comprenant déjà des données? Si oui quelles sont les conditions à respecter?Est-il possible de changer la taille d'un attribut d'une relation contenant des données? Si oui, quelles sont les conditions à respecter?
5.Mise à jour de Relations
Il vous est demandé de :·mettre en majuscule les noms des Avions(fonction UPPER)·mettre en minuscule les noms des villes Destinationde plus de 1000 kms de distance (fonction LOWER)·ajouter 1 heure de décalage aux Destination non francaises.5. Suppression de données Il vous est demandé de :(i)supprimer les avions dont l'équipage est de 5 personnes;(ii)supprimer les vols dont la date est inférieur au 1 janvier 2005.6. Chargement massif des données
Afin d'éviter l'insertion "un à la fois" des tuples dans les relations, nous allons utiliser la commande de chargement à partir d'un fichier.Pour ce faire, créer un fichier nomfichier.ctlqui contient les définitions suivantes: LOAD DATAINFILE *APPEND INTO TABLE NomTableFIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”’ [ATTENTION au " et au ' après le BY, c'est ‘ puis “ puis ‘](NomAtt1, NomAtt2, ..., NomAttn)BEGINDATA1,"AAAA",......,"BBBB "2,"CCCC", .....,"DDDD"Attention, vous ne pouvez pas mettre de valeurs nulles dans ce type de fichier. Tous les champs doivent être renseignés.Puis taper la commande suivante : sqlldr userid=/ control = fichier.ctl log = fichier.logpar ex : sqlldr userid=/ control=aeroport.ctl log=aeroport.logc) Visualiser le résultat du chargement en consultant le fichier .log par ex: > more aeroport.logd) Vérifier sous SQL*Plus, que vos données ont bien été insérées dans la table désirée. sqlplus /select * from Avion;e) Procéder à un chargement plus massif de toutes vos tables.______________________________________________________________________ANNEXESQL*PLUS est l'interface de base d'Oracle. Elle est composée des commandes de SQL et de SQL*PLUS. Les commandes SQL permettent de définir et manipuler les données. Les commandes SQL*PLus permettent de formater les résultats, établir des options, éditer et stocker les commandes SQL, …