Base de données relationnelles

Conception d’un schéma relationnel    8

III) Conception ascendante par l’algorithme de synthèse    20

Le langage algébrique    26

Les opérateurs ensemblistes    26

Les opérateurs relationnels    28

Le langage SQL    33

Les opérations de mise à jour    56

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éfinitions

Le 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éfinition
• Extension : 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 :

1. Dynamiques : propres à l’application et non prise en compte par le modèle relationnel.

1. Statiques : liées au modèle relationnel.

1. Contrainte de domaine : toute valeur d’attribut doit appartenir à son domaine de définition.
2. Contrainte de relation : toute valeur de clé primaire est unique.
3. 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 Ater

où 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 :

    t₁,t₂ , deux tuples d’une instance de R si     t₁(X) = t₂(X) alors t₁(Y) = t₂(Y)

Ex : la catégorie détermine le salaire

catégorie salaire

t₁(X) = t₂(X) = professeur t₁(Y) = t₂(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 a₃b₃ et a₃b₄

    AC        non car a₁c₁ et a₁c₂

    BC        non car b₁c₁ et b₁c₂

    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 1NF
• Il 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 )        R₁ ( A, B, C )

                    R₂ ( 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 2NF
• Il 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 :

    R₁ ( A, B, C )        

        

R₁₁ ( A, B)    

R₁₂ ( 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 :

R₁ ( A, C, D )    

R₂ ( 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 = R₁     jointure R₂

    avec R₁ = projection ( R / X,Y )

    et R₂ = 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 = { R₁, R₂, R₃ }

    Ensembles des DF = { f₁, f₂,f₃ }

Ex :

    R₁ ( A, B, C, D, E )

    Décomposition selon f₁ :

        R₁₁ ( A, B, D, E )

        R₁₂ ( B, C )

Décomposition selon f₂ :

        R₁₁ ( A, B, D, E )

        R₁₂ ( 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

R₁, R₂, R₃ décomposition de la relation.

La décomposition est-elle sans perte ?

Trois étapes :

Soit R(A₁, A₂, …, A_n) une relation à n attributs décomposée en m relations R₁, R₂, …, R_m

    

• 
  
• créer un tableau dont les lignes sont les relations R₁, R₂, …, R_m 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 A_j є R_i

	A1	A2	…	Aj	…	An
R1	α1			αj
R2				αj		αn
…
Rm

• Unification :

Le tableau est considéré comme une extension de la relation R_i.

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     R₁( n°etudiant, nom )

                R₂( 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     R₁( n°etudiant, nom, noteTest )

            R₂( 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 P₁, P₃, P₄ :

    

    P₁ : réflexivité : si Y c X alors XY

    P₃ : transitivité : si XY et YZ alors XZ

    P₄ : 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 f₂ car avec P₄ 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 P₄ .

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 P₁, P₂, P₃, P₄ .

P₂ : augmentation : si XY et W c Z alors X,ZW,Y

Ex :           _{f1     f2     f3}

    F = { A B ; B C ; A C}

    f₃ (AC) est redondante dans R car elle peut être obtenue à partir de f₁, f₂ et P₃.

Couverture minimale :

Soit F un ensemble de DF, M F est dit couverture minimale de F ssi :

• Si XY є M alors card(Y) = 1

Les cibles de DF sont mono-attributs.

• Si XY є M alors M-{XY} n’est pas équivalent à M

Il n’existe pas de DF redondante.

• Si XY et Z c X alors M-{XY} + {ZY} n’est pas équivalent à M

Il 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 cible

P6 : XY,Z alors XY et XZ

• Suppression des attributs étrangers des DF

P₁ (réflexivité), P₃ (transitivité), P₄ (pseudo transitivité)

• Suppression des DF redondantes

P₁ (réflexivité), P₂ (augmentation)

étape 2 : regroupement des DF

        On regroupe toutes les DF ayant la même source dans un ensemble E_i.

(autant d’ensemble E_i que de source de DF différentes)

étape 3 : regroupement des E_i

        On regroupe les ensembles E_i, E_j pour lesquels il existe une DF réciproque :

        E_i = { XY ; … }

        E_j = { 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 = { f₁ : A,BC,D

f₂ : CD        f₅ : BA

f₃ : ED        f₆ : E,FF

f₄ : FE,D        f₇ : DE    }

    1^ère étape : recherche d’une couverture minimale

• Eclatement des DF selon la cible

f₁ : A,BC,D            f₁₁ : A,BC

                f₁₂ : A,BD

f₄ : FE,D            f₄₁ : FE

                f₄₂ : FD

• Suppression des attributs étrangers

On examine toutes les DF dont la source est multi-attributs f₆, f₁₁, f₁₂

• f₆ : E,FF

XY et Y,ZW alors X,ZW

f₄₁ : FE et f₆ : E,FF alors FF

E est étranger dans f₆

• f₁₁ : A,BC

f₅ : BA et f₁₁ : A,BC alors BC

A est étranger dans f₁₁

• f₁₂ : A,BD

f₅ : BA et f₁₂ : A,BD alors BD

A est étranger dans f₁₂

• suppression des DF redondantes

• f₁₂ : BD

P₃ : f₁₁ : BC et f₂ : CD alors BD

f₁₂ est redondante

• f₄₂ : FD

P₃ : f₄₁ : FE et f₃ : ED alors FD

f₄₂ est redondante

• f₆ : FF

d’après la propriété P₁, f₆ est redondante

    M = { f₁₁ : BC ; f₅ : BA ; f₇ : DE ; f₂ : CD ; f₃ : ED ; f₄₁ : FE }

    2^ème étape : regroupement des DF

        E₁ = { BC ; BA }

        E₂ = { DE }

        E₃ = { CD }

        E₄ = { ED }

        E₅ = { FE }

    3^ème étape : regroupement des E_i

        E₁ = { BC ; BA }

        E₂₄ = { DE ; ED }

        E₃ = { CD }

        E₅ = { FE }

    4^ème étape : création des relations

        R₁ ( A , B , C )

        R₂ ( E , D )

        R₃ ( C , D )

        R₄ ( F , E )

    

    Dans R₂, 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.

    

    R₁ ( A , B )                R₂ ( 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 A₁, A₂, …, A_n, A_n+1, …, A_m attributs.

π _{A1, …, Am} (R) = { t(A₁, …, A_m) , t є R }

les tuples de R réduits aux attributs A₁, …, A_m

Adrpil

Paris

Toulon

Toulouse

Les doublons sont éliminés.

Opérateurs binaires

• la jointure

Soit R( A , B₁ ) et S( B₂ , C )

où B₁ et B₂ sont définis sur le même domaine.

R( B₁ Ө B₂ )S = { (t,s) / t tuple de R, s tuple de S et t(B₁)Өs(B₂) est vérifiée }

     = { t’ / t’є R×S et t’(B₁)Өt’(B₂) est vérifiée }

jointure entre R et S sur les attributs B₁ et B₂

si B₁ = B₁ 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 = R₁ ( A = A ) R₂

    La jointure permet de relier les relations entre elles.

• la division

Soient R( A , B₁ ) et S( B₂ ) avec B₁ et B₂ définis sur le même domaine.

R( B₁ ÷ B₂ )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 B₂ existant dans S, dans R.

        

a1	b1
a1	b2
a2	b1

b1

b2

    

Il n’y pas le couple a₂ b₂ 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ées

accès aux tuples de la base (consultation, mise à jour), extension des relations

• LDD : Langage de Définition de Données

Définitions des relations et des contraintes associées, intension des relations

• LCD : Langage de Contrôle des Données

Gè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 ( val₁, val₂,…, val_n )

• 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 :

attribut₁ Ө attribut₂

où Ө est un opérateur de comparaison et attribut₁ et attribut₂ 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 = fils

ou 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 :

    R₁ = π _{numpil, numav} (Vol)

    S₁ = σ (Avion, numav = ‘Airbus%’)

    S₂ = π _numav

    Résultat = R₁ ÷ R₂

π _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 + partitionnement
• double NOT EXISTS
• algé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     *

                 FROM      Vol V

                 WHERE     V.numpil = P.numpil ) ;

Ex : quels sont les pilotes conduisant tous les airbus ?

SELECT        *

FROM        Pilote P

WHERE    NOT EXISTS    ( SELECT    *

                      FROM     Avion A

                      WHERE    nomav = ‘Airbus’

                     AND    NOT EXISTS ( SELECT *

                                     FROM Vol V

                                     WHERE V.numpil = P.numpil

                                     AND V.nomav = A.nomav ));

!!! Il faut respecter la conduite d’interdépendance entre les blocs.

Ex : quels sont les pilotes tels qu’il n’existe pas d’avion, conduit par le pilote Dupont, qu’ils ne conduisent pas ?

SELECT        *

FROM        Pilote P

WHERE    NOT EXISTS    ( SELECT    *

                      FROM     Pilote P1, Vol V

                      WHERE    V.numpil = P1.numpil

                     AND    P1.nompil = ‘Dupont’

                     AND    NOT EXISTS ( SELECT *

                                     FROM Vol V2

                                     WHERE V2.numpil = P.numpil

                                     AND V2.nomav = V.nomav ));

algébrique

Confer     les vues.

Les opérations de mise à jour

UPDATE nom_relation SET attribut1 = valeur1, attribut2 = valeur2

WHERE condition ;

Si la condition n’est pas spécifiée, la mise à jour s’effectue sur tous les tuples.

La valeur peut dépendre de l’ancienne valeur d’un ou plusieurs attributs ou être une requête (éventuellement avec jointure).

Ex : le pilote n°104 gagne le même salaire que le pilote n°105.

UPDATE Pilote SET salaire = ( SELECT    salaire

                     FROM     Pilote

                     WHERE     numpil = 105 )

WHERE numpil = 104 ;

Insertion de tuples

INSERT INTO nom_relation [ ( liste_attributs ) ]

VALUES ( liste_de_valeurs ) ;

Si la partie optionnelle n’est pas indiquée, l’insertion se fera en fonction de la définition de la table.

Si on ne désire pas valuer des attributs :

• Il est possible d’utiliser le mot clé NULL.
• Il faut indiquer dans la liste des attributs uniquement ceux qui seront valués.

Suppression de tuples

DELETE FROM nom_relation

WHERE condition ;

Notion de transaction

Une opération de mise à jour n’est pas inscrite de façon définitive après son exécution.

Pour cela, il faut :

• Demander sa prise en compte par l’ordre COMMIT.
• Quitter la session par EXIT (qui provoque un COMMIT).

Tant que l’ordre n’a pas été posté, il est possible de défaire les opérations de mise à jour par l’ordre ROLLBACK. Et ceci jusqu’au dernier point de validation (début de session ou dernier COMMIT exécuté).

Une transaction est donc un ensemble d’opérations encadrées par deux COMMIT.

!!! les ordres du LDD (ex : modification de structure) ont un impact immédiat.

SQL comme langage de définition de données

Travail sur l’intension des relations (structure) :

• Création de relation
• Modification de relation :

• ajout d’attributs
• modifier le type d’un attribut
• ajout ou suppression des contraintes

• Supprimer une relation

CREATE TABLE nom_table ( attribut₁ type₁, …, attribut_n type_n ) ;

ALTER TABLE nom_table

    ADD ATTRIBUTE (attribut_m type_m) ;

    MODIFY (attribut_m new_type_m) ;

    ADD CONSTRAINT     …    ;

    DROP CONSTRAINT    …    ;

DROP TABLE nom_table ;

!!! attention aux relations liées lors d’un DROP TABLE.

Les contraintes sous Oracle :

• Domaine

CHECK (condition)

• Relation

PRIMARY KEY (liste_attributs)

• Référence

FOREIGN KEY attribut REFERENCES nom_relation_ref (attribut_référencé)

avec attribut_référencé qui doit être clé primaire.

CREATE TABLE nom_table

    ( CONSTRAINT nom_contrainte type_contrainte

     CONSTRAINT CP_nom_table PRIMARY KEY (attribut₁, …,attribut_n)

     CONSTRAINT dom_age CHECK (age > 0) ) ;

Si il y a des relations statiques (pas de clés étrangères) :

• création des relations statiques
• création des relations dynamiques

Si il n’y a que des relations dynamiques :

• création des relations comme si elles étaient statiques
• utilisation d’ALTER

Tables système (méta-base) :

USERS ( owner, login, … )

ALL_TABLES ( table_name, owner, …)

ALL_CONSTRAINT ( table_name, owner, constraint_name, constraint_type, … )

ALL_TAB_COLUMNS ( table_name, owner, column_name, data_type, … )

ALL_CONS_COLUMN ( table_name, owner, constraint_name, column_name, … )

La meta-base permet de traiter les aspects d’intension de la base comme des extensions de la meta-base.

L’intérêt est l’uniformité d’interrogation et de représentation.

A la place du mot clé ALL, on utilise le mot clé USER pour n’obtenir que les infos en "local".

Pour voir toutes les tables :

SELECT    table_name

FROM        user_table ;

!!! toutes les valeurs des tables systèmes sont en majuscule.

SQL comme langage de contrôle de données

Le Data Base Administrator (DBA) gère le partage des données, il gère les droits et les privilèges des autres utilisateurs.

Dans Oracle 9, on a :

Gestion des utilisateurs

Permettre à un utilisateur d’exister par rapport à la base :

GRANT    privilège_système

TO    sujets

[ IDENTIFIED BY mot_de_passe ] ;

Privilège système :

    C accès aux tables et aux vues (LMD)

    R création de tables (LDD) + une partie du LCD

    DBA tout (LMD, LDD et LCD)

Supprimer un utilisateur :

REVOKE    privilège_système

TO    sujet ;

Gestion des privilèges utilisateurs

GRANT    privilèges

ON    objets

TO    sujets

[ WITH GRANT OPTION ] ;

Le mot clé ALL garantit tous les privilèges.

La clause WITH GRANT OPTION permet à l’utilisateur de partager ses privilèges obtenus à d’autres utilisateurs.

Ex : autoriser Dupont à sélectionner et insérer dans la table avion avec possibilité de transmettre ses droits.

GRANT    insert, select

ON    Avion

TO    Dupont

WITH GRANT OPTION ;

Pour supprimer des privilèges :

REVOKE    privilège

ON        objet

TO        sujet ;

!!! l’ordre de suppression des privilèges ne peut être donné que par celui qui a accordé le privilège et par le DBA.

Ex : supprimer l’accès de tous les utilisateurs à la table vol.

REVOKE    ALL

ON        Vol

TO        public ;

Les tables systèmes associées sont :

    user_privs : contient les privilèges des utilisateurs.

    user_tab_privs : contient les privilèges sur les tables des utilisateurs.

    all_tab_privs : contient les privilèges pour tous les utilisateurs.

Les index

Les index sont liés aux arbres_B.

Ils permettent des accès plus rapides.

Il y a deux sortes d’index :

• primaire ou unique
• non primaire

CREATE [ UNIQUE ] INDEX    nom_index

ON table ( attribut₁ [ ASC / DES ], attribut₂, …, attribut_n) ;

La clause UNIQUE indique que pour une valeur de l’index, il n’existe qu’un seul tuple référencé.

Ex : création d’un index composé sur nompil et adresse de Pilote.

CREATE INDEX    pil_index

ON Pilote ( nompil, adresse) ;

Pour supprimer un index :

DROP INDEX nom_index ;

Ex :

UPDATE Pilote

SET        adresse = ‘Nice’ WHERE numpil IN (200, 300, 400, 500, 600) ;

!!! les index sont couteux en gestion

     ne pas créer d’index s’ils ne sont pas nécessaires.

Oracle 7 : pas de contrainte

     utilisation des index primaires pour prendre en charge les contraintes.

Oracle 9 : contraintes ( primary key)

     création automatique d’un index unique sur le ou les attributs concernés.

Les tables systèmes :

    user_indexes ( index_name, index_type, table_name, … )

    all_indexes ( index_name, index_type, table_name, owner, … )

Les vues

Objectif : servir de filtre entre les utilisateurs et la base.

Une vue est une perception particulière de la base stockée uniquement sous la forme de requêtes.

Le résultat d’une vue n’est jamais stocké ( une vue = une requête ).

CREATE [ OR REPLACE ] VIEW nom_vue

AS requête_sql

[ WITH CHECK OPTION ] ;

La clause WITH CHECK OPTION impose la vérification des conditions exprimées dans la clause WHERE de la requête lors des opérations de mise à jour.

Une vue est manipulable comme une relation de base .

Ex : vue des pilotes gagnant plus de 5000 euros.

CREATE OR REPLACE VIEW pil_sup

AS     SELECT *

    FROM     Pilote

    WHERE     salaire > 5000 ;

Opération acceptée :

INSERT INTO pil_sup VALUES ( 700, ‘Dupont’, ‘Nice’, 3000 ) ;

mais la requête suivante ne renverra pas la ligne :

SELECT * FROM pil_sup ;

Si l’on ajoute la clause WITH CHECK OPTION l’opération d’insertion sera refusée.

On peut redéfinir le nom des attributs résultats grâce à des alias :

CREATE [ OR REPLACE ] VIEW

nom_vue (alias₁ attribut₁, alias₂ attribut₂, …)

AS requête_sql ;

Suppression d’une vue :

DROP VIEW nom_vue ;

Intérêt des vues :

• Permet de manipuler facilement des requêtes complexes.
• Confidentialité des données.
• Contraintes dynamiques.

Ex : confidentialité sur la relation pilote, masquer l’information sur les salaires.

CREATE VIEW pil_sans

AS     SELECT numpil, nompil, adresse

    FROM     Pilote ;

Les droits sont uniquement sur pil_sans et non sur pilote.

Contraintes d’intégrité dynamique :

Règles de gestion qui ne sont pas décrites par le modèle conceptuel. Elles doivent toujours être vérifiées.

Ex : empêcher un abonné d’avoir plus de 3 emprunts.

Ex : un pilote ne doit pas effectuer plus de 10 vols.

CREATE VIEW vol_ok

AS     SELECT *

    FROM     vol

    WHERE     numpil NOT IN (     SELECT numpil

                        FROM     Vol

                        GROUP BY numpil

                        HAVING COUNT (*) > 9 )

WITH CHECK OPTION ;

L’opération suivante ne sera possible que si la contrainte dans le WHERE est vérifiée :

INSERT INTO vol_ok VALUES ( … ) ;

Contraintes d’intégrité dynamique avec les vues :

Il faut exprimer dans la clause WHERE la contrainte d’intégrité et ajouter la clause WITH CHECK OPTION ainsi que de gérer les droits (insertion uniquement à travers la vue).

Problème des insertions (ou mise à jour) à travers les vues :

• Si les attributs de la vue sont un sous-ensemble des attributs de la relation de base.

Ex :

pilote_sans

Les attributs manquants seront valués à null.

S’il s’agit d’un attribut clé (constituant ou faisant partie de la clé), l’insertion est impossible.

• Si plusieurs relations sont dans la requête

Ex :

Liste des vols avec le nom du pilote et le nom de l’avion.

CREATE VIEW vol_libelle

AS     SELECT numvol, h_dep, h_arr, vill_dep, ville_arr, nompil, nomav

    FROM     Pilote P, Vol V, Avion A

    WHERE     P.numpil = V.numpil

        AND     V.numav = A.numav ;    

L’opération suivante est impossible :

INSERT INTO vol_libelle VALUES ( ‘IT104’, …, ‘Marseille’, ‘Paris’, ‘Durant’, ‘B709’) ;

!!! il est impossible d’utiliser INSERT, UPDATE et DELETE sur une vue multi-relation

     problème de propagation

Il est également impossible de modifier une vue dont la requête comporte un GROUP BY (car utilisation de sous-table).

Expression des divisions :

R ( A,B )        pilote et avion qu’il conduit

S ( B )            avion de vol

R ÷ S :

    π_A (R) – π_A ( π_A (R) × S – R )

        

Ex : quels sont les pilotes qui conduisent tous les avions ?

On aura des problèmes avec les avions qui ne sont pas dans vol. On peut utiliser la relation vol mais il faut alors ajouter des alias.

On peut aussi utiliser des vues :

CREATE VIEW avion_service

AS     SELECT numav

    FROM     Vol ;    

CREATE VIEW pilote_avion

AS     SELECT numpil, numav

    FROM     Vol ;    

CREATE VIEW pilote_service

AS     SELECT numpil

    FROM     Vol ;    

SELECT    *

FROM        pilote_service

MINUS

SELECT     numpil

FROM        ( SELECT    *

         FROM    pilote_service, avio_service

         MINUS

         SELECT    *

         FROM    pilote_avion ) ;

!!!! attention à l’ordre des attributs lors de l’utilisation de *.

TheBroyeur