PSY38X2 : Traitement de données en Psychologie - TD N°1

Utilisation d'un SGBD relationnel - Première partie

1) Enoncé du cas

Une organisation non gouvernementale d’aide humanitaire aux pays du tiers-monde effectue des interventions d’aide à l’éducation (enseignements professionnels, alphabétisation, puériculture,...), au développement (agriculture, irrigation,...), à la prévention sanitaire (vaccinations, mise en service de dispensaires, ...) dans divers pays.

Ses interventions sont programmées pour une durée limitée (de quelques mois à deux ans). Chaque intervention possède un responsable sur place nommé en principe pour toute la durée de l’intervention. Pour chaque intervention, sont également définis un site principal (base d’intervention) et des sites secondaires, lieux géographiques dans lesquels l’organisation est présente au titre de cette intervention.

Des intervenants bénévoles ou rémunérés sont affectés aux différentes interventions pour des missions de quelques jours ou de quelques mois.

Le siège européen de l’organisation souhaite rassembler dans une base de données les informations relatives aux interventions, aux responsables, aux sites d’intervention et aux intervenants (actuellement en mission ou disponibles pour une nouvelle affectation). Il pense ainsi pouvoir accéder facilement à des informations telles que :

- la liste des interventions, avec les coordonnées de leurs responsables ;

- la liste des intervenants possibles ;

- la liste des personnes intervenant dans une mission donnée à une date donnée ;

- des pages d’information avec, pour chaque mission, la liste des personnes qui y interviennent ou y sont intervenus, etc.

On vous confie la tâche de mettre en place une telle base de données à l’aide du SGBD Microsoft Access. Après une étude des données qui interviennent, vous parvenez au MCD suivant :

2) Choix d'un logiciel

La résolution du problème posé à l'aide d'un micro-ordinateur suppose l'utilisation d'un SGBD (système de gestion de bases de données).

Un tableur tel qu'Excel ou le module "base de données" de ClarisWorks ne conviendraient pas. En effet, ce type de logiciel ne permet de travailler que sur un seul fichier à la fois. Pour être en mesure de répondre à des questions telles que celles posées ci-dessus, on serait donc conduit à faire une seule table dans laquelle les données relatives aux intervenants, par exemple, seraient répétées de nombreuses fois. Les problèmes de mise à jour des informations deviendraient pratiquement insolubles. D'autre part, ces logiciels chargent en mémoire centrale la totalité du fichier, et ne permettent donc pas de travailler sur des fichiers volumineux.

Nous allons utiliser ici un véritable SGBD, Microsoft Access, qui permet:

- de gérer des lots de données très volumineux (pourvu que le disque dispose de la place suffisante);

- de constituer plusieurs tables et de gérer des liens entre ces tables ;

- de gérer les droits d'accès des utilisateurs aux différents objets constituant la base de données.

 

3) Charger Access - Définir une nouvelle base de données

Chargez le logiciel Microsoft Access, par exemple à partir du menu Démarrer-Programmes-Microsoft Access.

Une base de données est susceptible de contenir des informations confidentielles relatives à l'organisation qui l'utilise. Microsoft Access comporte un système de protection qui permet de limiter l'accès aux données aux seules personnes autorisées par l'administrateur du système. Ces personnes doivent disposer d'un compte d'utilisateur, et, au chargement, s'affiche la fenêtre suivante :

Pour les travaux que nous ferons en TD ou en monitorat, nous nous connecterons sur le compte etudiant ou le compte invité.

Complétez la fenêtre de connexion de la façon suivante :

Nom : etudiant

Mot de passe : ubo

ou, de manière équivalente :

Nom : invité

Mot de passe : invité

N.B. L'accent aigu est obligatoire dans le nom d'utilisateur. Le mot de passe, quant à lui, doit être tapé exactement comme indiqué.

Dans le dialogue qui s'affiche, cliquez l'item Nouvelle base de données ou cliquez sur Annuler et utilisez le menu Fichier-Nouvelle base de données. Définissez sur votre compte du serveur Samba, sur le disque E:, ou à la rigueur sur votre disquette, une base de données nommée ONG

Microsoft Access crée alors sur la disquette un fichier ONG.MDB et affiche la fenêtre suivante, dite fenêtre Base de données :

