réalisation d’une première base de données …...première base access - tutoriel - pascal...

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

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

(Tutoriel - version 4.5 ; 21 septembre 2015)

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

- En cours de réalisation, veuillez donc excuser les imperfections de ce tutoriel

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

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

Préambule 2

Définitions synthétiques 2

1- Démarche suivie et règles essentielles 3

2- Les différentes étapes de la réalisation de la base ‘notes_etudiants’ 4

3 - Etablissement du Modèle Conceptuel des Données (MCD) 5

4- Du Modèle Conceptuel de Données au Modèle Logique de Données (MLD) 10

5 - Création de la base de données avec Access 14

6 – Donner un contenu : renseigner la base de données 20

7 – Requêtes : interroger la base de données

8 – Importation de données (format d'échange : texte ASCII) 22

9 - Vers une autre version de la base de données 30

10 – Transposition vers un autre SGBDR (OpenOffice)

11 - Règle du 80/20 31

12 - Résumé des étapes de la construction d’une base de données relationnelle 32

13 - Exportation (vers un fichier texte, csv, Excel, …) 33

14 - Les quatre métiers qui se cachent derrière une base de données 33


Préambule

Toutes les secondes le volume d'informations mondiales ne cesse d'augmenter, contribuant ainsi au

fameux Big Data. Mais sans analyse et sans base de données le Big Data n’a aucun sens!

Nous pouvons définir une base de données comme une collection structurée d'informations non

nécessairement du même type mais généralement relatives à une thématique ou une discipline

donnée. La base de donnée crée est usuellement disposée sur un même support, généralement

informatisé (numérique).

Nous effectuerons notre apprentissage sur un exemple très simple, la gestion des notes d'étudiants

(tout le monde sait ce qu’est un fichier de notes).

Nous verrons dans ce tutorial plusieurs notions sans qu’on puisse pour autant parler de cours

magistral sur les bases de données. L'idée maîtresse est que vous soyez rapidement opérationnels

sans perdre trop de temps à assimiler des concepts trop théoriques. Vous verrez que la logique et le

bon sens sont essentiels dans ce travail. Ce tutorial est ainsi assez axé sur la pratique permettant de

développer, concevoir et réaliser une base de données assez rapidement sur ACCESS tout en mettant

en scène les principaux concepts associés à la notion de base de données. Les étudiants très curieux

pourront trouver sur le web une foule de cours théoriques portant sur les bases de données qui

contribueront à étoffer leurs connaissances, bien que cela ne soit pas nécessaire.

Nous verrons ainsi à travers ce premier exemple les notions de : bases de données relationnelles et

de Système de Gestion de Base de Données Relationnelles (SGBDR), de modèle conceptuel des

données, de modèle logique des données, d'entités, d'attributs, de relations (encore appelées

liaisons ou associations) et de cardinalités. Mais aussi bien d'autres choses encore, sans que vous

vous en rendiez vraiment compte. Il sera l'occasion de faire vos premières armes sur ACCESS, un

logiciel très utilisé, vous offrant ainsi une compétence recherchée.

Je vous souhaite un plaisant apprentissage à la lecture de ce document.

Pascal RIGOLET

Enseignant à Paris-Saclay

Définitions synthétiques

(loin d’être uniques et loin d’être parfaites)

- Une base de données c’est essentiellement une collection structurée d’informations

non nécessairement du même type (format). Pièce centrale des dispositifs informatique qui

servent à la collecte, au stockage et à l’utilisation des informations, une base de données est

usuellement localisée en un seul lieu et un seul support (dupliqué en fait) qui est généralement

informatique (numérique)

- SGBDR (Système de Gestion de Base de Données Relationnelles) : logiciel moteur qui pilote

la base de données et en permet la manipulation et l’exploitation (interrogation,…)

(nous utiliserons ici le SGBDR ACCESS)


1- Démarche suivie et règles essentielles

Il va s'agir de modéliser puis de construire une base de données pour gérer les notes obtenues à

l'examen par un ensemble d'étudiants. Tout commence donc par établir ce que l'on veut y trouver et

le contexte de cette création. Nous avons vu les limites de la manipulation d'un fichier Excel

constituant une première approche de cette base. Excel n'est pas franchement l'outil adapté pour la

gestion et la conception d’une base de données, rendant difficile et peu pratique la manipulation des

données.

La modélisation est une étape fondamentale de la conception d’une base de données dans la mesure

où, d’une part, on y détermine son contenu et, d’autre part, on y définit la nature des relations entre

les principales entités qui la constituent. Une situation à modéliser peut conduire à plusieurs

schémas différents, chaque modèle présentant des avantages et des inconvénients.

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

Sans le savoir, ou même l’évoquer, on va appliquer les principes de cette méthode et faire preuve de

bon sens.

MERISE :

Vocabulaire de spécialiste

Faite pour organiser le travail des informaticiens (dès les 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

Une réalisation par étape

Il s'agit de modéliser puis de construire une base de données (avec ACCESS) pour gérer les notes obtenues à l'examen par un ensemble d'étudiants. Tout commence donc par établir ce que l'on veut y trouver et le contexte de cette création. Nous avons vu les limites de la manipulation d'un fichier Excel constituant une première approche de cette base. Excel n'est pas franchement l'outil adapté pour la gestion et la conception d’une base de données, rendant la manipulation des données assez lourde.

Nous passerons outre les détails d’une éventuelle version Excel pour nous pencher exclusivement sur la façon de réaliser une base Access fonctionnelle

Le but est d’essayer de réaliser (avec ACCESS) une base de données un peu plus sérieuse que celle proposée dans le fichier EXCEL pour la gestion des notes obtenues par les étudiants.

La réalisation, nous allons le voir, va se dérouler en plusieurs étapes en commençant par un brain storming permettant de jeter les premières idées et le brouillon du modèle retenu pour la base de données.

Dans les grandes lignes, il s’agit tout d’abord de donner un titre au projet, d’éclater l’information que l’on rassemblera ensuite en catégories (entités) qui vont composer cette base de données, d’essayer d’établir un mini scénario débouchant sur une phrase contextuelle (une histoire), de construire le modèle conceptuel des données (MCD) puis le modèle logique des données (MLD) et enfin de réaliser pratiquement la base de données avec ACCESS en se référant au MLD. IL sera alors possible de donner un contenu à cette base de données et de réaliser des requêtes sur ce contenu (interroger la base).


Règles essentielles :

La modélisation est une étape fondamentale de la conception d’une base de données dans la mesure où, d’une part, on y détermine son contenu et, d’autre part, on y définit la nature des relations entre les concepts principaux. Une situation à modéliser peut conduire à plusieurs schémas différents, chaque modèle présentant des avantages et des inconvénients. Cette modélisation s’organise progressivement avec une feuille de papier et un crayon. Nous allons aborder les notions de Modèle Conceptuel des Données (MCD) et de Modèle Logique des Données (MLD)

Contrairement au regroupement effectué dans le fichier EXCEL que nous avons créé, il faut :

- Eclater l’information ;

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

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

- Ranger dans une rubrique annexe tout ce que l’on ne peut pas ranger dans une entité précise ;

- Etre patient.

L’essentiel est conçu 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)

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

- Création du Modèle Conceptuel des Données (MCD)

- Transformation du MCD en Modèle Logique des Données (MLD)

- Réalisation pratique de la base ACCESS

- Entrée des données

- Validation du modèle


3 - Etablissement du Modèle Conceptuel des Données (MCD)

On s’occupe d’abord du contenant (préparant l’entrée du contenu).

Il faut donc structurer ce contenant.

Brain Storming

Il est très simple et évident dans cet exemple. Ce n’est pas toujours le cas

Rassembler les données en catégories et répertorier les entités qui vont composer la base

Sans trop compliquer la procédure, quelques catégories d’informations (entités) nous viennent à l’esprit :

Commençons donc par éclater et catégoriser l’information pour faire apparaitre les différentes entités et leurs attributs (ceci va permettre de repérer les futures tables et de définir les futurs champs des enregistrements). On repère ainsi assez facilement ainsi qu’au moins deux entités (catégories ; tables) peuvent être créées regroupant une information cohérente. Il reste un problème : où ranger les notes ?

En nous intéressant à l’information la plus facile à définir, trois entités peuvent être en fait rapidement identifiées. Nous les représenterons par des rectangles et les identifierons de façon explicite à l’aide d’un identificateur unique que nous mettrons dans un cadre au-dessus du rectangle de l’entité (ici : Etudiants, Matières et Enseignants). Une quatrième entité, nommée « Autres», contiendra les informations dont le traitement ou la catégorisation n’est pas évidente; c’est le cas des notes elles-mêmes. Chacune des entités est définie par des attributs (propriétés caractérisant les entités) dont les noms sont indiqués les uns en dessous des autres dans les rectangles des entités (ex : noms ; prénom ; date_naissance pour l’entité Etudiants).

???


Etudiants

Nom

Prénom

E-mail

Identificateur étudiant

‘Notes’

Enseignants

Identité

Tel

Matière

Matière

Coefficient

Intitulé

Autres/Annexe

Notes

Résultats ??

Il y a ici potentiellement 3-4 feuilles Excel

A ce stade, nous ne pouvons progresser dans le développement du modèle conceptuel des données sans définir une phrase simple et claire rendant compte du contexte de la création et de la gestion des données dans la base. Il va donc falloir proposer (plusieurs essais sont parfois nécessaires) une phrase permettant de placer la base de données dans un certain contexte. Cette phrase permettra de définir plus facilement la façon dont les entités sont reliées entre elles.

Phrase contextuelle (associée à la modélisation)

Aussi surprenant que cela puisse paraître, une base de données est associée à un contexte que l’on

peut décrire (on peut même aller jusqu’à dire qu’il y a une histoire sous-jacente)

> La phrase doit rassembler tous les éléments définissant le contexte (acteurs + actions)

Le contexte (répété autant de fois que nécessaire) dans cette base est :

L’étudiant machin a obtenu la note de X/20 dans la matière Truc enseignée par l’enseignant Bidule

forme passive [la note devient l’information centrale] :

A l’issue d’un examen, la note de X/20 a été obtenue par l’étudiant machin dans la matière truc enseignée par l’enseignant bidule

( )


Relations et cardinalités

Simplifions alors, dans un premier temps, la problématique pour essayer de définir un modèle

répertoriant les inscriptions des étudiants aux matières proposées dans les différentes UEs ou

formations (inscriptions pédagogiques, par exemple). Oublions donc, pour le moment, l’Entité « Autres ». Ce qui donne l’ensemble suivant :

La phrase « l’étudiant machin suit la matière truc enseignée par l’enseignant bidule » va nous

permettre d’établir une première série de relations entre les 3 entités Etudiants, Matieres et

Enseignants.

Construction des relations On représentera une liaison par une ellipse traversée et prolongée par un trait et contenant le verbe à l’infinitif qui contribue le mieux à définir la nature, le contexte et le type de la liaison établie entre deux entités distinctes. Une liaison peut avoir des attributs (précisant son contexte). On portera une attention toute particulière aux verbes employés, qui seront à l’origine des relations (associations, liaisons) que l’on pourra établir entre les entité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.

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

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


Partons maintenant du principe que les étudiants, pour valider les UEs auxquelles ils se sont inscrits, doivent passer des examens. Nous voici donc confrontés à une première ébauche du MCD qui nous permet de passer plus aisément au modèle répondant à la problématique du départ :

A l’issue d’un examen, la de X/20 a été obtenue par l’étudiant machin dans la matière note

truc enseignée par l’enseignant bidule

(on remarque que la note obtenue est un attribut de la relation « passer un examen »)

Hypothèses de travail : une matière peut être enseignée par plusieurs enseignants mais un enseignant n’intervient que dans une seule matière (d’où la relation 1 à N figurant sur le schéma du MCD)

L’étape suivante va consister à transformer le Modèle Conceptuel des Données (MCD) en Modèle Logique des Données (MLD) avant de passer à la pratique de la réalisation (et du renseignement) de la base avec un ACCESS (ou tout autre SGBDR, tel par exemple php/Mysql).

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.

Les éléments de base du modèle Entité–Relation sont :

- les entités (objets pouvant être identifiés distinctement) ; il existe deux catégories d’entités : les

entités régulières, ne dépendant pas de l’existence d’une autre entité, et les entités faibles,

dépendant de l’existence d’une autre entité ;

- les attributs (caractéristiques/propriétés communes à tous les objets /éléments/ enregistrements

décrits dans les entités) ;

- les identifiants (qui identifient de façon unique l’entité ; future clé primaire). L’identifiant est

constitué d’un attribut ou d’un ensemble d’attributs permettant de distinguer une entité dans

l’ensemble. Il est généralement souligné ;

- les associations ou relations (généralement désignées par un verbe d'action, donc caractérisées,

comme les entités, par un nom), lien existant ou prévu entre 2 entités. Une association peut, en

outre, elle-même contenir des attributs et d'autres informations de type codes numériques dont

elles dépendent pour leur identification unique. Nous verrons lesquels ;

- les cardinalités associées (à n'aborder qu'après avoir défini entités, attributs et associations)


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

Dans le Modèle logique de données, les entités deviennent des tables et les attributs deviennent des

champs

Afin de mieux assurer la portabilité de la base de données dans le monde anglo-saxon, nous

enlèverons les accents présents dans les identificateurs de tables et de champs (nous verrons que

cela facilitera la réalisation des requêtes).

Pour construire une relation il faut :

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

- b/ 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)

