PSY38X2 : Traitement de données en Psychologie - TD No 4

Utilisation d'un SGBD relationnel - Troisième partie

 

1) Reprendre le travail réalisé dans la fiche de TD No 3

Pendant la session de travail, vous utiliserez de préférence votre compte sur le serveur Samba comme support de la base de données,.

 

Si votre base de données se trouve sur votre compte sur le serveur, vous pouvez l'ouvrir à l'aide d'un double-clic. après vous être connectés sur votre compte. Sinon, vous devez copier la base de données ONG.MDB, dans l'état où elle se trouve à la fin de la fiche de TD N3, sur le disque de travail

 

N.B. Si vous n'avez pas de base de données ONG.MDB convenable sur votre compte ou sur une disquette, copiez la base W:\PSY3\TD ACCESS\ONG-FinTD2.MDB et ouvrez l'exemplaire recopié.

 

2) Pourquoi des requêtes ?

Dans la fiche de TD N2, nous avons vu comment interroger la base de données à l'aide de filtres et de tris. Cependant, ces outils sont trop limités pour permettre de traiter tous les problèmes de manipulation de données que nous pouvons nous poser. L'utilisateur final peut construire des filtres à partir des objets existant dans la base de données : tables, formulaires. Cela lui permet d'extraire l'information dont il a besoin, sans connaissance particulière de la structure de la base de données. Mais encore faut-il que le concepteur ait construit un objet (table ou formulaire) à partir duquel on peut définir le filtre.

 

De plus, certains filtres sont destinés à être exécutés fréquemment (par exemple, afficher la liste des intervenants, triée par ordre alphabétique). Il est alors pénalisant de ne pas pouvoir enregistrer la conception du filtre.

 

Les requêtes "sélection" sont des outils destinés, comme les filtres, à extraire et à trier les données présentes dans la base. Comme nous le verrons ci-dessous, il existe d'autres types de requêtes, répondant à d'autres besoins. Au contraire des filtres, les requêtes sont enregistrées comme des objets à part entière, et peuvent être ré-exécutées à volonté. Cependant, leur conception relève du concepteur de la base de données, et non du simple utilisateur.

 

Pour rédiger correctement une requête, il faut en effet :

3) Créer une requête simple

Nous souhaitons disposer d'une liste des bénévoles, triée par ordre alphabétique des noms et des prénoms. Dans cette liste, devront figurer le code, le nom, le prénom et la profession des intéressés.

4) Exemple de requête utilisant plusieurs tables

Nous souhaitons, par exemple, avoir des informations concernant les intervenants au Burkina Faso.

4.1 Questions à se poser pour rédiger une requête

4.1.1 Quels sont les champs dont on veut avoir le contenu ?

Nous voulons consulter les Code, Nom, Prénom des intervenants et le Site d'affectation, sans oublier le Pays, nécessaire pour rédiger le critère de sélection.

4.1.2 Quelles sont les tables impliquées dans la requête ?

Ce sont d'une part les tables contenant les champs demandés et d'autre part les tables permettant d'établir les liens entre les premières.

Ici, la table Intervenants contient les trois premiers champs, la table Sites les deux derniers. Il faut également prévoir la table Affectations, qui est nécessaire pour faire le lien entre les deux premières.

4.1.3 Comment sont reliées les données ?

Il faut définir les jointures, c'est-à-dire les relations entre les champs des tables ayant même signification. Les relations définies au niveau de la structure de la base de données nous suffiront-elles, ou faudra-t-il en définir d'autres et lesquelles ?

Ici, les liens "de structure" entre les deux champs Code Intervenant et les deux champs Code Site nous suffiront. Nous verrons ci-dessous d'autres exemples nécessitant la définition de relations au niveau de la requête elle-même.

4.1.4 Quels sont les critères de sélection éventuels ?

Ici, la sélection porte évidemment sur le pays.

4.2 Réalisation de la requête

Exercices

Remarque

Une requête qui met en jeu plusieurs tables ou requêtes doit absolument avoir des jointures. Une requête multitable sans jointures entre les tables donnera des résultats généralement idiots.

Exercice

5) Requêtes paramétrées

Avec Access, on peut de façon simple construire des requêtes dans lesquelles la sélection est faite à partir de valeurs saisies par l'utilisateur au moment de l'exécution. Par exemple, nous voulons rédiger une requête analogue à la requête Intervenants au Burkina, mais dans laquelle l'utilisateur peut spécifier le pays de son choix.

Exercices

6) Champs calculés, requêtes mise à jour

6.1 Champs calculés

