InfoApp S6 Séance2 [PDF]

Zitiervorschau

Université Hassan II - Casablanca Faculté des sciences juridiques, économiques et sociales Mohammedia

Cours Informatique Appliquée – S6

Gestion de Base de données avec ACCESS Séance 2 Amal EL MZABI

Relations entre les tables d’une BD Prenons l’exemple de la gestion d’une collection de livres dans une

bibliothèque Livres Nom Auteur Prénom Auteur Date de naissance de l’auteur Nationalité auteur Titre Nbre pages Année d’édition Éditeur 2

Il est plus judicieux de créer ici deux tables, il faut toujours regrouper dans une même table toutes les informations relatives au même « sujet » Auteurs Livres Nom Auteur

Nom Auteur Prénom Auteur

Prénom Auteur

Date de naissance

Date de naissance

Nationalité

Nationalité Livres

Titre

Nbre pages

Titre

Éditeur

Nbre pages

Année d’édition

Éditeur Année d’édition 3

Trouver un moyen pour savoir exactement à

Auteurs

quel auteur correspond chaque livre.

N° Auteur

→ ajouter dans la table Auteurs un champ qui

Nom Auteur

va identifier de façon unique chaque enregistrement → Clé Primaire

Prénom Auteur Date de naissance Nationalité

Associer un livre à un auteur-

Livres

→ ajouter un champ dans la table Livres, ce

N° Auteur

champ contient le n° de l’auteur de livre

Titre Nbre pages Éditeur

N° Auteur clé primaire dans la table Auteurs Et clé étrangère dans la table Livres

Année d’édition

4

Définitions ➢ Une relation est un lien entre deux tables, associant des données de la première (table source) avec celles de la deuxième (table destination).

➢ Relation : la relation entre deux tables est basée sur un champ commun aux deux tables. Elle est matérialisée par une ligne appelée « ligne de jointure ».

➢ Une relation a pour principe la correspondance des données des champs clés de deux tables : – Ces champs sont la clé primaire et la clé étrangère. 5

Relation un-à-un Dans une relation de type un-à-un: – Chaque

enregistrement

de

la

table

1

ne

peut

correspondre qu'à un (seul) enregistrement de la table 2,

– Et inversement, chaque enregistrement de la table 2 ne peut correspondre qu'à un (seul) enregistrement de la table 1. Cette relation est représentée par un

trait reliant la clé

primaire de la table 1 avec la clé primaire de la table 2 6

Table 1

Table 2

Enregistrement 1

Enregistrement 1

Enregistrement 2

Enregistrement 2

Enregistrement 3

Enregistrement 3

Enregistrement 4

Enregistrement 4

Enregistrement 5

Enregistrement 5

Exemple Possède Personne Num_permis

1

1

Voiture

Immatricule

Nom

Marque

Prénom

Couleur

Ville

Puissance

➢ Une personne possède une seule voiture ➢ Chaque voiture n’appartient qu’à une seule personne

7

Relation un à plusieurs (1 à n) Un enregistrement de la table 1 est en relation avec plusieurs enregistrements de la table 2, et un enregistrement de la table 2 est en relation avec un seul enregistrement de la table 2 . Table 1 Enregistrement 1 Enregistrement 2

Table 2 Enregistrement 1 Enregistrement 2 Enregistrement 3 Enregistrement 4 Enregistrement 5

Cette relation est représentée par un trait reliant la clé primaire de la table 1 avec la clé étrangère de la table 2 8

Exemple 1 Clé primaire

Clé secondaire Auteurs N° Auteur Nom Auteur Prénom Auteur Date de naissance Nationalité

1

n

Livres N° Auteur Titre Nbre pages Éditeur Année d’édition

Un auteur a un ou plusieurs livres

Chaque livre ne correspond qu’un seul auteur

9

Exemple 2 Un Fournisseur peut fournir un ou plusieurs produits.

Chaque produit est fourni qu’avec un seul fournisseur.

10

Relation Plusieurs à Plusieurs (n - m) Un enregistrement de la table 1 est en relation avec un ou plusieurs enregistrements de la table 2, et un enregistrement de la table 2 est en relation avec un ou plusieurs enregistrements de la table 2 .

Exemple ➢ Un client achète un ou plusieurs produits ➢ Un produit peut être vendu à un ou plusieurs clients, Autrement dit, un produit peut être présent sur plusieurs

ventes

11

Comment définir une relation Plusieurs à Plusieurs dans Access ? Solution Il est impossible de lier directement 2 tables par une relation de Plusieurs à Plusieurs. Il faut systématiquement passer par une troisième table. On suppose que nous avons déjà 2 tables construites correctement (notamment avec une clé primaire chacune). Dans ce cas : 1. Créez une troisième table, qu'on appellera "table de jonction". 2. Dans cette table, reprenez la clé primaire des tables 1 et 2. 3. La clef primaire de la table 3 sera au minimum la combinaison des 2 autres clés. 4. Dans la fenêtre Relations, tracez maintenant une relation classique "1 à Plusieurs" entre les tables 1 et 3, et faites de même entre les tables 2 et 3.

