Site A.T.L.A.N.T.I.C-83
COURS = Initiation aux bases de données (Chapitre_4) - VERSION: 1.0 (M.A.J: 26/11/2017)
- AUTEUR(s): Bernard GIACOMONI
Ecran large mobile
Retour
sommaire cours

IV.LE MODÈLE LOGIQUE DES DONNÉES:

AVANT PROPOS:
Nous avons étudié au chapitre précédent le MODÈLE CONCEPTUEL DES DONNÉES (MCD) dont le but est d'élaborer une représentation du SYSTEME D'INFORMATION compréhensible au niveau de l'UTILISATEUR, à l'aide de concepts appelés ENTITÉS et ASSOCIATIONS.

Cependant, il serait très compliqué de développer directement une base de données directement à partir du MCD. En effet, les notions d'entités et d'associations n'ont pas de traduction directe dans les langages de requêtes acceptés par les S.G.B.D: ces langages sont plutôt basés sur la notion de TABLES, qui est très proche de celle de TABLEAUX ASSOCIATIFS, bien connue dans le domaine des langages de programmation.

De ce fait, avant d'aborder la programmation d'une base de données (Modèle Physique des Données-MPD), il est commode de passer par l'étape du Modèle Logique des Données (MLD), qui, lui, est bâti à partir de la notion de TABLE. Ces TABLES, nous le verront plus loin, peuvent représenter à la fois des entités et des associations: elles permettent donc de traduire entièrement le MCD en MLD.

Le présent chapitre étudie la manière de passer du Modèle Conceptuel au Modèle Logique.

IV.1.REPRÉSENTATION D'UNE ENTITÉ SOUS FORME DE TABLE:

IV.1.1.PRINCIPE:

Dans le MODÈLE LOGIQUE DES DONNÉES, une entité du MCD peut être représentée sous la forme d'un TABLEAU ASSOCIATIF dans lequel les colonnes représentent les valeurs des attributs (ou champs) et dont chaque ligne (ou entrée de table) correspond à un représentant de l'entité. Le schéma suivant représente cette transformation:
Transformation d'une entité en table
REMARQUE: une TABLE du MLD est souvent appelée RELATION, car elle peut également permettre de représenter une ASSOCIATION.

IV.1.2.EXEMPLE:

Dans le schéma ci-dessous, la valeur de l'attribut prénom_cl du représentant 137 est "Isaac". La valeur de l'attribut adresse_cl du représentant n° 200 est la chaîne de caractères "132 rue Volta Paris 11e (France)"
Exemple de transformation d'une entité en table

IV.1.3.NOTION DE T-UPLET:

Chaque ligne (ou ENTRÉE) d'une table peut être aussi appelée T-uplé. Un T-uplé est l'ensemble des valeurs des attributs caractérisant un représentant donné de l'entité correspondante. Ainsi, dans l'exemple ci-dessus, le T-uplet correspondant à 3eme entrée de cette table a pour valeur: (137, "Newton", "Isaac", "28 Downing street, London, UK").
REMARQUE:
Deux entrées d'une même table ne peuvent contenir des valeurs de T-uplets identiques : en effet, si cela se produisait, cela voudrait dire que ces deux entrées correspondent à la même occurrence de l'entité d'origine. Il faut donc que, quel que soit le couple d'entrée que l'on choisi dans une même table, ces entrées diffèrent par les valeurs d'au moins un de leurs champs. Si l'entité qui est à l'origine de la table est munie d'au moins un IDENTIFIANT, cette condition est forcément réalisée, puisque les valeurs de la colonne correspondant à cet identifiant (ici, id_cl) sont par définition toutes différentes.

IV.1.4.NOTION DE CLEF PRIMAIRE:

Lorsque les valeurs d'une colonne (ou champ) ou d'un groupe de colonnes (groupe de champs) permettent de déterminer sans ambiguïté chacune des entrées d'une table, ce champ (ou ce groupe de champs) constitue une CLEF PRIMAIRE de cette table. Une clef primaire correspond donc à un IDENTIFIANT d'une entité.
Une CLEF PRIMAIRE peut, comme un identifiant, être composée d'UN ou PLUSIEURS champs. Ainsi:
  • Dans la table qui précède, Id_cl peut être pris comme clef primaire (Dans ce cas, la clef est dite SIMPLE);
  • On pourrait aussi prendre pour clef primaire le couple d'arguments nom_cl, prénom_cl, si on est sûr qu'aucun client ne possède (et ne possédera jamais) le même nom et les mêmes prénoms qu'un autre (Dans ce cas, la clef est dite COMPOSÉE).

IV.1.5.NOTION DE CLEF ÉTRANGÈRE:

Une CLEF ÉTRANGÈRE identifie un champ (ou un ensemble de champs) d'une table comme RÉFÉRENÇANT un champ (ou un ensemble de champs) d'une autre table (la table référencée par la clef). En d'autres termes, les VALEURS des champs de la CLEF ÉTRANGÊRE doivent pouvoir être IDENTIFIÉES aux valeurs des champs référencés (il ne suffit pas qu'il y ait égalité des valeurs, il faut que ces valeurs représentent la même donnée dans le système d'informations).
Les valeurs des champs de la table référencée doivent respecter une contrainte d'UNICITÉ (ces champs constituent donc une CLEF PRIMAIRE de la table référencée. D'autre part, les valeurs des champs concernés pour chaque ligne de la table RÉFÉRENÇANT doivent exister dans la table RÉFÉRENCÉE.
Une CLEF ÉTRANGÈRE établit donc une CONTRAINTE entre deux tables, qui consiste en une mise en correspondance deux à deux de leurs champs respectifs par le couple CLEF PRIMAIRE-CLEF ETRANGÈRE. On dit qu'elle garantit l'INTÉGRITÉ RÉFÉRENTIELLE entre deux tables. Ce mécanisme est fondamental dans le fonctionnement du modèle relationnel.
EXEMPLE:
Supposons que la Clef Primaire de la table VEHICULES_DE_SERVICE soit un champ "Immatriculation" dont les valeurs soient des immatriculations d'automobiles. On peut être tenté de mettre dans une table CONDUCTEURS un champ "Immatriculation_Vehicule dont les valeurs soient également des immatriculations d'automobiles et qui contiendrait l'immatriculation du véhicule de service attribué à chaque conducteur. Ce champ peut alors constituer une CLEF ÉTRANGÈRE qui correspond à la CLEF PRIMAIRE de la table VEHICULES_DE_SERVICE, puisque ses valeurs représentent la même donnée (l'immatriculation des véhicules) que celles de la clef primaire de cette table.
Notons qu'une CLEF ÉTRANGÈRE n'est pas forcément une CLEF PRIMAIRE de la table à laquelle elle appartient car ses valeurs ne sont pas forcément uniques (dans le cas ci-dessus, un véhicule peut très bien être affecté à plusieurs conducteurs).
AUTRE EXEMPLE:
Dans l'exemple ci-dessous, l'attribut #id-cl de la table des CLIENTS est une clef étrangère qui correspond à la clef primaire id_cl de la table des CLIENTS:
Tables et clefs étrangeres
Nous pouvons constater que la deuxième entrée de la table CLIENTS peut être mise en correspondance avec la première et la quatrième entrée de la table des commandes. Ceci peut être traduit par: Mc Cartney a effectué 2 commandes, la 1230 et la 4321: cet exemple dévoile donc la fonction principale des clefs étrangères qui est de METTRE EN RELATION deux entités (ici, cette relation pourrait être appelée CLIENT-COMMANDE).

