Relations entre les tables, exercice Access

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Relations entre les tables de la base de données

Ce neuvième exercice Access est le dernier sur la configuration des tables pour la construction de l'ossature de la base de données. Il est primordial que les informations soient découpées entre plusieurs tables afin d'éviter les répétitions. Et c'est ce que nous nous sommes attachés à confectionner. Mais dès lors, pour que ces données puissent être articulées entre elles, elles doivent être liées. Dans notre cas, sur les informations d'un client, nous devons pouvoir accéder à ses commandes. Et ces données sont placées dans une autre table. De même, à partir d'une commande, nous devons pouvoir obtenir le détail, comme la liste de tous les articles achetés. Bref, nous devons relier ces tables entre elles.

Source et présentation de la problématique
Pour aboutir cette configuration absolument fondamentale, nous devons réceptionner la base de données. Elle renferme tous les paramétrages essentiels que nous avons effectués au cours des exercices précédents.

• Télécharger la base clients-et-commandes-relations.accdb en cliquant sur son lien,
• Double cliquer sur le fichier téléchargé pour l'ouvrir dans Access,
• Puis, cliquer sur le bouton Activer le contenu du bandeau de sécurité,

Les six tables de la base de données sont listées dans le volet des objets Access, sur la gauche de l'écran.

• Cliquer droit sur la table Clients,
• Puis, dans le menu contextuel, choisir Mode création,

Nous accédons ainsi à la vue en conception de la table. Nous y retrouvons les champs et leurs paramétrages. C'est dans cette vue, pour chacun des champs de chacune des tables, que nous avons défini les types de données. Mais nous avons aussi réglé leur taille au plus juste. Nous les avons formatés et avons construit des masques de saisie pour certains. Nous avons enfin ajouté des règles de validité pour contrôler la conformité de l'information insérée.



Le premier champ de cette table se nomme Client_id. Il s'agit du champ de la clé primaire. C'est ce que rappelle le symbole de la petite clé sur son étiquette de ligne. C'est lui qui identifie chaque client de façon unique. C'est donc lui qui doit servir à repérer ses achats dans la table Commandes.

• Cliquer sur la croix de l'onglet pour fermer la table Clients,
• Dans le volet, cliquer droit sur la table Commandes,
• Dans le menu contextuel, choisir Mode création,

Le champ Com_num est celui de la clé primaire. Il identifie donc chaque commande de façon unique. C'est lui qui permettra d'accéder au détail de la commande dans la table liée. Le champ juste en dessous se nomme Com_client. Son type de données est numérique comme celui de la clé primaire Client_id dans la table Clients. Et pour cause, ce champ doit servir de clé externe. En d'autres termes, il doit rappeler l'identifiant du client pour lequel l'enregistrement décrit la commande. C'est donc entre la clé primaire de la table Clients et cette clé externe que la première relation doit être bâtie.

• Cliquer sur la croix de l'onglet pour fermer la table Commandes,
• Dans le volet des objets Access, cliquer droit sur la table Detail_commandes,
• Dans le menu contextuel, choisir Mode création,

Cette table propose une clé primaire et deux clés externes. Elle doit en effet être liée à deux tables. La clé primaire se nomme Det_num. Elle identifie donc chaque détail de commande de façon unique. La première clé externe se nomme Det_com. Elle doit rappeler le numéro de commande issu de la table Commandes. Et ce numéro peut être répété plusieurs fois. Si le client a acheté plusieurs articles, la table Detail_commandes les énumère tous dans des enregistrements différents. Donc ce numéro permet de récupérer toutes les informations de la commande, qui elle-même est liée au client. Enfin, le champ Det_ref est lui aussi une clé étrangère. Il est typé comme un texte car il rappelle la référence de l'article acheté sur l'enregistrement en cours. Cette référence est exploitée comme clé primaire dans la table Produits. C'est donc sur ces deux champs que la relation doit être établie. Ainsi, nous pouvons remonter au niveau de détail du produit, comme sa désignation et son prix unitaire par exemple.

• Fermer la table Detail_commandes en cliquant sur la croix de son onglet,
• Dans le volet des objets Access, cliquer droit sur la table Produits,
• Dans le menu contextuel, choisir Mode création,

En tête de liste, nous retrouvons en effet le champ produit_ref de la clé primaire. Il est bien typé comme un texte. De fait, il peut être exploité pour établir la relation dont nous parlions, avec la clé externe Det_ref de la table Detail_commandes.



Deux autres tables existent. La table Communes est indépendante. Nous l'exploiterons pour créer des listes déroulantes dépendantes. Il s'agira d'isoler les villes correspondant à un code postal. En revanche, la table Remises joue un rôle. Le code de la promotion doit intervenir dans la table Produits. Il est identifié par sa clé primaire Remise_id. Un champ du même type est prévu dans la table Produits. Il se nomme produit_code. Il doit donc servir de clé étrangère.