Cette fenêtre permet de gérer (création, suppression, modification de la structure ou des données) les différents objets constituant notre base de données. La colonne d'icônes indique le type d'objet actuellement sélectionné, et la zone de droite donne la liste des objets de ce type dans notre base. La fermeture de cette fenêtre ferme la base de données ONG.MDB.

Remarque

Vous aurez vite de nombreuses fenêtres ouvertes. Apprenez à organiser votre espace de travail: refermez ou réduisez en icônes les fenêtres inutiles à l'aide de leur menu système ou du menu Fichier-Fermer.

4) Définir la structure d'un fichier

Cliquez sur l'icône Table, puis sur le bouton Nouveau. Sélectionnez le Mode Création parmi les choix possibles. Vous obtenez alors l'écran suivant:

Définissez la structure suivante:

Nom du champ

Type du champ

Propriétés

Code Intervenant

Numérique

Entier long

Nom

Texte

20

Prénom

Texte

20

Nationalité

Texte

15

Adresse-Rue

Texte

30

CP

Texte

6

Ville

Texte

20

Pays

Texte

20

Tél

Texte

30

Type Permis

Texte

5

Profession

Texte

20

Type Intervention

Texte

8

Définissez ensuite le champ Code Intervenant comme clé primaire (sélection de la ligne concernée, puis menu Edition-Clé primaire ou icône dans la barre d'outils).

Remarquez que la propriété Indexé du champ prend la valeur Oui-sans doublons.

Enregistrez votre table sous le nom Intervenants et refermez sa fenêtre de structure.

5) Saisir des données - Importer des données

Ouvrez la table Intervenants et saisissez les données suivantes :

Code Intervenant

Nom

Prénom

Nationalité

Adresse-Rue

CP

Ville

1

Barthe

Rolland

Française

route des flots

45358

St Nazaire

2

Le Berre

André

Française

12,rue des voiles

63100

Moulin

Pays

Tél

Type Permis

Profession

Type Intervention

France

12/12/58/89

A/B

Enseignante

Rémunéré

France

65/12/45/53

A/B

Médecin

Bénévole

Remarquez les rôles des touches Entrée, Tabulation, etc. Ajustez les dimensions des colonnes et des lignes à nos besoins. Notez qu'il est possible d'écrire le contenu d'un champ sur deux lignes à l'aide de la combinaison de touches Ctrl+Entrée.

5.1 - Importer des données depuis un fichier Excel

Il est possible que la manipulation qui suit ne fonctionne pas sur les postes de nos salles. Dans ce cas, nous procéderons comme indiqué au paragraphe suivant.

Nous allons importer le reste des données relatives aux intervenants à partir d'une feuille de calcul Excel: W:\PSY3\TD ACCESS\ONG.XLS.

Veillez à ce que la table Intervenants soit fermée (ni fenêtre ni barre de titre dans le bas de l'écran)

Sélectionnez le menu Fichier-Données externes-Importer...

Sélectionnez Microsoft Excel (*.xls) dans la liste Type de fichier.

Sélectionnez W:\PSY3\TD ACCESS\ONG.XLS comme fichier à importer, puis Intervenants comme nom de feuille à importer. Constatez que la première ligne du tableau ne contient pas de noms de champs et indiquez-le à Access.

Sélectionnez la table Intervenants comme cible pour l'importation.

N.B. Le champ Code Intervenant ne devant pas comporter de doublons, on ne peut pas recommencer cette manipulation sans effacer les enregistrements ainsi ajoutés.

 

5.2 - Copier et coller des données depuis un fichier Excel

Si la manipulation précédente ne fonctionne pas, il est possible de procéder de la façon suivante :

Sans refermer Access, chargez Microsoft Excel et ouvrez le fichier W:\PSY3\TD ACCESS\ONG.XLS.

Sélectionnez et copiez la plage A1:L17 de la feuille Intervenants.

Revenez à Access, affichez la table Intervenants et utilisez le menu Edition-Coller par ajout.

 

6) Trier - Utiliser des filtres

Ouvrez la table Intervenants. La barre d'outils standard de cet écran comprend les outils suivants :

6.1 Tri alphabétique simple

On veut trier les fiches par ordre alphabétique des intervenants. Il suffit pour cela de placer le pointeur dans le champ Nom de l'une des fiches et de cliquer sur l'un des boutons de tri alphabétique.

