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

IV.LE MODLE LOGIQUE DES DONNES:

AVANT PROPOS:
Nous avons tudi au chapitre prcdent le MODLE CONCEPTUEL DES DONNES (MCD) dont le but est d'laborer une reprsentation du SYSTEME D'INFORMATION comprhensible au niveau de l'UTILISATEUR, l'aide de concepts appels ENTITS et ASSOCIATIONS.

Cependant, il serait trs compliqu de dvelopper directement une base de donnes directement partir du MCD. En effet, les notions d'entits et d'associations n'ont pas de traduction directe dans les langages de requtes accepts par les S.G.B.D: ces langages sont plutt bass sur la notion de TABLES, qui est trs 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 donnes (Modle Physique des Donnes-MPD), il est commode de passer par l'tape du Modle Logique des Donnes (MLD), qui, lui, est bti partir de la notion de TABLE. Ces TABLES, nous le verront plus loin, peuvent reprsenter la fois des entits et des associations: elles permettent donc de traduire entirement le MCD en MLD.

Le prsent chapitre tudie la manire de passer du Modle Conceptuel au Modle Logique.

IV.1.REPRSENTATION D'UNE ENTIT SOUS FORME DE TABLE:

IV.1.1.PRINCIPE:

Dans le MODLE LOGIQUE DES DONNES, une entit du MCD peut tre reprsente sous la forme d'un TABLEAU ASSOCIATIF dans lequel les colonnes reprsentent les valeurs des attributs (ou champs) et dont chaque ligne (ou entre de table) correspond un reprsentant de l'entit. Le schma suivant reprsente cette transformation:
Transformation d'une entit en table
REMARQUE: une TABLE du MLD est souvent appele RELATION, car elle peut galement permettre de reprsenter une ASSOCIATION.

IV.1.2.EXEMPLE:

Dans le schma ci-dessous, la valeur de l'attribut prnom_cl du reprsentant 137 est "Isaac". La valeur de l'attribut adresse_cl du reprsentant n 200 est la chane de caractres "132 rue Volta Paris 11e (France)"
Exemple de transformation d'une entit en table

IV.1.3.NOTION DE T-UPLET:

Chaque ligne (ou ENTRE) d'une table peut tre aussi appele T-upl. Un T-upl est l'ensemble des valeurs des attributs caractrisant un reprsentant donn de l'entit correspondante. Ainsi, dans l'exemple ci-dessus, le T-uplet correspondant 3eme entre de cette table a pour valeur: (137, "Newton", "Isaac", "28 Downing street, London, UK").
REMARQUE:
Deux entres d'une mme table ne peuvent contenir des valeurs de T-uplets identiques: en effet, si cela se produisait, cela voudrait dire que ces deux entres correspondent la mme occurrence de l'entit d'origine. Il faut donc que, quel que soit le couple d'entre que l'on choisi dans une mme table, ces entres diffrent 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 forcment ralise, puisque les valeurs de la colonne correspondant cet identifiant (ici, id_cl) sont par dfinition toutes diffrentes.

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 dterminer sans ambigut chacune des entres d'une table, ce champ (ou ce groupe de champs) constitue une CLEF PRIMAIREde cette table. Une clef primaire correspond donc un IDENTIFIANT d'une entit.
Une CLEF PRIMAIRE peut, comme un identifiant, tre compose d'UN ou PLUSIEURS champs. Ainsi:
  • Dans la table qui prcde, 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, prnom_cl, si on est sr qu'aucun client ne possde (et ne possdera jamais) le mme nom et les mmes prnoms qu'un autre (Dans ce cas, la clef est dite COMPOSE).

IV.1.5.NOTION DE CLEF TRANGRE:

Une CLEF TRANGRE identifie un champ (ou un ensemble de champs) d'une table comme RFRENANT un champ (ou un ensemble de champs) d'une autre table (la table rfrence par la clef). En d'autres termes, les VALEURS des champs de la CLEF TRANGRE doivent pouvoir tre IDENTIFIES aux valeurs des champs rfrencs (il ne suffit pas qu'il y ait galit des valeurs, il faut que ces valeurs reprsentent la mme donne dans le systme d'informations).
Les valeurs des champs de la table rfrence doivent respecter une contrainte d'UNICIT (ces champs constituent donc une CLEF PRIMAIRE de la table rfrence. D'autre part, les valeurs des champs concerns pour chaque ligne de la table RFRENANT doivent exister dans la table RFRENCE.
Une CLEF TRANGRE 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 ETRANGRE. On dit qu'elle garantit l'INTGRIT RFRENTIELLE entre deux tables. Ce mcanisme est fondamental dans le fonctionnement du modle 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 vhicule de service attribu chaque conducteur. Ce champ peut alors constituer une CLEF TRANGRE qui correspond la CLEF PRIMAIRE de la table VEHICULES_DE_SERVICE, puisque ses valeurs reprsentent la mme donne (l'immatriculation des vhicules) que celles de la clef primaire de cette table.
Notons qu'une CLEF TRANGRE n'est pas forcment une CLEF PRIMAIRE de la table laquelle elle appartient car ses valeurs ne sont pas forcment uniques (dans le cas ci-dessus, un vhicule peut trs bien tre affect plusieurs conducteurs).
AUTRE EXEMPLE:
Dans l'exemple ci-dessous, l'attribut #id-cl de la table des CLIENTS est une clef trangre qui correspond la clef primaire id_cl de la table des CLIENTS:
Tables et clefs trangeres
Nous pouvons constater que la deuxime entre de la table CLIENTS peut tre mise en correspondance avec la premire et la quatrime entre de la table des commandes. Ceci peut tre traduit par: Mc Cartney a effectu 2 commandes, la 1230 et la 4321: cet exemple dvoile donc la fonction principale des clefs trangres qui est de METTRE EN RELATION deux entits (ici, cette relation pourrait tre appele CLIENT-COMMANDE).

IV.1.6.REPRSENTATION DES TABLES DANS LES DIAGRAMMES LOGIQUES:

La reprsentation d'une TABLE dans les diagrammes du MLD est trs semblable celle d'une entit dans le MCD. La diffrence est que dans le MLD on ajoute l'indication du DOMAINE DE DFINITION de chaque ATTRIBUT.
Le DOMAINE DE DFINITION d'un ATTRIBUT est l'ensemble des valeurs (numriques, alphanumriques, logiques, etc.) dans lequel cet attribut peu prendre ses valeurs. Par exemple, dans l'exemple prcdent,
  • Les attributs nom_cl, prnoms_cl et adresse_cl sont des chanes de caractres: ils prennent leurs valeurs dans l'ensemble des chanes de caractres, 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 reprsents sur 4 octets, souvent not long ou "long int"dans les langages informatiques.
Reprsentation graphique d'une table
Les principaux ensembles de dfinition sont les types de donnes communment dfinis par les langages de programmation:
  • Caractre de 1 octet: char (character);
  • Entier de 2 octets: int(integer);
  • Entier de 4 octets: long int (ou long);
  • Nombre avec dcimales de 4 octets: float (nombre muni d'une virgule ou encore "virgule flottante");
  • Nombre avec dcimales de 8 octets: double;
  • Chane de caractres: string (chane de caractres);
  • Indicateur binaire (0/1): bool (Boolean);
  • Etc.


IV.2.REPRSENTATION D'UNE ASSOCIATION BINAIRE:

IV.2.1.REPRSENTATION D'UNE ASSOCIATION BINAIRE DE TYPE 1,N PAR AJOUT D'UNE CLEF ETRANGRE:

RAPPEL: lorsque, dans une association binaire, une et une seule des deux cardinalits maximales est gale 1, l'association est de type "un n".
RGLE: Lorsque une association est de type (1,n), il est possible, SI L'ASSOCIATION NE PORTE PAS D'ATTRIBUT, de reprsenter simplement l'association par l'ajout d'une CLEF TRANGRE dans la table situe du cte o la cardinalit maximale est 1. Cette CLEF TRANGRE doit rfrencer 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 TRANGRE 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 emprunts un moment donn.

Le signe # au dbut du nom d'un attribut signale que celui-ci est une clef trangre simple ou appartient une clef trangre compose. Remarquons que la valeur de #id_li doit toujours tre dfinie dans ce cas.

IV.2.2.REPRSENTATION D'UNE ASSOCIATION BINAIRE DE TYPE 1,1 PAR AJOUT D'UNE CLEF ETRANGRE:

RAPPEL: lorsque, dans une association binaire, les deux cardinalits maximales sont gales 1, l'association est de type "un un".
RGLE: Lorsque une association est de type (1,1), il est galement possible, SI L'ASSOCIATION NE PORTE PAS D'ATTRIBUT, de reprsenter simplement l'association par l'ajout d'une CLEF TRANGRE dans la table situe "cot sortie" de l'association. Cette CLEF TRANGRE doit rfrencer la CLEF PRIMAIRE de l'autre table. La diffrence avec le cas des associations de type (1,n) est que la clef trangre est soumise ici une condition d'UNICITE, ce qui revient dire que cette clef trangre 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 mme valeur, ce qui revient dire que deux prescriptions ne peuvent tre attribues au mme patient.

IV.2.3.REPRSENTATION D'UNE ASSOCIATION BINAIRE QUELCONQUE PAR UNE TABLE DE JOINTURE :

Une association qui possde des attributs ou bien qui n'est ni de type 1,1, ni de type 1,n peut toujours tre reprsente 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 ETRANGRES qui correspondent aux CLEFS PRIMAIRES des entits mises en correspondance.

EXEMPLE:
Traduction d'une Association quelconque en table
Nous pouvons voir que la traduction a t ralise en introduisant une table intermdiaire appele TABLE DE JOINTURE (c'est la table Emprunts, que l'on pourrait aussi appeler Abonns-Exemplaires). Cette table rcupre les attributs de l'association. D'autre part, deux attributs y sont rajouts:
  • L'attribut #id_ab prend les mmes valeurs que l'attribut id_ab de la table Abonns: il s'agit d'une CLEF TRANGRE qui permet d'tablir la relation entre les relations (ou tables) Abonns et Emprunts,
  • De mme, l'attribut #rfrence_ex est ajout en tant que CLEF TRANGRE qui rfrence la clef primaire rfrence_ex de l'entit Exemplaires;
  • Ces clefs trangres constituent elles deux la clef primaire de la table de jonction.


IV.3.REPRSENTATION D'UNE ASSOCIATION DE RANG SUPRIEUR 2:

Pour reprsenter les association de rang suprieur 2 (ternaires, quaternaires), on utilise une TABLE DE JOINTURE constitue comme suit:
- Elle rcupre les attributs propres l'association;
- Pour toutes les CLEFS PRIMAIRES des tables correspondant aux entits associes, elle intgre une CLEFS TRANGRES correspondante.
- L'ensemble de ces clefs trangres constitue la clef primaire de la table de jonction.

La dmarche est donc une extension de la dmarche adopte 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 dnomination assez "mathmatiques", les FORMES NORMALES, dans le domaine des bases de donnes relationnelles, ne sont pas des concepts trs abstraits. En fait, elles dfinissent un certain nombre de rgles assez simples dont l'application aux ATTRIBUTS des TABLES d'un MODLE LOGIQUE rend ces tables plus faciles traduire dans les langages de requtes des SGBD relationnels.
On distingue TROIS FORMES NORMALES (de la premire forme la troisime), 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 PREMIRE FORME NORMALE:

PREMIRE TAPE: ELIMINATION DES GROUPES RPTS:
Pour qu'une table soit en premire forme normale, il faut qu'elle ne comporte aucun GROUPE D'ATTRIBUTS RPTITIF. Le schma qui suit illustre cette notion:
Table comportant un groupe d'attributs rptitifs
lorsqu'on effectue une commande auprs d'un commerant cette commande peut en gnral 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, QuantitCommandeArt_x, PrixUnitaireArticle_x), chaque groupe reprsentant un des articles commands. Cette manire de procder peut amener dfinir une table des commandes du type reprsent gauche du schma:

Or, cette faon de procder comporte divers inconvnients. En particulier, il est difficile de dfinir le nombre de rptitions possibles. De ce fait, les tables de ce type doivent tre transformes.

La table de droit correspond la mise en premire 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 modle, puisqu'on perd l'information qu'une commande peut tre compose de plusieurs articles.

De ce fait, la mise en premire forme normale doit tre accompagne de l'ajout d'une nouvelle table qui permettra de rtablir la logique du modle. C'est ce que nous allons voir par la suite.
DEUXIME TAPE: REPSENTATION DES GROUPES RPTITIFS PAR UNE NOUVELLE TABLE:
Les ENTRES (ou T-Uplets) d'un table peuvent reprsenter les diffrentes valeurs d'un GROUPE D'ATTRIBUTS. De ce fait, nous allons crer, la place de la table d'origine, deux NOUVELLES TABLES: l'une d'elle sera la table obtenue par la premire transformation, dbarasse de son groupe rptitif, la seconde sera forme de telle faon que ses entres reprsenteront les diffrentes valeurs du GROUPES D'ATTRIBUTS RPTITIFS que nous avons limins dans un premier temps (dans le cas tudi, cette table sera la table des ARTICLES_COMMANDES). Le schma ci-dessous reprsente cette transformation:
Mise en premire forme normale
Par cette dcomposition, une commande peut donc tre associe n ARTICLES_COMMANDES. La rptition des attributs NumArticle, QuantitCommande 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 PREMIRE FORME NORMALE. Remarquons que la CLEF PRIMAIRE de la table ARTICLES_COMMANDES est COMPOSE 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 reprsente en gnral la liste des articles que l'on peut acheter: chaque entre correspond un article diffrent. Or, la table ARTICLES_COMMANDES doit contenir tous les achats d'articles qui ont t effectus l'occasion de toutes les COMMANDES. De ce fait, plusieurs entres de cette table peuvent correspondre au mme article mais des commandes diffrentes. C'est ce qui explique la prsence de l'attribut NumCommande dans cette table. Les entres de ARTICLES_COMMANDES pour lesquelles l'attribut NumCommande a une valeur identique correspondent la mme commande.

IV.4.3.MISE D'UNE TABLE EN DEUXIME FORME NORMALE:

CONDITION PORTANT SUR LES ATTRIBUTS:
Pour qu'une table soit en deuxime forme normale, il faut et il suffit qu'elle remplisse les conditions suivantes;
  • Elle doit tre en premire forme normale (donc, ne comporter aucun groupe d'attributs caractre RPTITIF);
  • TOUS ses attributs qui ne font pas partie de la clef primaire doivent dpendre 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: QuantitCommande et PrixUnitaire.
Si QuantitCommande et PrixUnitaire dpendent tous deux de NumArticle, PrixUnitaire ne dpend 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 forcment 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 transfrer les attributs ne dpendant 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 transfrs.
A l'issue de ce processus, on obtient donc 2 tables 2NF:
  • l'ancienne table laquelle les arguments non dpendants de la totalit de la clef ont t retirs;
  • la nouvelle table qui accueille les arguments non dpendants de la totalit de la clef et la partie de la clef qui les concerne.
EXEMPLE:
Mise en deuxime forme normale
- On cre une nouvelle table ARTICLES_PRIX;
- On retire de la table ARTICLES_COMMANDES l'attribut PrixUnitaire, qui ne dpend que de NumArticle;
- On transfre 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 TROISIME FORME NORMALE:

CONDITION PORTANT SUR LES ATTRIBUTS:
Pour qu'une table soit en troisime forme normale, il faut et il suffit qu'elle remplisse les conditions suivantes:
  • Elle doit tre en 2NF (donc, pas de groupe d'attribut rptitif et tous les attributs dpendent de la totalit de la clef);
  • Un attribut n'appartenant pas la CLEF PRIMAIRE ne doit pas DPENDRE TRANSITIVEMENT de cette CLEF.
DEFINITION DE LA DPENDANCE TRANSITIVE:
Par dfinition tout attribut d'une table 2NF dpend DIRECTEMENT de la clef primaire de cette table. On dit qu'un attribut dpend TRANSITIVEMENT de la clef primaire si on peut tablir cette dpendance PAR L'INTERMDIAIRE D'UN AUTRE ATTRIBUT.
EXEMPLE:
Soit une table COMMANDES dont la clef primaire est NumCommande (numro de commande) et comportant les attributs NumClient (numro de client) et NomClient (nom du client). NomClient dpend videmment directement de NumClient (A un numro de client correspond un seul nom de client). Comme NumClient dpend de NumCommande, on a la dpendance TRANSITIVE NumCommande => NumClient => NomClient.
Le fait qu'un attribut A2 d'une table soit en relation de dpendance transitive avec la clef par l'intermdiaire d'un autre attribut A1 introduit forcment une contrainte au niveau de la gestion de la table: toute modification de l'un des attribut ncessite la modification de l'autre attribut, sous peine d'introduire une incohrence. Il est donc prudent d'viter ce type de contrainte en transformant les tables 2NF possdant 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 dpendances transitives en tables 3NF va consister, comme dans les cas prcdents, encapsuler les groupes d'attributs en dpendance transitives dans de nouvelles tables:
EXEMPLE:
Mise en troisime forme normale
Dans l'exemple ci-contre, la valeur de l'attribut NomClient dpend transitivement de l'attribut NumClient, sans que NumClient soit une clef primaire. Pour rsoudre ce problme, on cre une nouvelle table CLIENTS qui va encapsuler ces deux attributs et o NumClient sera clef primaire.
Cet exemple illustre assez bien l'intrt de la troisime forme normale: la transformation permet de faire apparatre une nouvelle table, la table CLIENTS qui regroupe les donnes attaches chaque client de l'entreprise et qui correspond en fait une entit CLIENTS du niveau MCD que l'analyse ce niveau n'aurait pas dtecte. L'ajout de la table CLIENTS permet d'envisager une gestion des clients indpendante des commandes (par exemple, la gestion de comptes clients), ce qui aurait t difficile sans cet ajout.

IV.4.5.RCAPITULATION SUR LES FORMES NORMALE:

Le tableau suivant rsume les conditions que les formes normales imposent aux tables d'un MLD:
FORME NORMALE CONDITIONS A REMPLIR
Premire forme normale (1NF) Pas de groupe d'attributs caractre RPTITIF
Deuxime forme normale (2NF) tre en 1NF + tous les attributs dpendent de la totalit de la clef primaire (Ceci implique: une table 1NF dont la clef est SIMPLE est donc forcment en 2NF)
Troisime forme normale (1NF) tre en 2NF + aucune dpendance TRANSITIVE des attributs avec la clef primaire


IV.5.NOTATIONS "ALGBRIQUES":

IV.5.1.NOTATION ALGBRIQUE DES TABLES:

Une TABLE peut tre reprsente sous une forme ALGBRIQUE de la manire suivante:
TABLE ==> NomTable ( Attribut1, Attribut 2, .., Attribut n )
Les attributs qui sont des CLEFS PRIMAIRES sont souligns, tandis que les CLEFS TRANGRES sont prcdes 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 ALGBRIQUE DES ATTRIBUTS:

Pour dsigner un attribut d'une table donne, on emploie souvent la notation:
<nom de table>.<nom d'attribut>
Cette notation, qui est la mme que celle qui permet de noter les attributs d'un objet dans de nombreux langages objets, est galement utilise dans les langages de requtes tels que SQL.
EXEMPLE: L'attribut NomClient de la table CLIENTS sera not CLIENTS.NomClient.


IV.6.OPRATIONS SUR LES TABLES

IV.6.1.GNRALITS:

Ce sous-chapitre dcrit les PRINCIPES LOGIQUES des principales oprations qu'un SGBD permet de raliser sur les TABLES des bases de donnes qu'il gre. Le dtail de leur ralisation sera trait lors de l'tude du Modle Physique et du langage SQL.
Les Systmes de Gestion de Bases de Donnes permettent d'effectuer certaines oprations sur les tables des bases de donnes qu'ils grent, soit par l'intermdiaire de formulaires de gestion, soit par l'intermdiaire de langages de requtes utilisables depuis les applications.
Une partie de ces oprations concerne la cration, la modification ou la destruction de ces tables: ces oprations sont assez banales et nous les tudierons dans les chapitres suivants. Dans ce qui suit, nous nous intresserons plutt aux oprations qui mettent en oeuvre les PARTICULARITS DU MODLE RELATIONNEL. Nous tudierons successivement:
  • La SLECTION, qui permet de SLECTIONNER certaines entres d'une table afin d'EXTRAIRE les valeurs de certains des attributs de ces entres;
  • L'INTERCLASSEMENT des entresd'une table, qui permet de modifier l'ordre des entres d'une table en fonction de critres portant sur les valeurs d'attributs;
  • L'AGRGATION, qui permet de combiner les valeurs des attributs de certaines entres afin d'en tirer des rsultats globaux: sommes d'attributs,valeur maximale ou minimale d'un attribut, nombre d'entres satisfaisant certaines conditions, etc.;
  • La PROJECTION, qui consiste crer 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 crer une table rassemblant les colonnes de plusieurs table possdant une colonne commune du point de vue des valeurs des attributs;

IV.6.2.L'OPERATION DE SLECTION:

La SLECTION permet de SLECTIONNER certaines entres d'une table afin d'EXTRAIRE les valeurs de certains des attributs de ces entres. Cette opration correspond au schma suivant:
SELECT <Liste de noms d'attributs> FROM <nom de table> WHERE <condition de slection>
(slectionner les VALEURS DES ATTRIBUTS dont la liste est indique UNIQUEMENT DANS LES ENTRES de la TABLE INDIQUE qui correspondent aux CONDITIONS DE SLECTION)
Le rsultat obtenu est un TABLEAU ASSOCIATIF comprenant:
- les LIGNES de la table satisfaisant la condition de slection;
- Les COLONNES correspondant aux attributs de la liste.
Les langages de requtes fournissent des fonctions qui permettent d'accder aux valeurs de chacun des lments de ces tableaux associatifs et de les rcuprer (Exemple: directives fetch_array (rcuprer_tableau) en sql).
EXEMPLE:
Exemple d'opration de slection
L'opration ci-contre permet de slectionner les valeurs des attributs num_commande et nom_client qui correspondent uniquement aux entres qui satisfont la condition "la valeur de l'attribut montant doit tre suprieure 250". Le rsultat est le tableau associatif indiqu ci-contre:
Les conditions peuvent tre beaucoup plus compliques que dans cet exemple: elles peuvent porter sur plusieurs attributs, comporter des conditions logiques, etc.

IV.6.3.LES OPERATIONS D'INTERCLASSEMENT:

Ces oprations reviennent modifier l'ordre des entres d'une table en fonction des valeurs d'un ou de plusieurs attributs. L'interclassement peut tre effectu en fonction de diffrents critres:
  • Par valeurs croissantes ou dcroissances d'un attribut numrique (ex: classement d'une table FACTURES en fonction du montant croissant ou dcroissant des factures);
  • Par ordre alphabtique (ex: classement d'une table CLIENTS en fonction de l'ordre alphabtique des noms, puis des prnoms des clients);
  • factures);
  • Par ordre de dates croissantes ou dcroissantes (ex: classement d'une table MESSAGES en fonction d'un paramtre date_rception 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 entres vont se retrouver naturellement classes en fonction de la date de prise en compte des commandes: les premires entres seront celles qui ont t enregistres aux dates les plus anciennes. On peut, pour des raisons d'efficacit, vouloir reclasser ces entres selon d'autres critres. 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 entres sont class par ordre alphabtique en fonction des noms de clients, puis en fonction des montants des commandes passes. Cette transformation se nommera INTERCLASSEMENT en fonction des attributs "nom_client" et "montant".
Exemple d'opration d'interclassement
REMARQUES:
- Lorsqu'il s'agit d'un traitement informatis, l'opration d'interclassement agit en gnral sur la faon dont la table va tre PRSENTE l'utilisateur et non sur l'ORDRE REL des entres: ceci revient crer une autre table, reclasse suivant les critres spcifis. Cette table ne dure que le temps ncessaire l'excution du traitement qui a justifi cet interclassement.
- Lorsque dans un interclassement, les valeurs de l'attribut d'interclassement sont les mmes pour deux lignes de la table, les deux lignes sont prsentes dans la table rsultante dans l'ordre dans lequel se prsentent dans la table d'origine.

IV.6.4.OPRATIONS D'AGRGATION DE DONNES D'UNE TABLE:

Ce type d'oprations a pour caractristique de porter sur un ENSEMBLE D'ENTRES d'une table: les valeurs des attributs correspondant certaines entres sont "agrges" ("assembles", "combines") entre elles pour aboutir un rsultat. la notation gnrale de ce type d'oprations est:
FONCTION_AGREGATION( nom d'attribut )
o:
  • FONCTION_AGREGATION est le nom de l'opration ( count, max, min, etc.);
  • nom_attribut est le nom de l'attribut sur les valeurs duquel porte l'opration (ex: sum(montant_transaction) fait la sommes des valeurs de l'attribut montant_transaction pour plusieurs entres).
Ces oprations sont toujours intgres dans des OPRATIONS DE SLCTION. Elles agissent sur les entres slectionnes par l'intermdiaire de l'opration de slection laquelle sont intgres, ce qui veut dire qu'elles agiront uniquement sur les entres slectionnes par cette opration. Par exemple, l'opration de slection:
SELECT SUM(montant) nom_client FROM Commande WHERE num_commande > 2017251201
permettra la fonction d'agrgation SUM (somme) de faire le total des montants des factures dont le numero est suprieur 2017251201. Les principales fonctions d'agrgation sont:
FORME LE PLUS SOUVENT UTILISEFONCTION
AVG(nom_attribut)Calcule la moyenne des valeurs de l'attribut pour toutes les entres slectionnes
COUNT(nom_attribut)Compte le nombre d'entres slectionnes
MAX(nom_attribut)retourne la valeur maximale de l'attribut pour les entres slectionnes
MIN(nom_attribut)retourne la valeur minimale de l'attribut pour les entres slectionnes
SUM(nom_attribut)retourne la somme des valeurs de l'attribut pour les entres slectionnes

IV.6.5.OPRATION DE PROJECTION D'UNE TABLE:

PROJETER une table revient crer 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.OPRATIONS DE JOINTURE DE TABLES:

DFINITION:
Soient deux tables T1 et T2. Supposons qu'il existe respectivement dans T1 et T2 deux attributs A1 et A2 qui correspondent la mme information, exprime de la mme manire (format de reprsentation et units). Dans ce cas, il est possible de JOINDRE deux deux les entres de T1 et T2 pour lesquelles la valeur de A1 est identique celle de A2. Le rsultat de cette opration sera une table comprenant toutes les colonnes des deux tables T1 et T2, l'exception des colonnes correspondant A1 et A2 qui sont fusionnes en une seule colonne. Cette opration est appele JOINTURE des tables T1 et T2.
Schma de la jointure de deux tables
REMARQUES:
  • La jointure est, dans ce cas, dite COMPLTE 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 systmatique;
  • Pour une valeur donne V, Les couples forms en associant les entres de T1 et T2 telles que A1 = A2 = V ne sont pas forcment uniques. En effet, A1 et A2 n'tant pas forcment des clefs primaires, il est possible que l'attribut A1 soit gal V pour plusieurs entres de T1, et de mme pour A2 et T2. On retrouvera donc dans la table de jointure toutes les combinaisons possibles d'entres 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 entre de T2 prsentant la mme valeur pour T2, et inversement. Dans ce cas certains T-uplets de T1 ou T2 ne pourront tre apparis. Suivant que l'on intgrera ou non ces T-uplets dans la table de jointure, la jointure sera qualifie d'interne ou d'externe, de jointure " gauche" ou " droite", etc. Nous tudierons ces diffrents cas dans le chapitre traitant du modle 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 mme numrotation des clients. Les attributs num_client et id_client correspondent la mme INFORMATION (une identification de chaque client par un numro unique), exprime de la mme manire. La jointure des deux tables pourra donc tre effectue en associant les entres des deux tables pour lesquelles les valeurs des attributs num_client et id_client ont la mme 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 COMPLTES ET JOINTURES INCOMPLTES
La jointure est dite COMPLTE si la table de jointure reprend les attributs des deux tables (comme dans l'exemple prcdent). Elle est incomplte dans le cas contraire (par exemple: Clients_commandes ( nom_client, adresse_livraison ) est une jointure incomplte des tables clients et commandes.
DIFFRENTS MODES DE JOINTURE
Le fait que les deux attributs de jointure soient identifis 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 prcdent, 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 mme, 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 entres des deux tables: certaines entres resteront non apparies et les valeurs de certains attributs de la table de jonction resteront indfinies. De ce fait, diffrents modes de jointure peuvent tre dfinis, en fonction de la manire dont ces cas seront traits:
  • FULL JOIN (runion) si la table de jointure comprend la totalit des entres des deux tables initiales (mme celles qui ne peuvent tre apparies);
  • INNER JOIN (intersection) si seulement les entres possdant la mme valeur de l'attribut de jonction dans les deux tables sont pris en compte dans la table de jointure (on ne considre que les entres qui peuvent tre apparies);
  • LEFT JOIN si toutes les entres de la premire tables sont reprises et seulement les entres de la seconde possdant une valeur de l'attribut de jonction existant dans la premire sont prises en compte dans la table de jointure;
  • RIGHT JOIN si toutes les entres de la deuxime tables sont reprises et seulement les entres de la premire possdant 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 dmarche 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 rsultat obtenu;
  • Puis on joint T1 J(T2, J(T3,T4)): on note J(T1, J(T2, J(T3, T4)) ) le rsultat final.
UTILIT DE LA JOINTURE DE TABLES
La jointure de tables permet de mettre en oeuvre les RELATIONS entre tables dfinies par les couples CLEF PRIMAIRE - CLEF TRANGRE. En effet, lorsqu'on effectue la jointure de deux tables suivant leur couple d'attributs clef primaire - clef trangre, on obtient une table qui MET EN RELATION tous les attributs des deux tables.
EXEMPLE: Le schma ci-dessous reprsente deux tables mises en relation par un couple d'attributs Clef primaire - Clef trangre:
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 trangre correspondante) va donner la table de jointure suivante:
Utilisation de la jointure de deux tables
Pour connatre (par exemple) l'adresse mail du client de la commande 2017-05-08-22, il suffira d'utiliser une seule opration de slection:
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 (premire opration de slection, puis rcupration de la valeur slectionne), puis d'extraire de la table CLIENTS l'adresse mail du client dont la valeur des la clef trangre #nom_client correspond la valeur de nom_client extraite par la slection prcdente (deuxime opration de slection, puis rcupration de la valeur slectionne)
JOINTURE DE PLUS DE DEUX TABLES:
La dmarche est la mme lorsque plus de deux tables sont mises en relation par des couples clef primaire-clef trangre: la jonction transitive (vue plus haut) permet d'obtient une table qui MET EN RELATION tous les attributs des difrentes table jointes et, de ce fait, d'extraire en une seule slection des valeurs d'attributs qui auraient ncessit autant d'oprations de slection que de tables jointes.
JOINTURE ET SELECTION
Comme les autres oprations sur les tables, les oprations de jointures sont intgres dans des opration de slection. Ceci entrane que les jointures ne se font que sur les entres des deux tables qui sont slectionnes par l'opration de slection. Une opration de jointure est en gnral identifie par les oprateurs JOIN et ON: JOIN permet d'indiquer les tables joindre et ON permet d'indiquer les attributs suivant lesquels la jointure doit tre effectue:
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 entres de Commandes dont les montants sont infrieurs 1000 (par contre, on prend toutes les entres de Clients). INNER signifie qu'on ne conserve que les couples d'entres qui peuvent tre mises en correspondance).


IV.7.EXEMPLE DE PASSAGE DU MODLE CONCEPTUEL AU MODLE LOGIQUE:

IV.7.1.RAPPEL: PROBLME A RSOUDRE:

Le sous-chapitre III.4 nous a permis d'laborer le Modle Conceptuel de Donnes (MCD) d'une bibliothque. Le schma obtenu est le suivant:
MCD de la bibliothque
Nous allons laborer le Modle Logique des Donnes (MLD) qui correspond ce MCD en appliquant les directives et rgles nonces dans le prsent chapitre. D'aprs ce qui prcde, pour dduire le MLD correspondant ce MCD, nous devons:
  • Transformer les entits du MCD en TABLES;
  • Remplacer les ASSOCIATIONS de type 1:n et 1:1 non porteuses d'attributs par des changes de CLEFS TRANGRES entre les entits qu'elles relient;
  • Remplacer les autres associations par des TABLES DE JONCTION;
  • Mettre toutes les relations (tables) ainsi dfinies en troisime forme normale.

IV.7.2.TRANSFORMATION DES ENTITS DU MCD EN TABLES DU MLD:

Cette transformation ne pose, priori, pas beaucoup de problmes: 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 dfinition de l'ensemble dans lequel chaque attribut prend sa valeur ("int" pour nombre entier, "str" pour chanes de caractres, "text" pour texte de longueur indtermine, etc.). Par exemple, l'entit AUTEURS donnera la table AUTEURS que l'on reprsentera comme indiqu ci-dessous:
Table des auteurs dans le MLD de la bibliothque
La transformation des entits en tables ne pose donc pas de problme particulier part le choix des ensembles de dfinition. Pour fixer les esprits, voici un exemple du contenu de la table Auteurs lorsque celui-ci aura t dfini (lors de la ralisation du MODLE PHYSIQUE):
Exemple de contenu de la table des auteurs
REMARQUE: dans cette table, l'attribut id_au a t dfinie comme une clef primaire auto incrmentespar le SGBD chaque dfinition d'une nouvelle entre dans la table (l'utilisateur ne peut fixer lui-mme cette valeur). Supposons que l'entre d'Id_au = 2 soit supprime, puis qu'une nouvelle entre soit cre: le numro 2 ne sera jamais rattribu par le SGBD (sauf si la table est dtruite puis reconstitue). une nouvelle entre 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 entits Livres et Exemplaires. Les cardinalits sont respectivement 0,n et 1,1: comme une seule des cardinalits maximales est gale 1, l'association est de type 1,n. Comme elle ne porte pas d'attribut, cette association peut tre reprsente par l'adjonction l'entit Exemplaires d'une clef trangre 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 entits Abonne et Exemplaires. Les cardinalits sont respectivement 0,n et 1,1: comme une seule des cardinalits maximales est gale 1, l'association est de type 1,n. Cependant, comme elle porte des attributs, cette association ne peut tre reprsente par un simple couple clef primaire - clef trangre. Il faut crer une nouvelle table (table de jonction) qui portera les attributs de l'association ainsi que des clefs trangres associes aux clefs primaires d'Abonne 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 rcuprer les arguments de l'association Emprunter. Sinon, le simple ajout de la clef trangre #id_ab dans la table Exemplaires aurait suffi.
ASSOCIATION ECRIRE:
L'association Ecrire relie les entits Auteurs et Livres. Les cardinalits sont respectivement 1,n et 1,n: comme aucune cardinalits maximales n'est gale 1, l'association est de type m,n. De ce fait, mme si elle ne porte pas d'attribut, cette association ne peut tre reprsente par un simple couple clef primaire - clef trangre. Il faut donc l aussi crer une table de jonction. Cette table portera uniquement les clefs trangres associes respectivement aux clefs primaires des entits Auteurs et Livres:
Transformation de l'association Ecrire
REMARQUE:
S'il avait t spcifi au dpart qu'un livre ne peut tre crit que par un seul auteur, la seule adjonction dans la table Livres de la clef trangre #id_auteurs aurait permis d'identifier l'auteur d'un livre, puisque cet auteur est forcment 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 rgle de transformation).

IV.7.4.MODLE LOGIQUE COMPLET:

Compte tenu de ce qui prcde, le modle logique de donnes de la bibliothque se prsentera comme suit:
Menu de commande de l'animations

Modle logique complet de la bibliothque
COMMENTAIRES:
  • La partie droite du schma reprsente le Modle Conceptuel de la bibliothque (MCD);
  • La partie gauche du schma reprsente le Modle Logique de la bibliothque (MLD), directement dduit du MCD;
  • Nous pouvons constater que toutes les entits du MCD ont t directement reprsentes dans le modle logique par des tables portant les mmes noms;
  • L'association Ecrire qui relie les entits Auteurs et Livres du MCD a t reprsente dans le modle logique par la table Auteurs_livres, munie des clefs trangres #id_au et #id_li. Ces clefs trangres correspondent aux clefs primaires des tables Auteurs et Livres;
  • Chaque entre 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 cre donc bien une ASSOCIATION entre les auteurs et les livres;
  • La cration de la table Auteurs-livres est rendue ncessaire par le fait que les cardinalits de l'association Ecrire du MCD sont de type m-n;
  • L'association Correspondre qui associe dans le MCD les entits 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 grce l'adjonction d'une clef trangre la table situe du ct o la cardinalit maximale et 1;
  • La clef trangre #id_li a donc t ajoute la table Exemplaires. Cette clef permet de mettre en correspondance chaque reprsentant 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 entits Abonns et Exemplaires. Elle est de type 1,n, mais elle est dote d'attributs propres. Il est donc ncessaire de la reprsenter par une nouvelle table qui pourra acqueillir ces attributs. Nous appellerons cette table Abonns-Exemplaires;
  • D'autre part, pour traduire l'association emprunter, il sera ncessaire de doter cette table Abonns-Exemplaires de deux clefs trangres faisant rfrence aux clefs primaires des associations Abonns et Exemplaires. Ces clefs seront nommes respectivement #id_ab et #reference_ex;
  • Dans ces conditions, la table Abonns-Exemplaires permet bien d'associer chaque exemplaire l'abonn qui l'a emprunt (s'il a t emprunt);
  • Le MLD de la gauche du schma reprsente donc bien la traduction LOGIQUE du MCD la droite du schma;
Ce diagramme permet d'aborder directement la construction du modle physique. C'est ce que nous ferons au chapitre suivant.



Retour accs aux cours Retour sommaire cours
FormateurGaucheRepos FormateurGaucheActif FormateurDroitRepos FormateurDroitActif