BASES DE DONNESET MODLES DE CALCUL

Outils et mthodespour lutilisateur

Cours et exercices corrigs

Jean-Luc Hainaut

Professeur lInstitut dInformatiquedes Facults Universitaires Notre-Dame de la Paix, Namur

4

e

dition

0 lim Page I Jeudi, 2. juin 2005 5:19 17

hranFile Attachment2100491466_Hainaut.jpg

Illustration de couverture :

Contexture, digitalvision

Dunod, Paris, 2000, 2002, 2005

InterEditions, Paris, 1994

ISBN 2 10 049146 6

0 lim Page II Jeudi, 2. juin 2005 5:19 17

Yves, Vronique et Jean-Pierre,Alain, Benot et Carine,

Bertrand, Catherine, Muriel et Michel,Olivier, Mario et Bernard,

Didier, Jean, Vincent et Jean-Marc,Alain, Pierre, Thierry et Anne-France,

Arnaud, Stphane, Olivier, Philippe et Majid,Denis, Virginie et Thomas,

ChristineAurore et Fabrice,

Jean-Roch,Ravi et Julien,

Eric,Anthony,

Yannis et Frdric

Table des matires

AVANT-PROPOS

15

CHAPITRE 1

MOTIVATION ET INTRODUCTION

17

1.1 Lutilisateur-dveloppeur, heurs

17

1.2 et malheurs

18

1.3 Objectif de louvrage

21

PARTIE 1

LES BASES DE DONNES

CHAPITRE 2

INTRODUCTION

25

2.1 Lutilisateur et les donnes

25

2.2 Bases de donnes et SGBD relationnels

26

2.3 Construction dune base de donnes

28

2.4 Description de la premire partie

29

2.5 Pour en savoir plus

30

CHAPITRE 3

CONCEPTS DES BASES DE DONNES

31

3.1 Table, ligne et colonne

31

3.2 Rles dune colonne

33

3.2.1 Les identifiants 333.2.2 Les cls trangres 343.2.3 Les informations complmentaires 34

6

Table des matires

3.2.4 Les identifiants et cls trangres multicomposants 343.2.5 Les identifiants primaires 343.2.6 Les contraintes rfrentielles 353.2.7 Les colonnes facultatives 35

3.3 Structure et contenu dune base de donnes

36

3.4 Reprsentation graphique dun schma

37

3.5 Un exemple de base de donnes

38

3.6 Autres notations graphiques

40

3.7 Note sur les contraintes rfrentielles

41

3.8 Modification et contraintes dintgrit

43

3.8.1 Les contraintes dunicit (identifiants) 433.8.2 Les contraintes rfrentielles (cls trangres) 433.8.3 Les colonnes obligatoires 45

3.9 La normalisation

45

3.9.1 Le phnomne de redondance interne 463.9.2 Normalisation par dcomposition 463.9.3 Analyse du phnomne 473.9.4 Remarques 49

3.10 Les structures physiques

50

3.11 Les systmes de gestion de donnes

51

3.12 SQL et les bases de donnes

53

3.13 Exercices

54

CHAPITRE 4

LE LANGAGE SQL DDL

55

4.1 Introduction

55

4.2 Le langage SQL DDL

56

4.3 Cration dun schma

56

4.4 Cration dune table

57

4.5 Suppression dune table

61

4.6 Ajout, retrait et modification dune colonne

61

4.7 Ajout et retrait d'une contrainte

62

4.8 Les structures physiques

63

CHAPITRE 5

LE LANGAGE SQL DML

65

5.1 Introduction

65

5.2 Consultation et extraction de donnes dans une table

66

5.2.1 Principes 665.2.2 Extraction simple 66

Table des matires

7

D

unod

L

a ph

otoc

opie

non

aut

oris

e e

st u

n d

lit.

5.2.3 Extraction de lignes slectionnes 675.2.4 Lignes dupliques dans le rsultat 685.2.5 Des conditions de slection plus complexes 705.2.6 Un peu de logique 725.2.7 Donnes extraites et donnes drives 765.2.8 Les fonctions SQL 775.2.9 Les fonctions agrgatives (ou statistiques) 80

5.3 Slection utilisant plusieurs tables : les sous-requtes

82

5.3.1 Les sous-requtes 825.3.2 Sous-requte et cl trangre multi-composant 835.3.3 Attention aux conditions dassociation ngatives 845.3.4 Rfrences multiples une mme table 865.3.5 Les quantificateurs ensemblistes 88

5.4 Extraction de donnes de plusieurs tables (jointure)

91

5.4.1 La jointure de plusieurs tables 915.4.2 Conditions de jointure et conditions de slection 925.4.3 Jointures sans conditions : produit relationnel 935.4.4 La jointure et les lignes clibataires - Les oprateurs ensemblistes 935.4.5 Les requtes sur des structures de donnes cycliques 965.4.6 Sous-requte ou jointure? 1005.4.7 Valeurs drives dans une jointure 1035.4.8 Les jointures gnralises 1035.4.9 Interprtation du rsultat dune jointure 104

5.5 Extraction de donnes groupes

107

5.5.1 Notion de groupe de lignes 1075.5.2 Slection de groupes et slection de lignes 1085.5.3 Groupes et jointures 1095.5.4 Composition du critre de groupement 1105.5.5 Attention aux groupements multi-niveaux 1115.5.6 Peut-on viter lutilisation de donnes groupes ? 112

5.6 Ordre des lignes dun rsultat

113

5.7 Interprtation dune requte

114

5.8 Modification des donnes

115

5.8.1 Ajout de lignes 1155.8.2 Suppression de lignes 1165.8.3 Modification de lignes 1175.8.4 Mise jour et contraintes rfrentielles 1175.8.5 Modification des structures de donnes 120

5.9 Exercices

121

5.9.1 noncs de type 1 1215.9.2 noncs de type 2 1215.9.3 noncs de type 3 1225.9.4 noncs de type 4 123

8

Table des matires

5.9.5 noncs de type 5 1265.9.6 noncs de type 6 1285.9.7 nonc de type 7 129

CHAPITRE 6

SQL AVANC

131

6.1 Le contrle daccs

131

6.2 Les vues SQL

133

6.2.1 Principe et objectif des vues 1346.2.2 Dfinition et utilisation dune vue 1346.2.3 Les vues comme interface pour des besoins particuliers 1356.2.4 Les vues comme mcanisme de contrle daccs 1356.2.5 Les vues comme mcanisme dvolution de la base de donnes 1366.2.6 Les vues comme aide lexpression de requtes complexes 1366.2.7 Mise jour des donnes via une vue 136

6.3 Extension de la structure des requtes SFW

137

6.3.1 Extension de la clause select 1376.3.2 Extension de la clause from 1386.3.3 Les requtes rcursives 143

6.4 Les prdicats (check)

144

6.5 Les procdures SQL (stored procedures)

145

6.6 Les dclencheurs (triggers)

146

6.7 Le catalogue

147

6.8 Les extensions proposes par SQL3

151

6.9 Les interfaces entre BD et programmes dapplication

152

6.10 SQL et linformation incomplte

156

6.10.1 Introduction 1566.10.2 La valeur

null

de SQL 1566.10.3 La logique ternaire de SQL 1566.10.4 La propagation de

null

en SQL 1576.10.5 La propagation de

unknown

en SQL 1586.10.6 Les problmes de linformation incomplte en SQL 1596.10.7 Deux recommandations 164

6.11 Exercices

165

6.11.1 Contrle daccs 1656.11.2 Le catalogue 165

CHAPITRE 7

APPLICATIONS AVANCES EN SQL

167

7.1 Les structures dordre

167

7.2 Les bases de donnes actives

170

7.2.1 Les contraintes dintgrit statiques 1707.2.2 Les contraintes dintgrit dynamiques 170

Table des matires

9

D

unod

L

a ph

otoc

opie

non

aut

oris

e e

st u

n d

lit.

7.2.3 Le contrle de la redondance 1717.2.4 Les alerteurs 1727.2.5 Personnalisation des comportements standard 1737.2.6 Intgration dune rgle de gestion dans la base de donnes 173

7.3 Les donnes temporelles

174

7.3.1 Reprsentation des donnes temporelles 1747.3.2 Interrogation de donnes temporelles 1757.3.3 La projection temporelle 1767.3.4 La jointure temporelle 1787.3.5 Gestion des donnes historiques 180

7.4 La gnration de code

183

7.4.1 Migration de donnes 1837.4.2 Gnration de migrateurs de donnes 1847.4.3 Gnration de dfinitions de bases de donnes 1867.4.4 Gnration de pages HTML 1877.4.5 Gnration de documents XML 1897.4.6 Gnration de gnrateurs de pages HTML ou de documents XML 189

7.5 Exercices

190

7.5.1 Les structures dordre 1907.5.2 Les bases de donnes actives 1907.5.3 Les donnes temporelles 1927.5.4 La gnration de code 195

CHAPITRE 8

CONSTRUCTION DUNE BASE DE DONNES

199

CHAPITRE 9

LE MODLE ENTIT-ASSOCIATION

203

9.1 Types dentits

203

9.2 Attributs

204

9.3 Types dassociations

205

9.3.1 Proprits dun type dassociations 206

9.4 Les identifiants

211

9.4.1 Les identifiants hybrides 2129.4.2 Composition des identifiants 2149.4.3 Identifiants minimaux et identifiants implicites 2149.4.4 Importance du concept didentifiant 215

9.5 Autres contraintes dintgrit

216

9.5.1 Les contraintes dintgrit statiques 2179.5.2 Les contraintes dintgrit dynamiques 217

9.6 Contenu informationnel dun schma

217

9.7 Exemples

218

9.7.1 Une structure administrative 219

10

Table des matires

9.7.2 Gestion dune bibliothque 2209.7.3 Voyages en train 221

9.8 Quelques rgles de prsentation

222

9.9 Extensions du modle entit-association

222

9.10 ... et UML ?

226

9.10.1 Le modle de classes dUML 2269.10.2 Un exemple de schma de classes en UML 2299.10.3 Le modle de classes dUML revisit 230

9.11 Exercices

232

CHAPITRE 10

LABORATION DUN SCHMA CONCEPTUEL

233

10.1 Introduction

233

10.2 Dcomposition de lnonc

235

10.3 Pertinence dune proposition 241

10.4 Reprsentation dune proposition 241

10.5 Non-redondance des propositions 250

10.6 Non-contradiction des propositions 253

10.7 Les contraintes dintgrit 254

10.8 Documentation du schma 256

10.9 Compltude du schma 257

10.10 Normalisation du schma 258

10.11 Validation du schma 260

10.12 Exercices 261

CHAPITRE 11 PRODUCTION DU SCHMA DE LA BASE DE DONNES 269

11.1 Introduction 269

11.2 Reprsentation des types dentits 270

11.3 Reprsentation des attributs 270

11.4 Reprsentation des types dassociations 270

11.4.1 Types dassociations un--plusieurs 27011.4.2 Types dassociations un--un 27311.4.3 Types dassociations plusieurs--plusieurs 27511.4.4 Types dassociations cycliques 276

11.5 Reprsentation des identifiants 276

11.6 Traduction des noms 277

11.7 Synthse des rgles de traduction 277

11.8 Les structures physiques 278

Table des matires 11

Dun

od

