Architectures, modèles et langages de données Modèle relationnel, algèbre relationnelle et ...

173 Pages·2005·1.68 MB·French

Architectures, modèles et langages de données

Ingénierie des

bases de données

Hypercube
c,d

OLAP

Fascicule 2

Modèle relationnel, algèbre relationnelle et transposition du
conceptuel au relationnel 
André Gamache 2005

Architectures, Modèles et Langages de Données

Volume 1
Fascicule 1
1- Introduction 1
2- Architecture fonctionnelle du SGBD 20
3- Modèle conceptuel des données 79
- Index
Fascicule 2

4- Modèle relationnel : théorie et contraintes d’intégrité 1
5- Algèbre relationnelle 57
6- Transposition du MCD au MRD 137
- Index

Volume 2
Fascicule 3
7- Langage de données SQL 1
8- Indexation, vue relationnelle et base réactive 123
- Index
Fascicule 4
9- Langage de programmation et SQL 1
10- Théorie de la normalisation relationnelle : les formes normales FN1 à FN5
45
11- Optimisation des requêtes relationnelles 117
Annexes :
A- SQLoader
B- Projet ALU-Nord : Script de chargement des données
- Index
Chapitre 4 Modèle relationnel et normalisation 3

© André Gamache, Département d’informatique et de génie logiciel, Faculté des sciences et de génie, Université
Laval, Québec, Qc, Canada, G1K 7P4. Courriel : [email protected]
Chapitre 4 Modèle relationnel et normalisation 4

Chapitre 4 
Modèle relationnel: théorie et contraintes d’intégrité 
Dans ce chapitre, nous allons étudier le modèle relationnel (MRD) en abordant les bases
théoriques. Afin d’éviter une démarche trop désincarnée, nous introduirons les éléments
d’implémentation et le langage SQL-DDL pour illustrer leur utilisation dans la création d’une
base de données. Nous étudierons aussi plusieurs implémentations des mécanismes de validité et
de cohérence pour les données inhérentes à ce modèle, débordant les technologies à la mode de
l’heure. Il s’agit en premier d’une étude du MRD et non une étude exhaustive du système Oracle.
Vous y trouverez donc des notions théoriques implémentées et définies différemment selon le
SGBD auquel font référence les exemples. Le SGBD Oracle est en trame de fond dans de
nombreux exemples et illustrations des concepts sous-jacents au modèle relationnel.

4.1 Modèle relationnel
Le modèle relationnel proposé par l’article phare de Codd (1) est une représentation logique et
tabulaire des données stockées et cela dans une perspective propre aux usagers. En d’autres mots,
les utilisateurs voient les données sous forme tabulaire sans connaître les détails de leur
implémentation physique. L’exploitation des données fait appel à la même vision externe des
données. Les tables qui sont appelées relations dans la théorie relationnelle sont obtenues par la
transformation du modèle relationnel MCD vers le modèle relationnel (MRD) selon un ensemble
de règles de transposition appliquées à chaque classe et à chaque association du MCD. Une
relation est implémentée par une table logique. Cette dernière est nommée dans le langage SQL
par un libellé unique qui est inscrit dans le dictionnaire. L'en-tête de la table est composé des
attributs de la relation. Les lignes de la table sont les tuples qui correspondent aux instances de la
classe d’entités représentée par la table. Il ne peut pas y avoir deux tuples identiques dans
l’extension d’une même relation. Chaque table correspond à une sémantique précise qui peut être
explicitée par une interprétation d’un tuple en langage naturel.

Le passage du modèle conceptuel au logique est fait selon des règles précises appliquées 
à chaque élément du modèle : attribut, classe, association et spécialisation. A ce stade de 
notre  étude,  le  passage  sera  illustré  par  quelques  modèles  conceptuels  simples  qui 
permettront  de  justifier  la  structure  des  tables  utilisées  et  dʹétudier  les  propriétés 
particulières au modèle relationnel. Les autres règles de passage seront étudiées dans un 
prochain chapitre. 
Exemple : Gestion des comptes bancaires individuels modélisée par le diagramme des 
classes UML du MCD suivant : 

