1

Introduction aux bases de données

Université de Nice Sophia-AntipolisVersion 2.1 - 5/12/2000

Richard Grin

R. Grin SGBD 2

Généralités sur les bases de données

R. Grin SGBD 3

Définitions

• 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)

R. Grin SGBD 4

Fonctions d'un SGBD

• 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,... )

R. Grin SGBD 5

Fonctions d'un SGBD (2)

• 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,...)

R. Grin SGBD 6

Indépendance par rapport aux traitements

• 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",…)

2

R. Grin SGBD 7

Sans base de données

BP 2536

BP 2536

BP 2536 Facturation

Commercial

Prospects

Fichiers Applications

Délais de mise à jourDonnées contradictoires R. Grin SGBD 8

Avec base de données

BP 2536Facturation

Commercial

Prospects

Base de données ApplicationsFiltre (vues)

R. Grin SGBD 9

Avec base de données

• 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)

R. Grin SGBD 10

Avantages de l'utilisation des SGBD• 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

R. Grin SGBD 11

Avantages de l'utilisation des SGBD

• Pour les SGBD relationnels : langage nonprocédural simple ⇒ interrogation directe possible par les utilisateurs et réponses rapides à des questions non prévues par l'application

R. Grin SGBD 12

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

3

R. Grin SGBD 13

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 14

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 15

Types d'utilisateurs (2)• Le programmeur

– é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

• L'utilisateur final– 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

R. Grin SGBD 16

Types de SGBD• SGBD hiérarchique :

– 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

• SGBD réseau :– 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

R. Grin SGBD 17

Types de SGBD (2)

• 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

R. Grin SGBD 18

SGBD relationnel

• 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

4

R. Grin SGBD 19

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 jointures pour les structures hiérarchiques)

R. Grin SGBD 20

SGBD objet• A priori, beaucoup d'avantages sur le relationnel• Mais,

– 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 21

Modèles de données

R. Grin SGBD 22

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

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 23

Niveau conceptuel

• 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)

R. Grin SGBD 24

Niveau logique

• 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)

5

R. Grin SGBD 25

Niveau logique pour les SGBD relationnels

• 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 26

Niveau physique

• 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)

R. Grin SGBD 27

Commercialcommission : double

setCommission (double)

Directeur Administratif

ParticipationfonctionP:String

ProjetcodeP:StringnomP :String

ajouterEmploye(Employe)

Départementnuméro : intnom : Stringlieu : String

ajouterEmployé(Employe) : void

Employematricule : intnom : Stringposte : StringdateEmbauche : datesalaire : double

getSuperieur ()setSuperieur(Employe)getMatricule()

0..*0..* 0..*

+participe

0..*

1

0..*

+contient1

0..* appartient

0..1

0..*

+supérieur

0..1

hierarchie

+subordonné

0..*

Une classe

Une association Une classe-association

Agrégation

Rôle pourl’association

Héritage

Multiplicité

R. Grin SGBD 28

Modèle relationnel

R. Grin SGBD 29

Relation• La notion de relation traduit le fait que les

éléments appartenant à des ensembles (distincts ou non) peuvent être en relation

Robert

Alain

Bernard

Julien

Sylvie

Nathalie

Carole

×

×

×

××

×

×

{ (Robert, Sylvie),(Alain, Nathalie),(Alain, Carole) }

Hommes Femmes

Sous-ensemble deHomme × Femme :

R. Grin SGBD 30

Relation mathématique

• 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

6

R. Grin SGBD 31

Relations de degré > 2

• 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 :{ (Robert, Sylvie, Toto), (Alain, Nathalie, Bibi),(Alain, Nathalie, Titi) }

R. Grin SGBD 32

Représentation des données• 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(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

R. Grin SGBD 33

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 Aiqui 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, salaire, numDept)

R. Grin SGBD 34

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,…)

R. Grin SGBD 35

Clés d'une relation

• 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)

R. Grin SGBD 36

Exemples de clés• EMPLOYE(matricule, nom, salaire) a

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)– (nom, prénom)

En supposant que 2 employésn'ont jamais les mêmesnom et prénom

7

R. Grin SGBD 37

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 Dupond 150000 1012 Durand 120000 10

132 Bernard 8500 20

R. Grin SGBD 38

Passage d'un diagramme de classes à un schéma relationnel

R. Grin SGBD 39

Étapes du passage d’un diagramme de classes à un

schéma relationnel

• Traduction des classes en relations• Traduction des associations en relations• Traduction de l’héritage

R. Grin SGBD 40

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)

R. Grin SGBD 41

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 identificateursignificatif comme un nom peut engendrer des traitements lourds sur une base de données

R. Grin SGBD 42

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)

8

R. Grin SGBD 43

Exemple de traduction d’une classe

• DÉPARTEMENT(numéro, nom, lieu)