La

phot

ocop

ie n

on a

utor

ise

est

un

dlit

.

11.9 Traduction des structures en SQL 280

11.10 Complments 281

11.10.1 Les contraintes dintgrit additionnelles 28111.10.2 Au sujet des rles de cardinalit 1-N 283

11.11 Rtro-ingnierie dune base de donnes 284

11.12 Extensions de la mthode 288

11.13 Exercices 289

CHAPITRE 12 BASES DE DONNES : TUDES DE CAS 293

12.1 Introduction 293

12.2 Les animaux du zoo 294

12.2.1 nonc 29412.2.2 Construction du schma conceptuel 29412.2.3 Production du schma de tables 29612.2.4 Production du code SQL 297

12.3 Voyages ariens 299

12.3.1 nonc 29912.3.2 Construction du schma conceptuel 30012.3.3 Production du schma de tables 303

12.4 Exercice 303

PARTIE 2

LES MODLES DE CALCUL

CHAPITRE 13 INTRODUCTION 307

13.1 Le tableur 307

13.2 Le concept de modle 308

13.3 Construction dun modle de calcul 308

13.4 Description de la deuxime partie 309

13.5 Pour en savoir plus 309

CHAPITRE 14 CONCEPTS DES MODLES DE CALCUL 311

14.1 Modles et processeurs de modles 311

14.2 Modles et tableaux 312

14.3 Reprsentation dun modle dans une feuille de calcul 313

14.4 Le march des tableurs 315

12 Table des matires

CHAPITRE 15 UN TABLEUR TYPE : EXCEL 317

15.1 Prsentation dExcel 317

15.2 La feuille de calcul 318

15.3 Organisation des feuilles de calcul et des modles 318

15.4 Les composants dun modle 319

15.4.1 Dsignation de cellules 31915.4.2 Le contenu des cellules 31915.4.3 Les formules 320

15.5 Modifications lmentaires dun modle 322

15.6 Dplacement et copie de fragments de modles 323

15.6.1 Adresses relatives et adresses absolues 323

15.7 Les rfrences circulaires 325

15.8 Fonctions de bases de donnes 326

15.9 Les tables de donnes 326

15.10 Les scnarios 327

15.11 Macros et fonctions personnalises 327

15.12 Les rsolveurs avancs 328

15.12.1 La valeur cible 32815.12.2 Le solveur 328

CHAPITRE 16 CONSTRUCTION DUN MODLE DE CALCUL 331

CHAPITRE 17 EXPRESSION ABSTRAITE DUN MODLE 335

17.1 Introduction 335

17.2 Grandeurs et rgles 336

17.3 Notion de modle 338

17.4 Descriptions externe et interne dun modle 340

17.5 Grandeurs dfinition multiple 341

17.6 Grandeurs et rgles logiques 342

17.7 Graphe de dpendance 342

17.8 Les valeurs dexception 345

17.9 Grandeurs et modles dimensionns 347

17.10 Les fonctions agrgatives 349

17.11 Rgles de rcurrence et rcursivit 351

17.12 Sous-modles et modularisation 356

Table des matires 13

Dun

od

La

phot

ocop

ie n

on a

utor

ise

est

un

dlit

.

CHAPITRE 18 CONCEPTION DUN MODLE 361

18.1 Dmarche de conception dun modle 361

18.2 Les principes 362

18.3 La dmarche 36318.3.1 Analyse 36318.3.2 Normalisation du modle 36818.3.3 Validation du modle 37018.3.4 Gnralisation par dimensionnement 371

18.4 Sous-modles non directionnels 372

18.5 Cohrence dun modle 37418.5.1 Cohrence structurelle 37418.5.2 Cohrence des rgles de dfinition multiple 37518.5.3 Cohrence des rgles de rcurrence 37618.5.4 Cohrence des units 37718.5.5 Cohrence des dimensions 37918.5.6 Cohrence des domaines de valeurs du modle 381

18.6 Exercices 38818.6.1 Modles lmentaires 38818.6.2 Modles avancs 38918.6.3 Validation de modles 39418.6.4 ... et en guise de dessert 396

CHAPITRE 19 IMPLANTATION DUN MODLE DANS UNE FEUILLE DE CALCUL 397

19.1 laboration dune maquette 39719.1.1 Reprsentation des grandeurs dimensionnes 39819.1.2 Les grandeurs internes 40019.1.3 Les sous-modles 40119.1.4 Exemple de maquette 40219.1.5 Ergonomie des modles 403

19.2 Traduction des rgles 40419.2.1 Principes gnraux 40419.2.2 Grandeurs dfinition multiple 40519.2.3 Rgles de rcurrence et rgles rcursives 40519.2.4 Les contraintes 406

19.3 Squentialisation dun modle 407

19.4 Ralisation dun programme squentiel 410

19.5 Exercices 412

CHAPITRE 20 MODLES : TUDES DE CAS 413

20.1 Introduction 413

20.2 Les animaux du zoo 413

14 Table des matires

20.2.1 nonc 41420.2.2 Construction du modle abstrait 41420.2.3 Implantation du modle dans une feuille de calcul 418

20.3 Voyages ariens 42120.3.1 Construction du modle abstrait 42120.3.2 Implantation du modle dans une feuille de calcul 424

BIBLIOGRAPHIE 427

INDEX 431

Avant-propos

Un ouvrage qui combine bases de donnes et feuilles de calcul, ce que certainslecteurs traduiront un peu trop rapidement par Access + Excel, pourrait tonner une poque o les ouvrages techniques sont gnralement trs cibls.

Avant dtre technique, cet ouvrage est essentiellement mthodologique. Ilsattaque la question du savoir-(bien-)faire, plutt qu celle de la matrise tech-nique des arcanes dun outil, thme qui est largement majoritaire dans la productionlittraire informatique. Bien sr, construire une base de donnes ou une feuille decalcul correctes suppose des connaissances raisonnables sur les bases de donnes etles tableurs, mais ces connaissances sont sans objet si nous ne sommes pas capablede poser correctement le problme, ce quon appelle modliser. Une fois leproblme pos, exprimer sa solution laide des outils informatiques puissants dontnous disposons aujourdhui devient un problme tonnament simple.

Cest lambition de cet ouvrage que damener le lecteur motiv, quil soitdbutant ou informaticien curieux, bien comprendre les bases des deux outilsessentiels que sont les systmes de bases de donnes et les tableurs, et les utiliserpour rsoudre de manire correcte des problmes non triviaux.

Tout ceci ne rpond pas la question : pourquoi coupler bases de donnes etfeuilles de calcul ? Pour deux raisons. Dune part, il sagit de deux modes de rsolu-tion de problmes trs puissants, mais nanmoins la porte des utilisateurs, pourpeu quils soient raisonnablement motivs. Rappelons que toutes les suites bureauti-ques, dOffice de Microsoft jusqu Star Office, comportent un gestionnaire dedonnes et un tableur. Dautre part, il apparat que construire de manire disciplineune solution informatique, quil sagisse dune base de donnes ou dune feuille decalcul, constitue un seul et mme exercice intellectuel.

Cet ouvrage est le fruit dune dmarche denseignement et de recherche tant enmilieu universitaire quen entreprise. Sa structure a en grande partie t dicte parles difficults que des gnrations dtudiants et de professionnels ont rencontres

16 Avant-propos

lors des enseignements et des sminaires que nous avons organiss depuis prs devingt ans.

Dans ce contexte, il est clair quune telle matire na pu prendre forme sans denombreuses collaborations, parfois tnues en apparence, mais toujours significatives terme. Je remercie donc tous ceux qui mont aid, et en particulier :

Vronique Goemans, Lysiane Gailly-Goffaux et Jean-Pierre Thiry avec qui jaianim les sessions consacres laide la dcision, et les membres dupersonnel dUTA. Claire Faure, qui organisait ces formations;

Anne Borsu-Bilande, Vincent Sapin, Damien Lanotte, Carine Papin, AlainGofflot, Muriel Chandelon, Olivier Marchand, Jean Henrard, Jean-Marc Hick,Vincent Englebert, Didier Roland, Alain Gofflot, Pierre Delvaux, Anne-FranceBrogneaux, Philippe Thiran, Virginie Detienne, Aurore Franois et Jean-RochMeurisse qui mont aid, et maident encore, dans mes cours de lInstitutdInformatique et la facult des Sciences conomiques de Namur;

mes collgues, anciens et actuels, Karin Becker, Franois Bodart, Jean-PaulLeclercq, Jean-Marie Lambert, Patrick Heymans pour nos discussionsmthodologiques; une pense toute particulire Vincent Englebert, pour sasagacit dminer SQL;

les tudiants des facults de Sciences conomiques, Sociales et Politiques, deDroit et dInformatique de luniversit de Namur, qui, par leurs ractions etleurs exigences, ont peu peu impos cet ouvrage sa forme actuelle,

mes collgues dautres coles et universits, ainsi que leurs tudiants, qui nontpas hsit me faire part de leurs commentaires et suggestions concernant lespremires ditions de cet ouvrage; je remercie en particulier le professeur YvesPigneur de lUniversit de Lausanne et ses tudiants,

tous ceux que ma mauvaise mmoire et ma distraction mauraient fait oublier.

Cet ouvrage est la quatrime dition de celui qui avait t publi chez InterEditionsen 1994 [Hainaut, 1994]. Il sen distingue sur les points suivants : la premire partieconsacre aux bases de donnes a t substantiellement rvise et augmente afinque soient prises en compte les suggestions qui mont t formules, lvolution desoutils (SQL2), celle des mthodes (rtro-ingnierie, AGL, UML) et les nouvellesapplications (BD actives, BD temporelles, HTML, XML). La deuxime partie(modles de calcul) a t rafrachie et actualise. La troisime partie abordant lecouplage entre bases de donnes et modles de calcul a t supprime.

Un site Web accompagne cet ouvrage. Il contient des matriaux complmentairesconcernant les exercices, dont certains accompagns dune solution, des correctionsventuelles, des outils (dont lAGL DB-MAIN), ainsi que la troisime partie de[Hainaut, 1994] absente de cette dition.

Adresse du site : www.info.fundp.ac.be/libd (volet Documents / Ouvrages)Contacts : jlh@info.fundp.ac.be

http://www.info.fundp.ac.be/libd

Chapitre 1 1

Motivation et introduction

Ce premier chapitre prsente les problmes que posent les outils popu-laires de dveloppement destins lutilisateur final, en particulier lesgestionnaires de bases de donnes et les tableurs. Il voque les lacunessur le plan mthodologique qui conduisent des rsultats peu fiables etdifficiles modifier. Les objectifs et la structure de cet ouvrage sedduisent de cette analyse.

1.1 LUTILISATEUR-DVELOPPEUR, HEURS

Ds leur apparition sur ordinateurs personnels, la fin des annes 70, les systmesde gestion de bases de donnes et les tableurs ont t perus comme des outils dedveloppement accessibles lutilisateur final, celui-l mme qui devait jusqualorssen remettre au service informatique pour obtenir le moindre traitement dinforma-tion. Lutilisateur pouvait dsormais se librer et devenir son propre dveloppeur. Ildevait se prparer ce nouveau mtier. Il sy prpara. On enseigna donc dBase IIpuis dBase III, VisiCalc puis Lotus 1-2-3.