12

Dans cet exemple, la table Ventes est la table de jonction. On peut désigner la clé primaire : le champ Num_Vente et pas le couple composé de (Num_produit, Code_Client).

Pour établir ces relations sur Access: 1.

Fermez tous les objets, ne gardez que la fenêtre « Base de données » et cliquez sur Relations dans l’onglet Outils de base de données.

2. Activez la commande « Afficher la table » dans le menu « Relations », sélectionnez

ensuite la table et cliquez sur

«Ajouter».

3. Faites glisser le champ comportant la clé primaire sur le champ homologue de l’autre table

14

Exemple

Table Auteur

Table Livre

15

Outils de base de données → Relations

Cliquer sur relations

16

Afficher la table → Ajouter Auteur + Ajouter Livre → Fermer

17

Glisser N°auteur (table auteur) vers N°auteur (table Livre)

18

Cette relation signifie qu’un auteur peut être associé à plusieurs livres et un livre est associé à un seul auteur.

L’intégrité référentielle Un ensemble de règles suivies par Access pour gérer les relations entre tables et éviter des incohérences dans une base de données.

Elle empêche de supprimer accidentellement des enregistrements dans une table mère quand il existe des enregistrements correspondants dans la table Fille.

20

Prenons l'exemple de gestion Fournisseurs/Produits : un fournisseur fournit plusieurs produits. ➢Lorsque vous définissez la relation entre la table Fournisseurs et la table Produits, vous devez activer l'intégrité référentielle. De

cette façon, Access interdira la création d'un produit si le fournisseur n'existe pas au préalable.

➢D'une manière générale, l'intégrité référentielle implique de renseigner la table du côté "1" avant la table du côté "Plusieurs".

➢Les deux tables doivent appartenir à la même base de données. 21

Mises à jour et suppressions en cascade Mettre à jour en cascade Si cette option est cochée et que vous changez le code du

fournisseur (dans la table Fournisseurs), tous les produits correspondants verront aussi leur Code modifié. Inversement, si la case n'est pas cochée, il sera impossible de modifier un Code_Fournisseur si des produits lui sont attribuées.

Effacer en cascade Si cette option est cochée et que vous supprimez un fournisseur,

tous ses produits seront automatiquement détruites. Inversement, si la case n'est pas cochée, il sera impossible de supprimer un fournisseur tant que tous ses produits n'ont pas été détruites.

22

Application 1 Un service financier réalise un audit de données bancaires. Le schéma relationnel de ces données est le suivant : – Clients (IdClient, Nom, Prénom, Ville, Tél)

– Compte (IdCompte, IdClient, DateCréation, Solde) – Action (IdAction, IdCompte, DateAction, Montant)

1. Quelles sont les clés primaires et les clés secondaires de chaque table? Justifiez votre réponse. 2. Créer la BD sous Access. Ajouter les relations entre les

tables en activant l’intégrité référentielle.

23

3. Parmi les enregistrements suivants lesquels seront rejetés par le SGBD et pourquoi ? Table : Clients IdClient

Nom

1

Amine

Prenom

Ville

Tél

Casablanca

0663562773

Salim

Rajae

Tanger

0662152435

3

Amine

Jamal

Agadir

0661661610

4

Nasri

Reda

Tanger

0670263560

Table : Compte IdCompte

IdClient

DateCréation

Solde

123

1

10/3/2012

350200

124

3

12/1/2013

16I890

125

2

10/4/2013

680000

124

4

2/11/2014

255000

24

✓ Pour table Client : Le 2ème enregistrement car le champ « IdClient » étant la clé primaire ne peut pas être vide ou nulle. ✓ Pour table Compte :

Le 2ème enregistrement car la valeur du champ Solde ne respecte pas le format approprié. Le 3ème enregistrement car aucun client n’a l’Id = 2. Le 4ème enregistrement car il s’agit d’une clé primaire répétée (redondance). 25

Application 2 Le responsable du complexe cinématographique a mis en place cette base de données afin de gérer les projections des films dans les différentes salles de cinéma : Acteur (Num_acteur, Nom, Prénom) Jouer (Num_acteur, Num_Film, Rôle) Film (Num_Film, Titre, Genre, Année) Projection (Num_Ciné, Num_Film, Date) Cinéma (Num_Ciné, Nom, Adresse) 1. Quelles sont les clés primaires et les clés secondaires de chaque table ? Justifiez votre réponse. 2. Tracez les relations entre les tables de cette base de donnée. 26

Application 3: Base de données « Gestion de Commandes » La BD « Gestion de commandes », qui sera utilisée pour le reste du cours, contient les quatre tables suivantes : Clients, Commandes,

Produits et Détails_Commande, dont le schéma relationnel est :

