bases de donnees et sgbd - miage.ups-tlse.fr · sgbd relationnels standard sql sgbd objet sgbd...

BASES DE DONNEES et SGBDConceptionStructure

OrganisationMichel Tuffery

Bases de Données

Système de Gestion de Bases de Données

Présentation

Michel Tuffery 3

Historique des Systèmes de Gestion de Bases de Données (SGBD)

SGBD

Langages1965 1975 1985 1995 2005

IMS IDMSSOCRATE

System R DB2 INGRESORACLE INFORMIX

O2 ONTOSORION

ORACLE INFORMIX(OR)

AssembleurFortranCobolPL1

Pascal C C++ JAVA

SGBD Propriétaires(hiérarchiquesou réseaux)

SGBD relationnelsStandardSQL

SGBDObjet

SGBDObjet-Relationnel

Michel Tuffery 4

BD et SGBD : Définitions

BDUne Base de Données est un ensemble structuréd’informations enregistrées mis à la disposition de manière sélective à un ensemble d’utilisateurs

SGBDUn Système de Gestion de Base de Données permet de gérer toutes les informations stockées (Description, Consultation, Adjonction, Modification, Suppression, Autorisations) en toute sécurité dans un contexte multi-utilisateurs.

Michel Tuffery 5

Objectifs des Bases de Données

• Centraliser l’information– Ensemble unique de stockage– Non redondance de l’information

• Centraliser les contrôles– Contraintes d’intégrité au sein de la BD– Modifications aisées des règles de gestion

• Rendre indépendant les données et les programmes– Modification de la structure des données sans toucher aux

traitements• Faciliter l’accès aux données

– Langage standard d’accès aux données : SQL

Michel Tuffery 6

Fonctions d’un SGBD

• Décrire l’information– Création des objets avec leurs contraintes– Modification des structures et des contraintes– Gestion de l’espace disque

• Partager l’information– Droits et devoirs des utilisateurs– Notion de rôles et de privilèges

• Assurer la Sécurité de l’information– Reprise après incident

• Respecter l’Intégrité de l’information– Transaction : annulation ou confirmation

• Autoriser la Confidentialité des informations– Tout le monde ne peut pas voir et faire n’importe quoi

Michel Tuffery 7

SGBD Hiérarchiques : définitions

• Applications liées au langage COBOL• Associations de type Père-Fils• Encapsulation des objets fils avec le père• Attributs en occurrence (tableaux)• Associations N-N possibles en dupliquant la base

Michel Tuffery 8

SGBD Hiérarchiques : présentation

ENSEIGNANTCodeEnsEnomEgrade

MATIERECodeMatMnomMcoeffMheures

1

N

Association 1-N

EMPLOYECodeEmpnomEmpSalaire

PROJETCodeProjetBudgetProj

1

N EMPLOYECodeEmpnomEmp

PROJETCodeProjet

N

1

Association N-N

(par duplication !)

Michel Tuffery 9

SGBD Hiérarchiques : Avantages et Inconvénients

• Compréhension aisée pour les développeurs COBOL avec les occurrences

• Accès rapide à tous les objets fils pour un père nommé

• Accès lent pour chercher le père d’un fils nommé• Pas de sécurité des données : la suppression

d’un père entraîne la suppression de ses fils

SGBD Réseaux

Michel Tuffery 10

SGBD réseaux : définitions

• Amélioration des SGBD hiérarchiques• Associations N-N autorisées• Parcours des données en réseau• Notion de

RECORDet de SET

FOURNISSEURCodeFournomFourVilleFour

VENDREPrixUnitaireQteDelaiMinimum

1

N

PIECECodePieceLibelleCouleur

N

1

RECORD

SET

FOUR-VEN PIECE-VEN

Michel Tuffery 11

SGBD réseaux : structure physique

Tête d’anneau (OWNER) MEMBER

RECORD Père

RECORDs Fils

Michel Tuffery 12

SGBD réseaux : avantages et inconvénients

• Avantages– Problèmes des ajouts, suppressions, modifications

résolus– Sécurité des données– Accès rapide à l’information

• Inconvénients– Langage navigationnel : parcours des SET– Gestion des pointeurs physique à la charge du SGBD

SGBD Relationnels

Michel Tuffery 13

SGBD Relationnels : présentation succincte

• Représentation des données sous forme tabulaire (tables)

• Plus de pointeurs physiques : clés étrangères pour relier les données des différentes tables

• Pas d’occurrences autorisées : attributs simples• Langage standardisé pour structurer et accéder

aux données : SQL• SGBD indépendants des constructeurs

Michel Tuffery 14

BD Relationnelle :structure et accès aux données

TlseSylvie300ParisMichel200TlseJean100

Etudiant

IdEtu Nom Adresse

8oracleOr3Langage CC5Bases de D.Bd

UVIdUV NomUV Valeur

CoursIdEtu IdUV Note

13Or30014Bd20012Bd3008Or10015C10012Bd100

SELECT u.nomuv, u.valeurc.noteFROM etudiant e, uv u,

cours cWHERE e.idetu=c.idetu

AND c.iduv=u.iduvAND e.nom=‘Jean’

Structure d’une BD et d’un SGBD

Concepts généraux

Exemple avec Oracle

Michel Tuffery 16

Le Dictionnaire des données :un rôle essentiel

• Centralise les contrôles– Contrôles simples ou statiques– Contrôles dynamiques (Si insee(1)=2 alors sit_mil=null)– Contrôles de références fils-père (code_dip étudiant)– Contrôles complexes (3 emprunts maximum)

• Centralise les déclarations d’attributs– Plusieurs types possibles (char, varchar, ….)– Longueurs dynamiques ( nom varchar(40))

• Lien entre programmes et données– indépendance données – traitements :– aucune action physique sur les données– aucune modification de programmes

• Enregistre de nombreuses informations– informations statistiques (valeurs max , min )– informations de taille d’attributs ,… optimisation

Michel Tuffery 17

Le Dictionnaire des données :Organisation générale

SGBD

Dictionnaire de Données

Base de Données

Michel Tuffery 18

Le Dictionnaire des données :Organisation avec Oracle

• Tablespace spécial (system)– Présenté sous forme de tables SQL :

SELECT * FROM USER_TABLES ;– Trois types d’objets : USER_, ALL_ et DBA_– Uniquement en mode lecture pour l’utilisateur

• Sauvegarde obligatoire , toujours en ligne• Utilisation

– Les renseignements nécessaires sont chargés en mémoire centrale

– Le système conserve en mémoire les informations pour l’utilisateur

Michel Tuffery 19

Les composants d’une base de données :Objets physiques (données)

• Les tables contenant les données• Les fichiers index (primaire ou

secondaire)• Les clusters contenant les mélanges de

tables• Les fichiers de reprise (Logs)• Les fichiers de contrôle• Le dictionnaire de données • Les Snapshots

Michel Tuffery 20

Les composants d’une base de données :Objets physiques (programmes)

• Les traitements stockées– Packages– Procédures– Fonctions

• Les triggers ou déclencheurs– Mise à jour automatique de colonnes dérivées– Mise en œuvre de contraintes complexes– Génération automatique d’évènement

