01/02/2019

Matière : base de données

 Pourquoi cette matière?
 La conception d’une Base de données relationnelles
 Le traitement des requêtes (SQL)
 L’architecture d’un SGBD

 Quels valeur de la matière ?

 Crédits =4 coefficient = 2
 Comment ce font les enseignements ?
 1 cours / par semaine
 1 TD + 1TP
Aridj mohamed  Evaluation ?
 Moy= (examen *2+ (TD+TP)/2)/3
aridj_moh@[Link]
1

Bibliographie
Programme 1. Gardarin, Georges « Bases de données » Paris : Eyrolles, 2003
 Chapitre 1 : Introduction aux base de données
 Chapitre 2 : Les model des données 2. Hainaut, Jean-Luc « Bases de données : Concepts, utilisation et
développement » Paris : Dunod, 2009
- Model entité-association
 Chapitre 3 : Model relationnel 3. Boudjlida, Nacer « Bases de données et systèmes d'informations : le
 Chapitre 4 : Conception d’une base de données relationnelle modèle relationnel, langages, systèmes et méthodes : cours et
 Chapitre 5 : Langage relationnel exercices corrigés », Paris : Dunod, 2003
- L’algèbre relationnelles. 4. Adiba, Claude Delobel Et Michel, « Bases de données et systèmes
- Langage SQL relationnels » , Paris : Bordas, 1982
 Chapitre 6: Évaluation et optimisation des requêtes
 Chapitre 7: L'organisation physique des SGBD 5. Miranda Serge ,Busta josé maria « L’art des base de données » tome1
Paris : Eyrolles, 1988.

1. Date, Ch. Introduction to Database Systems. Prentice Hall.

Nombreux exercices et solutions

2. Ullman, J., Widom, J. A First Course in Database Syst. Prentice

Hall, 468. (Cours de Stanford Univ.)

3. Lewis, P., M., Berstein, A., Kifer, M. Database and Transaction

Processing. Addison Wesley
4. Garcia, H., Ullman, J., Widom, J., Database Syst. Prentice Hall,,
468. (Cours de Stanford Univ.)

5. Ramakrishnan, R. Database Management Syst. McGraw

Hill, 740. (Cours de Wisconsin Univ.)

6. D. Chamberlin. DB2. Universal Database. Morgan-Kaufman

1
01/02/2019

2
01/02/2019

3
 01/02/2019

Plan Approche classique = Utilisation des Fichiers

1. Approche totalitaire
2. Approche fragmentaire
Chapitre 1 : introduction aux base de données
Chapitre 2 : Modeles des données (model entité/association)
Chapitre 3 : model relationnel ( notion de base)
Chapitre 1 : □

Chapitre 4 : conception des base de données relationnelles introduction aux

Chapitre 5 : langages relationnel base de données
 Chapitre 6:évaluation et optimisation des requêtes
Chapitre 7: L'organisation physique des SGBD

ARIDJ MOHAMED UHBC 01/02/2019 ARIDJ MOHAMED UHBC 01/02/2019 ARIDJ MOHAMED UHBC 01/02/2019
1 2 3

Notions de base (1)

Il faut retenir : BD
□ Travailler directement sur un fichier présente plusieurs
inconvénients :
le terme base de données (data base) : conférence a en 1964, à • (1):ensemble de données structuré
1. Redondance d’information Santa Monica en Californie USA plusieurs définition proposées • (2) : Stockage sur disque
•(3) : Partage des données
2. Manipulation de données lourde et compliquée. Il  Def1 : BD = ensemble de données structuré et partageable entre •(4) : Confidentialité
faut être expert en programmation différant utilisateurs du système. •(5) : Performance

3. Le programmeur doit connaître la localisation physique

 Def2 : BD= ensembles de données opérationnel inter relié stocké
des fichiers, la structure physique des enregistrements, Exemples d ’application
le mode d’accès à ces fichiers sur un support accessible pour satisfaire simultanément plusieurs
•réservation dans une compagnie aérienne
utilisateurs
•Gestion d’une université
4 Toute modification de la structure des enregistrements
(ajout d’un champ par exemple) entraîne la réécriture de • Annuaire électronique
 Def3 base de données est un ensemble structuré de données
tous les programmes qui manipulent ces fichiers • Catalogue électronique d ’une bibliothèque
enregistrées sur des supports accessibles par l ’ordinateur pour
satisfaire simultanément plusieurs utilisateurs de manière sélective en
ARIDJ MOHAMED UHBC 01/02/2019 un temps opportun . ARIDJ MOHAMED UHBC 01/02/2019 ARIDJ MOHAMED UHBC 01/02/2019
4 5 6

1
 01/02/2019

SGBD (2) Objectifs des SGBD (1)

SGBD (1)
Il permet à des utilisateurs de créer et maintenir une □ Faciliter la représentation et la description de données.
□ Définition : Le logiciel et/ou matériel qui permet d ’interagir
OPlus besoin de travailler directement sur les fichiers physiques (tels
avec une BD est un Système de Gestion de Base de base de données. Les activités supportées sont la qu’ils sont enregistrés sur disque). Un SGBD nous permet de décrire
Données (SGBD) les données et les liens entre elles d’une façon logique sans se
définition d'une base de données (spécification des
soucier du comment cela va se faire physiquement dans les fichiers.
types de données à stocker), la construction d'une On parle alors d’ image logique de la base de données, (ou aussi
description logique ou conceptuelle ou encore de schéma logique). Ce
base de données (stockage des données proprement schéma est décrit dans un modèle de données par exemple le modèle
de tables, appelé le modèle relationnel.
BD dites) et la manipulation des données (principalement age logiqu
SGBD
ajouter, supprimer, retrouver des données).
Fichiers physique

5
© M.
ARIDJ MOHAMED UHBC 01/02/2019 ARIDJ MOHAMED UHBC 01/02/2019 ARIDJ MOHAMED UHBC 01/02/2019
7 8 9

□ Objectifs des SGBD (3)

Objectifs des SGBD (2) O Partage de données : Trois Fonctions d ’un SGBD
accès à la même information par plusieurs utilisateurs en même temps. Le SGBD
□ Faciliter la manipulation en travaillant directement sur le schéma inclut un mécanisme de contrôle de la concurrence basé sur des techniques de
logique. verrouillage des données (pour éviter par exemple qu'on puisse lire une information □ (LDD) Langage de Description de Données
qu'on est en train de mettre à jour)
On peut insérer, supprimer, modifier des données Description des données :codification structuration, grâce
directement sur l’image logique. Le SGBD va s’occuper de faire le O Intégrité :
travail sur les fichiers physiques des données grâce à la définition de contraintes sur les données. Le SGBD veille à ce
que toutes les contraintes soient vérifiées à chaque insertion, suppression, ou □ (LMD) Langage de Manipulation de Données
□ Permettre l’ajout des contraintes : modification d’une donnée.
Manipulation et restitution des données (insertion, mise à jour,
permettant d’avoir à tout instant des données cohérentes par O Confidentialité :
interrogation)
exemple l’âge d’une personne supérieurà zéro, salaire supérieur à plusieurs utilisateurs peuvent utiliser en même temps une base de données, se
zéro, etc. Dès que l’on essaie de saisir une pose le problème de la confidentialité des données. Des droits doivent être gérés
valeur qui ne respecte pas cette contrainte, le SGBD le refuse sur les données, droits de lecture, mise à jour, création, ... qui permettent □ ( LCD) Langage de Contrôle de Données
d'affiner.
□ Efficacité des Accès (Temps de réponse & débit global)
O Sécurité : Contrôle (partage, intégrité, confidentialité, sécurité)
une base de données est souvent vitale dans le fonctionnement d'une organisation, et
il n'est pas tolérable qu'une panne puisse remettre en cause son fonctionnement de
manière durable. Les SGBD fournissentdes mécanismes pour assurer cette sécurité.
ARIDJ MOHAMED UHBC 01/02/2019 ARIDJ MOHAMED UHBC 01/02/2019 ARIDJ MOHAMED UHBC 01/02/2019
10 11 12

2
 01/02/2019

Définition et description des données :

Définition et description des données
Niveau externe
□ 3 niveaux de description des données
Réel □ Description des données vues par un utilisateur (ou un groupe
Interview
Document d’utilisateurs)
•Indépendant du Oobjectifs : simplification, confidentialité
Modèle modèle de données
•Indépendant du
Modélisation
conceptuel SGBD Médecin effectue Visite OExemple :
Schéma
•Dépendant du
Externe1 • OUVRAGES édités par des éditeurs français
T Schéma conceptuel T Modèle modèle de données
Codasyl Relationnel Objet XML
•Indépendant du
Schéma logique SGBD
Externe2
Schéma physique •Dépendant du • Organisation physique des données
Modèle modèle de données
• Structures de stockage des données
•Dépendant du
1
Physique SGBD • Structures accélératrices (index)
ARIDJ MOHAMED UHBC 01/02/2019 ARIDJ MOHAMED UHBC 01/02/2019
13 14 15

Définition et description des données : Définition et description des données : Evolution des SGBD
niveau logique (conceptuel) Niveau physique  hiérarchique / modèle réseau ( 60-70)
□ Permet la description
• des objets : exemple OUVRAGES, ETUDIANTS
□ Description informatique des données et de leur  relationnel (70-90)
• des propriétés des objets (attributs) : exemple cote de OUVRAGES, Titre de organisation : en terme de fichiers, d ’index, de méthodes
 objet(90-200)
OUVRAGES, nombre d’exemplaires etc. d’accès, …
• des liens entre les objets : un OUVRAGE peut être emprunté par un
ETUDIANT  Déductive (2000 ….
• des Contraintes : le nombre d’exemplaires d’un OUVRAGE est supérieur à zéro
O Cette description est faite selon un modèle de données. □ Passage du modèle logique au modèle physique tend à □ Quelques SGBD (relationnels du marché)
O Un modèle de données est un ensemble de concepts permettant de décrire la être assisté par le SGBD : transparent et/ou semi-
structure d'une base de données. La plupart des modèles de données incluent automatique
des opérations permettant de mettre à jour et questionner la base. Le OMicro : ACCESS, PARADOX, DBASE V, ...
modèle de données le plus utilisé est le modèle relationnel,
O Cette description va donner lieu à un schéma de base de données. Un schéma de base de
OGros système : DB2, ORACLE, SYBASE, INGRES, …
données se compose d'une description des données et de leurs relations ainsi que d'un □ Objectifs : optimiser les performances
ensemble de contraintes d'intégrité
MySQL ……..

ARIDJ MOHAMED UHBC 01/02/2019 ARIDJ MOHAMED UHBC 01/02/2019 ARIDJ MOHAMED UHBC 01/02/2019
16 17 18

3
 01/02/2019

Démarche de construction d ’une BD

Modélisation

Interview
Entité association
Document

Schéma logique
en Entité-Association

Transformation
en relationnel

Schéma conceptuel
en relationnel

ARIDJ MOHAMED UHBC 01/02/2019

19

4
 PLAN

Chapitre 2 1. Introduction :
2. Le modèle relationnel
MODÈLE RELATIONNEL 3. Conclusion

17:15 L2 BD [Link] ARIDJ Mohamed 1 17:15 L2 BD [Link] ARIDJ Mohamed 2

Introduction Pourquoi le model relationnel :

1. Inventé par Edgar [Link] (IBM) Publi ACM 1970 a) Propose des schémas de données faciles à utiliser.
2. Implémentations commercialisées dans les années 80 b) Améliore l'indépendance logique et physique.
[Link]é sur le concept mathématique : la relation c) Propose des langages de haut niveau pouvant être
utilisés par des non informaticiens.
[Link] BDs relationnelles s’adaptent pour les applications
classique et complexes d) Accès optimisé à base de données.
e) Améliore l'intégrité et la confidentialité.
[Link] grande partie des SGBD commercialisés sont
relationnels : ORACLE, SQL SERVER, MYSQL , ACCES….. f) Approche méthodologique dans la construction de la
base de données.
17:15 L2 BD [Link] ARIDJ Mohamed 3 17:15 L2 BD [Link] ARIDJ Mohamed 4