-c/ que le type d’information partagé soit 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)

- d/ que la catégorie d’information soit la même

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

Commençons par décrire la liaison entre Enseignants et Matières Hypothèses : un enseignant peut enseigner plusieurs matières ;

une matière est enseignée par un seul enseignant

Dans le modèle conceptuel de donnée, l’information portée par la relation « enseigner » n’apporte pas d’information particulière. La relation 1 à N se définit donc simplement par un lien entre les champs code_enseignant et ID_enseignants


Continuons par décrire la liaison entre étudiants et matières (relation N à M)

Hypothèses : un étudiant est inscrit à une à plusieurs matières ; une matière est suivie par 0 à N étudiants

Dans le modèle conceptuel de donnée, une relation de type N à M (ou « plusieurs à plusieurs ») se transforme en table (dite de liaison). Elle apporte, outre les champs permettant l’échange d’information (en général gérée par codes), des champs permettant de mieux contextualiser la relation (ici : la date de l’examen et, bien sûr, la note obtenue à cet examen). Appelons cette table « Tables_notes ». Il apparait, et c’est logique, que la table note est la table principale (ou centrale) de la base de données ; c’est elle qui apporte toute l’information variable (en oposition à l’info figée contenue dans la Table_Etudiant, la Table-Matiere ou encore la Table_Enseignant).

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. Les principaux éléments définissant le contexte de la création de la base de données sont réunis dans ce schéma.

