TP3 Bases de Données ACCESS

2ème année FC

Les exercices de ce TP font référence à la base de données « InfoOrsay » (dont le schéma est ci-dessous)

Création d’une base de données sous Access

L’entreprise InfoOrsay commercialise des produits informatiques (ordinateurs, imprimantes...) sur la région
parisienne. Les commandes des clients étaient gérées dans un seul tableau Excel dont un extrait est fourni en
Annexe 1.
On a déjà vu l’intérêt d’organiser les données en plusieurs tables. Le but de ce TP est de créer sous Access la
base de données correspondante au schéma relationnel ci-dessous. Elle sera constituée de cinq tables :
categorie, client, produit, commande et ligneCommande, ainsi que des contraintes d’intégrité référentielle
que ces tables doivent respecter.
On montrera aussi, une fois la base crée, comment la manipuler.

Création des tables

1. Ouvrir une base de données vide sous Access et l’appeler infoOrsay;

2. Remplir la Table1 avec les données qui se trouvent dans l’onglet categorie du fichier Excel. Pour ce
faire :

Page 1 Bases de données ACCESS– TD3

 − En haut à gauche cliquer sur « Affichage » puis « Mode Création ». Renommer categorie cette
table ;
− Renommer codeCategorie le premier champ et choisir « Texte court » pour son type de données ;
− Ajouter les deux autres champs (libelleCategorie de type « Texte court » et remise de type «
Numérique/Entier »);

− Afficher la table categorie en « Mode Feuille de données » ;

− Remplir la table avec les trois lignes relatives aux trois possibles catégories de client (voir le
fichier Excel).
3. Créer la table client en l’important, cette fois, directement d’Excel. Pour ce faire : − Sous l’onglet «
Données externes » d’Access, cliquer sur « Excel » ;
− Indiquer le parcours pour accéder au fichier sauvegardé infoOrsay.xlsx, puis faire « OK » ;
− Choisir l’onglet client, puis « Suivant » ;
− Cocher « Première ligne contient les en-têtes de colonnes », puis « Suivant » ;
− Mettre numClient en type « Entier », puis « Suivant » ;
Page 2 Bases de données ACCESS– TD3
 − Cocher « Choisir ma propre clé primaire » : numClient, puis « Suivant » ;
− « Importer vers la table » : client, puis « Terminer » et « Fermer » ;
− Double-cliquer sur la table client pour vérifier qu’elle est bien remplie.
4. Créer les tables produit et commande en suivant les mêmes étapes que ci-dessus ;
5. Afficher la table commande en « Mode Création » et vérifier que numClient est bien de type «
Numérique/Entier » ;
6. Pour créer la table ligneCommande, on procède aussi comme pour client, sauf que :
− On coche « Pas de clé primaire » au moment de l’import ;

− Une fois la table créé, on l’affiche en « Mode Création », on sélectionne en même temps les deux
champs numCommande et numClient, puis on clique sur « Clé primaire » ;

− On vérifie que numProduit et quantite sont bien de type « Numérique/Entier ».

Création des contraintes d’intégrité référentielle (CIR)

1. Sous l’onglet « Outils de base de données », cliquer sur « Relations » ;
2. Ajouter toutes les tables proposées dans la fenêtre « Afficher la table » ;
Page 3 Bases de données ACCESS– TD3
 3. Faire glisser le curseur de categorie (table client) vers codeCategorie (table categorie) ;
4. Cocher « Appliquer l’intégrité référentielle », puis « Créer » ;
5. Faire de la même manière les liens :
− De numClient (table commande) vers numClient (table client) ;
− De numCommande (table ligneCommande) vers numCommande (table commande) ; − De
numProduit (table ligneCommande) vers numProduit (table produit).

Voici ce que l’on obtient à la fin :

Manipulation de la base de données

Consultation des données : les requêtes
On commence ici pas créer une requête très simple :
1. Cliquer sur « Création de requêtes » sous l’onglet « Créer ». S’en servir pour écrire une requête qui liste
les prénoms et noms des clients enregistrés dans la base. Pour ce faire :
− Faire afficher la table nécessaire (client) ;
− Double-cliquer sur les attributs que l’on veut voir affichés (prenomClient et nomClient).
2. Cliquer sur « Affichage », passer en « Mode SQL » et regarder le SQL généré. Est-ce vraiment le code
d’une requête ?
3. Passer en « Mode Feuille de Données ». Ajouter des lignes (ajoutez vous-même et votre binôme dans la
liste des clients). Est-ce vraiment une requête ? C’est-à-dire, le SQL généré a-t-il changé ? Enregistrer
cette requête sous le nom clientBIS. Que se passe-t-il lorsque l’on actualise la table client ? Proposez
maintenant une explication à votre enseignant.
4. Une fois que vous aurez répondu à ces questions, supprimez les deux clients que vous venez
d’ajouter.

