Base de Donnees Microsoft Access [PDF]

Zitiervorschau

REPUBLIQUE TUNISIENNE MINISTERE DE L’ENSEIGNEMENT SUPERIEUR ET DE LA RECHERCHE SCIENTIFIQUES ET TECHNOLOGIQUES

UNIVERSITE DE JENDOUBA FACULTE DES SCIENCES JURIDIQUES, ECONOMIQUES ET DE GESTION DE JENDOUBA

Support de cours Base de Données Access Adressé aux étudiants de : - 2ème année Licence Appliquée en Economie de Gestion Quantitative - 2ème année Licence Appliquée en Analyse de Projet et Consultation Economique - 2ème année Licence Appliquée en Technique de Commerce International

Equipe pédagogique : Chargé du cours : Riadh BOUSLIMI -

Technologue en Informatique

Chargées des TPs : Faten ABBASSI -

Professeur de l’enseignement secondaire détaché

Hedhli Afef

Professeur de l’enseignement secondaire détaché

-

Année Universitaire : 2010-2011

Table des matières Chapitre 1 : Introduction aux bases de données ....................................................................... 4 I.

Introduction à la gestion des données ............................................................................................................ 4 Notion de donnée et d'information ......................................................................................................... 4 La persistance ......................................................................................................................................... 4 II. Les bases de données : les notions de base ................................................................................................ 5 1. Définition ................................................................................................................................................ 5 2. Avantages d'une base de données ........................................................................................................... 6 III. Système de Gestion de Base de Données (SGBD) .................................................................................... 6 1. Définition d'un SGBD ............................................................................................................................ 6 2. Structure d'un SGBD .............................................................................................................................. 6 3. Les fonctions d'un SGBD ....................................................................................................................... 7 4. Les principaux SGBD............................................................................................................................. 7 IV. Cycles de développement des bases de données ........................................................................................ 7 Chapitre 2 : Structure d'une base de données ............................................................................ 8 I. Notion de table ............................................................................................................................................... 8 1. Définition ................................................................................................................................................ 8 2. Remarques : ............................................................................................................................................ 8 II. Notion de colonne ...................................................................................................................................... 8 1. Définition ................................................................................................................................................ 8 2. Caractéristiques d'un champ : ................................................................................................................. 8 III. Notion de ligne ........................................................................................................................................... 9 1. Définition ................................................................................................................................................ 9 IV. Notion de clé primaire ................................................................................................................................ 9 1. Définition ................................................................................................................................................ 9 2. Caractéristiques ...................................................................................................................................... 9 V. Liens entre les tables .................................................................................................................................. 9 1. Lien de type 1,n ...................................................................................................................................... 9 2. Lien de type n,n .................................................................................................................................... 10 VI. Notion de contrainte d'intégrité ................................................................................................................ 11 1. Les contraintes de domaines : (valide si) ............................................................................................. 11 2. Les contraintes d'intégrité de tables : (clé primaire) ............................................................................. 11 3. Les contraintes d'intégrité référentielles : ............................................................................................. 11 VII. Représentation de la structure d'une base de données .............................................................................. 11 1. La représentation textuelle : ................................................................................................................. 11 2. La représentation graphique : ............................................................................................................... 11 Chapitre 3 : Démarche de la création d'une base de données ........................................ 12 Exercice .............................................................................................................................................................. 12 1) Détermination de la liste des colonnes. ................................................................................................ 12 2) Etablissement de la liste des tables ....................................................................................................... 13 3) Affectation des colonnes aux tables. .................................................................................................... 13 4) Précision des clés primaires des tables. ................................................................................................ 13 5) Identification des liens entre les tables ................................................................................................. 13 6) Représentation de la structure de cette base de données. ..................................................................... 13 Chapitre 4 : Algèbre relationnelle.................................................................................................... 14 1. Introduction .......................................................................................................................................... 14 2. La Sélection .......................................................................................................................................... 14 3. La Projection ........................................................................................................................................ 15 4. Union .................................................................................................................................................... 15 1. 2.

2/69

5. L’Intersection ........................................................................................................................................ 16 6. La Différence ........................................................................................................................................ 16 7. Le Produit cartésien............................................................................................................................... 17 8. La Jointure, theta-jointure, equi-jointure, jointure naturelle ................................................................. 18 8.1. La Jointure ............................................................................................................................................. 18 8.2. La Theta-jointure ................................................................................................................................... 18 8.3. L’Equi-jointure ...................................................................................................................................... 18 8.4. Jointure naturelle ................................................................................................................................... 19 9. La Division ............................................................................................................................................ 19 TP n°1 ..................................................................................................................................................................... 21 Correction du TP n°1 ............................................................................................................................................ 22 TP n°2 ..................................................................................................................................................................... 25 Correction du TP2 ................................................................................................................................................. 29 TP n°3 ..................................................................................................................................................................... 33 TP n°4 ..................................................................................................................................................................... 35 Correction du TP n°4 ............................................................................................................................................ 37 TP n°5 ..................................................................................................................................................................... 40 TP n°6 ..................................................................................................................................................................... 44 TP n°7 ..................................................................................................................................................................... 53 Examen Base de données Access .......................................................................................................................... 61

3/69

Chapitre 1 : Introduction aux bases de données

I.

Introduction à la gestion des données 1. Notion de donnée et d'information Activité1 Un médecin désire informatiser la gestion de ses consultations. Suite à une interview avec lui, on dégage les opérations suivantes : − La création d'une nouvelle fiche "patient". − L'édition d'une ordonnance. − La prise des rendez-vous. On lui demande la description de la fiche patient il nous communique les données suivantes :

Fiche Client • Nom: • Prénom : • Adresse : • Téléphone : • N° CIN: Activité2 Identifier quelques informations utilisées dans les domaines suivants : ♦ Une administration d'une faculté ♦ Une banque ♦ Un magasin a) Définition d'une donnée Une donnée est description élémentaire d'une information. b) Définitions d'une information (1) Plusieurs données regroupées et se rapportant à un même contexte donnent naissance à l'information. (2) 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. La persistance La persistance ⇔ mémorisation + disponibilité (des données) Lorsqu'on mémorise les données, deux aspects sont pris en considération : Le type du support de mémorisation : disque dur (local, sur serveur, CD-ROM,…) 4/69

Le format de mémorisation : structure choisie pour stocker les données… La persistance des données peut être assurée grâce à plusieurs organisations : •

L'organisation papier : Fiches, registre, cahier… Contraintes : 1. 2. 3. 4.

•

Délai de recherche. Problème de sécurité. Volume important Classement et tri difficile.

L'organisation en fichiers : Un fichier (file en anglais) est un ensemble de données structurées stocké sur une mémoire de masse. Ces données se présentent sous forme d'enregistrements (Record en anglais).

