fondements des bases de donnéesefreidoc.fr/l3/bdd/cours/cours complet...

1

Abdelkrim LAHLOU

[email protected]

Fondements desBases de données

2

Plan

� Systèmes de fichiers− Format des fichiers et redondance des données

− Interrogation et tolérance aux pannes

− Confidentialité et partage de données

� L’approche BD− Modèle relationnel

− Exemple de langage d’interrogation SQL

− Gestion de l’intégrité et des vues

− Optimisation

− Gestion des transactions et de la concurrence

− Confidentialité et droits d’accès

� Architecture des SGBD

� Perspectives

3

Gestion des donnéespar Systèmes de fichiers

4

Chirurgie

Psychiatrie

Systèmes de fichiers Caractéristiques

Comptabilité

Consultations

Problèmes

2

5Format des fichiers CaractéristiquesPlusieurs applications � plusieurs formats

� plusieurs langages

Problèmes� Difficultés de gestion

DupontSalaire : 100000

Date de naissance :15 Septembre 1968

Adresse : Paris

DupontTurlututu : sq

SymptomyyyyAnalysesxxxx

Turlututudhjsd

Dupont

Symptômes : yyAnalyses : xxxx

Symptômes : yy

DupontDate : 01/10/2003

Heure : 22h30Tupe : Apendice

Nom Bloc : C

6Redondance (données)CaractéristiquesPlusieurs applications � plusieurs formats


Redondance de données


� Incohérence des données

DupantTurlututu : sq


Turlututudhjsd

Dupond

Symptomes : yyAnalyses : xxxx

Symptomes : yy



Nom Bloc : C



Adresse : Paris

7Interrogations CaractéristiquesPlusieurs applications � plusieurs formats



Pas de facilité d’interrogation � Requête ⇒développement


� Incohérence des données� Coûts élevés

� Maintenance difficileDupantTurlututu : sq


Turlututudhjsd

Dupond


Symptomes : yy

Chi

ruS

oft

ConsultS

oft Psy

chia

Sof

t

Com

ptaSoft



Nom Bloc : C



Adresse : Paris

8



Nom Bloc : C

Pannes ??? CaractéristiquesPlusieurs applications � plusieurs formats



Pas de facilité d’interrogation � Question ⇒développement

Redondance de code



� Maintenance difficile� Gestion de pannes ???Dupant

Turlututu : sq


Turlututudhjsd

Dupond


Symptomes : yy

Chi

ruS

oft

ConsultS

oft Psy

chia

Sof

t

Com

ptaSoft



Adresse : Paris

3

9



Nom Bloc : C



Adresse : Paris

Partage de données CaractéristiquesPlusieurs applications � plusieurs formats




Redondance de code



� Maintenance difficile� Gestion de pannes ???� Partage des données ???



Turlututudhjsd

Dupond


Symptomes : yy

Chi

ruS

oft

ConsultS

oft Psy

chia

Sof

t

Com

ptaSoft

10



Adresse : Paris



Nom Bloc : C

Confidentialité CaractéristiquesPlusieurs applications � plusieurs formats




Redondance de code



� Maintenance difficile� Gestion de pannes ???� Partage des données ???

� Confidentialité ???



Turlututudhjsd

Dupond


Symptomes : yy

Chi

ruS

oft

ConsultS

oft Psy

chia

Sof

t

Com

ptaSoft

11

L’approcheBases de données

12

L’approche ‘‘SGBD’’ (1/3)

• Modélisation des données � Eliminer la redondancede données

� Centraliser et organiser correctement les données manipulées par l’ensemble des applications constituant le SI

� Plusieurs niveaux de modélisation

� Outils de conception

• Logiciel «Système de Gestion de Bases de Données»� Factorisation des modules de contrôle des applications

- Interrogation, cohérence, intégrité, partage, gestion de pannes, etc…

� Administration aisée des données

4

13

L’approche ‘‘SGBD’’ (2/3)

• Description canoniquedes données :Description cohérente, unique et centralisée des donnéesmanipulées par l’ensemble des applications constituant lesystème d’information

� Perception globale du système d’informationAugmentation du niveau d’information

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 redondanceRedondance coûteuse en place et source d’incohérence

Redondance Système reste nécessaire pour : fiabilité, performance deconsultation, disponibilité en environnement distribué ou mobile

14L’approche ‘‘SGBD’’ (3/3)

BDVIII - Concurrence d’accès

VII - Gestion des pannes

I- Indépendance Physique

IX - Gestion de la confidentialité

II- Indépendance Logique

