BDD - Chapitre 1 : Algèbre Relationnelle et SQL

BDD – Chapitre 1
BDD - Chapitre 1 : Algèbre Relationnelle et SQL
Mise au point et compléments...
I. Les opérateurs de l'algèbre relationnelle
L'algèbre relationnelle et SQL s'appliquent aux bases de données relationnelles. Une base de
données relationnelle est un ensemble de tables (= relations). Chaque relation R a un schéma fixé R(A1; ...
; An) où {A1; ... ; An} est un ensemble d'attributs (chacun muni d'un type) et R est un ensemble de
lignes ou de n-uplets.
Ex : Relation R
ici 3 lignes.
●
de schéma R(A;B) avec A et B de type entier et avec R = {(1,4),(3,2),(5,2)} qui possède
Opérateurs de base sur les tables
Les opérateurs ensemblistes habituels :
○
Union : R υ S
○
Différence : R – S
○
Intersection : R n S
Essentiel : R et S doivent être de même schéma.
○
Produit cartésien : R x S
S
Ici R x S est
○
Sélection : (restriction)
R:F = {t Є R : t vérifie F}
Ex : R : (A > B)
○
Projection : R[A] = R restreint aux classes A
(A = liste d'attributs)
Ex : R[B]
○
Jointures : Voir étude complète ci-après.
page 1 / 6
BDD – Chapitre 1
●
Combiner les opérateurs pour exprimer des requêtes
Ex : Soit la base de données de 3 tables de schémas :
DEPOT(RefDepot, Adresse)
PRODUIT(RefP, Type)
STOCK(RefDepot, RefP, Quantite)
Exprimer la requête suivante : « Donner pour chaque dépôt les produits de type « Table » qu'il n'a pas en
stock. »
(DEPOT x PRODUIT) : ((Type = « Table ») [RefDepot, RefP]) – STOCK[RefDepot, RefP]
II. Requêtes SQL simples
Elles sont de la forme :
SELECT<liste d'expressions>
FROM <liste de variables de relations>
WHERE<conditions>
La <condition> peut contenir des sous-requêtes (= requêtes imbriquées).
Ex : (requête ci-dessus)
SELECT D.RefDepot, P.RefP
FROM DEPOT D, PRODUIT P
WHERE P.Type = « Table »
AND
(D.RefDepot, P.RefP) NOT IN
(SELECT RefDepot, RefP
FROM STOCK
);
Forme générale d'une requête SQL :
SELECT Exp1, ..., Expn
FROM Rel1.R1, Rel2.R2, ..., Relk.Rk
WHERE F;
=> s'exécute par la procédure :
Pour tout R1 Є Rel1, ..., pour tout Rk Є RelK
Faire
Si F alors
Calculer et Afficher Exp1, ..., Expn
Cas particulier : Si Exp1, ..., Expn sont des attributs distincts A1, ..., An on obtient la relation :
((R1 x ... x Rk) : F) [A1, ..., An]
où
R1 est une copie de la relation Rel1
Rk ........................................ Relk
page 2 / 6
BDD – Chapitre 1
Ex : Donner les dépots qui ont à la fois en stock les 2 produits de ref 1 et 2
SELECT S1.RefDepot
FROM STOCK S1, STOCK S2
WHERE S1.RefDepot = S2.RefDepot
AND S1.RefP = 1
AND S2.RefP = 2;
Se traduit en algèbre relationnelle par :
((S1 x S2) : F) [S1.RefDepot]
où
S1 = STOCK
S2 = STOCK et F est la formule soulignée
III. Extensions : GROUP BY, HAVING, UNION, etc
Forme générale d'une requête SQL :
SELECT ...
FROM ...
WHERE ...
GROUP BY ...
HAVING ...
ORDER BY ...
;
Ex : Dans la base de données « FILM(Titre, Annee, RefProd), PRODUCTEUR(RefProd, NomProd,
DateNaissance) », donner pour chaque nom de producteur qui a produit moins de 5 films depuis l'an 2000,
son nombre de films produits depuis 2000 (y compris pour les producteurs qui n'ont rien produit de 2000).
SELECT P.NomProd, COUNT(*) AS NbFilms
FROM PRODUCTEUR P, FILM F
WHERE P.RefProd = F.RefProd AND F.Annee >= 2000
GROUP BY P.RefProd
HAVING NbFilms < 5
UNION SELECT NomProd, 0 AS NbFilms
FROM PRODUCTEUR
WHERE RefProd NOT IN
(SELECT FILM
WHERE Annee >= 2000
);
page 3 / 6
BDD – Chapitre 1
Exprimer la requête : « Producteurs qui ont produit au moins 1 film en 2000 » => 3 façons différentes de
faire :
1. Requête imbriquée
SELECT NomProd
FROM PRODUCTEUR
WHERE RefProd IN
(SELECT RefProd
FROM FILM
WHERE Annee = 2000
);
2. Requête simple (pas d'imbrication)
SELECT DISTINCT P.RefProd
FROM PRODUCTEUR P, FILM F
WHERE P.RefProd = F.RefProd
AND F.Annee = 2000;
3. Sous-requête dans le FROM
SELECT DISTINCT P.NomProd
FROM PRODUCTEUR P, (SELECT *
FROM FILM
WHERE Annee = 2000) F
WHERE P.RefProd = F.RefProd;
Obligatoire : donner un nom (= alias) à la variable, ici F, appartenant à la table de la sous-requête.
4. Avec EXISTS
SELECT P.NomProd
FROM PRODUCTEUR P
WHERE EXISTS ( SELECT *
FROM FILM F
WHERE F.Annee = 2000
AND F.RefProd = P.RefProd
);
Remarque : La sous-requête est corrélée avec la requête principale. Elle utilise la variable P déclarée avant la
sous-requête. La sous-requête est exécutée autant de fois qu'il y a de lignes P dans la table PRODUCTEUR.
Avec des requêtes imbriquées on peut appliquer aussi (en plus de EXISTS, IN, etc.) les opérateurs ALL et
ANY (=SOME).
Ex : Donner le titre et la première année de chaque film qui a au moins une seconde version
SELECT F.titre, MIN(F.annee)
FROM FILM F
WHERE F.annee <> ANY( SELECT F2.annee
FROM FILM F2
WHERE F.titre = F2.titre
)
GROUP BY F.titre;
page 4 / 6
BDD – Chapitre 1
ou aussi :
SELECT titre, MIN(annee)
FROM FILM
GROUP BY titre
HAVING count(*) > 1;
IV. Les Jointures (en Algèbre Relationnelle et en SQL)
●
La Jointure Naturelle
R * S ou R
S
s'écrit aussi : R NATURAL JOIN S
Ex :
R
A
1
3
5
B
4
2
2
S
B C
2 6
3 1
R*S
A B C
3 2 6
5 2 6
Définition dans ce cas: R*S = ((RxS):(R.B = S.B))[A, R.B, C]
●
La Thêta Jointure
R *F S (R JOIN S ON F) où F est une formule.
Définition: R *F S = (RxS):F
●
Les Jointures Externes
○
○
Jointures Naturelles Externes
■
pleine (FULL) R °* S
R NATURAL FULL JOIN S
■
gauche (LEFT) R
■
droite (RIGHT) R * S
L
*
S
B
4
2
2
3
C
N
6
6
1
R
Thêta Jointures Externes
■ pleine R °* F S (R FULL JOIN S ON F)
Ex : R FULL JOIN S ON R.B > S.B
■
■
A
1
3
5
N
L
gauche R *F S
R
droite R * F S
A R.B S.B
1 4 2
1 4 3
3 2 N
5 2 N
C
6
1
N
N
Ex : En MySQL (Avec les bases de données FILMS et STOCKS)
Donner pour chaque film produit depuis 2000 le nom de son producteur.
SELECT titre, annee, nomProd
FROM FILM NATURAL JOIN PRODUCTEUR
WHERE annee >= 2000;
page 5 / 6
BDD – Chapitre 1
Donner les références des produits qui ne sont pas en stock (dans aucun dépôt).
SELECT DISTINCT P.RefP
FROM PRODUIT P LEFT JOIN STOCK S ON P.RefP = S.RefP
WHERE S.RefDepot IS NULL;
page 6 / 6