LES BASES DE DONNÉES RELATIONNELLES

Chapitre 1 : Introduction

1 - Préambule

Les activités humainesgénèrent des données. Il en a toujours été ainsi et, plus notre civilisation sedéveloppe, plus le volume de ces données croît. Aujourd'hui, les données sontde plus en plus souvent gérées par des moyens informatiques. Le mot"informatique" lui-même résulte de la contraction de"information" et "automatique". l'informatique est donc latechnique qui permet le traitement automatique de l'information.

 

Dans les entreprises, onmanipule souvent des données ayant la même structure. Prenons l'exemple de laliste des membres du personnel : pour chaque personne, on enregistre lenom, le prénom, le sexe, la date de naissance, l'adresse, la fonction dansl'entreprise, etc. Toutes ces données ont la même structure ; si ellessont gérées par des moyens informatiques, on dit qu'elles constituent une basede données. On utilise aussi le sigle BDD, et le terme anglais correspondantest Data Base.

 

Définition : une basede données est un ensemble structuré de données, géré à l'aide d'un ordinateur.

On rajoute parfois deuxconditions supplémentaires à la définition précédente :

·         exhaustivité: la base contient toutes les informations requises pour le service que l'on enattend ;

·         unicité: la même information n'est présente qu'une seule fois (pas de doublons).

Reprenons l'exemple de labase de données du personnel. Elle est utilisée pour la paye mensuelle, pourl'avancement, les mutations, les mises à la retraite, etc. L'exhaustivité estindispensable pour le personnel, car la personne qui est absente de la base...n'est pas payée. L'unicité est importante pour l'employeur, car la personne quiest enregistré deux fois... risque de toucher double paye ! Les bases dedonnées sont très utilisées dans les entreprises. Outre la liste des membres dupersonnel, on y trouve tout ce qui concerne :

• les fournisseurs
• les clients
• les prospects
• les contacts
• les commandes
• les factures
• les produits et services
• le stock
• le personnel
• les salaires et les charges correspondantes
• le commerce électronique, etc.

Bien entendu, les bases dedonnées existaient avant l'introduction de l'informatique au milieu duvingtième siècle, mais elles ne portaient pas encore ce nom. Pour stockerl'information, on utilisait des fiches, regroupées dans des boites appeléesfichiers. Initialement, les fiches étaient triées manuellement. Avecl'introduction des perforations, le tri devint mécanique, puisélectromécanique. Le développement des bases de données gérées par des moyensinformatiques a rendu obsolètes ces anciennes techniques.

 

 

2 - Le stockage desdonnées (les tables)

Des données ayant mêmestructure peuvent être rangées dans un même tableau. Dans le cas de la listedes membres du personnel, la première colonne contiendra les noms, la secondeles prénoms, la troisième le sexe, la quatrième la date de naissance, etc. Lacaractéristique d'un tel tableau est que toutes les données d'une même colonnesont du même type. Dans une base de données, un tel tableau s'appelle unetable. Ci-dessous se trouve un exemple simple de table :

Dans une table, les termesligne et enregistrement sont synonymes. Il en est de même pour les termescolonnes et champs. En anglais : row et column. La table d'une base de donnéesne doit pas être confondue avec la feuille de calcul d'un tableur. Cettedernière est également constituée d'un tableau, mais toutes les données d'unemême colonne ne sont pas forcément du même type. Dans le cas où elles le sont,la feuille de données peut facilement être transformée en table parimportation. Par contre, l'exportation d'une table de SGBD vers un tableur estthéoriquement toujours possible. En pratique il faut, dans les deux cas,disposer du filtre qui permet à l'un des logiciels de lire le format del'autre. A défaut, on peut exporter en mode texte (avec délimiteur) dans unlogiciel, puis réimporter dans l'autre.

3 - Le logiciel (SGBD)

Le logiciel qui gère une basede données s'appelle un système de gestion de base de données. On le désignegénéralement pas son sigle SGBD (DBMS en anglais, pour Data Base ManagementSystem). En fait, il devrait s'appeler "logiciel de gestion de base dedonnées" car, en informatique, le mot "système" désignegénéralement l'ensemble matériel + logiciel. Mais l'expression SGBD estconsacrée par l'usage, et nous n'avons pas d'autre choix que l'adopter.

Tous les SGBD présentent àpeu près les mêmes fonctionnalités. Ils se distinguent par leur coût, par levolume de données qu'ils sont capables de gérer, par le nombre d'utilisateursqui peuvent interroger la base simultanément, par la facilité avec laquelle ilss'interfacent avec les autres logiciels d'application utilisés parl'entreprise, etc.

Il existe des bases dedonnées de toutes tailles, depuis les plus modestes (une liste des numéros detéléphone utilisée par une seule personne), jusqu'aux plus grandes (la base desdonnées commerciales d'un magasin à succursales multiples, contenant destéraoctets de données ou plus, et utilisée par le service marketing).

Le nombre d'utilisateursutilisant une base de données est également extrêmement variable. Une BDD peutservir à une seule personne, laquelle l'utilise sur son poste de travail, ouêtre à la disposition de dizaines de milliers d'agents (comme dans les systèmesde réservation des billets d'avion par exemple).

 

Les éditeurs de SGBD separtagent un marché mondial enlente régression depuis deux ans : 8-9 milliards de dollars en 2000, 7-8milliards en 2001 et 6-7 milliards en 2002, les chiffres variant quelque peuselon les sources. Les principaux éditeurs (avec leurs parts de marché enl'an 2002, calculées sur le chiffre d'affaires) sont :

• IBM (36 %), éditeur des SGBD DB2 (développé en interne - mis sur le marché en 1984) et Informix (obtenu par rachat de l'entreprise correspondante en 2001 ; la société Informix avait été créée en 1981. Une version bridée de DB2 vient d'apparaître sur le marché, où elle concurrence SQL Server de Microsoft ;
• Oracle (34 %), éditeur du SGBD qui porte le même nom. Cette entreprise a été créée en 1977 ;
• Microsoft (18 %), éditeur de trois SGBD. SQL Server est destiné aux gros systèmes, Access est un produit de bureautique professionnelle, et Foxpro est destiné aux développeurs. L'arrivée de Microsoft sur le marché des SGBD date du début des années 90 ;
• Sybase (<3 %). Cette entreprise, qui a été créée en 1984, est aujourd'hui marginalisée.

Ces chiffres recouvrent desréalités contrastées, quand on les fractionne par plate-forme.Dans le monde Unix, Oracle est en tête avec 62 %, suivi d'IBM (Informix compris)avec 27 %, alors que Microsoft n'est pas présent sur ce marché. Dans lemonde Windows, Microsoft a pris la tête avec 45 %, suivi d'Oracle avec27 % et d'IBM avec 22 %.

Le classement par nombred'exemplaires (ou licences) vendus est très différent. Il met en avant les SGBDconçus pour gérer les bases de taille modeste ou modérée. Dans ce domainel'éditeur Microsoft, qui vend plusieurs millions d'exemplaires de son logicielAccess par mois, pulvérise tous les records. L'usage des SGBD se démocratise àtoute vitesse, bien qu'un SGBD soit plus difficile à maîtriser qu'un traitementde texte ou un tableur (pour ne citer que les logiciels les plus courants).L'image du SGBD servant uniquement les très grosses bases, propriété d'unegrande multinationale, fonctionnant sous Unix sur une machine monstrueuse, gérépar un administrateur dictatorial, et coûtant un prix fou -- a vécu. Bondébarras !

 

Les analystes pensent que lemarché des SGBD fonctionnant sous Windows rejoindra celui des SGBD fonctionnantsous Unix en 2003 (pour le nombre de logiciels vendus) et en 2005 (pour lechiffre d'affaires). De manière schématique, on peut dire qu'Oracle arrive entête pour la technicité, et Microsoft pour la convivialité et la facilitéd'emploi. A fonctionnalités comparables, Oracle a la réputation d'être deuxfois plus cher que ses principaux concurrents.

 

En une vingtaine d'années, lemarché des SGBD s'est fortement consolidé. Ainsi dBase, le SGBD le plus utilisédes années 80-90, n'a plus qu'une importance mineure. Paradox, qui eut sonheure de gloire, semble avoir totalement disparu, bien que son format soitencore utilisé.

Un SGBD est principalementconstitué d'un moteur et d'une interface graphique. Le moteur est le coeur dulogiciel, c'est à dire qu'il assure les fonctions essentielles : saisirles données, les stocker, les manipuler, etc. L'interface graphique permet àl'utilisateur de communiquer commodément avec le logiciel. Pour dialoguer avecles SGBD qui ne sont pas équipés d'une interface graphique, il faut utiliser lelangage SQL (Structured Query Language), et introduire les instructions àl'aide d'un éditeur de lignes.

Langage normalisé demanipulation des bases de données, SQL est utilisable avec pratiquement tousles SGBD du marché. Cependant, chaque éditeur ayant développé son propre"dialecte" -- comme c'est toujours le cas en informatique -- il fautpouvoir disposer d'un "dictionnaire" pour transporter une BDD d'unSGBD à l'autre. Ce "dictionnaire" a été développé par Microsoft sousle nom ODBC (Open Data Base Connectivity).

4 - Le matériel (serveurde BDD)

Le choix du matérielinformatique sur lequel on installe un SGBD est fonction, comme ce dernier, duvolume des données stockées dans la base et du nombre maximum d'utilisateurssimultanés.

Lorsque le nombred'enregistrements par table n'excède pas le million, et que le nombred'utilisateurs varie de une à quelques personnes, un micro-ordinateur actuel debonnes performances, un logiciel système pour poste de travail, et un SGBD"bureautique" suffisent. Exemple : le logiciel Access 2002de Microsoft, installé sur un PC récent, doté de 1 Go de mémoire vive etfonctionnant sous Windows XP.

 

Si ces chiffres sontdépassés, ou si le temps de traitement des données devient prohibitif, il fautviser plus haut. Le micro-ordinateur doit être remplacé par un serveur de BDD,dont les accès aux disques durs sont nettement plus rapides. Le logicielsystème client doit être remplacé par un logiciel système serveur (doncmulti-utilisateurs), et le SGBD bureautique par un SGBD prévu pour les grossesBDD multi-clients. Ceci dit, la structure d'une grosse base n'est pasdifférente de celle d'une petite, et il n'est pas nécessaire de disposer d'un"mainframe" (une grosse machine) gérant des milliers de milliards d'octetspour apprendre à se servir des BDD. Ce n'est pas parce qu'il gère un plus grandvolume de données qu'un SGBD possède plus de fonctionnalités.

Quelle que soit sa taille, lesystème constitué de la machine et du SGBD doit être correctement équilibré. Unserveur de BDD doit posséder à la fois les qualités de serveur de fichier (bonaccès aux disques) et celles d'un serveur d'applications (unité centrale biendimensionnée, mémoire vive suffisante). En observant un serveur de BDD en coursde fonctionnement, on peut observer les trois cas de déséquilibresuivants :

• la machine fait du "swapping", c'est à dire qu'elle passe son temps à promener des données entre la mémoire vive et la mémoire virtuelle (laquelle réside sur disque). Le remède consiste à augmenter la mémoire vive -- si la chose est matériellement possible ;
• si l'unité centrale est sous-occupée, alors que le disque dur ne cesse de tourner, la machine est sous-dimensionnée quant à sa mémoire de masse. Les remèdes : utiliser une interface disque plus performante (SCSI), un disque dur plus rapide, un système RAID 0. Ce cas est le plus fréquemment rencontré ;
• si l'unité centrale est utilisée à fond, alors que les disques durs sont peu sollicités, la machine est sous-motorisée. Les remèdes : utiliser une machine possédant des processeurs plus rapides, ou un plus grand nombre de processeurs.

Jusqu'à une date récente, lesconstructeurs de serveurs (et les éditeurs de SGBD) conseillaient à leursclients de consolider leurs données, en les rassemblant dans un nombre minimumde grosses BDD, installées sur un nombre minimum de serveurs surpuissants.Comme le coût des serveurs croît exponentiellement avec le nombre deprocesseurs, et que le coût des licences (des SGBD) est proportionnel au nombrede processeurs, constructeurs et éditeurs ont gagné de l'or pendant la dernièredécennie. Avec l'éclatement de la bulle Internet, les cordons de la bourse sesont resserrés, si bien que les services informatiques des entreprisescommencent à recourir - de gré ou de force - à des systèmes plus décentraliséset de taille plus raisonnable.

5 - L'administration de labase de données

L'ensemble "serveur deBDD + SGBD" constitue un système informatique dont l'importance ne cessede croître dans l'entreprise. La personne responsable de la maintenance et del'évolution de ce système s'appelle l'administrateur de la base de données. Dèsque l'entreprise atteint la taille d'une grosse PME, l'administration de la BDDpeut nécessiter la présence d'une personne à temps plein, voire plus.

Être administrateur de BDDrequiert des compétences particulières, très différentes de celles requisespour être administrateur de réseau ou de système informatique. Il en résulte ledéveloppement de deux pôles de compétences informatiques dans l'entreprise. On remarqueque, dans l'entreprise toujours, la spécialisation des informaticienss'accroît.

Pour être complet, il fautsignaler que le développement des sites web contribue à créer un troisième pôlede compétences dans l'entreprise. Le responsable correspondant est appeléwebmestre, et non "administrateur de site", parce que le posterequiert des compétences multidisciplinaires (et pas seulement informatiques).

6 - Les différents modèlesde bases de données

Les bases de données dumodèle "relationnel" sont les plus répandues (depuis le milieu desannées 80 environ), car elles conviennent bien à la majorité des besoinsdes entreprises. Nous verrons aux chapitres 5 et 6 en quoi ellesconsistent. Le SGBD qui gère une BDD relationnelle est appelé "SGBDrelationnel", ce qui est souvent abrégé en SGBDR.

D'autres modèles de bases dedonnées ont été proposés : hiérarchique, en réseau, orienté objet,relationnel objet. Aucun d'entre eux n'a pu détrôner le modèle relationnel, nise faire une place notable sur le marché (sauf le relationnel objet, prôné parOracle, qui connaît un certain développement). Malgré sa généralité, le modèlerelationnel ne convient pas à toutes les BDD rencontrées en pratique. Il existedonc des SGBD spécialisés. Les deux exemples les plus connus concernent lagestion des BDD bibliographiques (ou documentaires), et celle des BDDgéographiques gérées à l'aide d'un SIG (Système d'Information Géographique).

7 - Le support pratique ducours

Pour étudier lefonctionnement des bases de données, ce cours s'appuie sur le SGBD"Access", qui fait partie de la suite bureautique "Office"(version professionnelle) de l'éditeur Microsoft. Access est un SGBD de milieude gamme, commercialisé à un coût très abordable, et conçu pour être utilisableà la fois par le développeur professionnel et l'utilisateur courant demicro-ordinateur (d'où son abord convivial).

La version 1 d'Access a étélancée au COMDEX, à l'automne de 1992. La version 2 date de 1994. Les versionssuivantes portent le nom de l'année : 95, 97, 2000 (publiée en 1999) et2002 (publiée en 2001). La version 2002 possède les caractéristiquessuivantes :

• elle peut contenir 2 Go de données au maximum. Ce chiffre est quelque peu trompeur car, en cours d'exploitation, la taille de la base de données grossit : de nouvelles données sont générées, d'autres ne sont plus utilisées, etc. Le SGBD ne récupère la place perdue que si on lui en donne l'ordre (compactage). Or la limite de 2 Go concerne la taille de la BDD avant compactage. La limite pratique (la taille après compactage) peut donc être notablement inférieure à 2 Go ;
• elle peut servir 5 à 10 utilisateurs simultanément ;
• chaque table peut comporter 2 millions d'enregistrements au maximum.

 

Si on installe Access sur unserveur de fichiers d'entrée de gamme (qui ne coûte pas plus cher qu'un PCmusclé, mais dont les accès disques sont nettement plus rapides), on peutatteindre des volumes de données convenant à une petite PME. Au delà, il faututiliser un SGBD gérant de plus gros volumes de données, et l'installer sur unserveur plus puissant.

En fait, dans l'entreprise,c'est surtout la limitation à 5-10 utilisateurs qui restreint les usagesd'Access à des opérations de taille modestes. Mais cela ne veut pas dire qu'unlogiciel bureautique comme Access ne soit utile que dans les petites PME. Il asa place un peu partout, et c'est la raison pour laquelle il se répand aussivite :

• d'abord, toutes les données manipulées dans une entreprise ne vont pas s'entasser dans une BDD unique, et il y a place pour des SGBD de puissances diverses ;
• ensuite, lorsqu'on recherche de l'information dans une grande base, une première interrogation permet généralement d'isoler la petite fraction des données auxquelles on s'intéresse. Lorsque c'est possible, on a intérêt à transférer la suite des opérations dans un tableur comme Excel ou un SGBD comme Access, qui sont plus conviviaux et où il est plus facile de manipuler les données. Ce transfert n'est facilement praticable que si les deux conditions suivantes sont remplies : le "gros" SGBD fonctionne sous Windows, et il permet d'exporter les résultats d'une requête vers la suite Office ;
• enfin, même si l'on planifie la création d'une grosse BDD consultée par de nombreux utilisateurs, il peut être utile de créer d'abord une maquette dans Access où, grâce à l'interface graphique, les développements sont beaucoup plus rapides. Lorsque la maquette fonctionne correctement, on effectue une migration vers un SGBD plus puissant tel que SQL Server ou DB2. Les SGBD qui fonctionnent sous Unix, et/ou ceux qui ne connaissent que leur format propriétaire, n'offrent pas cette possibilité.

Chapitre 2 : Les tables

1 - Introduction

Nous avons vu au chapitreprécédent que, dans les BDD, les données sont stockées dans des tables. Nousaborderons donc l'étude des BDD par celle de la création et de la manipulationdes tables. Dans les bases de données, la table est le premier objet par ordred'importance décroissante. Pas de table, pas de données !

Pour étudier les tables, cecours utilise le SGBD Access comme support pratique.

2 - La création d'unetable

La première opérationconsiste à créer d'abord une base de données vide. Le logiciel réclame un nomde fichier et un seul, car toutes les informations relatives à la BDD serontstockées dans le même fichier. Ce dernier comporte l'extension".mdb", et sa taille initiale est voisine de 96-100 ko).

La fenêtre relative à la basede données apparaît. Dans la colonne de gauche figurent les "objets"de la base de données. Un mot sur ces objets, qui sont utilisés :

·         lestables, pour stocker les données ;

·         lesrequêtes, pour retrouver les données ;

·         lesformulaires, pour saisir les données ou les visualiser à l'écran ;

·         lesétats, pour imprimer les données ;

·         lespages, pour communiquer avec la BDD via un navigateur (Internet Exploreruniquement) ;

·         lesmacros, pour automatiser des opérations répétitives effectuées sur labase ;

·         lesmodules, pour rajouter des fonctionnalités grâce à de la programmation en VBA(Visual Basic for Applications).

 Sélectionner l'objet table, s'il ne l'est pasdéjà par défaut. Trois méthodes sont proposées pour créer une nouvelletable :

·         créerune table en mode création. C'est la méthode générale, la seule que nousrecommandions ;

·         créerune table à l'aide de l'assistant. Ce dernier vous offre un certain nombre detables toutes prêtes dont vous pouvez vous inspirer. Cependant, rien neremplace une bonne analyse du problème de stockage des données, suivie d'uneréalisation personnalisée et adaptée ;

·         créerune table en entrant des données. Une table toute prête vous est proposée, danslaquelle vous pouvez immédiatement saisir des données, le logiciel se chargeantde déterminer leur type et leur format. Cette façon de procéder est déplorable,et nous la déconseillons absolument, sauf pour des essais sans suite.

En mode création, une fenêtres'ouvre qui permet de définir la table champ par champ, en précisant le nom duchamp et le type de données qu'il contient.

3 - Les types de données

Tous les SGBD offrent lapossibilité de stocker du texte, de l'information numérique, et des dates (avecou sans les heures). Le type "monétaire" est un cas particulierd'information numérique, et le lien hypertexte un cas particulier de texte.Lorsque l'on utilise Access, une liste déroulante propose les types de donnéessuivants :

·         texte(type par défaut)

·         mémo(texte contenant plus de 255 caractères)

·         numérique

·         date/heure

·         monétaire(cas particulier du numérique)

·         numéroauto: numérotation automatique, séquentielle ou aléatoire

·         oui/non,c'est à dire booléen (deux valeurs possibles seulement)

·         objetOLE : pour le stockage des données numériques

·         autresque le texte, les nombres les dates

·         lienhypertexte : cas particulier du type texte

Le tableau ci-dessous préciseles propriétés de ces différents types. Il est nécessaire, à ce stade, d'effectuerles bons choix. Si l'on modifie ultérieurement le type de données d'un champ,alors que la table contient déjà des informations, ces dernières risquentd'être tronquées ou perdues.

Pour sauvegarder votretravail, cliquez sur l'icône "Enregistrer" dans la barred'outils. Lorsque tous les champs sont définis, fermez la fenêtre, en répondant"non" à la question relative à la clé primaire, et en donnant un nomà la table. Ce nom apparaît désormais dans la fenêtre relative à la base dedonnées. Nous verrons, au chapitre 4, à quoi set la clé primaire. Pourmodifier une table, il faut la sélectionner (dans la fenêtre base de données),puis cliquer sur l'icône "Modifier". La fenêtre s'ouvre enmode création comme précédemment. Pour supprimer une table, il faut lasélectionner et utiliser la fonction "supprimer" (clic droit) ou latouche du même nom.

4 - Les propriétés deschamps

La partie inférieure de lafenêtre du mode création est intitulée "Propriétés du champ". Cespropriétés se trouvent rassemblées dans l'onglet "Général". La listedes propriétés d'un champ dépend du type de données choisi, mais une propriétédonnée peut apparaître pour des types de données différents. Exemple : lapropriété "Taille du champ" apparaît pour les types de données"Texte", "Numérique"et "NuméroAuto". Lesprincipales propriétés sont :

·         Tailledu champ ;

·         Format: définit la manière dont les informations s'affichent. Exemple : leformat monétaire affiche deux chiffres après la virgule, puis un espace et lesymbole de l'euro ;

·         Masquede saisie : guide la saisie des données et exerce un contrôle. Exemple :un code postal français est composé de cinq chiffres ;

·         Légende: définit le nom de l'étiquette dans le formulaire associé à la table. Il estpréférable d'implémenter cette propriété au niveau du formulairelui-même ;

·         Valeurpar défaut : valeur qui s'affiche dans le champ avant saisie parl'utilisateur ;

·         Validesi : condition de validité du champ. Exemple : une notation sur 20 doitêtre comprise entre 0 et 20 ;

·         Messagesi erreur : ce message s'affiche si la condition de validité précédente n'estpas satisfaite ;

·         Nullinterdit : le champ correspondant ne peut rester vide lors de la saisie d'unenregistrement ;

·         Chaînevide autorisée : le champ peut contenir une chaîne ne comportant aucuncaractère ;

·         Indexé: un fichier index est associé au champ de telle sorte que les recherchesd'information s'effectuent plus rapidement. Le chapitre 3 explique cequ'est un index, et comment on le crée ;

·         Compressionunicode : un octet suffit pour saisir un caractère (pour les alphabets utilisésdans l'Europe de l'ouest et dans le monde anglophone).

Remarque 1 : la propriété"mode IME" concerne l'usage d'Access en japonais, et ne nousintéresse donc pas ici.

Pour faire fonctionnercorrectement certaines requêtes, il est important de bien comprendre ladifférence entre la valeur Null, une chaîne vide et une chaîne blanche. Unchamp d'un enregistrement :

·         possèdela valeur Null si aucune information n'a été introduite, ou si l'informationprésente a été supprimée ;

·         contientune chaîne vide si on a défini la valeur par défaut du champ à l'aide de deuxguillemets contigus (""), et si aucune information n'a été introduite(ou si l'information introduite a été supprimée) ;

·         contientune chaîne "blanche", si un ou plusieurs espaces ont été introduitset n'ont pas été supprimés.

·         Ladéfinition de certaines propriétés des champs soulève des problèmes de syntaxe.La touche F1 fournit une aide contextuelle, c'est à dire liée à la position ducurseur.

5 - Saisir les données

 Pour introduire des données dans une table, ilfaut l'ouvrir en mode "feuille de données". Dans la fenêtre base dedonnées (l'objet table étant sélectionné),

on peut  :

·         faireun double clic sur le nom de la table ;

·         sélectionnerla table, et cliquer sur l'icône "Ouvrir" ;

·         faireun clic droit sur la table et sélectionner "Ouvrir" dans la listedéroulante.

Sur le plan pratique, pourpasser facilement du mode "création" au mode "feuille dedonnées" ou vice versa, il suffit de cliquer sur l'icône"affichage" représentée ci-contre. Cette icône est présente dès quel'on se trouve déjà dans l'un des deux modes précités.

On peut ainsi vérifier le bonfonctionnement des listes, formats, masques de saisie, etc. On notera que lecontrôle des informations se fait lors du passage à l'enregistrement suivant.Par exemple, si une liste est obligatoire, une information qui ne fait paspartie de la liste ne sera rejetée qu'au passage à la ligne suivante. Avecl'affichage d'un message qui, selon les bonnes traditions de l'informatique,risque fort d'être sibyllin...

6 - Conclusion

 Il est essentiel de bien réaliser que, dansles BDD, les tables se présentent sous un double aspect. C'est ainsi qu'il fautdistinguer :

·         l'aspectstructure : noms des champs, types de données, propriétés, listes -- en bref, toutce qui est défini dans le mode "création" de la table ;

·         l'aspectcontenu : les valeurs introduites dans les champs des divers enregistrements,en mode "feuille de données".

Nous rencontrerons aussi cedouble aspect à propos des requêtes. Lors des opérations d'import/export, lesystème nous demandera si seule la structure est transportée, ou si les donnéesdoivent suivre.

Chapitre 3 : Les index

1 - Introduction

Les bases de données prennentsouvent des proportions importantes, voire considérables. Si une recherched'information dans une table s'effectue de manière simplement séquentielle(c'est à dire en examinant toute la table, ou du moins tous les champsconcernés, du début jusqu'à la fin), le temps d'attente peut devenir prohibitifpour l'opérateur. L'index est l'outil qui permet de résoudre ce problème.

La notion d'index est trèsancienne. Elle semble remonter à la grande bibliothèque d'Alexandrie, célèbredans l'Antiquité, mais malheureusement détruite par un incendie lors de laconquête de l'Égypte par les arabes. Cette bibliothèque s'était dotée d'unindex par auteurs et d'un index par matières pour faciliter les recherches deses lecteurs parmi les nombreux ouvrages (en papyrus) qu'elle possédait.

Imaginons une bibliothèquedans laquelle les livres sont rangés n'importe comment -- au fur et à mesure deleur acquisition, par exemple. Pour rechercher un livre dont on connaît letitre, il faut parcourir rayons dans l'ordre (recherche séquentielle). Ou l'onfinit par trouver (ouf !), ou l'on arrive bredouille au dernier rayonnageet on en conclut que la bibliothèque ne possède pas l'ouvrage.

Bien entendu, personne nesera jamais assez sot pour organiser une bibliothèque de pareille façon. Leslivres seront rangés par ordre alphabétique du titre, par exemple. Si le titreque nous recherchons commence par un L, nous irons vers le rayon du milieuet nous examinerons un ouvrage. Si son titre commence par un P, nousconclurons que nous sommes allés trop loin. Nous reculerons quelque peu, etnous réitérerons notre démarche. Nous arriverons beaucoup plus vite queprécédemment à trouver le livre que nous recherchons, ou à conclure qu'il n'estpas dans la bibliothèque, parce que nous pratiquons une méthode dichotomique(quelque peu optimisée), qui nous permet d'arriver au résultat en n'examinantqu'une petite fraction des livres contenus dans la bibliothèque. Nous pouvonspratiquer une technique efficace de recherche parce que les livres constituentun ensemble ordonné. Dans un ensemble désordonné, on ne peut pratiquer qu'unerecherche séquentielle, beaucoup plus lente. Mais... comment ferons-nous sinous recherchons un livre dont nous connaissons l'auteur, mais pas letitre ? Les livres de la bibliothèque ne peuvent pas être triés à la foispar ordre alphabétique de leur titre, et celui de leur auteur. C'est iciqu'intervient la notion d'index. Pour chaque livre, nous créons une fiche surlaquelle nous inscrivons le nom de l'auteur et le titre du livre. Puis nousrangeons ces fiches par ordre alphabétique des noms d'auteur. Pour rechercherle livre d'un auteur donné, nous compulsons les fiches. Comme elles constituentun ensemble ordonné, l'opération est rapide ; ou nous obtenons le titre dulivre, ou nous concluons que le livre ne se trouve pas dans la bibliothèque.Dans le premier cas, nous nous rendons dans les rayons munis du titre, et commeles livres sont classés par ordre alphabétique de leur titre, notre trouvonsrapidement l'ouvrage en question. Imaginons maintenant que la bibliothèque soitgérée par ordinateur. Si la table qui contient les livres est triée par ordrealphabétique des titres, il faut que nous construisions un index informatiquesur le champ auteur pour que la recherche d'un livre dont on connaît l'auteurs'effectue rapidement. Car l'ordinateur est programmé à l'image de ce que fontles humains : dans un ensemble non trié il recherche séquentiellement,alors que dans un ensemble trié il recherche par dichotomie. Dans le secondcas, il va beaucoup plus vite. Comme pour l'ensemble de ce cours, nousutilisons le SGBD Access comme support pratique.

2 - Le fonctionnement del'index

 Nous disposons maintenant d'un ordinateur pourgérer la bibliothèque. Au fur et à mesure que nous achetons des livres, nousles numérotons dans l'ordre. Puis nous saisissons dans la table d'une BDD lesdonnées qui les caractérisent (numéro, titre, auteur, éditeur, année d'édition,ISBN, etc.), et nous les rangeons sur les rayons dans l'ordre de leur numéro.La table se présente ainsi :

En informatique, un index estreprésenté par une table à une seule colonne, comme on le voit sur la figureci-dessous. Dans le premier index (index sur le titre), le premier titre parordre alphabétique correspond au livre n° 2 (Access), suivi du livren° 3 (Les écoles) et du livre n° 1 (Mon jardin). Les autres index s'interprètentde la même façon.

L'index présente desavantages :

• il accélère les recherches d'information. En effet, l'index est une représentation de la table, triée sur un champ donné. On peut donc lui appliquer les méthodes connues de recherche rapide sur un ensemble ordonné (c'est le SGBD qui se charge de l'opération, laquelle est transparente pour l'opérateur) ;
• il est de taille très inférieure à celle de la table : on peut le remettre à jour en temps réel à chaque modification de cette dernière ;
• il peut servir à empêcher l'opérateur de créer des enregistrements dupliquées en saisissant deux fois, par erreur, les mêmes données. Nous reviendrons sur ce point au paragraphe suivant.

L'index ne possède pas quedes avantages. Voici pour ses inconvénients :

• chaque fois que nous demandons au système de créer (et de maintenir) un index, nous augmentons sa charge de travail, et par conséquent nous le freinons. Ainsi, les opérations de saisie et de maintenance sont ralenties par la présence d'index, car ces derniers doivent être mis à jour immédiatement ;
• un index occupe de la place en mémoire sur le disque. En fait, ce dernier argument a beaucoup perdu de sa valeur avec le temps, parce que la mémoire de masse des ordinateurs ne cesse de croître rapidement, et qu'elle est devenue si bon marché (son coût à l'octet est divisé par deux tous les deux ans environ) qu'on la gaspille allégrement.

L'informatique permet decréer des index sur plusieurs champs. Imaginons que nous ayons séparé le nom etle prénom de l'auteur, par exemple. Un index sur les deux champs nom et prénomcorrespond en fait à l'index créé sur un champ unique dans lequel nous aurionsconcaténé le nom et le prénom.

3 - Les doublons

 On appelle "doublon" une informationqui apparaît au moins deux fois dans une table. La notion de doublonss'applique à une colonne donnée, ou à plusieurs colonnes, ou à la totalité descolonnes d'une même table (figure ci-dessous). Dans ce dernier cas, nous avonsaffaire à deux enregistrements (ou plus) identiques, une situation qu'il fauttoujours considérer comme anormale.