VI - Gestion de la cohérence

V - Optimisation des requêtes

III – Langage de manipulation

IV - Gestion des vues

X - Standards

15

Réel

Modèle conceptuel

• Indépendant du modèle de données

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

Modélisation du réel

Médecin effectue Visite

16

Champs, attributs, colonnes

Champs, attributs, colonnes

Champs, attributs ou colonnes

Modèle Relationnel (1)

Id-D Nom Prénom

1 Dupont Pierre

2 Durand Paul

3 Facile Alice

…. …….. ……

Relation ou table

Tuples, lignes ou n-uplets

5

17

Modèle Relationnel (2)

DocteurId-D Nom Prénom

1 Dupont Pierre

2 Durand Paul

3 Facile Alice

…. …….. ……

VisiteId-D Id-P Id-V Date Prix

1 2 1 15 juin 25

1 1 2 12 août 18

2 2 3 13 juillet 35

2 3 4 1 mars 25

PatientId-P Nom Prénom Ville

1 Lebeau Jacques Paris

2 Troger Zoe Evry

3 Doe John Paris

4 Perry Paule Valenton

…. ……. ……. …….

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

…. …. …. …………

MédicamentId-M Nom TypeM Description

1 Aspegic 1000 Aspirine ……………

2 Pénicilline Antibiotique ……………

3 Aspirine Aspirine …………..

…. …….. …….. …………..

18

I - Indépendance Physique

• Indépendance des programmes d'applications vis àvis des structures de stockage des fichiers (Modèlephysique) :

– Description logique des données;

