BASES DE DONNEES et SGBD

BASES DE DONNEES
et SGBD
Conception
Structure
Organisation
Michel Tuffery
Bases de Données
Système de Gestion
de Bases de Données
Présentation
Historique des Systèmes de Gestion de Bases
de Données (SGBD)
SGBD
IMS IDMS
SOCRATE
1965
Langages
Assembleur
Fortran
Cobol
PL1
SGBD
Propriétaires
(hiérarchiques
ou réseaux)
System R DB2 INGRES
O2 ONTOS ORACLE INFORMIX
ORACLE INFORMIX ORION
(OR)
1975
1985
Pascal
C
2005
1995
C++
SGBD relationnels
Standard
SQL
Michel Tuffery
JAVA
SGBD
Objet
SGBD
Objet-Relationnel
3
BD et SGBD : Définitions
BD
Une Base de Données est un ensemble structuré
d’informations enregistrées mis à la disposition de
manière sélective à un ensemble d’utilisateurs
SGBD
Un 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
4
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
5
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
6
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
7
SGBD Hiérarchiques : présentation
ENSEIGNANT
CodeEns
Enom
Egrade
1
N
MATIERE
CodeMat
Mnom
Mcoeff
Mheures
Association 1-N
EMPLOYE
PROJET
CodeEmp
nomEmp
Salaire
CodeProjet
1
1
N
PROJET
N
EMPLOYE
CodeProjet
BudgetProj
CodeEmp
nomEmp
Association N-N
(par duplication !)
Michel Tuffery
8
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
9
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
FOURNISSEUR
PIECE
CodeFour
CodePiece
nomFour
Libelle
RECORD
VilleFour
Couleur
1
et de SET
1
FOUR-VEN
PIECE-VEN
N
N
VENDRE
PrixUnitaire
Qte
DelaiMinimum
Michel Tuffery
RECORD
SET
10
SGBD réseaux : structure physique
Tête d’anneau (OWNER)
MEMBER
RECORD Père
RECORDs Fils
Michel Tuffery
11
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
12
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
13
BD Relationnelle :
structure et accès aux données
UV
IdUV NomUV
Etudiant
IdEtu Nom
Adresse
100 Jean
Tlse
200 Michel
Paris
300 Sylvie
Tlse
SELECT u.nomuv, u.valeur
c.note
FROM etudiant e, uv u,
cours c
WHERE e.idetu=c.idetu
AND c.iduv=u.iduv
AND e.nom=‘Jean’
Michel Tuffery
Valeur
Bd
Bases de D.
5
C
Langage C
3
Or
oracle
8
Cours
IdEtu
IdUV
Note
100
Bd
12
100
C
15
100
Or
8
300
Bd
12
200
Bd
14
300
Or
13
14
Structure d’une BD et
d’un SGBD
Concepts généraux
Exemple avec Oracle
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
16
Le Dictionnaire des données :
Organisation générale
Dictionnaire de Données
SGBD
Base de Données
Michel Tuffery
17
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
18
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
19
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
20
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
21
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
22
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
23
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
24
Base de Données en architecture
Client-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
25
Base de données en Client-Serveur
Les composants du Client
Interface graphique extérieure
ODBC
Interface graphique de la base
SQL*Net
TCP/IP
Transport / Communication
Michel Tuffery
26
Base de données en Client-Serveur
Les composants du Serveur
SGBD
BD
Listener
SQL*NET
TCP/IP
Transport / Communication
Michel Tuffery
27
Bases de Données et Intranet
Client léger
navigateur
URL
Serveur Web
Pages.html
HTML
SGBD
Objets
Multi-média
HTTP
Pages HTML
dynamiques
Serveur Applicatif
Serveur de Données
connexion
requêtes
Exécution
du
programme
Michel Tuffery
résultats
Base de Données
28
Conception d’une
Base de Données
Le Diagramme de Classes
UML
Conception d’une BD : les niveaux
•
•
•
•
schéma
conceptuel
schéma
logique
conceptuel
logique
physique
externe
Utilisateurs
schéma
physique
R1
R2
Michel Tuffery
30
Conception d’une BD : les étapes
Produits
Monde Extérieur
idPro
désignation
poids
Modélisation
(UML)
<< Association >>
1
*
Fournir
Fournisseurs
1..*
1
prixAchat
idFour
raisonSociale
adresse
Schéma Conceptuel : DC
Règles de
passage
Produits (IdPro, désignation, poids)
Normalisation ?
Fournir(IdPro, IdFour, PrixAchat)
Fournisseurs(IdFour, RaisonSociale, adresse)
Mise en œuvre
de la BD
Schéma Logique ou Relationnel
Schéma Physique
Michel Tuffery
31
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
32
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
Minimum, Maximum
Unicité
Multiplicité
Optionnel
Obligatoire
0..1
1..1 ou 1
0..* ou *
1..*
Michel Tuffery
33
Représentation des classes
Étudiant
Nom de la Classe
IneEtudiant
NomEtudiant
AdrEtudiant
Moyenne()
Modules_obtenus()
Inscription_correcte
Nombre_inscrits_dip
Identifiant
Attributs
Méthodes
Contraintes
Michel Tuffery
Procédures
Stockées
Déclencheurs
34
Associations de type Mère-Fille (1-N)
Etudiant
Diplome
IdDip
NomDip
RespDip
Inscrire
1
*
IneEtudiant
NomEtudiant
AdrEtudiant
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.
Michel Tuffery
35
Associations de type multiple (N-N)
sans attributs
Etudiant
IneEtudiant
NomEtudiant
AdrEtudiant
Stage
Choisir
*
1..*
IdStage
ThémeStage
Entreprise
RespStage
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
36
Associations de type multiple (N-N)
avec attributs
Fournisseur
IdFour
NomFour
AdrFour
Produit
*
1..*
IdProduit
NomProduit
QtéStock
Fournir
Classe d’association
Prix
DélaiLivraison
QuantitéMin
Michel Tuffery
37
Associations de type multiple (N-N)
N aires (N>2)
Conditionnement
IdCond
LibCond
Fournisseur
IdFour
NomFour
AdrFour
Produit
*
1..*
*
IdProduit
NomProduit
QtéStock
Fournir
Prix
DélaiLivraison
QuantitéMin
Michel Tuffery
38
Associations de type symétrique (1-1)
Etudiant
IneEtudiant
NomEtudiant
AdrEtudiant
Stage
Effectuer
0..1
1
IdStage
ThémeStage
Entreprise
RespStage
Note stage
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
39
Associations réflexives
Æ 1-N réflexif
0..1
Est responsable de
Enseignant
IdEnseignant
NomEnseignant
Fonction
indice
*
A pour responsable Æ
Michel Tuffery
40
Associations réflexives
Æ N-N réflexif
*
Est composé de
Produit
Composer
IdProduit
LibelléProduit
Prix
Quantité
*
Entre dans la composition de
Michel Tuffery
41
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
42
Reprise d’une classe d’association
Associations de type 1-N et N-N
Classes
stables
Joueur
IdLicence
NomJoueur
Classement
Amende
IdAmende
Montant
Tournoi
*
*
Recevoir
*
*
Participer
Résultat
IdTournoi
NomTournoi
Ville
Date
Obtenir
*
0..1
Association 1-N
Association N-N
sans attribut
Prix
IdPrix
Montant
(La classe d’association ‘Participer’ est transformée en classe normale)
Michel Tuffery
43
Associations d’agrégation
et de composition
UV
Etudiant
IneEtudiant
Nom
1..8 IdUV
*
IntUV
Inscrire
Note
Résultat
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
44
Généralisation et Spécialisation
Personnel
IdPers
Nom
Indice
Ingénieur
Enseignant
Chercheur
Grade
Fonction
Domaine
Section
Titre
HeuresCours
Spécialité
Laboratoire
Michel Tuffery
45
Conception d’une
Base de Données
Le Modèle Logique
Conception d’une BD : les niveaux
•
•
•
•
schéma
conceptuel
schéma
logique
conceptuel
logique
physique
externe
Utilisateurs
schéma
physique
R1
R2
Michel Tuffery
47
Conception d’une BD : les étapes
Produits
Monde Extérieur
idPro
désignation
poids
Modélisation
(UML)
<< Association >>
1
*
Fournir
Fournisseurs
1..*
1
prixAchat
idFour
raisonSociale
adresse
Schéma Conceptuel : DC
Règles de
passage
Produits (IdPro, désignation, poids)
Normalisation ?
Fournir(IdPro, IdFour, PrixAchat)
Fournisseurs(IdFour, RaisonSociale, adresse)
Mise en œuvre
de la BD
Schéma Logique ou Relationnel
Schéma Physique
Michel Tuffery
48
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
49
Schéma relationnel
Concepts et Terminologie
• Relation : nom de la structure tabulaire
regroupant 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
50
Schéma relationnel
Exemple
SERVICE
IdSer NomSer
EMPLOYE
NombreEmp
IdEmp NomEmp IdSer IdEmp_Resp
10 Informatique 2
100
Michel
20
300
20 Achats
200
Sylvie
10
400
300
Bernard 20
------
400
Claude
------
500
Thomas 10
3
10
400
Clés Primaires
Clé Candidate
Michel Tuffery
Clés Étrangères
51
Schéma relationnel
Quelques 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
52
Passage du S. Conceptuel au S. Relationnel
Les 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
53
Association 1-N : Exemple
Etudiant
Diplome
IdDip
NomDip
RespDip
Inscrire
1
*
IneEtudiant
NomEtudiant
AdrEtudiant
DIPLÔME (IdDip, NomDip, RespDip)
ETUDIANT (IneEtudiant, NomEtudiant, AdrEtudiant, IdDip#)
Michel Tuffery
54
Association N-N sans attribut : Exemple
Etudiant
IneEtudiant
NomEtudiant
AdrEtudiant
Stage
Choisir
*
1..*
IdStage
ThémeStage
Entreprise
RespStage
ETUDIANT (IneEtudiant, NomEtudiant, AdrEtudiant)
CHOISIR (IneEtudiant#, IdStage# )
STAGE (IdStage, Thèmestage, Entreprise, RespStage)
Michel Tuffery
55
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)
ETUDIANT (IneEtudiant, NomEtudiant, AdrEtudiant)
CHOISIR (IneEtudiant#, IdStage# )
STAGE (IdStage, Thèmestage, Entreprise, RespStage)
Michel Tuffery
56
Association N-N avec attributs : Exemple
Fournisseur
IdFour
NomFour
AdrFour
*
1..*
Fournir
Produit
IdProduit
NomProduit
QtéStock
Prix
DélaiLiv
QtéMin
FOURNISSEUR(IdFour, NomFour, AdrFour)
FOURNIR (IdFour #, IdProduit#, Prix, DélaiLiv, QtéMin )
PRODUIT (IdProduit, NomProduit, QtéStock)
Michel Tuffery
57
Association de type N-aires : Exemple
Conditionnement
IdCond
LibCond
Fournisseur
IdFour
NomFour
AdrFour
Produit
*
1..*
*
IdProduit
NomProduit
QtéStock
Fournir
Prix
DélaiLiv
QtéMin
Michel Tuffery
58
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
59
Association 1-N réflexive : Exemple
0..1
Est responsable de
Enseignant
IdEns
NomEns
Fonction
*
A pour responsable Æ
(nul autorisé)
ENSEIGNANT (IdEns, NomEns, Fonction, IdEns_Resp#)
Michel Tuffery
60
Association N-N réflexive : Exemple
*
Est composé de
Produit
Composer
IdProduit
LibProduit
Prix
*
Qté
Entre dans la composition de
PRODUIT (IdProduit, LibProduit, Prix)
COMPOSER (IdProduitComposé#, IdProduitComposant#, Qté)
Michel Tuffery
61
Reprise de classe d’association : Exemple
Tournoi
Joueur
IdLicence
NomJoueur
Classement
Amende
IdAmende
Montant
*
*
Recevoir
*
*
Participer
Résultat
Michel Tuffery
IdTournoi
NomTournoi
Ville
Date
Obtenir
*
0..1
Prix
IdPrix
Montant
62
Reprise de classe d’association : Exemple
(nul autorisé)
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)#)
2 clés étrangères
Michel Tuffery
63
Associations de type symétrique (1-1)
Règle N°4 : 3 cas possibles
C1
IdC1
Association
1
1
C2
IdC2
UNIQUE
C1 (IdC1, ……., IdC2#)
Valeurs nulles
autorisées ou pas
selon les valeurs
minimums
C2 (IdC2, ………..)
C1 (IdC1, …….)
C2 (IdC2, ……….., IdC1#)
Dans le cas d’une
association
optionnelle
C1 (IdC1, …….)
C2 (IdC2, ………..)
Association (IdC1#, IdC2#)
Michel Tuffery
64
Associations de type 1-1 : Exemple
Etudiant
IneEtudiant
NomEtudiant
AdrEtudiant
Stage
Effectuer
0..1
0..1
IdStage
ThémeStage
Entreprise
ETUDIANT (IneEtudiant, NomEtudiant, AdrEtudiant)
STAGE (IdStage, ThèmeStage, Entreprise)
EFFECTUER (IdStage#, IdEtudiant#)
UNIQUE
Michel Tuffery
65
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
66
Généralisation et Spécialisation : Exemple
Personnel
IdPers
Nom
Indice
Ingénieur
Enseignant
Chercheur
Grade
Fonction
Domaine
Section
Titre
HeuresCours
Spécialité
Laboratoire
Michel Tuffery
67
Généralisation et Spécialisation : Exemple
Attribut de Spécialisation
PERSONNEL (IdPers, Nom, Indice, TypePers)
INGENIEUR (IdPers, Grade, Fonction, Domaine)
ENSEIGNANT (IdPers, Section, Titre, HeuresCours)
CHERCHEUR (IdPers, Spécialité, laboratoire)
Michel Tuffery
68
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
69
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
70
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
71
Fermeture et couverture minimale
d’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
72
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
73
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
74
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
75
Approche par Décomposition :
Les étapes de décomposition
ETUDIANT
Ine
Nom
Matières
Adresse
IdMat
LibMat
(n)
IdRespEns NomRespEns
IdPar
LibPar
Partiels (m)
Note
ETDIANT(Ine, nom, adresse, Matières(IdMat, LibMat, IdRespEns,
(n)
NomRespEns, Partiels(IdPar, LibPar, Note)))
(m)
Michel Tuffery
76
Approche par Décomposition :
Les étapes de décomposition (suite)
ONF
ETDIANT(Ine, nom, adresse, Matières(IdMat, LibMat,
(n)
IdRespEns, NomRespEns, Partiels(IdPar, LibPar, Note)))
(m)
Élimination des attributs tableaux : éclatement des relations imbriquées
1NF
ETDIANT(Ine, nom, adresse)
MATIERES(Ine,IdMat, LibMat, IdRespEns, NomRespEns)
PARTIELS(Ine,IdMat,IdPar, LibPar, Note)
Élimination des DF non élémentaires
2NF
../
Michel Tuffery
77
Approche par Décomposition :
Les étapes de décomposition (suite et fin)
ETDIANT(Ine, nom, adresse)
2NF
MATIERES(IdMat, LibMat, IdRespEns, NomRespEns)
COURS(Ine, IdMat)
(IdMat est en occurrence avec Ine)
PARTIELS(IdPar, LibPar, IdMat)
NOTES(Ine,IdPar, Note)
Élimination des DF non directes ou transitives
ETDIANT(Ine, nom, adresse)
3NF
MATIERES(IdMat, LibMat, IdRespEns#)
ENSEIGNANT(IdRespEns, NomRespEns)
4NF : OK
COURS(Ine#, IdMat#)
PARTIELS(IdPar, LibPar, IdMat#)
NOTES(Ine#,IdPar#, Note)
Michel Tuffery
78
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
79
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
80
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
81
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
82
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→x
E1 = {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]
Michel Tuffery
83
Langage
Relationnel
Graphe de
Requêtes
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
85
Opérateurs relationnels
• Opérateurs unaires
– Sélection
– Projection
• Opérateurs binaires
–
–
–
–
–
–
Union
Intersection
Opérateurs ensemblistes
Différence
Division
Produit Cartésien
jointure
Michel Tuffery
86
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
R
• Symbole utilisé
Prédicat de Sélection
R1
Michel Tuffery
87
Sélection : exemple
ETUDIANT
Ine
Nom
Groupe Année
100
Sylvie
1
2
200
Michel
3
2
300
Corinne
1
1
400
Laurent
3
2
500
Jérôme
4
2
R
Année = 2
ET
Groupe = 3
ETUDIANT
Étudiants du groupe 3
de deuxième année ?
R
Ine
Nom
Groupe Année
200 Michel
3
2
400 Laurent 3
2
Michel Tuffery
88
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
• Symbole utilisé
R
ai,...,am
R1
Michel Tuffery
89
Projection : exemple
ETUDIANT
Ine
Nom
Groupe Année
100
Sylvie
1
2
200
Michel
3
2
300
Corinne
1
1
400
Laurent
3
2
500
Jérôme
4
2
R
Année
ETUDIANT
Quelles sont les années
existantes ?
R
Année
1
2
Michel Tuffery
90
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
R
R1
U
R2
U
• Symbole utilisé
R1
Michel Tuffery
R2
91
Union : Exemple
CLIENTS_TOULOUSE (IdCli, nom, adresse)
CLIENTS_BORDEAUX (IdClient, nomclient, chiffre)
R (nom)
R
Contient l’ensemble des clients
de l’entreprise
U
nom
CLIENTS_TOULOUSE
nomclient
CLIENTS_BORDEAUX
Michel Tuffery
92
Intersection
• Définition
– Soient R1[a1,..., an] et R2[b1,..., bn] deux relations de
même schéma
– Le résultat de l’intersection R contient les tuples qui
appartiennent, à la fois, à R1 et R2
– Opérateur commutatif
R
R1
∩ R2
∩
• Symbole utilisé
R1
Michel Tuffery
R2
93
Intersection : exemple
CLIENTS_TOULOUSE (IdCli, nom, adresse)
CLIENTS_BORDEAUX (IdClient, nomclient, chiffre)
R (nom)
R
Contient les clients de l’entreprise
à la fois, de Toulouse et Bordeaux
∩
nom
CLIENTS_TOULOUSE
nomclient
CLIENTS_BORDEAUX
Michel Tuffery
94
Différence
• Définition
– Soient R1[a1,..., an] et R2[b1,..., bn] deux relations de
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
R
R1
R2
• Symbole utilisé
R1
Michel Tuffery
R2
95
Différence : exemple
CLIENTS_TOULOUSE (IdCli, nom, adresse)
CLIENTS_BORDEAUX (IdClient, nomclient, chiffre)
R (nom)
R
Contient les clients de Toulouse
qui ne sont pas à Bordeaux
nom
CLIENTS_TOULOUSE
nomclient
CLIENTS_BORDEAUX
Michel Tuffery
96
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
97
Division (suite)
• Symbole utilisé
R
R1 de schéma S1,S2
R2 de schéma S2
R de schéma S1
R1
Michel Tuffery
R2
98
Division : exemple
BICOLORE
VEHICULE
Codev Nomv
Portes
BM1
316i
2P
noir
noir
BM1
316i
2P
blanc
blanc
BM2
318i
4P
noir
BM3
320d
4P
blanc
S1
Couleur
Coloris
S2
S2
Question : Quels sont les véhicules (Nomv) fabriquées en noir ET en blanc ?
Michel Tuffery
99
Division : exemple (suite)
R
R
Nomv
316i
Nomv
VEHICULE
BICOLORE
Michel Tuffery
100
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
R
• Symbole utilisé
×
R1
Michel Tuffery
R1
101
Produit cartésien : exemple
ETUDIANT
MATIERE
Ine
Nom
IdM
100
Sylvie
BD
Bases de Données
200
Michel
ACSI
Analyse
300
Laurent
MATH
Mathématiques
NomM
Question : Créer la relation COURS (Ine, IdM) sachant que chaque étudiant
suit toutes les matières ?
Michel Tuffery
102
Produit cartésien : exemple (suite)
COURS
Ine IdM
COURS
Ine, IdM
×
ETUDIANT
MATIERE
Michel Tuffery
100
BD
100
ACSI
100
MATH
200
BD
200
ACSI
200
MATH
300
BD
300
ACSI
300
MATH
103
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
104
Jointure (suite)
• Symbole utilisé
R
R1 de schéma S1
R2 de schéma S2
Prédicat
R de schéma S1,S2
R1
Michel Tuffery
R1
105
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
106
Jointure : exemple (suite)
R
Nom, Adresse
IdCli=C.IdCli
CLIENTS C
idCli
IdOuv= O.IdOuv ET
DateEmp = ‘10/10/2003’
idOuv
OUVRAGES O
Titre = ‘BD’
OUVRAGES
Michel Tuffery
107
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
108
Exemple de graphe SQL Server
EMPRUNT
CLIENT
100 Michel Toulouse
100 o1
2003-10-10
OUVRAGE
o1 BD
200 Sylvie
Toulouse
200 o1
2003-10-10
o2 SGBD
300 Laurent Toulouse
100 o2
2003-10-08
300 o2
2003-10-08
Question : Nom et Adresse des
clients ayant emprunté l’ouvrage
‘BD’ le ’10/10/2003’ ?
Michel
Toulouse
Sylvie
Toulouse
SELECT c.nom,c.adresse
FROM client c, emprunt e, ouvrage o
WHERE o.titre=‘BD’
AND o.idouv=e.idouv
AND e.dateemp = ‘10/10/2003’
AND e.idcli=c.idcli
Michel Tuffery
109
Exemple de graphe SQL Server (suite)
Michel Tuffery
110
Exemple de graphe SQL Server (suite)
Michel Tuffery
111
Le Modèle Physique des
Données
Mise en œuvre d’une
base de données
Les 3 niveaux de Conception :
termes utilisés
SGF
Conceptuel
Logique
Physique
Fichier
Relation
Table
Article
Classe
Association
Occurrence
Tuple
Ligne
Rubrique
Propriété
Attribut
Colonne
Clé Primaire
Identifiant
Clé
Primaire
Clé
étrangère
Clé
Primaire
Clé
étrangère
Clé
Secondaire
Michel Tuffery
113
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 ou
paramètres du tablespace
Michel Tuffery
114
Organisation physique et logique
des données
Logique (BD)
Physique (OS)
Tables
Fichier FT11
Tablespace T1
Fichier FT21
Tables
Tables
Tablespace T2
Fichier FT22
Michel Tuffery
115
Schéma conceptuel de la structure physique
d’une base
Database
1
1..* Tablespace 1
1..* Fichier OS
1
1
1..*
Extent
1..*
1
1..*
Bloc
1..*
1
Extent Libre
Index
Cluster
Extent utilisé 1..*
Data
Michel Tuffery
1 Segment
Rollback
Temporary
116
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
117
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
118
Gestion d’un Tablespace
• Création d’un tablespace
create tablespace stagiaire
datafile ‘usr/oracle/dbs/tssta.ora/’
size 10M
default storage
(initial
100K
next
10K
pctincrease
0
minextents
1
maxextents
100
)
online ;
Michel Tuffery
119
Gestion d’un Tablespace (suite)
• Modification d’un tablespace
alter tablespace nom
add datafile ‘spécification fichier’
size taille
rename datafile ancien_nom to nouveau_nom
default storage (clauses storage)
online
normal | temporary | immediate;
• Suppression d’un tablespace
drop tablespace nom
including contents
cascade contraints ;
Michel Tuffery
suppression
des
segments
120
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
121
Le segment de données : la table
• Création d’une table
create table nom
(colonne1
type(longueur),
colonne2
type(longueur),
constraint nom_contrainte
type_contrainte,
)
pctfree
valeur1 %libre aux insertions
pctused
valeur2
storage
( idem tablespace )
tablespace nom_tablespace
cluster
nom_cluster(colonne);
• Suppression d’une table
drop table nom
cascade contraints ;
Michel Tuffery
122
Le segment de données : la table
Les 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
123
Le segment de données : la table
Les contraintes (suite)
•
Contraintes ajoutées
alter table nom
add contraint nom_contrainte
type_contrainte;
•
Contraintes supprimées
alter table nom
drop contraint nom_contrainte [cascade];
•
Contraintes désactivées
alter table nom
disable contraint nom_contrainte [cascade];
•
Contraintes activées
alter table nom
enable contraint nom_contrainte
[exceptions into nom_table_rejets];
Michel Tuffery
124
Modification de la structure une table
•
Ajout d’une nouvelle colonne
alter table nom add
(colonne1
type(longueur),
colonne2type(longueur)) ;
•
Modification d’une colonne
alter table nom modify
(colonne1
colonne2
•
type(longueur),
type(longueur)) ;
Suppression d’une colonne : attention danger !
alter table nom drop column nom_colonne;
Michel Tuffery
125
Format d’un bloc de données
I ème tablespace
EN TETE DU BLOC
Liste des tables
(cluster)
J ème bloc
Liste des lignes
ESPACE
LIBRE
DONNEES
K ème ligne
ROWID (I, J, K)
Michel Tuffery
126
Le segment de données : le cluster
Principe
•
•
•
Assemblage de tables mère-fille
Dans chaque bloc : une ligne mère avec les lignes filles
La jointure est déjà faite physiquement
INFO, Informatique
Bloc 1
SERVICE
100, Michel, Toulouse
300, Sylvie, Blagnac
COM, Commercial
Bloc 2
EMPLOYE
200, Laurent, Toulouse
400, Thomas, Albi
Michel Tuffery
127
Cluster : création et utilisation
•
Création du cluster
create cluster personnel
(no_ser number) size 10K
storage
(initial 100K
next 50K pctincrease 10);
•
Création de l’index
create index ind_pers
on cluster personnel;
•
Création des tables en cluster
create table service(………)
cluster personnel(no_ser) ;
create table employe(………)
cluster personnel(no_ser) ;
Michel Tuffery
128
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
create [unique] index nom_fichier
on nom_table (colonne1[,colonne2..])
tablespace nom_ts storage (clauses) ;
• Suppression d’un index
drop index nom_fichier ;
Michel Tuffery
129
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 tablespace
system
• 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
130
Le segment de rollback:
Le rollback segment (suite)
•
•
•
•
On peut orienter une transaction vers un rollback segment
particulier :
set transaction use rollback segment
nom_rs ;
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
create rollback segment nom_rs
tablespace nom_ts
storages (clauses) ;
– le segment doit être actif :
alter rollback segment nom_rs
online | offline
storage (clauses) ;
Michel Tuffery
131
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
Michel Tuffery
132
Les fonctions assurées par un
SGBD
Sécurité
Intégrité
Confidentialité
Mise en œuvre avec Oracle
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
134
Sécurité : notion de transaction
• Suite finie d’actions portant sur des objets
T = < [ Ai, Oi], i=1,n>
• 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
Michel Tuffery
135
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
136
Sécurité : exemple de transaction
BD : état cohérent
DEBUT TRANSACTION
Lire livre ‘SGBD’ (nombre_disp = 50)
Ajouter dans emprunt (client/livre)
Si problème
ici ????
nombre_disp = nombre_disp –1 ;
Modifier dans livre (nombre_disp)
FIN TRANSACTION
Michel Tuffery
137
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
138
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
139
Sécurité : travail et sauvegarde
• Travail normal sur la base
Transaction 1
Update …
Insert …
Delete …
BD
SGBD
Transaction 2
Update …
Insert …
Delete …
• Sauvegarde
JOURNAL
BD
SAUVEGARDE
SGBD
SGBD
JOURNAL
Michel Tuffery
140
Sécurité : les reprises
• Reprise « à chaud »
BD
SGBD
• Reprise « à froid » : RECOVERY
JOURNAL
(J)
JOURNAL
SAUVEGARDE
(J-1)
RECOVERY
BD
(J)
Michel Tuffery
141
Sécurité : reconstruction de la base ou
« recovery »
•
Lecture arrière du journal et conservation d’informations sur les
transactions
•
Exemples de reprises
Panne
T1
T2
Transactions
T3
T4
T5
sauvegarde
Michel Tuffery
temps
142
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)
État incohérent
Update nb_places
(7)
Michel Tuffery
143
Intégrité : problème des accès concurrents
• Une solution : l’exclusion mutuelle (verrouillage)
Transaction 1
Lire nb_places
avec verrou
(10)
File d’attente sur
les verrous
1er arrivé Æ 1er
servi
nb_places=-5
(5)
Update
nb_places
(5)
Commit
Michel Tuffery
Transaction 2
Lire nb_places
Avec verrou
ATTENTE
Lire nb_places
Avec verrou
(5)
144
Intégrité : problème du verrouillage des
ressources
Transaction 1
Lire vol1 avec
verrou
Travail
Transaction 2
Lire vol2 avec
verrou
Travail
Lire vol2 avec
verrou
ATTENTE
Lire vol1 avec
verrou
ATTENTE
DEAD LOCK
INTERBLOCAGE
Michel Tuffery
145
Intégrité : résolution des interblocages
Mé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
146
Intégrité : méthode préventive
•
Exemple de situation blocage
T1
T2
T3
T4
Verrou R1
Verrou R2
Verrou
R1, R3
Travail
Travail
FIN
•
Attente
FIN
Verrou
R2, R3
Travail
Le système note l’attente de T3, arrête T4 et l’annule, lance
T3 et reprend éventuellement T4
Michel Tuffery
147
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
T1
T2
T3
•
T4
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
Michel Tuffery
148
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
149
Confidentialité : principe
Entrée
Utilisateur
Reconnaissance de
l’utilisateur
Identification
(nom, code)
Authentification
(agorithme,
voix, carte,…)
salaire
update
Affectation d’un
Rôle
(sous-schéma)
oui
Autorisations
d’accès
Michel Tuffery
150
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
151
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
Les changements d’uer
ne sont pas « vus »
durant les interrogations
EXEC SQL commit ;
Michel Tuffery
152
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
153
Points de confirmation explicites
•
•
•
Possibilité de poser des points de confirmation (pas de validation)
explicites
EXEC 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
154
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
155
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
156
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)
Michel Tuffery
157