Client Compte

nom* 1 EstProprio 1..* noCompte*

rue nomSucc

ville solde

© André Gamache, Département d’informatique et de génie logiciel, Faculté des sciences et de génie, Université
Laval, Québec, Qc, Canada, G1K 7P4. Courriel : [email protected]
Chapitre 4 Modèle relationnel et normalisation 5

Ce MCD est capable de représenter (modéliser) les instances suivantes :

Instances de Client Instances de Compte
Bérubé, des-roses, Québec c-100, Montcalm, 250.00
Cousin, des-lilas, Lévis c-200,Desjardins, 350.00
Vézina, des-saules, Montréal c-300, Plateau, 450.00
c-400, Laurier, 550.00

Figure 4.1

Le ou les attributs de la clé primaire de chaque classe sont identifiés par un astérisque dans le
MCD. En appliquant les règles de transposition du MCD de la figure 4.1, nous obtenons le
modèle relationnel (MRD) composé des deux tables ci-dessous :

Client (nom*, rue, ville)
Compte (noCompte*, nomSucc, solde, nom)

N.B. Dans une clause SQL, le trait d'union utilisé dans le libellé d'un mot sera remplacé par le
souligné puisque le premier n'est pas admissible dans la plupart des langages de définition des
tables (SQL-DDL). De même, un libellé d’attribut pu de classe ne peut pas avoir d’accent.

La règle de transposition de ce MCD simple ayant une association de type 1 à plusieurs vers le
modèle relationnel ci-dessus consiste à représenter chaque classe qui participe à l'association par
une table de même nom et dotée des mêmes attributs. L'association de un à plusieurs n'est pas
représentée, mais plutôt simulée par l'ajout, dans la table correspondant à la classe située du côté
1..n, des attributs spécifiques à l'association et ceux de la clé primaire de la classe située du côté
de la multiplicité 1. Au niveau des instances, chaque entité devient un tuple formé de valeurs
scalaires.

À noter que le modèle de cet exemple simple a des limites évidentes de représentation. Par
exemple, la clé étant le nom du client, on ne peut pas représenter des clients ayant le même
patronyme! En effet, le nom doit identifier les clients et les distinguer les uns des autres! Pour
contourner cette difficulté, il faudrait choisir une autre clé, le nas par exemple, et l'ajouter aux
attributs de la classe Client. Dans le modèle ci-dessus, le nom est donc présumé être la clé de la
relation. La clé de la deuxième relation présume aussi que le numéro de compte bancaire est
global pour une même banque, car autrement le numéro de compte ne serait pas nécessairement
unique parmi les comptes des différentes succursales.

Avec un MCD légèrement plus complexe doté d’une association de plusieurs à plusieurs, le
passage se fait selon une règle quelque peu différente. Par exemple, lorsque les contraintes de
multiplicité de part et d'autre d'une association sont de plusieurs à plusieurs, chaque classe et
chaque association sont respectivement transformées en une table pour chaque élément. Au total,
le MRD comprend alors 3 tables.

Client (nom*, rue, ville)
Compte (noCompte*, noSucc, solde)
EstProprio (nom*, noCompte*, dateS)

© André Gamache, Département d’informatique et de génie logiciel, Faculté des sciences et de génie, Université
Laval, Québec, Qc, Canada, G1K 7P4. Courriel : [email protected]
Chapitre 4 Modèle relationnel et normalisation 6

Client Compte

nom* 1..* 1..* noCompte*

rue nomSucc

ville solde
EstProp rio
dateS

Diagramme UML de classes avec une classe d’association
Figure 4.1c

L’attribut dateS représente la date de validité de la signature d’un client pour le compte bancaire
partagé entre plusieurs personnes.

