Ecole Supérieure en Informatique de Sidi Bel Abbes
Cycle Supérieur
Fiche TD4 : Bases de Données
er
3AI : 1 Cycle Supérieur
Exercice 1 : On souhaite gérer les informations relatives à une librairie à l’aide d’un SGBD Relationnel.
Le schéma de la base est le suivant
Œuvre (ISBN, titre, annéePub, nbPages)
Auteur ( idA, nom, prénom, annéeNais, nationalité)
Ecrit( ISBN, idA )
Exemplaire ( ISBN, numEx, dateLivraison, dateVente)
Soit la requête R1 exprimé en SQL :
SELECT A.nationalité
FROM Auteur A, Ecrit E, Exemplaire X
WHERE A.idA=E.idA
AND E.ISBN = X.ISBN
AND (X.ISBN, X.numEx, X.dateVente) NOT IN ( SELECT Y.ISBN, Y.numEx, Y.dateLivraison
FROM Exemplaire Y) ;
1) Que retourne la requête R1 ?
a. La nationalité des auteurs dont les exemplaires de livres n’ont pas encore été livrés.
b. La nationalité des auteurs dont tous les exemplaires de livre ont été vendu.
c. La nationalité des auteurs dont au moins un exemplaire de livre a été vendu.
d. La nationalité des auteurs dont au moins un exemplaire de livre a été vendu le jour
de sa livraison.
e. La nationalité des auteurs dont au moins un exemplaire de livre a été vendu avant
d’être livré.
f. Autre : ………………………………………………………………………………………………………………….
2) Donner un plan d’exécution P1 de la requête R1 ;
3) Le plan d’exécution P2 ci-dessous est-il optimal ? si oui justifiez, si non donnez un plan optimal. Le
plan d’exécution P2 ci-dessous est-il optimal ? si oui justifiez, si non donnez un plan optimal.
nationalité= ‘Algérienne annéePub < 2010 dateVente=’2017-10-10’
Exercice 2 : Soit le schéma relationnel représentant un championnat de course cycliste.
Equipes (EquipeID, Nom, pays)
Coureurs (CoureurID, Nom, Nationalité, #EquipeID)
Etapes (EtapeID, Départ, Arrivée, Kilomètres)
Temps (#CoureurID, #EtapeID, Temps)
Répondez aux questions suivantes (SQL) :
1. Créer une vue CoureursAlgériens contenant le : le numéro et le nom des coureurs dont la nationalité est
algérienne. Cette vue doit être modifiable et vérifiable.
2. Créer une vue EquipeCoureurs contenant le : le numéro et le nom des équipes et le numéro et le nom des
coureurs. Cette doit être non-modifiable.
3. Exprimer en SQL les requêtes suivantes en utilisant que les vues CoureursAlgériens , EquipeCoureurs :
Donner le nombre des équipes qui ont au moins un coureur algérien.
Donner les équipes qui ont plus de deux coureurs algériens.
4. Donner le script SQL qui permet de réaliser la transaction suivante :
Désactiver le mode de validation automatique
Verrouiller l’accès à la table coureurs en mode écriture
Dans la table coureurs, Insérer le tuple (15, karim, algérie, 1)
Mettre à jour le coureur numéro 2 en modifiant sa nationalité (algérien)
Valider la transaction.
Déverrouiller l’accès à la table coureurs
Réactiver le mode de validation automatique
Exercice 3 : Considérez l’ordonnancement de transactions suivant :
1. Cet ordonnancement est-il sérialisable? Si oui, donnez le ou les ordonnancements séquentiels
équivalents.
2. Cet ordonnancement est-il sérialisable par permutation? Justifiez votre réponse.
3. Expliquez la convergence ou la divergence des résultats des tests de sérialisabilité effectués en a) et
b). Aurait-on eu le même résultat pour d’autres opérations en t3 et t14 ?
Corrigé
Exercice 1 :
1) Que retourne la requête R1 ?
a. La nationalité des auteurs dont les exemplaires de livres n’ont pas encore été livrés.
b. La nationalité des auteurs dont tous les exemplaires de livre ont été vendu.
c. La nationalité des auteurs dont au moins un exemplaire de livre a été vendu.
d. La nationalité des auteurs dont au moins un exemplaire de livre a été vendu le jour
de sa livraison.
e. La nationalité des auteurs dont au moins un exemplaire de livre a été vendu avant
d’être livré.
f. Autre : ………………………………………………………………………………………………………………….
Solution :
2) Donner un plan d’exécution P1 de la requête R1
3) Le plan d’exécution P2 ci-dessous est-il optimal ? si oui justifiez, si non donnez un plan optimal. Le
plan d’exécution P2 ci-dessous est-il optimal ? si oui justifiez, si non donnez un plan optimal.
Exercice 2 :
1. Create or Replace View CoureursAlgériens As select coureursID, nom from Coureurs
Where nationalité=’algérie’
With check option;
2. Create or Replace Algorithm=Temptable View EquipeCoureurs (EquipeID, nomE,CoureursID, nomC)
As select e.EquipeID, e.nom, c.coureursID, c.nom from equipe e join coureurs c on
e.EquipeID=c.EquipeID;
3. Select count(distinct e.EquipeID) from EquipeCoureurs e join CoureursAlgériens c
on e.coureursID= c.coureursID
Select e.EquipeID, e.nomE from EquipeCoureurs e join CoureursAlgériens c
on e.coureursID= c.coureursID
Group by e.EquipeID having count (*) >2;
4. Set autocommit=0;
Lock table coureurs write;
Insert into coureurs values (15, ‘karim’,’algérien’,1);
Update coureurs set nationalite=algérien where coureursID=2;
Commit ;
Unlock tables ;
Set autocommit=1 ;
Exercice 3 :
1. En exécutant les transactions séquentiellement, on obtiendrait les résultats suivants:
Ordonnancement: T1 T2 T3
Affiche : A=5 et B=10
État final: A=10 et B=5
Ordonnancement: T1 T3 T2
Affiche : A=10 et B=5
État final: A=10 et B=5
Ordonnancement: T2 T1 T3
Affiche : A=5 et B=10
État final: A=10 et B=5
Ordonnancement: T2 T3 T1
Affiche : A=5 et B=10
État final: A=15 et B=0
Ordonnancement: T3 T1 T2
Affiche : A=15 et B=0
État final: A=15 et B=0
Ordonnancement: T3 T1 T2
Affiche : A=10 et B=5
État final: A=15 et B=0
L'ordonnancement donne les mêmes résultats que les ordonnancements séquentiels
(T1 T2 T3) et (T2 T1 T3). Par conséquent, l'ordonnancement est sérialisable
2) Considérons uniquement les opérations non-permutables entre les transactions T1 et T2:
1. Écrire(a,A) en t2 suivi de Lire(A,a) en t9 (sens T1 vers T2)
2. Lire(B,b) en t10 suivi de Écrire(b,B) en t15 (sens T2 vers T1)
Ces opérations non-permutables sont faites dans des directions opposées, donc l'ordonnancement n'est pas
sérialisable par permutation.
3. Le fait que l'ordonnancement est sérialisable est uniquement dû au choix des opérations en t3 et t14 (ces
opérations n'affectent pas la valeur de A et B). Si on avait choisi d'autres opérations, la sérialisabilité n'aurait pas
nécessairement été respectée.
