Les bases de données relationnelles
Base de données relationnelles
Introduction 3
Le modèle relationnel 4
Définitions 4
Les contraintes d’intégrité sur les domaines : 7
Conception d’un schéma relationnel 8
Introduction 8
II) Théorie de la normalisation : 1ère approche de conception 10
2.1 Définitions préliminaires 10
2.2 La 1ère forme normale : 1NF 12
2.3 La 2ème forme normale : 2NF 13
2.4 La 3ème forme normale : 3NF 14
2.5 La Boyce Codd Normal Form : BCNF 16
2.6 Conception descendante par normalisation 17
2.7 Test de validité de la décomposition 18
III) Conception ascendante par l’algorithme de synthèse 20
3.1 Définitions préliminaires 21
3.2 Algorithme de synthèse 22
Le langage algébrique 26
Les opérateurs ensemblistes 26
Union, intersection, différence 26
Produit cartésien 27
Les opérateurs relationnels 28
Opérateurs unaires de restriction 28
Opérateurs binaires 30
Le langage SQL 33
Introduction 33
LMD : Langage de Manipulation de Données 34
Structure d’un bloc de base 34
Expression des projections 34
Expression des sélections 35
Les calculs verticaux 37
Les calculs horizontaux 38
Expression des jointures sous forme prédicative 38
Expression des jointures sous forme imbriquée 40
Les opérateurs ensemblistes 42
Le partitionnement ou classification 45
Sélection dans les sous-tables 46
Tri des résultats 47
Recherche arborescente 47
Expression des divisions 50
comptage + partitionnement 52
double NOT EXISTS 54
algébrique 56
Les opérations de mise à jour 56
Modification de la valeur d’un attribut 56
Insertion de tuples 56
Suppression de tuples 57
Notion de transaction 57
SQL comme langage de définition de données 57
SQL comme langage de contrôle de données 60
Gestion des utilisateurs 60
Gestion des privilèges utilisateurs 61
Les index 63
Les vues 64
Introduction
Pour stocker le monde réel, il faut passer par plusieurs étapes, la première la conception, et ensuite la représentation.
But des bases de données :
Mettre en forme et structurer l'info du monde réel qu'on veut traiter pour la stocker et la manipuler facilement. Réalisation des opérations de création, destruction des structures déstockage, modification des données.
Le modèle relationnel a été créé par Codd.
Les données dans une BD vont être stockées de manière homogène, avec un format commun, si c'est le cas, on se trouve dans un SGBD. En plus du stockage, on va pouvoir manipuler les données. La frontière entre une bonne structure de fichier faite en C ou C++ est mince.
Le modèle relationnel
DéfinitionsLe modèle relationnel est basé sur la théorie des ensembles.
Une relation est un sous-ensemble du produit cartésien (de n domaines).
Un domaine est un ensemble de valeurs dotées d’une sémentique. (domaine ≠ type)
Ex :
D_nompilote = {“Dupond” , “Durand” , … }
D_nomavion = {“Airbus” , “Caravel” , … }
Vol ( D_numvol, D_nompilote, D_nomavion, D_ville, D_ville, D_heure, D_heure )
tuple de la relation vol : ( IT2400, Dupond, Boeing, Montpellier, Paris, 6h45, 8h05 )
Un attribut explicite le rôle joué par un domaine dans une relation.
On pourrait travailler sur l’ordre mais c’est peu explicite.
Ex :
Vol ( numvol, nompilote, nomavion, ville_dep, ville_arr, heure_dep, heure_arr )
Une relation peut être définie par :
Intension : attributs + domaine de définitionExtension : liste de ses instances ou tuples
Deux attributs définis sur le même domaine sont dits compatibles ou sémantiquement comparable.
Une clé primaire est un attribut ou un groupe d’attributs qui permet d’identifier de façon unique un tuple.
Il n’y a qu’une clé primaire par relation (elle est soulignée ).
Ex :
Vol ( numvol, nompilote, nomavion, ville_dep, ville_arr, heure_dep, heure_arr )
Un domaine primaire est un domaine sur lequel a été défini une clé primaire.
Ex :
D_numvol
Vol ( numvol, nompilote, nomavion, ville_dep, … )
D_numpil
Pilote ( numpilote, nompil, adresse, … )
D_numav
Avion ( numav, nomav, type_av, localisation, … )
Une clé étrangère est un attribut ou groupe d’attributs définis sur un domaine primaire et qui n’est pas clé primaire dans sa propre relation.
Ex :
Un schéma relationnel est un ensemble de relations sémantiquement liées via les domaines.
Le degré d’une relation correspond au nombre d’attributs de la relation.
La cardinalité d’une relation correspond au nombre de tuples qui la compose.
Une relation dynamique inclut une ou plusieurs clés étrangères.
Une relation statique n’inclut pas de clé étrangère.
Les contraintes d’intégrité sur les domaines :
Dynamiques : propres à l’application et non prise en compte par le modèle relationnel.
Statiques : liées au modèle relationnel.
Contrainte de domaine : toute valeur d’attribut doit appartenir à son domaine de définition.Contrainte de relation : toute valeur de clé primaire est unique.Contrainte de référence : toute valeur de clé étrangère existe comme clé primaire dans la relation référencée.
Conception d’un schéma relationnel
Introduction
La qualité d’un schéma relationnel va se mesurer lors des opérations de mise à jour (insertion, suppression, modification).
Ex :
Enseignant
Numprof
Nomprof
Catégorie
Salaire
1
Machin
professeur
2600
2
Dupond
professeur
2600
3
Martin
maître de conférence
1500
4
Durand
agrégé
2200
5
ChiChi
maître de conférence
1500
Hypothèse : la catégorie détermine le salaire.
Les anomalies de stockage :
Insertion : Ardourel est Ateroù est l’info le salaire d’un Ater est de …
Modification : augmentation du salaire des maîtres de conférence de 50%modifier tous les salaires des maîtres de conférence
Suppression : Durand est suppriméperte de l’info salaire d’un agrégé
L’objectif d’une démarche de conception est d’obtenir un schéma relationnel évitant les anomalies de stockage.
conserver la cohérence des données
II) Théorie de la normalisation : 1ère approche de conception
2.1 Définitions préliminaires
Dépendance fonctionnelle :
Soit R(U) une relation et U l’ensemble de ses attributs.
Soient X,Y c U , X et Y sont des attributs ou groupe d’attributs de U.
Il existe une dépendance fonctionnelle (DF) entre X et Y notée X Y ( X détermine Y ) ssi :
t1,t2 , deux tuples d’une instance de R si t1(X) = t2(X) alors t1(Y) = t2(Y)
Ex : la catégorie détermine le salaire
catégorie salaire
t1(X) = t2(X) = professeur t1(Y) = t2(Y) = 3200
Ex : R extension
A
B
C
a1
b1
c1
a1
b1
c2
a2
b2
c2
a3
b3
c2
a3
b4
c2
Quelles sont les DF existantes ?
AB non car a3b3 et a3b4
AC non car a1c1 et a1c2
BC non car b1c1 et b1c2
BA oui
CA ; A,BC ; A,CB ; CB non
B,CA oui
Dépendance fonctionnelle totale :
Soit R(U) une relation et U l’ensemble de ses attributs.
Soient X,Y c U , X et Y sont des attributs ou groupe d’attributs de U.
La DF XY est totale ssi X’ c X tel que X’Y
Ex :
A,BC n’est pas totale
Si la source de la DF est réduite à un attribut, la DF est totale.
Clé candidate :
Soit R(U) une relation et U l’ensemble de ses attributs.
Soient X,Y c U , X et Y sont des attributs ou groupe d’attributs de U.
X est une clé candidate dans R ssi
DF totale
X Y où Y = U-X
La clé primaire d’une relation est choisie parmi les clés candidates.
Si la DF n’est pas totale, X est seulement un identifiant.
Ex :
Abonné (n° sécu, n° abonné, nom, prénom, age)
Deux clés candidates : n° sécu et n° abonné
Peu importe laquelle on prend comme clé primaire.
n° abonné, nom est un identifiant.
2.2 La 1ère forme normale : 1NF
Une relation est en 1NF ssi tous ses attributs sont monovalués (atomiques).
Ex :
Personne
Num
Nom
Prénoms
1
Dupont
Jean, Paul
2
Durand
Marie, Chantal
3
Dujor
Eric, Bob, Patrick
n’est pas en 1NF car prénoms non atomique.
Comment normaliser en 1NF : 2 solutions
a créer autant d’attributs que de valeurs maximum possibles pour l’attribut multivalué.
normalisation par stockage horizontal
toutes les informations sont dans la table
pb : stockage des valeurs nulles et nombre de valeurs limité.
ex : Num, Nom, Prénom1, Prénom2, Prénom3
b créer une nouvelle relation contenant comme clé primaire, la clé primaire de la relation d’origine et un attribut correspondant à une valeur de l’attribut multivalué.
Création d’autant de tuples que de valeurs possibles.
normalisation par stockage vertical
nombre de valeurs possibles illimité
pas de valeur nulle
pb : nécessité de relier les tables pour avoir l’information.
ex : 3A Goscinny 3A Uderzo 4A Hergé
Tintin code 4A Astérix code 3A
Choix entre a et b :
a le nombre de valeur possible est connu et la majorité des instances possèdent des valeurs pour ces attributs.
b le nombre de valeur varie selon les instances.
relier les tables entre elles (jointure) ne pose pas de problème.
2.3 La 2ème forme normale : 2NF
Une relation est en 2NF ssi :
Elle est en 1NFIl n’existe pas de DF dont la source est une partie de la clé primaire
Ex : viole la 2NF
R ( A, B, C, D )
Emprunt ( n° abonné, n° livre, nom, date_emprunt )
n° abonné nom viole la 2NF
Comment normaliser en 2NF :
Isoler dans une nouvelle relation la DF gênante et conserver seulement la source de la DF dans la relation d’origine.
Ex :
R ( A, B, C, D ) R1 ( A, B, C )
R2 ( B, D )
Toute relation en 1NF avec une clé mono-attribut est en 2NF.
Le problème de la 2NF ne se pose que si la clé est multi-attributs.
2.4 La 3ème forme normale : 3NF
Une relation est en 3NF ssi :
Elle est en 2NFIl n’existe pas de DF transitive entre attributs non clés
Ex : viole la 3NF
R ( A, B, C )
Toute DF XY est telle que :
X est un identifiant
ou
Y est uniquement composé d’attributs clés
Ex :
Enseignant ( num, nom, catégorie, salaire ) non en 3NF
Ex :
R ( A, B, C, D ) 2NF oui
3NF non
Ex :
R ( A, B, C, D ) 3NF oui
Ce n’est pas par ce que la relation est en 3NF qu’il n’y a pas d’anomalie de stockage.
Comment normaliser en 3NF :
Isoler dans une nouvelle relation la DF responsable, dont la clé primaire est source de la DF.
Supprimer la cible de la DF dans la relation d’origine.
Ex :
R1 ( A, B, C )
R11 ( A, B)
R12 ( B, C )
Ex :
Enseignant ( num, nom, catégorie )
Salaire ( catégorie, salaire )
2.5 La Boyce Codd Normal Form : BCNF
Toute source de DF est un identifiant.
Ex :
R ( A, B, C, D ) 3NF oui
BCNF non
Comment normaliser en BCNF :
Il faut se poser la question de la nouvelle clé primaire de la relation d’origine.
Ex :
R1 ( A, C, D )
R2 ( D, B )
Théorème de décomposition de Casey-Delobel :
Soit R(U) une relation et X,Y,Z une partition de U telle que XY
R = R1 jointure R2
avec R1 = projection ( R / X,Y )
et R2 = projection ( R / X,Z )
2.6 Conception descendante par normalisation
A partir d’un schéma relationnel ou d’une unique relation :
1) identifier l’ensemble des DF
2) décomposer chaque relation si nécessaire selon Casey-Delobel jusqu’à la 3NF
Ex :
S = { R1, R2, R3 }
Ensembles des DF = { f1, f2,f3 }
Ex :
R1 ( A, B, C, D, E )
Décomposition selon f1 :
R11 ( A, B, D, E )
R12 ( B, C )
Décomposition selon f2 :
R11 ( A, B, D, E )
R12 ( E, C )
Les décompositions sont sans perte de données mais avec perte de dépendances.
2.7 Test de validité de la décomposition
R en entrée + un ensemble de DF
R1, R2, R3 décomposition de la relation.
La décomposition est-elle sans perte ?
Trois étapes :
Soit R(A1, A2, …, An) une relation à n attributs décomposée en m relations R1, R2, …, Rm
créer un tableau dont les lignes sont les relations R1, R2, …, Rm et les colonnes sont les n attributs de la relation initiale.à l’intersection d’une ligne i et d’une colonne j on inscrit αj si Aj є Ri
A1
A2
…
Aj
…
An
R1
α1
αj
R2
αj
αn
…
Rm
Unification :Le tableau est considéré comme une extension de la relation Ri.
On examine sur cette extension les différentes DF.
Si une DF n’est pas vérifiée, on vérifie les valeurs de la cible de la façon suivante : si l’une des valeurs de la colonne est αi , on inscrit dans la colonne concernée αi , sinon rien.
Validation :Si il existe au moins une ligne d’α la décomposition est sans perte de données sinon elle est avec perte de données.
Ex :
Rnotes ( n°etudiant, nom, codeUV, noteTest, noteCC )
Décomposition en R1( n°etudiant, nom )
R2( n°etudiant, codeUV, noteTest, noteCC )
1
2
3
4
5
n°etudiant
nom
codeUV
noteTest
noteCC
R1
α1
α2
R2
α1
α2
α3
α4
α5
Il y a une ligne d’α : la décomposition est sans perte de données.
Décomposition en R1( n°etudiant, nom, noteTest )
R2( n°etudiant, codeUV, noteCC )
1
2
3
4
5
n°etudiant
nom
codeUV
noteTest
noteCC
R1
α1
α2
α4
R2
α1
α3
α5
Il n’y a pas de ligne d’α : la décomposition est avec perte de données.
III) Conception ascendante par l’algorithme de synthèse
3.1 Définitions préliminaires
Attribut étranger :
Soit F un ensemble de DF
Soit f є F la DF A,B,C,D,…Y
A est un attribut étranger dans f si on peut engendrer B,C,D,…Y à partir de F et des propriétés P1, P3, P4 :
P1 : réflexivité : si Y c X alors XY
P3 : transitivité : si XY et YZ alors XZ
P4 : pseudo transitivité : si XY et Y,ZW alors X,ZW
Ex : f1 f2
F = { A B ; A,B C }
B est un attribut étranger dans f2 car avec P4 on a :
A B et A,B C alors A,A C donc A C
La DF A C est généré à partir de F et de P4 .
Dépendance fonctionnelle redondante :
Soit F un ensemble de DF et f є F une DF
f est dite redondante dans F si elle peut être générée à partir de F-{f} et des propriétés P1, P2, P3, P4 .
P2 : augmentation : si XY et W c Z alors X,ZW,Y
Ex : f1 f2 f3
F = { A B ; B C ; A C}
f3 (AC) est redondante dans R car elle peut être obtenue à partir de f1, f2 et P3.
Couverture minimale :
Soit F un ensemble de DF, M F est dit couverture minimale de F ssi :
Si XY є M alors card(Y) = 1Les cibles de DF sont mono-attributs.
Si XY є M alors M-{XY} n’est pas équivalent à MIl n’existe pas de DF redondante.
Si XY et Z c X alors M-{XY} + {ZY} n’est pas équivalent à MIl n’y a pas d’attributs étranger dans les DF de M : les DF sont totales.
3.2 Algorithme de synthèse
Point de départ : un ensemble d’attributs + un ensemble de DF
étape 1 : recherche d’une couverture minimale M
Eclatement des DF selon leur cibleP6 : XY,Z alors XY et XZ
Suppression des attributs étrangers des DFP1 (réflexivité), P3 (transitivité), P4 (pseudo transitivité)
Suppression des DF redondantesP1 (réflexivité), P2 (augmentation)
étape 2 : regroupement des DF
On regroupe toutes les DF ayant la même source dans un ensemble Ei.
(autant d’ensemble Ei que de source de DF différentes)
étape 3 : regroupement des Ei
On regroupe les ensembles Ei, Ej pour lesquels il existe une DF réciproque :
Ei = { XY ; … }
Ej = { YX ; … }
étape 4 : création des relations
Pour chaque ensemble obtenu, on crée une relation dont la clé primaire est constituée de la source des DF et dont les autres attributs sont les cibles des DF.
Si il existe une DF réciproque, il y a au moins 2 clés candidates : il faut en choisir une comme clé primaire .
les relations obtenues sont en 3NF
Cet algorithme permet d’obtenir un schéma relationnel correct.
Ex :
Point de départ : { A,B,C,D,E,F }
F = { f1 : A,BC,D
f2 : CD f5 : BA
f3 : ED f6 : E,FF
f4 : FE,D f7 : DE }
1ère étape : recherche d’une couverture minimale
Eclatement des DF selon la ciblef1 : A,BC,D f11 : A,BC
f12 : A,BD
f4 : FE,D f41 : FE
f42 : FD
Suppression des attributs étrangers
On examine toutes les DF dont la source est multi-attributs f6, f11, f12
f6 : E,FFXY et Y,ZW alors X,ZW
f41 : FE et f6 : E,FF alors FF
E est étranger dans f6
f11 : A,BCf5 : BA et f11 : A,BC alors BC
A est étranger dans f11
f12 : A,BDf5 : BA et f12 : A,BD alors BD
A est étranger dans f12
suppression des DF redondantes
f12 : BDP3 : f11 : BC et f2 : CD alors BD
f12 est redondante
f42 : FDP3 : f41 : FE et f3 : ED alors FD
f42 est redondante
f6 : FFd’après la propriété P1, f6 est redondante
M = { f11 : BC ; f5 : BA ; f7 : DE ; f2 : CD ; f3 : ED ; f41 : FE }
2ème étape : regroupement des DF
E1 = { BC ; BA }
E2 = { DE }
E3 = { CD }
E4 = { ED }
E5 = { FE }
3ème étape : regroupement des Ei
E1 = { BC ; BA }
E24 = { DE ; ED }
E3 = { CD }
E5 = { FE }
4ème étape : création des relations
R1 ( A , B , C )
R2 ( E , D )
R3 ( C , D )
R4 ( F , E )
Dans R2, E et D sont
Le langage algébrique
Les opérateurs :
ensemblistes : union, intersection, différence, produit cartésien.relationnels : projection, sélection, jointure, division (ces deux dernier ne sont pas de base : ils peuvent être exprimés en fonction des autres opérateurs).
Les opérateurs ensemblistes
Union, intersection, différence
Ce sont des opérateurs binaires.
Les deux relations doivent être unicompatibles :
même degré (même nombre d’attributs).attributs définis 2 à 2 sur le même domaine.
R1 ( A , B ) R2 ( C , D )
Soient R et S, deux relations unicompatibles :
R U S = { tuple t / t є R ou t є S }exprime le OU logique
R ∩ S = { tuple t / t є R et t є S }exprime le ET logique
R - S = { tuple t / t є R et t є S }exprime la négation
P1
Dupont
Toulouse
P2
Durand
Marseille
P3
Dujardin
Toulon
P3
Dujardin
Toulon
P4
Dupond
Paris
av1
B707
350
Nice
av2
Caravel
100
Paris
Produit cartésien
R × S = { ( t , s ) / t є R et s є S }
Le produit cartésien est la combinaison de tous les tuples de R avec tous les tuples de S.
Pilote 1 × Avion 1
Numpil
Nompil
Adrpil
Avpil
Avnom
Capacité
localisation
P1
Dupont
Toulouse
av1
B707
350
Nice
P1
Dupont
Toulouse
av2
Caravel
100
Paris
P2
Durand
Marseille
av1
B707
350
Nice
P2
Durand
Marseille
av2
Caravel
100
Paris
P3
Dujardin
Toulon
av1
B707
350
Nice
P3
Dujardin
Toulon
av2
Caravel
100
Paris
les doublons sont éliminés dans les relations obtenues.
Les opérateurs relationnels
Opérateurs unaires de restriction
la sélection
Soit Ө, un opérateur de comparaison :
Ө є { = , ≠ , < , > , ≤ , ≥ }
σ ( R , A Ө C ) = { t є R / t(A) Ө C }
la valeur de l’attribut A pour le tuple t vérifie la condition Ө C
La sélection sur R avec la condition A Ө C où A est un attribut ou un groupe d’attributs de R et C est un tuple de structure identique à R ou une structure identique à A.
Numpil
Nompil
Adrpil
P1
Dupond
Paris
P2
Durand
Paris
P3
Dujardin
Toulon
P4
Martin
Toulouse
P5
Leclerc
Toulouse
Si C = ( P2 , Durand , Paris )
Numpil
Nompil
Adrpil
P1
Dupond
Paris
P2
Durand
Paris
Numpil
Nompil
Adrpil
P3
Dujardin
Toulon
la projection
Soit R, une relation composée de A1, A2, …, An, An+1, …, Am attributs.
π A1, …, Am (R) = { t(A1, …, Am) , t є R }
les tuples de R réduits aux attributs A1, …, Am
Adrpil
Paris
Toulon
Toulouse
Les doublons sont éliminés.
Opérateurs binairesla jointure
Soit R( A , B1 ) et S( B2 , C )
où B1 et B2 sont définis sur le même domaine.
R( B1 Ө B2 )S = { (t,s) / t tuple de R, s tuple de S et t(B1)Өs(B2) est vérifiée }
= { t’ / t’є R×S et t’(B1)Өt’(B2) est vérifiée }
jointure entre R et S sur les attributs B1 et B2
si B1 = B1 jointure naturelle
si Ө = ‘=’ equijointure
sinon tetatjointure
Numpil
Nompil
Adrpil
100
Jean
Paris
101
Pierre
Paris
120
Paul
Paris
Numvol
Numpil
numav
IT500
100
110
IT501
100
130
IT503
120
140
IT504
100
110
IT505
120
110
IT506
120
120
IT507
110
130
Numpil
Nompil
Adrpil
Numvol
Numpil
numav
100
Jean
Paris
IT500
100
110
100
Jean
Paris
IT501
100
130
100
Jean
Paris
IT504
100
110
101
Pierre
Paris
Pas de correspondance pour 101
120
Paul
Paris
IT503
120
140
120
Paul
Paris
IT505
120
110
120
Paul
Paris
IT506
120
120
Numpil
Nompil
Adrpil
Numvol
Numpil
numav
100
Jean
Paris
Pas de correspondance pour 100
101
Pierre
Paris
IT500
100
110
101
Pierre
Paris
IT501
100
130
101
Pierre
Paris
IT504
100
110
120
Paul
Paris
IT500
100
110
120
Paul
Paris
IT501
100
130
120
Paul
Paris
IT504
100
110
120
Paul
Paris
IT507
110
130
Cf : théorème de décomposition
R = R1 ( A = A ) R2
La jointure permet de relier les relations entre elles.
la division
Soient R( A , B1 ) et S( B2 ) avec B1 et B2 définis sur le même domaine.
R( B1 ÷ B2 )S = { t(A) / t tuple de R tel que s, tuple de S, (t,s) є R }
les tuples de R réduits à l’attribut A tels qu’il soit associé à toutes les valeurs de B2 existant dans S, dans R.
a1
b1
a1
b2
a2
b1
b1
b2
Il n’y pas le couple a2 b2 sans R.
A
a1
Le langage SQL
Introduction
Le langage SQL à été développé par Codd chez IBM.
Norme SQL en 1981 : ANSI
Tous les SGBD proposent une interface SQL
les différences entre les interfaces sont minimes.
SQL présente 3 facettes :
LMD : Langage de Manipulation de Donnéesaccès aux tuples de la base (consultation, mise à jour), extension des relations
LDD : Langage de Définition de DonnéesDéfinitions des relations et des contraintes associées, intension des relations
LCD : Langage de Contrôle des DonnéesGère les mécanismes d’autorisation
Objectif de SQL :
base le langage sur le langage naturel (des mots clés en anglais) simplicité des requêtes
LMD : Langage de Manipulation de Données
SQL langage d’interrogation et de manipulation
Structure d’un bloc de base
Mots clés SQL :
SELECT liste des attributs résultats
FROM liste des relations
[ WHERE liste des conditions ] ;
Expression des projections
Les projections sont exprimées dans la clause SELECT (les attributs sont séparés par des ,).
Ex :
Vol ( numvol, numav, numpil, ville_dep, ville_arr, h_dep, h_arr )
Pilote ( numpil, nompil, adresse, salaire )
Avion ( numav, nomav, capacité, localisation )
Villes d’arrivée ?
SELECT ville_arr
FROM Vol ;
Noms et adresses des pilotes ?
algébrique : πnompil,adresse (Pilote)
SQL :
SELECT nompil, adresse
FROM Pilote ;
!! les doublons ne sont pas automatiquement supprimés.
il faut utiliser le mot clé DISTINCT juste après le mot clé SELECT.
Pour obtenir tous les attributs, on peut utiliser la clause *.
Ex :
Toutes les informations de toutes les tables ?
SELECT *
FROM * ;
Expression des sélections
Les sélections sont exprimées dans la clause WHERE sous forme de condition.
attribut Ө valeur
où Ө est un opérateur є { = , ≠ , < , > , ≤ , ≥ }
connecteurs logiques : AND, OR, NOT
Ex : pilotes Niçois ?
SELECT *
FROM Pilote
WHERE adresse = ‘Nice’ ;
notion d’intervalle
exprimée sous la forme suivante :
attribut BETWEEN borne_inférieure AND borne_supérieure
!! les bornes inférieure et supérieure sont incluses.
appartenance à une liste
exprimée sous la forme suivante :
attribut IN ( val1, val2,…, valn )
recherche de sous-chaînes
exprimée sous la forme suivante :
attribut LIKE ‘chaîne’
- : un caractère quelconque
% : un nombre quelconque de caractères
Ex : les informations sur les airbus dont le numéro est compris entre 100 et 150 et qui sont localisés à Nice, Toulouse, Marseille et Bordeaux.
SELECT *
FROM Avion
WHERE nomav LIKE ‘airbus%’
AND numav BETWEEN 100 AND 150
AND localisation IN (Nice, Toulouse, Marseille, Bordeaux) ;
SQL propose une optimisation des requêtes donc l’expression des conditions n’est pas ordonnée.
Les calculs verticaux
Il est possible d’exprimer des calculs verticaux qui s’appliquent sur une colonne (attribut ou groupe d’attributs) par les fonctions suivantes : SUM, AVG, MIN, MAX, COUNT.
!! ces fonctions ne retournent qu’une seule valeur.
Ex : la moyenne des salaires des pilotes.
SELECT AVG ( salaire )
FROM Pilote ;
On peut mixer ces fonctions avec la clause DISTINCT comme il suit :
SELECT COUNT ( DISTINCT attribut )
Ex : quel est le nombre de noms de pilote ?
SELECT COUNT ( DISTINCT nompil )
FROM Pilote ;
Ex : quel est le nombre de vols à destination de Nice ?
SELECT COUNT ( numvol )
FROM Vol
WHERE ville_arr = ‘Nice’ ;
Les calculs horizontaux
Il s’agit d’effectuer des opérations au niveau des attributs d’un même tuple.
Les opérateurs : +, -, *, /, ||Les fonctions : ABS, …
Ex : noms des pilotes qui gagneraient plus de 5000 euros avec une augmentation de 10%.
Expression des jointures sous forme prédicative
La jointure peut être exprimée dans la clause WHERE.
exprimée sous la forme suivante :
attribut1 Ө attribut2
où Ө est un opérateur de comparaison et attribut1 et attribut2 sont deux attributs compatibles (définis sur le même domaine : de même type syntaxique).
Si les deux attributs ont le même nom, il faut les préfixer par le nom de la relation.
Ex : numéro et horaire des vols au départ de Paris, assurés par un Airbus.
SELECT numvol, ville_arr, h_dep
FROM Avion, Vol
WHERE ville_dep = ‘Paris’
AND nomav LIKE ‘Airbus%’
AND Vol.numav = Avion.numav ;
Ex : quels sont les vols assurés par le pilote Jean Dupont ?
SELECT V.numvol
FROM Vol V, Pilote P
WHERE P.nompil = ‘Jean Dupont’
AND V.numpil = P.numpil ;
Ex : quels sont les noms des avions localisés dans la ville de départ d’un vol à destination de Toulouse ?
SELECT A.nomav
FROM Avion A, Vol V
WHERE V.ville_arr = ‘Toulouse’
AND V.ville_dep = A.localisation ;
Dans le cas de l’auto-jointure, il est nécessaire d’utiliser un alias pour différencier les deux relations.
Ex : numéro des pilotes gagnant plus que le pilote n° P3.
SELECT P2.numpil
FROM Pilote P1, Pilote P2
WHERE P1.numpil = ‘P3’
AND P1.salaire < P2.salaire ;
Expression des jointures sous forme imbriquée
Il est possible d’imbriquer des blocs de base pour exprimer la jointure.
Ex : nom des pilotes gagnant plus que la moyenne des pilotes.
SELECT P.nompil
FROM Pilote P
WHERE P.salaire > ( SELECT AVG(P2.salaire)
FROM Pilote P2 ) ;
la condition doit être vérifiée pour au moins une des valeurs retournées.
Le connecteur sera IN ou ӨANY.
Ex : nom des pilotes assurant un vol au départ de Nice.
SELECT P.nompil
FROM Pilote P
WHERE P.nompil IN ( SELECT V.numpil
FROM Vol V
WHERE V.ville_dep = ‘Nice’ ) ;
OU
SELECT P.nompil
FROM Pilote P
WHERE P.nompil = ANY ( SELECT V.numpil
FROM Vol V
WHERE V.ville_dep = ‘Nice’ ) ;
la condition doit être vérifiée pour toutes les valeurs retournées.
Le connecteur sera ӨALL.
Ex : nom des pilotes niçois gagnant plus que tous les pilotes parisiens.
SELECT nompil
FROM Pilote
WHERE adresse = ‘Nice’
AND salaire > ALL ( SELECT salaire
FROM Pilote
WHERE adresse = ‘Paris’ ) ;
OU
SELECT nompil
FROM Pilote
WHERE adresse = ‘Nice’
AND salaire > ( SELECT MAX ( salaire )
FROM Pilote
WHERE adresse = ‘Paris’ ) ;
Dans les sous-requêtes, il est possible de comparer un ensemble d’attributs.
Ex : les avions de même nom et localisés au même endroit que l’avion de numéro 505.
SELECT *
FROM Avion
WHERE ( nomav, localisation ) = ( SELECT nomav, localisation
FROM Avion
WHERE numav = 505 ) ;
Version prédicative :
SELECT A1.numav
FROM Avion A1, Avion A2
WHERE A1.nomav = A2.nomav
AND A1.localisation = A2.localisation
AND A2.numav = 505 ;
Les opérateurs ensemblistes
SELECT liste des attributs résultats
FROM liste des relations
[ WHERE liste des conditions ] ;
UNION / INTERSECTION / MINUS
SELECT liste des attributs résultats
FROM liste des relations
[ WHERE liste des conditions ] ;
!!! les deux blocs doivent être unicompatibles (l’ordre des attributs est important).
Ex : liste des pilotes habitant soit à Paris soit à Nice.
SELECT numpil
FROM Pilote
WHERE adresse = ‘Nice’
UNION
SELECT numpil
FROM Pilote
WHERE adresse = ‘Paris’ ;
Equivalant à :
SELECT numpil
FROM Pilote
WHERE adresse IN (‘Nice’, ‘Paris’) ;
Equivalant à :
SELECT numpil
FROM Pilote
WHERE adresse = ‘Nice’
OR adresse = ‘Paris’ ;
INTERSECTION AND
MINUS NOT
Ex : pilote niçois n’assurant aucun vol au départ de Nice.
SELECT numpil
FROM Pilote
WHERE adresse = ‘Nice’
MINUS
SELECT numpil
FROM Vol
WHERE ville_dep = ‘Nice’ ;
Equivalant à :
SELECT P1.nompil
FROM Pilote P1
WHERE P1.adresse = ‘Nice’
AND P1.numpil NOT IN ( SELECT V.numpil
FROM Vol V
WHERE V.ville_dep = ‘Nice’ ) ;
Ex : ensemble des pilotes niçois assurant au moins un vol dont le départ n’était pas à Nice.
SELECT P.numpil
FROM Pilote P, Vol V
WHERE P.numpil = V.numpil
AND V.ville_dep ≠ ‘Nice’
AND P.adresse = ‘Nice’ ;
Le partitionnement ou classification
Ceci permet de regrouper les tuples en sous-relations en fonction des valeurs des attributs de partition.
Le partitionnement est exprimé dans la clause GROUP BY.
Ex :
SELECT *
FROM Vol
GROUP BY numpil ;
Les sous-tables sont composées des autres attributs (sans l’attribut de partition). Il n’est possible d’accéder aux sous-tables que via des fonctions agregatives (verticales).
!!! seuls les attributs de partitionnement (exprimés dans le GROUP BY) peuvent être exprimés dans le SELECT.
Ex : nombre de vol de chacun des pilotes.
SELECT numpil, COUNT(*)
FROM Vol
GROUP BY numpil ;
Ex : nombre de vol par pilote et par avion.
SELECT numpil, numav, COUNT(*)
FROM Vol
GROUP BY numpil, numav ;
Il y a autant de sous-tables par pilote qu’il conduit d’avions différents.
Sélection dans les sous-tables
De la même façon qu’il est possible d’élimer des tuples dans la relation, il est possible d’éliminer des sous-tables dans la partition.
La sélection est exprimée dans la clause HAVING condition.
(la condition porte sur les sous-tables)
Ex : quels sont les pilotes assurant plus de 5 vols ?
SELECT numpil, nompil
FROM Vol V, Pilote P
WHERE V.numpil = P.numpil
GROUP BY P.numpil, P.nompil
HAVING COUNT(*) > 5 ;
Tri des résultats
Il est possible de trier les attributs par ordre croissant ou décroissant grâce à la clause suivante :
ORDER BY liste des attributs [ ASC / DES ]
!!! c’est toujours la dernière clause de la requête.
Si il y a plusieurs attributs, le tri est réalisé selon l’ordre des attributs.
Ex : liste des pilotes par ordre décroissant de salaire puis par ordre alphabétique.
SELECT *
FROM Pilote
ORDER BY salaire DES, nompil ASC ;
Recherche arborescente
La recherche arborescente est une spécificité de SQL + d’Oracle.
La structure arborescente permet de décrire "est responsable de".
Employé ( num_emp, nom_emp, responsable )
association réflexive
Ex : les employés sous la direction de l’employé 707.
SELECT *
FROM Employé E1, Employé E2
WHERE E1.responsable = 707
AND E1.num_emp = E2.responsable ;
Ex : les employés sous la direction de l’employé 909.
impossible de parcourir la structure sans connaître le niveau de profondeur.
Objectif : parcourir une structure hiérarchique quelle que soit la profondeur.
SELECT …
FROM …
WHERE …
CONNECT BY [PRIOR] colonne1 = [PRIOR] colonne2
[START WITH condition de départ]
[ORDER BY LEVEL] ;
Le CONNECT BY s’organise comme il suit :
père = PRIOR fils ou PRIOR fils = père
PRIOR père = filsou fils = PRIOR père
Hiérarchie dans le modèle relationnel :
père : clé étrangère dans la relation.fils : clé primaire dans la relation.
Ex : les employés sous la direction de l’employé 909.
SELECT num_emp
FROM Employé
CONNECT BY responsable = PRIOR num_emp
START WITH responsable = 909
ORDER BY LEVEL ;
Expression des divisions
Ex : quels sont les pilotes qui conduisent tous les airbus ?
R ÷ S
avec :
R couple pilote et avion conduit
S ensemble des airbus
En algébrique :
R1 = π numpil, numav (Vol)
S1 = σ (Avion, numav = ‘Airbus%’)
S2 = π numav
Résultat = R1 ÷ R2
π numpil (R) × S division idéale
π numpil (R) × S – R tous ceux qui ne sont pas associés dans R à tous les éléments de
S ( = tous les mauvais )
π numpil (R) – π numpil ( π numpil (R) × S – R )
Tous – Les mauvais = Les bons
Il n’existe pas en SQL de clause spécifique : il faut simuler la division.
Il y a trois façons de la simuler :
comptage + partitionnementdouble NOT EXISTSalgébrique : π (R) – π ( π (R) × S – R )
comptage + partitionnement
Ex : quels sont les pilotes dont le nombre d’avions qu’ils conduisent est égal au nombre d’avions ?
SELECT numpil
FROM Vol
GROUP BY numpil
HAVING COUNT (DINSTINCT numav) = ( SELECT COUNT (DINSTINCT numav)
FROM Vol ) ;
Ex : quels sont les pilotes qui conduisent tous les Airbus ?
SELECT numpil
FROM Vol V, Avion A
WHERE V.numav = A.numav
AND A.nomav LIKE ‘Airbus%’
GROUP BY numpil
HAVING COUNT (DINSTINCT V.numav)
= ( SELECT COUNT (DINSTINCT V1.numav)
FROM Vol V1, Avion A1
WHERE V1.numav = A1.numav
AND A1.nomav LIKE ‘Airbus%’ ) ;
Ex : quels sont les pilotes conduisant tous les avions du pilote Dupont ?
SELECT numpil
FROM Vol
WHERE numav IN ( SELECT V.numav
FROM Vol V, Pilote P
WHERE P.nompil = ‘Dupond’
AND V.numpil = P.numpil )
GROUP BY numpil
HAVING COUNT (DINSTINCT numav) = ( SELECT COUNT (DINSTINCT numav)
FROM Vol V1, PiloteP1
WHERE P1.nompil = ‘Dupont’
AND P1.numpil = V1.numpil ) ;
OU
SELECT numpil
FROM Vol V1, Vol V2, Pilote P
WHERE V1.numav = V2.numav
AND V2.numpil = P.numpil
AND P.nompil = ‘Dupont’
GROUP BY numpil
HAVING COUNT (DINSTINCT numav)
= ( SELECT COUNT (DINSTINCT numav)
FROM Vol V3, Pilote P1
WHERE P1.nompil = ‘Dupont’
AND P1.numpil = V3.numpil ) ;
double NOT EXISTS
EXISTS en SQL est un prédicat qui vaut vrai si la requête associée retourne au moins un tuple.
Ex : les pilotes assurant au moins un vol.
SELECT *
FROM Pilote P
WHERE EXISTS ( SELECT *
- Les bases de données relationnelles
- Récupérer données json (javascript) - Conseils pratiques -Javascript
- Base de données javascript - Conseils pratiques -Javascript
- Conserver bases de données online ✓ - Forum SQL
- Restauration base de données sql server ✓ - Forum SQL
- Base de données e-commerce - Forum PHP