CodeS-SourceS
Rechercher un code, un tuto, une réponse

Les bases de données relationnelles

Août 2017

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     *
  

A voir également

Publié par thebroyeur.
Ce document intitulé «  Les bases de données relationnelles  » issu de CodeS-SourceS (codes-sources.commentcamarche.net) est mis à disposition sous les termes de la licence Creative Commons. Vous pouvez copier, modifier des copies de cette page, dans les conditions fixées par la licence, tant que cette note apparaît clairement.
Ajouter un commentaire

Commentaires

Donnez votre avis
Superviser SQL Server Analysis Services
Utilisation d'un curseur en sql