Administration et optimisation

des bases de données

Rappels
ID1 - S2, ENSAH
Y. EL MORABIT
Plan
 Rappels
 Administration des bases de données
 Optimisation des bases de données
Plan
 Rappels
 Les dépendances fonctionnelles
 Les formes normales
 Normalisation et dénormalisation
 Les contraintes d’intégrité
 Algèbre relationnelle
 Base de Données
 Langage de Requête : SQL
 Administration des bases de données
 Optimisation des bases de données
Plan
 Rappels
 Administration des bases de données
 PL/SQL: Introduction, variables, structure de contrôle
 Gestion des curseurs
 Gestion des exceptions et les transactions
 Les fonctions et les procédures
 Les triggers
 Les packages
 Sauvegarde et restauration de la base de données
 Les outils de monitoring de la base de données
 La différence entre l’environnement OLTP et OLAP
 Optimisation des bases de données
Plan
 Rappels
 Administration des bases de données
 Optimisation des bases de données
 Optimisation : Opérations
 Optimisation : Plans d’exécutions
 Tuning de Requêtes SQL
Plan
 Rappels
 Administration des bases de données
 Optimisation des bases de données
 Optimisation : Opérations
 Principes de l’Optimisation
 Indexes
 Algorithmes des Opérateurs
 Algorithmes de Sélection
 Algorithme de tri
 Algorithmes de projection
 Algorithmes de jointure
 Optimisation : Plans d’exécutions
 Tuning de Requêtes SQL
Plan
 Rappels
 Administration des bases de données
 Optimisation des bases de données
 Optimisation : Opérations
 Optimisation : Plans d’exécutions
 Plan d’Exécution Logique
 Plan d’Exécution Physique
 EXPLAIN
 Optimisation des Plans d’Exécution
 Tuning de Requêtes SQL
 Indexation de fonctions
 HINT
Comment ça se passe?
 Cours (24h), TD (10h) et TP (24h)

 Réalisation des travaux pratiques

 Oracle

 Des mini-projets à réalisés par des groupes de trois

élèves

 Des évaluations
 Examen écrit
 Examen pratique
Les dépendances fonctionnelles
 Une donnée B dépend fonctionnellement (ou est en
dépendance fonctionnelle) d’une donnée A lorsque la
connaissance de la valeur de la donnée A nous permet la
connaissance d’une et au maximum d’une seule valeur de
B
 Exemple: la connaissance de la valeur d’un numéro de client
nous permet de connaître sans ambiguïté la valeur d’un et d’un
seul nom de client.
Les dépendances fonctionnelles
 Le but des dépendances fonctionnelles et de la théorie de
la normalisation est de s'assurer que le schéma
relationnel défini pour une base de données est
correctement construit.
 Un mauvais schéma relationnel peut en effet entrainer
des anomalies lors de manipulation de la base de
données.
 Les dépendances fonctionnelles
 Exemple
 Considérons la relation suivante
PRODUIT (Refproduit, LibelleProduit, PU, Quantité, NumService,
Adresse, Capacité)

Refproduit LibelleProduit PU Quantité NumService Adresse Capacité

P1 CH7 23.510 300 S1 France 9000
P1 CH7 23.510 500 S2 Espagne 6000
P3 VISI2 0.150 900 S3 France 2000

 Elle est visiblement redondante

Les dépendances fonctionnelles
 Exemple
 Cette relation présente certaines anomalies :
 Redondance : un produit apparaît autant de fois qu’il sera livré par un
service
 Mise à jour : les mises à jour conduiront à des risques d’incohérence
et de non intégrité.
 Insertion et suppression : l’insertion et la suppression ou le transfert
d’attributs pourront faire apparaître des valeurs nulles
Refproduit LibelleProduit PU Quantité NumService Adresse Capacité
P1 CH7 23.510 300 S1 France 9000
P1 CH7 23.510 500 S2 Espagne 6000
P3 VISI2 0.150 900 S3 France 2000

 Quel est la solution pour construire une base de données bien

conçue et cohérente?
Les dépendances fonctionnelles
 La solution est dont d'éviter toute redondance dans la