IV.1.6.REPRÉSENTATION DES TABLES DANS LES DIAGRAMMES LOGIQUES:

La représentation d'une TABLE dans les diagrammes du MLD est très semblable à celle d'une entité dans le MCD. La différence est que dans le MLD on ajoute l'indication du DOMAINE DE DÉFINITION de chaque ATTRIBUT.
Le DOMAINE DE DÉFINITION d'un ATTRIBUT est l'ensemble des valeurs (numériques, alphanumériques, logiques, etc.) dans lequel cet attribut peu prendre ses valeurs. Par exemple, dans l'exemple précédent,
  • Les attributs nom_cl, prénoms_cl et adresse_cl sont des chaînes de caractères : ils prennent leurs valeurs dans l'ensemble des chaînes de caractères, souvent noté « string » dans les langages informatiques;
  • L'attribut num_cl est un nombre entier positif qui peut être assez grand: il prend ses valeurs dans l'ensemble des entiers représentés sur 4 octets, souvent noté « long » ou "long int" dans les langages informatiques.
Représentation graphique d'une table
Les principaux ensembles de définition sont les types de données communément définis par les langages de programmation :
  • Caractère de 1 octet: char (character);
  • Entier de 2 octets: int (integer);
  • Entier de 4 octets: long int (ou long);
  • Nombre avec décimales de 4 octets: float (nombre muni d'une virgule ou encore "virgule flottante");
  • Nombre avec décimales de 8 octets: double;
  • Chaîne de caractères: string (chaîne de caractères);
  • Indicateur binaire (0/1): bool (Boolean);
  • Etc.


IV.2.REPRÉSENTATION D'UNE ASSOCIATION BINAIRE:

IV.2.1.REPRÉSENTATION D'UNE ASSOCIATION BINAIRE DE TYPE 1,N PAR AJOUT D'UNE CLEF ETRANGÈRE:

RAPPEL: lorsque, dans une association binaire, une et une seule des deux cardinalités maximales est égale 1, l'association est de type "un à n".
RÈGLE: Lorsque une association est de type (1,n), il est possible, SI L'ASSOCIATION NE PORTE PAS D'ATTRIBUT, de représenter simplement l'association par l'ajout d'une CLEF ÉTRANGÈRE dans la table située du côte où la cardinalité maximale est 1. Cette CLEF ÉTRANGÈRE doit référencer la CLEF PRIMAIRE de l'autre table.
EXEMPLE:
Traduction d'une Association de type 1_N en table
Ici, l'attribut id_li, CLEF PRIMAIRE de la table Livres est ajouté dans la table Exemplaires. Il devient une CLEF ÉTRANGÈRE de cette table qui permet:
- D'une part d'associer chaque exemplaire à un livre;
- D'autre part d'associer à un livre tous les exemplaires disponibles ou empruntés à un moment donné.

Le signe # au début du nom d'un attribut signale que celui-ci est une clef étrangère simple ou appartient à une clef étrangère composée. Remarquons que la valeur de #id_li doit toujours être définie dans ce cas.

IV.2.2.REPRÉSENTATION D'UNE ASSOCIATION BINAIRE DE TYPE 1,1 PAR AJOUT D'UNE CLEF ETRANGÈRE:

RAPPEL: lorsque, dans une association binaire, les deux cardinalités maximales sont égales à 1, l'association est de type "un à un".
RÈGLE: Lorsque une association est de type (1,1), il est également possible, SI L'ASSOCIATION NE PORTE PAS D'ATTRIBUT, de représenter simplement l'association par l'ajout d'une CLEF ÉTRANGÈRE dans la table située "coté sortie" de l'association. Cette CLEF ÉTRANGÈRE doit référencer la CLEF PRIMAIRE de l'autre table. La différence avec le cas des associations de type (1,n) est que la clef étrangère est soumise ici à une condition d'UNICITE, ce qui revient à dire que cette clef étrangère doit être aussi clef primaire.
EXEMPLE:
Traduction d'une Association de type 1_1 en table
Dans le cas ci-dessus, cela se traduit par: plusieurs champs #num_ss_pa de la table Prescription ne peuvent contenir la même valeur, ce qui revient à dire que deux prescriptions ne peuvent être attribuées au même patient.

IV.2.3.REPRÉSENTATION D'UNE ASSOCIATION BINAIRE QUELCONQUE PAR UNE TABLE DE JOINTURE :

Une association qui possède des attributs ou bien qui n'est ni de type 1,1, ni de type 1,n peut toujours être représentée par une TABLE DE JOINTURE. Une table de jointure est une table dont les attributs sont ceux de l'association qu'elle traduit plus des CLEFS ETRANGÈRES qui correspondent aux CLEFS PRIMAIRES des entités mises en correspondance.

EXEMPLE:
Traduction d'une Association quelconque en table
Nous pouvons voir que la traduction a été réalisée en introduisant une table intermédiaire appelée TABLE DE JOINTURE (c'est la table Emprunts, que l'on pourrait aussi appeler Abonnés-Exemplaires). Cette table récupère les attributs de l'association. D'autre part, deux attributs y sont rajoutés :
  • L'attribut #id_ab prend les mêmes valeurs que l'attribut id_ab de la table Abonnés: il s'agit d'une CLEF ÉTRANGÈRE qui permet d'établir la relation entre les relations (ou tables) Abonnés et Emprunts,
  • De même, l'attribut #référence_ex est ajouté en tant que CLEF ÉTRANGÈRE qui référence la clef primaire référence_ex de l'entité Exemplaires;
  • Ces clefs étrangères constituent à elles deux la clef primaire de la table de jonction.


IV.3.REPRÉSENTATION D'UNE ASSOCIATION DE RANG SUPÉRIEUR À 2:

Pour représenter les association de rang supérieur à 2 (ternaires, quaternaires), on utilise une TABLE DE JOINTURE constituée comme suit:
- Elle récupère les attributs propres à l'association;
- Pour toutes les CLEFS PRIMAIRES des tables correspondant aux entités associées, elle intègre une CLEFS ÉTRANGÈRES correspondante.
- L'ensemble de ces clefs étrangères constitue la clef primaire de la table de jonction.

La démarche est donc une extension de la démarche adoptée pour les associations de type quelconque.

EXEMPLE(Association ternaire):

Traduction d'une association ternaire en table


IV.4.LES FORMES NORMALES:

IV.4.1.INTRODUCTION:

Malgré leur dénomination assez "mathématiques", les FORMES NORMALES, dans le domaine des bases de données relationnelles, ne sont pas des concepts très abstraits. En fait, elles définissent un certain nombre de règles assez simples dont l'application aux ATTRIBUTS des TABLES d'un MODÈLE LOGIQUE rend ces tables plus faciles à traduire dans les langages de requêtes des SGBD relationnels.
On distingue TROIS FORMES NORMALES (de la première forme à la troisième), que l'on note "1NF","2NF" et "3NF" (NF: Normal Forms) et qui traduisent trois niveaux CROISSANTS d'éxigences concernant les ATTRIBUTS des tables. De ce fait:
  • Une table qui est en 2NF respecte aussi les exigences de la 1NF;
  • Une table qui est en 3NF respecte aussi les exigences de la 1NF et de la 2NF;

IV.4.2.MISE D'UNE TABLE EN PREMIÈRE FORME NORMALE:

PREMIÈRE ÉTAPE: ELIMINATION DES GROUPES RÉPÉTÉS:
Pour qu'une table soit en première forme normale, il faut qu'elle ne comporte aucun GROUPE D'ATTRIBUTS RÉPÉTITIF. Le schéma qui suit illustre cette notion:
Table comportant un groupe d'attributs répétitifs
lorsqu'on effectue une commande auprès d'un commerçant cette commande peut en général concerner plusieurs articles, chacun étant commandé dans une certaine quantité et pour un prix unitaire donné. Il est donc tentant d'introduire dans l'entité COMMANDE un certain nombre de groupes d'attributs du type: (NumArticle_x, QuantitéCommandéeArt_x, PrixUnitaireArticle_x), chaque groupe représentant un des articles commandés. Cette manière de procéder peut amener à définir une table des commandes du type représenté à gauche du schéma:

Or, cette façon de procéder comporte divers inconvénients. En particulier, il est difficile de définir le nombre de répétitions possibles. De ce fait, les tables de ce type doivent être transformées.

La table de droit correspond à la mise en première forme normale de la table de gauche. On voit que cette transformation consiste simplement à ne garder qu'un seul groupe d'attributs: ce n'est évidemment pas satisfaisant du point de vue de la logique du modèle, puisqu'on perd l'information qu'une commande peut être composée de plusieurs articles.

De ce fait, la mise en première forme normale doit être accompagnée de l'ajout d'une nouvelle table qui permettra de rétablir la logique du modèle. C'est ce que nous allons voir par la suite.
DEUXIÈME ÉTAPE: REPÉSENTATION DES GROUPES RÉPÉTITIFS PAR UNE NOUVELLE TABLE:
Les ENTRÉES (ou T-Uplets) d'un table peuvent représenter les différentes valeurs d'un GROUPE D'ATTRIBUTS. De ce fait, nous allons créer, à la place de la table d'origine, deux NOUVELLES TABLES: l'une d'elle sera la table obtenue par la première transformation, débarassée de son groupe répétitif, la seconde sera formée de telle façon que ses entrées représenteront les différentes valeurs du GROUPES D'ATTRIBUTS RÉPÉTITIFS que nous avons éliminés dans un premier temps (dans le cas étudié, cette table sera la table des ARTICLES_COMMANDES). Le schéma ci-dessous représente cette transformation:
Mise en première forme normale
Par cette décomposition, une commande peut donc être associée à n ARTICLES_COMMANDES. La répétition des attributs NumArticle, QuantitéCommandée et PrixUnitaire a été traduite par la relation établie entre la table COMMANDES et les ARTICLES_COMMANDES par le biais de l'attribut NumCommande. Les deux tables obtenues sont en PREMIÈRE FORME NORMALE. Remarquons que la CLEF PRIMAIRE de la table ARTICLES_COMMANDES est COMPOSÉE de l'attribut NumArticle qui permet d'identifier chaque article assicié à la clef primaire NumCommande de la nouvelle table COMMANDES.


ATTENTION:
Ce n'est pas un hasard si nous avons appelé la nouvelle table ARTICLES_COMMANDES et pas simplement ARTICLES. En effet, une table ARTICLES représente en général la liste des articles que l'on peut acheter: chaque entrée correspond à un article différent. Or, la table ARTICLES_COMMANDES doit contenir tous les achats d'articles qui ont été effectués à l'occasion de toutes les COMMANDES. De ce fait, plusieurs entrées de cette table peuvent correspondre au même article mais à des commandes différentes. C'est ce qui explique la présence de l'attribut NumCommande dans cette table. Les entrées de ARTICLES_COMMANDES pour lesquelles l'attribut NumCommande a une valeur identique correspondent à la même commande.

IV.4.3.MISE D'UNE TABLE EN DEUXIÈME FORME NORMALE:

CONDITION PORTANT SUR LES ATTRIBUTS:
Pour qu'une table soit en deuxième forme normale, il faut et il suffit qu'elle remplisse les conditions suivantes;
  • Elle doit être en première forme normale (donc, ne comporter aucun groupe d'attributs à caractère RÉPÉTITIF);
  • TOUS ses attributs qui ne font pas partie de la clef primaire doivent dépendre de TOUS les attributs de cette clef.
EXEMPLE:
La table ARTICLES_COMMANDES obtenue lors du passage en 1NF comprend les attributs suivants:
- Attributs faisant partie de la clef primaire: NumCommande, NumArticle;
- Attributs ne faisant pas partie de la clef primaire: QuantitéCommandée et PrixUnitaire.
Si QuantitéCommandée et PrixUnitaire dépendent tous deux de NumArticle, PrixUnitaire ne dépend pas de NumCommande: en effet, à priori, le prix d'un article n'est pas fixé en fonction de chaque commande. De ce fait, la table ARTICLES_COMMANDES, n'est pas en 2NF.
Remarquons au passage qu'une table rempissant les conditions 1NF dont la clef primaire est SIMPLE est forcément en 2NF.
TRANSFORMATION EN 2NF D'UNE TABLE EN 1NF:
PRINCIPE:Pour transformer une table respectant les conditions 1NF en tables respectant les conditions 2NF, il suffit de transférer les attributs ne dépendant pas de la totalité de la clef primaire de la table d'origine dans une nouvelle table et d'ajouter à cette nouvelle table la partie de la clef primaire qui concerne les arguments transférés.
A l'issue de ce processus, on obtient donc 2 tables 2NF:
  • l'ancienne table à laquelle les arguments non dépendants de la totalité de la clef ont été retirés;
  • la nouvelle table qui accueille les arguments non dépendants de la totalité de la clef et la partie de la clef qui les concerne.
EXEMPLE:
Mise en deuxième forme normale
- On crée une nouvelle table ARTICLES_PRIX;
- On retire de la table ARTICLES_COMMANDES l'attribut PrixUnitaire, qui ne dépend que de NumArticle;
- On transfère l'attribut PrixUnitaire dans la nouvelle table ARTICLES_PRIX;
- On duplique l'attribut NumArticle dans la nouvelle table. Celui-ci devient CLEF PRIMAIRE de la nouvelle table:

IV.4.4.MISE D'UNE TABLE EN TROISIÈME FORME NORMALE:

CONDITION PORTANT SUR LES ATTRIBUTS:
Pour qu'une table soit en troisième forme normale, il faut et il suffit qu'elle remplisse les conditions suivantes:
  • Elle doit être en 2NF (donc, pas de groupe d'attribut répétitif et tous les attributs dépendent de la totalité de la clef);
  • Un attribut n'appartenant pas à la CLEF PRIMAIRE ne doit pas DÉPENDRE TRANSITIVEMENT de cette CLEF.
DEFINITION DE LA DÉPENDANCE TRANSITIVE:
Par définition tout attribut d'une table 2NF dépend DIRECTEMENT de la clef primaire de cette table. On dit qu'un attribut dépend TRANSITIVEMENT de la clef primaire si on peut établir cette dépendance PAR L'INTERMÉDIAIRE D'UN AUTRE ATTRIBUT.
EXEMPLE:
Soit une table COMMANDES dont la clef primaire est NumCommande (numéro de commande) et comportant les attributs NumClient (numéro de client) et NomClient (nom du client). NomClient dépend évidemment directement de NumClient (A un numéro de client correspond un seul nom de client). Comme NumClient dépend de NumCommande, on a la dépendance TRANSITIVE NumCommande => NumClient => NomClient.
Le fait qu'un attribut A2 d'une table soit en relation de dépendance transitive avec la clef par l'intermédiaire d'un autre attribut A1 introduit forcément une contrainte au niveau de la gestion de la table: toute modification de l'un des attribut nécessite la modification de l'autre attribut, sous peine d'introduire une incohérence. Il est donc prudent d'éviter ce type de contrainte en transformant les tables 2NF possédant des relations de transitivité entre attributs en tables 3NF.
TRANSFORMATION D'UNE TABLE 2NF EN 3NF:
PRINCIPE:
La transformation d'une table 2NF comportant des dépendances transitives en tables 3NF va consister, comme dans les cas précédents, à encapsuler les groupes d'attributs en dépendance transitives dans de nouvelles tables:
EXEMPLE:
Mise en troisième forme normale
Dans l'exemple ci-contre, la valeur de l'attribut NomClient dépend transitivement de l'attribut NumClient, sans que NumClient soit une clef primaire. Pour résoudre ce problème, on crée une nouvelle table CLIENTS qui va encapsuler ces deux attributs et où NumClient sera clef primaire.
Cet exemple illustre assez bien l'intérêt de la troisième forme normale: la transformation permet de faire apparaître une nouvelle table, la table CLIENTS qui regroupe les données attachées à chaque client de l'entreprise et qui correspond en fait à une entité CLIENTS du niveau MCD que l'analyse à ce niveau n'aurait pas détectée. L'ajout de la table CLIENTS permet d'envisager une gestion des clients indépendante des commandes (par exemple, la gestion de comptes clients), ce qui aurait été difficile sans cet ajout.

IV.4.5.RÉCAPITULATION SUR LES FORMES NORMALE:

Le tableau suivant résume les conditions que les formes normales imposent aux tables d'un MLD:
FORME NORMALE CONDITIONS A REMPLIR
Première forme normale (1NF) Pas de groupe d'attributs à caractère RÉPÉTITIF
Deuxième forme normale (2NF) être en 1NF + tous les attributs dépendent de la totalité de la clef primaire (Ceci implique: une table 1NF dont la clef est SIMPLE est donc forcément en 2NF)
Troisième forme normale (1NF) être en 2NF + aucune dépendance TRANSITIVE des attributs avec la clef primaire


IV.5.NOTATIONS "ALGÉBRIQUES":

IV.5.1.NOTATION ALGÉBRIQUE DES TABLES:

Une TABLE peut être représentée sous une forme ALGÉBRIQUE de la manière suivante :
TABLE ==> NomTable ( Attribut1, Attribut 2, ….., Attribut n )
 
Les attributs qui sont des CLEFS PRIMAIRES sont soulignés, tandis que les CLEFS ÉTRANGÈRES sont précédées d'un #.
EXEMPLES:
  • La table CLIENTS s'écrira: Clients ( NumClient, NomClient, PrenomClient, AdresseClient);
  • La table CLIENTS-ARTICLES s'écrira: ClientsArticles ( #NumClient, #NumArticle, QuantiteCommandee, Date_Commande);

IV.5.2.NOTATION ALGÉBRIQUE DES ATTRIBUTS:

Pour désigner un attribut d'une table donnée, on emploie souvent la notation :
<nom de table>.<nom d'attribut>
Cette notation, qui est la même que celle qui permet de noter les attributs d'un objet dans de nombreux langages objets, est également utilisée dans les langages de requêtes tels que SQL.
EXEMPLE: L'attribut NomClient de la table CLIENTS sera noté CLIENTS.NomClient.


IV.6.OPÉRATIONS SUR LES TABLES

IV.6.1.GÉNÉRALITÉS:

Ce sous-chapitre décrit les PRINCIPES LOGIQUES des principales opérations qu'un SGBD permet de réaliser sur les TABLES des bases de données qu'il gère. Le détail de leur réalisation sera traité lors de l'étude du Modèle Physique et du langage SQL.
Les Systèmes de Gestion de Bases de Données permettent d'effectuer certaines opérations sur les tables des bases de données qu'ils gérent, soit par l'intermédiaire de formulaires de gestion, soit par l'intermédiaire de langages de requêtes utilisables depuis les applications.
Une partie de ces opérations concerne la création, la modification ou la destruction de ces tables: ces opérations sont assez banales et nous les étudierons dans les chapitres suivants. Dans ce qui suit, nous nous intéresserons plutôt aux opérations qui mettent en oeuvre les PARTICULARITÉS DU MODÈLE RELATIONNEL. Nous étudierons successivement:
  • La SÉLECTION, qui permet de SÉLECTIONNER certaines entrées d'une table afin d'EXTRAIRE les valeurs de certains des attributs de ces entrées;
  • L'INTERCLASSEMENT des entrées d'une table, qui permet de modifier l'ordre des entrées d'une table en fonction de critères portant sur les valeurs d'attributs;
  • L'AGRÉGATION, qui permet de combiner les valeurs des attributs de certaines entrées afin d'en tirer des résultats globaux: sommes d'attributs,valeur maximale ou minimale d'un attribut, nombre d'entrées satisfaisant à certaines conditions, etc.;
  • La PROJECTION, qui consiste à créer une table T2 à partir d'une table T1 par suppression de certaines colonnes (on extrait une sous-table d'une table);
  • La JOINTURE de tables qui consiste à créer une table rassemblant les colonnes de plusieurs table possédant une colonne commune du point de vue des valeurs des attributs;

IV.6.2.L'OPERATION DE SÉLECTION:

La SÉLECTION permet de SÉLECTIONNER certaines entrées d'une table afin d'EXTRAIRE les valeurs de certains des attributs de ces entrées. Cette opération correspond au schéma suivant:
SELECT <Liste de noms d'attributs> FROM <nom de table> WHERE <condition de sélection>
(sélectionner les VALEURS DES ATTRIBUTS dont la liste est indiquée UNIQUEMENT DANS LES ENTRÉES de la TABLE INDIQUÉE qui correspondent aux CONDITIONS DE SÉLECTION)
Le résultat obtenu est un TABLEAU ASSOCIATIF comprenant:
- les LIGNES de la table satisfaisant à la condition de sélection;
- Les COLONNES correspondant aux attributs de la liste.
Les langages de requêtes fournissent des fonctions qui permettent d'accéder aux valeurs de chacun des éléments de ces tableaux associatifs et de les récupérer (Exemple: directives fetch_array (récupérer_tableau) en sql).
EXEMPLE:
Exemple d'opération de sélection
L'opération ci-contre permet de sélectionner les valeurs des attributs num_commande et nom_client qui correspondent uniquement aux entrées qui satisfont à la condition "la valeur de l'attribut montant doit être supérieure à 250". Le résultat est le tableau associatif indiqué ci-contre:
Les conditions peuvent être beaucoup plus compliquées que dans cet exemple: elles peuvent porter sur plusieurs attributs, comporter des conditions logiques, etc.

IV.6.3.LES OPERATIONS D'INTERCLASSEMENT:

Ces opérations reviennent à modifier l'ordre des entrées d'une table en fonction des valeurs d'un ou de plusieurs attributs. L'interclassement peut être effectué en fonction de différents critères:
  • Par valeurs croissantes ou décroissances d'un attribut numérique (ex: classement d'une table FACTURES en fonction du montant croissant ou décroissant des factures);
  • Par ordre alphabétique (ex: classement d'une table CLIENTS en fonction de l'ordre alphabétique des noms, puis des prénoms des clients);
  • factures);
  • Par ordre de dates croissantes ou décroissantes (ex: classement d'une table MESSAGES en fonction d'un paramètre date_réception reconnu comme ayant valeur de date par le SGBD);
  • Etc...