Astuce : 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 le contexte de la base de données (quitte à revoir, à posteriori, le schéma des relations).

Il y aura autant de notes que de couples (matière,étudiant) : notes(matière,étudiant)


Validation du modèle à l’aide d’un jeu de données test

On pourra alors avantageusement valider le MLD à l’aide d’un jeu test de quelques enregistrements

(2à 5 suffisent).

Rendre le modèle plus performant

Nous avons la possibilité de rendre le modèle plus performant en permettant à la base de données

de s’adapter à un contexte plus général qui consiste à considérer qu’un enseignant peut intervenir

dans plusieurs matières et qu’une matière peut être enseignée par plusieurs enseignants. Ce qui

revient à considérer une liaison de type N à M entre Table_enseignant et Table_matière (mais de

fait, également entre Table_enseignant et Table_etudiants). De ce fait, la Table Table_note peut

accueillir un autre champ : code_enseignant permettant de gérer l’information de l’origine des notes

obtenues, étant due chacune à un enseignant donné. On voit donc bien par ce nouveau modèle

qu’une note est attribuée à un étudiant donné par un enseignant donné pour une matière donnée. Il

est cependant évident que le champ code_enseignant ne peut être associé à une clé primaire (étant

simplement une information précisant les conditions d’obtention). Si les 3 champs (code_etudiant,

code_matiere, code_enseignant) portaient tous une clé primaire, il pourrait être possible pour un