Le schéma logique de la table EstProprio qui modélise l'association comprend la clé primaire de
chaque classe UML participante et les attributs propres à l'association, c’est-à-dire ceux de la
classe d’association. Il existe aussi des règles de transformation pour les autres structures utilisées
dans les MCD. Elles seront étudiées avec plus de détails dans un chapitre subséquent.
Interprétation du modèle 
Le modèle relationnel représente parfois de façon approximative une certaine réalité
organisationnelle. La sémantique du MRD est souvent précisée par une phrase qui interprète
chaque association et chaque table. Par exemple la table Client dérivée du MCD de la figure 4.1 a
une sémantique particulière : Un client nommé Jacques habite sur la rue des Lilas à Québec. Un
client peut avoir plusieurs comptes dans une succursale bancaire et au moins un, peu importe la
succursale. Un compte doit être la propriété d’une seule personne. Dans ce modèle très simple, le
client est présumé être identifié sans équivoque par son nom, lequel joue le rôle de clé! Il est bien
sûr possible de représenter une réalité plus complexe en changeant la définition des classes et des
contraintes des associations du MCD et en multipliant les tables.
Conventions dʹécriture pour la relation et son extension 
Nous conviendrons d'une notation particulière pour désigner une relation du MRD et une autre
pour référer à son extension, c’est-à-dire à son contenu :
a) Lorsqu’il y a référence à une relation (ou table), le nom commence par une majuscule ou est
entièrement en majuscules. Si la référence est faite à son extension, le nom de celle-ci commence
par une minuscule ou est entièrement en lettres minuscules. De façon générale, l'extension de la
relation R est notée par r(R), tandis que son schéma relationnel est noté R. On peut en pratique
utiliser indifféremment dans les expressions soit le schéma Client, soit son extension client. Dans
les deux cas, le système réfère aux données de la table.

b) Les attributs de la clé primaire sont marqués par un astérisque ou soulignés. Les attributs de la
clé primaire sont regroupés de préférence au début de la table.

c) Le type des attributs - exemples : numérique, chaîne de caractères et date - n'est pas
obligatoirement précisé dans le modèle relationnel (logique). Il devra l’être cependant lors de la
création de chaque relation. Il est parfois utile d’ajouter le domaine de chaque attribut dans le
MRD.

© André Gamache, Département d’informatique et de génie logiciel, Faculté des sciences et de génie, Université
Laval, Québec, Qc, Canada, G1K 7P4. Courriel : [email protected]
Chapitre 4 Modèle relationnel et normalisation 7

Par exemple, le MCD de la figure 4.1 est implémenté par les deux tables ci-dessous dans
lesquelles les clés sont respectivement nom et noCompte. Voici deux extensions relationnelles
valides de ce modèle :

Client : Compte :
nom* rue ville noCompte* nomSucc solde nom
Bérubé des-roses Québec c-100 Montcalm 250.00 Bérubé
Cousin des-lilas Lévis c-200 Desjardins 350.00 Cousin
Vézina des-saules Montréal c-300 Plateau 450.00 Vézina
c-400 Laurier 550.00 Vézina
Figure 4.2

L’extension de Compte (pouvant être notée compte) regroupe dans cet exemple quatre tuples
valides de la table. L’extension d’une table est dynamique et varie donc dans le temps selon les
mises à jour effectuées par les différentes applications autorisées à exploiter la base de données.
L’en-tête de la table est le schéma de la relation et est défini par le nom de la table et les attributs.
Les tuples constituent son extension dont le nom débute par une lettre minuscule.
Relation unique pour un MRD 
Pourquoi alors ne pas représenter toute l'information sur les clients et les comptes bancaires par
une seule table au lieu de deux comme cela est préposé par les règles de transposition ? Une telle
approche éviterait une multiplication des tables et partant, allégerait le calcul de certaines
réponses qui exigent la consultation de deux ou de plusieurs tables. Par exemple, à la figure 4.3,
on a l'extension de la relation CompteClient qui contient toute l'information des deux tables
précédentes. La réponse à une question du genre trouver le nom et le solde d'un client est alors
obtenue par la consultation d'une seule table, soit CompteClient.

CompteClient :
nom* rue ville noCompte* solde nomSucc
Bérubé des-roses Québec c100 250.00 Montcalm
Cousin des-lilas Lévis c200 350.00 Desjardins
--> Vézina des-saules Montréal c300 450.00 Plateau
--> Vézina des-saules Montréal c400 550.00 Laurier

