Les bases de données

relationnelles

1ère Année: R&T

B.FRIKH 1
Généralités

B.FRIKH 2
Historique

 Comment les données peuvent être stockées et

extraites des organes périphériques?
 bandes magnétiques:
 Données stockées sous forme d’enregistrements
séquentiels pour constituer des fichiers
 Disque magnétique:
 accès plus rapide aux données
  développement des principes d’organisation et
B.FRIKH 3
Historique

 Or, les fichiers classiques posent un certain

nombre de défauts et de contraintes
 Les besoins de l’entreprise et la multitude des
données manipulées, nécessitent une
connaissance de plus en plus fine de leurs
activités
  concept des Bases de données
B.FRIKH 4
Historique

 Les années 60:

 première génération des SGBDs
 Séparation des données et des programmes
d’application
 SGBDs hiérarchiques et réseaux
 Les années 70:
 Deuxième génération: les SGBDs relationnels
 Actuellement: B.FRIKH 5
Étude de cas

 Considérons une compagnie d’assurance vendant

trois produits: Vie, Automobile, Local
 La compagnie est organisée en 3 départements,
chacun ayant ses propres fichiers
 Les fichiers et les programmes d’application
classiques sont utilisés pour effectuer les travaux
de la compagnie
B.FRIKH 6
Gestion des contrats

 Un fichier F1 des assurés contient les données

suivantes:
 N° de l’assuré, Nom, Prénom, Age, Adresse,
Profession, Condition d’assurance

 Les programmes de gestion des assurés:

 P1: Appel de prime
 P2: Production des attestations
 P3: Mises à jours B.FRIKH 7
Gestion des sinistres

 Un fichier F2 des sinistres contient les données

suivantes:
 N° de l’assuré, date du sinistre, détails du sinistres,
détails du sinistre

 Les programmes d’application

 P4: Saisie d’un sinistre
 P5: Mise à jour d’un sinistre
 P6: Règlement des sinistres
B.FRIKH
(impliquant F1 et F2) 8
Le système informatique de chaque département

LES ASSURES
Appel de prime
P1
attestations
F1 P2
MAJ assuré
P3

Règlement sinistre
P6

saisie sinistre
P4
F2 MAJ sinistre
P5

LES SINISTRES

B.FRIKH 9
Les défauts des fichiers classiques

 Monsieur X a souscrit une assurance vie et une

assurance automobile
 Son identification (Nom, Prénom, profession,
adresse,…) figure dans deux fichiers

 Pb 1:
 Redondance d’informations
B.FRIKH 10
Les défauts des fichiers classiques

 Monsieur X déménage et envoi un avis de

changement d’adresse
 L’avis aboutit au département Automobile
 Le département met à jour son fichier sans aviser
les autres

 Pb2: B.FRIKH 11
Les défauts des fichiers classiques

 Un changement de catégorie de prime exige une

nouvelle donnée dans le fichier F1: état
patrimonial
 Les programmes P2 et P3 sont à modifier même
s’ils ne sont pas concernés par cette donnée
 Le fichier F1 est à reconstituer complètement

B.FRIKH 12
Les défauts des fichiers classiques

 Question: combien de femmes de 25 à 40 ans

ont souscrit une assurance vie
 Il faut écrire un programme pour le savoir

 PB 4:
 Les programmes sont accessibles seulement à travers
des programmes d’application
B.FRIKH 13
Les défauts des fichiers classiques

 On veut savoir combien de personnes ont

souscrit une assurance vie, une assurance local
et pas d’assurance automobile
 Il faut interroger les trois fichiers
 Ce qui peut être complexe en raison des codages
différents

 PB 5:
B.FRIKH 14
 Objectifs des bases de données

Défauts des fichiers classiques Objectifs visés avec les bases de

données

Redondance d’information Unicité et cohérence de l’information

Dépendance des programmes à l’égard Indépendance totale entre les programmes

des données et les données

Les données sont accessibles uniquement Les langages de requête

par les programmes • manipulation des données par des non
informaticiens
• efficacité des accès)

Données dispersées Intégration des données

• administration centralisée
• sécurité des données

Chacun a ses propres fichiers Partageabilité des données

• + confidentialité

B.FRIKH 15
Base de données..?

 Une base de données est un ensemble de

données
 structurées,
 interdépendantes,
 stockées sans redondance inutile sur des supports
accessibles par l’ordinateur,
 organisées de manière indépendante des programmes
