tuto_first_base.pdf

Première base Access - tutoriel - Pascal Rigolet Septembre 2013 1

Réalisation d’une première base de données

(Tutoriel - version 3.0)

(Guettez les nouvelles versions de ce tutoriel sur le site web de l’enseignement)

(Entrainez-vous à reconstituer la base des notes avec ce tutoriel)

(Ce tutoriel n’est pas un cours académique, il vise à rendre les étudiants rapidement opérationnels)

Règles essentielles :

Il faut :

- Eclater l’information ;

- Rassembler ce qui peut l’être en catégories (familles ou rubriques) ;

- Avancer par étapes (chacune doit être opérationnelle) ;

- Mettre dans une rubrique annexe tout ce que l’on ne peut pas ranger dans une catégorie

précise ;

- Etre patient.

Mais l’essentiel se règle avec une feuille de papier et un crayon ! C’est la première étape du travail.

La phase papier/crayon doit être suffisamment claire et exacte pour passer à la pratique

immédiatement. Ceci implique de procéder à une étape de «brain storming» puis d’établir et de

suivre un cahier des charges. Finalement le schéma des relations doit conduire à la réalisation d’une

première version de la base ACCESS faisant passer le Modèle Conceptuel des Données (MDC) à un

Modèle Logique de Données (MLD)

Il existe une méthode pour réaliser (construire) une base de données � MERISE

Sans le savoir ou le dire on va appliquer les principes de cette méthode et faire preuve de bon sens.

MERISE :

� Vocabulaire de spécialiste

� Fait pour organiser le travail des informaticiens (années 60-70) et rendre le traitement de

l’information structurée plus performant

� Modèle conceptuel des données � vision pratique de la base de données (vocabulaire

logiciel de la base de données)

Les différentes étapes de la réalisation de la base ‘notes_étudiants’

(Nous passerons outre les détails d’une éventuelle version Excel pour nous pencher exclusivement

sur la façon de réaliser une base Acces fonctionnelle)

Aussi surprenant que cela puisse paraître, une base de données relate une histoire !

L’histoire qui est sous jacente (et répétée autant de fois que nécessaire) dans cette base est :

< L’étudiant Machin a obtenu la note X sur 20 dans la matière Truc enseignée par l’enseignant Bidule >

Ou encore :

< La note X sur 20 a été obtenue par l’étudiant Machin dans la matière Truc enseignée par le prof Bidule >


Etudiants

Nom

Prénom

E-mail

Identificateur étudiant

‘Notes’

Enseignants

Identité

Tel

Matière

Matière

Coefficient

Intitulé

Annexe

Notes

Résultats ??

Table_enseignant

Id_prof texte (‘nom’)

matière texte

tel numérique (entier)

Table_matière

Id_matiere numéro auto clé primaire

intitule texte

coef numérique (entier)

Table_étudiant

Id_etudiant numéro auto clé primaire

nom texte

prenom texte

e-mail texte

Id_INE numérique Table_notes

nom_etu texte (‘nom’)

matière texte

note sur 20 numérique

On s’occupe d’abord du contenant (préparant l’entrée du contenu). Il faut donc structurer ce contenant

Commençons donc par éclater et catégoriser l’information, ceci va permettre de repérer les futures

tables et de définir les futurs champs des enregistrements.

On repère ainsi que 3 catégories (tables) peuvent être ainsi facilement créées regroupant une

information cohérente. Il reste un problème : où ranger les notes ?

Il y a ici potentiellement 3-4 feuilles Excel

ON NE FIGURE PAS LES OPERATIONS ET AUTRES REQUETES SUR LES DONNEES BRUTES

Maintenant que l’étape de conceptualisation est terminée, nous pouvons commencer le travail dans Access

(ou dans tout autre SGBDR)

Premières étapes avec Access

Création de tables : il est clair que 4 tables (ça y est le mot est lâché) vont être créées l’une après l’autre

