PSY38X2 : Traitement de données en Psychologie - TD No2

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 :

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:

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

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é.

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

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

Nom du champ Type du champ
Code Intervenant Numérique

Propriétés du champ : Taille du champ = Entier long
Nom Texte
Propriété : Taille du champ = 20
Prénom Texte
Propriété : Taille du champ = 20
Nationalité Texte
Propriété : Taille du champ = 15
Adresse-Rue Texte
Propriété : Taille du champ = 30
CP Texte
Propriété : Taille du champ = 6
Ville Texte
Propriété : Taille du champ = 20
Pays Texte
Propriété : Taille du champ = 20
Tél Texte
Propriété : Taille du champ = 20
Type Permis Texte
Propriété : Taille du champ = 5
Profession Texte
Propriété : Taille du champ = 20
Type Intervention Texte
Propriété : Taille du champ = 8

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

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

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.

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 :

6) Trier - Utiliser des filtres

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

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 :

Remarquez l'utilisation du joker * .

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.

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
Code Intervention Texte
Propriété : Taille du champ = 6
Objectif Texte
Propriété : Taille du champ = 30
Pays Intervention Texte
Propriété : Taille du champ = 50
Date Début Date/Heure
Propriété :
Durée Numérique
Propriété : Taille du champ = Entier Long
Code Responsable Numérique
Propriété : Taille du champ = 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.

 

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

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.

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:

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é.

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 ayant la structure suivante :

 

Champ Type
NUMERO Numérique
Propriété : Taille du champ = Entier
NOM Texte
Propriété : Taille du champ = 20
RUE Texte
Propriété : Taille du champ = 30
VILLE Texte
Propriété : Taille du champ = 20
DEBUT Date/Heure
Propriété : Taille du champ = Date, abrégée

 

Définissez le champ NUMERO comme clé de la table, puis enregistrez la table sous le nom Abonnés.

Saisissez quelques fiches.

 

Chargez ensuite le classeur Excel W:\PSY3\TD ACCESS\BIBLI.XLS, copiez les données de la feuille ABONNES et collez-les par ajout dans la table Abonnés.

III - Création de la table des ouvrages

On définira une table 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
CODE Texte
Propriété : Taille du champ =7
GENRE Texte
Propriété : Taille du champ =12
AUTEUR Texte
Propriété : Taille du champ =20
TITRE Texte
Propriété : Taille du champ =30
ANNEE Numérique
Propriété : Taille du champ = Entier
DATE EMPRUNT Date/Heure
Propriété : Taille du champ = Date, abrégée
CODE ABONNE Numérique
Propriété : Taille du champ =7

Définissez le champ CODE comme clé primaire, puis enregistrez la table sous le nom Ouvrages.

 

Copiez ensuite les données de la feuille OUVRAGES du classeur Excel W:\PSY3\TD ACCESS\BIBLI.XLS et collez-les par ajout dans la table Ouvrages.

 

Définissez une relation entre le champ NUMERO de la table Abonnés et le champ CODE ABONNE de la table Ouvrages, en activant si possible l'intégrité référentielle.

 

N.B. Si vous n'êtes pas parvenu à réaliser les manipulations précédentes, vous pouvez recopier sur votre compte la base de données W:\PSY3\TD ACCESS\Biblio-correction.mdb pour résoudre les questions qui suivent.

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.