Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

Bases de données – ING1 TD 11 : Optimisation de requêtes - Corrigé Durée : 1h30

Bases de données – ING1 TD 11 : Optimisation de requêtes - Corrigé Durée : 1h30 Le but de ce TD est de vous apprendre à : - calculer le coût d'exécution d'une requête SQL, - construire des arbres algébriques optimisés en utilisant les heuristiques de restructuration. Exercice 1 On considère les deux relations suivantes et leurs données : Train(NoTrain, NoWagon) Wagon(NoWagon, TypeWagon, PoidsVide, Capacité, Etat, Gare) Relation Taille Longueur d'un enregistrement Train Wagon 60.000 200.000 10 octets 30 octets Attribut Nombre de valeurs possibles Longueur NoTrain NoWagon TypeWagon 2.000 200.000 200 4 octets 6 octets 2 octets Et deux arbres algébriques : TypeWagon TypeWagon NoTrain =4002 Train Wagon Train NoTrain =4002 Wagon NoWagon = = (a) (b) 1. Montrer que les arbres syntaxiques (a) et (b) donnent le même résultat. 2. Pour chaque arbre syntaxique, donner le volume de données manipulées. 3. Ecrire les requêtes SQL équivalentes. Réponse 1. Les 2 arbres permettent de trouver les types de wagon du train n° 4002 2. Pour chaque arbre syntaxique, donner le volume de données manipulées. a) Arbre non optimisé : La table résultat de la jointure naturelle comporte 60000 lignes de 34 octets (10+30-6) puisqu’un wagon se trouve au plus dans un train, et l'attribut NoWagon est représenté une seule fois dans la table résultat. La table résultat de la condition donne à peu près 30 lignes (il y a en moyenne 60000 / 2000 wagons par train) de 34 octets. Donc : Après la jointure naturelle : 60.000 lignes x (10 + 30 - 6) octets = 2.040.000 octets Après la restriction : (60000 / 2000) lignes x 34 octets = 1.020 octets Après la projection : 30 lignes x 2 octets = 60 octets b) Arbre optimisé : La table résultat de la sélection donne 30 lignes de 10 octets. La table résultat de la projection donne 30 lignes de 6 octets. La table résultat de la jointure naturelle donne 30 lignes de 30 octets. Donc : Après la restriction : (60000 / 2000) lignes x 10 octets = 300 octets Après la première projection : 30 lignes x 6 octets = 180 octets Après la jointure naturelle : 30 lignes x 30 octets = 900 octets Après la deuxième projection : 30 lignes x 2 octets = 60 octets Il est clair que le volume de données manipulées dans le deuxième arbre est beaucoup plus petit. 3. Ecrire les requêtes SQL équivalentes. a) SELECT TypeWagon FROM Train T , Wagon W WHERE T.NoWagon = W.NoWagon AND NoTrain = 4002; b) SELECT TypeWagon FROM Wagon WHERE NoWagon IN (SELECT NoWagon FROM Train WHERE NoTrain = 4002); Ou SELECT TypeWagon FROM Wagon W, (SELECT NoWagon FROM Train WHERE NoTrain = 4002) T1 WHERE W.NoWagon = T1.NoWagon; Exercice 2 Soit le schéma relationnel de la Société Française d’Archéologie (fictive) : Objet (num-obj, type, num-musée) Musée (num-musée, nom) Publication (num-pub, titre, date, éditeur) Auteur (num-aut, nom, prénom) Coopération (num-aut, num-pub) Référence (num-pub, num-obj) Cette base gère des objets archéologiques et des publications sur ces objets. Les clés primaires sont soulignées. Les clés étrangères sont en italique. Soit la requête SQL suivante : SELECT P.titre, P.date FROM Publication P, Auteur A, Coopération C, Référence R, Objet O, Musée M WHERE A.nom = ‘Vieille’ AND A.prénom = ‘Pierre’ AND A.num-aut = C.num-aut AND C.num-pub = P.num-pub AND P.éditeur = ‘Éditions archéologiques modernes’ AND P.num-pub = R.num-pub AND R.num-obj = O.num-obj AND O.type = ‘Mosaïque’ AND O.num-musée = M.num-musée AND M.nom = ‘Louvre’ Question 1. Que fait cette requête ? Question 2. Proposez deux arbres algébriques différents pour exécuter cette requête. Le premier arbre sera le pire possible et le second sera optimisé au mieux en utilisant les heuristiques de restructuration algébrique. Réponse 1. Cette requête permet de trouver le titre et la date d'une publication écrite par l'auteur Pierre Vieille, publiée par l'éditeur ‘Éditions archéologiques modernes’ et qui parle d'objets de type mosaïque exposés au Louvre. Réponse 2. Arbre non optimisé : P.titre, P.date A.nom = ‘Vieille’ A.prénom = ‘Pierre’ P.éditeur= ‘Éditions archéologiques modernes’ O.type = ‘Mosaïque’ M.nom = ‘Le Louvre’ A.num-aut = C.num-aut P.num-pub = R.num-pub C.num-pub = P.num-pub R.num-obj = O.num-obj O.num-musée = M.num-musée O M R P C A Arbre optimisé : P.titre, P.date A.nom = ‘Vieille’ and A.prénom = ‘Pierre’ P.éditeur= ‘Éditions archéologiques modernes’ O.type = ‘Mosaïque’ M.nom = ‘Le Louvre’ A.num-aut = C.num-aut P.num-pub = R.num-pub C.num-pub = P.num-pub R.num-obj = O.num-obj O.num-musée = M.num-musée O M R P C A O.num-obj, O.num-musée M.num-musée P.num-pub, P.titre, P.date A.num-aut uploads/Voyage/ 11-optimisation-requete-corrige.pdf