- On renseigne les noms des champs et le type des données (ne pas se tromper dans les formats).

On peut ajouter des champs à n’importe quel moment en basculant vers le mode création

- Enregistrement de la table en cours (soit clic droit puis Enregistrer, soit la bascule en mode

feuille de données demande automatiquement si l’on veut enregistrer les modifications)

Une règle essentielle : il faut éviter les doublons

L’unicité de l’information est plus facilement assurée par un codage numérique.

Au début, cela surprend un peu, mais on s’y fait rapidement lorsque l’on constate tous les avantages

que cela procure en souplesse dans la création, la gestion et l’utilisation pratique de la base de données.


Création d’une nouvelle base Access

(Nous abordons alors l’aspect contenu pour la première fois)

Commençons par créer une base Access qui contiendra l’ensemble des données structurées sous le

nom note1.mbd (il faut toujours miser sur la prudence en numérotant les versions de la base de

données pour garder une trace de sa réalisation progressive et archiver les versions fonctionnelles

dans un répertoire « Versions »)

L’interface de Access 2007 est un peu garni mais il faut toujours faire les choses simplement : ici

cliquer sur l’icône (en haut à gauche de la fenêtre centrale) « Base de données vides ». Sélectionner

le répertoire d’accueil et définissez le nom du fichier base de données dans le menu de droite (ici

note1.accdb). Depuis la version 2007 la terminaison des fichiers n’est plus mdb (pour master

database) mais accdb (pour access database).

Une fois votre fichier ouvert vous rentrez dans l’univers pratique de la réalisation d’une base de

données avec le SGBDR (Système de Gestion de Base de Données Relationnelles) Access.

Il faudra vous laisser guider par les commandes, fonctions et autres options que proposent les menus

déroulants et les boutons du logiciel. Vous verrez que la prise en main est assez intuitive et rapide.

On y retrouve déjà les rubriques habituelles de création et gestion de fichier, d’édition et puis de

manipulation de données.


A ce stade, vous venez de facto de créer une base de données vide. Il faut maintenant commencer à

organiser son contenu et créer les tables prévues à l’étape de conception (figurant sur le schéma).

Entrez dans la fenêtre de création de la première table de cette base de données Access. On repère

tout de suite le bouton « Affichage» (cerclé en rouge sur la figure ci-dessous) qui permet de faire

passer l’affichage de la table en mode feuille de données (contenu) ou en mode création (format /

contenant).

Il est logique de commencer par définir le format de la table (contenant) en précisant la structure des

champs des enregistrements, répondant à des types de données précis. On bascule donc en mode

création en cliquant sur le bouton affichage. Lors de la validation des premières informations de la

table, le logiciel nous demande de les enregistrer. Enregistrons les sous l’identificateur

« Table_etudiant » ; sans accent pour respecter la règle du latin de base (format ASCII)

Dés lors la définition précise de chacun des champs composant les enregistrements peut commencer

Le premier champ sera l’identificateur numérique (interne) de l’étudiant ID_etudiant dont le type sera num_auto. Dés lors, la numérotation sera automatiquement incrémentée par le système à chaque nouvel étudiant entré dans la table.


Il est assez simple de définir chacun des champs par son identificateur et son type. On pourra

avantageusement utiliser les cases Description pour saisir des commentaires et préciser certains

éléments. L’unicité de l’information n’étant pas garantie avec le nom ou même avec le couple (nom,

prénom), elle le sera grâce au numéro ID de l’étudiant. On associe alors (avec le bouton clé primaire,

cerclé en rouge sur la figure, ou à l’aide d’un clic droit souris / choix clé primaire) la clé primaire sur

le champ ID_etudiant

On remarque que la structure des enregistrements est décrite de telle façon qu’à chaque ligne

correspond la définition d’un champ dans l’ordre de leur rencontre.

En basculant en mode «feuille de données » le logiciel demande s’il faut enregistrer les modifications

effectuées. Il faut répondre « oui »

