Rappel

sur les
Bases de Données

Didier DONSEZ
Université de Valenciennes
Institut des Sciences et Techniques de Valenciennes
donsez@[Link]

Didier Donsez, 1998-2000, Rappel Bases de Données 1

 Concepts de Base

• Modèle Relationnel SQL

n Concept de Base
• Relation Table
• Tuple Ligne
• Attribut Colonne
Didier Donsez, 1998-2000, Rappel Bases de Données

n Manipulation
• Opérateurs Relationnels Ordres
∪ , -, ∏ , σ, ×, ∩ , ÷ , SELECT …
• Composition d ’Opérateurs FROM …
associativité des jointures, … WHERE ...

04/10/00 2
 Exemples de Requêtes

n Soit la base constituée par les relations

BUVEURS(NB, NOM, VILLE)
VINS(NV, CRU, MILL, REGION, COULEUR)
CONSO(NB, NV, QTE)
n Mono-Relation
Didier Donsez, 1998-2000, Rappel Bases de Données

• Noms des buveurs parisiens

∏ ( σ( Buveurs/ Ville=‘Paris ’)/ Nom)
• Crus des bordeaux rouges de millésime 1994
∏(
σ( Vins/ Region=‘Bordelais ’ ∧ Mill=1994 ∧
Couleur=‘Rouge ’)
04/10/00 / Cru) 3
 La Restriction σ et la Projection ∏

Buv NB Nom Ville

100 Ben Nice
101 Ben Lille
102 Charlie Paris
103 David Paris
Didier Donsez, 1998-2000, Rappel Bases de Données

σ( Buveurs/ Ville=‘Paris ’) ∏ (Buveurs/Ville)

Ville Proj Ville

Rest NB Nom
Nice
102 Charlie Paris
Lille
103 David Paris
Paris
04/10/00 4
 Exemples de Requêtes

n Soit la base constituée par les relations

BUVEURS(NB, NOM, VILLE)
VINS(NV, CRU, MILL, REGION, COULEUR)
CONSO(NB, NV, QTE)

n Multi-Relations
• Noms des buveurs de vins
Didier Donsez, 1998-2000, Rappel Bases de Données

∏ ( σ( (Buveur x Conso)/ [Link]=[Link])/ Nom)

∏ ( ><( Buveur,Conso/ [Link]=[Link])/ Nom)
• Noms des buveurs qui ont consommé un vin rouge
∏( ><(σ( Vins/Couleur=‘Rouge ’),
><( Buveur,Conso/ [Link]=[Link])
/[Link]=[Link])
/Nom)
04/10/00 5
 L ’union∪ et l ’intersection ∩
Buv1 NB Nom Ville Buv2 NB Nom Ville
100 Ben Nice 101 Ben Lille
101 Ben Lille 102 Charlie Paris
103 David Paris
Didier Donsez, 1998-2000, Rappel Bases de Données

Buveurs1 ∪ Buveurs2 Buveurs1 ∩ Buveurs2

Union NB Nom Ville Inter NB Nom Ville

100 Ben Nice 101 Ben Lille
101 Ben Lille
102 Charlie Paris
103 David Paris 6
04/10/00
 La Jointure ><

Buv NB Nom
100 Al
101 Ben NB Nom NB Qte
102 Charlie 100 Al 100 50
103 David 100 Al 100 25
Didier Donsez, 1998-2000, Rappel Bases de Données

100 Al 100 75
Conso NB Qte 101 Ben 101 100
100 50 101 Ben 101 50
100 25 102 Charlie 102 25
100 75
101 100
101 50
102 25
04/10/00 7
 Propriétés de l ’algèbre relationnelle

n Associativité des jointures

• Noms des buveurs qui ont consommé un vin rouge
∏( ><( σ( Vins/Couleur=‘Rouge ’),
><( Buveur,Conso/ [Link]=[Link])
Didier Donsez, 1998-2000, Rappel Bases de Données

/[Link]=[Link])
/Nom)

∏ ( ><( Buveurs
><(σ( Vins/Couleur=‘Rouge ’),Conso/ [Link]=[Link])
/[Link]=[Link])
/Nom)

04/10/00 8
 Propriétés de l ’algèbre relationnelle

n Commutativité des jointures et des restrictions

• Noms des buveurs qui ont consommé un vin rouge

∏ ( σ( ><( Buveurs
Didier Donsez, 1998-2000, Rappel Bases de Données

><(Vins,Conso/ [Link]=[Link])
/[Link]=[Link])
/Couleur=‘Rouge ’)
/Nom)