Contraintes : 1. 2. 3. 4.

II.

Nécessiter d'écrire des programmes Manque de sécurité. La redondance et de MAJ Accès exclusif (un utilisateur à la fois)

Les bases de données : les notions de base 1. Définition 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/69

2. Avantages d'une base de données

a) Centralisation : Les données peuvent êtres utilisées par plusieurs programmes et plusieurs utilisateurs.

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

c) Intégration des liaisons entre les données : Pas besoin d'un programme pour retrouver les liens entre les données.

d) Intégrité des données Ce sont des règles de sécurité assurant la cohérence des données : •

Unicité des enregistrements.

•

Interdiction de la suppression des données utilisées par d'autres données.

e) Concurrence d'accès Plusieurs utilisateurs peuvent accéder simultanément à la BD.

III.

Système de Gestion de Base de Données (SGBD) 1. Définition d'un SGBD 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.

2. Structure d'un SGBD Un SGBD est constitué de deux composantes principales : • •

Le moteur L'interface

6/69

3. Les fonctions d'un SGBD a) 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). b) 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) c) 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. d) La gestion des accès concurrents Le SGBD gère l'accès simultané des utilisateurs à la base de données. e) 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. f) La sécurité de fonctionnement Faire une copie de sauvegarde de la base. Remise en marche de la base en cas de panne.

4. Les principaux SGBD • • • •

IV.

ORACLE MICROSOFT SQL SERVER MySQL Microsoft Access

Cycles de développement des bases de données Niveau externe : Analyse de l'existant Niveau conception : Modélisation des entités du mode réel Niveau interne : Création de la base de données.

7/69

Chapitre 2 : Structure d'une base de données

I.

Notion de table 1. Définition Une table est un ensemble de données relatives à une même entité, structurée sous forme d'un tableau (liste). Une table peut être appelée aussi "Relation".

Exemple : Cas d'un cabinet médical.

PATIENTS CIN

Nom

Prénom

Téléphone

Date_n

ORDONNANCES Numéro_ord

Date

CNAM

CIN

2. Remarques :

II.

•

Les données d'une table peuvent être stockées sur un ou plusieurs fichiers.

•

Une table peut être considérée comme un ensemble mathématique. Ainsi, on pourra faire l'union ou l'intersection de deux tables.

Notion de colonne 1. Définition Une colonne (champ) représente une propriété élémentaire de l'entité décrite par cette table.

2. Caractéristiques d'un champ : ♦ ♦ ♦ ♦ ♦ ♦

Nom Type de données (Chaine, Numérique, Date) Taille éventuelle Obligatoire (oui/non) Valeur par défaut Valide si : on peut créer une règle indiquant les valeurs utilisées.

8/69

Exemple : cas de la table Patients Nom de colonne

Description

Type de données

Carte d'Identité Nationale

Chaîne

CIN

Prénom Nom Téléphone Date_n

III.

Date de naissance

Chaîne Chaîne Numérique Date

Taille Obligatoire Valeur par défaut 8 Oui

50 50 8

Non Non Non Non

Valeurs autorisées

78 ≤Aujourd'hui()

Notion de ligne 1. Définition Une ligne (enregistrement) représente une occurrence du sujet représenté par la table. Exemple : cas de la table Ordonnances ORDONNANCES

IV.

Numéro_ord

Date

CNAM

CIN

..... ..... 30327 … …..

….. ….. 14/01/2011 …. ……

….. ….. T3 ….. ……

….. ….. 07815464 ….. ……

Notion de clé primaire 1. Définition La clé primaire d'une table est un champ ou un ensemble de champs permettant d'identifier de manière unique chaque enregistrement de la table.

2. Caractéristiques Unique + non nul.

V.

Liens entre les tables 1. Lien de type 1,n

Dans un contexte relationnel, les entités d'un système d'information admettent des relations entre elles. On peut formuler ces relations comme suit : 9/69

Cas des tables patients et ordonnances : •

Un patient peut avoir un ou plusieurs ordonnances.

•

Une ordonnance est délivrée à un et un seul patient.

Dans ce cas on parle de lien de type un à plusieurs (1,n) et il y'aura naissance d'une clé étrangère qui assurera la relation entre les deux tables Définition Un lien entre deux tables A et B se traduit par l'ajout dans la table B d'un nouveau champ correspondant à la clé primaire de la table A. Ce champ est appelé clé étrangère. Dans ce cas A est une table mère, B est une table fille.

2. Lien de type n,n On peut aussi parler de liens plusieurs à plusieurs (n, n) Cas des tables Ordonnances et médicaments. •

Une ordonnance contient un ou plusieurs médicaments.

•

Un médicament est inscrit dans une ou plusieurs ordonnances.

Définition Ce type de lien entraine la création d'une troisième table dite intermédiaire. Elle aura comme clé primaire les deux clés primaires de ses tables mères. D'autres champs pourront s'ajouter à cette clé en cas de besoin. Dans ce cas A et B sont deux tables mères et A_B est une table intermédiaire.

10/69

VI.

Notion de contrainte d'intégrité Définition Une contrainte d'intégrité est une règle appliquée à un champ ou à une table et qui doit être toujours vérifiée.

1. Les contraintes de domaines : (valide si) Ce sont des contraintes appliquées à des colonnes. Elles permettent de fixer le caractère obligatoire ou pas d'une colonne et les règles de validité des valeurs qui peuvent être prises par cette colonne. Exemple : La note doit être comprise entre 0 et 20.

2. Les contraintes d'intégrité de tables : (clé primaire) Elles permettent d'assurer que chaque table a une clé primaire.

3. Les contraintes d'intégrité référentielles :

VII.

•

Champ clé étrangère ne peut contenir qu'une valeur déjà existante dans la clé primaire correspondante.

•

La suppression d'un enregistrement d'un table mère A utilisé par une table fille B est interdit.

Représentation de la structure d'une base de données Le schéma base de données est une représentation des différentes structures de la base. Cette représentation peut être faite selon deux formalismes :