DOMAINE
ENSEMBLE DE VALEURS

• Exemples:
Concepts de base – ENTIER
– REEL
du – CHAINES DE CARACTERES
– EUROS
modèle relationnel – SALAIRE = {4 000..100 000}
– COULEUR= {BLEU, BLANC, ROUGE}
– POINT = {(X:REEL,Y:REEL)}
– TRIANGLE = {(P1:POINT,P2:POINT,P3:POINT)}

17:15 L2 BD [Link] ARIDJ Mohamed 5 17:15 L2 BD [Link] ARIDJ Mohamed 6

Page 1
 PRODUIT CARTÉSIEN RELATION
• le produit cartésien sur D1x D2x ... x Dn est • sous-ensemble du produit cartésien d'une liste de
l'ensemble des tuples <V1,V2,....Vn> domaines
tels que VI DI • Une relation est caractérisée par un nom

Exemple
D1=nom ={B727, A320, Concord}
Exemple:
D2=capacité={100,200}
Nom = {B727, A320, Concord}
Capacité : entier naturel (0,1,2 ……..

17:15 L2 BD [Link] ARIDJ Mohamed 7 17:15 L2 BD [Link] ARIDJ Mohamed 8

Graphe d'une relation

• Relation binaire R(A1,A2)
DOM(A2)
DOM(A1) – Nom donné à une colonne d'une relation
DOM(A2)
a
1 – Prend ses valeurs dans un domaine
4 •
b
2 – Nom , capacité
3 •
3
2 • c
1 • • 4
a b c DOM(A1)

• Une relation n-aire est une généralisation à n dimensions

17:15 L2 BD [Link] ARIDJ Mohamed 9 17:15 L2 BD [Link] ARIDJ Mohamed 10

CLÉ CANDIDATE Clé Primaire

• Groupe d'attributs minimum qui identifie d’ une • La clé primaire d'une relation est une de ses clés
manière unique chaque tuple de la relation candidates.

• Exemples: • Remarque :
– NSS DANS PERSONNE Pour signaler la clé primaire, ses attributs sont généralement
– NISS dans étudiant soulignés.
– Code_matière dans matière

• Remarque :
– Toute relation doit posséder au moins une clé candidate .
17:15 L2 BD [Link] ARIDJ Mohamed 11 17:15 L2 BD [Link] ARIDJ Mohamed 12

Page 2
 Clé Etrangère représentation d’une relation

• Une clé étrangère d'une relation est formée d'un ou

plusieurs de ses attributs qui constituent une clé candidate
dans une autre relation.

Exemple a) toutes les entrées d’une même colonne doivent être du même type.
• Remarque : b) Un non distinct doit être attribué à chaque colonne.
Voiture / propriétaire
c) Il y’a pas de lingues identiques
d) L’ordre des linges n’est pas significatif et de même pour les
colonnes.
17:15 L2 BD [Link] ARIDJ Mohamed 13 17:15 L2 BD [Link] ARIDJ Mohamed 14

Degré d’une relation Cardinalité d’une relation

Degré de relation Cardinalité de relation
Degré d’une relation est le nombre de ces attribues. Cardinalité d’une relation est le nombre de ces articles.
Degré(R)= nombre attribues= nombre de colonne Car (R)= nombre articles = nombre de lignes
Exemple Exemple

Degré(vointure)= 8 Car (vointure)= 5

17:15 L2 BD [Link] ARIDJ Mohamed 15 17:15 L2 BD [Link] ARIDJ Mohamed 16

Définition intensive Définition extensive

• Consiste à définir un prédicat d’appartenance que • Consiste à lister tous les tuples de la relation.
tous les tuples de la relation doivent vérifiés.

• Exemple :
• R={(x,y) / (x  N et y  N et x mod y = 0)}

17:15 L2 BD [Link] ARIDJ Mohamed 17 17:15 L2 BD [Link] ARIDJ Mohamed 18

Page 3
 Schéma de relation Base de données relationnelle
• Un schéma de relation est le nom de la
relation suivi de la liste des attributs. • Une base de données est relationnelle
lorsque son schéma est un ensemble de
• Exemple: schémas de relations
– Etudiant(NIS, Nom, prénom, adr , date_naiss,..) • Exemple
• Etudiant(Nis, Nom, prénom, adr , date_naiss,..)
• Matiére (Code-Matière, desig, coef, crédit)
Remarque • resultat(Niis code-matière, note)
Par convention, la clé primaire est soulignée

17:15 L2 BD [Link] ARIDJ Mohamed 19 17:15 L2 BD [Link] ARIDJ Mohamed 20

Conclusion Questions ?

Atrributs /Champs

Articles

Cardinalité

17:15 L2 BD [Link] ARIDJ Mohamed 21 17:15 L2 BD [Link] ARIDJ Mohamed 22

Degré

Page 4
 PLAN

Chapitre 3 1. Introduction :
2. Rappel : Le modèle Entité-Association (E/R)
3. Passage du MCD-MLD
Conception d’une base de données
relationnelle 4. Conclusion

17:15 L2 BD [Link] ARIDJ Mohamed 17:15 L2 BD [Link] ARIDJ Mohamed

Introduction générale Introduction générale

I. Introduction Modèle de données: c’est quoi?
Démarche de conception d’une BD  Un ensemble de concepts et de règles .

 La composition de ces concepts permettant de décrire des

données(monde réel).

 Modèle de données : pourquoi faire ?

 Permettre une meilleure compréhension

 Permettre une conception progressive

 Isoler les concepts fondamentaux

 Faciliter la visualisation du système

17:15 L2 BD [Link] ARIDJ Mohamed 3 17:15 L2 BD [Link] ARIDJ Mohamed 4

Modèle entité-association:
Modèle entité-association Introduction
: Introduction Modèle entité-association: Les concepts

II.1 Généralité
2.1 Classe d’Entité (entité , Individu, Objet).
 modèle permettant la modélisation conceptuelle DB relationnelle.

 intégré dans la méthode MERISE .

 Une entité est un objet du monde réel ayant une existence
propre et présentant un intérêt pour l'application. Une entité
 une double origine : Chen (1976) USA / Tardieu (1975) France.
est utilisée par l'application, l'entreprise ou l'organisme.
 Plusieurs extensions ont été proposer : Batini (1992/1996) et
Elmasri(1994) .

 Le Modèle E/A se concentre sur le concept de : Entité  Une entité est une constituante concrète ou abstraite du
 Dans ce chapitre on va aborder les concepts suivant s: mode réel
- Entités (et classes d’entités)
 Une entité peut représenter : une personne , objet , lieu,
- Propriétés (aussi appelées attributs)
- Associations (ou classes d’association) statut, évènement ……

17:15 - Cardinalités (association)

L2 BD [Link] ARIDJ Mohamed 5 17:15 L2 BD [Link] ARIDJ Mohamed 6

1
Modèle entité-association: Les concepts Modèle entité-association: Les concepts

 Exemple 1 : 2.2 Occurrence (instance) d’une classe d’entité:

L’étudiant fréquente université

un élément particulier de la classe d'entité (un objet)

Exemple 1:
Dans cet exemple deux classes d’entités : classe : étudiant et classe
L’étudiant Mohamed fréquente université UHBC
université
Mohamed est une occurrence de la classe étudiant
 Exemple 2
UHBC est une occurrence de la classe université
Dans une entreprise qui achète et vend des produits

Nous avons trois classes d’entité : Clients, articles et fournisseurs.

17:15 L2 BD [Link] ARIDJ Mohamed 7 17:15 L2 BD [Link] ARIDJ Mohamed 8

Modèle entité-association:
Les concepts
Les concepts Modèle entité-association: Les concepts
2.3 Association.
2.4 Propriété (Information, Attribut).
 Lien sémantique entre une ou plusieurs entités. chaque entité
joue un rôle particulier.  Donnée élémentaire permettant de décrire une entité ou une
 Une association n’a pas une existence propre. association.
 Cette donnée peut se mesurer par une valeur

Etudiant1 Exemple .
UHBC
Etudiant
2
Etudiant
3
Etudiant4
Etudiant
5

Etudiant Univesité
,N 1,
Frequente

17:15 L2 BD [Link] ARIDJ Mohamed 9 17:15 L2 BD [Link] ARIDJ Mohamed 10

Modèle entité-association: Les concepts Modèle entité-association: Les concepts

 Ordre degré d’une association :
2.5 Clé (Identifiant) d’une classe d’entité :  Le nombre d’entité impliquées dans l’association
 son nombre de "pattes".
propriété ou groupe de propriétés permettant d’identifier de manière
unique chaque occurrence de la classe d’entités.
Exemple : 1. Association binaire
N° immatriculation pour VOITURE.

2.5 Clé (Identifiant) d’une classe d’association 2. Association n-aire

la concaténation des clé de classe d’entité qu’elle associée
Exemple :
Etudiant , 1 :N Univesité
,1:1 Frequente
Nins Code_Univ

