1

UNIVERSITE DUBURUNDI

Faculté des Sciences de

l’Ingénieur

Technologies de l’Information et
de la Communication

Cours d’Administration de base

de données

Année Académique 2022-2023

 i

Liste des figures

Figure 1: Hiérarchie des mémoires ............................................................................3
Figure 2 : Disque magnétique ....................................................................................5
Figure 3: Requêtes SQL sont déclaratives .................................................................7
Figure 4: Requête SQL au plan d’exécution ..............................................................8
Figure 5: Deux phases de l’optimisation ...................................................................8
Figure 6 : Processus général d’optimisation et d’évaluation .....................................9
Figure 7: Expression algébrique sous forme arborescente ......................................13
Figure 8: Modèle Physique de Données ..................................................................31
 ii

Liste des tableaux

Tableau 1: Exemple de transaction concurrente en MS Server ...............................25
Tableau 2 : Exemple de perte de mise à jour ...........................................................26
Tableau 3: Mode de verrouillage .............................................................................28
 iii

Table des matières

Liste des figures ......................................................................................................... i
Liste des tableaux ...................................................................................................... ii
Table des matières .................................................................................................... iii
CHAPITRE I. Introduction ........................................................................................1
CHAPITRE II. TECHNIQUES DE STOCKAGES ..................................................2
II.1. Stockage de données .......................................................................................3
II.1.1. Supports ...........................................................................................................3
II.1.2. Fonctionnement d’un disque ...........................................................................4
II.1.3. Dispositif .........................................................................................................4
II.1.4. Accès aux données ..........................................................................................6
II.2. Optimisation ....................................................................................................7
II.2.1. Introduction à l’optimisation et à l’évaluation ................................................7
II.2.2. Traitement de la requête ..................................................................................9
II.2.3. Règles d’optimisation de requêtes.................................................................14
II.3. Technologie RAID ........................................................................................14
CHAPITRE III. INDEX ...........................................................................................17
III.1. Types d’index ..............................................................................................18
CHAPITRE IV. CONCURRENCE D’ACCES .......................................................21
IV.1. Préliminaires ................................................................................................21
IV.2. Exécutions concurrentes: Sérialisabilité .....................................................22
IV.3. Exemple de transactions concurrentes ........................................................25
IV.4. Exemple de perte de mise à jour..................................................................26
IV.5. Verrous ........................................................................................................27
CHAPITRE V. TRANSACT STRUCTURED QUERY LANGAGE ....................29
V.1. Expressions ...................................................................................................30
V.2. Notions en transact SQL ...............................................................................31
V.2. Curseurs ........................................................................................................38
 iv

V.3. Procédures stockées ......................................................................................42

V.3.1. Création d'une procédure stockée .................................................................42
V.4. Fonctions .......................................................................................................48
V.5. Déclencheurs (Triggers)................................................................................49
V.5.1. Types de Triggers..........................................................................................50
V.5.2. Fonctionnement des tables inserted et deleted ..............................................51
V.5.3. Fonctionnement des Triggers INSTEAD OF et AFTER ..............................52
V.6. Vue ................................................................................................................55
V.6.1. Création et interrogation d’une vue ..............................................................55
V.6.2. Mise à jour d’une vue....................................................................................57
CHAPITRE VI. MAPPING OBJET/RELATIONNEL ..........................................59
VI.1. Introduction .................................................................................................59
VI.2. Logiciel Mapping Objet/Relationnel ...........................................................59
VI.3. Entités ..........................................................................................................59
VI.4. Mapping entre le bean entité et la table .......................................................60
REFERENCES BIBLIOGRAPHIQUES .................................................................66
 1

CHAPITRE I. Introduction
Les bases de données ont pris aujourd’hui une place essentielle dans l’informatique, plus
particulièrement en gestion. Au cours des cinquante dernières années, des concepts,
méthodes et algorithmes ont été développés pour gérer des données sur mémoires
secondaires ; ils constituent aujourd’hui l’essentiel de la discipline « Bases de Données
» (BD). Cette discipline est utilisée dans de nombreuses applications. Il existe un grand
nombre de Systèmes de Gestion de Bases de Données (SGBD) qui permettent de gérer
efficacement de grandes bases de données. De plus, une théorie fondamentale sur les
techniques de modélisation des données et les algorithmes de traitement a vu le jour. Les
bases de données constituent donc une discipline s’appuyant sur une théorie solide et
offrant de nombreux débouchés pratiques.
Le mot base de données est souvent utilisé pour désigner n’importe quel ensemble de
données ; il s’agit là d’un abus de langage qu’il faut éviter. Une base de données est un
ensemble de données modélisant les objets d’une partie du monde réel et servant de
support à une application informatique. Pour mériter le terme de base de données, un
ensemble de données non indépendantes doit être interrogeable par le contenu, c’est-à-
dire que l’on doit pouvoir retrouver tous les objets qui satisfont à un certain critère, par
exemple tous les produits qui coûtent moins de 1000 francs. Les données doivent être
interrogeables selon n’importe quel critère. Il doit être possible aussi de retrouver leur
structure, par exemple le fait qu’un produit possède un nom, un prix et une quantité.
Plutôt que de disserter longuement sur le concept de bases de données, précisons ce
qu’est un SGBD. Un SGBD peut être perçu comme un ensemble de logiciels systèmes
permettant aux utilisateurs d’insérer, de modifier et de rechercher efficacement des
données spécifiques dans une grande masse d’informations (pouvant atteindre quelques
milliards d’octets) partagée par de multiples utilisateurs. Les informations sont stockées
sur mémoires secondaires, en général des disques magnétiques. Les recherches peuvent
être exécutées à partir de la valeur d’une donnée désignée par un nom dans un ensemble
d’objets (par exemple, les produits de prix inférieur à 1000 francs), mais aussi à partir
de relations entre objets (par exemple, les produits commandés par un client habitant
Bujumbura). Les données sont partagées, aussi bien en interrogation qu’en mise à jour.
Le SGBD rend transparent le partage, à savoir donne l’illusion à chaque utilisateur qu’il
est seul à travailler avec les données.
 2

CHAPITRE II. TECHNIQUES DE STOCKAGES

Une base de données est constituée, matériellement, d’un ou plusieurs fichiers
volumineux stockés sur un support non volatile. Le support le plus couramment employé
est le disque magnétique (« disque dur ») qui présente un bon compromis en termes de
capacité de stockage, de prix et de performance. Il y a deux raisons principales à
l’utilisation de fichiers. Tout d’abord il est courant d’avoir affaire à des bases de données
dont la taille dépasse de loin celle de la mémoire principale. Ensuite – et c’est la
justification principale du recours aux fichiers, même pour des bases de petite taille –
une base de données doit survivre à l’arrêt de l’ordinateur qui l’héberge, que cet arrêt
soit normal ou dû à un incident matériel.

L’accès à des données stockées sur un périphérique, par contraste avec les applications
qui manipulent des données en mémoire centrale, est une des caractéristiques
essentielles d’un SGBD. Elle implique notamment des problèmes potentiels de
performance puisque le temps de lecture d’une information sur un disque est
considérablement plus élevé qu’un accès en mémoire principale. L’organisation des
données sur un disque, les structures d’indexation mises en œuvre et les algorithmes de
recherche utilisés constituent donc des aspects très importants des SGBD. Un bon
système se doit d’utiliser au mieux les techniques disponibles afin de minimiser les
temps d’accès. Il doit aussi offrir à l’administrateur des outils de paramétrage et de
contrôle qui vont lui permettre d’exploiter au mieux les ressources matérielles et
logicielles d’un environnement donné.
 3

II.1. Stockage de données

Un système informatique offre plusieurs mécanismes de stockage de l’information, ou
mémoires. Ces mémoires se différencient par leur prix, leur rapidité, le mode d’accès
aux données (séquentiel ou par adresse) et enfin leur durabilité. Les mémoires volatiles
perdent leur contenu quand le système est interrompu, soit par un arrêt volontaire, soit à
cause d’une panne. Les mémoires non volatiles, comme les disques ou les bandes
magnétiques, préservent leur contenu même en l’absence d’alimentation électrique.

II.1.1. Supports
D’une manière générale, plus une mémoire est rapide, plus elle est chère et –
conséquence directe – plus sa capacité est réduite. Les différentes mémoires utilisées par
un ordinateur constituent donc une hiérarchie (figure 1), allant de la mémoire la plus
petite mais la plus efficace à la mémoire la plus volumineuse mais la plus lente.
1. la mémoire cache est utilisée par le processeur pour stocker ses données et ses
instructions ;
2. la mémoire vive, ou mémoire principale stocke les données et les processus
constituant l’espace de travail de la machine ; toute donnée ou tout programme doit
d’abord être chargé en mémoire principale avant de pouvoir être traité par un
processeur ;
3. les disques magnétiques constituent le principal périphérique de type mémoire ; ils
offrent une grande capacité de stockage tout en gardant des accès en lecture et en
écriture relativement efficaces ;
4. enfin les bandes magnétiques sont des supports très économiques mais leur lenteur
les destine plutôt aux sauvegardes.

Figure 1: Hiérarchie des mémoires

 4

II.1.2. Fonctionnement d’un disque

Un disque est une surface circulaire magnétisée capable d’enregistrer des informations
numériques.
La surface magnétisée peut être située d’un seul côté (« simple face ») ou des deux côtés
(« simple face ») du disque.
Les disques sont divisés en secteurs, un secteur constituant la plus petite surface
d’adressage. En d’autres termes, on sait lire ou écrire des zones débutant sur un secteur
et couvrant un nombre entier de secteurs. La taille d’un secteur est le plus souvent de
512K.

II.1.3. Dispositif
La petite information stockée sur un disque est un bit qui peut valoir 0 ou 1. Les bits sont
groupés par 8 pour former des octets, et une suite d’octets forme un cercle ou piste sur
la surface du disque.
Un disque est entraîné dans un mouvement de rotation régulier par un axe. Une tête de
lecture (deux si le disque est double-face) vient se positionner sur une des pistes du
disque et y lit ou écrit les données. Le nombre minimal d’octets lus par une tête de lecture
est physiquement défini par la taille d’un secteur (en général 512K). Cela étant le
système d’exploitation peut choisir, au moment de l’initialisation du disque, de fixer
une unité d’entrée/sortie supérieure à la taille d’un secteur, et multiple de cette dernière.
On obtient des blocs, dont la taille est typiquement 512K (un secteur), 1024K (deux
secteurs) ou 4096K (huit secteurs).
 5

Chaque piste est donc divisée en blocs (ou pages) qui constituent l’unité d’échange entre
le disque et la mémoire principale.

Figure 2 : Disque magnétique

Toute lecture ou toute écriture sur les disques s’effectue par blocs. Même si la lecture ne
concerne qu’une donnée occupant 4 octets, tout le bloc contenant ces 4 octets sera
transmis en mémoire centrale.
La tête de lecture n’est pas entraînée dans le mouvement de rotation. Elle se déplace
dans un plan fixe qui lui permet de se rapprocher ou de s’éloigner de l’axe de rotation
des disques, et d’accéder à une des pistes. Pour limiter le coût de l’ensemble de ce
dispositif et augmenter la capacité de stockage, les disques sont empilés et partagent le
même axe de rotation (voir figure 2). Il y a autant de têtes de lectures que de disques
(deux fois plus si les disques sont à double face) et toutes les têtes sont positionnées
solidairement dans leur plan de déplacement. À tout moment, les pistes accessibles par
les têtes sont donc les mêmes pour tous les disques de la pile, ce qui constitue une
contrainte dont il faut savoir tenir compte quand on cherche à optimiser le placement
des données.
 6

L’ensemble des pistes accessibles à un moment donné constitue le cylindre. La notion

de cylindre correspond donc à toutes les données disponibles sans avoir besoin de
déplacer les têtes de lecture.
Enfin le dernier élément du dispositif est le contrôleur qui sert d’interface avec le
système d’exploitation. Le contrôleur reçoit du système des demandes de lecture ou
d’écriture, et les transforme en mouvements appropriés des têtes de lectures.

II.1.4. Accès aux données