BD = Données + Programmes

Michel Tuffery 21

Les composants d’une base de données :Objets virtuels

• Les vues – Tables virtuelles– Schémas externes ou sous-schémas

• Les synonymes • Les database link

– Liens inter-bases• Les contraintes

Pas d’implémentation physique :

Activation à l’appel

Michel Tuffery 22

Notion de ‘base de données’ : Plusieurs ‘bases utilisateur’

• Notion de propriétaire (‘owner’) d’objets• Partage de la même base par plusieurs utilisateurs • L’utilisateur système : DBA système

– responsable de la ‘base’– sauvegarde et restauration – gestion des utilisateurs – gestion de l’espace disque – gestion des ressources système

• L’utilisateur propriétaire : DBA données – responsable de ses objets – délègue des droits sur ses objets à d’autres utilisateurs – gère ses propres sauvegardes

• L’utilisateur ’de base’ accède aux objets et aux actions autorisés

Michel Tuffery 23

Notion de ‘base de données’ : Notion de ‘rôle utilisateur’

• Le rôle DBA : équivalent du ‘root’ système• Le rôle RESOURCE : création et modification de

ressources (propriétaire)• Le rôle CONNECT : connexion à la base et

respect des privilèges reçus• Des rôles sur mesure :

– CREATE ROLE secr_scol ;– GRANT SELECT,INSERT ON etudiant TO secr_scol ;– GRANT SELECT,UPDATE(adr,tel) ON enseignant TO

secr_scol ;– GRANT secr_scol TO martine, simone ;

Michel Tuffery 24

Notion de ‘base de données’ : Instance

• 1 instance = 1 Base en mouvement• Plusieurs instances possibles sur une même machine (1

licence serveur)• Les composants d’une instance :

– un ensemble de process – un dictionnaire de données – autonomie complète (DBA)– liens possibles entre instances

• Pourquoi plusieurs instances ?– sécurisation de certaines bases– bases tests– répartition physique et administrative

Michel Tuffery 25

Base de Données en architectureClient-Serveur

• Le poste Client– Interface de présentation– Interface graphique

• Le Middleware– Drivers de communication

• Le Serveur– Base de données– Sécurité, Intégrité et Confidentialité

Michel Tuffery 26

Base de données en Client-ServeurLes composants du Client

Interface graphique extérieure

ODBC

SQL*Net

TCP/IP

Transport / Communication

Interface graphique de la base

Michel Tuffery 27

Base de données en Client-ServeurLes composants du Serveur

SGBD BD

Transport / Communication

TCP/IP

SQL*NET

Listener

Michel Tuffery 28

Bases de Données et Intranet

navigateur

Client léger Serveur Web

Serveur Applicatif

Serveur de Données

SGBDURL

HTML

HTTP

Pages.html

ObjetsMulti-média

Exécutiondu

programme

connexionrequêtes

résultats

Base de Données

Pages HTMLdynamiques

Conception d’une Base de Données

Le Diagramme de Classes UML

Michel Tuffery 30

Conception d’une BD : les niveaux

• conceptuel• logique• physique• externe

schémaphysique

schémaconceptuel

schémalogique

R1R2

Utilisateurs

Michel Tuffery 31

Conception d’une BD : les étapes

Monde Extérieur

Modélisation(UML)

Produits

idProdésignationpoids

Fournisseurs

idFourraisonSocialeadresse

FournirprixAchat

<< Association >>1 1* 1..*

Schéma Conceptuel : DC

Produits (IdPro, désignation, poids)

Fournir(IdPro, IdFour, PrixAchat)

Fournisseurs(IdFour, RaisonSociale, adresse)

Schéma Logique ou Relationnel

Règles depassage

Normalisation ?

Mise en œuvre

de la BD Schéma Physique

Michel Tuffery 32

Le Diagramme de Classes (DC) :concepts de base

• Classe– Ensemble d’objets concrets ou abstraits de même nature– Une classe est décrite par ses attributs, méthodes et

contraintes– Exemples : Etudiant, Employe, Produit, …

• Attribut– Propriété décrivant une classe– Valeur unique pour chaque classe– Exemple : nom, prenom, adresse , ….

• Identifiant– Attribut particulier permettant de repérer une occurrence – Exemple : idClient, idEtudiant, ….

• Association– Permet de relier une classe à une ou plusieurs autres–

Michel Tuffery 33

Le Diagramme de Classes :Notion d’Association

• Dimension d’une association– Nombre de classes entrant liées– Binaire : 2, Ternaire : 3, N-aire : n

• Nom d’une association– Verbe à l’infinitif : Appartenir, Fournir, …

• Multiplicité– Nombre minimum et maximum d’objets liés

1..*0..* ou *Multiplicité1..1 ou 10..1Unicité

ObligatoireOptionnelMinimum, Maximum

Michel Tuffery 34

Représentation des classes

ÉtudiantIneEtudiantNomEtudiantAdrEtudiant

Moyenne()Modules_obtenus()

Inscription_correcteNombre_inscrits_dip

Nom de la Classe

Attributs

Identifiant

Méthodes

Contraintes

ProcéduresStockées

Déclencheurs

Michel Tuffery 35

Associations de type Mère-Fille (1-N)

Diplome Etudiant

IdDipNomDipRespDip

IneEtudiantNomEtudiantAdrEtudiant*1

Un diplôme (classe mère) peut concerner aucun ou plusieurs étudiants (classe fille).

Un étudiant doit être inscrit à , au moins et au plus, un seul diplôme.

Inscrire

Michel Tuffery 36

Associations de type multiple (N-N)sans attributs

Etudiant Stage

IneEtudiantNomEtudiantAdrEtudiant

IdStageThémeStageEntrepriseRespStage

1..**

Choisir

Un étudiant doit choisir au moins un stage et peut en choisir plusieurs.

Un stage peut être choisi par aucun étudiant ou plusieurs.

Michel Tuffery 37

Associations de type multiple (N-N)avec attributs

Fournisseur Produit

IdFourNomFourAdrFour

IdProduitNomProduitQtéStock

* 1..*

Fournir

PrixDélaiLivraisonQuantitéMin

Classe d’association

Michel Tuffery 38

Associations de type multiple (N-N)N aires (N>2)

Fournisseur ProduitIdFourNomFourAdrFour


FournirPrixDélaiLivraisonQuantitéMin

Conditionnement

IdCondLibCond

1..*

*

*

Michel Tuffery 39

Associations de type symétrique (1-1)

Etudiant Stage


IdStageThémeStageEntrepriseRespStageNote stage

10..1

Effectuer

Un étudiant effectue au moins un et un seul stage.

Un stage peut être effectué par aucun ou un seul étudiant.

(Une association de type 1-1 est souvent le résultat d’un éclatement de classe)

Michel Tuffery 40

Associations réflexives1-N réflexif

Enseignant

IdEnseignantNomEnseignantFonctionindice

Est responsable de

A pour responsable

*

0..1

Michel Tuffery 41

Associations réflexivesN-N réflexif

Produit

IdProduitLibelléProduitPrix

Est composé de

Entre dans la composition de

*

*

ComposerQuantité

Michel Tuffery 42