On cherche maintenant à créer une requête afin de retrouver le tableau en Annexé 1. Pour ce faire :
1. Sous l’onglet « Créer », faire « Création de requête » ;
2. Ajouter toutes les tables proposées dans la fenêtre « Afficher la table » ;
3. Faire glisser sur les cellules « Champ » du tableau en bas, toutes les colonnes que l’on veut voir
affichées, soit :
− numCommande,
− dateCommande,
Page 4 Bases de données ACCESS– TD3
 − nomClient,
− prenomClient,
− adresseClient
− telephoneClient
− categorie
− remise,
− numProduit,
− designation,
− prix,
− quantite

4. Afficher en « Mode Feuille de données » le résultat (trier par numéro de commande croissant) ;
5. Revenir à l’affichage en « Mode Création » ;
6. Ajouter une colonne dont le nom de champ est montantHT : [prix]*[quantite] ;
7. Ajouter une colonne dont le nom de champ est TVA : 20 ;
8. Ajouter une colonne dont le nom de champ est montantTTC :
[montantHT]*(1+[TVA]/100)*(1[remise]/100) ;

9. Afficher en « Mode Feuille de données » cette requête. Vérifier qu’elle correspond bien au Annexe
Page 5 Bases de données ACCESS– TD3
 1.
Mise à jour des données
Maria Rossi change de numéro de téléphone. Il est maintenant : 01 74 23 20 55. Apporter cette modification à la
table client et vérifier que la Requete1 (en « Mode Feuille de données »), est bien cohérente avec ce
changement.

Ajout des données

Nous sommes le 8 mai 2019. Le client Kevin Dufour souhaite commander 1 PC Pentium 120MHz, 1
imprimante laser et 3 cartouches laser. Il est domicilié au 25 rue des Châtaigniers à Gif-sur-Yvette (91190) et a
pour numéro de téléphone 01 23 45 67 89. C’est un client particulier n’ayant pas de carte de fidélité. Complétez
les tables nécessaires afin d’enregistrer cette nouvelle commande.

Requêtes sur plusieurs tables : jointures et CIR

On va maintenant chercher à comprendre comment Access gère ses contraintes d’intégrité référentielle (CIR)
au moment d’une requête.

1. Créer une requête qui affiche, pour chaque client, son numéro, son nom, son prénom et le taux de remise
qui lui est réservé ;

2. Vérifier que, si on passe en « Mode Feuille de données », cette requête affiche bien huit lignes ;
3. Revenir à l’affichage en « Mode Création » ;

4. Se positionner sur le lien entre les deux tables et le supprimer ;

Page 6 Bases de données ACCESS– TD3

 5. Remarque : Cette suppression ne concerne que la requête en cours. La CIR entre les deux tables est
toujours présente, comme on peut le vérifier à la rubrique « Relations ».
6. Passer à nouveau en « Mode Feuille de données », on voit maintenant s’afficher vingt-quatre lignes
(parmi lesquelles on retrouve les huit précédentes).
7. Comment expliquez-vous cette différence ? Quel était le rôle du lien entre les tables client et categorie ?
Pour répondre à cette question :
− Supprimez la Requete2 et la récréer mais cette fois en ajoutant deux champs à la requête :
categorie (table client) et codeCategorie (table categorie) ;

− Vous aurez toujours huit lignes si le lien entre le tables est maintenu, et vingt-quatre lignes sinon.
Qu’est-ce qui caractérise les sept lignes du premier cas ? Proposez une explication à votre
enseignant.

Page 7 Bases de données ACCESS– TD3

Annexe I

Date N° Prix Montant Montant

Nom Prenom Adresse Téléphone Cat. Remise Designation Qté TVA
Cde Produit HT HT TTC
25/03/19 ROSSI Maria 21 avenue de la Forêt, 01 74 23 21 E 10% 1233 Imprimante laser 1.222 2 2.444 € 19,60% 2.631 €
91400 Orsay 33 €
25/03/19 ROSSI Maria 21 avenue de la Forêt, 01 74 23 21 E 10% 1569 PC Pentium 1.500 5 7.500 € 19,60% 8.073 €
91400 Orsay 33 120Mhz €
25/03/19 ROSSI Maria 21 avenue de la Forêt, 01 74 23 21 E 10% 342 Cartouche laser 158 € 4 632 € 19,60% 680 €
91400 Orsay 33