6.2 Sélection de fiches selon des critères élémentaires

Sélectionnez les intervenants de nationalité française.

On peut simplement sélectionner l'une des fiches d'un intervenant de nationalité française, puis utiliser le bouton Filtrer par sélection.

On peut aussi utiliser le bouton Filtrer par formulaire, en complétant le formulaire comme suit :

Sélectionnez les intervenants possédant un permis B.

Remarquez l'utilisation du joker * .

Sélectionnez les bénévoles de nationalité française.

Sélectionnez les médecins et les infirmiers. Il y a deux solutions :

- On peut utiliser deux onglets dans le formulaire :

- On peut également utiliser la fonction OU :

Notez que, pour Access, Et , Ou sont des opérateurs (à placer entre les opérandes) et non des fonctions. Les principaux opérateurs reconnus sont : =, <, &, Et, Ou, Comme. Le mot-clé Entre est également reconnu, et on peut spécifier des critères tels que : Entre "D" Et "M" .

6.3 Les filtres/tris avancés

Les filtres des types précédents ne permettent pas de faire un tri multicritères sur le résultat du filtre. Un outil plus puissant est donné par le menu Enregistrements-Filtre-Filtre/tri avancé...

Tri des enregistrements

On veut trier les enregistrements par pays, et, pour chacun des, par nom d'intervenant.

Compléter les zones d'édition de la fenêtre de filtre/tri de la façon suivante:

Cliquez sur le bouton Appliquer le filtre (ou utilisez le menu Filtre-Appliquer le filtre/tri) et observez le résultat.

Exercice : Reprendre les questions précédentes et réaliser les filtres/tris avancés correspondants.

Remarque : Les filtres sont des moyens puissants d'interrogation d'une base de données. Mais, il n'est pas prévu d'enregistrer ces objets en tant que tels. Pour rédiger des critères de sélection et les conserver, il faut passer par une requête. Ces objets seront étudiés ultérieurement.

7) La table des interventions

Les champs de la table des interventions sont d'une part ceux correspondant aux attributs de l'entité Intervention, d'autre part le champ Code Responsable, clé étrangère permettant de représenter l'association hiérarchique ...est responsable de... Sa structure est la suivante :

Nom du champ

Type du champ

Longueur

Code Intervention

Texte

6

Objectif

Texte

30

Pays Intervention

Texte

50

Date Début

Date/Heure

 

Durée

Numérique

Entier Long

Code Responsable

Numérique

Entier Long

7.1 Importer des données depuis une autre base de données Access

Si cette manipulation ne fonctionne pas sur votre poste de travail, vous pourrez procéder comme indiqué dans le paragraphe suivant.

Importez cette table depuis la base de données de référence W:\PSY3\TD ACCESS\ONG-CORRIGE.MDB en procédant de la manière suivante:

- Affichez la fenêtre Base de données

- Menu Fichier-Données externes-Importer - Source des données : Microsoft Access

- Sélectionnez le fichier W:\PSY3\TD ACCESS\ONG-CORRIGE.MDB puis la table Interventions et l'option Structure et données.

Ouvrez et observez le contenu de cette table.

7.2 Copier et coller des objets entre deux bases de données Access

Si la manipulation précédente ne fonctionne pas, on peut procéder de la manière suivante :

- Sans quitter Access, ouvrez un exemplaire de l'explorateur pour copier W:\PSY3\TD ACCESS\ONG-CORRIGE.MDB sur un support accessible en lecture/écriture (E: ou A:).

- Double-cliquez sur l'icône du nouvel exemplaire du fichier ONG-CORRIGE.MDB ainsi créé.

Un deuxième exemplaire d'Access se charge en mémoire et ouvre cette base de données.

- Sélectionnez l'icône de la table Interventions et utilisez le menu Edition-Copier.

- Revenez à votre base de données ONG.MDB et utilisez le menu Edition-Coller.

- Répondez aux quelques dialogues qui vous sont proposés.

8) Définir un lien entre deux tables

L'association ...est responsable de... du MCD est représentée dans la base de données par une jointure entre les tables Intervenants et Interventions portant sur les champs Code Intervenant et Code Responsable. Dans Access, une telle jointure peut être définie comme une relation entre les deux tables.

Pour définir cette relation :

- Refermez les tables et/ou formulaires ouverts

- Affichez la fenêtre Base de données

