Embed Size (px)
Citation preview
Les bases de données
TP n°1 Objectifs
Au terme de la séance, l’étudiant sera capable : - de savoir les limites du système de fichiers ;
- de définir une base de données ;
- de connaitre les avantages d'une base de données ; - de définir un Système de Gestion de Base de données ;
- de connaitre quelques SGBD actuels ainsi leurs fonctions.
Questions de cours
1) Qu'est ce qu'une donnée ?
2) Qu'est ce qu'une information ?
3) Qu'est ce qu'un fichier ?
4) Quelles sont les limites du système de fichiers ?
5) Qu'est ce qu'une base de données ?
6) Quelles sont les avantages de la base de données ?
7) Qu'est ce qu'un SGBD ?
8) Quel est le rôle d'un SGBD ?
9) Citez quelques SGBD que vous connaissez ?
10) Quelles sont les fonctions d'un SGBD ?
Correction du TP n°1
1) Qu'est ce qu'une donnée ?
Réponse :
Une donnée est description élémentaire d'une information.
11) Qu'est ce qu'une information ?
Réponse :
(a) Plusieurs données regroupées et se rapportant à un même contexte donnent naissance à
l'information.
(b) L'information est tout renseignement, écrit, sonore, visuel ou audiovisuel, codé susceptible
d'être stocké ou transmis, en vue de déclencher ou de modifier le comportement d'un processus.
2) Qu'est ce qu'un fichier ?
Réponse :
Un fichier est une suite d’informations codé sous forme binaire.
3) Quelles sont les limites du système de fichiers ?
Réponse :
Les inconvénients du système de fichiers sont :
Nécessiter d'écrire des programmes
Manque de sécurité.
La redondance et de MAJ
Accès exclusif (un utilisateur à la fois)
4) Qu'est ce qu'une base de données ?
Réponse :
Une base de données est une collection de données structurées relatives à un ou plusieurs domaines du
monde réel.
Exemple : BD d'une bibliothèque.
5) Quelles sont les avantages de la base de données ?
Réponse :
Les avantages de la base de données sont :
- Centralisation : Les données peuvent êtres utilisées par plusieurs programmes et plusieurs
utilisateurs.
- Indépendance entre données et programmes : Dans une BD les données sont décrites
indépendamment des programmes. Ce qui n'est pas le cas avec les fichiers.
- Intégration des liaisons entre les données : Pas besoin d'un programme pour retrouver les
liens entre les données.
- Intégrité des données : Ce sont des règles de sécurité assurant la cohérence des données :
1. Unicité des enregistrements.
2. Interdiction de la suppression des données utilisées par d'autres données.
- Concurrence d'accès : Plusieurs utilisateurs peuvent accéder simultanément à la BD.
6) Qu'est ce qu'un SGBD ?
Réponse :
Un Système de Gestion de Base de Données(SGBD) est un logiciel qui permet de : décrire, modifier,
interroger et administrer les données d'une base de données.
7) Quel est le rôle d'un SGBD ?
Réponse :
Il joue le rôle d’interface entre d’interface entre l’utilisateur et la base de données
8) Citez quelques SGBD que vous connaissez ?
Réponse :
ORACLE
MICROSOFT SQL SERVER
MySQL
Microsoft Access
9) Quelles sont les fonctions d'un SGBD ?
Réponse :
- La définition des données
Le SGBD nous permet de créer et de décrire les objets de la base de données (table, liens,
utilisateur…), grâce au Langage de Description de Données (LDD).
- La manipulation des données
La manipulation des données peut être :
La recherche
La lecture
La suppression
La modification
L'ajout
Le SGBD nous offre un Langage de Manipulation de Données (LMD)
- L'intégrité des données
C'est l'ensemble des opérations de contrôle que le SGBD effectue pour préserver la cohérence des
données.
Exemple : Vérification de la validité de la valeur d'un champ.
- La gestion des accès concurrents
Le SGBD gère l'accès simultané des utilisateurs à la base de données.
- La confidentialité
Tous les utilisateurs d'une base de données ne sont pas supposés pouvoir consulter toutes les
informations. Des sous schémas de la base permettent de résoudre ce problème en plus des mots de
passes et droits d'accès.
- La sécurité de fonctionnement
Faire une copie de sauvegarde de la base. Remise en marche de la base en cas de panne
TP n°2
Objectifs
Au terme de la séance, l’étudiant sera capable : - de déduire la structure d’une B.D. à partir d’un énoncé décrivant un domaine donné.
- de détecter les anomalies dans la structure des tables.
Exercice n°1
Soit à représenter l’activité d’une bibliothèque disposant d’un ensemble de livres qu’elle met à la disposition de
ses abonnés. Chaque livre de la bibliothèque est décrit à l’aide d’un code unique, un titre, un auteur, un éditeur et
une date de parution. L’enregistrement d’un nouvel abonné consiste à renseigner son numéro qui servira comme
identifiant, son nom et prénom, son adresse et son numéro de téléphone.
Chaque abonné peut emprunter plusieurs livres. A chaque emprunt on enregistre le code du livre, le numéro de
l’abonné et la date d’emprunt. Au retour du livre on enregistre la date de retour.
Questions :
1. Souligner à partir de l’énoncé les noms des colonnes et compléter le tableau ci-dessous. Nom colonne Type de
données Taille Obligatoire Valeur par
défaut Valeurs autorisées Sujet
(1)
(2)
(1)
(2)
2. Etablir la liste des tables
Liste des tables
Nom table Description Sujet
3. Affecter les colonnes aux tables et en donner une description textuelle.
…………………(……………….,…...……….,……………….,………..……….,………………)
…………………(……………….,……….………..,…..…….……….,………...……….,….……)
…………………(……………………….,………………….,………………….,………..……….)
4. Préciser les clés primaires des tables.
…………………(……………….,…...……….,……………….,………..……….,………………)
…………………(……………….,……….………..,…..…….……….,………...……….,….……)
…………………(……………………….,………………….,………………….,………..……….)
5. Identifier les liens entre les tables en utilisant le tableau ci-dessous, déduire la description textuelle :
Table mère Table fille Clé primaire Clé étrangère
…………………(……………….,…...……….,……………….,………..……….,………………)
…………………(……………….,……….………..,…..…….……….,………...……….,….……)
…………………(……………………….,………………….,………………….,………..……….)
6. Représenter la structure de cette base de données sous forme graphique.
Exercice n°2
Pour décrire les employés d’une entreprise et leur répartition entre les différents services la table suivante a été
créée.
N°
emp
Nom Prénom Date
naissance
N°
Service
Nom Service Date
création
Service
1 TOUNSI Safa 01/10/1980 20 Financier 01/01/1970
2 KEFI Ali 12/09/1981 10 Administratif 01/01/1975
3 BEJI Mohamed 14/04/1977 20 Financier 01/01/1970
4 TOUATI Lamia 21/06/1980 20 Financier 01/01/1970
5 SOUSSI Leila 28/11/1982 10 Administrative 01/01/1975
6 SFAXI Nouri 20/08/1990 30 Juridique 01/04/1980
7 GABSI Mouna 04/04/1987 10 Administratif 01/01/1957
8 JERBI Lotfi 09/06/1988 30 Juridique 01/04/1980
9 EZZAR Samia 12/12/1982 20 Financier 01/01/1970
Questions :
1. Identifier les anomalies de cette structure.
………………..............................
……………………………………………………………………………………………
……………………………………………………………………………………………
……………………………………………………………………………………………
…………………………………………………………………………………
……………………………………………………………………………………………
……………………………………………………………………………………………
……………………………………………………………………………………………
…………………………………………………………………………………
………………..............................
……………………………………………………………………………………………
……………………………………………………………………………………………
……………………………………………………………………………………………
…………………………………………………………………………………
2. Donner la nouvelle structure.
………………………………………………………………………………………………………………………
………………………………………………………………………………………………………………………
………………………………………………………………………………………………………………………
………………………………………………………………………………………………………………………
………………………………………………………………………………………………………………………
………………………………………………………………………………………………………………………
…………………………………………………………………………………………………………………...….
…..………………..
……………………………
Exercice n°1
Correction du TP2
Soit à représenter l’activité d’une bibliothèque disposant d’un ensemble de livres qu’elle met à la disposition
de ses abonnés.
Chaque livre de la bibliothèque est décrit à l’aide d’un code unique, un titre, un auteur, un éditeur et une date
de parution.
L’enregistrement d’un nouvel abonné consiste à renseigner son numéro qui servira comme identifiant, son
nom et prénom, son adresse et son numéro de téléphone.
Chaque abonné peut emprunter plusieurs livres. A chaque emprunt on enregistre le code du livre, le numéro
de l’abonné et la date d’emprunt. Au retour du livre on enregistre la date de retour.
Questions :
1. Souligner à partir de l’énoncé les noms des colonnes et compléter le tableau ci-dessous.
Nom colonne Type de
données
Taille Obligatoire Valeur par
défaut
Valeurs autorisées
Sujet
Code_livre Texte 10 O Livres
Titre Texte 50 O Livres
Auteur Texte 50 N Livres
Editeur Texte 30 N Livres
Date_parution Date N Livres
Num_abonne Numérique 5 O Abonnés
Nom_abonne Texte 20 O Abonnés
Prenom_abonne Texte 20 O Abonnés
Adresse Texte 50 Abonnés
Tel Texte 20 Abonnés
Code_liv_emp Texte 10 O (1) Emprunts
Num_ab_emp Numérique 5 O (2) Emprunts
Date_emprunt Date O (3) Emprunts
Date_retour Date (4) Emprunts
(1) Les valeurs de la colonnes Code_livre_emp doivent exister dans la colonne Code_livre : un livre emprunté doit exister dans la
bibliothèque.
(2) Les valeurs de la colonnes Num_ab_emp doivent exister dans la colonne Num_abonne: une personne ne peut emprunter un
livre que lorsqu'il est déjà existant.
(3) La valeur par défaut de la date d'emprunt est la date système
(4) La date de retour du livre doit être supérieur à la date d'emprunt
2. Etablir la liste des tables
Liste des tables
Nom table Description Sujet
Livre Regroupe l'ensemble des livres de la
bibliothèque
Livres
Abonne Regroupe les personnes abonnées à la
bibliothèque
Abonnés
Emprunt Stocke l'historique des emprunts de
livres
Emprunts
3. Affecter les colonnes aux tables et en donner une description textuelle.
Livre(Code_livre,Titre,Auteur,Editeur,Date_parution)
Abonne(Num_abonne,Nom_abonne,Prenom_abonne,Adresse,Tel)
Emprunt(Code_liv_emp,Num_ab_emp,Date_emprunt,Date_retour)
4. Préciser les clés primaires des tables.
Livre(Code_livre,Titre,Auteur,Editeur,Date_parution)
Abonne(Num_abonne,Nom_abonne,Prenom_abonne,Adresse,Tel)
Emprunt(Code_liv_emp,Num_ab_emp,Date_emprunt,Date_retour)
5. Identifier les liens entre les tables en utilisant le tableau ci-dessous, déduire la description textuelle :
Table mère Table fille Clé primaire Clé étrangère
Livre Emprunt Code_livre Code_liv_emp
Abonne Emprunt Num_abonne Num_ab_emp
Livre(Code_livre,Titre,Auteur,Editeur,Date_parution)
Abonne(Num_abonne,Nom_abonne,Prenom_abonne,Adresse,Tel)
Emprunt(Code_liv_emp#,Num_ab_emp#,Date_emprunt,Date_retour)
6. Représenter la structure de cette base de données sous forme graphique.
Exercice n°2
Pour décrire les employés d’une entreprise et leur répartition entre les différents services la table suivante a été
créée.
N° emp Nom Prénom Date de
naissance
N°
Service
Nom Service Date création
Service
1 TOUNSI Safa 01/10/1980 20 Financier 01/01/1970
2 KEFI Ali 12/09/1981 10 Administratif 01/01/1975
3 BEJI Mohamed 14/04/1977 20 Financier 01/01/1970
4 TOUATI Lamia 21/06/1980 20 Financier 01/01/1970
5 SOUSSI Leila 28/11/1982 10 Administrative 01/01/1975
6 SFAXI Nouri 20/08/1990 30 Juridique 01/04/1980
7 GABSI Mouna 04/04/1987 10 Administratif 01/01/1957
8 JERBI Lotfi 09/06/1988 30 Juridique 01/04/1980
9 EZZAR Samia 12/12/1982 20 Financier 01/01/1970
Questions :
3. Identifier les anomalies de cette structure.
Incohérence de données :
On remarque que le service N°10, le nom du service n'est pas le même pour les
employés N°2,5 et 7 (Administratif, Administrative).
Pour ce même service, la date de création diffère entre les employés N°2,5 et 7
(01/01/1975).
Redondance de données
On remarque que lorsqu'il y a plusieurs employés appartenant au même service, les
informations relatives) ce dernier sont dupliquées ce qui a entraîner les incohérences
précédentes.
4. Donner la nouvelle structure.
Nous proposons d'éclater la table actuelle en deux tables:
Service(Num_serv,Nom_serv,Date_creat_serv)
Employe (Num_emp,Nom_emp,Prenom_emp,Date_naiss_emp,Num_serv#)
Service
N° Service Nom service Date création Service
10 Administratif 01/01/1975
20 Financier 01/01/1970
30 Juridique 01/04/1980
Employe
N°emp Nom Prénom Date naissance N° Service
1 TOUNSI Safa 01/10/1980 20
2 KEFI Ali 12/09/1981 10
3 BEJI Mohamed 15/04/1977 20
4 TOUATI Lamia 21/06/1980 20
5 SOUSSI Leila 28/11/1982 10
6 SFAXI Nouri 20/08/1990 30
7 GABSI Mouna 04/04/1987 10
8 JERBI Lotfi 09/06/1988 30
9 EZZAR Samia 12/12/1982 20
TP n°3 (Les tables)
Objectifs de la séance
Au terme de la séance, l’étudiant sera capable : - de créer des tables, les relations entre ces tables et de saisir des enregistrements via le logiciel Access
- de vérifier que le SGBD Access assure une cohérence des données par la contrainte d’intégrité
référentielle
Travail à réaliser :
1) Lancer le logiciel Access et créer une nouvelle base de données.
2) Nommer cette base "biblio01" et l'enregistrer.
3) Créer les tables :
Classes comprenant les champs :
CODCLA de type NuméroAuto (à mettre en clé primaire)
LIBELCLA de type Texte
Elèves comprenant les champs :
CODELE de type NuméroAuto (à mettre en clé primaire)
CODCLA de type Numérique
TITELE de type Texte
NOMELE de type Texte
PRENOMELE de type Texte
Emprunts comprenant les champs :
CODLIV de type Numérique (à mettre en clé primaire)
CODELE de type Numérique (à mettre en clé primaire)
DATEMPRUNT de type Date/Heure
DATRETOUR de type Date/Heure
Livres comprenant les champs :
CODLIV de type NuméroAuto (à mettre en clé primaire)
TITLIV de type Texte
AUTLIV de type Texte
ANPARUTIONLIV de type Texte
ETATLIV de type Texte
4) Mettre les champs :
TITELE en zone de liste comprenant les valeurs : "M", "Mlle" et "Mme" avec comme valeur par
défaut "M" et valide uniquement pour les valeurs "M", "Mlle" et "Mme".
ETATLIV en zone de liste modifiable comprenant les valeurs : "Neuf" ; "Bon" ; "Moyen" ;
"Mauvais"
DATEMPRUNT et DATRETOUR avec un masque de saisie de type / /
ANPARUTIONLIV avec une taille de 4 caractères, une condition de validité (compris entre 2000
et 2011) et le message d'erreur : "Année non valide !" en cas de non-conformité.
5) Créer les liens entre les tables (ne pas oublier d'appliquer les intégrités référentielles si nécessaire).
6) Saisir les enregistrements suivant dans les tables correspondantes :
Classes :
CODCLA LIBELCLA
1 2LAEGQ
2 2APTCI
3 2LFECO
4 2LFCOP
Elèves :
CODELE CODCLA TITELE NOMELE PREELE
1 1 M Zayeti Salah
2 4 Mlle Abidi Maha
3 4 M Hosni Walid
4 2 Mme Bouraoui Besma
Livres :
CODLIV TITLIV AUTLIV ANPARUTIONLIV ETATLIV
1 Ouioui à la montagne Rousseau 1960 Moyen
2 Nonnon fait du ski De la fontaine 1968 Bon
3 Toto Tartampion 1975 Mauvais
4 Les pingoins Cousteau 1950 Bon
Emprunts :
CODLIV CODELE DATEEMPRUNT DATERETOUR
1 1 16/09/2009 15/12/2009
4 1 23/12/2009 11/01/2010
1 2 01/01/2005 01/02/2005
2 3 10/02/2006 15/02/2006
7) Supprimer l'emprunt concernant M Hosni dans la table Emprunts et remplacer l'année de parution du livre
du livre Toto par l'année 1986.
8) Effectuer la mise en forme suivante sur la feuille de données de la table Emprunts :
Appliquer la police Comic sans MS de taille 11.
Mettre une couleur de texte rouge
Appliquer une apparence de cellules 3D relâché
Ajuster les colonnes au texte et masquer la colonne "code du livre" (CODLIV)
Renommer la colonne "code élève" (CODELE) en "Numéro de l'élève"
9) Ouvrir la table Elève en mode feuille de données et appliquer un filtre permettant d'obtenir tous les élèves
appartenant au code classe "4". Appliquer un nouveau filtre permettant d'afficher toutes les demoiselles
appartenants à la classe "4".
TP n°4
Objectifs de la séance
Au terme de la séance, l’étudiant sera capable d'interroger une base de données en utilisant l'algèbre relationnelle.
Exercice
On donne le schéma relationnel suivant :
PILOTE (NUMPIL, NOMPIL, ADR, SAL) AVION (NUMAV, NOMAV, CAPACITE, LOC) VOL (NUMVOL#, NUMPIL#, NUMAV, VILLE_DEP, VILLE_ARR, H_DEP, H_ARR)
NUMPIL: clé de PILOTE, nombre entier NOMPIL: nom du pilote, chaîne de caractères ADR: ville de la résidence du pilote, chaîne de caractères SAL: salaire du pilote, nombre entier NUMAV: clé de AVION, nombre entier CAPACITE: nombre de places d'un avion, nombre entier
Extension des tables
LOC: ville de l'aéroport d'attache de l'avion, chaîne de caractères NUMVOL: clé de VOL, nombre entier VILLE_DEP: ville de départ du vol, chaîne de caractères VILLE_ARR: ville d'arrivée du vol, chaîne de caractères H_DEP: heure de départ du vol, nombre entier entre 0 et 23 H_ARR: heure d'arrivée du vol, nombre entier entre 0 et 23
PILOTE AVION
NUMPIL NOMPIL ADR SAL NUMAV NOMAV CAPACITE LOC 1 ZIGHED Tunis 21000 1 A300 300 Monastir
2 BOUSSAID Djerba 21000 2 A310 300 Monastir
3 BOUSLIMI Monastir 18000 3 B707 250 Tunis
4 JBELI Tunis 17000 4 A300 280 Sousse 5 ZAGHDOUDI Djerba 19000 5 Concorde 160 Monastir
6 NASRAOUI Tunis 18000 6 B747 460 Tunis
7 GARBAA Monastir 17000 7 B707 250 Tunis
8 CHALGHOUM Sousse 15000 8 A310 300 Djerba
9 ARBI Monastir 18000 9 Mercure 180 Sousse
10 HOSNI Tunis 20000 10 Concorde 160 Tunis
NUMVOL NUMPIL NUMAV VILLE_DEP VILLE_ARR H_DEP H_ARR
1 1 1 Monastir Djerba 11:00 12:00
2 1 8 Tunis Djerba 17:00 19:00
3 2 1 Djerba Sousse 14:00 15:30
4 5 3 Djerba Sousse 18:00 19:30
5 9 1 Tunis Monastir 06:00 08:00
6 10 2 Sousse Monastir 11:00 11:30
7 1 4 Tunis Djerba 08:00 09:00
8 8 4 Monastir Tunis 07:00 08:00
9 1 8 Tozeur Djerba 09:00 10:00
10 8 2 Monastir Tunis 12:00 14:00
11 9 2 Tunis Tabarka 15:00 16:00
12 1 2 Monastir Tozeur 16:00 17:00
13 4 5 Monastir Tabarka 11:00 13:00
14 3 5 Tabarka Tunis 15:00 16:00
15 8 9 Tunis Djerba 17:00 19:00
16 7 5 Tunis Djerba 18:00 20:00
VOL
Exprimer en algèbre relationnelle les requêtes suivantes :
1. Expression des projections et sélections
(a) Donnez la liste des avions dont la capacité est supérieure à 350 passagers.
(b) Quels sont les numéros et noms des avions localisés à Monastir ?
(c) Quels sont les numéros des pilotes en service et les villes de départ de leurs vols ?
(d) Donnez toutes les informations sur les pilotes de la compagnie.
(e) Quel est le nom des pilotes domiciliés à Tunis dont le salaire est supérieur à 15000 dt ?
2. Utilisation des opérateurs ensemblistes
(a) Quels sont les avions (numéro et nom) localisés à Monastir ou dont la capacité est inférieure à 350
passagers ?
(b) Liste des vols au départ de Monastir allant à Tunis après 18 heures ?
(c) Quels sont les numéros des pilotes qui ne sont pas en service ?
(d) Quels sont les vols (numéro, ville de départ) effectués par les pilotes de numéro 100 et 204 ?
3. Expression des jointures
(a) Donnez le numéro des vols effectués au départ de Monastir par des pilotes de Monastir?
(b) Quels sont les vols effectués par un avion qui n’est pas localisé à Monastir ?
(c) Quels sont les pilotes (numéro et nom) assurant au moins un vol au départ de Monastir avec un avion de
capacité supérieure à 300 places ?
(d) Quels sont les noms des pilotes domiciliés à Tunis assurant un vol au départ de Monastir avec un Airbus ?
(e) Quels sont les numéros des vols effectués par un pilote de Monastir au départ ou à l’arrivée de Monastir
avec un avion localisé à Tunis ?
(f) Quels sont les pilotes (numéro et nom) habitant dans la même ville que le pilote ZAGHDOUDI ?
(g) Quels sont les numéros des pilotes en service différents de celui de BOUSLIMI ?
(h) Quelles sont les villes desservies (VILLE_ARR) à partir de la ville d’arrivée d’un vol au départ de Tunis ?
(i) Quels sont les appareils (leur numéro) localisés dans la même ville que l’avion numéro 100 ?
(j) Quels sont les pilotes (leur nom) de Tunis qui ont volé avec tous les avions ?
Correction du TP n°4
1. Expression des projections et sélections
(a) Donnez la liste des avions dont la capacité est supérieure à 350 passagers.
CAPACITE>350(AVION)
(b) Quels sont les numéros et noms des avions localisés à Monastir ?
NUMAV, NOMAV (LOC='Monastir'(AVION)) (c) Quels sont les numéros des pilotes en service et les villes de départ de leurs vols ?
NUMPIL, VILLE_DEP (VOL) (d) Donnez toutes les informations sur les pilotes de la compagnie.
NUMPIL, NOMPIL, ADR, SAL (PILOTE) (e) Quel est le nom des pilotes domiciliés à Tunis dont le salaire est supérieur à 15000 dt ?
NOMPIL (ADR='Tunis' SAL>15000 (PILOTE)) 2. Utilisation des opérateurs ensemblistes
(a) Quels sont les avions (numéro et nom) localisés à Monastir ou dont la capacité est inférieure à 350
passagers ?
Solution possible avec une projection et sélection
NUMAV,NOMAV (LOC='Monastir' CAPACITE<350 (AVION)) Solution avec les opérateurs ensemblistes
R1 = NUMAV,NOMAV (LOC='Monastir' (AVION))
R2 = NUMAV,NOMAV (CAPACITE<350 (AVION)) R = R1 R2
(b) Liste des vols au départ de Monastir allant à Tunis après 18 heures ?
Solution possible avec une sélection
VILLE_DEP='Monastir' VILLE_ARR='Tunis' H_DEP>'18:00' (VOL) Solution avec les opérateurs ensemblistes
R1 = VILLE_DEP='Monastir' (VOL)
R2 = VILLE_ARR='Tunis' H_DEP>'18:00' (VOL)
R3 = H_DEP>'18:00' (VOL) R4 = R1 R2
R = R4 R3
(c) Quels sont les numéros des pilotes qui ne sont pas en service ?
R1 = NUMPIL (PILOTE) // Les numéros de tous les pilotes
R2 = NUMPIL (VOL) // Les numéros des pilotes qui sont affectés à des voles
R = R1 − R2
(d) Quels sont les vols (numéro, ville de départ) effectués par les pilotes de numéro 100 et 204 ?
Solution possible avec une projection et sélection
NUMVOL,VILLE_DEP (NUMPIL=100 NUMPIL=204 (AVION)) Solution avec les opérateurs ensemblistes
R1 = NUMVOL,VILLE_DEP(NUMPIL=100(AVION))
R2 = NUMVOL,VILLE_DEP(NUMPIL=204(AVION))
R = R1 R2 Remarque : Il n'aura pas de doublons avec l'opérateur.
3. Expression des jointures
(a) Donnez le numéro des vols effectués au départ de Monastir par des pilotes De Monastir ?
R1 = VILLE_DEP='Monastir' (VOL) R2 = ADR='Monastir' (PILOTE)
R = NUMVOL(R1 R2)
R1.NUMPIL = R2.NUMPIL
(b) Quels sont les vols effectués par un avion qui n’est pas localisé à Monastir ?
R = LOC≠'Monastir' (AVION VOL)
AVION.NUMAV = VOL.NUMAV
(c) Quels sont les pilotes (numéro et nom) assurant au moins un vol au départ de Monastir avec un avion
de capacité supérieure à 300 places ?
R1 = VILLE_DEP='Monastir' (VOL) R2 = CAPACITE>300 (AVION)
R = PILOTE.NUMPIL,NOMPIL(PILOTE R1 R2 )
PILOTE.NUMPIL = R1.NUMPIL R1.NUMAV = R2.NUMAV
(d) Quels sont les noms des pilotes domiciliés à Tunis assurant un vol au départ de Monastir avec un
Airbus ?
R1 = ADR='Tunis' (PILOTE) R2 = VILLE_DEP='Monastir' (VOL) R3 = NOMAV='Airbus' (AVION)
R = NOMPIL(R1 R2 R3 )
R1.NUMPIL = R2.NUMPIL R2.NUMAV = R3.NUMAV
(e) Quels sont les numéros des vols effectués par un pilote de Monastir, au départ ou à l’arrivée de
Monastir avec un avion localisé à Tunis ?
R1 = ADR='Monastir' (PILOTE) R2 = VILLE_DEP='Monastir' ∨ VILLE_ARR='Monastir' (VOL)
R3 = LOC='Tunis' (AVION)
R = NUMVOL(R1 R2 R3 )
R1.NUMPIL = R2.NUMPIL R2.NUMAV = R3.NUMAV
(f) Quels sont les pilotes (numéro et nom) habitant dans la même ville que le pilote ZAGHDOUDI ?
R1 = PILOTE R2 = NOMPIL='ZAGHDOUDI' (PILOTE)
R = R1.NUMPIL,R1.NOMPIL(R1 R2) R1.ADR = R2.ADR
(g) Quels sont les numéros des pilotes en service différents de celui de BOUSLIMI ?
R1 = NOMPIL≠'BOUSLIMI' (PILOTE)
R2 = VOL
R = R1.NUMPIL (R1 R2) R1.NUMPIL = R2.NUMPIL
(h) Quels sont les numéros des pilotes en service différents de celui de BOUSLIMI ?
R1 = NOMPIL≠'BOUSLIMI' (PILOTE)
R2 = VOL
R = R1.NUMPIL (R1 R2) R1.NUMPIL = R2.NUMPIL
(i) Quelles sont les villes desservies (VILLE_ARR) à partir de la ville d’arrivée d’un vol au départ de Tunis ?
R1 = VILLE_DEP='TUNIS' (VOL)
R2 = VOL
R = R1.VILLE_ARR (R1 R2) R1.VILLE_DEP = R2.VILLE_ARR
(j) Quels sont les appareils (leur numéro) localisés dans la même ville que l’avion numéro 100 ?
R1 = NUMAV=100 (AVION)
R2 = AVION
R3 = R1.NUMAV (R1 R2) R1.LOC = R2.LOC
R = NUMAV≠100 (R3)
(k) Quels sont les pilotes (leur nom) de Tunis qui ont volé avec tous les avions ?
R1 = ADR='TUNIS' (PILOTE)
R2 = R1 VOL) R1.NUMPIL = VOL.NUMPIL
R = NOMPIL,NUMAV(R2) ÷ NUMAV(AVION)
TP n°5 (Les requêtes)
Correspondance entre le vocabulaire employé en cours et le vocabulaire d’Access
Cours Access
Relation, Table
Attribut Champ
N-uplet Enregistrement
Création et manipulation de la base de données
Soit le schéma relationnel suivant :
JOUEUR (NoJoueur, NomJ, Sexe, NoEq#)
EQUIPE (NoEq)
TOURNOI (NoTour, NomT, Date, Coef, NoPays#)
PAYS (NoPays, NomP)
JOUE_J (NoJoueur#, NoTour#, Score_J)
JOUE_E (NoEq#, NoTour#, Score_E)
1. Créer une nouvelle base de données. Lancer Access. Sélectionner l’option « Nouvelle base de données », puis donner
un nom à la base de données pour l’enregistrer.
La base de données est maintenant créée. Il s’agit d’un fichier portant l’extension .mdb et contenant sept types
d’objets : tables, requêtes, formulaires, états, pages (web), macros et modules. Ces différents objets sont
accessibles en cliquant sur les onglets appropriés de la fenêtre de gestion de la base (voir ci- dessous).
2. Définir la structure des tables. Cliquer sur l’onglet « Tables », puis sur « Créer une table en mode Création
» (ou utiliser le bouton et sélectionner le mode création). Le mode création est utilisé pour paramétrer la
structure d’une table : nom de chaque champ et type, principalement. Renseigner les colonnes « Nom du champ »
et « Type de donnée », ainsi que la propriété « Taille du champ » (fenêtre du bas). Définir la clé primaire de la table en
sélectionnant la ligne du champ adéquat, puis en cliquant sur l’icône dans la barre d’outils. Fermer la table en
cliquant sur le bouton de fermeture ou par le menu Fichier/Fermer. Si la sauvegarde n’a pas été effectuée auparavant,
le système vous préviendra.
3. Renseigner chaque table avec quelques enregistrements. Dans l’onglet « Tables » de la fenêtre de gestion
de la base, sélectionner une table et cliquer sur le bouton « Ouvrir » ( ). En mode «Feuille de
données», il est possible de saisir, modifier ou supprimer les données. Expérimenter la modification et la
suppression d’enregistrements.
4. Définir les relations entre les tables (Menu Outils / Relations ou icône ). Cet outil Access définit
implicitement les contraintes de clés étrangères et permet de faciliter les opérations de jointure. Pour cela,
ajouter successivement toutes les tables, puis fermer la boite de dialogue. Pour définir une relation, par
exemple celle qui est associée à la clé étrangère NumCli de la table Commande de l’exemple ci-dessous,
cliquer sur le champ NumCli dans la table Commande et le glisser sur le champ auquel il fait référence :
NumCli dans la table Client (ou vice-versa). Cocher la case « Appliquer l’intégrité référentielle ».
Interrogation de la base
Formuler les requêtes suivantes avec le formalisme graphique d’Access (QBE, Query By Example). Cliquer sur
l’onglet « Requêtes » de la fenêtre de gestion de la base, puis sélectionner le mode création.
Σ
Choisir la ou les tables ou requêtes sources de la requête.
Utiliser la grille de création pour spécifier la requête en faisant glisser les champs à projeter dans la grille de
création, en indiquant les critères de tri ou de restriction, etc.
1. Liste de tous les tournois avec toutes leurs caractéristiques (pseudo-champ *).
2. Noms des tournois classés par importance (i.e., par coefficient d’importance décroissant).
3. Nom des joueuses.
4. Caractéristique d’un joueur dont le numéro est saisi au clavier (=[Message] en critère).
5. Liste des tournois en précisant Nom du tournois / Nom du pays.
6. Maximum des coefficients de tournois (fonction d’agrégat Max() accessible par le bouton ).
7. Nombre total de tournois (fonction d’agrégat Compte()).
8. Nombre de tournois, par pays (fonction d’agrégat Compte() + regroupement).
9. Pour chaque équipe, nom des joueurs de cette équipe. Veiller à ne pas inclure dans la liste les joueurs de simple
uniquement.
10. Nom du joueur qui fait équipe avec un joueur dont le nom est saisi au clavier.
11. Pour chaque tournoi, scores obtenus par un joueur dont le nom est saisi au clavier.
12. Copier/coller la requête 13. Ajouter à cette requête un champ calculé Score_Pondéré tel que
Score_Pondéré = Score_Joueur x Coef.
13. À partir de la requête 14, calculer le score total (fonction d’agrégat Somme() sur les scores pondérés) d’un joueur
dont le nom est saisi au clavier.
14. Pour chaque joueur de sexe masculin, son score total. Trier les scores totaux par ordre décroissant.
15. À partir des requêtes 15 et 16, afficher les noms des joueurs dont le score total est supérieur au score d’un joueur
dont le nom est saisi au clavier.
16. Par numéro de tournoi, le plus haut score obtenu pour ce tournoi.
17. À partir de la requête 18, afficher, pour chaque tournoi, la gagnante (la joueuse dont le score est égal au plus haut
score obtenu pour ce tournoi).
Annexe 1 : Principaux types de données sous Access
Texte Texte ou combinaison de texte et de chiffres, ou chiffres ne servant pas à faire des calculs (numéros de
téléphone, codes postaux…) ; taille maximum : 255 caractères.
Mémo Textes longs (résumés, appréciations…) ; taille maximum : 32 000 caractères.
Numérique Plusieurs types de nombres :
octet : valeur entière comprise entre 0 et 255
entier : valeur entière comprise entre –32 768 et 32 768
entier long : valeur entière comprise entre –2 147 483 648 et 2 147 483 647
réel simple: valeur décimale comprise entre –3,40282E38 et 3,402823E38 avec une
précision de six chiffres
réel double : valeur décimale comprise entre –1,79769313486232E308 et 1,79769313486232E308 avec une précision
de six chiffres.
Date/Heure Dates et/ou heures.
Monétaire Nombres en style monétaire (ex. 1 000 000,00 €).
NuméroAuto (Numérotation Automatique)
Oui/Non Booléen (seulement deux valeurs possibles).
Annexe 2 : Principales propriétés des champs sous Access
Taille du champ Pour un champ de type texte, il s’agit du nombre maximal de caractères autorisé dans le champ
(valeur par défaut : 50 ; limites : de 1 à 255). Pour un champ de type numérique, il faut choisir parmi octet, entier, etc.
Format Pour les nombres, choix d’un format monétaire ou pourcentage, par exemple ; pour les dates et heures, choix de l’affichage entre 01/01/2011, 01/01/11, 01-jan-11, etc.
Décimales Pour un nombre, saisir les nombre de décimales désiré.
Masque de saisie Guide et assiste l’opérateur dans la saisie des données ; par exemple, un masque de saisie sur un code postal contraint l’opérateur à saisir 5 caractères de type numérique.
Légende Spécifie le libellé à afficher dans l’en-tête de colonne de la feuille de données ou dans l’étiquette de contrôle du formulaire de saisie.
Valeur par défaut C’est une constante, une expression ou une fonction qui est automatiquement validée si aucune autre n’est entrée.
Valide si C’est une constante, une expression ou une fonction qui définit les conditions de validation d’un champ (contrainte de domaine).
Message si erreur Dans le cas d’une utilisation de la propriété Valide si, définit le message à envoyer en cas de non- respect des conditions de validité.
Null interdit Spécifie si la saisie de donnée dans ce champ est obligatoire ou non (pas de saisie = valeur Null)
Chaîne vide autorisée Indique si une chaîne de caractères ne contenant aucun caractère est valide ou non. Indexé Champ indexé (avec ou sans doublons) ou non
TP n°6
(Les requêtes)
La requête est un objet employé fréquemment pour le calcul. En effet, afin de minimiser la taille des bases
de données, il est recommandé de ne pas stocker dans des champs d’une table les valeurs issues d’un calcul. Pour
visualiser ces dernières, on préfère exécuter une requête : ainsi c’est mémorisée que la formule du calcul mais non
son résultat.
1. Mise en place de la base de données
Une société de vente par correspondance souhaite effectuer quelques calculs sur les commandes
passées par ses clients. Elle emploie une base Access nommée vpc.mdb.
La base contient une seule table Ligne de commandes qui possède la structure suivante :
Ligne de commandes(NuméroVente,CodeClient,NomClient,PrenomClient,DateVente,
NombreArticle,PrixUnitaire)
1.1. Saisie de données dans la table
Ouvrir la table Ligne des commandes et saisir dans le champ DateVente pour chaque enregistrement une valeur
: vous n’entrez pas l’expression « Aujourd’hui – 7 », mais la date correspondant au jour effectif du TP moins 7
jours (par exemple 17/04/2011 si la séance de travaux pratiques a lieu le 24/04/2011).
2. Champ calculé dans une requête
Un champ calculé est constitué d’un identificateur (le nom du champ), d’un opérateur (le symbole : pour un
champ calculé dans un formulaire) et d’une formule de calcul construite à partir d’identificateurs d’autres champs
et de constantes. Des champs calculés peuvent être insérés dans les requêtes, les formulaires et les états ; ils ne
peuvent pas l’être dans une table.
2.1. Calcul à partir de deux champs de la table
Nous allons créer une première requête permettant de calculer le montant hors taxe de chaque ligne
de commandes.
2.1.1. Création d’une requête de calcul
a) Cliquez sur le bouton Requêtes de la fenêtre de Base de données puis double cliquez sur la proposition
« Créer une requête en mode création ». Ajoutez la table Ligne de commandes.
b) Créez la structure de la requête en suivant les étapes ci-dessous :
– Inscrire dans la première ligne de la grille de création les sept champs de la table, un par colonne.
– Entrer comme titre du huitième champ l’expression suivante :
« Montant : PrixUnitaire*NombreArticle ».
– « Montant » est l’identificateur du champ calculé ;
– «:» est l’opérateur d’affectation ;
– « PrixUnitaire*NombreArticle » est la formule de calcul.
c) Cliquez sur le bouton pour exécuter la requête et vérifier son bon fonctionnement.
Attention ! Si le calcul n’est pas effectué et si la requête agit comme une requête paramétrée, cela
signifie que vous avez mal orthographié le nom d’un champ dans la formule du calcul.
d) Enregistrez cette requête sous le nom CalculHT.
2.1.2. Modifier les propriétés du champ calculé
Attention ! Pour modifier une propriété d’une requête, vous devez avoir enregistré cette requête. Toute
modification d’une requête qui n’a pas été enregistrée au préalable n’est pas prise en compte par Access.
L’objectif est de changer une propriété du champ calculé de manière à faire apparaître comme titre du champ
calculé « Montant hors taxe », titre plus explicite que « Montant » pour l’utilisateur.
En mode de création, sélectionnez dans la grille de création la colonne contenant le champ calculé, activez le
bouton de propriété de la barre d’outils et dans la fenêtre de propriétés du champ écrire comme légende :
« Montant hors taxe ».
2.2. Calcul à partir de champs calculés
L’objectif est de créer une requête permettant de calculer et d’afficher le montant de la TVA et le montant TTC
de chaque commande à partir du montant hors taxes que vous venez de calculer.
a) Créez une nouvelle requête à partir non pas de la table Ligne de commandes, mais de la requête
CalculHT.
b) Placez les huit noms de champs de la requête CalculHT en première ligne de la grille de création de la
nouvelle requête.
c) Placez le pointeur dans la première colonne vide (la neuvième). Ecrivez l’expression du champ calculé :
« TVA :Montant*0,196 ».
Le champ TVA est calculé à partir du champ calculé Montant de la requête CalculHT.
d) Dans la dixième colonne, écrire la formule suivante :
MontantTTC : Montant+TVA
e) Exécutez la requête. Vous devez obtenir le résultat suivant :
Si aucun calcul n’est effectué, vérifiez l’orthographe des champs employés dans les calculs.
Si l’unité monétaire est erronée, enregistrez la requête sous le nom CalculTTC, puis modifiez le format
des deux nouveaux champs calcul´es pour que les valeurs soient exprimées en euros.
f) Enregistrez la requête sous le nom CalculTTC.
3. Les fonctions d’agrégats : les fonctions somme, moyenne, min et max appliquées à l’ensemble des
données
Une fonction d’agrégation permet d’obtenir une valeur issue d’un calcul sur l’ensemble de valeurs d’un
même champ pour une table déterminée. Par exemple : recherche du maximum, somme des valeurs,
dénombrement des valeurs, etc. La liste des opérations disponibles est reproduite dans le document en annexe.
L’exercice consiste à créer une requête permettant d’afficher sur une seule ligne le total des montants TTC, les
montants TTC maximum, minimum et moyen, le nombre de commandes passées jusqu’à ce jour.
a) Créez une nouvelle requête à partir de la requête CalculTTC.
b) Dans la première ligne de la grille de création, sélectionnez, dans les 5 premières colonnes, le nom du
champ MontantTTC.
c) Cliquez sur le bouton Opérations de la barre d’outils. Apparait comme troisième ligne de la grille de
création une nouvelle ligne intitulée Opérations.
d) Sélectionnez dans la première colonne l’opération de sommation (somme), dans la deuxième l’opération
du maximum (max), dans la troisième l’opération du minimum (min), dans la quatrième l’opération de
moyenne (moyenne) et dans la cinquième l’opération de dénombrement (compte).
e) Enregistrez la requête sous le nom Statistiques sur commandes et l’exécuter.
f) En vous inspirant de la copie d’écran ci-dessous, modifiez la propriété Légende des différents calculs pour
faire apparaître un message conforme à l’usage correct de la langue française.
4. Les fonctions de regroupement
Les fonctions de calcul peuvent porter sur des sous-ensembles d’enregistrements groupés selon un critère
au lieu de porter, comme dans le paragraphe précédent, sur tous les enregistrements d’une table. Le logiciel
recherche dans un champ que choisit l’utilisateur toutes les valeurs qui sont différentes les unes des autres ;
pour chacune de ces valeurs, l’application crée un sous-ensemble des enregistrements possédant cette valeur
unique ; il existe donc autant de sous-ensembles que de valeurs uniques ; sur chacune de ces sous-ensembles,
Access exécute enfin un calcul à un champ choisi par l’utilisateur.
Les opérations disponibles sont décrites dans le document en annexe.
4.1. Le regroupement selon les valeurs uniques d’un seul champ
Vous devez créer une requête qui permet d’afficher le total des montants TTC dû par chaque client.
a) Créez une nouvelle requête basée sur la requête CalculTTC.
b) Insérez les champs CodeClient et MontantTTC en première ligne de la grille de création.
c) Cliquez sur le bouton Opérations de la barre d’outils pour faire apparaître la ligne des opérations.
d) Dans la ligne Opération, sélectionner l’opération Regroupement pour le champ CodeClient. Pour le champ
calculé MontantTTC, sélectionner la fonction Somme.
Le logiciel recherchera les valeurs uniques du champ CodeClient, regroupera les enregistrements ayant la
même valeur dans le champ CodeClient, puis fera pour chaque groupe la somme des valeurs du champ
calculé MontantTTC.
e) Testez la requête. L’enregistrer sous le nom Total des montants par client.
f) Modifiez la propriété du deuxième champ de la requête pour faire afficher la légende suivante : « Total des
commandes par client ».
4.2. Le regroupement selon les valeurs uniques de plusieurs champs
Il est possible d’obtenir le même résultat en regroupant les factures selon le nom puis pour chaque valeur
identique du nom selon le prénom du client. Le résultat est plus facile à lire.
a) Créez une nouvelle requête basée sur la requête CalculTTC.
b) Insérez les champs NomClient, PrenomClient et MontantTTC en première ligne de la grille de création.
c) Dans la ligne Opération, sélectionner l’opération de Regroupement pour les deux premiers champs
et la fonction Somme pour le dernier.
d) Demandez le tri par ordre alphabétique des noms, puis des prénoms.
e) Testez la requête. L’enregistrer sous le nom Total des montants par client 2.
4.3. Les limites des sous-ensembles de regroupements
Attention : La multiplicité des critères de regroupement peut entraîner des résultats contraires `a ceux esp´er´es.
En effet, le regroupement des informations se fait d’abord à partir du premier champ auquel est appliquée
l’opération de regroupement. Puis, chaque groupe est scindé selon le critère du deuxième champ et ainsi de suite.
La multiplicité des sous-groupes peut aboutir `a l’absence, de fait, de regroupements.
a) Créez une nouvelle requête basée sur la requête CalculTTC.
b) Insérez les champs DateVente, NomClient, PrenomClient et MontantTTC.
c) Sélectionnez l’opération de Regroupement pour les trois premiers champs et la fonction Somme pour le
dernier.
d) Nommez la requête Trop c’est trop et testez-la. Vous constatez que le logiciel édite les mêmes valeurs que
celles de la requête CalculTTC. En effet, du fait du nombre trop important de critères retenus, il n’y a aucun
enregistrement qui ne puisse être sommé à un autre.
4.4. La combinaison de regroupements et de critères de sélection
4.4.1. Regroupement selon les valeurs uniques d’un champ et selon un critère pour ne retenir que
certaines de ces valeurs
La requête suivante a pour objet d’éditer le montant quotidien des commandes effectuées depuis 4 jours.
a) Créez une requête à partir de la requête CalculTTC.
b) La requête est fondée sur le champ DateVente auquel on applique l’opération de Regroupement et le
champ MontantTTC auquel on applique la fonction Somme.
c) Sur le champ DateVente, insérez le critère >=Date()-3.
d) Enregistrez la requête et la nommer Chiffre d’affaires quotidien sur 3 jours. Vous devez obtenir un résultat
conforme à ce qui suit, aux dates de vente près :
4.4.2. Regroupement selon les valeurs uniques d’un champ et selon un critère pour ne retenir qu’une
de ces valeurs
La requête suivante emploie d’une part le calcul par regroupement pour obtenir la somme des ventes réalisées
chaque jour et d’autre part la recherche sur critère pour ne faire apparaître que la somme des commandes passées
un jour déterminé.
a) Créez une requête à partir de la requête CalculTTC
b) La requête est fondée sur le champ DateVente auquel on applique le critère de Regroupement et le champ
MontantTTC auquel on applique la fonction Somme.
c) Insérez le critère paramétré «[Entrer une date : ] » sur le champ DateVente de manière à ce que
ne soit affiché que le résultat du jour choisi par l’utilisateur.
d) Enregistrez la requête et nommez-la Total des montants perçus par jour.
4.4.3. Combinaison d’un regroupement selon les valeurs uniques d’un champ et d’un critère
portant sur le résultat du calcul
La requête suivante a pour objet d’éditer les chiffres d’affaires quotidiens supérieurs à 4000 euros.
a) Créez une requête à partir de la requête CalculTTC.
b) La requête est fondée sur le champ DateVente auquel on applique le critère de Regroupement et le champ
MontantTTC auquel on applique la fonction Somme.
c) Dans le champ MontantTTC, insérez le critère de sélection.
d) Enregistrez la requête et nommez-la Chiffres d’affaires supérieurs à 4000.
4.4.4. Combinaison d’un regroupement selon les valeurs uniques d’un champ et d’un critère
portant sur un champ autre que celui du résultat et du regroupement
La requête a pour objet d’éditer le chiffre d’affaires journalier effectué avec les personnes qui portent le nom de
Bouslimi. Le critère de sélection ne porte pas sur un des champs nécessaires au calcul.
a) Créez une requête à partir de la requête CalculTTC.
b) La requête est fondée sur le champ DateVente auquel on applique le critère de Regroupement, le champ
NomClient auquel on applique l’opération Où et le champ MontantTTC auquel on applique la fonction
Somme.
c) Dans le champ NomClient, insérez le critère de sélection.
d) Enregistrez la requête et la nommer Bouslimi. Vous devez obtenir un résultat conforme à ce qui suit, aux
dates de vente près :
5. Exercices complémentaires
5.1. Le montant le plus élevé par date
Créez une requête permettant d’afficher la plus élevée des lignes de facture faites chaque jour. Enregistrez
la requête sous le nom Montant maximal par date.
5.2. Liste de toutes les commandes effectuées depuis moins de 5 jours
Nous allons vérifier dans cette requête qu’un critère peut être mémorisé sous la forme d’un champ calcul´e de
type booléen. Créez une nouvelle requête CalculDate. Déplacez le champ astérisque de la requête CalculTTC vers
la grille de création de requête.
Entrez dans le champ suivant l’expression Calcul : [DateVente]>Date()-6.
Calcul est un champ calcul´e de type booléen. Calcul prend la valeur Vrai si la date de vente est supérieure à la
date d’aujourd’hui moins 6 jours. Pour ne retenir que les dates de vente comprises dans les cinq jours précédents
aujourd’hui, il faut imposer la valeur logique Vrai dans la propriété Critères du champ Calcul.
Ne pas afficher ce champ lors de la présentation du résultat de la requête.
5.3. Statistiques sur les commandes faites par chaque personne dont le code commence par R
Editez le chiffre d’affaire total, le chiffre d’affaire moyen, le nombre d’articles total et le nombre de commande
de chacune des personnes dont le code client commence par R. Nommez cette requête Statistiques Code R.
5.4. Statistiques sur l’ensemble des personnes dont le code commence par R
Editez le chiffre d’affaire total, le chiffre d’affaire moyen, le nombre d’articles total et le nombre de commande
de l’ensemble des personnes dont le code client commence par R. Nommez cette requête Statistiques R.
5.5. Ristourne
L’entreprise décide d’offrir un chèque d’un montant de 10% du chiffre d’affaire fait avec chaque client qui a
achet´e pour plus de 1000 dinars. Construisez la requête qui édite le nom, le prénom, le montant total du chiffre
d’affaire et le montant de la ristourne pour les seuls clients qui bénéficient de la ristourne.
Nommez cette requête Ristourne.
TP n°7
(Formulaire et État)
L’objectif de cette séance est de montrer l’utilisation des formulaires sous Access.
1. Base de données
Nous utilisons dans cette séance la base de données Salarie. La base salarie.mdb se trouve dans le bureau
de windows.
Lancez ACCESS et ouvrez la base de données salarie.mdb.
2. Les formulaires
Un formulaire est une grille de présentation, de saisie et de modification des données d’une base sans avoir
à manipuler les tables directement. Un formulaire est obtenu à partir d’une table ou d’une requête. Sous Access, il
existe deux façons pour créer un formulaire : (1) à l’aide de l’assistant ou (2) manuellement.
2.1. Créer un formulaire à l’aide de l’assistant
L’assistant permet de guider l’utilisateur pendant toute la phase de création du formulaire. Pour créer un
formulaire `a l’aide de l’assistant, suivez les étapes suivantes :
1. Cliquez sur le bouton Formulaires ;
2. Choisir l’option Créer un formulaire à l’aide de l’assistant ;
3. Dans la liste des Tables/Requêtes choisissez la table Personnel ;
4. Dans la liste des champs disponibles, faîtes déplacer les champs vers la zone Champs sélectionnés à
l’aide des boutons ou ;
5. Cliquez sur le bouton suivant, choisissez le format d’affichage de votre formulaire à colonne simple
;
6. Cliquez sur le bouton suivant et choisissez le style d’affichage de votre formulaire, sélectionnez l’option
Expédition ;
7. Cliquez sur suivant et nommez le formulaire F liste personnel, appuyez sur le bouton Terminer. Le
résultat de cette manipulation est un formulaire d’affichage qui contient tous les champs de la table
sélectionnée.
Remarquez que Access a ajouté des boutons de navigation en bas du formulaire servant à la navigation
dans la base de données. Cette barre est illustrée dans la figure ci-dessous.
Attention : Le formulaire obtenu permet l’insertion de nouveaux enregistrements dans la table Personnel. Pour
cela, cliquez sur le bouton Ajouter un enregistrement et remplissez les champs du formulaire. La saisie directe
d’un enregistrement sans l’utilisation du bouton Ajouter un enregistrement provoque la modification du contenu
des champs de l’enregistrement en cours.
2.2. Création d’un formulaire pour le résultat d’une requête
1. Créez une nouvelle requête qui permet d’afficher la liste des employés qui résident dans une rue. Pour
cela, insérez l’expression *rue* dans le critère du champ adresse ; nommez la requête Rue.
2. Créez un formulaire, à l’aide de l’assistant, permettant de visualiser le contenu de la requête Rue.
Nommez le formulaire F_rue. Ajoutez en utilisant le formulaire l’enregistrement suivant :
3. Vérifiez
données
requête Rue et de la table Personnel. Remarques !
les
de la
2.3. Création manuelle d’un formulaire
Pour créer un formulaire manuellement, Access met à votre disposition une “boîte à outils” comme sur la figure
suivante :
3801 09-dec-83 Jendoubi Amel 98 rue des roses Jendouba 8100 20-mars-65
Commencez par sélectionner l’option Créer un formulaire en mode création afin d’avoir la possibilité de le créer
manuellement. La boîte `a outils s’affiche automatiquement ; si ce n’est pas le cas, cliquez sur le bouton . La
première opération à faire dans ce cas est d’associer au formulaire une source de données.
1. Sélectionnez le formulaire (la zone grisée et non pas celle avec des rectangles !) et allez dans le menu
Affichage/Propriétés ou cliquez sur le bouton dans la barre d’outils.
2. Sélectionnez l’onglet Toutes pour voir toutes les propriétés du formulaire.
3. Choisissez la table Personnel comme source de données.
4. Choisissez comme Légende du formulaire F_Liste_Personnel_bis.
Une fois la table sélectionnée, ses champs apparaissent et il suffit de glisser/déplacer les champs sur le
formulaire pour l’instancier. Reproduisez alors manuellement le formulaire automatique, obtenu
précédemment, et enregistrez le sous le nom de F_Liste_Personnel_bis.
Maintenant que vous vous êtes familiarisé avec les composants, reproduisez avec des boutons la barre
de déplacement qu’offre Access à la création d’un formulaire. Utilisez pour cela le composant
“Boutons de commande” de la boîte à outils.
5. Verrouillez le champ N˚ employé afin qu’il ne puise pas être modifié par le formulaire. Pour cela,
sélectionnez le champ, allez dans propriétés (cliquez sur le bouton ou Affichage >
propriétés) puis l’onglet Données. Mettez la valeur du champ Verrouillé à Oui.
6. Ajoutez au formulaire, `a l’aide des boutons de la boîte à outils, un bouton qui permet de fermer le
formulaire.
Remarques : On peut aussi créer un formulaire instantané à partir d’une table ou d’une requête.
Pour cela, sélectionnez la table ou la requête et cliquez sur le bouton nouvel objet : formulaire automatique.
3. Les états
Les formulaires permettent d’afficher les données à l’écran, de naviguer dans une base de données et de modifier
ou d’ajouter des données dans des tables. Une autre fonction importante est l’´edition des états. Un état permet de
mettre en forme le contenu d’une base de données pour des fins d’impression.
Nous allons illustrer l’utilisation des états dans ce qui suit.
1. Créez un Etat à l’aide de l’assistant, reprendre les informations de la table Personnel.
2. Dans la fenêtre Etats, choisissez Créer un état à l’aide de l’assistant, glissez l’ensemble des champs
de la table Personnel dans la fenêtre “Champs sélectionnés” (bouton ).
3. Choisissez ensuite la disposition des données et le mode de présentation.
4. Choisissez un affichage Tabulaire et nommez votre Etat E_Liste_Personnel.
Vous obtenez alors un état contenant toutes les informations du personnel et prêt à l’impression.
La présentation de l’état peut être modifiée en passant en mode création (position des contrôles,
étiquettes, formules, etc.).
4. Exercices complémentaires
1. Créez un formulaire qui retourne les informations d’une personne saisie au clavier.
2. Créez une requête permettant de calculer le salaire de chaque membre du personnel.
Sachant que le salaire de base de chaque employé est de 1000 euros, ce salaire est augmenté par le produit
du nombre d’année d’expérience et de 10% du salaire de base. Nommez la requête P_Salaire.
Fonctions utiles : Pour calculer l’arrondi d’un réel, Access dispose de la fonction ENT(réel).
Remarque : Access exprime la différence entre deux dates par le nombre de jours.
3. A partir de la requête P_Salaire, créez un état éditant les fiches de paye des employés.