La clé de l’association Fréquente = Nins,code_Univ 3. Association réflexive

17:15 L2 BD [Link] ARIDJ Mohamed 11 17:15 L2 BD [Link] ARIDJ Mohamed 12

2
Modèle entité-association: Les concepts Modèle entité-association: Les concepts
Association 0:1
 Cardinalité d’une association : une entité de E1 peut être reliée à aucune ou à une seule entité de E2

 Couple de valeurs (N:M) Représente la connectivité de l’association Enseignent Université

0,1
dirige

 Soit A une association de deux entités E1 et E2

E1 E2
N ,M N,M
A
Association 1:1

une entité de E1 est reliée à une seule entité de E2

Etudiant Université
 3 types d’association 1,1
fréquente

17:15 L2 BD [Link] ARIDJ Mohamed 13 17:15 L2 BD [Link] ARIDJ Mohamed 14

Modèle entité-association: Les concepts Modèle entité-association: Les concepts

Association N - M: (N=0,1,2….) Représentations graphiques :
une entité de E1 peut être reliée au minimum à N entités de E2
Il existe plusieurs systèmes de conventions graphiques.
et au maximum à M
Nous adopterons le suivant :
Exemples:
1. Les entités sont représentées par des rectangles.
Etudiant Module
-ninst -code
2. Les attributs sont inscrits à l’intérieur des rectangles
-nom 1,N Inscrit -intitulé
-pnom -….. 3. Les types associations sont représentés par des ovales ou des losanges
-adr
-…… [Link] vers les entités qui sont reliées par l’association
5. Les cardinalités sont inscrites sur les arcs de l’association
Etudiant Module
Client Produit -ninst -code
0,N -nom M,N M:N -intitulé
Inscrit
Commande -pnom -…..
-adr
-……

17:15 L2 BD [Link] ARIDJ Mohamed 15 17:15 L2 BD [Link] ARIDJ Mohamed 16

Modèle entité-association: élaboration Modèle entité-association: élaboration

u Etape1: Reconnaissance des entités associations u Etape 4 : Affectation des propriétés aux entités et aux associations
Ce sont celles qui font l'objet de l'application à gérer. dont elles dépendent.
Eventuellement, créer de nouvelles entités ou de nouvelles
associations.
uEtape2: Etablissement d'une liste d'informations (de propriétés) Question à se poser :
que l'on veut gérer et mémoriser dans l'application.
De quoi dépend cette information ?
Si elle dépend d'une seule entité, elle appartient à cette entité. Si
Issues de documents, de données à éditer ...
elle dépend de plusieurs entités, elle appartient à une
association entre ces entités.
uEtape3: Reconnaissance des entités associations
uEtape 5 : Recherche et choix des identifiants
... Les liens entre les entités
u Etape 6 : Définition des cardinalités.

17:15 L2 BD [Link] ARIDJ Mohamed 17 17:15 L2 BD [Link] ARIDJ Mohamed 18

3
Modèle entité-association: Avantage Modèle entité-association: Inconvénients

 Simple  Non déterministe

3 concepts : entités, attributs, associations Le choix entre attribut, entité et association n’est pas évident
Représentation graphique concise (conventions différentes mais Permet d’attacher de la sémantique dans la modélisation
compréhensible)
 Pauvreté de la modélisation des contraintes
 Rapide Associations entre 2 entités
Éviter la redondance
Modélisation immédiate de problèmes simples
Privilégier la simplicité et lisibilité du modèle
Partir d’exemples pour trouver la structure
 Modèle statique
Non modalisation des traitement

 Problèmes de validation

17:15 L2 BD [Link] ARIDJ Mohamed 19 17:15 L2 BD [Link] ARIDJ Mohamed 20

Modèle entité-association: Conclusion Règles de passage MCD-MLD:

Classe d’Entité

 Modélisation simple du monde réel 1. Une Entité  Une Table

 Transposition relationnelle automatisable 2. Un Identifiant  Une Clé primaire

 Un Attribut
 Outils d’aide à la conception E/A disponibles 3. Une Propriété

Concept Définition formelle Exemples

ENTITE un objet que l’on peut Fournisseur, pièce, cargaison,
distinguer Personne
Employé, département
PROPRIETE une information qui N° fournisseur, qté cargaison,
décrit une entité département d’employé, poids
de personne
ASSOCIATION une entité qui permet Cargaison(fournisseur-pièce)
de connecter 2 entités Affectation( employé-département)
ou plus

17:15 L2 BD [Link] ARIDJ Mohamed 21 17:15 L2 BD [Link] ARIDJ Mohamed 22

Règles de passage MCD-MLD:

Association : (-,n) (-,n) Association dont une seule des cardinalité maximum est n
les deux cardinalités maximum sont n

17:15 L2 BD [Link] ARIDJ Mohamed 23

4
Règles de passage MCD-MLD: Règles de passage MCD-MLD:
Association : (-,1) (-,1) Association :d’ordre >2

Règles de passage MCD-MLD: Règles de passage MCD-MLD:

Association : (-,n) (-,n) Association : (-,n) (-,n)

Règles de passage MCD-MLD:

Association : (-,n) (-,n) Règles de passage d ’un MCD à un Modèle Logique
Relationnel
Modèle conceptuel de données.

Equipes JOUEURS
5,n 1,1
N°Equipe Fait-Jouer N°Joueur
NomEquipe NomJoueur
Ville PrénomJoueur

Modèle logique relationnel.

EQUIPES JOUEURS
N°EQUIPE N°JOUEUR
NOMEQUIPE N°EQUIPE
VILLE NOMJOUEUR
PRENOMJOUEUR

Schéma Relationnel.
EQUIPES (N°Equipe, nomequipe, Ville, Entraineur)
JOUEURS (N°Joueur, N°Equipe, NomJoueur, PrénomJoueur)
17:15 L2 BD [Link] ARIDJ Mohamed 30

5
 5. Les autres associations 5. Les autres associations
1,1 JOUEURS
EQUIPES 5,n Fait-Jouer
Cardinalités de la relation : x,n - x,n Modèle Conceptuel de Données N°Equipe
N°Joueur
NomJoueur
NomEquipe PrénomJoueur
Ville
 Une Table ayant comme clé primaire la concaténation des Entraineur 0,n
0,n
0,n Matchs-Visiteurs
identifiants des entités qu'elle relie.
Matchs-Locaux A_JOUE
NbPointsMarqués
Matchs NbFautes
A-joué 0,n JOUEURS
Modèle Conceptuel de Données. N°Match
NbSpectateurs
0,n
NbPointsMarqués N°Joueur
NomJoueur
1,1

NbPtsLocaux NbFautes
PrénomJoueur EQUIPES 1,1 MATCHS
NbPtsVisiteurs JOUEURS 0,n
N°Equipe N°Joueur N°Match
NomEquipe N°Equipe NbSpectateurs
Ville NomJoueur NbPtsLocaux
MATCHS
Entraineur PrénomJoueur NbPtsVisiteurs
N°MATCH A_JOUE
NBSPECTATEURS N°MATCH
NBPTSLOCAUX N°JOUEUR
NBPTSVISITEURS NBPOINTSMARQUES
Modèle Logique Relationnel. NBFAUTES A_JOUE Modèle Logique de données
N°Joueur
JOUEURS N°Match
N°JOUEUR NbPointsMarqués
NOMJOUEUR NbFautes

Schéma Relationnel PRENOMJOUEUR MATCHS

N°Match
N°Locaux
MATCHS (N°Match, Date, NbSpectateurs, NbPtsLocaux, N°Visiteurs
Date
NbPtsVisiteurs) NbSpectateurs
NbPtsLocaux
NbPtsVisiteurs
A_JOUE (N°Match, N°Joueur, NbPointsMarqués, NbFautes)
17:15 L2 BD [Link] ARIDJ Mohamed 31 17:15 L2 BD [Link] ARIDJ Mohamed 32

-3- Passage à un Modèle Logique de Données de type

5. Les autres associations
Relationnel
u Deuxième étape du cycle de vie:
Schéma Relationnel
EQUIPES (N°Equipe, NomEquipe, Ville, Entraineur) On choisit le type de SGBD que l'on veut utiliser indépendamment d’un
JOUEURS (N°Joueur, N°Equipe, NomJoueur, PrénomJoueur) SGBD spécifique.
MATCHS (N°Match, N°Locaux, N°Visiteurs, Date, NbSpectateurs, NbPtsLocaux,
NbPtsVisiteurs) Choix possibles ?
A_JOUE (N°Joueur, N°Match, NbPointsMarqués, NbFautes)