Lavenir de linformatique sannonait exaltant. Dun ct une informatiquetraditionnelle, lourde, aux mains des professionnels, base sur les mainframes1, enmarge de lvolution naturelle, manifestement destine disparatre terme, enentranant avec elle ses informaticiens. De lautre, le foisonnement croissant et

1. On dsignait (et on dsigne encore) sous ce terme un gros ordinateur central traditionnellementdestin linformatique lourde, scientifique ou plus gnralement de gestion et auquel sontconnects des terminaux.

18 Chapitre 1 Motivation et introduction

multicolore dordinateurs personnels conviviaux, bon march, puissants, transfor-mant le moindre utilisateur, le moindre amateur en dveloppeur passionn et effi-cace. Celui-ci ne vient-il pas de construire, partant de zro, sa premire base dedonnes en vingt minutes, son premier modle de calcul (une vraie comptabilit enminiature) en une heure, son premier programme dinterrogation dBase en deuxheures? Et ce pour un cot drisoire. Linformaticien de mtier navait qu bien setenir, lui qui rclamait plusieurs semaines pour livrer le mme rsultat, dailleurs auprix fort.

1.2 ET MALHEURS

Linformaticien est toujours l. Son mtier a chang, sa position dans lentreprise avolu, de mme que ses rapports avec les utilisateurs. Les bouleversements techno-logiques que nous avons voqus y sont pour beaucoup. Bien sr, il dveloppeaujourdhui autant en Access, Visual Basic et en Excel quen COBOL (ou C ou Java)et SQL, mais son rle de dveloppeur ne lui a toujours pas t retir. Lutilisateurnen a plus voulu, les raisins taient sans doute encore un peu verts.

Cette histoire en rappelle dautres :

COBOL devait permettre aux comptables eux-mmes de dcrire les informa-tions et les traitements relatifs leur mtier, pour pallier le manque deprogrammeurs (1958);

BASIC, par sa simplicit, tait destin lutilisateur final (Beginners All-purpose Symbolic Instruction Code), enfin libr du joug du programmeur(1970);

les systmes experts devaient tre construits par les experts eux-mmes, lachose tait vidente; quy a-t-il de plus simple en effet quexprimer sesconnaissances et ses raisonnements sous la forme de rgles de production(annes 80)?

le modle relationnel devait rvolutionner le domaine des bases de donnes : lastructure tabulaire des donnes et un langage dclaratif semi-naturel devaientmettre cette technologie la porte des utilisateurs (annes 80); la constructionde la base de donnes elle-mme devait se simplifier fortement : lutilisateurdcrit ses tables et lordinateur se charge de limplantation efficace et de lopti-misation des requtes.

Loin de nous lide que ces outils originaux que sont les tableurs et les systmes debases de donnes ne sont pas aussi la porte des utilisateurs. Nous voulons simple-ment rappeler que la construction dune application informatique, quelle quen soitla complexit et quel quen soit loutil de ralisation, est une affaire srieuse, quelleexige rigueur, soin et mthode, afin que cette application constitue pour ses utilisa-teurs un outil fiable et efficace. Lutilisateur peut devenir, sans douleur mais pas sansefforts, un dveloppeur comptent, pourvu quil se soumette certaines exigencesdu mtier, qui vont le plus souvent bien au-del de la simple matrise de loutil infor-matique, dailleurs lui-mme de moins en moins facile matriser.

1.2 et malheurs 19

Dun

od

La

phot

ocop

ie n

on a

utor

ise

est

un

dlit

.

Les professionnels du dveloppement informatique sont confortablement outillspour rpondre ces exigences. Ils ont reu un enseignement technique et thoriquesolide, le plus souvent accompagn dune exprience pratique de dpart, expriencequils enrichissent rapidement sur le terrain. Ils disposent de mthodes de modlisa-tion et de construction dapplications qui les guident dans leurs activits de produc-tion. Ils utilisent des environnements de dveloppement puissants, tels lesdictionnaires de donnes, les gnrateurs dapplication, les environnements de dve-loppement rapide (RAD), les librairies de composants ou les ateliers de gnie logi-ciel.

Si ltat de lart en matire doutils de dveloppement pour lutilisateur, enloccurrence les tableurs et les systmes de bases de donnes, volue trs rapide-ment, on ne peut en dire autant des aspects mthodologiques. Lutilisateur-dve-loppeur, qui nest pas un professionnel de linformatique, et qui le plus souvent nepeut justifier dune formation pralable solide, est confront des logiciels de plusen plus puissants, de plus en plus difficiles matriser, et surtout aborde desproblmes de plus en plus complexes. Au contraire du dveloppeur professionnel, ilne dispose ni de modles, ni de mthodes, ni a fortiori doutils daide au dveloppe-ment. La situation est donc proche de celle du programmeur du dbut des annes 60,peu peu conscient que la matrise de FORTRAN ou de COBOL ne suffit plus laconstruction systmatique de programmes fiables et maintenables.

Les ouvrages largement disponibles chez les libraires, voire dans les grandessurfaces, contribuent masquer le problme. Destins lutilisateur, ils prsententune vision idalise du dveloppement dapplications qui ne correspond pas laralit. Combien de titres ne comportent-ils pas les mots simple, facile, pour les nulsou en un week-end? Non, Excel nest ni simple ni facile ds quon attaque desproblmes complexes. Comment pourrait-il en tre autrement dun logiciel dont ladocumentation de base, notoirement insuffisante1, occupe plus de 1 800 pages?Non, SQL nest plus un langage naturel et sr, ds quon sloigne des requteslmentaires. Il ne lest pas plus que la logique mathmatique, dont il nest quuneexpression lisible certes, mais maladroite, incomplte, irrgulire voire contradic-toire (voir la section 6.10 par exemple).

Il existe peu de rsultats publis concernant la qualit des applications ralisespar des utilisateurs, tant en bases de donnes que sur tableurs. Nous citerons deuxrfrences qui abordent le problme. [Ronen, 1989] attire lattention sur le problmede la qualit des modles de calcul, propose une classification des erreurs et suggreune dmarche de construction systmatique et raisonne. [Brown, 1987] va plus loinet fait tat dune analyse quantitative des types derreurs. Les rsultats sont rienmoins quinquitants. Nous les rsumons2.

Neuf utilisateurs de tableurs chevronns (1 5 annes dutilisation, 2,7 enmoyenne) ont t soumis des tests consistant raliser trois modles simples en

1. Do la prolifration douvrages de complment, souvent indispensables, tels que [Kyd, 1992]ou [Blattner, 1999].2. Voir aussi [Teo, 1999].

20 Chapitre 1 Motivation et introduction

1-2-3 de Lotus, partir dnoncs dune demi-page chacun. Tous les participantssauf un avaient une exprience en programmation classique. Lexigence portait surla qualit et la prcision de la solution, et non sur la rapidit de ralisation. Chaqueproblme a t rsolu en moyenne en 41 minutes, les participants ont dclar avoirconfiance en leurs solutions (degr de confiance moyen de 4 sur une chelle de 1 5). Selon leur avis, les problmes poss taient plus simples que ceux quils avaientgnralement rsoudre. Les rsultats sont les suivants :

44 % des modles produisent des rsultats faux (12 modles sur 27), 63 % des modles comportent des omissions ou produisent des rsultats faux

(17 modles sur 27), sans compter les erreurs indirectes (induites par dautres erreurs), 17 erreurs

ont t dtectes; 12 sont des erreurs de formules, 1 est directement visible lcran,

il ny a pas de corrlation entre la qualit des modles et le degr de confianceannonc par les participants,

le nombre derreurs est fonction directe de la complexit du problme : les9 solutions du problme le plus complexe comportaient des erreurs, alors quece nombre est de 3 pour le plus simple.

Les auteurs ajoutent que dautres tudes1 font tat dun taux de 20 40 % demodles oprationnels contenant des erreurs.

Les cours et les formations que nous avons organiss nous ont galement donnquelques indices sur les performances des apprenants. La population est celle destudiants de deuxime anne en Sciences conomiques et Politiques et en Informa-tique, auxquels sajoutent les tudiants de troisime en Informatique. Les observa-tions sont les suivantes :

Formulation de requtes SQL de complexit moyenne (niveau : types 3, 4 et 5selon le chapitre 4 de cet ouvrage) :

55 % des requtes sont correctes, 15 % des requtes comportent des erreurs syntaxiques (erreurs dtectables), 25 % des requtes comportent des erreurs de logique qui produiront des

rponses fausses mais plausibles (erreurs difficilement dtectables), 10 % des requtes comportent des erreurs de logique qui produiront des

rponses aberrantes (erreurs gnralement dtectables).

Construction dun petit schma de base de donnes SQL partir dun nonc enfranais :

50 % des schmas sont corrects, 20 % des schmas contiennent des erreurs conceptuelles graves, 30 % des schmas contiennent des erreurs conceptuelles lgres,

1. En particulier : Creet, R., Micro-computer spreadsheets : their uses and abuses, J. Account,Juin 1985.

1.3 Objectif de louvrage 21

Dun

od

La

phot

ocop

ie n

on a

utor

ise

est

un

dlit

.

15 % comportent des erreurs de traduction du schma conceptuel en structuresSQL.

Modles de calcul, rsolution des tudes de cas (chapitre 18, formulation desmodles abstraits uniquement, erreurs syntaxiques ignores) :

40 % des modles sont corrects, 20 % des modles contiennent des erreurs de structure (erreurs dindices par

exemple), 30 % des modles comportent des erreurs de logique qui produiront des

rponses incorrectes mais plausibles, et donc difficiles dtecter, 20 % des modles comportent des erreurs de logique qui produiront des

rponses aberrantes.

Les tudes et les expriences confirment ce qui est une vidence pourlinformaticien : la difficult principale ne rside pas dans la matrise de loutil, maisdans llaboration des principes de lapplication dvelopper. Les problmes tech-niques tant dsormais relativement bien matriss, reste alors la tche la plus ardue,lanalyse du problme que lutilisateur veut rsoudre et la construction dune solu-tion logique correcte et facile modifier.

Cest dans ce domaine que ce livre voudrait apporter une contribution.

1.3 OBJECTIF DE LOUVRAGE

Cet ouvrage, sil pourra intresser linformaticien de mtier, est cependant aussidestin lutilisateur final. Il tente de combler ce qui nous semble constituer un videsur le plan mthodologique : labsence de concepts et de mthodes qui soient laporte de lutilisateur et qui permettent lanalyse rigoureuse des problmes et laconstruction de solutions qui seront immdiatement prises en charge par les gestion-naires de bases de donnes et par les tableurs.

En clair, louvrage se propose daider lutilisateur mieux matriser le dveloppe-ment dapplications laide de ces outils privilgis de rsolution de problmes. Ilest organis en deux parties. La premire est consacre aux bases de donnes et leurs gestionnaires et la deuxime aux modles de calcul et aux tableurs. Une troi-sime partie est dveloppe dans [Hainaut, 1994] et est dsormais disponible sur lesite Web de louvrage. Elle aborde le couplage des modles de calcul et des bases dedonnes, ainsi que les technologies qui permettent ce couplage.

Ces parties ont une structure identique, illustre par le schma 1.1 :

description des concepts fondamentaux (des bases de donnes et des modlesde calcul),