• Fermer la table Produits,

Relier les tables entre elles
Les relations entre les tables d'une base de données sont donc fondamentales. Elles permettent d'accéder en cascade aux contenus liés. Elles contribuent à améliorer les performances globales. Mais aussi et surtout, elles préservent l'intégrité des données. Une fois qu'elles sont établies, il n'est plus possible de créer des enregistrements orphelins. Par exemple, il n'est pas possible d'attribuer une commande à un numéro de client n'étant pas référencé dans la table Clients. Enfin, elles apportent beaucoup de souplesse. Elles permettent les mises à jour et suppressions en cascade. Si vous supprimez un client obsolète, toutes ses informations dans les tables liées, sont éliminées en cascade. Ce mécanisme est fort précieux pour ne pas risquer de conserver des données inutiles. En outre, il permet de ne pas avoir à rechercher les informations dépendantes à éliminer.

• Cliquer sur l'onglet Outils de base de données en haut de la fenêtre Access,
• Dans la section Relations du ruban, cliquer sur le bouton Relations,
• Dans la fenêtre qui apparaît, sélectionner toutes les tables sauf la table Communes,

• Puis, cliquer sur le bouton Ajouter et sur le bouton Fermer,

Nous basculons dans une vue représentant schématiquement chacune des tables avec ses champs. Vous remarquez que chaque champ de la clé primaire est accompagné du symbole de la clé. Ce repère visuel est important pour créer les liaisons. Il convient de réorganiser ces tables en hauteur pour mettre en valeur la hiérarchie.



En effet, la table Commandes dépend de la table Clients. La table Detail_commandes dépend de la table Commandes.

• Cliquer et glisser le champ de la clé primaire Client_id de la table Clients sur le champ de la clé externe Com_client de la table Commandes,

Instantanément, une boîte de dialogue apparaît. Elle matérialise le lien que nous venons d'établir entre ces deux champs.

• Cocher la case : Appliquer l'intégrité référentielle,
• Puis, cocher la case : Mettre à jour en cascade les champs correspondants,
• Ensuite, cocher la case : Effacer en cascade les enregistrements correspondants,

Ces actions résument le mécanisme que nous avons expliqué précédemment. L'intégrité référentielle porte bien son nom. Elle est destinée à préserver l'intégrité des données sur la base des références représentées par les clés. Elle empêche la création de ces fameux enregistrements orphelins. La mise à jour et la suppression en cascade parlent d'elles-mêmes. Comme nous l'évoquions, si un client est supprimé, toutes les traces liées dans les tables dépendantes sont automatiquement éliminées.

• Cliquer sur le bouton Créer pour valider cette première relation,

Cette liaison apparaît désormais matérialisée graphiquement dans l'éditeur de relation. Une jointure s'opère entre le champ de la clé primaire Client_id et le champ de la clé externe Com_client. Le symbole du chiffre 1 accompagne le champ de la clé primaire. Le symbole infini accompagne celui de la clé externe. Ce schéma est sans équivoque. Il rappelle qu'un client peut passer autant de commandes qu'il le souhaite. Son numéro peut donc être répété indéfiniment dans la table Commandes. Inversement, une commande correspond à un seul client.

De la même façon, nous devons désormais établir les relations entre les autres tables.

• Glisser le champ de la clé primaire Com_num de la table Commandes sur le champ de la clé externe Det_com de la table Detail_commandes,
• Dans la boîte de dialogue, Cocher la case : Appliquer l'intégrité référentielle,
• Puis, cocher la case : Mettre à jour en cascade les champs correspondants,
• Ensuite, cocher la case : Effacer en cascade les enregistrements correspondants,
• Enfin, cliquer sur le bouton Créer pour établir la liaison,

La même symbolique schématise désormais la relation existant entre la table Commandes et la table Detail_commandes. Une commande peut en effet être composée de plusieurs articles achetés. Sur chaque enregistrement de la table dépendante, ils seront reliés au numéro de commandede la table parente.

• Glisser le champ de la clé primaire produit_ref de la table Produits sur le champ de la clé externe Det_ref de la table Detail_commandes,
• Dans la boîte de dialogue, Cocher les trois cases et cliquer sur le bouton Créer,

Une même référence article peut être achetée indéfiniment, expliquant cette relation entre les deux tables.

• Enfin, glisser le champ de la clé primaire Remise_id de la table Remises sur le champ de la clé externe produit_code de la table Produits,
• Dans la boîte de dialogue, Cocher les trois cases et cliquer sur le bouton Créer,

Un même code promotionnel peut effectivement être appliqué sur n'importe quel article, donc une infinité de fois.



Toutes les relations sont désormais établies. Ces dépendances vont sécuriser les données de la base. Notez que la table Detail_commandes dépend de deux tables.