Ainsi, dans la table :
commande (num_commande, montant, nom_client, date)
Il est probable qu'en l'absence d'action sur leur classement, les entrées vont se retrouver naturellement classées en fonction de la date de prise en compte des commandes : les premières entrées seront celles qui ont été enregistrées aux dates les plus anciennes. On peut, pour des raisons d'efficacité, vouloir reclasser ces entrées selon d'autres critères. Par exemple , si l'on a besoin de retrouver des commandes en fonction du client, il peut être avantageux de disposer d'un tableau où les entrées sont classé par ordre alphabétique en fonction des noms de clients, puis en fonction des montants des commandes passées. Cette transformation se nommera INTERCLASSEMENT en fonction des attributs "nom_client" et "montant".
Exemple d'opération d'interclassement
REMARQUES:
- Lorsqu'il s'agit d'un traitement informatisé, l'opération d'interclassement agit en général sur la façon dont la table va être PRÉSENTÉE à l'utilisateur et non sur l'ORDRE RÉEL des entrées : ceci revient à créer une autre table, reclassée suivant les critères spécifiés. Cette table ne dure que le temps nécessaire à l'exécution du traitement qui a justifié cet interclassement.
- Lorsque dans un interclassement, les valeurs de l'attribut d'interclassement sont les mêmes pour deux lignes de la table, les deux lignes sont présentées dans la table résultante dans l'ordre dans lequel se présentent dans la table d'origine.