Figure 4.3

Par contre, avec cette relation, le fait que la cliente Vézina ait deux comptes bancaires à des
succursales différentes est représenté par deux tuples. Du même coup, il y a redondance pour les
valeurs des attributs non primaires, rue et ville du client. Cette redondance peut devenir
importante avec la croissance du schéma de la table et/ou l'augmentation de la taille de
l'extension. Elle est une source d'erreurs en cas d'une mise à jour incomplète. En effet, la mise à
jour d'une table est précédée par une recherche dont le prédicat peut être formulé de manière à ne
pas retrouver tous les tuples correspondant aux comptes de Vézina. Ainsi, lorsque la propriétaire
du compte c300 change d’adresse et que cette modification n’est enregistrée par l'application que
pour ce compte, il en résulte que Vézina aura deux adresses différentes dans la base, et cela même
si ce n’est pas le cas dans la réalité. La base est donc devenue incohérente. Par exemple,
l'application ci-dessous effectue une telle mise à jour partielle de la table CompteClient pour
modifier l’adresse de Vézina propriétaire du compte bancaire c300 :

{Ouvrir CompteClient;
noCompte = 'c300';
© André Gamache, Département d’informatique et de génie logiciel, Faculté des sciences et de génie, Université
Laval, Québec, Qc, Canada, G1K 7P4. Courriel : [email protected]
Chapitre 4 Modèle relationnel et normalisation 8

nom = ' Vézina'; -- recherche par la clé 'Vézina + c300'
Lire un tuple t dans CompteClient ;
Tant que ( t.nom != 'Vézina' et t.noCompte != 'c300')
Lire un tuple t dans l’extension de CompteClient;
fin ;
t.adresse = 'Berry'; -- mise à jour d’un tuple
fin
}

Pour éviter une mise à jour partielle de la table, et pour simplifier la programmation des
applications, il suffit d'avoir deux relations plutôt qu’une seule. Ainsi, l'adresse de chaque client
n’apparaîtra que dans un seul tuple qui sera celui maintenu à jour par l'application.

En poussant le raisonnement à sa limite, nous pourrions demander pourquoi ne pas avoir que des
relations binaires ? Par exemple, chaque attribut de Client faisant l'objet d'une relation :

ClientRue (nom*, rue) ClientVille (nom*, ville)
. . .

De toute évidence, la modélisation relationnelle avec seulement des relations binaires multiplie le
nombre de relations à consulter dans le calcul d'une réponse. Par exemple, pour obtenir toute
l’information sur un client, il faudra consulter autant de tables qu’il y a d’attributs le caractérisant.
Cette multiplication des lectures aura donc un effet négatif sur la performance du logiciel SGBD
et sur la complexité de l’application.

D’autre part, il suffit d’imaginer une base opérationnelle ayant par exemple une table nommée
Assurance définie par 20 attributs. Il est inconcevable de faire près de 20 relations lorsque
quelques unes suffisent pour représenter correctement le contrat d’assurance de chaque client.
Certaines relations sont donc préférables (ce sont les formes dites normales) à d’autres parce
qu’elles ont une structure qui bloque certaines anomalies potentielles pouvant apparaître en cours
d'exploitation et mettre en péril l'intégrité de la base. Ces relations dites normales constituent le
schéma optimal de la base. Nous verrons dans une autre section sur les formes normales comment
vérifier qu’une table est en forme normale et comment la transformer au besoin.

Dans certains cas, la recherche des formes normales aboutit à une multiplication des relations qui
alourdit le traitement subséquent. Dans la conception d’une base, il faudra faire la part des choses
entre la cohérence et l’efficacité. Nous discuterons de cet aspect plus loin dans ce chapitre en
abordant la question de la dénormalisation.
Domaines des attributs : sémantique et syntaxique 
Chaque attribut d’une relation peut avoir un domaine sémantique propre, défini comme
l’ensemble des valeurs atomiques (i.e. scalaires) admissibles pour valuer cet attribut (to value an
attribute signifie lui assigner une valeur). Deux attributs ayant des libellés différents peuvent
cependant partager le même domaine sémantique. Ils sont alors considérés compatibles. Par
exemple, le schéma de la table Menage ci-dessous est défini avec deux attributs, le premier est le
nas du conjoint et l'autre celui de la conjointe. Les deux attributs partagent le même domaine, soit
d-nas. Le schéma peut être formulé ainsi :
Menage (conjoint*:d_nas, conjointe*:d_nas)

