Evry, 29-31 mai 2000

Programme

Introduction aux BD et aux SGBD
Le modèle relationnel
© INT

Le langage de requête SQL

La conception d’une BD relationnelle

Protection des informations

Perspectives des BD
Evry, 29-31 mai 2000
1
Le modèle relationnel

Inventé par E. F. Codd

Laboratoire de recherche IBM à San José

Publication ACM 1970 "A Relational Model of Data for
Large Shared Data Banks"
© INT
Evry, 29-31 mai 2000
2
Les concepts descriptifs
© INT

Notion de domaine

Clé

Produit cartésien

Schéma de relation

Relation

Clé étrangère

Attribut

Métabase

Exemples de relations

Résumé des notions
Evry, 29-31 mai 2000
3
Notion de domaine

Définition
– Ensemble de valeurs

Exemples
– Entier, réel, chaîne de caractères, booléen
– Salaire = 4000...100000
– Couleur = {bleu, blanc, rouge}
– Point = [X: entier, Y: entier]
– Triangle = [P1, P2, P3: Point]
– Polygone = {Point}
© INT
Evry, 29-31 mai 2000
4
Produit cartésien

Définition
– Le produit cartésien de D1, ...., Dn est l'ensemble des n-uplets (Tuples)
<V1, ...., Vn> tel que Vi  Di.

Notation
– D1 X ....X Dn
© INT
Evry, 29-31 mai 2000
5
Produit cartésien (suite)

Exemple
– D1 = {Cadre, Ouvrier, Directeur}
– D2 = {5000, 15000, 30000}
D1 X D2
© INT
D1
Cadre
Cadre
Cadre
Ouvrier
Ouvrier
Ouvrier
Directeur
Directeur
Directeur
D2
5000
15000
30000
5000
15000
30000
5000
15000
30000
Evry, 29-31 mai 2000
6
Relation

Définition
– sous-ensemble du produit cartésien d'une liste de domaines
– caractérisée par un nom.

Exemple
– D1 = Fonction
– D2 = Salaire
Salaire_moyen
© INT
D1
Cadre
Ouvrier
Directeur
D2
15000
5000
30000
Evry, 29-31 mai 2000
7
Relation (suite)

Plus simplement, une relation est un tableau à deux
dimensions.

Une ligne est un n-uplet (tuple).

On associe un nom à chaque colonne afin de la repérer
indépendamment de l'ordre.
© INT
Evry, 29-31 mai 2000
8
Attribut

Définition
– nom donné à une colonne d'une relation
– prend ses valeurs dans un domaine

Exemple
– Fonction
© INT
Evry, 29-31 mai 2000
9
Exemples de relations
Salaire_moyen
Départements
© INT
D1
Cadre
Ouvrier
Directeur
Nom
R&D
DRH
R&D
R&D
Production
D2
15000
5000
30000
Région
Ile de France
Ile de France
Sarthe
Ile de France
Sarthe
Directeur
Le Blanc
Dupont
Le Bihan
Lajoie
Le Bihan
Nb_employé
100
300
100
200
250
Evry, 29-31 mai 2000
10
Clé

Définition
– Une clé est un groupe d'attributs minimum qui détermine un n-uplet
unique dans une relation (à tout instant).

Exemple
– Clé de salaire_moyen ?
– Clé de départements ?

Contrainte d'intégrité
– Toute relation doit posséder une clé renseignée (sans valeur inconnue).
© INT
Evry, 29-31 mai 2000
11
Schéma de relation

Définition
– Le schéma d'une relation décrit:


son nom

la liste des attributs qu'elle comporte et des domaines associés

la liste des attributs composant la clé (la clé est soulignée)
Exemple
– Départements(Nom: texte, Région: texte, Directeur: texte, Nb_employés:
entier)
© INT
Evry, 29-31 mai 2000
12
Schéma de relation (suite)

Intention
– Un schéma de relation: intention de la relation
– table: extension
– Le schéma d'une BD relationnelle: ensemble des schémas des relations
© INT
Evry, 29-31 mai 2000
13
Clé étrangère