IV.6.4.OPÉRATIONS D'AGRÉGATION DE DONNÉES D'UNE TABLE:

Ce type d'opérations a pour caractéristique de porter sur un ENSEMBLE D'ENTRÉES d'une table: les valeurs des attributs correspondant à certaines entrées sont "agrégées" ("assemblées", "combinées") entre elles pour aboutir à un résultat. la notation générale de ce type d'opérations est:
FONCTION_AGREGATION( nom d'attribut )
où:
  • FONCTION_AGREGATION est le nom de l'opération ( count, max, min, etc.);
  • nom_attribut est le nom de l'attribut sur les valeurs duquel porte l'opération (ex: sum(montant_transaction) fait la sommes des valeurs de l'attribut montant_transaction pour plusieurs entrées).
Ces opérations sont toujours intégrées dans des OPÉRATIONS DE SÉLÉCTION. Elles agissent sur les entrées sélectionnées par l'intermédiaire de l'opération de sélection à laquelle sont intégrées, ce qui veut dire qu'elles agiront uniquement sur les entrées sélectionnées par cette opération. Par exemple, l'opération de sélection:
SELECT SUM(montant) nom_client FROM Commande WHERE num_commande > 2017251201
permettra à la fonction d'agrégation SUM (somme) de faire le total des montants des factures dont le numero est supérieur à 2017251201. Les principales fonctions d'agrégation sont :
FORME LE PLUS SOUVENT UTILISéEFONCTION
AVG(nom_attribut)Calcule la moyenne des valeurs de l'attribut pour toutes les entrées sélectionnées
COUNT(nom_attribut)Compte le nombre d'entrées sélectionnées
MAX(nom_attribut)retourne la valeur maximale de l'attribut pour les entrées sélectionnées
MIN(nom_attribut)retourne la valeur minimale de l'attribut pour les entrées sélectionnées
SUM(nom_attribut)retourne la somme des valeurs de l'attribut pour les entrées sélectionnées

IV.6.5.OPÉRATION DE PROJECTION D'UNE TABLE:

PROJETER une table revient à créer une autre table en supprimant certaines colonnes (attributs). Par exemple, la table :
commande (num_commande, nom_client)
est une projection de la table:
commande (num_commande, montant, nom_client, date)
Exemple de projection d'une table

IV.6.6.OPÉRATIONS DE JOINTURE DE TABLES:

DÉFINITION:
Soient deux tables T1 et T2. Supposons qu'il existe respectivement dans T1 et T2 deux attributs A1 et A2 qui correspondent à la même information, exprimée de la même manière (format de représentation et unités). Dans ce cas, il est possible de JOINDRE deux à deux les entrées de T1 et T2 pour lesquelles la valeur de A1 est identique à celle de A2. Le résultat de cette opération sera une table comprenant toutes les colonnes des deux tables T1 et T2, à l'exception des colonnes correspondant à A1 et A2 qui sont fusionnées en une seule colonne. Cette opération est appelée JOINTURE des tables T1 et T2.
Schéma de la jointure de deux tables
REMARQUES:
  • La jointure est, dans ce cas, dite COMPLÈTE car tous les attributs des deux tables sont repris dans la table de jointure, mais nous verrons par la suite que ce n'est pas systématique;
  • Pour une valeur donnée V, Les couples formés en associant les entrées de T1 et T2 telles que A1 = A2 = V ne sont pas forcément uniques. En effet, A1 et A2 n'étant pas forcément des clefs primaires, il est possible que l'attribut A1 soit égal à V pour plusieurs entrées de T1, et de même pour A2 et T2. On retrouvera donc dans la table de jointure toutes les combinaisons possibles d'entrées de T1 et T2 pour lesquelles A1 = A2 = V.
  • Il est possible que pour certaines valeurs de l'attribut A1 de T1, on ne trouve aucune entrée de T2 présentant la même valeur pour T2, et inversement. Dans ce cas certains T-uplets de T1 ou T2 ne pourront être appariés. Suivant que l'on intêgrera ou non ces T-uplets dans la table de jointure, la jointure sera qualifiée d'interne ou d'externe, de jointure "à gauche" ou "à droite", etc. Nous étudierons ces différents cas dans le chapitre traitant du modèle physique.
EXEMPLE:
Supposons les deux tables:
Commandes (nun_commande, num_client, date)
et
Clients (id_client, @mail, passwd)
Supposons que les colonnes num_client et id_client concernent toutes les deux une même numérotation des clients. Les attributs num_client et id_client correspondent à la même INFORMATION (une identification de chaque client par un numéro unique), exprimée de la même manière. La jointure des deux tables pourra donc être effectuée en associant les entrées des deux tables pour lesquelles les valeurs des attributs num_client et id_client ont la même valeur.
La JOINTURE sera donc la table obtenue en ajoutant à chaque triplet de la table commande les triplets de la table client pour lesquels la valeur de id_client est identique à celle de nom_client, puis en fusionnant les attributs id_client et nom_client dans la table de jointure:
Commandes (num_commande, num_client, date) X Clients (id_client, @mail, passwd)
=> Commandes_clients (num_commande, num_client, date, @mail, passwd )
JOINTURES COMPLÈTES ET JOINTURES INCOMPLÈTES
La jointure est dite COMPLÈTE si la table de jointure reprend les attributs des deux tables (comme dans l'exemple précédent). Elle est incomplète dans le cas contraire (par exemple : Clients_commandes ( nom_client, adresse_livraison ) est une jointure incomplète des tables clients et commandes.
DIFFÉRENTS MODES DE JOINTURE
Le fait que les deux attributs de jointure soient identifiés l'un à l'autre de veut pas dire que toutes leurs valeurs se retrouvent dans les deux tables. Par exemple, dans la table Commandes de l'exemple précédent, on ne trouve pas de client dont l'identificateur est 123. Ici, cela veut dire que ce client a un compte client mais n'a fait aucune commande). De même, le client identifié par 116 dans la table des commandes ne figure pas (on ne figure plus) dans la table des clients
Dans ce cas, il est difficile d'apparier toutes les entrées des deux tables: certaines entrées resteront non appariées et les valeurs de certains attributs de la table de jonction resteront indéfinies. De ce fait, différents modes de jointure peuvent être définis, en fonction de la manière dont ces cas seront traités:
  • FULL JOIN (réunion) si la table de jointure comprend la totalité des entrées des deux tables initiales (même celles qui ne peuvent être appariées);
  • INNER JOIN (intersection) si seulement les entrées possédant la même valeur de l'attribut de jonction dans les deux tables sont pris en compte dans la table de jointure (on ne considère que les entrées qui peuvent être appariées);
  • LEFT JOIN si toutes les entrées de la première tables sont reprises et seulement les entrées de la seconde possédant une valeur de l'attribut de jonction existant dans la première sont prises en compte dans la table de jointure;
  • RIGHT JOIN si toutes les entrées de la deuxième tables sont reprises et seulement les entrées de la première possédant une valeur de l'attribut de jonction existant dans la seconde table sont prises en compte dans la table de jointure.
SCHEMA EXPLICATIF DE L'OPERATION DE JOINTURE:
Menu de commande de l'animations

Principe de la jointure de deux tables
JOINTURE DE PLUS DE DEUX TABLES
La jointure de plus de deux tables s'effectue par une démarche TRANSITIVE. Exemple pour 4 tables T1, T2, T3 et T4 :
  • On joint T3 et T4 : on note J(T3,T4) la table de jointure obtenue;
  • Puis on joint T2 à J(T3,T4) : on note J(T2, J(T3, T4)) le résultat obtenu;
  • Puis on joint T1 à J(T2, J(T3,T4)): on note J(T1, J(T2, J(T3, T4)) ) le résultat final.
UTILITÉ DE LA JOINTURE DE TABLES
La jointure de tables permet de mettre en oeuvre les RELATIONS entre tables définies par les couples CLEF PRIMAIRE - CLEF ÉTRANGÈRE. En effet, lorsqu'on effectue la jointure de deux tables suivant leur couple d'attributs clef primaire - clef étrangère, on obtient une table qui MET EN RELATION tous les attributs des deux tables.
EXEMPLE: Le schéma ci-dessous représente deux tables mises en relation par un couple d'attributs Clef primaire - Clef étrangère:
Utilisation de la jointure de deux tables
La jonction de ces deux tables suivant le couple d'attributs num_client - #num_client (clef primaire et clef étrangère correspondante) va donner la table de jointure suivante:
Utilisation de la jointure de deux tables
Pour connaître (par exemple) l'adresse mail du client de la commande 2017-05-08-22, il suffira d'utiliser une seule opération de sélection:
SELECT adresse_mail FROM Clients_Commandes WHERE num_commande = "2017-05-08-22"
Si l'on n'avait pas utilisé la jointure, on aurait été obligé d'extraire d'abord de la table COMMANDES la valeur de l'attribut nom_client qui correspond à la commande 2017-05-08-22 (première opération de sélection, puis récupération de la valeur sélectionnée), puis d'extraire de la table CLIENTS l'adresse mail du client dont la valeur des la clef étrangère #nom_client correspond à la valeur de nom_client extraite par la sélection précédente (deuxième opération de sélection, puis récupération de la valeur sélectionnée)
JOINTURE DE PLUS DE DEUX TABLES:
La démarche est la même lorsque plus de deux tables sont mises en relation par des couples clef primaire-clef étrangère: la jonction transitive (vue plus haut) permet d'obtient une table qui MET EN RELATION tous les attributs des diférentes table jointes et, de ce fait, d'extraire en une seule sélection des valeurs d'attributs qui auraient nécessité autant d'opérations de sélection que de tables jointes.
JOINTURE ET SELECTION
Comme les autres opérations sur les tables, les opérations de jointures sont intégrées dans des opération de sélection. Ceci entraîne que les jointures ne se font que sur les entrées des deux tables qui sont sélectionnées par l'opération de sélection. Une opération de jointure est en général identifiée par les opérateurs JOIN et ON: JOIN permet d'indiquer les tables à joindre et ON permet d'indiquer les attributs suivant lesquels la jointure doit être effectuée:
SELECT * FROM Clients INNER JOIN Commandes ON Clients.nom_client = Commandes.id_client WHERE Commandes.montant < 1000 (effectuer la jointure entre les tables Clients et Commandes suivant les attributs respectifs id_client et nom_client en prenant en compte uniquement les entrées de Commandes dont les montants sont inférieurs à 1000 (par contre, on prend toutes les entrées de Clients). INNER signifie qu'on ne conserve que les couples d'entrées qui peuvent être mises en correspondance).


IV.7.EXEMPLE DE PASSAGE DU MODÈLE CONCEPTUEL AU MODÈLE LOGIQUE:

IV.7.1.RAPPEL: PROBLÈME A RÉSOUDRE:

Le sous-chapitre III.4 nous a permis d'élaborer le Modèle Conceptuel de Données (MCD) d'une bibliothèque. Le schéma obtenu est le suivant:
MCD de la bibliothèque
Nous allons élaborer le Modèle Logique des Données (MLD) qui correspond à ce MCD en appliquant les directives et rêgles énoncées dans le présent chapitre. D'après ce qui précède, pour déduire le MLD correspondant à ce MCD, nous devons :
  • Transformer les entités du MCD en TABLES;
  • Remplacer les ASSOCIATIONS de type 1:n et 1:1 non porteuses d'attributs par des échanges de CLEFS ÉTRANGÈRES entre les entités qu'elles relient;
  • Remplacer les autres associations par des TABLES DE JONCTION;
  • Mettre toutes les relations (tables) ainsi définies en troisième forme normale.

IV.7.2.TRANSFORMATION DES ENTITÉS DU MCD EN TABLES DU MLD:

Cette transformation ne pose, à priori, pas beaucoup de problèmes : à chaque occurrence de l'entité correspond une LIGNE de la table alors qu'à chaque attribut correspond une colonne. Le seul ajout notable est le TYPAGE de la valeur des attributs, c'est à dire la définition de l'ensemble dans lequel chaque attribut prend sa valeur ("int" pour nombre entier, "str" pour chaînes de caractères, "text" pour texte de longueur indéterminée, etc.). Par exemple, l'entité AUTEURS donnera la table AUTEURS que l'on représentera comme indiqué ci-dessous:
Table des auteurs dans le MLD de la bibliothèque
La transformation des entités en tables ne pose donc pas de problème particulier à part le choix des ensembles de définition. Pour fixer les esprits, voici un exemple du contenu de la table Auteurs lorsque celui-ci aura été défini (lors de la réalisation du MODÈLE PHYSIQUE):
Exemple de contenu de la table des auteurs
REMARQUE: dans cette table, l'attribut id_au a été définie comme une clef primaire auto incrémentées par le SGBD à chaque définition d'une nouvelle entrée dans la table (l'utilisateur ne peut fixer lui-même cette valeur). Supposons que l'entrée d'Id_au = 2 soit supprimée, puis qu'une nouvelle entrée soit créée : le numéro 2 ne sera jamais réattribué par le SGBD (sauf si la table est détruite puis reconstituée). une nouvelle entrée se verra donc attribuer par le SGBD une id_au égale à 5.

IV.7.3.TRANSFORMATION DES ASSOCIATIONS DU MCD:

ASSOCIATION CORRESPONDRE:
L'association Correspondre relie les entités Livres et Exemplaires. Les cardinalités sont respectivement 0,n et 1,1: comme une seule des cardinalités maximales est égale à 1, l'association est de type 1,n. Comme elle ne porte pas d'attribut, cette association peut être représentée par l'adjonction à l'entité Exemplaires d'une clef étrangère correspondant à la clef primaire de l'entité Livres (id_li). Cette adjonction permettra, pour tous les exemplaires (de l'entité Exemplaires), de retrouver le livre (de l'entité Livres) qui lui correspond.
Transformation de l'association Correspondre
ASSOCIATION EMPRUNTER:
L'association Emprunter relie les entités Abonnée et Exemplaires. Les cardinalités sont respectivement 0,n et 1,1: comme une seule des cardinalités maximales est égale à 1, l'association est de type 1,n. Cependant, comme elle porte des attributs, cette association ne peut être représentée par un simple couple clef primaire - clef étrangère. Il faut créer une nouvelle table (table de jonction) qui portera les attributs de l'association ainsi que des clefs étrangères associées aux clefs primaires d'Abonnée et Exemplaires:
Transformation de l'association Emprunter
REMARQUE:Il est clair que dans ce cas, la table de joinction n'est utile que parce qu'il faut récupérer les arguments de l'association Emprunter. Sinon, le simple ajout de la clef étrangère #id_ab dans la table Exemplaires aurait suffi.
ASSOCIATION ECRIRE:
L'association Ecrire relie les entités Auteurs et Livres. Les cardinalités sont respectivement 1,n et 1,n: comme aucune cardinalités maximales n'est égale à 1, l'association est de type m,n. De ce fait, même si elle ne porte pas d'attribut, cette association ne peut être représentée par un simple couple clef primaire - clef étrangère. Il faut donc là aussi créer une table de jonction. Cette table portera uniquement les clefs étrangères associées respectivement aux clefs primaires des entités Auteurs et Livres:
Transformation de l'association Ecrire
REMARQUE:
S'il avait été spécifié au départ qu'un livre ne peut être écrit que par un seul auteur, la seule adjonction dans la table Livres de la clef étrangère #id_auteurs aurait permis d'identifier l'auteur d'un livre, puisque cet auteur est forcément unique dans ce cas. La table de jonction n'aurait donc pas été utile (d'ailleurs, l'association aurait été de type 1,n, sans arguments, ce qui confirme bien la règle de transformation).

IV.7.4.MODÈLE LOGIQUE COMPLET:

Compte tenu de ce qui précède, le modèle logique de données de la bibliothèque se présentera comme suit:
Menu de commande de l'animations

Modèle logique complet de la bibliothèque
COMMENTAIRES:
  • La partie droite du schéma représente le Modèle Conceptuel de la bibliothèque (MCD);
  • La partie gauche du schéma représente le Modèle Logique de la bibliothèque (MLD), directement déduit du MCD;
  • Nous pouvons constater que toutes les entités du MCD ont été directement représentées dans le modèle logique par des tables portant les mêmes noms;
  • L'association Ecrire qui relie les entités Auteurs et Livres du MCD a été représentée dans le modèle logique par la table Auteurs_livres, munie des clefs étrangères #id_au et #id_li. Ces clefs étrangères correspondent aux clefs primaires des tables Auteurs et Livres;
  • Chaque entrée de la table Auteurs_livres permet donc de mettre en correspondance l'auteur identifié par la valeur de #id_au au livre identifié par la valeur de #id_li. Cette table crée donc bien une ASSOCIATION entre les auteurs et les livres;
  • La création de la table Auteurs-livres est rendue nécessaire par le fait que les cardinalités de l'association Ecrire du MCD sont de type m-n;
  • L'association Correspondre qui associe dans le MCD les entités Livres et Exemplaires, est de type 1,n et ne porte pas d'attribut. De ce fait, sa traduction dans le MLD peut se faire grâce à l'adjonction d'une clef étrangère à la table située du côté où la cardinalité maximale et 1;
  • La clef étrangère #id_li a donc été ajoutée à la table Exemplaires. Cette clef permet de mettre en correspondance chaque représentant de l'entité Exemplaires avec le livre dont il est un exemplaire. Elle traduit donc bien l'association Correspondre;
  • L'association Emprunter associe dans le MCD les entités Abonnés et Exemplaires. Elle est de type 1,n, mais elle est dotée d'attributs propres. Il est donc nécessaire de la représenter par une nouvelle table qui pourra acqueillir ces attributs. Nous appellerons cette table Abonnés-Exemplaires;
  • D'autre part, pour traduire l'association emprunter, il sera nécessaire de doter cette table Abonnés-Exemplaires de deux clefs étrangères faisant référence aux clefs primaires des associations Abonnés et Exemplaires. Ces clefs seront nommées respectivement #id_ab et #reference_ex;
  • Dans ces conditions, la table Abonnés-Exemplaires permet bien d'associer à chaque exemplaire l'abonné qui l'a emprunté (s'il a été emprunté);
  • Le MLD de la gauche du schéma représente donc bien la traduction LOGIQUE du MCD la droite du schéma;
Ce diagramme permet d'aborder directement la construction du modèle physique. C'est ce que nous ferons au chapitre suivant.



Retour accès aux cours Retour sommaire cours
FormateurGaucheRepos FormateurGaucheActif FormateurDroitRepos FormateurDroitActif