Bases de données relationnelles Cours Les bases de données rélationnelles sont un type particulier de bases de données, dans lequel les données sont présentées et maniuplées à travers des tables. Après avoir replacé dans sont contexte ce type de bases de données dans l’ensemble des types de bases de données qui sont définis et utilisés actuellement, nous présenterons les bases de données relationnelles et en particulier le vocabulaire spécifique qui est utilisé dans ce domaine pour décrire les tables et autres éléments. Nous présenterons la méthode utilisée pour définir une base de données qui répond aux besoins et exigences d’un projet, et le passage à l’implémentation, c’est-à-dire le processus de conception du modèle conceptuel de données et sa transformation en le modèle logique correspondant. 1 - Introduction : Différents types de bases de données Il existe actuellement cinq grands types de bases de données : Les bases hiérarchiques Ce sont les premiers SGBD apparus. Elles font partie des bases navigationnelles constituées d’une gestion de pointeurs entre les enregistrements. Le schéma de la base doit être arborescent. Les bases réseaux. Sans doute les bases les plus rapides, elles ont très vite supplanté les bases hiérarchiques dans les années soixante-dix. Ce sont aussi des bases navigationnelles qui gèrent des pointeurs entre les enregistrements. Cette fois-ci, le schéma de base est beaucoup plus ouvert. Les bases relationnelles. À l’heure actuelle, ce sont les plus utilisées. Les données y sont représentées en tables. Elles sont basées sur l’algèbre relationnelle et un langage déclaratif (généralement SQL). Les bases déductives. Les données y sont représentées en tables (prédicats), le langage d’interrogation se base sur le calcul des prédicats et la logique du premier ordre. Les bases objets. Les données y sont représentées en tant qu’instance de classes hiérarchisées. Chaque champ est un objet. De ce fait, chaque donnée est active et possède ses propres méthodes d’interrogation et d’affectation. L’héritage est utilisé comme mécanisme de factorisation de la connaissance. La répartition du parc des SGBD n’est pas équitable entre ces 5 types de base : 75% sont relationnels, 20% réseaux, les 5% restants étant partagés entre bases déductives et objets. Ces chiffress risquent néanmoins d’évoluer d’ici quelques années, et la frontière entre bases relationnelles et objets pourrait être éliminée par l’introduction d’une couche objets sur les bases relationnelles. Quelques systèmes reconnus : http://www.oracle.com Oracle base relationnelle http://www.software.ibm.com DB2 base relationnelle http://www.sybase.com Sybase base relationnelle http://www.microsoft.com SQL Server base relationnelle ftp://s2k-ftp.CS.Berkeley.EDU/pub/ingres base relationnelle Ingres http://www.informix.com Informix base relationnelle http://www.o2tech.fr ou base objet O2 http://www.ardentsoftware.com http://www.gemstone.com Gemstone base objet http://www.objectdesign.com ou base objet ObjectStore http://www.odi.com http://cai.com/jasmine Jasmine base objet et des bases relationnelles sur micro : Page 1 sur 16 http://www.microsoft.com Access 2000 base relationnelle http://www.corel.com Paradox 8.0 " Visual DBase http://www.borland.com " http://www.microsoft.com FoxPro " http://www.claris.fr FileMaker " http://www.aci.fr 4D 6.5 " http://www.pcsoft.fr Windev " Il existe aussi quelques freewares et sharewares que l’on peut trouver sur Internet pour s’initier aux bases de données relationnelles comme : http://www.mysql.net ou MySQL http://www.mysql.com http://Hughes.com.au MSQL http://www.postgresql.org Postgres http://www.microsoft.com InstantDB Entièrement écrit en Java 2 - Les modèles de données a) Les niveaux ANSI/SPARC Pour assurer les objectifs précédemment décrits, les trois niveaux suivants de description (voir la figure suivante) ont été distingués par le groupe ANSI/X3/SPARC en 1975 : Le niveau conceptuel Il correspond à ce que l’on retrouve dans la méthode Merise avec les modèles de données MCD (modèle conceptuel de données) et MLD (modèle logique de données). Le niveau interne Il correspond à la structure de stockage des données : types de fichiers utilisés, caractéristiques des enregistrements (longueur, composants), chemin d’accès aux données (type d’index, chaînages, etc.). Le niveau externe Il est caractérisé par l’ensemble des vues externes qu’ont les groupes d’utilisateurs. modèles externes modèle externe modèle externe modèle externe schéma conc eptuel schéma interne Figure 1 : Le modèle Ansi/Sparc Ces trois niveaux correspondent à trois métiers bien précis de l’entreprise. Le concepteur de SGBD s’occupe principalement du schéma interne. Il travaille sur les structures de données de base, l’optimisation des techniques d’accès aux données (qu’on appelle « techniques de hachage ») et les dispositifs d’optimisation des requêtes (notamment en définissant des index). L’administrateur de bases Page 2 sur 16 de données conçoit les bases, organise les tables, optimise les requêtes (notamment en définissant des index) et gère les droits d’accès. Enfin les développeurs et utilisateurs écrivent les programmes applicatifs et utilisent les outils de haut niveau du SGBD permettant une abstraction logique sur les données (notamment les vues). Pour s’attaquer à tout problème, il est toujours nécessaire de réfléchir profondément aux tenants et aboutissants de ce que l’on veut réaliser. La phase de conception nécessite souvent de nombreux choix qui auront parfois des répercussions importantes par la suite. La conception des bases de données ne fait pas exception à la règle. Les théoriciens de l’information ont donc proposé des méthodes permettant de structurer un projet et de présenter de manière abstraite le travail que l’on souhaite réaliser. Ces méthodes ont donné naissance à une discipline, l’analyse, et à un métier, l’analyste. L’analyse est la discipline qui étudie et présente abstraitement le travail à effectuer. La phase d’analyse est très importante puisque c’est elle qui sera validée par les utilisateurs avant la mise en œuvre du système concret. Il existe de nombreuses méthodes d’analyse (AXIAL, OMT, etc.), la plus utilisée en France étant la méthode Merise. Merise sépare les données et les traitements à effectuer avec le système d’information en différents modèles conceptuels et physiques. Le plus intéressant pour la conception d’une base de données est le MCD. Le MCD (modèle conceptuel de données) est un modèle abstrait de la méthode Merise permettant de représenter l’information d’une manière compréhensible aux différents services de l’entreprise. Il permet une description statique du système d’information à l’aide d’entités et d’associations. Le travail de création d’une base de données par le concepteur commence juste après celui des analystes qui ont établi le MCD. b) Définitions La propriété est une donnée élémentaire et indécomposable du système d’information, par exemple, une date de début de projet, la couleur d’une voiture, une note d’étudiant. L’entité est la représentation, dans le système d’information, d’un objet matériel ou immatériel ayant une existence propre et conforme aux choix de gestion de l’entreprise. L’entité est composée de propriétés. Par exemple, une personne, une voiture, un client, un projet sont en général des entités. nom de l’ entité . . . li ste des propriétés . . Figure 2 : Représentation graphique d'une entité L’association traduit dans le système d’information le fait qu’il existe un lien entre différentes entités. Le nombre d’intervenants dans cette association caractérise sa dimension : réflexive sur une même entité ; binaire entre deux entités ; ternaire entre trois entités ; n-aire entre n entités ; Page 3 sur 16 Personne Service nom préno m . . . . type (admi n, scola...) . . . trava ill e dans un Figure 3 : Représentation graphique d’une association binaire Matériel 0,n numMat . . . . est composé de 0,n Figure 4 : Lien réflexif typique Avion numAvion . . . . Pilote 0,n 0,n Vol numPilote . . . 0,n Lign e numLigne . . . Figure 5 : Lien ternaire typique Des propriétés peuvent être attachées aux associations. Par exemple, un employé peut passer 25% de son temps dans un service et 75% de son temps dans un autre. L’association « travaille dans » qui relie une personne à un service portera dans ce cas la propriété « volume de temps passé ». Les cardinalités caractérisent le lien entre une entité et une association. La cardinalité d’une association est constituée d’une borne minimale et d’une borne maximale : minimale : nombre minimal de fois qu’une occurrence d’une entité participe aux occurrences de l’association, généralement 0 ou 1 ; maximale : nombre maximal de fois qu’une occurrence d’une entité participe aux occurrences de l’association, généralement 1 ou n. Les cardinalités maximales sont nécessaires pour la création de la base de données. Les cardinalités minimales sont nécessaires pour exprimer les contraintes d’intégrité. Page 4 sur 16 Personne nom prénom . . . . Service 1,n trava ill e dans un 1,n volume admi nistration gestion informatique . . . . Figure 6 : Représentation des cardinalités De la Figure 6, on déduit qu’ « une personne peut travailler dans un ou plusieurs services ». On constate de plus que « dans chaque service, il y a au moins une personne mais il peut y en avoir plusieurs ». Enfin, une mesure du « volume de travail » est stockée pour chaque personne travaillant dans un service donné. Remarque : Il existe une notation des cardinalités « à l’américaine » dans laquelle on ne note que les cardinalités maximales. Il peut donc y avoir deux sortes de cardinalités « américaines » : (1 : n) et (n : m). Dans la figure précédente, la notation américaine serait n : m, puisuq’une personne peut travailler dans plusieurs services et que, dans un service, il peut y avoir plusieurs personnes. Un lien hiérarchique est un lien 1 : n en notation américaine. Un lien maillé est un lien n : m en notation américaine. Seules les cardinalités maximales permettent de déterminer le nombre de tables. Les cardinalités minimales servent à exprimer certaines contraintes d’intégrité mais ne modifient en aucun cas la structure des tables de la base. L’identifiant d’une entité est constitué d’une ou plusieurs propriétés de l’entité de sorte que, à chaque valeur de l’identifiant corresponde une et une seule occurrence de l’entité. L’identifiant d’une association est constitué de la réunion des identifiants des entités qui participent à l’association. Dans la Figure 4, l’entité Matériel a pour identifiant, le numéro de matériel. Dans ce lien réflexif d’une entité sur elle-même, un matériel peut être constitué d’un ou plusieurs autres matériels et viceversa. La Figure 5, avec les entités Avion, Pilote et ligne, reliées par l’association ternaire définissant le Vol. Un vol est ici caractérisé un numéro d’avion fixé, un numéro de pilote fixé et un numéro de ligne fixé. Il en résulte qu’un pilote peut voler avec des avions différents sur une même ligne. L’identifiant est représenté en souligné dans le MCD. Il constituera par la suite, la clé d’une table relationnelle. La conception d’un MCD avec de nombreuses entités est parfois une tâche ardue et nécessite un savoirfaire que seuls les analystes professionnels peuvent acquérir par l’expérience. La gestion des dates, par exemple, est souvent délicate. Dans le MCD de l’illustration suivante, la relation entre Salariés et Tâche est constitué d’un lien maillé dont les dates sont de simples propriétés. Il en résulte qu’un salarié ne pourra participer plusieurs fois à la même tâche ! En effet, la clé de la table correspondant à l’association sera constituée du couple (numéro de salarié, numéro de tâche) qui devra donc être unique. Dans de nombreux MCD, on est souvent obligé de créer une entité Date dès qu’une date doit faire partie d’une clé, et bien que l’on ne traduise jamais cette entité par une table. Précisons enfin qu’il est toujours difficile de dissocier les données des traitements qui seront effectués. Le MCD est donc généralement associé à un MCT (modèle conceptuel de traitements). Souvent, on modifie le MCD ou directement le MLD (modèle logique de données), pour améliorer les traitements. C’est pourquoi, dans l’exemple précédent, on ne crée pas une table pour les dates. Pendant la conception, on ne traite que les cas « normaux », puis on vérifie et on modifie, si besoin, les modèles en fonction des cas exceptionnels !Pourquoi une requête est-elle meilleure qu’une autre ? Page 5 sur 16 c) Exemple de MCD Le modèle conceptuel de données de l’illustration suivante, décrit le système d’information d’une petite société de services. Cette société réalise des projets commandés pas des clients. Les projets sont composés de plusieurs tâches qui seront réalisées pas les salariés de l’entreprise. Chaque tâche a un coût qui lui est propre. Plusieurs salariés peuvent participer à une même tâche et, bien sûr, une tâche est en général réalisée par plusieurs salariés. En général, les salariés sont affectés à une tâche pour une durée déterminée par une date de début et de fin. On considère qu’un salarié ne peut participer qu’une seule fois à une tâche donnée. Pour effectuer ce travail, il utilise différents matériels référencés par l’entreprise. Un matériel peut être composé de plusieurs autres matériels de l’entreprise. Un projet est toujours coordonné par un chef de projet, salarié de l’entreprise. Un chef de projet encadre donc d’autres salariés. Le personnel est obligatoirement rattaché à une seule des divisions de l’entreprise mais peut, en revanche, être regroupé dans différentes équipes de l’entreprise. Équip e Division Cli ent Numéro de l’équipe Numéro de la division Numéro du client Spécialisation Nom de la division CA de la division Adresse de la division .Nom du client Raison sociale Adresse du client Activité du client Contact chez le client Téléphone du client Fax du client 1,n 1,n regroupe travaille 1,n 1,1 0,n Salarié a pour chef 0,1 Numéro du salarié utilise 0,n 0,n .Nom du salarié Prénom du salarié Fonction du salarié Rémunération du salarié Commission du salarié encadre 0,n commande est chef de 0,n Matériel 0,n 1,1 Numéro de matériel coordonne .Nom du matériel .Type de matériel Projet 1,1 0,n composant 0,n Participe Date début Date fin Numéro du projet .Thème du projet .Libellé du projet .Date début du projet .Date échéance .Date fin du projet composé 1,n 0,n compose Tâche 1,1 constitue Nom de la tâche Coût de la tâche Figu Figure 7 : Exemple de MCD Page 6 sur 16 On le voit, le MCD nécessaire à cette entreprise contient des liens hiérarchiques comme travaille, des liens maillés comme regroupe, des liens réflexifs comme encadre ou compose. Page 7 sur 16 3 - Introduction du “relationnel” C’est en 1970, au centre de recherche d’IBM à San José (USA) qu’Edgard Frank Codd établit la théorie des bases de données relationnelles. Il cherchait à l’époque à formaliser de façon rigoureuse l’organisation des données. Pour cela, il a utilisé la théorie des ensembles en mathématiques pour en dégager une algèbre relationnelle. Cette algèbre lui a permis d’organiser tout type de données en un ensemble de tables et de relations. On a alors parlé de bases de données relationnelles. L’étape la plus importante lors de la conception d’une base relationnelle est l’organisation des informations à manipuler. Une bonne organisation va permettre un accès rapide aux données, assurer la cohérence des informations, autoriser des recherches à partir de critères complexes, faciliter les mises à jour, gérer la confidentialité, etc. Les systèmes de gestion de bases de données relationnelles organisent les données en tables (à la manière d’un tableur). Il est simple, facile à comprendre et fidèle à un cadre mathématique (l’algèbre relationnelle). Le concept mathématique sous-jacent est celui de relation de la théorie des ensembles, qui se définit comme un sous-ensembles du produit cartésien de plusieurs domaines. a) Le modèle relationnel Une base de données relationnelle est structurée de manière hiérarchique en commençant par la base ellemême, qui contient un ensemble de tables, qui contiennent des enregistrements, qui contiennent des champs.L’efficacité d’une base de données relationnelle provient du fait que les informations sont organisées selon le même schéma que celui sur lequel s’applique l’algèbre relationnelle de E.F. Codd. Ce schéma fait intervenir les concepts de tables et de relations. Un domaine est un ensemble fini ou infini de valeurs possibles : le domaine des entiers, le domaine des couleurs du drapeau français {bleu, blanc, rouge }, etc… On utilise alors le produit cartésien d’un ensemble de domaines pour définir un ensembles de n-uplets. Le produit cartésien d’un ensemble de domaines D1, D2, …Dn que l’on écrit D1x D2 x … x Dn est un ensemble de n-uplets (ou tuples) < V1, V2, …Vn > tel queVi Di. Exemple : le produit cartésien des domaines D1 = { Connu, Tarre, Mauve} et D2 = Alain, Guy} donne : Connu Alain Connu Guy Tarre Alain Tarre Guy Mauve Alain Mauve Guy Le produit cartésien est une opération plus générale que la simple application à des domaines. On peut par exemple l’appliquer aussi à des ensembles de tuples, ce que nous ferons en algèbre relationnelle. Une table relationnelle est un sous-ensemble du produit cartésien d’une liste de domaines. Elle est généralement caractérisée par un nom permettant de l’identifier clairement. Personne D1 D2 Bon Jean Bien Maeva Harne Luc Afin de rendre l’ordre des colonnes sans importance tout en permettant plusieurs colonnes de même domaine, on associe un nom à chaque colonne. Les différentes colonnes d’une table constituent ce que l’on appelle les attributs de la table relationnelle. Page 8 sur 16 Remarque : comme pour toute définition d’ensembles, il existe en fait deux possibilités pour définir une relation, en intention ou en extension. La forme intentionnelle est utilisée dans les bases de données déductives, par exemple {(x,y,z) (N,Z,Q) tels que x+y > z}, mais pas dans les bases relationnelles ni objet. La forme extensionnelle qui consiste à spécifier un à un tous les tuples de la relation est la seule forme utilisable dans les bases de données relationnelles. Elle est bien sûr utilisée aussi dans tous les autres types de base. Le schéma d’une table relationnelle est constitué de l’ensemble des attributs de la table. Par extension, le schéma de la base de données est constitué de l’ensemble de toutes les tables. Une base de données relationnelle est une base donnée dont le schéma est un ensemble de schémas de tables relationnelles et dont les occurrences sont des tuples de ces tables. b) Passage du MCD aux tables relationnelles Une fois le MCD écrit par les analystes, le travail du concepteur de bases de données consiste à traduire ce modèle en un modèle plus proche du SGBD utilisé : le MLD (modèle logique de données). Dans le MLD relationnel, l’unique type d’objet existant est la table. La méthode de passage d’un MCD Merise aux tables relationnelles est simple et systématique : Traitement des entités : chaque entité devient une table ; chaque propriété d’une entité devient une colonne de cette table ; l’identifiant d’une entité devient la clé primaire de la table correspondante (création d’un index). Traitement des associations : Une association (0,n)–(0,1) (lien hiérarchique) provoque la migration d’une clé étrangère (l’identifiant côté (0,n)) vers la table de l’entité côté (0,1). Si des propriétés étaient sur l’association, elles migreraient côté (0,1). Entit é 1 A B Entit é 2 0,1 Relation 0,n C D E Figure 8: Modèle conceptuel avec lien hiérarchique Dans l’exemple ci-dessus, l’identifiant C de l’Entité 2 deviendra une clé étrangère dans la table associée à l’Entité 1, et la relation E devient une colonne de la table associée à l’Entité 1 Table 1 Table 2 A B C E C D Figure 9 : Modèle logique correspondant au modèle conceptuel précédent Une association (0,n)–(0,n) (lien maillé) donne naissance à une nouvelle table. Les identifiants des entités auxquelles l’association est reliée migrent dans cette table. La clé primaire de cette nouvelle table est constituée de la réunion de ces identifiants. Si des propriétés étaient portées par l’association, elles migreraient dans la nouvelle table aussi côté (0,1) ; Page 9 sur 16 Entit é 1 A B Entit é 2 0,n Relation C D 0,n E Figure 10 : MCD avec lien maillé Table 1 Table 3 Table 2 A B A C E C D Figure 11 : MLD correspondant à la figure pour le lien maillé Les associations n-aires sont gérées, comme précédemment, avec la naissance d’une nouvelle table. Les schémas du MLD contiennent généralement des flèches indiquant les reports de clé (clés étrangères). Ces flèches sont présentes à titre informatif. En aucun cas, ces flèches ne correspondent à un pointeur physique. Les tables relationnelles sont toutes physiquement indépendantes. Fourn isseur fno nom adresse vill e Produits Command es 1,n 1,n quté pno design prix poids couleur Figure 12 : MCD Fournisseur-Produits-Commandes Dans le cas « fournisseurs-produits-commandes » ci-dessus, l’association Commandes est un lien maillé porteur d’une propriété. Il y a donc création d’une table Commandes avec report des clés des entités kiées, ce qui nous donne bien les trois tables vues plus haut. Le lecteur averti notera que, dans ce schéma, il n’est pas possible de passer deux commandes différentes au même fournisseur. Pour résoudre ce problème, il aurait fallu transformer la relation Commandes en une entité Commandes. Rappel : seules les cardinalités maximales servent à définir le nombre de tables et les reports de clé.Les cardinalités minimales ne servent qu’à préciser par la suite si les colonnes peuvent prendre la valeur NULL ou pas. Dans l’exemple du MCD de la mini-entreprise (voir page 6 ), on obtient les onze tables suivantes : à partir des entités à partir des associations Division Regroupe Équipe Participe Client Compose Matériel Utilise Salarié Projet Tâche Page 10 sur 16 c) Exemple rudimentaire L’illustration suivante représente la base de données « Exemple », constituée de deux tables. Les tables sont des conteneurs répartissant sémantiquement l’information. Elles contiennent un ensemble de données concernant une même entité. Des tables habituelles sont les tables « Clients », « Entreprises », « Produits », « Commandes »… Une table est divisée en enregistrements, chaque enregistrement contient toutes les informations d’une même entité. Dans une table « Clients », chaque enregistrement contient toutes les informations sur un client donné. La valeur d’un attribut pour un enregistrement, est un champ. Ainsi, un enregistrement est divisé en champs, chaque champ contient une information particulière pour une entité contenue dans la table. Dans une table « Clients », le nom « Bon » d’un client sera contenu dans un champ « Nom ». “Exemple” “Clie nts” Bon Jean 05.02.03.04.05 Bien Maeva 03.23.24.25.77 Harne Luc 04.38.44.55.66 “Entreprises” Base de donnée s Au bon miel 13 bd des abeill es, 87000 La Roche Thé SA 14, rue du Lac, 97111 Morne à l’ Eau Table Enreg ist reme nt Champ Il faut différencier les contenus d’une table et de sa structure. Dans la base de données de l’illustration cidessus, les deux tables « Clients » et « Entreprises » contiennent les informations suivantes : Contenu de la table « Clients » Nom Prénom Téléphone Bon Jean 05.02.03.04.05 Bien Maeva 03.23.24.25.77 Harne Luc 04.38.44.55.66 Contenu de la table « Entreprises » Nom Adresse Au bon miel 13, bd abeilles, 87800 La Roche Thé SA 14, rue du lac, 97111 Morne A l’Eau Chaque enregistrement de la table « Clients » a la même structure : un nom, un prénom et un numéro de téléphone. Nous décrivons les structures de ces tables sous la forme suivante : Page 11 sur 16 i) Choix des champs Pour faciliter la manipulation de la base de données, il est indispensable de découper les enregistrements en un maximum de champs. Les champs doivent permettre d’accéder directement aux informations, en évitant de faire plusieurs fois les mêmes « calculs ». Par exemple, un seul champ pour toute une « Adresse » n’est souvent pas judicieux, car il oblige à extraire le nom de la ville par exemple pour imprimer des étiquettes d’adresse. On conseillera donc plutôt que Avec cette nouvelle structure, les informations sont clairement désignées et chaque information sur une adresse est facilement accessible. ii) Choix des tables Concernant les tables, de mauvais choix peuvent limiter les traitements possibles, voir entraîner l’impossibilité d’exploiter les informations stockées. Par exemple pour gérer une collection de disques, la solution la plus simple est de définir une table « Albums » de la manière suivante : Supposons que le contenu à mettre dans cette table soit : Nom Prénom Titre Année Nicouette Sandra C’est la ouate 1994 Moitout Eddy T’as pas, t’as pas tout dit 1966 Moitout Eddy C’est la ouate 1994 Cette structure de table présente plusieurs inconvénients : Redondance : nous avons deux album de Eddy Moitout, ce qui oblige à stocker deux fois son nom et son prénom. « C’est la ouate » a deux interprètes, nous répétons donc deux fois les informations concernant ce titre. Si l’ont fait une faute de frappe, on peut rendre les données incohérentes. Pérennité : la structure choisie rend presque impossible l’évolution de la base de données : si on veut ajouter une information sur l’auteur comme sa nationalité, il est nécessaire d’ajouter un champ dans la table et de mettre à jour tous les renseignements. Taille et efficacité : d’après les points précédents, avoir une seule table implique énormément de redondance, ce qui augmente sensiblement la taille occupée par la base de données. La place occupée par Page 12 sur 16 le contenu des tables influe directement sur la rapidité d’accès aux informations et donc sur la vitesse d’exécution des traitements. La solution au problème de redondance est la création de plusieurs tables, chacune regroupant les informations concernant une même entité. Dans l’exemple d’une collection de disque, le plus naturel est de construire une table « Auteurs » et une table « Album ». Nom Prénom Nicouette Sandra Moitout Eddy Titre C’est la ouate T’as pas, t’as pas tout dit Année 1994 1966 Il devient alors très simple d’ajouter des informations concernant les albums, comme le genre et le support. Titre Année Genre Support C’est la ouate 1994 variété CD T’as pas, t’as pas tout dit 1966 variété K7 Avec l’utilisation de ces deux tables, nous avons donc résolu le problème de redondance et de pérennité. iii) Choix des clés primaires Nous disposons, dans l’exemple précédent, de deux tables, mais nous avons perdu le lien entre les albums et leurs auteurs : quels sont les interprètes des différents albums ? La solution est de donner un numéro à chaque auteur et d’ajouter ce numéro dans chaque enregistrement de la table « Album ». Pour que cela fonctionne, il faut une numérotation unique : il faut éviter que deux auteurs puissent avoir le même numéro. Ce numéro unique est appelé clé primaire. Une fois le nouveau champ « N° auteur » ajouté, la structure de la table « Auteurs » est : Pour pouvoir associer un album à un auteur, il faut ajouter le champ « N° auteur » dans la table « Album » Essayons de remplir nos deux tables avec nos deux albums : Page 13 sur 16 N° Nom Prénom auteur 1 Nicouette Sandra 2 Moitout Eddy N° Titre Année Genre Support auteur 1 C’est la ouate 1994 variété CD T’as pas, t’as pas tout dit 1966 variété K7 ??? En remplissant la table « Album », on se rend compte qu’on ne peut pas remplir le champ « N° auteur », puisqu’il y a deux auteurs pour le deuxième album.On ne peut pas ajouter un champ pour un deuxième auteur, puisqu’il pourrait aussi bien y en avoir trois, etc…De plus pour trop d’album, se champ serait vide. La solution est d’utiliser une troisième table qui fera le lien entre un auteur et un album. Pour cela, nous allons numéroter les albums en utilisant une clé primaire « N° album », et la nouvelle table servira à mettre les numéros en relation, avec un enregistrement par relation. Ce qui conduit aux 3 tables suivantes : N° Nom Prénom auteur 1 Nicouette Sandra 2 Moitout Eddy et N° Titre Année Genre Support album 1 C’est la ouate 1994 variété CD 2 T’as pas, t’as pas tout dit 1966 variété K7 N° auteur 1 1 2 N° album 1 2 2 Le schéma ci-dessous reprend une partie des trois tables pour bien mettre en évidence l’intérêt de la nouvelle table « Auteurs-Albums » Nom Prénom N° auteur Nicoue tte Sand ra 1 Moit out Eddy 2 N° auteur 1 1 2 N° album 1 2 2 N° album Titre 1 C’est la oua te 2 T’as pas, t’as pas tout dit Cette nouvelle organisation des données a plusieurs avantages. Elle évite la redondance. Les informations concernant un auteur ou un album ne sont saisies qu’une seule fois. On évite tout problème d’incohérences dû à des fautes de frappe. L’ajout d’une table intermédiaire faisant le lien entre les auteurs Page 14 sur 16 et les albums est très souple puisqu’il n’y a plus de limitations portant sur le nombre d’auteur par album. Enfin, le découpage de l’information en tables de petites tailles facilité l’exploitation des données. Les clés primaires permettent de désigner de manière unique un enregistrement dans une table. Ces clés sont indispensables à l’application des principes de l’algèbre relationnelle et donc à la bonne exploitation de la base de données. En fait, chaque table devrait contenir une clé. Pour les tables « Auteurs » et « Album », nous avons ajouté des champs numérotant les enregistrements. Par contre dans la table « Auteurs-Albums », nous n’avons rien prévu. Il est possible de définir simplement une clé dans cette table. Il suffit de définir une clé correspondant au couple [ « n° auteur », « n° album » ]. Noter que ces couples sont uniques même si un « N° auteur » ou un « N° album » peut apparaître plusieurs fois. Une clé, construite sur plusieurs champs, s’appelle une clé composite. On parle souvent de clé double lorsqu’elle est construite sur deux champs. iv) Les relations Après les tables, le deuxième concept utilisé dans les bases de données relationnelles est la relation. Les relations permettent de décrire de manière pratique les liens entre les tables. Dans notre exemple de collection de disques, les relations décrivent le fait que la table « Auteurs-Albums » contient des numéros d’auteur et d’album qui correspondent exactement à des numéros trouvés dans les tables « Auteurs » et « Albums ». Les relations utilisent des clés primaires et composites pour établir les liens entre les tables. Ces liens ne sont possibles que parce qu’il y a unicité des clefs dans chaque table. Il existe trois types de relations : (1n), (n-m), et (1,1). On parle de relation « un à plusieurs » notée (1-n) lorsqu’un enregistrement d’une table A peut-être en relation avec plusieurs enregistrements d’une table B et qu’un enregistrement de la table B ne peut pas être en relation avec plusieurs enregistrements de la table A. L’exemple typique est celui de la filiation. On parle de relation « plusieurs à plusieurs » notée (n-m) lorsqu’un enregistrement d’une table A peut être en relation avec plusieurs enregistrements d’une table B et qu’un enregistrement de la table B peutêtre en relation avec plusieurs enregistrements de la table A. Dans l’exemple de la collection de disques, un auteur peut interpréter plusieurs albums et un album peut être interprété par plusieurs auteurs. On a donc une relation « plusieurs à plusieurs ». Nous pouvons schématisé ces liens avec la figure. En fait, ce type de relation n’est possible qu’en théorie. Pratiquement, il n’est pas possible de gérer des relations « plusieurs à plusieurs » et il faut passer par des tables intermédiaires. Page 15 sur 16 C’est en fait exactement ce que nous avons fait lorsque nous avons construit nos tables. Retenez donc que lorsqu’on a besoin d’une relation « plusieurs à plusieurs », il est obligatoire de passer par une table intermédiaire. On parle de relation « un à un » notée (1-1) lorsqu’un enregistrement d’une table A peut être en relation avec au plus un enregistrement d’une table B et qu’un enregistrement de la table B peut être en relation avec au plus un enregistrement de la table A. Ce type de relation est très peu utilisé. En effet, deux tables A et B en relation « un à un » peuvent être remplacées par une seule table C contenant les champs des tables A et B. Le seul intérêt de construire deux tables en relation « un à un » est lié à des raisons de sécurité. Par exemple, la deuxième table peut contenir des données confidentielles comme un salaire ou une prime, son accès sera limité. Le plus simple est alors de mettre les informations sensibles dans une table à part et d’en limiter l’accès. Page 16 sur 16