base de données. Ceci exige que le schéma de la base de
données soit bien construit. La méthode pour cela se
décompose en deux étapes:
1. Étude des dépendances entre données
2. Décomposition et normalisation des relations
Les dépendances fonctionnelles
 Définition:
 Soit R(X,Y,Z) un schéma de relation, avec X, Y, Z des ensembles
d'attributs. On dit qu'il existe une dépendance fonctionnelle
entre X et Y si la connaissance d'une valeur de X détermine au
plus une valeur de Y.

 Remarque:
 Les dépendances fonctionnelles expriment la sémantique entre
les attributs
Les dépendances fonctionnelles
 Propriétés: « Axiomes d’Armstrong »

Propriété Equivalence
DF1 (Réflexivité) X->Y ⇒Y->X
DF2 (augmentation) X->Y⇒X,Z ->Y,Z
DF3 (transitivité) X->Y ET Y->Z ⇒X->Z
DF4 (pseudo-transitivité) X -> Y ET Y,W -> Z ⇒X,W -> Z
DF5 (union) X -> Y ET X -> Z ⇒X -> Y,Z
Les dépendances fonctionnelles
 Exemple:

 Soit la table ligne_de_commande (N°decommande,

N°article, désignation, quantité commandé). On a les
dépendances fonctionnelles suivantes:

 N°article -> désignation

 N°decommande, N°article -> quantité commandé
Normalisation
 La normalisation est un processus de décomposition
d’une table universelle en plusieurs tables en évitant les
problèmes d’incohérence et de redondances
 L’objectif est donc de définir un bon schéma relationnel
qui décrit d’une manière convenable le système
d’information
 Un bon schéma de base de données est un schéma qui
vérifie les 3 formes normales
 Les formes normales s'appuient sur les dépendances
fonctionnelles entre attributs d'un schéma de base de
données.
Normalisation
 NB:
 On ne présente ici que les formes normales dont la définition
utilise exclusivement les dépendances fonctionnelles. Si on
prend en compte d'autres dépendances entre données comme
les dépendances multivaluées on obtient alors les 4FN et 5FN.
Les 3FN reste cependant l'objectif de normalisation le plus
"classique".

 La normalisation est un processus réversible (.i.e., a partir

des tables décomposés on peut constitue la table
universelle (dénormalisation) )
Normalisation
Les formes normales
 Les 3 premières formes normales ont été proposées par E.F.
Codd ( inventeur du modèle relationnel) en 1972
 1FN: une relation est en première forme normale ssi tout attribut a
une valeur atomique
 2FN: une relation est en deuxième forme normale ssi elle est en
1FN et si tous les attributs non clés sont en dépendance
fonctionnelle pleine avec la clé (pas de dépendance non-élémentaire)
 3FN: une relation est en troisième forme normale ssi elle est en
2FN et si tous les attributs non clés sont en dépendance
fonctionnelle directe avec la clé (pas de dépendance transitive)
 Il existe d’autres formes normales :
 La 3 forme normale de Boyce code
ème

 Les 4, 5, et 6 forme normale.

ème
Exemple: 1FN
 1FN c’est le point de départ vers les autres formes normales
 1FN exige chaque valeur d’un attribut soit atomique ( un
attribut atomique est celui ayant une seule valeur)
 Table ouvrage

Code ouvrage Titre Auteur

001 Systèmes d'information Isabelle Walsh, Michel Kalika

002 Stratégie Thomas Loilier, Albéric Tellier

 Ici l’attribut «Auteur» c’est un attribut qui accepte plusieurs

valeur, est donc n’est pas atomique. Et par la suite cette
relation n’est pas en première FN
 Processus de normalisation en 1FN
1. Sortir l’attribut non atomique de la table initiale
2. Transformer l’attribut non atomique en table, ajouter
dans cette nouvelle table la clé primaire de la première
table
Code auteur Auteur Code ouvrage

Code ouvrage Titre

1 Isabelle Walsh 001
001 Systèmes
d'information 2 Michel Kalika 001
002 Stratégie
3 Thomas Loilier 002

4 Albéric Tellier 002

 Exemple: 2FN
 Cette 2FN exige que les tables sont déjà en première 1FN
 Elle ne concerne que les tables a clé primaire composé
(composée de plusieurs attributs)
 La règle impose que les attributs non-clé primaire dépendent