pour satisfaire simultanément
B.FRIKH plusieurs utilisateurs de 16
SGBD..?

 Un système de gestion de bases de données est

un logiciel qui permet à un utilisateur d’interagir
avec la BD pour
 stocker,
 chercher,
 mettre à jour les données

 en garantissant leur cohérence et leur sécurité

B.FRIKH 17
Les fonctions d’un SGBD

 Fonction principale
 organiser les données sur les supports, et fournir les
procédures de recherche et de sélection de ces
mêmes données
 Description des données
 Utilisation des données
 Synchronisation d’accès
 Confidentialité
B.FRIKH 18
Les fonctions d’un SGBD

 Description des données

 Le SGBD doit offrir à l’utilisateur un outil pour décrire
l’ensemble des données qui seront mémorisées dans
la BD
 LDD: langage de description des données
 Utilisation des données:
 Interrogation de la BD pour rechercher, selectionner et
modifier des données
 Deux types d’utilisateurs: informaticiens ou non
B.FRIKH 19
Les fonctions d’un SGBD
 Synchronisation d’accès
 X et Y utilisent la même partie de la BD
 X transfert la donnée A dans sa zone de travail
 Y en fait de même
 X modifie la donnée A et la retourne. Y en fait de même

A 3
A 4
A 10

10 – 6 = 4 10 – 7 = 3
Utilisateur X Utilisateur Y

Le SGBD introduit une exclusion mutuelle entre les utilisateurs dans le cas
de conflits B.FRIKH 20
Les fonctions d’un SGBD

 Confidentialité:
 Une BD est partagée
 Certains sous ensembles de données ne doivent être
accessibles que par des personnes réellement
autorisées
 Le SGBD offre des mécanismes de droit d’accès des
utilisateurs (mot de passe, permission d’accès…)
 Exemple: B.FRIKH 21
Les fonctions d’un SGBD

 L’intégrité:
 BD = intégration de toutes les données de l’entreprise
en un réservoir commun de données
 Grande quantité de données enregistrées  risque
que les données soient erronées
 Le SGBD donne à l’utilisateur la possibilité de définir
des règles qui permettent de maintenir l’intégrité de la
BD 22
B.FRIKH
Modèle relationnel et algèbre
relationnelle

23
Partie2

 Modèle relationnel
 Opérateurs de l’algèbre
 Exemples de requêtes
 Optimisation

24
 Modèle relationnel
Les concepts descriptifs
 Notion de domaine
 Produit cartésien
 Relation
 Attribut
 Clé
 Schéma de relation et de BD
 Clé étrangère
 Métabase

25
 Modèle relationnel
Notion de domaine

 Définition
 Ensemble de valeurs
 Exemples
 Entier, réel, chaîne de caractères, booléen
 Salaire = 1000…100000 (Dh)
 Couleur = {‘rosé’, ‘blanc’, ‘rouge’}

26
 Modèle relationnel

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
 Exemple : D1 X D2 D1 D2

 D1 = {‘BD’, ‘IO’} (Code UV) BD Benabou

BD Lahlou
 D2 = {‘Benabou’, ‘Lahlou’, ‘Mjahed} (Prof)
BD Mjahed
IO Benabou
IO Lahlou
IO Mjahed

27
 Modèle relationnel

Relation
 Définition
 Sous-ensemble du produit cartésien d'une liste de
domaines
 Caractérisée par un nom
 Exemple
 D1 = Code UV
 D2 = Prof UV D1 D2

BD Benabou
IO Lahlou

28
 Modèle relationnel

Relation (2)
 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 = attribut
 Prend ses valeurs dans un domaine
 Exemple : code UV code coord
BD Benabou
IO Lahlou

29
 Modèle relationnel

Exemples de relations
Elève Num Nom Adresse Age

1 Berrada Marakech 20
2 Mirouan Berrechid 20
3 Mjahed Marakech 21

Inscrit NumElève CodeUV Note

2 BD 10
1 BD 20
2 IO 17
3 IO 18

30
 Modèle relationnel

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 Elève ?
 Clé de UV ?
 Clé de Inscrit ?
 Contrainte d'intégrité
 Toute relation doit posséder une clé renseignée (sans valeur
inconnue)

31
 Modèle relationnel

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
 Elève(num : entier, nom : chaîne, adresse : chaîne, age :
entier de 18 à 35)
 Intention vs. Extension
 Schéma de relation : intention de la relation
 Table : extension
 Schéma d'une BD relationnelle : ensemble des schémas des