- Menu Outils-Relations

- Ajoutez les tables Intervenants et Interventions.

- Faites glisser le champ Code Intervenant de la table Intervenants sur le champ Code Responsable de la table Interventions.

- Complétez la fenêtre qui s'affiche de la manière suivante :

En cliquant sur le bouton Créer, on revient alors sur la fenêtre Relations, qui se présente de la façon suivante :

Remarques:

1. Ce type de lien est toujours établi entre la clé primaire de la première table et un champ de même type de la seconde.

2. L'option "appliquer l'intégrité référentielle" permet d'avoir le comportement suivant:

- il est impossible d'ajouter à la table Interventions un enregistrement pour lequel le Code Responsable ne correspondrait pas à un intervenant de la table Intervenants;

- il est impossible de supprimer un intervenant de la table Intervenants, si celui-ci correspond à des enregistrements de la table Interventions.

Testez le comportement de l'intégrité référentielle en essayant de saisir quelques enregistrements.

Exercice: Avec les règles d'intégrité référentielle précédentes, comment faut-il s'y prendre pour modifier le code d'un intervenant, lorsque la table Interventions contient des enregistrements liés à cet intervenant ?

 

 

EXERCICE DE MONITORAT - UN CAS BIBLIOTHEQUE

I - Enoncé du cas

Une bibliothèque dispose d'ouvrages qui peuvent être empruntés par des abonnés. Les abonnements peuvent être souscrits à un moment quelconque de l'année, et ont une durée d'un an. Les abonnés peuvent emprunter au maximum deux ouvrages à la fois, et la durée du prêt est limitée à deux semaines.

La phase d'étude conceptuelle a permis de dégager deux entités, Abonné et Ouvrage. Après avoir étudié les documents à produire, on décide de mémoriser les renseignements suivants concernant les abonnés: le numéro d'abonné, le nom, la partie rue de l'adresse, la ville de résidence, la date de souscription d'abonnement.

Ces éléments constituent les attributs de l'entité Abonné.

De même, l'entité Ouvrage aura les attributs suivants: un numéro de code, une rubrique précisant le genre d'ouvrage (Histoire, Informatique, Roman, ....), l'auteur, le titre, l'année de publication, un résumé, ou une description de l'ouvrage.

Les deux entités Abonné et Ouvrage sont liées par l'association Emprunte, qui a comme attribut Date d'emprunt.

Remarque importante: Si on se borne à mémoriser les emprunts en cours à la date actuelle, sans chercher à conserver l'historique des emprunts précédents, chaque abonné peut avoir emprunté 0, 1 ou 2 ouvrages tandis que chaque ouvrage ne peut faire l'objet que de 0 ou 1 emprunt.

II - Création de la table des abonnés

Définissez une nouvelle base de données nommée BIBLIO. Définissez ensuite une table Abonnés ayant la structure suivante :

Champ

Type

Taille

NUMERO

Numérique

Entier

NOM

Texte

20

RUE

Texte

30

VILLE

Texte

20

DEBUT

Date/Heure

Date, abrégée

Définissez le champ NUMERO comme clé de la table.

Saisissez quelques fiches. Importez ensuite le reste des abonnés à partir de la feuille ABONNES du fichier Excel W:\PSY3\TD ACCESS\BIBLI.XLS.

III - Création de la table des ouvrages

On définira une table Ouvrages ayant la structure suivante. Remarquez la façon dont l'association emprunte a été représentée dans la base de données.

Champ

Type

Taille

CODE

Texte

7

GENRE

Texte

12

AUTEUR

Texte

20

TITRE

Texte

30

ANNEE

Numérique

Entier

DESCRIPTION

Mémo

 

DATE EMPRUNT

Date/Heure

Date, abrégée

CODE ABONNE

Numérique

Entier

Saisissez quelques fiches, puis importez les fiches contenues dans la feuille OUVRAGES fichier Excel W:\PSY3\TD ACCESS\BIBLI.XLS.

IV - Filtres et tris

Trier les fiches des abonnés par nom, par numéro.

A l'aide d'un filtre, afficher la liste des ouvrages actuellement empruntés.

A l'aide d'un filtre, afficher la liste des ouvrages écrits par Boris Vian et publiés avant 1970.

A l'aide d'un filtre, afficher la liste des ouvrages écrits par Balzac ou Zola.