Fonctionnalits des SGBD Georges GARDARIN Architecture dun SGBD

Fonctionnalités des SGBD Georges GARDARIN

Architecture d'un SGBD ANALYSEUR META-BASE ANALYSEUR CONTROLEUR OPTIMISEUR Plan d'Accès EXECUTEUR Analyse syntaxique Analyse sémantique Gestion des schémas Modification de requêtes Contrôle d'intégrité Contrôle d'autorisation Ordonnancement Optimisation Élaboration d'un plan Exécution du plan Méthodes d'accès Contrôle de concurrence Atomicité des transactions BD 2

1. Description des données n SQL DDL n n Organisation de la méta-base n n L'analyseur vérifie la correction des commandes Il stocke le schéma dans la méta-base (catalogue système) Celle-ci est souvent une BD relationnelle C'est un ensemble de tables décrivant les autres (et elle même) Bases, Tables, Attributs, Domaines, Clés, . . . Vues, Contraintes, Index, . . . Commandes traitées n Create Table, Alter Table, etc. 3

Noyau de métabase n n n n SCHEMAS (CATALOG, NOMB, Créateur, Caractère_Set, …) TABLES (CATALOG, NOMB, NOMR, Type, …) DOMAINS (CATALOG, NOMB, NOMD, Type, Défaut, Contrainte, . . ) COLUMNS (CATALOG, NOMB, NOMR, NOMA, Pos, Type, …) TYPES (CATALOG, NOMB, NOM, Max. L, Precision, …) CONSTRAINTS (CATALOG, NOMB, NOMC, Type. C, Nom. R, …) USERS (NOM, …) 4

2. Manipulation des Données n L'analyseur analyse la requête n n Syntaxe SQL (rappel) n n n Analyse syntaxique selon la grammaire SQL Analyse sémantique selon la métabase Traduction en arbre d'algèbre relationnel Select <Liste de champs ou de calculs à afficher> From <Liste de relations mises en jeu> Where <Liste de prédicats à satisfaire> Group By <Groupement éventuel sur un ou plusieurs champs> Order By <Tri éventuel sur un ou plusieurs champs> Représenter les arbres algébriques des requêtes suivantes 5

Exemple de question SQL (1) n Nom et description des médicaments de type aspirine Select From Where Nom, Description Médicaments Type = ‘Aspirine’ 6

Exemple de question SQL (2) n Patients parisiens ayant effectués une visite le 15 juin Select Patients. Nom, Patients. Prénom From Patients, Visites Where Patients. Id-P = Visites. Id-P and Patients. Ville = ’Paris’ and Visites. Date = ’ 15 juin’ 7

Exemple de question SQL (3) n Dépenses effectuées par patient trié par ordre décroissant Select From Where Group. By Order. By Patients. Id-P, Patients. Nom, sum(Prix) Patients, Visites Patients. Id-P = Visites. Id-P Patients. Id-P, Patients. Nom sum(Prix) desc 8

3. Gestion des vues Les vues permettent d’implémenter l’indépendance logique en permettant de créer des objets virtuels n Vue = Question SQL stockée n Le SGBD stocke la définition et non le résultat n Exemple : la vue des patients parisiens Create View Parisiens as ( Select Nom, Prénom From Patients Where Patients. Ville = ’Paris’ ) n 9

Gestion des vues Le SGBD transforme la question sur les vues en question sur les relations de base Question Q sur des vues Gestionnaire de Vues Définition des vues Question Q’ sur les relations de base 10

Syntaxe et Exemple CREATE VIEW <nom de table> [(<nom de colonne>+)] AS <spécification de question> [WITH CHECK OPTION] n n CREATE VIEW GROSBUVEURS (NB, NOM, PRENOM) AS SELECT NB, NOM, PRENOM La clause "WITH CHECK FROM BUVEURS B, ABUS OPTION" demande la vérification du critère lors des A insertions et mises à jour via la WHERE B. NB = A. NB vue AND A. QTE > 100 n

Modification de questions Résultat B. NOM = "MARTIN" Question Vue B. NB, B. NOM, B. PRENOM A. NB A. QTE > 100 = B. NB Définition de vue BUVEURS B BOIRE A 12

4. Exécution et Optimisation n Traduction automatique des questions déclaratives en programmes procéduraux : Utilisation de l’algèbre relationnelle n Optimisation automatique des questions Utilisation de l’aspect déclaratif de SQL Gestion centralisée des chemins d'accès (index, hachages, …) Techniques d’optimisation poussées n Economie de l'astuce des programmeurs n milliers d'heures d'écriture et de maintenance de logiciels. 13

Sélection Patients Id-P Nom Prénom Ville 1 Lebea u Jacques Paris 2 Troge r Zoe Evry 3 Doe John Paris 4 Perry Paule Valento n s Id-P Nom Prénom Ville 1 Lebeau Jacques Paris 2 Troger Zoe Evry 3 Doe John Paris 4 Perry Paule Valenton Patients de la ville de Paris 14

Projection Patients Id-P Nom Prénom Ville 1 Lebea u Jacques Paris 2 Troge r Zoe Evry 3 Doe John Paris 4 Perry Paule Valento n p Id-P Nom Prénom Ville 1 Lebeau Jacques Paris 2 Troger Zoe Evry 3 Doe John Paris 4 Perry Paule Valenton Nom et prénom des patients 15

