2
Point préalable sur le planning
• Cours divisé en trois parties– Qu’est ce qu’un SGBD et comment s’en sert-on ?
• Modèle EA et modèle relationnel, SQL, intégrité, vues, droits d’accès� commun M1 Info et M1 Stat
– Comment fonctionne un SGBD ?• Stockage et indexation, évaluation de requêtes, transactions• Stockage et indexation, évaluation de requêtes, transactions� M1 Info
– Comment analyser les données ?• Entrepôts de données, fouille de données� M1 Stat
• Supports de cours– Tirage papier – Mais aussi : http://www-smis.inria.fr/~pucheral/
Mais d’abord :
Introduction et ObjectifsObjectifs
4
1. Introduction• Les volumes de données à gérer sont de plus en plus grands
– Giga (109), Tera (1012), Péta (1015), exa (1018) – octets– Google a stocké 2,5 exa-octets en 2010 (25.000 x contenu de la BNF)– Le contenu digital double tous les 18 mois (source Cisco Visual Networking Index)
• Il faut pouvoir facilement– Archiver les données sur mémoires secondaires et assurer leur résilience– Retrouver les données pertinentes à un traitement– Mettre à jour les données variant dans le temps– Mettre à jour les données variant dans le temps– Protéger la confidentialité de ces données
• Les données sont multi-formes– Données numériques, Textuelles, Multi-média (images, films,...), flux – Elles sont plus faciles à gérer quand elles sont structurées et identifiées
• Dossier de l’étudiant Franck Lefort, de l’assuré social 1233456789 …
• Qu'est-ce qu'une BD ?– Collection de données structurées reliées par des relations– Interrogeable et modifiable par des langages de haut niveau
5
Un peu d'histoire• Années 60:
– Récipients logiques de données � fichiers sur disque
– Accès séquentiel puis sur clé• Lire (Nomf, Article), Ecrire (Nomf, Article)
• Lire (Nomf, Article, Clé), Ecrire (Nomf, article, Clé)
• Années 70:– Avènement des Bases de Données Réseaux/CODASYL (BD)
– Ensemble de fichiers reliés par des pointeurs
– Langage d'interrogation par navigation
• Années 80:– Avènement des Bases de Données Relationnelles (BDR)
– Relations entre ensemble de données
– Langage d'interrogation par assertion logique
6
Chirurgie
Systèmes de fichiers Caractéristiques
Comptabilité
PsychiatrieConsultations
Problèmes
7Format des fichiers CaractéristiquesPlusieurs applications � plusieurs formats
� plusieurs langagesDupontSymptomes : yTurlututu : sqjSymptomes : yTurlututu : sddAnalyses : xxx
DupondTurlututusqjskSymptom: yyyyAnalyses xxxx
TurlututudhjsdAnalyses :xx
Problèmes� Difficultés de gestion
DuipontTurlututu : sq
SymptomyyyyAnalysesxxxx
Turlututudhjsd
Duhpon
Symptomes : yyAnalyses : xxxx
Symptomes : yy
8Redondance (données)CaractéristiquesPlusieurs applications � plusieurs formats
� plusieurs langages
Redondance de données
DupontSymptomes : yTurlututu : sqjSymptomes : yTurlututu : sddAnalyses : xxx
DupondTurlututusqjskSymptom: yyyyAnalyses xxxx
TurlututudhjsdAnalyses :xx
Problèmes� Difficultés de gestion� Incohérence des données
DuipontTurlututu : sq
SymptomyyyyAnalysesxxxx
Turlututudhjsd
Duhpon
Symptomes : yyAnalyses : xxxx
Symptomes : yy
9Interrogations CaractéristiquesPlusieurs applications � plusieurs formats
� plusieurs langages
Redondance de données
Pas de facilité d’interrogation � Question ⇒ programme
DupontSymptomes : yTurlututu : sqjSymptomes : yTurlututu : sddAnalyses : xxx
DupondTurlututusqjskSymptom: yyyyAnalyses xxxx
TurlututudhjsdAnalyses :xx C
hiru
Sof
tCom
ptaSoft
Problèmes� Difficultés de gestion� Incohérence des données� Coûts élevés� Maintenance difficile
DuipontTurlututu : sq
SymptomyyyyAnalysesxxxx
Turlututudhjsd
Duhpon
Symptomes : yyAnalyses : xxxx
Symptomes : yy
ConsultS
oft Psy
chia
Sof
t
10Pannes CaractéristiquesPlusieurs applications � plusieurs formats
� plusieurs langages
Redondance de données
Pas de facilité d’interrogation � Question ⇒ programme
A gérer dans l’application
DupontSymptomes : yTurlututu : sqjSymptomes : yTurlututu : sddAnalyses : xxx
DupondTurlututusqjskSymptom: yyyyAnalyses xxxx
TurlututudhjsdAnalyses :xx C
hiru
Sof
tCom
ptaSoft
Problèmes� Difficultés de gestion� Incohérence des données� Coûts élevés� Maintenance difficile� Risque de perte de donnéesDuipont
Turlututu : sq
SymptomyyyyAnalysesxxxx
Turlututudhjsd
Duhpon
Symptomes : yyAnalyses : xxxx
Symptomes : yy
ConsultS
oft Psy
chia
Sof
t
11Accès simultané aux données CaractéristiquesPlusieurs applications � plusieurs formats
� plusieurs langages
Redondance de données
Pas de facilité d’interrogation � Question ⇒ programme
A gérer dans l’application
DupontSymptomes : yTurlututu : sqjSymptomes : yTurlututu : sddAnalyses : xxx
DupondTurlututusqjskSymptom: yyyyAnalyses xxxx
TurlututudhjsdAnalyses :xx C
hiru
Sof
tCom
ptaSoft
Problèmes� Difficultés de gestion� Incohérence des données� Coûts élevés� Maintenance difficile� Risque de perte de données� Risque d’incohérence
DuipontTurlututu : sq
SymptomyyyyAnalysesxxxx
Turlututudhjsd
Duhpon
Symptomes : yyAnalyses : xxxx
Symptomes : yy
ConsultS
oft Psy
chia
Sof
t
12Confidentialité CaractéristiquesPlusieurs applications � plusieurs formats
� plusieurs langages
Redondance de données
Pas de facilité d’interrogation � Question ⇒ programme
A gérer dans l’application
DupontSymptomes : yTurlututu : sqjSymptomes : yTurlututu : sddAnalyses : xxx
DupondTurlututusqjskSymptom: yyyyAnalyses xxxx
TurlututudhjsdAnalyses :xx C
hiru
Sof
tCom
ptaSoft
Problèmes� Difficultés de gestion� Incohérence des données� Coûts élevés� Maintenance difficile� Risque de perte de données� Risque d’incohérence� Risque de violation
DuipontTurlututu : sq
SymptomyyyyAnalysesxxxx
Turlututudhjsd
Duhpon
Symptomes : yyAnalyses : xxxx
Symptomes : yy
ConsultS
oft Psy
chia
Sof
t
13
L’approche ‘‘Bases de données’’
• Modélisation des données � Eliminer la redondancede données
� Centraliser et organisercorrectement les données
� Plusieurs niveaux de modélisation
� Outils de conception� Outils de conception
• Logiciel «Système de Gestion de Bases de Données»�Factorisation des modules de contrôle des applications
- Interrogation, cohérence, partage, gestion de pannes, etc…
�Administration facilitée et cohérente des données
142. Objectifs des SGBD
I- Indépendance Physique
IX - Gestion de la
II- Indépendance Logique
X - Standards
III – Langage de
BDVIII - Concurrence d’accès
VII - Gestion des pannes
IX - Gestion de la confidentialité
VI - Gestion de la cohérence
V - Optimisation des questions
III – Langage de manipulation
IV - Gestion des vues
O1: Description canonique des données� Description cohérente, unique et centralisée des données
manipulées par l’ensemble des applications constituant lesystème d’information.
— Perception globale du système d'information=> augmentationduniveaud’informatisation=> augmentationduniveaud’informatisation
=> nouveaux traitements (aide à la décision, analyse de données, …)
— Factorisation de la description des données et de leurcomportement (contraintes d’intégrité …)
— Elimination de la redondance=> redondance coûteuse en place et source d’incohérence
=> redondance système reste nécessaire (fiabilité, performance, …)
16
Réel
Modèle conceptuel
• Indépendant du modèle de données
• Indépendant du
Modélisation du réel
conceptuel • Indépendant du SGBD
Modèle logique
•Dépendant du modèle de données
• Indépendant du SGBD
Codasyl Relationnel Objet XML
Modèle Physique
•Dépendant du modèle de données
•Dépendant du SGBD
• Organisation physique des données
• Structures de stockage des données
• Structures accélératrices (index)
Médecin effectue Visite
17
Réel
Modèle conceptuel
• Indépendant du modèle de données
• Indépendant du
Modèle logique
conceptuel • Indépendant du SGBD
Modèle logique
•Dépendant du modèle de données
• Indépendant du SGBD
Codasyl Relationnel Objet XML
Modèle Physique
•Dépendant du modèle de données
•Dépendant du SGBD
• Organisation physique des données
• Structures de stockage des données
• Structures accélératrices (index)
Médecin effectue Visite
18
Champs, attributs, colonnes
Champs, attributs, colonnes
Champs, attributs, colonnes
Modélisation Relationnelle (1)
Id-D Nom Prénom
Relation ou table
1 Dupont Pierre
2 Durand Paul
3 Masse Jean
…. …….. ……
Tuples, lignes ou n-uplets
Tuples, lignes ou n-uplets
Tuples, lignes ou n-uplets
Tuples, lignes ou n-uplets
19
Modélisation Relationnelle (2)
DocteursId-D Nom Prénom
1 Dupont Pierre
2 Durand Paul
3 Masse Jean
…. …….. ……
VisitesId-D Id-P Id-V Date Prix
1 2 1 15 juin 250
1 1 2 12 août 180
PrescriptionsId-V Ligne Id-M Posologie
1 1 12 1 par jour
1 2 5 10 gouttes
2 1 8 2 par jour
2 2 12 1 par jour
2 3 3 2 gouttes2 2 3 13 juillet 350
2 3 4 1 mars 250
PatientsId-P Nom Prénom Ville
1 Lebeau Jacques Paris
2 Troger Zoe Evry
3 Doe John Paris
4 Perry Paule Valenton
…. ……. ……. …….
2 3 3 2 gouttes
…. …. …. …………
MédicamentsId-M Nom Description
1 Aspegic 1000 ……………………………..
2 Fluisédal ……………………………..
3 Mucomyst ……………………………..
…. …….. ……………………………..
20
O2 - Indépendance Physique
• Indépendance des programmes d'applications vis à vis du modèle physique :
– Possibilité de modifier les structures de stockage(fichiers, index, chemins d'accès, …) sans modifier (fichiers, index, chemins d'accès, …) sans modifier les programmes;
– Ecriture des applications par des non-spécialistes des fichierset des structures de stockage;
– Meilleure portabilité des applications et indépendancevis à vis du matériel.
21
Réel
Modèle conceptuel
• Indépendant du modèle de données
• Indépendant du
Modèle physique
conceptuel • Indépendant du SGBD
Modèle logique
•Dépendant du modèle de données
• Indépendant du SGBD
Codasyl Relationnel Objet XML
Modèle Physique
•Dépendant du modèle de données
•Dépendant du SGBD
• Organisation physique des données
• Structures de stockage des données
• Structures accélératrices (index)
Médecin effectue Visite
22
O3 - Indépendance Logique
Les applications peuvent définir des vues logiquesde la BD
Gestion des médicaments Cabinet du Dr. Masse
Visites
2
1
Id -D
1 mars
15 juin
Date
250
250
Prix
4
1
Id -V
3
2
Id -P
Visites
2
1
Id -D
1 mars
15 juin
Date
250
250
Prix
4
1
Id -V
3
2
Id -P
PatientsPatients
…………….….….
5
12
Id -M
10 gouttes
1 par jour
Posologie
2
1
Ligne
1
1
Id -V
Prescription
…………….….….
5
12
Id -M
10 gouttes
1 par jour
Posologie
2
1
Ligne
1
1
Id -V
PrescriptionNombre_Médicaments
Id-M Nom Description Nombre
1 Aspegic 1000 …………………………….. 30
2 Fluisédal …………………………….. 20
… …… … ..… .
PrénomN omId-D
JeanM asse3
PaulD urand2
PierreD upont1
Docteur
… …… … ..… .
PrénomN omId-D
JeanM asse3
PaulD urand2
PierreD upont1
Docteur
V isites
2
2
1
1
Id-D
1 mars
13 juillet
12 août
15 juin
D ate
250
350
180
250
Prix
4
3
2
1
Id-V
3
2
1
2
Id-P
V isites
2
2
1
1
Id-D
1 mars
13 juillet
12 août
15 juin
D ate
250
350
180
250
Prix
4
3
2
1
Id-V
3
2
1
2
Id-P
… ….… … .… .
PaulePerry4
PrénomN omId-P
JohnD oe3
ZoeTroger2
JacquesLebeau1
Patients
… ….… … .… .
PaulePerry4
PrénomN omId-P
JohnD oe3
ZoeTroger2
JacquesLebeau1
Patients
… … … …… .… .… .
2 gouttes332
12
8
5
12
Id-M
1 par jour
2 par jour
10 gouttes
1 par jour
Posologie
2
1
2
1
L igne
2
2
1
1
Id-V
Prescription
… … … …… .… .… .
2 gouttes332
12
8
5
12
Id-M
1 par jour
2 par jour
10 gouttes
1 par jour
Posologie
2
1
2
1
L igne
2
2
1
1
Id-V
Prescription
… … … …… … … … … … … ..… … ..… .
Des criptionN omId-M
… … … …… … … … … … … ..M ucom yst3
… … … …… … … … … … … ..Fluisédal2
… … … …… … … … … … … ..A spegic 10001
M éd icam en t
… … … …… … … … … … … ..… … ..… .
Des criptionN omId-M
… … … …… … … … … … … ..M ucom yst3
… … … …… … … … … … … ..Fluisédal2
… … … …… … … … … … … ..A spegic 10001
M éd icam en t
…….…….….
PrénomNomId -P
ZoeTroger2
JacquesLebeau1
Patients
…….…….….
PrénomNomId -P
ZoeTroger2
JacquesLebeau1
Patients
……………………………..……..….
DescriptionNomId -M
……………………………..Mucomyst3
……………………………..Fluisédal2
……………………………..Aspegic 10001
Médicament
……………………………..……..….
DescriptionNomId -M
……………………………..Mucomyst3
……………………………..Fluisédal2
……………………………..Aspegic 10001
Médicament3 Mucomyst …………………………….. 230
…. …….. …………………………….. …..
23
Avantages de l’indépendance logique
• Possibilité pour chaque application d'ignorer les besoins des autres (bien que partageant la même BD).
• Possibilité d'évolution de la base de donnéessans réécriture des applications :– ajout de champs, ajout de relation, renommage de champs.– ajout de champs, ajout de relation, renommage de champs.
• Possibilité d'intégrer des applications existantessans modifier les autres.
• Possibilité de limiter les conséquences du partage : Données confidentielles.
24
O4 - Manipulation aisée
• La manipulation se fait via un langage déclaratif
– La question déclare l’objectif sans décrire la méthode
– Le langage suit une norme commune à tous les SGBD
– SQL : Structured Query Langage
• Sémantique• Sémantique– Logique du 1er ordre
• Exemple
Retrouver le nom et le n° de téléphone de tous les pédiatres
Select Nom, Tel
From Docteur
Where Specialite = ‘Pédiatre’
25
O5 – Optimisation de requêtes
• Traduction automatique des requêtes déclaratives en programmes procéduraux (composition d’opérateurs élémentaires)
• Optimisation automatique de ces programmes– Exploitation des propriétés des opérateurs élémentaires – Exploitation des propriétés des opérateurs élémentaires – Gestion centralisée des chemins d'accès (index, hachage, …)
• Economie de l'astuce des programmeurs– milliers d'heures d'écriture et de maintenance de logiciels.
• Course aux performances mesurées en transactions par seconde (TPS)sur des "benchmark" standardisés (TPC).
26
O6 - Intégrité logique des données
• Objectif : Détection automatique des mises à jour erronées
• Contrôle sur les données élémentaires – Contrôle de types: Nom alphabétique
– Contrôle de valeurs: Salaire mensuel entre 1 et 10k€
• Contrôle sur les relations entre les données– Relations entre données élémentaires : Prix de vente > Prix d'achat
– Relations entre objets : Un électeur est inscrit sur une seule liste électorale
• Avantages– simplification du code des applications– sécurité renforcée par l'automatisation– mise en commun des contraintes
O7: Confidentialité des données
— Objectif : garantir la confidentialité de certaines informations et les protéger contre la dégradation
– Dossier médical, procédé de fabrication, salaire des employés ...
— Plusieurs niveaux de protection :— Plusieurs niveaux de protection :– Authentification des usagers
– Privilèges d'accès aux objets de la base
– Chiffrement et hachage crytographique des données
— La protection peut porter sur :– Des données stockées
– Des données virtuelles (vues)
– Des programmes
28
Confidentialité des données
Service des ressources humaines
Employés(intranet)
Public(internet)
5485
PosteJim
PrénomRicks
NomId-E1
890
MasseSalariale
Nombred’employés
4
160
380
120
230
Salaire
Paris
Chartres
Versailles
Paris
Ville
4049
5489
1254
5485
Poste
Joe
Zoe
Jack
Jim
Prénom
Doe
Lerich
Trock
Ricks
Nom
……….4
AdresseId-E
……….3
……….2
……….1
4049
5489
1254
Joe
Zoe
Jack
Doe
Lerich
Trock
4
3
2
29
O8 – Tolérance aux pannes• Motivations
– Transaction Failure : Contraintes d'intégrité, abandon de l’utilisateur– System Failure : Panne de courant, Crash serveur ...– Media Failure : Perte du disque– Communication Failure : Défaillance du réseau
• Objectifs :– Assurer l'atomicité des transactions– Garantir la durabilité des effets des transactions validées
• Moyens :– Journalisation : Mémorisation des états successifs des données– Mécanismes de reprise
30
Transaction
Etat cohérent Etat cohérentIncohérence possible...
Begin Commit
TransactionTransaction
BeginCEpargne = CEpargne - 3000CCourant = CCourant + 3000
Commit T1
31
Atomicité et Durabilité
ATOMICITE
BeginCEpargne = CEpargne - 3000
Panne
DURABILITE
BeginCEpargne = CEpargne - 3000CEpargne = CEpargne - 3000
CCourant = CCourant + 3000Commit T1
���� Annuler le débit !!
CEpargne = CEpargne - 3000CCourant = CCourant + 3000
Commit T1
� S’assurer que le virement a été fait !
Crash disque
32
09 – Accès concurrents aux données
BD
• Conflits d’accès �• pertes de mises à jour
• introduction d’incohérences
• lectures non reproductibles
33
Isolation et Cohérence
BD
• Le SGBD gère les accès concurrents
� Chacun à l’impression d’être seul (Isolation)
� Cohérence conservée (Pas de maj conflictuelles)
34
O10 - Standardisation• L’approche bases de données est basée sur plusieurs
standards– Langage SQL (SQL1, SQL2, SQL3)
– Communication SQL CLI (ODBC / JDBC)
– Transactions (X/Open DTP, OSI-TP)
• Force des standards
– Portabilité des applications
– Interopérabilité des systèmes
3. Architecture de référence des SGBD
• De nombreuses architectures fonctionnelles ont été proposées
• Ces architectures dépendent souvent du modèle de données utilisé
• ANSI/X3/SPARC est une architecture de référence mais sa normalisation a échouée.
• L'architecture ANSI/X3/SPARC repose sur un concept fondamental: la distinction de 3 niveaux de schémas
Trois niveaux de schéma
Schéma Externe 1 Schéma Externe nSchéma Externe i… …
SCHEMA CONCEPTUEL
vision spécifique à une application
vision canonique globale exprimée en SCHEMA CONCEPTUEL
SCHEMA INTERNE
vision canonique globale exprimée en terme d'entités et d'associations
description physique des fichiers, des modes de stockage (séquentiel, trié,
haché) et des index
ANSI/X3/SPARC : principes
Admin.Entreprise
Admin.BD
Admin.Application
Processeurde schémaConceptuel
Processeurde schéma
Interne
Processeurde schéma
ExterneDICTIONNAIRE
Constructionde la BD
Interne
TransformateurConceptuel
Interne
TransformateurExterne
Conceptuel
Programme
d’application
Système
d’E/S
Programmeur.d’application
Exploitationde la BD
Dictionnaire et Méta-base sont synonymes
38
Architecture fonctionnelle d’un SGBD
Optimiseur
Analyseur sémantique
Analyseur syntaxique
Requête
Méta-base
Gestion deMémoire
Gestion deVerrous
Gestion desJournaux
Méthodes d’accès aux données
Opérateurs relationnels
Evaluateur de plan d’exécution
Base de données
3. Architectures distribuées des SGBD
• Une floraison de vocabulaire– BD centralisée
– BD client/serveur
– BD 3-tiers
– BD répartie
– BD hétérogène
– Cloud Computing
– …
40
Architecture centralisée• Des terminaux clients passifs
• Un réseau
• Un ordinateur central– grande puissance (‘mainframe’)
– Maintient la base et les applications
Terminaux passifs
réseauapplications
MainframeSGBD
Appli 1 Appli 2 Appli n
données
le minitel, mais aussi Google ☺Exemple d’instance de cette architecture?
41
Architecture client-serveur
Clients intelligents
Appli 1Appli 2
Appli n
• Des clients intelligents– Font tourner les applications
• Un réseau
• Un serveur de données– Maintient la base
serveur
SGBD
réseau
donnéescode
Client messagerieExemple d’instance de cette architecture?
42
Architecture 3-tiers
réseau
• Des clients concentrés sur la présentation
• Un réseau
• Un serveur d’application– Exécute le code applicatif
• Un serveur de données– Maintient la base (sur la même machine ou des
machines différentes)
Serveur
Serveurde données SGBD
donnéescode
Appli WebExemple d’instance de cette architecture?
Appli 1 Appli 2 Appli nServeur
d’application
43
Architecture répartie
Appli 1Appli 2
Appli n
• Des clients intelligents– Font tourner l’application
– Interagissent avec ‘1 SGBD’
(l’application ne voit pas que sa requête est réacheminée)
• Un réseau
• Des serveurs
SGBD 1.1
donnéescode
SGBD 1.2
donnéescode
– Une même base
– Gèrent chacun une partition
Agences d’une société Exemple d’instance de cette architecture?
44
Architecture hétérogène
Appli 1 Appli 2 Appli n
• Des clients intelligents– Interagissent avec ‘1 médiateur’
• Un médiateur– Interroge les sources
– Nettoyage, intégration, etc.
• Des sources de données– Données hétérogènes
Source 1 : SGBD
donnéescode
Source 2 : serveur Web
donnéescode
Médiateur
Kelkoo
– Données hétérogènes• Type, schéma, etc.
– Gestion des données différente…
Exemple d’instance de cette architecture?
45
Architecture en Cloud• Virtualisation de l’approche
centralisée avec une mutualisation des ressources matérielles et logicielles
• Objectif = élasticité «Pay as you go»
Terminaux
réseauyou go»
MainframeSGBD
Appli 1 Appli 2 Appli n
données
Amazon EC2Exemple d’instance de cette architecture?
46
4. Applications traditionnelles des SGBD
• OLTP (On Line Transaction Processing)– Cible des SGBD depuis leur existence
– Banques, réservation en ligne ...
– Très grand nombre de transactions en parallèle
– Transactions simples– Transactions simples
• OLAP (On Line Analytical Processing)– Entrepôts de données, DataCube, Data Mining …
– Faible nombre de transactions
– Transactions très complexes
• Mais de nouvelles classes émergent …– DB in the (very) large
– DB in the (very) small