TP SQL - Georges Gardarin

Travaux pratiques : SQL sous Oracle

Attention :
CE TP DE MANIPULATION SE COMPOSE DE DEUX PARTIES.
La première partie s'effectue sur papier et à l'écran en environ 1h30.
Elle concerne la définition en SQL de tables relationnelles, et des
manipulations possibles sur le schéma de données.
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 AU
DÉBUT)
Démarrez SQL*Plus par la commande : jungle$ sqlplus / (attention
sqlplus ). Vous êtes prêts à travailler.

PARTIE 1 : Définition de données
LE 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 Aeroport


1. 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
. Destination


Les 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

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 Destination de 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.ctl qui contient les
définitions suivantes:
LOAD DATA
INFILE *
APPEND
INTO TABLE NomTable
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' [ATTENTION au
" et au ' après le BY, c'est ' puis " puis ']
(NomAtt1, NomAtt2, ..., NomAttn)
BEGINDATA
1,"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.log
par ex : sqlldr userid=/ control=aeroport.ctl log=aeroport.log

c) Visualiser le résultat du chargement en consultant le fichier .log
par ex: > more aeroport.log

d) 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.
______________________________________________________________________

ANNEXE
SQL*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, ...

Commandes SQL*PLUS
SET pause ON/OFF : permet d'effectuer un défilement page par page de
l'écran.
SET LINESIZE 132 : permet d'augmenter le nombre de caractères par
ligne
SET PAGESIZE 60 : permet d'augmenter le nombre de lignes par page
SET TIMING ON : donne le temps de calcul de chaque commande SQL



La dernière commande SQL exécutée est stockée dans le buffer principal de
l'environnement SQL*PLUS. Plusieurs commandes de manipulation de ce
buffer sont possibles :
List : affiche le contenu du buffer.
List n : affiche la n-ième ligne du buffer.
List m n : affiche les lignes m à n du buffer.
Save : sauve le buffer dans un fichier.
Get : restitue le fichier dans le buffer.
Start : exécute le contenu du fichier
Run : exécute le contenu du buffer.
Change /// : remplace une chaîne du buffer par
une autre.
Clear buffer : vide le contenu du buffer

Commandes SQL
COMMIT : permet d'enregistrer les relations dans la base.
ROLLBACK : permet de défaire toutes les opérations effectuées depuis
le dernier commit.



Les Types de oracle
Char(n) : chaine de n caractères (n?240)
Number : nombre
Number(w,d) : nombre de longeur w avec d chiffre décimaux
Long : longue chaine de caractère ?65 535
Date :
Integer
Float
PARTIE 2 :

Manipulation des données

Tout d'abord, téléchargez