Un disque est une mémoire à accès direct. Contrairement à une bande magnétique par
exemple, il est possible d’accéder à une information située n’importe où sur le disque
sans avoir à parcourir séquentiellement tout le support. L’accès direct est fondé sur une
adresse donnée à chaque bloc au moment de l’initialisation du disque par le système
d’exploitation. Cette adresse est généralement composée des trois éléments suivants :
1. le numéro du disque dans la pile ou le numéro de la surface si les disques sont à
double-face ;
2. le numéro de la piste ;
3. le numéro du bloc sur la piste.

La lecture d’un bloc, étant donné son adresse, se décompose en trois étapes :
- positionnement de la tête de lecture sur la piste contenant le bloc ;
- rotation du disque pour attendre que le bloc passe sous la tête de lecture
(rappelons que les têtes sont fixe, c’est le disque qui tourne) ;
- transfert du bloc.

La durée d’une opération de lecture est donc la somme des temps consacrés à chacune
des trois opérations, ces temps étant désignés respectivement par les termes délai de
positionnement, délai de latence et temps de transfert. Le temps de transfert est
négligeable pour un bloc, mais peu devenir important quand des milliers de blocs
doivent être lus. Le mécanisme d’écriture est à peu près semblable à la lecture, mais
peu prendre un peu plus de temps si le contrôleur vérifie que l’écriture s’est faite
correctement.
 7

II.2. Optimisation
SQL étant un langage déclaratif dans lequel on n’indique ni les algorithmes à appliquer,
ni les chemins d’accès aux données, le système a toute latitude pour déterminer ces
derniers et les combiner de manière à obtenir les meilleures performances.
Le module chargé de cette tâche, l’optimiseur de requêtes, tient donc un rôle
extrêmement important puisque l’efficacité d’un SGBD est fonction, pour une grande
part, du temps d’exécution des requêtes. Ce module est complexe. Il applique d’une part
des techniques éprouvées, d’autre part des heuristiques propres à chaque système. Il est
en effet reconnu qu’il est très difficile de trouver en un temps raisonnable
l’algorithme optimal pour exécuter une requête donnée. Afin d’éviter de consacrer des
ressources considérables à l’optimisation, ce qui se ferait au détriment des autres tâches
du système, les SGBD s’emploient donc à trouver, en un temps limité, un algorithme
raisonnablement bon.

II.2.1. Introduction à l’optimisation et à l’évaluation

SQL permet de déclarer un besoin, mais ne dit pas comment calculer le résultat. C’est
au système de produire une forme opératoire, un programme, pour effectuer ce calcul.
Notez que cette approche a un double avantage. Pour l’utilisateur, elle permet de ne pas
se soucier d’algorithmique d’exécution. Pour le système elle laisse la liberté du choix de
la meilleure méthode. C’est ce qui fonde l’optimisation, la liberté de déterminer la
manière de répondre à un besoin.

Figure 3: Requêtes SQL sont déclaratives

 8

En base de données, le programme qui évalue une requête a une forme très particulière.
On l’appelle plan d’exécution. Il a la forme d’un arbre constitué d’opérateurs qui
échangent des données. Chaque opérateur effectue une tâche précise et restreinte:
transformation, filtrage, combinaisons diverses. Cela permet au système de construire
très rapidement, à la volée, un plan et de commencer à l’exécuter.

Figure 4: Requête SQL au plan d’exécution

Le passage de SQL à un plan s’effectue en deux étapes :

1) on tire partie de l’équivalence entre SQL, ou une grande partie de SQL, avec
l’algèbre. Pour toute requête on peut donc produire une expression de l’algèbre.
Une telle expression est déjà une forme opérationnelle, qui nous dit quelles
opérations effectuer. Nous l’appellerons plan d’exécution logique. Une
expression de l’algèbre peut se représenter comme un arbre, et nous sommes déjà
proches d’un plan d’exécution. Il reste cependant assez abstrait.
2) le système va choisir des opérateurs particuliers, en fonction d’un contexte
spécifique. Ce peut être là présence ou non d’index, la taille des tables, la mémoire
disponible. Cette étape 2 donne un plan d’exécution physique, applicable au
contexte.

Figure 5: Deux phases de l’optimisation

 9

Il faut ici élargir le schéma: à chaque étape, 1 ou 2, plusieurs options sont possibles.
 Pour l’étape 1 : c’est la capacité des opérateurs de l’algèbre à fournir plusieurs
expressions équivalentes. La Figure 6 montre par exemple deux combinaisons
possibles issues de la même requête SQL.
 Pour l’étape 2 les options sont liées au choix de l’algorithmique, des opérateurs à
exécuter.

Figure 6 : Processus général d’optimisation et d’évaluation

II.2.2. Traitement de la requête

1. Décomposition en bloc
Une requête SQL est décomposée en une collection de blocs. L’optimiseur se
concentre sur l’optimisation d’un bloc à la fois. Un bloc est une requête select-
from-where sans imbrication. La décomposition en blocs est nécessaire à cause des
requêtes imbriquées. Toute requête SQL ayant des imbrications peut être décomposée
en une collection de blocs.
Soient les schémas de la base des films suivant:
 Film (idFilm, titre, annee, genre, resume, idRealisateur, codePays)
 Pays (code, nom, langue)
 Artiste (idArtiste, nom, prenom, anneeNaissance)
 Role (idFilm, idActeur, nomRole)
 10

 Internaute (email, nom, prenom, region)

 Notation (email, idFilm, note)

Considérons par exemple la requête suivante qui calcule le film le mieux ancien:
select titre from Film where annee = (select min (annee) from Film)

On peut décomposer cette requête en deux blocs: le premier calcule l’année

minimale \(A\). Le deuxième bloc calcule le(s) film(s) paru en \(A\) grâce à une
référence au premier bloc.
select titre from Film where annee = A

Cette méthode peut s’avérer très inefficace et il est préférable de transformer la requête
avec imbrication en une requête équivalente sans imbrication (un seul bloc) quand cette
équivalence existe. Malheureusement, les systèmes relationnels ne sont pas toujours
capables de déceler ce type d’équivalence. Le choix de la syntaxe de la requête SQL a
donc une influence sur les possibilités d’optimisation laissées au SGBD.

Prenons un exemple concret pour comprendre la subtilité de certaines situations, et

pourquoi le système à parfois besoin qu’on lui facilite la tâche. Notre base de données
est toujours la même, rappelons le schéma de la table Role car il est important.
Le système crée un index sur la clé primaire qui est composée de deux attributs. Quelles
requêtes peuvent tirer parti de cet index? Celles sur l’identifiant de l’acteur, celles sur
l’identifiant du film?
Maintenant, notre requête est la suivante: « Dans quel film paru en 1958 joue James
Stewart ». Voici comment on peut exprimer la requête SQL.

select titre from Film f, Role r, Artiste a where [Link] = 'Stewart'

and [Link]='James' and f.id_film = r.id_film and r.id_acteur
= [Link] and [Link] = 1958
 11

Cette requête est en un seul « bloc », mais il est tout à fait possible – question de style ?
– de l’écrire de la manière suivante:

select titre from Film f, Role r where f.id_film = r.id_film and

[Link] = 1958 and r.id_acteur in (select id_acteur from Artiste
where nom='Stewart' and prenom='James')

Au lieu d’utiliser in, on peut également effectuer une requête corrélée avec exists.

select titre from Film f, Role r where f.id_film = r.id_film and

[Link] = 1958 and exists (select 'x' from Artiste a where
nom='Stewart' and prénom='James' and r.id_acteur = a.id_acteur)

Encore mieux (ou pire), on peut utiliser deux imbrications:

select titre from Film where annee = 1958 and id_film in (select
id_film from Role where id_acteur in (select id_acteur from Artiste
where nom='Stewart' and prenom='James'))

Que l’on peut aussi formuler en:

select titre from Film where annee = 1958 and exists (select * from
Role where id_film = [Link] and exists (select * from Artiste where
id = Role.id_acteur and nom='Stewart' and prenom='James'))

Dans les deux dernier cas on a trois blocs. La requête est peut-être plus facile à
comprendre, mais le système a très peu de choix sur l’exécution: on doit parcourir tous
les films parus en 1958, pour chacun on prend tous les rôles, et pour chacun de ces rôles
on va voir s’il s’agit bien de James Stewart.
S’il n’y a pas d’index sur le champ annee de Film, il faudra balayer toute la table,
puis pour chaque film, c’est la catastrophe: il faut parcourir tous les rôles pour garder
ceux du film courant car aucun index n’est disponible. Enfin pour chacun de ces rôles
il faut utiliser l’index sur Artiste.
 12

1. Pourquoi ne peut-on pas utiliser l’index sur Rôle?

La clé de Rôle est une clé composite (id_acteur, id_film). L’index est un arbre B
construit sur la concaténation des deux identifiants dans l’ordre où ils sont spécifiés.
Un arbre B s’appuie sur l’ordre des clés, et on peut effectuer des recherches sur
un préfixe de la clé. En revanche il est impossible d’utiliser l’arbre B sur un suffixe.
Ici, on peut utiliser l’index pour des requêtes sur id_acteur, pas pour des requêtes
sur id_film.
Telle quelle, cette syntaxe basée sur l’imbrication présente le risque d’être
extrêmement coûteuse à évaluer. Or il existe un plan bien meilleur, mais le système
ne peut le trouver que s’il a des degrés de liberté suffisants, autrement dit si la requête
est à plat, en un seul bloc. Il est donc recommandé de limiter l’emploi des requêtes
imbriquées à de petites tables dont on est sûr qu’elles résident en mémoire.

2. Traduction et réécriture
Nous nous concentrons maintenant sur le traitement d’un bloc, étant entendu que ce
traitement doit être effectué autant de fois qu’il y a de blocs dans une requête. Il
comprend plusieurs phases. Tout d’abord une analyse syntaxique est effectuée, puis
une traduction algébrique permettant d’exprimer la requête sous la forme d’un
ensemble d’opérations sur les tables. Enfin l’optimisation consiste à trouver les
meilleurs chemins d’accès aux données et à choisir les meilleurs algorithmes possibles
pour effectuer ces opérations.
L’analyse syntaxique vérifie la validité (syntaxique) de la requête. On vérifie
notamment l’existence des relations (arguments de la clause from) et des attributs
(clauses select et where). On vérifie également la correction grammaticale
(notamment de la clause where). D’autres transformations sémantiques simples sont
faites au delà de l’analyse syntaxique. Par exemple, on peut détecter des contradictions
comme année = 1998 and année = 2003. Enfin un certain nombre de
simplifications sont effectuées. À l’issue de cette phase, le système considère que la
requête est bien formée.
L’étape suivante consiste à traduire la requête en une expression algébrique. Nous
allons prendre pour commencer une requête un peu plus simple que la précédente:
 13

trouver le titre du film paru en 1958, où l’un des acteurs joue le rôle de John Ferguson.
Voici la requête SQL:

select titre from Film f, Role r where nom_role ='John Ferguson'

and [Link] = r.id_film and [Link] = 1958

Cette requête correspond aux opérations suivantes: une jointure entre les rôles et les
films, une sélection sur les films (année=1958), une sélection sur les rôles (‘John
Ferguson’), enfin une projection (titre) pour éliminer les colonnes non désirées.

Dans l’arbre, les feuilles correspondent aux tables de l’expression algébrique, et les
nœuds internes aux opérateurs. Un arc entre un nœud et son nœud père indique que
l’« opération s’applique au résultat de l’opération ».

Figure 7: Expression algébrique sous forme arborescente

L’interprétation de l’arbre est la suivante. On commence par exécuter les opérations