Nous sommes ainsi arrivés à l’étape de l’entrée des données


Créons deux enregistrements :

On voit que les enregistrements sont affichés les uns après les autres selon la structure que l’on a

définie en mode création, à raison d’un enregistrement par ligne. Les colonnes de la table

correspondent ainsi aux champs des enregistrements crées (le champ ID_etudiant est

automatiquement incrémenté d’une unité à chaque enregistrement crée, on ne s’en occupe pas).

On procédera ainsi pour chacune des tables à créer

Importation d’un fichier Excel/d’un fichier .txt

Nous pouvons nous faciliter la tache en récupérant les noms des étudiants depuis un fichier texte

1 / Enregistrer la feuille Excel en format texte (.txt) (séparateur tabultation)

2 / Ouvrir Access, dans données externes : importer fichier .txt, dans la boite de dialogue choisir

le bon séparateur et cocher « 1ère

ligne contient les noms des champs » si besoin

3 / On peut choisir de laisser Access choisir une clé primaire ou choisir le champ de la clé

primaire ou ne pas indiquer de clé primaire.

4 / Il faudra ajouter à cette table le numéro d’index de chaque enregistrement (ID_etudiant)

5 / Enregistrer la table / elle est désormais totalement utilisable


Une première ébauche – les différentes étapes, fruit de notre réflexion

1 - Une réalisation par étape

2 - Répertorier les entités qui vont composer la base de données / rassembler les données en catégories

3 - Proposer une phrase permettant de placer la base de données dans un certain contexte


4 - Vers un premier schéma relationnel de la Base des Notes

Nous constatons que la table principale est une table intermédiaire, celle qui contient les notes que

les étudiants ont obtenues dans les différentes matières. Tous les éléments principaux de l’histoire

sont réunis dans ce schéma. D’une façon générale, on peut avoir intérêt, ne serait-ce que pour gagner

du temps, à définir d’emblée une table centrale qui contient les champs les plus impliqués dans

l’histoire associée à la base de données (quitte à revoir, à posteriori, le schéma des relations).


Création des relations

Facilitons nous la tâche en créant la table Table_notes

Icône relations dans menu ‘feuille de données’

ATTENTION à l’unicité de l’information ! (il faut faire la

chasse aux doublons). Pour rendre l’information

unique on l’associe à une ou plusieurs clés dites clés

primaires.

Table_notes = TABLE CENTRALE

(sans s’en rendre compte on vient également de créer une table intermédiaire permettant

l’établissement de relations de type n à m, dite également 1 à plusieurs)

On a créé un champ code_etudiant (ajouté dans la table table_étudiant) pour avoir un numéro unique.

Dans table_étudiant : clic droit Id_etudiant � clé primaire (ou clic direct sur clé primaire)

Le champ correspondant à la clé primaire contient une information rendant UNIQUE

l’enregistrement complet

Quand un champ correspond à la clé primaire il ne peut pas être supprimé (il faut au préalable

supprimer l’attribut « clé primaire »)

Pour créer une relation :

- Toutes les tables devant être mises en relation doivent être fermées

- cliquer-glisser du champ Id_etudiant (table_étudiant) vers code_etudiant (table_notes)

- dans la fenêtre de dialogue cocher :

« Appliquer l’intégrité référentielle »

et

« Mettre à jour en cascade les champs correspondants »

Table_notes

code_etudiant numérique clé étrangère

code_matiere numérique



Du Modèle Conceptuel de Données (MDC) à un Modèle Logique de Données (MLD)

MDC, Relations et cardinalités

Nous serons amenés à rencontrer 3 types de relations (associées à des cardinalités), brièvement

illustrées dans cette page : les relations un à un, un à plusieurs et plusieurs à plusieurs.

Il n’est pas toujours aussi évident que cela de déterminer quel type de relation (ou cardinalité) doit

être utilisée entre 2 tables de la base.

Prenez l’habitude de définir les cardinalités (minimum et maximum possibles) dans les deux sens.