description doutils reprsentatifs (bases de donnes relationnelles et SQL,EXCEL),

description dune mthode de conception dapplications adapte lutilisa-teur final (conception de bases de donnes, conception de modles de calcul),

22 Chapitre 1 Motivation et introduction

rsolution de deux tudes de cas. Ces deux cas sont identiques pour les deuxparties.

Des exercices dapplication clturent les chapitres les plus importants de louvrage.

Figure 1.1 - Organisation de louvrage

Le contenu de ce livre tant intrinsquement de nature conceptuelle, il ne prtend sesubstituer ni aux manuels qui accompagnent les logiciels, ni aux nombreuxouvrages techniques de complment disponibles en librairie. En particulier, ladescription des outils inclut ce qui nous semble ncessaire et suffisant pour aborderle problme de la conception dune base de donnes et dune feuille de calcul.

Chapitres 8, 9, 10, 11

Chapitres16, 17, 18, 19

Chapitre 15

Chapitre 3

Chapitres 4, 5, 6, 7

Chapitre 12

Chapitre 14

Chapitre 20

Bases de donnes Modles de calcul

Concepts

Outils

Mthodes

Etudes de cas

P

ARTIE

1

LES BASES DE DONNES

C

hapitre

2

2

Introduction

Ce chapitre prsente, notamment dans une perspective historique, leconcept de base de donnes la fois comme un service de gestion dedonnes destin aux utilisateurs et comme une ressource, dont llabo-ration requiert des mthodes adquates. Il dcrit la structure de lapremire partie de louvrage.

2.1 LUTILISATEUR ET LES DONNES

Les donnes permanentes constituent certainement le matriau de base partirduquel vont slaborer la plupart des applications informatiques. tel pointdailleurs que certaines de celles-ci se rduisent grer et consulter des donnes :rpertoire dadresses, rfrences bibliographiques ou catalogue de pices derechange. Ces donnes sont ranges dans ce quon appelle un fichier et sont structu-res en enregistrements. Chaque correspondant du rpertoire dadresses y est dcritpar un enregistrement qui reprend son nom et son prnom, ainsi que ses coordon-nes postales et tlphoniques. De tels fichiers sont grs par des logiciels simples etintuitifs et ne posent gure de problmes leurs utilisateurs.

Les applications plus complexes rclament des donnes dont la structure est elleaussi plus complexe. Les donnes sont classes dans plusieurs fichiers en fonctiondes objets quelles dcrivent : fichier des clients, fichier des produits, fichier descommandes, fichier des factures, etc. Il existe entre les fichiers des liens qui sont limage des relations entre les objets dcrits. Cest via ces liens entre les donnesquon indique les commandes qui ont t mises par tel client, ou les produits quisont rfrencs par telle commande. Il apparat aussi que les donnes ncessaires

26 Chapitre 2

Introduction

une application pourraient tre utiles dautres applications, voire mme dautresutilisateurs. Ces donnes constituent alors ce quon appelle une

base

de

donnes

.Grer de telles donnes nest plus la porte de logiciels lmentaires. Garantir laqualit des donnes enregistres (retrouve-t-on ce quon a enregistr?), leur coh-rence (le client de chaque commande est-il rpertori?), les protger en cas dinci-dent, permettre plusieurs utilisateurs dy accder simultanment, tout en contrlantstrictement laccs aux donnes confidentielles, offrir de bonnes performancesdaccs toutes les applications, en particulier celles qui sont interactives, sont desfonctions qui rclament des logiciels puissants et complexes, les

systmes

degestion

de

bases

de

donnes

, ou

SGBD

. Ce type de logiciels constitue lun des outils fondamentaux de dveloppement des

grosses applications informatiques, mais aussi dapplications plus lgres ou deserveurs Web. Ils existent sur toutes les plates-formes, depuis les gros ordinateurs(mainframes ou serveurs de grande puissance) jusquau PC portable ou au PDA,voire au tlphone mobile.

2.2 BASES DE DONNES ET SGBD RELATIONNELS

a) Principes

Une

base

de

donnes

relationnelle

apparat comme une collection de tables dedonnes, ou fichiers

plats

. Il sagit dune structure extrmement simple et intuitivequi, pour lutilisateur du moins, ne sencombre daucun dtail technique concernantles mcanismes de stockage sur disque et daccs aux donnes. La figure 3.6 montreune petite base de donnes relationnelle constitue des tables CLIENT,COMMANDE, DETAIL et PRODUIT. Ce schma, qui est suffisant pour dcrire lesdonnes et leur structure, ne dit rien des techniques dimplantation de ces donnessur disque par exemple.

Toutes les manipulations seffectuent au moyen dun unique langage, SQL(

Structured Query Language

). Ce langage permet lutilisateur de demander auSGBD de crer des tables, de leur ajouter des colonnes, dy ranger des donnes et deles modifier, de consulter les donnes, de dfinir les autorisations daccs. Lesinstructions de consultation des donnes sont essentiellement de nature dclarativeet non procdurale

1

. On y dcrit les proprits des donnes quon recherche, notam-ment en spcifiant une condition de slection, mais on nindique pas le moyen de lesobtenir, dcision qui est laisse linitiative du SGBD.

Le langage SQL semble donc destin tant au dveloppeur informaticien qulutilisateur final. Il est vrai que des requtes simples sexpriment simplement.

1. On qualifie de

procdurale

une suite dinstructions indiquant les oprations excuter pourobtenir un certain rsultat. Si les instructions se contentent de dcrire les caractristiques dursultat recherch, alors elles sont de type

dclaratif

. Linstruction

select

dSQL est de nature

dclarative

dans la mesure o lutilisateur y dcrit les donnes recherches, mais ne peut spcifierla procdure permettant de les obtenir. Cest dailleurs le rle du SGBD que de construire cetteprocdure et de lexcuter.

2.2

Bases de donnes et SGBD relationnels

27

D

unod

L

a ph

otoc

opie

non

aut

oris

e e

st u

n d

lit.

Rechercher le nom et ladresse de tous les clients de Toulouse sexprime par larequte suivante, qui, applique la table CLIENT comportant les colonnes NOM,ADRESSE et LOCALITE, ne rclame gure deffort dinterprtation :

select NOM,ADRESSE from CLIENT where LOCALITE = 'Toulouse'

Bien sr, lextraction de donnes issues de plusieurs tables, et satisfaisant desconditions complexes, sexprimera par des requtes galement plus complexes, quincessitent de la part de lutilisateur qui les formule un apprentissage adquat. Onimagine aisment en effet que si on dsire obtenir la liste des localits, accompagnesdes quantits totales commandes par produit, et ordonnes par nombre dcroissantde clients dont le compte est ngatif, il nous faudra rdiger une requte beaucoupplus labore que celle qui vient dtre propose.

Le march offre aujourdhui nombre doutils qui permettent daccder une basede donnes relationnelle sans quil soit ncessaire de matriser le langage SQL;citons notamment MS Access et FileMaker Pro. Ces modes daccs sont cependantlimits des requtes simples.

b) De lalgbre lindustrie : un peu dhistoire

Les bases de donnes relationnelles trouvent leur origine dans des travaux thori-ques sur les structures de donnes des annes 60. La premire prsentation officiellede lapproche relationnelle est incontestablement larticle de E.F. Codd, chercheurdIBM, paru en 1970 [Codd, 1970]. Lauteur y proposait une structure de donnestabulaire minimale, indpendante des technologies de mise en uvre, accompagnedoprateurs dextraction de donnes. Ce modle de donnes est par essencemathmatique : les ensembles de valeurs sont des domaines, les tables correspon-dent des relations sur les domaines et les oprateurs sont des extensions de ceux delalgbre des relations.

Il restait rendre ces propositions oprationnelles. Cest ainsi que sont ns lespremiers SGBD prototypes : System

R dIBM et INGRES lUniversit deBerkeley entre autres. Sont galement apparus les premiers langages de requtedrivs des langages mathmatiques dorigine, mais accessibles aux praticiens :SEQUEL, qui deviendra SQL, pour le System R [Chamberlin, 1974] et QUEL pourINGRES

2

[Date, 1990]. Les premiers SGBD relationnels commerciaux sont apparusrapidement. Ds 1979, ORACLE (alors Relational Software), puis SQL/DS chezIBM (1981) ont t introduits sur le march, ainsi quune version industrielledINGRES. Dautres ont suivi, tels INFORMIX, DB2, UNIFY, RDB, SYBASE etSQL-Server. A lheure actuelle, les SGBD relationnels, et leurs extensions rela-tionnel-objet, ont investi tous les types de machines, au point quil faut dsormais les

2. Bien que QUEL ait t gnralement jug suprieur SQL, ce dernier la rapidementsupplant sur le plan commercial.

28 Chapitre 2

Introduction

considrer comme formant la famille principale de SGBD, renvoyant les autres versdes niches confidentielles. Cette situation dcoule sans doute des qualits intrins-ques du modle relationnel et du langage SQL, mais aussi, plus prosaquement, dunphnomne irrsistible de standardisation. Les arguments sont imparables : portabi-lit des applications, indpendance par rapport au fournisseur du SGBD, stabilit dela formation, mobilit et interchangeabilit des dveloppeurs, disponibilit doutilsannexes, pour nen citer que quelques-uns. SQL fait dailleurs lobjet defforts cons-tants de normalisation au niveau de lANSI, mais aussi de lISO [ANSI, 1989], dontil est le membre le plus actif. Nous verrons cependant que le concept de norme estinterprt avec beaucoup de sens potique par les diteurs de SGBD. Les extensionsvont actuellement (via la norme SQL:1999) vers une intgration avec le Web, unenrichissement des structures de donnes, le couplage avec le langage Java et XML,lintgration de fonctions de gestion de donnes multimdia et de donnes spatiales,ainsi que des fonctions de fouille de donnes ( data mining ).

2.3 CONSTRUCTION DUNE BASE DE DONNES

Linterrogation, voire la gestion, dune base de donnes sont donc dsormais laporte de lutilisateur averti et motiv. Quen est-il de la construction elle-mme dela base de donnes? Cette activit est traditionnellement le domaine rserv desinformaticiens. Ils disposent en effet non seulement de modles et de mthodesspcifiques qui leur permettent de dfinir progressivement les structures de la basede donnes, mais ils utilisent aussi des outils qui les aident dans cette activit : lesateliers logiciels. Le lecteur en contact avec des informaticiens aura sans douteentendu parler du modle Entit-association, de la mthode MERISE, des notationsUML ou des ateliers dingnierie logicielle AMC-Designor, Power-Designer, Rose,Designer-2000 ou MEGA, pour nen citer que quelques-uns.

Toutes les mthodes de conception sont bases sur la mme ide : dcouplerlanalyse du problme de limplantation de la solution dans une machine. Lanalysedu problme conduit au schma conceptuel de la base de donnes, qui est une solu-tion abstraite, cest--dire indpendante de la technologie, et qui sexprime le plussouvent sous une forme graphique du modle Entit-association

3

. La seconde phase,limplantation, consiste traduire le schma conceptuel en une structure de tables eten instructions SQL de cration de ces tables. Le lecteur press comparera lafigure 12.1, qui dcrit de manire abstraite la gestion dun parc zoologique, avec letexte SQL qui dfinit les tables devant contenir les donnes relatives cette gestion(section 12.2.4).