Hiérarchique (e.g., IMS d'IBM) obsolète

Réseau (e.g., IDS Honeywell) obsolète
Relationnel
Objet (O2, ...) L’avenir ?

u Modèle Logique Relationnel

Access, Oracle, Ingres, DB2, ...
17:15 L2 BD [Link] ARIDJ Mohamed 33 17:15 L2 BD [Link] ARIDJ Mohamed 34

6
 PLAN

Chapitre 4 1. Introduction :
2. Dépendance fonctionnelle
Normalisation d’une base de 3. Formes normales
données relationnelle

17:16 L2 BD [Link] ARIDJ Mohammed 1 17:16 L2 BD [Link] ARIDJ Mohammed 2

Introduction Introduction
• On peut créer un modèle relationnel des données de •La normalisation est une approche formelle pour la
deux manières: conception des BD relationnelles.
1. Modèle E/A traduction en modèle relationnel
•La normalisation repose sur des règles pour placer les
2. Produit directement un modèle relationnel qui est ensuite attributs dans les relations.
raffiné pour s’assurer de sa qualité

•Au passage d’une forme normale à une autre, les

données s’organisent davantage.
Normalisation

17:16 L2 BD [Link] ARIDJ Mohammed 3 17:16 L2 BD [Link] ARIDJ Mohammed 4

Pourquoi la normalisation :

1. pour limiter les redondances de données,

Concepts la dépendance
2. pour limiter les pertes de données,
fonctionnelle
3. pour limiter les incohérences au sein des données et
DF

4. pour améliorer les performances des traitements.

17:16 L2 BD [Link] ARIDJ Mohammed 5 17:16 L2 BD [Link] ARIDJ Mohammed 6

Page 1
 DF Définitions : DF EXEMPLE
R(A1,A2,X..Y,An)

• On dit que l’attribue Y dépend fonctionnellement

de X ou X détermine Y si, et seulement si, pour
chaque occurrence x de X correspond toujours la
même occurrence y de Y dans la relation R.
Les dfs de cette relation sont:
F={A -> B, C -> B}
• On le note : X -> Y

17:16 L2 BD [Link] ARIDJ Mohammed 7 17:16 L2 BD [Link] ARIDJ Mohammed 8

DF Généralisation DF Propriétés: axiomes d’ Armstrong

• Soit R(A1,A2 X1…….Xn,..,Y1……Ym,………An.) un schéma de relation

• X={X1………Xn} et Y={Y1….Ym} deux sous ensembles d’attribues de R.

• On dit que l’ensemble d’attribue Y dépend fonctionnellement de de

l’ensemble d’attribue X ou X détermine Y si, et seulement si pour
chaque combinaison (x1,x2,……….xn) de X correspond toujours la
même combinaison (y1,y2………………ym) de Y par la relation R.

17:16 L2 BD [Link] ARIDJ Mohammed 9 17:16 L2 BD [Link] ARIDJ Mohammed 10

DF Propriétés Graphe de DF

• Il est possible de visualiser un ensemble F par un

graphe (appelé graphe des dépendances).

• Nœuds = attributs

• Arcs = DF

17:16 L2 BD [Link] ARIDJ Mohammed 11 17:16 L2 BD [Link] ARIDJ Mohammed 12

Page 2
 Graphe de DF DF ÉLÉMENTAIRE

F= { code-ouvrier  Nom-ouvrier /qualification / indice

code-machine type-machine/code-F
• Une dépendance fonctionnelle X ->Y est dite élémentaire
est si :
code-F Nom-F / Adresse
–1. Y n’est pas inclus dans X
code-ouvrier,code-machine  Temps-passé }

–2. Il n’existe pas X’ inclus dans X tel que X’-> Y

• on note : DFE
DFE
X Y

17:16 L2 BD [Link] ARIDJ Mohammed 13 17:16 L2 BD [Link] ARIDJ Mohammed 14

DF ÉLÉMENTAIRE DF :DIRECTE
• Exemple • Une dépendance fonctionnelle X → Y est dite directe Si :
• Soit F={ NF  NOMF,
NF PNOMF n'existe pas d'attribut ou d'ensemble d'attributs Z
NFADRF, tel que X → Z et Z → Y.
NF, NOMF ADRF}
• on note DFD :
DFE
X Y

• En d'autres termes, X → Y n'est pas Transitive

17:16 L2 BD [Link] ARIDJ Mohammed 15 17:16 L2 BD [Link] ARIDJ Mohammed 16

DF DIRECT Fermeture transitive

• Fermeture transitive d'un ensemble de dépendances
• Exemple
fonctionnelles est ce même ensemble enrichi de toutes les
• Soit F={ NF  NOMF,
NF PNOMF dépendances fonctionnelles déduites par transitivité.
NFADRF,
NOMF ADRF}
• Fermeture transitive d'un ensemble F de DF est notée F+

• F+ = F U DF obtenues via les axiomes

17:16 L2 BD [Link] ARIDJ Mohammed 17 17:16 L2 BD [Link] ARIDJ Mohammed 18

Page 3
 Fermeture transitive Couverture minimale
exemple • La couverture minimale d'un ensemble de dépendances
F={ MAT  TYPE ,COULEUR fonctionnelles est un sous ensemble minimum de
TYPE  MARQUE ,PUISS }
dépendances fonctionnelles élémentaires permettant de
• Par exemple générer toutes les autres.
MAT  TYPE et TYPE  MARQUE Donc MAT  MARQUE
• La couverture minimale d'un ensemble F de DF est
F+={MAT  TYPE ,COULEUR / TYPE  MARQUE ,PUISS notée F^
MAT  MARQUE/ MAT  PUISS}
(PS: Graphe de DF)
• F^ = F - DF obtenues via les axiomes
17:16 L2 BD [Link] ARIDJ Mohammed 19 17:16 L2 BD [Link] ARIDJ Mohammed 20

Couverture minimale Clé d’une relation

• Exemple:
• Définition
F={MAT  TYPE ,COULEUR / MARQUE/ PUISS – ensemble minimum d’attributs permettant de
TYPE  MARQUE ,PUISS déterminer tous les autres
MAT,NSS  MARQUE/PRIX } • Soit R(A1, A2, ..., An) un schéma de relation. Soit
• Donner F^ F l’ensemble des DF associées à R. X est une
clé de R ssi :
– X  A1, A2, ..., An
– il n’existe pas de sous-ensemble Y de X tel que:
Y  A1, A2, ..., An

17:16 L2 BD [Link] ARIDJ Mohammed 21 17:16 L2 BD [Link] ARIDJ Mohammed 22

LES BESOINS DE NORMALISATION :

LES FORMES NORMALES

1. Anomalie d'insertion

2. Anomalie de suppression

3. Anomalie de modification
17:16 L2 BD [Link] ARIDJ Mohammed 23 17:16 L2 BD [Link] ARIDJ Mohammed 24

Page 4
 PREMIERE FORME NORMALE (1 NF) DEUXIEME FORME NORMALE ( 2NF)
R en 1NF  tous les domaines de ses attributs sont atomique R en 2 NF 1. R est en 1NF
(simples, non multiples, non composées).
2. Toute dépendance fonctionnelle avec la clé est
élémentaire

R en 2 NF 1. R est en 1NF

2. Tout attribue non clé ne dépend pas
fonctionnellement d’une partie de la clé.

17:16 L2 BD [Link] ARIDJ Mohammed 25 17:16 L2 BD [Link] ARIDJ Mohammed 26

DEUXIEME FORME NORMALE ( 2NF) TROISIEME FORME NORMALE (3 NF)

Commande(codeClient, codeproduit, nonclient, Desproduit, pu) R en 3 NF 1. R est en 2NF
F={codeClient → nomclient 2. tous attribue non clé est en dépendance
codeproduit → desproduit fonctionnelle non transitive avec la clé .
codeClient, codeproduit → pu}

R en 3NF 1. R est en 2NF

Client(codeClient, nonclient)
[Link] attribue non clé ne dépend pas
Produit(codeproduit, desproduit)
fonctionnellement d’un autre attribue non clé
Commande(codeClient, codeproduit, pu)

17:16 L2 BD [Link] ARIDJ Mohammed 27 17:16 L2 BD [Link] ARIDJ Mohammed 28

TROISIEME FORME NORMALE (3 NF) FORME NORMALE DE BOYCE-CODD (BCNF)

Voiture (mat, marque, type, puis, couleur)
R en BCNF 1. R est en 3 NF
F={mat  marque, type, puis, couleur 2. Un attribue non clé ne détermine pas une partie de
Type  marque} la clé.

Adresse (rue ,ville, codepostale)

Voiture1 (mat,type, puis, couleur) F={ rue, ville codepostale
codepostale  ville}
Voiture2(type, marque)
Adresse1(rue,ville)
Adresse2 (codepostale, ville)

17:16 L2 BD [Link] ARIDJ Mohammed 29 17:16 L2 BD [Link] ARIDJ Mohammed 30

Page 5
 AUTRES FORMES NORMALES Conception d’une BD relationnel
Conception de
base de
QUATRIÈME FORME NORMALE (4NF) données

Une relation est en 4 NF si et seulement si les seules dépendances

Approche par
Approche
multi-valuées élémentaires sont celles dans lesquelles une clé conceptuelle
: Normalisation

détermine un attribut.
Modèle
Modèle entité conceptuel de Le passage au
association modèle Dépendances Décomposition Formes
données fonctionnelles d’une relation normales
(E/A) (MCD) relationnel
CINQUIEME FORME NORMALE (5 NF)
Une relation est en 5 NF si et seulement si toute dépendance de
jointure est impliquée par les clés candidates de la relation. Un schéma de la base de données conforme au besoin et ne présentant pas d’anomalie.

17:16 L2 BD [Link] ARIDJ Mohammed 31 17:16 L2 BD [Link] ARIDJ Mohammed 32

Construction de schéma relationnel Normalisé Par décomposition

Monde réel R<A,F>
Deux algorithmes de conception : Domaines élémentaires
1NF
1. Par décomposition DFs élémentaires avec la clé
2 NF
2. Par synthèse à partir du graphe des DF DFs directe avec la clé

3NF

Schéma
Monde information (R1,R2 ….
17:16 L2 BD [Link] ARIDJ Mohammed 33 17:16 L2 BD [Link] ARIDJ Mohammed 34

Par synthèse à partir du graphe des DF Exemple

Etape 1: Calculer F^ (couverture minimale de F)

Etape 2: Dessiner le Graphe de DF de F^ R<A,F>

Etape 3 : Regrouper les attributs isolés au sein d’une relation R0 : A={ mat,type,marque,puiss,couleur}
la clé de R =toutes les attributs ; supprimer ces attributs du Graphe
F={ mat  type
Etape 4: Rechercher le plus grand ensemble X déterminant.
type  puiss
Etape 5 : Déterminer Y ensemble d’attributs déterminé par X. matcouleur
Etape 6 : Constituer la relation Ri( X,Y): mat marque
Etape 7 : Eliminer les dépendances (X Y) du graphe marque,type puiss}
Etape 8 : Eliminer les attributs isolés dans le graphe

Etape 9 : Si non graphe vide alors aller à 4

17:16 L2 BD [Link] ARIDJ Mohammed 35 17:16 L2 BD [Link] ARIDJ Mohammed 36

Page 6
 Questions ?

17:16 L2 BD [Link] ARIDJ Mohammed 37

Page 7
 PLAN

Chapitre 5 1. Introduction et définitions

Algèbre relationnelle 2. Opérateurs ensemblistes
3. Opérateurs relationnels

17:16 L2 BD [Link] ARIDJ Mohamed 1 17:16 L2 BD [Link] ARIDJ Mohamed 2

Algèbre relationnelle Opérateurs de base

• L'algèbre relationnelle est un support • Opérateurs ensemblistes
mathématique cohérent sur lequel repose le – Union, intersection, produit cartésien, différence
modèle relationnel et division .
• Les opérandes sont les relations ou variables
(qui représentent des relations)
• Opérateurs relationnels
• Les opérateurs sont conçus afin de répondre
– Affectation ,Sélection, Projection et jointure.
aux besoins des utilisateurs
• Le résultat est une algèbre qui peut être utilisé
comme langage de requêtes

17:16 L2 BD [Link] ARIDJ Mohamed 3 17:16 L2 BD [Link] ARIDJ Mohamed 4

L’algèbre relationnelle
Opérateurs ensemblistes L’algèbre relationnelle
• Union : opération portant sur deux relations ayant le même schéma et L’union (1)
construisant une troisième relation constituée des tuples appartenant à
chaque relation. Les tuples en double sont éliminer L’union de deux relations R1 et R2 de même schéma est une relation R
• Intersection : Opération portant sur deux relations ayant le même schéma et
de même schéma contenant l’ensemble des tuples appartenant à R1, à
construisant une troisième relation dont les tuples sont constitués de ceux R2 ou aux deux.
appartenant au deux relations

• Différence relationnelle : Opération portant sur deux relations ayant le même Notation : R1 U R2 ou union (R1, R2)
schéma et construisant une troisième relation dont les tuples sont constitués
de ceux ne se trouvant que dans une seule relation
Représentation graphique :
• Le produit cartésien : opération sur deux relations de schéma différents
construisant une troisième relation constituée des attributs appartenant à R
chaque relation et dont les tuples sont constitués de toutes les concaténations
des tuples des deux relations.

• La division : opération sur deux relations de schéma différents construisant R1 S2

une troisième relations constituée de tous les tuples qui concaténé à chaque
tuple de la deuxième relation,
17:16 L2 BD
donnent toujours un tuple de la première5
[Link] ARIDJ Mohamed 17:16 L2 BD [Link] ARIDJ Mohamed 6
relation.

1
 L’algèbre relationnelle L’algèbre relationnelle
L’union (2) L’intersection(1)
L’intersection de deux relations R1 et R2 de même schéma
est une relation R de même schéma contenant l’ensemble
R1 A B C R2 A B C des tuples appartenant à la fois à R1 et à R2.
a d 1 a d 1
b e 2 s e 4
c f 3 c d 3 Notation : R1 I R2 ou inter (R1,R2)

Représentation graphique : R

R=Union (R1,R2) A B C
a d 1 I
b e 2
c f 3
s e 4
c d 3
R1
17:16 L2 BD [Link] ARIDJ Mohamed 7 17:16 L2 BD [Link] ARIDJ Mohamed
R2 8

L’algèbre relationnelle L’algèbre relationnelle

L’intersection(2) La différence (1)
La différence entre deux relations R1 et R2 de même schéma est une
relation R de même schéma contenant l’ensemble des tuples appartenant
à R1 et n’appartenant pas à R2.
R1 A B C R2 A B C
a d 1
a
b
d
e
1
2 s e 4 Notation : R1 – R2 ou minus (R1, R2)
c f 3 c d 3

Représentation graphique :
R

R=Inter (R1,R2) A B C
a d 1
-
R1
R2
17:16 L2 BD [Link] ARIDJ Mohamed 9 17:16 L2 BD [Link] ARIDJ Mohamed 10

L’algèbre relationnelle L’algèbre relationnelle

La différence (2) Le produit cartésien (1)
Le produit cartésien de deux relations R et S de schémas quelconques
est une relation T ayant pour attributs la concaténation des attributs de
R et de S et dont les tuples Sont constitués de toutes les concaténations
R A B C T A B C d’un tuple de R à un tuple de S.
a d 1 a d 1
b e 2 s e 4
c f 3 c d 3 Notation : R x S ou product(R,S)

Représentation graphique :

R- T A B C
X
b e 2
c f 3

R
S
17:16 L2 BD [Link] ARIDJ Mohamed 11 17:16 L2 BD [Link] ARIDJ Mohamed 12

2
 L’algèbre relationnelle L’algèbre relationnelle
Le produit cartésien (2) La division (1)
La division (ou quotient) de la relation R (A1, A2, …, An) par
la (sous-relation ) D de schéma D(A p+1, …, An) est la relation de
schéma Q (A1, A2, …, Ap) formée de Tous les tuples qui, concaténés à
R A B C S D E chaque tuple de S, donnent toujours un tuple de R.
a d 1 a d
b e 2 b e
c f 3 Notation : R / S ou div(R,S)

Représentation graphique :
RxS A B C D E Q
a d 1 a d
a d 1 b e
b e 2 a d ÷
b e 2 b e
c f 3 a d
c f 3 b e
R S
17:16 L2 BD [Link] ARIDJ Mohamed 13 17:16 L2 BD [Link] ARIDJ Mohamed 14

L’algèbre relationnelle
La division (2)

R A B C D
c S C D
a b c c d
f
a b e d f c d
b c e f e f
e d c d
e d e f
a b d e

R/S A B
a b
e d

17:16 L2 BD [Link] ARIDJ Mohamed 15 17:16 L2 BD [Link] ARIDJ Mohamed 16

L’algèbre relationnelle L’algèbre relationnelle

Opérateurs relationnels L’affectation
• Affectation : opération qui consiste à transférer des tuples d’une table
dans une autre table. Cette opération est notée 
L’affectation permet de sauvegarder le résultat d’une expression de recherche ou bien
• Sélection / Restriction : Opération qui consiste à supprimer les tuples de renommer une relation et ses attributs.
d’une relation ne satisfaisant pas la condition précisée. Cette opération est Notation : 
notée s Représentation graphique :
Exemple :
• Projection : Opération qui consiste à supprimer des attributs d’une R A B C
relation et à éliminer les tuples en double apparaissant dans la nouvelle
a d 1
relation. Cette opération est notée p . b e 2
S(D,E,F)  R donne : c f 3
• La jointure : opération qui consiste à faire le produit cartésien de deux
relations, puis à supprimer les tuples ne satisfaisant pas une condition
portant sur un attribut de la première relation et sur un attribut de la S R D E F
seconde. a d 1
b e 2
c f 3
17:16 L2 BD [Link] ARIDJ Mohamed 17 17:16 L2 BD [Link] ARIDJ Mohamed 18

3
 L’algèbre relationnelle L’algèbre relationnelle
La Sélection (1) La restriction (2)
La sélection (ou restriction ) d’une relation R par un prédicat P est une relation S
de même schéma dont les tuples sont ceux de R qui satisfont à le prédicat R A B C
(condition) P. a d 1
b e 2
c f 3
P est un prédicat qui peut s’exprimer à l’aide de constante, d’attributs, de
comparateurs : (>, >=, <, <=,=,<>) et d’opérateurs logiques ( ,, )
Select (R)
Notation : select P (R) ou select (R/ P) s P ( R ) C < 4 et A <> ‘a’

Représentation graphique :
Select (R) A B C
C < 4 et A <> ‘a’
b e 2
P c f 3

17:16 L2 BD [Link]
R
ARIDJ Mohamed 19 17:16 L2 BD [Link] ARIDJ Mohamed 20

L’algèbre relationnelle L’algèbre relationnelle

La projection (1) La projection (2)
La projection d’une relation R de schéma R(A1, A2, …, An) sur les
attributs Ai1, Ai2, …, Aip avec p<= n est une relation S (Ai1, Ai2, …, Aip )
Exemple :
R A B C
dont Les tuples sont obtenus par élimination des attributs de R
a d 1
b e 2
n’appartenant pas à R’ et par suppression des doublons c f 3

Notation proj Ai1, Ai2, …, Aip (R) / proj (R / Ai1, Ai2, …, Aip )
ou p Ai1, Ai2, …, Aip (R
proj A,B(R) A B proj A (R) A
a d a
Représentation graphique : b e b
c f c
Ai1, Ai2, …, Aip

17:16 L2 BD [Link] ARIDJR Mohamed 21 17:16 L2 BD [Link] ARIDJ Mohamed 22

L’algèbre relationnelle
L’algèbre relationnelle
La jointure (1)
La jointure (2)
La jointure de deux relations R et S selon un prédicat P est
l’ensemble des Tuples du produit cartésien R qui satisfont à la
condition du prédicat P.
R A B C S D E
Notation : joint P (R,S) a d b f
b
d
e
b e g
c f c
Représentation graphique
R (S)
B<D et A != C

P R
B<D et A != C
S A B C D E
a d b f d
b e g f d
R S

17:16 L2 BD [Link] ARIDJ Mohamed 23 17:16 L2 BD [Link] ARIDJ Mohamed 24

4
 L’algèbre relationnelle L’algèbre relationnelle
L’équi-jointure La jointure naturelle (1)

L’équi-jointure de deux relations R et S est une q jointure avec pour qualification

La jointure naturelle de deux relations R et S est une équi-jointure sur tous les
Q l’égalité entre deux colonnes, c’est-à-dire R Ai = Bj S avec Ai et Bj, deux attributs de même nom dans R et dans S, suivie de la projection qui permet de ne
conserver qu’un seul des cas attributs égaux de même nom.
attributs de R et de S respectivement
Notation : R S ou joint (R,S)
R A B C
S D E
a d d d f Représentation graphique :
b e g b e
c f c

R S A B C D E
B= D
a d d d f
R S
17:16 L2 BD [Link] ARIDJ Mohamed 25 17:16 L2 BD [Link] ARIDJ Mohamed 26

L’algèbre relationnelle
Problèmes liés à la jointure (1)
La jointure naturelle (2)

La jointure n’inclut les tuples que s’il y a égalité entre deux colonnes
R A B C S A B D Exemple : on désire la liste de tous les départements de l’entreprise avec les employés
a d s a d d Associés.
b e g a d g
c f c c f c Dpt Did Dnom Dville
D1 Achats Amiens
D2 Recherche Boves
D3 Ventes Ailly
D4 Informatique Dreuil
R S A B C D
a d s d Emp Eid Enom Epren Did
a d s g E10 Black John D3
c f c c E20 White Bob D3
E30 Léger Ferdinand D2
E40 Flam Captain D3
E50 Albator Roger D1
E60 DreamBox René D1

17:16 L2 BD [Link] ARIDJ Mohamed 27 17:16 L2 BD [Link] ARIDJ Mohamed 28

Problèmes liés à la jointure (2) Agrégats

Un agrégat est un partitionnement horizontal d ’une relation selon des valeurs
d ’attributs, suivi d ’un regroupement par une fonction de calcul

Notation : Fonction_d ’Agrégat Ai1,Ai2,…,Aip (R)

Dpt Emp Did Dnom Dville Eid Enom Epren Représentation graphique :
D1 Achats Amiens E50 Albator Roger
D1 Achats Amiens E60 Dreambox René
D2 Recherche Boves E30 Léger Ferdinand
FA Ai1,Ai2,…,Aip
D3 Ventes Ailly E10 Black John Fonction d’agrégation:
D3 Ventes Ailly E40 Flam Captain COUNT
D3 Ventes Ailly E20 White Bob SUM R
AVG
MAX
MIN

Remarque : il nous manque le département informatique !!!

17:16 L2 BD [Link] ARIDJ Mohamed 29 17:16 L2 BD [Link] ARIDJ Mohamed 30

5
 Agrégats : Exemple (1) Agrégats : Exemple(2)

R A B C Count (R) Count R A B C Max (R,C) MAX

a b 10 a b 10
d a 15 4 d a 15 15
c b 5 c b 5
b g 8 b g 8

AVG(R , C) Moyenne MIN(R , C) Min

9,5 5

Somme B (R, C) B Somme

SUM(R , C) Somme
b 15
28
a 15
g 8

17:16 L2 BD [Link] ARIDJ Mohamed 31 17:16 L2 BD [Link] ARIDJ Mohamed 32

Exemple
Arbre algébrique
PILOTE (NUMPIL, NOMPIL, ADR, SAL)

AVION (NUMAV, NOMAV, CAPACITE, LOC)

VOL (NUMVOL, NUMPIL, NUMAV, VILLE_DEP, VILLE_ARR, H_DEP, H_ARR)

17:16 L2 BD [Link] ARIDJ Mohamed 33 17:16 L2 BD [Link] ARIDJ Mohamed 34

Q1: Donnez la liste des avions dont la capacité est supérieure à

350 passagers. Q5: Quel est le nom des pilotes domiciliés à Paris dont le salaire
est supérieur à 15000 ?
R1.1= Sélection (AVION / CAP > 350)
R5.1 = Sélection (PILOTE / ADR = 'PARIS')

Q2 : Quels sont les numéros et noms des avions localisés à Nice ? R5.2 = Sélection (R5.1 / SAL > 15000)
R5.3 = Projection (R5.2 / NOMPIL)
R2.1 = Sélection (AVION / LOC = 'NICE')
Q6 : Quels sont les avions (numéro et nom) localisés à Nice ou
R2.2 = Projection (R2.1 / NUMAV, NOMAV)
dont la capacité est inférieure à 350 passagers ?

Q3 : Donnez toutes les informations sur les pilotes de la compagnie. R6.1 = Sélection (AVION / CAP < 350)
R6.2 = Projection (R6.1 / NUMAV, NOMAV)
R3.1 = PILOTE
R6.3 = Union (R2.2, R6.2)
( R2.2 est la relation générée pour Q2)
17:16 L2 BD [Link] ARIDJ Mohamed 35 17:16 L2 BD [Link] ARIDJ Mohamed 36

6
Q7 : Liste des vols au départ de Nice allant à Paris après 18h? Q10 : Donnez le numéro des vols effectués au départ de Nice par
des pilotes Niçois ?
R7.1 = Sélection (VOL / VILLE_DEP = 'NICE')
R10.1 = Sélection (PILOTE / ADR = 'NICE')
R7.2 = Sélection (VOL / VILLE_ARR = 'PARIS')
R10.2 = Jointure (R10.1, R7.1 / NUMPIL = NUMPIL)
R7.3 = Sélection (VOL / H_DEP > 18.00)
(R7.1 générée pour Q7)
R7.4 = Inter (R7.1, R7.2)  R7.1  R7.2
R10.3 = Projection (R10.2 / NUMVOL)
R7.5 = Inter(R7.3, R7.4)  R7.3  R7.4
Q11 : Quels sont les vols (NUMVOL) effectués par un avion qui
n'est pas localisé à Nice ?
Q8:Quels sont les numéros des pilotes qui ne sont pas en service ?
R11.1 = Sélection (AVION / LOC ≠ 'NICE')
R8.1 = Projection (PILOTE / NUMPIL)
R11.2 = Jointure (VOL, R11.1 / NUMAV = NUMAV)
R8.2 = Projection (VOL / NUMPIL)
R11.3 = Projection (R11.2 / NUMVOL)
R8.3 = Minus(R8.1 , R8.2) R8.1 - R8.2

17:16 L2 BD [Link] ARIDJ Mohamed 37 17:16 L2 BD [Link] ARIDJ Mohamed 38

13. Quels sont les pilotes (NUMPL) ayant effectuée au moins Q15 : Quels sont les pilotes (numéro et nom) habitant dans la
un vol sur toutes les avion de la compagnie ? même ville que le pilote Dupont ?

R15.1 = Sélection (PILOTE / NOMPIL = 'DUPONT')

R15.2 = Jointure (PILOTE, R15.1 / ADR = ADR)
Q14 : Quelles sont les villes desservies à partir de la ville d'arrivée R15.3 = Projection (R15.2 / NUMPIL, NOMPIL)
d'un vol au départ de Paris ?

R14.1 = Sélection (VOL / VILLE_DEP = 'PARIS')

R14.2 = Jointure (VOL, R14.1 / VILLE_DEP = VILLE_ARR)
R14.3 = Projection (R14.2 / VILLE_ARR)

17:16 L2 BD [Link] ARIDJ Mohamed 39 17:16 L2 BD [Link] ARIDJ Mohamed 40

Q16 : Y a-t-il des homonymes parmi les pilotes ? Si oui, donner

leur numéro et nom.

R16.1 = Jointure (PILOTE, PILOTE / NOMPIL = NOMPIL)

R16.2 = Jointure (PILOTE, PILOTE / NUMPIL = NUMPIL)
R16.3 = Minus (R16.1, R16.2)
R16.4 = Projection (R16.3 / NUMPIL, NOMPIL)

17:16 L2 BD [Link] ARIDJ Mohamed 41

7
 PLAN
1. Introduction
Chapitre 6
2. LMD: Langage de manipulation des données
Langage SQL
3. LDD: Langage de définition des données
(Structured Query Langage) 4. LCD: Langage de contrôle des données.

L2 BD [Link] ARIDJ Mohamed

17:17 1 17:17 L2 BD [Link] ARIDJ Mohamed 2
/45

SQL Classification des requêtes SQL(1)

• Langage de requêtes (Structured Query Language )
• Origine
– 1975 : QUEL
1. Le langage de définition de données LDD :
– 1976 : Structured English QUEry Langage (SEQUEL) par IBM (Data Definition facilities) :
– 1981 : SQL par IBM
– CREATE,
• Il existe trois versions normalisées, du simple au
complexe : – ALTER,
– SQL1 86 version minimale
– SQL1 89 addendum (intégrité) – DROP,
– SQL2 (92) langage complet à 3 niveaux
– RENAME, ……………….
• Une version 3 étendue (objets, règles) est la norme 99.
• La plupart des systèmes supportent SQL2 complet
• SGBDR : Oracle, Sybase, DB2, Ingres, MS SQL Server,
MySQL, MS Access ...
• Mais des différences subsistent selon le SGBD utilisé.

17:17 L2 BD [Link] ARIDJ Mohamed 3 /45 17:17 L2 BD [Link] ARIDJ Mohamed 4 /45

Classification des requêtes SQL(2) Classification des requêtes SQL(3)

2. Langage de manipulation de données (LMD) : 3. langage de contrôle (Data Control facilities ) :
3.1 Transaction Control Langage :
2. 1 langage d’interrogation : (query facilities) – COMMIT
– SELECT FROM WHERE
– SAVEPOINT
– ROLLBACK
2.2 langage de mise à jours de données – SET TRANSACTION
(Data manipulation facilities) 3.2 Data Control Language :
– INSERT, – GRANT
– UPDATE – REVOKE
– DELETE……….,
17:17 L2 BD [Link] ARIDJ Mohamed 5 /45 17:17 L2 BD [Link] ARIDJ Mohamed 6 /45

1
 Langage de manipulation de donnée Langage de manipulation de donnée
requêtes de d’interrogation requêtes de d’interrogation

Schéma d’illustration SQL =une clase globale pour l’interrogation

des base de données :
CLIENT (N°C, NomC, AdrC, CP, Ville, Tél)
PRODUIT (N°P, Design, Prix, QtéP, Ville)
SELECT < liste d’attribue >
COMMANDE (N°Comm, N°C, N°P, QtéC, DateC)
FROM <liste de relation >
Where < liste de condition>

17:17 L2 BD [Link] ARIDJ Mohamed 7 /45 17:17 L2 BD [Link] ARIDJ Mohamed 8 /45

Langage de manipulation de donnée Langage de manipulation de donnée

requêtes de d’interrogation requêtes de d’interrogation

Projection Sélection
SQL
Algèbre relationnel SQL
Algèbre relationnel
Proj ( relation / A1, A2……An) SELECT A1,….A2 SELECT *
Select ( Relation / condition )
FROM relation FROM Relation
Where condition
Exemple :Lister les noms de tous les clients
Exemple : Trouver les clients de la ville de « Chlef »
SELECT NomC
FROM Client
SELECT *
FROM Client
WHERE Ville = 'Chlef‘
17:17 L2 BD [Link] ARIDJ Mohamed 9 /45 17:17 L2 BD [Link] ARIDJ Mohamed 10 /45

Langage de manipulation de donnée Langage de manipulation de donnée

requêtes de d’interrogation requêtes de d’interrogation
Renommage des colonnes = Trier les n-uplets du résultat
SELECT NomColonne(s) AS NomColonneRésultat ORDER BY <expression> [ ASC | DESC ] [, ...]

Suppression des doublons = Exemple :

Lister les libellés et prix des produits triés par ordre de
SELECT DINSTINCT NomColonne(s) prix croissants »
FROM table
SELECT Design, Prix
FROM Produit
Extraction de toutes les colonnes d'une table =
ORDER BY Prix ASC
SELECT *
17:17 FROM table L2 BD [Link] ARIDJ Mohamed 11 /45 17:17 L2 BD [Link] ARIDJ Mohamed 12 /45

2
 Langage de manipulation de donnée Langage de manipulation de donnée
requêtes de d’interrogation requêtes de d’interrogation
Requêtes imbriquées Utilisation des opérateurs ensembliste

• La clause WHERE peut contenir un Select (Select

Exemple Where
From ) OP ( Select
« Désignation des produits dont le prix = prix d'une imprimante »
Where
SELECT Design From )
FROM Produit
OP = {UNION , INTERSECT, MINUS , DIV}
WHERE Prix = (SELECT Prix

FROM Produit

WHERE Design = ' imprimante ‘)

17:17 L2 BD [Link] ARIDJ Mohamed 13 /45 17:17 L2 BD [Link] ARIDJ Mohamed 14 /45

Langage de manipulation de donnée Langage de manipulation de donnée

requêtes de d’interrogation requêtes de d’interrogation
• Exemples:
« Donnez le numéro et la désignation des produit dans le prix est
> 1500 et qui sont produis à ‘Chlef ‘» Produit cartésien
SQL
Algèbre relationnel
SELECT NP, DESP
Prod ( R ,S) SELECT *
FROM PRODUIT
FROM R S
WHERE (PRIXP > 1500) and (VILLE = ‘Chlef’)
(SELECT NP, DESP
FROM PRODUIT
WHERE (PRIXP > 1500)) INTERSECT (SELECT NP, DESP
FROM PRODUIT
WHERE VILLE=‘CHLEF’))

17:17 L2 BD [Link] ARIDJ Mohamed 15 /45 17:17 L2 BD [Link] ARIDJ Mohamed 16 /45

Langage de manipulation de donnée Langage de manipulation de donnée

requêtes de d’interrogation requêtes de d’interrogation

Jointure
SQL « donner la désignation de touts les
«Algèbre
• join relationnel
( R , S / condition )
SELECT * produit commandés en 2014 »
FROM R,S
Where condition
SELECT Desp
Exemple : « joindre client et commande sur le N°c » From Produit , Commande
Where ( DateC=2014)
SELECT *
and ([Link]=[Link])
FROM Client,Commande
WHERE Client.N°c=Commande.N°c
17:17 L2 BD [Link] ARIDJ Mohamed 17 /45 17:17 L2 BD [Link] ARIDJ Mohamed 18 /45

3
 Langage de manipulation de donnée Langage de manipulation de donnée
requêtes de d’interrogation requêtes de d’interrogation
[Link] le nombre de clients de la ville de Chlef
• Fonction d’agrégation :
SELECT COUNT (*)
– MAX : Valeur maximale des éléments d'une colonne FROM Client
WHERE Ville = 'Chlef‘
– MIN : Valeur minimale des éléments d'une colonne
– AVG : Moyenne arithmétique des valeurs d'une
colonne [Link] le prix moyen des produits
– SUM : Somme des valeurs d'une colonne SELECT AVG (Prix) AS Moyenne
– COUNT : Nombre d'occurrences d'une colonne FROM Produit

17:17 L2 BD [Link] ARIDJ Mohamed 19 /45 17:17 L2 BD [Link] ARIDJ Mohamed 20 /45

Langage de manipulation de donnée Langage de manipulation de donnée

Langage de mise à jours de données Langage de mise à jours de données
1. Insertion de n-uplets : INSERT INTO Client_chlef (N°C, NomC, AdrC, CP, Ville, Tél)

INSERT INTO nom_table VALUES (val_1, val_2, ...) « Insérer à partir le la table Client tous les client
installés à Chlef dans la table Client_chlef »
Exemple :
Ajouter le nouveau produit (212, ' imprimante ', 98, 50, 'Alger') INSERT INTO Client_chlef
(SELECT * FROM Client WHERE (Ville= ‘Chlef’))
INSERT INTO Produit
VALUES ( 212, ' imprimante ', 98, 50, 'Alger')

17:17 L2 BD [Link] ARIDJ Mohamed 21 /45 17:17 L2 BD [Link] ARIDJ Mohamed 22 /45

Langage de manipulation de donnée Langage de manipulation de donnée

Langage de mise à jours de données Langage de mise à jours de données
2. Modification de n-uplets : UPDATE «Augmenter le prix de 5% des produits dont le prix est > 1000 »
UPDATE Produit
UPDATE nom_table
SET Prix = Prix * 1.05
SET nom_col_1 = {expression_1 } WHERE Prix > 1000
nom_col_2 = {expression_2},

... «Attribuer au NP 123 le même prix qu'au NP 52 »

UPDATE Produit
nom_col_n = {expression_n}
SET Prix = (SELECT Prix FROM Produit WHERE NP = 52)
WHERE condition WHERE NP = 123

17:17 L2 BD [Link] ARIDJ Mohamed 23 /45 17:17 L2 BD [Link] ARIDJ Mohamed 24 /45

4
 Langage de manipulation de donnée Langage de manipulation de donnée
Langage de mise à jours de données Langage de mise à jours de données
3. Suppression de n-uplets : DELETE 4. Modifier le schéma d’une relation
4.1 ajouter un ou plusieurs attributs
DELETE FROM nom_table ALTER TABLE nom_table ADD
WHERE predicat ([nom_colonne1 type [contrainte]
([nom_colonne2 type [contrainte] ,
Exemple ...])
« Supprimer toutes les commandes avant 1/1/1997 » Exemple:
« Ajouter le champ Email à la table CLIENT »
DELETE FROM Commande
WHERE Date < '1/1/1997' ALTER TABLE CLIENT ADD
(Email VarChar(30) NOT NULL)
17:17 L2 BD [Link] ARIDJ Mohamed 25 /45 17:17 L2 BD [Link] ARIDJ Mohamed 26 /45

Langage de manipulation de donnée Langage de définition de données

Langage de mise à jours de données Création d’une table (Syntaxe)
4. Modifier le schéma d’une relation
CREATE TABLE nomTable
4.2. Modifier un ou plusieurs attributs
ALTER TABLE nom_table MODIFY ( Attribut 1 Domaine [Contraintes ...],
([nom_colonne1 type [contrainte] Attribut 2( Domaine [Contraintes ...],
([nom_colonne2 type [contrainte] , ...
...]) Attribut n Domaine [Contraintes ...])
Exemple:
« Modifier le type du champ Email à la table CLIENT »

ALTER TABLE CLIENT MODIFY

(Email VarChar(50) NOT NULL)
17:17 L2 BD [Link] ARIDJ Mohamed 27 /45 17:17 L2 BD [Link] ARIDJ Mohamed 28 /45

Langage de définition de données Langage de définition de données

Création d’une table (Domaines) Création d’une table (Contraintes)

INTEGER : entiers 4 octets. NOT NULL : Interdire la valeur 0

BIGINT : entiers 8 octets.
NULL : autorise la valeur 0
REAL : codés 4 octets.
UNIQUE : Interdire des valeurs identiques pour cet attribut
DOUBLE PRECISION : réels 8 octets.
CHAR(longueur) :chaînes de caractères de longueur fixe <256 PRIMARY KEY : l’attribut clé primaire
VARCHAR(longueur) : longueur variable< 2000 CHECK (condition) :Vérifie lors de l’insertion « condition ».
DATE : date. DEFAULT valeur : la valeur par défaut
TIMESTAMP date et d’une heure.
BOOLEAN : Booléenne.
MONEY : valeurs monétaires.
TEXT : chaînes de caractères de longueur variable.
17:17 L2 BD [Link] ARIDJ Mohamed 29 /45 17:17 L2 BD [Link] ARIDJ Mohamed 30 /45

5
 Langage de définition de données Langage de définition de données
Création d’une table (Exemple) Supprimer une table
« créer la table Client »
La syntaxe DROP TABLE nom_table
CREATE TABLE Client
(NC integer NOT NULL, PRIMARY KEY,
Exemples :
NomC VarChar(20) NOT NULL, Supprimer table Client
AdrC VarChar(50) NOT NULL,
DROP TABLE Client
CP integer NOT NULL,
Ville VarChar(30) ,
Tél VarChar(10))

17:17 L2 BD [Link] ARIDJ Mohamed 31 /45 17:17 L2 BD [Link] ARIDJ Mohamed 32 /45

Langage de définition de données

Administration de Base de Données Langage de contrôle de données
Administration des Utilisateurs
Création d'une base de données • GRANT privilege ON object TO user ;
• REVOKE privilege ON object FROM user ;
CREATE DATABASE NomBdd;

– privilege = {SELECT|INSERT|UPDATE|DELETE|…
Destruction totale d'une base de données
|ALTER|DROP|ALL}

DROP DATABASE NomBdd; – object ={nom de TABLE , nom base …

17:17 L2 BD [Link] ARIDJ Mohamed 33 /45 17:17 L2 BD [Link] ARIDJ Mohamed 34 /45

Langage de contrôle de données EXEMPLE

Administration des transaction
– COMMIT, SAVEPOINT, ROLLBACK,SET TRANSACTION
PILOTE (NUMPIL, NOMPIL, ADR, SAL)

• Set Transaction AVION (NUMAV, NOMAV, CAPACITE, LOC)

– update
VOL (NUMVOL, NUMPIL, NUMAV, VILLE_DEP, VILLE_ARR, H_DEP, H_ARR)
– update unité d'oeuvre

– savepoint Non perte du contexte

– update
unité d'oeuvre
– update
• Commit (Roll back)

17:17 L2 BD [Link] ARIDJ Mohamed 35 /45 17:17 L2 BD [Link] ARIDJ Mohamed 36 /45

6
Q1: Donnez la liste des avions dont la capacité est supérieure à Q3 :
350 passagers. a) Donnez toutes les informations sur les pilotes de la compagnie.

R1.1= Sélection (AVION / CAP > 350)

R3.1 = PILOTE / SELECT * FROM PILOTE
SELECT * FROM AVION WHERE (CAP >350)

Q2 : Quels sont les numéros et noms des avions localisés à Nice ?

b) Donner le nombre de pilotes de la compagnie
R2.1 = Sélection (AVION / LOC = 'NICE')
R2.2 = Projection (R2.1 / NUMAV, NOMAV) SELECT COUNT(*) FROM PILOTE

SELECT NUAV, NOMAV FROM AVION WHERE LOC=‘NICE’

17:17 L2 BD [Link] ARIDJ Mohamed 37 /45 17:17 L2 BD [Link] ARIDJ Mohamed 38 /45

Q4:
a) Donner le salaire moyen des pilotes de la compagnie. Q5: Quel est le nom des pilotes domiciliés à Paris dont le salaire
est supérieur à 15000 ?

SELECT AVG(SAL) FROM PILOTE R5.1 = Sélection (PILOTE / ADR = 'PARIS')

R5.2 = Sélection (R5.1 / SAL > 15000)
R5.3 = Projection (R5.2 / NOMPIL)
b) Donner le nom du pilote le mieux payé de la compagnie

SELECT NOMPL FROM PILOTE SELECT NOMPIL FROM PILOTE WHERE (ADR=PARIS)
WHERE SAL = SELECT MAX(SAL) FROM PILOTE
and (SAL >1500)
SELECT NOMPIL FROM PILOTE WHERE (ADR=PARIS)
INTERSECT
SELECT NOMPIL FROM PILOTE WHERE (SAL >1500)
17:17 17:17 L2 BD [Link] ARIDJ Mohamed 40 /45
L2 BD [Link] ARIDJ Mohamed 39 /45

Q6 : Quels sont les avions (numéro et nom) localisés à Nice ou Q7 : Liste des vols au départ de Nice allant à Paris après 18h?
dont la capacité est inférieure à 350 passagers ?
R7.1 = Sélection (VOL / VILLE_DEP = 'NICE')
R7.2 = Sélection (VOL / VILLE_ARR = 'PARIS')
SELECT NUMAV, NOMAV FROM AVION WHERE (CAP < 350) R7.3 = Sélection (VOL / H_DEP > 18.00)
OR LOC =‘NICE’ R7.4 = Inter (R7.1, R7.2)  R7.1  R7.2
R7.5 = Inter(R7.3, R7.4)  R7.3  R7.4

SELECT NUMAV, NOMAV FROM AVION WHERE (CAP < 350)

UNION
SELECT NUMAV, NOMAV FROM AVION WHERE (LOC =‘NICE’)

17:17 L2 BD [Link] ARIDJ Mohamed 41 /45 17:17 L2 BD [Link] ARIDJ Mohamed 42 /45

7
Q8:Quels sont les numéros des pilotes qui ne sont pas en service ? Q9 : Quels sont les vols (NUMVOL) effectués par un avion qui
n'est pas localisé à Nice ?
R8.1 = Projection (PILOTE / NUMPIL)
R9.1 = Sélection (AVION / LOC ≠ 'NICE')
R8.2 = Projection (VOL / NUMPIL)
R9.2 = Jointure (VOL, R9.1 / NUMAV = NUMAV)
R8.3 = Minus(R8.1 , R8.2) R8.1 - R8.2
R9.3 = Projection (R9.2 / NUMVOL)

SELECT NUMPIL FROM PILOTE

MINUS
SELECT NUMVOL
SELECT NUMPIL FROM VOL
FROM VOL , AVION
WHERE ([Link] = [Link]) AND
SELECT NUMP FROM PILOT
(LOCALISATION <> ’NICE’);
WHERE NUMP NOT IN (SELECT NUMP FROM VOL);
17:17 L2 BD [Link] ARIDJ Mohamed 43 /45 17:17 L2 BD [Link] ARIDJ Mohamed 44 /45

13. Quels sont les pilotes (NUMPL) ayant effectuée au moins

un vol sur toutes les avion de la compagnie ?

17:17 L2 BD [Link] ARIDJ Mohamed 45/45

8
 01/02/2019

PLAN

• Introduction
Chapitre 7
Evaluation et Optimisation de • Traitement des requêtes
requêtes • Optimisation de requêtes

Traitement d’une requête

Introduction SQL

•Requêtes exprimées en SQL: langage déclaratif Analyse

– On indique ce que l'on veut obtenir
– On ne dit pas comment l'obtenir Modification
• Le SGBD doit faire le reste
– Déterminer la façon d'exécuter la requête: plan d'exécution
Plan d’executions Optimisation
– Plusieurs plans possibles choisir le meilleur : optimisation
– Exécuter le plan choisi: évaluation
Exécution
• Plan d'exécution
– Exprimé en algèbre relationnelle (expression algébrique)
– Forme exécutable: on sait précisément comment l'évaluer

Optimisation des requêtes Optimisation des requêtes

• Objectif : trouver le plan d’exécution le moins « coûteux »

• Pour optimiser,:
« descendre les opérateurs de sélection et
• Fonction de coût : donne une estimation du coût total projection le plus près possible des feuilles »
réel d’’un plan d’exécution •

• Coût total = coût I/O (entrées/sorties) + coût CPU • On passe d'une expression à une autre
équivalente en utilisant des :
On peut négliger le coût CPU : coût I/O = 1000 coût CPU
« règles de réécriture »

1
 01/02/2019

R1- Séquence de sélections : R2- Commutativité de la sélection

SELECTION C1 and c2 ... and Cn(R) = SELECTION C1( SELECTION C2(...( SELECTION Cn(R))))
SELECTION C1( SELECTION C2(R)) = SELECTION C2( SELECTION C1(R))

Cn
C2
C1

C1 et C2

et Cn
C2
C1
C2

Cn

R4- Commutation de la sélection et de

R3- Séquence de projections :
la projection :
PROJECTION L1( PROJECTION L2(...( PROJECTION Ln(R)))) = PROJECTION L1(R)
PROJECTION A1, A2, ..., An( SELECTION C(R)) =
SELECTION C( PROJECTION A1, A2, ..., An(R))

L1

L
L2
C
L1

Ln

R6- Commutation de la sélection et de

R5-Commutativité de la jointure :
la jointure (ou produit cartésien) :
Join ( R , S /C) = Join ( S,R /C)
SELECTION C(Join (R , S/ P) : On distingue trois cas :

a) si C porte sur R :

SELECTION C(Join (R , S/ P) = Join (SELECTION C(R), S /P)

C C
C
R S S R
P

P C

R S S
R

2
 01/02/2019

R6- Commutation de la sélection et de R6- Commutation de la sélection et de

la jointure (ou produit cartésien) : la jointure (ou produit cartésien) :
SELECTION C(Join (R , S/ P) : On distingue trois cas : SELECTION C(Join (R , S/ P) : On distingue trois cas :

C) si C = C1 and C2 : C1 porte R et C2 S
b) si C porte sur S :

SELECTION C(Join (R , S/ P) = Join (SELECTION C(R), S /P) SELECTION C(Join (R , S/ P) = Join (SELECTION C(R), S /P)

C C=C1 and C2

P P

C C1 C2
P P

R S R R S
S R S

R7- Commutation de la projection et R7- Commutation de la projection et

de la jointure (ou produit cartésien) : de la jointure (ou produit cartésien) :
PROJECTION L(R * S) : On distingue trois cas : PROJECTION L(R * S) : On distingue trois cas :

a) si L est composée seulement d’attribues de R b) si L est composée seulement d’attribues de S

PROJECTION L(join (R , S / P ) =Join ( PROJECTION L (R) ,S / P) ROJECTION L(join (R , S / P ) =Join ( S,PROJECTION L (S) / P)

L P L P

L L
P P

S
R S R R S R S

R7- Commutation de la projection et R8- Commutativité des opérations

de la jointure (ou produit cartésien) : ensemblistes (union et intersection)
PROJECTION L(R * S) : On distingue trois cas :
O = { UNION , INTERSECTION }
C) si L=L1 U L2 : L1 de R et L2 de S
(R O S) = (S O R)
PROJECTION L(join (R , S / P ) =
Join ( PROJECTION L1 (R) , PROJECTION L2 (S) / P)

o o
L=L1UL2
P
R S R
S
L1 L2
P

R S R S

3
 01/02/2019

R10- Commutation de la sélection avec

R9- Associativité de la jointure, du produit
les opérateurs ensemblistes :
cartésien, de l'union et de l'intersection :
O = { UNION , INTERSECTION , -}

O = { UNION , INTERSECTION , *, X, join}

(R O S) O T = R O (S O T) SELECTION C(R O S) = ( SELECTION C(R)) O ( SELECTION C(S))

o
o o
C

o
R
o
o C C

R S
R S S T
R S

R11- Commutation de la projection Algorithme général d'optimisation

avec les opérateurs ensemblistes : Etape1 : Séparer les sélections conjonctives en une séquence de sélections
O = { UNION , INTERSECTION , -} (règle 1)

PROJECTION L(R O S) =( PROJECTION L(R)) O ( PROJECTION L(S)) Etape2 : Descendre les opérations de sélection le plus bas possible dans
l'arbre (règles 2, 4, 6 et 10)

Etape3 : Réarranger les feuilles de l'arbre pour évaluer les sélections les
plus restrictives d'abord (règle 9)

L1 o Etape4 : Combiner les produits cartésiens avec des expressions de

sélection appropriées pour en faire des jointures

o L1 L1
Etape5 : Faire des projections le plus tôt possible dans l'arbre pour
manipuler seulement l'information intéressante (règles 3, 4, 7 et 11)
R S R S
Etape6: Identifier les sous arbres qui peuvent être exécutés par un seul
algorithme (les sélection - projection - jointure par exemple)

Exemple Arbre de la requête

non optimisé
Livre(CoL, titre, ...) , Prêt(NuE, CoL, date), Etudiant( NuE² nom, ...)

« Lister les noms des étudiants et des titres des livres pour tous les prêts d'avant le Nom, titre Projection
'15/06/2004' »
SELECT nom, titre ncarte=ncarteP &
FROM Prêt, Lecteur , Livre code=codeP & Sélection
date<15/06/2004
WHERE Prê[Link]=[Link] AND
SQL
Pré[Link] =[Link] AND
date < '15/06/2004‘; Jointure sue Col

Jointure sur
NuE
Livre
R1 Join (Prêt , Etudiant/ Prê[Link]=[Link]
A.L R2 Join (R1, Livre/ [Link]=[Link]) Prêt Etudiantr

R3 Select (R2/date<‘15/06/2004’

Réponse proj(R3/ nom, titre)

4
 01/02/2019

Arbre de la requête Arbre de la requête

après décomposition des sélections en cascade après descente des sélections

Nom, titre nom, titre

Etape 1 Etape 2
ncarte=ncarteP
code=codeP

code=codeP
Règle R1

date<15/06/2004

ncarte=ncarteP Livre

Livre
date<15/06/2004 Lecteur

Prêt Lecteur Prêt

W.K. Hidouci, ESI 2009

Arbre de la requête Arbre de la requête

descente des projections 1/4 descente des projections 2/4

nom, titre nom, titre

code=codeP code=codeP

nom,titre,code,codeP

nom,codeP titre,code

Livre
ncarte=ncarteP Livre ncarte=ncarteP

date<15/06/2004 Lecteur date<15/06/2004 Lecteur

Prêt Prêt

Arbre de la requête Arbre de la requête

descente des projections 3/4 descente des projections 4/4

nom, titre
nom, titre
code=codeP
code=codeP

nom,codeP titre,code
nom,codeP titre,code
ncarte=ncarteP
ncarte=ncarteP Livre
Livre

nom,codeP,ncarte,ncarteP

codeP,ncarteP nom,ncarte

date<15/06/2004 Lecteur date<15/06/2004 Lecteur

Prêt Prêt

5
 01/02/2019

Arbre de la requête
groupage des opérateurs unaires autour des op. binaires

nom, titre
Le groupage détermine l'ordre d'exécution:
- on commence par les groupes feuilles code=codeP
- un groupe ne peut être évalué qu'après equi-
exécution de ses groupes fils. join

nom,codeP titre,code

ncarte=ncarteP equi-
join Livre
scan

codeP,ncarteP nom,ncarte

date<15/06/2004 Lecteur
scan scan
Prêt