Travaux Dirigés de Fonctionnement d'un SGBD

Master MIAGE 1

Université de Nice Sophia-Antipolis

année universitaire 2009-2010

Sujet 2 (Révisions : utilisation d'un SGBD 2/3)


Ce sujet est proposé par Isabelle Mirbel et Pierre Crescenzo, inspirés par Richard Grin.

Vous pouvez accéder au support de CM et au sujet des autres TD. Vous pouvez voir ces supports d'enseignement intéressants ou cet autre.

Sauf cas particulier ou indication contraire, il est conseillé après toute commande de modification, ou tout groupe de commandes de modification qui forment une transaction, de vérifier que tout s'est bien passé et de faire ensuite un commit. Si vous ne faites pas cela, le premier rollback annulera tout ce que vous avez fait depuis le début de la transaction.

C'est une bonne habitude à prendre, notamment lorsque vous travaillez avec une base de données partagée. En effet, commit ou rollback libère les verrous positionnés pendant la transaction.

Modifications de données un peu plus compliquées
  1. Enregistrez les participations suivantes :
    • dans le projet Sports (en cherchant auparavant, et en notant, le matricule des employés et le code du projet), celles de Chatel et de Mercier ;
    • dans le projet Qualité (en utilisant une sous-requête pour trouver le matricule des employés et le code du projet Qualité), celles de Chatel et de Leroy.
  2. Enregistrez les nominations suivantes :
    • Mercier devient chef du projet Sports ;
    • Chatel est désormais trésorier de tous les projets auxquels il participe.
  3. Les départements localisés à Grenoble sont supprimés et leurs employés sont réaffectés au département 40. Mettez à jour toutes les tables concernées.
Transactions et contraintes d'intégrité
Commencez une nouvelle transaction. Exceptionnellement, pendant cet exercice, ne faites aucun commit car tout devra être annulé à la fin de l'exercice.
  1. Supprimez tous les employés sauf ceux du département 10. Si vous n'y parvenez pas, expliquez pourquoi. Ensuite, commencez par supprimer les participations aux projets des employés des départements de numéros différents de 10 avant de supprimer ceux-ci.
  2. Affichez les nom, poste, département, salaire et commission de tout le personnel.
  3. Annulez la transaction en cours. Relancez ensuite la requête de la question précédente et vérifier l'effet de l'annulation.
Contraintes d'intégrité
Commencez une nouvelle transaction. Exceptionnellement, pendant cet exercice, ne faites aucun commit car tout devra être annulé à la fin de l'exercice.
  1. Listez les informations sur les employés du département 10.
  2. Essayez de supprimer le département 10 (pas ses employés, le département lui-même). Cela ne devrait pas fonctionner. Expliquez ce qui se passe. Si vous ne l'avez pas déjà fait, profitez-en pour étudier les instructions et, en particulier les contraintes, du fichier inibase.sql qui a permis la création des différentes tables. Puis relancez l'exécution du fichier contraintes.sql.
  3. Modifiez la définition des tables pour que la suppression du département 10 soit autorisée et entraîne automatiquement la suppression des employés de ce département.
  4. Supprimez le département 10 et vérifiez ce qui s'est passé pour les employés de ce département.
  5. Annulez la transaction et rechangez les définitions de contrainte pour que le comportement soit le même qu'au début de l'exercice.
Différer une contrainte d'intégrité
  1. Différez la contrainte d'intégrité de clé étrangère de la table des employés vers celle des départements (sur la colonne dept).
  2. Donnez une suite de modifications des données qui fonctionnera si la contrainte est différée et ne fonctionnera pas sinon. Testez-la.
  3. Annulez le fait que la contrainte soit différée.
Interrogations sur une seule table
  1. Listez tous les postes, en n'affichant chaque poste qu'une seule fois.
  2. Considérez qu'une commission NULL correspond à un employé qui ne touche aucune commission.
    1. Listez le nom des employés dont le salaire est inférieur à la commission.
    2. Listez le nom des employés dont le salaire est supérieur à la commission.
    3. Listez le nom des employés qui ne touchent pas de commission.
  3. Listez le nom des employés dont la deuxième lettre est E.
  4. Listez le numéro des départements qui ont au moins un employé qui touche une commission (au sens où la commission n'est pas NULL).
  5. Listez le nom des employés qui touchent entre 2000 et 8000 €.
Jointures
  1. Listez le produit cartésien des noms de département par les lieux.
  2. Listez le nom des employés et le lieu où chacun travaille.
  3. Listez le lieu des départements (sans le nom des départements) dans lesquels au moins un employé touche une commission, en utilisant une jointure.
  4. Listez le nom des employés, suivi de celui de leur supérieur direct ou d'une case vide quand ils n'en ont pas.
  5. Listez le nom des employés qui ont un supérieur direct, suivi du nom de celui-ci.
  6. Listez le nom des employés qui sont le supérieur d'au moins un autre employé.
  7. Listez le nom des projets où sont inscrits des employés des départements localisés à Toulouse.
Sous-interrogations
  1. Listez le lieu des départements dans lesquels des employés touchent une commission, en utilisant cette fois une sous-interrogation.
  2. Listez les nom et lieu des départements dans lesquels il y a au moins un ingénieur. Avec une jointure ? Avec une sous-interrogation ?
  3. Listez les nom et lieu des départements dans lesquels il n'y a aucun ingénieur. Avec une jointure ? Avec une sous-interrogation ?
  4. Listez les matricule et nom des employés qui ne sont le supérieur de personne. Il se pourrait que vous ayez une réponse étonnante. Si c'est le cas, faites afficher le nom de chaque employé avec le matricule de son supérieur et faites appel à votre logique. Corrigez votre requête initiale.
Sous-interrogations avancées
  1. À l'aide d'une sous-interrogation synchronisée, listez le nom des employés qui gagnent moins de 50 % du salaire de leur supérieur direct.
  2. Listez le nom des employés qui gagnent plus que tous les commerciaux.
  3. Listez le nom des employés qui gagnent plus que tous les commerciaux de leur département. Proposez deux méthodes : une affichant le nom des employés dont le département ne comprend pas de commerciaux et une autre ne l'affichant pas. Laquelle vous semble le mieux répondre à la question initiale ?
  4. En utilisant une sous-interrogation à plusieurs colonnes, listez le nom des employés ayant le même poste et le même supérieur que Berger.

XHTML 1.1 validé ! CSS 2 validé ! GNU's Not Unix Debian GNU/Linux Optimisé pour tout navigateur ! Contre les brevets logiciels !

URI : http://www.crescenzo.nom.fr/2009-2010/MMIAGE1-FSGBD-TD02/ conforme à XHTML 1.1 et à CSS 2.1
Contact et maintenance : Pierre Crescenzo
Création : 2009/09/21
Dernière modification : 2009/10/16