Sil est hors de question, dans un tel ouvrage, de tenter de former lutilisateur ces mthodes et ces outils, il est cependant possible den retirer un ensemble de

3. Appel galement

Entit-Relation

, ou

Individuel

ou

Entity-Relationship

. On utilise aussi leterme de modle de classes (UML), emprunt aux approches orientes-objets, mais qui recouvreun concept similaire.

2.4

Description de la premire partie

29

D

unod

L

a ph

otoc

opie

non

aut

oris

e e

st u

n d

lit.

concepts et de principes qui lui seront prcieux non seulement dans ses contactsavec les informaticiens, mais aussi pour laider mener bien ses propres dvelop-pements. Cest la raison pour laquelle nous proposerons une version simplifie, maisoprationnelle, du modle Entit-association, ainsi quune dmarche simple et intui-tive de construction dune base de donnes.

Un peu dhistoire

Dans les annes 70, il tait de rgle de considrer que les modles de donnes offertspar les SGBD constituaient un mode dexpression adquat lors de la phase danalysedu problme. Cest ainsi quon a assist, durant toute cette dcennie, une grandeconcurrence entre le modle rseau4, le modle hirarchique5 et le modle rela-tionnel, chacun tant propos par ses supporters comme le formalisme conceptuelidal. Il est cependant rapidement apparu que tant les modles de SGBD (incompletset trop techniques) que le modle relationnel (trop pauvre) taient inadquatscomme support de raisonnement abstrait. Sont alors apparus des formalismes dedescription de structures de donnes la fois plus abstraits que les modles desSGBD et plus riches que le modle relationnel : les modles conceptuels.

Malgr quelques tentatives dextension du modle relationnel [Codd, 1979], lemodle Entit-association sest rapidement impos comme modle conceptuel.Dvelopp ds le dbut des annes 70, notamment en Europe [Deheneffe, 1974], etpopularis par [Chen, 1976], il propose une reprsentation explicite des entits dudomaine dapplication, de leurs associations et de leurs attributs. Son succs estaussi d lexistence dune reprsentation graphique des concepts du domainedapplication. La plupart des mthodes de conception de bases de donnes, et plusgnralement de systmes dinformation, telles que MERISE sur le march franais(et mme francophone), ont adopt ce modle. Des modles tels que NIAM, basssur des associations binaires entre objets dotes dune interprtation linguistique, ontaussi leurs adeptes, bien que leur perce ait t plus modeste face au modle Entit-Association. Le dernier avatar de ce dernier, le modle de classes dUML, bien quepouss par une vague commerciale sans prcdent, pose quelques problmes dont ondiscutera dans la section 9.10.

2.4 DESCRIPTION DE LA PREMIRE PARTIE

La premire partie de cet ouvrage est constitue de quatre blocs.

Les concepts : le chapitre 3 dcrit les concepts fondamentaux des bases dedonnes : tables, colonnes, identifiants et cls trangres.

Les outils : les chapitres 4 et 5 sont consacrs un expos des deux volets prin-cipaux du langage SQL : le DDL et le DML. Il suit une approche pdagogique

4. Principalement le modle CODASYL, inspir dIDS (Honeywell), qui a donn naissance denombreux SGBD, dont certains sont toujours en activit (IDMS, IDS2, UDS, MDBS).5. Principalement le modle IMS dIBM.

30 Chapitre 2 Introduction

qui va de lexpression de requtes simples vers celle de requtes complexes, enmettant laccent sur les difficults les plus frquentes. Le chapitre 6 dcrit destechniques SQL avances tandis que le chapitre 7 prsente quatre applicationspratiques particulirement utiles, mais un peu plus complexes.

Les mthodes de conception : les chapitres 8 11 dcrivent les principes de laconstruction systmatique et raisonne dune base de donnes : modle Entit-association (chapitre 9), laboration du schma conceptuel (chapitre 10),production du schma de la base de donnes (chapitre 11).

Des tudes de cas : deux problmes typiques sont discuts et rsolus commeillustration de la mthode de conception (chapitre 12).

2.5 POUR EN SAVOIR PLUS

Le lecteur la recherche dune introduction plus substantielle aux principes desbases de donnes se tournera vers des ouvrages tels que [Delobel, 1981], [Pichat,1990], [Date, 2001] ou [Bouzeghoub, 1998]. Les rfrences en anglais ne sont pas ngliger, telles que [Date, 1999], [Elmasri, 2000] ou [Connolly, 2002]. Desouvrages tels que [Bouzeghoub, 1997] et [Gardarin, 1999] proposent une descrip-tion des nouvelles tendances dans les SGBD.

Le langage SQL fait lobjet dinnombrables monographies de tous niveaux, etpour tous les SGBD. Bornons-nous citer [Delmal, 2001], et, en langue anglaise,[Date, 1997] et [Melton, 2002]. Le couplage des bases de donnes et XML est dve-lopp dans [Gardarin, 2003].

Dans le domaine mthodologique, on pourra recommander, parmi dautres,[Batini, 1992], [Blaha, 1998], [Ceri, 1997]. Les ouvrages en langue franaise sontgalement nombreux : citons seulement [Nancy, 1996] comme rfrence de base ence qui concerne la mthode MERISE. Le lecteur trouvera dans [Bodart, 1994],[Hainaut, 1986], [Bouzeghoub, 1990], [Rolland, 1991], [Akoka, 2001] quelquesapproches similaires ou alternatives. Les rfrences [Habrias, 1988] et[Halpin, 1995] prsentent les mthodes NIAM et ORM.

La lecture des articles qui sont lorigine des avances technologiques etmthodologiques majeures est toujours enrichissante. On recommandera en particu-lier [Codd, 1970] et [Chen, 1976] o le lecteur trouvera un expos clair et argumentde concepts considrs encore aujourdhui comme novateurs.

Chapitre 3 3

Concepts des bases de donnes

Ce chapitre dcrit les notions essentielles des bases de donnes. Lesdonnes se prsentent sous la forme de tables formes de lignes et decolonnes. Chaque ligne reprsente une entit ou un fait du domainedapplication, tandis quune colonne reprsente une proprit de cesentits ou faits. Une table contient donc des informations similaires surune population dentits ou de faits. Certaines colonnes ont pour butdidentifier les lignes (identifiants), dautres sont des rfrences versdautres lignes (colonnes de rfrence et contraintes rfrentielles). Onpropose une reprsentation graphique de la structure des tables ainsiquun exemple de base de donnes servant illustrer les conceptsexamins dans dautres chapitres. On tudiera aussi le phnomne deredondance interne, qui conduira au concept de table normalise.

3.1 TABLE, LIGNE ET COLONNE

Les donnes dune base de donnes sont organises sous la forme dune ou plusieurstables. Une table contient une collection de lignes stockes sur un support externe,gnralement un disque. Une ligne est elle-mme une suite de (une ou) plusieursvaleurs, chacune tant dun type dtermin. Dune manire gnrale, une ligneregroupe des informations concernant un objet, un individu, un vnement, etc.,cest--dire un concept du monde rel (externe linformatique), que nous appelle-rons parfois une entit ou un fait.

32 Chapitre 4 Concepts des bases de donnes

La figure 3.1 reprsente une table comportant huit lignes, dcrivant chacune unclient. On y trouve quatre valeurs reprsentant respectivement le NOM etlADRESSE du client, la LOCALITE o il rside ainsi que ltat de son COMPTE.Lune de ces lignes reprsente le fait suivant :

Il existe un client de nom AVRON, rsidant 8, chausse de la Cure Toulouse,et dont le compte est dbiteur dun montant de 1 7001.

Toutes les lignes dune table ont mme format ou structure. Cette proprit signifieque dans la table CLIENT, toutes les lignes sont constitues dune valeur de NOM,dune valeur dADRESSE, dune valeur de LOCALITE ainsi que dune valeur deCOMPTE. Lordre des lignes est indiffrent2.

Figure 3.1 - Structure et contenu de la table CLIENT

Lensemble des valeurs de mme type correspondant une mme proprit desentits dcrites sappelle une colonne de la table. On parlera de la colonne NOM oude la colonne COMPTE de la table CLIENT. HANSENNE est la valeur de la colonneNOM de la premire ligne. On dfinira une colonne par son nom, le type de sesvaleurs et leur longueur. Les valeurs de la colonne NOM sont constitues de 1 32 caractres et celles de la colonne COMPTE de 9 positions numriques, dont2 aprs la virgule dcimale (ou point dcimal pour un logiciel anglo-saxon).

Il est possible dajouter des lignes une table et den supprimer. Il est possible gale-ment de modifier la valeur dune colonne dune ligne, ou plus gnralement dun sous-ensemble des lignes. On pourrait par exemple, titre de bonus, ajouter 5 % la valeurde COMPTE des lignes dont COMPTE > 500.

Les trois tables dcrites la figure 3.2 constituent une petite base de donnes. Lapremire table, de nom FOURNISSEUR, dcrit des fournisseurs, dont elle spcifie lenumro (NUMF), le nom (NOMF) et la ville de rsidence (VILLEF). La deuxime

1. Dans cet ouvrage, nous laisserons lunit montaire indtermine.2. On ne peut donc en principe parler de la premire, de la troisime ou de la dernire ligne dunetable. Pour tre plus prcis, on dira que le contenu dune table un instant dtermin est dfinicomme un ensemble de lignes au sens mathmatique du terme. Les lments dun ensemble sontnon ordonns et distincts. Cependant, afin dillustrer certains concepts laide des figures dutexte, il nous arrivera de parler, par exemple, de la premire ligne dune table.

3.2 Rles dune colonne 33

Dun

od

La

phot

ocop

ie n

on a

utor

ise

est

un

dlit

.

table, nomme PIECE, dcrit des pices de rechange, caractrises par leur numro(NUMP) et leur type (TYPE). La troisime table, de nom OFFRE, reprsente lesoffres des fournisseurs pour les pices quils peuvent livrer. Ces offres sont caract-rises par le numro du fournisseur (NUMFL) pouvant effectuer la livraison, lenumro de la pice livrable (NUMPL) et le prix (PRIX) auquel ce fournisseurpropose cette pice.

Figure 3.2 - Un exemple de base de donnes

3.2 RLES DUNE COLONNE

On admet quune ligne regroupe des informations sur une entit ou un fait du monderel. Dans cette optique, la valeur dune colonne reprsente une proprit de cetteentit. Cependant, toutes les colonnes ne jouent pas le mme rle vis--vis desentits reprsentes par les lignes dune table.

Une analyse plus prcise de lexemple de la figure 3.2 permet de dceler troistypes de colonnes dans cette base de donnes, selon le rle quelles y jouent.

3.2.1 Les identifiants

Ce premier type de colonne permet didentifier une entit, et donc aussi la ligne quila reprsente dans la table. Tel est le cas de NUMF pour les lignes de FOURNIS-SEUR et NUMP pour celles de PIECE. Une telle colonne est appele lidentifiant dela table3. Dclarer que NUMF est lidentifiant de FOURNISSEUR, cest imposerqu tout instant les lignes de cette table aient des valeurs distinctes de NUMF.

3. La terminologie standard des bases de donnes relationnelles propose le terme de cl ou keypour dsigner ce concept. Etant donn le grand nombre dacceptions de ce terme dans ledomaine des bases de donnes, nous lui prfrerons celui didentifiant.