relations

32
 Modèle relationnel
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

33
 Modèle relationnel
Clé étrangère (2)
 Mises à jour et clés étrangères
 Insertion: la valeur des attributs doit exister dans la
relation référencée.
 Insertion de (4, ‘BD’, 15) dans Inscrit ?
 Suppression dans la relation référencée; les n-
uplets référençant doivent disparaître.
 Suppression de l’élève 2 dans Elève ?
 Les clés étrangères sont la traduction des
associations du modèle E/A

34
 Modèle relationnel

Clé étrangère
 Exemples
Élève(num, nom, adresse, age)

UV(code, nbh, coord)

Inscrit(numElève, codeUV, note)

Livre(côte, titre,numElève, datePrêt)

Chambre(no, prix, numElève)

35
 Modèle relationnel
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

36
 Modèle relationnel
Résumé des notions
Schéma
Relation1 Attribut1 Attribut2 Attributn
Intention
Schém NomRel Attribut
v1 w1 a
v1 w2 BD
Relation Attribut1
1
Tuple / n-uplet
Table

v3 Relation Attribut2
w2
1
vn
…
Clé
Relation2 Attributa Attributb
Clé
w1 étrangère
w2

BD Métabase
xi
wn 37
Langages associés au modèle relationnel
 Langages de Définition de Données (LDD) :
 Définition /mise à jour des schémas des relations
 Langages de manipulation de données (LMD) :
 Interrogation : recherche de données
 Mises à jour : insertion, suppression, modification

38
 Algèbre relationnelle

Les opérateurs de manipulation

 Tout résultat d'une opération est une relation; peut donc
être réutilisée en entrée d'un nouvel opérateur.
 Les opérateurs peuvent être classifiés en :
 opérateurs ensemblistes / opérateurs relationnels
 opérateurs de base / opérateurs dérivés
 opérateurs unaires / opérateurs binaires
 Unaires : sélection (restriction), projection,
 Binaires : union, intersection, différence, produit cartésien, jointure,
division

39
 Algèbre relationnelle

Restriction
 But  Inscrits en BD :
 Permet de "sélectionner" codeUV=‘BD’(Inscrit)
des tuples
 La restriction réduit la taille Resu NumElève CodeUV Note
de la relation verticalement
2 BD 10
 Contraintes
 Unaire 1 BD 20
 Spécifier une condition
 Notation
 Notation textuelle:  Majors (note > 15) de BD :
Tcond(R) codeUV=‘BD’ et note >15(Inscrit)
 Notation graphique:
R
Resu NumElève CodeUV Note

Cond. 1 BD 20

40
T
Projection Algèbre relationnelle

 But
 Permet de "sélectionner" des  Adresses des élèves :
attributs Adresse(Elève)
 La projection réduit la taille de la
relation horizontalement
Resu Adresse
 Contraintes
 Unaire
 Spécifier une liste d'attributs Marakech
Pas de
 Notation doublon Berrechid
 Notation textuelle: Tattributs(R)
 Code et nb heures des UV :
 Notation graphique:
R
code,nbh(UV)
Resu Code nbh
IO 45
attributs.
BD 15

T
41
 Algèbre relationnelle
Union  Nom des profs, des élèves
 But
Prof Nom Elève2 Nom
 Permet de fusionner 2 relations
Lahlou Berrada
 Contraintes
Benabou Mirouane
 Binaire
 Même schéma Mjahed Mjahed
 Notation
 Notation textuelle: T  R S 
Nom des personnes à l’INT : ProfEleve2
 Notation graphique: Resu Nom

R S Lahlou
Benabou
 Pas de Mjahed
doublon Berrada
T Miroune

42
Intersection Algèbre relationnelle

 But  Nom des profs, des élèves

 Permet d’obtenir l’ensemble des
tuples appartenant à deux Prof Nom Elève2 Nom
relations
Lahlou Berrada
 Contraintes
Benabou Mirouane
 Binaire
 Même schéma Mjahed Mjahed
 Notation
 Notation textuelle: T R S  Noms communs élèves-
 Notation graphique: profs : ProfElève2
R S
Resu Nom
 Mjahed

43
Différence
Algèbre relationnelle

 But
 Obtenir l’ensemble des tuples  Nom des profs, des élèves
d’une relation qui ne figurent
pas dans une autre
Prof Nom Elève2 Nom
 Contraintes