© André Gamache, Département d’informatique et de génie logiciel, Faculté des sciences et de génie, Université
Laval, Québec, Qc, Canada, G1K 7P4. Courriel : [email protected]
Chapitre 4 Modèle relationnel et normalisation 9

Les deux attributs primaires de cette table Menage partagent le même domaine, d-nas quelle que
soit la définition explicite que l’on puisse donner à ce domaine. Les valeurs d’un domaine
partageront un même type. Par contre, il peut arriver dans certains schémas que deux attributs de
même libellé aient un domaine sémantique différent. Cette situation peut engendrer des
problèmes d’ambiguïté qu’il faudra savoir résoudre au moment de l’exploitation.

L’extension r d’une relation R sera toujours un sous-ensemble du produit cartésien du domaine
des attributs :
r ⊆ d1 x d2 x d3 x ... di

Le domaine exclut la valeur nulle qui représente l’absence d’une valeur. Celle-ci, qui est une
réalité lors de l’implantation, devra être traitée de façon particulière et systématique par le SGBD.
Comme un attribut ne peut pas être comparé à une valeur nulle (qui est hors domaine), il faudra
représenter cette valeur particulière (le null) par un code interne nommé indicateur d’absence de
valeur. Ce dernier ne pourra être testé que par un prédicat spécial, le IS NULL. Souvent par abus
de langage, cet indicateur est aussi appelé une valeur nulle ou null ! Cette dernière est hors du
domaine.
Domaine syntaxique 
Lorsque le domaine sémantique ne peut pas être spécifié par un ensemble de valeurs (ex. les
noms de client n’étant pas tous connus à priori), il est remplacé par le domaine syntaxique qui
correspond aux valeurs autorisées par le type des données du domaine. Ex. nom est une chaîne
de caractères de type VARCHAR2(50). Toute chaîne de caractères dont la longueur est égale ou
inférieure à 50 sera considérée comme une valeur du domaine nom, même s’il est peu réaliste.
Par exemple, la chaîne ‘ABCDEF’ est une valeur autorisée ! Le domaine syntaxique d’un attribut
est formalisé au moment de la création du schéma d’une relation :

Create table Client (
nas Number (8,0),
nom VARCHAR2(50); <--toute chaîne de 50 car.ou moins est valide)

Cette commande SQL-DDL Create table lance la création d’une structure de table qui servira par
la suite pour ranger des données. Les métadonnées (le schéma) qui définissent cette structure sont
logées dans le dictionnaire du SGBD. Au plan logique, la table Client est composée de deux
colonnes libellées respectivement nas et nom. Les données qui peuvent être rangées dans chaque
colonne sont assujetties éventuellement aux contraintes imposées par le domaine de chaque
attribut. Par exemple, il n’est pas possible de ranger un nom dépassant les 50 caractères ou d’y
ranger un nas qui est une chaîne de caractères.

Plusieurs attributs peuvent partager le même domaine et ses contraintes. Pour faciliter l’écriture
de ces domaines, il est possible de créer un domaine nommé. La commande SQL-92 CREATE
DOMAIN permet de définir à la fois les domaines syntaxique et sémantique :

- domaine syntaxique :
CREATE DOMAIN d_nom Varchar2(50);
--d_nom est le nom du domaine syntaxique

- domaine sémantique (numérique dans cet exemple) :
CREATE DOMAIN d_solde Number(8,2)
CHECK (solde >0 and solde < = 10000.00);
© André Gamache, Département d’informatique et de génie logiciel, Faculté des sciences et de génie, Université
Laval, Québec, Qc, Canada, G1K 7P4. Courriel : [email protected]