Institut National des Sciences Appliquées et de Technologie de Tunis
Filière : GL2
Matière : Base de données avancées
TD N°2
Exercice 1 :
On considère le schéma de base suivant ou les clés primaires sont soulignées, et les clés
étrangères sont précédées d’un #.
Ville ( noville , nomville , codepostal )
Train ( notrain , date_dep , date_arr , #noville_dep , #noville_arr )
Passager ( nopassager , Nom , #notrain , #date_dep )
Tarif ( #notrain, #date_dep, categorie, prix )
Distance ( #noville1, #noville2, nbkm )
Commentaires : noville_dep, noville_arr, noville1 et noville2 se réfèrent a noville de la table
Ville.
Questions :
(a) Donner les commandes SQL pour créer les tables ci-dessus, en spécifiant les clés
primaires et étrangères, et en assurant que :
• Les prix de la table Tarif et les distances entre les villes soient des nombres positifs
• Pour un numéro de train, le numéro de la ville d’arrivée soit différent de celui la
ville de départ.
(b) Définir un trigger pour assurer qu’à l’insertion de données dans la table Train la
différence entre la date d’arrivée et la date de départ d’un train ne dépasse pas 8 heures si la
distance entre la ville de départ et la ville d’arrivée ne dépasse pas 300Km.
Exercice 2 :
Considérer les schémas de relations suivants :
EMP (N, S, E, P, Dp) // N est un employé dans l’équipe E, il a un salaire
mensuel S, et participe à un projet P à partir de la date
Dp.
CHEF (C, E, P ,Dc) // L’employé C est le chef de l’équipe E, qui crée un
projet P à la dateDc.
Sur les schémas EMP et CHEF, on considère les contraintes suivantes:
(i) Chaque équipe a un seul chef, et chaque chef est le chef d’une seule équipe. Chaque
projet a une seule date de création.
(ii) Chaque employé a un seul salaire mensuel, et est dans une seule équipe. La date de
début de participation d’un employé a un projet est unique.
(iii) Une équipe qui figure dans la table EMP doit exister dans la table CHEF. Aussi un
projet qui figure dans la table EMP doit exister dans la table CHEF.
Questions :
a. Donner les clés des schémas EMP et CHEF.
b. Créer les tables EMP et CHEF.
c. En plus des contraintes ci-dessus, on doit assurer que la date de participation d’un
employé à un projet doit être ultérieure ou égale à la date de la création du projet.
Définir un trigger pour renforcer cette contrainte.
Exercice 3 :
Le schéma relationnel de la base de données « requests » est donné ci-dessous.
Facility (facNo, facName)
Customer (custNo, custName, custContact, custPhone, custAddress, custCity,
custState, custZip)
EventRequest (eventNo, facNo#, custNo#, dateHeld, dateReq, dateAuth, status,
estCost, estAudience)
1. Écrire une procédure stockée de nom custProfile qui affiche toutes les caractéristiques
du client dont le numéro (custNo) est passé en paramètre de la procédure.
2. Ajouter à la procédure custProfile une exception si le numéro de client n’existe pas
dans la table
Customer.
3. Écrire un bloc PL/SQL anonyme qui affiche les informations (eventNo, facName,
custName, estCost, status) triées par coût estimé (estCost) décroissant.
4. Écrire un déclencheur de nom eventCheck qui vérifie que, pour chaque nouvelle
demande (EventRequest) ou modification de demande, la date d’autorisation dateAuth
existe. Si ce n’est pas le cas, interrompre l’exécution avec le message « Erreur
d’autorisation ». Sinon, vérifier que la date d’autorisation dateAuth est supérieure ou
égale à la date de demande dateReq et que la date tenue dateHeld est supérieure ou
égale à la date d’autorisation dateAuth. Si ce n’est pas le cas, interrompre l’exécution
avec le message « Erreur de date ».