Répondez pour cela à la question suivante : pour un enregistrement de la première table considérée

combien peut-il y en avoir dans la seconde table ?

De plus, essayer d’associer un verbe (à l’infinitif) dans le schéma qui résulte de cette relation.

Pour construire une relation il faut :

- 2 tables (au sens large, requêtes incluses)

- 1 champ permettant l’échange des contenus communs dans chacune des tables

Ex : Id_etudiant (table_étudiant) et code_etudiant (table_notes)

Il faut nécessairement que l’un d’entre eux soit associé à une clé primaire (l’autre sera par

définition la clé étrangère)

- Le type d’information partagé doit être le même dans les 2 champs et leur taille identique

Ex : entier long lié à un entier long, réel avec réel, un texte de 4 caractères avec un texte de 4

caractères … (la seule exception est celle liant un champ de type numéroAuto avec un

champ de type numérique Entier long)

- Même catégorie d’information : un code_enseignant ne peut pas être relié à un ID_matiere

ou à un ID_etudiant, cela va de soi.

Rq : On peut également (je le déconseille) créer un lien provisoire dans une requête

On validera le Modèle Logique de Données :

- A l’aide d’un jeu d’essai

- En poussant la base à ses limites


Relation « un à un » (1 à 1)

1 étudiant circule avec 1 vélo qui est le sien donc n’appartient qu’à lui

Il n’est pas dit que tous les étudiants possèdent un vélo et qu’il n’existe pas de vélo sans propriétaire

Ici le verbe est « posséder »

0 à 1 étudiant « possède » 0 à 1 vélo

Et 0 à 1 vélo « est possédé » par 0 à 1 étudiant

Relation « un à plusieurs » (1 à N)

1 matière est enseignée par un ou plusieurs enseignants ; chacun d’eux n’enseignant qu’une seule

matière

Hypothèses : toutes les matières sont enseignées (par au moins un enseignant)

Chaque enseignant enseigne une et une seule matière

Ici le verbe est « enseigner »

1 à 1 enseignant « enseigne » 1 à 1 matière

Et 1 à 1 matière « est enseignée » par 1 à N enseignant(s)

Relation « plusieurs à plusieurs » (N à M)

1 matière est suivie par un ou plusieurs étudiants ; chacun d’eux suivant aucune à plusieurs matière

Hypothèses : - les étudiants inscrits mais ne suivant pas les cours ne suivent aucune matière

- certaines matières ne sont suivies par aucun étudiant (option n’ouvrant pas cette année-là)

Ici le verbe est « suivre »

0 à N étudiant(s) « suit » par 0 à N matières(s)

Et 1 à N matières(s) « est/sont suivie(/s) » 0 à N étudiant(s)

Posséder

0 ,1

0, 1

0, 1

0, 1

Table_Etudiant Table_Bicyclette

Enseigner

1 ,1

1, 1

1, N

1, 1

Table_Matiere Table_Enseignant

Suivre

0 ,N

0, N

0, N

1, N

Table_Etudiant Table_Matiere


Table_étudiant


nom texte

prenom texte

e-mail texte

Id_INE numérique

Table_notes


code_matiere numérique clé étrangère


Table_matière


intitule texte

coef numérique

code_enseignt numérique

Table_enseignant

Id_enseignant numéro auto clé primaire

nom texte

code_matiere numérique clé étrangère

qualité texte

tel numérique

e-mail texte

Schéma des relations

(Rajoutez à la main le type des

relations et associez-y un verbe)

Ca colle bien avec l’histoire à raconter :

L’étudiant ‘machin’ a obtenu la note X sur 20

dans la matière ‘truc’ enseignée par le prof ‘bidule’.

Tout va bien, on a bien travaillé.

1 plusieurs

Hypothèses :

1. un étudiant a plusieurs notes mais une seule par matière

2. Un enseignant peut enseigner plusieurs matières