Assemblage de classes Reprise d’une classe d’association

• Certaines associations N-aires peuvent être transformées en plusieurs associations binaires

• On associe d’abord les deux classes les plus stables : stabilité du schéma

• La classe d’association se transforme en classe normale pour la troisième classe associée

• Merise parlait d’agrégation d’entités

Michel Tuffery 43

Reprise d’une classe d’associationAssociations de type 1-N et N-N

Joueur TournoiIdLicenceNomJoueurClassement

IdTournoiNomTournoiVilleDate

* *

ParticiperRésultat

AmendeIdAmendeMontant

* *Recevoir

Classesstables

Association N-Nsans attribut

PrixIdPrixMontant

Obtenir

0..1*Association 1-N

(La classe d’association ‘Participer’ est transformée en classe normale)

Michel Tuffery 44

Associations d’agrégation et de composition

EtudiantIneEtudiantNom

UVIdUVIntUV

InscrireNoteRésultat

* 1..8

En cas de suppression d’un étudiant : on supprime toutes ses inscriptions

On ne peut pas supprimer une UV ayant au moins un inscrit

Michel Tuffery 45

Généralisation et Spécialisation

PersonnelIdPersNomIndice

ChercheurSpécialitéLaboratoire

EnseignantSectionTitreHeuresCours

IngénieurGradeFonctionDomaine

Conception d’une Base de Données

Le Modèle Logique

Michel Tuffery 47

Conception d’une BD : les niveaux

• conceptuel• logique• physique• externe

schémaphysique

schémaconceptuel

schémalogique

R1R2

Utilisateurs

Michel Tuffery 48

Conception d’une BD : les étapes

Monde Extérieur

Modélisation(UML)

Produits

idProdésignationpoids

Fournisseurs

idFourraisonSocialeadresse

FournirprixAchat

<< Association >>1 1* 1..*

Schéma Conceptuel : DC

Produits (IdPro, désignation, poids)

Fournir(IdPro, IdFour, PrixAchat)

Fournisseurs(IdFour, RaisonSociale, adresse)

Schéma Logique ou Relationnel

Règles depassage

Normalisation ?

Mise en œuvre

de la BD Schéma Physique

Michel Tuffery 49

Schéma Logique : Schéma Relationnel Présentation

• Base théorique du langage SQL• Modèle basé sur les valeurs

– Di = {valeurs atomiques}• Attribut

– ai prenant sa valeur dans Di

• Relation– R[a1,..., an] définie sur les domaines D1, ..., Dn– R sous-ensemble du produit cartésien D1*D2*...*Dn

ETUDIANT (INE, NOM,ADRESSE)

INE = {100, 200, 300}NOM = {‘Sylvie’,’Patrick’}ADRESSE = {‘Toulouse’,’Montauban’}

Michel Tuffery 50

Schéma relationnelConcepts et Terminologie

• Relation : nom de la structure tabulaireregroupant les informations de même nature

• Tuple ou N-Uplet : ligne d’une relation• Attribut : nom du domaine • Clé primaire : attribut(s) permettant d’identifier

d’une manière unique une ligne • Clé étrangère : attribut(s) permettant d’identifier

d’une manière unique une ligne d’une autre relation (correspondant à une clé primaire)

• Clé candidate : attribut(s) pouvant se substituer àla clé primaire

Michel Tuffery 51

Schéma relationnelExemple

3Achats20

2Informatique10IdSer

SERVICE

NomSer NombreEmp

40010Thomas500

------10Claude400

------20Bernard300

40010Sylvie200

30020Michel100

EMPLOYE

IdEmp NomEmp IdSer IdEmp_Resp

Clés Primaires

Clés ÉtrangèresClé Candidate

Michel Tuffery 52

Schéma relationnelQuelques postulats

• Clé primaire– Obligatoire pour toutes les relations– Peut être composée de plusieurs attributs– Ne peut pas avoir de valeur indéfinie (NULL)

• Clé étrangère– Aucune ou plusieurs par relation– Peut être composée de plusieurs attributs– Contrainte d’Intégrité Référentielle

• Clé candidate– Accès rapide dans le schéma physique

Michel Tuffery 53

Passage du S. Conceptuel au S. RelationnelLes règles de passage

• Règle n°1 : Classes normales– Chaque classe devient une relation – L’identifiant de la classe devient la clé primaire de la relation

• Règle n°2 : Classes d’Associations 1-N (Mère-Fille)– Cette classe disparaît– La clé de la relation mère glisse dans la relation fille Clé

Étrangère• Règle n°3 : Classes d’Associations N-M (et n-aires)

– Cette classe devient une relation– La clé primaire est composée des clés associées (clé primaire

composée)• Règle n°4 : Classes d’Associations 1-1

– Cas particulier : expliqué plus loin• Règle n°5 : Généralisation et Spécialisation

– Expliqué plus loin

Michel Tuffery 54

Association 1-N : Exemple

Diplome Etudiant

IdDipNomDipRespDip

IneEtudiantNomEtudiantAdrEtudiant*1

Inscrire

DIPLÔME (IdDip, NomDip, RespDip)