étudiant d’avoir plusieurs notes pour une même matière (dépendant du prof ayant noté la matière).

Ce n’est pas ce que nous cherchions à obtenir. Ce sont donc avant tout des notes renseignées pour

chaque couple (matière, étudiant) qui sont collectées dans cette base, l’information de l’enseignant

(il y en a plusieurs possibles par matière) n’est qu’une précision apportée à l’information des notes

obtenues. En conséquence, dans la table Table_note, seuls les champs code_etudiant et

code_matiere sont associés à une clé primaire (garantissent l’unicité de l’information). Le champ

code_enseignant dans la table Table_matiere ne sert plus à rien, nous pouvons donc le supprimer.

Ce code passe dans la table Table_note et il devient implicite que l’enseignant qui a donné la note

(comme indiqué dans la table centrale) est intervenu dans la matière.


Nous pouvons donc proposer le modèle suivant :

Ce schéma est bien compatible avec le fait que plusieurs matières peuvent être enseignées par un

enseignant, qu’un enseignant peut intervenir dans plusieurs matières, qu’il a plusieurs étudiants, que

les étudiants peuvent avoir plusieurs profs et plusieurs matières et que chacune des matières

regroupe plusieurs étudiants.

Maintenant que l’étape de conceptualisation est terminée, nous sommes maintenant prêts à