Lahlou Berrada
 Binaire
 Même schéma Benabou Mirouane
 Non commutatif Mjahed Mjahed
 Notation
 Notation textuelle: T  R -S
 Notation graphique:
 Noms des élèves qui ne
portent pas le nom d’un prof :
R S Eleve2-Prof
Résu Nom
- Berrada
Mirouane
T

44
 Algèbre relationnelle
Produit cartésien
 But
 Ensemble de tous les tuples obtenus par concaténation de chaque

tuple de R avec chaque tuple de S

 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) R S
 Notation
 Notation textuelle: T  R X S
X
 Notation graphique:

45
 Algèbre relationnelle
Produit cartésien (2)
Elève Num Nom Adresse Age UV Code Nbh Coord

1 Berrada Marakech 20 IO 45 Lahlou

2 Mirouan Berrechid 20 BD 15 Benabbou
e
3 Mjahed Marrakech 21
Elève X UV Num Nom Adresse Age Code Nbh Coord

1 Berrada Marrakech 20 IO 45 Lahlou

2 Mirouan Berrechid 20 IO 45 Lahlou
e
3 Mjahed Marrakech 21 IO 45 Lahlou
1 Berrada Marrakech 20 BD 15 Benabou
2 Mirouan Berrechid 20 BD 15 Benabou
e
3 Mjahed Marrakech 21 BD 15 Benabou
46
 Algèbre relationnelle
Jointure
 But
 Permet d’établir le lien sémantique entre les relations

 Contraintes
 Binaire

 Schéma du résultat:

 R(a1, a2, ...., an), S(b1, b2, ..., bp)

 T  R  S T(a1, a2, ...., an, b1, b2, ..., bp)
 Notation R S
 Notation textuelle: T  R  S condition
condition

 Notation graphique:
T

47
1er exemple de jointure
Elève Num Nom Adresse Age Elève.Num=Chambre.numElève

1 Berrada Marakech 20
2 Mirouane Berrechid 20
3 Mjahed Marakech 21 Chambre No Prix numElève
10 200 3
21 150 2
Elève Chambre Num Nom Adresse Age No Prix numElève
2 Mirouan Berrechid 20 21 150 2
e
3 Mjahed Marakec 21 10 200 3
h

 1 tuple de Chambre  1 tuple de résultat

 1 tuple de Elève  0 ou 1 tuple de résultat
 On a perdu Berrada !

48
2ème exemple de jointure
Inscrit NumElève CodeUV Note Inscrit.NumElève=Elève.Num

2 BD 10 Elève Num Nom Adresse Age

1 BD 20 1 Berrada Marakech 20
2 IO 17 2 Mirouane Berrachid 20
3 IO 18 3 Mjahed Marakech 21

EleveInscrit Num Nom Adresse Age NumElève CodeUV Note

1 Berrada Marakech 20 1 BD 20
2 Mirouane berechid 20 2 IO 17
2 Mirouane Berec hid 20 2 BD 10
3 Mjahed Marakech 21 3 IO 18

 1 tuple de Inscrit  1 tuple de résultat

 1 tuple de Elève  0 à n tuples de résultat
 On a dupliqué Mirouane !

49
 Algèbre relationnelle
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:
 R(a1, a2, ...., an, b1, b2, ..., bp), S(b1, b2, ..., bp)
 T  R  S, T(a1, a2, ...., an)
 Notation
 Notation textuelle: T = R  S R S
 Notation graphique:
 Dérivation 
 Projection + Produit cartésien + Différence.
 R  S  T1 - T2 avec:
T
 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)

50
 Algèbre relationnelle

Division (2)
 Exemple
 Quels sont les élèves inscrits à toutes les UVs ?

Inscrit NumElève CodeUV Note

2 BD 10

1 BD 20

2 IO 17

3 IO 18

51
 Algèbre relationnelle

Division (3)
 Exemple  RNumElève,CodeUV(Inscrit)
 Construire R : ensemble de R NumElève CodeUV
toutes les informations dont 2 BD
on a besoin = attributs
1 BD
NumElève et CodeUV de
Inscrit (R) 2 IO
 Construire S : ensemble 3 IO
correspondant à "tous les"
(UV) = codeUV (S)  SCode(UV)
 Résultat  R  S S CodeUV
 Vérification : BD
 Résultat X S  R
IO

 Résultat
Resu NumElève

2 52
Bilan : sémantique et notations des Algèbre relationnelle

opérateurs
Opérateur Sémantique Notation Notation
textuelle graphique