34 Chapitre 4 Concepts des bases de donnes

3.2.2 Les cls trangres

Une colonne du deuxime type est une copie de lidentifiant dune autre table.Chacune de ses valeurs joue le rle dune rfrence une ligne de cette table. Onlappellera colonne de rfrence, ou selon la terminologie standard, cl trangre(foreign key). La table OFFRE contient deux cls trangres : NUMFL, qui constitueune rfrence une entit fournisseur (et donc aussi une ligne de FOURNIS-SEUR), et NUMPL, qui est une rfrence une pice (et aussi une ligne de PIECE).Cest par des cls trangres quon peut mettre en relation des lignes dans des tablesdistinctes. Pour chaque offre, reprsente par une ligne de OFFRE, il est possible deconnatre les informations concernant le fournisseur (via NUMFL) et celles quiconcernent la pice (via NUMPL).

On notera que le nom dune colonne formant une cl trangre est indpendant decelui de lidentifiant quelle rfrence. Il arrivera souvent quon donne une cltrangre le nom de lidentifiant cible (NUMP dans OFFRE), mais on pourrait aussilui donner celui de la table rfrence (PIECE), ou encore celui du rle que jouentles lignes rfrences (PIECE_OFFERTE)

3.2.3 Les informations complmentaires

Le troisime type de colonne ne joue dautre rle que celui dapporter une informa-tion complmentaire sur lentit. Ainsi en est-il de NOMF, VILLEF, TYPE et PRIX.

A partir de ces lments de base, nous mettrons encore en vidence quatre conceptsimportants : les identifiants et cls trangres multicomposants, les identifiantsprimaires, les contraintes rfrentielles et les colonnes facultatives.

3.2.4 Les identifiants et cls trangres multicomposants

Rien ninterdit que lidentifiant dune table soit constitu de plus dune colonne. Onpourrait ainsi imposer que les colonnes (NUMFL, NUMPL) forment lidentifiant dela table OFFRE. Cette proprit revient dire quon nenregistre quune seule offrepar fournisseur pour une pice dtermine, ou encore quune pice ne peut fairelobjet que dune seule offre de la part dun fournisseur dtermin.

Par voie de consquence, une cl trangre qui rfrence une table ayant un iden-tifiant primaire multicomposant est elle-mme multicomposant.

3.2.5 Les identifiants primaires

Rien ninterdit non plus quon impose plus dun identifiant une table. Par exemple,une table qui reprend les informations signaltiques dune population dote dunecouverture sociale pourrait inclure, entre autres, une colonne NUMERO-INSCRIP-TION et une colonne NUMERO-CARTE-IDENTITE, chacune constituant un identi-fiant. Parmi les identifiants dune table, lun est choisi comme le plus reprsentatif.Il sera dclar identifiant primaire4, les autres tant les identifiants secondaires5 de

3.2 Rles dune colonne 35

Dun

od

La

phot

ocop

ie n

on a

utor

ise

est

un

dlit

.

la table. Toute table possde un identifiant primaire et un nombre quelconque (ven-tuellement nul) didentifiants secondaires6. Ceci a pour consquence importante queles lignes dune table sont distinctes, en accord avec la dfinition du contenu dunetable comme un ensemble de lignes.

Remarque sur les identifiants. Tout ensemble de colonnes qui comprend un iden-tifiant est encore un identifiant : (NUMF, NOMF) est un identifiant de FOURNIS-SEUR. Un identifiant dont on ne peut retirer aucun composant sans quil perde saqualit didentifiant est appel identifiant minimal. Il est vident quon cherchera ne dfinir que des identifiants minimaux.

Remarque sur les cls trangres. Bien que, thoriquement, la cible dune cltrangre soit lun des identifiants de la table rfrence, on convient de se limiter lidentifiant primaire.

3.2.6 Les contraintes rfrentielles

Une valeur dune cl trangre constitue de la colonne RA dune table B (ou, tantdonn lexistence didentifiants multicomposants, constitue dun groupe decolonnes de rfrence) est destine dsigner une ligne dune table A. Concr-tement, on peut imposer que pour toute valeur de RA dans B, il y ait une ligne de latable A identifie par cette valeur. On en dduit une proprit trs importante,dnomme contrainte rfrentielle. Celle-ci stipule que lensemble des valeursdune cl trangre est tout instant une partie de lensemble des valeurs de liden-tifiant primaire de la table rfrence. On imposerait par exemple que toute valeur deNUMPL dans OFFRE soit prsente dans la colonne NUMP de PIECE (ce quonnotera OFFRE.NUMPL PIECE.NUMFL), et que toute valeur de NUMFL dansOFFRE se retrouve comme valeur NUMF de la table FOURNISSEUR. Il est doncinterdit dintroduire dans la table OFFRE une ligne telle que (NUMFL:93, NUMPL:57,PRIX:32), puisquil nexiste dans FOURNISSEUR aucune ligne dont lidentifiantprimaire NUMF ait la valeur 93.

3.2.7 Les colonnes facultatives

Il se peut quune information ne soit pas connue au moment o on introduit ladescription dune entit ou dun fait dans une table. Par exemple, on apprend que lefournisseur 259 offre dsormais des pices 15, mais on nen connat pas encore leprix. On admet alors dintroduire dans la table OFFRE la ligne incomplte suivante :

4. Selon la terminologie standard, cl primaire ou primary key.5. Le terme de secondaire nest pas standard. La littrature utilisera le terme de cls candidatespour dsigner tous les identifiants dune table. Un identifiant secondaire est donc une cl candi-date non primaire.6. En conformit avec les recommandations nonces dans [Codd, 1990] et [Date, 1992], on sentiendra des structures de tables avec identifiants. Signalons cependant que le langage SQL, quenous tudierons dans la suite, permet de dfinir des tables sans identifiants, possibilit que nousignorerons ici.

36 Chapitre 4 Concepts des bases de donnes

(NUMFL:259, NUMPL:15, PRIX:-). On dira que la valeur de PRIX de cette ligne estinconnue. Cette latitude donne une colonne daccepter labsence de valeur pourcertaines lignes nest pas valable pour toute colonne. Par exemple, on peut dciderque le nom dun fournisseur est indispensable, et donc que toute ligne de la tableFOURNISSEUR doit avoir une valeur de NOMF valable. La colonne PRIX seradclare facultative, tandis que la colonne NOMF sera obligatoire. En fait, unevaleur absente sera reprsente par la valeur conventionnelle null, dont nous repar-lerons dans la section 6.10.

On impose que les composants de tout identifiant primaire soient obligatoires. Ence qui concerne les colonnes de rfrence, le concept de colonne facultativedemande que nous affinions la dfinition de la contrainte rfrentielle :

Si un groupe de colonnes de la table B est dclar cl trangre vers la table A, cescolonnes sont soit obligatoires, soit facultatives; dans ce dernier cas, elle sontcoexistantes, cest--dire qu tout instant, pour toute ligne de la table B,

soit des valeurs existent pour chacune de ces colonnes et dans ce cas ellesdoivent identifier une ligne existante de A,

soit il ny a de valeurs pour aucune de ces colonnes.

On nadmet donc pas quil existe des lignes pour lesquelles certains composantsdune cl trangre possdent une valeur tandis que les autres nen ont pas7.

3.3 STRUCTURE ET CONTENU DUNE BASE DE DONNES

On distingue deux parties distinctes dans une base de donnes : son schma et soncontenu. Le schma dune base de donnes en dfinit la structure en termes detables, de colonnes (avec le type de valeurs et le caractre obligatoire ou facultatif dechacune), didentifiants primaires et secondaires, et de cls trangres. Son contenu un instant dtermin est lensemble des lignes. En supprimant les lignes de lafigure 3.2, nous obtenons le schma (encore partiel ce stade) reprsent lafigure 3.3.

Figure 3.3 - Structure de la base de donnes de la figure 3.2

7. Il faut cependant noter que ce cas est gnralement accept par les SGBD. Nous lexcluonspour des raisons de simplicit et de scurit.

3.4 Reprsentation graphique dun schma 37

Dun

od

La

phot

ocop

ie n

on a

utor

ise

est

un

dlit

.

Le contenu dune base de donnes relle est gnralement volumineux (plusieursmillions de lignes) et susceptible dvoluer constamment. Il est donc sans intrt dele reprsenter dans un expos comme celui-ci, sauf titre illustratif. En revanche, leschma comporte un nombre limit dlments (quelques dizaines quelquesmilliers de tables en gnral) prsentant une relative stabilit dans le temps : on nemodifie la structure dune base de donnes que lorsque la structure de la ralit reprsenter volue. Notre intrt se portera essentiellement sur la composition et lesproprits des schmas de bases de donnes.

3.4 REPRSENTATION GRAPHIQUE DUN SCHMA

Nous pouvons prsent prciser et complter les conventions graphiques de repr-sentation dun schma. Nous proposons un premier jeu de conventions (figure 3.4). Une table et ses colonnes8 sont reprsentes par un cartouche contenant le nom de

la table et celui de chaque colonne. Les colonnes sont places dans un ordre quel-conque. Cependant, afin de faciliter la comprhension, on veillera autant quepossible disposer de faon contigu les colonnes dun mme identifiant demme que celles de chaque cl trangre, et placer lextrme gauche lescolonnes de lidentifiant primaire.

On soulignera dun trait continu les noms des colonnes de lidentifiant primaire etdun trait pointill ceux de chaque identifiant secondaire.

Le nom dune colonne facultative sera entour de parenthses. Une cl trangre constitue dun groupe de colonnes de rfrence (cl trangre

multi-composant) sera reprsente par une accolade. Une contrainte rfrentielle sera reprsente par une flche qui part du nom de la

colonne de rfrence (ou de laccolade en cas de groupe de rfrence) et quipointe vers le cartouche de la table rfrence.

Figure 3.4 - Reprsentation des identifiants et des cls trangres

8. On ne prvoit pas de convention particulire de reprsentation des types de valeurs.

38 Chapitre 4 Concepts des bases de donnes

3.5 UN EXEMPLE DE BASE DE DONNES

La base de donnes qui nous servira dexemple dans cette section est structureselon le schma de la figure 3.5. On donnera une brve dfinition de la significationdes tables et des colonnes

Figure 3.5 - Schma de la base de donnes exemple

Table CLIENT : chaque ligne dcrit un client; les colonnes dcrivent successi-vement le numro du client (NCLI), son nom (NOM), son adresse (ADRESSE),sa localit (LOCALITE), sa catgorie (CAT) et ltat de son compte (COMPTE).Lidentifiant primaire est constitu de NCLI. La colonne CAT est facultative.

Table PRODUIT : chaque ligne dcrit un produit; les colonnes dcriventsuccessivement le numro du produit (NPRO), son libell (LIBELLE), son prixunitaire (PRIX) et la quantit restant en stock (QSTOCK). NPRO est lidenti-fiant primaire.

Table COMMANDE : chaque ligne dcrit une commande passe par un client;les colonnes dcrivent successivement le numro de la commande (NCOM), lenumro du client qui a pass la commande (NCLI) et la date de la commande(DATECOM). NCOM est lidentifiant primaire de la table. NCLI est une cltrangre vers la table CLIENT.

