Introduction aux bases de données, modèle relationnel

Introduction aux bases de
données
Généralités sur les
bases de données
Université de Nice Sophia-Antipolis
Version 2.1 - 5/12/2000
Richard Grin
R. Grin
• Décrire les données qui seront stockées
• Manipuler ces données (ajouter, modifier,
supprimer des informations)
• Consulter les données et traiter les
informations obtenues (sélectionner, trier,
calculer, agréger,...)
• Définir des contraintes d'intégrité sur les
données (contraintes de domaines,
d'existence,... )
• Une base de données est un ensemble
structuré de données enregistrées dans un
ordinateur et accessibles de façon sélective
par plusieurs utilisateurs.
• Un logiciel qui permet d'interagir avec une
base de données s'appelle un système de
gestion de base de données (SGBD)
SGBD
3
• Définir des protections d'accès (mots de passe,
autorisations,...)
• Résoudre les problèmes d'accès multiples aux
données (blocages, interblocages)
• Prévoir des procédures de reprise en cas
d'incident (sauvegardes, journaux,...)
SGBD
R. Grin
SGBD
4
Indépendance par rapport aux
traitements
Fonctions d'un SGBD (2)
R. Grin
2
Fonctions d'un SGBD
Définitions
R. Grin
SGBD
5
• Pour faciliter la maintenance, un SGBD doit
favoriser l'indépendance des traitements
– par rapport à l'implantation physique des données
(codage, support d'enregistrement, ordre dans
lequel les données sont enregistrées,…)
– et même, si possible, par rapport à l'implantation
logique des données (existence d'index,
décomposition en "fichiers logiques",…)
R. Grin
SGBD
6
1
Sans base de données
Avec base de données
Applications
Fichiers
BP 2536
Base de données
Filtre (vues) Applications
Facturation
Facturation
BP 2536
BP 2536
BP 2536
R. Grin
Commercial
Commercial
Prospects
Prospects
Délais de mise à jour
Données contradictoires
SGBD
7
9
Avantages de l'utilisation des SGBD
• Pour les SGBD relationnels : langage non
proc édural simple ⇒ interrogation directe
possible par les utilisateurs et réponses rapides
à des questions non prévues par l'application
R. Grin
SGBD
8
• Centralisation des données ⇒ intégrité des
données
• Contrôle centralisé de l'accès aux données ⇒
sécurité accrue
• Instructions de traitement très puissantes ⇒
grande rapidité de développement
• Indépendance vis-à-vis de la structure physique
et logique des données ⇒ maintenance facilitée
• Une information n'est stockée qu'une seule
fois
• Une seule base pour toutes les applications
• …mais chaque application ne voit que ce
qu'elle doit voir (contrôle par les filtres ou
vues)
SGBD
SGBD
Avantages de l'utilisation des SGBD
Avec base de données
R. Grin
R. Grin
11
R. Grin
SGBD
10
Niveau de description d’une base
• Externe : modélisation des différentes
visions de la base par les utilisateurs
• Conceptuel : structure globale des données
de la base qui intègre les différentes visions
externes, sans se soucier des contraintes (de
performance ou autres) liées à l’implantation
• Interne : manière dont la base est implantée
sur les ordinateurs
R. Grin
SGBD
12
2
Avantages de la séparation des 3
niveaux
• On peut limiter les modifications liées aux
changements de matériel, de système
d’exploitation ou des logiciels utilisés
• La vision de chaque utilisateur est
indépendante des visions des autres
utilisateurs et n’est pas modifiée par les
modifications du schéma conceptuel qui ne
le concernent pas
R. Grin
SGBD
13
Types d'utilisateurs
• L'administrateur de la base est chargé
– du contrôle de la base de données, en
particulier, permettre l'accès aux données aux
applications ou individus qui y ont droit
– de conserver de bonnes performances d'accès à
ces données
– des sauvegardes et des procédures de reprise
après les pannes
R. Grin
• SGBD hiérarchique :
• Le programmeur
– les données sont représentées dans la base sous
la forme d'un arbre
– la structure d'arbre utilise des pointeurs et
détermine le chemin d'accès aux données
– écrit des applications qui utilisent la base de
données
– il crée les tables et les structures associées
(vues, index,...) utilisées par ses applications
• SGBD réseau :
• L'utilisateur final
– les données sont représentées dans la base sous
la forme d'un graphe quelconque
– la structure de graphe utilise des pointeurs et
détermine le chemin d'accès aux données
– n'a accès qu'aux données qui lui sont utiles
• par l'intermédiaire d'applications
• en interrogeant directement les tables ou vues sur
lesquelles l'administrateur lui a accordé des droits
SGBD
15
R. Grin
• Pour les SGBD hiérarchiques et réseaux, les
programmes
– ne sont pas indépendants de la structure logique
de la base
– doivent indiquer le chemin d'accès aux données
– utilisent un langage complexe pour travailler
avec les données
SGBD
SGBD
16
SGBD relationnel
Types de SGBD (2)
R. Grin
14
Types de SGBD
Types d'utilisateurs (2)
R. Grin
SGBD
17
• La théorie des SGBDR est fondée sur la théorie
mathématique des relations
• Représentation très simple des données sous forme
de tables constituées de lignes et de colonnes
• Plus de pointeurs qui figent la structure de la base
• Langage non procédural, puissant et simple
d'emploi
• Langage SQL est un standard parmi ces langages
• Dominent le marché des SGBD
R. Grin
SGBD
18
3
SGBD objet
SGBD objet
• Les SGBDOO enregistrent les données sous
forme d'objets ; les données sont enregistrées
avec les procédures et les fonctions qui
permettent de les manipuler
• Ils supportent la notion d'héritage entre
classes d'objets
• Très simple de rendre les objets persistants
• Meilleures performances pour la gestion
d'objets complexes (les pointeurs remplacent les
• A priori, beaucoup d'avantages sur le relationnel
• Mais,
jointures pour les structures hiérarchiques)
R. Grin
SGBD
19
– manque de normalisation et de standard
– inertie de l'existant (presque toutes les bases actuelles
sont de type relationnel)
• Vont détrôner les SGBD relationnels ?
• Ceux-ci tentent de résister avec les SGBD
relationnels-objet (introduction des types complexes
et des pointeurs mais au détriment de la "pureté"
relationnelle)
R. Grin
SGBD
20
Niveaux d'abstraction
• Il est nécessaire de décrire l'application selon
différents niveaux d'abstraction pour faciliter
– le développement des applications (ne pas être noyé dans
Modèles de données
par les détails et contraintes techniques au début de son étude)
– l'adaptation de l'application aux futures modifications
de l'environnement technique et logiciel
• Pour les données, on distingue généralement les 3
niveaux conceptuel, logique et physique
R. Grin
SGBD
21
R. Grin
SGBD
22
Niveau conceptuel
Niveau logique
• Il décrit les données sous une forme
indépendante du matériel et du SGBD
• Dans ce cours d'introduction, nous décrirons ce
niveau conceptuel par un diagramme de classes
UML (simplification qui peut suffire pour les
cas simples)
• Il adapte le niveau conceptuel au type de SGBD
utilisé
• Pour les SGBD relationnels, il traduit le niveau
conceptuel sous formes de relations
• Pour les SGBD objets, le modèle conceptuel est
traduit sous forme de classes (direct depuis un
schéma UML)
R. Grin
SGBD
23
R. Grin
SGBD
24
4
Niveau logique pour les SGBD
relationnels
Niveau physique
• Les relations sont normalisées pour éviter les
problèmes liés à une mauvaise répartition entre
les relations
• Des besoins d'optimisation peuvent conduire à
dénormaliser certaines relations
R. Grin
SGBD
25
Département
numéro : int
nom : String
lieu : String
Une association
+contient
Agrégation
+subordonné
0..*
appartient
0..*
Employe
matricule : int
nom : String
0..1 poste : String
dateEmbauche : date
salaire : double
+supérieur
hierarchie
Rôle pour
l’association
R. Grin
SGBD
26
Une classe
ajouterEmployé(Employe) : void
1
• Il décrit l'implantation du niveau logique à
l'aide du matériel et du SGBD choisi
• On précise les emplacements des données
sur les différents supports
• On peut optimiser les accès aux données en
utilisant les possibilités particulières
offertes par le SGBD (clusters par exemple
avec Oracle)
Participation
fonctionP: String
0..*
0..*
Une classeassociation
Multiplicité
Modèle relationnel
Projet
codeP: String
nomP : String
+participe
ajouterEmploye(Employe)
getSuperieur ()
setSuperieur(Employe)
getMatricule()
Héritage
Commercial
commission : double
R. Grin
setCommission (double)
Directeur
Administratif
SGBD
27
R. Grin
SGBD
28
Relation
Relation mathématique
• La notion de relation traduit le fait que les
éléments appartenant à des ensembles
(distincts ou non) peuvent être en relation
Robert
×
× Sylvie
Alain ×
× Nathalie
Bernard ×
Julien ×
Hommes
R. Grin
• Une relation entre n ensembles Di est un
sous-ensemble du produit cartésien des n
ensembles D1 × D2 ...× Dn
• C'est donc un ensemble de n-uplets
(a1, a2,…, an) où ai ∈ Di
• n est appelé le degré de la relation
Sous-ensemble de
Homme × Femme :
{ (Robert, Sylvie),
(Alain, Nathalie),
(Alain, Carole)
}
SGBD
× Carole
Femmes
29
R. Grin
SGBD
30
5
Relations de degré > 2
Représentation des données
• Si de nombreux ensembles sont en relation, il est
plus difficile de dessiner la relation
• Mais la représentation sous forme mathématique
ne pose pas de problème particulier
• Par exemple, une relation père-mère-enfants
pourrait être décrite par un ensemble de 3-uples :
• Dans la théorie des bases de données
relationnelles
toutes les données sont représentées sous
forme de relations
• Par exemple, le t-uple
R. Grin
SGBD
(125, Dupond, 15000, 10)
indique que l'employé de matricule 125
s'appelle Dupond, gagne 15.000 F par mois
et travaille dans le département numéro 10
{ (Robert, Sylvie, Toto),
(Alain, Nathalie, Bibi),
(Alain, Nathalie, Titi) }
31
R. Grin
SGBD
32
Domaines et attributs
• Les ensembles Di d'une relation sont appelés les
domaines de la relation
• Certains de ces domaines peuvent être identiques
mais, dans une relation, l'ordre est significatif et
chaque domaine possède un nom d'attribut Ai
qui est lié à son rôle dans la relation
• La relation est alors notée R(A1, A2,…, An), où
R est le nom de la relation
• Par exemple, Employe(matricule, nom,
Schéma relationnel
• Un schéma relationnel est
– un ensemble de définitions de relations liées à
un même schéma conceptuel
– les contraintes d'intégrité associées à ces
relations (clés, contraintes de domaine,
d'existence ou de référence,…)
salaire, numDept)
R. Grin
SGBD
33
R. Grin
SGBD
34
Exemples de clés
Clés d'une relation
• EMPLOYE(matricule, nom, salaire) a
• Clé candidate : sous-ensemble minimal
d'attributs qui permet d'identifier chacun des
t-uples de la relation
• Clé primaire : une des clés candidates,
choisie comme identifiant privilégié (par le
concepteur de la base)
1 clé candidate : matricule
• Si on est certain que 2 employés n'ont
jamais le même nom, nom est une 2ème clé
candidate
• EMPLOYES(matricule, nom, prénom,
salaire) a 2 clés candidates :
– matricule (que l'on choisira certainement
comme clé primaire) En supposant que 2 employés
n'ont jamais les mêmes
– (nom, prénom)
nom et prénom
R. Grin
SGBD
35
R. Grin
SGBD
36
6
Table
• Dans les SGBD relationnels les relations sont
représentées simplement sous forme de tables
• Les lignes correspondent aux t-uples et les
colonnes aux attributs
Matricule Nom
Salaire
NumDept
525
12
Dupond 150000 10
Durand 120000 10
132
Bernard 8500
R. Grin
20
SGBD
37
Étapes du passage d’un
diagramme de classes à un
schéma relationnel
SGBD
R. Grin
SGBD
38
Le problème de l’identificateur
• Tout objet est identifiable. Cette identification
est automatique dans les langages objet
• Dans les schémas relationnels ou dans SQL on
peut être amené à ajouter un identificateur
• On peut ainsi ajouter en attribut un nombre sans
signification particulière, incrémenté
automatiquement à chaque création d’objet
• Parfois un ou plusieurs attributs assurent cette
identification (n° SS, ISBN)
• Traduction des classes en relations
• Traduction des associations en relations
• Traduction de l’héritage
R. Grin
Passage d'un diagramme de
classes à un schéma relationnel
39
R. Grin
SGBD
40
Remarque sur les identificateurs
• Il est rarement bon d’utiliser des identificateurs
significatifs, par exemple, un nom
• En effet, ces identificateurs vont être utilisés
dans d’autres relations pour désigner les t-uples
qu'ils identifient
• Un identificateur ne devrait donc jamais être
modifié
• Une faute de frappe dans un identificateur
significatif comme un nom peut engendrer des
traitements lourds sur une base de données
R. Grin
SGBD
41
Traduction d’une classe
• Une classe est traduite en une relation
• On peut être amené à ajouter un identificateur,
clé primaire de la relation
• Exemple : la classe Département est traduite
par la relation
DÉPARTEMENT(numéro, nom, lieu)
R. Grin
SGBD
42
7
Traduction d’une association binaire
Exemple de traduction
d’une classe
• Lorsque les nombres maximum des 2
multiplicités sont supérieurs à 1
(associations M:N), on doit créer une
relation pour traduire l’association
• La clé primaire de cette relation est formée
des 2 clés des relations qui traduisent les
classes qui interviennent dans l’association
• DÉPARTEMENT(numéro, nom, lieu)
• En SQL :
create table departement (
numero smallint primary key,
nom varchar(15),
lieu varchar(15))
R. Grin
SGBD
43
R. Grin
Exemple de traduction d’une
association binaire M:N
create table participation (
matr integer references emp,
codeP varchar(2) references projet,
primary key(matr, dept))
SGBD
45
Traduction d’une association binaire
M:N avec classe-association
create table participation (
matr integer references emp,
codeP varchar(2) references projet,
fonctionP varchar(15),
primary key(matr, dept))
SGBD
• Si l'association est représentée par une classe
association (cf. classe Participation de
l'association participe), on ajoute les
attributs de classe dans la nouvelle relation
R. Grin
SGBD
46
Interprétation des relations qui
traduisent une association
• Pour savoir qui participe au projet
"QUALITÉ"
• PARTICIPATION(matr, codeP, fonctionP)
• En SQL :
R. Grin
44
Classes association
• PARTICIPATION(matr, codeP)
• En SQL :
R. Grin
SGBD
47
1. on récupère le code de projet QUALITÉ dans
la relation PROJET
2. on note tous les matricules qui sont associés à
ce code dans la relation PARTICIPATION
3. on cherche les noms des employés qui ont ce
matricule dans la relation EMPLOYÉ
R. Grin
SGBD
48
8
Traduction d’une association
binaire dont une multiplicité
maximum est 1 (1:N ou 1:1)
Exemple de traduction d’une
association binaire 1:N
• On peut traduire par une nouvelle relation comme
pour une association M:N
• On peut aussi ajouter dans la relation qui traduit
la classe placée du côté opposé à la multiplicité 1,
la clé de l’autre classe
• EMP(matr,…, dept)
• En SQL :
create table emp (
matr integer primary key,
…
dept smallint references dept)
• La 1ère solution est plus souple mais plus coûteuse
(jointures)
R. Grin
SGBD
49
• Si l'association est représentée par une classe
association, on peut ajouter les attributs de
classe dans la relation qui reçoit la clé de
l'autre classe, ou créer une nouvelle relation
• Mais si les attributs de la classe association
sont nombreux, il est souvent préférable de
traduire l'association par une relation à part
SGBD
51
• Le sous-ensemble peut être stricte si une dépendance
fonctionnelle existe entre ces clés
R. Grin
SGBD
52
Traduction de l’héritage
• RESERVATION(nVol, nSiège, codePassager, …)
• En SQL :
create table reservation(
nvol varchar(10) references vol,
nsiege integer,
codePassager varchar(10) references passager ,
primary key(nVol, nSiege, codePassager),
…)
SGBD
50
• On doit créer une relation pour traduire
l’association
• La clé de cette relation est formée d'un sous ensemble des clés des relations qui traduisent
les classes qui interviennent dans l’association
Exemple de traduction d’une
association de degré > 2
R. Grin
SGBD
Traduction d’une association de
degré > 2
Classes association
R. Grin
R. Grin
53
• La classe fille et sa classe mère sont traduites
par 2 relations
• Un objet de la classe fille
– a ses attributs répartis dans les 2 relations
– son identité est préservée en donnant un même
identifiant au t-uple qui correspond à l’objet dans
les 2 relations
R. Grin
SGBD
54
9
Exemple de traduction de
l’héritage
Héritage multiple
• COMMERCIAL(matr, commission)
• En SQL :
• Pour traduire l’héritage multiple, on met
comme identifiant dans la classe fille
l’ensemble des identifiants des classes mères
create table emp (
matr integer primary key,
nom VARCHAR(30),
. . .)
create table commercial (
matr integer primary key references emp ,
commission numeric (8,2))
R. Grin
SGBD
55
Variantes pour la traduction de
l’héritage
• Pour réduire le nombre de relations et améliorer
les performances, on peut
– soit tout réunir dans la relation de la classe mère, en
ajoutant un attribut pour le sous-type (si les attributs
des classes filles sont peu nombreux) ; il y aura alors
des attributs non renseignés
– soit copier les attributs de la classe mère dans
chacune des relations qui traduisent les classes filles
(si les attributs de la classe mère sont peu nombreux)
R. Grin
SGBD
57
R. Grin
SGBD
56
Schéma relationnel utilisé pour
ce cours
Employé(matr, nomE, poste, dateEmb, sup,
salaire, commission, dept)
Dept(dept, nomD, lieu)
Matricule
du chef
Projet(codeP, nomP)
Participation(matr, codeP , fonctionP)
R. Grin
SGBD
58
Calcul des prédicats
• La logique du 1er ordre, ou calcul des prédicats,
est la théorie mathématique qui étudie les
formules logiques formelles (sans signification
particulière)
• On se limite à des formules logiques construites
avec un ensemble de prédicats, les opérateurs
"et", "ou", "négation", "⇒ ", les opérateurs ∃ et
∀ et des constantes et des variables
• Un prédicat a un nombre fixe d'arguments et
peut être vrai ou faux
Langages d'interrogation
relationnels
R. Grin
SGBD
59
R. Grin
SGBD
60
10
Sémantique
• On peut appliquer le calcul des prédicat à un
domaine de discours particulier en définissant
– des valeurs concrètes pour les constantes
– des prédicats
• A chaque formule logique correspond alors
l'ensemble des données du domaine qui
vérifient la formule (si on donne ces valeurs
aux variables, la formule est vraie)
R. Grin
SGBD
61
Calcul relationnel des t-uples
{E.nomE / ∃ P PARTICIPATION(P) ∧
(P.matr = E.matr) ∧
EMP(E)}
(les variables sont E et P)
SGBD
• Ces langages permettent de retrouver des
informations enregistrées dans la base sous
forme de relations
• Ils permettent d'exprimer des formules
logiques associées à ces données
• Selon les domaines dans lesquels les
constantes prennent leur valeur, on a
plusieurs langages d'interrogation
R. Grin
SGBD
62
Calcul relationnel des domaines
• Il est fondé sur le calcul des prédicats du 1er
ordre, avec les variables qui prennent leur
valeur dans l’ensemble des t-uples
Prédicat
(P t-uple de
• Exemple (noms des employés qui PARTICIPATION)
participent à un projet) :
R. Grin
Langages d'interrogation fondés sur
le calcul des prédicats du 1er ordre
• Il est fondé sur le calcul des prédicats du 1er
ordre, avec les variables qui prennent leur
valeur dans l’ensemble des domaines
• Exemple (noms des employés qui
participent à un projet) :
{ y / ∃ x EMP(matr:x, nomE:y) ∧
PARTICIPATION(matr:x) }
(les variables sont x et y)
Prédicat
63
R. Grin
SGBD
(valeurs de x
dans domaine
de matr
Algèbre relationnelle
Opérateurs relationnels
• Le principe est différent pour ce langage
d'interrogation qui a inspiré le langage SQL
• On se donne des opérateurs que l'on
applique aux relations de la base, pour
obtenir les données que l'on cherche
• On a 2 types d'opérateurs : relationnels et
ensemblistes
• Soit une relation R[A1,…, An]
• Projection sur les attributs Aj,…, Ak, notée
R. Grin
SGBD
65
64
R[Aj,…, Ak]
• Sélection des t-uples qui vérifient une
condition c, notée R / c
• Jointure (équi-jointure) de 2 relations R et S
sur 2 attributs C et D, notée R J{C=D} S
• Division de 2 relations, notée R ÷ B S
R. Grin
SGBD
66
11
Equi-jointure
Opérateurs relationnels de base
• C'est un opérateur fondamental de la théorie des
bases de données relationnelles
• Il permet de créer une relation qui comprend
des données venant de 2 relations
• Soient R[A, B] et S[C, D], avec C et D 2
ensembles d'attributs de types compatibles, la
jointure R J{B=C} S est la relation dont les tuples sont obtenus par concaténation des t-uples
de R et de S qui ont la même valeur pour les
attributs B et C
• Exemple de projection :
Employé[matricule, nom, salaire]
• Exemple de sélection :
Employé / salaire > 12000
R. Grin
SGBD
67
R. Grin
68
Exemple de jointure naturelle
Exemple d'équi-jointure
Employé
SGBD
Employé
Département
Département
matricule nom
dept
dept nom
lieu
matricule nom
dept
dept nom
lieu
1050
Dupond
10
10
Finances
Paris
1050
Dupond
10
10
Finances
Paris
832
Durand
20
20
Ventes
Nice
832
Durand
20
20
Ventes
Nice
900
Duval
10
900
Duval
10
Employé J{dept=dept} Département
Notation simplifiée :
Employé J{dept} Département
matricul nom
dept dept nom
lieu
e1050
Dupond 10
10 Finances Paris
832
Durand 20
20
Ventes
900
Duval
10
Finances Paris
R. Grin
10
Nice
quand le nom
des 2 colonnes
est le même
SGBD
69
Employé J{dept=dept} Département
La colonne de
jointure n'est
pas répétée
matricul nom
dept nom
lieu
e1050
Dupond 10
Finances Paris
832
Durand 20
Ventes
900
Duval
Finances Paris
10
R. Grin
Nice
SGBD
70
Division
Jointure en général
• On peut remplacer = par un opérateur de
comparaison >, <, ≥, ≤, ≠
• La jointure est alors notée, par exemple,
R J{C < D} S
• Exemple :
Employé J{salaire < salaire} Employé
R. Grin
SGBD
S
R ÷B S
A
B
C
A
x
1
1
x
y
2
3
z
x
1
3
R
• R ÷ B S = les A qui sont associés à tous les C
= {a ∈ R[A] / ∀c ∈ S, (a, c) ∈ R}
= {a ∈ R[A] / ∃/ c ∈ S, (a, c) ∈
/ R}
• "Division", car c'est le plus grand sous-ensemble
D de R[A] tel que D × S est inclus dans R
71
R. Grin
SGBD
72
12
Quand utilise-t-on la division ?
Opérateurs ensemblistes
• La division fournit la réponse au type de question
suivante : quels sont les "A" qui sont associés à
tous les C ?
• Exemple : quels sont les matricules des employés
qui participent à tous les projets :
R1 = Participation[matr, codeP] ÷ matr Projet[matr]
R. Grin
SGBD
73
• Pour 2 relations qui ont des attributs de
types compatibles :
– Réunion : R1 ∪ R2
– Intersection : R1 ∩ R2
– Différence : R1 - R2
• Pour 2 relations quelconques, produit
cartésien : R1 × R2
R. Grin
SGBD
74
Equivalence des langages
• On peut démontrer que l'algèbre
relationnelle, les calculs relationnels des tuples et des domaines sont équivalents : ils
permettent de définir les mêmes ensembles
de données
R. Grin
SGBD
75
But de la normalisation
SGBD
R. Grin
SGBD
76
Dépendance fonctionnelle (DF)
• Une mauvaise répartition des données entre
les relations peut occasionner de graves
problèmes lors de l'évolution de la base
• La normalisation des relations permet
d'éviter ces problèmes, essentiellement en
évitant les redondances
• Les problèmes viennent en fait des
dépendances fonctionnelles internes aux
relations
R. Grin
Normalisation d'un schéma
relationnel
77
• Soient X et Y, 2 attributs (ou groupe d'attributs)
d'une même relation
• Il y a DF entre X et Y (on dit aussi que Y
dépend de X)
• On note X → Y, si la valeur de X détermine la
valeur de Y
R. Grin
SGBD
78
13
Exemples de DF
Propriétés des DF
• Il y a DF d'une clé candidate vers tous les autres
attributs d'une relation
• Adresse(nom, ville, codePostal,
département); on a codePostal → ville
(mais pas ville → département)
• Etudiant(nom, prénom, moyenne, âge,
enseignant)
(nom, prénom) → moyenne
(nom, prénom) → âge
R. Grin
SGBD
79
Réflexivité : Y⊆ X ⇒ X → Y
Augmentation : X → Y ⇒ XZ → YZ
Transitivité : X → Y et Y → Z ⇒ X → Z
Union : X → Y et X → Z ⇒ X → YZ
Pseudo-transitivité : X → Y et WY → Z ⇒
WX → Z
• Décomposition : X → Y et Z ⊆ Y ⇒ X → Z
•
•
•
•
•
R. Grin
R. Grin
SGBD
81
• En pratique, normaliser un schéma relationnel
c'est remplacer chaque relation du schéma par
des relations qui sont dans la forme normale
voulue
• Le schéma est équivalent si la jointure naturelle
sur les attributs communs des relations
obtenues par éclatement redonne la relation de
départ
R. Grin
• Normaliser une relation consiste à extraire les
DF internes qui posent des problèmes, en les
transférant dans de nouvelles relations
SGBD
SGBD
82
Théorème important
Pratique de la normalisation
R. Grin
80
Pratique de la normalisation
Normaliser un schéma relationnel
• C'est remplacer un schéma relationnel par un
autre schéma "équivalent" (représentant les
mêmes données) dont toutes les relations sont
dans une certaine forme normale
SGBD
83
• Cas simple pour lequel on peut éclater une
relation en 2 relations, en étant certain de
retrouver la relation de départ par jointure
sur les attributs communs
• Théorème de décomposition sans perte de
données : Soit une relation R(A, B, C) où A,
B et C sont des ensembles d'attributs
disjoints, avec B → C, alors
R(A, B, C) = R[A, B] J{B} R[B, C]
(on peut dire que l'on a "extrait" la DF de R)
R. Grin
SGBD
84
14
Exemple d'éclatement sans perte
de données
• Employé(matr, nomE, dept, nomD)
• Peut s'éclater en :
Employé(matr, nomE, dept)
Département(dept, nomD)
R. Grin
SGBD
La source de la
DF reste dans la
relation de
départ en clé
étrangère
85
Degrés de normalisation
• Il existe plusieurs degrés de normalisation :
de la 1ère forme normale à la 5ème
• Plus le degré est grand,
– moins on risquera de rencontrer des anomalies
lors des mises à jour des données
– plus les conditions à remplir sont strictes
R. Grin
1ère forme normale
SGBD
87
Problèmes de mise à jour d'une
relation pas en 2ème forme normale
• Pour modifier le nom d'un employé, on doit
le modifier dans toutes les lignes des projets
auxquels participe l'employé
• On ne peut ajouter un employé qui ne
participe à aucun projet
• On perd toute information sur un employé
qui ne participe plus à aucun projet
R. Grin
SGBD
86
2ème forme normale
• Toutes les relations sont en 1 FN : elles
n'ont pas d'attributs multivalués multivalué
• La pseudo-relation
Livre(codeISBN, titre, auteurs)
peut être décomposée en 2 vraies relations :
Livre(codeISBN, titre)
Auteurs (codeISBN, auteur)
R. Grin
SGBD
89
• Une relation est en 2 FN
– si elle est en 1 FN
– si chaque attribut qui ne fait partie d'aucune clé
candidate ne dépend pas d'une partie stricte
d'une clé candidate
• Employé(matr, codeP, nomE, fonctionP)
n'est pas en 2 FN
R. Grin
SGBD
88
Normalisation en 2ème forme
• On "extrait" la DF (voir théorème de non
perte de données) :
Employé(matr, nomE)
Participation(matr, codeP, fonctionP)
R. Grin
SGBD
90
15
3ème forme normale
Forme normale de Boyce-Codd
• Une relation est en 3 FN si tout attribut qui
ne fait pas partie d'une clé candidate ne peut
dépendre que d'une clé candidate
• Employé(matr, nomE, dept, nomD)
n'est pas en 3 FN
• On obtient un schéma en 3 FN par
extraction de la DF :
Employé(matr, nomE, dept)
Département(dept, nomD)
R. Grin
SGBD
91
• Une relation est en FNBC si les seules
sources de DF sont les clés candidates Pourquoi ?
• Une FNBC est toujours en 3 FN mais
l'inverse n'est pas vrai car pour la 3FN on
n'impose rien pour les DF dont le but est un
attribut clé
R. Grin
Exemple de relation pas en FNBC
• Introduisons une nouvelle règle de gestion :
2 personnes d'un même département ne
peuvent participer à un même projet
• Cette règle induit la DF suivante :
(dept, codeP) → matr
• Soit la relation
Participation2(dept, codeP, matr, fonctionP)
• Elle est en 3 FN mais pas en FNBC
R. Grin
SGBD
93
Théorème de décomposition en 3 FN
• On peut toujours normaliser en 3 FN, sans
perte de données ni perte de dépendances
• On peut toujours normaliser en FNBC sans
perte de données, mais on peut avoir des
pertes de dépendances
R. Grin
SGBD
95
SGBD
92
Normalisation en FNBC
• Si on extrait la DF, on obtient :
Participation(matr, codeP , fonctionP)
Emp(matr, dept)
• On obtient bien un schéma en FNBC mais
on perd la DF (dept, codeP) → matr
• Il faut choisir entre
– avoir un schéma en 3 FN, avec toutes les DF
– avoir un schéma en FNBC, avec perte de DF
R. Grin
SGBD
94
Projection des DF d'une relation
• Si R est décomposée en 2 relations R1 et
R2, la projection de l'ensemble des DF de R
sur R1 est l'ensemble des DF de R qui ont
leurs attributs de départ et d'arrivée dans R1
• On dit que la décomposition de R en R1 et
R2 est sans perte de dépendance si on peut
retrouver (par transitivité) toutes les DF de
R à partir des projections sur R1 et R2
R. Grin
SGBD
96
16
Conséquence d'une perte de
dépendance
• On est obligé de faire des jointures pour vérifier
les règles de gestion liées aux DF perdues
• Exemple : si on décompose Participation2 en
Participation et Emp, à chaque nouvelle
participation à un projet, il faut vérifier par
programme qu'il n'y a pas déjà un employé du
même département dans le projet (dans
Utilisation d'un
SGBD relationnel
Participation2, la vérification est faite automatiquement
par unicité de la clé primaire)
R. Grin
SGBD
97
R. Grin
Sécurité Contrôle de l'accès à la base
– un nom de login
– un mot de passe
• Chaque utilisateur a des privilèges d'accès à
la base : droit ou non
– de créer des tables ou des vues
– de lire ou de modifier des tables ou des vues
–…
SGBD
98
Sécurité Propriétaire des données
• Le contrôle de l'accès à la base est effectué
en associant à chaque utilisateur
R. Grin
SGBD
99
• Les données d'une table appartiennent à
celui qui l'a créée
• Le propriétaire d'une table peut donner à
d'autres le droit de travailler avec sa table
• Les vues permettent aussi de restreindre
l'accès aux données
R. Grin
SGBD
100
Modules d'utilisation des SGBD
• Nombreux produits autour des SGBD :
– interface pour interrogation directe de la base
– générateur de formulaires pour la saisie et
l'interrogation de la base
– générateur de menus et d'états imprimés
– générateur d'applications à partir d'études de
conception
– langages dit de 4ème génération
– interfaces avec les langages de 3ème génération
– interfaces avec le Web et les logiciels de bureautique
R. Grin
SGBD
101
Implantation d'un
SGBD relationnel
R. Grin
SGBD
102
17
Fichiers de la base
Images "avant" et "après"
• Les données de la base sont enregistrées
dans de très gros fichiers du système
d'exploitation
• Le SGBD gère lui-même ses données ; il
enregistre les tables, vues, index,… dans ces
gros fichiers
• Le SGBD peut même se passer
complètement du système d'exploitation
hôte pour l'enregistrement des données
(fichiers "raw", hors système de fichiers)
R. Grin
SGBD
103
• Images "avant" : fichiers système, ou
emplacement spécial de la base, contenant les
informations nécessaires pour remettre la base
dans un état antérieur à une modification (chez
Oracle, segments de rollback écrits dans la base)
• Images "après" : fichiers système contenant les
informations nécessaires pour refaire une
modification à partir d'un état antérieur de la
base (fichiers "redo log" chez Oracle)
R. Grin
SGBD
104
Archivage des images "après"
• La place disque réservée aux images "avant" et
"après" est limitée ; elle est recyclée pour
enregistrer les dernières images
• Il peut être intéressant d'archiver sur des
bandes magnétiques (ou autre) les images
"après" pour les réutiliser en cas de panne
• On peut ainsi redérouler toutes les dernières
actions effectuées par les utilisateurs depuis la
dernière sauvegarde
R. Grin
SGBD
105
• Les différents SGBD peuvent avoir des
implantations très différentes sur certains
points, en particulier sur la façon de traiter
les accès concurrents
• C'est l'implantation d'Oracle (versions 7 et
8i) qui est décrite ici
R. Grin
Processus clients et serveurs
– les interfaces du SGBD avec l'utilisateur ou avec
les applications sont clientes de la partie serveur du
SGBD : elles lui envoient des requêtes et le serveur
renvoie les données résultats
– les parties clientes et serveur du SGBD peuvent
être sur des machines différentes ; elles utilisent un
protocole réseau (le plus souvent propriétaire) pour
communiquer
SGBD
106
Écriture dans la base
• Les SGBD s'appuient sur le mode clientserveur :
R. Grin
SGBD
107
• En attendant leur écriture dans la base, les
données restent dans les buffers en mémoire
centrale
• Écriture asynchrone des données dans la base :
– pour des raisons de performances ; même un commit
peut ne pas provoquer l'écriture dans la base
– si les buffers sont pleins, des données non validées
peuvent être enregistrées dans la base
R. Grin
SGBD
108
18
Oracle versions 7 et 8
Segments de rollback
• L'écriture est effectuée par un seul processus
(en version monoprocesseur)
• Quand une modification est effectuée par une
transaction, les données de la base sont
modifiées tout de suite (dans les buffers ou les
tables de la base), sans attendre un commit
R. Grin
SGBD
109
• Dans Oracle (versions 7 et 8), les images
"avant" sont enregistrées dans la base dans des
segments de rollback
• Ces segments sont utilisés
– pour annuler les transactions
– pour cacher aux autres transactions les données modifiées
par les transactions non validées
– pour que les transactions "read only" ne voient pas les
modifications effectuées par les autres transactions
R. Grin
SGBD
110
Fichiers redo log
Oracle version 6
• Avec Oracle 7 et 8, les images "après" sont
enregistrées dans des fichiers redo log,
• Avec Oracle version 6, les modifications
effectuées par les utilisateurs n'étaient
enregistrées dans les tables de la base
qu'après un commit
• En attendant le commit, les modifications
effectuées par un utilisateur étaient
enregistrées à part dans la base
– avant même que les données ne soient vraiment
modifiées dans les tables de la base,
– et au moins à chaque validation de transaction (à
partir des buffers redo log) ; c'est cet
enregistrement dans les fichiers redo log qui "fait
foi" pour savoir si une transaction a été validée
• L'écriture est séquentielle, donc rapide
R. Grin
SGBD
111
R. Grin
SGBD
Client 1
Archivage des fichiers redo log
• L'écriture dans les fichiers redo log est
circulaire, en écrasant les premières données
par les dernières quand les fichiers sont pleins
• On peut demander à Oracle d'archiver
automatiquement les fichiers redo log avant
qu'ils ne soient pleins (pour les utiliser en cas
de panne)
SGBD
SGBD
113
Fichiers
Redo Log
R. Grin
Client 3
Serveur 2
Buffers de la base
LGWR
Disque
R. Grin
Client 2
Serveur 1
Buffer
Redo Log
112
DBWR
Fichiers de la base
SGBD
114
19
Reprise après panne quand les
fichiers sont corrects
Types de pannes
• Automatiquement, quand il redémarre, le
SGBD
• logicielles
• matérielles
• dues aux réseaux
– termine les transactions validées qui n'ont pu
être enregistrées complètement dans la base
– annule les transactions qui n'ont pas été
validées (rollback ou panne avant le commit)
• Les fichiers de la base peuvent avoir été
endommagés ou pas
R. Grin
SGBD
• Pour cela, le SGBD utilise les images
"après" et "avant"
115
R. Grin
Reprise après panne quand les
fichiers sont endommagés
– si les images "après" de la base ont été archivées,
on redéroule les actions des images "après"
enregistrées depuis cette dernière sauvegarde
– sinon, il faut relancer à la main toutes les
transactions perdues (si on peut !)
SGBD
116
Optimiseur de requêtes
• L'administrateur doit commencer par recharger
la dernière sauvegarde complète de la base
• Ensuite 2 cas :
R. Grin
SGBD
117
• SQL est un langage non procédural : on ne
décrit pas comment obtenir le résultat
• L'optimiseur du SGBD va concevoir un
plan pour aller rechercher les données de la
manière la plus efficace
• Il doit comparer tous les plans possibles, ou
une partie des plans sélectionnée par
heuristique
R. Grin
SGBD
118
Optimisation
• L'optimiseur prend en compte
– des considérations logiques comme de
commencer par réduire la taille des tables (faire
d'abord des sélections ou projections) avant de
faire des jointures
– l'implantation physique des données comme
l'existence des index (et des clusters)
– des statistiques sur les données contenues dans
les tables (nombre de lignes, de valeurs
différentes, etc.)
R. Grin
SGBD
119
Bases de données
réparties
R. Grin
SGBD
120
20
COMMIT "distribués"
Bases de données réparties
• Les processus qui fonctionnent dans un SGBD,
sont souvent réparties sur plusieurs machines
• De plus en plus fréquemment les données sont
elles-mêmes réparties sur plusieurs sites
• Cette répartition doit être le plus transparente
possible pour l'utilisateur
R. Grin
SGBD
121
COMMIT à 2 phases
SGBD
R. Grin
SGBD
122
COMMIT à 2 phases ; étape 1
• Un des sites (le plus souvent celui qui a reçu
l'ordre COMMIT) est le coordinateur de la
manœuvre
• Le COMMIT va se dérouler en 2 étapes bien
distinctes
• En fonctionnement normal, ce COMMIT à 2
phases est transparent pour l'utilisateur
R. Grin
• L'implantation des COMMIT est complexe si la
transaction porte sur des données réparties sur
plusieurs sites distants
• Un problème de réseau peut survenir quand un
COMMIT est lancé, et une machine distante
peut ne jamais recevoir l'avis de COMMIT
• Le COMMIT à 2 phases permet de conserver les
propriétés des transactions, même sur des bases
réparties (ou des bases multiprocesseurs)
123
1. Préparation du COMMIT : le coordinateur
ordonne à tous les autres sites de préparer sa
part du travail
Si un site indique qu'il est prêt, il assure qu'il
peut faire les modifications, même s'il tombe en
panne avant de recevoir l'ordre final du
coordinateur
Pour avoir cette assurance, les sites enregistrent à ce
moment les modifications à faire dans les images
"après"
R. Grin
SGBD
124
COMMIT à 2 phases ;
traitement des pannes
COMMIT à 2 phases ; étape 2
2. Exécution du COMMIT :
- si tous les sites répondent qu'ils sont prêt pour le
COMMIT, le coordinateur
a. enregistre le COMMIT dans ses images "après"
b. lance à tous les sites l'ordre d'effectuer le
COMMIT
- si un des sites ne répond pas ou indique qu'il ne
pourra effectuer le COMMIT, le coordinateur
ordonne un ROLLBACK à tous les sites
• Une panne peut intervenir sur un site qui a indiqué
qu'il était prêt, avant qu'il ne reçoive l'avis de
validation ou d'annulation du coordinateur
• Dans ce cas, lors de la reprise après la panne, le
SGBD du site en cause va
– s'apercevoir qu'il n'a pas reçu l'ordre final du
coordinateur
– s'informer auprès du coordinateur de sa décision
– effectuer un COMMIT ou un ROLLBACK (en utilisant
ses images "avant" ou "après")
R. Grin
SGBD
125
R. Grin
SGBD
126
21
Réplication des données
• Une difficulté des bases réparties est la perte de
performance due à la lenteur des réseaux
• Pour les sites confrontés à ce problème, il est
possible de ne gérer en temps réel que les
données locales, en utilisant des copies des
données distantes
• On peut automatiser la réplication de ces
données distantes à intervalles réguliers
R. Grin
SGBD
127
22