27

Les requêtes La

requête

est

une

action

qui

porte

sur

plusieurs

enregistrements. Elle sert à exploiter les données contenues dans les tables. Elle permet de: ➢ Trier des données ➢ Extraire des données par critères ➢ Produire des calculs ➢ Créer, Modifier des tables

Le SGBD Access permet de créer des requêtes en utilisant soit une interface graphique QBE, soit le langage SQL 28

Types de requêtes ✓ La requête sélection : Elle permet de sélectionner des enregistrements qui répondent aux critères demandés, de faire des calculs et des regroupements. Elles ressemblent beaucoup aux filtres, mais permettent, en plus, de travailler sur plusieurs tables simultanément. ✓ La requête d'Analyse croisée : Cette requête présente ses résultats sous forme de tableau (de type Excel). On l'utilisera pour comparer des valeurs, dégager des tendances. ✓ La requête de Création de table : Cette requête crée une table à partir des données qu'elle a extraite dans une ou plusieurs autres tables. ✓ La requête Mise à Jour : Elle modifie le contenu d'un ou plusieurs champs d'une ou plusieurs tables. C'est le moyen le plus efficace pour mettre à jour un grand nombre d'enregistrements en une seule opération. ✓ La requête Ajout : Cette requête ajoute les données qu'elle a extraites à la fin d'une table déjà existante. ✓ La requête Suppression : Cette requête supprime un ou plusieurs enregistrements dans une ou plusieurs tables. 29

Requête sélection Création et exécution d’une requête de sélection en mode QBE Dans la suite on utilisera la Base de données « Gestion de commandes » Pour créer une requête de sélection :

− Fermer toutes les tables ouvertes de la BD ; − Dans de l’onglet « Créer » aller dans le groupe « Requêtes » et cliquer sur l’élément « Création de requête ». − La fenêtre « Afficher la table » s’ouvre.

30

Ajouter, à partir de la fenêtre « Afficher la table », la ou les tables concernées par la requête. Ajouter par exemple la table Clients et refermer la fenêtre.

31

On obtient ensuite l’interface suivante: Pour basculer entre le mode création et mode exécution

Pour exécuter une requête

Pour afficher la ou les tables concernées par la requête

Les types de requêtes action

Remarque : Une requête qui exploite une seule table est dite requête monotable ou simple 32

Structure et paramétrage d’une requête de sélection

Nom de la requête Liste des champs

Les paramètres de la requête

Requête1 est le nom par défaut de la requête. On peut le changer par un nom significatif une fois l’enregistrée.

33

Pour le paramétrage d’une requête, on sélectionne les champs : soit que l’on souhaite afficher dans notre résultat de requête soit dont on a besoin en critères pour exécuter

la requête. La ligne "Champ" indique les champs à ajouter. Pour ajouter un champ, soit double-cliquer sur le champ, soit le

sélectionner dans la table et l'amener avec la souris, soit on clique sur la ligne "Champ", la liste de tous les champs s'affiche alors et on en sélectionne un. Un des champs

proposés s’appelle '*', ce champ signifie "tous les champs de la table". 34

La ligne "Table" sert à sélectionner la table contenant le champ sélectionné. La colonne "Tri" indique de quel ordre vont être triés les champs dans le résultat de la requête.

La colonne "Afficher" indique si le champ doit être affiché ou non.

La ligne "Critères" indiquera le ou les critères de sélection pour la requête. 35

Manipulation des critères ➢ Si vous avez deux conditions regroupées par ou, la deuxième condition est spécifiée dans la ligne OU. ➢ Si les conditions sont regroupées par l’opérateur ET alors elles sont spécifiées dans la même ligne CRITERES. ➢ Une donnée de type texte doit être écrite entre guillemets ("...").

➢ Les nombres sont écrits tels quels. ➢ La date et / ou l'heure doivent être placées entre dièses (#), avec séparation de jours, mois et année par la barre oblique (/) ou un trait d'union (-), heure avec séparation de minute et seconde par deux points (:).

36

Access met à notre disposition en plus des opérateurs (=, , , =), les opérateurs suivants: Opérateur Entre Pas Entre

In

Pas In Est NULL Est pas NULL

Comme Pas

Signification Sélectionne les enregistrements pour lesquels la valeur d'un champ est comprise dans un intervalle de valeurs Sélectionne les enregistrements pour lesquels la valeur d'un champ est comprise dans une liste.

Exemple Entre "A" et « D" Pas Entre 20 et 30 Entre #01/01/18# et #31/12/18#

In ("Rabat "; "Fès")

Pas In (1;3;5)

Sélectionne les enregistrements pour lesquels un champ est vide ou non

Est NULL

Sélectionne les enregistrements contenant une donnée approximative.

Comme « ma*"

Sélectionne les enregistrements ne correspondant pas au critère

Pas Entre "A" et "D"

Est pas NULL

37