sur les feuilles (ici une jointure); sur le résultat, on effectue les opérations
correspondant aux nœuds de plus haut niveau (ici une sélection), et ainsi de suite,
jusqu`à ce qu’on obtienne le résultat (ici après la projection). Cette interprétation est
bien sûr rendue possible par le fait que tout opérateur prend une table en entrée et
produit une table en sortie.
 14

II.2.3. Règles d’optimisation de requêtes

Lorsque vous écrivez vos requêtes, même si les SGBDs ont des optimiseurs, voici
quelques règles à respecter pour optimiser vos requêtes. (Qui ne sont pas
nécessairement dans l’ordre).
 R1 : Éviter le SELECT * : écrire plutôt le nom des colonnes dont vous avez
besoin pour la requête.
 R2 : Créez des indexes sur les colonnes que vous utilisez dans la clause
WHERE. Pour plus de performances, ces indexes doivent-être créés après
l’insertion des données dans la table.
 R3 : Lorsque c’est possible, utilisez le WHERE à la place du Having.
 R4 : Éviter les jointures dans le WHERE, utilisez plutôt le INNER JOIN.
 R5 : Lorsque c’est possible, utilisez une jointure à la place d’une sous-requête.
Les jointures sont l’essentiel des SGBDRs alors ils sont optimisés pour
l’écriture des jointures.

II.3. Technologie RAID

La technologie RAID (pour Redundant Array of Independent Disks) a pour objectif
principal de limiter les conséquences des pannes en répartissant les données sur un
grand nombre de disques, et de manière à s’assurer que la défaillance de l’un des
disques n’entraîne ni perte de données, ni même l’indisponibilité du système.

Il existe plusieurs niveaux RAID (de 0 à 6), chacun correspondant à une organisation
différente des données et donc à des caractéristiques différentes. Le niveau 0 est
simplement celui du stockage sur un seul disque. Nous présentons ci-dessous les
caractéristiques des principaux niveaux.

1. Duplication (RAID 1)
Le RAID 1 applique une solution brutale : toutes les entrées/sorties s’effectuent en
parallèle sur deux disques. Les écritures ne sont pas simultanées afin d’éviter qu’une
panne électrique ne vienne interrompre les têtes de lecture au moment où elles écrivent
le même bloc, qui serait alors perdu. L’écriture a donc d’abord lieu sur le disque
principal, puisque sur le second (dit « disque miroir »).
 15

Le RAID 1 est coûteux puisqu’il nécessite deux fois plus d’espace que de données. Il
permet certaines optimisations en lecture : par exemple la demande d’accès à un bloc
peut être transmise au disque dont la tête de lecture est la plus proche de la piste
contenant le bloc.
Les performances sont également améliorées en écriture car deux demandes de deux
processus distincts peuvent être satisfaites en parallèle. En revanche il n’y a pas
d’amélioration du taux de transfert puisque les données ne sont pas réparties sur les
disques.

2. Répartition et parité (RAID 4)

Ce niveau combine deux techniques. La première consiste à traiter les n disques comme
un seul très grand disque, et à répartir les données sur tous les disques. L’unité de
répartition est le bloc. Si on a 4 disques et des données occupant 5 blocs, on écrira le
premier bloc sur le premier disque, le second bloc sur le deuxième disque, et ainsi de
suite. Le cinquième bloc est écrit sur le premier disque et le cycle recommence.
L’avantage de cette répartition est d’améliorer les performances en lecture. Si un seul
bloc de données est demandé, une lecture sur un des disques suffira. Si en revanche les
2, 3 ou 4 premiers blocs de données sont demandés, il sera possible de combiner des
lectures sur l’ensemble des disques. Le temps de réponse est alors celui d’une lecture
d’un seul bloc. Plus généralement, quand de très larges volumes doivent être lus, il est
possible de répartir en parallèle la lecture sur les n disques, avec un temps de lecture
divisé par n, et un débit multiplié par n.
L’autre aspect du RAID 4 est une gestion « intelligente » de la redondance en stockant
non pas une duplication des données, mais un bit de parité. L’idée est la suivante : pour
n disques de données, on va ajouter un disque de contrôle qui permettra de récupérer
les données en cas de défaillance de l’un (un seul) des n disques.

3. Répartition des données de parité (RAID 5)

Dans le RAID 4, le disque de contrôle a tendance à devenir le goulot d’étranglement du
système puisque qu’il doit supporter n fois plus d’écritures que les autres. Le RAID 5
propose de remédier à ce problème en se basant sur une remarque simple : si c’est le
disque de contrôle lui-même qui tombe en panne, il est possible de le reconstituer en
 16

fonction des autres disques. En d’autres termes, pour la reconstruction après une panne,
la distinction disque de contrôle/disque de données n’est pas pertinente.

4. Défaillances simultanées (RAID 6)

Le dernier niveau de RAID prend en compte l’hypothèse d’une défaillance simultanée
d’au moins deux disques.
Le RAID 6 s’appuie sur une codification plus puissante que la parité : les codes de
Hamming ou les codes de Reed-Solomon. Ces codes permettent de reconstituer
l’information même quand plusieurs disques subissent des défaillances, le prix à payer
étant une taille plus importante que la simple parité, et donc l’utilisation de plus de
disques de contrôles.
 17

CHAPITRE III. INDEX

Un index est un objet de la base de données permettant d’accélérer l’accès aux données.
C’est un peu comme un code postal qui permet à un facteur de retrouver une adresse
rapidement ou comme une recherche de livres dans une bibliothèque ou alors comme
une recherche d’information dans un livre, voir la table d’index à la fin. Le principe est
d’aller directement à l’information souhaitée dans le cas d’un livre plutôt que de lire le
livre au complet de manière séquentielle pour trouver l’information recherchée. Le
principe de recherche dans un index se fait un peu comme dans un B-Arbre un arbre
parfaitement équilibré.

Le rôle d’un index est d’accélérer la recherche d’information (lors d’un SELECT) dans
une base une base de données.
Par défaut, TOUS les SGBD entretiennent un index primaire qui est l’index crée sur la
clé primaire (PK). Cependant les développeurs peuvent décider de créer d’autres index
sur des colonnes qui ne sont pas des PK.
 Créer des index sur les colonnes de Foreign KEY pour accélérer les jointures,
sauf si la combinaison de FK forme une clé primaire (redondance d’index).
 Créer des index sur les colonnes de la clause WHERE sauf si le WHERE contient
un like de fin (WHERE nom like ‘%CHE’), ou si le WHERE contient une
fonction.
 Créer des index sur des colonnes utilisées dans un ORDER BY, un GROUP BY,
un HAVING.
 Créer des index sur une colonne ayant une petite plage de valeurs inutiles. (NULL)
 Créer des index une fois que les insertions sont complétées.

Attention : Même si les index sont des accélérateurs, trop d’index ralenti le SGBD. Il
ne faudrait pas que le SGBD passe son temps à maintenir TOUS les index. Les index
ralentissent le système durant les insertions, car la table des index doit être mise à jour.
 18

III.1. Types d’index

MS SQL server manipule deux types d’index : CLUSTERED index et les NON
CLUSTERD index

1. Clustered index
Il existe un seul clustered index par table. Ces index stockent les lignes de données de la
table en fonction de leurs valeurs de clé. Les index clustérisés trient et stockent les lignes
de données dans la table ou la vue en fonction de leurs valeurs de clé. En principe, toutes
les tables devraient avoir un index cluster défini sur la ou les colonnes ayant la propriété
d’unicité ou de clé primaire. Par défaut lorsque SQL server crée une table avec clé
primaire, il y ajoute un CLUSTERD index.

Si vous voulez mettre un autre index Cluster sur votre table il faudra :
1. A la création de table indique que la PK n’est pas un index Cluster
Create table personnages(id int identity(1,1) not null primary key
nonclustered, alias varchar(10) NOT NULL, nom varchar(30) not null,
descriptions varchar(60) not null, typ char(1) not null );

2. Créer un Index Cluster sur la colonne que vous souhaitez

CREATE CLUSTERED INDEX INDXALIAS ON personnages(ALIAS);

Il est conseillé de créer des index clustérisés sur des :

 Des colonnes avec des valeurs uniques ou très peu de valeurs identiques.
 Colonne définie avec IDENTITY
 Colonnes fréquemment utilisées pour trier (ORDER BY) les données extraites
d’une table.
 Colonne avec accès séquentiel mais avec un where between, car un ordre est
spécifié.
 19

Encore il serait conseillé de créer des index clustérisé pour:

 les requêtes avec qui retournes une plage de valeurs : WHERE >, WHERE <,
WHERE BETWEEN
 les requêtes qui retournent un résultat volumineux
 les requêtes avec jointures
 les requêtes avec Order by et group by

Éviter les index sur les colonnes :

 Très sujettes au changement : UPDATE
 Les clés étendues (clé composée et de types varchar)

Dans les index Clustérisés le système est organisé sous forme d’arborescence binaire
parfaitement équilibré, B-Arbre. Le parcours de l’arbre est suffisant pour obtenir toute
l’information désirée.

2. Index non CLUSTERED INDEX :

Un index non-cluster contient les valeurs de clé d'index et les localisateurs de ligne qui
pointent vers l'emplacement de stockage des données de table.
Vous pouvez créer plusieurs index non cluster sur une table ou une vue indexée.
Les index non-cluster doivent, en principe, améliorer les performances des requêtes
fréquemment utilisées qui ne sont pas couvertes par l'index cluster.

Commande CREATE INDEX

Pour les index non cluster :
CREATE INDEX nomIndex ON nomTable(nomColonne);
Pour les index cluster
CREATE CLUSTERED INDEX nomIndex ON nomTable(nomColonne);
 20

En général :
CREATE [CLUSTERED] INDEX nom_de_index ON nom_table (nom_colonne)

CREATE INDEX nom_de_index ON nom_table (nom_colonne)

ALTER TABLE nom_table ADD CONSTRAINT nom_contrainte

PRIMARY KEY (nom_colonne)

ALTER TABLE nom_table ADD CONSTRAINT nom_contrainte

PRIMARY KEY NONCLUSTERED (nom_colonne)

ALTER TABLE nom_table ADD CONSTRAINT nom_constrainte

UNIQUE (nom_colonne)

ALTER TABLE nom_table ADD CONSTRAINT nom_constrainte

UNIQUE [CLUSTERED] (nom_colonne)

Suppression d’un index

DROP INDEX nom_index ON nom_table

Afficher les index définis sur une table

EXEC sys.sp_helpindex @objname = 'nom_table'
 21

CHAPITRE IV. CONCURRENCE D’ACCES

Les bases de données sont le plus souvent accessibles à plusieurs utilisateurs qui
peuvent rechercher, créer, modifier ou détruire les informations contenues dans la
base. Ces accès simultanés à des informations partagées soulèvent de nombreux
problèmes de cohérence : le système doit pouvoir gérer le cas de deux utilisateurs
accédant simultanément à une même information en vue d’effectuer des mises-à jour.
Plus généralement, on doit savoir contrôler les accès concurrents de plusieurs
programmes complexes effectuantde nombreuses lectures/écritures.
Un SGBD doit garantir que l’exécution d’un programme effectuant des mises-à-jour
dans un contexte multi utilisateurs s’effectue “correctement”. Bien entendu la
signification du “correctement” doit être définie précisément, de même que les techniques
assurant cette correction : c’est l’objet du contrôle de concurrence.

IV.1. Préliminaires
Commençons dès maintenant par un exemple illustrant le problème. Supposons que
l’application Officiel des spectacles propose une réservation des places pour une
séance. Voici le programme de réservation:

ProgrammeRESERVATION
Entrée
Une séance s
Le nombre de places souhaité NbPlaces
Le client c
debut
Lire la séance s
si (nombre de places libres> NbPlaces)
Lire le compte du spectateur c
Débiter le compte du client
Soustraire NbPlaces au nombre de places vides
Ecrire la séance s
Ecrire le compte du client c
finsi
fin
 22

Il est important de noter dès maintenant que, du point de vue du contrôle de

concurrence, des instructions comme les tests, les boucles ou les calculs n’ont pas
vraiment d’importance. Ce qui compte, ce sont les accès aux données. Ces accès sont
de deux types :
1. Les lectures, que l’on notera à partir de maintenant par r.
2. Les écritures que l’on notera w.

De plus, la nature des informations manipulées est indifférente : les règles pour le contrôle
de la concurrence sont les mêmes pour des films, des comptes en banques, des stocks
industriels, etc. Tout ceci mène à représenter un programme de manière simplifiée
comme une suite de lectures et d’écritures opérant sur des données désignées
abstraitement par des variables (généralement x, y, z, ...).
Le programme RSERVATION se représente donc simplement par la séquence
𝒓[𝒔] 𝒓[𝒄] 𝒘[𝒄] 𝒘[𝒔]

IV.2. Exécutions concurrentes: Sérialisabilité

On va maintenant s’intéresser aux exécutions d’un programme dans un contexte
multi-utilisateurs. Il pourra donc y avoir plusieurs exécutions simultanées du même
programme : pour les distinguer, on emploie simplement un indice : on parlera du
programme 𝑃1, du programme P2.

Exemple
Voici un exemple de deux exécutions concurrentes du programme RSERVATION
𝑷𝟏 et 𝑷𝟐. Chaque programme veut réserver des places dans la même séance, pour
deux clients distincts 𝑪𝟏et 𝑪𝟐

𝑟1(𝑠)𝑟1(𝑐1)𝑟2(𝑠)𝑟2(𝑐2)𝑤2(𝑠)𝑤2(𝑐2)𝑤1(𝑠)𝑤1(𝑐1)
 23

Donc on effectue d’abord les lectures pour 𝑃1, puis les lectures pour 𝑃2 enfin les
écritures pour 𝑃2 et 𝑃1 dans cet ordre. Imaginons maintenant que l’on se trouve dans
la situation suivante :
1. Il reste 50 places libres pour la séance 𝑺.
2. 𝑃1 veut réserver 5 places pour la séance 𝑺.
3. 𝑃2 veut réserver 2 places pour la séance 𝑺.

Voici le déroulement imbriqué des deux exécutions 𝑃1(𝑠, 5, 𝑐1)𝑒𝑡 𝑃2(𝑠, 2, 𝑐2), en
supposant que la séquence des opérations est celle donnée ci-dessus. On se concentre
pour l’instant sur les évolutions du nombre de places vides.
1. 𝑃1 lit 𝑺 et 𝑐1. Nb places vides : 50.
2. 𝑃2 lit 𝑺 et 𝑐2. Nb places vides : 50.
3. 𝑃2 écrit 𝑺 avec nb places =50-2=48.
4. 𝑃2 écrit le nouveau compte de 𝑐2.
5. 𝑃1 écrit 𝑺 avec nb places =50-5=45.
6. 𝑃1 écrit le nouveau compte de 𝑐1.

A la fin de l’exécution, il y a un problème : il reste 45 places vides sur les 50 initiales

alors que 7 places ont effectivement été réservées et payées. Le problème est
clairement issu d’une mauvaise imbrication des opérations de 𝑃1 et 𝑃2 : 𝑃2 lit et
modifie une information que 𝑃1 a déjà lue en vue de la modifier.
Ce genre d’anomalie est évidemment fortement indésirable. Une solution brutale est
d’exécuter en série les programmes : on bloque un programme tant que le précédent
n’a pas fini de s’exécuter.

Exemple Exécution en série de 𝑃1 et 𝑃2 :

𝑟1(𝑠)𝑟1(𝑐)𝑤1(𝑠)𝑤1(𝑐)𝑟2(𝑠)𝑟2(𝑐)𝑤2(𝑠)𝑤2(𝑐)
 24

On est assuré dans ce cas qu’il n’y a pas de problème : 𝑃2 lit une donnée écrite par
𝑃1 qui a fini de s’exécuter et ne créera donc plus d’interférence.

Cela étant cette solution de “concurrence zéro” n’est pas viable : on ne peut se
permettre de bloquer tous les utilisateurs sauf un, en attente d’un programme qui peut
durer extrêmement longtemps. Heureusement l’exécution en série est une contrainte
trop forte, comme le montre l’exemple suivant.

Exemple Exécution imbriquée de 𝑃1 et 𝑃2.

𝑟1(𝑠)𝑟1(𝑐1)𝑤1(𝑠)𝑟2(𝑠)𝑟2(𝑐2)𝑤2(𝑠)𝑤1(𝑐1)𝑤2(𝑐2)

1. 𝑃1 lit 𝒔 et 𝑐1. Nb places vides : 50.

2. 𝑃1 écrit 𝒔 avec nb places = 50-5=45.
3. 𝑃2 lit 𝒔. Nb places vides : 45.
4. 𝑃2 lit 𝑐2.
5. 𝑃2 écrit 𝒔 avec nb places =45-2=43.
6. 𝑃1 écrit le nouveau compte du client 𝑐1.
7. 𝑃2 écrit le nouveau compte du client 𝑐2.

Cette exécution est correcte. On obtient un résultat strictement semblable à celui issu
d’une exécution en série. Il existe donc des exécutions imbriquées qui sont aussi
correctes qu’une exécution en série et qui permettent une meilleure concurrence. On
parle d’exécutions sérialisables pour indiquer qu’elles sont équivalentes à des
exécutions en série. Les techniques qui permettent d’obtenir de telles exécutions
relèvent de la sérialisabilité.
 25

IV.3. Exemple de transactions concurrentes

Un SGBDR permet à plusieurs transactions d’accéder la même information en même
temps. Pour éviter que les transactions interfèrent l’une avec l’autre, des mécanismes
sont nécessaires pour contrôler l’accès aux données.

Transaction A temps Transaction B

A(a = 40) A(a = 40)

Update A set A.a = A.a – 20 where …; t1

t2 Update A set A.a = A.a – 5

where…;
Commit A(a = 20)

Commit A(a = 15)

Tableau 1: Exemple de transaction concurrente en MS Server

 26

IV.4. Exemple de perte de mise à jour

Le tableau suivant illustre un exemple d’un cas d’une perte de mise à jour.
Transaction A temps Transaction B
A(a=40) A(a=40)

Select @v=A.a from A where… t1

@v=@v-20 (@v = 20)

t2 Select @v=A.a from A where …

@v=@v-10 (@v = 30)

if @v<=15 rollback if @v<=15 rollback

Update A set A.a = @v where … t3

t4 Update A set A.a = @v where …

Commit A(a=20) t5

t6 Commit A(a = 30) → A(a = 10)

Tableau 2 : Exemple de perte de mise à jour

 27

use exemple
declare @v int
begin tran

select @v = A.a from A where id=1

set @v =@v-20
--select * from A
print @v
if @v<=15
rollback

begin tran
select @v = A.a from A where id=1
set @v = @v-10
print @v
--select * from A

if @v<=15
rollback

--select * from A

commit
--select * from A
commit

IV.5. Verrous
Le verrouillage est un mécanisme utilisé par le Moteur de base de données SQL Server
pour synchroniser l'accès simultané de plusieurs utilisateurs à la même donnée.
Avant qu'une transaction acquière une dépendance sur l'état actuel d'un élément de
données, par exemple par sa lecture ou la modification d'une donnée, elle doit se protéger
des effets d'une autre transaction qui modifie la même donnée. Pour ce faire, la
transaction demande un verrou sur l'élément de données. Le verrou possède plusieurs
modes, par exemple partagé ou exclusif. Le mode de verrouillage définit le niveau de
dépendance de la transaction sur les données.
 28

Le tableau suivant illustre les modes de verrouillage des ressources utilisés par le Moteur
de base de données.

Mode de verrouillage Description

Partagé (S) Utilisé pour les opérations de lecture qui n'effectuent

aucune modification ou mise à jour des données, par
exemple une instruction SELECT

Mise à jour (U) Utilisé pour les ressources pouvant être mises à jour.
Empêche une forme de blocage courante qui se produit
lorsque plusieurs sessions lisent, verrouillent et mettent
à jour des ressources ultérieurement.

Exclusif(X) Utilisé par les opérations de modification de données,

telles que INSERT, UPDATE ou DELETE. Empêche
des mises à jour multiples sur la même ressource au
même moment.

Tableau 3: Mode de verrouillage

 29

CHAPITRE V. TRANSACT STRUCTURED QUERY LANGAGE

Le Transact Structured Query Langage (T-SQL) est un langage de communication
avec une base de données relationnelle SQL Server. Il définit une batterie « simple »
mais complète de toutes les opérations exécutables sur une base de données (lecture
de données, opérations d’administration du serveur, ajout, suppression et mises à jour
d’objets SQL - tables, vues, procédures stockées, déclencheurs, types de données
personnalisés … -). Ce langage est composé d’instructions, réparties dans de 3
catégories distinctes :
 DML : Data Modification Language, soit langage de manipulation de
données. Dans cette catégorie, s’inscrivent les instructions telles que
l’instruction SELECT ou encore les instructions qui nous permettent la
création, la mise à jour et la suppression de données stockées dans les tables de
la base de données. Il est important de retenir que le DML sert simplement pour
les données, et en aucun cas pour la création, mise à jour ou suppression
d’objets dans la base de données SQL Server. Il utilise principalement les
instructions SELECT, INSERT, UPDATE et DELETE ; ex : select * from
produits
 DDL : Data Definition Language, soit langage de définition de données. Les
instructions de cette catégorie, permettent d’administrer la base de données,
ainsi que les objets qu’elle contient. Elles ne permettent pas de travailler sur les
données. Il utilise principalement les instructions CREATE, ALTER et DROP ;
ex : create database Test ;
 DCL : Data Control Language, soit langage de contrôle d’accès. Cette
catégorie d’instructions nous permet de gérer les accès (autorisations) aux
données, aux objets SQL, aux transactions et aux configurations générales de
la base. . Il utilise principalement les instructions GRANT, DENY et REVOKE.
Ex : GRANT select ON produits to steve

Ces trois catégories combinées permettent que le langage T-SQL prenne en compte
des fonctionnalités algorithmiques, et admette la programmabilité. Le T-SQL est non
seulement un langage de requêtage, mais aussi un vrai langage de programmation à
part entière. Sa capacité à écrire des procédures stockées et des déclencheurs
 30

(Triggers), lui permet d’être utilisé dans un environnement client de type .NET, au
travers d’une application en C#, en [Link] ou JAVA.
V.1. Expressions
Dans le T-SQL, nous pouvons utiliser des expressions, permettant de mettre en œuvre
l’aspect algorithmique du langage. Les expressions peuvent prendre plusieurs formes.
- Les constantes : une constante est une variable, dont la valeur ne peut être
changée lors de l’exécution d’instructions T-SQL.
- Les noms de colonnes : ils pourront être utilisés comme expressions. La valeur
de l’expression étant la valeur stockée dans une colonne pour une ligne donnée.
- Les variables : il s’agit d’entités qui peuvent être employées en tant
qu’expressions ou dans des expressions. Les variables sont préfixées par le
caractère @. Les variables systèmes sont préfixées par les caractères @@. La
valeur de l’expression variable est la valeur de la variable elle-même.
- Les fonctions : il est possible d’utiliser comme expression n’importe quelle
fonction. Elles permettent d’exécuter des blocs d’instructions T-SQL, et de
retourner une valeur.
- Les expressions booléennes : elles sont destinées à tester des conditions. Elles
sont utilisées dans des structures algorithmiques de type WHILE, IF ou encore
dans la clause WHERE d’une requête SQL, à affiner de permettre d’afficher
une recherche, ou bien à poser une condition d’exécution.
- Les sous-requêtes : une sous requête SELECT peut-être placée en tant
qu’expression. La valeur de l’expression est la valeur renvoyée par la requête.
 31

V.2. Notions en transact SQL

Pour les exemples de ce cours, la base de données SQL Server GestionCom qui sera
utiliséeest comme suivante :

Figure 8: Modèle Physique de Données

1. Affichage d’informations
Syntaxe :
Print Elément_A_Afficher

Exemples:
Soient @a et @b des variables de type Chaîne de caractères, @c et @d des variables de
type entier
 Print 'Bonjour' -- Affiche le texte Bonjour
 Print @a -- Affiche la valeur de @a
 Print @c -- Affiche la valeur de @c
 Print @c + @d -- Affiche la somme des variables @c et @d
 Print convert(varchar, @c) + @b

-- Affiche la valeur de @c concaténé avec la valeur de @b mais puisque @c est de type

numérique et qu'on ne peut jamais concaténer une valeur numérique avec unevaleur
chaîne de caractères, il faut passer par une fonction de conversion dont la syntaxe est la
suivante :
Convert (Type de conversion, Valeur à convertir)
 32

2. Structures alternatives
If...Else:
Syntaxe :
If Condition
Begin
Instructions
End
Else
Begin
Instructions
End

Remarques:
 Si une instruction Select apparaît dans la condition, il faut la mettre entre
parenthèses
 Si dans la clause If ou Else il existe une seule instruction, on peut omettre
le Begin et le End

Exemple :
On souhaite vérifier si le stock de l'article portant le numéro 10 a atteint son seuil
[Link] c'est le cas afficher le message 'Rupture de stock' :
Declare @QS int
Declare @SM int
Select @QS = (Select QteEnStock from article Where NumArt =10)
Select @SM = (Select SeuilMinimum from article Where NumArt =10)
If @QS<=@SM
Print 'Rupture de stock'
Else
Print 'Stock disponible'
 33

3. Instruction case

Case : Permet d'affecter, selon une condition, une valeur à un champ dans une
requête Select
Syntaxe:
Case
When Condition1 Then Résultat 1
When Condition2 Then Résultat 2
...
Else Résultat N
End

Exemple :
Afficher la liste des articles (Numéro, Désignation et prix) avec en plus une
colonne Observation qui affiche 'Non Disponible' si la quantité en stock est égale
à 0, 'Disponible' si laquantité en stock est supérieure au stock Minimum et 'à
Commander' sinon.

Select NumArt, DesArt, PUArt, 'Observation' =

Case
When QteEnStock=0 then 'Non Disponible'
When QteEnStock>SeuilMinimum then 'Disponible'
Else 'à Commander'
End
From Article

Exercices :
1. Ecrire un programme qui calcule le montant de la commande numéro 10 et affiche un
message 'Commande Normale' ou 'Commande Spéciale' selon que le montant est
inférieur ousupérieur à 100000 Fbu
2. Ecrire un programme qui supprime l'article numéro 8 de la commande numéro 5 et
met àjour le stock. Si après la suppression de cet article, la commande numéro 5 n'a plus
 34

d'articlesassociés, la supprimer.
3. Ecrire un programme qui affiche la liste des commandes et indique pour chaque
commande dans une colonne Type s'il s'agit d'une commande normale (montant
<=100000Fbu) ou d'une commande spéciale (montant > 100000 Fbu)
4.. A supposer que toutes les commandes ont des montants différents, écrire un
programme qui stocke dans une nouvelle table temporaire les 5 meilleures commandes
(ayant le montantle plus élevé) classées par montant décroissant (la table à créer aura la
structure suivante : NumCom, DatCom, MontantCom)
5. Ecrire un programme qui :
 Recherche le numéro de commande le plus élevé dans la table commande et
l'incrémente de 1
 Enregistre une commande avec ce numéro
 Pour chaque article dont la quantité en stock est inférieure ou égale au seuil
minimumenregistre une ligne de commande avec le numéro calculé et une
quantité commandée égale au triple du seuil minimum

4. Structures répétitives

Syntaxe :
While Condition
Begin
instructions
End

Remarques:
 Le mot clé Break est utilisé dans une boucle While pour forcer l'arrêt de la boucle
 Le mot clé Continue est utilisé dans une boucle While pour annuler l'itération en
cours et passer aux itérations suivantes (renvoyer le programme à la ligne du
while)
 35

Exemple:
Tant que la moyenne des prix des articles n'a pas encore atteint 3000 Fbu et le prix le
plus élevépour un article n'a pas encore atteint 30 Fbu, augmenter les prix de 10% et
afficher après chaque modification effectuée la liste des articles. Une fois toutes les
modifications effectuées, afficher la moyenne des prix et le prix le plus élevé :
While (((Select avg(puart) from article)<3000) and (select max(puart)
from article) <6000)
Begin
Update article Set puart=puart+(puart*10)/100
Select * from article
End
Select avg(puart) as moyenne , max(puart) as [Prix eleve] from article

5. Test de modification d’une colonne

L'instruction If Update renvoie une valeur true ou false pour déterminer si une
colonne spécifique d'une table a été modifié par une instruction insert ou update
(cette instruction est utilisée spécialement dans les déclencheurs et ne s'applique pas à
une instruction Delete).

Syntaxe:
If Update (Nom_Colonne)
Begin
…
End

Exemple :
If update (numCom)
Print 'Numéro de commande modifié'

6. Branchement

L'instruction Goto renvoie l'exécution du programme vers un point spécifique repéré par
une étiquette
 36

Syntaxe :
Goto Etiquette

Remarque :
Pour créer une étiquette, il suffit d'indiquer son nom suivi de deux points (:)

Exemple :
L'exemple précédent peut être écrit ainsi en utilisant l'instruction goto :
Declare @a decimal, @b decimal Etiquette_1:
Set @a= (Select avg(puart) from article) Set @b= (Select Max(puart) from
article)
If @a<20 and @b<30
Begin
Update article Set puart=puart+(puart*20)/100
Select * from article
Goto Etiquette_1
End
Select avg(puart) as moyenne , max(puart) as [Prix eleve] from article

7. Transaction
Une transaction permet d'exécuter un groupe d'instructions. Si pour une raison ou une
autrel'une de ces instructions n'a pas pu être exécutée, tout le groupe d'instructions est
annulé (letout ou rien) :
 Pour démarrer une transaction on utilise l'instruction Begin Tran
 Pour valider la transaction et rendre les traitements qui lui sont associés
effectifs, onutilise l'instruction Commit Tran
 Pour interrompre une transaction en cours qui n'a pas encore été validée, on
utilise l'instruction Rollback Tran
 Si plusieurs transactions peuvent être en cours, on peut leur attribuer des noms
pourles distinguer
 37

Syntaxe :
Begin Tran [Nom_Transaction]
…
If Condition
RollBack Tran [Nom_Transaction]
…
Commit Tran [Nom_Transaction]

Exemple :
Supposons qu'il n'existe pas de contrainte clé étrangère entre le champ NumCom de
la table LigneCommande et le champ NumCom de la Commande.
On souhaite supprimer la commande numéro 5 ainsi que la liste de ces articles. Le
programmeserait :
Delete from Commande where NumCom=5
Delete from LigneCommande where NumCom=5

Mais si, juste après l'exécution de la première instruction et alors que la deuxième n'a
pas encore eu lieu, un problème survient (une coupure de courant par exemple) la base
de données deviendra incohérente car on aura des lignes de commande pour une
commande qui n'existe pas.
En présence d'une transaction, le programme n'ayant pas atteint l'instruction Commit
Tran, aurait annulé toutes les instructions depuis Begin Tran. Le programme devra être
alors :
Begin Tran
Delete from Commande where NumCom=5
Delete from LigneCommande where NumCom=5
Commit Tran

8. Affichage des messages d’erreurs

L'instruction Raiserror affiche un message d'erreur système. Ce message est créé par
 38

l'utilisateur ou appelé à partir de la table SysMessages de la base de données Master

(table contenant la liste des messages systèmes disponibles en SQL Server).

Syntaxe :
Raiserror (Num message|Texte message, gravité, état[, Param1,
Param2... ] )

Description :
 Numéro du message : Indiquer le numéro de message pour faire appel à un
message déjà disponible dans la table SysMessages.
 Texte Message : Représente le texte du message. Pour rendre certaines parties
dumessage paramétrables, Il faut la représenter avec %d. Les valeurs à affecter
à cesparamètres seront spécifiés par l'instruction raiserror (au maximum 20
paramètrespeuvent être utilisés dans un message).
 Gravité : Représente le niveau de gravité. Seul l'administrateur système peut
ajouter des messages avec un niveau de gravité compris entre 19 et 25
(consulter l'aide Transact-SQL dans l'analyseur de requêtes SQL pour le détail
des niveaux de gravité).
 Etat : Valeur entière comprise entre 1 et 127 qui identifie la source à partir de
laquellel'erreur a été émise (consulter l'aide Transact-SQL pour le détail sur les
différents états).
 Param : Paramètres servant à la substitution des variables définies dans le
[Link] paramètres ne peuvent être que de type int, varchar, binary ou
varbinary

V.2. Curseurs
Un curseur est un groupe d'enregistrements résultat de l'interrogation d'une base de
données.L'intérêt d'utiliser des curseurs est de pouvoir faire des traitements ligne par
ligne chose qui n'est pas possible avec une requête SQL simple où un seul traitement
sera appliqué à toutes les lignes répondant à cette requête et seul le résultat final sera
visible.
 39

Il existe plusieurs types de curseurs :

 Curseurs à défilement en avant (Forward Only) : A l'ouverture du
curseur, la base de données est interrogée et la requête associée au curseur
est traitée. Une copie des enregistrements répondant aux critères demandés
est créée. De ce fait toutes les modifications effectuées sur les
enregistrements du curseur ne seront pas visibles sur la base de données
source tant que le curseur n'a pas été fermé. De même si d'autres utilisateurs
ont opéré des modifications sur la base de données source, celles-ci ne seront
visibles que si le curseur a été fermé et réouvert. Ce type de curseur
ne met, à ladisposition de l'utilisateur, q'une seule ligne à la fois. Cette ligne
peut être lue et mise à jouret l'utilisateur ne peut se déplacer que vers la ligne
suivante (accès séquentiel).
 Curseurs statiques (Static) : Ce curseur crée une copie statique de toutes
les lignes concernées de la base de données source. Les modifications
apportées ne vont être visibles que si le curseur a été fermé et ré-ouvert.
L'avantage de ce type de curseur par rapport au précédent c'est que l'accès
peut se faire à partir d'une ligne dans différents sens (MoveFirst,MoveNext,
MovePrior, MoveLast).
 Curseurs d'ensemble de valeurs clés (Keyset) : Une clé (un signet) faisant
référence à la ligne d'origine de la base de données source est créée et
enregistrée pour chaque ligne du curseur cela permet d'accéder aux données
en temps réel à la lecture ou à la manipulation d'une ligne du curseur. Le
déplacement entre les lignes dans ce genre de curseur est sans restriction
(MoveFirst, MoveNext, MovePrior, MoveLast) et la mise à jour des données
est possible. Remarque : La liste des membres est figée dès que l'ensemble
des valeurs clés est rempli.
 Curseurs dynamiques (Dynamic) : Avec ce type de curseurs, le système
vérifie en permanence si toutes les lignes vérifiant la requête du curseur sont
incluses. Ce curseur ne crée pas de clé sur les lignes ce qui le rend plus rapide
que le curseur Keyset mais il consomme plus de ressources système.
 40

Syntaxe
 Pour déclarer un curseur
Declare nom_curseur Curs Static For Select …
MUTAMA
2022-05-19 [Link]
Keyset
--------------------------------------------
Dynamic
Cursor

 Pour ouvrir un curseur

Open nom_curseur

 Pour lire un enregistrement à partir d'un curseur

Atteindre le premier enregistrement du curseur
Fetch First from nom_curseur into variable1, variable2,..

Atteindre l'enregistrement du curseur suivant celui en cours

Fetch Next from nom_curseur into variable1, variable2,...
ou Fetch nom_curseur into variable1, variable2…

Atteindre l'enregistrement du curseur précédent celui en cours

Fetch Prior from nom_curseur into variable1, variable2,...

Atteindre le dernier enregistrement du curseur

Fetch Last from nom_curseur into variable1, variable2,...

Atteindre l'enregistrement se trouvant à la position n dans le curseur

Fetch absolute n from nom_curseur into variable1,
variable2,...

Atteindre l'enregistrement se trouvant après n positions de la ligne en cours

Fetch Relative Num_Ligne from nom_curseur into variable1,
variable2...
 41

Remarque : La variable système @@fetch_status est utilisée pour détecter la fin du

[Link] que cette variable a la valeur 0, on n’a pas encore atteint la fin du curseur.
 Fermer un curseur
Close nom_curseur

 Libérer les ressources utilisées par un curseur :

Deallocate Nom_Curseur

Exemple :
Pour afficher la liste des articles sous la forme :
L'article Numéro ........ portant la désignation ………coûte …. …..

Declare @a int, @b Varchar(10), @c real

Declare Cur_ListeArt Cursor for Select NumArt, DesArt,puart from article
Open Cur_ListeArt
Fetch Next from Cur_ListeArt into @a,@b,@c
While @@fetch_status=0
Begin

Print 'L''article numéro ' + convert(varchar,@a) + ' portant la

désignation ' + @b+ ' coûte ' + convert(varchar,@c)
Fetch Next from Cur_ListeArt into @a,@b,@c
End
Close Cur_ListeArt
Deallocate Cur_ListeArt
 42

Exercices
1- Ecrire un programme qui pour chaque commande :
 Affiche le numéro et la date de commande sous la forme :
Commande N° : ……Effectuée le : …
 La liste des articles associés
 Le montant de cette commande
2- Ecrire un programme qui pour chaque commande vérifie si cette commande
a au moinsun article. Si c'est le cas affiche son numéro et la liste de ses articles
sinon affiche un message d'erreur : Aucun article pour la commande …. Elle
sera supprimée et supprime cette commande

V.3. Procédures stockées

Les procédures stockées permettent d'automatiser des actions qui peuvent être très
complexes.
Une procédure stockée est en fait une série d'instructions SQL désignée par un nom.
Lorsque l'on crée une procédure stockée, on l'enregistre dans la base de données que
l'on utilise, au même titre qu'une table, par exemple. Une fois la procédure créée, il
est possible d'appeler celle-ci par son nom. Les instructions de la procédure sont alors
exécutées.
Contrairement aux requêtes préparées, qui ne sont gardées en mémoire que pour la
session courante, les procédures stockées sont, comme leur nom l'indique, stockées
de manière durable, et font bien partie intégrante de la base de données dans
laquelle elles sont enregistrées.

V.3.1. Création d'une procédure stockée

1. Procédure stockée sans paramètres
La procédure stockée exécute un traitement donné mais ce traitement ne dépend
d'aucune valeur provenant de l'application appelante.
 43

Syntaxe:
Create Procedure Nom_Procédure as
Instructions

Exécution :
Exec Nom_Procedure

Exemples :
1. Créer une procédure stockée nommée SP_Articles qui affiche la liste des articles avec
pour chaque article le numéro et la désignation :

Create Procedure SP_Articles as Select NumArt, DesArt from Article

--Exécuter cette procédure :

Exec SP_Articles

2. Créer une procédure stockée qui calcule le nombre d'articles par commande :

2. Procédure stockée avec des paramètres en entrée

La procédure stockée en fonction de valeurs provenant de l'extérieur va effectuer certains
traitements et donc il n'est pas normal qu'une procédure stockée reçoive des paramètres
en entrée dont les valeurs ne soient pas exploitées dans les instructions des procédures
(dans des tests, dans des conditions…)

Syntaxe :
Create Procedure Nom_Propriétaire.Nom_Procedure Nom_Param1_Entrée
Type_Donnée = Valeur_Par_Defaut, Nom_Param2_Entrée Type_Donnée =
Valeur_Par_Defaut… as
Instructions
 44

Exécution :
Exec Nom_Procedure Valeur_Param1, Valeur_Param2... Ou
Exec Nom_Procedure Nom_Param1 = Valeur_Param1, Nom_Param2 =
Valeur_Param2...

Remarque:
Avec la deuxième syntaxe, l'utilisateur n'est pas obligé de passer les paramètres dans
l'ordre eten plus si des paramètres ont des valeurs par défaut, il n'est pas obligé de les
passer.

Exemples :
1. Créer une procédure stockée nommée SP_ListeArticles qui affiche la liste des
articlesd'une commande dont le numéro est donné en paramètre :
Create Procedure SP_ListeArticles @NumCom int as Select [Link],
DesArt, PUArt, QteCommandee From Article A, LigneCommande LC
Where [Link]=[Link] and [Link]=@NumCom

--Exécuter cette procédure pour afficher la liste des articles de la

commande numéro 1 :
Exec SP_ListeArticles 1
--Ou
Declare @nc int
Set @nc=1
Exec SP_ListeArticles @nc
2. Créer une procédure stockée nommée SP_ComPeriode qui affiche la liste des
commandes effectuées entre deux dates données en paramètre :

3. Créer une procédure stockée nommée SP_TypeComPeriode qui affiche la liste des
commandes effectuées entre deux dates passées en paramètres. En plus si le
nombre de ces commandes est supérieur à 100, afficher 'Période rouge'. Si le
nombre de ces commandes est entre 50 et 100 afficher 'Période jaune' sinon
afficher 'Période blanche'(exploiter la procédure précédente) :
 45

4. Créer une procédure stockée nommée SP_EnregistrerLigneCom qui reçoit un numéro

de commande, un numéro d'article et la quantité commandée :
 Si l'article n'existe pas ou si la quantité demandée n'est pas disponible
afficher un message d'erreur
 Si la commande introduite en paramètre n'existe pas, la créer
 Ajoute ensuite la ligne de commande et met le stock à jour

3. Procédure stockée avec des paramètres en sortie

La procédure stockée suite à un traitement réalisé va attribuer des valeurs à des
paramètres en sortie. Les valeurs de ces paramètres peuvent être récupérées par des
applications clientes. Il n'est pas normal qu'une procédure stockée contenant des
paramètres de sortie n'affecte pas devaleurs à ces paramètres avant la fin du traitement.

Remarque :
Les procédures utilisant des paramètres de sortie peuvent avoir ou ne pas avoir (selon
lebesoin) des paramètres en entrée
Syntaxe :
Create Procedure Nom_Propriétaire.Nom_Procedure
Nom_Param1_Entrée Type_Donnée = Valeur_Par_Defaut,
Nom_Param1_Entrée Type_Donnée = Valeur_Par_Defaut,…
Nom_Param1_Sortie Type_Donnée Output, Nom_Param2_Sortie
Type_Donnée Output...
as
Instructions

--Exécution :
Declare Var_Param1_Sortie Type_Param1_Sortie
Declare Var_Param2_Sortie Type_Param2_Sortie
...
Exec Nom_Procedure Val_Param1_Entrée, Val_Param2_Entrée...,
Var_Param1_Sortie Output, Var_Param2_Sortie Output...
 46

Exemples :
1. Créer une procédure stockée nommée SP_NbrCommandes qui retourne le nombre
decommandes :
Create Procedure SP_NbrCommandes @Nbr int output as Set @Nbr = (Select
count(NumCom) from Commande)

--Exécuter cette procédure pour afficher le nombre de commandes :

Declare @n int
Exec SP_NbrCommandes @n Output
Print 'Le nombre de commandes : ' + convert(varchar,@n)

2. Créer une procédure stockée nommée SP_NbrArtCom qui retourne le nombre

d'articles d'une commande dont le numéro est donné en paramètre :

4. Procédure stockée avec valeur de retour

L'instruction return arrête l'exécution d'une procédure stockée. Une valeur entière
représentant, en général, l'état d'exécution d'une procédure peut être associée à
l'instruction return. Cette valeur peut être récupérée par le programme appelant de la
procédure stockée.
Remarque :
 Les procédures ayant une valeur de retour peuvent avoir ou ne pas avoir
(selon lebesoin) des paramètres en entrée ou des paramètres de sortie ;
 Pour détecter les erreurs système, SQL Server offre la variable globale
@@ERROR qui retourne le code d'erreur déclenché par SQL Server. Si la
valeur de cette variableest 0 c'est qu'aucune erreur n'a été générée.
 47

Syntaxe :
Create Procedure Nom_Propriétaire.Nom_Procedure
...
as
Instructions
...
Return Valeur_Sortie

Exécution :
Declare Var_Retour Type_Var_Retour
...
Exec Var_Retour=Nom_Procedure …

Exemple :
Créer une procédure stockée nommée SP_TypePeriode qui renvoie un code de retour.
Si le nombre de commandes est supérieur à 100, la procédure renvoie 1. Si le nombre
de commandes est entre 50 et 100, la procédure renvoie 2. Si le nombre de commandes
est inférieur à 50, la procédure renvoie 3. Si une erreur système a lieu, la procédure
renvoie 4 :
Create Procedure SP_TypePeriode as Declare @NbrCom int
Set @NbrCom = (Select count(NumCom) from Commande)
If @NbrCom >=100
Return 1
If @NbrCom >50
Return 2
If @NbrCom <=50
Return 3
If @@ERROR <>0
Return 4

declare @an int

exec @an=SP_TypePeriode
print @an
 48

Suppression d’une procédure stockée

Syntaxe :
Drop Procedure Nom_Procédure

Exemple :
Drop Procedure NbrArticlesCommande

V.4. Fonctions
1. Fonction qui retourne une valeur scalaire
Syntaxe :
Create Function Nom_Procedure (Nom_Param1 Type_Donnée, …)
Returns type_de_retour
as
Instructions
...
Return Valeur

Exemple :
Créer une fonction nommée F_NbrCommandes qui retourne le nombre de commandes
Create Function F_NbrCommandes() Returns int
as begin
declare @Nbr int
Set @Nbr = (Select count(NumCom) from Commande)
Return @Nbr
end
 49

2. Fonction qui retourne une table

Syntaxe :
Create Function Nom_Fonction (Nom_Param1 Type_Donnée, …)
Returns nom_table
Table ( champ1 type1, …)
as
Select …
Return

Exemple :
Créer une fonction nommée F_ListeArticles qui return la liste des articles d'une
commandedont le numéro est donné en paramètre :

Create Function F_ListeArticles (@NumCom int ) Returns Liste-Art Table (

Num int , nom varchar(29))
as
Select [Link], NomArt From Article A, LigneCommande LC
Where [Link]=[Link] and [Link]=@NumCom

--Exécuter cette procédure pour afficher la liste des articles de la

commande numéro 1 :
Select * from F_ListeArticles (1)

V.5. Déclencheurs (Triggers)

Les triggers peuvent intercepter les opérations sur les données de la table avant
qu'elles ne soient définitivement appliquées. Ils peuvent alors interrompre les
traitements de mise à jouret selon certaines conditions annuler ces modifications, leur
associer des traitements complémentaires ou laisser le système poursuivre leur
validation.
 50

Les déclencheurs peuvent être associés à trois types d'actions de déclenchement sur
une table :
 Déclencheurs d'insertion : Se déclenchent suite à une opération d'ajout
d'enregistrements dans la table ;
 Déclencheurs de modification : Se déclenchent suite à une opération de
modification des enregistrements de la table ;
 Déclencheurs de suppression : Se déclenchent suite à une opération de
suppression d'enregistrements à partir de la table.

Les triggers consomment peu de ressources système à condition qu'ils n'utilisent pas
decurseurs.

V.5.1. Types de Triggers

Les déclencheurs peuvent être de deux types : INSTEAD OF et AFTER.
 Les déclencheurs INSTEAD OF :
 Sont exécutés à la place de l'action de déclenchement ;

 Sont vérifiés avant les contraintes d'intégrité associées à la table ce qui

permet demettre en place des traitements qui complètent les actions de ces
contraintes ;
 Peuvent être associés aussi bien à des tables qu'à des vues ce qui permet
la mise à jour des données associées à ces vues ;
 Ne peuvent être associés à des tables cible de contraintes d'intégrité
référentielle en cascade ;
 Un seul déclencheur INSTEAD OF est autorisé par action de
déclenchement dans une table
 Même si un trigger INSTEAD OF contient une action d'insertion sur la
table ou la vue à laquelle il est associé, il ne sera jamais exécuté à nouveau
(exécution non récursive).
 51

 Les déclencheurs AFTER :

 Sont exécutés après la validation des contraintes associées à la table. Si
une contrainte n'est pas vérifiée ce type de déclencheurs ne se déclenchera
jamais ;
 Ne peuvent être associés qu'à des tables ;

 Plusieurs déclencheurs AFTER sont autorisés sur une même table et pour
une même action de déclenchement. La procédure stockée système
sp_SetTriggerOrder permet de spécifier le premier et le dernier
déclencheur à exécuter pour une action :

Exec sp_SetTriggerOrder
@triggername = 'MyTrigger',
@order = 'first|Last|None',
@stmttype = 'Insert|Update|Delete'

V.5.2. Fonctionnement des tables inserted et deleted

Au cours des opérations d'ajout, de suppression et de modification, le système utilise
les tables temporaires inserted et deleted. Ces tables ne sont accessibles qu'au niveau
des triggers et leur contenu est perdu dès que les triggers sont validés.
 Action d'ajout : Les enregistrements ajoutés sont placés dans une table
temporaire nomméeinserted ;
 Action de suppression : Les enregistrements supprimés sont placés dans une
table temporaire nommée deleted.
 Action de modification : L'opération de modification est interprétée comme
une opération de suppression des anciennes informations et d'ajout des
nouvelles informations. C'est pourquoi le système utilise dans ce cas les deux
tables temporaires deleted et inserted. En fait quand un utilisateur demande à
modifier des enregistrements, ceux-ci sont d'abord sauvegardés dans la table
temporaire deleted etla copie modifiée est enregistrée dans la table inserted.
 52

V.5.3. Fonctionnement des Triggers INSTEAD OF et AFTER

 Cas où seul un trigger INSTEAD OF est associé à l'action de mise à jour (insert,
deleteou update) : Dans le trigger INSTEAD OF, les enregistrements ajoutés
(respectivement modifiés ou supprimés) apparaissent uniquement dans les tables
temporaires mais pas dans la table d'origine et si le code associé à ce trigger ne
prend pas en charge l'ajout (respectivement la modification ou la suppression) de
ces enregistrements, ils ne seront pas ajoutés (respectivement modifiés et
supprimés)même si aucune action n'annule le déclencheur.

Exemple :
Un utilisateur exécute l'action suivante :
Insert into commande values (100,'2007-09-13')

Supposons qu'un trigger instead of est associé à l'action d'insertion sur la table
commande. Dans le corps de ce trigger, on affiche le contenu de la table inserted et le
contenu de la table commande.
Dans la table inserted, on remarquera la présence de la commande numéro 100 mais
dans la table commande cet enregistrement est absent et ne sera pas ajouté à la
table commande même après la fin de l'exécution de l'action d'ajout. Ceci est dû au
fait que l'exécution des triggers instead of remplace l'action de déclenchement.

 Cas où seul des triggers AFTER sont associés à l'action de mise à jour (insert,
deleteou update) : Les contraintes sont testées en premier. Si une contrainte n'est
pas vérifiée l'insertion est annulée sans que le trigger soit exécuté. Si les
contraintes sont vérifiées, le trigger est exécuté. Les enregistrements ajoutés
apparaissent et dans la table d'origine et dans les tables temporaires concernées
par l'action. Si dans le code associé à ce trigger, aucune action n'annule la
transaction, l'opération est validée.

 Cas où un trigger INSTEAD OF ainsi que des triggers AFTER sont associés à
l'action de mise à jour (insert, delete ou update) : Le trigger INSTEAD OF est
exécuté en premier, les enregistrements concernés par l'action de mise à jour
 53

(insert, delete ou update) apparaissent uniquement dans les tables temporaires

mais pas dans la table d'origine et si le code associé à ce trigger ne prend pas en
charge les opérations sur ces enregistrements, ils ne seront pas ajoutés (modifiés
ou supprimés) même si aucune action n'annule le trigger et les triggers AFTER ne
seront pas exécutés.

Si le trigger INSTEAD OF, déclenche une opération (ajout, modification ou

suppression) sur la même table, les triggers AFTER vont se déclencher et les tables
temporaires au sein de ces triggers vont contenir les nouvelles valeurs manipulées.
Si d'autres instructions se trouvent après l'instruction de mise à jour (insert, delete ou
update) dans le trigger instead of, elles seront exécutées après la fin de l'exécution des
triggers After sauf si une instruction Rollback a été rencontrée.

1. Création d’un déclencheur (Trigger)

Syntaxe :
Create Trigger Nom_Trigger
On Nom_Table
Instead Of | For Opération1, Opération2...
As

1. Instructions
Remarque :
 Opération peut prendre Insert, Delete ou Update selon le type de trigger à créer
 Un même trigger peut être associé à une seule opération ou à plusieurs
opérations à la fois
 A chaque table, peuvent être associées trois triggers au maximum : ajout,
modificationet suppression (un trigger concernant deux opérations est compté
comme deux triggers)
 Le corps du trigger créé peut inclure n'importe quelles instructions excepté
Create Database, Alter Database, Drop Database, Restore Database, Restore
 54

Log et reconfigure ;

Exercices :
1. Le trigger suivant interdit la modification des commandes
Create Trigger Tr_Empêcher_Modif
On Commande
For Update As Rollback

2. Le trigger suivant interdit la modification du numéro de commande et vérifie si la

date saisie pour la date de commande est supérieure ou égale à la date du jour

3. Le trigger suivant empêche la suppression des commandes ayant des articles

associés
Remarque : Ce trigger ne se déclenchera pas s'il existe une contrainte clé étrangère
entre le champ NumCom de la table ligneCommande et le champNumCom de la table
commande.

4. Le trigger suivant à la suppression d'une ligne de commande, remet à jour le stock

et vérifie s'il s'agit de la dernière ligne pour cette commande. Si c'est le cas la
commande est supprimée :

5. Le trigger suivant à l'ajout d'une ligne de commande vérifie si les quantités sont
disponibles et met le stock à jour

Remarque :
Si le trigger déclenché effectue une opération sur une autre table, les triggers associés
à cettetable sont alors déclenchés (principe de cascade)
 55

2. Suppression d’un déclencheur(Trigger)

Syntaxe :
Drop Trigger Nom_Trigger

3. Modification d’un déclencheur(Trigger)

Syntaxe :
Alter Trigger Nom_Trigger
On Nom_Table
For Opération1, Opération2...
as
Nouvelles Instructions

V.6. Vue
Une vue est une des fonctionnalités les plus remarquables des SGBD relationnels.
Elle suggère la possibilité d’ajouter au schéma des tables ’virtuelles’ qui ne sont rien
d’autres que le résultat de requêtes stockées. De telles tables sont nommées des vues
dans la terminologie relationnelle. On peut interroger des vues comme des tables
stockées. Une vue n’induit aucun stockage puisqu’elle n’existe pas physiquement, et
permet d’obtenir une représentation différente des tables sur lesquelles elle est basée.

V.6.1. Création et interrogation d’une vue

Une vue est tout point comparable à une table : en particulier on peut l’interroger par
SQL. La grande différence est qu’une vue est le résultat d’une requête, avec la
caractéristique essentielle que ce résultat est réévalué à chaque fois que l’on accède à
la vue. En d’autre termes une vue est dynamique : elle donne une représentation fidèle
de la base au moment de l’évaluation de la requête.
Une vue est essentiellement une requête à laquelle on a donné un nom. La syntaxe de
création d’une vue est très simple :
CREATE VIEW <nom-vue> AS <requête> [WITH CHECK OPTION]
 56

Les exemples vont se baser sur le schéma « Officiel » ci-dessous.

 Cinema (nomCinema, numero, rue, ville)
 Salle (nomCinema, no, capacite, climatisee)
 Horaire (idHoraire, heureDebut, heureFin)
 Seance (idFilm, nomCinema, noSalle, idHoraire, tarif)
 Film (idFilm, titre, annee, genre, resume, idMES)
 Artiste (id, nom, prenom, anneeNaissance)
 Role (idActeur, idFilm, nomRole)

Exemple : on peut créer une vue qui ne contient que les cinémas parisiens :
CREATE VIEW ParisCinemas
AS SELECT * FROM Cinema WHERE ville = ’Paris’

On peut aussi en profiter pour restreindre la vision des cinémas parisiens à leur nom
et à leur nombre de salles.
CREATE VIEW SimpleParisCinemas AS SELECT nom, COUNT(*) AS nbSalles
FROM Cinema c, Salle s WHERE ville = ’Paris’ AND [Link] = [Link]
GROUP BY [Link]

Enfin un des intérêts des vues est de donner une représentation dénormalisée de la
base, en regroupant des informations par des jointures. Par exemple on peut créer une
vue Casting donnant explicitement les titres des films, leur année et les noms et
prénoms des acteurs.
CREATE VIEW Casting (film, annee, acteur, prenom) AS SELECT titre,
annee, nom, prenom FROM Film f, Role r, Artiste a WHERE [Link] =
[Link] AND [Link] = [Link]

Remarque : on a donné explicitement des noms d’attributs au lieu d’utiliser les

attributs de la clause SELECT.
Maintenant, on peut utiliser les vues et les tables dans des requêtes SQL. Par exemple
la requête « Quels acteurs ont tourné un film en 1997 » s’exprime par :
 57

SELECT acteur, prenom FROM Casting WHERE annee = 1997

On peut ensuite donner des droits en lecture sur cette vue pour que cette information
limitée soit disponible à tous.
GRANT SELECT ON Casting TO PUBLIC

V.6.2. Mise à jour d’une vue

L’idée de modifier une vue peut sembler étrange puisqu’une vue n’a pas de contenu.
En fait il s’agit bien entendu de modifier la table qui sert de support à la vue. Il existe
de sévères restrictions sur les droits d’insérer ou de mettre-à-jour des tables à travers
les vues. Un exemple suffit pour comprendre le problème.
Imaginons que l’on souhaite insérer une ligne dans la vue Casting.
INSERT INTO CASTING (film, annee, acteur, prenom) VALUES (’Titanic’,
1998, ’DiCaprio’, ’Leonardo’);

Cet ordre s’adresse à une vue issue de trois tables. Il n’y a clairement pas assez
d’information pour alimenter ces tables de manière cohérente, et l’insertion n’est pas
possible (de même que toute mise à jour). De telles vues sont dites non modifiables.
Les règles définissant les vues modifiables sont très strictes.
1. La vue doit être basée sur une seule table.
2. Toute colonne non référencée dans la vue doit pouvoir être mise à NULL ou
disposer d’une valeur par défaut.
3. On ne peut pas mettre-à-jour un attribut qui résulte d’un calcul ou d’une
opération.

Il est donc tout à fait possible d’insérer, modifier ou détruire la table Film au travers
de la vue ParisCinema.

INSERT INTO ParisCinema

 58

VALUES (1876, ’Breteuil’, 12, ’Cite’, ’Lyon’)

En revanche, en admettant que la ville est définie à NOT NULL, il n’est pas possible
d’insérer dans SimpleParisCinemas.
L’insertion précédente illustre une petite subtilité : on peut insérer dans une vue, sans
être en mesure de voir la ligne insérée au travers de la vue par la suite ! Afin d’éviter
ce genre d’incohérence, SQL2 propose l’option WITH CHECK OPTION qui permet
de garantir que toute ligne insérée dans la vue satisfait les critères de sélection de la
vue.

CREATE VIEW ParisCinemas AS SELECT * FROM Cinema WHERE ville =

‘Paris’ WITH CHECK OPTION

L’insertion donnée en exemple ci-dessus devient impossible. Enfin on détruit une vue
avec la syntaxe courante SQL :
DROP VIEW ParisCinemas
 59

CHAPITRE VI. MAPPING OBJET/RELATIONNEL

VI.1. Introduction
Par définition, "un mapping objet-relationnel est une technique de programmation
informatique qui crée l'illusion d'une base de données orientée objet à partir d'une base
de données relationnelle en définissant des correspondances entre cette base de
données et les objets du langage utilisé."

VI.2. Logiciel Mapping Objet/Relationnel

Un logiciel de Mapping Objet / Relationnel est une couche de persistance connectant
les objets d'un système orienté-objet à des données stockées dans une base de données
relationnelle. Pour simplifier, on peut dire que le Mapping
Objet/Relationnel consiste à modéliser, de façon objet, une base de données. Ainsi,
les tables de la base de données, deviennent des classes, avec une liste de propriétés
qui correspondent aux colonnes de la table.
Une couche de persistance, c'est en quelque sorte une surcouche à votre projet, qui
se chargera de représenter vos tables en classes.
C'est grâce à cette couche de persistance que vous allez pouvoir appeller des méthodes
de votre classe, qui seront en fait des méthodes agissant directement sur votre base de
données (méthodes Save, Update, etc...).
Vous avez donc la possibilité de créer cette couche de persistance vous-même: pour
chaque table de la base de données, vous créez la classe associée (en prenant garde de
bien respecter les clés primaires, les identifiants auto-générés, etc..).

VI.3. Entités
Les entités dans les spécifications de l'API Java Persistence permettent d'encapsuler
les données d'une occurrence d'une ou plusieurs tables. Ce sont de simples POJO
(Plain Old Java Object). Un POJO est une classe Java qui n'implémente aucune
interface particulière ni n'hérite d'aucune classe mère spécifique. Un objet Java de type
POJO mappé vers une table de la base de données grâce à des méta data via l'API Java
Persistence est nommé bean entité (Entity bean). Un bean entité doit obligatoirement
avoir un constructeur sans argument et la classe du bean doit obligatoirement être
 60

marquée avec l'annotation @[Link]. Cette annotation possède un

attribut optionnel nommé name qui permet de préciser le nom de l'entité dans les
requêtes. Par défaut, ce nom est celui de la classe de l'entité.
En tant que POJO, le bean entity n'a pas à implémenter d'interface particulière mais il
doit respecter les règles de tous Java beans :
 Être déclaré avec l'annotation @[Link]
 Posséder au moins une propriété déclarée comme clé primaire avec l'annotation
@Id
Le bean entity est composé de propriétés qui seront mappées sur les champs de la table
de la base de données sous-jacente. Chaque propriété encapsule les données d'un
champ d'une table. Ces propriétés sont utilisables au travers de simples accesseurs
(getter/setter).
Une propriété particulière est la clé primaire qui sert d'identifiant unique dans la base
de données mais aussi dans le POJO. Elle peut être de type primitif ou de type objet.
La déclaration de cette clé primaire est obligatoire.

VI.4. Mapping entre le bean entité et la table

La description du mapping entre le bean entité et la table peut être faite de deux façons
:
 Utiliser des annotations
 Utiliser un fichier XML de mapping
L'API propose plusieurs annotations pour supporter un mapping O/R assez
complet.

Annotation Rôle
@[Link] : Préciser le nom de la table concernée par le
mapping

@[Link] : Associer un champ de la table à la propriété (à

utiliser sur un getter)
 61

@[Link] : Associer un champ de la table à la propriété en

tant que clé primaire (à utiliser sur un getter)

@[Link] : Demander la génération automatique de la clé

primaire au besoin

@[Link] : Représenter la forme de mapping la plus simple.

Cette annotation est utilisée par défaut

@[Link] : Demander de ne pas tenir compte du champ lors

du mapping

L'annotation @[Link] permet de lier l'entité à une table de la base de

données. Par défaut, l'entité est liée à la table de la base de données correspondant au
nom de la classe de l'entité. Si ce nom est différent alors l'utilisation de l'annotation
@Table est obligatoire. C'est notamment le cas si des conventions de nommage des
entités de la base de données sont mises en place.

L'annotation @Table possède plusieurs attributs :

Attributs : Rôle

name : Nom de la table

Catalog : Catalogue de la table

Schema : Schéma de la table

uniqueConstraints : Contraintes d'unicité sur une ou plusieurs colonnes

L'annotation @[Link] permet d'associer un membre de l'entité à

une colonne de la table. Par défaut, les champs de l'entité sont liés aux champs de la
table dont les noms correspondent. Si ces noms sont différents alors l'utilisation de
l'annotation @Column est obligatoire. C'est notamment le cas si des conventions de
nommage des entités de la base de données sont mises en place.
 62

VI.5. EntityManager
Les interactions entre la base de données et les beans entités sont assurées par un objet
de type [Link] : il permet de lire et rechercher des données
mais aussi de les mettre à jour (ajout, modification, suppression). L'EntityManager est
donc au coeur de toutes les actions de persistance.
Les beans entités étant de simple POJO, leur instanciation se fait comme pour tout
autre objet Java. Les données de cette instance ne sont rendues persistantes que par
une action explicite demandée à l'EntityManager sur le bean entité.
L'EntityManager assure aussi les interactions avec un éventuel gestionnaire de
transactions.
Un EntityManager gère un ensemble défini de beans entités nommé persistence unit.
La définition d'un persistence unit est assurée dans un fichier de description nommé
[Link].

1. Obtention d'une instance de la classe EntityManager

Lors d'une utilisation dans un conteneur Java EE, il est possible d'obtenir un objet de
type EntityManager en utilisant l'injection de dépendance pour l'objet lui-même ou
d'obtenir une fabrique de type EntityManagerFactory qui sera capable de créer l'objet.
Dans un environnement Java SE, comme par exemple dans Tomcat ou dans une
application de type client lourd, l'instanciation d'un objet de type EntityManager doit
être codée.
Sous Java SE, pour obtenir une instance de type EntityManager, il faut utiliser une
fabrique de type EntityManagerFactory. Cette fabrique propose la méthode
createEntityManager() pour obtenir une instance.
Pour obtenir une instance de la fabrique, il faut utiliser la méthode statique
createEntityManagerFactory() de la classe [Link] qui attend en
paramètre le nom de l'unité de persistence à utiliser. Elle va rechercher le fichier
[Link] dans le classpath et recherche dans ce fichier l'unité de persistance
dont le nom est fourni.
Pour libérer les ressources, il faut utiliser la méthode close() de la fabrique une fois
que cette dernière n'a plus d'utilité.
 63

Sous Java EE, il est préférable d'utiliser l'injection de dépendance pour obtenir une
fabrique ou un contexte de persistance.
L'annotation @[Link] sur un champ de type
EntityManagerFactory permet d'injecter une fabrique. Cette annotation possède un
attribut unitName qui précise le nom de l'unité de persistance.

Exemple :
@PersistenceUnit(unitName="MaBaseDeTestPU")

private EntityManagerFactory factory;

Il est alors possible d'utiliser la fabrique pour obtenir un objet de type EntityManager
qui encapsule un contexte de persistence de type extended. Pour associer ce contexte
à la transaction courante, il faut utiliser la méthode joinTransaction().
La méthode close() est automatiquement appelée par le conteneur : il ne faut pas
utiliser cette méthode dans un conteneur sinon une exception de type
IllegalStateException est levée.
L'annotation @[Link] sur un champ de type
EntityManager permet d'injecter un contexte de persistance. Cette annotation
possède un attribut unitName qui précise le nom de l'unité de persistance.
Exemple :
@PersistenceContext(unitName="MaBaseDeTestPU")
private EntityManager entityManager;

2. Classe EntityManager
La méthode contains() de l'EntityManager permet de savoir si une instance fournie en
paramètre est gérée par le contexte. Dans ce cas, elle renvoie true, sinon elle renvoie
false.
La méthode clear() de l'EntityManager permet de détacher toutes les entités gérées par
le contexte. Dans ce cas, toutes les modifications apportées aux entités sont perdues :
 64

il est préférable d'appeler la méthode flush() avant la méthode clear() afin de rendre
persistante toutes les modifications.
L'appel des méthodes de mise à jour persist(), merge() et remove() ne réalise pas
d'actions immédiates dans la base de données sous-jacente. L'exécution de ces actions
est à la discrétion de l'EntityManager selon le FlushModeType (AUTO ou COMMIT).
Dans le mode AUTO, les mises à jour sont reportées dans la base de données avant
chaque requête. Dans le mode COMMIT, les mises à jour sont reportées dans la base
de données lors du commit de la transaction.
Le mode COMMIT est plus performant car il limite les échanges avec la base de
données.
Il est possible de forcer l'enregistrement des mises à jour dans la base de données en
utilisant la méthode flush() de l'EntityManager.

3. Classe EntityManager pour la création d'une occurrence

Pour insérer une nouvelle entité dans la base de données, il faut :
 Instancier une occurrence de la classe de l'entité
 Initialiser les propriétés de l'entité
 Définir les relations de l'entité avec d'autres entités si besoin
 Utiliser la méthode persist() de l'EntityManager en passant en paramètre
l'entité

4. Classe EntityManager pour rechercher des données par requête

La recherche par requête repose sur des méthodes dédiées de la classe EntityManager
(createQuery(), createNamedQuery() et createNativeQuery()) et sur un langage de
requête spécifique nommé EJB QL.
L'objet Query encapsule et permet d'obtenir les résultats de son exécution. La méthode
getSingleResult() permet d'obtenir un objet unique retourné par la requête.
La méthode getResultList() renvoie une collection qui contient les éventuelles
occurrences retournées par la requête.
 65

L'objet Query gère aussi des paramètres nommés dans la requête. Le nom de chaque
paramètre est préfixé par « : » dans la requête. La méthode setParameter() permet de
fournir une valeur à chaque paramètre.

5. Classe EntityManager pour modifier une occurrence

Pour modifier une entité existante dans la base de données, il faut :
 Obtenir une instance de l'entité à modifier (par recherche sur la clé primaire ou
l'exécution d'une requête)
 Modifier les propriétés de l'entité
 Selon le mode de synchronisation des données de l'EntityManager, il peut être
nécessaire d'appeler la méthode flush() explicitement.

6. Classe EntityManager pour fusionner des données

L'EntityManager propose la méthode merge() pour fusionner les données d'une entité
non gérée avec la base de données. Ceci est particulièrement utile notamment lorsque
l'entité est sérialisée pour être envoyée au client : dans ce cas, l'entité n'est plus gérée
par le contexte. Lorsque le client renvoie l'entité modifiée, il faut synchroniser les
données qu'elle contient avec celles de la base de données. C'est le rôle de la méthode
merge().

7. Classe EntityManager pour supprimer une occurrence

Pour supprimer une entité existante dans la base de données, il faut :
Obtenir une instance de l'entité à supprimer (par recherche sur la clé primaire ou
l'exécution d'une requête)
Appeler la méthode remove() de l'EntityManager en lui passant en paramètre
l'instance de l'entité

EXERCICE D’APPLICATION
 66

REFERENCES BIBLIOGRAPHIQUES
[1] Jarke M., Koch J., « Query Optimization in Database Systems » ACM Computing
Surveys, vol. 16, n° 2, p. 111-152, Juin 1984.
[2] Kim Won, Reiner S., Batory D., Query Processing in Database Systems, Springer-
Verlag Ed., 1985.
[3] Graefe G., McKenna W., « The Volcano Optimizer Generator », Proceedings of
the 9th International Conference on Data Engineering, IEEE Ed., p. 209-218, 1993.
[4] Gray J. Ed., The Benchmark Handbook , Morgan & Kaufman Pub., San Mateo,
1991.
[5] Selinger P., « Access Path Selection in a Relational Database Management System
», ACM SIGMOD Intl. Conf. On Management of Data, Boston, Mai 1979.
[6] Transaction Processing Council, Benchmark TPC/D, San Fransisco, CA, 1995.
[7]Wikipedia,"Mapping objet-relationnel", juin 2016: [Link]
Mapping_objet-relationnel
[8]Carrez C., Des Structures aux Bases de Données, Masson
[9] Gardarin G., Maîtriser les Bases de Données: modèles et langages, Eyrolles
[10] Marcenac, P., SGBD relationnels, Optimisation des performances, Eyrolles.
[11] Date C.J., An Introduction to Database Systems, 5e edition, The Systems
Programming Series, volumes I (854 pages) et II (383 pages), Addison Wesley, 1990.
[12] Delobel C., Lécluse Ch., Richard Ph., Bases de Données : Des Systèmes
Relationnels aux Systèmes à Objets, 460 pages, InterÉditions, Paris, 1991.
[13] Gardarin G., Gardarin O., Le Client-Serveur, 470 pages, Éditions Eyrolles, 1997.
[14] Gray J. Ed., The Benchmark Handbook, Morgan & Kaufman Pub., San Mateo,
1991.
[15] Silberschatz A., Korth H., Sudarshan S., Database System Concepts, 819 pages,
Mc Graw-Hill Editions, 3e edition, 1997.