1. La représentation textuelle : La représentation textuelle consiste a affecter les colonnes au différentes tables et rajouter le symbole dièse (#) dans la table fille s’il y’en a une liaison entre deux tables. Patients (CIN, Prénom, Nom, Téléphone, Date_n) Ordonnances (Numéro_ord, Date, CNAM, CIN#) CIN#: clé étrangère

2. La représentation graphique :

11/69

Chapitre 3 : Démarche de la création d'une base de données Exercice Une agence "LV" de location de voitures gère manuellement son parc, composé d'une centaine de véhicules à partir d'un paquet de fiches cartonnées. Ci-après, un exemple de fiche de voiture : ∅ ∅ ∅ ∅ ∅ ∅ ∅ ∅ ∅

Numéro de l'immatriculation

:

150 TN 6065

La marque Le type La couleur

: : :

Peugeot 407 Grise

La puissance Le Kilométrage L'âge de la voiture Prix par jour Type carburant

: : : : :

6 CV 12000 1 ans 150 dinars Essence/Diesel

Le responsable de la société "LV" décide d'implanter une base de données pour améliorer la gestion de son parc de voitures. Après étude, une voiture est décrite par le numéro d'immatriculation comme identifiant, une marque, un type, une puissance, un kilométrage, un âge de voiture, un prix par jour et un type de carburant. Chaque locataire est identifié par un numéro locataire et chacun a un nom et une adresse. A chaque location un enregistrement sera effectué : Le numéro d'immatriculation de la voiture, le numéro du locataire et la date de location. Le kilométrage de retour et la date de retour (date fin location) seront enregistrés au retour de la voiture.

Questions : 1) Souligner à partir de l’énoncé les noms des colonnes et compléter le tableau ci-dessous.

Nom colonne IMMAT MARQUE TYPE COULEUR PUISS KILOM AGE PRIX_PAR_JOUR TYPE_CARBURANT LOC LOCNOM ADR DL KILD KILR DR

Description Numéro d'immatriculation Marque de la voiture Type de la voiture Couleur de la voiture Puissance de la voiture Le kilométrage indiqué par le compteur Age de la voiture Prix de location par jour Type de carburant de la voiture Numéro de locataire Nom de locataire Adresse de locataire Date de location Kilométrage de départ Kilométrage de retour Date de retour de location

Valeur par défaut

Valeurs autorisées

Type de Données

Taille

Obligatoire

Caractère Caractère Caractère Caractère Numérique Numérique

10 10 10 10 2 10

OUI OUI OUI NON NON OUI

Voiture Voiture Voiture Voiture Voiture Voiture

Numérique monétaire Caractère

2 4 10

NON

Voiture Voiture Voiture

Caractère Caractère Caractère Date Numérique Numérique Date

10 20 20

OUI OUI OUI OUI

Locataire Locataire Locataire Locataire Location Location Location

10 10

(1) La valeur par défaut de la date de location est la date du jour. 12/69

OUI

(1) (2) (3)

Sujet

(2) La valeur de kilométrage de retour reste avec le kilométrage de départ jusqu'à la date de retour. (3) La date de retour d'une voiture doit être supérieur ou égale à la date de location. 2) Etablir la liste des tables Liste des tables Description

Nom table Voiture Locataire Location

Regroupe de l'ensemble des voitures de la société Regroupe les personnes qui louent des voitures de la société Stocke l'historique des locations de voitures

Sujet Voitures Locataires Locations

3) Affecter les colonnes aux tables et en donner une description textuelle. Voiture(IMMAT,MARQUE,TYPE,COULEUR,PUISS,KILOM,AGE,PRIX_PAR_JOUR,TYPE_CARBURANT) Locataire(LOC,LOCNOM,ADR) Location(IMMAT,LOC,DL,KILD,KILR,DR) 4) Préciser les clés primaires des tables. Voiture(IMMAT,MARQUE,TYPE,COULEUR,PUISS,KILOM,AGE,PRIX_PAR_JOUR,TYPE_CARBURANT) Locataire(LOC,LOCNOM,ADR) Location(IMMAT,LOC,DL,KILD,KILR,DR) 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

Voiture

Location

IMMAT

IMMAT

Locataire

Location

LOC

LOC