Dans une BDD, lesenregistrements dupliqués peuvent provenir de deux sources :

• les erreurs de saisie. Le taux des erreurs humaines est de l'ordre de un à quelques pourcents. Il est inévitable que, de temps en temps, un opérateur tente d'introduire dans une BDD des informations qui s'y trouvent déjà. Il est normal de confier au SGBD le soin de l'en empêcher ;
• la manipulation des informations contenues dans la base. Considérons par exemple la table qui illustre ci-dessus le cas du doublon sur deux colonnes. Si, pour une raison quelconque, nous supprimons la troisième colonne, nous transformons ce doublon sur deux colonnes en un enregistrement dupliqué, dont la présence peut être souhaitée (comptage), inutile ou nuisible suivant les cas.

Lorsque nous introduisons del'information dans une table pourvue d'un index, le SGBD met ce dernier à jouren temps réel. Au cours de cette opération, il peut détecter facilement sicette nouvelle information constitue un doublon sur les champs concernés. Ilest donc aisé de doter le SGBD d'une fonction permettant, si on le désire,d'empêcher la validation de la saisie d'un enregistrement constituant undoublon. Nous reviendrons, dans les chapitres relatifs aux requêtes, sur leproblème de la création de doublons indésirables lors de la manipulation desinformations d'une BDD.

4 - L'indexation d'un champ

Dans le chapitre consacré auxtables, nous avons rencontré la "Propriété du champ" intitulée"Indexé", pour tous les types de données sauf "Objet OLE".Quand nous cliquons dans la zone de texte correspondante, une liste déroulantenous est proposée, qui contient les trois choix suivants :

• Non
• Oui - Avec doublons
• Oui - Sans doublons

Pour créer un index sur lechamp correspondant, il suffit de répondre "Oui", avec ou sansdoublon selon le cas. Si nous conservons la valeur "Non" par défaut,aucun index ne sera créé. Il est inutile de ralentir le fonctionnement dusystème lors de la saisie des données, si cela ne nous fait pas gagner du tempsultérieurement. Il est donc préférable de répondre "Non" dans les cassuivants :

• la table considérée contient peu d'enregistrements ;
• nous effectuons rarement (voire jamais) de recherche dans ce champ ;
• nous ne trions jamais la table sur ce champ ;
• il est normal que le champ contienne des doublons. Ainsi, il est totalement inutile d'indexer un champ booléen, bien que ce soit techniquement possible.

Dans les cas contraires, lacréation d'un index présente de l'intérêt. Se pose alors le problème de savoirsi nous admettons ou non les doublons :

• si les doublons ne posent pas de problème (ex : homonymie), nous choisirons l'option "Avec doublons". Ceci dit, indexer dans le seul but de rendre les recherches plus rapides, sans chercher à empêcher les fautes de saisie, peut constituer dans certains cas une erreur ;
• dans le cas général, nous choisirons l'option "Sans doublons", ce qui aura pour effet de nous empêcher de créer des doublons par mégarde. Le système refusera de valider l'enregistrement fautif (lors du passage à la ligne suivante, ou lors de la fermeture de la table).

Rappelons pour mémoire qu'unchamp doté d'une clé est toujours indexé sans doublons. Or une table ne peutcontenir qu'une seule clé, alors qu'elle peut être dotée de plusieurs index. Ilfaut donc réserver la clé pour la réalisation des relations, et ne pasl'utiliser comme index.

5 - La création d'un indexmulti-champ

Dans une table contenant desdonnées relatives à plusieurs milliers de personnes, le risque d'homonymiedevient important. A fortiori dans une plus grande table, celle représentantl'annuaire téléphonique d'une grande ville par exemple. Pour accepterl'homonymie tout en rejetant les doublons dus à des erreurs de saisie, onutilise un index basé sur plusieurs champs. Si la probabilité de trouver deuxfois "Dupont" est importante, celle de trouver deux fois "DupontJean" est déjà nettement plus faible, et celle de trouver deux fois"Dupont Jean né le 12/06/1978" est pratiquement nulle. En créant unindex sur deux champs (Nom + Prénom) ou sur trois champs (Nom + Prénom + Datede naissance), on peut rejeter les doublons dus à des erreurs de saisie tout entolérant parfaitement l'homonymie. On notera que le SGBD Access permet degrouper dix champs au maximum dans un index multi-champ, mais qu'on ne dépassepratiquement jamais la valeur trois. Pour créer un index multi champ, il fautse trouver en mode création de la table, et cliquer sur l'icône  "Index". Une fenêtre s'ouvre, etl'on procède aux opérations suivantes :

• dans la colonne de gauche, on donne un nom à l'index multi-champ ;
• dans la colonne médiane, on écrit les uns sous les autres les noms des champs constitutifs de l'index ;
• dans la colonne de droite, on précise l'ordre de tri. Par défaut, on conserve "Croissant" ;
• on clique sur le nom de l'index puis, dans la moitié inférieure de la boite, intitulée "Propriétés de l'index", on fixe à "Oui" la propriété "Unique" si l'on désire interdire les doublons.

La figure ci-dessousreprésente l'état de la boite de dialogue en fin de saisie, dans le cas simpleoù seulement deux champs ("Nom" et "Prénom") sontconcernés.

Plusieurs index multi-champpeuvent coexister dans une même table. Ils peuvent également cohabiter avec uneclé. Tout ce petit monde se retrouve listé dans la fenêtre de définition desindex. L'index relatif à une clé se repère à l'icône correspondante dans lacolonne (grisée) la plus à gauche, à son nom "PrimaryKey", et par lefait que la propriété "Primaire" affiche "Oui", comme lemontre la figure ci-dessous.

On notera pour terminer que,si un champ fait partie d'un index multi-champ, sa propriété "Indexé"vaut "Non". C'est normal, il ne faut rien y changer.

6 - Conclusion

Les index jouent un rôlediscret mais important dans la gestion des BDD. La décision de créer un indexrésulte de l'examen des avantages et inconvénients de cette opération. L'indexralentit les saisies et consomme un peu de place, mais il rend les tris et lesrecherches d'information plus rapides. Bien entendu, il est inutile de créer unindex sur quelque champ que ce soit dans une table qui renferme très peud'enregistrements. L'index joue un rôle important dans l'élimination desdoublons résultant d'erreurs de saisie.

Chapitre 4 : Les listes de choix

1 - Introduction

 Considérons l'exemple d'une table (que nousappellerons "Personnes") constituée comme le montre l'exempleci-dessous.

Il est tout à fait fastidieuxde saisir de nombreuses fois la même information, telle que celle du titre(Mme, Melle, M.). En outre, si la liste est assez longue, le même nom decommune sera saisi à plusieurs reprises -- avec le risque d'une faute defrappe, suivie d'une erreur si l'on effectue dans la table des recherchesbasées sur le nom de la commune. Enfin, on n'est pas à l'abri d'une erreur desaisie conduisant à associer à une commune un code postal erroné. Pour éviterde saisir plusieurs fois le titre ou le même nom de commune, nous pouvonsl'enregistrer dans une table séparée, et travailler ensuite par copier/coller.C'est encore mieux s'il nous suffit d'indiquer au système où se trouve l'informationcorrespondante pour l'enregistrement que nous sommes en train de renseigner.Pour ce faire, certains SGBD sont dotés d'un outil appelé liste de choix (ouplus simplement liste), que nous allons maintenant examiner. Comme dans lechapitre précédent de ce cours, nous utiliserons le SGBD Access comme supportpratique.

2 - La liste simple (listeinterne)

 Dans un premier temps, nous créons une table"Personnes" contenant seulement les trois champs "Nom","Prénom" et "Titre", possédant tous le type de donnéestexte. Nous allons faire en sorte de faire écrire le titre par le système lorsdu remplissage de la table.