ETUDIANT (IneEtudiant, NomEtudiant, AdrEtudiant, IdDip#)

Michel Tuffery 55

Association N-N sans attribut : Exemple

Etudiant Stage


IdStageThémeStageEntrepriseRespStage

1..**

Choisir

ETUDIANT (IneEtudiant, NomEtudiant, AdrEtudiant)

CHOISIR (IneEtudiant#, IdStage# )

STAGE (IdStage, Thèmestage, Entreprise, RespStage)

Michel Tuffery 56

Clé primaire composée : postulats

• Autant de composants que de classes associées (N-aires)

• Composée entièrement de clés étrangères• L’ordre des composants n’a pas d’importance

(pour le schéma relationnel)


CHOISIR (IneEtudiant#, IdStage# )

STAGE (IdStage, Thèmestage, Entreprise, RespStage)

Michel Tuffery 57

Association N-N avec attributs : Exemple

Fournisseur Produit

IdFourNomFourAdrFour


* 1..*

Fournir

PrixDélaiLivQtéMin

FOURNISSEUR(IdFour, NomFour, AdrFour)

FOURNIR (IdFour #, IdProduit#, Prix, DélaiLiv, QtéMin )

PRODUIT (IdProduit, NomProduit, QtéStock)

Michel Tuffery 58

Association de type N-aires : Exemple

Fournisseur ProduitIdFourNomFourAdrFour


FournirPrixDélaiLivQtéMin

Conditionnement

IdCondLibCond

1..*

*

*

Michel Tuffery 59

Association de type N-aires : Exemple

FOURNISSEUR(IdFour, NomFour, AdrFour)

PRODUIT (IdProduit, NomProduit, QtéStock)

CONDITIONNEMENT (IdCond, LibCond)

FOURNIR (IdFour #, IdProduit#, IdCond#,

Prix, DélaiLiv, QtéMin )

Michel Tuffery 60

Association 1-N réflexive : Exemple

Enseignant

IdEnsNomEnsFonction

Est responsable de

A pour responsable *

0..1

ENSEIGNANT (IdEns, NomEns, Fonction, IdEns_Resp#)

(nul autorisé)

Michel Tuffery 61

Association N-N réflexive : Exemple

Produit

IdProduitLibProduitPrix

Est composé de

Entre dans la composition de *

*

ComposerQté

PRODUIT (IdProduit, LibProduit, Prix)

COMPOSER (IdProduitComposé#, IdProduitComposant#, Qté)

Michel Tuffery 62

Reprise de classe d’association : Exemple

Joueur TournoiIdLicenceNomJoueurClassement

IdTournoiNomTournoiVilleDate

* *

ParticiperRésultat

AmendeIdAmendeMontant

* *Recevoir

PrixIdPrixMontant

Obtenir

0..1*

Michel Tuffery 63

Reprise de classe d’association : Exemple

JOUEUR (IdLicence, NomJoueur, Classement)

TOURNOI (IdTournoi, NomTournoi, Ville, date)

PARTICIPER (IdLicence#, IdTournoi#, Résultat, IdPrix#)

PRIX (IdPrix, Montant)

AMENDE (IdAmende, Montant)

RECEVOIR (IdAmende#,(IdLicence,IdTournoi)#)

(nul autorisé)

2 clés étrangères

Michel Tuffery 64

Associations de type symétrique (1-1)Règle N°4 : 3 cas possibles

C1 C2IdC1 IdC2

Association

1 1

C1 (IdC1, ……., IdC2#)

C2 (IdC2, ………..)

C1 (IdC1, …….)

C2 (IdC2, ……….., IdC1#)

C1 (IdC1, …….)

C2 (IdC2, ………..)

Association (IdC1#, IdC2#)

UNIQUE

Valeurs nulles autorisées ou passelon les valeursminimums

Dans le cas d’uneassociation optionnelle

Michel Tuffery 65

Associations de type 1-1 : Exemple

Etudiant Stage


IdStageThémeStageEntreprise0..10..1

Effectuer


STAGE (IdStage, ThèmeStage, Entreprise)

EFFECTUER (IdStage#, IdEtudiant#)

UNIQUE

Michel Tuffery 66

Généralisation et Spécialisation : Règle N°5

• Chaque classe devient une relation• Une ‘super classe’ peut contenir un attribut de

spécialisation• Chaque relation de l’ensemble possède la même

clé primaire• La vraie spécialisation avec l’héritage

correspondant sera pris en charge avec les SGBD Objet-Relationnel

Michel Tuffery 67

Généralisation et Spécialisation : Exemple

PersonnelIdPersNomIndice

ChercheurSpécialitéLaboratoire

EnseignantSectionTitreHeuresCours

IngénieurGradeFonctionDomaine

Michel Tuffery 68

Généralisation et Spécialisation : Exemple

PERSONNEL (IdPers, Nom, Indice, TypePers)

INGENIEUR (IdPers, Grade, Fonction, Domaine)

ENSEIGNANT (IdPers, Section, Titre, HeuresCours)

CHERCHEUR (IdPers, Spécialité, laboratoire)

Attribut de Spécialisation

Michel Tuffery 69

Notion de Dépendance Fonctionnelle (DF)

• Objectifs des DF• Conception du Schéma Logique• Aide à la normalisation des relations

• Définition• Soit a et b deux attributs, b est fonctionnellement

dépendant de a si à toute valeur de a correspond au plus une valeur de b (notation : a→b)

• Exemples et contre exemple• IneEtudiant → NomEtudiant• IneEtudiant → AdrEtudiant• NomEtudiant → AdrEtudiant

Michel Tuffery 70

Qualité des dépendances fonctionnelles

• DF Élémentaire• a→b est une DF élémentaire si ∀ a’⊂a alors a’→b

n’est pas une DF• IdLicence, IdTournoi, IdPrix → Résultat

est une DF non élémentaire

• DF Directe• a→b est une DF directe si il n’existe pas b⊄c et c⊄a

tel que a→c et c→b• une DF non directe est déduite par transitivité

Michel Tuffery 71

Propriétés des dépendances fonctionnelles

• Réflexivité – a attribut de R ⇔ a→a est une DF

• Transitivité– Si a→b et b→c sont des DF ⇒ a→c est une DF

• Pseudo-transitivité – a→b et b,c→d DF ⇒ a,c→d DF

• Additivité (union) – a→b et a→c DF ⇔ a→b,c est une DF

• Décomposition– a→b,c DF ⇔ a→b et a→c DF

• Augmentation– a→b DF et ∀ c ⇒ a,c→b DF

Michel Tuffery 72

Fermeture et couverture minimaled’un graphe F (ensemble de DF)

• Fermeture• la fermeture de F est l’ensemble de toutes les DF

résultant de l’application des propriétés des DF (réflexivité...)

• Exemple : soit F={ a→b ;b→c}, F+ est composée de 21 DF en plus (a→a ; b→b ; a→c...)

• Couverture minimale• la couverture minimale est un ensemble minimal de

DF (élimination des DF redondantes)• Exemple : F = {a→b1 ;b→c2 ; c→d3 ;a→d4 ; c,f→g5 ;

a→b,c6 ;a,f→g7}, la couverture minimale de F est {a→b1 ;b→c2 ; c→d3 ; c,f→g5}

Michel Tuffery 73

Conception directe du Schéma logique :deux approches possibles

• Approche par Décomposition– Notion de Forme normale– Décomposition ou normalisation

• Approche par Synthèse– Étude des DF– Construction directe du schéma normalisé

Michel Tuffery 74

Approche par Décomposition :Normalisation

• Schéma• Ensemble de relations• Ensemble de DF

• Objectifs de la normalisation• Classification des relations• Minimiser la redondance d’informations• Préserver l’intégrité des informations

• Dénormalisation• Diminuer le nombre de relations• Réduire les futures jointures

Michel Tuffery 75

Approche par Décomposition :les formes normales

• 1e forme normale • Tous les attributs non clé sont en DF avec la clé • Pas d’attributs tableaux

• 2e forme normale• Déjà en 1e forme normale• Toutes les DF issues de la clé sont des DF élémentaires

• 3e forme normale• Déjà en 2e forme normale• Toutes les DF issues de la clé sont des DF directes

• 4e forme normale : test de validité du schéma• Déjà en 3e forme normale• Pas de DF à l’intérieur d’une clé

Michel Tuffery 76

Approche par Décomposition :Les étapes de décomposition

ETDIANT(Ine, nom, adresse, Matières(IdMat, LibMat, IdRespEns,

NomRespEns, Partiels(IdPar, LibPar, Note)))

ETUDIANT

Ine Nom Adresse Matières

IdMat LibMat IdRespEns NomRespEns Partiels

IdPar LibPar Note

(n)

(m)

(n)

(m)

Michel Tuffery 77

Approche par Décomposition :Les étapes de décomposition (suite)

ETDIANT(Ine, nom, adresse, Matières(IdMat, LibMat,

IdRespEns, NomRespEns, Partiels(IdPar, LibPar, Note)))(n)

(m)ONF

1NF

Élimination des attributs tableaux : éclatement des relations imbriquées

ETDIANT(Ine, nom, adresse)

MATIERES(Ine,IdMat, LibMat, IdRespEns, NomRespEns)

PARTIELS(Ine,IdMat,IdPar, LibPar, Note)

2NF

Élimination des DF non élémentaires

../

Michel Tuffery 78

Approche par Décomposition :Les étapes de décomposition (suite et fin)

2NF


MATIERES(IdMat, LibMat, IdRespEns, NomRespEns)

COURS(Ine, IdMat)

PARTIELS(IdPar, LibPar, IdMat)

NOTES(Ine,IdPar, Note)

3NF

Élimination des DF non directes ou transitives


MATIERES(IdMat, LibMat, IdRespEns#)

ENSEIGNANT(IdRespEns, NomRespEns)

COURS(Ine#, IdMat#)

PARTIELS(IdPar, LibPar, IdMat#)

NOTES(Ine#,IdPar#, Note)

4NF : OK

(IdMat est en occurrence avec Ine)

Michel Tuffery 79

Avantages de la Normalisation

• Non redondance de l’information– Pour une matière donnée, on n’enregistre pas toute

l’information sur son enseignant responsable• Mise à jour facilitée

– MAJ de l’intitulé d’un partiel : 1 fois• Accès identique à toutes les informations

– Jointures identiques• Sécurité de l’information assurée

– On n’attend pas les notes pour enregistrer les informations sur un partiel

Constructions d’une base à partir de fichiers existants

Michel Tuffery 80

Approche par Synthèse : Présentation

• Généralités• Concevoir ou modifier un schéma• Point de départ : F = {a→b1 ;b→c2, … }, résultat

schéma relationnel normalisé : R1[a, b, …]...• Étapes

• Construction de l’ensemble départ des DF• Suppression des DF redondantes déduites par

transitivité, union et décomposition• Regroupement des DF ayant même partie gauche

dans des sous ensembles, les DF de type x→y et y→x doivent être regroupés dans le même sous ensemble

Michel Tuffery 81

Approche par Synthèse :la démarche

• 1ére étape : suppression des DF redondantes– Déduite par transitivité, union, décomposition– Exemple : a→b1, b→c2, c→d3, a→d4 la dernière est

redondante• 2éme étape : regroupement des DF

– DF ayant même partie gauche sont regroupés dans des sous ensembles

– DF de type x→y et y→x doivent être regroupés dans le même sous ensemble

• 3éme étape : relations (3 NF) – Chaque sous ensemble devient une relation dont la clé

primaire est la partie gauche des DF du sous ensemble

Michel Tuffery 82

Approche par Synthèse :Exemple de mise en oeuvre

• Point de départ : – A = {a, b, c, d, e, f, g, h, j, k} – F = {a→b1 ; a→c2 ; a,b,h→e,g3 ; h→j4 ; j→k5 ; h→k6 ;

b→a7}• 1ére étape : suppression des DF redondantes

– 6 est redondante car elle peut être obtenue avec 4 et 5 par transitivité : h→j ; j→k donne h→k

– 1 et 3 permettent de simplifier 3 par pseudo-transitivitéa→b et a,b,h→e,g donne a,a,h→e,g a,a,h→e,g donne a,h→e,g3

Michel Tuffery 83

Approche par Synthèse :Exemple de mise en œuvre (suite)

• 2éme étape : regroupement des DF– même partie gauche E1 = {a→b1 ; a→c2} ; E2 = { a,h→e,g3} E3 = {h→j4} ; E4 = {j→k5} ; E5 = {b→a7}

– x→y et y→xE1 = {a→b1 ; a→c2 ; b→a7} et E5 = {b→a7}

• 3éme étape : relations (4 NF)– R1[a,b,c]– R2[a#,h#,e,g]– R3[h,j#]– R4[j,k]

LangageRelationnel

Graphe deRequêtes

Michel Tuffery 85

Présentation

• Basé sur la théorie des ensembles– relations , opérateurs et opérandes– Opérateurs : relationnels, de test (<, >, = , …) et

booléens (ET, OU, NON)– Opérandes : constantes ou variables

• Recherche d’informations sur les relations par langage algébrique– Résultat d’une recherche : relation– Base du langage SQL

GRAPHE DES REQUETES

Michel Tuffery 86

Opérateurs relationnels

• Opérateurs unaires– Sélection– Projection

• Opérateurs binaires– Union– Intersection– Différence– Division– Produit Cartésien– jointure

Opérateurs ensemblistes

Michel Tuffery 87

Sélection

• Définition– Une sélection appliquée sur la relation R1[a1,...,an], selon

le prédicat p1 (condition) donne une relation R2[a1,...,an] ayant les n-uplets satisfaisant la condition

• Symbole utilisé

R1

R

Prédicat de Sélection

Michel Tuffery 88

Sélection : exemple

24Jérôme50023Laurent40011Corinne30023Michel20021Sylvie100

ETUDIANTIne Nom Groupe Année

ETUDIANT

R

Année = 2ET Groupe = 3

RIne Nom Groupe Année

23Laurent40023Michel200

Étudiants du groupe 3de deuxième année ?

Michel Tuffery 89

Projection

• Définition– la projection de la relation R1[a1,...,an] sur les attributs

ai,...,am (ai,...,am ⊂a1,...,an) est une relation R2[ai,...,am]– Pas de duplication des n-uplets résultats


R1

R

ai,...,am

Michel Tuffery 90

Projection : exemple

24Jérôme50023Laurent40011Corinne30023Michel20021Sylvie100

ETUDIANTIne Nom Groupe Année

Quelles sont les annéesexistantes ?

ETUDIANT

R

Année

R

21

Année

Michel Tuffery 91

Union

• Définition– Soient R1[a1,..., an] et R2[b1,..., bn] deux relations de

même schéma , – Le résultat de l’union R contient les tuples de R1 et les

tuples de R2 qui n’appartiennent pas à R1 – Opérateur commutatif


R1 R2U

R1 R2

R

U

Michel Tuffery 92

Union : Exemple

CLIENTS_TOULOUSE (IdCli, nom, adresse)CLIENTS_BORDEAUX (IdClient, nomclient, chiffre)

CLIENTS_TOULOUSE CLIENTS_BORDEAUX

U

nom nomclient

R R (nom)

Contient l’ensemble des clients de l’entreprise

Michel Tuffery 93

Intersection


même schéma – Le résultat de l’intersection R contient les tuples qui

appartiennent, à la fois, à R1 et R2 – Opérateur commutatif

• Symbole utiliséR1 R2

R

∩R1 R2∩

Michel Tuffery 94

Intersection : exemple



∩

nom nomclient

R R (nom)

Contient les clients de l’entreprise à la fois, de Toulouse et Bordeaux

Michel Tuffery 95

Différence


même schéma , – Le résultat de la différence R contient les tuples de R1

qui n’appartiennent pas à R2– Opérateur non commutatif


R1 R2

R1 R2

R

Michel Tuffery 96

Différence : exemple



nom nomclient

R R (nom)

Contient les clients de Toulouse qui ne sont pas à Bordeaux

Michel Tuffery 97

Division

• Définition– Soient R1[a1,...,an,b1,...,bn] et R2[b1,...,bn] deux relations telles

que la structure de R2 soit incluse dans la structure de R1– R = résultat de la division de R1 par R2– R est une relation de structure R[a1,...,an] qui contient les

tuples ti vérifiant :ti ∈ R (ti est un tuple de structure [a1,...,an])tj ∈ R2 (tj est un tuple de structure [b1,...,bn])ti,tj ∈ R1 (ti,tj est un tuple de structure [a1,...,an,b1,...,bn])

• Opérateur non commutatif• Sert à comparer un ensemble avec un autre ensemble (dit

de référence)

Michel Tuffery 98

Division (suite)


R1 R2

RR1 de schéma S1,S2R2 de schéma S2

R de schéma S1

Michel Tuffery 99

Division : exemple

blanc4P320dBM3noir4P318iBM2blanc2P316iBM1noir2P316iBM1

VEHICULE

Codev Nomv Portes Couleur

blancnoir

BICOLORE

Coloris

S1 S2

S2

Question : Quels sont les véhicules (Nomv) fabriquées en noir ET en blanc ?

Michel Tuffery 100

Division : exemple (suite)

VEHICULE BICOLORE

Nomv

R

316i

R

Nomv

Michel Tuffery 101

Produit cartésien

• Définition– Soient R1[a1,...,an] et R2[b1,...,bm] deux relations de schéma

différent, R = résultat du produit R1× R2– R est une relation de structure R3[a1,...,an,b1,...,bm] qui contient

les combinaisons des tuples de R1 et R2

• Opérateur commutatif


R1 R1

R

×

Michel Tuffery 102

Produit cartésien : exemple

Laurent300Michel200Sylvie100

ETUDIANT

Ine Nom

MathématiquesMATHAnalyseACSIBases de DonnéesBD

MATIERE

IdM NomM

Question : Créer la relation COURS (Ine, IdM) sachant que chaque étudiantsuit toutes les matières ?

Michel Tuffery 103

Produit cartésien : exemple (suite)

ETUDIANT MATIERE

×

Ine, IdM

COURS

COURSIne IdM

MATH300ACSI300BD300MATH200ACSI200BD200MATH100ACSI100BD100

Michel Tuffery 104

Jointure

• Définition– Soient R1[a1,...,an] et R2[b1,...,bm] deux relations, R =

jointure de ces relations avec un prédicat– R[a1,...,an,b1,...,bm] contient les tuples de R1 et de R2

résultant un produit cartésien vérifiant le prédicat• Opérateur fondamental (basé sur les valeurs)• Le prédicat contient une clause de jointure et

éventuellement d’autres conditions• Opérateur binaire commutatif

Michel Tuffery 105

Jointure (suite)


R1 R1

R

Prédicat

R1 de schéma S1R2 de schéma S2

R de schéma S1,S2

Michel Tuffery 106

Jointure : exemple

CLIENTS (IdCli, Nom, Adresse)OUVRAGES (IdOuv, Titre, Nbex)EMPRUNTS (IdCli#, IdOuv#, DateEmp)

Question : Nom et Adresse des clients ayant emprunté l’ouvrage ‘BD’ le ’10/10/2003’ ?

Michel Tuffery 107

Jointure : exemple (suite)

OUVRAGES

Titre = ‘BD’

idOuv

IdOuv= O.IdOuv ET DateEmp = ‘10/10/2003’

OUVRAGES O

idCli

IdCli=C.IdCli

CLIENTS C

Nom, Adresse

R

Michel Tuffery 108

Graphe des requêtes et SGBD

• Tous les SGBD R de type SQL mettent en œuvre un graphe de requêtes

• Oracle fournit une table (plan_table) montrant le schéma global

• Ce graphe permet d’optimiser les requêtes• Certains, comme SQL Server présentent ce

graphe sous forme graphique

Michel Tuffery 109

Exemple de graphe SQL Server

100 Michel Toulouse

200 Sylvie Toulouse

300 Laurent Toulouse

o1 BD

o2 SGBD

100 o1 2003-10-10

200 o1 2003-10-10

100 o2 2003-10-08

300 o2 2003-10-08

EMPRUNTCLIENT OUVRAGE

Question : Nom et Adresse des clients ayant emprunté l’ouvrage ‘BD’ le ’10/10/2003’ ?

SELECT c.nom,c.adresseFROM client c, emprunt e, ouvrage oWHERE o.titre=‘BD’AND o.idouv=e.idouv AND e.dateemp = ‘10/10/2003’ AND e.idcli=c.idcli

Michel Toulouse

Sylvie Toulouse

Michel Tuffery 110

Exemple de graphe SQL Server (suite)

Michel Tuffery 111

Exemple de graphe SQL Server (suite)

Le Modèle Physique des Données

Mise en œuvre d’une base de données

Michel Tuffery 113

Les 3 niveaux de Conception : termes utilisés

Clé étrangère

Clé étrangère

CléSecondaire

Clé Primaire

Clé Primaire

IdentifiantClé Primaire

ColonneAttributPropriétéRubrique

LigneTupleOccurrenceArticle

TableRelationClasseAssociation

Fichier

PhysiqueLogiqueConceptuelSGF

Michel Tuffery 114

Organisation physique des données

• Les données sont rangées dans des objets appelées ‘tables’

• L’ordre de création d’une table :CREATE TABLE employé …… ;

– crée la structure de la table dans le dictionnaire– nom et type de chaque colonne– les contraintes associées

• La table est associée à un ‘tablespace’• La table est crée avec des paramètres initiaux :

– paramètres de la commande ouparamètres du tablespace

Michel Tuffery 115

Organisation physique et logiquedes données

Logique (BD) Physique (OS)

Tables

Tablespace T1Fichier FT11

Tables

Tablespace T2

Fichier FT21

Fichier FT22

Tables

Michel Tuffery 116

Schéma conceptuel de la structure physique d’une base

Database Tablespace1 1..*

Extent1..*1

Fichier OS

Bloc

1..*1

1..*11..*1

Extent utiliséExtent Libre Segment1..* 11

1..*

Index Cluster Data Rollback Temporary

Michel Tuffery 117

Notion de Tablespace

• Unité logique de stockage des données• Un ou plusieurs fichiers• Peut être désactivé (OFF LINE) sauf celui

contenant le dictionnaire de données (système)• Allocation par défaut à l’utilisateur pour stocker

ses objets• Sauvegarde et restauration limitée • Distribution des informations sur plusieurs unités • Paramètres d’allocation par défaut pour les

données

Michel Tuffery 118

Tablespace système

• Créé à la création de la database• Contient :

– le dictionnaire de données– procédures, fonctions, packages et triggers– le rollback segment system– les tables des produits Oracle

• Peut contenir des données utilisateur(déconseillé)

• Sauvegarde obligatoire

Michel Tuffery 119

Gestion d’un Tablespace

• Création d’un tablespace

create tablespace stagiairedatafile ‘usr/oracle/dbs/tssta.ora/’size 10Mdefault storage

(initial 100Knext 10Kpctincrease 0minextents 1maxextents 100

)online ;

Michel Tuffery 120

Gestion d’un Tablespace (suite)

• Modification d’un tablespace

• Suppression d’un tablespace

alter tablespace nomadd datafile ‘spécification fichier’size taillerename datafile ancien_nom to nouveau_nomdefault storage (clauses storage)online

normal | temporary | immediate;

drop tablespace nomincluding contentscascade contraints ;

suppressiondessegments

Michel Tuffery 121

Notion de Segment

• Ensemble d’extensions logiques et de blocs physiques

• Plusieurs types de segments :– segments de données (table, index et cluster)– segment d’index– segment rollback– segment temporaire– segment de démarrage– segments libres

Michel Tuffery 122

Le segment de données : la table

• Création d’une table

• Suppression d’une table

create table nom(colonne1 type(longueur),colonne2 type(longueur),constraint nom_contrainte

type_contrainte,)pctfree valeur1pctused valeur2storage ( idem tablespace )tablespace nom_tablespacecluster nom_cluster(colonne);

%libre aux insertions

drop table nomcascade contraints ;

Michel Tuffery 123

Le segment de données : la tableLes contraintes

• Contraintes de tables– - unique– - primary key– - foreign key– - check

• Contraintes de colonnes- not null

• Mises à la création de la table– - dans le dictionnaire de données– - nom de contrainte

Michel Tuffery 124

Le segment de données : la tableLes contraintes (suite)

• Contraintes ajoutées

• Contraintes supprimées

• Contraintes désactivées

• Contraintes activées

alter table nomadd contraint nom_contraintetype_contrainte;

alter table nomdrop contraint nom_contrainte [cascade];

alter table nomdisable contraint nom_contrainte [cascade];

alter table nomenable contraint nom_contrainte[exceptions into nom_table_rejets];

Michel Tuffery 125

Modification de la structure une table

• Ajout d’une nouvelle colonne

• Modification d’une colonne

• Suppression d’une colonne : attention danger !

alter table nom add(colonne1 type(longueur),colonne2type(longueur)) ;

alter table nom modify(colonne1 type(longueur),colonne2 type(longueur)) ;

alter table nom drop column nom_colonne;

Michel Tuffery 126

Format d’un bloc de données

EN TETE DU BLOCListe des tables

(cluster)

Liste des lignes

ESPACELIBRE

DONNEES

I ème tablespace

J ème bloc

K ème ligne

ROWID (I, J, K)

Michel Tuffery 127

Le segment de données : le clusterPrincipe

• Assemblage de tables mère-fille• Dans chaque bloc : une ligne mère avec les lignes filles• La jointure est déjà faite physiquement

Bloc 1

Bloc 2

INFO, Informatique

COM, Commercial

100, Michel, Toulouse300, Sylvie, Blagnac

200, Laurent, Toulouse400, Thomas, Albi

SERVICE

EMPLOYE

Michel Tuffery 128

Cluster : création et utilisation

• Création du cluster

• Création de l’index

• Création des tables en cluster

create cluster personnel (no_ser number) size 10Kstorage (initial 100Knext 50K pctincrease 10);

create index ind_person cluster personnel;

create table service(………)cluster personnel(no_ser) ;create table employe(………)cluster personnel(no_ser) ;

Michel Tuffery 129

Le segment de données :Les index

• Index unique ou non• Optimisation des recherches• Unicité de la clé• Organisés en B-Arbre• Création d’un index

• Suppression d’un index

create [unique] index nom_fichieron nom_table (colonne1[,colonne2..])tablespace nom_ts storage (clauses) ;

drop index nom_fichier ;

Michel Tuffery 130

Le segment de rollback:Le rollback segment

• Fichier ‘image avant’ pour :– les lectures consistantes – l’annulation de la transaction – les reprises ‘à chaud’

• Premier rollback segment crée dans le tablespacesystem

• Il faut en créer au moins un autre • Nombre en fonction du débit transactionnel

– nombre de transactions simultanées / 4 (<50) – utilisation automatique par oracle

Michel Tuffery 131

Le segment de rollback:Le rollback segment (suite)

• On peut orienter une transaction vers un rollback segment particulier :

• Le système utilise les extents libres du tablespace• On peut orienter une transaction vers un rollback segment

particulier• Création d’un rollback segment

– le segment doit être actif :

set transaction use rollback segmentnom_rs ;

create rollback segment nom_rstablespace nom_tsstorages (clauses) ;

alter rollback segment nom_rsonline | offlinestorage (clauses) ;

Michel Tuffery 132

Le segment temporaire:Le temporary segment

• Utilisé si la mémoire n’est pas suffisante :– jointures– sous - interrogations– create index ..– select ..order by, distinct, group by, union, intersect,

minus

• Utilisé par défaut dans le tablespace courant• Possibilité de dédier un tablespace temporaire à

un utilisateur

Les fonctions assurées par un SGBD

SécuritéIntégrité

Confidentialité

Mise en œuvre avec Oracle

Michel Tuffery 134

Les fonctions essentielles d’une base de données

• Sécurité– Reprise après incident– Journalisation des transactions

• Intégrité– Problème des accès concurrents– Verrouillage des ressources– Interblocages : détection et résolution

• Confidentialité– Droits d’accès– Sous-schémas ou schémas externes

Michel Tuffery 135

Sécurité : notion de transaction

• Suite finie d’actions portant sur des objets

• Principe du « tout ou rien »– Toutes les actions doivent être exécutées correctement– Point de confirmation ou de validation

• Condition indispensable pour assurer la cohérence de la base de données

T = < [ Ai, Oi], i=1,n>

Michel Tuffery 136

Transaction : ACID

• Atomicité– Les opérations d’une transaction sont atomiques (tout

ou rien)• Cohérence

– Passage d’un état cohérent à un autre état cohérent• Isolation

– Degré total d’isolation entre deux transactions concurrentes

• Durabilité– Effets durables même en cas de panne (persistance)

Michel Tuffery 137

Sécurité : exemple de transaction

BD : état cohérentDEBUT TRANSACTION

FIN TRANSACTION

Lire livre ‘SGBD’ (nombre_disp = 50)

Ajouter dans emprunt (client/livre)

nombre_disp = nombre_disp –1 ;

Modifier dans livre (nombre_disp)Si problème ici ????

Michel Tuffery 138

Sécurité : les vies possibles d’une transaction

• Vie sans histoire– La transaction arrive sur la fin– Point de confirmation : commit

• Un assassinat– Arrêt par un événement extérieur– Arrêt par le sgbd lui même (dead lock)– Le système fait marche arrière (rollback) et annule les

actions déjà effectuées• Un suicide

– Arrêt et annulation par la transaction elle même (rollback)

Michel Tuffery 139

Sécurité : journalisation des transactions

• Principe général– Conservation de la trace des opérations sur la base

dans un fichier– Plusieurs copies physiquement séparées

• Les informations enregistrées– Utilisateur, date , …– Code de l’opération (insert, update, delete)– Ancienne valeur – nouvelle valeur– commit et rollback

• Les sauvegardes sont notées

Michel Tuffery 140

Sécurité : travail et sauvegarde

• Travail normal sur la base

• Sauvegarde

Transaction 1Update …Insert …Delete …

Transaction 2Update …Insert …Delete …

SGBD

BD

JOURNAL

SGBD

BD

JOURNAL

SAUVEGARDE

SGBD

Michel Tuffery 141

Sécurité : les reprises

• Reprise « à chaud »

• Reprise « à froid » : RECOVERY

BD

JOURNAL

SGBD

SAUVEGARDE(J-1)

JOURNAL(J)

RECOVERY

BD(J)

Michel Tuffery 142

Sécurité : reconstruction de la base ou « recovery »

• Lecture arrière du journal et conservation d’informations sur les transactions

• Exemples de reprises

temps

Transactions

sauvegarde

Panne

T5

T4

T1

T3

T2

Michel Tuffery 143

Intégrité : accès concurrents et partage des ressources

• Plusieurs transactions accèdent simultanément aux mêmes ressources

• Lecture des données mémoire locale• Calcul nouvelle valeur re-écriture ! !

Transaction 1 Transaction 2

Lire nb_places(10) Lire nb_places

(10)nb_places=-5

(5) nb_places=-3(7)

Update nb_places(5)

Update nb_places(7)

État incohérent

Michel Tuffery 144

Intégrité : problème des accès concurrents

• Une solution : l’exclusion mutuelle (verrouillage)


Lire nb_placesavec verrou

(10) Lire nb_placesAvec verrou

nb_places=-5(5) ATTENTE

Update nb_places

(5)

Lire nb_placesAvec verrou

(5)

Commit

File d’attente sur les verrous

1er arrivé 1er

servi

Michel Tuffery 145

Intégrité : problème du verrouillage des ressources


Lire vol1 avec verrou Lire vol2 avec

verrouTravail

Travail

Lire vol2 avec verrou

Lire vol1 avec verrouATTENTE

ATTENTE

INTERBLOCAGE

DEAD LOCK

Michel Tuffery 146

Intégrité : résolution des interblocagesMéthode préventive

• Toutes les ressources nécessaires à la transaction sont verrouillées au départ

• Problème : cas des transactions qui ne démarrent jamais !

• Méthode peu utilisée aujourd’hui

Michel Tuffery 147

Intégrité : méthode préventive

• Exemple de situation blocage

• Le système note l’attente de T3, arrête T4 et l’annule, lance T3 et reprend éventuellement T4

T1 T2 T3 T4

Verrou R1Verrou R2

VerrouR1, R3Travail

Travail

FIN FIN

Attente VerrouR2, R3

Travail

Michel Tuffery 148

Intégrité : méthode curative

• Le SGBD permet les interblocages et les transactions peuvent verrouiller à tout moment

• Détection de l’interblocage– Consultation (péroidique ou lors d’une attente) du graphe QAQ– Détection d’un cycle

• Résolution de l’interblocage– Arrêt et annulation de la transaction la « moins lourde »

(assassinat)– Poursuite et fin de l’autre, reprise éventuelle et fin de la

transaction annulée

T1 T2

T3 T4

Michel Tuffery 149

Confidentialité dans les bases de données

• Principe : « Tout le monde ne peut pas VOIR et FAIRE n’importe quoi »

• Restriction de la VISION– Utilisation de sous – schémas ou schémas externes– Objets virtuels : les vues

• Restriction des ACTIONS– Délégation de privilèges avec l’ordre GRANT– Rôles utilisateurs– Encapsulation des données

Michel Tuffery 150

Confidentialité : principe

Identification(nom, code)

Authentification(agorithme,

voix, carte,…)

Reconnaissance de l’utilisateur

EntréeUtilisateur

Affectation d’un Rôle

(sous-schéma)salaire

update oui

Autorisationsd’accès

Michel Tuffery 151

Gestion des accès concurrents avec Oracle :Lecture consistante au niveau ligne

• Même vision des données du début à la fin d’une interrogation

• Vue fixe ou « cliché » pour l’interrogation (utilisation des rollback segment)

• Exemple : montant des soldes des comptes de dépôt avec de nombreuses transactions de virement de comptes :

SELECT sum(solde) INTO somme FROM comptes ;

Michel Tuffery 152

Lecture consistante multi version

• Problème : plusieurs lectures consistantes• Oracle permet de conserver un cliché pour plusieurs lectures :

• SET TRANSACTION READ ONLY ;

• Exemple:

EXEC SQL set transaction read only ;

Lire code_uer

Tant_que code_uer # ‘fin’ Faire

EXEC SQL select count(*) INTO n

FROM etudiant WHERE uer= :code_uer ;

Afficher(n,’étudiants de’,code_uer)

Fin_tant_que

EXEC SQL commit ;

Les changements d’uerne sont pas « vus »durant les interrogations

Michel Tuffery 153

Points de confirmation implicites

• Dans les versions antérieures, Oracle exécutait un rollback complet de la transaction sitôt un problème rencontré

• Impossible de continuer la transaction• Aujourd’hui, Oracle pose des points de

confirmations implicites à chaque action : « statement level rollback »

• L’utilisateur peut donc choisir :– Effectuer lui même un rollback– Tenter de poursuivre la transaction

Michel Tuffery 154

Points de confirmation explicites

• Possibilité de poser des points de confirmation (pas de validation) explicitesEXEC SQL savepoint nom_save_point ;

• Possibilité de défaire jusqu’au point de confirmation • Exemple :

EXEC SQL insert into employe values(…) ;

EXEC SQL savepoint apres_insert ;

EXEC SQL delete from employe where … ;

IF %rowcount>50 THEN

EXEC SQL rollback to apres_insert ;

EXEC SQL commit ;

ELSE

EXEC SQL commit ;

END IF ;

Michel Tuffery 155

Modification en accès concurrent : modification sans verrouillage

• Modifications sans précaution : attention au problème des transactions concurrentes

• EXEC SQL update employe set salaire=salaire*1.1 where catégorie= :cat ;

– Lectures simultanées – Écritures avec écrasement (et verrouillage)

• Éviter dans un contexte transactionnel concurrent

Michel Tuffery 156

Modification en accès concurrent : modification avec verrouillage préventif

• Les lignes sélectionnées sont verrouillées préventivement

• Utilisation d’un cursor : le verrouillage est effectif à la fin de l’ouverture (sélection des lignes)

• EXEC SQL declare cursor c1 for select nom,salaire from employe where catégorie= :cat for update of salaire ;

• Les lignes sélectionnées sont chargées en SGA puis verrouillées

• EXEC SQL update employe set salaire=salaire*1.1 where current of c1 ;

Michel Tuffery 157

Les différents types de verrous

• Les verrous DDL (Data Dictionary Lokcs)– Le système interdit toute opération pendant une modification

structurelle– A l’inverse : le système interdit toute modification structurelle

si un utilisateur travaille sur la table• Trois types de verrous

– Exclusif : posé sur l’objet si aucun autre verrou (DDL ou DML) – Partagé : posé sur l’objet si l’un des ordres suivants est

utilisé : audit, noaudit, grant, revoke, comment, create table,create view, create synonym

– « Parsing » : posé chaque objet référencé dans un ordre SQL et utilisé pour déterminer si l’ordre SQL dans la zone de partage n’est pas obsolète (changement structure)

bases de donnees et sgbd - miage.ups-tlse.fr · sgbd relationnels standard sql sgbd objet sgbd...

Documents