– Possibilité de modifier lesstructures de stockage(fichiers,index, chemins d'accès, …) sans modifier les programmes;

– Ecriture des applications par desnon spécialistes desfichiers et des structures de stockage;

– Meilleureportabilité des applications etindépendancevis àvis du matériel.

19

II - Indépendance Logique

Nombre_MédicamentsId-M Nom Description Nombre

1 Aspegic 1000 ………………………….. 30

2 Fluisédal ………………………….. 20

3 Mucomyst ………………………….. 230

…. …….. ………………………….. …..

Les applications peuvent définir des vues logiquesde la BD

……… … ..… .

PrénomNo mId-D

JeanMasse3

PaulDu rand2

PierreDu pon t1

Doc teur

……… … ..… .

PrénomNo mId-D

JeanMasse3

PaulDu rand2

PierreDu pon t1

Doc teur

Visites

2

2

1

1

Id-D

1 m ars

13 juillet

12 août

15 ju in

Date

25 0

35 0

18 0

25 0

Prix

4

3

2

1

Id-V

3

2

1

2

Id-P

Visites

2

2

1

1

Id-D

1 m ars

13 juillet

12 août

15 ju in

Date

25 0

35 0

18 0

25 0

Prix

4

3

2

1

Id-V

3

2

1

2

Id-P

… … .… … .… .

PaulePer ry4

Préno mNomId-P

Joh nDoe3

ZoeTrog er2

Jacqu esLebeau1

Patients

… … .… … .… .

PaulePer ry4

Préno mNomId-P

Joh nDoe3

ZoeTrog er2

Jacqu esLebeau1

Patients

… …… …… .… .… .

2 go uttes332

1 2

8

5

1 2

Id- M

1 p ar jo ur

2 p ar jo ur

10 gou ttes

1 p ar jo ur

Posolog ie

2

1

2

1

Lig ne

2

2

1

1

Id-V

Prescription

… …… …… .… .… .

2 go uttes332

1 2

8

5

1 2

Id- M

1 p ar jo ur

2 p ar jo ur

10 gou ttes

1 p ar jo ur

Posolog ie

2

1

2

1

Lig ne

2

2

1

1

Id-V

Prescription

… … …… … …… …… … …..…… ..… .

DescriptionNomId- M

… … …… … …… …… … …..Mucom yst3

… … …… … …… …… … …..Flu isédal2

… … …… … …… …… … …..Aspeg ic 1 0001

Médicament

… … …… … …… …… … …..…… ..… .

DescriptionNomId- M

… … …… … …… …… … …..Mucom yst3

… … …… … …… …… … …..Flu isédal2

… … …… … …… …… … …..Aspeg ic 1 0001

Médicament

Gestion des médicaments Cabinet du Dr. Dupont

Visite

2

1

Id -D

1 mars

15 juin

Date

250

250

Prix

4

1

Id -V

3

2

Id -P

2

1

Id -D

1 mars

15 juin

Date

250

250

Prix

4

1

Id -V

3

2

Id -P

…….…….….

PrénomNomId -P

ZoeTroger2

JacquesLebeau1

Patient

…….…….….

PrénomNomId -P

ZoeTroger2

JacquesLebeau1

…………….….….

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

……………………………..……..….

DescriptionNomId -M

……………………………..Mucomyst3

……………………………..Fluisédal2

……………………………..Aspegic 10001

MédicamentDescriptionNomId -M

3

2

1

20

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 non concernées :– ajout de champs, ajout de relation, renommage de champs.

• Possibilité d'intégrer des applications existantessans modifier les autres.

• Les données décrites restent virtuelles (vues) et le SGBD se charge de leur faire correspondre des données réelles.

• Possibilité de limiter les conséquences du partage : Données confidentielles.

6

21

Exemple d’indépendance logique

Id-E Nom Prénom Poste Adresse Ville Salaire1 Ricks Jim 5485 ………. Paris 2300

2 Trock Jack 1254 ………. Versailles 1200

3 Lerich Zoe 5489 ………. Chartres 3800

4 Doe Joe 4049 ………. Paris 1600

Id-E Nom Prénom Poste1 Ricks Jim 5485

2 Trock Jack 1254

3 Lerich Zoe 5489

4 Doe Joe 4049

Nombred’employés

MasseSalariale

4 8900

Service des ressources humaines

Employés(Intranet) Public

(Internet)

22

III – Langage de manipulation

• Manipulation aisée des données à partir de la seule connaissance du monde réel et de la modélisation qui en est faite

• La manipulation se fait via des primitives intégrées à un langage de programmation tel Java ou C++ ou bien en utilisant un langage de commandes déclaratif

– La requête déclare l’objectif sans décrire la méthode– Le langage suit une norme commune à tous les SGBD– SQL : Structured Query Langage

• Exemple de requête SQL :Retrouver le nom et le n° de téléphone de tous les pédiatres

Select Nom, Tel <Liste de champs ou de calculs à afficher>From Docteur <Liste de relations mises en jeu>Where Spécialité = ’Pédiatre’; <Liste de prédicats à satisfaire>

23

Exemple de requête SQL (1)

• Nomet descriptiondes médicamentsde type Aspirine

Select Nom, Description

From Médicament

Where upper(TypeM) LIKE ’ASPIRINE’ ;

24


• Patientsparisien ayant effectués une visitele 15 juin 2003

Select Patient.Nom, Patient.Prénom

From Patient, Visite

Where Patient.Id_P = Visite.Id_P

and lower(Patient.Ville) LIKE ’paris’

and Visite.Date = ’15 juin 2003’ ;

7

25


• Dépenseseffectuées par patienttrié par ordre décroissant

Select Patient.Id_P, Patient.Nom, sum(Prix)


Where Patient.Id_P = Visite.Id_P

Group By Patient.Id_P, Patient.Nom

Order By sum(Prix) desc ;

26

IV – Gestion des vues• Les vues permettent d’implémenter l’indépendance

logique en permettant de créer des objets virtuels

• Vue = Requête SQL stockée

• Le SGBD stocke la définition et non le résultat

• Exemple : la vue des patients parisiens

Create ViewParisiensas(

Select Nom, Prénom

From Patient

Where Patient.Ville = ’Paris’ ) ;

27

Gestion des vuesLe SGBD transforme la requête sur les vues en requête sur les relations de base

Requête Q sur des vues

Définition des vues

Gestionnaire de Vues

Requête Q’ sur les relations

de base

28

V – Optimisation des requêtes

• Traduction automatique des requêtes déclaratives en programmes procéduraux : � Utilisation de l’algèbre relationnelle

• Optimisation automatique des programmes (requêtes) � Utilisation de l’aspect déclaratif de SQL� Gestion centralisée des chemins d'accès (index, hachages, …)� Techniques d’optimisation poussées

• Performances mesurées en transactions par seconde (TPS) sur des benchmarks standardisés (TPC)

• Economie de l'astuce des programmeurs– milliers d'heures d'écriture et de maintenance de logiciels.

8

29

Sélection

Patients de la ville de Paris



2 Troger Zoe Evry

3 Doe John Paris




2 Troger Zoe Evry

3 Doe John Paris


σσσσ

30

Projection



2 Troger Zoe Evry

3 Doe John Paris


ππππ

Nom et prénom des patients



2 Troger Zoe Evry

3 Doe John Paris


31

JointurePatient

Id-P Nom Prénom Ville


2 Troger Zoe Evry

3 Doe John Paris



1 2 1 15 juin 250

1 1 2 12 août 180

2 2 3 13 juillet 350

2 3 4 1 mars 250

Id-P Nom Prénom Ville Id-D Id-P Id-V Date Prix

1 Lebeau Jacques Paris 1 1 2 12 août 180

2 Troger Zoe Evry 1 2 1 15 juin 250

2 Troger Zoe Evry 2 2 3 13 juillet 350

3 Doe John Paris 2 3 4 1 mars 250

Patients et leurs visites

32

Exemple de plan d’exécution

Select Patient.Nom, Patient.Prénom


Where Patient.Id-P = Visite.Id-P

and Patient.Ville = ’Paris’

and Visite.Date = ’15 juin 2003’ ;

ππππ

σσσσ

Patient Visite

Patientparisien ayant effectués une visitele 15 juin 2003

9

33

Plan d’exécution optimisé

ππππ

σσσσ

Patient Visite

ππππ

ππππσσσσ

Visite

ππππσσσσ

Patient

34

VI – Gestion de la cohérence(Intégrité Logique)

Objectif : Détection automatique des mises à jour erronées

• Contrôle sur les données élémentaires– Contrôle de types: ex: Nom alphabétique

– Contrôle de valeurs: ex: Salaire mensuel entre 10 et 45k€

• 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 doit être inscrit sur une seule liste électorale

35

Contraintes d’intégrité (2)

• Avantages :– simplification du code des applications

– sécurité renforcéepar l'automatisation

– mise en commundes contraintes

• Contraintes :– un langage de définition de contraintes d'intégrité

– la vérification automatique de ces contraintes

36

Exemples de contraintes• Contraintes d’intégrité référentielles

DocteurId-D Nom Prénom

1 Dupont Pierre

2 Durand Paul

3 Facile Alice

…. …….. ……


1 2 1 15 juin 250

1 1 2 12 août 180

2 2 3 13 juillet 350

2 3 4 1 mars 250

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

…. …. …. …………

10

37VII – Gestion des pannes(Intégrité Physique)

• Motivations : assurer la pérennité des données en présence de pannes multiples (Tolérance aux fautes)– Transaction Failure : Contraintes d'intégrité, Annulation– 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 de MAJ– Garantir la durabilité des effets des transactions validées

• Moyens :– Journalisation : Mémorisation des états successifsdes données– Mécanismes de reprise

38

Gestion des transactions

Etat cohérent Etat cohérentIncohérence possible...

Begin CommitTransaction

BeginC_Epargne = C_Epargne - 3000C_Courant = C_Courant + 3000

Commit T1

39

Propriétés ACID40

Atomicité et Durabilité

ATOMICITE

BeginC_Epargne = C_Epargne –3000C_Courant = C_Courant +3000

Commit T1

�� Annuler le débit !!

Panne

DURABILITE

BeginC_Epargne = C_Epargne–3000C_Courant = C_Courant+3000

Commit T1

� S’assurer que le virement a été fait !

Crash disque

11

41

VIII – Gestion de la concurrence (1)

BD

• Accès concurrent aux mêmes données

� Conflits d’accès !!

42

Accès concurrents aux données (2)

• Le SGBD gère les accès concurrents

� Chacun à l’impression d’être seul (Isolation)� Cohérence conservée (Verrouillage)

BD

43

Accès concurrents aux données (3)

• Assurer l’isolation des transactions, c.à.d que différentesapplications partageant les mêmes données doivent pouvoirs’ignorer et travailler de manière asynchrone.

• Le SGBD garantit la sérialisabilité des accès : l’effet d’uneexécution simultanée de transactions doit être le même quecelui d’une exécution séquentielle

<T1 || T2 … || Tn> = <T1; T2; … Tn>

• Les transactions exécutées en parallèle ne doivent pas entrer enconflit lecture-écriture ou écriture-écriture, afin d’éviter :

• des pertes de MAJ

• des introductions d’incohérence

• des lectures non reproductibles

44

IX – Gestion de la confidentialité• Objectif : Protéger les données de la BD contre des

accès non autorisés et la dégradation

• Deux niveaux :– Connexion restreinte aux usagers répertoriés(mot de passe)

– Privilègesd'accès aux objets de la base

• Usagers : Usager, groupe, rôle

• Objets : Relation, Vue, autres objets (procédures, etc.)

12

45

Puissance des droits SGBD

Id-E Nom Prénom Poste Adresse Ville Salaire1 Ricks Jim 5485 ………. Paris 230

2 Trock Jack 1254 ………. Versailles 120

3 Lerich Zoe 5489 ………. Chartres 380

4 Doe Joe 4049 ………. Paris 160

Id-E Nom Prénom Poste1 Ricks Jim 5485

2 Trock Jack 1254

3 Lerich Zoe 5489

4 Doe Joe 4049

Nombred’employés

MasseSalariale

4 890

Service des ressources humaines

Employés(intranet)

Public(internet)

46

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

– Interopérabilté

– Applications multisources…

47

Architecture des SGBD

48

Architecture fonctionnelle d’un SGBD

ANALYSEUR

TRADUCTEUR

OPTIMISEUR

EXECUTEUR

BD

META BASE

Analyse syntaxiqueAnalyse sémantiqueGestion des schémas

Modification de requêtesContrôle d’intégritéContrôle d’autorisation

OrdonnancementOptimisationElaboration de plans d’exécution

Exécution du planMéthodes d’accèsGestion des transactions

13

49

L’architecture ANSI/SPARC

Niveau Externe 1 Niveau Externe 2 Niveau Externe n

Niveau Conceptuel(logique)

Niveau Interne(physique)

définit la façon selon laquelle sontstockée les données et les méthodespour y accéder (modes de stockage :séquentiel, trié, haché et index)

définit l'arrangement des informationsau sein de la base de données

MCD (modèle conceptuel des données)ou MLD (modèle logique des données)

définit les vuesdes utilisateurs

50

Architecture des SGBD

☞ Les architectures physiques de SGBD sont très liées aumode de répartition.

— BD centralisée

— BD client/serveur

— BD client/multi-serveurs

— BD répartie

— BD hétérogène

— BD mobile

☞ Le challenge se déplace des Péta-bases aux Pico-bas es.

— Péta-bases => parallélisme et grandes mémoires

— Pico-bases => faible empreinte et forte sécurité

51

Architecture centralisée

Terminaux passifs

MainframeSGBD

Appli 1 Appli 2 Appli n

Réseau

données

52

Architecture client - serveur

Clients intelligents

ServeurSGBD

Appli 1Appli 2

Appli n

Réseau

donnéescode

14

53

Architecture client - multiserveurs

SGBD 2

donnéescode

ODBC ODBC

SGBD 1

donnéescode

Appli 1

SQL SQL

SQLSQL

54

Architecture répartie

SGBD 1

donnéescode

SGBD 2

donnéescode

Appli 1Appli 2

Appli n

55

Architecture hétérogène

Source 1 : SGBD

donnéescode

Source 2 : serveur Web

donnéescode

Appli 1 Appli 2 Appli n

Médiateur

56

Architecture mobile

Clients intelligents mobiles

serveur

SGBD

Réseau sans fil

donnéescode

Données répliquées et/ou personnelles

15

57

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

• OLAP (On Line Analytical Processing)– Entrepôts de données, DataCube, Data Mining …

– Faible nombre de transactions

– Transactions très complexes

58

Applications émergentesdes SGBD (1)

• BD et WEB– Serveurs Web dynamiques, sites marchands, portails, ...

– Plusieurs profils (OLTP, publication d’informations en ligne, hébergement de données …)

• Challenges majeurs– Gestion de données XML

– Fédération de sources de données hétérogènes

– Grilles de données (Data Grid)

– Sécurité des données en ligne

59

Applications émergentesdes SGBD (2)

• BD personnelles ou PME– Comptabilité

– Agenda, comptes bancaires, carnet d’adresses, dossiers portables

– BD embarquées sur calculateurs ultra-légers (PDA, téléphones cellulaires, cartes à puce …)

• Challenges majeurs– Gérer les données multimédia et Web

– Gérer la mobilité

– S’adapter aux contraintes matérielles du calculateur hôte

– Assurer la durabilité des données

– Assurer la confidentialité des données

60

Evolution des BD

BD d’entreprise

BD personnelles

BD ‘light’(PDA/Mobile/carte à puce)

Capacité

Prix

Nombre

16

61

Perspectives• Gestion d’objets complexes

– Objet/Relationnel– SGBD à objets

• BD et Web– Gestion de données non structurées ou semi-structurées– Architectures multi-tiers– Sécurité des données– Publish/Subscribe

• Performances– Péta-bases de données– SGBD parallèle et grandes mémoires

• Mobilité– Pico-bases de données et SGBD embarqué– Gestion de données mobiles

fondements des bases de donnéesefreidoc.fr/l3/bdd/cours/cours complet...

Documents