• Enregistrer les relations (CTRL +S),
• Dans le ruban Contextuel Créer, cliquer sur le bouton Fermer,
• Dans le volet des objets Access, sélectionner la table Detail_commandes,
• En haut de la fenêtre Access, cliquer sur l'onglet Outils de base de données,
• Dans la section Relations du ruban, cliquer sur le bouton Dépendances d'objet,

Un volet apparaît sur la droite de l'interface. Il résume les dépendances existant entre les différents objets Access. Pour l'instant, notre base de données n'est constituée que de tables. Mais comme vous le remarquez, cet outil montre la relation de dépendance entre la table Detail_commandes et les tables Commandes et Produits.

Si vous déployez l'une et l'autre, vous accédez aux dépendances respectives. Cette fonctionnalité est intéressante pour rappeler la construction de la base et son mode de fonctionnement. Rappelez-vous d'un principe fondamental : Avant de chercher à établir les relations, l'information doit être décomposée en plusieurs tables. Et dans chacune, la clé primaire et la clé externe doivent être prévues.

Intégrité des données
Il est temps de tester l'impact de ces relations sur le fonctionnement de notre base de données.

• Cliquer sur la croix du volet Dépendances d'objet pour le fermer,
• Dans le volet des objets Access, double cliquer sur la table Clients,

Comme le champ Client_nom l'indique, il s'agissait d'un essai que nous n'avions pas supprimé.

• Cliquer droit sur son étiquette de ligne,
• Dans le menu contextuel, choisir Supprimer l'enregistrement,
• Puis, cliquer sur le bouton Oui du message de confirmation qui apparaît,

Désormais, sur la dernière ligne vide, nous proposons de créer un nouveau client.

• Dans le champ Client_civilite, choisir Monsieur avec la liste déroulante,
• Dans le champ Client_nom, saisir : Taha,
• Dans le champ Client_prenom, saisir : Rachid,
• Dans le champ Client_dep, taper : 06000,
• Dans le champ Client_ville, saisir : Nice,
• Puis, cliquer sur la ligne vide du dessous pour valider la création de l'enregistrement,

En raison des règles de validité que nous avons posées dans un exercice précédent, nous avons été contraints de renseigner toutes les informations. Et c'est tant mieux.



Comme vous le constatez, le numéro auto-incrémenté de la clé primaire n'est pas dans la suite logique du précédent. Nous avions réalisé quelques tests de création d'enregistrements dans les précédents exercices. Et les numéros attribués ne le sont plus, pour des raisons de sécurité. Nous passons donc du 34 au 45.

• Cliquer sur la croix de l'onglet pour fermer la table Clients,
• Dans le volet des objets Access, double cliquer sur la table Commandes,

Cette dernière est vide pour l'instant. En effet, aucune commande n'a encore été passée. Nous proposons de créer la première et de l'attacher à un client. Naturellement, le champ de la clé primaire n'est pas à renseigner.

• Dans le champ Com_client, saisir le numéro 38,
• Enfoncer la touche Tab pour sélectionner le champ Com_date,
• Enfoncer de nouveau la touche Tab pour conserver la date du jour proposée par défaut,
• Dans le champ Com_montant, saisir : 1200,
• Puis, enfoncer de nouveau la touche Tab du clavier pour valider l'enregistrement,

Instantanément et comme vous le remarquez, la règle de l'intégrité référentielle que nous avons cochée, se déclenche. Elle empêche la création d'une commande attachée à un client n'existant pas dans la table Clients. Nous l'avons remarqué en effet, la clé primaire saute du numéro 34 au 45, dans la table Clients. Il en serait de même si nous créions un détail de commande attaché à un numéro de commande non référencé. La création serait également refusée si nous renseignions un détail de commande sur la base d'un code article non archivé dans la table Produits. Ces règles de sécurité sont donc fondamentales dans le fonctionnement d'un gestionnaire de bases de données comme Access.

• Cliquer sur le bouton Ok de l'alerte Access,
• Dans le champ Com_client, remplacer le numéro 38 par 45,
• Puis, cliquer sur la ligne vide du dessous pour valider l'enregistrement,

Cette fois, aucune alerte ne se déclenche. En effet, l'identifiant 45 correspondant au dernier client que nous avons créé. Tel que le dictent les relations établies entre les tables, nous avons donc le droit de lui affecter une commande.

• Cliquer sur la croix de l'onglet pour fermer la table Commandes,
• Dans le volet des objets Access, double cliquer sur la table Clients,
• Atteindre le dernier enregistrement en queue de liste,
• Cliquer droit sur l'étiquette de ligne du client au numéro 45,
• Dans le menu contextuel, choisir Supprimer l'enregistrement,
• Puis, valider l'alerte en cliquant sur le bouton Oui,
• Fermer la table Clients et ouvrir la table Commandes,

Comme vous le constatez, cette dernière est vide bien que nous ne soyons pas intervenus dessus. Grâce aux relations entre les tables, la suppression du client a engagé la suppression de toutes ses dépendances.