Restriction « Sélectionner » des tuples Tcond(R)

Cond.

Projection « Sélectionner » des attributs Tattributs(R)

attributs.

Union condition
Fusionner les extensions de 2 relations T  R S

Intersection Obtenir l’ensemble des tuples communs à deux T R S
relations 
Différence Tuples d’une relation qui ne figurent pas dans une T  R -S
autre -
Produit Concaténer chaque tuple de R avec chaque tuple de T  R XS
cartésien S X
Jointure Etablir le lien sémantique entre les relations T  R  S condition
condition

Division Répondre aux requêtes de type « tous les » T  R /S



53
 Algèbre relationnelle

Bilan : contraintes des opérateurs

Opérateur Unaire/ Schémas « Paramètres »
Binaire

Restriction Tcond(R) Unaire Schéma(T) = Schéma(R) Condition sur attributs de R

Projection Tattributs(R) Unaire Schéma(T)  Schéma(R) Liste d’attributs de R

Union T  R S Binaire Schéma(R)=Schéma(S)=Schéma(T

)

Intersection T R S Binaire Schéma(R)=Schéma(S)=Schéma(T

)

Différence T  R -S Binaire Schéma(R)=Schéma(S)=Schéma(T

)

Produit cartésien T  R XS Binaire Schéma(T)=Schéma(S)Schéma(

R)

Jointure T  R  S Binaire Schéma(T)=Schéma(S)Schéma( Condition de jointure sur

condition R) attributs de R et S

Division T  R S Binaire Schéma(R)=Schéma(S)

+Schéma(T)

54
Exemples de requêtes en algèbre Exemples

relationnelle
 Base de données exemple : les jus
Jus(num, nom, annee, degre) V
Recoltes(njus, nprod, quantite) R
Producteurs(num, nom, prenom, region) P
Buveurs(num, nom, prenom, ville) B
Commandes(ncde, date, nb, njus, qte) C
Livraisons(ncde, no_ordre, qteLivree) L

55
Modèle E/A de la BD des jus Exemples

sjus
Buveurs
num
num
cru
nom
degré
prenom
annee
ville
0,n
0,n
0,n

concerne
recoltes Passer
quantite

0,n 1,1 1,1

Producteurs Livraisons
Commandes
Donner_lieu (1,1) No_ordre
num ncde 0,n
nom qteLivrée
qté
prenom date
date
region

56
Composition des opérateurs Exemples

 Ecriture textuelle :
Temp   annee=2010 (Jus)
Resultat   num(Temp)
Jus
Resultat   num( annee=2010 (Jus))
annee=2010
 Arbre algébrique :
num

Résultat

57
Noms des producteurs de Citron Exemples

Mus   nom=‘Citron’ (V) Jus

projection
nom=‘Citron.
Recoltes
RecMus  Mus  R num=njus
Mus.num=R.njus

Producteurs
projection
nprod=num
ProdMus  RecMus  P
Rec.nprod=P.num

nom
Resultat   nom(ProdMus)
Résultat

58
Numéros des jus ne faisant l’objet d’aucune
Exemples

commande
NumJus   num(Jus)

JusCdes   njus(Commande)
Jus Commandes

Resultat  Numjus – jusCdes

num njus

Résultat

59
Optimisation Optimisation

 Noms et prénoms des buveurs habitant Paris

ayant commandé du Citron 2010 avant le 01
janvier 2011
 Plusieurs réponses possibles
 Optimiser = choisir la meilleure façon

60
Réponse 1 Optimisation

Buveurs Commandes Jus

cru=‘Citron et
ville=‘Paris’ date < 01/01/2011
annee=2010

num=nb

nvin=num

nom, prenom

Résultat

61
Réponse 2 Optimisation

Buveurs Commandes Vins

cru=‘Citron’ et
ville=‘Paris’ date < 01/01/2011
annee=2010

nvin=num

num=nb

nom, prenom

Résultat

62
Réponse 3 Optimisation

Buveurs Commandes Vins

num=nb

njus=num

ville=‘Paris’ et
date < 01/01/2000 et
cru=‘Citron’ et
annee =2010

nom, prenom

Résultat

63
Réponse 4 Optimisation

Buveurs Commandes Vins

cru=‘citron’ et
ville=‘Paris’ date < 01/01/2011
annee=2010

nb, nom, prenom njus,nb num

num=nb

njus, nom, prenom

njus=num

nom, prenom

Résultat

64