Définition
– Une clé étrangère est un groupe d'attributs qui apparaît comme clé dans
une autre relation
– R1(A1, A2, .... , Ap, Ap+1, ...., An)
– R2(B1, B2, ......, Bn)

Rôle
– Les clés étrangères définissent des contraintes d'intégrités référentielles
entre relations
© INT
Evry, 29-31 mai 2000
14
Mises à jour et clés étrangères
Département
No_dept
R&D1
R&D2
R&D3
Prod
DRH
Directeur
1234
5678
4545
5678
1111
Employé
No
1234
5678
4545
1111
Nom
Le Blanc
Le Bihan
Lajoie
Dupont
– Insertion: la valeur des attributs doit exister dans la relation référencée.

Insertion de (R&D4, 2222) ?
– Suppression dans la relation référencée, les n-uplets référençant doivent
disparaître.

Suppression de l'employé 5678
– Les clés étrangères définissent les liens entités-associations.
© INT
Evry, 29-31 mai 2000
15
Clé étrangère

Exemples
– Département(No_départ, Libellé, Région)
– Employé(No_emp, Nom, Prénom, Adresse)
– Travaille_dans(No_dept, No_emp, Date_embauche)
– Clés étrangères:
© INT

No_dept dans Travaille_dans référence No_départ dans Département;

No_emp dans Travaille_dans référence No_emp dans Employé.
Evry, 29-31 mai 2000
16
Métabase

Définition
– base de données contenant l'ensemble des schémas et des règles de
correspondances associées à une base de données

Principe
– Une base décrivant les autres bases, c'est-à-dire:

les relations

+ les attributs

+ les domaines

+ les clés .....
– Notion de dictionnaire de données
– Base particulière, système, gérée par l'administrateur de BD
© INT
Evry, 29-31 mai 2000
17
Résumé des notions
© INT
Evry, 29-31 mai 2000
18
Les opérateurs de manipulation

Tout résultat d'une opération est une relation; celui-ci peut
donc être réutilisé en entrée d'un nouvel opérateur.

Les opérateurs peuvent être classifiés en :
– opérateurs ensemblistes / opérateurs relationnels
– opérateurs unaires / opérateurs binaires
– opérateurs de base / opérateurs dérivés
© INT

Base: union, différence, restriction, projection, produit cartésien

dérivés: intersection, jointure, division
Evry, 29-31 mai 2000
19
Union

But
– Permet de fusionner deux relations

Contraintes
– Binaire
– Même schéma
© INT
Evry, 29-31 mai 2000
20
Union (suite)

Exemple
Employé
No_emp
1234
4545
5678
1111
Nom
Le Blanc
Lajoie
Le Bihan
Dupont
Employé
© INT
Embauche No_emp
2222
3333
No_emp
1234
4545
5678
1111
2222
3333
Nom
Martin
Augier
Nom
Le Blanc
Lajoie
Le Bihan
Dupont
Martin
Augier
Evry, 29-31 mai 2000
21
Union (suite)

Notation
– Notation textuelle: T = R S
– Notation graphique: R
S

T
© INT
Evry, 29-31 mai 2000
22
Différence

But
– Conserver les tuples d'une relation ne figurant pas dans une autre

Contraintes
– Binaire
– Même schéma
– Non commutatif
© INT
Evry, 29-31 mai 2000
23
Différence (suite)

Exemple
Employé
No_emp
1234
4545
5678
2222
3333
1111
Nom
Le Blanc
Lajoie
Le Bihan
Martin
Augier
Dupont
Employé
© INT
Licenciement No_emp
1111
No_emp
1234
4545
5678
2222
3333
Nom
Dupont
Nom
Le Blanc
Lajoie
Le Bihan
Martin
Augier
Evry, 29-31 mai 2000
24
Différence (suite)

Notation
– Notation textuelle: T = R - S
– Notation graphique: R
S

T
© INT
Evry, 29-31 mai 2000
25
Restriction

But
– Permet de "sélectionner" des tuples
– La restriction réduit la taille de la relation verticalement

Contraintes
– Unaire
– Spécifier une condition
© INT
Evry, 29-31 mai 2000
26
Restriction (suite)

Exemple
Employé