3. Chaque matière à son coefficient

4. Il y a un seul correcteur par matière

Exercice :

Comment transformer cette structure pour tenir compte du fait qu’une matière peut également

faire intervenir plusieurs enseignants (il y a plusieurs solutions) ?


Astuce

Premier enregistrement auto pour chacune des tables

� « non défini » + valeur par défaut = 1 pour tous les code_...

� Permet de ne pas avoir de problème et toujours avoir des liaisons qui marchent.

Listes de choix

Dans table_matière il faut pouvoir imposer que le coefficient soit compris dans une liste :

- On crée une nouvelle table ‘Table_coef’ avec un seul champ ‘coef_matiere’ qui est

numérique.

- Dans ‘Table_matiere’ on change le type de données (si cela ne marche pas effacer le champ

puis recommencer) et on sélectionne ‘assistant liste de choix’ au lieu de ‘numérique’.

Assistant liste de choix doit rechercher dans ‘table,….’, on choisit ‘Table_coef’ puis le champ

‘coef_matiere’.

- Ainsi lorsque l’on veut renseigner d’un coefficient dans le champ ‘coef’ on dispose d’un

ascenseur qui nous permet de choisir parmi les valeurs de ‘coef_matiere’ dans la

‘Table_coef’.

- !!! Attention !!! on peut mettre d’autres valeurs, si l’on veut rester dans la fourchette

donnée par la ‘Table_coef’ il faut aller dans valide si … >0 Et <=5


Relations dans le Modèle Logique de Données (ex : Base de Données Access)

Finalement un compromis possible est celui figurant ci-dessous

(hypothèse : un seul enseignant par groupe)

Transformation de la base de données des notes pour intégrer le champ code_enseignant dans la

table Table_note et relier cette table à la table Table_enseignant (relation N à M)

Ceci va autoriser l’application de l’hypothèse : plusieurs enseignants peuvent enseigner dans une

même matière et un enseignant peut enseigner plusieurs matières

Pour commencer, il nous faut tout d’abord supprimer le lien qui relie la table Table_enseignant à la

table Table_matiere. Pour cela : clic droit sur le lien puis sélectionner l’option supprimer. Le logiciel,

prudent, vous demande alors si vous êtes surs de vouloir supprimer la relation sélectionnée.

Répondez « oui ».


Les 2 tables sont dés lors indépendantes.

Vous pouvez réarranger l’organisation des tables dans le schéma des relations afin de faire

apparaître la table Table_enseignant sous la table Table_matiere. De cette façon, le nouveau lien se

construira plus facilement et l’on y gagnera en présentation.

Il est alors possible de rajouter un champ dans la table Table_note dont le contenu sera partagé avec

l’identificateur de l’enseignant. Bouton de souris droit puis choix « insérer des lignes ». Ensuite

création du champ (numérique) correspondant à la clé étrangère : code_enseignant.


Il faudra ensuite penser à initialiser le champ à une valeur par défaut cohérente (ici : 1 qui

correspond à un enseignant « à définir ») puis à renseigner correctement les nouveaux champs avec

le code du professeur enseignant chacune des matières. Cette démarche assurera l’intégrité

référentielle des données reliées entre les deux tables (rappel : un enseignant peut enseigner

plusieurs matières mais une matière n’est enseignée que par un seul prof).

Finalement il faudra effacer les 2 clés primaires déjà présentes dans la table pour redéfinir l’unicité

de l’information sur le triplet (code_etudiant, code_matiere, code_enseignant). Vous êtes donc

amenés à insérer 3 clés primaires sur ce triplet. Il est à noter que ceci n’affectera ni le contenu de la

table, ni les liens déjà existants. Puissant, n’est-il pas ?


Il ne nous reste plus qu’à créer le lien « 1 à N » (« 1 à plusieurs ») entre la table Table_note et la table

Table_enseignant par l’intermédiaire du partage de contenu entre les champs code_enseinant