Table DETAIL : chaque ligne reprsente un dtail dune commande; lescolonnes dcrivent successivement le numro de la commande laquelle ledtail appartient (NCOM), le numro du produit command (NPRO) et laquantit commande (QCOM). Lidentifiant primaire est constitu de NCOM etNPRO. NCOM et NPRO sont en outre chacune une cl trangre respective-ment vers les tables COMMANDE et PRODUIT.

Le contenu de la base de donnes pourrait, un instant donn, se prsenter commeindiqu la figure 3.6.

3.5 Un exemple de base de donnes 39

Dun

od

La

phot

ocop

ie n

on a

utor

ise

est

un

dlit

.

Figure 3.6 - Contenu typique de la base de donnes exemple

40 Chapitre 4 Concepts des bases de donnes

3.6 AUTRES NOTATIONS GRAPHIQUES

La notation de reprsentation adopte dans ce chapitre est simple et naturelle. Ellene conviendra cependant pas pour dessiner des schmas plus volumineux et pluscomplexes. On pourrait adopter une disposition telle que celle dAccess de Microsoft (figure3.7), selon laquelle les noms des colonnes sont prsents en liste verticale. Lidenti-fiant primaire est indiqu en gras et les contraintes rfrentielles par un trait reliantla cl trangre (symbole ) lidentifiant primaire (symbole 1).

Figure 3.7 - Reprsentation graphique des schmas Microsoft Access

Ces symboles se lisent un--plusieurs, indiquant par l qu un lment du ct 1correspondent plusieurs () lments du ct , et qu un lment du ct correspond un (1) lment du ct 1.

Figure 3.8 - Reprsentation graphique plus complte (atelier DB-MAIN)

La notation dAccess ne permet cependant pas de reprsenter de manire graphiquecertaines constructions qui nous seront utiles9, ce qui nous amnera utiliser,lorsque cela sera ncessaire, une notation plus complte telle que celle qui est illus-

9. Les colonnes facultatives, les identifiants secondaires, les cls trangres cycliques, des identifiantset/ou cls trangres non disjoints, les contraintes dexistence, les structures objet-relationnelles, etc.

PRODUITNPROLIBELLEPRIXQSTOCKid: NPRO

DETAIL

NCOMNPROQCOMid: NCOM

NPROref: NCOMref: NPRO

COMMANDENCOMDATECOMNCLIid: NCOMref: NCLI

CLIENTNCLINOMADRESSE LOCALITECAT[0-1]COMPTEid: NCLI

3.7 Note sur les contraintes rfrentielles 41

Dun

od

La

phot

ocop

ie n

on a

utor

ise

est

un

dlit

.

tre dans la figure 3.8, et qui est propre latelier dingnierie DB-MAIN dont nousreparlerons plus loin. Les conventions de cette notation sont les suivantes :

une table est reprsente par une boite trois compartiments indiquant succes-sivement le nom de la table, le nom de ses colonnes et les contraintesdintgrit;

une colonne facultative est caractrise par le symbole [0-1] qui indiquequune ligne possde de 0 1 valeur pour cette colonne10;

lidentifiant primaire est spcifi par la clause "id:" qui numre ses compo-sants; en outre, ceux-ci sont souligns dans le compartiment des colonnes;

tout identifiant secondaire est spcifi par une clause similaire "id:"; une cl trangre est spcifie par une clause "ref:" qui numre ses compo-

sants; de cette clause est issue une flche qui pointe vers lidentifiant rfrenc(celui-ci pourrait tre secondaire, ce quon ignorera dans cet ouvrage);

un groupe de colonnes qui forme la fois un identifiant et une cl trangre estsimultanment not "id:" (ou "id:") et "ref".

3.7 NOTE SUR LES CONTRAINTES RFRENTIELLES

Un schma qui comporte un identifiant constitu de deux (ou plusieurs) cls tran-gres doit faire lobjet dune attention toute particulire lorsque cet identifiant estlui-mme vis par une cl trangre. Considrons par exemple (figure 3.9) une tableCOMPTE, dont chaque ligne reprsente le crdit ouvert par un client (table CLIENT)auprs dun fournisseur (table FOURNISSEUR). Chaque achat du client (tableACHAT) est attach un compte.

Figure 3.9 - Contraintes rfrentielles complexes

La contrainte rfrentielle relative la cl trangre de la table ACHAT stipule quetoute valeur de (NCLI, NFOURN) dACHAT doit se retrouver dans une ligne de

10. En fait toute colonne possde une telle caractristique; cependant la valeur la plus frquente[1-1] nest pas reprsente pour allger le dessin.

3

1 2

42 Chapitre 4 Concepts des bases de donnes

COMPTE (proprit 3). tant donn que lidentifiant (NCLI, NFOURN) de COMPTEest constitu de deux cls trangres, on a aussi les proprits suivantes :

toute valeur de NCLI de COMPTE est une valeur de NCLI de CLIENT (proprit1),

toute valeur de NFOURN de COMPTE est une valeur de NFOURN de FOUR-NISSEUR (proprit 2).

Des proprits 1, 2 et 3, on dduit alors que (figure 3.10) :

toute valeur de NCLI de ACHAT est une valeur de NCLI de CLIENT (proprit4),

toute valeur de NFOURN de ACHAT est une valeur de NFOURN de FOURNIS-SEUR (proprit 5).

Cependant, les proprits 4 et 5 ne peuvent jamais se substituer la proprit 3,comme le suggre le schma de la figure 3.10.

Figure 3.10 - Une version incorrecte du schma 3.9

Par exemple, un achat reprsent par (C123,F445,14/04/2006,...) est valide,non pas par lexistence dun client n C123 et dun fournisseur n F445, mais parcequil existe un compte identifi par (C123,F445), auquel il est attach11. Il sagitdune erreur qui apparat frquemment chez les modlisateurs dbutants.

11. Plus prcisment, cette discussion se base sur deux proprits importantes concernant lesrgles dinclusion, et donc les cls trangres. La premire (dite de dcomposition) veut que siU.(A,B) T.(A,B), alors on a aussi que U.A T.A et U.B T.B; linverse nest cependant pasvraie. La seconde (dite de transitivit) stipule que si U.A T.A et T.A R.A, alors on a aussiU.A R.A. Etant donn les proprits (T.A R.A) et (T.B S.B), la proprit (U.(A,B) T.(A,B)) permet dinfrer (U.A R.A) et (U.B S.B). Mais, une fois encore, linverse nest pasvraie. Dans cette formulation, R(A,..), S(B,..), T(A,B,..) et U(A,B,..) sont des schmas de tables,R.A dsigne lensemble des valeurs de A dans R, et U.(A,B) dsigne lensemble des couples devaleurs de (A,B) dans U.

1 2

4 5

3.8 Modification et contraintes dintgrit 43

Dun

od

La

phot

ocop

ie n

on a

utor

ise

est

un

dlit

.

3.8 MODIFICATION ET CONTRAINTES DINTGRIT

Les proprits structurelles (identifiant, contrainte rfrentielle, colonne obligatoire/facultative) associes aux donnes doivent tre respectes tout instant; elles cons-tituent donc des contraintes imposes aux oprations de modification de cesdonnes. Ajouter une ligne, supprimer une ligne ou modifier une valeur de colonnedune ligne sont des oprations qui ne sont autorises que si ces proprits sonttoujours respectes par les donnes aprs lopration. Si ces proprits sont violes,on dira que les donnes ont perdu leur intgrit. Ces proprits constituent ds lorsdes contraintes dintgrit. Nous examinerons brivement limpact de cescontraintes sur les oprations de modification du contenu dune base de donnes.

3.8.1 Les contraintes dunicit (identifiants)

Un identifiant constitue une contrainte dunicit imposant qu tout instant les lignesdune table possdent des valeurs distinctes pour une ou plusieurs colonnes. Il nepeut exister, aucun moment, plus dune ligne de CLIENT ayant la mme valeur deNCLI.

Cration dune ligne : il ne peut exister de ligne possdant la mme valeur delidentifiant.

Suppression dune ligne : pas de contrainte.

Modification de lidentifiant dune ligne : il ne peut exister de ligne possdantla nouvelle valeur de lidentifiant.

3.8.2 Les contraintes rfrentielles (cls trangres)

Une contrainte rfrentielle prcise que certaines colonnes dune table, appeles cltrangre, doivent tout instant, pour chaque ligne, contenir des valeurs quonretrouve comme identifiant primaire dune ligne dans une autre table12. Cest ainsique la table DETAIL est soumise deux contraintes rfrentielles. La premireindique que toute valeur de NCOM doit identifier une ligne de COMMANDE. Laseconde indique que toute valeur de NPRO doit identifier une ligne de PRODUIT.

Les rgles qui rgissent les oprations de modification des donnes sont pluscomplexes que pour les autres contraintes. Nous raisonnerons sur les tables CLIENTet COMMANDE (figure 3.11), cette dernire faisant lobjet dune contrainte rf-rentielle, puisque la valeur de NCLI de toute ligne de COMMANDE doit tre prsentedans la colonne NCLI dune ligne CLIENT (en termes de la ralit de lentreprise,toute commande doit appartenir un client enregistr). On ignorera dans cetteanalyse limpact des autres contraintes rfrentielles du schma sur les oprations.

12. Il peut dailleurs sagir de la mme table.

44 Chapitre 4 Concepts des bases de donnes

Figure 3.11 - Schma dtude de lintgrit rfrentielle

Cration dune ligne de COMMANDE

La valeur de NCLI de cette ligne doit tre prsente dans la colonne NCLI dune lignede CLIENT (si la colonne NCLI de COMMANDE avait t dclare facultative, alorsla valeur de NCLI de la ligne pourrait tre absente).

Suppression dune ligne de COMMANDE

Effectue sans restriction13.

Modification de la valeur de NCLI dune ligne de COMMANDE

La nouvelle valeur de NCLI de la ligne doit tre prsente dans la colonne NCLI duneligne de CLIENT (si NCLI de COMMANDE avait t dclare facultative, alors onaurait pu effacer la valeur de NCLI de la ligne).

Cration dune ligne de CLIENT

Effectue sans restriction.

Suppression dune ligne de CLIENT

Le problme se pose lorsque ce client possde des commandes; il existe alors deslignes dans COMMANDE qui rfrencent la ligne de CLIENT supprimer. On dfinittrois comportements possibles qui laissent la base de donnes dans un tat correct.

Blocage. Le premier consiste refuser la suppression de la ligne de CLIENTafin dviter de laisser dans la base de donnes des lignes de COMMANDEorphelines.

Propagation ou cascade. Le deuxime consiste supprimer non seulement laligne de CLIENT, mais aussi toutes les lignes de COMMANDE qui la rfren-cent14.

13. Pour tre tout fait prcis, noublions pas que DETAIL est aussi li COMMANDE par unecontrainte rfrentielle. La suppression dune ligne de COMMANDE na pas dimpact sur sarelation avec CLIENT, mais elle en a sur celle qui concerne DETAIL, que nous ignorons ici poursimplifier le raisonnement.14. Et bien sr aussi les lignes de DETAIL qui rfrencent les lignes de COMMANDE ainsisupprimes.

3.9 La normalisation 45

Dun

od

La

phot

ocop

ie n

on a

utor

ise