Voiture(IMMAT,MARQUE,TYPE,COULEUR,PUISS,KILOM,AGE,PRIX_PAR_JOUR,TYPE_CARBURANT) Locataire(LOC,LOCNOM,ADR) Location(IMMAT#,LOC#,DL,KILD,KILR,DR) 6) Représenter la structure de cette base de données sous forme graphique.

13/69

Chapitre 4 : Algèbre relationnelle 1. Introduction L’algèbre relationnelle est un support mathématique cohérent sur lequel repose le modèle relationnel. L’objet de ce chapitre est d’aborder l’algèbre relationnelle dans le but de décrire les opérations qu’il est possible d’appliquer sur des relations pour produire de nouvelles relations. L’approche suivie est donc plus opérationnelle que mathématique. On peut distinguer trois familles d’opérateurs relationnels :   

Les opérateurs unaires (Sélection, Projection) : ce sont les opérateurs les plus simples, ils permettent de produire une nouvelle table à partir d’une autre table. Les opérateurs ensemblistes (Union, Intersection Différence) : ces opérateurs permettent de produire une nouvelle relation à partir de deux relations de même degré et de même domaine. Les opérateurs binaires ou n-aires (Produit cartésien, Jointure, Division) : ils permettent de produire une nouvelle table à partir de deux ou plusieurs autres tables.

2. La Sélection Définition -sélection- La sélection (parfois appelée restriction) génère une relation regroupant exclusivement toutes les occurrences de la relation R qui satisfont l’expression logique E, on la note σ(E)R. Il s’agit d’une opération unaire essentielle dont la signature est : relation × expression logique —→ relation Les opérateurs logiques sont : ∧ (et), ∨ (ou) et !(non) Les opérateurs relationnels sont : > , ≥ , < , ≤ , = , et ≠ En d’autres termes, la sélection permet de choisir (i.e. sélectionner) des lignes dans le tableau. Le résultat de la sélection est donc une nouvelle relation qui a les mêmes attributs que R. Si R est vide (i.e. ne contient aucune occurrence), la relation qui résulte de la sélection est vide. Le tableau 2 montre un exemple de sélection. Prénom Numéro Nom 5 Zaghdoudi Walid 1 Abidi Abdelbasset 12 Bouslimi Nadia 3 Abidi Nour El Houda Tableau 1: Exemple de relation Personne

Numéro Nom Prénom 5 Zaghdoudi Walid 12 Bouslimi Nadia Tableau 2: Exemple de sélection sur la relation Personne du tableau 1 : σ(Numéro≥5)Personne

14/69

3 . La P r o j e c t i o n Définition -projection- La projection consiste à supprimer les attributs autres que A1, … An d’une relation et à éliminer les n-uplets en double apparaissant dans la nouvelle relation ; on la note Π(A1, … An)R. Il s’agit d’une opération unaire essentielle dont la signature est : relation × liste d’attributs —→ relation En d’autres termes, la projection permet de choisir des colonnes dans le tableau. Si R est vide, la relation qui résulte de la projection est vide, mais pas forcément équivalente (elle contient généralement moins d’attributs). Le tableau 3 montre un exemple de sélection. Nom Zaghdoudi Abidi Bouslimi Tableau 3: Exemple de projection sur la relation Personne du tableau 1 : Π(Nom)Personne

4. Union Définition -union- L’union est une opération portant sur deux relations R1 et R2 ayant le même schéma et construisant une troisième relation constituée des n-uplets appartenant à chacune des deux relations R1 et R2sans doublon, on la note R1 ∪ R2. Il s’agit une opération binaire ensembliste commutative essentielle dont la signature est : relation × relation —→ relation Comme nous l’avons déjà dit, R1 et R2 doivent avoir les mêmes attributs et si une même occurrence existe dans R1 et R2, elle n’apparaît qu’une seule fois dans le résultat de l’union. Le résultat de l’union est une nouvelle relation qui a les mêmes attributs que R1 et R2. Si R1 et R2 sont vides, la relation qui résulte de l’union est vide. Si R1 (respectivement R2) est vide, la relation qui résulte de l’union est identique à R2 (respectivement R1). Le tableau 4 montre un exemple d’union. Relation R1 Nom Prénom Zaghdoudi Walid Abidi Abdelbasset Bouslimi Nadia Abidi Nour El Houda

Relation R2 Relation R Nom Prénom Nom Prénom Bouslimi Nadia Zaghdoudi Walid Nasri Karima Abidi Abdelbasset Djebali Nadia Bouslimi Nadia Abidi Nour El Houda Nasri Karima Djebali Nadia

Tableau 4: Exemple d’union : R = R1 ∪ R2 15/69

5. L’Intersection Définition -intersection- L’intersection est une opération portant sur deux relations R1 et R2 ayant le même schéma et construisant une troisième relation dont les n-uplets sont constitués de ceux appartenant aux deux relations, on la note R1 ∩ R2. Il s’agit une opération binaire ensembliste commutative dont la signature est : relation × relation —→ relation Comme nous l’avons déjà dit, R1 et R2 doivent avoir les mêmes attributs. Le résultat de l’intersection est une nouvelle relation qui a les mêmes attributs que R1 et R2. Si R1 ou R2 ou les deux sont vides, la relation qui résulte de l’intersection est vide. Le tableau 5 montre un exemple d’intersection. Relation R1 Nom

Relation R2

Prénom

Relation R

Nom

Prénom

Nom

Prénom

Zaghdoudi Walid

Bouslimi

Nadia

Zaghdoudi Walid

Abidi

Abdelbasset

Nasri

Karima

Bouslimi

Nadia

Bouslimi

Nadia

Djebali

Nadia

Nasri

Karima

Abidi

Nour El Houda

Zaghdoudi Walid

Nasri

Karima

Tableau 5: Exemple d’intersection : R = R1 ∩ R2

6. La Différence Définition -différence- La différence est une opération portant sur deux relations R1 et R2 ayant le même schéma et construisant une troisième relation dont les n-uplets sont constitués de ceux ne se trouvant que dans la relation R1 ; on la note R1 − R2. Il s’agit une opération binaire ensembliste non commutative essentielle dont la signature est : relation × relation —→ relation Comme nous l’avons déjà dit, R1 et R2 doivent avoir les mêmes attributs. Le résultat de la différence est une nouvelle relation qui a les mêmes attributs que R1 et R2. Si R1 est vide, la relation qui résulte de la différence est vide. Si R2 est vide, la relation qui résulte de la différence est identique à R1.

16/69

Le tableau 6 montre un exemple de différence. Relation R1 Nom

Relation R2

Prénom

Relation R

Nom

Prénom

Nom

Prénom

Zaghdoudi Walid

Bouslimi

Nadia

Abidi Abdelbasset

Abidi

Abdelbasset

Nasri

Karima

Abidi Nour El Houda

Bouslimi

Nadia

Djebali

Nadia

Abidi

Nour El Houda

Zaghdoudi Walid

Nasri

Karima Tableau 6: Exemple de différence : R = R1 − R2

7. Le Produit cartésien Définition 31 -produit cartésien- Le produit cartésien est une opération portant sur deux relations R1 et R2 et qui construit une troisième relation regroupant exclusivement toutes les possibilités de combinaison des occurrences des relations R1 et R2, on la note R1 × R2. Il s’agit une opération binaire commutative essentielle dont la signature est : relation × relation —→ relation Le résultat du produit cartésien est une nouvelle relation qui a tous les attributs de R1 et tous ceux de R2. Si R1 ou R2 ou les deux sont vides, la relation qui résulte du produit cartésien est vide. Le nombre d’occurrences de la relation qui résulte du produit cartésien est le nombre d’occurrences de R1 multiplié par le nombre d’occurrences de R2. Le tableau 7 montre un exemple de produit cartésien. Relation Amie Nom Prénom

Relation Cadeau Article

Prix

Relation R Nom Prénom Article Prix

Djebali Nadia

livre

45

Djebali Nadia

livre

Nasri

poupée

25

Djebali Nadia

poupée 25

montre

87

Djebali Nadia

montre 87

Karima

Nasri

Karima livre

Nasri

Karima poupée 25

Nasri

Karima montre 87

Tableau 7: Exemple de produit cartésien : R = Amie × Cadeau

17/69

45

45

8. La Jointure, theta-jointure, equi-jointure, jointure naturelle 8.1. La Jointure Définition 32 -jointure- La jointure est une opération portant sur deux relations R1 et R2 qui construit une troisième relation regroupant exclusivement toutes les possibilités de combinaison des occurrences des relationsR1 et R2 qui satisfont l’expression logique E. La jointure est notée R1 ▷◁E R2. Il s’agit d’une opération binaire commutative dont la signature est : relation × relation × expression logique —→ relation Si R1 ou R2 ou les deux sont vides, la relation qui résulte de la jointure est vide. En fait, la jointure n’est rien d’autre qu’un produit cartésien suivi d’une sélection : R1 ▷◁E R2 = σE (R1 × R2) Le tableau 8 montre un exemple de jointure. Relation Famille Nom

Relation Cadeau

Prénom Age

AgeC

Djebali

Nadia

99

livre

30

Djebali Nadia

6

99

livre

30

Nasri

Karima 42

6

poupée

60

Djebali Nadia

6

20

baladeur

45

Chrif

Marwa 16

20

baladeur

45

Djebali Nadia

6

10

déguisement 15

10

déguisement 15

Nasri

Karima

42

99

livre

30

Chrif

Marwa

16

99

livre

30

Chrif

Marwa

16

20

baladeur

45

6

Article

Relation R

Prix

Nom

Prénom Age AgeC

Article

Prix

Tableau 8: Exemple de jointure : R = Famille ▷◁((Age ≤ AgeC) ∧ (Prix < 50)) Cadeau

8.2. La Theta-jointure Définition 33 -theta-jointure- Une theta-jointure est une jointure dans laquelle l’expression logique E est une simple comparaison entre un attribut A1 de la relation R1 et un attribut A2 de la relation R2. La thetajointure est notée R1 ▷◁E R2.

8.3. L’Equi-jointure Définition 34 -equi-jointure- Une equi-jointure est une theta-jointure dans laquelle l’expression logique E est un test d’égalité entre un attribut A1 de la relation R1 et un attribut A2 de la relation R2. L’equijointure est notée R1 ▷◁A1,A2 R2. Remarque : Il vaut mieux écrire R1 ▷◁A1=A2 R2 que R1 ▷◁A1,A2 R2 car cette dernière notation peut prêter à confusion avec une jointure naturelle explicite.

18/69

8.4. Jointure naturelle Définition 35 -jointure naturelle- Une jointure naturelle est une jointure dans laquelle l’expression logique E est un test d’égalité entre les attributs qui portent le même nom dans les relations R1 et R2. Dans la relation construite, ces attributs ne sont pas dupliqués mais fusionnés en une seul colonne par couple d’attributs. La jointure naturelle est notée R1 ▷◁ R2. On peut préciser explicitement les attributs communs à R1 et R2 sur lesquels porte la jointure : R1 ▷◁A1, …, An R2. Généralement, R1 et R2 n’ont qu’un attribut en commun. Dans ce cas, une jointure naturelle est équivalente à une equi-jointure dans laquelle l’attribut de R1 et celui de R2 sont justement les deux attributs qui portent le même nom. Lorsque l’on désire effectuer une jointure naturelle entre R1 et R2 sur un attribut A1 commun à R1 et R2, il vaut mieux écrire R1 ▷◁A1 R2 que R1 ▷◁ R2. En effet, si R1 et R2 possèdent deux attributs portant un nom commun, A1 et A2, R1 ▷◁A1 R2 est bien une jointure naturelle sur l’attribut A1, mais R1 ▷◁ R2 est une jointure naturelle sur le couple d’attributs A1, A2, ce qui produit un résultat très différent ! Le tableau 9 montre un exemple de jointure naturelle. Relation Famille Nom

Prénom Age

Djebali Nadia

6

Relation Cadeau

Relation R

Age Article Prix

Nom

40

livre

Djebali Nadia

45

Prénom Age Article Prix 6

poupée 25

Nasri

Karima 40

6

poupée 25

Nasri

Karima 40

livre

Chrif

Marwa 20

20

montre 87

Chrif

Marwa 20

montre 87

Chiboub Emna

6

Chiboub Emna

6

45

poupée 25

Tableau 9: Exemple de jointure naturelle : R = Famille ▷◁ Cadeau ou R = Famille ▷◁Age Cadeau

9. La Division Définition -division- La division est une opération portant sur deux relations R1 et R2, telles que le schéma de R2 est strictement inclus dans celui de R1, qui génère une troisième relation regroupant toutes les parties d’occurrences de la relation R1 qui sont associées à toutes les occurrences de la relation R2 ; on la note R1 ÷ R2. Il s’agit d’une opération binaire non commutative dont la signature est : relation × relation —→ relation Autrement dit, la division de R1 par R2 (R1 ÷ R2) génère une relation qui regroupe tous les n-uplets qui, concaténés à chacun des n-uplets de R2, donne toujours un n-uplet de R1. La relation R2 ne peut pas être vide. Tous les attributs de R2 doivent être présents dans R1 et R1 doit posséder au moins un attribut de plus que R2 (inclusion stricte). Le résultat de la division est une nouvelle relation qui a tous les attributs de R1 sans aucun de ceux de R2. Si R1 est vide, la relation qui résulte de la division est vide.

19/69

Le tableau 10 montre un exemple de division. Relation Enseignement

Relation Etudiant

Relation R

Enseignant Etudiant

Nom

Enseignant

Abidi

Zied

Zied

Abidi

Chrif

Yamine

Yamine

Chrif

Skander

Zied

Abidi

Yamine

Chrif

Zied

Abidi

Zaghdoudi

Skander

Zaghdoudi

Tableau 10: Exemple de division : R = Enseignement ÷ Etudiant. La relation R contient donc tous les enseignants de la relation Enseignement qui enseignent à tous les étudiants de la relation Etudiant.

20/69

UNIVERSITE DE JENDOUBA

Equipe pédagogique : Chargé de cours : Riadh BOUSLIMI Chargées des TPs : Faten ABBASSI Afef HEDHLI Module : Base de données (Access) Classe : 2ème Licence appliquée

Faculté des Sciences Juridiques, Economiques et de Gestion de Jendouba

Année Universitaire : 2010/2011 Semestre 2

TP n°1 Objectifs de la séance 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 ?

21/69

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. 22/69

-

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. 23/69

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.

24/69

UNIVERSITE DE JENDOUBA

Equipe pédagogique : Chargé de cours : Riadh BOUSLIMI Chargées des TPs : Faten ABBASSI Afef HEDHLI Module : Base de données (Access) Classe : 2ème Licence appliquée

Faculté des Sciences Juridiques, Economiques et de Gestion de Jendouba

Année Universitaire : 2010/2011 Semestre 2

TP n°2 Objectifs de la séance 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)___________________________________________________________________________________ ___________________________________________________________________________________ (3)___________________________________________________________________________________ (4)___________________________________________________________________________________ 25/69