Une requête peut contenir des champs calculés à partir d'autres champs présents dans la requête ou de variables d'environnement (telles que la date système, par exemple). Par exemple, la table des interventions contient la date de début d'intervention, et la durée de l'intervention en nombre de jours. Mais il serait également commode de disposer de la date de fin d'intervention.

Exercices

1) Créez une requête donnant la durée des missions en jours, et affichant le nom et le prénom des intervenants dans un même champ (formule : Identité: Nom & " " & Prénom).

2) Les intervenants ont droit à une indemnité couvrant leurs frais de séjour et s'élevant à 200 F par jour. Calculer le montant des indemnités versées pour chaque mission.

 

6.2 Requêtes "mise à jour"

Les requêtes vues jusqu'à présent étaient toutes de type "Sélection". Mais Access permet d'autres types de requêtes. En particulier, on peut faire des modifications systématiques d'une sélection ou de la totalité des fiches d'une table à l'aide de requêtes de type "Mise à jour".

 

Remarque. Si la définition de la requête est erronée, la mise à jour donnera des résultats incorrects, et l'annulation de l'opération sera bien souvent impossible. C'est pourquoi il est préférable de rédiger d'abord une requête sélection, de vérifier qu'elle répond à nos besoins et de la transformer ensuite en une requête "Mise à jour".

 

A titre d'exemple, nous allons réaliser des requêtes mettant en majuscules (resp. en minuscules) le nom des intervenants.

 

Remarque. Si l'on obtient pas le résultat escompté... Détruisez la table Intervenants, copiez puis collez la table la table Intervenants - Copie de Secours et indiquant comme nouveau nom : Intervenants. Rétablissez ensuite les relations entre la table Intervenants et les autres tables.

 

Exercice

Faites une mise à jour de la table Intervenants en mettant tous les noms en minuscules (fonction Minuscule() ou Minuscule$()). Exécutez ensuite alternativement chacune des deux requêtes.

 

7) Spécifier des relations dans une requête

Dans une requête, les données peuvent être reliées par d'autres jointures que celles qui ont été définies au niveau de la structure de la base de données. Considérons, par exemple, le problème suivant :

 

7.1 Faire intervenir plusieurs fois la même table dans une requête

 

7.2 Auto-jointures

Etant donné une intervention, existe-t-il d'autres interventions se déroulant dans le même pays ? Quels sont les intervenants de même nationalité qu'un intervenant donné ?

Pour rédiger des requêtes répondant à ces questions, il faut définir des liens entre deux exemplaires d'une même table. Il s'agit alors d'auto-jointures.

 

7.3 Jointures externes

7.3.1 Les intervenants qui ne sont jamais partis en mission.

La relation qui existe entre ces tables est une équijointure. Seules les lignes des deux tables pour lesquelles les champs joints sont égaux sont incluses, et cette requête ne nous affiche les coordonnées d'un intervenant que dans la mesure où il a participé à au moins une mission.

Pour obtenir la liste de tous les intervenants, il faut spécifier une propriété particulière de la relation entre les deux tables : une jointure externe.

 

La requête nous affiche alors toutes les fiches des intervenants (chaque fiche est au besoin répétée plusieurs fois), le champ Code Intervention restant éventuellement vide.

 

7.3.2 Les intervenants actuellement disponibles

Exercices :

Les intervenants disponibles

Nous voulons définir une requête donnant la liste des intervenants qui sont actuellement disponibles pour une mission.

 

Les problèmes de planning ne sont jamais simples à résoudre avec les outils fournis par un SGBD. Pour résoudre le problème posé, nous allons devoir procéder en deux temps : déterminer d'abord la liste des intervenants actuellement en mission, puis, à l'aide d'une jointure externe, déterminer par différence la liste de ceux qui ne sont pas en mission.

 

La requête En Mission

La requête Disponibles :

Remarque

L'utilisation de jointures externes est problématique lorsque plus de deux tables ou requêtes sont concernées et que l'on doit faire une sélection de fiches "à droite" de la flèche. Par exemple, on souhaite écrire une requête donnant, pour chaque responsable d'intervention, la liste de ses collaborateurs (chaque ligne contient donc le nom du responsable et le nom d'un collaborateur). On désire que les interventions pour lesquelles il n'y a pas d'autre intervenant que le responsable figurent également dans le tableau, et que le responsable ne soit pas indiqué comme collaborateur de lui-même...

 

Exercice

8) Les requêtes avec regroupements

Les champs calculés permettent de réaliser des calculs sur les lignes d'une table. Mais on a souvent besoin de faire des calculs "statistiques" sur les colonnes. Par exemple, le nombre d'intervenants par intervention, la durée moyenne d'une mission ou d'une intervention, etc.

Pour cela, on utilise le menu Affichage-Opérations.