No_emp
1234
4545
5678
2222
3333
Nom
Le Blanc
Lajoie
Le Bihan
Martin
Augier
Restriction telle que salaire 
15000
Employé
© INT
Salaire
15000
14000
16000
14000
16000

Restriction telle que Nom > 'Le
Blanc'
No_emp
1234
4545
2222
Nom
Le Blanc
Lajoie
Martin
Salaire
15000
14000
14000
Employé
No_emp
2222
Nom
Martin
Salaire
14000
Evry, 29-31 mai 2000
27
Restriction (suite)

Notation
– Notation textuelle: T =  cond (R)
– Notation graphique:
R
Cond
T
© INT
Evry, 29-31 mai 2000
28
Projection

But
– Permet de "sélectionner" des attributs
– La projection réduit la taille de la relation horizontalement

Contraintes
– Unaire
– Spécifier une liste d'attributs
© INT
Evry, 29-31 mai 2000
29
Projection (suite)

Employé


Exemple
No_emp
1234
4545
5678
2222
3333
Nom
Le Blanc
Lajoie
Le Bihan
Martin
Augier
Employé
© INT
Employé
Salaire
15000
14000
16000
14000
15000
Projection sur No_emp
No_emp
1234
4545
5678
2222
3333
Projection sur Nom

Nom
Le Blanc
Lajoie
Le Bihan
Martin
Augier
Projection sur No_emp et salaire
Employé
No_emp
1234
4545
5678
2222
3333
Salaire
15000
14000
16000
14000
15000
Evry, 29-31 mai 2000
30
Projection (suite)

Notation
– Notation textuelle: T =  liste d'attributs (R)
– Notation graphique:
R
Attributs
T
© INT
Evry, 29-31 mai 2000
31
Produit cartésien

But
– Ensemble de tous les tuples obtenus par concaténation de chaque tuple de
R avec chaque tuple de S
– Relation X Relation Relation

Contraintes
– Binaire
– Schéma du résultat:

R(a1, a2, ...., an), S(b1, b2, ..., bp)

T = R X S T(a1, a2, ...., an, b1, b2, ..., bp)
– Card (R X S) = Card (R) * Card (S)
© INT
Evry, 29-31 mai 2000
32
Produit cartésien (suite)

Exemple
Employé
No_emp
1234
5678
2222
3333
Nom
Le Blanc
Le Bihan
Martin
Augier
Employé X Département
© INT
Salaire
15000
16000
14000
15000
No_emp
1234
1234
5678
5678
2222
2222
3333
3333
Nom
Le Blanc
Le Blanc
Le Bihan
Le Bihan
Martin
Martin
Augier
Augier
Département No_dept
R&D1
R&D2
Salaire
15000
15000
16000
16000
14000
14000
15000
15000
No_dept
R&D1
R&D2
R&D1
R&D2
R&D1
R&D2
R&D1
R&D2
Evry, 29-31 mai 2000
33
Produit cartésien (suite)

Notation
– Notation textuelle: T = R X S
– Notation graphique: R
S
X
T
© INT
Evry, 29-31 mai 2000
34
Intersection

But
– Permet d’obtenir l’ensemble des tuples appartenant à deux relations

Contraintes
– Binaire
– Même schéma
© INT
Evry, 29-31 mai 2000
35
Intersection (suite)

Exemple
Directeur R&D No_emp
1234
4545
5678
1111
Nom
Le Blanc
Lajoie
Le Bihan
Dupont
Directeur Prod No_emp
5678
Nom
Le Bihan
Directeurs R&D et Prod No_emp Nom
5678
Le Bihan
© INT
Evry, 29-31 mai 2000
36
Intersection (suite)

Notation
– Notation textuelle: T = R S
– Notation graphique: R
S

T

Dérivation
– T = R - (R - S)
– T = S - (S - R)
© INT
Evry, 29-31 mai 2000
37
Jointure

But
– Permet d’établir le lien sémantique entre les relations

Contraintes
– Binaire
– Schéma du résultat:
© INT

R(a1, a2, ...., an)

S(b1, b2, ..., bp)

T = R X S  T(a1, a2, ...., an, b1, b2, ..., bp)
Evry, 29-31 mai 2000
38
Jointure (suite)

