Pr.
Imane DAOUDI ENSEM 2021-2022 1ère GI
TP SQL : CREATION MODIFICATION DE SCHEMA
----------------------------------------------------------------------------------------------------------------------------------------
PARTIE 1 : DEFINITION DE DONNEES
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 compagnies 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
réservations), 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)
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 <nom_table>
1
Pr. Imane DAOUDI ENSEM 2021-2022 1ère GI
2. MODIFICATION DU SCHEMA DE LA BASE DE DONNEES
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 :
a) 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.
b) 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 françaises.
5. SUPPRESSION DE DONNEES
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.
2
Pr. Imane DAOUDI ENSEM 2021-2022 1ère GI
---------------------------------------------------------------------------------------------------------------------------
PARTIE 2 : MANIPULATION DE DONNEES
Pour chacune des requêtes suivantes, écrivez la requête en SQL correspondante. Nous vous
demandons de noter la requête SQL, de formuler la requête et de noter les résultats obtenues lors de
l'exécution.
1-Donnez la vitesse d'un A320.
2-Donnez les noms de tous les pilotes.
3-Déterminez tous les pays différents existant dans la base.
4-Donnez la date de naissance de MICHEL.
5-Donnez toutes les destinations vers lesquelles à volé MICHEL, classées par ordre alphabétique.
6-Donnez tous types d'avions effectuant la liaison avec Londres, classé du plus rapide au plus lent
7-Donnez la liste des hôtesses avec qui COHEN a volé, classée par âge.
8-Combien de passagers différents HUBERT va-t-il transporter entre le 10 et le 13 août?
9-Donnez pour chaque hôtesse le nombre de passagers avec qui elle aura volé pendant la journée du 12
août.
10-Parmi tous les vols effectués le 10 août, donnez celui dont la moyenne d'âge de l'équipage est la
plus basse.
11-Donnez le nom de toutes les compagnies possédant au moins un appareil de même type que la
compagnie d'identifiant 3.
12-Donnez pour chaque compagnie la liste des avions qu'elle possède, classée par ordre de capacité
croissante.
13-Donnez la liste des personnes prenant un avion vers une destination ayant un décalage horaire de -
1, et ne possédant pas de réduction.
14-Classez les destinations vers lesquelles a volé COHEN selon le nombre de gens qu'il a transporté
labas.
15-Donnez le nom du pilote ayant piloté, entre le 10 et le 13 aout, l'avion le plus rapide.
16-Donnez toutes les hôtesses plus expérimentées (c.à.d avec plus d’heures de vol) que SOPHIE.
17-Donnez le nom de toutes les personnes ayant une réservation sur un vol où l’employé le moins
expérimenté (c.à.d. avec le moins d’heures de vol) participe.
18-Donnez pour chaque hôtesse la liste des pilotes avec lesquels elle a volé, classée par nombre
d'heures de vol.
19-Donnez toutes les destinations vers lesquelles SOPHIE a voyagé en étant la personne de l’équipage
la plus jeune.
__________________________________________________________________________________