2. Etablir la liste des tables Liste des tables Description

Nom table

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.

26/69

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 1 2 3 4 5 6 7 8 9

Nom TOUNSI KEFI BEJI TOUATI SOUSSI SFAXI GABSI JERBI EZZAR

Prénom

Date naissance

Safa Ali Mohamed Lamia Leila Nouri Mouna Lotfi Samia

01/10/1980 12/09/1981 14/04/1977 21/06/1980 28/11/1982 20/08/1990 04/04/1987 09/06/1988 12/12/1982

N° Service 20 10 20 20 10 30 10 30 20

Date création Service Financier 01/01/1970 Administratif 01/01/1975 Financier 01/01/1970 Financier 01/01/1970 Administrative 01/01/1975 Juridique 01/04/1980 Administratif 01/01/1957 Juridique 01/04/1980 Financier 01/01/1970 Nom Service

Questions : 1. Identifier les anomalies de cette structure. •

………………..............................  …………………………………………………………………………………………… …………………………………………………………………………………………… …………………………………………………………………………………………… …………………………………………………………………………………  …………………………………………………………………………………………… …………………………………………………………………………………………… …………………………………………………………………………………………… …………………………………………………………………………………

•

………………..............................  …………………………………………………………………………………………… …………………………………………………………………………………………… …………………………………………………………………………………………… …………………………………………………………………………………