04/10/00 9
 Représentation
en Arbre Algébrique
Utile à l ’optimiser pour déterminer un plan d ’exécution de la requête optimal

R R
Partition
+Agrégat ∑ (Qte) ∑ (Qte)

NB,Nom,Qte NB,Nom,Qte
Didier Donsez, 1998-2000, Rappel Bases de Données

NV=NV NB=NB

NB=NB NV=NV

Mill=1994 Ville=Paris Mill=1994 Ville=Paris

Vins Conso Buveurs Vins Conso Buveurs

04/10/00 10
 La Notion de Clé

n Clé d ’un relation

• Groupe minimum d ’attributs dont la connaissance de la valeur permet de
connaître celle des autres attributs
• identifie un tuple parmi les autres
n Clés candidates (Candidate Keys)
• plusieurs clés possibles pour la même relation
Didier Donsez, 1998-2000, Rappel Bases de Données

• CodePostal(Ville,Rue,CodPost) -> (Ville,Rue) ou (CodPost)

n Clé primaire (Primary Key)
• une clé choisie parmi les clés candidates (CodPost)
n Clés étrangères (Foreign Keys)
• clé primaire C d ’une relation A
apparaissant parmi les attributs d ’une relation B
• on dit : C de B référence A
• définit une contrainte d ’intégrité référentielle
04/10/00 11
 Création des Tables en SQL
CREATE TABLE BUVEURS( NB DECIMAL(5),
NOM CHAR(20),
VILLE CHAR(15),
PRIMARY KEY(NB) -- clé primaire
);
CREATE TABLE VINS( NV CHAR(5),
CRU CHAR(20),
MILL INTEGER,
Didier Donsez, 1998-2000, Rappel Bases de Données

REGION CHAR(10),
COULEUR CHAR(10)
PRIMARY KEY(NV) -- clé primaire
);
CREATE TABLE CONSO( NB DECIMAL(5),
NV CHAR(5),
QTE DECIMAL(5)
PRIMARY KEY(NB,NV), -- clé primaire
FOREIGN KEY (NB) REFERENCES BUVEURS, -- clé étrangère
FOREIGN KEY (NV) REFERENCES VINS, -- clé étrangère
);
04/10/00 12
 Requêtes : ordre SELECT
SELECT Colonnes à “Projeter”
FROM Tables Sources
WHERE Conditions sur les Lignes
GROUP BY Colonnes de Groupage
HAVING Condition sur les groupes
ORDER BY Colonnes du tri
Didier Donsez, 1998-2000, Rappel Bases de Données

n Requêtes Mono-Tables
• 1 seule table source
n Requêtes Multi-Tables
• N tables sources
• Condition testée sur chaque ligne
issu de leur produit cartesien
04/10/00 13
 Exemples de Requêtes

n Mono-Table
• Noms des buveurs parisiens
• Crus des bordeaux rouges de millésime 1994
n Multi-Tables
Didier Donsez, 1998-2000, Rappel Bases de Données

• Noms des buveurs de vins

• Noms des buveurs parisiens qui ont consommé un vin rouge
• Exprimez les jointures de 4 manières différentes
• Noms des buveurs qui ne boivent pas de vins

04/10/00 14
 Exemple de Jointure
SELECT * FROM Buveurs JOIN Conso
ON ([Link]=[Link])

Buv NB Nom
100 Al
101 Ben NB Nom NB Qte
102 Charlie 100 Al 100 50
103 David 100 Al 100 25
Didier Donsez, 1998-2000, Rappel Bases de Données

100 Al 100 75
Conso NB Qte 101 Ben 101 100
100 50 101 Ben 101 50
100 25 102 Charlie 102 25
100 75
101 100
101 50
102 25
04/10/00 15
 Réponses avec X et JOIN
SELECT [Link]
FROM BUVEURS, CONSO, VINS
WHERE [Link]=’Paris ’AND [Link]=’Rouge’
AND [Link] = [Link] AND [Link] = [Link];
n Et …
SELECT [Link]
FROM (BUVEURS JOIN CONSO ON [Link] = [Link])
Didier Donsez, 1998-2000, Rappel Bases de Données

JOIN VINS ON [Link] = [Link]

WHERE [Link]=’Paris’AND [Link]=’Rouge’;
n Et …
SELECT [Link]
FROM (BUVEURS JOIN CONSO USING(NB)) JOIN VINS USING(NV)
WHERE [Link]=’Paris’AND [Link]=’Rouge’;
n Et …
04/10/00 16
 Réponses avec IN et EXISTS