26/03/19 DEGHAR Ali 33 avenue des Platanes, 01 46 57 28 P 0% 342 Cartouche laser 158 € 1 158 € 19,60% 189 €
91400 Orsay 32
26/03/19 DEGHAR Ali 33 avenue des Platanes, 01 46 57 28 P 0% 651 Carte son 16 bits 300 € 1 300 € 19,60% 359 €
91400 Orsay 32

02/04/19 TURNER J.- 3 rue des Rosiers, 92160 01 34 56 43 E 10% 1233 Imprimante laser 1.222 1 1.222 € 19,60% 1.315 €
Baptiste Antony 22 €

03/04/19 ROSSI Maria 21 avenue de la Forêt, 01 74 23 21 E 10% 890 Imprimante jet 870 € 2 1.740 € 19,60% 1.873 €
91400 Orsay 33 d'encre
03/04/19 ROSSI Maria 21 avenue de la Forêt, 01 74 23 21 E 10% 1233 Imprimante laser 1.222 1 1.222 € 19,60% 1.315 €
91400 Orsay 33 €
03/04/19 ROSSI Maria 21 avenue de la Forêt, 01 74 23 21 E 10% 1569 PC Pentium 1.500 10 15.000 19,60% 16.146
91400 Orsay 33 120Mhz € € €
03/04/19 ROSSI Maria 21 avenue de la Forêt, 01 74 23 21 E 10% 2345 Scanner à main 327 € 1 327 € 19,60% 352 €
91400 Orsay 33

05/04/19 SCHMITT Michel 41 impasse des Tilleuls, 01 53 22 41 P 0% 1569 PC Pentium 1.500 1 1.500 € 19,60% 1.794 €
91000 Evry 33 120Mhz €
06/04/19 DUVAL Tatiana app. 19, 23 r. des Sapins, 01 32 56 43 F 5% 342 Cartouche laser 158 € 1 158 € 19,60% 180 €
91300 Massy 21
06/04/19 DUVAL Tatiana app. 19, 23 r. des Sapins, 01 32 56 43 F 5% 651 Carte son 16 bits 300 € 1 300 € 19,60% 341 €
91300 Massy 21
06/04/19 DUVAL Tatiana app. 19, 23 r. des Sapins, 01 32 56 43 F 5% 670 Enceintes stéréo 122 € 1 122 € 19,60% 139 €
91300 Massy 21

18/04/19 ROSSI Maria 21 avenue de la Forêt, 01 74 23 21 E 10% 342 Cartouche laser 158 € 1 158 € 19,60% 170 €
91400 Orsay 33
18/04/19 ROSSI Maria 21 avenue de la Forêt, 01 74 23 21 E 10% 651 Carte son 16 bits 300 € 1 300 € 19,60% 323 €
91400 Orsay 33

18/04/19 ROSSI Maria 21 avenue de la Forêt, 01 74 23 21 E 10% 342 Cartouche laser 158 € 3 474 € 19,60% 510 €
91400 Orsay 33
18/04/19 ROSSI Maria 21 avenue de la Forêt, 01 74 23 21 E 10% 1569 PC Pentium 1.500 2 3.000 € 19,60% 3.229 €
91400 Orsay 33 120Mhz €

26/04/19 DUVAL Matthieu 3 rue des Acacias, 91300 06 22 33 41 F 5% 1569 PC Pentium 1.500 1 1.500 € 19,60% 1.704 €
Massy 27 120Mhz €
26/04/19 DUVAL Matthieu 3 rue des Acacias, 91300 06 22 33 41 F 5% 890 Imprimante jet 870 € 1 870 € 19,60% 988 €
Massy 27 d'encre

02/05/19 TURNER J.- 3 rue des Rosiers, 92160 01 34 56 43 E 10% 1569 PC Pentium 1.500 4 6.000 € 19,60% 6.458 €
Baptiste Antony 22 120Mhz €

04/05/19 DUVAL Tatiana 23 rue des Sapins, 91300 01 32 56 43 F 5% 342 Cartouche laser 158 € 1 158 € 19,60% 180 €
Massy 21
04/05/19 DUVAL Tatiana 23 rue des Sapins, 91300 01 32 56 43 F 5% 651 Carte son 16 bits 300 € 1 300 € 19,60% 341 €
Massy 21