Pour la plupart, les opérations de regroupement proposées n'appellent pas de commentaires. Seule l'opération mérite d'être expliquée : elle permet d'introduire une sélection (sans regroupement) sur les valeurs d'un champ, qui ne sera donc pas affiché. Par exemple, on se limitera aux seules interventions au Mali à l'aide de :

Pays Site
MALI

Exercice

9) Les requêtes Analyse croisée

Les données que nous possédons s'y prêtent mal. Mais, sur des données plus volumineuses, on pourrait souhaiter connaître, par exemple, la durée totale des interventions, par objectif et par pays.

Construire cette requête en se basant sur la table Interventions. Utiliser pour cela l'assistant analyse croisée. Les dialogues ne présentent pas de difficultés, si l'on sait précisément ce que l'on veut obtenir.

 

Objectif

Total de Durée

Bangla-desh

Burkina Faso

Colombie

Guatéma-la

Honduras

Mali
Aide agricole

60

60
Aide médicale

60

60
Aide sinistrés

390

150

120

120
Dispensaire

300

300
Ecole

304

304
Irrigation

120

120
Vaccination

330

300

30

10) Les Etats

Les formulaires sont destinés à la saisie et à la consultation des données à l'écran. Ils peuvent être imprimés, mais ce n'est pas leur destination première, et cela pose parfois des problèmes : comment imprimer correctement un sous-formulaire comportant une barre de défilement ?

Les états, au contraire sont des objets destinés à présenter les données en vue de l'impression. La présentation définie dans un état peut être affichée à l'écran, mais il s'agit d'un aperçu avant impression : il n'est pas possible de modifier des données à l'aide d'un état.

10-1 Un cas simple : des fiches sur les intervenants

On veut produire des fiches reproduisant les informations contenues dans la table Intervenants.

 

Vous devriez obtenir un état ayant l'allure suivante :

10-2 Les différentes étapes de création d'un état

- Faire, sur papier, une maquette du document de sortie à obtenir.

- Faire la liste de toutes les données types nécessaires.

- Rechercher si l'on dispose d'une table ou d'une requête fournissante ces données. Créer la requête si nécessaire. La requête doit posséder comme champs l'ensemble des données types, même si celles-ci n'apparaissent que "rarement" dans l'état. Par exemple, si l'on veut afficher les caractéristiques des interventions par pays, le champ Pays doit apparaître comme champ dans la requête, même s'il n'apparaît que sur 3 ou 4 pages de l'état.

- Créer l'état proprement dit.

Exemple :

On veut créer un état donnant la liste de intervenants et pour chacun d'eux, le lieu de mission actuel éventuel.

Sans être identique à l'état ci-dessous (qui a été réalisé sans assistants), l'état obtenu devrait disposer les informations de la même manière.

10-3 Les états avec regroupements

Très fréquemment, les données à imprimer comportent des parties répétitives, que nous ne souhaitons voir apparaître qu'une seule fois sur l'état, sous forme de "sous-titre". Le problème que nous souhaitons résoudre ici est analogue à celui traité par les formulaires avec sous-formulaires. Mais, la solution mise en oeuvre dans les états est plus simple (et plus puissante) que la solution "sous-formulaire".

 

Nous voudrions définir un état donnant les fiches des interventions avec, pour chacune d'elles, la liste des intervenants qui y ont participé et les sites sur lesquels ils se trouvaient présents. Nous pouvons, dans un premier temps, nous contenter des sites sur lesquels au moins un intervenant a été affecté.

Les interventions seront triées par code d'intervention. Pour chaque intervention, les sites seront triés par ordre alphabétique (ou par code) et, sur chaque site, les intervenants seront triés par nom.

 

Les regroupements. Regrouper les données, c'est effectuer une opération très semblable à une mise en facteurs d'expressions algébriques.

Au lieu de :

V96880 - Chittagong - Durand
V96880 - Chittagong - Duval
V96880 - Dacca - Martin

on écrit :

V96880    
  Chittagong  
   

Durand

Duval

  Dacca  
    Martin

Définition de l'état envisagé

Examinez l'état ainsi produit. Il n'y a apparemment pas moyen d'indiquer à l'assistant que les champs Objectif et Pays Intervention sont de même niveau que le champ Code Intervention, et doivent donc faire partie de la même section de l'état.

 

 

Pour arranger ce genre de choses :

On peut sans trop de mal, obtenir un état ayant l'allure suivante :

 

 

11) Petit résumé des fonctions et de la syntaxe

11-1 La syntaxe :

Les constantes chaînes de caractères sont placées entre guillemets : "Médecin", "Mali"

 

Les constantes de type date sont placées entre dièses : #15 Février 1995#

 