SELECT [Link]
FROM BUVEURS
WHERE [Link]=’Paris’
AND [Link] IN ( SELECT [Link]
FROM CONSO
WHERE [Link] IN (
SELECT [Link]
FROM VINS
WHERE [Link]=’Rouge’)
Didier Donsez, 1998-2000, Rappel Bases de Données

);
SELECT [Link]
FROM BUVEURS
WHERE [Link]=’Paris’
AND EXISTS( SELECT *
FROM CONSO
WHERE [Link] = [Link]
AND EXISTS( SELECT *
FROM VINS
WHERE [Link]=[Link]
AND [Link]=’Rouge’ )
17
04/10/00
);
 Réponses avec
NOT IN et NOT EXISTS
Noms des buveurs qui ne boivent pas de vins
SELECT [Link]
FROM BUVEURS
WHERE [Link] NOT IN ( SELECT [Link]
FROM CONSO
);
Didier Donsez, 1998-2000, Rappel Bases de Données

SELECT [Link]
FROM BUVEURS
WHERE NOT EXISTS( SELECT *
FROM CONSO
WHERE [Link] = [Link]
);

04/10/00 18
 Agrégats et Groupage (i)

n Fonctions d ’agrégat
• SUM, AVG (Moyenne), MIN, MAX, COUNT

n Agrégat Simple
Didier Donsez, 1998-2000, Rappel Bases de Données

• Moyenne des consommations de vins rouges

n Agrégat Groupé (clause GROUP BY)

• Moyenne des consommations par couleur de vin
• Quantité totale de vins consommée par chaque buveur

n Condition sur les Groupes (clause HAVING)

• Nom des buveurs qui consomment plus que la moyenne
04/10/00 19
 Agrégats et Groupage (ii)
Quantité totale de vins consommée par chaque buveur ?

Buv NB Nom
100 Al
101 Ben NB Nom NB Qte
102 Charlie 100 Al 100 50
100 Al 100 25 Nom Qte
Al 150
Didier Donsez, 1998-2000, Rappel Bases de Données

100 Al 100 75
Conso NB Qte 101 Ben Ben 150
101 100
100 50 101 Ben Charlie 25
101 50
100 25 102 Charlie 102 25
100 75
101 100 54,166 108,33
101 50 Moyenne des quantités de vin
102 25 consommées
04/10/00 Moyenne de la quantité totale de vins consommée par chaque buveur
20
 Agrégats et Groupage (Réponses)

• Moyenne des consommations de vins rouges

SELECT AVG(QTE) FROM CONSO, VINS
WHERE [Link]=’Rouge’AND [Link] = [Link];
• Moyenne des consommations par couleur de vin
SELECT COULEUR, AVG(QTE) FROM CONSO, VINS
WHERE [Link] = [Link] GROUP BY COULEUR;
Didier Donsez, 1998-2000, Rappel Bases de Données

• Quantité totale de vins consommée par chaque buveur

SELECT NB, SUM(QTE) AS QTOT FROM CONSO GROUP BY NB;
• Nom des buveurs qui consomment plus que la moyenne
SELECT NOM FROM BUVEUR
WHERE NB IN (
SELECT NB FROM CONSO GROUP BY NB
HAVING SUM(QTE) >
(SELECT AVG(QTOT) FROM
(SELECT NB, SUM(QTE) AS QTOT FROM CONSO GROUP BY NB)));
04/10/00 21
 La Jointure externe (i)

• Liste des Buveurs avec leur consommation

SELECT [Link], SUM([Link])
FROM BUVEUR JOIN CONSO ON [Link] = [Link];

n Remarque
• les buveurs n ’ayant pas bu n ’apparaissent pas
Didier Donsez, 1998-2000, Rappel Bases de Données

n Solution (avec des INNER JOIN)

(SELECT [Link], SUM([Link])
FROM BUVEUR JOIN CONSO ON [Link] = [Link])
UNION
(SELECT NOM, 0
FROM BUVEURS
WHERE NB NOT IN (SELECT NB FROM CONSO));

04/10/00 22
 La Jointure externe (ii)

n Définition
• Jointure
• Les lignes qui ne satisfont jamais la condition de jointure,
sont complétés (padded) avec des nulls. Le "padding" peut
être complet (FULL), à droite (RIGHT) ou à gauche (LEFT)
Didier Donsez, 1998-2000, Rappel Bases de Données