• En SQL :create table departement (numero smallint primary key,nom varchar(15),lieu varchar(15))

R. Grin SGBD 44

Traduction d’une association binaire

• 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

R. Grin SGBD 45

Exemple de traduction d’une association binaire M:N

• PARTICIPATION(matr, codeP)• En SQL :create table participation (matr integer references emp, codeP varchar(2) references projet,primary key(matr, dept))

R. Grin SGBD 46

Classes association

• 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 47

Traduction d’une association binaire M:N avec classe-association

• PARTICIPATION(matr, codeP, fonctionP)• En SQL :create table participation (matr integer references emp, codeP varchar(2) references projet,fonctionP varchar(15),primary key(matr, dept))

R. Grin SGBD 48

Interprétation des relations qui traduisent une association

• Pour savoir qui participe au projet "QUALITÉ"1. on récupère le code de projet QUALITÉ dans

la relation PROJET2. on note tous les matricules qui sont associés à

ce code dans la relation PARTICIPATION3. on cherche les noms des employés qui ont ce

matricule dans la relation EMPLOYÉ

9

R. Grin SGBD 49

Traduction d’une association binaire dont une multiplicité maximum est 1 (1:N ou 1:1)

• 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

• La 1ère solution est plus souple mais plus coûteuse (jointures)

R. Grin SGBD 50

Exemple de traduction d’une association binaire 1:N

• EMP(matr,…, dept)• En SQL :create table emp (matr integer primary key,…dept smallint references dept)

R. Grin SGBD 51

Classes association

• 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

R. Grin SGBD 52

Traduction d’une association de degré > 2

• 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

• Le sous-ensemble peut être stricte si une dépendance fonctionnelle existe entre ces clés

R. Grin SGBD 53

Exemple de traduction d’une association de degré > 2

• 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),…)

R. Grin SGBD 54

Traduction de l’héritage

• 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

10

R. Grin SGBD 55

Exemple de traduction de l’héritage

• COMMERCIAL(matr, commission)• En SQL :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 56

Héritage multiple

• Pour traduire l’héritage multiple, on met comme identifiant dans la classe fille l’ensemble des identifiants des classes mères

R. Grin SGBD 57

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 58

Schéma relationnel utilisé pour ce cours

Employé(matr, nomE, poste, dateEmb, sup, salaire, commission, dept)

Dept(dept, nomD, lieu)Projet(codeP, nomP)Participation(matr, codeP , fonctionP)

Matriculedu chef

R. Grin SGBD 59

Langages d'interrogation relationnels

R. Grin SGBD 60

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

11

R. Grin SGBD 61

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 62

Langages d'interrogation fondés sur le calcul des prédicats du 1er ordre• 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 63

Calcul relationnel des t-uples

• 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

• Exemple (noms des employés qui participent à un projet) :{E.nomE / ∃ P PARTICIPATION(P) ∧

(P.matr = E.matr) ∧EMP(E)}

(les variables sont E et P)

Prédicat(P t-uple de

PARTICIPATION)

R. Grin SGBD 64

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 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

(valeurs de xdans domaine

de matr

R. Grin SGBD 65

Algèbre relationnelle

• 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

R. Grin SGBD 66

Opérateurs relationnels

• Soit une relation R[A1,…, An]• Projection sur les attributs Aj,…, Ak, notée 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

12

R. Grin SGBD 67

Opérateurs relationnels de base

• Exemple de projection :Employé[matricule, nom, salaire]

• Exemple de sélection :Employé / salaire > 12000

R. Grin SGBD 68

Equi-jointure• 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 t-uples 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

R. Grin SGBD 69

Exemple d'équi-jointure

matricule nom dept

1050 Dupond 10

832 Durand 20

900 Duval 10

dept nom lieu

10 Finances Paris

20 Ventes Nice

Employé Département

matricule

nom dept dept nom lieu

1050 Dupond 10 10 Finances Paris

832 Durand 20 20 Ventes Nice

900 Duval 10 10 Finances Paris

Employé J{dept=dept} Département

Notation simplifiée :Employé J{dept} Département

quand le nomdes 2 colonnes

est le même

R. Grin SGBD 70

Exemple de jointure naturelle

matricule nom dept

1050 Dupond 10

832 Durand 20

900 Duval 10

dept nom lieu

10 Finances Paris

20 Ventes Nice

Employé Département

matricule

nom dept nom lieu

1050 Dupond 10 Finances Paris

832 Durand 20 Ventes Nice

900 Duval 10 Finances Paris

Employé J{dept=dept} DépartementLa colonne dejointure n'estpas répétée

R. Grin SGBD 71

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 72

Division

• 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}

A B

x 1

y 2

z 1x 3

C

1

3

R SA

x

R ÷B S

/ /• "Division", car c'est le plus grand sous-ensemble