Sauf dans des cas très simples, les noms d'objets créés par l'utilisateur (champs, tables, requêtes, formulaires...) sont placés entre crochets : [Date Début], ...

 

Lorsqu'un objet défini par l'utilisateur doit être spécifié à l'aide d'un "nom composé" (exemple : le champ Code Intervenant de la table Intervenants, qu'il faut distinguer du champ de même nom de la table des Affectations), les deux parties du nom sont généralement séparées par un point d'exclamation : [Intervenants]![Date Début], Le point peut être substitué au point d'exclamation si aucune confusion avec une propriété prédéfinie dans Access n'est possible : si la table MaTable contient des champs Truc et Visible, on pourra écrire MaTable.Truc ou MaTable!Truc, mais on devra écrire MaTable!Visible.

11-2 Les opérateurs

Nous avons pratiqué les opérateurs suivants :

Arithmétiques : + - * /

Chaînes : & Comme

Logiques : <   >   <=   >=   <>   Et   Ou   Pas   Ou_X   Entre et    Est Null   Est Pas Null

11-3 Les fonctions

Il en existe des quantités. On pourra consulter avec profit l'aide en ligne. Nous en avons utilisé quelques unes :

Maintenant()  ou   Date() : la date système

Page : n de page dans un état

AjDate(type, q, d) : produit la date obtenue en ajoutant à la date initiale d la durée (algébrique) q exprimée dans l'unité spécifiée par type. Parmi les types possibles : "aaaa" : année, "j" : jours, "m" : mois, etc

 

Quelques autres fonctions :

Gauche(Chaîne, Entier)

Droite(Chaîne, Entier)

ExtracChaîne(Chaîne, Début, Longueur)

NbCar(chaîne)

Fonctions de regroupement : Somme, Moyenne, Compte, Min, Max, Premier, Dernier, Ecartype, Var.

 

Remarque. Le comportement d'Access vis-à-vis des valeurs manquantes.

Les logiciels ont un comportement généralement imprévisible vis-à-vis des valeurs manquantes. Access n'échappe pas à la règle. L'expression Est Null, a un comportement généralement cohérent. En revanche, les autres opérateurs de comparaison traitent les valeurs nulles de manière particulière, et l'on s'en rend compte facilement en rédigeant des critères "négatifs".

 

Reprendre la table des intervenants et supprimer le contenu du champ Type Intervention pour les intervenants Le Coz et Irwin. Ensuite, sélectionner les bénévoles :

Type Intervention
Bénévole

Les deux derniers intervenants, Le Coz et Irwin, pour lesquels le champ Type Intervention est vide, ne sont pas sélectionnés.

 

Sélectionner alors les non-bénévoles :

Type Intervention
Pas "Bénévole"

Les deux derniers intervenants ne sont toujours pas sélectionnés!! Les opérateurs <>, >, < aboutiraient au même résultat.

 

En fait, pour obtenir toutes les fiches ne satisfaisant pas un certain critère, il faut le rédiger sous la forme suivante :

Type Intervention
Pas "Bénévole" Ou Est Null

 

12) Exercices de Monitorat

On reprend la base de données du cas "Suivi Psychologique" (cf. fin de la fiche de TD précédente et le fichier W:\PSY3\TD ACCESS\SuiviPsy.MDB ).

 

1) Réalisez un formulaire comportant un sous-formulaire donnant, pour chaque séance, ses caractéristiques complètes ainsi que la liste des sujets concernés (code, nom, prénom). Enregistrez ce formulaire sous le nom Participants aux séances.

 

2) Rédigez une requête donnant les code, nom et prénom des sujets qui n'ont jamais été observés en séance collective. N.B. une requête intermédiaire sera sans doute nécessaire.

 

3) Rédigez une requête donnant les code, nom et prénom des sujets qui n'ont pas été observés durant la période du 1/03/99 au 15/03/99. N.B. une requête intermédiaire sera sans doute nécessaire.

 

4) Réalisez une requête donnant les code, lieu, date, heure et type des séances collectives, classées par ordre chronologique.

 

5) Réalisez une requête fournissant toutes les coordonnées des sujets dont vous avez arrêté le suivi. Enregistrez-la sous le nom.

 

6) Utilisez la requête de la question précédente pour définir une requête donnant les coordonnées des sujets dont vous poursuivez le suivi.

 

7) Réalisez un formulaire comportant un sous-formulaire donnant, pour chaque sujet, toutes ses coordonnées, ainsi que les code, lieu, date et type des séances au cours desquelles il a été observé.

 

8) Rédigez une requête fondée sur la table Sujet, comportant un champ calculé donnant l'identité (Nom + prénom) de chaque sujet.