Jointure Visites Patients Id-P Nom Prénom Ville 1 Lebea u Jacques Paris Troge r Zoe 3 Doe John 4 Perry Paule 2 Id. D Id. P Id. V Date Prix 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 Evry Paris Valento n Id-P Nom Préno m 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 16

Exemple de plan d’exécution Select Patients. Nom, Patients. Prénom From Patients, Visites Where Patients. Id-P = Visites. Id-P and Patients. Ville = ’Paris’ and Visites. Date = ’ 15 juin’ Patients p s Visites 17

Plan d’exécution optimisé p p s Patients Visites p p s s Patients Visites 18

5. Intégrité Logique n Objectif : Détecter les mises à jour erronées n Contrôle sur les données élémentaires n n n Contrôle de types: ex: Nom alphabétique Contrôle de valeurs: ex: Salaire mensuel entre 5 et 50 kf Contrôle sur les relations entre les données n Relations entre données élémentaires: n n Prix de vente > Prix d'achat Relations entre objets: n Un électeur doit être inscrit sur une seule liste électorale 19

Contraintes d’intégrité n Avantages : n simplification du code des applications n sécurité renforcée par l'automatisation n mise en commun des contraintes n Nécessite : n un langage de définition de contraintes d'intégrité n la vérification automatique de ces contraintes 20

Exemples de contrainte n Contraintes d’intégrité référentielles Docteurs Id-D Nom Prénom 1 Dupo nt Pierre 2 Duran d Paul 3 Masse Jean …. …… Prescriptions Id-V Ligne Id-M Posologie Visites Id-D Id-P Id. V Date Prix 1 1 12 1 par jour 1 2 5 10 gouttes 2 1 8 2 par jour 1 2 1 15 juin 250 2 2 12 1 par jour 1 1 2 12 août 180 2 3 3 2 gouttes 2 2 3 13 juillet 350 …. …. ………… 2 3 4 1 mars 250 21

Techniques de contrôle n domaine de valeurs n n unicité de clé n n test de non existence dans l'index références inter-tables n n n vérification à la volée insertion référençante : test d'existence dans l'index suppression référencée: test de non existence (index ? ) assertion logique (quantificateurs, agrégats) n n très complexe intérêt de gérer des agrégats pré-calculés

6. Intégrité Physique n Motivations : Tolérance aux fautes n n n Objectifs : n n n 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 Assurer l'atomicité des transactions Garantir la durabilité des effets des transactions commises Moyens : n n Journalisation : Mémorisation des états successifs des données Mécanismes de reprise 23

Transaction Etat cohérent Incohérence possible. . . Etat cohérent Begin Commit Transaction Begin CEpargne = CEpargne - 3000 CCourant = CCourant + 3000 Commit T 1 24

Atomicité et Durabilité DURABILITE ATOMICITE Panne Begin CEpargne = CEpargne - 3000 CCourant = CCourant + 3000 Commit T 1 Crash disque Annuler le débit !! S’assurer que le virement a été fait ! 25

7. Partage des données BD • Accès concurrent aux mêmes données Conflits d’accès !! 26

Partage des données BD n Technique de base n n Le SGBD verrouille les données accédées pour l'utilisateur Il relâche les verrous en fin de transaction Distinction verrou en lecture et en écriture - Compatibilité lecture-lecture 27

8. Confidentialité n Objectif : Protéger les données de la BD contre des accès non autorisés n Deux niveaux : n n Connexion restreinte aux usagers répertoriés (mot de passe) Privilèges d'accès aux objets de la base n Usagers : Usager ou groupe d’usagers n Objets : Relation, Vue, autres objets (procédures, etc. ) 28

Puissance des droits SGBD Service des ressources humaines Public (internet) Employés (intranet) Id-E Nom Prénom Poste 1 Ricks Jim 5485 2 Trock Jack 1254 3 Lerich Zoe 5489 4 Doe Joe 4049 Nombre Masse d’employés Salariale 4 890 Id-E Nom Prénom Poste Adresse Ville Salaire 1 Ricks Jim 5485 ………. Paris 230 2 Trock Jack 1254 ………. Versailles 120 3 Lerich Zoe 5489 ………. Chartres 380 4 Doe Joe 4049 ………. Paris 160 29

Commandes de SQL n Attribuer autorisation n n n Retirer autorisations n n n GRANT <DROITS> ON <RELATION> TO (<SUJET>) [WITH GRANT OPTION] <DROITS > : : = ALL | [<OPERATION>]… <OPERATION> : : = SELECT| INSERT| DELETE | UPDATE | ALTER REVOKE <DROITS> ON <RELATION> FROM (<SUJET>) Remarque n REVOKE DOIT RETIRER AUSSI LES DROITS TRANSMIS

9. Principaux SGBD Les SGBD mettrent en œuvre des techniques similaires n Aujourd’hui 3 leaders : n n n Oracle, IBM, Microsoft Développements vers le e-business Site Web dynamiques n Commerce électronique Bto. C n Commerce électronique Bto. B n Support des documents (XML). . . n 31

Principaux SGBD n Les grands SGBD n n n n Oracle IBM DB 2 Microsoft SQL Server Sybase SQL Server Ingres Informix Les open sources n n My. SQL Postgre. SQL n Les SGBD personnels n n n Borland Paradox Filemaker Interbase Microsoft Access Microsoft Fox. Pro Les SGBD objets n n Objectivity Object Store Versant O 2 32

Le marché des SGBD n Parts de marché Source: Dataquest