n La réponse
SELECT [Link], [Link], SUM(NVL([Link],0))
FROM BUVEURS LEFT OUTER JOIN CONSO
ON [Link] = [Link]
GROUP BY [Link], [Link];

04/10/00 23
 La Jointure externe (iii)
LEFT OUTER JOIN

Buv NB Nom
100 Al
101 Ben NB Nom NB Qte
102 Charlie 100 Al 100 50
100 Al 100 25 Nom Qte
103 David Al 150
Didier Donsez, 1998-2000, Rappel Bases de Données

100 Al 100 75
Conso NB Qte 101 Ben Ben 150
101 100
100 50 101 Ben Charlie 25
101 50
100 25 David 0
102 Charlie 102 25
100 75 103 David Null Null
101 100
101 50
102 25
04/10/00 24
 La Jointure externe (iv)
FULL OUTER JOIN

Buv NB Nom
100 Al
101 Ben NB Nom NB Qte
102 Charlie 100 Al 100 50
103 David 100 Al 100 25
Didier Donsez, 1998-2000, Rappel Bases de Données

100 Al 100 75
Conso NB Qte 101 Ben 101 100
100 50 101 Ben 101 50
100 25 102 Charlie 102 25
100 75 103 David Null Null
101 100 Null Null 104 125
101 50
102 25
04/10/00
104 125 25
 La Jointure externe (v)

n Syntaxe Oracle
SELECT [Link], [Link], SUM(NVL([Link],0))
FROM BUVEURS, CONSO
WHERE [Link] (+) = [Link]
GROUP BY [Link], [Link];
n Syntaxe SQLBase, DB2
SELECT [Link], [Link], SUM(NVL([Link],0))
Didier Donsez, 1998-2000, Rappel Bases de Données

FROM BUVEURS (+), CONSO

WHERE [Link] = [Link]
GROUP BY [Link], [Link];
n Syntaxe Sybase
SELECT [Link], [Link], SUM(NVL([Link],0))
FROM BUVEURS, CONSO
WHERE [Link] += [Link]
GROUP BY [Link], [Link];
04/10/00 26
 Puzzle de Joe Celko ([Link])

n La table NAME contient ces 13 noms

n On veut ce tableau
name1 name2 name3 name4
=================================
Didier Donsez, 1998-2000, Rappel Bases de Données

Al Ben Charlie David

Ed Frank Greg Howard
Ida Joe Ken Larry
Mike NULL NULL NULL

04/10/00 27
 Elément de réponse

n Que donne cette requête ?

SELECT [Link], MIN([Link]) AS name2
FROM Names AS N1 LEFT OUTER JOIN Names AS N2 ON [Link] < [Link]
WHERE [Link]
IN (
SELECT [Link] FROM Names AS A INNER JOIN Names AS B ON [Link] <=
Didier Donsez, 1998-2000, Rappel Bases de Données

[Link]
GROUP BY [Link]
HAVING MOD(COUNT([Link]), 2) = (SELECT MOD(COUNT(*),2) FROM Names)
)
GROUP BY [Link]
ORDER BY [Link];

04/10/00 28
 La réponse
SELECT [Link], LEFT OUTER JOIN
MIN([Link]) AS name2, Names AS N5
MIN([Link]) AS name3, ON [Link] < [Link]
MIN([Link]) AS name4, AND [Link] < [Link]
MIN([Link]) AS name5 AND [Link] < [Link]
FROM (Names AS N1 AND [Link] < [Link]
LEFT OUTER JOIN WHERE [Link] IN (SELECT [Link]
Didier Donsez, 1998-2000, Rappel Bases de Données

Names AS N2 FROM Names AS A

ON [Link] < [Link]) INNER JOIN
LEFT OUTER JOIN Names AS B
Names AS N3 ON [Link] <= [Link]
ON [Link] < [Link] GROUP BY [Link]
AND [Link] < [Link] HAVING MOD(COUNT([Link]), 5) =
LEFT OUTER JOIN (SELECT MOD(COUNT(*),5)
Names AS N4 FROM Names))
ON [Link] < [Link] GROUP BY [Link] ORDER BY [Link];
AND [Link] < [Link]
AND [Link] < [Link] 29
04/10/00
 Le calcul du Rang (Top Five)

n On veut connaître le rang des buveurs en

consommation (jusqu ’à la 5ème place)
par couleur COUNT(Distinct Qte)

