TP : Cration dune BD sous Access

Objectif du TP
Lobjectif de ce TP est de crer une Base de Donnes sous Access (versions antrieures Access 2007) et
dinterroger cette BD avec des requtes dites interactives (Query By Example).

CREATION DE LA BASE DE DONNEES

Nous allons crer ensemble la base de donnes des employes dune mairie reprsente par le schma
relationnel suivant :

Service (NoSrv:NumAuto, NomSrv:Texte(20))

Employe (NoEmp:NumAuto, Nom:Texte(20), Prenom:Texte(20), Sexe:Texte(1), Salaire:
Montaire, Prime:Montaire, #NoSrv:Entier, DateEmbauche:Date)
Projet (NoProjet:Entier, LibelleProjet:Texte(20), #NoSrv:Entier)
Intervenir (#NoProj:Entier, #NoEmploye:Entier, NbHeures:Entier)

1. Lancez le logiciel Access et cliquez sur licne Base de donnes vide pour crer une nouvelle BD. Donnez
un nom significatif votre fichier (vitez si possible accents et espaces) et enregistrez-le sur votre espace
disque personnel (P:/). Gardez lextension accdb pour que le systme puisse associer correctement votre
fichier au logiciel Access et crez le fichier.
2. laide du menu Crer > Cration de table, nous allons crer une table en prcisant les informations
de chaque attribut de cette table :
a. Dans la premire cellule, saisissez le nom du premier attribut de la table Service ;
b. Dans la cellule de droite, choisissez laide de la liste droulante le type de lattribut nosrv. Dans le
schma relationnel ci-dessus, 20 correspond la taille du champ ;
c. Dans la cellule encore droite, vous pouvez commentez lintrt de cet attribut dans cette table, par
exemple : numro du service ;
d. Dans la ligne suivante, faites de mme que prcdemment pour lattribut suivant de Service. On
nautorisera pas de chaine vide pour cet attribut ;
e. Pour dfinir la cl primaire de la table, slectionnez les lignes laide de la souris en slectionnant les
cases bleues gauche des noms des champs et cliquez sur licne correspondant une cl.
f. Cliquez sur licne Affichage pour afficher la table en mode feuille de donnes. Bien sr, vous
lui donnerez le nom adquat !
3. Faites de mme que prcdemment en crant la table Employe. Ne vous
proccupez pas de la cl trangre pour linstant. La date dembauche sera
une date au format abrg. La valeur par dfaut pour cette date sera la
date du jour ; pour cela :
a. Cliquez sur la cellule droite de valeur par dfaut, puis cliquez
sur pour faire apparaitre le gnrateur dexpression
dAccess (cf. ci-contre) ;
b. Faites apparaitre les fonctions intgres de date/heure et choisissez la plus adquate pour
rcuprer la date du jour.

Lise BRENAC & Denis PALLEZ Module 1104 (Introduction aux BDs) 1/6
IUT de Nice Cte dAzur Dpartement Informatique TP : Cration dune BD sous Access
____________________________________________________________________________________________
c. On souhaite saisir une contrainte dintgrit qui prcise que le salaire dun employ est compris entre
0 et 7000 . Pour cela, utilisez le gnrateur dexpression pour saisir loprateur Entre dans la
proprit valide si pour lattribut Salaire.
d. Vrifiez que la contrainte cre prcdemment est constamment vrifie par le SGBD : affichez la
table Employ en mode feuille de donnes puis saisissez un nouvel employ avec un salaire ngatif.
Quel est le message derreur affich par Access ?

e. Pour annuler la saisie dun enregistrement, appuyez sur la touche Escape ou chap.
4. Crez les tables Projet et Intervenir.
5. Vrifiez que la cl primaire de la table Intervenir a t correctement dfinie :
a. Essayez de saisir lenregistrement (rien),3,45 . Quel est le message derreur ?
.
b. Essayez de saisir les enregistrements suivants 1,2,25 puis 1,2,14. tes-vous capable de saisir le dernier
enregistrement ? Quel est le message derreur ? Comment lexpliquez-vous ?
..
c. Si vous navez pas deux messages derreur, retournez la question 4.

IMPORTATION DE DONNEES
6. Par chance, les donnes correspondantes aux employs de la mairie ont t sauvegardes mais dans des
fichiers texte nayant pas tous le mme format. Les SGBDs donnent souvent la possibilit dimporter ou
exporter des donnes textuelles si le module dimport est correctement configur :
a. Cliquez sur la table Employ avec le bouton droit de la souris et choisissez le module
dimportation partir dun fichier texte ;
b. Spcifiez le fichier Employe.txt qui se trouve dans S:\, cochez loption de copie des
enregistrements la table et validez votre choix ;
c. Cliquez une premire fois sur Suivant pour valider le fait que les donnes sont dlimites ;
d. Cliquez sur le bouton Avanc pour paramtrer correctement limportation des donnes en portant
une attention particulire au sparateur de champs, au dlimiteur de date, lordre de la date et au
symbole dcimal. Pour paramtrer correctement limport, ouvrez le fichier texte dans un simple
diteur de texte (Scite par exemple), analysez-le et dduisez-en les valeurs des paramtres. 31
enregistrements doivent tre insrs dans la table. Importez les donnes des tables Projet et
Intervenir (chaque table a t enregistre par une personne diffrente !).

RELATIONS ENTRE LES TABLES OU CLES ETRANGERES

8. Access donne la possibilit de crer interactivement les cls trangres en crant des relations 1- entre les
tables. Dans le menu Outils de base de donnes > Relations, ajoutez un seul exemplaire de
chaque table de votre base de donnes. Pour supprimer un exemplaire, cliquer sur la table et appuyez sur
Suppr.

9. Afin de prciser que lattribut NoProj dans la table Intervenir est une cl trangre qui correspond
lattribut NoProjet dans Projet (qui est cl primaire), il faut slectionner avec la souris lattribut NoProjet
dans la table Projet et le glisser-lcher exactement sur lattribut NoProj dans la table Intervenir puis

Lise BRENAC & Denis PALLEZ Module 1104 (Introduction aux BDs) 2/6
IUT de Nice Cte dAzur Dpartement Informatique TP : Cration dune BD sous Access
____________________________________________________________________________________________
appliquer lintgrit rfrentielle pour une modification ou une suppression (si une modification ou une
suppression a lieu dans la table Projet, alors elles sont rpercutes automatiquement par le SGBD dans la
table Intervenir). Bien souvent, les noms des attributs cls primaires et cls trangres sont identiques
mais ce nest pas obligatoire ; cest simplement pour viter de nommer explicitement lattribut rfrenc par
la cl trangre. En effet, en plus du schma relationnel et du symbole #, il aurait t utile de prciser que
NoProj de Intervenir rfrence NoProjet de Projet.

10. Crez lautre cl trangre de Intervenir.

11. Faites de mme avec les cls trangres NoSrv. Que se passe-t-il ?
...
12. Contrairement ce que vous propose Access, vous souhaitez absolument suivre les rgles dintgrit
rfrentielle. Que proposez-vous comme solution pour rsoudre le problme de la question prcdente ?

13. Saisir des donnes dans la table Service.
Voici quelques propositions si vous navez pas
du tout dimagination : Direction, Commercial,
Comptabilit, Terrassement, Maonnerie,
Espaces Verts, Culture, Ressources Humaines,
Scurit
14. Refaites la question 11. Le schma de votre
Base de Donnes doit ressembler ci-contre.

REQUETES INTERACTIVES (QUERY BY EXAMPLE)

15. Avant de maitriser le langage informatique ddi aux requtes informatiques intitul SQL, Access et dautres
SGBD proposent la possibilit de construire des requtes en
mode interactif (appele mode cration par Access). Pour
crer une requte interactive, cliquez sur licne Crer >
Cration de requte. Par dfaut, Access vous propose
le mode de cration interactif et commence par vous
demander les tables qui vont intervenir dans votre requte.
Par exemple, si vous souhaitez afficher lensemble des
employs de votre base de donnes, il suffira dajouter la
table Employ la requte interactive en cours de cration
(cf. ci-contre).
a. Vous venez de prciser sur quelle(s) table(s) vous souhaitez travailler, mais vous devez galement
prciser les attributs que vous souhaitez prsenter dans le rsultat. Pour linstant, nous souhaitons
afficher les champs numro, nom et prnom de lemploy. Modifiez la requte interactive pour en
tenir compte.
b. Pour visualiser le rsultat de votre requte, cliquez sur licne Affichage > Mode Feuille de
Donnes. Pour modifier la requte, cliquez sur licne Affichage > Mode Cration.

c. Modifiez votre requte pour voir tous les employs qui gagnent plus de 2000 (le salaire ne doit pas
tre affich). Astuce : utilisez la ligne Critres.

Lise BRENAC & Denis PALLEZ Module 1104 (Introduction aux BDs) 3/6
IUT de Nice Cte dAzur Dpartement Informatique TP : Cration dune BD sous Access
____________________________________________________________________________________________
d. Enregistrer votre requte en fermant la fentre et en lui donnant un nom TRS explicite comme
TP1_Q15_Employe_avec_salaire_superieur_a_2000 par exemple. Vous pouvez modifier ce
nom tout moment en cliquant sur la requte avec le bouton droit de la souris.
16. Crer une requte interactive qui affiche le nom et le prnom dans une seule colonne et le salaire dans une
autre colonne. Pour cela, vous utiliserez le menu contextuel Crer (bouton droit de la souris) sur la zone
Champ ainsi que du symbole & comme oprateur de concatnation de texte. Afficher le rsultat puis revenez
en mode interactif. Remplacer Expr1 par le texte Employ pour renommer temporairement le nom de la
colonne lors de laffichage du rsultat.

17. Crer une requte qui affiche le rsultat suivant : .

18. Afficher le nom des employs embauchs en Janvier.
19. Afficher le prnom des employs tri par ordre croissant.
20. Il est possible de supprimer les prnoms en double des rsultats de la question prcdente en affichant les
proprits de la requte (bouton droit de la souris sur le fond bleu o les tables sont affiches) et en
modifiant loption Valeurs distinctes.
21. Affichez toutes les informations (i.e. tous les attributs) des employs masculins (ou fminin si vous prfrez).
Pour cela, plutt que de saisir chaque champ, il suffit simplement dutiliser le caractre * qui reprsente tous
les attributs dune table.
22. Affichez les numros de projet qui font intervenir lemploy N5. Quen dduisez-vous ? Pour vous
convaincre, testez nouveau votre requte mais avec lemploy N14 mais seulement aprs avoir rpondu
la question !
23. Il existe un moyen simple pour changer facilement une valeur dans une requte sans modifier la requte elle-
mme : cest ce quon appelle des requtes paramtres. Pour cela, faites une copie de la requte 22 et
remplacer le chiffre 5 ou 14 par un nom de variable quAccess ne connait pas comme par exemple
NumeroDeEmploye et le mettre entre crochets. Testez votre requte et saisissez le chiffre quil vous plaira !

24. Affichez toutes les informations des employs dont le numro est compris entre 10 et 20.
25. Affichez les informations des employs dont le nom commence par LE. Astuce : Pensez utiliser le
gnrateur dexpressions et utiliser les fonctions intgres sur du texte.
26. Affichez les informations des employs dont le nom commence par LE ou LA.
27. Affichez les informations des employs dont le nom ne commence ni par LE ni par LA.
28. Affichez les informations des employs qui nont pas de prime. Pour cela, il est ncessaire de conserver que
les enregistrements pour lesquels lattribut prime na pas t renseign. (Gnrateur dexpression >
Constantes > Null).

29. On souhaite calculer le salaire moyen des employs de la mairie. Pour cela, il est ncessaire dappliquer une
opration particulire sur lensemble des enregistrements de la table. Cliquez sur licne Totaux pour faire
apparatre une nouvelle ligne Opration et choisissez la moyenne (pensez regarder les autres oprations
possibles, ca pourra vous aider plus tard).
30. Afficher le numro des employs et le service dans lequel ils sont affects.

Lise BRENAC & Denis PALLEZ Module 1104 (Introduction aux BDs) 4/6
IUT de Nice Cte dAzur Dpartement Informatique TP : Cration dune BD sous Access
____________________________________________________________________________________________
31. Dans une requte base sur la question 30, on souhaite compter le nombre demploy pour chaque service.
Pour cela, on va regrouper les enregistrements qui ont la mme valeur de NoSrv et compter le nombre
demploy reprsent par leur numro. Enfin, triez le rsultat suivant un numro de service croissant.

LIAISON ENTRE LES TABLES OU JOINTURE

32. Dans la question 31, on a compt le nombre demploys par service. Malheureusement, on ne connait pas
forcment le nom du service correspondant au numro. Or, lassociation entre le nom et le numro du
service se trouve dans la table Service. Nous allons donc faire la liaison entre la table Employ de notre
requte 31 et la table Service :
a. Slectionnez la requte 31 dans les objets Access et crez une copie de cette requte ;
b. Ouvrez cette nouvelle requte en mode interactif ;
c. Avec le menu contextuel, ajoutez la table Service. Access a automatiquement ajout la relation qui
existe entre Service et Employ. Nous y reviendrons plus tard, mais il ne faut surtout pas
confondre le schma qui nous a servi construire les cls trangres (cf. question 8) avec le schma
affich pour crer une requte. Ils ont une signification compltement diffrente.
En ralit, nous sommes en train de crer une requte dont les donnes proviennent de la table
Employ lie la table Service ; autrement dit, la requte va utiliser les employs qui travaillent
dans un service.
d. Ajoutez le nom du service.
33. Crez une requte qui affiche le libell du projet, le numro du projet et le nombre dheures travailles dans
le projet. Nommez la dernire colonne HeuresTravaillees.
34. Affichez les noms des employs et le numro de projet dans lesquels ils interviennent.
35. Il est important de comprendre que les tables et les liaisons entre elles affiches dans la partie suprieure de
la requte interactive ont une signification diffrente de celles affiches dans la fentre Relations, utilise
pour dfinir les cls trangres (question 9). Pour preuve :
a. Faites une copie de la requte 34 et slectionnez le lien entre les deux tables puis supprimez le.
Excutez la requte. Constatez !
b. Affichez les relations entre les tables de la base de donnes (menu Outils de base de donnes
> Relations). Les relations entre les tables sont toujours existantes. Fermer la fentre.

c. Revenez la requte et modifiez-la pour compter le nombre denregistrements. Notez ce chiffre et

appelons c.
d. Crez une autre requte qui compte le nombre denregistrements de la table Employ et notez ce
chiffre e.
e. Crez une autre requte qui compte le nombre denregistrements de la table Intervenir et notez
ce chiffre i.
f. Quel est le lien entre c, e et i ? Les enregistrements obtenus la
question 35.a sont le rsultat du produit cartsien (toutes les
combinaisons possibles entre tous les enregistrements) de
Intervenir et Employ.

g. Inversement, il est possible de crer une relation temporaire entre

les deux tables juste pour les besoins de la requte. Crez la requte
Lise BRENAC & Denis PALLEZ Module 1104 (Introduction aux BDs) 5/6
IUT de Nice Cte dAzur Dpartement Informatique TP : Cration dune BD sous Access
____________________________________________________________________________________________
ci-contre et excutez-la. Vrifiez que les relations de la Base de Donnes nont toujours pas t
modifies en refaisant la question 35.b. Proposez lnonc littral correspondant ce rsultat
( requte qui affiche . ).

Pour aller plus loin

36. Afficher les 50 premiers pourcent des employs. Astuce : utilisez les proprits de la requte.
37. Afficher le nom de lemploy et sa date dembauche dans le format mercredi 25 janvier 2010.
38. Afficher le nom des employs et leurs salaires augments de 10%.
39. Afficher le nom des employs (sans doublons) qui ont leur conjoint comme employ.
40. Afficher le nom des employs qui participent plusieurs projets.
41. Afficher toutes les informations des employs qui participent au plus grand nombre de projets.
42. Afficher toutes les informations des employs qui participent aucun projet.
43. Afficher les noms des services dont le salaire moyen est suprieur au salaire moyen de la mairie.
44. Affiche le nom et numro de service des employs qui interviennent dans au moins 2 projets ?
45. crire en franais ce que pourrait tre lnonc de la requte interactive suivante :

Sources pour ce TP

Lise BRENAC & Denis PALLEZ Module 1104 (Introduction aux BDs) 6/6