passer à la réalisation de cette première version dans ACCESS (ou dans tout autre SGBDR)


5 - Création de la base de données avec Access

(La création de la base de données avec d’autres version d’ACCESS procède d’une démarche

très voisine de celle employée ci-dessous)

Création d’une nouvelle base Access

Remarque : Sans attendre d’avoir terminé l’ébauche du modèle logique des données, il est déjà possible commencer la prise en main du logiciel ACCESS (étape de création des 3 premières tables par exemple).

Nous abordons ici l’aspect du 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.accd (note1.mbd si créée avec la version 2003) ; 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 ».

Pour créer une nouvelle base il faut commencer par créer une « base de données vide » ; c’est la

règle sous ACCESS.


L’interface d'Access 2007/2010 est un peu garni mais nous allons aborder son apprentissage en

douceur : 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.

Laissez-vous guider par les commandes, les 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 fichiers, d’édition et de

manipulation de données.

Création de tables sous Access

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

Il est clair que 4 tables (les entités du MCD deviennent des tables) vont être créées l’une après l’autre.

Pour chacune d'elle, la procédure sera la même :

renseigner 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

enregistrer la table en cours (soit à l'aide d'un clic droit puis Enregistrer, soit en basculant en

mode feuille de données, ce qui engendre automatiquement une fenêtre demandant 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.

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


Commençons par créer la table Table_Etudiants :

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").

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

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.

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.


A l'issue de la création des premiers champs de la table, lorsque vous cliquez sur le bouton

"Affichage", le logiciel nous demande d'enregistrer ce contenant.

En règle générale, à chaque modification apportée à la structure, en basculant en mode «feuille de

données » le logiciel demande s’il faut enregistrer les modifications effectuées.

Il faut répondre « oui », si vous voulez valider cette structuration de la table. Enregistrons cette

structure sous l’identificateur « Table_etudiant » ; sans accent pour respecter la règle du latin de

base (format ASCII).


6 - Donner un contenu : renseigner la base de données

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


Continuons en créant la table Table_Enseignants :

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


8 – Importation de données (format d'échange : texte ASCII)

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

Nous pouvons récupérer 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


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 (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



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 issues du Modèle Logique de Données (ex : Base de Données Access)

Attribut

Base_Notes : notes [ étudiant, matière, enseignant, date ]

U dentités (matière, étudiant, enseignant, coefficient) (UE, parcours) <

connaissances humain humain poids

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

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

9 - 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)


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.

11 - 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).


12 – Résumé des étapes de la construction d’une base de données relationnelle

Les étapes de la construction d’une base de données relationnelle peuvent être résumées comme suit :

1/ décrire, à l’aide d’une phrase scénario, le contexte de la création de la base de données relationnelle ;

2/ éclater l’information en catégories distinctes et déterminer la liste des entités ;

3/ établir une liste des attributs pour chacune des entités et, parmi eux, un identifiant ;

4/ construire les relations entre les entités et leur donner un nom (généralement un verbe à l’infinitif) ;

5/ ajouter, si nécessaire, des attributs propres à chacune des relations et définir les cardinalités ;

6/ vérifier que le schéma obtenu répond au cahier des charges ;

7/ valider le modèle à l’aide d’un jeu de données test

8/ transformer le MDC en Modèle Logique de Données (MLD)

9/ réaliser la base de Données sous ACCESS (ou autre SGBDR)

10/ valider la base de données à l’aide d’un jeu de données test

11/ ajouter d’autres spécificités au modèle

12/ Validation de la base avec les utilisateurs, dans une situation réelle

La qualité d’une modélisation de type entités-relations peut être évaluée à l’aide de plusieurs

critères utilisables de manière combinée :

L’expressivité (qui traduit la richesse sémantique du schéma et qui peut être caractérisée par

exemple par le nombre de concepts et/ou contraintes exprimés) ;

La minimalité (qui tend à privilégier les schémas avec un nombre de redondances minimales) ;

La lisibilité (qui consiste à évaluer la représentation graphique proprement dite) ;

La simplicité (qui privilégie les schémas contenant un nombre de concepts minimal et qui peut

être mesurée en calculant le nombre d’entités et d’associations présentes sur un schéma).


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

14 - Les quatre 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.

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

environnementales

réalisation d’une première base de données …...première base access - tutoriel - pascal...

Documents