• nous choisissons l'option "Je taperai les valeurs souhaitées". Il ne serait pas raisonnable, en effet, de créer une table pour y introduire seulement trois abréviations ;
• nous conservons le nombre de colonnes égal à 1. Nous saisissons les trois valeurs (M., Mme, Melle) les unes sous les autres dans la colonne intitulée "Col1" (utiliser la tabulation ou les flèches pour passer d'une valeur à l'autre). Enfin, nous réglons la largeur de la colonne, en la saisissant par le haut de son bord droit ;
• nous laissons le choix au système du nom de la liste (l'étiquette), et l'opération est terminée.

Dans la fenêtre de définitionde la table, aux "Propriétés du champ", onglet "Liste dechoix", nous trouvons les informations représentées sur la figuresuivante :

Commentons ces propriétés :

• Afficher le contrôle : Zone de liste déroulante. Une liste non déroulante conviendrait tout aussi bien, puisque la liste est fort courte, et le système ne nous proposera pas de barre de défilement. Mais attention : choisir "zone de texte" conduit à supprimer la liste, et il faudra la recréer ;
• Origine source : Liste valeurs. Pour nous rappeler que nous avons saisi la liste directement dans l'assistant ;
• Contenu : "M.";"Mme";"Melle". Les trois termes saisis sont rassemblés ici, séparés par des points-virgules, et mis entre guillemets pour rappeler qu'il s'agit de chaînes de caractères ;
• Colonne liée : 1. La colonne liée (ici la première colonne) est celle qui contient l'information que le système copiera / collera pour nous ;
• Nbre colonnes : 1. Nous n'avons demandé qu'une seule colonne ;
• En-têtes colonnes : Non. Sans objet pour nous ;
• Largeurs colonnes : 1 cm (par exemple). C'est la valeur que nous avons fixée dans l'assistant ;
• Lignes affichées : 8. C'est la valeur par défaut, mais le système limitera aux seules trois lignes utiles ;
• Largeur liste : 1 cm. C'est la largeur de l'unique colonne. La valeur "auto" convient également ;
• Limiter à liste : Non. C'est la valeur proposée par défaut. Nous reviendrons sur ce choix au paragraphe suivant.

Enregistrons et passons enmode "feuille de données" pour introduire du contenu dans la table.Quand nous cliquons dans le champ "Titre", l'icône de la liste apparaît. Si nous cliquonsdessus, la liste que nous avons saisie nous est proposée telle quelle par lesystème pour remplir le champ "Titre". Il suffit que nous cliquionssur la valeur désirée pour que le système l'inscrive à notre place, comme lemontre la figure ci-dessous.

3 - La liste obligatoire

 En saisissant des données dans la table"Personnes", nous constatons que nous pouvons introduire la chaîne denotre choix dans la colonne titre. Or il serait plus judicieux que nous soyonslimités aux seules trois valeurs qui ont un sens. Pour ce faire, il nous fautrevenir en "mode création". Dans la ligne du champ "titre",nous cliquons dans la colonne "type de données". Dans l'onglet"liste de choix", nous réglons à "oui" la propriété"Limiter à liste". Puis nous revenons au"mode feuille dedonnées". Nous constatons alors que le système nous permet toujours desaisir dans le champ "Titre" une information qui n'est pas dans laliste, mais il refuse de l'enregistrer lorsque nous passons à la lignesuivante. Notre liste de titres est effectivement devenue obligatoire, lecontrôle s'effectuant lors du passage à l'enregistrement suivant, ou lors de lafermeture de la table. Rendre une liste obligatoire est une décision qui doitêtre prise au coup par coup, en fonction des besoins. Il est souvent utile derendre une liste obligatoire, mais ce n'est pas une règle absolue.

Remarque : on ne peut rendrela liste obligatoire que si la propriété "Afficher le contrôle" est à"Zone de liste déroulante". Une "Zone de liste" ne lepermet pas... et seul l'éditeur Microsoft sait pourquoi.

4 - La liste issue d'unetable (liste externe)

Lorsque le nombre d'élémentsde la liste est important, et / ou s'il est susceptible d'êtrecomplété de temps en temps, il est plus judicieux de placer les éléments de laliste dans une table plutôt que de les saisir dans l'assistant. C'est le cas,par exemple, de la liste des communes. La liste peut de nouveau être mise enplace avec l'aide de l'assistant, mais il faut au préalable créer la tablecorrespondante. Nous l'appelons "Communes", nous lui attribuons unseul champ (intitulé "commune"), doté du type de données "texte".Passons en mode "feuille de données" et introduisons quelques noms decommunes dans la nouvelle table. Nous retournons à la table"Personnes" en mode création, et nous rajoutons le champ"Commune" en mode texte. Nous lançons l'assistant liste de choix et nousprocédons aux opérations suivantes :

• nous choisissons l'option "Je veux que la liste de choix recherche les valeurs dans une table ou requête" ;
• nous choisissons la table "Communes" ;
• nous sélectionnons son unique champ, intitulé "Commune" ;
• nous réglons la largeur de la future liste ;
• nous laissons le système régler tout seul son problème d'étiquette ;
• nous répondons "oui" à la demande d'enregistrement, et l'opération est terminée.

Les propriétés du champ"Commune" de la table "Personnes", apparaissent ainsi(onglet "liste de choix") :

Par comparaison avec la listesimple, nous remarquons les deux différences suivantes :

·         lapropriété "Origine source" contient maintenant"Table/Requête", ce qui est normal ;

·         lapropriété "Contenu" contient le code SQL "SELECTCommunes.commune FROM Communes; ", ce qui signifie en clair :"choisir le champ commune de la table Communes".

De retour dans la table"Personnes" en mode "feuille de données", nous constatonsque la liste contenue dans la table "Communes" nous est proposée, etque de plus elle est triée par ordre alphabétique (parce que la propriété"Indexé" est passée de "Non" à "Oui - Avec doublons").

Pour assurer la cohérenceentre la table principale "Personnes" et la table"Communes", nous avons tout intérêt à rendre la liste obligatoirecomme précédemment. Ceci nous astreint à renseigner le nom de commune dans latable "Communes" avant de saisir l'enregistrement correspondant dansla table "Personnes", ce qui n'est pas très commode. Nous verronsplus loin comment l'usage d'une sous-table permet de régler élégamment leproblème.

5 - La clé (clé primaire)

 Nous pouvons améliorer la fiabilité du systèmeprécédent en faisant en sorte que nous ne puissions pas saisir deux fois lemême nom dans la table Communes. Nous ouvrons cette dernière en mode"création", nous sélectionnons le champ "Commune", et nouscliquons sur l'icône qui représente une petite clé.

La clé (encore appelée"clé primaire") identifie de manière unique chaque enregistrement dela table. Le champ auquel on applique une clé acquière les propriétéssuivantes :

·         lesdoublons (deux informations identiques ou plus) sont désormais interdits par lesystème. La propriété "Indexé" passe automatiquement à "Oui -Sans doublons" ;

·         laprésence de la clé interdit la présence d'un champ vide dans un enregistrement.Bien que cela n'apparaisse pas dans les propriétés du champ (encore un petitbug !), la valeur "Null" est désormais bannie ;

·         lechamp auquel on applique une clé est automatiquement trié par ordre croissant.

Pour supprimer une clé, ilfaut sélectionner le champ et cliquer sur l'icône de la clé ; cette icône fonctionne commeun commutateur. Notons enfin qu'il ne peut y avoir qu'une seule clé par table.

6 - La sous-table

La présence de la clé a aussipour effet de faire apparaître la table "Personnes" comme sous-tablede la table "Communes". En effet, si nous ouvrons cette dernière,nous voyons que chaque ligne commence maintenant par un signe + (appelé"indicateur de développement" dans Access). Si nous cliquons sur cesigne (en face de la commune "Uriage", par exemple), la liste despersonnes de la table "Personnes" habitant Uriage apparaît (figureci-dessous), et nous pouvons la compléter. Si nous cliquons sur le signe - quise trouve maintenant en face de la commune "Uriage", la sous-tabledisparaît. On peut faire apparaître plusieurs sous-tables de la table"Personnes" dans la table "Communes" si on le désire.

L'existence de la sous-tablenous permet de remplir simultanément la table "Personnes" et la table"Communes" qui lui sert de liste, d'autant que la liste simple duchamp "Titre" fonctionne effectivement dans la sous-table. Comme onpeut le constater, les sous-tables sont fort commodes, et elles rendentsuperfétatoire l'usage des formulaires.

7 - L'usage d'un code

Nous désirons maintenantassocier le code postal au nom de la commune. Un problème naît immédiatement dufait que, en France du moins, plusieurs codes postaux peuvent être attribués àune même commune, lorsque cette dernière est une grande ville. A Paris, parexemple, chaque arrondissement possède son code postal, lequel varie donc de75001 à 75020. A Grenoble, bien qu'il n'y ait pas d'arrondissement, il existedeux codes postaux (38000 et 38001). Si nous rajoutons une colonne "Codepostal" à la table "Communes", nous serons amenés à écrire deuxfois Grenoble dans la colonne "Commune", ce que la présence de la cléinterdit.

La solution consiste àutiliser un code. Le code est une donnée qui identifie un enregistrement demanière univoque. En d'autres termes, on ne peut pas trouver dans une tabledeux lignes possédant le même code.

Nous rajoutons donc unecolonne "Code_com" à la table "Communes", et nousattribuons la clé à cette nouvelle colonne (ce qui assure l'unicité du code).Ainsi, les couples 38000-Grenoble et 38001-Grenoble seront dotés d'un codedifférent. De même, il y aura 20 codes pour Paris, un par arrondissement.

La gestion du code peut êtreconfiée au SGBD. Pour ce faire, il suffit d'utiliser le type de donnéesNuméroAuto (entier long) pour le champ "Code". Le système attribuerales codes dans l'ordre croissant, ou de manière aléatoire, comme nous ledésirons.

La première opérationconsiste à détruire la liste externe que nous avons créée au paragraphe 4.En mode création, nous modifions la propriété "Afficher le contrôle"(du champ "Commune" de la table "Personnes") de "Zonede liste déroulante" à "Zone de texte". Si nous repassons enmode "Feuille de données" (après avoir enregistré), nous constatonsqu'il n'y a plus de liste pour alimenter le champ "Commune". Maistout n'a pas disparu pour autant, car une liste externe implique une relation.Comme nous n'avons pas encore étudié les relations, nous ne savons pas commentles détruire. Nous allons donc procéder de la manière suivante :

·         noussélectionnons la table "Communes",  nous donnons l'ordre de lasupprimer, et nous confirmons ;

·         lesystème nous alerte : "Impossible d'effacer la table 'Communes' avant lasuppression de ses relations avec d'autres tables. Effacer les relationsmaintenant ?" ;

·         nousconfirmons et la table "Communes" disparaît avec les dernières tracesde la liste de choix.

Nous recréons maintenant latable "Communes", qui va désormais comporter trois colonnes :

·         lapremière colonne ("Code_com") est du type NuméroAuto. Elle est dotéede la clé ;

·         laseconde colonne ("Commune") est recréée à l'identique ;

·         latroisième colonne ("Code postal") est en mode texte(5 caractères), et non en numérique. En effet, certains codes postauxcommencent par un zéro (exemple : 01000 pour l'Ain), et le mode numériquesupprimerait le premier zéro.

Nous revenons dans le champ"Commune" de la table "Personnes", et nous relançonsl'assistant "Liste de choix". Nous procédons aux opérationssuivantes :

·         nouschoisissons "Je veux que la liste de choix recherche les valeurs dans unetable ou requête" de manière à créer une liste externe ;

·         nouschoisissons la table "Communes" ;

·         noussélectionnons les deux champs "Code_com" et"Commune" ;

·         nousconservons la coche "Colonne clé cachée" et nous réglons la largeurde la liste ;

·         nouslaissons le système donner un nom (étiquette) à la liste, nous cliquons sur"Terminer", et nous enregistrons.

Si nous regardons ce que sontdevenues les propriétés du champ "Commune" de la table"Personnes", nous constatons de sérieux changements :

·         lechamp "Commune" est devenu numérique, avec comme taille de champ"Entier long" ;

·         lapropriété "Valeur par défaut" vaut zéro. Nous supprimons celaimmédiatement, pour des raisons d'esthétique uniquement ;

·         lecode SQL de la propriété "Contenu" a changé, ce qui est normal (laliste implique désormais deux colonnes au lieu d'une) ;

·         dansla propriété "Largeurs colonnes", nous constatons que la premièrecolonne de la liste possède une largeur nulle.

Mais nous ne sommes pas aubout de nos surprises : si nous utilisons la liste de choix ainsi crééedans la table "Personnes", nous constatons que le système propose etécrit le nom de la commune et non son code (figure ci-dessous). Des noms decommune (c'est à dire du texte) dans un champ numérique, c'est unecatastrophe ! Pas de panique : le champ est, et reste, numérique.Simplement, le SGBD a affiché la traduction du code en texte, pour nousfaciliter la lecture de la table.

Que le code soit cachérésulte directement du fait que la largeur de sa colonne soit déclarée nulledans les propriétés de la liste de choix. Il suffit que nous donnions unevaleur non nulle à cette largeur pour que le code remplace le nom de la commune(faire l'expérience). L'opération est réversible, et nous retrouvonsl'affichage de la figure ci-dessus si nous donnons de nouveau une valeur nulleà la première colonne de la liste.

Si le code"Code_com" est caché dans la table "Personnes", pourquoi nepas en faire autant dans la table "Communes" ? Il suffit de tireravec la souris sur le bord droit de la colonne "Code_com" jusqu'à ceque cette dernière disparaisse. La table "Communes" se présente alors(avec la sous-table "Personnes") comme le montre la figureci-dessous. Désormais, le SGBD gère les codes, mais nous ne les voyons pas.Aucun regret !

En cas de besoin (ou si nousaimons les codes...), nous pouvons faire réapparaître le champ"Code_com". Affichons la table "Communes" en mode feuillede données et, dans le menu, utilisons "Format > Afficher lescolonnes...". La boite de dialogue "Afficher les colonnes"s'ouvre ; cochons "Code_com", et refermons.

8 - Compléments

Comme nous pouvons le constatersur la figure ci-dessus, la champ "Commune" n'est pas trié par ordrealphabétique, ce qui n'est pas pratique du tout. Nous pouvons le trier en lesélectionnant, puis en cliquant sur l'icône "Tri croissant".

Par contre, nous pouvonsfaire en sorte que, dans la table "Personnes", la liste des communesapparaisse automatiquement triée par ordre alphabétique. Nous ouvrons la table"Personnes" en mode modification, nous cliquons sur le champ"Commune" puis sur l'onglet "Liste de choix", et nousmodifions comme suit le code SQL de la propriété "Contenu" :

SELECT Communes.Code_com, Communes.Commune FROMCommunes ORDER BY Communes.Commune;

Nous aurions pu obtenir lemême résultat en cliquant sur le code, puis sur le bouton . Une fenêtre intitulée "InstructionSQL : Générateur de requête" s'ouvre. Nous pouvons alors demander untri croissant dans la colonne "Commune". Nous apprendrons l'usage dece type de fenêtre lorsque nous étudierons les requêtes.

Notons que l'on peutremplacer le code SQL de la propriété "Contenu" par le nom de latable contenant la liste. C'est souvent ce que pratiquent ceux qui n'utilisentpas l'assistant pour créer leurs listes. Mais on ne peut plus demander que laliste apparaisse automatiquement triée par ordre alphabétique. Évidemment, onpeut toujours la trier via l'icône "Tri croissant". En pratique, ilest fortement conseillé de toujours utiliser l'assistant pour créer une listede choix.

Arrivés à ce stade, vous souhaiteriezsans doute que le code postal s'inscrive automatiquement dans la table"Personnes", dès lors que le choix de la commune a été effectué. Ditcomme tel, c'est impossible. Mais il existe deux solutions pour rassembler lesinformations réparties dans les deux tables "Personnes" et"Communes" :

• créer une vue via une requête portant sur les deux tables ;
• créer un formulaire basé sur les deux tables.

Ces deux opérations serontétudiées dans les chapitres suivants de ce cours

9 - Conclusion

Il existe deux façons deréaliser une liste de choix : en saisissant immédiatement les valeurs(liste interne), ou en les introduisant dans une table auxiliaire (listeexterne). La première façon est recommandée lorsque la liste est courte, et peususceptible de changer. La seconde façon est recommandée lorsque la liste estlongue, et / ou susceptible d'être souvent modifiée ou complétée.Quelle que soit la manière utilisée pour réaliser une liste de choix, l'usagede l'assistant "liste de choix" est fortement recommandé.

Rappelons que le codageconstitue une solution pour régler un problème d'homonymie. Si un tel problèmen'est pas susceptible de se poser, le codage constitue une complicationinutile. Si l'usage de codes s'avère indispensable, on peut toujours faire en sortede ne pas les voir, alors que le SGBD continue à les gérer.

Nous reviendrons, au chapitre9, sur l'usage des listes, et sur les rapports complexes qui existent entreliste et relation.

Chapitre 5 : le mécanisme relationnel

 

1 - Introduction

Les entreprises sont nées etse sont développées bien avant que l'informatique n'apparaisse. De ce fait, laplupart des logiciels ont été créés pour informatiser des opérations que l'oneffectuait auparavant manuellement. Au fur et à mesure que les ordinateurs acquéraientde la puissance, et que leur coût baissait, le nombre des applicationsinformatisables ne cessait d'augmenter. Les SGBD n'ont pas fait exception à larègle, même si on l'a passablement oublié aujourd'hui.

Tout ce que les anciensgestionnaires d'entreprise avaient inventé -- l'usage des codes, des index etdes opérateurs logiques, le fractionnement des informations et leur répartitiondans des "fichiers" multiples mais reliés, etc. -- a été repris (leplus astucieusement possible) pour créer les SGBD relationnels. Il faut bienreconnaître que, lors de l'informatisation des données de l'entreprise, on abeaucoup adapté, beaucoup formalisé, mais peu inventé. Cela devrait incitermessieurs les informaticiens à un peu plus de modestie.

Il est de bon ton, dansl'enseignement des BDD, d'oublier tout le passé. Pire, on présente souvent leschoses sous un aspect aussi abstrait que possible -- la théorie des ensembles,vous connaissez ? -- et en usant d'un vocabulaire ésotérique à souhait.Nous nous donnons donc pour but, dans ce chapitre, de montrer que lefonctionnement des bases de données relationnelles est fondé sur des techniqueséprouvées, qu'il est possible d'exposer simplement, et qu'il relativementfacile de comprendre et d'assimiler.

2 - Les données redondantes

Réduire le plus possible lasaisie d'informations redondantes est l'un des gros problèmes auquel se sontheurtés les gestionnaires des données de l'entreprise, avant que l'informatiquene vienne à la rescousse. Expliquons-nous à l'aide d'un exemple.

Vous travaillez dans uneentreprise qui vend des matériaux de construction, et l'informatique n'existepas encore. Vos principaux clients sont des entrepreneurs du bâtiment et desentreprises de génie civil. Les clients les plus assidus viennent chercher desmatériaux au moins une fois par jour. Quand vous écrivez dans vos livres que lecamion de la "Société des Grands Travaux du Dauphiné et de laMatheysine" est venu charger 30 sacs de ciment, vous n'allez pas pourla centième fois depuis le début de l'année écrire laborieusement le nom et lescoordonnées de ce fidèle client. Vous serez même lassé d'écrire seulement sonnom, qui est beaucoup trop long ! Vous remplacerez ce nom par un code, quien constitue une représentation très abrégée. Vous pouvez utiliser un code àconsonance mnémotechnique (SGTDM par exemple), ou au contraire parfaitementarbitraire (530-Z, pourquoi pas).

Lorsque votre comptableexploitera le bon de livraison pour alimenter le compte du client ou pourgénérer une facture, il n'aura aucun mal à déterminer ce que désigne le codeSGTDM. S'il ne s'en souvient plus, un fichier "Clients" est là pourl'aider. Tous les clients y ont leur fiche, et ces fiches sont classées parordre alphabétique des codes. Chaque fiche contient tout ce qu'il faut pouridentifier le client : son nom, son adresse, son numéro de téléphone, lesremises consenties, etc. Toutes ces informations ont été saisies une fois etune seule, mais elles vont servir à de multiples reprises : à chaquefacturation, à chaque rappel (le client paye en retard), à chaque coup de fil,à chaque envoi de publicité ciblée, etc. Bien entendu, le fait que les fichessoient classées par ordre alphabétique permet de retrouver très vite la fiched'un code donné. Si les fiches se trouvaient dans le désordre, il faudrait enlire la moitié (en moyenne) pour retrouver la bonne.

Votre entreprise applique lamême technique pour gérer son stock de produits, la liste de ses fournisseurs,son personnel, etc.

Vous n'êtes pas surpris,aujourd'hui, de voir des codes partout autour de vous (avec des codes barres,pour en rendre la lecture automatique) : dans les grandes surfaces pourles produits de consommation courante, dans les pharmacies pour lesmédicaments, dans les catalogues de vente par correspondance, chez le garagistepour les pièces détachées, etc. De même que M. Jourdain faisait de la prosesans le savoir, vous baignez dans le relationnel sans vous en rendre compte.

3 - L'informatique arrive

Le temps passe, le coût de lamain d'oeuvre ne cesse d'augmenter, et les décideurs de l'entreprise font leurscomptes : informatiser la gestion des données courantes de l'entreprise vapermettre de faire des économies. D'ailleurs, les concurrents suivent le mêmechemin, pas question de rester à la traîne.

Les données que maniecouramment l'entreprise sont structurées. Pour chaque produit du stock, parexemple, on enregistre les mêmes séquences de données : code, nom, prixunitaire, code du fournisseur, état du stock, état minimal admissible, quantitéminimale par commande, etc. Ces données peuvent donc être rangées dans destables, dotées du nombre de colonnes requis. La première conséquence del'informatisation a été la dématérialisation des données : chaque fichierest devenu une table, chaque fiche un enregistrement. Au lieur de remplir lesfiches à la main, ou à la machine à écrire, on saisit désormais lesinformations au clavier. Les informations ne résident plus sur les fiches enbristol d'une boite appelée "fichier", mais sur le disque dur d'unordinateur.

Mais ce n'est pas tout. Legestionnaire du stock sait que le code du fournisseur relie la fiche produit aufichier "fournisseurs", mais il est doté d'intelligence, alors quel'ordinateur en est totalement dépourvu. Il faut donc imaginer un moyen pourfaire en sorte que l'ordinateur sache que la table des produits et celle desfournisseurs sont liées par une relation basée sur des codes, et qu'il la gère.Il faut donc construire une base de données relationnelle, c'est à dire capablede gérer les relations comme le faisait jusque-là instinctivement le personnelde l'entreprise.

4 - Traduire les relations

Pour comprendre commentfonctionne une relation, il suffit de jeter un coup d'oeil à l'exemplereprésenté ci-dessous. Désormais, les "fichiers" sont dématérialiséset transformés en tables. La table de gauche contient la liste des produitscommercialisés par l'entreprise. La table de droite contient la liste desfournisseurs. Considérons le premier produit, le ciment C-21 ; son codefournisseur vaut 3. Dans la table de droite, l'ordinateur lira que lefournisseur est la société "Ciments X", ainsi que toutes lesinformations qui la concernent (l'adresse, le téléphone, le fax, les conditionsconsenties, etc.). Sa recherche sera d'autant plus rapide que la table"Fournisseurs" sera triée dans l'ordre croissant des codes. Bref, leSGBD maniera les relations comme le ferait un être humain.

Pour rendre le système plussûr, nous allons demander à l'ordinateur de vérifier que, dans la table"Fournisseurs", nous n'avons pas attribué deux fois le même code(cela s'appellera "l'unicité de la clé"). Puis nous allons imposerque l'on ne puisse pas introduire un produit dans la table de gauche tant quele code fournisseur correspondant n'est pas défini dans la table de droite(cela fait partie de l'intégrité référentielle). Bref, l'ordinateur fera ce quefaisaient les employés qui manipulaient les données, mais il ira beaucoup plusvite, il fera plus de contrôles, et il se trompera beaucoup moins. Cela ne veutpas dire qu'il n'y a plus personne dans l'entreprise, car il faut bien quequelqu'un pilote l'ordinateur -- et reçoive le client !

Mais... tous les étudiantsdes écoles de commerce vous le diront : il faut, chaque fois que c'estpossible, avoir deux fournisseurs par produit. Cela évite les difficultésd'approvisionnement et le dérapage excessif des prix. Comment, dans le schémaci-dessus, traduire le fait que le ciment peut provenir soit desCiments X, soit de la société Truc ?

Créer une nouvelle colonne"Code four" dans la table de gauche ne servirait à rien, car rienn'indiquerait dans quel cas il faut utiliser le premier code, et quand il faututiliser le second. La bonne solution consiste à créer un nouveau code,"C-22" si vous voulez. Nul ne sera surpris que le ciment ait deuxcodes car, même s'il s'agit du même produit (un Portland courant, par exemple),l'emballage des sacs est différent. Les clients les plus pointilleux vousdiront même que le ciment C-22 et meilleur que le C-21. Après tout, ce n'estpas impossible, même si le ciment Portland a fait l'objet d'une norme.

Résumons-nous : la relationentre les deux tables nous permet de ne saisir qu'une seule fois lesinformations relatives à un fournisseur, même si ce dernier nous fournitplusieurs produits. Le mécanisme de la relation est basé sur l'usage de codes.Ce mécanisme fonctionne parce que, si un fournisseur nous livre plusieursproduits, chaque produit ne peut provenir que d'un seul fournisseur (pour qu'ilen soit bien ainsi, nous avons dû créer quelques codes supplémentaires). Eninformatique, on parle de "relation 1-n" ou de "relation un àplusieurs". Les SGBD gèrent les relations 1-n sans problème, comme lefaisaient les humains auparavant.

5 - Un cas difficile

Continuons l'informatisationde l'entreprise, et attaquons-nous aux bons de livraison. Pour chaque commandeexécutée, il faut noter le numéro de la commande, la date, le nom -- ou plutôtle code -- de l'entreprise, puis la liste des produits -- ou plutôt de leurscodes -- avec les quantités livrées. Sur papier, pas de difficulté : onpeut toujours faire tenir quelques lignes ou quelques dizaines de ligne sur unefeuille de papier A4. Mais, pour reporter le tout dans une table, nousrencontrons un sérieux problème, comme le montre la figure ci-dessous. Combiendevons-nous prévoir de colonnes pour le code du produit et la quantitécorrespondante ?

Si nous en prévoyonsbeaucoup, nous gaspillerons de la mémoire à tour de bras. Si nous en prévoyonspeu, nous serons obligés de faire plusieurs bons pour une même livraison, etnous dirons en hochant la tête : "C'est la faute del'ordinateur". Un mien collègue, en pareil cas, donnait à ses étudiants leconseil suivant : "Vous ne savez pas comment faire ? Créez unenouvelle table !". Comparé à de l'algèbre relationnelle, cela fait unpeu simpliste, mais... cela marche dans bien des cas, y compris celui qui nousoccupe actuellement.

La nouvelle table (dite tablede jonction) comportera trois colonnes :

·         lapremière colonne contiendra un code assurant le lien avec la table "Bonsde livraison". Ce code, qui doit désigner de manière unique chaque bon delivraison, sera tout simplement son numéro ;

·         laseconde colonne contiendra les codes des produits 

·         latroisième colonne contiendra les quantités livrées.

Nous écrirons autant delignes dans cette nouvelle table qu'il y avait de produits mentionnés surchaque bon de livraison, comme le montre la figure ci-dessous. Nous voyons danscette table que le bon de livraison n° 1225 comporte 30 sacs deciment (C-22), 2 palettes de pavés (P-5) et 5 regards de 40 cm(R-10), toutes informations tirées de la table "Produits", larelation étant assurée via le code produit. La table "Bons delivraison" nous montre (grâce à la relation assurée par le numéro de bon)que l'entreprise livrée est notre bon client SGTDM. Les informations leconcernant se trouvent dans la table "Clients", la relation étantassurée par le code client.

Que s'est-il passé dans lecas des bons de livraison ? Nous nous sommes trouvés devant une relationplus complexe que précédemment. Un même bon de livraison peut mentionnerplusieurs produits, et un même produit apparaît dans de nombreux bons delivraison (sinon, c'est un produit qui ne se vend pas, et il fautl'abandonner). Nous avons affaire à une relation n-n (ou plusieurs àplusieurs), difficile à gérer par des moyens informatiques, alors qu'il n'y apas de problème avec les moyens manuels.

Heureusement pour nous, lacréation d'une table de jonction, puisant ses codes dans deux autres tables("Bons de livraison" d'un côté, "Produits" de l'autre), nousa tirés d'affaire. Les informaticiens vous confirmerons qu'une relation n-npeut toujours être scindée en deux relations 1-n par création d'une tablesupplémentaire, laquelle est parfois appelée "table de jonction". Lesmathématiciens pourront même vous en faire la démonstration rigoureuse, si vousappréciez l'abstraction.

6 - Conclusion

Nous avons tenté de montrer,à l'aide d'exemples concrets, que les bases de données relationnelles tirentleur origine dans la manière dont les entreprises géraient leurs données avantla grande vague d'informatisation de ces 20 dernières années. Les tables etleurs enregistrements sont issus des fichiers et de leurs fiches. Les clés etles relations proviennent directement de l'usage des codes. Le problème de larelation n-n, par contre, est spécifique de l'introduction de l'informatique,mais la création d'une table de jonction résout le problème sans grandedifficulté.

Les premiers SGBD mis sur lemarché ne géraient pas les relations, et les entreprises les trouvaient fortmalcommodes. Le succès des SGBD relationnels provient du fait qu'ils répondentbien aux besoins des entreprises -- parce qu'ils ont été conçus pour cela. Iln'y a ni mystère, ni mathématiques ensemblistes, cachés là-dessous.

Nous espérons que les informationscontenues dans ce chapitre vous faciliteront l'étude du chapitre suivant,consacré à l'établissement du schéma relationnel. Si vous pensez que nous avonsmanqué notre but, et si vous avez des suggestions à nous faire pour améliorerce texte, n'hésitez pas à envoyer un courrier électronique au CERIG.Merci !

Chapitre 6 : le schéma relationnel de la base

1 - Introduction

Nous avons vu au chapitreprécédent que l'informatisation des données courantes de l'entreprise nécessitela création de plusieurs tables, reliées entre elles via des codes. Pour que lesystème fonctionne, il faut que les relations entre tables soient du type 1-n.Si on rencontre une relation de type n-n, on peut toujours la scinder en deux relationsde type 1-n par création d'une table supplémentaire, dite table de jonction.

On attribue généralement lapaternité des premiers travaux consacrés aux BDD relationnelles à un chercheurde la compagnie IBM nommé Ted Codd. En 1970, il publia un article sur les basesde données relationnelles, au contenu très mathématique. Les méchantes languesvous diront que tous ceux qui publient sur le sujet des BDD citent cet article,mais que fort peu l'ont lu (l'auteur de ces lignes est dans ce cas).

En termes savants, Coddvoulait assurer l'indépendance entre l'organisation logique des données et latechnique informatique utilisée pour les stocker. En termes simples, ilcherchait une méthode permettant de stocker des données (structurées) de toutenature, sans recourir chaque fois à de la programmation spécifique. Ted Coddest considéré comme le créateur de l'algèbre relationnelle (l'aspect théoriquedes bases de données), qui utilise la théorie des ensembles.

En 1976, P. Chen proposa lemodèle entité-relation. Depuis, ce modèle est presque universellement utilisépour établir le schéma relationnel des BDD.

Au cours des années 70,des laboratoires universitaires et des entreprises travaillèrent à mettre aupoint les bases de données relationnelles. A la fin des années 70,plusieurs produits arrivèrent sur le marché. A cette époque, lesmicro-ordinateurs étaient encore dans l'enfance, et les premiers SGBDrelationnels furent implantés sur des mini-ordinateurs ou des mainframes.Progressivement, les SGBD relationnels reléguèrent aux oubliettes les SGBDhiérarchiques qui les avaient précédés. C'est également à cette époquequ'apparut le SQL, le langage de manipulation des BDD relationnelles.

Une dizaine d'années plustard, les micros avaient acquis assez de puissance pour accueillir les SGBDrelationnels. C'est alors que Microsoft introduisit la première versiond'Access sur le marché. En une dizaine d'années, ce SGBD de milieu de gamme estdevenu très populaire, bien qu'il reste moins connu que le traitement de texte etle tableur qui l'accompagnent dans la version professionnelle de la suitebureautique "Office".

2 - Les entités

Le terme "entité"est utilisé de manière générique pour désigner les données. A la granderéprobation des puristes, nous utiliserons ces deux termes comme s'ils étaientsynonymes.

Lorsqu'on veut gérer desdonnées (structurées) par des moyens informatiques, la première opérationconsiste à les recenser, puis à les classer (dans la mesure du possible) parordre d'importance décroissante. Un exemple relativement simple concerne lesdonnées que l'on trouve sur les cartes de visite, données que l'on peututiliser pour se créer une liste de contacts, à l'échelle d'une personne, d'unservice ou d'une entreprise. Ces données sont peu nombreuses, et elles setrouvent pratiquement rangées par ordre d'importance décroissante.

Le logo de l'entreprise mis àpart, on trouve typiquement sur une carte de visite professionnelle :

• le nom de l'entreprise
• le nom et le prénom de la personne
• la fonction
• le contact : adresse, téléphone, fax, mail, etc.

3 - Les relations 1-1

Commençons par un cas simple: le nom d'une personne et son prénom sont liés de manière univoque. Nousdirons que le nom et le prénom sont liés par une relation "un à un"ou "1-1". Nous les placerons dans la même table (que nous appellerons"Personnes"), sur la même ligne, et dans des colonnes adjacentes.D'une manière générale, nous placerons dans la même table les données qui sonten relation 1-1 entre elles. Ce sera notre première règle.

Certes, un même prénom peutêtre associé à des noms de famille différents, mais il ne viendrait à l'espritde personne de se compliquer la vie pour si peu. Ce n'est pas parce que le mêmeprénom revient toutes les 50 lignes dans une base de données qu'il faut crier àla redondance. Par ailleurs, une faute d'orthographe sur le prénom n'est pas undrame : il est peu probable que nous effectuions des recherches dans notrebase de contacts en utilisant un critère basé sur le prénom.

On pourrait même songer àrassembler le nom et le prénom dans une même colonne. Cette façon de procéderest généralement considérée comme maladroite, car on n'est pas sûr de lamanière dont seront saisies les informations : le nom d'abord et le prénomensuite, ou l'inverse ? Même si une consigne est édictée, il n'est pas sûrqu'elle soit toujours respectée. Il est donc préférable de séparer les deuxinformations, et de les placer dans des colonnes distinctes. Cette façon deprocéder est appelée l'atomisation des données. Il faut en user avec bon sens.

4 - Les relations 1-n

Examinons maintenant larelation qui existe entre la personne et l'entreprise. Excluons pour l'instantle cas où une personne exerce plusieurs fonctions. Nous pouvons alorsconstruire les deux phrases suivantes :

• une personne est employée par une seule entreprise ;
• une entreprise emploie (généralement) plusieurs personnes.

Nous avons affaire à unerelation "un à plusieurs" ou "1-n" entre la personne etl'entreprise. Si nous plaçons le nom de l'entreprise dans la même table que le nomde la personne, nous créons de la redondance chaque fois que nous établissonsun contact avec une nouvelle personne de la même entreprise. Nous placeronsdonc les personnes et les entreprises dans des tables distinctes (nousappellerons la seconde "Organismes", et non "Entreprises",parce qu'une même entreprise peut comporter plusieurs établissements ouorganismes : un siège social, des usines, des agences, des filiales, etc.).

D'une manière générale,chaque fois que nous rencontrerons une nouvelle relation 1-n, nous créerons unenouvelle table. Ce sera notre deuxième règle.

De plus, nous devons indiquerau système quelles sont les personnes qui font partie d'une entreprise donnée.Nous créerons donc une relation entre les tables "Personnes" et"Organismes". En pratique, nous attribuerons un code à chaqueorganisme, et nous utiliserons ce code dans la table "Personnes",comme le montre l'exemple ci-dessous. Nous constatons immédiatement que nousavons eu un contact avec deux personnes (Durand Pierre et Machin Jean)travaillant pour l'organisme CQFD.

 

D'une manière générale, nousrecenserons toutes les relations 1-n existant entre les données, de manière àles introduire dans le SGBD. Ce sera notre troisième règle.

5 - Les relations n-n

L'expérience montre que l'onrencontre des personnes qui exercent dans des entreprises différentes(affiliation multiple). Le cas est même fréquent chez les cadres supérieurs, oùl'on est volontiers directeur d'une usine et PDG d'une filiale. On rencontreégalement le cas de personnes qui partagent leur temps entre une entreprise etun établissement d'enseignement, ou une entreprise et un syndicat patronal,etc. Si nous voulons tenir compte de ces cas en évitant la redondance, noussommes amenés à modifier les phrases précitées :

• une personne peut être employée par plusieurs organismes (entreprise, établissement d'enseignement, syndicat patronal, association, etc.) ;
• un organisme emploie généralement plusieurs personnes.

Nous nous trouvons alors faceà une relation qui semble être 1-n dans les deux sens, ce qui signifie qu'il s'agitd'une relation "plusieurs à plusieurs" ou "n-n".

Pour gérer une tellerelation, il faut introduire un code dans la table "Personnes", puiscréer une table supplémentaire (appelée "Affiliation"), dans laquelleon introduit les informations relatives aux couples personne-organisme, enutilisant les codes correspondants. Cette procédure est illustrée dansl'exemple ci-dessous. Nous voyons que Durand travaille pour deux organismes,CQFD et EFPG.

Entre une personne et uneaffiliation, il existe une relation 1-n, de même qu'entre un organisme et uneaffiliation. Cet exemple nous montre, comme dans le chapitre précédent, quetoute relation n-n peut être scindée en deux relations 1-n en introduisant unetable supplémentaire appelée table de jonction. Ce sera notre quatrième règle.

6 - Le schéma relationne l

En poursuivant l'analyse desrelations existant entre les données comme nous l'avons fait ci-dessus, nousdressons la liste des tables et des relations. Il est d'usage de représenterl'ensemble tables+relations dans un schéma relationnel qui se présente comme lemontre l'exemple ci-dessous. Pour des raisons de simplicité, nous avons évitéd'atomiser l'adresse.

Comme vous pouvez leconstater, les tables sont ici représentées de manière différente. La liste deschamps s'étend verticalement sous le nom de la table, de manière à pouvoirreprésenter correctement les relations. Ce changement de représentation est dûau fait que nous traitons ici des problèmes de structure et non de contenu (cf.le chapitre 3).

Des annexes ont été créespour vous aider. Le traitement correct de l'adresse fait l'objet del'annexe 10. Le schéma relationnel complet de la liste des contacts figuredans l'annexe 11. Un autre exemple (liste de fournisseurs) est traité dansl'annexe 12.
7 - Conclusion

Dans le processus de créationd'une base de données, l'établissement du schéma relationnel de la base dedonnées représente l'étape fondamentale. Il est inutile d'aller plus loin, etde se ruer sur l'ordinateur, tant que cette étape n'est pas parfaitementmaîtrisée.

Comme vous pouvez leconstater, on n'utilise pas de moyens informatiques au cours de cette étape. Ilexiste certes des logiciels d'aide à la création du schéma relationnel, quirendent service dans les cas très complexes, mais les cas que vous rencontrereznécessiteront surtout de la réflexion, de la méthode et du bon sens. Vos outilsseront du papier, un crayon... et une bonne gomme !

Lorsque le schéma relationnelvous parait bon, testez-le par simulation sur papier. Suivez les relations etvérifiez que pouvez remplir les tables sans problème. Alors, mais alorsseulement, vous pouvez vous asseoir devant l'ordinateur, et lancer le SGBD.Mais là encore, soyez prudent : dès que vous avez introduit une petitequantité de données, testez le système et retestez-le. Car corriger le schémarelationnel d'une BDD qui est déjà remplie de données est presque toujours uneopération douloureuse.

 

 

 

 

 

Chapitre 7 : les relations

1 - Introduction

Nous avons vu, au chapitreprécédent, comment établir le schéma relationnel d'une base de données. Pourimplémenter ce schéma dans un SGBD, il faut créer des tables et des relations.Les tables ayant fait l'objet du chapitre 2 et de ses annexes, il nous fautmaintenant apprendre à créer les relations.

Comme précédemment, nousutilisons le logiciel Access de l'éditeur Microsoft comme support de ce cours.

2 - Un exemple simple

Nous commençons par un cassimple, celui où la base ne contient que deux tables liées par une relation1-n. Pour ce faire, nous réutilisons l'exemple traité au chapitre 4. Unetable intitulée "Personnes" contient les champs "Nom","Prénom", et "Commune". Une personne habite dans unecommune et une seule, mais une commune peut héberger plusieurs personnes. Pouréviter la saisie redondante du nom de la commune dans la table"Personnes", nous avons le choix entre deux méthodes. Toutes deuximpliquent la création d'une seconde table, que nous intitulerons"Communes", et qui contiendra le champ "Commune". Nouspouvons :

·         créerune liste de choix externe dans la table "Personnes", les noms decommunes provenant du champ "Commune" de la table"Communes" ;

·         relierles deux tables "Communes" et "Personnes" par une relation1-n portant sur leur champ "Commune".

Cet exemple simple nousmontre qu'une liste de choix externe n'est pas différente, dans son principe,d'une relation 1-n entre deux tables. Les différences se manifestent sur leplan pratique, car les techniques utilisées pour créer une liste externe et unerelation ne sont pas exactement les mêmes. Nous examinerons ces différences endétail dans le chapitre suivant.

3 - La création d'unerelation

Les deux tables"Personnes" et "Communes" étant créées, et la fenêtre"Base de données" étant active, nous ouvrons la fenêtre"Relations" en cliquant sur le bouton du même nom. Si les deux tables n'apparaissentpas, nous cliquons sur le bouton "Afficher la table". Une fenêtre dumême nom s'ouvre, qui nous permet d'ajouter les deux tables.

Pour créer la relationdésirée entre les deux tables, nous cliquons sur le champ "Commune"de l'une d'elles, et nous tirons le curseur (le bouton gauche de la sourismaintenu enfoncé) vers le champ "Commune" de l'autre table. Une fenêtreintitulée "Modification des relations" s'ouvre, comme le montre lafigure ci-dessous.

Il suffit de cliquer sur lebouton "Créer" pour que la relation apparaisse, comme le montre lafigure suivante.

Pour supprimer une relation :nous ouvrons la fenêtre "Relations", nous sélectionnons la relationd'un clic droit, nous choisissons "Supprimer" dans la liste quis'affiche, et nous confirmons la suppression.

4 - L'utilisation de laclé

Telle quelle, la relation quenous venons de créer ne sert pas à grand'chose, parce qu'elle est dépourvue depropriétés. En particulier, le SGBD ne sait pas que la relation est du type1-n.

La bonne démarche consiste àposer une clé sur le champ "Commune" de la table"Communes". Il en résulte que les doublons sont interdits, et que lechamp est trié par ordre alphabétique croissant. C'est indispensable pour quele champ puisse servir de côté 1 dans la relation 1-n. Nous avons expliqué, auchapitre 4, comment on pose une clé sur un champ. Rappelons-lebrièvement : il faut ouvrir la table "Communes" en modemodification, sélectionner le champ "Commune", et cliquer sur l'icône"Clé primaire".

Pour vérifier que la relationest bien du type 1-n, il faut ouvrir la fenêtre "Relations",effectuer un clic droit sur la relation, choisir "Modifier unerelation...", de telle sorte que la fenêtre "Modification desrelations s'ouvre. Nous constatons alors que, dans le bas de cette fenêtre, lapropriété "Type de relation :" est passée de "Non définie"à "Un-à-plusieurs". Le SGBD sait désormais que la relation est dutype 1-n, et que le côté 1 est du côté de la clé.

Dans la fenêtre"Relations", la présence de la clé est révélée par le fait que le nomdu champ correspondant est écrit en caractères gras, comme le montre la figureci-dessous.

La présence de la clé fait aun autre effet, qu'illustre le paragraphe suivant.

5 - La sous-table

Si nous ouvrons la table"Communes", nous constatons que nous pouvons faire apparaître"Personnes" en sous-table. Nous pouvons ainsi saisir des données dansles deux tables, sans avoir à passer de l'une à l'autre (seule la table "Communes"est ouverte). La figure ci-dessous explicite cette situation.

Introduisons quelques donnéesdans la table, puis faisons l'expérience suivante : refermons la sous-table,sélectionnons la première ligne et supprimons-la. Le SGBD ne proteste pas. Dansla table "Personnes", l'enregistrement de M. Trombe Jean, qui habiteGrenoble, est toujours présent, alors que Grenoble ne figure plus dans la listedes communes.

Dans la table"Personnes", nous pouvons introduire un enregistrement avec un nom decommune qui ne figure pas dans la table "Communes", et le SGBD neproteste toujours pas. Dans la table "Personnes", nous pouvonsintroduire un enregistrement avec un nom de commune qui ne figure pas dans latable "Communes", et le SGBD ne proteste toujours pas.

En pareil cas, on dit que labase de données manque de cohérence. La relation entre les deux tables n'estpas assez contraignante, et l'opérateur peut faire un peu n'importe quoi. Pourremédier à cette situation, il faut renforcer la relation, comme expliqué auparagraphe suivant.

6 - L'intégritéréférentielle

Dans la fenêtre"Modification des relations", un choix s'offre à nous, celui del'intégrité référentielle. Ce terme implique que le SGBD effectue un certainnombre de contrôles, pour assurer la cohérence interne de la BDD. Si nousappliquons l'intégrité référentielle :

·         unnom de commune ne provenant pas de la table "Communes" sera refusédans la table "Personnes" ;

·         ilne sera pas possible de supprimer un nom de commune dans la table"Communes" s'il a été utilisé dans la table "Personnes".

Nous cochons donc la case"Appliquer l'intégrité référentielle", puis nous appuyons sur lebouton "OK". Dans la fenêtre "Relations", la présence dessignes 1 et infini traduit l'application de l'intégrité référentielle, comme lemontre la figure ci-dessous. On remarquera que le nom du champ qui porte la clé(et qui se trouve du côté 1 de la relation) est toujours écrit encaractères gras.

Attention ! le SGBD refuserad'appliquer l'intégrité référentielle si les deux champs liés par la relation nepossèdent pas le même type de données. Seule exception : si le champcôté 1 est du type NuméroAuto, il doit être du type numérique (entierlong) du côté n. De même, le SGBD refusera d'appliquer l'intégritéréférentielle si les tables contiennent déjà des données, dont certaines ontdes valeurs empêchant l'intégrité référentielle de s'appliquer. Exemple :un nom de commune dans la table "Personnes" ne figure pas dans latable "Communes".

Si nous demandons l'intégritéréférentielle (et il est très fortement conseillé de le faire !), lesystème nous propose deux autres choix. Le premier, "Mettre à jour encascade les champs correspondants", signifie que si nous modifionsl'écriture du nom d'une commune du côté 1 de la relation, cette modificationsera reportée partout du côté n. D'une manière générale, il est recommandéd'activer cette mise à jour en cascade. Si nous ne le faisons pas, et si noustentons de modifier un nom de commune (pour corriger une faute d'orthographe,par exemple), le système nous arrêtera, avec le message suivant :"Impossible de supprimer ou de modifier l'enregistrement car la table'Personnes' comprend des enregistrements connexes". C'est clair, n'est-cepas ?

Le second choix,"Effacer en cascade les enregistrements correspondants", signifie quesi nous supprimons une donnée du côté 1 de la relation, tous lesenregistrements utilisant cette donnée du côté n seront supprimés. Celaimplique que, si nous supprimons par erreur un nom de commune dans la table"Communes", nous supprimons en même temps de la table"Personnes" toutes les personnes habitant cette commune. Il ne fautdonc pas activer cette option, sauf momentanément et en cas de besoinspécifique.

Supposons par exemple que desnoms de fournisseurs se trouvent du côté 1 de la relation, et des noms deproduits du côté n. Si un fournisseur disparaît, nous pouvons activerl'effacement en cascade. Quand nous supprimons le nom du fournisseurcôté 1, tous ses produits disparaissent du côté n. Nous effectuonsainsi la mise à jour de la base. Ensuite, nous décochons l'effacement encascade pour éviter tout risque d'effacement involontaire.

Remarque : le bouton"Type jointure..." ouvre la boite de dialogue intitulée"Propriétés de la jointure". Nous étudierons la notion de jointure auchapitre 13, dans le cadre des requêtes

7 - Conclusion

Nous avons vu, sur un exemplesimple (deux tables liées par une relation 1-n), comment créer unerelation et la doter des propriétés qui assurent la cohérence de la base dedonnées (intégrité référentielle). Nous avons reconnu au passage des notionsque nous avions déjà rencontrées à propos des listes (chapitre 4) :l'usage de la clé, les sous-tables. Il serait maintenant bon que nous étudiionsce que les listes et les relations ont en commun, et ce qui les différencie. Cesera l'objet du prochain chapitre.

 

Chapitre 8 : les listes comparées aux relations

1 - Introduction

Arrivés à ce stade de notreétude des SGBD, nous sommes amenés à nous poser la question suivante :quelle est la différence entre une liste externe (basée sur une table) et unerelation ? Pourquoi ne pas toujours utiliser l'une et ignorerl'autre ?

Comme nous allons le montrer,une liste externe implique une relation, et nous pourrons la doter de tous lesattributs d'une relation. Une relation, par contre, ne crée pas de listedéroulante, et ne peut donc pas jouer le rôle de liste. Nous sommes tentés d'endéduire que, dans Access tout au moins, il est préférable de créer une relationsous forme de liste, puis d'attribuer les propriétés voulues à la relationsous-jacente. En fait, la conclusion finale est plus nuancée.

Comme pour les autreschapitres, nous utiliserons le SGBD Access comme support pratique de ce cours.

  2 - La relation sous-jacente à une liste

Nous réutilisons l'exemple duchapitre 4, dans lequel une table appelée "Communes" sert deliste externe à une table appelée "Personnes". La table"Communes" possède un champ intitulé "Commune". La table"Personnes" possède trois champs intitulés "Nom","Prénom" et "Commune".

Dès que la liste est créée àl'aide l'assistant "Assistant liste de choix", les deux tables setrouvent liées par une relation. IL suffit, pour s'en rendre compte, d'ouvrirla fenêtre "Relations" en cliquant sur l'icône correspondante. Si nécessaire, on clique surl'icône "Afficher la table" pour ouvrir laboite de dialogue du même nom, et introduire les deux tables précitées. Lafigure ci-dessous représente le résultat obtenu : lorsqu'il a créé laliste, l'assistant a simultanément créé une relation, qui est en quelque sortesous-jacente à la liste.

L'existence de cette relationn'est pas vraiment une surprise. Comme nous l'avons déjà signalé dans lechapitre précédent, à une liste externe correspond effectivement une relation1-n.

Si nous supprimons larelation sous-jacente (clic droit, option "Supprimer"), nousconstatons que la liste fonctionne comme si de rien n'était, et que sespropriétés (onglet "Liste de choix") ne sont pas modifiées. Nous enconcluons que la relation sous-jacente n'est pas indispensable aufonctionnement de la liste.

Au passage, nous en déduisonsla méthode qui permet de supprimer une liste. Pour l'appliquer à l'exemple quenous avons choisi :

·         dansla fenêtre "Relations", nous supprimons la relation correspondant àla liste ;

·         latable "Personnes" étant ouverte en mode "Modifier", noussélectionnons le champ "Commune", puis l'onglet "Liste dechoix", et nous modifions la propriété "Afficher le contrôle" de"Zone de liste déroulante" en "Zone de texte".

3 - Une liste est aussiune relation

En règle générale, nousn'avons pas intérêt à supprimer la relation sous-jacente à une liste, car nouspouvons profiter de sa présence pour bénéficier de ses propriétés, en plus decelles de la liste.

Ainsi, si nous plaçons uneclé sur le champ "Commune" de la table "Communes", nousvoyons apparaître la sous-table, comportement normal d'une relation. Mais nousdisposons aussi, dans la table "Personnes", de la présence de laliste permettant de remplir le champ "Commune" (si la commune requiseest déjà présente dans la table "Communes").

La relation sous-jacente peutégalement être dotée de l'intégrité référentielle, ce qui rend plus sûr lefonctionnement de la liste. Pourquoi s'en priver ?

Bref, une liste fonctionne àla fois comme une liste et comme une relation. Il est des cas où nous n'enavons pas vraiment besoin, mais il est aussi des cas où cela peut nous rendreservice -- celui des tables de jonction, par exemple.

Attention ! il estimpossible, dans Access 2002, de créer une liste quand la relationcorrespondante existe déjà. Il faut détruire la relation, créer la liste, puisdoter la relation sous-jacente des propriétés désirées.

4 - Le cas des tables dejonction

Rappelons qu'une table dejonction est une table que l'on crée pour scinder une relation n-n en deuxrelations 1-n. Pour illustrer ce cas, nous utiliserons l'exemple d'une liste defournisseurs et de leurs produits.

Un même fournisseur peutfournir plusieurs produits, et un même produit peut provenir de plusieursfournisseurs. Nous nous trouvons dans le cas classique d'une relation n-n, quenous scindons en deux relations 1-n en créant une table de jonction. Notreexemple implique donc trois tables ("Fournisseurs", "Produits","Jonction") liées par deux relations. Nous faisons l'hypothèse qu'iln'y a pas de problème d'homonymie, ni pour les fournisseurs, ni pour lesproduits. Nous plaçons une clé sur le champ "Fournisseur" de la table"Fournisseurs", et une sur le champ "Produit" de la table"Produits".

Les tables se présententcomme le montre la figure ci-dessous. Pour remplir la table de jonction, ilfaut recopier soit le nom du produit (quand la sous-table produit estaffichée), soit le nom du fournisseur (quand la sous-table fournisseurs estaffichée), ce qui n'est pas admissible.

Supprimons les relations,reconstruisons-les sous forme de liste, puis dotons-les de l'intégritéréférentielle. La nouvelle situation est représentée par la figure ci-dessous.Si la table "Produit" est renseignée, on peut remplir la table"Fournisseurs" et la table "Jonction" (sous-table) dans uneseule fenêtre, comme le montre la figure.

5 - L'usage des codes

Tout ce que nous avons dit auchapitre 4 sur l'usage des codes dans les listes s'applique tel quel auxrelations. Supposons que, dans l'exemple que nous utilisons, nous ayons desproblèmes d'homonymie pour les fournisseurs et pour les produits. Nous allonsdonc introduire des codes pour ces deux entités. Le schéma relationnel setrouve modifié comme suit :

Bien entendu, comme auchapitre 4, on masque les codes, et tout se passe comme s'ils n'existaient pasquand on remplit les tables.

6 - Conclusion

Une liste de choix externe(c'est à dire basée sur une table) est une relation 1-n dotée d'un mécanismeparticulier. Il est souvent intéressant de créer une relation sous forme deliste d'abord, puis de doter ensuite la relation sous-jacente des propriétésrequises (intégrité référentielle). Cette technique est particulièrementintéressante pour la saisie des informations dans les tables de jonction.

Il ne faut pas hésiter àutiliser des codes pour régler des problèmes d'homonymie. Il faut absolumentconfier la gestion de ces codes au SGBD, et nous conseillons de faire en sorteque ces codes soient cachés à l'opérateur -- à moins qu'il ne tienne absolumentà les voir, bien entendu.

 Chapitre 9 : larecherche manuelle

1 - Introduction

Dès que des données ont étéintroduites dans une table, des moyens simples sont à notre disposition pour yrechercher de l'information. Ces moyens, qui font partie de ce que nousappelons la "recherche manuelle", sont très spécifiques du SGBDconsidéré. Dans le cas d'Access, nous pouvons utiliser :

·         lafonction " Rechercher". Cette fonction est présente (avec plus oumoins de perfectionnements) dans tous les logiciels qui manipulent du texte, ycompris Access et les autres SGBD ;

·         letri. Nous pouvons facilement rechercher de l'information dans une colonne sielle est triée par ordre croissant. De plus, le tri croissant fait apparaîtreen tête de colonne la valeur la plus faible, alors que le tri décroissant faitapparaître en tête la valeur la plus forte ;

·         lesfiltres. Appliqués à une table, ils ne laissent apparaître que lesenregistrements répondant à un -- ou à quelques -- critères simples. Il existeplusieurs sortes de filtre : le filtre par sélection, le filtre horssélection (l'inverse du précédent), le filtre par formulaire et le filtre/tri.

Ces techniques de recherche"manuelle" ne permettent pas d'effectuer des opérations trèssophistiquées, mais elles ont le mérite de la rapidité et de la simplicité.Quand elles s'avèrent insuffisantes, il faut utiliser l'outil de recherche dontsont dotés tous les SGBD, et qui s'appelle la requête. Nous étudierons lesrequêtes dans les chapitres 10 à 17.

Il existe d'ailleurs unetransition presque continue entre filtres et requêtes, puisque la formulationd'un filtre élaboré fait appel aux mêmes techniques que celle d'une requête,qu'un filtre peut être enregistré comme une requête, et qu'une requête peutservir de filtre.

Comme pour les autreschapitres de cours, nous utiliserons le SGBD Access (version 2002) commesupport pratique. On notera que, dans ce logiciel, tout ce qui concerne le triet les filtres s'applique non seulement aux tables, mais aussi aux formulaires.

2 - La fonction"Rechercher"

Une table étant ouverte, etune colonne étant sélectionnée, cliquons dans le menu sur "Édition",puis sur "Rechercher..." : la fenêtre "Rechercher etremplacer" s'ouvre, l'onglet "Rechercher" étant sélectionné.Nous serions arrivés au même résultat en cliquant sur l'icône "Rechercher". Par défaut, la zone"Rechercher dans :" contient le nom de la première colonne, etla zone "Rechercher :" le premier élément de cette colonne,comme le montre l'image ci-dessous.

Il est une valeur par défautdont il faut se méfier comme de la peste, c'est "Champ entier" dansla zone "Où :". Cette zone, en effet, propose troisoptions :

·         N'importeoù dans le champ

·         Champentier

·         Débutde champ

Dans le premier cas, lachaîne recherchée occupe tout ou partie du champ. Dans le second cas, beaucoupplus restrictif, la chaîne correspond exactement au contenu du champ. Dans letroisième cas, également restrictif, la chaîne occupe le début ou la totalitédu champ. Le résultat d'une recherche dépend évidemment beaucoup du choixeffectué, et "Champ entier" ne correspond pas forcément à ce que vousavez l'intention de faire. Attention, donc, à ce "Où :" !

  3 - Le tri

Trier une table sur unecolonne donnée est une opération fort simple. La table étant ouverte, noussélectionnons la colonne désirée en plaçant le curseur en tête de colonne, puisen cliquant lorsqu'une petite flèche noire apparaît. Le contenu de la colonneapparaît en blanc sur fond noir. Nous cliquons alors sur l'icône pour obtenir le tri en ordre croissant, ousur l'icône pour obtenir le tri en ordre décroissant. Sices icônes n'apparaissent pas, nous sélectionnons "Affichage" dans lemenu, puis "Barres d'outils", puis nous cochons "Feuilles dedonnées de tables".

Si la table ne contient quequelques centaines d'enregistrements, l'opération de tri est presqueinstantanée. Si la table contient plusieurs centaines de milliersd'enregistrements, l'opération peut demander une minute environ, pour un PC dequalité moyenne. Comme on peut le constater de visu, le tri s'effectue sur ledisque dur, si bien qu'une machine possédant un disque SCSI (un serveur defichiers, par exemple) se montrera beaucoup plus rapide qu'un PC ordinaire enpareil cas.

Lorsque nous refermons latable, le SGBD nous demande si nous voulons conserver la modification que nouslui avons fait subir. En cas de réponse affirmative, la table apparaît denouveau triée quand nous la rouvrons. En fait, le SGBD Access conserve lesdonnées dans l'ordre où elles ont été initialement saisies, mais ré-applique letri lors de l'ouverture de la table -- ce que confirme l'expérience suivante.

Créons une table à une seulecolonne de type texte, saisissons quelques chaînes de caractères, puis trionsla table en ordre croissant et enregistrons-la en confirmant la modification.Créons alors une seconde colonne de type NuméroAuto, enregistrons cettemodification, et consultons la table. Surprise : la numérotation de lasecond colonne ne suit pas l'ordre alphabétique, mais l'ordre de créationinitial, comme le montre la figure ci-dessous. Nous verrons auparagraphe 7 où se trouve stockée l'information de tri d'une table.

Conclusion : pour changerdéfinitivement l'ordre des informations enregistrées dans une table, il faututiliser une requête permettant de recréer la table sous un autre nom, ou del'insérer dans une autre table (vide).

4 - Le filtre parsélection et le filtre hors sélection

Le filtre par sélection. Unetable étant ouverte, sélectionnons une chaîne de caractères dans l'une de sescolonnes, puis cliquons sur l'icône "Filtrer par sélection". Tous lesenregistrements disparaissent, à l'exception de ceux qui contiennent la chaînesélectionnée dans le champ considéré. En bas de la table s'inscrit le nouveaunombre de lignes, suivi de la mention "(Filtré)".

Le retour de la table à sonétat initial s'obtient en cliquant sur l'icône "Supprimer le filtre", ou enrefermant la table (avec ou sans confirmation). On notera que l'icône fonctionne comme un commutateur : lefiltre étant supprimé, l'icône prend le nom "Appliquer le filtre", etcliquer de nouveau dessus a pour effet de rétablir le filtre.

Il est fréquent que le filtrepar sélection facilite considérablement l'examen du contenu d'une table.Supposons par exemple que la table considérée contiennent le résultat desventes d'une entreprise, et que dans une colonne figure le nom du commercialresponsable de chaque vente. Pour obtenir l'ensemble des ventes d'un commercialdonné, il suffit que nous sélectionnions son nom, puis que nous cliquions surl'icône . Toutes les ventes ne le concernant pasdisparaissent instantanément. Le filtre par sélection est un outil simple --mais extrêmement rapide -- d'analyse des données contenues dans une table.

Attention ! le filtre parsélection fonctionne comme la fonction "Rechercher", en ce sens quela position de la chaîne sélectionnée importe beaucoup. Si cette chaîne :

·         n'estpas en contact avec les extrémités du champ, le SGBD retient lesenregistrements qui contiennent cette chaîne (n'importe où dans le champ) ;

·         setrouve au début du champ, le SGBD retient les enregistrements dont le champcommence par cette chaîne ;

·         setrouve en fin de champ, le SGBD retient les enregistrements dont le champ finitpar cette chaîne.

Le filtre hors sélection. Cefiltre fonctionne à l'opposé du filtre par sélection : tous lesenregistrements disparaissent, à l'exception de ceux qui ne contiennent pas lachaîne sélectionnée. Mais il n'existe pas d'icône qui corresponde au filtrehors sélection, si bien qu'il nous faut passer par le menu. Nous cliquons sur"Enregistrements", puis sur "Filtrer", et enfin sur"Filtrer hors sélection".

La figure ci-dessous illustrele fonctionnement des deux filtres (par sélection et hors sélection) dans uncas fort simple :

Nous pouvons appliquer unfiltre par sélection au résultat d'un précédent filtre par sélection, de manièreà affiner une recherche. En d'autres termes, les filtres par sélection sontemboîtables.

  5 - Le filtre par formulaire

Le filtre par formulairepermet de filtrer une table en utilisant simultanément plusieurs chaînes decaractères, liées par des opérateurs logiques ET et OU. Il peut être considérécomme un perfectionnement du filtre par sélection, avec cependant uneréserve : les chaînes choisies représentent obligatoirement le contenuexact du champ.

Pour illustrer lefonctionnement de ce filtre sur un exemple, nous créons une table("Table5") contenant quatre colonnes, et nous y introduisons desdonnées comme représenté sur la figure ci-dessous. Puis nous cliquons surl'icône "Filtrer par formulaire". S'ouvrealors une fenêtre intitulée "Table5: Filtrer par formulaire".Une liste déroulante (dédoublonnée) nous permet, pour chaque colonne, dechoisir une donnée : le SGBD filtrera en appliquant l'opérateur logique ETentre ces termes. Nous pouvons également effectuer un OU, en cliquant surl'onglet du même nom. Pour appliquer le filtre, nous cliquons sur l'icône "Appliquer le filtre", et nousobtenons le résultat représenté ci-dessous. Pour supprimer le filtre, nouscliquons une seconde fois sur l'icône qui s'appelle maintenant "Supprimer lefiltre".

A notre connaissance, lefiltre par formulaire est peu utilisé. On peut lui reprocher son manque desouplesse : les chaînes que l'on choisit représentent exactement lecontenu du champ. De plus, si on pratique un OU, le premier terme del'alternative disparaît de l'écran. Le filtre par sélection, plus simple, maisplus souple, rend de meilleurs services.

6 - Le filtre/tri

Le tri que nous avonsconsidéré au paragraphe 2 ne concerne qu'une seule colonne, c'est un trisimple. Dans certains cas, nous avons besoin d'effectuer un tri sur plusieurscolonnes, encore appelé tri multiple. Le filtre/tri est l'outil qui nous permetd'arriver à nos fins sans avoir besoin de créer une requête.

Considérons l'exemple dufichier journal d'un site web, dans lequel chaque ligne correspond à unerequête (une demande de fichier). Importé dans un SGBD, ce fichier devient unetable, dans laquelle la première colonne contient la date, la seconde colonnel'heure, etc. Mais l'importation, et les manipulations qui la suivent, peuventperturber l'ordre dans lequel les requêtes ont été traitées par le serveur web.Pour rétablir cet ordre, il faut que nous puissions trier la table sur la dated'abord, et sur l'heure ensuite.

Pour ce faire, nous cliquonsdans le menu sur "Enregistrements", puis sur "Filtrer", etenfin sur "Filtre/tri avancé...". S'ouvre une fenêtre"Filtre" qui ressemble, à s'y méprendre, à celle qui permet dedéfinir une requête. A noter que le filtre/tri possède une icône , mais que cette dernière ne se trouve pas enstandard dans la barre d'outils "Feuille de données de table". Onpeut l'y introduire par personnalisation de la barre d'outils.

Nous remplissons la grillecomme indiqué dans la figure ci-dessous, puis nous appliquons le filtre encliquant sur l'icône "Appliquer le filtre", enfin nousrefermons la fenêtre "Filtre". Nous vérifions que la table esteffectivement triée comme nous l'avons demandé.

Si nous refermons puisrouvrons la table, nous constatons que le filtre/tri agit toujours : latable reste triée. Si nous cliquons comme précédemment sur"Enregistrements", puis sur "Filtrer", et enfin sur"Filtre/tri avancé...", le filtre/tri s'affiche de nouveau comme nousl'avons défini, et nous pouvons le modifier à loisir. Par contre, si nouscréons une nouvelle colonne autonumérotée, nous constatons que la table reprendson ordre initial, comme dans le cas d'un tri simple. Nous constatons de plusque le filtre/tri est de nouveau vierge, ce qui montre que toute modificationnotable de la table fait disparaître le filtre/tri. Dans le cas où la tablen'est pas modifiée, par contre, le filtre/tri la suit comme son ombre.

La ligne "Critères:" permet, comme dans une requête, de sélectionner les enregistrements àafficher sur des critères plus ou moins complexes. Les techniquescorrespondantes sont exposées dans les chapitres relatifs aux requêtes.

Le filtre/tri est un outilintéressant, car il permet de doter une table d'un tri permanent plus ou moinsélaboré. Chaque fois que nous ouvrons la table, elle se présente sous la formetriée que nous désirons, sans que nous ayons à intervenir.

7 - L'enregistrement d'untri ou d'un filtre

Ouvrons la table contenantdes noms, et faisons-lui subir un tri par sélection sur "Truc".Ouvrons ensuite la fenêtre "Filtre" : le terme "Truc"apparaît sur la ligne "Critères :", les guillemets indiquant qu'ils'agit d'une chaîne de caractères. La fenêtre "Filtre" révèle donc àla fois le stockage des filtres et celui des tris.

Si nous appliquonssuccessivement à une table un filtre par sélection, puis un filtre/tri, nous retrouveronstrace des deux opérations dans la fenêtre "Filtre", comme le montrela figure ci-dessous, relative à la table contenant des dates et des heures.

Un filtre/tri peut êtreenregistré : il devient alors une requête. Pour ce faire, la fenêtrefiltre étant ouverte, nous cliquons sur l'icône "Enregistrer en tant que requête".Pour distinguer le filtre d'une requête, nous lui donnons un nom commençant par"Filtre...". Par précaution, nous rajoutons le nom de la table àlaquelle il s'applique. Exemple : Filtre3_table5.

Pour réutiliser le filtreainsi enregistré, nous ouvrons la table concernée, puis la fenêtre"Filtre", et nous cliquons sur l'icône "Charger à partir d'une requête".La liste des requêtes s'affiche, dans laquelle nous choisissons le filtredésiré. Ce dernier s'affiche dans la grille, et nous pouvons l'appliquer encliquant sur l'icône .

Un filtre enregistré sousforme d'une requête bénéficie de toutes les propriétés de ces dernières.

8 - Conclusion

Ce chapitre traite detechniques modestes, mais qui ont leur utilité. La fonction"Rechercher" permet de vérifier si une donnée figure ou non dans unetable. Dans la négative, il faut tout de suite vérifier que l'on ne s'est pastrompé de colonne, ni de place dans le champ...

Tout utilisateur de SGBD quimanipule des tables se sert du tri à tour de bras. Le tri par sélection est unetechnique très simple qui rend bien des services. On l'utilise souvent coupléeà la fonction "Rechercher". Cette dernière permet de trouver lapremière occurrence d'une chaîne donnée, avant d'appliquer le tri par sélectionqui fournit immédiatement les autres.

Le filtre/tri, enfin, méritele détour, car il permet de présenter une table triée comme on a envie de lavoir et de l'utiliser.

Chapitre 10 : Introduction aux requêtes

1 - Préambule

Nous savons désormais stockerdes informations structurées dans les tables d'une base de donnéesrelationnelle. Cette étape franchie, il nous faut maintenant apprendre à gérerces informations, et à retrouver celles dont nous avons besoin quand celas'avère nécessaire.

Une base de données a besoinde maintenance. Il faut pouvoir supprimer les informations obsolètes après lesavoir archivées. Il est, par exemple, inutile de laisser traîner dans une BDDdes données relatives à des factures qui ont été réglées, et qui sont relativesà un exercice clos.

Une base de données estsouvent une mine d'informations, en particulier dans le domaine économique etfinancier. Il est très important pour le bon fonctionnement d'une entrepriseque ces informations puissent être retrouvées rapidement et simplement par lespersonnes qui en ont besoin et qui sauront en faire bon usage.

Pour ce faire, la requêteconstitue l'outil adéquat. La requête est, par ordre d'importance décroissante,le deuxième "objet" des BDD après la table.

Comme pour les autreschapitres de ce cours, nous nous servirons du SGBD Access pour lesdéveloppements pratiques

2 - Les trois fonctionsdes requêtes

L'outil requête a troisfonctions principales :

·         laréalisation de vues présentant tout ou partie de l'information contenue dans laBDD. Dans une base de données relationnelle, les données sont éparpillées dansde multiple tables, liées par des relations, et contenant souvent des codes nonexplicites. Pour appréhender, en partie ou en totalité, le contenu de la base,il faut rassembler les données utiles dans une seule table, que l'utilisateurpeut consulter directement ou via un formulaire. Pour ce faire, on sélectionnedes colonnes dans différentes tables, et on met les lignes en correspondancegrâce aux relations ;

·         lamaintenance de la BDD. Cette opération consiste à archiver et / ousupprimer des enregistrements obsolètes, mettre à jour des données révisables,rechercher et supprimer les doublons indésirables, etc. Elle concerne deslignes particulières, mais le nombre de colonnes n'est pas modifié ;

·         larecherche d'information dans la BDD. Cette opération consiste à créer unesous-table contenant les enregistrements répondant à certains critères etappartenant à certains champs. Elle porte à la fois sur les lignes et lescolonnes d'une table, ou de plusieurs tables liées par des relations.

3 - Les différents typesde requêtes

Pour assurer les troisfonctions précitées, différents types de requêtes ont été créés, que l'onretrouve dans presque tous les SGBD. On peut les classer ainsi :

·         Lasélection simple ou projection permet de réaliser les vues précitées ;

·         Lasélection est l'outil de recherche d'information par excellence, même si cen'est pas le seul qui soit utilisé. Cette requête est dotée de deuxperfectionnements importants (la jointure et le regroupement) ;

·         Lesopérations ensemblistes (dont la plus importante est l'union), auxquelles onpeut associer l'ajout. Elles permettent de regrouper dans une même table desenregistrements provenant de deux tables différentes ;

·         Lesrequêtes de maintenance sont principalement la mise à jour et la suppression.La première permet de modifier le contenu de certains champs, la seconde desupprimer certains enregistrements ;

·         L'analysecroisée est une spécificité d'Access. Comme son nom l'indique, c'est un outild'analyse qui permet, sous certaines conditions, de réorganiser complètementune table.

Le SGBD Access permet decréer des requêtes en utilisant soit une interface graphique, soit le langageSQL. Nous étudions tour à tour ces deux possibilités :

·         Interfacegraphique. La sélection simple (ou projection) fait l'objet duchapitre 11. La sélection est étudiée dans le chapitre 12, et sesperfectionnements dans les trois chapitres suivants. L'ajout et l'analysecroisée sont regroupées dans le chapitre 16. La mise à jour et lasuppression sont étudiées dans le chapitre 17.

·         LangageSQL. Quatre chapitres (18-21) sont consacrés à la création des divers types derequêtes.

Dans le SGBD Access, lacréation d'une requête union n'est possible qu'en SQL. Nous évoquerons ce pointau chapitre 20. On notera par ailleurs que deux opérations ensemblistes(intersection et différence) ne sont pas implémentées dans Access.

4 - Conclusion

La recherche d'informationest, à notre humble avis, l'aspect le plus intéressant -- pour ne pas dire leplus passionnant -- de l'étude des bases de données. En enchaînantastucieusement un petit nombre de requêtes bien choisies, on peut souvent fairedes merveilles dans la recherche d'information, sans avoir besoin de recouriraux outils plus compliqués (et combien plus onéreux !) du "datamining", ou autres techniques à la mode.

Chapitre 11 : la sélection simple

1 - Introduction

Dans ce chapitre (le secondd'une série de quatre consacrés aux requêtes), nous apprendrons à réaliser desopérations de sélection simple (encore appelée projection). La sélection simpleopère sur les colonnes. Il n'y a pas de critère de sélection relatif au contenudes enregistrements, et de ce fait le nombre de lignes reste inchangé.

La figure ci-dessousreprésente schématiquement une table contenant 7 colonnes. Grâce à unesélection simple (ou projection), nous pouvons reconstituer une table necontenant que les colonnes V, Y et Z (colorées en jaune).

En fait, le nombre de lignespeut diminuer quelque peu. C'est le cas lorsqu'on élimine les doublons, oulorsqu'on effectue une requête basée sur plusieurs tables et qu'il manque desinformations dans certaines d'entre elles. Nous déborderons quelque peu ducadre de la sélection simple, pour apprendre à mettre en forme l'informationobtenue, par élimination des doublons, concaténation de chaînes, etc.

Comme pour les autreschapitres de ce cours nous utiliserons le SGBD Access comme support pratique.

2 - La création d'unerequête

A titre de premier exemple desélection simple, nous allons créer une requête qui extrait d'une table uneliste de personnes désignées par leur nom et leur prénom. Notre point de départsera la table "Personnes" représentée ci-dessous.

Notons d'abord qu'une requêteopère sur une ou sur plusieurs tables. On ne peut donc pas créer de requêtedans une base de données vide. Certes, le SGBD Access ne refusera pas d'ouvrirla fenêtre de création d'une requête dans une base vide, mais si aucune tablen'est présente, nous ne pourrons rien faire d'autre que créer une requête vide.Ouvrons donc la BDD contenant la table "Personnes" représentéeci-dessus. Dans la fenêtre "Base de données", sélectionnons l'objet"Requêtes". Double cliquons sur "Créer une requête en modecréation". Une fenêtre intitulée "Requête1 : RequêteSélection" s'ouvre, ainsi qu'une boite de dialogue intitulée"Afficher la table". Cette boite affiche la liste des tables quecontient la BDD. Nous sélectionnons la table "Personnes" sur laquelledoit porter la requête, puis nous cliquons successivement sur les boutons"Ajouter" et "Fermer". La table "Personnes" estmaintenant présente dans la moitié haute de la fenêtre de création de larequête.

La moitié basse contient lagrille de définition de la requête. Pour y introduire un champ (on notera aupassage que l'astérisque représente la totalité des champs), nous disposons detrois méthodes :

• cliquer sur la ligne "Champ :" et choisir dans la liste déroulante qui s'affiche ;
• double cliquer sur le nom du champ ;
• tirer le nom du champ avec la souris de la table vers la grille.

Pour extraire de la table"Personnes" les deux premières colonnes, nous introduisons dans lagrille les champs correspondants. Sur la ligne "Afficher :", lescases doivent être cochées (elles le sont par défaut). La figure suivante estextraite de la grille de définition de la requête :

La requête étant définie,nous l'exécutons en cliquant sur le bouton de la barre d'outils. Nous obtenons lerésultat suivant :

Nous voyons que, comme unetable, une requête présente un double aspect :

• l'aspect structure, lequel est défini en mode création ;
• l'aspect résultat, qui est représentée par une table à l'existence volatile, laquelle s'appelle "feuille de données" dans la terminologie de Microsoft.

Comme pour une tableégalement, on peut passer rapidement d'un aspect à l'autre en cliquant dans labarre d'outils sur le bouton (en mode feuille de données), ou le bouton (en mode création).

Pour conserver la structurede la requête, il suffit de cliquer sur l'icône "Enregistrer", de donner un nom(par exemple, "Sélection des personnes") à la requête dans la boitede dialogue qui s'ouvre, et de confirmer. Ce nom figurera désormais dans lafenêtre "Base de données" (l'objet "Requêtes" étantsélectionné), précédé de l'icône , pour rappeler qu'il s'agit d'une requête desélection. Si nous fermons la fenêtre de définition de la requête sans avoirpréalablement enregistré la structure, le SGBD nous demande si nous voulonsconserver la requête. Dans l'affirmative, la boite de dialogue s'ouvre, et nousprocédons comme précédemment. Mais le résultat de la requête a disparu !Pour retrouver cette "feuille de données" volatile, il faut relancerla requête, soit en double-cliquant sur son nom, soit en la sélectionnant et encliquant sur le bouton "Ouvrir" (lequel devrait plutôts'appeler "Exécuter").

3 - La requête avec créationde table

Le résultat d'une requête estune table, et il peut être enregistré comme tel. Pour ce faire, noussélectionnons la requête précédente, et nous cliquons sur le bouton  "Modifier". La requête s'ouvre en mode création. Nous cliquons sur lebouton de la barre d'outils et, dans la listedéroulante qui s'affiche, nous sélectionnons "Requête Création detable...". Une boite de dialogue s'ouvre, dans laquelle nous renseignonsle nom de la table ("Liste de personnes", par exemple). Dans la listedes requêtes, "Sélection des personnes" apparaît maintenant avecl'icône , qui rappelle que le résultat de la requêteest enregistré dans la base sous forme d'une table. Exécutons la requête : deuxboites d'alerte s'ouvrent successivement. Pas de panique, répondons"oui" dans les deux cas. Si la table existe déjà, une troisième boited'alerte prévient de son écrasement. Que de précautions ! (Si ces alertesvous agacent, vous pouvez les supprimer en utilisant la rubrique"Outils" du menu. Cliquez sur "Options...", puis surl'onglet "Modifier/Rechercher, et décochez les cases de la zone"Confirmer"). Nous pouvons maintenant vérifier, dans la fenêtre"Base de donnée" (l'objet "Tables" étant sélectionné), quela table "Liste de personnes" a bien été créée. Si nous l'ouvrons,nous constatons que son contenu correspond bien à la structure de la requête"Sélection des personnes". Comment faire pour qu'une requête ne créeplus de table ? Il semble que l'éditeur Microsoft n'ait pas prévu la choseen mode graphique, si bien qu'il faut passer en mode SQL. La fenêtre decréation (ou modification) de la requête étant ouverte, nous cliquons sur lapetite flèche adjacente à l'icône "Affichage". Dans la listedéroulante, nous choisissons "Mode SQL", et la traduction de notrerequête en langage SQL s'affiche. Dans la première ligne du code, nous repéronsle terme "INTO" suivi du nom de la table (éventuellement écrit entrecrochets). Nous les supprimons tous les deux, nous refermons la fenêtre, etnous confirmons la modification de la requête.

4 - Le tri simple et letri multiple

On ne peut retrouverrapidement des informations dans une liste que si elle est triée (par ordrealphabétique). Or la liste des personnes que crée notre requête présente ledéfaut d'être présentée dans l'ordre où les informations ont été saisies. Unetable, en effet, se remplit toujours par la ligne la plus basse. Pour trier latable, nous pouvons utiliser le bouton , mais il est plus pratique de rendrel'opération automatique. Sélectionnons la requête, et cliquons sur . Dans la grille, cliquons à l'intersectionde la colonne "nom_personne" et de la ligne "Tri :". Uneliste s'affiche, qui nous propose les trois options possibles : croissant,décroissant et non trié. Choisissons "croissant", refermons lafenêtre, confirmons la modification, et relançons la requête : la table"Liste de personnes" s'affiche désormais par ordre alphabétique desnoms, comme le montre la feuille de données ci-dessous.

Nous pouvons égalementdemander le tri croissant dans le champ "prénom". Si deux personnesportent le même nom, elles apparaîtront dans l'ordre croissant de leurs prénomsrespectifs. Attention ! ce tri multiple s'exécute de gauche àdroite : par les noms d'abord, par les prénoms ensuite. Si nous voulonsobtenir le résultat inverse, il faut que nous placions la colonne nom à droitede la colonne prénom dans la grille de création de la requête. Pour ce faire,il faut sélectionner (par le haut) la colonne à déplacer, puis la tirer(toujours par le haut) jusqu'à sa nouvelle position.

5 - L'élimination desdoublons

Dans la table "Liste depersonnes", Paul Lechant apparaît à deux reprises : nous avonsaffaire à un doublon, une information répétée deux fois ou plus. Dans la table"Personnes" de départ, cette double apparition de Paul Lechant étaitjustifiée par deux affiliations distinctes. La sélection a fait disparaître lesinformations correspondant à l'affiliation et créé le doublon. Nous pouvonsfaire en sorte que les doublons soient éliminés du résultat :

• grâce à une modification des propriétés de la requête ;
• grâce à une opération de regroupement.

Première méthode. Ouvrons larequête "Requête1" en mode création. Effectuons un clic droit dans lafenêtre de définition de la requête et sélectionnons "Propriétés"dans la liste déroulante, ou cliquons sur l'icône "Propriétés". La boite de dialogue"Propriétés de la requête" s'ouvre. Modifions la propriété"Valeurs distinctes" de "Non" à "Oui". Fermons laboite de dialogue, et basculons en mode feuille de données : les doublonsont disparu, comme le montre la feuille de données ci-dessous.

L'opération est réversible :si nous basculons en mode création, ramenons la propriété "Valeursdistinctes" de "Oui" à "Non", et rebasculons en modefeuilles de données, les doublons sont de retour.

Deuxième méthode. Comme sonnom l'indique, l'opération de regroupement consiste à rassembler les lignesd'une table qui ont quelque chose en commun -- la même valeur dans un champdonné, par exemple. Au cours de l'opération de regroupement, les doublons sontautomatiquement éliminés. On se sert habituellement du regroupement poureffectuer des calculs sur des groupes de lignes, au lieu de les effectuer surla table entière. Mais on peut aussi utiliser le regroupement pour éliminer lesdoublons. Créons une requête simple basée sur la table "Personnes",et sélectionnons les deux champs "nom_personne" et"prénom". Cliquons sur l'icône  "Totaux" : une nouvelle ligne(intitulée "Opération :") apparaît dans la grille de définitionde la requête, avec la mention "Regroupement" déjà inscrite pardéfaut pour chacun des deux champs (si cette mention n'apparaît pas, il fautcliquer à l'endroit correspondant, et choisir "Regroupement" dans laliste déroulante qui s'affiche). La requête se présente comme le montre lafigure ci-dessous.

Basculons ensuite en mode"feuille de données" : les doublons ont disparu et la feuille dedonnées est triée par ordre alphabétique croissant. Notons que ces deuxtechniques éliminent également les doublons éventuellement présents dans latable de départ.

6 - La requête aveccréation de champ

Dans la liste des personnes,nous voulons maintenant rassembler chaque nom, suivi de son prénom, dans unemême colonne. Pour ce faire, nous créons la requête suivante :

La signification du contenude la ligne "Champ :" de la grille ci-dessus est la suivante :

• la requête crée une feuille de données contenant une colonne intitulée "personne" ;
• chaque ligne contiendra le nom, puis un espace, puis le prénom. Ces données proviendront de la table située au-dessus de la grille.

Le signe & désigne, commeen Visual Basic, l'opérateur de concaténation de chaînes. Les crochets[........] signifient que l'on évoque le contenu des champs correspondants.L'espace qui sépare le nom du prénom est mis entre guillemets pour rappelerqu'il s'agit d'une chaîne de caractères. Le résultat de la requête est lesuivant :

De la même manière, on peutconcaténer le code postal avec un tiret suivi du nom de la commune,reconstituer une adresse complète, etc. Cette technique de reconstitution dechaînes est intéressante parce que, au nom du principe d'atomisation, lesinformations situées dans une BDD sont divisées le plus possible en petitsmorceaux.

De manière plus générale, unerequête avec création de champ permet d'effectuer des opérations (numériques ousur chaînes de caractères) sur le contenu des champs d'un même enregistrement,c'est à dire horizontalement. On peut effectuer des opérations verticalementdans une table (en utilisant ou non la notion de regroupement), mais on obtientune meilleure présentation en se servant des états, que nous étudierons dans unchapitre ultérieur.

7 - Les requêtes emboîtées

Une requête peut prendrecomme point de départ la feuille de données résultant de l'exécution d'uneautre requête. Il suffit de lancer la seconde requête pour que la premières'exécute en premier lieu. On peut généraliser, et créer une chaîne de requêtesqui s'exécutent dans l'ordre par simple lancement de la dernière. Il fautsimplement veiller à ce que chaque requête (à l'exclusion de la dernière) necrée pas de table. Sinon, le logiciel proposera de partir de cette table, et lachaîne sera rompue.

A titre d'exemple, créons lesrequêtes suivantes :

·         larequête n° 1 extrait les colonnes nom et prénom de la table"Personnes", et trie par ordre croissant des noms ;

·         larequête n° 2 part du résultat de la requête n° 1 et élimine lesdoublons (par modification de propriété, ou par regroupement) ;

·         larequête n° 3 part du résultat de la requête n° 2 et concatène nom etprénom.

Il suffit de lancer latroisième requête pour que l'ensemble s'exécute et fournisse le résultat obtenuau paragraphe précédent. Nous avons ainsi créé un automatisme élémentaire. Nousverrons dans un chapitre ultérieur que l'on peut obtenir le même résultat avecune macro.

Il ne faut pas abuser del'emboîtement, et les professionnels conseillent généralement de ne pasemboîter plus de 3 requêtes à la file. Il y a plusieurs raisons à cela :

·         siune requête est utilisée plusieurs fois dans une application, toutes lesrequêtes emboîtées qui la précédent seront re-exécutées. On allonge ainsi letemps machine requis pour l'application ;

·         siune requête faisant partie d'un emboîtement contient une erreur, cette erreursera signalée par le système (du moins par le SGBD Access) comme faisant partiede la dernière requête de l'emboîtement. L'emboîtement rend donc la correctiondes erreurs plus difficile ;

·         l'emboîtementse programme malaisément lorsqu'on utilise le langage SQL, et le risque d'erreurcroit avec le nombre de requêtes emboîtées.

8 - La requêtemultifonctionnelle

Pour des raisons didactiques,nous avons créé une nouvelle requête pour chaque opération que nous voulionsréaliser. Dans la pratique, nous éviterons de multiplier les requêtes, enregroupant le plus possible les opérations à effectuer dans une même requête. Ainsi,la requête représentée par la figure ci-dessous permet d'obtenir le résultatfinal (la liste des noms concaténés avec les prénoms, dans l'ordrealphabétique, et sans doublons) en une seule étape :

et on peut lui demander enplus de créer une table si on le désire. On notera qu'il n'est pas nécessaireque le nom de la table figure dans la grille (mais la table doit être présenteau-dessus de la grille), et qu'il est inutile de spécifier un tri car cedernier est implicite en cas de regroupement.

9- La requête multi-table

Dans une BDD relationnelle, lesinformations sont généralement dispersées dans plusieurs tables (une dizainecouramment, voire plus) liées par un nombre similaire de relations, ce qui faitqu'il est impossible d'avoir une vue globale du contenu de la base. Une requêtemulti-table permet de rassembler dans une même table les informations désirées,et d'obtenir au premier coup d'oeil une idée de ce contenu.

Revenons à la table"Personnes" que nous avons utilisée au début de ce chapitre. Unepersonne pouvant travailler pour plusieurs organismes, et un organisme pouvantemployer les services de plusieurs personnes, la table "Personnes"doit être séparée en trois tables (dont une table de jonction), liées par desrelations. Le schéma relationnel correspondant apparaît sur la figure ci-dessous.

Mais cette séparation entrois tables fragmente les données, et nous empêche de voir simplement quitravaille pour qui. Si nous nous plaçons dans la table "Personnes",nous voyons aussi (grâce à la sous-table) les données de la table "Affiliation",mais pas celles de la table "Organismes". Si nous nous plaçons dansla table "Organismes", nous voyons aussi (grâce à la sous-table) lesdonnées de la table "Affiliation", mais pas celles de la table"Personnes". La solution consiste à rassembler pour examen, dans unemême table, les données que nous voulons examiner simultanément. Bref, il fautque nous exécutions une requête de sélection simple multi-table.

Dans la fenêtre "Base dedonnées", l'objet "Requêtes" étant sélectionné, nous cliquonssur "Créer une requête en mode Création". Dans la boite de dialogue"Afficher la table", nous sélectionnons les trois tables nécessaires(l'une après l'autre, ou simultanément grâce à la touche CTRL), et nous construisonsla requête représentée ci-dessous.

Nous obtenons ainsi une vueclaire du contenu de la base, vue que nous n'avons absolument pas lorsque nousexaminons les trois tables de départ.

Attention ! Si nouseffectuons une sélection sur les colonnes de deux tables qui ne sont pas liéespar une relation, le logiciel associe chaque ligne de la première table àtoutes les lignes de la seconde (cela s'appelle faire leur produit vectoriel).Le résultat est généralement sans intérêt et, si les deux tables sontconséquentes, l'opération risque d'être fort longue.

Dans le même ordre d'idée, ilne faut jamais introduire dans la fenêtre de création de requête une table dontla présence n'est pas nécessaire. Le résultat de la requête risque d'être toutà fait aberrant.

10 - Conclusion

Les opérations de sélectionsimple (ou projection) s'effectuent sur les colonnes des tables. Les lignes nesont pas modifiées.

Les opérations de sélectionsimple (ou projection) sont fort utiles lorsqu'on désire :

·         obtenirune vue simplifiée d'une table, en ne conservant que les colonnes contenant lesinformations désirées ;

·         rassemblerdes informations dispersées parmi plusieurs tables liées par des relations. Oncrée ainsi une vue partielle ou globale du contenu de la base de données.

Chapitre 12 : la requête de sélection

1 - Introduction

Stocker sans cesse desinformations dans une base de données, et en assurer la maintenance, n'est pasune fin en soi. Il faut pouvoir retrouver, chaque fois que cela est nécessaire,les informations pertinentes dont on a besoin. La requête de sélection a étécréée dans ce but. Elle joue, dans les BDD, un rôle très important.

2 - La requête desélection

Dans le cas le plus simple,la requête sélection s'applique à une seule table dont elle conserve toutes lescolonnes. Contrairement à la sélection simple (ou projection) qui permetd'extraire d'une table certaines colonnes nommément désignées, la sélectionpermet d'extraire d'une table les lignes répondant à certains critères, commele montre la figure ci-dessous. L'ensemble des critères d'une requête desélection est parfois appelé filtre (par analogie avec le filtre manuel), etl'expression filtrer une table à l'aide d'une requête est assez courante.

La sélection représentel'outil courant de recherche de l'information dans les bases de données. D'unemanière générale, la sélection :

·         s'appliquesoit à une seule table, soit à plusieurs tables liées par des relations ;

·         permetde sélectionner les lignes par application d'un ou plusieurs critères portantsur un ou plusieurs champs ;

·         permetde choisir les colonnes que l'on veut conserver (comme la sélectionsimple) ;

·         peutenregistrer son résultat sous forme d'une table ;

·         peutcréer une nouvelle colonne ;

·         peutêtre paramétrée.

Tout ce que nous avons exposéau chapitre 11 sur la sélection simple s'applique a fortiori à lasélection en général : choix des colonnes, requête multi table, créationde table, création de champ, tri, requêtes emboîtées. La formulation d'unerequête de sélection met en jeu des critères liés par des opérateurs logiques.Sa réalisation pratique pose des problèmes de syntaxe, qui sont propres au SGBDutilisé. A titre d'exemple, recherchons les clients du représentant Dupont, oude son collègue Durand, qui ont passé une commande de plus de 1.000 € lemois dernier. Dans une des tables de notre BDD se trouve une colonne"Représentant", et il faut que nous exprimions le fait que nousrecherchons les enregistrements qui possèdent le nom Durand, ce qui soulève unproblème de syntaxe (le nom "Durand" doit être mis entre guillemets).Ensuite, il faut que nous exprimions le fait que c'est "Durand" OU"Dupont", ce qui met en jeu l'opérateur logique OU. Dans une tablenommée "Commandes" existe un champ "Coût total", et il fautque nous exprimions le fait que ce coût est supérieur à 1.000 €, ce quimet en jeu l'opérateur de comparaison "supérieur à".

3 - La syntaxe

La syntaxe varie avec le typede données du champ sur lequel porte le critère :

·         unedonnée de type texte doit être écrite entre guillemets ("..."), etprécédée de l'opérateur "Comme". Cet opérateur peut être omis siaucun opérateur de comparaison n'est présent ;

·         lesnombres sont écrits tels quels ;

·         ladate et / ou l'heure doivent être placées entre dièses (exemple :#01/01/2003#). Dans certains SGBD, le dièse est remplacé parl'apostrophe ;

La valeur Null (case vide,pas de données) possède une syntaxe particulière. Pour détecter lesenregistrements dont un champ particulier est vide, il faut écrire : EstNull

 

Dans le cas contraire, ilfaut écrire : Est Pas Null

La casse n'a pasd'importance, le SGBD corrigeant de lui-même.

4 - Les caractèresgénériques

Pour exprimer le fait quenous recherchons les enregistrements qui possèdent la chaîne de caractères"truc" dans un champ donné, nous écrivons, conformément auxindications du paragraphe précédent :

Comme "truc"

L'application d'un critère àun champ de type texte recèle un piège particulier. Quand nous exprimons cecritère comme ci-dessus, nous ne sélectionnons que les enregistrementspossédant exactement la chaîne "truc" dans le champ considéré. Si lechamp contient "trucage", ou "le truc", l'enregistrementcorrespondant est ignoré. Il nous faut donc pouvoir préciser comment la chaînerecherchée se présente dans le champ : occupe-t-elle tout le champ,est-elle précédée ou suivie d'autres caractères, et (éventuellement) quel estleur nombre. Pour ce faire, nous utilisons des caractères génériques, c'est àdire des caractères qui peuvent remplacer un ou plusieurs autres caractères quelsqu'ils soient. Le caractère générique le plus fréquemment utilisé estl'astérisque, qui remplace un nombre quelconque de caractères. Ainsi : Comme"*truc" permet de sélectionner tout enregistrement dont le champconsidéré contient une chaîne de caractères se terminant par "truc",telle que "truc" et "le truc" par exemple. Par contre,"trucage" sera ignoré. De même : Comme "truc*" permetde sélectionner tout enregistrement dont le champ considéré contient une chaînede caractères commençant par "truc", telle que "truc" et"trucage". Par contre, "le truc" sera ignoré. Enfin : Comme"*truc*" permet de sélectionner tout enregistrement dont le champconsidéré contient la chaîne "truc", tel que "truc","trucage" et "le truc". L'astérisque peut être placéen'importe où, et non pas seulement en début ou en fin de chaîne.

Le second caractère générique(par fréquence d'usage) est le point d'interrogation, qui remplace un caractèrequelconque et un seul. Ainsi, le critère : Comme "c?d"  retrouvera par exemple cad, ced, cid, cod,mais pas cd car le point d'interrogation implique la présence d'un caractère. Sil'on veut rechercher l'astérisque ou le point d'interrogation dans un champ, ilfaut placer ces caractères entre crochets. Par exemple, la recherche du pointd'interrogation (placé n'importe où dans un champ) s'écrit : Comme"*[?]*"

Attention ! Les caractèresgénériques * et ? ne s'appliquent qu'à l'interrogation des champs de typetexte. Dans un champ de type Date/Heure, une expression telle que #**/**/2002#est considérée comme invalide par le SGBD Access. Notons de plus que, dans lesversions récentes de la plupart des SGBD, l'astérisque est remplacée par lepourcent (%) et le point d'interrogation par le caractère de soulignement (_).

5 - Les opérateurslogiques

Une requête un peu élaboréefait appel à plusieurs critères s'appliquant soit à un même champ, soit à deschamps distincts. Ces critères sont liés par des opérateurs logiques, dont lesplus utilisés sont ET, OU et PAS. L'utilisation de parenthèses permet dedéfinir l'ordre dans lequel s'appliquent les opérateurs. Les personnesfamiliarisées avec la recherche documentaire connaissent bien cette façon deprocéder, qui provient directement de la théorie des ensembles.

Dans Access, les opérateurslogiques Et et OU peuvent être écrits explicitement, ou être représentésgraphiquement dans la grille de l'interface graphique de création d'unerequête. L'opérateur PAS doit être écrit explicitement. Pour rechercher, dansle champ "Nom" d'une table intitulée "Personnes", lesindividus s'appelant Truc ou Machin, nous avons le choix entre les deuxsolutions que nous avons représentées ci-dessous (en détourant une partie de lagrille de définition de la requête) :

Nous voyons que l'opérateurOU peut être écrit explicitement (à droite), ou traduit graphiquement (àgauche). Le résultat de la requête est, bien entendu, le même dans les deuxcas. L'opérateur logique peut porter sur deux champs distincts. La traductiongraphique de l'opérateur OU est alors plus simple que son écriture explicite,comme le montrent les figures ci-dessous. On notera que le OU s'obtient en sedécalant d'une ligne... sinon c'est l'opérateur ET qui fonctionne !

L'opérateur ET peut lui aussiêtre traduit graphiquement ou écrit explicitement, comme le montrent lesfigures ci-dessous. La requête recherche les noms commençant par la lettre"m" et finissant par la lettre "n", et retrouve par exemple"Machin".

Notons que la requête auraitpu être formulée plus simplement : Comme "m*n".

L'opérateur logique peutimpliquer deux champs distincts, comme le montre l'exemple ci-dessous. La requêterecherche les enregistrements relatifs à une personne nommée Pierre Machin.

 

Conclusion : dans la grillede création d'une requête, le déplacement horizontal correspond à l'opérateurET, et le déplacement vertical correspond à l'opérateur OU. L'opérateur PAS estsans représentation graphique. Dans une requête complexe, l'application desopérateurs s'effectue ligne par ligne, comme le montre l'exemple ci-dessous.

La requête fonctionne selonl'expression ensembliste suivante :

((Personnes.Nom Comme"machin") ET (Personnes.Prénom Comme "pierre"))
OU
((Personnes.Prénom Comme "jacques") ET (Personnes.[Datenaissance]=#8/30/1975#))

c'est à dire que :

• l'expression qui se trouve sur la ligne "Critères :" est évaluée (elle réalise un ET) ;
• l'expression qui se trouve sur la ligne "Ou :" est évaluée (elle réalise aussi un ET) ;
• un OU est ensuite effectué entre les résultats des deux expressions précédentes.

6 - Les opérateurs de comparaison

Les opérateurs de comparaisonarithmétiques :

= (égal),< (inférieur), <= (inférieur ou égal), > (supérieur),>= (supérieur ou égal), <> (différent)

s'appliquent aux donnéesnumériques et monétaires, mais aussi aux dates et aux chaînes de caractères.Pour ces dernières, on notera que :

• le signe égal est équivalent à l'opérateur "Comme" ;
• le signe différent est équivalent à l'opérateur "Pas Comme".

Pour préciser un intervalle,on peut utiliser l'expression : Entre ... Et ... qui fonctionne avec les typesde données texte, date/heure et numérique/monétaire.

7 - Les fonctions

Pour exprimer des critères,on peut utiliser des fonctions, mais ces dernières sont spécifiques à la foisdu SGBD et du type de données du champ considéré. Nous consacrerons une annexeaux fonctions, et nous nous contenterons ici de citer quelques exemples (dontnous avons vérifié qu'ils fonctionnaient effectivement). Pour les champs enmode texte :

• NbCar([Nom])="4" retrouve les noms de 4 caractères ;
• Droite([Nom];2)="se" retrouve les noms se terminant par "se" ;
• Gauche([Nom];2)="du" retrouve les noms commençant par "du" ;
• ExtracChaîne([Nom];2;3)="ach" retrouve le nom "Machin", lequel contient la chaîne de 3 caractères "ach" en commençant au deuxième caractère.

Pour les dates et lesheures :

• PartDate("aaaa";[Date_commande])=2000 retrouve les commandes de l'année 2000. Cette fonction opère aussi avec "j" pour le jour, "m" pour le mois, et "t" pour le trimestre ;
• DiffDate("j";[Date_commande];[Date_livraison])>100 retrouve les produits qui ont été livrés plus de 100 jours après avoir été commandés. Cette fonction opère aussi avec "m" pour le mois, et avec "aaaa" pour l'année ;
• Jour([Date_commande])=12 retrouve les commandes effectuées le 12 (des mois présents dans la table) ;
• Mois([Date_commande])=6 retrouve les commandes du mois de juin ;
• Année([Date_commande])=2000 retrouve les commandes de l'année 2000 ;
• AjDate("j";-10;[Date_livraison]) fournit une date antérieure de 10 jours à la date de livraison.

Attention ! La francisationdes fonctions (date/heure) issues de VBA n'a pas toujours été effectuée parl'éditeur avec tout le sérieux nécessaire, et l'utilisateur ne doit pas êtresurpris s'il se heurte à des disfonctionnements. Ainsi la fonction : JourSem(#date#)  qui donne le numéro du jour d'une datedonnée, marche à l'américaine : le jour numéro 1 est le dimanche, etnon le lundi comme c'est le cas en Europe. Par contre, la fonction : WeekdayName(n° du jour)qui donne le nom du jour connaissant son numéro, fonctionne àl'européenne : le jour n° 1 est bien le lundi. Il en résulte quel'expression obtenue en emboîtant les deux fonctions précédentes : WeekdayName(JourSem(#date#))donne un résultat faux (le lundi à la place du dimanche, etc.). De même lesfonctions qui, dans leurs arguments, acceptent le jour ("j"), le mois("m"), le trimestre ("t") et l'année ("aaaa"),n'acceptent pas la semaine contrairement à ce qui se passe dans la versionanglophone d'Access.

Pour les champs de typenumérique ou monétaire, on trouve :

• les fonctions arithmétiques habituelles (somme, différence, produit, quotient) ;
• des fonctions mathématiques et statistiques ;
• des fonctions telles que Abs() (valeur absolue), Arrond() (partie entière), Ent() (partie entière, arrondie inférieurement pour les nombres négatifs), Aléat() (nombre aléatoire compris entre 0 et 1) et Sgn() (signe d'un nombre).

A la valeur particulière Nullcorrespond la fonction : EstNull([Nom d'un champ])

Elle retourne la valeur -1 sile champ est vide, et 0 (zéro) dans le cas contraire.

Attention ! Notez bien que,lorsqu'une fonction possède plusieurs arguments, le caractère séparateur est lepoint-virgule, alors que c'est la virgule dans la version anglophone d'Access.C'est un détail de syntaxe ridicule, mais il est à l'origine de bien des mauvaisessurprises.

8 - La requête desélection paramétrée

Soit une table contenantdiverses informations, dont une date, comme le montre l'exemple ci-dessous.

Imaginons que nous ayonsrégulièrement besoin des informations relatives à un jour donné. Nous pouvons,bien sûr, créer chaque fois une requête nouvelle, mais il est plus commoded'écrire une seule fois la requête et de paramétrer la valeur de la date. Dansla grille de création de la requête, la valeur du paramètre date est remplacéepar un message écrit entre crochets :

Si nous lançons la requête,la boite de dialogue suivante s'affiche :

Nous saisissons la date dansle format utilisé par la table (jj/mm/aaaa), et nous validons. Le SGBD afficheles lignes relatives à la date indiquée :

9 - Conclusion

Les SGBD mettent à ladisposition des utilisateurs des outils extrêmement variés pour exprimer lescritères utilisés dans l'élaboration d'une requête. En emboîtant, sinécessaire, plusieurs requêtes, les utilisateurs peuvent extraire des BDDtoutes les informations qu'ils désirent -- ou presque. Il est fort rare quel'on se trouve dans l'impossibilité réelle de transcrire un critère donné.

Pour qui fait l'effortd'apprendre à créer des requêtes, et obtient de son entreprise l'autorisationde s'en servir, il y a là une mine d'or... au sens de l'information tout au moins.

 

 

 

Chapitre 13 : la notion de jointure

1 - Introduction

Dans une base de donnéesrelationnelle, les informations sont réparties sur un grand nombre de tables.Il est donc fréquent qu'une requête porte sur deux tables (ou plus), liées parune (ou plusieurs) relation(s). La notion de jointure précise commentfonctionnent cette (ou ces) relation(s) lors de l'exécution de la requête. Commepour les autres chapitres de ce cours, nous utilisons le SGBD Access commesupport pratique. Pour bâtir un exemple, nous faisons appel aux deux tables"Personnes" et "Communes" dont nous nous sommes déjà servisau chapitre 4. Nous remplissons ces deux tables comme le montre la figureci-dessous. Précisons que le champ "Commune" de la table"Personnes" n'a pas été rendu obligatoire (le Null est autorisé), sibien qu'il peut arriver qu'une commune ne soit pas attribuée à une personne,comme c'est le cas pour Jeanne Dupont.

Les deux tables sont liéespar une relation, assurée via un code masqué. Cette relation apparaît dans lafenêtre "Relations", comme le montre la figure ci-dessous.

2 - La relation

Si nous ajoutons les deuxtables précitées à la fenêtre de création d'une requête, nous constatons que larelation qui les lie est toujours présente. Dans la fenêtre de création d'unerequête, nous pouvons supprimer cette relation. La procédure est identique àcelle pratiquée dans la fenêtre "Relations" : nous effectuons unclic droit sur la relation, et nous choisissons "Supprimer". Nousfermons la fenêtre de création de la requête, et nous enregistrons cettedernière. Si nous ouvrons la fenêtre "Relations", nous constatons quela relation qui lie les deux tables existe toujours. Cette relation est enquelque sorte une propriété des deux tables. La suppression que nous avonseffectuée est liée à une requête particulière. Elle n'a d'effet que lors del'exécution de la requête. Ce n'est pas une propriété des deux tables, mais unepropriété de la requête. En conclusion, les opérations que nous effectuons surles relations (création, suppression, modification des propriétés) ont uneffet :

·         permanentlorsqu'elles sont effectuées dans la fenêtre "Relations" ;

·         éphémèrelorsqu'elles sont effectuées dans la fenêtre de création d'une requêteparticulière.

Remarque : même s'il n'existepas de relation entre deux tables, le SGBD Access en crée une automatiquementlorsque vous ajoutez ces deux tables à la fenêtre de création d'une requête, àcondition que ces tables aient chacune un champ du même nom et du même type dedonnées, et qu'un des deux champs possède une clé primaire.

 

3 - Le produit vectoriel

Nous rouvrons la requêteprécédente en mode "Modification". Nous vérifions qu'aucune relationn'apparaît entre les deux tables. Dans la grille, nous introduisons les champs"Nom" et "Prénom" de la première table, et les champs"Commune" et "Code postal" de la seconde. La feuille dedonnées résultante contient 20 lignes ! Que s'est-il passé ?

Le SGBD a associé chaqueligne de la première table (il y en a 4) à chaque ligne de la seconde (ily en a 5). On dit qu'il a effectué le produit vectoriel des deux tables.L'absence de relation fait que le SGBD ne sait pas comment il doit associer leslignes des deux tables ; de ce fait, il réalise toutes les combinaisonspossibles. Il faut faire attention au fait que le produit vectoriel peut nousconduire à créer des tables gigantesques : le produit de deux tablescontenant chacune 1.000 enregistrements est une table possédant1 million de lignes ! En pratique, on n'utilise pas le produitvectoriel, sauf dans des cas très rares, comme par exemple pour réunir dans uneseule table des comptages isolés. Ces derniers se présentent en effet sousforme de tables à une seule ligne, et l'on peut en faire le produit vectorielsans risque, car le résultat est alors une table à une seule ligne.

4 - La jointure interne

Dans la fenêtre de créationde la requête, nous rétablissons la relation entre les deux tables. Cette fois,la feuille de données résultante ne contient plus que 3 lignes, comme lemontre la figure ci-dessous.

Nous constatons que nefigurent dans la table résultante que les enregistrements qui sont présentsdans les deux tables. La personne Dupont Jeanne, dont la commune n'est pasprécisée, est absente du résultat. Les villes Grenoble (38001) et SMH,auxquelles ne correspond aucune personne, sont également absentes. Le SGBD atraité la relation entre les deux tables comme une jointure interne.

Effectuons un clic droit surla relation, et sélectionnons "Propriétés de la jointure". La fenêtredu même nom s'affiche ; elle se présente comme le montre la figure ci-dessous.Bien que le terme ne soit pas présent, l'option 1 de la fenêtre correspondeffectivement à la jointure interne.

Remarque : dans la requêteprécédente, le champ "Commune" est issu de la table"Communes". S'il provenait de la table "Personnes", lerésultat s'afficherait de la même façon. C'est seulement en exportant la tableque l'on peut s'apercevoir que dans le second cas, le champ contient un code aulieu d'un nom de commune.

5 - La jointure gauche

La fenêtre "Propriétésde la jointure", représentée ci-dessus, nous fournit deux autres options.Nous activons le bouton 2 et nous validons par "OK". La requêtese présente maintenant comme le montre la figure ci-dessous. Nous avons affaireà une jointure gauche. Pour le signaler, la liaison prend la forme d'uneflèche... dirigée vers la droite.

Si nous basculons en modefeuille de données, nous obtenons le résultat suivant :

Cette fois, le SGBD a conservétous les enregistrements de la table "Personnes", et il leur aassocié les enregistrements disponibles dans la table "Communes".Comme nous n'avons pas précisé de critère de sélection, tous cesenregistrements ont été conservés.

6 - La jointure droite

Dans la fenêtre"Propriétés de la jointure", nous activons le bouton 3 et nousvalidons par "OK". Nous avons maintenant affaire à une jointuredroite. Pour le signaler, la liaison prend la forme d'une flèche... dirigéevers la gauche. Si nous basculons en mode feuille de données, nous obtenons lerésultat suivant :

Cette fois, le SGBD aconservé tous les enregistrements de la table "Communes", et il leura associé les enregistrements disponibles dans la table "Personnes".Comme nous n'avons pas précisé de critère de sélection, tous cesenregistrements ont été conservés. Conclusion : le résultat d'une requêtemulti-table dépend du type de jointure choisi. Par défaut, c'est la jointureinterne qui s'applique.

7 - La requête de noncorrespondance

La requête de noncorrespondance constitue une application importante de la notion de jointure.Elle met en jeu deux tables ayant en commun un champ possédant le même type dedonnées, et doté des mêmes propriétés (mais pas forcément du même nom). Larequête de non-correspondance ne conserve un enregistrement de la premièretable que si le contenu du champ n'est pas présent dans la seconde table. Lesdeux tables n'ont pas besoin d'être liées au préalable par une relation, cettedernière sera créée en même temps que la requête. Pour construire un exemplesimple, nous créons deux tables à un seul champ, contenant des prénoms, etintitulées "Prénoms1" et "Prénoms2". Les tables seprésentent ainsi :

 

Nous recherchons les prénomsde la première table qui sont absents de la seconde. Pour ce faire, nous devonspasser en revue tous les prénoms de la première table, et regarder s'ils sontou non dans la seconde. Pour créer la requête correspondante, nous songeonsdonc à utiliser une jointure gauche.

Pour bien comprendre ce quise passe, nous pouvons décomposer en deux temps le fonctionnement de larequête. D'abord, le SGBD sélectionne tous les prénoms de la première table, etleur associe les prénoms de la seconde table quand ils sont identiques. Lerésultat de cette première étape peut être représenté ainsi :

Il faut maintenant que leSGBD applique un critère de sélection, pour ne conserver que les lignes dont ladeuxième colonne est vide. Nous plaçons donc le critère "Est Null"dans la colonne relative au champ "Prénom" de la seconde table. Endéfinitive, nous obtenons la requête représentée sur la figure ci-dessous(remarquez la flèche qui traduit la jointure gauche). Nous avons supprimél'affichage de la seconde colonne, car il conduirait à créer une colonne videdans la feuille de données résultante.

Si nous basculons en modefeuille de données, nous obtenons le résultat suivant :

 

Il est indispensable, dans unrequête de non correspondance, de ne pas se tromper sur le type de jointure àutiliser. Ainsi, si nous choisissons la jointure interne (par défaut), le SGBDne sélectionne que les prénoms qui sont simultanément présents dans les deuxtables. Le résultat de cette étape intermédiaire est représentéci-dessous :

Lorsque nous appliquons lecritère "Est Null" à la deuxième colonne,  le SGBD ne conserveque les lignes pour lesquelles la deuxième colonne est vide. Comme il n'y en apas, la feuille de données résultante ne contient aucun enregistrement -- ceque l'expérience confirme. Si nous utilisons la jointure droite, le SGBDsélectionne tous les prénoms de la seconde table, et seulement ceux de lapremière table qui se trouvent dans la seconde. Cette étape intermédiaire peutêtre représentée ainsi :

Puis le SGBD élimine leslignes pour lesquelles la seconde colonne est vide. Comme il n'y en a pas, lafeuille de données résultante ne contient aucun enregistrement -- ce quel'expérience confirme.

Si le raisonnement relatif àla requête de non correspondance vous paraît ardu, ne vous inquiétez pas :vous n'êtes pas le seul. C'est la raison pour laquelle Microsoft a créé unassistant pour ce type de recherche. Vous le trouverez dans la fenêtre"Base de données", l'objet "Requêtes" étant sélectionné.Vous cliquez sur l'icône "Nouveau", et vous sélectionnez"Assistant Requête de non-correspondance".

8 - La requête decorrespondance

La requête de correspondanceest en quelque sorte le complément de la précédente. Elle met en jeu deuxtables ayant en commun un champ possédant le même type de données, et doté desmêmes propriétés (mais pas forcément du même nom). Elle ne conserve unenregistrement de la première table que si le contenu du champ est présent dansla seconde table. La requête de correspondance constitue elle aussi uneapplication courante de la notion de jointure. Comme précédemment, les deuxtables n'ont pas besoin d'être liées au préalable par une relation, cettedernière étant créée en même temps que la requête. Cette fois, nous recherchonsles prénoms de la première table qui sont présents dans la seconde. Pour créerla requête correspondante, il nous faut utiliser une jointure interne. Celasuffit, il n'est pas utile de préciser un critère. Nous obtenons ainsi larequête représentée sur la figure ci-dessous.

Si nous basculons en modefeuille de données, nous obtenons le résultat suivant, complémentaire de celuiobtenu avec la requête de non correspondance :

 

 

Exercez-vous à prévoir ce quise passe si vous vous trompez de jointure, et vérifiez si l'expérience confirmevos prédictions. La requête de correspondance étant plus facile à créer que larequête de non-correspondance, l'éditeur d'Access n'a pas prévu d'assistantpour aider à la créer. Cependant, vous pouvez utiliser l'assistant précédent,et changer simplement la condition "Est Null" par son contraire"Est Pas Null". C'est inutilement compliqué, mais cela marche.

9 - Conclusion

La notion de jointure joue unrôle important dans les requêtes multi-tables, en particulier dans les requêtesde correspondance et de non correspondance.

Si l'opérateur ne précise pasle type de jointure, c'est la jointure interne que le SGBD applique par défaut.

Chapitre 14 : la requête de regroupement

1- Introduction

La requête de regroupementest un important outil d'analyse et de synthèse. Pour cette raison, nous luiconsacrons un chapitre entier. Le terme "Requête de regroupement" estle plus courant , mais on rencontre aussi "Requête d'agrégation", quiest synonyme.

Les requêtes de regroupementsont très utilisées dans l'analyse des résultats comptables et financiers.Comme nous le verrons dans le chapitre suivant, elles sont aussi utilisées pourle comptage et l'élimination des doublons.

La notion de regroupementdéborde le cadre des seules requêtes. Nous la retrouverons également dans lesétats et les formulaires.

Comme pour les autreschapitres de ce tutoriel (ou tutorial, ou cours en ligne), nous utilisons leSGBD Access comme support pratique.

2 - La notion deregroupement

Créons unetable intitulée "Résultats", contenant le chiffre d'affairesjournalier d'une entreprise possédant trois agences. Le champ "Date"est du type "Date/Heure", le champ "Agence" du type texte(10 caractères), et le champ CA (Chiffre d'Affaires) du type monétaire(format euro). Introduisons quelques valeurs dans la table, qui prend alorsl'aspect représenté ci-dessous.

Pour juger les performancesde l'entreprise, ces données brutes sont malcommodes. Un décideur a besoin duchiffre d'affaires non seulement au jour le jour, mais aussi à la semaine, aumois et pour l'exercice annuel. Il le veut toutes agences confondues pour jugerdes performances de l'entreprise. Il le veut aussi agence par agence, pourjuger des performances de ces dernières (le responsable de l'agence Est vaprendre un savon). Et il ne veut pas être obligé de sortir sa calculette pourregrouper les chiffres qui l'intéressent ; le regroupement doit êtreeffectué par le SGBD.

Pour l'exemple très simpleque nous avons choisi, deux regroupements du chiffre d'affaires sontpossibles :

·         pardate, en sommant les CA des trois agences, de manière à obtenir le CA quotidiende l'entreprise. Dans ce cas, la notion d'agence s'efface ;

·         paragence, en sommant les CA de chaque agence sur l'ensemble des dates mentionnéesdans la table. Dans ce cas, la notion de date s'efface.

Quandpeut-on envisager d'effectuer un regroupement dans une table ?

·         Quandil existe un champ possédant des doublons. Dans l'exemple ci-dessus, il seraitimpossible de regrouper par date si chaque valeur de la date n'apparaissaitqu'une seule fois. De même, il serait impossible d'envisager le regroupementpar agence, si le nom de chaque agence n'apparaissait pas de manière répétée.

Quelleopération peut-on envisager quand on effectue un regroupement ? La naturede cette opération dépend du type des données à regrouper :

·         desdonnées numériques ou monétaires se prêtent à des opérations arithmétiques(somme, moyenne, minimum, maximum), statistiques (variance et écart-type),voire mathématiques. Tout dépend des possibilités offertes par le SGBD ;

·         desdonnées de type texte se prêtent au classement et au comptage (la concaténationn'est pas prévue).

Nousvoyons tout de suite qu'une requête de regroupement met en jeu le plus souventdeux colonnes :

·         unecolonne sur laquelle s'effectue le regroupement (elle doit contenir desdoublons). On peut effectuer le regroupement sur plusieurs colonnes lorsqu'ilexiste des doublons s'étendant sur plusieurs colonnes ;

·         unecolonne sur laquelle s'effectue une opération (somme, ou moyenne, ou etc.).

La mise aupoint d'une requête de regroupement peut s'avérer délicate, et il faut garderen mémoire les observations suivantes :

·         Regroupement.Le SGBD permet d'effectuer le regroupement sur plusieurs colonnes, mais laprobabilité pour qu'il existe des doublons (sur plusieurs colonnes) diminuetrès vite avec le nombre de ces dernières. Dans beaucoup de cas rencontrés enpratique, on effectue le regroupement sur une colonne seulement ;

·         Opérations.On peut envisager d'effectuer des opérations sur plusieurs colonnes, si elless'y prêtent. Dans l'exemple ci-dessus, le CA pourrait être ventilé sur deuxcolonnes (l'une pour les biens, l'autre sur les services, par exemple), quenous pourrions sommer séparément ;

·         Requêtemulti-table. Une requête de regroupement peut impliquer plusieurs tables liéespar des relations, mais il est alors beaucoup plus facile de commettre deserreurs de conception. Il est donc prudent d'utiliser d'abord une requête de sélectionpour regrouper dans une même table les données dont on a besoin, avantd'appliquer la requête de regroupement, même si cela risque d'augmenter un peule temps d'exécution.

Il résulte de cesconsidérations qu'une requête de regroupement met généralement en jeu un nombretrès restreint de champs. En fait, il est fortement conseillé de commencer lamise au point d'une requête de regroupement sur deux colonnes seulement, et queces deux colonnes appartiennent à la même table (ou à la même feuille de données).

3 - La création de larequête

Nous allons créer le premierregroupement envisagé au paragraphe précédent (calcul du CA quotidien del'entreprise). Pour ne pas nous tromper, nous allons opérer de manièreméthodique.

Première étape. Elle consisteà ouvrir la fenêtre de définition d'une requête, et à y introduire la table surlaquelle on veut effectuer l'opération de regroupement (ici"Résultats").

Seconde étape. Elle consisteà introduire dans la grille le champ sur lequel s'effectue le regroupement. Commenous cherchons à calculer des CA quotidiens, ce champ ne peut être que la date.Nous introduisons donc le champ "Date" dans la grille de création dela requête.

Troisième étape. Il fautsignifier au SGBD que la requête implique un regroupement sur le champ"Date". Pour ce faire, nous cliquons sur l'icône "Totaux". Une nouvelle ligne,baptisée "Opération :", apparaît dans la grille de définition dela requête, entre "Table :" et "Tri :" (figuresci-dessous). La valeur par défaut, pour le champ "Date", estjustement "Regroupement" (si cette valeur n'apparaît pas, nouscliquons sur la ligne et nous sélectionnons "Regroupement" dans laliste déroulante). Ainsi, le regroupement sera effectué sur la date.

Quatrième étape. Il fautmaintenant introduire le champ sur lequel s'effectue l'opération liée auregroupement. Dans le présent exemple, l'opération consiste à sommer les CA dechaque agence. Nous introduisons donc le champ "CA" dans la grille.

Cinquième étape. Il fautindiquer au SGBD à quelle opération il doit procéder sur le champ"CA". Nous cliquons sur la ligne "Opération :", nousutilisons la liste déroulante pour remplacer "Regroupement" (qui s'estinscrit par défaut) par "Somme".

La requête se présente ainsicomme le montre la figure ci-dessous à gauche.

  

Quand nous basculons en modefeuille de données, nous obtenons le résultat représenté sur la figureci-dessous (à gauche). Nous vérifions que le SGBD a bien calculé, pour chaquedate, la somme des chiffres d'affaires des trois agences.

  

De la même façon, nouspouvons regrouper le chiffre d'affaires par agence. La requête et son résultatsont représentés dans les deux figures ci-dessus (à droite). Cette fois le SGBDa calculé, pour chaque agence, la somme des chiffres d'affaires quotidiens,pour les deux dates figurant dans la table.

Remarque 1 : l'icône fonctionne comme un commutateur. Si nouscliquons dessus alors que la ligne "Opération :" est présente,celle-ci disparaît, et vice versa.

Remarque 2 : le nom du champ("SommeDeCA") a été attribué par le SGBD, mais on peut le changer àvolonté dans la grille de création de la requête. Pour l'appeler "CAagence", par exemple, il faut remplacer "CA" sur la ligne"Champ :" par "CA agence: CA".

 

4 - Les opérations sur lescolonnes

Dans leparagraphe précédent, nous avons créé des requêtes impliquant un regroupementsur une colonne donnée, ce qui nous a permis de calculer le chiffre d'affairespar date (pour chacune des trois agences) ou par agence (pour chacune des deuxdates). Mais nous pouvons également avoir besoin du chiffre d'affaire total,pour toutes les agences et toutes les dates de la table. Pour ce faire, nouscréons de nouveau une requête de regroupement, mais sans déclarer sur quelchamp nous regroupons, comme le montre la figure ci-dessous à gauche. Vousnoterez que nous avons utilisé la syntaxe qui nous permet d'imposer le nom duchamp (CA_total).

  

Le résultatfigure ci-dessus à droite ; vous pourrez vérifier qu'il est bien exact. Bienentendu, vous pouvez utiliser d'autres fonctions que la somme dans cesopérations effectuées verticalement. L'étude de ces fonctions fait l'objet duparagraphe suivant.

Ainsi vouspouvez effectuer des opérations sur les colonnes d'une table, comme vous leferiez dans un tableur. La différence avec un tableur -- outre la syntaxe --provient du fait que le résultat ne peut pas être enregistré dans la table. Ilapparaît systématiquement dans une nouvelle "feuille de données"volatile, à moins que vous ne demandiez que la requête crée une nouvelle table.

5 - Les fonctions

Lafonction "Somme" (qui ne s'applique qu'aux données numériques etmonétaires) n'est pas la seule qui puisse être utilisée lors du regroupement.Dans Access, on trouve également les fonctions suivantes :

·         Moyenne: calcule la moyenne. S'applique uniquement aux données numériques oumonétaires ;

·         Min: retient seulement la valeur la plus basse. S'applique aussi au texte(classement alphabétique) ;

·         Max: retient seulement la valeur la plus haute. S'applique aussi au texte(classement alphabétique) ;

·         ÉcartType: calcule l'écart type. S'applique uniquement aux données numériques oumonétaires ;

·         Var: calcule la variance. S'applique uniquement aux données numériques oumonétaires ;

·         Premier: retient la première valeur rencontrée (en parcourant la table du haut enbas). S'applique aussi au texte ;

·         Dernier: retient la dernière valeur rencontrée (en parcourant la table du haut enbas). S'applique aussi au texte ;

·         Compte: compte le nombre de doublons dans le regroupement. Nous dédierons un chapitreparticulier à l'étude de cette fonction, qui s'applique à tous les types dedonnées.

La listedes fonctions utilisables lors du regroupement varie d'un SGBD à l'autre. Lasomme, la moyenne et le comptage sont présents dans tous les SGBD.

Si nousessayons de faire opérer une fonction sur un type de données incompatible, leSGBD Access affiche le message d'erreur suivant : "Type de donnéesincompatible dans l'expression du critère". Et qu'en termes galants ceschoses-là sont dites ! Évidemment, il ne s'agit pas d'un critère, maisd'une fonction.

On trouveraci-dessous des exemples illustrant l'application de ces différentes fonctions,à l'exception de l'écart type et de la variance, qui n'auraient guère de sensvu le petit nombre de données (trois par regroupement) contenues dans la table"Résultats" qui nous sert d'exemple.

Date	MoyenneDeCA		Agence	MoyenneDeCA
06/01/2003	862,26 €		Est	621,19 €
07/01/2003	1 156,07 €		Nord	1 156,29 €
			Sud	1 250,01 €

 

Date	MinDeCA		Agence	MinDeCA
06/01/2003	561,29 €		Est	561,29 €
07/01/2003	681,09 €		Nord	927,02 €
			Sud	1 098,46 €

 

Date	MaxDeCA		Agence	MaxDeCA
06/01/2003	1 098,46 €		Est	681,09 €
07/01/2003	1 401,56 €		Nord	1 385,55 €
			Sud	1 401,56 €

 

 

 

 

 

Date	PremierDeCA		Agence	PremierDeCA
06/01/2003	927,02 €		Est	561,29 €
07/01/2003	1 385,55 €		Nord	927,02 €
			Sud	1 098,46 €

 

Date	DernierDeCA		Agence	DernierDeCA
06/01/2003	561,29 €		Est	681,09 €
07/01/2003	1 401,56 €		Nord	1 385,55 €
			Sud	1 401,56 €

 

6 - Le filtrage d'unerequête de regroupement

Une requêtede regroupement peut être filtrée avant et /ou filtrée après le regroupement.Il faut indiquer au SGBD dans quel cas on entend se trouver.

Le filtrageaprès regroupement. Ce filtrage ne pose pas de problème particulier, puisqueles champs sur lesquels nous pouvons opérer sont présents dans la grille dedéfinition de la requête, et que cette dernière comporte une ligne"Critères :". Nous y inscrivons les critères de filtrage, enrespectant les règles de syntaxe propre au SGBD, comme nous avons appris à lefaire au chapitre précédent.

Dans lacolonne "CA", par exemple, le critère :

>2000

limiteral'affichage aux regroupements conduisant à des chiffres d'affaires supérieurs à2.000 euros. Dans la colonne "Agence", le critère :

Comme "Nord"

limiterale calcul du chiffre d'affaire cumulé par agence à la seule agence Nord. Dansla colonne "Date", le critère :

<#08/01/2003#

limitera lecalcul du chiffre d'affaire cumulé par date aux jours précédant le8 janvier 2003.

Lefiltrage avant regroupement. Pour filtrer une requête avant regroupement, nousprocédons ainsi :

·         nousintroduisons le champ dans la grille ;

·         nouscliquons sur la ligne "Opération :", où "Regroupement"s'est inscrit par défaut ;

·         noussélectionnons "Où" tout en bas de la liste déroulante. Nousconstatons alors que la coche disparaît de la case qui se trouve sur la ligne"Afficher :". Attention ! si nous oublions d'inscrire"où" sur la ligne "Opération :", la requête nefonctionnera pas ;

·         nousintroduisons le(s) critère(s) de filtrage sur la ligne"Critères :".

Avant defiltrer une requête de regroupement, il faut bien réfléchir au résultat quel'on veut obtenir. Car un même critère, appliqué au même champ, ne donnera pasle même résultat suivant qu'il opère avant ou après le regroupement. Pour lemontrer, nous avons créé deux exemples dans lesquels le filtre consiste à neconserver un CA que s'il est supérieur à 1000 euros, et où le regroupements'effectue sur l'agence.

·         Premierexemple (figures de gauche). Nous appliquons le filtre aux CA regroupés paragence, c'est à dire que nous ne les affichons que s'ils dépassent1000 euros ;

·         Deuxièmeexemple (figures de droite). Nous appliquons le filtre aux CA quotidiens, etnous ne les prenons en compte que s'ils dépassent 1000 euros.

Lesrequêtes et leurs résultats sont représentés sur les figures ci-dessous.

  

  

Si lecritère porte sur le champ de regroupement, l'application du filtre avant ouaprès le regroupement donne le même résultat. Faites l'expérience !

 

7 - Conclusion

La requêtede regroupement est un outil fort utile. Mais, pour l'utiliser correctement, ilfaut prendre les précautions suivantes :

·         poureffectuer des opérations sur les colonnes, il ne faut pas préciser sur quelchamp s'opère le regroupement ;

·         ilfaut appliquer le regroupement à un seul champ, ou à un très petit nombre dechamps, sinon il n'y a plus de regroupement possible et la requête, bienévidemment, ne regroupe plus rien

·         ilfaut, avant d'appliquer un filtre, décider s'il doit agir avant ou après leregroupement. Le résultat, en effet, ne sera pas le même dans les deux cas,sauf si le filtre est appliqué au champ de regroupement.

Leregroupement avec comptage, et son application au traitement des doublons, sontabordés au chapitre suivant.

Chapitre 15 : le comptage et l'élimination desdoublons

1 -Introduction

Nouspoursuivons notre étude du regroupement (commencée au chapitre précédent) dansle cas particulier où la fonction utilisée est "Compte". Cettefonction n'effectue pas d'opération ; comme son nom l'indique, ellefournit le nombre d'enregistrements regroupés, dans la colonne où on luidemande d'opérer.

Pour créerdes exemples de requête, nous utilisons la table du chapitre précédent, danslaquelle nous avons supprimé une donnée (le chiffre d'affaires de l'agence Estpour la journée du 7 janvier 2003 n'a pas été communiqué au siège del'entreprise -- quelqu'un va se faire tirer les oreilles). La table estreprésentée ci-dessous.

Comme pourles autres chapitres de ce cours, nous utilisons le SGBD Access comme supporttechnique.

2 - Leregroupement avec comptage

Nous créonsune requête dans laquelle :

·         leregroupement est effectué sur le champ "Date" ;

·         l'opérationde comptage concerne le champ "CA".

Les deuxfigures ci-dessous représentent la requête de regroupement (à gauche), et lafeuille de données résultante (à droite).

Nousremarquons immédiatement que la fonction "Compte" ne prend pas encompte les enregistrements vides. Il est d'usage de dire que la fonction"Compte" ignore les "Null".

 

3 - Lecomptage des doublons

Nouspouvons appliquer la fonction "Compte" au champ de regroupementlui-même. Dans le cas du champ "Date", les deux figures ci-dessousreprésentent la requête de regroupement (à gauche), et la feuille de donnéesrésultante (à droite).

Nousdisposons ainsi d'un moyen de compter les doublons -- à condition d'admettrequ'une valeur qui n'apparaît qu'une seule fois (et sera comptée comme telle)fait partie des doublons. Si nous prenons le terme "doublon" au sensstrict, nous devons rajouter, dans la colonne de comptage, sur la ligne"Critères :", un filtre qui ne conserve que les valeurssupérieures à l'unité (>1). Nous reconnaissons au passage un exemple defiltrage après regroupement.

Le SGBDAccess possède un assistant de comptage des doublons, et il est instructif deregarder comment il fonctionne. Dans la fenêtre "Base de données",nous sélectionnons l'objet "Requêtes", nous cliquons sur l'icône "Nouveau", nous sélectionnons"Assistant Requête trouver les doublons", et nous cliquons sur"OK". Nous demandons le comptage des doublons sur le seul champ"Date", et nous observons la manière dont la requête est formulée(figure ci-dessous).

Surprise !Le regroupement et le comptage sont bien effectués sur le même champ"Date", mais le regroupement et l'affichage du champ sont disjoints.Cette complication, qui apparaît comme inutile, trouverait-elle sa source dansle désir d'assurer la compatibilité avec une ancienne version d'Access, ou avecd'autres SGBD ? L'auteur de ces lignes donne sa langue au chat.

4 -L'élimination des doublons

De la tablequi nous sert d'exemple, extrayons une nouvelle table ne contenant que lacolonne "Agence". Cette table contient des doublons, puisque toutesles agences y sont citées deux fois. Il suffit d'opérer une sélection avecregroupement, mais sans comptage des doublons, pour éliminer les doublons de latable. Le regroupement est une bonne technique de dédoublonnage, que nous avonsdéjà utilisée au chapitre 11, pour un exemple de doublons sur deux champs.La table de départ, la requête et son résultat sont représentés ci-dessous.

 

Soyonsconcis : pour dédoublonner, il suffit de regrouper sans compter.

5 - Lecomptage sans regroupement

En faisantexactement l'inverse, c'est à dire en comptant sans regrouper, nous déterminonsle nombre d'enregistrements (non Null) présents dans une ou plusieurs colonnes.La figure ci-dessous représente la requête correspondante (en haut) et lafeuille de données résultante (en bas). Au passage, nous vérifions que, dans lacolonne CA, la fonction "Compte" a négligé la case vide.

Nous pouvons ainsi déterminer le nombred'enregistrements d'une table, à condition que nous soyons sûrs que la colonneutilisée pour l'opération ne contient pas de case vide. Nous pouvons songer àutiliser une colonne dans laquelle le Null est interdit, mais il n'en n'existepas toujours. Pour nous affranchir du comptage sur une colonne particulière,nous procédons comme suit :

·         nousajoutons la table "Résultats" dans la fenêtre de création de larequête, mais nous n'introduisons aucun champ dans la grille ;

·         surla ligne "Champ :", nous inscrivons le nom de la colonne danslaquelle figurera le résultat du comptage, soit par exemple"Comptage" ;

·         nousfaisons suivre ce nom de deux points et d'un espace. Le SGBD sait alors que lasuite définit le contenu du champ ;

·         àla suite, nous écrivons la fonction Compte(*). L'astérisque indique au SGBDqu'il doit compter les lignes de la table, sans utiliser de colonneparticulière ;

·         surla ligne "Table :", nous n'inscrivons rien. En effet, la colonneque nous allons créer n'appartient à aucune table. Par contre, le SGBD sait quela fonction "Compte(*)" s'applique à la table "Résultats",qu'il était donc indispensable d'introduire dans la fenêtre

Le principe de l'opération est simple. Nous créons unerequête sélection sans introduire aucun des champs de la table sélectionnée.Nous demandons à cette requête de créer un nouveau champ, auquel nous donnonsun nom (indispensable pour l'affichage). Nous définissons le contenu de cenouveau champ à l'aide la fonction "Compte()" qui compte des lignes.Nous utilisons l'astérisque comme argument, de telle sorte que la fonctioncompte les lignes de la table. La requête affiche une feuille de donnéespossédant une seule colonne (puisque nous l'avons définie ainsi), et une seuleligne (puisque la fonction fournit une valeur unique).

Les figures ci-dessous représentent la requête (àgauche) et la feuille de données résultante (à droite).

Cette méthode compte effectivement les lignes d'unetable, même si elles sont toutes vides. Vous pouvez faire l'expérience eneffaçant tout ce que contient la table "Résultats" (après en avoirgardé copie).

Remarque : si nous remplaçonsl'astérisque par le nom de l'un des champs de la table "Résultats"(mis entre crochets pour respecter la syntaxe), nous obtenons le résultat duparagraphe précédent, c'est à dire le comptage des enregistrements (non vides)du champ considéré.

6 -Conclusion

Comme vous pouvez le constater, cette page est pleinede ressources. Elle vous montre comment :

·         compterles enregistrements regroupés ;

·         compterles doublons ;

·         éliminerles doublons ;

·         compterles enregistrements (non vides) dans une colonne ;

·         compterles lignes dans une table.

Le comptage des doublons, et leur élimination, sontdes techniques importantes à connaître, car elles sont souvent utilisées pourla maintenance des bases de données et l'interrogation de leur contenu.

Chapitre 16 : les requêtesajout et analyse croisée

1 -Introduction

Nous avonsconsacré 5 chapitres à la requête de sélection et à ses diversdéveloppements. Nous avons d'abord étudiée sa forme élémentaire (la sélectionsimple), puis sa forme générale (la sélection avec critères). Nous avonsensuite découvert la notion de jointure, qui s'introduit naturellement lorsquela sélection porte sur plusieurs tables, et de là nous sommes passés à lacorrespondance et à la non-correspondance. Nous avons enfin perfectionné lasélection grâce à la notion de regroupement, ce qui nous a permis d'effectuerdes synthèses, et de manipuler les doublons. Bref, comme nous pouvons leconstater, la sélection est la reine des requêtes !

Cependant,la sélection ne peut pas tout faire, et sous la pression des besoins, d'autrestypes de requête ont été créés. Nous en avons rassemblé deux dans ce chapitre(l'ajout et l'analyse croisée), et deux dans le chapitre suivant (lasuppression et la mise à jour). La requête analyse croisée est une spécificitéd'Access, et on ne la retrouve généralement pas dans les autres SGBD. Pour lesafficionados du SQL, la requête ajout n'est pas connue sous ce nom ; elleest simplement considérée comme un cas particulier d'utilisation de la commandeINSERT.

Il resteune grande absente, la requête union, qu'on ne peut pas créer dans la fenêtregraphique d'Access, mais que nous traiterons lorsque nous étudierons le langageSQL. Comme pour les autres chapitres de ce cours, nous utilisons le SGBD Accesscomme support pratique.

2 - Lefonctionnement de la requête ajout

La requêteajout permet d'insérer les enregistrements d'une table n° 2 dans une tablen° 1. L'opération ne peut se faire que si les deux tables ont au moins unchamp commun (même nom, même type de données ou conversion de type possible --cela dépend du SGBD). Comme le montre la figure ci-dessous, les champs de latable n° 2 qui ne sont pas communs avec ceux de la table n° 1 sontignorés ou refusés (ex : le champ "T"). Les champs de la tablen° 1 qui n'existent pas dans la table n° 2 ne sont pas renseignés(ex : le champ "W") -- à moins que le champ ne soit du type NuméroAuto,auquel cas le système le remplira lui-même, comme nous le constaterons dans unprochain exemple.

Attention !la requête ajout modifie irréversiblement la table à laquelle on ajoute desdonnées (la table n° 1 dans la terminologie du paragraphe ci-dessus).L'opération une fois effectuée, il n'est plus possible de revenir en arrière.Il est donc très fortement recommandé de créer une copie de la table n° 1avant de procéder à l'ajout. La table que l'on ajoute (la table n° 2)n'est ni modifiée, ni supprimée, au cours de l'opération.

Pour créerune requête ajout dans le SGBD Access, nous introduisons la table à ajouter (latable n° 2 dans notre terminologie) dans la fenêtre decréation/modification d'une requête, et nous sélectionnons les champs que nousvoulons -- ou que nous pouvons -- ajouter. Puis nous cliquons sur la petiteflèche qui borde l'icône "Type de requête" et, dans la listedéroulante qui s'affiche, nous choisissons "Requête Ajout...". Dansla boite de dialogue "Ajout" qui s'ouvre, nous précisons quelle estla table dans laquelle doit s'effectuer l'ajout (la table n° 1 dans notreterminologie). La grille de définition de la requête acquiert alors une lignesupplémentaire intitulée "Ajouter à :", comme le montre la figureci-dessous.

Les donnéesde la table n° 2 seront effectivement ajoutées à la table n° 1lorsque nous exécuterons la requête. Des messages nous avertiront de ce qui sepassera -- à moins que nous n'en ayons décidé autrement dans les options(onglet "Modifier/Rechercher", cadre "Confirmer").

Diversessophistications sont possibles. Nous pouvons :

·        sélectionnerune partie seulement des champs de la table n° 2 ;

·        sélectionnerà l'aide de critères les enregistrements de la table n° 2 qui doivent êtreajoutés à la table n° 1 ;

·        remplacerla table n° 2 par une requête ;

·        faireen sorte qu'une requête (mono ou multi-table) effectue également un ajout dansune autre table.

Il nousfaut cependant bien veiller à ce que les colonnes qui sont utilisées pourdéfinir les opérations de sélection, mais qui ne sont pas concernées parl'ajout, ne contiennent aucune information sur la ligne "Ajouter à:", sinon le SGBD Access nous gratifiera d'un message d'erreur qui nousplongera dans des abîmes de réflexion (exemple à méditer :"Destination de sortie 'requête' répliquée"). Voici une liste nonlimitative des diverses utilisations de la requête ajout :

·        rassemblerdans une même table des enregistrements provenant de tables séparées. Danscette application, la requête ajout entre en concurrence avec la requête union,que nous étudierons dans l'un des chapitres consacrés au SQL (chapitre 22).Attention : les deux requêtes n'imposent pas les mêmes contraintes, et nefournissent pas forcément le même résultat (problème des doublons) ;

·        imposerà une table des propriétés particulières, en l'ajoutant à une tablemodèle,  initialement vide et dont les propriétés sont soigneusementdéfinies (largeur et visibilité des colonnes, tri, police de caractères,etc.) ;

·        gardertrace d'un classement dans une table ;

·        etc.

Dans leparagraphe suivant, nous examinerons quelques exemples d'utilisation de larequête ajout.

3 -L'utilisation de la requête ajout (exemples)

Notrepremier exemple illustre simplement la procédure exposée ci-dessus. La figuresuivante représente le contenu des deux tables avant et après l'ajout.

 

Attention !Le résultat de l'ajout dépend de l'ordre dans lequel on effectue lesopérations. Si nous permutons les rôles des tables 1 et 2, nousredéfinissons notre requête ajout comme suit :

car le SGBDAccess n'acceptera pas que nous tentions d'introduire dans une table des champsqui n'y sont pas initialement présents. Nous notons que seuls les noms deschamps de la table n° 2 figurent dans la grille, mais nous pourrionsécrire "Table2.Nom" et "Table2.Prénom" à la place"Nom" et de "Prénom", sans que le système ne proteste. Lafigure ci-dessous représente le contenu des deux tables avant et après l'ajout.

Notre secondexemple, directement inspiré de la fin du précédent, illustre l'introduction decritères aux enregistrements de la table que l'on ajoute à l'autre. Voicicomment se présente la grille de la requête, si l'on impose des critères à deuxchamps de la table n° 1 avant de l'ajouter à la table n° 2 (pourfaire bonne mesure nous avons également ajouté un tri) :

Et voici lerésultat :

Notretroisième exemple montre comment on peut modifier les propriétés d'une table enl'ajoutant à une table vide. La table n° 1 que nous avons utiliséeci-dessus comporte un champ "Date", pour lequel nous avons choisi leformat jj/mm/aaaa, appelé "Date, abrégé" en mode création. Nousvoulons maintenant obtenir la date dans le format complet (exemple :dimanche 19 juin 1944) pour avoir connaissance du jour. La méthode la plussimple consiste, bien entendu, à changer manuellement de format en modecréation. Mais si l'opération doit être répétée souvent, il faut trouver unmoyen pour l'automatiser. Une requête ajout, qu'il est facile d'exécuter depuisune macro, nous fournit la solution.

Parcopier/coller (structure seulement) à partir de la table n° 1, nousobtenons une table "modèle" qui contient les mêmes champs (maisvides). Nous modifions le format du champ "Date", initialement"Date, abrégé", en "Date, complet" et nous enregistrons lamodification. Grâce à une macro (cet objet est étudié dans leschapitres 26 et suivant), nous créons une copie de la table"modèle" que nous appelons table n° 3, puis nous lui ajoutonstous les champs de la table n° 1. Nous constatons que, dans la table n° 3,la date s'affiche en format complet, comme le montre la figure ci-dessous.

Il existeen principe une méthode beaucoup plus simple pour modifier les propriétés d'unchamp de manière automatisable. Elle consiste à écrire une requête SQLutilisant la commande ALTER TABLE avec la clause MODIFY (chapitre 18).Malheureusement, la clause MODIFY ne fonctionne pas dans Access... et onutilise la requête ajout pour pallier cette déficience.

Notrequatrième exemple montre comment on peut garder la trace du classement d'unetable. Pour ce faire, nous traitons l'exemple d'une entreprise qui veut établirla liste de ses produits classés par ordre décroissant de chiffre d'affaires(CA) au cours de l'année écoulée. Le point de départ est une table contenant laliste des produits (classés par ordre alphabétique) avec leur CA. La méthode laplus simple consiste à trier la table par ordre de CA décroissant, àl'enregistrer, puis à la doter (en mode création) d'une colonne supplémentairedu type de données NuméroAuto. Mais si nous avons besoin d'automatiserl'opération, il nous faut recourir à une autre solution.

La figureci-dessous montre la méthode utilisée. Nous trions la table de départ (Table1)par ordre de CA décroissant. Nous l'ajoutons à une table vide contenant lesmêmes champs, plus un champ de type NuméroAuto (Table2). Puis, à l'aide d'unerequête de sélection simple, nous trions la table Table2 par ordre alphabétiquedu premier champ. Le résultat final est une table des produits classés parordre alphabétique, avec une colonne indiquant le rang de classement par ordrede CA décroissant. On notera que le champ "Classement" a été remplipar le SGBD (l'opérateur ne peut pas écrire dans ce champ).

 

Une autresolution à ce problème consiste à utiliser la commande ALTER TABLE en SQL, avecla clause ADD COLUMN et le type de données COUNTER, qui correspond à NuméroAuto(voir le chapitre 18).

4 -L'ajout sans doublons

La requêteajout crée des doublons si la seconde table contient des enregistrementsidentiques à ceux de la première. Le cas le plus flagrant résulte de l'ajoutd'une table à elle-même, opération qui est tout à fait licite dans Access, mêmesi son intérêt parait à peu près nul. On notera que la plupart des SGBDinterdisent cette opération. Le premier correctif auquel nous songions consisteà basculer de "Non" à "Oui" la propriété "Valeursdistinctes" de la requête ajout. Ainsi modifiée, la requête n'élimine pasles doublons qui résultent de l'ajout, mais évite de transporter dans lapremière table des enregistrements qui constituent des doublons dans laseconde. C'est mieux que rien, mais ce n'est pas suffisant. Le second correctifauquel nous songions consiste à créer un index sans doublons sur les champs dela première table communs avec ceux de la seconde table. Lorsque nous lançonsla requête ajout, nous recevons l'alerte suivante, qui constitue un morceaud'anthologie en matière de message informatique. Mais si nous admettons que par"violation de clé" il faut entendre "violation d'indexation sansdoublons", tout s'éclaire :

Cliquonssur "Oui" et le tour est joué : le SGBD n'ajoute que lesenregistrements qui ne créent pas de doublon. Si nous cliquons sur"Non", la requête est annulée. Si nous cliquons sur "Aide",nous obtenons une aide qui n'a rien à voir avec le contexte. Deux autressolutions peuvent être pratiquées :

·         fairesuivre la requête Ajout d'une requête qui élimine les doublons. Nous avonsappris à créer une telle requête dans le chapitre précédent ;

·         créerune requête Union en langage SQL. Nous apprendrons à nous servir du langage SQLdans un chapitre ultérieur.

Il fautcependant bien noter que l'ajout et l'union fonctionnent de manière distincte.Dans la requête union, les deux tables que l'on réunit jouent des rôlesidentiques. Comme nous venons de le constater, ce n'est pas le cas dans larequête ajout.

5 -L'analyse croisée

La requêteanalyse croisée s'applique à une table comportant au moins trois colonnes, etpossédant des caractéristiques particulières. L'une des colonnes doit comporterdes doublons, sur lesquels sera effectuée l'opération de regroupement (lacolonne "U" de la figure ci-dessous). Une autre colonne (la colonne"W" de la figure ci-dessous) doit comporter un nombre restreint devaleurs distinctes, qui serviront à créer les nouvelles colonnes. Un assistantfacilite la création de ce type de requête, dont la conception n'est pas aisée.

Considéronsl'exemple de la table (nommée "Table1") représentée ci-dessous. Uneentreprise a dressé la liste de ses fournisseurs et, pour chacun d'entre eux,la liste des produits fournis ainsi que le classement par ordre de chiffred'affaires.

Dans lafenêtre "Base de données", l'objet "Requêtes" étant sélectionné,nous cliquons sur le bouton nouveau, nous choisissons "AssistantRequête analyse croisée" dans la liste qui s'affiche, et nous cliquons sur"OK". Le dialogue suivant s'établit avec l'assistant :

·         nousindiquons d'abord sur quelle table nous voulons opérer. Dans le cas présent, ils'agit de la table "Table1" ;

·         nouschoisissons le champ "Société" comme "en-tête de ligne".Dans le jargon de l'éditeur, cela signifie que ce champ sera le premier de lanouvelle table ;

·         nouschoisissons le champ "Rang" comme "en-tête de colonne".Cela signifie que le SGBD va créer les colonnes "1", "2",etc. ;

·         nouschoisissons "premier" et nous décochons la case "Oui, inclureles sommes des lignes" car les données sont du type texte et non du typenumérique ;

·         nouscliquons sur le bouton "Terminer" et nous basculons en mode feuillede données pour examiner le résultat (figure ci-dessous).

La requêteanalyse croisée est surtout utilisée dans le domaine financier, où elle sert àcréer des bilans à partir de données comptables. Les nouvelles colonnes quisont crées correspondent alors à des périodes de temps données (jours,semaines, mois, etc.).

Attention !Si nous demandons à cette requête de créer une table, le résultat obtenu esterroné. Il y a là un bug que nous pouvons contourner en créant une requêtesélection simple (avec création de table et conservation de tous les champs),opérant sur le résultat de la requête analyse croisée.

5 -Conclusion

Comme vousavez pu le constater, nous avons rassemblé dans ce chapitre deux requêtes quin'ont pas de points communs. Vous voudrez bien nous en excuser... sachant quenous ferons mieux dans le chapitre suivant, où les requêtes de suppression etde mise à jour peuvent être regroupées sous la bannière unique de lamaintenance des BDD.

 

 

Chapitre 17 : les requêtes de maintenance

1 -Introduction

		Une base de données évolue sans arrêt : de nouveaux enregistrements sont introduits, d'autres sont archivés, d'autres sont modifiés, d'autres enfin sont supprimés. Des contrôles, suivis éventuellement de corrections, sont effectués.
		Deux types de requête sont particulièrement utilisés pour ces opérations de maintenance :
		la suppression, qui permet de faire disparaître des enregistrements jugés obsolètes, erronés ou inutiles ;     la mise à jour, qui permet de modifier le contenu de certains enregistrements.
		Sont supprimés ou modifiés les enregistrements qui répondent à certains critères. Ces opérations sont généralement effectuées sur une seule table à la fois. Cependant, par le jeu des relations, suppressions et modifications peuvent se répercuter en cascade dans d'autres tables, si l'option correspondante a été choisie lors de la création de la relation, après que l'intégrité référentielle eût été requise (Cf. le chapitre 5).
		La suppression est une opération qui s'effectue au niveau de l'enregistrement. La mise à jour intervient souvent à un niveau plus fin : seuls certains champs, dans certains enregistrements, sont concernés.
		Attention ! Une fois effectuées, suppressions et mises à jour sont irréversibles. Avant d'effectuer une requête de ce type, il est indispensable d'effectuer une copie des tables concernées, voire de la BDD toute entière.
		Bien entendu, une opération de modification ou de suppression concernant un tout petit nombre d'enregistrements ne justifie pas la création d'une requête ; il suffit d'intervenir ponctuellement dans la table considérée. Par contre, si le nombre d'opérations à effectuer croit, la création d'une requête fait gagner du temps et diminuer le risque d'erreurs.
		Comme il est d'usage dans tout ce tutoriel (encore appelé "cours en ligne" ou tutorial), nous utiliserons le SGBD Access comme support pratique.

 

	2 - La suppression
		La requête de suppression opère sur une table. Elle supprime les enregistrements (ou lignes) répondant à un ou plusieurs critères. Dans la figure ci-dessous, les enregistrements répondant à ces critères sont colorés en rouge ; la requête les fait disparaitre irrémédiablement.
U V W X Y Z T  1                                        2              3              4              5              6                 U V W X Y Z T  1                                        3              6
		A titre d'exemple, l'opération de suppression peut être utile dans les cas suivants :
		retirer de la BDD d'une entreprise toutes les données économiques relatives à un exercice clos ;     retirer de la table des prêts (d'une bibliothèque) toutes les opérations terminées (le livre emprunté a été rendu) ;     retirer du fichier journal d'un système informatique tous les enregistrements âgés de plus d'un mois ;     retirer de la liste des clients tous ceux qui n'ont rien commandé depuis deux ans ;     éliminer d'une table tous les enregistrements incomplets (un champ donné n'a pas été renseigné) ;     retirer de la table du stock tous les articles qui se sont mal vendus au cours des douze derniers mois ;     etc.
		Supposons, à titre d'exemple, que nous voulions éliminer d'une table "Factures" toutes les factures soldées avant le 1er janvier de cette année (2002). Dans la fenêtre "Base de données" nous sélectionnons l'objet "Requêtes", puis nous double-cliquons sur "Créer une requête en mode Création". Dans la boite "Afficher la table", nous sélectionnons la table "Factures", nous l'ajoutons et nous fermons. Dans la fenêtre "Microsoft Access", nous cliquons sur l'icône de la barre d'outils et, dans la liste déroulante qui s'affiche, nous choisissons "Requête Suppression". Nous introduisons le champ "Date_de_réglement" dans la grille et, sur la ligne "Critères :", nous écrivons :
		<#01/01/2002#
		l'opérateur < signifiant "antérieur à", et les signes # rappelant que nous opérons sur une date.
		Attention ! Si nous basculons en mode feuille de données en cliquant sur l'icône , le logiciel affiche la liste des enregistrements qui seront supprimés, et non l'aspect de la table après suppression comme nous pourrions nous y attendre.
		Si nous exécutons la requête (icône ), tous les enregistrements antérieurs au 1er janvier 2002 sont éliminés. Si la table "Factures" est ouverte pendant la suppression, le mot "Supprimé" apparaît dans chaque champ de chaque enregistrement supprimé. Dès que l'on referme la table, les enregistrements supprimés disparaissent sans laisser de traces.
		L'écriture d'un critère de suppression doit tenir compte du type de données du champ auquel il s'applique. Nous associerons sous peu à ce chapitre des annexes détaillant les règles d'écriture des critères pour les différents cas.

 

	3 - La mise à jour
		Les informations contenues dans une BDD peuvent avoir besoin d'une mise à jour. Ainsi, un taux de TVA peut varier, de même que le prix de vente des produits et services de  l'entreprise, etc. Mais la mise à jour peut être soumise à un ou plusieurs critères, s'appliquant ou non au champ susceptible d'être modifié. Par exemple, une prime peut être versée à l'ancienneté, et donc dépendre de la date d'embauche du salarié (laquelle figure dans la table du personnel de l'entreprise). Par conséquent, la mise à jour s'applique à une table, et concerne soit une colonne complète, soit seulement certains enregistrements de la colonne (colorés en rose dans la figure ci-dessous).
U V W X Y Z T  1                                        2              3              4              5              6
		L'actualité proche nous fournit un excellent exemple mise à jour simple : lors du passage à l'euro, tous les prix contenus dans les base de données durent être convertis de francs en euros à l'aide du fameux coefficient 6,55957. Pour traiter ce cas, créons une table "Produits" contenant trois champs : le code, le nom, et le prix unitaire HT de chaque produit. Les types de données correspondants sont : NuméroAuto, texte, et numérique (réel, deux décimales, pas de format particulier). Saisissons quelques enregistrements, et créons une copie de la table ainsi remplie.
		Créons une requête en mode création. Introduisons la table "Produits", cliquons sur l'icône , et sélectionnons "Requête Mise à jour" dans la liste déroulante. Introduisons le champ "Prix_unitaire" dans la grille et, sur la ligne "Mise à jour :", saisissons l'expression représentée dans la figure ci-dessous. Laissons la ligne "Critères :" vide, puisque le passage des francs aux euros concerne tous les enregistrements de la colonne sans exception.
		Attention ! si nous passons en mode feuille de données à l'aide de l'icône , nous constatons que l'opération de mise à jour n'est pas effectuée. Un bogue de plus à signaler à l'éditeur !
		Si nous exécutons la requête, nous constatons que tous nos prix ont bien été convertis en euros. Mais prenons garde de ne pas exécuter la requête une deuxième fois !
		Cet exemple de requête "mise à jour" est particulièrement simple, parce qu'il s'applique à tous les enregistrements sans distinction. On peut créer facilement un exemple avec critère, comme le montre la figure ci-dessous. La requête portera à 20,6 % le taux de TVA de 19,6 %, mais ne touchera pas aux autres taux.

 

		4 - Conclusion
			Les bases de données nécessitent des opérations de maintenance. Pour réaliser ces dernières, on utilise des requêtes, et plus particulièrement deux d'entre elles, la suppression et la mise à jour.
			Ces requêtes ne créent pas de table, elles modifient les tables existantes. Ces modifications étant irréversibles, il est indispensable d'effectuer des copies de sauvegarde avant d'exécuter les requêtes.
			Quelles que soient les précautions prises, il est parfois difficile d'éviter que des informations soient saisies deux fois. La recherche et l'élimination des doublons est donc également utilisée pour la maintenance des BDD.
Chapitre 18 : les tables en SQL

 

	1 - Introduction à SQL
		Le sigle SQL signifie "Structured Query Language", soit en français "Langage de recherche structuré". SQL est un langage de gestion des bases de données relationnelles que presque tous les SGBD comprennent. Il a été développé par IBM dans le courant des années 70, et son nom actuel (il s'appelait initialement SEQUEL) date du début des années 80.
		SQL a été normalisé par l'ANSI (American National Standards Institute) et par l'ISO (International Organization for Standardization). Voici les principales étapes de ce processus :
		première norme ANSI en 1986 ;     première norme ISO (SQL1) en 1987, révisée en 1989 ;     deuxième norme ISO (SQL2) en 1992 ;     troisième norme (SQL3) en cours de rédaction depuis 1999 par l'ANSI et l'ISO, après une très longue gestation, et avec beaucoup de retard sur l'événement.
		Malgré la normalisation ISO, l'implémentation du SQL par les différents éditeurs de SGBD comporte des différences plus ou moins marquées concernant :
		les détails de la syntaxe ;     l'écriture des commandes ;     le fonctionnement exact des commandes ;     l'implémentation de nouveaux types de données (images, animations, vidéos, liens hypertexte, etc.).
		Bref, il n'y a qu'un seul langage SQL, mais chaque éditeur de SGBD implémente son propre dialecte. Le "dictionnaire" qui permet de passer d'un dialecte à l'autre s'appelle ODBC (Open Data Base Connectivity). Il a été imaginé par Microsoft, et mis sur le marché en 1993.
		Contrairement à ce que son nom indique, SQL ne sert pas qu'à écrire des requêtes. C'est un langage complet, qui permet de créer des BDD, des tables, de saisir des données et de les corriger, de créer des vues, des index et des états (parfois baptisés "rapports", par francisation de l'anglais "reports"). Sauf erreur de notre part, il ne permet pas de créer des formulaires, parce qu'il a été conçu à une époque ou l'interface graphique n'existait pas sur ordinateur, et parce qu'un formulaire sans interface graphique n'a guère d'intérêt.
		Par contre, dans les SGBD sans interface graphique, le recours à SQL est obligatoire pour toutes les opérations, y compris la création de la BDD, celle des tables, et la saisie des données. La tentation est donc forte, pour un professeur qui ignore à quels SGBD ses étudiants seront confrontés lorsqu'ils entreront dans la vie active, de faire dans son enseignement une large part à SQL, qui représente l'outil universel de manipulation des données. Ceci dit, l'interface graphique est tellement entrée dans les moeurs, qu'il parait difficile qu'à terme tous les SGBD n'en soient pas dotés. En attendant, nous avons adopté une position mixte, en commençant par l'interface graphique, plus facile à appréhender, et en rajoutant à ce tutoriel quatre chapitres consacrés au langage SQL.
		Selon notre habitude, nous utiliserons le SGBD Access comme support pratique de ce tutoriel (ou tutorial, ou cours en ligne). A l'occasion, nous comparerons l'implémentation du SQL d'Access à celle d'Oracle. Comme nous ne disposons pas d'une base Oracle (nos moyens ne nous le permettent pas...), nous nous inspirerons d'un manuel de formation à la version 8 (l'avant-dernière).

 

	2 - Le langage SQL dans Access
		Dans Access, le langage SQL est utilisé par le moteur du SGBD pour traduire en commandes exécutables les instructions que donne l'utilisateur à travers l'interface graphique. Mais l'utilisateur n'a pas accès à ce code, sauf pour la conception des requêtes, où il peut passer facilement du mode graphique au mode SQL et vice versa. Nous utiliserons largement cette possibilité dans les trois chapitres suivants.
		Pour les tables, la situation est nettement moins satisfaisante. L'utilisateur qui se sert de l'interface graphique pour créer une table n'a pas accès au code SQL correspondant. Par contre, il dispose d'un éditeur de SQL qui reconnaît les principales commandes concernant la création et la modification des tables, et la saisie des données.
		Cet éditeur, cependant, ne doit pas faire illusion, car il est loin d'être complet. Il ne permet pas de régler dans le détail les propriétés des champs, comme on peut le faire dans l'interface graphique. Il ne permet pas non plus de créer des listes. Il rend donc des services limités. Il présente cependant de l'intérêt dans les deux cas suivants :
		l'apprentissage initial du SQL, pour lequel il n'est pas utile d'entrer immédiatement dans les moindres détails ;     l'automatisation (via les macros) de certaines opérations relatives aux tables. En effet, le code SQL que nous allons écrire sera enregistré sous forme de requête par le SGBD, et il est très facile de lancer une requête à partir d'une macro.
		Évidemment, il est beaucoup plus facile de créer, remplir, modifier, et supprimer une table dans l'interface graphique d'Access qu'en utilisant des commandes SQL. Mais tous les SGBD ne sont pas dotés d'une interface graphique, et il est bon de savoir se débrouiller sans elle le cas échéant.

 

	3 - La procédure
		Pour gérer les tables en langage SQL dans Access, il nous faut opérer de la manière suivante. Dans la fenêtre "Base de données", nous sélectionnons l'objet "Requêtes". Nous effectuons un double clic sur "Créer une requête en mode création", nous refermons la fenêtre "Afficher la table" sans introduire de table et, dans le menu, nous suivons le chemin suivant :
		Requête --> Spécifique SQL --> Définition des données
		S'ouvre alors une fenêtre intitulée "Requête1 : Requête Définition des données", dans laquelle nous pouvons écrire du code SQL.
		Pour exécuter ce code, nous cliquons sur l'icône "Exécuter". Pour l'enregistrer, nous cliquons sur "Enregistrer". Nous constatons alors que le SGBD Access traite notre code comme une requête.
		Pour modifier le code SQL, nous sélectionnons la requête enregistrée précédemment, et nous cliquons sur l'icône "Modifier". La fenêtre "Requête Définition des données" s'ouvre à nouveau.
		Tous les exemples cités dans ce chapitre ont été transportés (par copier/coller) dans Access 2002, et nous avons vérifié leur bon fonctionnement. Vous ne devriez donc pas rencontrer de difficulté pour les reproduire.

 

	4 - La création et la suppression d'une table
		Dans la fenêtre ouverte grâce à la procédure précédente, nous écrivons notre première commande (ou instruction) SQL, contenant la clause CREATE TABLE, pour créer la table "Personnes" (nous notons qu'un point-virgule marque la fin de la commande) :
CREATE TABLE Personnes (Nom CHAR(20), Prénom CHAR(20));
		Nous exécutons cette commande en cliquant sur l'icône "Exécuter". Le fait qu'aucun message ne soit émis signifie que tout s'est bien passé. Nous sélectionnons l'objet "Table", et nous constatons que :
		la table "Personnes" est effectivement créée ;     qu'elle possède deux champs de type texte (de 20 caractères au maximum) ;     qu'ils sont intitulés "Nom" et "Prénom".
		Si nous enregistrons cette commande en cliquant sur l'icône "Enregistrer", le SGBD Access la traite comme une requête. Devant son nom, il place une icône particulière (, à ne pas confondre avec l'icône "Modifier") pour rappeler qu'il s'agit d'une commande SQL liée à la manipulation des tables.
		Bien entendu, si nous n'exécutons pas la requête, la table "Personnes" ne sera pas créée. Par contre, si la table "Personnes" existe déjà, la commande ne s'exécute pas (la table existante n'est pas écrasée), et le SGBD affiche le message suivant :
		Attention ! Si l'objet "Table" est sélectionné quand vous lancez l'exécution de la commande SQL, la table "Personnes" n'apparaîtra pas (c'est l'éternel problème de la synchronisation dans Access). Il suffit de cliquer, dans le menu, sur "Affichage", puis sur "Actualiser", pour que le nom de la table apparaisse.
		Les commandes SQL s'expriment en format libre. Nous pouvons écrire les clauses en minuscules, et nous ne sommes pas tenus d'aller à la ligne pour détailler les champs. Bien que la précédente présentation (sur trois lignes) soit considérée comme plus lisible, l'expression suivante est parfaitement exacte et s'exécute normalement :
create table Personnes (Nom char(20),Prénom char(20));
		Les conventions relatives aux noms des tables et des champs varient quelque peu d'un SGBD à l'autre. En ce qui concerne plus particulièrement les champs :
		le nombre de caractères ne doit pas être trop grand (64 dans Access, 18 à 30 dans d'autres SGBD) ;     seuls les lettres, les nombres et le caractère de soulignement sont autorisés. Access admet les caractères accentués. Il admet aussi l'espace, mais le nom du champ doit alors être écrit entre crochets ;     certains SGBD requièrent que le nom d'un champ commence par une lettre, mais ce n'est pas le cas d'Access ;     les termes faisant partie du vocabulaire du langage SQL sont interdits ("date" par exemple). Ce sont les mots réservés.
		Les types de données sont définis dans le DDL (Data Definition Language) de chaque SGBD, et ils varient beaucoup d'un logiciel à l'autre. Dans Access, les mêmes termes ne sont pas toujours utilisés dans l'interface graphique, en VBA et en SQL. Voici un échantillon représentatif des différentes façons d'exprimer un type de données lors de la création d'une table en SQL dans Access :
		Booléen : BIT ;     Nombre entier : SHORT (entier), SMALLINT (entier), LONG (entier long), INTEGER (entier long), BYTE (octet) ;     Nombre réel : SINGLE (réel simple), DOUBLE (réel double), NUMERIC (réel double) ;     Monétaire : CURRENCY, MONEY ;     Date/Heure : DATE, TIME, DATETIME ;     Texte : VARCHAR (255 caractères), CHAR(n) ou TEXT(n) (n caractères), LONGTEXT (mémo, 32K max.) ;     Fichier binaire : LONGBINARY (Objet OLE) ;     Compteur : COUNTER (NuméroAuto).
		On notera qu'il n'est pas possible de créer un champ de type hypertexte via une commande SQL dans Access. Même remarque en ce qui concerne les listes de choix.
		Pour supprimer une table, on utilise la clause DROP TABLE, comme le montre l'exemple suivant :
DROP TABLE Personnes;
		Attention ! Si l'objet "Table" est sélectionné dans la fenêtre "Base de données" quand vous lancez l'exécution de la commande SQL de suppression, la table "Personnes" ne disparaîtra pas (c'est l'éternel problème de la synchronisation dans Access). Il suffit de cliquer, dans le menu, sur "Affichage", puis sur "Actualiser", pour que le nom de la table disparaisse.
		Si nous enregistrons la commande de suppression de table, Access place devant son nom l'icône spécifique des requêtes SQL liées à la création de tables.

 

	5 - La modification d'une table
		Il est possible de modifier une table existante. Les exemples les plus classiques concernent l'addition d'une nouvelle colonne et la suppression d'une colonne existante. La commande :
ALTER TABLE Personnes ADD Naissance DATE;
		permet, lorsqu'on l'exécute, d'ajouter le champ intitulé "Naissance", de type Date/Heure, à la table "Personnes". La variante suivante fonctionne également :
ALTER TABLE Personnes ADD COLUMN Naissance DATE;
		La clause INIT, qui permet de donner une valeur initiale aux champs ainsi créés, ne fonctionne pas dans Access. Par défaut, cette valeur initiale est Null. Pour la modifier, il faut utiliser une commande UPDATE (dont nous parlerons au paragraphe 9 ci-dessous).
		Pour supprimer la colonne que nous venons de créer, nous utilisons la commande suivante :
ALTER TABLE Personnes DROP Naissance;
		ou sa variante :
ALTER TABLE Personnes DROP COLUMN Naissance;
		En SQL standard, la commande ALTER TABLE peut aussi être utilisée pour modifier les propriétés d'une colonne existante. Exemple :
ALTER TABLE Personnes MODIFY Nom CHAR(40);
		mais la clause MODIFY n'est pas reconnue par Access, et l'exécution de la commande ci-dessus entraîne un message d'erreur. L'ignorance de la clause MODIFY enlève à la commande ALTER TABLE une bonne partie de son intérêt dans Access, et l'on se demande pourquoi l'éditeur a fait les choses à moitié.
		Nous verrons cependant au paragraphe 8 que la commande ALTER TABLE admet la clause ADD CONSTRAINT, ce qui permet de rajouter une clé ou de créer une relation.

 

	6 - Les propriétés des champs
		Le langage SQL est doté de clauses permettant de définir les propriétés des champs lors de la création d'une table. Mais le moteur d'Access ne les reconnaît pas toutes, loin de là.
		Pour empêcher un champ de rester vide, nous utilisons la clause NOT NULL, comme le montre l'exemple suivant :
CREATE TABLE Personnes (Nom CHAR(20) NOT NULL, Prénom CHAR(20));
		Après avoir exécuté la commande nous vérifions, dans le propriétés de la table "Personnes" ainsi créée, que le Null est interdit dans le champ "Nom".
		Pour qu'un champ soit indexé sans doublons, nous utilisons la clause UNIQUE, comme le montre l'exemple suivant :
CREATE TABLE Personnes (Nom CHAR(20) UNIQUE, Prénom CHAR(20));
		Après avoir exécuté la commande, nous ouvrons la table "Personnes" en mode modification, nous cliquons sur le champ "Nom", et nous vérifions que la propriété "Indexé :" vaut "Oui - Sans doublons". En fait, cette clause possède un intérêt limité pour deux raisons :
		nous ne pouvons pas donner de nom à l'index. Ceci nous interdit de supprimer l'index via une commande SQL ;     nous ne pouvons pas créer d'index multi-champ de cette façon.
		Il est donc souvent préférable d'utiliser la commande de création d'index que nous présenterons au paragraphe suivant.
		Pour poser une clé primaire sur un champ, nous utilisons la clause PRIMARY KEY, comme le montre l'exemple suivant :
CREATE TABLE Personnes (Nom CHAR(20) PRIMARY KEY, Prénom CHAR(20));
		Après avoir exécuté la commande, nous ouvrons la table "Personnes" en mode modification, nous cliquons sur le champ "Nom", et nous vérifions qu'il est effectivement doté de la clé. Cette commande possède les deux mêmes défauts que la précédente : nous ne sommes pas maîtres du nom de la clé (le système l'appellera Index_976A9AC0_494C_41C1, par exemple...), et nous ne pouvons pas appliquer la clé à plusieurs champs simultanément. Le premier défaut peut être corrigé grâce à la commande suivante :
CREATE TABLE Personnes (Nom CHAR(20) CONSTRAINT clé_primaire PRIMARY KEY, Prénom CHAR(20));
		qui permet d'attribuer le nom "clé_primaire" à la clé ainsi créée. Nous verrons au paragraphe suivant comment placer une clé sur plusieurs champs.
		Les autres clauses permettant de définir les propriétés des champs ne fonctionnent pas dans Access. Il en est ainsi de DEFAULT, qui permet de fixer la valeur par défaut d'un champ, ainsi que de CHECK, qui permet de fixer des contraintes sur le contenu d'un champ (propriété "Valide si").

 

	7 - La clé primaire et l'index
		Pour placer une clé primaire sur un champ, nous pouvons utiliser la clause CONSTRAINT, qui est obligatoirement suivie d'un nom d'index, et que nous avons déjà rencontrée au paragraphe précédent. Créons, par exemple, la table "Personnes" avec une clé primaire (intitulée clé_primaire) sur le champ "Nom". La commande s'écrit :
CREATE TABLE Personnes (Nom CHAR(20) NOT NULL, Prénom CHAR(20), CONSTRAINT clé_primaire PRIMARY KEY(Nom));
		L'objet "Table" étant sélectionné, nous cliquons sur l'icône "Index", et la fenêtre du même nom s'ouvre. Nous vérifions que la clé est bien nommée "clé_primaire", comme le montre la figure suivante :
		Pour appliquer la clé à deux champs, nous utilisons la syntaxe suivante :
CREATE TABLE Personnes (Nom CHAR(20), Prénom CHAR(20), CONSTRAINT essai_index PRIMARY KEY(Nom, Prénom));
		La création d'un index peut s'effectuer alors que la table existe déjà, mais cela requiert l'usage d'une syntaxe différente. Créons par exemple un index sur le champ "Nom" de la table "Personnes" :
CREATE UNIQUE INDEX essai_index ON Personnes (Nom);
		Dans le cas d'un index sur deux champs, cette syntaxe devient :
CREATE UNIQUE INDEX essai_index ON Personnes (Nom, Prénom);
		Pour supprimer un index, la syntaxe SQL standard s'écrit :
DROP INDEX Personnes.essai_index;
		Mais cette syntaxe standard ne fonctionne pas dans Access. Il faut utiliser la variante suivante :
DROP INDEX essai_index ON Personnes;

 

	8 - La création et la suppression d'une relation
		Pour montrer comment on crée une relation 1-n entre deux tables, nous avons décomposé les opérations en quatre étapes. Dans un premier temps, nous créons la table "Personnes", avec un champ "Nom", un champ "Prénom", et un champ "Code_Ville" (entier long), en exécutant la commande suivante :
CREATE TABLE Personnes (Nom TEXT(30), Prénom TEXT(30), Code_Ville LONG);
		Dans un deuxième temps, nous créons la table "Villes", avec un champ "Code_Ville" (NuméroAuto) et un champ "Ville". Cette table servira de liste externe pour la table "Personnes". Nous exécutons la commande suivante :
CREATE TABLE Villes (Code_Ville COUNTER, Ville TEXT(30));
		Dans un troisième temps, nous modifions la table "Villes" en plaçant une clé primaire sur le champ "Code_Ville", qui servira de côté 1 à la future relation. Pour ce faire, nous exécutons la commande suivante :
ALTER TABLE Villes ADD CONSTRAINT clé_primaire PRIMARY KEY(Code_Ville);
		Notons que, si nous ne désirons pas donner un nom à la clé (ici "clé_primaire"), nous pouvons simplifier la commande précédente en l'écrivant ainsi :
ALTER TABLE Villes ADD PRIMARY KEY(Code_Ville);
		Dans un quatrième temps, nous modifions la table "Personnes" en plaçant une clé étrangère, nommée "relation_ville", sur le champ "Code_Ville" de la table "Personnes" (on parle de clé étrangère pour le côté n de la relation), en précisant que le côté 1 de la relation est le champ "Code_Ville" de la table "Villes". Nous exécutons donc la commande suivante :
ALTER TABLE Personnes ADD CONSTRAINT relation_ville FOREIGN KEY (Code_Ville) REFERENCES Villes (Code_Ville);
		Notons que :
		si nous ne désirons pas donner un nom à la relation (ici "relation_ville"), et     s'il n'y a pas d'ambiguïté sur le champ qui est du côté 1 de la relation (ici "Code_Ville de la table "Villes")
		nous pouvons simplifier la commande précédente en l'écrivant ainsi :
ALTER TABLE Personnes ADD FOREIGN KEY (Code_Ville) REFERENCES Villes;
		Nous vérifions dans la fenêtre "Relations" qu'une relation 1-n a bien été créée entre les deux tables, avec application de l'intégrité référentielle.
		Nous aurions pu être plus directs, en installant les clés (clé primaire et clé étrangère) dès la création des tables. Pour la table ville, la commande s'écrit :
CREATE TABLE Villes (Code_Ville COUNTER PRIMARY KEY, Ville TEXT(30));
		Pour la table "Personnes", la commande s'écrit :
CREATE TABLE Personnes (Nom TEXT(30), Prénom TEXT(30), Code_Ville LONG, CONSTRAINT relation_ville FOREIGN KEY (Code_Ville) REFERENCES Villes);
		Ces deux commandes étant exécutées, nous vérifions dans la fenêtre "Relations" que les deux tables sont présentes avec tous leurs champs, et liées par une relation 1-n.
		La suppression de cette relation s'obtient à l'aide de la commande ALTER TABLE. Dans le cas de la table "Personnes", côté n de la relation "relation_ville" créée précédemment, la commande s'écrit :
ALTER TABLE Personnes DROP CONSTRAINT relation_ville;
		On notera que les options "Mettre à jour en cascade les champs correspondants" et "Supprimer en cascade les enregistrements correspondants" ne sont pas disponibles.
		En conclusion, la création et la suppression d'une relation sont réalisées grâce à la clause CONSTRAINT appliquée à la table située du côté n de la relation.

 

	9 - La saisie et la correction des données
		Pour saisir des données dans la table "Personnes", la commande SQL utilise la clause INSERT INTO. Les données en mode texte doivent être placées entre guillemets. Exemple :
INSERT INTO Personnes VALUES ("Machin","Pierre");
		Nous vérifions, après exécution de la commande, que Pierre Machin a bien été introduit dans la table "Personnes". Si nous enregistrons la commande, le SGBD la fait précéder de l'icône , qui symbolise les requêtes de type "Ajout". Cependant, si nous essayons de basculer en "Mode création" (le mode graphique), le SGBD Access nous oppose le message suivant :
		En bon français, notre commande ressemble à une requête ajout, elle possède l'icône d'une requête ajout, elle utilise la clause INSERT comme une requête ajout, mais ce n'est pas une requête ajout. Qu'on se le dise !
		Si le Null n'est pas interdit dans les champs de la table "Personnes", nous pouvons introduire un nom sans le prénom correspondant, en opérant de la manière suivante :
INSERT INTO Personnes (Nom) VALUES ("Truc");
		Pour modifier un enregistrement existant, nous faisons appel à la clause UPDATE (qui signifie "mise à jour" en anglais). Si, par exemple, nous voulons doter M. Truc de son prénom, nous écrirons :
UPDATE Personnes SET Prénom="Henri" WHERE Nom="Truc";
		Si nous enregistrons cette commande, le SGBD Access lui attribue l'icône caractéristique des requêtes de mise à jour. C'en est effectivement une, comme nous pouvons le constater en basculant en mode graphique. Nous reparlerons de ce type de requête au chapitre 22.
		Pour supprimer une ligne, nous utilisons la commande basée sur la clause DELETE :
DELETE FROM Personnes WHERE Nom="Truc";
		Lorsque la clause WHERE est absente, le SGBD supprime tous les enregistrements, laissant la table vide (mais ne la supprimant pas) :
DELETE FROM Personnes;
		Si nous enregistrons ces deux commandes, le SGBD Access fait précéder leur nom de l'icône caractéristique des requêtes suppression. Cependant, si nous créons la requête suppression correspondante en mode graphique, et si nous basculons en mode SQL, nous obtenons une syntaxe légèrement différente. Nous reviendrons sur ce point au chapitre 22.
		Dans certaines implémentations du langage SQL (mais pas dans Access), on peut omettre la clause FROM qui suit la clause DELETE.

 

		10 - Conclusion
			La création d'une table en SQL n'est pas une travail bien ardu, même s'il est certain qu'une bonne interface graphique simplifie fortement l'opération. La saisie des informations en SQL, par contre, est une tâche quasi désespérante. A moins que les données ne soient importées, l'usage d'une interface graphique s'impose.
			En ce qui concerne les tables, l'interface graphique du SGBD Access est beaucoup plus développée que son interface SQL. Nous verrons dans les chapitres suivants que la situation est très différente, et nettement plus équilibrée, pour les requêtes.
Chapitre 19 : la sélection simple en SQL

 

	1 - Introduction
		Nous avons vu au chapitre précédent qu'il était possible, dans le SGBD Access, de manipuler les tables en langage SQL. Cependant, les commandes correspondantes sont considérées comme des requêtes, et il n'est pas possible de basculer entre le mode graphique et le mode SQL. En effet, le mode graphique s'obtient lorsque l'objet "Tables" est sélectionné, alors que le mode SQL requiert que l'objet "Requêtes" soit actif.
		En ce qui concerne les requêtes, la situation est nettement plus satisfaisante. La plupart des commandes SQL relatives aux requêtes sont connues du moteur d'Access, et on bascule sans problème du mode graphique au mode SQL (l'objet "Requêtes" étant sélectionné).
		Il existe cependant quelques exceptions, que nous étudierons au chapitre 21. Il s'agit des opérations ensemblistes, pour lesquelles il n'existe pas d'interface graphique. Ces trois opérations sont :
		l'union de deux tables, pour laquelle l'opérateur UNION fonctionne ;     l'intersection de deux tables, pour laquelle l'opérateur INTERSECT ne fonctionne pas ;     la différence de deux tables, pour laquelle les opérateurs EXCEPT et MINUS ne fonctionnent pas.
		Selon notre habitude, nous utiliserons le SGBD Access comme support pratique de ce cours.

 

	2 - La sélection simple
		Créons, dans l'interface graphique, la requête qui extrait de la table "Personnes" (contenant une liste de personnes) les deux champs "Nom" et "Prénom". Cliquons sur la petite flèche située à droite de l'outil "Affichage", et dans la liste déroulante, choisissons "Mode SQL". La commande (ou instruction) suivante s'affiche :
SELECT Personnes.Nom, Personnes.Prénom FROM Personnes;
		La requête simple commence par la clause "SELECT", suivie du nom des champs, puis continue avec la clause "FROM", suivie du nom de la table à laquelle appartiennent les champs. Le point-virgule marque la fin de la commande.
		La syntaxe relative aux noms des champs consiste à écrire le nom de la table, suivi d'un point et du nom du champ. Cette façon de procéder s'appelle la qualification.  Dans le cas présent, cette qualification est redondante, et nous pouvons très bien écrire :
SELECT Nom, Prénom FROM Personnes;
		La politique la plus raisonnable consiste à qualifier les champs chaque fois qu'une ambiguïté existe (même nom de champ dans deux tables différentes, lors d'une requête multi-table), et de ne pas les qualifier dans le cas contraire.
		Nous avons vu au chapitre précédent qu'il existait des restrictions sévères sur les noms des tables et des champs en SQL. Pour s'en affranchir, il faut mettre les noms des champs, et celui de la table, entre crochets pour éviter les ennuis. Les expressions :
SELECT [Personnes].[Nom], [Personnes].[Prénom] FROM [Personnes];     SELECT [Nom], [Prénom] FROM [Personnes];
		sont parfaitement valables. Par prudence, certains professionnels utilisant les SGBD préfèrent s'abstenir de tout caractère accentué, remplacent systématiquement l'espace par le caractère de soulignement, et évitent d'utiliser les termes réservés. Rappelons que l'implémentation de SQL par Access accepte les caractères accentués pour les noms des champs et des tables.
		Attention aux détails de syntaxe ! Comme tous les langages informatiques, SQL a ses petites manies qui empoisonnent les utilisateurs. L'interface graphique a ceci de bon qu'elle nous débarrasse de ces problèmes stupides -- en plus du fait qu'elle nous permet de créer des requêtes plus simplement et plus rapidement. On notera que, dans Access, le point-virgule qui marque la fin d'une commande n'est pas indispensable.

 

	3 - La requête avec création de table
		Récupérons la requête précédente dans l'interface graphique, faisons en sorte qu'elle crée une table appelée "Essai", puis basculons en mode SQL. Nous obtenons :
SELECT Personnes.Nom, Personnes.Prénom INTO Essai FROM Personnes;
		Nous voyons que la création de la table est effectuée grâce à la clause INTO, suivi du nom de la table. En SQL version Oracle, on écrirait plutôt :
INSERT INTO Essai SELECT Personnes.Nom, Personnes.Prénom FROM Personnes;
		Dans Access, cette syntaxe fonctionne à condition que la table "Essai" préexiste, et contienne au moins les champs "Nom" et "Prénom" avec les mêmes propriétés que dans la table "Personnes". Access effectue alors une requête ajout des deux premières colonnes de la table "Personnes" à la table "Essai".

 

	4 - Le tri simple ou multiple
		Nous pouvons demander que le résultat de la requête soit trié sur un ou plusieurs champs. Récupérons la requête précédente dans l'interface graphique, faisons en sorte que le résultat soit trié sur les noms d'abord, sur les prénoms ensuite, et basculons en mode SQL. Nous obtenons :
SELECT Personnes.Nom, Personnes.Prénom FROM Personnes ORDER BY Personnes.Nom, Personnes.Prénom;
		Nous voyons que le tri (dans l'ordre croissant) s'obtient grâce à la clause ORDER BY, suivi des noms des champs. Le tri multiple est effectué dans l'ordre d'énumération des champs.
		Le tri d'un champ dans l'ordre décroissant s'obtient en faisant suivre le nom de ce champ par l'opérateur DESC. L'exemple suivant effectue un tri croissant sur les noms, suivi d'un tri décroissant sur les prénoms :
SELECT Personnes.Nom, Personnes.Prénom FROM Personnes ORDER BY Personnes.Nom, Personnes.Prénom DESC;

 

	5 - L'élimination des doublons
		Comme nous l'avons vu au chapitre 8, la requête simple peut créer des doublons, et il est possible de remédier de façon simple à cette situation en jouant sur les propriétés de la requête. Créons dans l'interface graphique une requête de sélection simple qui concerne le seul champ "Nom" de la table "Personnes". Modifions la propriété "Valeurs distinctes" de "Non" à "Oui", puis basculons en mode SQL. Nous obtenons :
SELECT DISTINCT Personnes.Nom FROM Personnes;
		Nous voyons que l'élimination des doublons s'obtient à l'aide de l'opérateur DISTINCT placé juste après la clause SELECT. Une syntaxe plus ancienne est également comprise par Access, mais elle ne semble plus guère utilisée :
SELECT DISTINCT(Nom) FROM Personnes;
		Pour éviter de créer des doublons sur deux champs, la commande SQL s'écrit :
SELECT DISTINCT Personnes.Nom, Personnes.Prénom FROM Personnes;

 

	6 - La requête avec création de champ
		Reprenons l'exemple déjà traité au chapitre 8, lequel consiste à concaténer le nom avec le prénom, en les séparant par un espace. Appelons "Nom_complet" le nouveau champ. En mode SQL, nous obtenons :
SELECT [Nom] & " " & [Prénom] AS Nom_complet FROM Personnes;
		La façon d'extraire le contenu des champs et d'exprimer la concaténation varient d'un SGBD à l'autre. Cependant, la possibilité de créer un nouveau champ (et d'en définir le contenu à partir de champs existants) se retrouve dans tous les SGBD dignes de ce nom.

 

	7 - La requête multi-fonctionnelle
		En définitive, nous pouvons regrouper toutes les opérations précédentes (requête simple, création de table, création de champ, tri et élimination des doublons) en une seule requête, dont voici le code SQL (en version Access) :
SELECT DISTINCT [Nom] & " " & [Prénom] AS Nom_complet INTO Liste_de_noms FROM Personnes ORDER BY [Nom] & " " & [Prénom];

 

	8 - Les requêtes emboîtées
		Nous avons vu au chapitre 8 qu'il est possible de créer dans Access une requête à partir du résultat d'une autre requête, à condition que cette dernière ne crée pas de table. En mode SQL, la commande s'écrit :
SELECT Requête1.Nom FROM Requête1;
		On ne peut pas rêver plus simple pour emboîter deux requêtes ! Cette belle simplicité ne se retrouve pas en SQL pur et dur, où l'emboîtement de deux requêtes est d'une écriture plutôt complexe. Que l'on en juge :
		si la première requête (encore appelée sous-requête, ou sous-interrogation) ramène une valeur numérique unique (résultat d'une opération du type comptage, sommation, calcul de moyenne, etc.), on utilise les opérateurs arithmétiques usuels : =, <,  >, >=, <= et <> ;     si la première requête ramène une seule ligne, on utilise les opérateurs IN, ALL, ou ANY suivant les cas ;     si la première requête est susceptible de ramener plusieurs lignes, on utilise EXISTS ou NON EXISTS.
		Bonjour les erreurs !

 

		Chapitre 17 : les requêtes de maintenance
			Le chapitre 11 est consacré à la sélection simple, mise en oeuvre à l'aide de l'interface graphique d'Access. Le présent chapitre 19 suit pratiquement le même plan, mais utilise le langage SQL. La comparaison entre ces deux chapitres nous amène à faire une double constatation :
			le langage SQL est d'un usage assez facile, sauf en ce qui concerne l'emboîtement des requêtes. Pour réaliser l'emboîtement, l'utilisation de l'interface graphique d'Access est beaucoup plus simple. Cette situation résulte du fait que le SQL mis en oeuvre par Access permet d'évoquer une requête par son nom, ce qui n'est pas le cas du SQL usuel ;     Access fournit un moyen didactique commode pour aborder l'étude du langage SQL.
Chapitre 20 : la sélection en SQL

 

	1 - Introduction
		Nous connaissons désormais l'allure des commandes SQL, dans le cadre de la sélection simple. Il est temps que nous abordions :
		la sélection avec un ou plusieurs critères s'appliquant à un ou plusieurs champs ;     la sélection multi-table, avec les jointures ;     le regroupement.
		Comme pour les autres chapitres, nous utilisons le SGBD Access comme support pratique de ce tutoriel (ou tutorial, ou cours en ligne).
		Chaque fois que cela sera possible, nous ferons écrire le code SQL par le SGBD, en partant de l'interface graphique de définition des requêtes avant de basculer en mode SQL.

 

	2 - La sélection en SQL
		La sélection permet d'extraire d'une table (ou de plusieurs tables liées par des relations) les enregistrements répondant à un ou plusieurs critères. Notons au passage qu'un critère peut aussi être appelé une condition. Rappelons que l'ensemble des critères définit un filtre.
		A titre de premier exemple, cherchons dans la table "Personnes" (qui contient des noms, des prénoms, des adresses, etc.) les personnes dont le nom commence par la lettre "c" (majuscule ou minuscule, la casse n'importe pas). Dans la grille de création de la requête, nous exprimons la condition sous la forme :
		Comme "c*"
		Si nous basculons en mode SQL, nous obtenons le code suivant :
SELECT Personnes.Nom, Personnes.Prénom FROM Personnes WHERE (((Personnes.Nom) Like "c*"));
		Nous voyons que la condition s'exprime à l'aide de la clause WHERE, suivie du nom du champ et de la condition. L'opérateur LIKE correspond au français "Comme".
		La clause WHERE est suivie d'un parenthèsage aussi impressionnant qu'inutile. De plus, la qualification est superflue. Si nous écrivons la requête sous la forme :
SELECT Nom, Prénom FROM Personnes WHERE Nom Like "c*";
		elle fonctionne tout aussi bien, et le SGBD Access ne tente de rétablir ni les parenthèses, ni la qualification.
		Rajoutons un champ de type date dans la table "Personnes", et sélectionnons les enregistrements relatifs à l'année 2002 seule. Dans l'interface graphique, nous écrivons le critère sous la forme :
		>#31/12/2001# ET <#01/01/2003#
		Si nous basculons en mode SQL, nous obtenons le code suivant, après élimination des parenthèses et de la qualification :
SELECT Nom, Prénom, Date FROM Personnes WHERE Date>#12/31/2001# And Date<#1/1/2003#;
		Nous pourrions faire des expériences analogues avec un champ numérique (opérateurs arithmétiques), ou un champ booléen. Les conditions sur le "Null" (champ non renseigné dans un enregistrement donné) s'écrivent à l'aide des expressions "Is Null" et "Is Not Null", dont la traduction française dans Access est "Est Null" ou "Est Pas Null". On peut combiner plusieurs conditions à l'aide des opérateurs logiques "And", "Or" et "Not", dont la traduction française dans Access est "Et", "Ou" et "Pas". Enfin, une condition peut comprendre des opérateurs arithmétiques ou des fonctions mathématiques (si elle porte sur des nombres) ou des opérateurs de chaînes (si elle porte sur du texte).
		Remarque : notre façon de sélectionner l'année 2002 est d'un usage très général, mais elle semble un peu lourde. L'utilisation de la fonction "Year()" est plus élégante, mais cette fonction est spécifique de VBA (Visual Basic for Applications, le langage de programmation lié à Office). Voici le code SQL correspondant :
SELECT Nom, Prénom, Date FROM Personnes WHERE Year([Date])=2002;
		Nous constatons que la requête fonctionne toujours si nous éliminons les crochets entourant l'argument "Date" dans la fonction "Year". Mais, dans l'interface graphique, la définition de la requête a changé ! Access a créé un nouveau champ intitulé "Année([Date])" et inscrit la valeur "2002"sur la ligne "Critères :". Notons au passage que l'on peut franciser "Year" en "Année" dans l'interface graphique d'Access, mais pas dans la programmation d'un module VBA ou l'écriture d'une commande SQL.

 

	3 - Les jointures
		Effectuons maintenant une requête sur deux tables ("Personne" et "Communes") liées par une relation (appliquée aux champs "code_commune"). La requête SQL s'écrit :
SELECT Personnes.Nom, Personnes.Prénom, Personnes.Adresse, Communes.commune, Communes.[code postal] FROM Communes INNER JOIN Personnes ON Communes.code_commune = Personnes.code_commune;
		Cette fois, la qualification est obligatoire pour les champs "code_commune", car il faut préciser de quelle table provient chaque champ. Mais la qualification est inutile pour les autres champs, car il ne peut y avoir d'ambiguïté. Enfin, les crochets sont obligatoires pour évoquer le champ "code postal", dont le nom contient un espace (ils ne le sont pas pour "Prénom", parce que Access tolère les caractères accentués). La requête SQL ainsi simplifiée s'écrit :
SELECT Nom, Prénom, Adresse, commune, [code postal] FROM Communes INNER JOIN Personnes ON Communes.code_commune = Personnes.Code_commune;
		Nous voyons que "INNER JOIN" traduit l'existence d'une relation entre les deux tables, et que "ON" précise quels sont les champs mis en relation.
		En SQL1, la commande précédente s'écrit différemment. La clause INNER JOIN disparaît au profit d'une simple virgule, et la traduction de la mise en relation des tables s'exprime à l'aide de la clause WHERE :
SELECT Nom, Prénom, Adresse, commune, [code postal] FROM Communes, Personnes WHERE Communes.code_commune = Personnes.code_commune;
		Le SGBD Access accepte cette syntaxe mais, si nous revenons dans l'interface graphique, nous constatons que cette dernière a changé : la relation entre les deux tables a disparu ! Elle est remplacée par la condition d'égalité de contenu entre les champs "code_commune" des deux tables (condition exprimée en utilisant la syntaxe du SQL), comme le montre la figure ci-dessous -- une façon élémentaire, mais parfaitement exacte, de créer une relation entre deux tables. On notera que la relation normale entre les deux tables n'est pas supprimée, mais simplement éliminée de la fenêtre de création de la requête.
		La clause INNER JOIN a fait son apparition avec la version 2 de SQL, parce que le besoin s'était fait sentir de préciser à quel type de jointure appartenait une relation. Plus précisément, on distingue :
		la jointure interne, qui utilise INNER JOIN. Ne sont incluses dans le résultat final que les lignes qui se correspondent dans les deux tables. Ce cas est celui que nous avons traité précédemment ;     la jointure externe gauche, dans laquelle INNER JOIN est remplacé par LEFT OUTER JOIN. Toutes les lignes de la première table sont incluses dans le résultat de la requête, même s'il n'existe pas de ligne correspondante dans la seconde table ;     la jointure externe droite, dans laquelle INNER JOIN est remplacé par RIGHT OUTER JOIN. Toutes les lignes de la seconde table sont incluses dans le résultat de la requête, même s'il n'existe pas de ligne correspondante dans la première table.
		Dans Access, la syntaxe des jointures gauche et droite est simplifiée en LEFT JOIN et RIGHT JOIN, comme le montrent les deux exemples suivants :
SELECT Nom, Prénom, Adresse, commune, [code postal] FROM Personnes LEFT JOIN Communes ON Communes.code_commune = Personnes.Code_commune;   SELECT Nom, Prénom, Adresse, commune, [code postal] FROM Personnes RIGHT JOIN Communes ON Communes.code_commune = Personnes.Code_commune;
		La jointure peut également être précisée dans la fenêtre graphique de définition d'une requête, comme nous l'avons vu au chapitre 13.

 

	4 - Le regroupement
		Nous avons étudié le regroupement au chapitre 14. Nous réutilisons la même table, intitulée "Résultats" et représentée ci-dessous.
Date Agence CA 06/01/2003 Nord 927,02 € 06/01/2003 Sud 1 098,46 € 06/01/2003 Est 561,29 € 07/01/2003 Nord 1 385,55 € 07/01/2003 Est 681,09 € 07/01/2003 Sud 1 401,56 €
		Nous effectuons le regroupement par date et nous sommons sur le CA. Nous obtenons le code SQL suivant :
SELECT Date, Sum(CA) AS CA_total FROM Résultats GROUP BY Date;
		dans lequel le regroupement est exprimé par la clause GROUP BY. La requête fournit le résultat suivant :
Date CA_total 06/01/2003 2 586,77 € 07/01/2003 3 468,20 €
		Nous avons vu au chapitre 14 qu'une requête avec regroupement peut être filtrée avant ou après regroupement. Nous créons un exemple de filtrage avant regroupement en éliminant l'agence Est. Nous obtenons le code SQL suivant :
SELECT Date, Sum(CA) AS CA_total FROM Résultats WHERE Agence Not Like "Est" GROUP BY Date;
		Nous reconnaissons une requête de sélection classique (dans les trois premières lignes), suivie d'un regroupement. La requête fournit le résultat suivant :
Date CA_total 06/01/2003 2 025,48 € 07/01/2003 2 787,11 €
		Nous rajoutons ensuite un filtrage après regroupement en ne retenant que les CA supérieurs à 2500 €. Nous obtenons le code SQL suivant :
SELECT Date, Sum(CA) AS CA_total FROM Résultats WHERE Agence Not Like "Est" GROUP BY Date HAVING Sum(CA)>2500;
		dans lequel le filtrage après regroupement utilise la clause HAVING. La requête fournit le résultat suivant :
Date CA_total 07/01/2003 2 787,11 €
		D'une manière générale, une requête avec regroupement s'écrit en SQL :
SELECT ......... FROM ........... WHERE ............ GROUP BY ........... HAVING ............;

 

	5 - Le comptage et les doublons
		Nous avons étudié les doublons au chapitre 15, en utilisant la table "Résultats", dans laquelle nous avons effacé une des valeurs du champ CA.
Date Agence CA 06/01/2003 Nord 927,02 € 06/01/2003 Sud 1 098,46 € 06/01/2003 Est 561,29 € 07/01/2003 Nord 1 385,55 € 07/01/2003 Est   07/01/2003 Sud 1 401,56 €
		Nous créons une requête effectuant un regroupement sur le champ "Date", et un comptage sur le champ "CA". Le code SQL s'écrit :
SELECT Date, Count(CA) AS Nombre_CA FROM Résultats GROUP BY Date;
		Nous voyons que le comptage est effectué par la fonction Count(). Le résultat de la requête est le suivant (la fonction de comptage ignore les Null) :
Date Nombre_CA 06/01/2003 3 07/01/2003 2
		Si nous appliquons la fonction "Count" au champ de regroupement lui-même, nous effectuons un comptage des doublons sur ce champ. Le code SQL correspondant s'écrit :
SELECT Date, Count(Date) AS Doublons_date FROM Résultats GROUP BY Date;
		La requête fournit le résultat suivant :
Date Doublons_Date 06/01/2003 3 07/01/2003 3
		Si nous prenons le terme "doublon" au sens strict, nous devons éliminer du résultat les comptages égaux à l'unité, en pratiquant un filtrage après regroupement. Le code SQL s'écrit alors :
SELECT Date, Count(Date) AS Doublons_date FROM Résultats GROUP BY Date HAVING Count(Date)>1;
		Le comptage des doublons sur deux champs s'écrit ainsi :
SELECT Date, Agence, Count(*) AS Doublons FROM Résultats GROUP BY Date, Agence;
		On notera que les deux champs doivent figurer dans la clause de regroupement. On notera également que l'argument de la fonction Count() devient l'astérisque, ce qui signifie que l'on compte les lignes sans se référer à une colonne particulière. Le code ci-dessus fournit le résultat suivant (il n'y a pas de doublons) :
Date Agence Doublons 06/01/2003 Nord 1 06/01/2003 Sud 1 06/01/2003 Est 1 07/01/2003 Nord 1 07/01/2003 Est 1 07/01/2003 Sud 1
		Le regroupement sans comptage nous permet d'éliminer les doublons. Si nous effectuons l'opération sur la colonne "Agence", nous obtenons la liste des agences. Le code SQL s'écrit :
SELECT Agence FROM Résultats GROUP BY Agence;
		La requête fournit le résultat suivant (les agences sont triées par ordre alphabétique) :
Agence Est Nord Sud
		Terminons par le comptage sans regroupement, qui permet d'obtenir le nombre d'enregistrements d'une table. Le code SQL s'écrit :
SELECT Count(*) AS Comptage FROM Résultats;
		La requête fournit le résultat suivant :
Comptage 6

 

		5 - Conclusion
			Comme dans le chapitre précédent, le langage SQL ne nous a pas permis d'effectuer des requêtes qui eussent été impossibles dans l'interface graphique d'Access. Il nous a cependant permis de simplifier la recherche des doublons (Cf le chapitre 15). Une commande SQL plus simple s'exécute plus vite, et la différence de temps devient sensible lorsqu'on traite un grand volume de données.
Chapitre 21 : les opérations ensemblistes en SQL

 

	1 - Introduction
		Le langage SQL permet de réaliser sur un groupe de deux tables les trois opérations de base de la théorie des ensembles, c'est à dire :
		l'union     l'intersection     la différence
		De ces trois opérations, seule l'union présente vraiment de l'intérêt. Si, dans les exposés relatifs à SQL, on regroupe généralement les trois opérations ensemblistes, c'est pour se conformer à une logique mathématicienne. Comme nous le verrons dans la suite de ce chapitre, l'intersection et la différence sont en fait des requêtes multi-tables particulières, que l'on peut réaliser même si l'on ne dispose pas d'une commande spécifique -- ce qui est effectivement le cas lorsqu'on utilise le SGBD Access.
		La requête union, par contre, est quasi irremplaçable : elle seule permet de mettre deux tables bout à bout en leur faisant jouer des rôles identiques, et en éliminant systématiquement les doublons. Certes, la requête ajout permet d'ajouter une table à une autre, mais dans ce cas les deux tables ne jouent pas le même rôle, et l'élimination des doublons -- si elle s'avère nécessaire -- requiert soit la création d'un index multi-champ, soit un traitement ultérieur (regroupement).
		A ce chapitre nous rajouterons un paragraphe concernant le produit cartésien de deux tables, requête qui n'est utilisée que très rarement et dans des cas très spécifiques.
		Comme pour les autres chapitres de ce tutoriel (ou tutorial, ou cours en ligne), nous utiliserons le SGBD Access comme support pratique.

 

	2 - L'union de deux tables
		L'union de deux tables est une une feuille de données (ou une table) contenant chaque ligne de la première table et chaque ligne de la seconde table. Les lignes communes aux deux tables ne sont conservées qu'en un seul exemplaire, c'est à dire que l'opération d'union élimine les doublons. Les champs que l'on fait correspondre dans les deux tables n'ont pas besoin de porter les mêmes noms ni de se présenter dans le même ordre -- ni même de posséder le même type de donnée si la transposition est possible (une date en texte, par exemple).
		Exemple 1. La figure ci-dessous illustre l'opération d'union dans le cas simple où les champs portent le même nom, possèdent les mêmes propriétés, et sont situés dans le même ordre.
nom prénom Chose Jules Machin Pierre Truc Patrick   union   nom prénom Pouf Jean Chose Jules   =   nom prénom Chose Jules Machin Pierre Pouf Jean Truc Patrick   Table1   Table2   Résultat
		Il n'existe pas dans Access d'interface graphique permettant de créer une requête Union. Il faut donc écrire soi-même le code SQL requis. Pour ouvrir l'éditeur de requêtes SQL, nous sélectionnons l'objet "Requêtes" dans la fenêtre "Base de données", nous n'introduisons aucune table dans l'interface graphique de définition des requêtes, et nous basculons en mode SQL.
		Si notre base contient les deux tables précitées, nous pouvons exécuter la requête SQL suivante, contenant l'opérateur UNION :
SELECT nom, prénom FROM Table1 UNION SELECT nom, prénom FROM Table2;
		A l'exécution de la requête, nous constatons qu'une feuille de données est créée, qui rassemble le contenu des deux tables, et en élimine les doublons. Si nous enregistrons la requête (sa structure, pas son contenu), Access fait précéder son nom de l'icône pour rappeler qu'il s'agit d'une requête de type "Union".
		Exemple 2. Les champs mis en correspondance n'ont pas besoin de porter le même nom, comme le montre l'exemple suivant, dans lequel nous avons modifié les intitulés des champs de la table "Table2" :
nom prénom Chose Jules Machin Pierre Truc Patrick   union   last-name first-name Pouf Jean Chose Jules   =   nom prénom Chose Jules Machin Pierre Pouf Jean Truc Patrick   Table1   Table2   Résultat
		La requête union correspondante est représentée ci-dessous. Le SGBD sait que "last-name" correspond à "nom", et "first-name" à "prénom", parce que les champs sont cités dans cet ordre dans les deux clauses SELECT. La feuille de données résultante emprunte les noms de ses champs à la première table. La présence des crochets traduit le fait que SQL ne tolère pas le tiret dans les noms des tables ni dans ceux des champs (caractère non autorisé).
SELECT nom, prénom FROM Table1 UNION SELECT [last-name], [first-name] FROM Table2;
		Exemple 3. Nous pouvons faire en sorte d'imposer les noms des champs dans la feuille de données résultante pour obtenir, par exemple, le résultat suivant :
nom prénom Chose Jules Machin Pierre Truc Patrick   union   nom prénom Pouf Jean Chose Jules   =   Col1 Col2 Chose Jules Machin Pierre Pouf Jean Truc Patrick   Table1   Table2   Résultat
		En SQL, la requête s'écrit ainsi :
SELECT nom AS Col1, prénom AS Col2 FROM Table1 UNION SELECT nom AS Col1, prénom AS Col2 FROM Table2;
		Exemple 4. Nous pouvons appliquer des critères de sélection (clause WHERE) aux enregistrements de chacune des deux tables que nous voulons réunir. Il faut cependant que nous fassions bien attention à bien définir le même critère dans les deux clauses SELECT. Ainsi, pour exclure les noms commençant par A, B ou C dans le résultat final :
nom prénom Chose Jules Machin Pierre Truc Patrick   union   nom prénom Pouf Jean Chose Jules   =   nom prénom Machin Pierre Pouf Jean Truc Patrick   Table1   Table2   Résultat (avec critères)
		nous utilisons la requête union suivante :
SELECT nom, prénom FROM Table1 WHERE nom>"D" UNION SELECT nom, prénom FROM Table2 WHERE nom>"D";
		Exemple 5. Pour faire en sorte que la requête union n'élimine pas les doublons, nous rajoutons l'opérateur ALL après UNION, comme le montre la requête ci-dessous :
SELECT nom, prénom FROM Table1 UNION ALL SELECT nom, prénom FROM Table2;
		La feuille de données obtenue résulte de la simple mise bout à bout des deux tables, sans tri ni élimination des doublons :
nom prénom Chose Jules Machin Pierre Truc Patrick   union all   nom prénom Pouf Jean Chose Jules   =   Col1 Col2 Chose Jules Machin Pierre Truc Patrick Pouf Jean Chose Jules   Table1   Table2   Résultat
		Exemple 6. Comment faire en sorte que la requête Union crée une table ? Une des techniques possibles consiste à emboîter la requête union dans une commande d'insertion. Le code SQL suivant :
INSERT INTO Table3 SELECT * FROM (SELECT nom, prénom FROM Table1 UNION SELECT [last-name], [first-name] FROM Table2);
		peut être exécuté à condition que la table "Table3" ait été préalablement créée (vide) -- mais cette condition n'est pas requise pour observer le résultat de la requête en mode feuille de données. La requête ci-dessus est reconnue par le SGBD Access comme une requête "Ajout" (à cause de la clause INSERT) et, si nous l'enregistrons, son nom apparaît précédé de l'icône correspondante. Le code ci-dessus nous offre un exemple d'emboîtement en SQL.
		Cet aspect didactique mis à part, il est plus simple, quand on opère dans le SGBD Access, de réaliser d'abord la requête union, puis de réutiliser son résultat dans une requête de sélection simple, à laquelle on demande de créer une table.
		Remarque. Si nous tentons de réaliser l'union de deux tables contenant un champ (nommé "essai") de type Mémo, Objet OLE ou Lien hypertexte, nous obtenons le message suivant (rédigé dans le jargon franglais des informaticiens) :
		Cela signifie que, dans le SGBD Access, la requête Union ne fonctionne pas sur les champs de type Mémo, Objet OLE et Lien hypertexte. Pour réunir deux tables comportant des champs de ce type sans les perdre, il faut utiliser la requête Ajout.
		La requête Union présente un autre défaut : elle est lente à l'exécution, aussi bien programmée soit-elle. Cela provient du fait qu'elle trie et dédoublonne, alors que la requête Ajout se contente de compléter une table avec les données d'une autre. A titre d'exemple, sur un PC de qualité standard, il faut environ plusieurs minutes (3-4) pour réaliser l'union de 10.000 enregistrements avec 300.000 autres.

 

	3 - L'intersection de deux tables
		L'intersection de deux tables est une une feuille de données (ou une table) contenant seulement les lignes communes aux deux tables. Les conditions sont les mêmes que pour l'union. La figure ci-dessous illustre l'opération intersection :
nom prénom Chose Jules Machin Pierre Truc Patrick   inter   nom prénom Pouf Jean Chose Jules   =   nom prénom Chose Jules
		Le code SQL correspondant dépend de la version utilisée. En SQL1, il s'écrit :
SELECT nom, prénom FROM Table1 WHERE Table1.nom IN (SELECT nom FROM Table2) AND Table1.prénom IN (SELECT prénom FROM Table2);
		Cette syntaxe, qui fait appel à l'emboîtement autant de fois qu'il y a de colonnes, a été simplifiée par l'introduction de l'opérateur INTERSECT en SQL2. Le nouveau code s'écrit :
SELECT nom, prénom FROM Table1 INTERSECT SELECT nom, prénom FROM Table2;
		Mais... le SGBD Access ne reconnaît pas l'opérateur INTERSECT. Il affiche un message d'erreur qui, bien entendu, ne correspond pas à la situation (une habitude bien ancrée en informatique). Seule reste en lice la première syntaxe, qui apparaît de la manière suivante dans l'interface graphique :
		Comme on peut le constater, il est possible d'utiliser des critères écrits en code SQL dans l'interface graphique. Ma foi, autant utiliser la fenêtre SQL directement !
		Que l'opérateur INTERSECT ne soit pas reconnu par Access n'est pas un drame. Outre les deux commandes SQL déjà citées, nous en imaginer une troisième, qui résulte directement de la définition de l'intersection :
SELECT DISTINCT Table1.nom, Table1.prénom FROM Table1, Table2 WHERE Table1.nom=Table2.nom AND Table1.prénom=Table2.prénom;
		La présence de l'opérateur DISTINCT est indispensable, sinon tous les enregistrements de l'intersection figurent en double dans la feuille de données. Le SGBD les écrit deux fois parce qu'il les trouve une fois dans la première table, et une autre fois dans la seconde.
		Le code ci-dessus est représenté ainsi dans l'interface graphique (on vérifie en outre que la propriété "Valeurs distinctes" de la requête vaut "Oui") :
		Nous pouvons encore traduire l'intersection en utilisant des relations (ou jointures) entre les champs des deux tables. Rappelons qu'une relation créée dans la fenêtre graphique de définition des requêtes possède une existence éphémère, ce que prouve le fait qu'elle n'apparaît pas dans la fenêtre "Relations". Pour traduire qu'un enregistrement de la seconde table est identique à celui de la première table, nous utilisons l'opérateur "Est Pas Null". Dans l'interface graphique, la requête apparaît comme le montre la figure ci-dessous.
		La version SQL de cette requête s'écrit :
SELECT Table1.nom, Table1.prénom FROM Table1 INNER JOIN Table2 ON Table1.nom = Table2.nom AND Table1.prénom = Table2.prénom WHERE Table2.nom Is Not Null AND Table2.prénom Is Not Null;