D de R[A] tel que D × S est inclus dans R

13

R. Grin SGBD 73

Quand utilise-t-on la division ?

• 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] ÷matrProjet[matr]

R. Grin SGBD 74

Opérateurs ensemblistes

• 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 75

Equivalence des langages

• On peut démontrer que l'algèbre relationnelle, les calculs relationnels des t-uples et des domaines sont équivalents : ils permettent de définir les mêmes ensembles de données

R. Grin SGBD 76

Normalisation d'un schéma relationnel

R. Grin SGBD 77

But de la normalisation• 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 SGBD 78

Dépendance fonctionnelle (DF)

• 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

14

R. Grin SGBD 79

Exemples de 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 80

Propriétés des DF

• 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 SGBD 81

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

R. Grin SGBD 82

Pratique de la normalisation

• 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 SGBD 83

Pratique de la normalisation

• Normaliser une relation consiste à extraire les DF internes qui posent des problèmes, en les transférant dans de nouvelles relations

R. Grin SGBD 84

Théorème important• 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, alorsR(A, B, C) = R[A, B] J{B} R[B, C](on peut dire que l'on a "extrait" la DF de R)

15

R. Grin SGBD 85

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)

La source de la DF reste dans la relation de départ en clé étrangère

R. Grin SGBD 86

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 SGBD 87

1ère forme normale

• Toutes les relations sont en 1 FN : elles n'ont pas d'attributs multivalués

• La pseudo-relationLivre(codeISBN, titre, auteurs)peut être décomposée en 2 vraies relations :Livre(codeISBN, titre)Auteurs (codeISBN, auteur)

multivalué

R. Grin SGBD 88

2ème forme normale

• 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 89

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 90

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)

16

R. Grin SGBD 91

3ème forme normale• 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 92

Forme normale de Boyce-Codd

• Une relation est en FNBC si les seules sources de DF sont les clés candidates

• 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é

Pourquoi ?

R. Grin SGBD 93

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 relationParticipation2(dept, codeP, matr, fonctionP)

• Elle est en 3 FN mais pas en FNBC

R. Grin SGBD 94

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 95

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 96

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

17

R. Grin SGBD 97

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 Participation2, la vérification est faite automatiquement par unicité de la clé primaire)

R. Grin SGBD 98

Utilisation d'un SGBD relationnel

R. Grin SGBD 99

Sécurité -Contrôle de l'accès à la base

• Le contrôle de l'accès à la base est effectué en associant à chaque utilisateur– 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– …

R. Grin SGBD 100

Sécurité -Propriétaire des données

• 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 101

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 102

Implantation d'un SGBD relationnel

18

R. Grin SGBD 103

Fichiers de la base• 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 104

Images "avant" et "après"

• 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 105

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 106

• 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 SGBD 107

Processus clients et serveurs• Les SGBD s'appuient sur le mode client-

serveur :– 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

R. Grin SGBD 108

Écriture dans la base

• 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

19

R. Grin SGBD 109

Oracle versions 7 et 8

• 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 110

Segments de rollback

• 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 111

Oracle version 6

• 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

R. Grin SGBD 112

Fichiers redo log• Avec Oracle 7 et 8, les images "après" sont

enregistrées dans des fichiers redo log, – 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 113

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)

R. Grin SGBD 114

Client 1 Client 2 Client 3

Serveur 1 Serveur 2

BufferRedo Log Buffers de la base

Fichiers de la baseFichiersRedo Log

Disque

SGBD

LGWR DBWR

20

R. Grin SGBD 115

Types de pannes

• logicielles• matérielles• dues aux réseaux

• Les fichiers de la base peuvent avoir été endommagés ou pas

R. Grin SGBD 116

Reprise après panne quand les fichiers sont corrects

• Automatiquement, quand il redémarre, le SGBD – 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)

• Pour cela, le SGBD utilise les images "après" et "avant"

R. Grin SGBD 117

Reprise après panne quand les fichiers sont endommagés

• L'administrateur doit commencer par recharger la dernière sauvegarde complète de la base

• Ensuite 2 cas : – 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 !)

R. Grin SGBD 118

Optimiseur de requêtes

• 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 119

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 120

Bases de donnéesréparties

21

R. Grin SGBD 121

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 122

COMMIT "distribués"• 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)

R. Grin SGBD 123

COMMIT à 2 phases

• 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 SGBD 124

COMMIT à 2 phases ; étape 11. Préparation du COMMIT : le coordinateur

ordonne à tous les autres sites de préparer sa part du travailSi 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 coordinateurPour avoir cette assurance, les sites enregistrent à ce moment les modifications à faire dans les images "après"

R. Grin SGBD 125

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

R. Grin SGBD 126

COMMIT à 2 phases ;traitement des pannes

• 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")

22

R. Grin SGBD 127

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