27/69

2. Donner la nouvelle structure. ……………………………………………………………………………………………………………………… ……………………………………………………………………………………………………………………… ……………………………………………………………………………………………………………………… ……………………………………………………………………………………………………………………… ……………………………………………………………………………………………………………………… ……………………………………………………………………………………………………………………… …………………………………………………………………………………………………………………...…. …..………………..

……………………………

28/69

Correction du TP2 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

Code_livre Titre Auteur Editeur Date_parution Num_abonne Nom_abonne Prenom_abonne Adresse Tel Code_liv_emp Num_ab_emp Date_emprunt Date_retour

données Texte Texte Texte Texte Date Numérique Texte Texte Texte Texte Texte Numérique Date Date

Taille

Obligatoire

10 50 50 30

O O N N N O O O

5 20 20 50 20 10 5

O O O

Valeur par défaut

Valeurs autorisées

(1) (2) (3) (4)

Sujet

Livres Livres Livres Livres Livres Abonnés Abonnés Abonnés Abonnés Abonnés Emprunts Emprunts Emprunts 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

29/69

2. Etablir la liste des tables

Nom table Livre Abonne

Emprunt

Liste des tables Description Regroupe l'ensemble des livres de la bibliothèque Regroupe les personnes abonnées à la bibliothèque Stocke l'historique des emprunts de livres

Sujet Livres Abonnés

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 Livre Abonne

Table fille Emprunt Emprunt

Clé primaire Code_livre Num_abonne

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.

30/69

Clé étrangère Code_liv_emp Num_ab_emp

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 1 2 3 4 5 6 7 8 9

Nom TOUNSI KEFI BEJI TOUATI SOUSSI SFAXI GABSI JERBI EZZAR

Prénom Safa Ali Mohamed Lamia Leila Nouri Mouna Lotfi Samia

Date de naissance 01/10/1980 12/09/1981 14/04/1977 21/06/1980 28/11/1982 20/08/1990 04/04/1987 09/06/1988 12/12/1982

N° Service 20 10 20 20 10 30 10 30 20

Nom Service Financier Administratif Financier Financier Administrative Juridique Administratif Juridique Financier

Date création Service 01/01/1970 01/01/1975 01/01/1970 01/01/1970 01/01/1975 01/04/1980 01/01/1957 01/04/1980 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#)

31/69

N° Service 10 20 30

Service Nom service Administratif Financier Juridique

Date création Service 01/01/1975 01/01/1970 01/04/1980

Employe N°emp 1 2 3 4 5 6 7 8 9

Nom TOUNSI KEFI BEJI TOUATI SOUSSI SFAXI GABSI JERBI EZZAR

32/69

Prénom Safa Ali Mohamed Lamia Leila Nouri Mouna Lotfi Samia

Date naissance 01/10/1980 12/09/1981 15/04/1977 21/06/1980 28/11/1982 20/08/1990 04/04/1987 09/06/1988 12/12/1982

N° Service 20 10 20 20 10 30 10 30 20

UNIVERSITE DE JENDOUBA

Equipe pédagogique : Chargé de cours : Riadh BOUSLIMI Chargées des TPs : Faten ABBASSI Afef HEDHLI Module : Base de données (Access) Classe : 2ème Licence appliquée

Faculté des Sciences Juridiques, Economiques et de Gestion de Jendouba

Année Universitaire : 2010/2011 Semestre 2

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é. 33/69

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 1 2 3 4 Elèves : CODELE 1 2 3 4

CODCLA 1 4 4 2

Livres : CODLIV TITLIV 1 Ouioui à la montagne 2 Nonnon fait du ski 3 Toto 4 Les pingoins Emprunts : CODLIV 1 4 1 2

CODELE 1 1 2 3

LIBELCLA 2LAEGQ 2APTCI 2LFECO 2LFCOP

TITELE M Mlle M Mme

AUTLIV Rousseau De la fontaine Tartampion Cousteau

NOMELE Zayeti Abidi Hosni Bouraoui

ANPARUTIONLIV 1960 1968 1975 1950

DATEEMPRUNT 16/09/2009 23/12/2009 01/01/2005 10/02/2006

PREELE Salah Maha Walid Besma

ETATLIV Moyen Bon Mauvais Bon

DATERETOUR 15/12/2009 11/01/2010 01/02/2005 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".

34/69

UNIVERSITE DE JENDOUBA

Equipe pédagogique : Chargé de cours : Riadh BOUSLIMI Chargées des TPs : Faten ABBASSI Afef HEDHLI Module : Base de données (Access) Classe : 2ème Licence appliquée

Faculté des Sciences Juridiques, Economiques et de Gestion de Jendouba

Année Universitaire : 2010/2011 Semestre 2

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

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

Extension des tables PILOTE NUMPIL 1 2 3 4 5 6 7 8 9 10

VOL NUMVOL 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

NOMPIL

ADR

SAL

ZIGHED BOUSSAID BOUSLIMI JBELI ZAGHDOUDI NASRAOUI GARBAA CHALGHOUM ARBI HOSNI

Tunis Djerba Monastir Tunis Djerba Tunis Monastir Sousse Monastir Tunis

21000 21000 18000 17000 19000 18000 17000 15000 18000 20000

NUMPIL 1 1 2 5 9 10 1 8 1 8 9 1 4 3 8 7

NUMAV 1 8 1 3 1 2 4 4 8 2 2 2 5 5 9 5

AVION NUMAV 1 2 3 4 5 6 7 8 9 10

VILLE_DEP

VILLE_ARR

Monastir Tunis Djerba Djerba Tunis Sousse Tunis Monastir Tozeur Monastir Tunis Monastir Monastir Tabarka Tunis Tunis

Djerba Djerba Sousse Sousse Monastir Monastir Djerba Tunis Djerba Tunis Tabarka Tozeur Tabarka Tunis Djerba Djerba

35/69

NOMAV

CAPACITE

LOC

A300 A310 B707 A300 Concorde B747 B707 A310 Mercure Concorde

300 300 250 280 160 460 250 300 180 160

Monastir Monastir Tunis Sousse Monastir Tunis Tunis Djerba Sousse Tunis

H_DEP

H_ARR

11:00 17:00 14:00 18:00 06:00 11:00 08:00 07:00 09:00 12:00 15:00 16:00 11:00 15:00 17:00 18:00

12:00 19:00 15:30 19:30 08:00 11:30 09:00 08:00 10:00 14:00 16:00 17:00 13:00 16:00 19:00 20:00

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 ?

36/69

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.

σ

(AVION)