de la totalité de la clé primaire
 Comme vous remarquez la clé de cette nouvelle
Article_commandé table est composé de deux attributs.
Clé N°commande  Est-ce que cette table est en 1FN ? OUI
primaire
 Est-ce que cette table est en 2FN (i.e., toute
composé N° article attribut non clé dépend de la totalité de la clé) ?
NON
Désignation
 Cette règle n’est pas vérifie parce que l’attribut
Quantité commandé « désignation » dépend qu’une partie de la clé,
qui est N°article. Et donc cette relation n’est pas
en 2FN
Processus de normalisation en 2FN
1. Regrouper dans une table les attributs dépendant de la
totalité de la clé et conserver cette clé pour cette table
2. Regrouper dans une table les attributs dépendant d’une
partie de la clé, et faire de cette partie la clé primaire de
cette nouvelle table

Article_commandé Article
N°commande N° article
N° article Désignation
Quantité commandé
Exemple: 3FN
 La mise en 3FN ne s’applique que sur les tables déjà en
première et en deuxième forme normale
 La règle a pour objet l’élimination des dépendances
fonctionnelles transitives au sein d’une table

Commande
 Est-ce que cette table est en 1FN ? OUI
N°commande
 Est-ce que cette table est en 2FN ? OUI
Date commande  Est-ce que cette table est en 3FN? Cette table a
une dépendance fonctionnelle transitive, donc
N° client n’est pas en 3FN
Nom client  N°client (attribut non-clé) détermine nom client
Processus de normalisation en 3FN
1. Conserver dans la table initiale les attributs dépend
directement de la clé
2. Regrouper dans une autre table les attributs dépendant
transitivement de la clé; l’attribut de transition reste
dupliqué dans la table initiale, et devient la clé primaire
de la nouvelle table

Commande Client
N°commande N° client
Date commande Nom client
N° client
Les contraintes d’intégrité (CI)
 Une contrainte d’intégrité est une règle qui définit la cohérence
d’une donnée ou d’un ensemble de données de la BD
 Il existe deux types de contraintes:
 Sur une colonne unique
 Sur une table lorsque la contrainte porte sur une ou plusieurs
colonnes.
 Remarque:
 Les contraintes sont définies au moment de la création des tables
 Exemple:
 PRIMARY KEY, Foreign Key, UNIQUE, NOT NULL, REFERENCES (3
modes: cascade, set Null, restrict = default), CHECK(), …
Exemple (CI)
Algèbre Relationnelle
 L’algèbre relationnel est un langage de requêtes qui permet de
réaliser des opérations sur les bases de données relationnelles
 Les opérations ensemblistes: l’union, l’intersection, la différence et le
produit cartésien.
 Les opérations relationnelles: la projection, la sélection et la jointure.
 Le résultat d’une opération (ou d’une requête) est une nouvelle
table qui est exploitable à son tour par une nouvelle opération
 La projection et la sélection sont des opérations qui s’appliquent à
une table
 Les opérations ensemblistes (union, intersection, différence) ne
peuvent être utilisés qu’avec deux tables ayant les mêmes attributs
et fournissent une troisième table ayant les mêmes attributs
 Le produit cartésien et la jointure fournissent une troisième table à
partir de deux tables quelconque
Projection
 L’opération de la projection sélectionne un ou plusieurs
attributs d’une table T.

 Exemple
Sélection
 L’opération sélection permet de sélectionner des
enregistrements selon des conditions qui portent sur les
attributs.

 Exemple
Intersection
 L’opération intersection permet d’obtenir une relation qui
ne contient que les enregistrements communs aux deux tables
A et B.

A A∩B
 Exemple B
Union
 L’opération union unie les enregistrements en éliminant les
éventuels doublons.

A
A∪B B
 Exemple
Différence
 L’opération différence permet d’obtenir une table où en
enlevant de A tous les enregistrements de B.

A–B
A
 Exemple
Produit Cartésien
 Le produit cartésien de A par B s’obtient en ajoutant à
chaque ligne de A autant de lignes qu’il y a dans B par
concaténation.

 Exemple
Jointure
 L’opération jointure permet de coller au bout des lignes de
la table A toutes les lignes de la table B vérifiant la condition
de jointure

A⋈B=𝝈cond(AXB)
 Exemple