Exemple
Département
No_dept
R&D1
R&D2
R&D3
Prod
DRH
Département
© INT
Directeur
1234
5678
4545
5678
1111
No_dept
R&D1
R&D2
R&D3
Prod
DRH
Directeur
1234
5678
4545
5678
1111
Employé
No
1234
5678
4545
5678
1111
No
1234
4545
5678
1111
2222
3333
Nom
Le Blanc
Lajoie
Le Bihan
Dupont
Martin
Augier
Nom
Le Blanc
Le Bihan
Lajoie
Le Bihan
Dupont
Evry, 29-31 mai 2000
39
Jointure (suite)

Notation
– Notation textuelle: T = R
><
S
Critère de jointure
– Notation graphique : R
S
Critère de jointure
T

Dérivation
– Produit cartésien + restriction
© INT
Evry, 29-31 mai 2000
40
Division

But
– Répondre aux requêtes de type « tous les »
– Un tuple t est dans T si et seulement si pour tout tuple s de S, le tuple <t,s>
est dans R

Contraintes
– Binaire
– Schéma du résultat:
© INT

R(a1, a2, ...., an, b1, b2, ..., bp)

S(b1, b2, ..., bp)

T = R ¸ S  T(a1, a2, ...., an)
Evry, 29-31 mai 2000
41
Division (suite)

Exemple
Projet
No_proj
P2
P1
P5
P3
P1
P1
P3
No_emp
1234
5678
5678
1111
1234
1111
5678
– Quels sont les employés qui travaillent sur tous les projets ?
© INT
Evry, 29-31 mai 2000
42
Division (suite)

Exemple
– Construire R: ensemble de toutes les informations dont on a besoin
– Construire S: ensemble correspondant à "tous les" (projets)
R = Projet
No_proj
P2
P1
P5
P3
P1
P1
P3
T
© INT
No_emp
1234
5678
5678
1111
1234
1111
5678
S
No_projet
P1
P2
P3
P5
No_emp
5678
Evry, 29-31 mai 2000
43
Division (suite)

Notation
– Notation textuelle: T = R S
– Notation graphique: R
S

T

Dérivation
– Projection + Produit cartésien + Différence.
– R  S = T1 - T2 avec:
© INT

T1 =  schéma(R) - schéma(S) (R)