CAPACITE>350

(b) Quels sont les numéros et noms des avions localisés à Monastir ?

∏

NUMAV, NOMAV

(σ

(AVION))

LOC='Monastir'

(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'18:00' (VOL) R3 = σH_DEP>'18:00' (VOL) R4 = R1 ∩ R2

R=

R4 ∩ R3

37/69

(c) Quels sont les numéros des pilotes qui ne sont pas en service ?

∏ R2 = ∏

R1

=

(PILOTE) // Les numéros de tous les pilotes (VOL) // Les numéros des pilotes qui sont affectés à des voles

NUMPIL

NUMPIL

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) R2) R = ∏ NUMVOL(R1 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

PILOTE.NUMPIL = R1.NUMPIL

R2

)

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 R1.NUMPIL = R2.NUMPIL

R2

R3 R2.NUMAV = R3.NUMAV

38/69

)

(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 R1.NUMPIL = R2.NUMPIL

R3

)

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)

39/69

UNIVERSITE DE JENDOUBA

Equipe pédagogique : Chargé de cours : Riadh BOUSLIMI Chargées des TPs : Faten ABBASSI Afef HEDHLI Module : Base de données (Access)

Faculté des Sciences Juridiques, Economiques et de Gestion de Jendouba

Classe : 2ème année Licence appliquée Année Universitaire : 2010/2011 Semestre 2

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

40/69

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 d’outils. Fermer la table en cliquant sur le bouton de fermeture n’a pas été effectuée auparavant, le système vous préviendra.

dans la barre

ou par le menu Fichier/Fermer. Si la sauvegarde

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.

41/69

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. 2. 3. 4. 5. 6. 7. 8. 9.

Liste de tous les tournois avec toutes leurs caractéristiques (pseudo-champ *). Noms des tournois classés par importance (i.e., par coefficient d’importance décroissant). Nom des joueuses. Caractéristique d’un joueur dont le numéro est saisi au clavier (=[Message] en critère). Liste des tournois en précisant Nom du tournois / Nom du pays. Maximum des coefficients de tournois (fonction d’agrégat Max() accessible par le bouton Σ ). Nombre total de tournois (fonction d’agrégat Compte()). Nombre de tournois, par pays (fonction d’agrégat Compte() + regroupement). 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.

42/69

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

− réel double :

Plusieurs types de nombres : valeur entière comprise entre 0 et 255 valeur entière comprise entre –32 768 et 32 768 valeur entière comprise entre –2 147 483 648 et 2 147 483 647 valeur décimale comprise entre –3,40282E38 et 3,402823E38 avec une précision de six chiffres 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).

− − − −

octet : entier : entier long : réel simple:

Annexe 2 : Principales propriétés des champs sous Access Taille du champ

Format Décimales Masque de saisie Légende Valeur par défaut Valide si Message si erreur Null interdit Chaîne vide autorisée Indexé

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. 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. Pour un nombre, saisir les nombre de décimales désiré. 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. 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. C’est une constante, une expression ou une fonction qui est automatiquement validée si aucune autre n’est entrée. C’est une constante, une expression ou une fonction qui définit les conditions de validation d’un champ (contrainte de domaine). Dans le cas d’une utilisation de la propriété Valide si, définit le message à envoyer en cas de nonrespect des conditions de validité. Spécifie si la saisie de donnée dans ce champ est obligatoire ou non (pas de saisie = valeur Null) Indique si une chaîne de caractères ne contenant aucun caractère est valide ou non. Champ indexé (avec ou sans doublons) ou non

43/69

UNIVERSITE DE JENDOUBA

Equipe pédagogique : Chargé de cours : Riadh BOUSLIMI Chargées des TPs : Faten ABBASSI Afef HEDHLI Module : Base de données (Access)

Faculté des Sciences Juridiques, Economiques et de Gestion de Jendouba

Classe : 2ème année Licence appliquée Année Universitaire : 2010/2011 Semestre 2

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.

44/69

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 « Montant hors taxe ».

et dans la fenêtre de propriétés du champ écrire comme légende :

45/69

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.

46/69

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.

47/69

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

48/69

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.

49/69

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.

50/69

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 :

51/69

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.

52/69

UNIVERSITE DE JENDOUBA

Equipe pédagogique : Chargé de cours : Riadh BOUSLIMI Chargées des TPs : Faten ABBASSI Afef HEDHLI Module : Base de données (Access)

Faculté des Sciences Juridiques, Economiques et de Gestion de Jendouba

Classe : 2ème année Licence appliquée Année Universitaire : 2010/2011 Semestre 2

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 à ou ; l’aide des boutons

53/69

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.

54/69

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 3801 09-d´ec-83 Jendoubi Amel 98 rue des roses données requête Rue et de la table Personnel. Remarques !

Jendouba 8100

20-mars-65

de

les 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 :

55/69

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 dans la barre d’outils. Affichage/Propriétés ou cliquez sur le bouton 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.

56/69

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, ou Affichage > sélectionnez le champ, allez dans propriétés (cliquez sur le bouton 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 ).

57/69

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.

58/69

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

59/69

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.

60/69

Ministère de l’enseignement supérieur et de la recherche scientifique en Tunisie Faculté des Sciences Juridiques, Economique et de Gestion de Jendouba Module : Base de données Access Durée : 2heures Session : Mai 2011 Documents non autorisés Enseignant : Riadh BOUSLIMI Nombre de Pages : 8 Code secret de l’administration :

Numéro de carte d’identité nationale :

Nom et prénom : _____________________________________Goupe : ________________ N° ordre : __________________ Spécialité : ______________________________________________ Signatures des enseignants : ___________________ --------------------------------

Examen Base de données Access Questions de cours (15pts)

Code secret de l’administration:

1) Qu’est ce qu’une base de données ? ____________________________________________________________________________________________ ____________________________________________________________________________________________ ____________________________________________________________________________________________ _________ 2) Qu’est ce qu’un Système de Gestion de Base de Données ? Citer trois exemples. ____________________________________________________________________________________________ ____________________________________________________________________________________________ ____________________________________________________________________________________________ _________ ____________________________________________________________________________________________ ____________________________________________________________________________________________ ______ 3) Quels sont les fonctions de base d’un Système de Gestion de Base de Données (sans les détaillées)? ____________________________________________________________________________________________ ____________________________________________________________________________________________ ____________________________________________________________________________________________ _________ 4) Pourquoi les entreprises utilisent souvent Microsoft Access ? ____________________________________________________________________________________________ ____________________________________________________________________________________________ ______