(Table_notes) et ID_enseinant (Table_enseignant). N’oubliez pas de fermer les tables concernées

pour créer ce nouveau lien.

Pour finir, supprimez le champ code_prof de la table Table_matiere qui ne sert plus à rien.

Nous obtenons ainsi la structure figurant ci-dessous :

Ainsi construite, la base de données est encore plus performante. Nous avons atteint là un bon

niveau de MDC (Modèle Conceptuel des Données).


Table_module

Id_module numéro auto clé primaire


responsable texte

secretaire texte

code_APOGEE numérique

Table_etudiant


nom texte

prenom texte

e-mail texte

Id_INE numérique

Table_notes

code_etudiant numérique clé primaire

code_matiere numérique clé primaire


Table_matiere


intitule texte

coef numérique

code_enseignt numérique

Table_enseignant

Id_enseignant numéro auto clé primaire

nom texte

code_matiere numérique

qualité texte

tel numérique

e-mail texte

Vers une autre version de la base de données

On peut s’intéresser à intégrer l’ensemble dans la spécialité (module) choisie par les étudiants. Pour

Chaque module il y aura une série de notes par étudiants (correspondant aux matières définissant le

module). Cela peut donner le schéma suivant :

La clé primaire est définie sur les champs code_etudiant et code_matiere pour éviter les doublons

dans l’attribution des notes


Règles d’accès/de priorités

SQL : System Query Langage � langage de programmation reconnu par Access (transparent pour nous)

Exportation vers un fichier excel

Exemple : résultat d’une requête

Exercice :

Extraire la table résultat de la requête (matière= ‘Géologie ‘) et réaliser le graphe de la répartition

des notes en classes de 2 points.


Les 4 métiers qui se cachent derrière une base de données :

l’administrateur de la base qui gère les informaticiens (créent le SGBD, définissent les requêtes,

fonctions, validations etc…), les personnes qui renseignent la base de données (donnent un contenu

fiable à la base) et celles qui saisissent les données.

Besoin de faire des tests pour révéler le maximum d’erreur � mises à jour de la base.

Règle du 80/20

Règle du 80/20 : (à propos de la recherche de performance)

S’applique à la notion de cahier des charges

Cahier des charges : objectifs 100% des charges réalisées

On passe 20% du temps pour honorer 80% du cahier des charges.

Pour s’intéresser au reste (détails) du cahier des charges (20% restant), le temps à y consacrer est

indéfini…

Le parfait n’existe pas, il faut déjà faire quelque chose de bien. Apprendre à travailler vite et bien.

Aller à l’essentiel (au plus évident).

A l’issue de ce travail, nous sommes prêts à nous intéresser à des considérations plus

environnementales


Table_master_étudiant

code_etu numérique

code_specialite numérique

Table_étudiant


nom texte

prenom texte

e-mail texte

Id_INE numérique

Table_master

Id_master numéro auto clé primaire

Intitule liste

Responsable texte

Liste_master

Nom_master texte

Comment renseigner la base des spécialités (4) suivies par l’étudiant(e) ???

(variante)

But : pouvoir sortir la liste des étudiants pour chaque spécialité + pouvoir sortir la liste des spécialités

pour chaque étudiant (1 ou plusieurs)

Créer une nouvelle table. Chaque étudiant peut suivre une ou plusieurs spécialités. Créer une table

avec la liste des spécialités pour pouvoir choisir dans la ‘Table_master’ la spécialité dans la liste

‘Liste_master’.

Règle du 80/20 :

• Permet de combiner indépendamment des

informations nouvelles corrélées (spécialités = intitulé

précis du master)

• Soit la nouvelle table ‘Table_master’ est en

amont de ‘Table_matière’, soit la ‘Table_master’

directement reliée par une relation de 1 à plusieurs (1

à n) à la ‘Table_étudiant’.

Les requêtes permettent d’afficher (ou non) cette

nouvelle information avec les autres.

tuto_first_base.pdf

Documents