T2 = schéma(R) - schéma(S) (( schéma(R) - schéma(S) (R) X (S) - R)
Evry, 29-31 mai 2000
44
Bilan
Opérateurs
Union
Différence
Restriction
Projection
Produit cartésien
Intersection
Jointure
Unaire/Binaire
Binaire
Binaire
Unaire
Unaire
Binaire
Binaire
Binaire
Contraintes
Même schéma
Même schéma
Condition
Liste attributs
Schéma résultat
Même schéma
Schéma résultat
Notations
RS
R-S
 cond (R)
 liste d'attributs (R)
RXS
RS
R  S
critère
Division
© INT
Binaire
Schémas
R S
Evry, 29-31 mai 2000
45
Exemples de requêtes en algèbre
relationnelle

Base de données
– Employé(Nom_emp, Prénom_emp, No_ss, Date_naiss, Adresse_emp,
Sexe, Salaire, No_chef, Nod)
– Département(Nom_d, No_dept, No_dir, Date_affect)
– Dept_local(Num_dept, Id_loc)
– Projet(Nom, No_proj, Local_proj, Nod)
– Travaille_sur(No_ss, No_projet, Nb_heures)
© INT
Evry, 29-31 mai 2000
46
Requête 1

Nom et adresse de tous les employés travaillant pour le
département "R&D"

Solution textuelle
– DEPT_RECH <-  Nom_d="R&D" (Département)
– EMP_DE_RECH <- DEPT_RECH >< Employé
No_dept = Nod
– RESULTAT <-  Nom_emp, Prénom_emp, Adresse_emp (EMP_DE_RECH)
© INT
Evry, 29-31 mai 2000
47
Arbre algébrique (Requête 1)
© INT
Evry, 29-31 mai 2000
48
Requête 2

Pour chaque projet situé à Paris, donner le numéro du
projet, le numéro du département correspondant, et le nom,
l'adresse et la date de naissance du directeur

Solution textuelle
– PROJ_PARIS <-  Local_proj="Paris" (Projet)
– DEPT_DE_PROJ_PARIS <- PROJ_PARIS >< Département
Nod= No_dept
– DIR_DE_PROJ_PARIS <- DEPT_DE_PROJ_PARIS >< Employé
No_dir= No_ss
– RESULTAT <-  No_proj, No_dept, Nom_emp, Adresse_emp, Date_naiss
(DIR_DE_PROJ_PARIS)
© INT
Evry, 29-31 mai 2000
49
Arbre algébrique (Requête 2)
© INT
Evry, 29-31 mai 2000
50
Requête 3

Donner le nom des employés qui travaillent sur tous les
projets du departement

Solution textuelle
– PROJ_DE_DEPT5 <-  No_proj ( Nod=5 (Projet))
– EMP_PROJ <-  No_ss, No_projet(Travaille_sur)
– EMP_PROJETS_DEPT5 <- EMP_PROJ  PROJ_DE_DEPT5
– RESULTAT <-  Nom_emp (EMP_PROJETS_DEPT5 >< Employé)
No_ss = No_ss
© INT
Evry, 29-31 mai 2000
51
Arbre algébrique (Requête 3)
© INT
Evry, 29-31 mai 2000
52
Requête 4

Donner la liste des numéro de projets sur lesquels travaille
"Karl", soit en tant que directeur soit en tant qu'exécutant

Solution textuelle
– EMP_KARL <-  Prénom_emp="Karl" (Employé)
– TRAVAILLEUR_KARL  No_projet (EMP_KARL >< Travaille_sur)
No_ss = No_ss
– DEPT_DIRIGES_KARL  No_dept (EMP_KARL >< Département)
No_ss = No_dir
– PROJ_DIR_KARL <-  No_projet (DEPT_DIRIGES_KARL >< Projets)
No_dept = No_d
– RESULTAT <- TRAVAILLEUR_KARL  PROJ_DIR_KARL
© INT
Evry, 29-31 mai 2000
53
Arbre algébrique (Requête 4)
© INT
Evry, 29-31 mai 2000
54
Requête 5

Donner le nom des employés qui ne travaillent sur aucun
projet

Solution textuelle
– TOUS_EMP <-  No_ss (Employé)
– EMP_SUR_PROJ <-  No_ss (Travaille_sur)
– EMP_SANS_PROJ <- TOUS_EMP - EMP_SUR_PROJ
– RESULTAT <-  Nom_emp (EMP_SANS_PROJ >< Employé)
No_ss = No_ss
© INT
Evry, 29-31 mai 2000
55
Arbre algébrique (Requête 5)
© INT
Evry, 29-31 mai 2000
56
Requête 6

Donner le nom des directeurs qui travaillent au moins sur
un projet

Solution textuelle
– DIREC <-  No_dir (Département)
– TRAV <-  No_ss (Travaille_sur)
– RESULTAT <-  Nom_emp ((DIREC  TRAV) >< Employé)
No_ss = No_ss
© INT
Evry, 29-31 mai 2000
57
Arbre algébrique (Requête 6)
© INT
Evry, 29-31 mai 2000
58
Optimisation de requêtes
© INT

On peut répondre de diverses façon à une même requête

Optimiser = choisir la meilleure façon
Evry, 29-31 mai 2000
59
Exemple: Requête 1
© INT
Evry, 29-31 mai 2000
60
Facteurs déterminants
© INT

Ordre d'exécution des opérations algébriques

Algorithme implantant les opérations algébriques

Placement des données sur le disque

Taille des relations intermédiaires
Evry, 29-31 mai 2000
61
Restructuration

Règles
– Exécuter les opérateurs unaires le plus tôt possible (restriction, projection)
=> diminution de la taille des relations
– Exécuter les jointures le plus tard possible

Propriétés
– Associativité des jointures
– Commutativité restriction / projection
– Commutativité restriction / jointure
– Commutativité projection / jointure
© INT
Evry, 29-31 mai 2000
62