COUNT(Qte)

Conso Nom Couleur Qte Rang Rang

Didier Donsez, 1998-2000, Rappel Bases de Données

Alice Rouge 1000 1 1

Bob Rouge 900 2 2
Cary Rouge 800 3 4
Daniel Rouge 800 3 4
Eric Rouge 700 4 5
Frank Rouge 600 5 7
Gilles Rouge 600 5 7
04/10/00 30
 Le calcul du Rang (Top5)

n La réponse
SELECT [Link], [Link], [Link],
(SELECT COUNT(DISTINCT Qte) FROM Conso AS C1
WHERE ([Link] >=[Link]) AND ([Link]=[Link]))
AS RANG
Didier Donsez, 1998-2000, Rappel Bases de Données

FROM Conso AS C0
WHERE RANG<= :n; -- n est ajustable (5 pour un Top5)

04/10/00 31
 Les Vues (Views)

n Schéma Externe
• définit à partir du schéma conceptuel ou d ’une autre vue
n Exemple
• Vue sur les vins rouges
• Vue sur les gros buveurs (quantité totale > 100)
Didier Donsez, 1998-2000, Rappel Bases de Données

CREATE VIEW GROSBUVEURS(NB, NOM, VILLE, QTE)

AS SELECT NB, NOM, VILLE, SUM(QTE)
FROM BUVEURS, CONSO
WHERE [Link]=[Link]
GROUP BY NB
HAVING SUM(QTE) > (SELECT AVG(QTETOT) FROM
(SELECT SUM(QTE) AS QTETOT FROM CONSO GROUP BY NB))

n Modification sur une vue

04/10/00
• pas toujours possible (à moins d ’un trigger INSTEAD OF) 32
 Les Modifications

n Insertion
• “ Inserer un nouveau tuple décrivant un Riesling de 1997 ”
INSERT INTO VINS (NV, CRU, MILL, COULEUR)
VALUES (‘RIES1’, ‘Riesling’, 1997, ‘Blanc’)

n Modification de tuples
Didier Donsez, 1998-2000, Rappel Bases de Données

• “ Augmenter de 1 les consommations du buveur 101 ”

UPDATE CONSO SET QTE = [Link] + 1
WHERE ([Link]=101)

n Suppression de tuples
• “ Supprimer les consommations des buveurs habitant Paris”
DELETE FROM CONSO
WHERE NB IN (SELECT NB FROM BUVEURS WHERE VILLE = ‘Paris’)
04/10/00 33
 Les limites de SQL

n Compatibilité SQL92 des SGBDs

• 3 niveaux (Full, Intermediate, Entry)
n Programmation Procédurale
• puzzle de Joe Celko, rang (Top Ten), ...
• Réponse : Embedded SQL, ODBC/JDBC, PL/SQL, …
Didier Donsez, 1998-2000, Rappel Bases de Données

n Récursion
• relation composant/composé, graphe, arbre, …
• Réponse : SQL3
n Orientation-Objet
• Abstraction, Spécialisation, Généralisation
• Réponse : SQL3 et le modèle Objet-Relationnel
04/10/00 34
 Conception de Bases de Données

• 2 approches complémentaires
n Théorie de la Normalisation
• Déterminer les attributs et leurs dépendances
• Décomposer la relation universel en 3ème forme normale
• suppression des redondances
Didier Donsez, 1998-2000, Rappel Bases de Données

n Modèle Entité-Association E/A [Chen79]

• Concept repris par tous (MERISE, OMT, … , UML)
• Modéliser en Entité et en Association
Etudiants Suivre Cours

numss nom adresse année code titre heures

04/10/00
• Transformation vers le modèle relationnel 35
 Bibliographie

• Chris Date, "Introduction aux Bases de Données", 6ème

édition, Ed Intl Thomson Publ. ISBN 2-84180-964-1, 970 pp
• 7ème édition en anglais
• Jeffrey D. Ullman, Jennifer Widom, "A First Course in
Database Systems", 1ère édition, Ed. Prentice Hall
Didier Donsez, 1998-2000, Rappel Bases de Données

Engineering, Science & Math, Avril 1997, ISBN 0-13-

861337-0, 470 pp.
• G. Gardarin, "Bases de Données Objet et Relationnel", Ed
Eyrolles, 1999, ISBN 2-212-09060-9
• Joe Celko, "SQL Avancé", 1997, Ed Intl Thomson Publ.,
ISBN 2-84180-141-1
• [Link]
04/10/00 36