est

un

dlit

.

Indpendance. Le troisime est possible lorsque la cl trangre est constituede colonnes facultatives. Il consisterait ici, si NCLI de COMMANDE avait tdclare facultative, effacer la valeur de la colonne NCLI des lignes deCOMMANDE qui rfrencent la ligne de CLIENT supprimer. De la sorte, cescommandes nappartiennent plus aucun client aprs lopration.

Modification de NCLI dune ligne CLIENT Si aucune ligne de COMMANDE ne rfrence cette ligne de CLIENT, la contrainterfrentielle nimpose pas de restriction. Si au contraire de telles lignes existent, onadmet trois comportements similaires ceux de lopration de suppression : refus demodification, modification des valeurs de cls trangres qui rfrencent cette ligne,effacement des valeurs de cls trangres qui rfrencent cette ligne.

On voit donc que limpact dune contrainte rfrentielle nest pas unique et que,selon la ralit reprsenter et la politique de gestion des donnes, on sera amen choisir lun ou lautre des comportements dcrits.

3.8.3 Les colonnes obligatoires

Si une colonne est dclare obligatoire, chaque ligne doit en possder une valeur.Lors des oprations de cration et de modification de lignes, cette colonne devrarecevoir une valeur significative. Par exemple, il est permis de crer une ligne deCLIENT sans valeur pour CAT, mais pas sans valeur pour LOCALITE.

3.9 LA NORMALISATION

Les tables que nous avons rencontres jusquici reprsentaient chacune un ensembledentits clairement identifi : des fournisseurs, des clients, des commandes, descomptes ou des achats. Certaines tables peuvent prsenter une structure pluscomplexe, gnralement considre comme indsirable. Tel est le cas de la table dela figure 3.12, que nous allons examiner de plus prs.

Figure 3.12 - La table LIVRE enregistre les informations sur les livres disponiblesdans une bibliothque

46 Chapitre 4 Concepts des bases de donnes

3.9.1 Le phnomne de redondance interne

Le propritaire de la table LIVRE dsire manifestement y enregistrer les livres de labibliothque dont il est responsable. Pour chaque livre, il a repris le numro, le titre,lauteur, le code ISBN, la date dachat et son emplacement dans les rayonnages. Unlivre qui fait lobjet dune demande importante de la part des lecteurs peut treacquis en plusieurs exemplaires, qui font chacun lobjet dune ligne distincte de latable.

Pour naturelle quelle puisse paratre, cette reprsentation pose cependant unproblme : lorsquun livre existe en plusieurs exemplaires, les lignes dcrivantceux-ci reprennent les mmes valeurs du titre, de lauteur et du code ISBN.

On nomme ce phnomne redondance dinformation, puisquune mme informa-tion est enregistre plusieurs fois. Si par inadvertance nous effacions le titre du livre1067, il serait ais de le restaurer par une recherche dun autre livre qui possderaitle mme code ISBN, et qui aurait aussi le mme titre. Une telle situation viole leprincipe fondateur des bases de donnes : tout fait pertinent du domaine dapplica-tion doit y tre enregistr une et une seule fois. Sur le plan pratique, la redondancenest pas sans inconvnients.

1. Avant tout, la table occupe un espace excessif.

2. Ensuite, la modification ultrieure du titre ou de lauteur dun livre exigera lamme modification des lignes de tous les exemplaires de ce livre, dfaut dequoi les donnes deviendraient incohrentes.

3. Si lenregistrement dun premier exemplaire dun livre peut se faire librement,celui des exemplaires suivants doit tre conforme aux informations dj prsen-tes.

4. Plus subtilement encore, leffacement du seul exemplaire dun livre entra-nerait la perte dfinitive des informations sur son titre et son auteur.

Cette analyse montre donc que la table est soumise une contrainte dintgritdun type nouveau : si deux lignes ont la mme valeur dISBN, alors elles ont lesmmes valeurs de TITRE et dAUTEUR. On dira aussi que la valeur de TITRE (etdAUTEUR) est principalement fonction de celle dISBN, colonne qui nest paslidentifiant de la table.

3.9.2 Normalisation par dcomposition

Lobservation attentive des donnes contenues dans la table LIVRE montre quecelle-ci contient des renseignements sur deux catgories dentits : les livres eux-mmes et les ouvrages dont les livres sont des exemplaires multiples. Il faudrait parexemple distinguer louvrage Mercure dA. Nothomb (ISBN 2 253 14911 X) destrois exemplaires de celui-ci que constituent les livres 1032, 1067 et 1022. Lesdonnes concernant un ouvrage sont enregistres autant de fois que cet ouvrage adexemplaires.

3.9 La normalisation 47

Dun

od

La

phot

ocop

ie n

on a

utor

ise

est

un

dlit

.

La rsolution de ce problme passe par la dcomposition de la table LIVRE endeux tables distinctes, auxquelles nous donnerons de nouveaux noms, pour vitertoute ambigut (figure 3.13). La premire, OUVRAGE, contient la description desouvrages. On y reprend le code ISBN, le titre et lauteur de chacun de ceux-ci. Laseconde table, EXEMPLAIRE, dcrit les exemplaires, ventuellement multiples, deces ouvrages. On y indique, pour chacun deux, le numro dexemplaire, le codeISBN de louvrage (rfrence OUVRAGE), la date dachat et lemplacement dansles rayonnages.

Ce processus de dcomposition en vue dliminer les redondances internes portele nom de normalisation. Les tables de la figure 3.13 sont dites normalises, alorsque la table LIVRE ne lest pas.

Figure 3.13 - Dans une base de donnes normalise, on distingue les exemplairesdes ouvrages dont ils sont la matrialisation. Les redondances prsentes dans latable de la figure 3.12 sont ainsi limines

3.9.3 Analyse du phnomne

La proprit qui veut que deux lignes qui ont la mme valeur dISBN ont les mmesvaleurs de TITRE et dAUTEUR porte le nom de dpendance fonctionnelle, et senote

ISBN TITRE, AUTEURen vertu du fait que cette proprit spcifie simplement une fonction, au sens math-matique du terme ( une valeur dISBN correspond une seule valeur de TITRE etdAUTEUR). La partie gauche se nomme le dterminant de la dpendance et lapartie droite le dtermin.

On notera deux proprits importantes : (1) un identifiant dune table est undterminant de chacune des (autres) colonnes de la table, (2) inversement, toutecolonne, ou groupe de colonnes, qui est un dterminant pour chacune des (autres)colonnes de la table est un identifiant. En particulier, on a :

NUMERO TITRE, AUTEUR, ISBN, DATE_ACH, PLACE

48 Chapitre 4 Concepts des bases de donnes

Ces dfinitions et observations nous permettent de rpondre trois questions impor-tantes.1. Comment dcomposer une table ?

La dcomposition dune table doit se faire selon une dpendance fonctionnelle, dfaut de quoi lopration entrane une perte de donnes, en ce sens quunerecomposition (quon appellera plus tard jointure) ne restitue pas les donnesinitiales. Elle doit obir au cannevas de la figure 3.14, qui montre que la table Rpeut tre remplace par la table S, qui regroupe le dterminant et le dtermin dela dpendance, et dune variante rduite de la table R, de laquelle le dtermin at retir. Les colonnes A, B et C sont en toute gnralit des groupes de colonnes.

Figure 3.14 - Dcomposition sans pertes selon une dpendance fonctionnelle

2. Quest-ce quune table normalise ?Une table est normalise si tout dterminant y est un identifiant. A linverse, siune table est le sige dune dpendance fonctionnelle anormale, cest--dire dontle dterminant nest pas un identifiant, alors cette table nest pas normalise, et estsusceptible de contenir des donnes redondantes.

3. Comment traiter une table non normalise ?Toute table qui est le sige dune dpendance anormale doit tre dcomposeselon cette dpendance, selon le cannevas de la figure 3.14. Si cette table contientplusieurs dpendances anormales, on la dcomposera itrativement en traitant, chaque stade, celles dont le dtermin nest pas un dterminant.

On observera que la dcomposition de la table LIVRE en EXEMPLAIRE etOUVRAGE obit ces principes (ISBN est un dterminant mais nest pas un identi-fiant). Examinons le traitement de deux exemples supplmentaires.

Les achatsChaque ligne (c, p, x) de la table ACHAT ci-dessous reprsente le fait que le clientc achte le produit p au prix x. On sait que le prix dun produit est constant, quelque soit le client qui lachte.

B C

PRODUIT PRIX

3.9 La normalisation 49

Dun

od

La

phot

ocop

ie n

on a

utor

ise

est

un

dlit

.

Cette table est donc le sige de la dpendance fonctionnelle PRODUIT PRIX.Elle nest pas normalise, puisque son identifiant (CLIENT, PRODUIT) est diff-rent du dterminant de la dpendance. Il est donc ncessaire de la dcomposer enune table TARIF et une table ACHAT.

Les voyagesDans le second exemple, la table VOYAGE dcrit des vhicules (NUMV) duncertain modle (MODELE) effectuant des voyages une certaine date (DATE),sous la responsabilit dun conducteur (NUMC) dont on enregistre le nom (NOM).Lidentifiant est constitu des colonnes (NUMV, NUMC, DATE).Tout conducteur a un nom et tout vhicule est dun certain modle, caractristi-ques qui ne dpendent pas des autres informations de la table. Ces deuxproprits se traduisent par les dpendances fonctionnelles suivantes :

NUMC NOMNUMV MODELE

La table VOYAGE est le sige de deux dpendances anormales, et nest donc pasnormalise. On la traitera comme dcrit ci-dessous.

La question de la dnomination des deux fragments est assez simple rsoudre. Latable rduite conserve son nom. Elle reprsente en effet les mmes faits que la tabledorigine, dbarrasse des informations problmatiques (dtermin). Ces derniressont regroupes, avec le dterminant, dans une seconde table, dont il faut prciser lasignification. Celle-ci sobtient en rpondant la question, relative la figure 3.14 :quels sont les objets ou les faits qui sont identifis par B et caractrise par C ?

3.9.4 Remarques

Ltude des dpendances fonctionnelles, des formes normales et des techniques denormalisation constitue une partie essentielle du domaine des bases de donnes.Elle est plus riche et plus complexe que ce que nous avons discut dans cette section.En particulier, il existe dautres types de dpendances et dautres formes normales.Celle que nous avons prsente, qui veut que tout dterminant soit un identifiant, estdnomme forme normale de Boyce-Codd, mais est souvent appele (impropre-ment) 3me forme normale. Le lecteur intress par cette question consultera, parexemple, [Bouzeghoub, 1998] ou [Date, 2001].

Les SGBD ignorent les dpendances fonctionnelles, sauf celles dont le dtermi-nant est un identifiant de la table. Ils sont donc incapables de grer les redondancesinternes dune table non normalise (sinon via des dclencheurs, tudis auChapitre 6). Il est donc important de nadmettre que des tables normalises.

NUMV MODELENUMC NOM

50 Chapitre 4 Concepts des bases de donnes

3.10 LES STRUCTURES PHYSIQUES

A la forme tabulaire des donnes correspond sur le support une structure physiquerelativement complexe qui garantit de bonnes performances lors de l'excution desrequtes. On dcrira brivement certaines de ces structures : index, espaces de stoc-kage et agrgats physiques