Exercice n°1(10pts) Après l'analyse du système d'information de gestion de voyage, on dégage les règles de gestions suivantes :  Un vol comprend un ou plusieurs voyageurs ;  Un voyageur peut faire un ou plusieurs vols ;  Un avion est affecté à un ou plusieurs vols ;  Un pilote est affecté à un ou plusieurs vols ;

61/69

Ne rien écrire ici

Compléter la description graphique de la base de données ci-dessous en rajoutant les clés primaires et les liens entre les tables: Avion Avnum Avnom capacite

Pilote Vol

Plnum Plnom Plprenom Pladresse

Datedebut datefin Heuredebut heurefin

Voyageur Voynum Voynom Voyprenom voyadresse

Exercice n°2(45pts) La faculté des Sciences Juridiques, Économiques et de Gestion de Jendouba souhaite implémenter une base de données de sa gestion de la scolarité. Les membres du service introduisent chaque année les données concernant les étudiants. Un étudiant est caractérisé par une carte d’identité nationale unique (numérique de huit chiffres), nom, prénom, date de naissance, adresse et sexe (les valeurs autorisées sont « F » ou « H » et par défaut c’est « H »). Un étudiant est affecté à une et une seule spécialité et chaque spécialité comporte un ou plusieurs étudiants. Une spécialité est caractérisée par un code unique (caractères de taille 8) et un libellé. Chaque étudiant effectue une et une seule inscription pour chaque année et chaque inscription est identifiée par un numéro d’inscription unique. Dans une année on a un ou plusieurs étudiants qui sont inscrit. À la fin de chaque semestre, les membres de la scolarité feront la saisie des notes des étudiants.

62/69

Ne rien écrire ici

Chaque étudiant aura dans chaque module une note de contrôle contenu et une note d’examen, dont chacune des deux notes doit être comprises entre 0 et 20. Un module est caractérisé par un code (caractère de taille 3), nom abrévié, nom complet et coefficient. 1) Etablir la liste des colonnes Nom de colonne

Description

Type de données

Taille

63/69

Oblig- Valeur atoire par défaut

Valeurs autorisées

Sujet

Ne rien écrire ici

2) Identifier les liens entre les tables en utilisant le tableau ci-dessous. Table mère

Table fille

Clé primaire

Clé étrangère

3) Donner une description textuelle de la base de données en spécifiant les clés primaires et les clés étrangères. _______________________________________________________________________________ _______________________________________________________________________________ _______________________________________________________________________________ _______________________________________________________________________________ _______________________________________________________________________________ _______________________________________________________________________________ _______________________________________________________________________________

4) Donner une description graphique de la base de données.

64/69

Ne rien écrire ici

Exercice n°3(30pts) On donne le schéma de la base de données d’une gestion d’emploi du temps : Enseignant(codeEns,nom,prénom,ville,téléphone) Module(codeMod,libelle,coef,domaine) Classe(codeCl,effectifs) Cours(codeEns#,codeMod#,codeCl#,jour,heuredébut,heurefin,typecours) Remarques : − pour la colonne « type cours » les valeurs possibles sont : « cours », « Travaux dirigés » et « Travaux pratiques ». − pour la colonne « domaine » les valeurs possibles sont « Informatique », « Economie », « Gestion » et « Droit » Exprimer en algèbre relationnelle les requêtes suivantes : Q1) nom et prénom des enseignants qui habitent à Jendouba et Béja. Par deux méthodes. 1. Méthode en utilisant les opérateurs ensemblistes.

__________________________________________________________________________________________ __________________________________________________________________________________________ __________________________________________________________________________________________ _________________________________________________________________________________________ 2. Méthode sans utilisation des les opérateurs ensemblistes.

__________________________________________________________________________________________ __________________________________________________________________________________________ __________________________________________________________________________________________ ________________________________________________________________________________________

65/69

Ne rien écrire ici

Q2) nom et prénom des enseignants qui n’ont pas de cours. __________________________________________________________________________________________ __________________________________________________________________________________________ __________________________________________________________________________________________ __________________________________________________________________________________________ Q3) codecl, effectifs des classes qui ne suivent pas des cours du module « Base de Données Access ». __________________________________________________________________________________________ __________________________________________________________________________________________ __________________________________________________________________________________________ __________________________________________________________________________________________ __________________________________________________________________________________________ Q4) codecl des classes qui suivent des « cours » de « Base de Données Access » avec l’enseignant « BOUSLIMI Riadh ». __________________________________________________________________________________________ __________________________________________________________________________________________ __________________________________________________________________________________________ __________________________________________________________________________________________ __________________________________________________________________________________________ __________________________________________________________________________________________

66/69

Ne rien écrire ici

Q5) nom et ville des enseignants qui enseignent des « Travaux pratiques » du module « Base de Données Access ». __________________________________________________________________________________________ __________________________________________________________________________________________ __________________________________________________________________________________________ __________________________________________________________________________________________ Q6) nom des enseignants qui habitent « Jendouba » ou « Beja » ou « Kef » qui enseignent que les modules « Informatique » et qui rentrent avant « 15:00 ». __________________________________________________________________________________________ __________________________________________________________________________________________ __________________________________________________________________________________________ __________________________________________________________________________________________ __________________________________________________________________________________________ Q7) nom, prénom, libelle, jour, heuredébut et heurefin des cours enseignés par les enseignants qui habitent à « Tunis » et qui enseignent que le « lundi », le « mardi » et le « mercredi ».

__________________________________________________________________________________________ __________________________________________________________________________________________ __________________________________________________________________________________________ __________________________________________________________________________________________ __________________________________________________________________________________________ __________________________________________________________________________________________

67/69

Ne rien écrire ici

Q8) nom, codecl dont les enseignants enseignent le « lundi » les classes dont leurs effectifs est supérieurs à 100 et dont leurs villes est « Jendouba ». __________________________________________________________________________________________ __________________________________________________________________________________________ __________________________________________________________________________________________ __________________________________________________________________________________________ __________________________________________________________________________________________ __________________________________________________________________________________________ Q9) Nom et prénom des enseignants qui habitent à « Jendouba » qui enseignent toutes les classes. __________________________________________________________________________________________ __________________________________________________________________________________________ __________________________________________________________________________________________ __________________________________________________________________________________________ __________________________________________________________________________________________ __________________________________________________________________________________________

Bonne chance

68/69

Bibliographie     

G. Gardarin, Bases de données, Eyrolles, 2002 J.-M. Hasenfratz, ACCESS 2002 : Gérer ses bases de données, PUG, 2002 R. Alaguillaume, Access 2003 : notions de base, Dunod, 2004 J. Moréjon, Principes et conception d'une base de données relationnelle , les Éd. d'Organisation , 1992 P. Vincent, Votre première base de données avec Access 2002 , Osman Eyrolles multimédia , 2002

69/69