Bases de données - e-Learn Université Ouargla

Présenté par:
Rouagat Wahab
Contenu
 Algèbre relationnelle
 SQL
par: Rouagat Wahab
2
L'algèbre relationnelle
l'algèbre relationnelle a inspiré le langage SQL.
Elaborée par Codd dans sa thèse, elle repose sur un petit
nombre d’opérateurs que l'on applique à des relations
de la base de données.
Les opérateurs sont de deux types : relationnels et
ensemblistes.
par: Rouagat Wahab
3
Opérateurs relationnels
Projection
La projection est un opérateur d’accès par les colonnes.
La projection U d’une relation R sur une liste
d’attributs est la relation dont le schéma se réduit aux
attributs de la liste. Ses tuples sont ceux de R, avec
élimination des tuples en double.
par: Rouagat Wahab
4
Projection
 Exemple:
Soit la relation suivante:
A
R
B
C
a
1
a
b
1
b
a
1
d
b
2
f
A
par: Rouagat Wahab
B
a
1
b
1
b
2
5
Projection
par: Rouagat Wahab
6
Opérateurs relationnels
Restriction (ou Sélection )
La restriction est un opérateur d’accès par les lignes. Une
formule de restriction ou qualification q est une
expression logique reliant des attributs de la relation
opérande avec des constantes par l’intermédiaire
d’opérateurs de comparaison ; par exemple :
A = a AND B < 2
La restriction U de la relation R par la qualification q est
une relation de même schéma que R dont les tuples
sont ceux de R qui vérifient la qualification.
par: Rouagat Wahab
7
Restriction
 Exemple:
U=
A
R
B
б A=a (R)
C
a
1
a
b
1
b
a
1
d
b
2
f
par: Rouagat Wahab
A
B
C
a
1
a
a
1
d
8
Restriction
par: Rouagat Wahab
9
Opérateurs ensemblistes
Union
L’union de deux relations R et S de même schéma est la
relation de même schéma dont les tuples sont à la fois
ceux de R et de S.
par: Rouagat Wahab
10
Union
 Exemple:
Soit les relations R et S
S
A
a
R
B
3
A
C
f
A
B
C
a
1
a
b
1
b
a
1
d
b
2
f
par: Rouagat Wahab
K= R
US
B
C
a
1
a
b
1
b
a
1
d
b
2
f
a
3
f
11
Union
par: Rouagat Wahab
12
Opérateurs ensemblistes
Différence
La différence de deux relations de même schéma R et S
est une relation de même schéma dont les tuples sont
ceux de R n’appartenant pas à S. La différence n’est pas
un opérateur commutatif.
par: Rouagat Wahab
13
Différence
par: Rouagat Wahab
14
Opérateurs ensemblistes
L’intersection
L’intersection de deux relations R et S de même schéma
est une relation de même schéma constituée des tuples
qui appartiennent à la fois R et à S.
par: Rouagat Wahab
15
intersection
par: Rouagat Wahab
16
Produit cartésien
Le produit cartésien de deux relations R et S est une
relation dont le schéma est la concaténation de ceux
des relations composantes et dont les tuples sont
obtenus en combinant chaque tuple de R avec tous
ceux de S.
par: Rouagat Wahab
17
jointures
On distingue plusieurs opérateurs de jointures. Ils sont
associés à des formules de qualification reliant entre
eux certains attributs des relations composantes.
l’équijointure : La clause de qualification est une
égalité entre deux attributs des relations opérandes.
La relation résultante est le sous-ensemble du produit
cartésien réduit aux tuples vérifiant la qualification,
sans répétition, dans le schéma, de l’attribut commun.
par: Rouagat Wahab
18
Exemple:
soit la relation R et T ou:
R
A
B
C
a
1
a
b
1
b
a
1
d
b
2
f
par: Rouagat Wahab
19
jointure
par: Rouagat Wahab
20
Composition d’opérations
La composition des divers opérateurs permet d’envisager
des traitements algébriques complexes autorisant des
manipulations formelles des données.
Soient les relations :
EMP(numemp, nomemp, salaire, emploi, departement)
DEPT(numdept, nomdept, adresse, ville)
La détermination des informaticiens basés à Ouargla et
gagnant plus de 20000 DA se fera par :
par: Rouagat Wahab
21
Composition d’opérations
Cette expression peut être représentée graphiquement
par l’arbre statique d’exécution suivant :
par: Rouagat Wahab
22
arbre statique d’exécution
par: Rouagat Wahab
23
Exercice
On suppose qu'une bibliothèque gère une base de données dont le
schéma est le suivant :
Emprunt(Personne, Livre, DateEmprunt, DateRetourPrevue,
DateRetourEective)
Retard(Personne, Livre, DateEmprunt, PenalitéRetard)
Question:
Exprimer les requêtes suivantes en algèbre relationnelle:
1. Quelles sont les personnes ayant emprunté le livre "Recueil
Examens BD" ?
2. Quelles sont les personnes n'ayant jamais rendu de livre en
retard ?
3. Quelles sont les personnes ayant emprunté tous les livres
(empruntés au moins une fois) ?
4. Quels sont les livres ayant été empruntés par tout le monde
(i.e. tous les emprunteurs) ?
par: Rouagat Wahab
24
Solution
1:
2:
3:
4:
par: Rouagat Wahab
25
SQL (Structured Query Language)
Le langage SQL peut être considéré comme le langage d’accès
normalisé aux bases de données. Il est aujourd’hui supporté
par la plupart des produits commerciaux que ce soit par les
systèmes de gestion de bases de données micro tel que
Access ou par les produits plus professionnels tels que
Oracle ou Sybase. Il a fait l’objet de plusieurs normes
ANSI/ISO dont la plus répandue aujourd’hui est la norme
SQL2 qui a été définie en 1992.
Le succès du langage SQL est du essentiellement à sa
simplicité et au fait qu’il s’appuie sur le schéma conceptuel
pour énoncer des requêtes en laissant le SGBD responsable
de la stratégie d’exécution.
par: Rouagat Wahab
26
SQL
Le langage SQL comporte :
une partie sur la définition des données :
le langage de définition des données (LDD) qui permet de
définir des relations, des vues externes et des contraintes
d’intégrité;
une partie sur les requêtes :
le langage de manipulation des données (LMD) qui permet
d’interroger une base de données sous forme déclarative
sans se préoccuper de l’organisation physique des données;
une partie sur le contrôle des données :
le langage de contrôle des données (LCD) qui permet de
contrôler la sécurité et les accès aux données.
par: Rouagat Wahab
27
Le langage de définition des données (LDD)
Le langage de définition des données permet de définir
et de manipuler les concepts du modèle relationnel
liés au schéma : relation, attribut, clé, contraintes
d’intégrité, vues ainsi que certains éléments liés à
l’administration de la base de données : index, droits
des utilisateurs.
Les types de données disponibles dépendent du SGBD
mais on retrouve généralement les types entier, réel,
chaînes de caractères et date. Les principaux types de
données disponibles en SQL sont : SMALLINT,
INTEGER, DECIMAL, FLOAT, DOUBLE, DATE, TIME,
TIMESTAMP, CHAR et VARCHAR.
par: Rouagat Wahab
28
CREATE TABLE (LDD)
Crée une relation (table)
Syntaxe :
CREATE TABLE <nom_de_relation> (
<nom_d’attribut> <type_de_données> [NOT NULL]
[, ...]
[, PRIMARY KEY (<nom_d’attribut> [, ...] ) ]
[, FOREIGN KEY (<nom_d’attribut>) REFERENCES <nom_de_relation> [, ...]
])
par: Rouagat Wahab
29
exemple
Soit le schéma relationnel suivant:
Article (n_art, design, prix_ach, prix_v)
Stock (#sn_art, quantite, seuil)
La requête suivante nous permet de créer la relation
‘Article’:
create table article(
n_art
numeric(3) not null,
design
varchar(30),
prix_ach
decimal(6,2),
prix_v
decimal(6,2),
primary key (n_art)
)
par: Rouagat Wahab
30
exemple
Stock (# sn_art, quantite, seuil)
La requête suivante nous permet de créer la relation
‘stock’:
create table stock(
sn_art
numeric(3) not null,
quantite
numeric(2),
seuil
numeric(2),
primary key (sn_art),
foreign key (sn_art) references article(n_art)
)
par: Rouagat Wahab
31
ALTER TABLE (LDD)
Modifier une relation (table)
Syntaxe :
ALTER TABLE <nom_de_relation> ADD ( <nom_d'attribut> <type_de_données> )
ALTER TABLE <nom_de_relation> ADD constraint <contrainte>
Exemple:
Si on veut augmenter la relation stock par le champ etage de type
chaîne de caractères
Stock (#sn_art, quantite, seuil, etage)
alors, on doit appliquer la requête suivante:
alter table stock add etage varchar(2)
par: Rouagat Wahab
32
DROP TABLE (LDD)
Supprimer une relation (table)
Syntaxe :
DROP TABLE <nom_de_relation> [CASCADE CONSTRAINTS]
Remarque:
On ne peut pas supprimer une table qui est référencée par
d’autres table, en effet, Si on veut supprimer la table ‘article’ on
doit d'abord supprimer la table ‘stock’
Remarque :
[CASCADE CONSTRAINTS]: forcer la supprission de la relation ainsi
que toute relation référençante
Exemple:
drop table stock
par: Rouagat Wahab
33
Le langage de manipulation des données (LMD)
Insertion de données dans une relation
Syntaxe
INSERT INTO <nom_de_relation >
[(<liste_d’attributs>)] VALUES (<liste_de_valeurs >)
Exemples :
insérer les données (1, ordinateur, 300, 400) dans la table
‘article’:
insert into article values (1, ‘ordinateur’, 300, 400)
insérer les données (2, datashow) dans ‘article’:
insert into article (n_art, design) values (2, ‘datashow’)
par: Rouagat Wahab
34
Le langage de manipulation des données (LMD)
Suppression de données dans une relation
Syntaxe
DELETE FROM < nom_de_relation > [WHERE
<expression_de_selection > ]
Exemples :
Supprimer tous les enregistrements de la table ‘article’:
delete from article
Supprimer les enregistrements de la table ‘article’ où le
numéro d’article égale 2:
delete from article where n_art=2
par: Rouagat Wahab
35
Le langage de manipulation des données (LMD)
Modification des données d’une relation
Syntaxe
UPDATE <nom_de_relation> SET < champ >
[WHERE <expression_de_selection > ]
Exemples :
Modifier le prix d’achat et de vente dans la table ‘article’
où le nom d’article est datashow:
update article set prix_ach=30000 and prix_v=35000
where design=‘datashow’
par: Rouagat Wahab
36
Le langage de manipulation des données (LMD)
Sélection de données
Syntaxe
SELECT [DISTINCT] { * | <nom_d’attribut> [, ...] }
FROM <nom_de_relation> [, ...]
[WHERE <condition>]
Remarque: * remplace la liste des attributs de la relation
Exemples :
Quels sont les prix d’achat et de vente de l’article
‘datashow’?
select prix_ach, prix_v from article where
design=‘datashow’
par: Rouagat Wahab
37
Exemple de Sélection de données :
Quels sont les articles dans l’étage B6 et combien d’unité
disponible dans le stock?
Solution:
select design, quantite from article, stock where
sn_art=nart and etage=‘B6’
par: Rouagat Wahab
38
Lien entre algèbre relationnelle et SQL
Opération
Projection
Sélection
Produit cartésien
Jointure
Union
Intersection
Différence
Division
Expression algébrique Expression SQL équivalente
P A ( R1 )
SELECT A FROM R1
SELECT * FROM R1 WHERE
s < condition > ( R1 )
<condition >
R1 X R2
SELECT * FROM R1, R2
SELECT * FROM R1, R3 WHERE R1.A =
R1 * R3
R3.A
SELECT * FROM R1 UNION SELECT *
R1 U R2
FROM R2
SELECT * FROM R1, R2 WHERE R1.A =
R1 n R2
R2.C and R1.B = R2.D
SELECT * FROM R1 WHERE not exists
R1 / R2
(SELECT * FROM R2 WHERE R2.C =
R1.A and R2.D = R1.B )
R1 : R2
par: Rouagat Wahab
SELECT A FROM R1 GROUP BY A
HAVING COUNT (distinct B ) =
(SELECT count (distinct B ) FROM R2)
39
Exercice
Prenons l’exemple de la bibliothèque :
Emprunt(Personne, Livre, DateEmprunt, DateRetourPrevue,
DateRetourEective)
Retard(Personne, Livre, DateEmprunt, PenalitéRetard)
Questions:
En utilisant le langage SQL:
 Créer les relations Emprunt et Retard
 Exprimer les requêtes suivantes :
1. Quelles sont les personnes ayant emprunté le livre "Recueil
Examens BD" ?
2. Quelles sont les personnes n'ayant jamais rendu de livre en
retard ?
3. Quelles sont les personnes ayant emprunté tous les livres
(empruntés au moins une fois) ?
4. Quels sont les livres ayant été empruntés par tout le monde
(i.e. tous les emprunteurs) ?
par: Rouagat Wahab
40