Cours SGBD - smis
publicité
SGBD: Systèmes de Gestion de Bases de Données Nicolas Anciaux – [email protected] Ressources web pour ce cours : www-smis.inria.fr/~anciaux/MSI/ 1 Objectifs du module • Aucun pré requis • PARTIE I: Acquérir une culture SGBD minimale… – Fondements - Conception • Approches BD • Conception : modèle E/A, modèle relationnel – Fonctionnalités SGBD • Concurrence d'accès, optimisation de requêtes, contrainte d’intégrité – Programmation SGBD • Langage SQL, • procédures stockées, triggers, API ODBC et JDBC • … pour introduire la PARTIE II: Sécurité des SGBD – Enjeux de la sécurité SGBD, menaces, législation, attaques connues – Moyens de protection • • • • Authentification, protection des communications, contrôle d'accès Chiffrement de la BD Audit des accès Projet 2 Planning des sessions (PARTIE I) Jour 1 28/09 Jour 2 30/09 Jour 3 12/10 Jour 4 18/11 Jour 5 23/11 Jour 6 30/11 – Approches fichiers vs approche BD – Conception de BD (MCD) – Modèle relationnel – Algèbre relationnelle – Conception de BD (MLD) → → Exercices de conception – cas pratique Exercices d’algèbre → → → → Installation d’Oracle Express Création d'une base de données en SQL Insertion des données en SQL, chargement massif Mise à jour – SQL (3/3) : Méthodologie SQL – Concurrence d’accès – Optimisation de requêtes → → → Interrogation des données en SQL Expérience sur la concurrence Expériences sur l’optimisation – Programmation SGBD: PL/SQL Oracle – Programmation SGBD: JDBC → → PL/SQL, procédures stockées, fonctions, package Programmation JDBC, Module applicatif Java/JDBC – Programmation SGBD: Triggers – Contraintes d’intégrité → → Définition de contraintes d’intégrité Programmation par triggers – Introduction à la sécurité des SGBD – Compétences acquises – Révisions → Examen PARTIE I – Fonctionnalités des SGBD – SQL (1/3) : LDD (create, etc.) – SQL (2/3) : LMD (insert, delete, update) – SQL (2/3 cont.) : LMD (select) 3 Modélisation SGBD 4 Plan de la journée 1. Approche SGBD – Différences avec une approche fichier – Conclusion: modélisation des données + logiciel de gestion 2. Conception SGBD – Modèle conceptuel: modèle Entités/Associations – Règles de conception 3. Exercices – Modélisation E/A sur un cas concret – Exercices d’algèbre relationnelle 5 L’approche bases de données (SGF vs. SGBD) 6 Systèmes de fichiers Comptabilité Caractéristiques Chirurgie Problèmes Consultations Psychiatrie 7 Plusieurs applications Caractéristiques Plusieurs applications Dupont Symptomes : y Turlututu : sqj Symptomes : y Turlututu : sdd Analyses : xxx Dupond plusieurs formats plusieurs langages Turlututusqjsk Symptom: yyyy Analyses xxxx Turlututudhjsd Analyses :xx Problèmes Difficultés de gestion Duhpon Duipont Symptomes : yy Analyses : xxxx Symptomyyyy Analysesxxxx Symptomes : yy Turlututudhjsd Turlututu : sq 8 Redondance (données) Caractéristiques Plusieurs applications Dupont Symptomes : y Turlututu : sqj Symptomes : y Turlututu : sdd Analyses : xxx Dupond Turlututusqjsk Symptom: yyyy Analyses xxxx plusieurs formats plusieurs langages Redondance de données Turlututudhjsd Analyses :xx Problèmes Difficultés de gestion Incohérence des données Duhpon Duipont Symptomes : yy Analyses : xxxx Symptomyyyy Analysesxxxx Symptomes : yy Turlututudhjsd Turlututu : sq 9 Interrogations Caractéristiques ComptaSoft Dupont Symptomes : y Turlututu : sqj Symptomes : y Turlututu : sdd Analyses : xxx Dupond Turlututusqjsk Symptom: yyyy Analyses xxxx Turlututudhjsd Analyses :xx ChiruSoft Plusieurs applications plusieurs formats plusieurs langages Redondance de données Pas de facilité d’interrogation Question développement Problèmes ConsultSoft Duhpon Duipont Symptomes : yy Analyses : xxxx Symptomyyyy Analysesxxxx Symptomes : yy Turlututudhjsd Turlututu : sq Difficultés de gestion Incohérence des données Coûts élevés Maintenance difficile PsychiaSoft 10 Pannes ??? Caractéristiques ComptaSoft Dupont Symptomes : y Turlututu : sqj Symptomes : y Turlututu : sdd Analyses : xxx Dupond Turlututusqjsk Symptom: yyyy Analyses xxxx Turlututudhjsd Analyses :xx ChiruSoft Plusieurs applications plusieurs formats plusieurs langages Redondance de données Pas de facilité d’interrogation Question développement Redondance de code ConsultSoft Duhpon Duipont Symptomes : yy Analyses : xxxx Symptomyyyy Analysesxxxx Symptomes : yy Turlututudhjsd Turlututu : sq PsychiaSoft Problèmes Difficultés de gestion Incohérence des données Coûts élevés Maintenance difficile Gestion de pannes ??? 11 Partage de données Caractéristiques ComptaSoft Dupont Symptomes : y Turlututu : sqj Symptomes : y Turlututu : sdd Analyses : xxx Dupond Turlututusqjsk Symptom: yyyy Analyses xxxx Turlututudhjsd Analyses :xx ChiruSoft Plusieurs applications plusieurs formats plusieurs langages Redondance de données Pas de facilité d’interrogation Question développement Redondance de code ConsultSoft Duhpon Duipont Symptomes : yy Analyses : xxxx Symptomyyyy Analysesxxxx Symptomes : yy Turlututudhjsd Turlututu : sq PsychiaSoft Problèmes Difficultés de gestion Incohérence des données Coûts élevés Maintenance difficile Gestion de pannes ??? Partage des données ??? 12 Confidentialité Caractéristiques ComptaSoft Dupont Symptomes : y Turlututu : sqj Symptomes : y Turlututu : sdd Analyses : xxx Dupond Turlututusqjsk Symptom: yyyy Analyses xxxx Turlututudhjsd Analyses :xx ChiruSoft Plusieurs applications plusieurs formats plusieurs langages Redondance de données Pas de facilité d’interrogation Question développement Redondance de code ConsultSoft Duhpon Duipont Symptomes : yy Analyses : xxxx Symptomyyyy Analysesxxxx Symptomes : yy Turlututudhjsd Turlututu : sq PsychiaSoft Problèmes Difficultés de gestion Incohérence des données Coûts élevés Maintenance difficile Gestion de pannes ??? Partage des données ??? Confidentialité ??? 13 L’approche ‘‘Bases de données’’ (1) • Modélisation des données Éliminer la redondance de données Centraliser et organiser correctement les données 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, partage, gestion de pannes, etc… Administration facilitées des données 14 L’approche ‘‘Bases de données’’ (2) I- Indépendance Physique X - Standards II- Indépendance Logique III – Langage de manipulation IX - Gestion de la confidentialité VIII - Concurrence d’accès BD IV - Gestion des vues V - Optimisation des questions VII - Gestion des pannes VI - Gestion de la cohérence 15 Conception de bases de données (Production du modèle conceptuel de données) 16 Modélisation du réel 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 Modèle Physique • Dépendant du modèle de données • Dépendant du SGBD Médecin effectue Visite Codasyl Relationnel Objet XML • Organisation physique des données • Structures de stockage des données • Structures accélératrices (index) 17 Méthode de conception ? • Plusieurs façons d’aborder la conception d’une BD – Intuition + création directe de la BD – Suivre une méthode de conception (MCDMLDMPD) • Entité/Association (E/A) ou Entity/Relationship (E/R) • Merise • UML • Suivre son intuition peut conduire à des erreurs – – – – Redondances Valeurs nulles Difficulté de gestion Impossibilité de répondre à certaines questions • Une fois la base de données crée, difficile à modifier… (cf. TP) • Les outils de conception sont une aide précieuse 18 Exemple de mauvaise conception (1) • Stocker des propriétaires de véhicule: N° Nom Prénom Ville Pays Immatriculation Marque Couleur 1 Bar Joe Paris France 125 PP 75 Renault Rouge 2 Dean Pascal Vence France 234 FF 45 Peugeot Vert 3 Ben Zoe Lyon France 324 DFT 56 Renault Rouge 4 Bar Joe Paris France 245 FT 75 Renault Jaune • Redondance des données et incohérence potentielle – Personne répétée pour chaque voiture : • ex. Si Joe Bar change de ville et qu’une seule ligne est mise à jour… – Redondance Ville/Pays : impact d’une erreur de saisie • Anomalies de mises à jour et besoin de valeurs nulles. – Comment insérer une personne sans voiture ? – Sémantique de calculs avec des valeurs nulles… – Comment supprimer la dernière voiture d'une personne ? 19 Exemple de mauvaise conception (2) • Stocker des personnes qui ont des enfants: N° Nom Prénom Ville Pays Enfant1 Enfant2 1 Bar Joe Paris France Paul Zoe 2 Dean Pascal Vence France Lili 3 Ben Zoe Lyon France Sam 4 Cat Tom Lens France Enfant3 NbEnfants 2 1 Tor Tar 3 0 • Redondance cachée : – Nombre d’enfants vs enfants • Difficulté de gestion – Comment gérer les personnes ayant plus de 3 enfants ! – Comment afficher la liste des enfants ? 20 Méthodes de conception : Exemple Merise Réel DONNEES Modèle conceptuel Modèle logique Modèle Physique TRAITEMENT MCD Quelles données ? Quelle organisation ? MCT Quels traitements ? MLD Modèle logique (e.g, relationnel) MLT Structuration en procédure MPD Création de la base de donnée MPT Description de l’architecture des traitements, algorithmes Objectif du cours : E/A, Merise, UML ? E/A light, Merise ultra-light 21 Approche proposée : orientée données 1/ Définir l’application (~MCT) – Que veut-on faire exactement, définir les sorties (états) 2/ Définir les données (~MCD) – quelles sont les données nécessaires ? Comment les organiser ? 3/ Définir les questions nécessaires pour l’application (~MLT) 4/ Validation : Est ce que la structure choisie permet de répondre aux questions ? Sinon, retour en 1/ ou 2/ 5/ Passer du MCD au MLD 6/ Définir les requêtes nécessaires pour l’application (~MPT). Normalement, le MLD doit permettre de répondre aux requêtes ? 7/ Passer du MLD au MPD GENERATION AUTOMATIQUE POSSIBLE ! 22 Déf° (1) : entité / type d’entité • Entité : représentation d’un objet du monde réel … … par rapport au problème à modéliser. Une entité peut donc être … … concrète : ex. un docteur, un médicament, un client … abstraite : ex. une visite médicale, une commande • Type d'entité : représentation d'un ensemble d'entités perçues comme similaires et ayant les mêmes caractéristiques – Ex. docteurs, patients, médicaments, clients, visites, commandes Profs Bouganim Profs Luc Profs Crenn ..... Isabelle .... Entités Nom Prénom Adresse … Type d'entité 23 Déf° (2) : association / type d’association • Association : représentation d'un lien non orienté entre plusieurs entités (qui jouent un rôle déterminé). – Ex. Un prof enseigne un cours – lien non orienté : un prof enseigne un cours un cours est enseigné par un prof. • Type d'association : représentation d'un ensemble d'associations ayant la même sémantique et les mêmes caractéristiques – Ex. enseigner • Question : quid de visite : entité ou association ??? – Un docteur visite un patient association – Un docteur effectue une visite concernant un patient – Différence ? Et clients - commandes - produits ? 24 Déf° (3) : Propriétés / Identifiants • Propriété : donnée élémentaire permettant de décrire une entité ou une association – Le nom du patient, la date de la visite, l’adresse du patient • Identifiant d’entité : Une entité est identifiée de manière unique par au moins une propriété (généralement une) – Ex. n° de sécurité sociale du patient, référence d’un produit • Identifiant d’association : il n’existe pas – On peut identifier une association par l’ensemble des identifiants des entités associées – Ex. pour enseigne : Code du prof, Code du cours 25 Exemple : Profs et cours... Cours Prof CodeProf enseigne CodeCours NomCours … NbreHeures Nom Prénom Adresse Cours Profs 1 enseigne Crenn 55 Isabelle Profs 1 Maths ..... .... 3 Lewis enseigne Cours 16 2 Info s ... Jerry .... Profs 2 Bouganim enseigne 24 Luc ..... 26 Déf°(4) : Cardinalités • Cardinalité : Exprime le nombre minimum et maximum d’association(s) par entité. Il est indiqué sur chaque arc, entre le type d’entité et le type d’association concernées • Pour un prof donné, combien d’enseignements ? – Au minimum : Min=0 Profs 0,n – Au maximum : Max=n Un prof enseigne de 0 à n cours enseigne Cours • Pour un cours donné, combien d’enseignements ? – Au minimum : 0 Cours enseigne Profs – Au maximum : 1 0,1 – Un cours est enseigné par 0 à 1 prof 27 Comment produire le MCD ? (1) • ‘‘Énoncer le réel’’ à modéliser avec des phrases Patient • Exemple pour la gestion de rendez vous – Les patients ont des rendez vous avec des médecins • Un patient peut avoir plusieurs RDV (voire aucun) 0,n • Un médecin reçoit plusieurs patients (voire aucun) 0,n 0,n À un RDV 0,n Médecin • Un patient prendre plusieurs rendez-vous avec un même médecin Ce MCD n’est pas bon ! Patient 0,n à 1,1 RDV 1,1 correspond 0,n Médecin 28 Comment produire le MCD ? (2) Patient 0,n à 1,1 RDV 1,1 correspond 0,n – Un médecin exerce dans une salle • Un médecin n’exerce que dans une seule salle 1,1 • Une salle peut être partagée par plusieurs médecins 1,n Médecin 1,1 exerce 1,n Salle 29 Comment produire le MCD ? (3) • On obtient : Patient 0,n à 1,1 RDV 1,1 correspond 0,n Médecin 1,1 • Pour connaître la salle, pour un rendez vous, on passe par le médecin exerce 1,n Salle Ce MCD est bon • Et si maintenant le médecin peut exercer dans plusieurs salles ? Patient 0,n à 1,1 RDV 1,1 correspond 0,n Médecin 1,n • Comment connaître la salle d’un rendez vous ? Ce MCD n’est pas bon ! exerce 1,n Salle 30 Comment produire le MCD ? (4) Patient à 0,n 1,1 RDV 1,1 correspond Médecin 0,n 1,1 exerce (A) (B) à 0,n 1,1 Patient a un rdv 1,1 est dans 1,n Salle (C) 1,1 correspond 0,n Médecin à 0,n a un rdv Patient 0,n Médecin 0,n 1,n Salle Différences fonctionnelles (A) (B) (C) Connaître la salle pour 1 RDV donné Non Oui Oui Non Non Oui Pré-allouer des salles aux médecins correspond 1,1 est dans 0,n Salle 1,1 1,n exerce 31 32 Règles (1) respect des règles de gestion • Il faut vérifier que le MCD correspond bien au ‘réel’, c’est à dire aux règles fixées (celles que l’application doit respecter) • par exemple: – un prof enseigne plusieurs cours – une matière est enseignée par plusieurs profs (info/anglais) – les notes peuvent être données par n’importe quel prof ou par plusieurs profs enseignant une matière... (info par exemple) – On peut redoubler une fois.... – etc... 32 Règles (2) : propriétés élémentaires, calculées, constantes 33 • Toute propriété doit être élémentaire – – – – sinon, complexité de traitement Ex. de propriétés élémentaires : Age, Salaire, N° de rue Ex. de propriétés non élémentaires : Adresse (complète), N°SS la notion d’élémentaire dépend de l’application. L’adresse peut devenir élémentaire si elle est toujours manipulée comme telle (on ne cherchera jamais à faire, par exemple, un tri par ville) «Il n’est pas gênant d’éclater des propriétés qui devrait être groupés, mais on ne peut grouper des propriétés qui devrait être éclatées» • Une propriété calculée n’est pas à stocker ! – Sinon, c’est redondant source d’incohérence • Une propriété constante n’est pas à stocker – Sinon, c’est redondant source d’incohérence – On utilisera une table de constantes ... 33 34 Règles (3) : propriétés répétitives • Pour une entité, il ne peut y avoir qu’une instance de chaque propriété de l’entité – exemple: Cours ne peut être une propriété de Prof, puisqu’un prof enseigne plusieurs cours... – Remarque : Si un prof ne peut enseigner qu’un seul cours, cours peut être une propriété de prof • Attention aux propriétés n’ayant pas le même nom mais la même sémantique – Ex. : Enfant1, Enfant2, Enfant3 (cf. Slide 16) – Ex. : différents types de notes d’un étudiant • Remarque : pour éviter d’éventuelles erreurs, on nommera différemment des propriétés de différentes entités : – Ex. NomPatient, NomDocteur, etc. – Du coup une propriété n’apparaît que dans une seule entité ou association. 34 35 Règles (4) : propriétés sans signification • Une propriété ne peut être sans signification pour une partie des entités ou associations – exemple : si un prof ne peut enseigner qu’un seul cours, mais qu’on a choisi de créer une entité ‘personnel’ et non ‘prof’, on ne stockera pas le cours dans l’entité ‘personnel’ car il serait sans signification pour une secrétaire... – contre exemple : Téléphone et Fax pour un étudiant... 35 36 Règles (5) : identifiants d’entités • Toute entité doit être identifiée ! • Un identifiant doit être pérenne. Ex. nom et prénom peut être l’identifiant de l’étudiant, mais ça peut être insuffisant. – il ne faut pas concevoir le MCD en observant les données telles qu’elles sont (ex. l’école tel qu’elle est). Il faut le concevoir pour le cas à modéliser (ex. l’école telle qu’elle peut être.... et telle que l’on se propose de la gérer.... ) • Plusieurs identifiants peuvent coexister – En choisir un… • Si vous ne trouvez aucun identifiant, votre « entité » est sans doute une association … 36 Règles (6) : dépendance pleine de l’identifiant 37 • Toute propriété doit dépendre pleinement de l’identifiant (et non d’une partie de celui-ci) – Sinon on introduit des redondances • Les propriétés d’une association doivent dépendre de la totalité des entités associées – Sinon, les déplacer, voire créer une nouvelle association… • Exemple : Prof enseigne Heure Salle Salle ? Salle Cours Groupe 1/ la salle dépend du prof, du cours et du groupe OK 2/ la salle ne dépend que du prof Not OK (dans prof) 3/ la salle ne dépend que du prof et du cours not OK (nouvelle association entre prof et cours) 37 38 Règles (7) : entités et associations • 2 entités ne peuvent être directement liées : il faut une association ! • Cependant: – Une association peut ne pas avoir de « nom » • Il est des fois difficile à trouver… – Une association peut ne pas avoir de propriété • C’est un cas très fréquent Enseigne Prof Heure Groupe ? Salle Cours 38 39 Règles (8) : pas de dépendance transitive • Une propriété ne peut dépendre d’une autre propriété qui ne soit pas l’identifiant – Permet d’éliminer des sous-entités incluses dans une entité • Exemple : – Etudiant(nom, adresse, ville, pays) – Le pays dépend de la ville or l’identifiant d’étudiant est le nom. Prof CodeProf Prof CodeProf Nom Nom Prénom Prénom 1,1 est dans 0,n Ville NomVille Pays Ville Pays 39 40 Première modélisation ‘restreinte’ • Gérer les notes des étudiants – Hypothèses : • une base de données pour chaque promo et pour chaque semestre – Traitements : • • • • Moyenne par cours pour chaque étudiant Moyenne par pôle pour chaque étudiant Moyenne générale pour chaque étudiant Moyenne par groupe, par cours. – ‘‘Énoncer le réel’’ à modéliser avec des phrases • Un cours a un nom, un coefficient, et appartient à un pôle • Un étudiant a un nom et un prénom, et appartient à un groupe • Un étudiant obtient les notes DS1, DS2, participation, examen pour les cours qu’il suit 40 41 Modèle entité-association (1/2) Etudiant N° Nom Prénom Groupe a obtenu 0,n Cours 0,n NomCours DS1 DS2 Pôle Participation Coefficient Examen Commentaires? Le schéma est simple, il répond au problème On a un minimum de données On ne peut pas faire de suivi sur une promo On ne peut pas faire de suivi par prof Pas de statistiques sur plusieurs années Problème de gestion: on aura 4 fois les mêmes programmes 41 Modèle entité-association (2/2) Etudiant N° Cours a obtenu 0,n Nom Prénom Groupe 0,n NomCours DS1 DS2 Pôle Participation Coefficient Examen TypeNote Type Coefficient 0,n Etudiant N° Nom Prénom Groupe 0,n a obtenu Note 0,n Cours NomCours Pôle Coefficient 42 43 Modélisation ‘complète’ • Gérer les notes des étudiants veut dire: – Hypothèses : • Une base de données pour l’Université (pour plusieurs années) • On veut gérer les profs pour faire des stats par profs, par promos, etc... – Problèmes : • • • • Gestion des redoublement, de la situation (actuelle) d’un étudiant Cohabitation de notes sur plusieurs années, des profs, des étudiants ?? Les matières sont enseignés par plusieurs profs, qui met les notes ?? Comment modéliser qu’un prof enseigne à un groupe de TP ? – Données : • Etudiants, Matières, Notes • TypeDeNotes, Periodes, Profs, Groupes, etc... 43 44 Modèle entité-association TypeNote Type Coefficient 1,n Etudiant N° Nom Prénom a obtenu 1,n Note Cours NomCours Pôle Coefficient 1,n 1,n 1,n Est dans 1,n Période Code Année Semestre 1,n 1,n Enseigne Nb heures 1,n 1,n Groupe Code 1,n Profs Nom Prénom Adresse 1,n 44 Exercice de conception Modèle E/A 45 Division Division NumDiv NumDiv NomDiv NomDiv EmployéEmployé Employé 1-n 1-n 1-1 Est dirigée Travaille Travaille 1-n 1-1 Travaille Fonction Fonction a la a la 1-n 1-n Fonction NumFonc NumFonc DescFonc DescFonc NumFonc Prime Prime DescFonc Prime (taux) Vente Vente 0-1 1-1 1-1 NumVente NumVente DateVente DateVente DateLivrVente DateLivr FraisPort FraisPort NumVente 1-n 1-n DateVente DateLivr FraisPort Concern Concern e e 1-1 a 0-n 1-1 SalaireEmp DateNaissEmp PrénomEmp 1-1 DateNaissEmp 1-11-1 AdresseVoie SalaireEmp 1-1 AdresseCPN°DsRueEmp Tel TelEmp 1-n DateNaissEmp 1-n Client 0-n 1-1 1-1 1-n NumCli 1-n NumCli 0-n NomCli Nom PrénomCli Prénom N°DsRueCli AdresseVoie TelCli AdresseCP Tel Livraison 0-1 CP 0-1 1-n 0-n Est2 Dans la rue1 1-n 1-n 1-n 1-1 1-1 Dans la rue2 habite 1-1 1-n 1-n 1-1 Personne dans IdPers 1-n Nom Prénom Ville N°DsRue Tel Ville 1-1 Rue 1-1 Ds IdRue dans Rue NomRue CP1-n Rue 1-1 Pays IdRue Dans NomRue Pays CP 0-1 1-n 1-n Correction de l’exercice de conception Passée Dans lahabite 0-1 NumMess Correction de l’exercice de conception rue3 1-1 1-1 NumLiv--- Nom 1-1 1-n 1-n 1-1 1-1 Prénom NomLiv --AdresseVoie PrénomLiv 1-n 1-n Dans11-n 1-n 1-1 Quelques modèles Entité Associations possibles AdresseCP N°DsRue 1-1 Est3 Ville Dans lapossibles Tel Quelques modèles Entité Associations TelLiv Livrée à Livre rue4 habite 1-1 1-1 1-n 1-n1-n Qté Qté Remise Remise Livrée Livrée parpar Produit Produit Fournisseur Fournisseur 1-1 Concerne 1-1 Qté Remise Messager Livrée à 1-1 Messager Messager 1-1 Destinataire NumMess NumMess NomMess 1-n N°DsRueMess TelMess 0-n 0-n NumProd NumProd NomProd NomProd PrixUnitaire PrixUnitaire Est1 habite Client NumCli Client Rédige Rédige Réalise Passée directement Code postal 0-1 Matricule Matricule 0-1Matricule SalaireEmp NomEmp NumDest Nom Prénom AdresseVoie 1-1 AdresseCP Tel 1-1 1-1 NumFour NumFour fourni par 1-1 NumProd 1-n NomFour NomProd 1-n PrénomFour PrixUnitaire N°DsRue TelFour 1-1 1-1 Dans Fournisseur Produit 0-n parpar fourni fourni Est4 Dans la rue5 1-n IdVille Ville NomVill habite e IdVille 1-1 NomVill e 1-n NumFour Nom Prénom AdresseVoie AdresseCP Tel Dans2 1-n 1-n Pays Pays IdPays IdPays NomPay NomPay s s 46 Modèle relationnel 47 Le modèle relationnel • En 1970, Edward Codd, Prix Turing 1981, chercheur chez IBM, propose le Modèle Relationnel, basé la Logique du premier ordre définie par les mathématiciens de la fin du 19e siècle pour formaliser le langage des mathématiques A Relational Model of Data for Large Shared Data Banks, CACM 13, No. 6, June 1970 • Il définit le Calcul Relationnel et l’Algèbre Relationnelle, sur lesquels sont basés SQL (Structured Query Language), le langage standard de manipulation (LMD) et de description des données (LDD) de tous les SGBD Relationnels actuels Théorème de Codd: une question est exprimable en calcul relationnel si et seulement si elle peut être évaluée avec une expression de l’algèbre relationnelle – de plus, il est facile de transformer une requête du calcul relationnel en une expression algébrique qui évalue cette requête. 48 Domaine • • ENSEMBLE DE VALEURS Exemples: – – – – – – ENTIER REEL CHAINES DE CARACTERES EUROS SALAIRE = {4 000..100 000} COULEUR= {BLEU, BLANC, ROUGE} 49 Produit cartésien • Le produit cartésien D1x D2x ... x Dn est l’ensemble des tuples (n-uplets) <V1,V2,....Vn> tels que ViDi • Exemple: – D1 = {Bleu,Blanc,Rouge} – D2 = {Vrai, Faux} D1x D2 = Bleu Vrai Bleu Faux Blanc Vrai Blanc Faux Rouge Vrai Rouge Faux 50 Relation, attribut • Relation = sous-ensemble du produit cartésien d’une liste de domaines – Une relation est caractérisée par un nom – Exemple: CoulVins • D1 = COULEUR • D2 = BOOLEEN • Plus simplement … Coul Bleu Choix Faux Blanc Vrai Rouge Vrai – Une relation est une table à deux dimensions – Une ligne est un tuple – Un nom est associé à chaque colonne afin de la repérer indépendamment de son numéro d'ordre • Attribut = – nom donné à une colonne d'une relation – prend ses valeurs dans un domaine 51 Exemple de relation VINS CRU MILL REGION CHENAS TOKAY TAVEL CHABLIS ST-EMILION 1983 1980 1986 1986 1987 BEAUJOLAIS ALSACE RHONE BOURGOGNE BORDELAIS COULEUR ROUGE BLANC ROSE BLANC ROUGE 52 Clé • Définition = Groupe d’attributs minimum qui détermine un tuple unique dans une relation • Exemples – {CRU,MILLESIME} DANS VINS – NSS DANS PERSONNE • Contrainte d’entité – Toute relation doit posséder au moins une clé documentée 53 Clé Etrangère • Définition = Groupe d’attributs devant apparaître comme clé dans une autre relation • Les clés étrangères définissent les contraintes d'intégrité référentielles – Lors d'une insertion, la valeur des attributs doit exister dans la relation référencée – Lors d'une suppression dans la relation référencée les tuples référençant doivent disparaître • Elles correspondent aux liens obligatoires entre les entités (modèle E/A) 54 Schéma • Schéma de relation – Définition = Nom de la relation, liste des attributs avec domaines, et clé de la relation – Exemple • VINS(NV: Int, CRU:texte, MILL:entier, DEGRE: Réel, REGION:texte) – Par convention, la clé primaire est soulignée • Intention et extension de relation – L'intention de la relation = le schéma de la relation – Une instance de table représente une extension de la relation • Schéma d’une BD relationnelle = l'ensemble des schémas des relations composantes 55 Exemple de Schéma • BUVEURS (NB, NOM, PRENOM, TYPE) • VINS (NV, CRU, MILL, DEGRE) • ABUS (NB, NV, DATE, QUANTITE) BUVEURS NB NOM PRENOM ABUS TYPE NB NV VINS DATE NV CRU MILL. DEGRE QUANTITE • CLES ETRANGERES (italique) –ABUS.NV référence VINS.NV –ABUS.NB référence BUVEURS.NB 56 Exemple de modélisation relationnelle Docteurs Prescriptions Id-D Nom Prénom 1 Dupont Pierre 2 Durand Paul 3 Masse Jean …. …….. …… Id-V Ligne Id-M Posologie 1 1 12 1 par jour Visites Id-D Id-P Id-V Date Prix 1 2 5 10 gouttes 1 2 1 15 juin 250 2 1 8 2 par jour 1 1 2 12 août 180 2 2 12 1 par jour 2 2 3 13 juillet 350 2 3 3 2 gouttes 2 3 4 1 mars 250 …. …. …. ………… Patients Médicaments Id-P Nom Prénom Ville 1 Lebeau Jacques Paris Id-M Nom Description 2 Troger Zoe Evry 1 Aspegic 1000 …………………………….. 3 Doe John Paris 2 Fluisédal …………………………….. 4 Perry Paule Valenton 3 Mucomyst …………………………….. …. ……. ……. ……. …. …….. …………………………….. 57 Clés primaires Docteurs Prescriptions Id-D Nom Prénom 1 Dupont Pierre 2 Durand Paul 3 Masse Jean …. …….. …… Id-V Ligne Id-M Posologie 1 1 12 1 par jour Visites Id-D Id-P Id-V Date Prix 1 2 5 10 gouttes 1 2 1 15 juin 250 2 1 8 2 par jour 1 1 2 12 août 180 2 2 12 1 par jour 2 2 3 13 juillet 350 2 3 3 2 gouttes 2 3 4 1 mars 250 …. …. …. ………… Patients Médicaments Id-P Nom Prénom Ville 1 Lebeau Jacques Paris Id-M Nom Description 2 Troger Zoe Evry 1 Aspegic 1000 …………………………….. 3 Doe John Paris 2 Fluisédal …………………………….. 4 Perry Paule Valenton 3 Mucomyst …………………………….. …. ……. ……. ……. …. …….. …………………………….. 58 Clés étrangères Docteurs Prescriptions Id-D Nom Prénom 1 Dupont Pierre 2 Durand Paul 3 Masse Jean …. …….. …… Id-V Ligne Id-M Posologie 1 1 12 1 par jour Visites Id-D Id-P Id-V Date Prix 1 2 5 10 gouttes 1 2 1 15 juin 250 2 1 8 2 par jour 1 1 2 12 août 180 2 2 12 1 par jour 2 2 3 13 juillet 350 2 3 3 2 gouttes 2 3 4 1 mars 250 …. …. …. ………… Patients Médicaments Id-P Nom Prénom Ville 1 Lebeau Jacques Paris Id-M Nom Description 2 Troger Zoe Evry 1 Aspegic 1000 …………………………….. 3 Doe John Paris 2 Fluisédal …………………………….. 4 Perry Paule Valenton 3 Mucomyst …………………………….. …. ……. ……. ……. …. …….. …………………………….. 59 Métabase DICTIONNAIRE DE DONNEES, ORGANISE SOUS FORME RELATIONNELLE, CONTENANT LA DESCRIPTION DES RELATIONS, ATTRIBUTS, DOMAINES, CLES, etc. RELATIONS NUM 12 14 1 ATTRIBUTS NUM 1 2 3 BASE PERSO PERSO SYS TYPE ENTIER TEXTE TEXTE NOM EMPLOYE DEPARTEMENT RELATIONS NOM NUM NOM PNOM NBATT 4 5 4 NUMREL 12 12 12 60 Algèbre relationnelle 61 Algèbre relationnelle OPERATIONS PERMETTANT D'EXPRIMER LES REQUETES SOUS FORME D'EXPRESSIONS ALGEBRIQUES • Avantages – – – – – Concis Sémantique simple Représentation graphique Utile pour raisonner (cf. TD) – peu d’erreur de syntaxe ! Utile pour l’optimisation de requêtes 62 Projection • Elimination des attributs non désirés et suppression des tuples en double • Relation Relation notée: a1,a2,...Ap (R) VINS Cru Mill Région Qualité VOLNAY 1983 BOURGOGNE A VOLNAY 1979 BOURGOGNE B CHENAS 1983 BEAUJOLAIS A JULIENAS 1986 BEAUJOLAIS C Cru,Région (VINS) Cru,Région (VINS) = Cru VOLNAY Région BOURGOGNE CHENAS BEAUJOLAIS JULIENAS BEAUJOLAIS 63 Restriction • Obtention des tuples de R satisfaisant un critère Q • Relation Relation, notée Q(R) • Q est le critère de qualification de la forme : – Ai Valeur, = { =, <, ≥, >, ≤ , != } – Il est possible de réaliser des "ou" (conjonction) et des "et" (disjonction) de critères simples VINS Cru VOLNAY Mill 1983 Région BOURGOGNE Qualité A VOLNAY 1979 BOURGOGNE B CHENAS 1983 BEAUJOLAIS A JULIENAS 1986 BEAUJOLAIS C MILL>1983 Mill>1983 (VINS) = VINS Cru Mill JULIENAS 1986 Région Qualité BEAUJOLAIS C 64 Jointure • Composition des deux relations sur un domaine commun • Relation Relation Relation – notée • Critère de jointure – Attributs de même nom égaux • Attribut = Attribut • Jointure naturelle – Comparaison d'attributs • Attribut1 Attribut2 • Théta-jointure • Cas particulier : équi-jointure 65 Exemple de Jointure VINS Cru VOLNAY Mill 1983 Qualité A VOLNAY 1979 B CHABLIS 1983 A JULIENAS 1986 C LIEU VINSREG VINS LIEU = Cru=Cru Cru Cru VOLNAY VOLNAY CHABLIS CHABLIS Région QualMoy VOLNAY Bourgogne A CHABLIS Bourgogne A CHABLIS Californie B Mill 1983 1979 1983 1983 Qualité A B A A Région QualMoy Bourgogne Bourgogne Bourgogne Californie A A A B 66 Exemple de -Jointure EMPLOYES NOM DEPT SALAIRE MARTIN DURAND DUPONT DUPOND 1 1 2 3 RESPONSABLE 130 235 280 150 DEPT SALAIRE 1 2 3 4 EMPLOYES E 230 250 300 270 RESPONSABLE R = E.salaire > R.salaire et E.dept = R.dept RESULTAT NOM DURAND DUPONT DEPT E.SALAIRE 1 2 235 280 R.SALAIRE 230 250 NB : signification de la requête ? Employés ayant un salaire supérieur à celui du responsable de leur département 67 Unions, intersections, différences • Opérations binaires – Relation Relation Relation • Opérations pour des relations de même schéma – UNION – INTERSECTION – DIFFERENCE notée notée notée — • Extension pour des relations de schémas différents – Ramener au même schéma avec des valeurs nulles 68 Union ( UNION ENSEMBLISTE POUR DES RELATIONS DE MEME SCHEMA Exemple : VINS1 VINS2 Vins1 Vins2 Cru Mill Region Couleur CHENAS 1983 BEAUJOLAIS ROUGE TOKAY 1980 ALSACE BLANC TAVEL 1986 RHONE ROSE Cru TOKAY Mill Region 1980 ALSACE Couleur BLANC CHABLIS 1985 BOURGOGNE ROUGE VINS CRU MILL REGION COULEUR Chenas 1983 Beaujolais Rouge Tokay 1980 Alsace Blanc Tavel 1986 Rhône Rosé Chablis 1985 Bourgogne Rouge 69 Intersection () INTERSECTION ENSEMBLISTE POUR DES RELATIONS DE MEME SCHEMA Exemple : VINS1 VINS2 Vins1 Vins2 Cru Mill Region Couleur CHENAS 1983 BEAUJOLAIS ROUGE TOKAY 1980 ALSACE BLANC TAVEL 1986 RHONE ROSE Cru TOKAY Mill Region 1980 ALSACE Couleur BLANC CHABLIS 1985 BOURGOGNE ROUGE VINS CRU MILL REGION COULEUR Tokay 1980 Alsace Blanc 70 Différence (-) DIFFERENCE ENSEMBLISTE POUR DES RELATIONS DE MEME SCHEMA Exemple : VINS1 - VINS2 Vins1 Vins2 Cru Mill Region Couleur CHENAS 1983 BEAUJOLAIS ROUGE TOKAY 1980 ALSACE BLANC TAVEL 1986 RHONE ROSE Cru TOKAY Mill Region 1980 ALSACE Couleur BLANC CHABLIS 1985 BOURGOGNE ROUGE VINS CRU MILL Chenas 1983 Tavel 1986 REGION COULEUR Beaujolais Rouge Rhône Rose 71 Division () • PERMET DE RECHERCHER DANS UNE RELATION, L’ENSEMBLE DES ‘SOUS TUPLES’ QUI SATISFONT UNE ‘SOUS-RELATION’ – inverse du produit cartésien ….. • Le quotient de la relation D(A1, …Ap, Ap+1…An) par la relation d(Ap+1…An) est la relation Q(A1, …Ap) dont les tuples sont ceux qui concaténés à tout tuple de d donnent un tuple de D. – Notations D d = Q 72 Division () - Exemple Compte Agence NomAgence nomAgence nomClient ParisAuteuil Dupont Bellecourt NantesCentre Queffelec Brotteaux Bellecourt Girard Brotteaux Letailleur LaDoua Girard ParisDupleix Dutour NantesCentre Passard NantesCentre Martin Bellecourt Letailleur Brotteaux Girard LaDoua Letailleur LaDoua Lagaffe LaDoua = Resultat nomClient Girard Letailleur 73 Représentation graphique Union Différence Division Intersection Projection Produit cartésien critères Restriction Jointure 74 Récapitulatif Produit Projection Sélection Union a b c x y Jointure Naturelle a a b b c c x y x y x y Division a1 b1 b1 c1 a1 b1 c1 a2 b1 b2 c2 a2 b1 c1 a3 b2 b3 c3 a3 b2 c2 a a a b c x y z x y x y Intersection Différence a 75 Autres opérateurs Renomage () Permet de renommer des attributs Notation : a1:b1, …, an:bn (Rel) Affectation () Affecte une expression à une relation temporaire Notation : Temp Expression_relationnelle Exemple : Temp r – s 76 SemiJoin, AntiJoin, OuterJoin • SemiJoin • AntiJoin • Left OuterJoin • Right OuterJoin 77 Conception de bases de données (Passage au MLD) 78 Passage au niveau logique • Le modèle conceptuel est un compromis entre la flexibilité de la langue courante et la rigueur nécessaire d’un traitement informatisé. • Le niveau logique est une étape de plus vers cette informatisation – Utilisation du formalisme du modèle relationnel : • • • • • tables (ou relations) attributs domaine clefs contrainte d’intégrité référentielles (relations entre tables) – Simplification du schéma de la base • Des règles trop strictes entraînent des schémas trop complexes • On ‘‘tolère’’ un peu de redondance ou quelques valeurs nulles.... 79 Propriétés, Entités • Règle 1 : Chaque propriété devient un attribut. • Règle 2 : Chaque entité devient une table et son identifiant devient sa clef primaire • Règle 3 : Une association peut : – être ‘‘absorbée’’ par l’une ou l’autre des entités – devenir une table 80 Cas 1 Profs 1,1 Nom Cours Enseigne 1,1 NbreHeures Prénom • Un prof enseigne un et un seul cours NomCours Description • Un cours est enseigné par un et un seul prof Adresse Solution 1 Nom Bouganim Crenn Rousseau Prénom Luc Isabelle Martine Adresse Paris Paris Versailles Nom Bouganim Crenn Rousseau Solution 2 Nom Bouganim Crenn Rousseau Prénom Luc Isabelle Martine Adresse Paris Paris Versailles NomCours Info Math Droit NomCours Info Math Droit Description Informatique Mathématiques Droit NbreHeures 44 78 26 Nbreheures 44 78 26 NomCours Info Math Droit Description Informatique Mathématiques Droit 81 Cas 2 Profs Nom 1,1 Cours Enseigne 0,1 NbreHeures Prénom NomCours Description Adresse Solution 1 Nom Bouganim Crenn Prénom Luc Isabelle Adresse Paris Paris • Un prof enseigne un et un seul cours • Un cours est enseigné par un prof ou n’est pas enseigné NomCours Info Math Droit Description Informatique Mathématiques Droit NbreHeures 44 78 Solution 2 Nom Bouganim Crenn Prénom Luc Isabelle Adresse Paris Paris NomCours Info Math NbreHeures 44 78 NomCours Info Math Droit Description Informatique Mathématiques Droit 82 Cas 3 Profs Nom 0,1 Cours Enseigne 1,1 NbreHeures Prénom • Un prof enseigne un cours ou aucun NomCours Description • Un cours est enseigné par un et un seul prof Adresse Solution 1 Nom Bouganim Crenn Rousseau Prénom Luc Isabelle Martine Adresse Paris Paris Versailles NomCours Info Math Description Informatique Mathématiques NbreHeures 44 78 Solution 2 Nom Bouganim Crenn Rousseau Prénom Luc Isabelle Martine Adresse Paris Paris Versailles Nom Bouganim Crenn NomCours Info Math Description NbreHeures Informatique 44 Mathématiques 78 83 Cas 4 Profs Nom 0,1 Cours Enseigne 0,1 NbreHeures Prénom • Un prof enseigne un cours ou aucun NomCours Description Adresse Solution 1 Nom Bouganim Crenn Prénom Luc Isabelle Adresse Paris Paris • Un cours est enseigné par un prof ou n’est pas enseigné NomCours Description Info Informatique Droit Solution 2 Nom Bouganim Crenn Prénom Luc Isabelle Nom Bouganim Adresse Paris Paris NomCours Info NbreHeures 44 Droit Nbre Heures 44 NomCours Info Droit Description Informatique Droit 84 Cas 5 Profs Nom 1,1 Cours Enseigne 1,n NbreHeures Prénom NomCours Description Adresse Solution 1 Nom Bouganim Crenn Rousseau Prénom Luc Isabelle Martine Adresse Paris Paris Versailles • Un prof enseigne un et un seul cours • Un cours est enseigné par un ou plusieurs profs NomCours Info Info Droit Description Informatique Informatique Droit NbreHeures 20 24 26 Solution 2 Nom Bouganim Crenn Rousseau Prénom Luc Isabelle Martine Adresse Paris Paris Versailles NomCours Info Info Droit NbreHeures 20 24 26 NomCours Info Droit Description Informatique Droit 85 Cas 6 Profs Nom 1,n Cours Enseigne 1,1 NbreHeures Prénom NomCours Description Adresse Solution 1 Nom Bouganim Crenn Crenn Prénom Luc Isabelle Isabelle Adresse Paris Paris Paris • Un prof enseigne un ou plusieurs cours • Un cours est enseigné par un et un seul prof NomCours Info Math Droit Description Informatique Mathématique Droit NbreHeures 20 48 26 Solution 2 Nom Bouganim Crenn Prénom Luc Isabelle Adresse Paris Paris Nom Bouganim Crenn Crenn NomCours Info Math Droit Description Informatique Mathématique Droit NbreHeures 20 48 26 86 Cas 7 Profs Nom 1,n Enseigne NbreHeures Prénom Cours 1,n NomCours Description Adresse Solution 1 Nom Bouganim Crenn Crenn Prénom Luc Isabelle Adresse Paris Paris Paris Nom Bouganim Crenn Crenn Solution 2 Nom Bouganim Crenn Prénom Luc Isabelle Isabelle Adresse Paris Paris • Un prof enseigne un ou plusieurs cours • Un cours est enseigné par un ou plusieurs profs NomCours Info Info Droit NomCours Info Info Droit Description Informatique Informatique Droit NbreHeures 22 26 34 Nbreheures 22 26 34 NomCours Description Info Informatique Droit Droit 87 Cas 8 Prof 1,n enseigne Heure Salle 1,n Groupe 1,n Cours Nom Bouganim Crenn Crenn Bouganim Nom Bouganim Crenn Prénom Luc Isabelle Adresse Paris Paris NomCours Info Math Info Info Groupe 2.1 2.1 2.2 2.1 NomCours Description Info Informatique Math Mathématique heure 10h 12h 17h 14h Salle A1 A3 A1 A2 Groupe 2.1 2.2 Option Finance Comptabilité Responsable Guter Paul Bourdin Jean 88 Passage au modèle relationnel - Conclusion • Objectifs – Ne pas créer de tables inutiles – Ne pas dégrader le modèle conceptuel (pas de propriété répétitive ni sans signification) • Méthode – Si possible, passer les propriétés de l’association dans l’une ou l’autre des entités mais: • Si la cardinalité minimum est 0, on ne peut le faire car, pour certaines entités, il y aurait des valeurs nulles (ex. un prof ne donnant pas de cours) • Si la cardinalité maximum est n, on ne peut le faire car il y aurait des attributs répétitif (ex. un prof donnant plusieurs cours) – Sinon, créer une table pour l’association contenant • les clefs des entités associées • les propriétés de l’association 89 Exercice d’algèbre Fin de l’exercice de conception (passage au modèle relationnel) 90 Fonctionnalités des bases de données Langage SQL 91 Plan de la journée 1. Fonctionnalités des SGBD – 10 grands principes 2. Langage SQL – – – – Définition de données Mises à jour Interrogation Dictionnaire de données (Oracle) 3. Exercices SQL (OracleXE) – Création de la BD en SQL – Chargement massif (SQL loader) – Mises à jour en SQL 92 Fonctionnalités des bases de données (en 10 grands principes) 93 L’approche ‘‘Bases de données’’ I- Indépendance Physique X - Standards II- Indépendance Logique III – Langage de manipulation IX - Gestion de la confidentialité VIII - Concurrence d’accès BD IV - Gestion des vues V - Optimisation des questions VII - Gestion des pannes VI - Gestion de la cohérence 94 I - 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 les programmes; – Ecriture des applications par des non-spécialistes des fichiers et des structures de stockage; – Meilleure portabilité des applications et indépendance vis à vis du matériel. 95 Modélisation Relationnelle Docteurs Prescriptions Id-D Nom Prénom 1 Dupont Pierre 2 Durand Paul 3 …. Masse …….. Jean …… Id-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 …. …. …. ………… Visites 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 Patients Médicaments Id-P Nom Prénom Ville 1 Lebeau Jacques Paris Id-M Nom Description 2 Troger Zoe Evry 1 Aspegic 1000 …………………………….. 3 Doe John Paris 2 Fluisédal …………………………….. 4 Perry Paule Valenton 3 Mucomyst …………………………….. …. ……. ……. ……. …. …….. …………………………….. 96 II - Indépendance Logique Les applications peuvent définir des vues logiques de la BD Gestion des médicaments Cabinet du Dr. Masse Prescriptions Nombre_Médicaments Id-M 1 Nom Aspegic 1000 Description …………………………….. 30 Fluisédal …………………………….. 20 3 Mucomyst …………………………….. 230 …. …….. …………………………….. ….. Ligne Id-M 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 …. …. …. ………… Visites Nombre 2 Id-V Id-D Id-P Id-V Date Prix 2 2 3 13 juillet 350 2 3 4 1 mars 250 Posologie Patients Docteurs Id-D Médicaments Id-P Nom Prénom Ville 1 Lebeau Jacques Paris Id-M Nom Description 2 Troger Zoe Evry 1 Aspegic 1000 …………………………….. 3 Doe John Paris 2 Fluisédal …………………………….. 4 Perry Paule Valenton 3 Mucomyst …………………………….. …. ……. ……. ……. …. …….. …………………………….. Prescriptions Nom Prénom 1 Dupont Pierre 2 Durand Paul 3 Masse Jean …. …….. …… Id-V Ligne Id-M 1 1 12 1 par jour Visites 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 Posologie 1 2 5 10 gouttes 2 1 8 2 par jour 2 2 12 1 par jour 2 3 3 2 gouttes …. …. …. ………… Patients Id-P Nom Prénom Ville 1 Lebeau Jacques Paris 2 Troger Zoe Evry Médicaments Id-M Nom Description 1 Aspegic 1000 …………………………….. 3 Doe John Paris 2 Fluisédal …………………………….. 4 Perry Paule Valenton 3 Mucomyst …………………………….. …. ……. ……. ……. …. …….. …………………………….. 97 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ées sans réécriture des applications : – ajout de champs, ajout de relation, renommage de champs. • Possibilité d'intégrer des applications existantes sans modifier les autres. • Possibilité de limiter les conséquences du partage : Données confidentielles. 98 III - 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 • Syntaxe (aperçu !) Select From Where Group By Order By <Liste de champs ou de calculs à afficher> <Liste de relations mises en jeu> <Liste de prédicats à satisfaire> <Groupement éventuel sur un ou plusieurs champs> <Tri éventuel sur un ou plusieurs champs> 99 Exemple de question SQL (1) • Nom et description des médicaments de type aspirine Select From Where Nom, Description Médicaments Type = ‘Aspirine’ 100 Exemple de question SQL (2) • Patients parisiens ayant effectués une visite le 15 juin Select From Where and and Patients.Nom, Patients.Prénom Patients, Visites Patients.Id-P = Visites.Id-P Patients.Ville = ’Paris’ Visites.Date = ’15 juin’ 101 Exemple de question SQL (3) • Dépenses effectuées par patient trié par ordre décroissant Select Patients.Id-P, Patients.Nom, sum(Prix) From Patients, Visites Where Patients.Id-P = Visites.Id-P Group By Patients.Id-P, Patients.Nom Order By sum(Prix) desc 102 IV – Gestion des vues • Les vues permettent d’implémenter l’indépendance logique en permettant de créer des objets virtuels • Vue = Question SQL stockée • Le SGBD stocke la définition et non le résultat • Exemple : la vue des patients parisiens Create View Parisiens as ( Select Nom, Prénom From Patients Where Patients.Ville = ’Paris’ ) 103 Les vues : des relations virtuelles ! Le SGBD transforme la question sur les vues en question sur les relations de base Question Q sur des vues Gestionnaire de Vues Question Q’ sur les relations de base Définition des vues Docteurs Id-D Prescriptions Nom Prénom 1 Dupont Pierre 2 Durand Paul 3 Masse Jean …. …….. …… Id-V Ligne Id-M 1 1 12 1 par jour Visites 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 Posologie 1 2 5 10 gouttes 2 1 8 2 par jour 2 2 12 1 par jour 2 3 3 2 gouttes …. …. …. ………… Patients Id-P Nom Prénom Ville 1 Lebeau Jacques Paris 2 Troger Zoe Evry Médicaments Id-M Nom Description 1 Aspegic 1000 …………………………….. 3 Doe John Paris 2 Fluisédal …………………………….. 4 Perry Paule Valenton 3 Mucomyst …………………………….. …. ……. ……. ……. …. …….. …………………………….. 104 Les vues : Mise à jour • Non définie si la répercussion de la mise à jour vers la base de données est ambiguë – ajouter un tuple à la vue calculant le nombre de médicaments ? • Restrictions SQL (norme): – – – – – Pas de distinct, d’agrégats, ni d’expression La vue contient les clés et les attributs « non nulls » Il y a une seule table dans le from Requêtes imbriquées possibles Certains SGBDs supportent plus de mises à jour • Clause « With check option » – Le SGBD vérifie que les tuples insérés ou mis à jour correspondent à la définition de la vue 105 Les vues : Les instantanés (snapshot) • Instantané, Snapshot, vue concrète, vue matérialisée – matérialisée sur le disque – accessible seulement en lecture – peut être réactualisé • Exemple – create snapshot Nombre_Médicaments as Select Id-M, Nom, Description, count(*) From Médicaments M, Prescriptions P Where M.Id-M = P.Id-M refresh every day • Objectif principal : la performance 106 V – Exécution et Optimisation • Traduction automatique des questions déclaratives en programmes procéduraux : Utilisation de l’algèbre relationnelle • 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 • Economie de l'astuce des programmeurs – milliers d'heures d'écriture et de maintenance de logiciels. 107 Sélection Patients Patients Id-P Nom Prénom Ville 1 Lebeau Jacques Paris 2 Troger Zoe Evry 3 Doe John Paris 4 Perry Paule Valenton 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 108 Projection Patients Patients Id-P Nom Prénom Ville 1 Lebeau Jacques Paris 2 Troger Zoe Evry 3 Doe John Paris 4 Perry Paule Valenton 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 109 Jointure Visites Patients Id-P Nom Prénom Ville Id-D Id-P Id-V Date Prix 1 Lebeau Jacques Paris 1 2 1 15 juin 250 2 Troger Zoe Evry 1 1 2 12 août 180 3 Doe John Paris 2 2 3 13 juillet 350 4 Perry Paule Valenton 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 110 Exemple de plan d’exécution Select From Where and and Patients.Nom, Patients.Prénom Patients, Visites Patients.Id-P = Visites.Id-P Patients.Ville = ’Paris’ Visites.Date = ’15 juin’ Patients Visites 111 Plan d’exécution optimisé Patients Visites Patients Visites 112 VI - Intégrité Logique • Objectif : Détecter les 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 5 et 50kf • 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 113 Contraintes d’intégrité • Avantages : – simplification du code des applications – sécurité renforcée par l'automatisation – mise en commun des contraintes, cohérence • Nécessite : – un langage de définition de contraintes d'intégrité – la vérification automatique de ces contraintes BD BD 114 Exemples de contrainte • Contraintes d’intégrité référentielles Docteurs Prescriptions Id-D Nom Prénom 1 Dupont Pierre 2 Durand Paul 3 …. Masse …….. Jean …… Visites 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 Id-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 …. …. …. ………… • Vérification lors de l’insertion, la suppression, la modification • Propagation des suppression, modification en cascade possible (on delete cascade) 115 VII - Intégrité Physique • Motivations : 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 – Garantir la durabilité des effets des transactions commises • Moyens : – Journalisation : Mémorisation des états successifs des données – Mécanismes de reprise 116 Transactions Incohérence possible... Etat cohérent Etat cohérent Begin Commit Transaction Begin CEpargne = CEpargne - 3000 CCourant = CCourant + 3000 Commit T1 117 Atomicité et Durabilité DURABILITE ATOMICITE Begin Panne CEpargne = CEpargne - 3000 CCourant = CCourant + 3000 Commit T1 Begin CEpargne = CEpargne - 3000 CCourant = CCourant + 3000 Commit T1 Crash disque Annuler le débit !! S’assurer que le virement a été fait ! 118 VIII - Partage des données BD • Accès concurrent aux mêmes données Conflits d’accès !! 119 VIII - Partage des données BD • Le SGBD gère les accès concurrents Chacun à l’impression d’être seul (Isolation) Cohérence conservée (Verrouillage) 120 IX – Confidentialité • Objectif : – protéger les données de la base contre des accès non autorisés • Mécanismes simples et puissants – Connexion restreinte aux usagers répertoriés (mot de passe) – Privilèges d'accès aux objets de la base (relation, vue, etc.) • Repose sur la sécurité de l’architecture (de bout en bout) BD Serveur BD Utilisateur Identification Authentification Sécurisation des communications (confidentialité, intégrité, non répudiation) Protection du serveur Protection des fichiers 121 Droits d'accès : Syntaxe de base et rôles • Syntaxe de base – – – – – Grant <droits> on <objet> to (<usager>*) [with grant option] Revoke <droits> on <objet> from (<usager>*) <droits> ::= all | select | insert | delete |update | alter <objet> ::= relation | vue | procedure <usager> ::= public | nom d'usager • Rôles (SQL3): – – – – – Create role <nom_role> : Création d’un nouveau rôle Drop role <nom_role> : Suppression d’un rôle Les instructions Grant et Revoke s’appliquent sur des rôles Grant <liste roles> to (<usager>*) : Affectation de rôles aux usagers Set role <liste_roles> : Activation de rôle(s) pendant une session 122 Droits d'accès : droits sur les vues Employés Id-E 1 Nom Ricks 2 Trock Jack 1254 3 Lerich Zoe 5489 4 Doe Joe 4049 Service des ressources humaines Public Prénom Poste Jim 5485 Nombre Masse d’employés Salariale 4 Id-E 1 Nom Ricks Prénom Poste Adresse Jim 5485 ………. 2 Trock Jack 1254 3 Lerich Zoe 4 Doe Joe 890 Ville Paris Salaire 230 ………. Versailles 120 5489 ………. Chartres 380 4049 ………. Paris 160 123 Droits d'accès : droits évolués • Règles indépendantes du contenu : – Ex. la secrétaire a accès aux infos administratives – grant ou revoke sur relation ou vue • Règles dépendantes du contenu : – Ex. Le médecin a accès aux dossier de ses patients – Utilisation de vues paramétrées (e.g. avec Current_User) • Règles dépendantes du contexte : – Ex. en l’absence du médecin traitant, tout médecin a accès au dossier du patient – Utilisation de vues paramétrées avec tables contextuelles – Ex. Les salaires ne peuvent être modifié que le 1er du mois – Utilisation de procédures stockées 124 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érabilité – Applications multi sources… 125 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 126 Applications des SGBD (1) • BD et WEB – Serveurs Web dynamiques, sites marchands ... – 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 Sécurité des données en ligne 127 Applications des SGBD (2) • BD personnelles ou PME – Comptabilité – Agenda, comptes bancaires, carnet d’adresses, dossiers portables – BD embarquées sur PC, smartphones, tablettes, cartes SIM, … • Challenges majeurs – – – – Gérer la mobilité S’adapter aux contraintes matérielles de l’hôte Assurer la durabilité des données Assurer la confidentialité des données 128 Le langage SQL Structured Query Langage 129 SQL : pour quoi faire (1) ? • Avant SQL, rechercher des données satisfaisant une qualification ressemblait à : Select B.plage From Nageurs N, Baignade B Where N.qualité = ‘excellent’ and B.date between ‘O1-08-89’ and ’31-08-89’and N.idn = B.idn 130 SQL : pour quoi faire (2) ? • Une interface plus simple pour les utilisateurs ? – La majorité des manipulations utilisateurs se font via des formulaires ! – Mais rend possible les requêtes libres (ex: analyse) • Simplifier le développement des applications – Code plus compact et facile à valider/maintenir – Indépendance physique et logique des programmes par rapport aux données – Optimisation automatique et dynamique des requêtes par le SGBD • Une puissance d’expression exploitable de nb façons – Sélection, mises à jour, intégrité, droits d’accès, audit … 131 Le standard SQL (1) • Trois versions normalisées de SQL : – SQL1 (1986) : version minimale – SQL1 (1989) : addendum (intégrité) – SQL2 (1992) : version étendue à trois niveaux de conformité (entry, intermediate, full) – SQL3 (1999) : version étendue à la gestion d’objets complexes – SQL3 (2003) : introduction de fonctions de manipulation XML 132 Le standard SQL (2) LANGAGE DE DEFINITION DE DONNEES CREATE TABLE CREATE VIEW LANGAGE DE CONTROLE ALTER …. ... GRANT REVOKE COMMIT WORK LANGAGE DE MANIPULATION DE DONNEES ROLLBACK WORK SELECT INSERT UPDATE DELETE INTEGRATION AUX LANGAGES DE PROGRAMMATION EXEC SQL MODULE PROCEDURE ... 133 Le langage SQL (1/3) LDD : Langage de Définition de Données Création, modification du schéma 134 EXEMPLE DE BASE DE DONNEES 135 Création de table CREATE TABLE <nom_table> (<def_colonne> * [<def_contrainte_table>*]) ; < def_colonne > ::= <nom_colonne> < type > [CONSTRAINT nom_contrainte < NOT NULL | UNIQUE | PRIMARY KEY | CHECK (condition) | REFERENCES nom_table (colonne) > ] < def_contrainte_table > ::= CONSTRAINT nom_contrainte < UNIQUE (liste_colonnes) | PRIMARY KEY (liste_colonnes) | CHECK (condition) | FOREIGN KEY (liste_colonnes) REFERENCES nom_table (liste_colonnes)> 136 Exemple de création de table CREATE TABLE RDV( NumRdv Integer, DateRDV Date, NumDoc Integer, NumPat Integer, Motif Varchar(200), CONSTRAINT Clé_Primaire_RDV PRIMARY KEY (NumRdv), CONSTRAINT Référence_DOC FOREIGN KEY (NumDoc) REFERENCES DOC, CONSTRAINT Référence_PAT FOREIGN KEY (NumPat) REFERENCES PAT); L'association d'un nom à une contrainte est optionnelle. Ce nom peut être utilisé pour référencer la contrainte (ex: messages d'erreurs). • Exercice 1 – Donnez l’expression SQL de la création des tables DOC et DET 137 Index, modification du schéma • Création d’index – CREATE [UNIQUE] INDEX nom_index ON nom_table (<nom_colonne> * ); • Suppression – DROP TABLE <nom_table> – DROP INDEX <nom_index> • Modification – – – – – • ALTER TABLE <nom_table> ADD COLUMN <def_colonne> ALTER TABLE <nom_table> ADD CONSTRAINT <def_contrainte_table > ALTER TABLE <nom_table> MODIFY <def_colonne> ALTER TABLE <nom_table> DROP COLUMN <nom_colonne> ALTER TABLE <nom_table> DROP CONSTRAINT <nom_contrainte > Exemples – CREATE INDEX Index_date_RDV ON RDV (DateRDV) ; – ALTER TABLE RDV ADD COLUMN Commentaires varchar(300); – ALTER TABLE RDV ADD CONSTRAINT MotifNN NOTNULL(Motif); • Exercice 2 – Créez un index sur le nom du docteur – Supprimez l’attribut Motif de la table RDV – Ajoutez une contrainte de clé primaire à la table MED (sur NumMed) 138 Le Langage SQL (2/3) LMD : Langage de Manipulation des Données (INSERT, DELETE , UPDATE) 139 Insertion de données : INSERT INSERT INTO < nom_table > [( attribute [,attribute] … )] {VALUES (<value spec.> [, <value spec.>] … ) | <query specification>} ; • Exemples : – INSERT INTO DOC VALUES (1, ‘Dupont’, ‘Paris’); – INSERT INTO DOC (NumDoc, NomDoc) VALUES (2, ‘Toto’); – INSERT INTO PAT (NumPat, NomPat, VillePat) SELECT NumDoc, NomDoc, VilleDoc FROM DOC; • Exercice 3 – Insérez un ensemble cohérent de tuples dans chaque table 140 Mise à jour : UPDATE SYNTAXE : UPDATE <relation_name> SET <attribute> = value_expression [, <attribute> = value_expression ] … [WHERE <search condition> ]; EXEMPLES : Mettre “Inconnue” quand VilleDoc n’est pas renseignée UPDATE DOC SET VilleDoc = “Inconnue” WHERE VilleDoc IS NULL Mettre en majuscule le nom des docteurs qui n’ont jamais prescrit UPDATE DOC SET NomDoc = UPPER(NomDoc) WHERE NumDoc NOT IN (SELECT NumDoc FROM ORD) ATTENTION AUX CONTRAINTES D’INTEGRITE REFERENTIELLES !!! EXERCICE 4 • Augmenter le prix des aspirines de 10% • Fixer le prix des médicaments non prescrits à 100 euros 141 Suppression : DELETE SYNTAXE : DELETE FROM <relation_name> [WHERE <search_condition>]; EXEMPLES : Supprimer les docteurs quand VilleDoc n’est pas renseignée DELETE FROM DOC WHERE VilleDoc IS NULL Supprimer les docteurs qui n’ont jamais prescrit DELETE FROM DOC WHERE NumDoc NOT IN (SELECT NumDoc FROM ORD) ATTENTION AUX CONTRAINTES D’INTEGRITE REFERENTIELLES !!! EXERCICE 5 • Supprimer les patients de moins de 10 ans • Supprimer les médicaments non prescrits 142 Correction exercice 1 Donnez l’expression SQL de la création des tables DOC et DET CREATE TABLE DOC( NumDoc integer, NomDoc char(30), VilleDoc varchar(50), CONSTRAINT Clé_Primaire_Doc PRIMARY KEY (NumDoc)) ; CREATE TABLE DET( NumOrd integer, NumLig integer, NumMed integer, Qté integer, CONSTRAINT Clé_Primaire_DET PRIMARY KEY (NumOrd, NumLig), CONSTRAINT Référence_ORD FOREIGN KEY (NumOrd) REFERENCES ORD, CONSTRAINT Référence_MED FOREIGN KEY (NumMed) REFERENCES MED); 143 Correction exercice 2 Créez un index sur le nom du docteur CREATE INDEX Index_nom_docteur ON DOC (NomDoc) ; Supprimez l’attribut Motif de la table RDV ALTER TABLE RDV DROP COLUMN Motif; Ajoutez une contrainte de clé primaire à la table MED (sur NumMed) ALTER TABLE MED ADD CONSTRAINT cle_prim_MED PRIMARY KEY (NumMed) ; 144 Correction exercice 3 Insérez un ensemble cohérent de tuples dans chaque table (1 par table) • • • • • • • • INSERT INTO DOC VALUES (11, ‘Dupont’, ‘Paris’); INSERT INTO PAT VALUES (23,’Toto’, ‘Paris’, 25) INSERT INTO MED VALUES (17, ‘Aspirine’, 4) INSERT INTO MED VALUES (18, ‘Feroxite’, 19) INSERT INTO RDV VALUES (45, #20/11/2006#, 11, 23, ‘mal de tête’) INSERT INTO ORD VALUES (38, #20/11/2006#, 11, 23) INSERT INTO DET VALUES (38, 1, 17, 3) INSERT INTO DET VALUES (38, 2, 18, 1) 145 Exercices 4 et 5 : correction • Augmenter le prix des aspirines de 10% UPDATE MED SET Prix = Prix * 1.1 WHERE NomMed LIKE “aspirine%” • Fixer le prix des médicaments non prescrits à 100 euros UPDATE MED SET Prix = 100 WHERE NumMed NOT IN (SELECT NumMed FROM DET); • Supprimer les patients de moins de 10 ans DELETE FROM PAT WHERE AgePat < 10; • Supprimer les médicaments non prescrits DELETE FROM MED WHERE NumMed NOT IN (SELECT NumMed FROM DET); 146 Le Langage SQL (2/3) LMD : Langage de Manipulation des Données (SELECT) 147 EXEMPLE DE BASE DE DONNEES 148 SELECT : forme générale SELECT [DISTINCT| ALL] { * | <value exp.> [, <value exp.>]...} FROM relation [alias], relation [ alias]… [WHERE <search condition>] [GROUP BY <attribute> [,<attribute>]...] [HAVING <search condition>] [ORDER BY <attribute> [{ASC | DESC}] [,<attribute>[{ASC | DESC}] ]...] * EXPRESSION DE VALEURS * CONDITION DE RECHERCHE - Calculs arithmétiques - Sélection, projection, jointure - Fonctions agrégats - Recherche textuelle - Recherche par intervalle - Recherche sur valeur nulle 149 Forme générale de la condition de recherche <search condition> ::= [NOT] <nom_colonne> IS [NOT] NULL <nom_colonne> constante <nom_colonne> <nom_colonne> LIKE <modèle_de_chaîne> <nom_colonne> IN <liste_de_valeurs> <nom_colonne> (ALL ANY/SOME) <liste_de_valeurs> EXISTS <liste_de_valeurs> UNIQUE <liste_de_valeurs> <tuple> MATCH [UNIQUE] <liste_de_tuples> <nom_colonne> BETWEEN constante AND constante AND OR <search condition> avec ::= < = > <> Remarques: <liste_de_valeurs> et <liste_de_tuples> peuvent être déterminés par une requête <tuple> ::= (<nom_colonne> [,<nom_colonne>]…) 150 Projections et restrictions simples Liste des médicaments de plus de 500 FF NomMed SELECT NomMed FROM MED WHERE Prix> 500 ; Liste des médicaments de plus de 100 € NomMed (prix stocké en FF) SELECT NomMed FROM MED WHERE Prix/6,55957 > 100 ; Nom des docteurs de LAON NomDoc SELECT NomDoc FROM DOC WHERE VilleDoc = “Laon” Quelles ont été les motifs de consultations de 25/12/2006 Motif SELECT DISTINCT Motif FROM RDV WHERE DateRDV = #25/12/2006# EXERCICE 6 • Liste des patients de plus de 40 ans NumPat, NomPat • Age des patients en mois (il est stocké en année) NomPat, AgeMois • Numéro des docteurs ayant consulté entre le 1/9 et le 31/12/06 NumDoc • Nom des patients niçois de plus de 36 ans NumPat, NomPat 151 Restrictions complexes et jointures Liste des patients ayant un RDV avec le docteur "Dupont" NomPat SELECT DISTINCT PAT.NomPat FROM PAT, RDV, DOC WHERE PAT.NumPat = RDV.NumPat and RDV.NumDoc = DOC.NumDoc and DOC.NomDoc like “dupont”; Médicaments commençant par « ASPI » prescrits le 25/12/2006 NomMed SELECT DISTINCT M.NomMed FROM MED M, DET D, ORD O WHERE M.NumMed = D.NumMed and D.NumOrd = O.NumOrd and O.Date = #25/12/2006# and NomMed like “ASPI%”; Docteurs ayant le même nom qu’un patient NomDoc SELECT D.NomDoc FROM PAT P, DOC D WHERE P.NomPat = D.NomDoc; EXERCICE 7 • Docteurs ayant le même nom que leur patient NomDoc • Nom des médicaments prescrits le 25/12/2006 NomMed • Nom des docteurs ayant un RDV pour un motif commençant par ‘grippe’NomDoc • Liste des quantité distinctes prescrites du médicament Bubol Qté 152 Requêtes imbriquées : IN et EXIST Liste des patients ayant un RDV avec le docteur "Dupont" NomPat SELECT DISTINCT P.NomPat FROM PAT P, RDV R, DOC D WHERE P.NumPat = R.NumPat and R.NumDoc = D.NumDoc and D.NomDoc = “Dupont”; SELECT P.NomPat FROM PAT P WHERE P.NumPat in (SELECT R.NumPat FROM RDV R WHERE R.NumDoc in (SELECT D.NumDoc FROM DOC WHERE D.NomDoc = “Dupont”)); SELECT P.NomPat FROM PAT P WHERE EXISTS (SELECT * FROM RDV R WHERE P.NumPat = R.NumPat and EXISTS (SELECT * FROM DOC D WHERE R.NumDoc=D.NumDoc and D.NomDoc = “Dupont”)); SELECT P.NomPat FROM PAT P WHERE EXISTS (SELECT * FROM RDV R WHERE EXISTS (SELECT * FROM DOC D WHERE P.NumPat = R.NumPat and R.NumDoc = D.NumDoc and D.NomDoc = “Dupont”)); EXERCICE 8 (avec IN ou EXIST) • Nom des docteurs ayant au moins un RDV pour une grippe NomDoc • Nom des patients ayant eu des RDV avec "Dupont" et "Durand" NomPAT • Nom des patients qui n'ont jamais eu de rendez-vous NomPAT 153 Calculs d'agrégats Les fonctions d’agrégation (Count, Sum, Avg, Min, Max) permettent de réaliser des calculs sur des ensembles de données • Calcul de statistiques globales – Nombre de patients : SELECT count(*) FROM PAT – Prix moyen des médicaments : SELECT avg(Prix) FROM MED • Calcul de statistiques par groupe – Nombre de patients par ville : SELECT VillePat, count(*) NbPatient FROM PAT GROUP BY VillePat – Nombre de patients par ville ayant consulté pour un mal de tête SELECT VillePat, count(DISTINCT(NumPat)) NbPatient FROM PAT, RDV WHERE PAT.NumPat = RDV.NumPat and Motif = ‘mal de tête’ GROUP BY VillePat – Ville où plus de 10 patients ont consulté pour un mal de tête SELECT VillePat FROM PAT, RDV WHERE PAT.NumPat = RDV.NumPat and Motif = ‘mal de tête’ GROUP BY VillePat HAVING count(DISTINCT(NumPat)) > 10 154 Agrégats : autres exemples et exercices • Nom du(des) médicament(s) le(s) moins cher(s) SELECT NomMed FROM MED WHERE Prix = SELECT MIN(Prix) FROM MED • Total des prix des médicaments prescrits par patient SELECT P.NomPat, sum(M.prix * D.Qté) PrixTotal FROM PAT P, ORD O, DET D, MED M WHERE M.NumMed = D.NumMed AND D.NumOrd = O.NumOrd AND O.NumPat = P.NumPat GROUP BY P.NomPat • Moyenne du : Total des prix des médicaments prescrits par patient … SELECTAVG(TMP.PrixTotal) FROM ( SELECT P.NomPat, sum(M.prix * O.Qté) PrixTotal FROM PAT P, ORD O, DET D, MED M WHERE M.NumMed = D.NumMed AND D.NumOrd = O.NumOrd AND O.NumPat = P.NumPat GROUP BY P.NomPat ) TMP EXERCICE 9 • Nombre total d’ordonnances • Nombre d’ordonnances par docteur • Age du plus jeune patient • Nom du plus jeune patient 155 Union/Intersection/Différence <requêteSQL_A> UNION | INTERSECT | EXCEPT [ALL] CORRESPONDING précise que l’intersection se fait sur le nom des colonnes et non sur leur position… [CORRESPONDING [BY <liste_de_colonnes>]] <requêteSQL_B> BY liste de colonnes précisée réduit « l’assiette » de l’opération au sous-ensemble spécifié de colonnes… [ALL] permet de conserver les doublons dans le résultat [CORRESPONDING BY] n’est en général pas implanté (mais peut etre remplacé par une jointure) 156 Union/Inter°/Diff. : exemples et exercices • Ensemble des personnes de la base médicale SELECT NomDoc NomPers FROM DOC UNION SELECT NomPat NomPers FROM PAT • Patients qui sont aussi médecins SELECT NomPat PatDoc FROM PAT INTERSECT SELECT NomDoc PatDoc FROM DOC • Patients qui ne sont pas médecins SELECT NomPat Patient FROM PAT EXCEPT SELECT NomDoc Patient FROM DOC EXERCICES 10 • • • • Nom des médicaments jamais prescrits Villes mentionnées dans la base Villes où il y a au moins un docteur mais aucun patient Villes où il y a au moins un docteur et au moins un patient 157 Des différences selon le SGBD… Access SQL Server 2000 Oracle8 GROUP BY Y Y Y HAVING Y Y Y UNION Y Y Y INTERSECT N N Y EXCEPT N N Y (MINUS) CORRESPONDING BY N N N 158 Jointure Interne / Externe <Join_expression> ::= Évite d’écrire la condition de jointure (clause d’égalité sur les colonnes de même nom…) <table-ref> [NATURAL] [<join_type>] JOIN <table-ref> [ON <condition> USING <nom_colonne> *] <table-ref> CROSS JOIN <table-ref> <join_type> ::= INNER USING precise le sous-ensemble des colonnes communes à considérer dans la jointure (NB: remplace le ON…) Produit cartésien (INNER JOIN avec clause toujours vrai) OUTER est optionnel dans la syntaxe (même fonctionnalité) LEFT [OUTER] RIGHT [OUTER] OUTER produit certains tuples ne joignant pas… FULL [OUTER] …LEFT… UNION…RIGHT… 159 Exercice 6 : correction • Liste des patients de plus de 40 ans NumPat, NomPat SELECT NumPat, NomPat FROM PAT WHERE AgePat > 40 • Age des patients en mois (il est stocké en année) NomPat, AgeMois SELECT NomPat, AgePat*12 AgeMois FROM PAT • Numéro des docteurs ayant consulté entre le 1/9 et le 31/12/06 NumDoc SELECT DISTINCT NumDoc FROM RDV WHERE DateRDV BETWEEN #01/09/2006# AND #31/12/2006# SELECT DISTINCT NumDoc FROM RDV WHERE DateRDV #01/09/2006# AND DateRDV #31/12/2006# • Nom des patients niçois de plus de 36 ans NumPat, NomPat SELECT NumPat, NomPat FROM PAT WHERE AgePat > 36 AND VillePat LIKE "Nice" 160 Exercice 7 : correction • Docteurs ayant le même nom que leur patient NomDoc SELECT DISTINCT D.NomDoc FROM PAT P, RDV R, DOC D WHERE P.NumPat = R.NumPat AND R.NumDoc = D.NumDoc AND P.NomPat = D.NomDoc; • Nom des médicaments prescrits le 25/12/2006 NomMed SELECT DISTINCT M.NomMed FROM MED M, DET D, ORD O WHERE M.NumMed = D.NumMed AND D.NumOrd = O.NumOrd AND O.Date = #25/12/2006# ; • Nom des docteurs ayant un RDV pour un motif commençant par ‘grippe’NomDoc SELECT DISTINCT D.NomDoc FROM RDV R, DOC D WHERE R.NumDoc = D.NumDoc AND R.Motif LIKE “grippe%”; • Liste des quantité distinctes prescrites du médicament Bubol Qté SELECT DISTINCT D.Qté FROM MED M, DET D WHERE M.NumMed = D.NumMed AND M.NomMed LIKE “Bubol”; 161 Exercice 8 : correction (1/3) • Nom des docteurs ayant au moins un RDV pour une grippe NomDoc SELECT DISTINCT D.NomDoc FROM DOC D, RDV R WHERE R.NumDoc = D.NumDoc AND R.Motif LIKE “grippe”; SELECT D.NomDoc FROM DOC D WHERE D.NumDoc IN (SELECT R.NumDoc FROM RDV R WHERE R.Motif LIKE “grippe”): SELECT D.NomDoc FROM DOC D WHERE EXISTS (SELECT * FROM RDV R WHERE R.NumDoc = D.NumDoc AND R.Motif LIKE “grippe”): • Nom des patients qui n'ont jamais eu de rendez-vous NomPAT SELECT P.NomPat FROM PAT P WHERE P.NumPat NOT IN (SELECT R.NumPat FROM RDV R ) SELECT P.NomPat FROM PAT P WHERE NOT EXISTS (SELECT * FROM RDV R WHERE P.NumPat = R.NumPat) 162 Exercice 8 : correction (2/3) 163 Exercice 8 : correction (3/3) 164 Exercice 9 : Correction (1/2) • Nombre total d’ordonnances SELECT COUNT(*) FROM ORD • Nombre d’ordonnances par docteur SELECT DOC.NumDoc, DOC.NomDoc, COUNT(*) FROM DOC, ORD where DOC.numDoc = ORD.NumDoc GROUP BY DOC.NumDoc, DOC.NomDoc • Age du plus jeune patient SELECT min(AgePat) from PAT • Nom du plus jeune patient SELECT NomPat FROM PAT WHERE AgePat = SELECT min(AgePat) from PAT 165 Exercice 9 : Correction (2/2) • Nom des médicaments jamais prescrits SELECT NomMED FROM MED WHERE NumMED NOT IN (SELECT NumMed FROM ORD) • Villes mentionnées dans la base SELECT VilleDoc Ville FROM DOC UNION SELECT VillePat Ville FROM PAT • Villes où il y a au moins un docteur mais aucun patient SELECT VilleDoc Ville FROM DOC MINUS SELECT VillePat Ville FROM PAT • Villes où il y a au moins un docteur et au moins un patient SELECT VilleDoc Ville FROM DOC INTERSECT SELECT VillePat Ville FROM PAT 166 Le dictionnaire de données Oracle 167 Contenu du Dictionnaire Oracle • Dictionnaire Oracle = tables systèmes en lecture seule • Evolution du dictionnaire – Généré au moment de la création de la DB – Mis à jour automatiquement • Contient des informations sur la structure de la BD – Utilisateurs (privilèges, rôles) – Noms et caractéristiques des objets (tables, vues, index, clusters, triggers, packages, ...) – Contraintes d'intégrité – Ressources physiques allouées à la base – Valeurs par défaut pour des colonnes – Les autres informations générales sur la base des données • Structure – Tables de base = tables fondamentales contenant les informations sur la BD • Conservées dans le tablespace SYSTEM – Vues de ces tables = présentation dépendant du rôle de celui qui consulte… 168 Utilisation du dictionnaire • Accès avec SQL • Utilisé par le DBA et les utilisateurs – Consultation d’informations sur la structure de la BD – Seulement des droits en lecture sur des vues du dictionnaire – NB: seulement par des requêtes SELECT (lecture seule) • Utilisé par Oracle – A la connexion et pendant l’exécution des requêtes • Consultation d’informations sur les utilisateurs et leurs privilèges • Consultation d’informations sur les objets de la BD – Lors des requêtes SQL DDL (Data Definition Language) • Modification du dictionnaire 169 Différentes vues SQL> SELECT table_name, comments > FROM dictionary > WHERE table_name LIKE '%CONSTRAINTS%'; TABLE_NAME COMMENTS ---------------------------------------------------------------ALL_CONSTRAINTS Constraint definitions on accessible tables DBA_CONSTRAINTS Constraint definitions on all tables USER_CONSTRAINTS Constraint definitions on user's own tables 3 rows selected. • La vue ‘DICTIONARY’ permet d’accéder aux noms/desc. des vues DBA, ALL, USER, V$... 170 Les vues USER • Description des objets logiques créés par l’utilisateur connecté – Objets logiques = tables, index, vues, triggers, procédures… • Exemples de vues USER_ – USER_TABLES • Tables créées par l’utilisateur – USER_INDEXES • Index créés par l’utilisateur – USER_CONSTRAINTS • Contraintes créées par l’utilisateur SQL> SELECT table_name > FROM dictionary > WHERE table_name LIKE '%USER_%'; TABLE_NAME -----------------------------… USER_VIEWS USER_HISTOGRAMS 115 row(s) selected. – USER_VIEWS • Informations sur les vues créées par l’utilisateur – USER_USERS • Information sur l’utilisateur 171 Les vues ALL • Ces vues décrivent – les objets créés par l’utilisateur connecté (comme dans user_tables) – Et aussi tous les objets accessibles à cet utilisateur Dans user_tables SQL> desc user_tables; Nom -----------------------------TABLE_NAME … … Dans all_tables SQL> desc all_tables; Nom -----------------------------OWNER … TABLE_NAME … … • Exemples de vues ALL_ SQL> … SQL> … SQL> … SQL> … SELECT table_name FROM all_tables; SELECT owner FROM all_users; SELECT index_name FROM all_indexes; SELECT table_name, constraint_name FROM all_constraints; 172 Les vues ALL : exemple • La vue ALL_CONSTRAINTS – Contraintes des tables accessibles à l’utilisateur 173 Les vues DBA (1) • Décrivent tous les objets de la base – Sur certains objets, la description est plus complète • Accessibles qu’aux utilisateurs – Ayant le rôle SELECT_CATALOG_ROLE – Ou ayant le privilège système SELECT ANY DICTIONARY • Ex. La vue DBA_TABLES SQL> SELECT table_name, num_rows, blocks, < empty_blocks, avg_space > FROM dba_tables > WHERE table_name=’Livre’; TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE ---------- -------- ------ ------------ --------LIVRE 6764 180 19 861 1 row(s) selected. 174 Les vues dynamiques V$ • Vues dont les informations sont «dynamiques» – Evoluent du démarrage de l’instance jusqu’à son arrêt – Décrivent l’activité de la DB et de l’instance – Sont appelées «dynamiques» mais • en fait, elle externalise l’état de variables internes à Oracle • Usage de ces données – Principalement pour l’amélioration des performances de la BD • Ex. V$Session SQL> SELECT sid, serial#, username, type, status > FROM v$session; SID SERIAL# USERNAME TYPE STATUS ----- -------- ------------ ---------- ------1 1 BACKGROUND ACTIVE … 8 6 HEURTEL USER INACTIVE – Le DBA peut déconnecter les utilisateurs avec la commande suivante SQL> ALTER SYSTEM KILL SESSION ‘8,6’ ; 175 Table DUAL • Elle permet de – – – – récupérer la date système (SYSDATE) tester le formatage de données de type DATE tester le bon «parenthésage» d'expressions etc. • Possède une seule colonne (DUMMY) et un seul tuple SQL> SELECT TO_CHAR(SYSDATE,'DD-MM-YYYY HH24:MI') > FROM DUAL; TO_CHAR(SYSDATE,'DD-MM-YYYY HH24:MI') ------------------------------------23-02-2004 22:05 SQL> SELECT 89456/562 FROM dual; 89456/562 ---------159.174377 176 Exercice SQL Création de schéma Insertion massive Mises à jour 177 Méthodologie SQL Gestion de la concurrence Optimisation de requêtes 178 Plan de la journée 1. Méthodologie SQL – Compilation 2. Exercices SQL (OracleXE) – Interrogation SQL 3. Optimisation de requêtes – Problème combinatoire – Impact pour les éditeurs, les concepteurs, les développeurs 4. Gestion de la concurrence – Protocole indispensable 5. Expérimentations (OracleXE) – Concurrence d’accès dans Oracle – Optimisation dans Oracle 179 Le Langage SQL (3/3) Méthodologie 180 Évaluation « sémantique » d’une requête SQL 1. FROM Réalise le produit cartésien des relations 2. WHERE Réalise restriction et jointures 3. GROUP BY Constitue les partitions (e.g., tri sur l’intitulé du groupe) XXX XXX YYY ZZZ 4. HAVING Restreint aux partitions désirées 5. SELECT Réaliser les projections/calculs finaux 6. ORDER BY Trier les tuples résultat XXX XXX AGG1 YYY AGG2 ZZZ AGG3 AGG1 XXX AGG3 ZZZ AGG1 ZZZ AGG3 XXX 181 Example (1) • SELECT FROM * PAT, RDV, DOC DOC: NumDoc NomDoc VilleDoc 1 2 PAT: NumPat NomPat VillePat AgePat RDV: NumRDV DateRDV NumDoc NumPat Motif Jules Dupont 1 2 3 4 5 1 2 1 2 1 1 2 3 1 2 3 1 2 DOC x RDV x PAT: NumDoc NomDoc VilleDoc NumRDV DateRDV NumDoc NumPat Motif NumPat NomPat VillePat AgePat 1 2 1 2 1 1 2 1 2 1 1 2 1 2 1 1 2 1 2 1 1 2 1 2 1 1 2 1 2 1 Jules Dupont Jules Dupont Jules Dupont Jules Dupont Jules Dupont Jules Dupont Jules Dupont Jules Dupont Jules Dupont Jules Dupont Jules Dupont Jules Dupont Jules Dupont Jules Dupont Jules Dupont 1 1 2 2 3 3 4 4 5 5 1 1 2 2 3 3 4 4 5 5 1 1 2 2 3 3 4 4 5 5 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 182 Example (2) • SELECT FROM WHERE * PAT, RDV, DOC DOC.NomDoc like “dupont”; DOC.NomDoc like ‘Dupont’ (DOCxRDVxPAT): NumDoc NomDoc VilleDoc NumRDV DateRDV NumDoc NumPat Motif NumPat NomPat VillePat AgePat Jules Dupont Jules Dupont Jules Dupont Jules Dupont Jules Dupont Jules Dupont Jules Dupont Jules Dupont Jules Dupont Jules Dupont Jules Dupont Jules Dupont Jules Dupont Jules Dupont Jules Dupont DOC.NomDoc like ‘Dupont’ (DOCxRDVxPAT): NumDoc NomDoc VilleDoc NumRDV DateRDV NumDoc NumPat Motif NumPat NomPat VillePat AgePat Dupont Dupont Dupont Dupont Dupont Dupont Dupont Dupont Dupont Dupont Dupont Dupont Dupont Dupont Dupont 183 Examples (3) • SELECT FROM WHERE * PAT, RDV, DOC DOC.NomDoc like “dupont” AND PAT.NumPat = RDV.NumPat AND RDV.NumDoc = DOC.NumDoc; DOC.NomDoc like ‘Dupont’ (DOCxRDVxPAT): NumDoc NomDoc VilleDoc NumRDV DateRDV NumDoc NumPat Motif NumPat NomPat VillePat AgePat 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 Dupont Dupont Dupont Dupont Dupont Dupont Dupont Dupont Dupont Dupont Dupont Dupont Dupont Dupont Dupont 1 2 3 4 5 1 2 3 4 5 1 2 3 4 5 1 2 1 2 1 1 2 1 2 1 1 2 1 2 1 1 2 3 1 2 1 2 3 1 2 1 2 3 1 2 1 1 1 1 1 2 2 2 2 2 3 3 3 3 3 DOC.NomDoc like ‘Dupont’ (DOCxRDVxPAT): NumDoc NomDoc VilleDoc NumRDV DateRDV NumDoc NumPat Motif NumPat NomPat VillePat AgePat 2 2 Dupont Dupont 4 2 2 2 1 2 1 2 184 Utiliser l’algèbre relationnelle • En s’entraînant un peu, il semble plus facile d’écrire une requête en algèbre puis de la traduire en SQL – Cette traduction s’effectue de manière systématique et peut générer des expressions un peu ‘lourde’. Toutefois, elle permet de bien comprendre la requête – Certaine requêtes ne peuvent s’exprimer en algèbre (Ex: contenant des calculs d’agrégats, groupements) Toutefois, on peut souvent en exprimer une partie en algèbre… – La traduction des opérateurs d’algèbre est assez simple (excepté pour la division) • • • • • Sélection = <critère>(R) select * from R where <critère>; Projection = <liste>(R) select <liste> from R; Jointures (naturelle/théta) = R <critère>S = select * from R, S where <critere>; Union/Intersection/Différence opérateur UNION/INTERSECT/EXCEPT… Division double négation, expression relationnelle de la division… 185 Traduction par double négation • On veut diviser R(A1,...,Ap,...,An) par Q (A1, ..., Ap) • Et obtenir S (Ap+1, ....., An) Select Ap+1, ...., An from R R1 where not exists ( select * from Q where not exists ( select * from R R2 where R2.A1 = Q.A1 and R2.A2 = Q.A2 and .... and R2.Ap = Q.Ap and R2.Ap+1 = R1.Ap+1 and .... and R2.An = R1.An )) • La logique sur un exemple… • Nom des docteurs qui ont prescrit tous les médicaments • NomDoc,NumMed(DOC VIS ORD MED) % NumMed MED • Equivalent à: Nom des docteurs tels qu’il n’existe pas de médicaments tel qu’il n’existe pas de prescription de ces médicaments faites par ces docteurs SELECT NomDoc, FROM DOC WHERE NOT EXIST( SELECT * FROM MED WHERE NOT EXIST( SELECT * FROM VIS, ORD WHERE DOC.NumDoc=VIS.NumDoc AND VIS.NumVis=ORD.NumVis AND ORD.NumMed=MED.NumMed ) ); 186 Erreurs dans l’écriture de requêtes • • • • • • Mauvaise compréhension du schéma de la base Mauvaise compréhension de la question Instances de la même table Oubli de clauses dans les prédicats Doublons Utilisation d’instruction ou de structures SQL incorrectes • Erreurs de syntaxe 187 Schéma et question • Avant de se lancer dans l’écriture d’une requête, il faut bien comprendre le schéma des tables sur lequel on va s’appuyer • Si le schéma est complexe, il faut le dessiner, c.a.d. dessiner les tables et les relations entre ces tables • En lisant la question, on repère sur le schéma dans quelle(s) relation(s) se trouve chaque donnée. On a alors une idée des tables mises en jeu • Si la question est complexe, il faut la reformuler et/ou la décomposer. • Une fois ce travail effectué, on peut commencer a écrire du SQL. 188 Reformulation d’une question (1) • Paraphrasage : – En exprimant de manière plus détaillée une requête, elle devient plus claire... (ou plus facilement exprimable) • Exemple : Qui est dans la base ? Ensemble des personnes de la base médicale Nom des médecins et nom des patients Union des noms des médecins et des noms des patients SELECT NomDoc NomPers FROM DOC UNION SELECT NomPat NomPers FROM PAT 189 Reformulation (2) : Négation • • Souvent l’inverse de la requête est plus facile à exprimer. Cela est particulièrement vrai lorsque la requête contient : Que – Dans quelle ville n’y a-t-il QUE des patients de plus de 40 ans? – Toutes les villes (où il y a au moins un patient de plus 40 ans), moins les villes où il y a un patient de 40 ans ou moins • Aucun – Dans quelle ville n’y a-t-il AUCUN patient de plus de 40 ans? – L’ensemble des villes, moins celles où il y a au moins un patient de plus de 40 ans • Tous (conduisant à une division relationnelle…) – Quels sont les patients qui ont RDV avec TOUS les médecins – Les patients pour lesquels il n’existe pas de docteur avec qui ils n’ont pas de RDV • Tous – Quels sont les patients dont TOUS les motifs de rendez vous sont « mal de tête » – L’ensemble des patients qui ont un RDV pour un « mal de tête », moins les patients qui ont un RDV pour un motif différent de « mal de tête » 190 Reformulation (3) : Décomposition • En décomposant, on simplifie la compréhension et on diminue les risques d’erreurs : – Quels sont les patients dont tous les motifs de rendez vous sont « mal de tête » • Paraphrase : – L’ensemble des patients qui ont un RDV pour un mal de tête moins ceux qui ont un RDV pour un motif différent • Décomposition : – Create view V1 AS …. (patients qui ont un RDV pour un mal de tête) – Create view V2 AS …. (patients qui ont un RDV pour un motif ≠ mal de tête) – SELECT NomPat FROM V1 EXCEPT SELECT NomPat FROM V2 191 Instance de tables • Il faut parfois utiliser plusieurs instances de la même table Comment savoir? • Lorsqu’une même table est utilisée pour obtenir deux informations de ‘sémantique différente’ (2 instances différentes d’entité) il faut prendre plusieurs instances de cette table • Exemple : Nom des patients ayant eu des RDV avec les docteurs "Dupont" et "Durand" RDV (R1) DOC (D1) Dupont RDV (R2) DOC (D2) Durand PAT (P) SELECT DISTINCT P.NomPat FROM PAT P, RDV R1, DOC D1, RDV R2, DOC D2 WHERE P.NumPat = R1.NumPat AND R1.NumDoc = D1.NumDoc AND D1.NomDoc = “Dupont” AND P.NumPat = R2.NumPat AND R2.NumDoc = D2.NumDoc AND D2.NomDoc = “Durand”; 192 Oubli de clauses • Il est assez facile d’oublier une clause dans les prédicats de sélection, surtout lorsque l’on joint plusieurs tables. • Pour le vérifier, une règle simple pour les requêtes plates (nécessaire mais non suffisante) : – Une requête impliquant n tables doit posséder au moins (n1) prédicats de jointure (outre les prédicats de sélection). 193 Doublons • Deux types de doublons peuvent apparaître – plusieurs réponses sont identiques – plusieurs réponses sont ‘sémantiquement équivalentes’ • Les premiers s’éliminent en utilisant la clause distinct. Attention toutefois à son utilisation... • Le deuxième cas se produit lorsque l’on demande des combinaisons (ex: couples de personnes, ensemble de 3 pièces etc...). Le résultat (A,B) est ‘sémantiquement équivalent’ à (B,A). – Dans ce cas, il suffit d’utiliser un prédicat > entre chaque composantes afin de n’obtenir qu’une seule des combinaisons équivalentes : SELECT P1.Nom, P2.Nom FROM Personne P1, Personne P2 WHERE P1.Nom > P2.Nom 194 Base de tests (1) • Quand une requête est vraiment complexe, on peut, malgré tout, avoir un doute sur la solution trouvée. Dans ce cas, il faut créer l’extension d’une base de test afin de vérifier la validité de la requête. • Preuve par l’exemple : – Un résultat positif ne prouve rien (c’est probablement juste...) – Un résultat négatif prouve que la requête est fausse • Comment trouver l’extension la plus ‘sensible’ possible ? – C’est à dire, l’extension de taille minimum comportant tous les cas et garantissant la validité de la requête – Le problème est presque aussi complexe que la résolution de la requête elle-même (mais souvent le fait d’aborder la requête de cette manière permet de mieux la comprendre…) 195 Base de tests (2) • Exemple 1 : Les couples de parents ayant au moins deux enfants? – Dans la base de test il faut avoir : • des couples de parents qui ont 1, 2 et 3 enfants • et des parents seuls (ayant 2 et 3 enfants) • Exemple 2 : Producteurs qui ne voient que les films qu’ils produisent – Les producteurs qui voient au moins 1 film qu’ils produisent, moins ceux qui voient au moins 1 film qu’ils ne produisent pas Réponse : SELECT DISTINCT P.Nom FROM Producteur P, Spectateur S WHERE P.Titre = S.Titre AND P.Nom = S.Nom MINUS SELECT DISTINCT P.Nom FROM Producteur P, Spectateur S WHERE P.Titre = S.Titre AND P.Nom != S.Nom Est-ce correct ? – Dans la base de test il faut avoir : • • • • un producteur qui ne voit aucun film un producteur qui ne voit que des films qu’il produit un producteur qui voit des films qu’il produit et d’autres Attention, cette base n’est pas forcément suffisante.... 196 Base de tests (3) SELECT DISTINCT P.Nom FROM Producteur P, Spectateur S WHERE P.Titre = S.Titre AND P.Nom = S.Nom MINUS SELECT DISTINCT P.Nom FROM Producteur P, Spectateur S WHERE P.Titre = S.Titre AND P.Nom != S.Nom Producteur.Nom Producteur.Titre Spectateur.Nom Titre1 Titre2 … Nom1 Nom2 … Nom1 Nom2 … Spectateur.Titre Producteur Nom1 qualifié, ne voit que le film qu’il produit. Producteur Nom2 non qualifié… Titre1 Titre1 … SELECT DISTINCT P.Nom FROM Producteur P, Spectateur S WHERE P.Titre = S.Titre AND P.Nom = S.Nom Producteur.Titre Spectateur.Nom Nom1 Nom1 Titre1 Titre1 Nom1 Nom2 Titre1 Titre1 Nom2 Nom2 Titre2 Titre2 Nom1 Nom2 Titre1 Titre1 Producteur.Nom Spectateur.Titre SELECT DISTINCT P.Nom FROM Producteur P, Spectateur S WHERE P.Titre = S.Titre AND P.Nom != S.Nom Producteur.Titre Spectateur.Nom Nom1 Nom1 Nom2 Titre1 Titre1 Titre2 Nom1 Nom2 Nom1 Titre1 Titre1 Titre1 Nom2 Titre2 Nom2 Titre1 Producteur.Nom Spectateur.Titre Un spectateur voit le film du producteur… Resultat = Le 2ème membre devrait être: SELECT Nom,Titre FROM Spectateur MINUS SELECT Nom,Titre FROM Producteur 197 Exercice SQL (suite) Interrogation 198 Concurrence d’accès 199 Problème de la concurrence d’accès • Un problème transactionnel • Les protocoles de concurrence sont nécessaires – Sinon conduit le SGBD à des erreurs • La difficulté : des protocoles performants • Les techniques – Degrés d’isolation – Multi-versions – Modification des programmes • Leur utilisation sur des exemples 200 Gestion de transactions Incohérence possible... Etat cohérent Etat cohérent Begin Commit Transaction Séquence d'instructions d’un programme de base de données encadrée par un ordre de début et de fin de transaction faisant passer la base d’un état cohérent à un état cohérent. Begin CEpargne = CEpargne - 3000 CCourant = CCourant + 3000 Commit T1 201 Les propriétés ACID • Problèmes liés aux transactions : – Concurrence d’accès – Violation de la cohérence – Pannes • • • • BD de transaction : ex. annulation du système : ex. crash serveur de media : ex. perte du disque de communication : ex. défaillance réseau • Un SGBD garantit l'exécution ACID de toute transaction : – – – – Atomicité : la transaction s'exécute en tout ou rien Cohérence : la transaction respecte les contraintes d'intégrité Isolation : la transaction ne "voit pas" l'effet des autres transactions Durabilité : les effets d'une transaction validée persistent 202 Atomicité / Durabilité ATOMICITE DURABILITE Panne Begin CEpargne = CEpargne - 3000 CCourant = CCourant + 3000 Commit T1 Begin CEpargne = CEpargne - 3000 CCourant = CCourant + 3000 Commit T1 Crash disque Annuler le débit !! Gestion d’un journal avant – images physiques avant modification – permet de ‘défaire’ une transaction n’ayant pas commise S’assurer que le virement a été reporté dans la BD ! Gestion d’un journal après • images physiques après modification • permet de ‘refaire’ une transaction qui a commise. 203 Isolation : définition du problème • Scénario 1 : – • Scénario 2 : – • Les places de la finale de la coupe du monde sont mises en vente sur Internet le lundi à 8h (conflits massifs) conflits massifs ! Scénario 5 : – • Une analyse des ventes (lectures) est faite dans un supermarché alors que les caisses sont ouvertes (écritures) La lecture ne doit pas bloquer les caisses ! Scénario 4 : – • Joe retire 200 euros (écritures) au même moment où sa banque le crédite de 500 euros (écritures) Blocker le crédit ? Ne pas le perdre ! Scénario 3 : – • Joe consulte son compte (lectures) au moment où sa banque le crédite de 500 euros (écritures) Blocker le crédit ? Le montrer à Joe ? Réservation SNCF (lectures (choix) puis écritures (achat)) Comment éviter de bloquer tout le monde ? Objectif : Chacun doit travailler en isolation i.e. comme si il était seul utilisateur de la base de données 204 Exécution sans contrôle • Absence de contrôle – – – – Perte d’opérations Observation d’incohérence Introduction d’incohérence Lectures non reproductibles Protocole de concurrence But: éviter les problèmes et rester performant • A base de verrouillage: – Verrou en lecture : partagé – Verrou en écriture : exclusif Scénario 2 – Perte d’opérations T1 (Joe) T2 (banque) Begin Lire CCx Begin Lire CCx x = x + 500 Ecrire X CC Commit T2 x = x –200 Ecrire X CC Commit T1 temps 205 verrous Protocole de Verrouillage à 2 Phases T Lecture temps Ecriture Lecture Ecriture • Règle 1 : verrouillage – Avant d'accéder à une donnée D, une transaction doit acquérir un verrou sur D. Si D est déjà verrouillée dans un mode non compatible, la transaction attend. • Règle 2 : deux phases – Dès qu'une transaction relâche un verrou, elle ne peut plus acquérir de nouveau verrou • Verrouillage 2 phases stricts – Les verrous sont gardés jusqu’au commit 206 Optimisation des transactions • L'objectif, en termes de performances, est de réduire : – les blocages : une transaction est bloquée en attente d’un verrou – les inter-blocages : un ensemble de transactions s’attendent mutuellement T1 • Solutions existantes T2 T3 T4 – Degrés d’isolation – Protocoles multiversions – Modification des données / programmes 207 Degrés d'isolation SQL – normalisés SQL2 • Objectif: accroître le parallélisme en autorisant certaines transactions à violer la règle d'isolation • Degrés standardisés SQL2 (Set Transaction Isolation Level) – Degré 0 (Read Uncommitted) • Lecture de données sales – Interdiction d’écrire. • Ex. lecture sans verrouillage – Degré 1 (Read Committed) • Lecture de données propres – Ecritures autorisées • Ex. Verrous court en lecture, long en écriture – Degré 2 (Repeatable Read) • Pas de lecture non reproductible • Ex. Verrous longs en lecture et en écriture – Degré 3 (Serializable) • Pas de requête non reproductible (fantôme) 208 Degrés d'isolation SQL : exemples T1(°0, Read uncommited) Begin Lit CC ( 100) T2(°3, serializable) Begin T1(°1, Read commited) Begin Lit CC ( 100) Lit CC ( 100) Ecrire CC, CC+10 Lit CC ( 110) Lit CC ( bloque) Ecrire CC, CC+20 Commit Lit CC ( 130) Commit Begin Lit CC ( 100) Lit CC ( 100) Begin Lit CC ( 100) Ecrire CC, CC+10 Ecrire CC, CC+20 T1(°2, Repeatable read) T2(°3, serializable) T2(°3, serializable) Begin ( 130) T1(°2, Repeatable read) Begin Select count(*) from Voit where couleur="rouge" Lit CC ( 100) Ecrire CC, CC+10 bloque Lit CC ( 100) Commit Ecrire CC, CC+20 T2(°3, serializable) Begin Insert into Voit values (R4, "rouge") Commit Select count(*) from Voit where couleur="rouge" Commit 209 Protocoles multiversions (Oracle) • Objectif : faire cohabiter sans blocage des transactions conflictuelles en les faisant travailler sur différentes versions des données T1(°1, Read commited) Begin Lit CC ( 100)(V1) T2(°3,serializable) Begin T1 (°3, serializable) Begin Lit CC ( 100)(V1) Lit CC ( 100)(v1) Ecrire CC, CC+10 (V2) Lit CC ( 100)(V1) T2(°3,serializable) Begin Lit CC ( 100)(v1) Ecrire CC, CC+10 (V2) Lit CC ( 100)(V1) Ecrire CC, CC+20 (V2) Commit Lit CC ( 130)(V2) T1 (°1, Read commited) T2(°3, serializable) Begin Begin Lit CC ( 100)(V1) Lit CC ( 100) (v1) Ecrire CC, CC+10 (V2) Ecrire CC, CC-50 (V?) bloque Ecrire CC, CC+20 (V2) Commit Ecriture faite (CC = 80) Ecrire CC, CC+20 (V2) Commit Lit CC ( 100)(V1) T1 (°3, serializable) Begin Lit CC ( 100)(V1) T2(°3,serializable) Begin Lit CC ( 100)(v1) Ecrire CC, CC+10 (V2) Ecrire CC, CC-50 (V?) bloque Ecrire CC, CC+20 (V2) Commit Erreur ! 210 Modifications des données / Programmes • Scénario 4 : Les places de la finale de la coupe du monde sont mises en vente sur Internet le lundi à 8h • Une seule donnée Trop de conflits • Exemple de solution : – Idée : partitionner le stade : – Chaque utilisateur se connectant est aléatoirement dirigé vers 1 des partitions. – Le degré de parallélisme est multiplié par le nombre de partitions. • Scénario 5 : Réservation SNCF (choix puis achat) – Si verrouillage dès le début, beaucoup de conflits – Sinon, risque de perdre la place convoitée – C’est le deuxième choix qui est implanté … 211 212 Conclusion sur le verrouillage • Scénario 1 : Compte Joe : lecture / écriture Multiversion + Joe en degré Repeatable Read ou Read Commited • Scénario 2 : Compte Joe : écriture / écriture Conflit inévitable quelque soit la méthode (mais rare) • Scénario 3 : lectures massives / écritures (supermarché) Multiversion + degré Read Commited (peu de versions) • Scénario 4 : écritures massives (coupe du monde) Partitionnement du stade • Scénario 5 : réservations SNCF (lectures puis écritures) Transaction 1 = Phase de choix en Multiversion et Read Commited Transaction 2 = Phase d’achat en Multiversion et Repeatable Read Il est donc particulièrement important de maîtriser les degrés d’isolations et les notions transactionnelles 212 Optimisation 213 Problème de l’optimisation • Un problème global – Touche l’ensemble des acteurs (pas seulement l’éditeur…) Select From Where Requête SQL Arbre logique Arbre Physique Optimisation 214 215 Un problème d’équivalence sémantique Coût • Une question • Plusieurs expressions équivalentes en SQL • Plusieurs expressions équivalentes en algèbre • Plusieurs algorithmes algébriques équivalents Ex. 9 jointures 17 Milliards de plans… Plans sémantiquement équivalents 215 216 Objectifs de l’optimisation • Objectif de l’optimiseur : trouver le meilleur plan … – Donnant les résultats le + vite …. • Optimisation pour le temps de réponse (response time) – Minimisant la consommation de ressources • Optimisation du travail total (Total work) – Minimisant le temps de délivrance des premiers tuples • Optimisation de la latence (Latency / First tuples …) • Qui optimise ? – Idéalement 2 requêtes équivalentes même plan, le meilleur ! Seuls les concepteurs de SGBD doivent maîtriser l’optimisation – Pratiquement, ce qui conduit à des plans différents • 2 modèles conceptuel/physiques différents (d’un même problème) • 2 opérations sémantiques équivalentes (série de requêtes SQL différentes) • 2 requêtes SQL équivalentes Le concepteur et le programmeur BD jouent un rôle majeur 216 217 Indexation (principe et B+Tree) • Objectif : accès rapide à partir d’une clé • Moyen : ajout de structures de données (généralement hiérarchiques) Jean • Exemple : B+Tree Adam Alain Ben Claire Felix Gil Hilda Jean Karine Martin Nicole Paul Sophie Théo Tom Zoe • Attention : – Surcoût lors des mises à jour ! – Des fois un accès par index est plus coûteux ! 217 218 Importance du Schéma Physique • L'utilisation d'index – – – – accélère l'exécution des sélections et des jointures ralentit l'exécution des mises à jour et des insertions offre des informations statistiques à l'optimiseur permet le contrôle efficace de contraintes d'intégrité • L'organisation des données – égaliser les I/O entre disques (log et bases, index et tables, partitions de tables sur ° disques) Ex: tablespaces en Oracle • Le choix des "bons" index et l'organisation des données sont déterminants pour les performances 218 219 Plan d’exécution optimisé Select From Where and and Patients.Nom, Patients.Prénom Patients, Visites Patients.Id-P = Visites.Id-P Patients.Ville = ’Paris’ Visites.Date = ’15 juin’ Patients Visites Patients Visites 219 220 Optimisation Physique … Jointure Patients Visites – – – – Nested Loop Join ? Index Join ? Hash Join ? Sort Merge Join ? Sélection – FTS (Full Table Scan) ? – Index Scan (B+tree) ? – Index Scan (Bitmap) ? 220 221 Algorithmes de jointure • Nested loop Join : Jointures par boucle imbriquées – Pour chaque visite, parcourir les patients • Index Join : Utilisation d’index sur une des relations – Pour chaque visite, retrouver le patient grâce à l’index • Sort Merge Join – Trier les visites sur le N° de patient – Trier les patients sur le N° de patient – Fusionner les deux tables triées (jointure ‘à deux doigts’) • Hash Join – Hacher les patients sur le N° de patient – Pour chaque visite, calculer la valeur de hachage et chercher dans la table de hachage le patient correspondant 221 222 Optimiseur heuristique • L’optimisation est indépendante des données – • Dépend uniquement de la requête SQL… Exemple d’heuristiques classiques – – – – Effectuer les sélections en premier Ajouter un maximum de projections Utiliser tous les indexes disponibles Utiliser les ‘meilleurs’ algorithmes de jointure, dans l’ordre 1. 2. 3. 4. • Hash join Sort merge join Nested Loop join avec index Nested loop join Conclusion – L’ordre des opérations dépends de l’expression SQL • – Ex = ordre des jointures déterminé par leur ordre d’apparition Présent dans Oracle = Rule Based Optimizer (RBO) 222 Optimisation basée coût • Dépend des caractéristiques des données • Présent dans Oracle (Cost Based Optimizer ou CBO) • Plus efficace que le RBO ! Graphe d'opérations Schéma interne Plans d'exécution (espace de recherche) Générateur de Plans Statégie de Recherche Bibliothèque de transformations Heuristiques de choix Modèle de coût Plan d'exécution Optimal 223 Difficultés de l’optimisation basée coût • Espace de recherche (plans candidats) – Plusieurs algorithmes pour chaque opérateur – Coûts et comportement différents – Plusieurs ordonnancement pour les opérations binaires • Sans considérer les algorithmes, il y a 1620 ordres possibles pour joindre 5 relations, et 17 milliards pour 10 relations ! Utilisation d’heuristiques et de programmation dynamique • Modèle de coût (choix du plan) – Difficulté pour estimer le coût de chaque opérateur – Difficulté encore plus importantes pour estimer la taille des résultats intermédiaires (permettant de calculer l’opérateur suivant) – Propagation exponentielle des erreurs (dans l’arbre d’exécution) ! Utilisation de statistiques re-calculés fréquemment 224 225 Les statistiques • Possibilité d’histogrammes – RunStat(<Table>, <attribut>) construction et stockage d’un histogramme de variation de l’attribut dans la table. – Utilisation par le modèle de coût – Sinon, hypothèse d ’uniformité • Exemple : – Personnes ayant un salaire entre 2K€ et 4 K€ ? 20% – Personnes ayant 2 véhicules ? 15% – Personnes ayant 2 véhicules et un salaire entre 2 et K4 K€? 3% ? Non ! En fait, 14% 15% 20% 0 0.5 1 1.5 2.0 2.5 3.0 3.5 4 0 1 2 3 4 225 Qualité de l’optimisation 226 1. Qualité du schéma physique – Indexes – Partitionnement, placement – Configuration 2. Qualité de l'optimiseur (heuristique/coût) – Qualité du modèle de coût utilisé – Qualité de la stratégie de recherche de l'optimiseur 3. Qualité de l’administration – Qualité des traces ou indicateurs générés par le système – Qualité des outils d'aide à l'administration – Qualité de l’administrateur ! 4. Qualité des développeurs d’application ? 226 227 Réglage du SGBD – Database Tuning • Réglages du SGBD, amélioration des performances – Manuel par l’administrateur BD – par des outils externes de diagnostiques – par des outils intégrés automatiques • Oracle : Automatic SQL Tuning • SQL Server : Database Tunning Advisor 227 Oracle : Automatic SQL Tuning Statistiques manquantes SQL Profile Index manquants Mauvaises constructions SQL SQL Tuning Advisor Requête SQL Automatic Tuning Optimizer Analyse des Statistiques SQL Profiling Analyse des chemins d’accès Analyse des structures SQL 228 229 Conclusion sur l’optimisation • Mécanismes puissant mais complexes à maîtriser • Fort impact sur les performances du système • Les performances dépendent d’autres facteurs – Schéma physique – Configuration du serveur (mémoire, disques, etc.) – Administration adéquate • De plus en plus de tâches automatiques ou automatisables – – – – Gestion de la mémoire automatique dans Oracle 9 Automatic SQL Tuning dans Oracle 10 Database Tunning Advisor dans SQL Server 2005 Etc. • Mais aussi : une bonne application BD des concepteurs qui connaissent le SGBD 229 Expérimentations sur Oracle Quels protocoles de concurrence? Quelles techniques d’optimisation? 230 Programmation SQL PL/SQL et JDBC 231 Plan de la journée • Cours – Introduction des techniques de programmation SQL – Langage PL/SQL Oracle – Programmation JDBC • Exercices sur Oracle Express – Programmation PL/SQL • Bloc PL/SQL • Curseurs PL/SQL • Fonctions, procédures, packages – Programmation JDBC • Connexion à la base • Traitements JDBC Ressources: - Eclipse/JAVA - OracleXE – Comparaison des performances SQL, PL/SQL, JDBC 232 Programmation SGBD: objectif • Comment passer une commande dans la base telle que : PROCEDURE COMM Si le client n’est pas encore dans la base Insérer les informations du client dans la table client Si l’article est disponible dans la quantité commandée ET le livreur disponible à la date de livraison désirée Insérer sa commande dans la base Sinon abandonner la commande *cette procédure peut être déclenchée par un trigger ! • Impossible en SQL pur (SQL n’est pas un langage complet) – Pas de structure de contrôle : itérations, tests … – Besoin d’un langage complet pour programmer des actions sur les BD • Les différentes possibilités – Utiliser un langage traditionnel (C, C++, Java, Cobol, …) ...qui puisse parler avec la BD (avec SQL grâce à SQL CLI, JDBC, ODBC, …) – Utiliser un langage propriétaire (PL/SQL Oracle, TSQL MS-SQLServer, …) …fourni par l’éditeur du SGBD … pour créer des procédures stockées coté serveur 233 Programmation SGBD: outils • Utiliser un langage traditionnel – Qui appelle des API Propriétaires fournies par l’éditeur de SGBD • Ex: OCI (Oracle), DB-Lib (Sybase), … • Programme écrit dans un langage classique (C, C++, Cobol, etc.) … avec des appels à ces API non standards fournies par l’éditeur du SGBD – Qui appelle des API indépendantes du SGBD • SQL-CLI (Call Level Interface) • Popularisé par les médiateurs ODBC, JDBC – Embedded SQL (générateur de code + API propriétaires) • Ex: Pro*C (Oracle), ECPG (Postgres), Pro*Cobol (Oracle), etc… • Programme écrit dans un langage classique (C, C++, Cobol, etc.) … avec des instructions SQL directement dans le programme • Précompilation : le source (ex: C+SQL) … … est transformé en code compilable (ex: C pur) • Suivi d’une compilation classique (ex: C pur) • Utiliser un langage « SGBD » propriétaire (fourni par l’éditeur) – Ex: PL/SQL Oracle, PL/pgSQL (PostgreSQL), T-SQL (SQLServer), … – Extension de SQL à des éléments de programmation procédurale • Instructions conditionnelles, itérations, variables, etc. 234 Oracle PL/SQL Procedural Language / Structured Query Language (Langage propriétaire Oracle) 235 Langages procéduraux dédiés • Stockage de procédures sur le serveur – Chargement et compilation de la procédure sur le serveur – Lancement à distance par appel de procédure (EXECUTE) Client Execute COMM Résultat exécution COMM PL/SQL Moteur Serveur SQL PL/SQL BD Serveur Oracle – Stocké comme un objet base de données • Le créateur a les droits d’exécuter, modifier, re-compiler la procédure • Partage les droits avec d’autres (GRANT/REVOKE EXECUTE) 236 Bloc PL/SQL • Bloc de déclaration de variables: DECLARE <liste des variables utiles au programme> • Suivi d’un bloc d’instructions et exceptions: BEGIN <liste d’instructions du programme PL/SQL> [ EXCEPTION <instructions exceptionnelles> ] END; / 237 Types des variables PL/SQL (1) • Types de base – Types Oracle (CHAR, NUMBER, DATE...) – Type Booléen : boolean – Types référençant le dictionnaire de données : table.col%TYPE • Types complexes – Record • TYPE monType IS RECORD (champ1 NUMBER, champ2 VARCHAR2); – Table • TYPE maListe IS TABLE OF NUMBER ; – Curseurs (cf. plus loin) 238 Types et variables PL/SQL (2) • Déclaration des variables dans le bloc declare DECLARE maVar maVar maVar maVar VARCHAR2 DEFAULT ‘ROUGE’; Personne.nom%TYPE; -- type de l’attribut concerné Personne%ROWTYPE; -- type RECORD MonCurseur%ROWTYPE; -- type RECORD • Affectation de valeur – Dans toutes les sections avec l’opérateur := maVar := 2; – Dans la section begin end avec l’opérateur into Select max(table.col) into maVar from table; • Appel de variables extérieures dans le bloc begin end – Variables SQL*FORMS préfixées par : – Variables SQL*PLUS préfixées par & 239 Structures de contrôle PL/SQL • Traitement conditionnel IF condition THEN traitement1 [ELSIF condition THEN traitement2] [ELSE traitement3] END IF; • Itérations WHILE condition LOOP traitement END LOOP; FOR i IN 1..n LOOP traitement END LOOP; LOOP traitement EXIT WHEN condition END LOOP; 240 Curseurs PL/SQL : déclaration • Résultat d’une requête SQL géré comme un fichier séquentiel • Déclaration DECLARE CURSOR monCurseur IS requête_SQL; • Curseurs avec paramètres – Paramètre fixés à l’ouverture du curseur DECLARE CURSOR monCurseur (nomRecherche IN VARCHAR2) IS SELECT nom, adresse FROM Personne WHERE nom = nomRecherche; 241 Curseurs PL/SQL : manipulation (1) • Attributs de curseur – %NOTFOUND, %FOUND, %ISOPEN, %ROWCOUNT – S’évaluent à true, false, null, ou au numéro de tuple • commandes classiques d’ouverture, lecture, fermeture • OPEN, FETCH, CLOSE DECLARE CURSOR dept_10 IS SELECT Nom, Salaire FROM employes WHERE NumDept = 10; tuple dept_10%ROWTYPE; BEGIN OPEN dept_10; LOOP FETCH dept_10 INTO tuple; ......... EXIT WHEN(dept_10%NOTFOUND) END LOOP; CLOSE dept_10; END; 242 Curseurs PL/SQL : manipulation (2) • Manipulation simplifiée – – – – – Déclaration implicite du curseur Déclaration implicite de l’enregistrement récepteur Ouverture et fermeture du curseur implicites Ordre de lecture pas à pas fetch implicite Condition de sortie implicite BEGIN FOR tuple IN (SELECT Nom, Salaire FROM employes WHERE NumDept = 10) LOOP ......... END LOOP; END; 243 Curseurs PL/SQL : exemple • Augmente de 5% le salaire des employés du service compta… DECLARE CURSOR Compta IS SELECT nom, salaire FROM Employe WHERE service = ‘comptabilité’; Emp Compta%ROWTYPE; BEGIN OPEN Compta; FETCH Compta INTO Emp; WHILE Compta%FOUND LOOP IF Emp.salaire IS NOT NULL AND Emp.Salaire < 30.000 THEN UPDATE Employe SET salaire = salaire*1,05 WHERE nom = Emp.nom; END IF; FETCH Compta INTO Emp; END LOOP; CLOSE Compta; END; 244 Exceptions prédéfinies (1) • Levées automatiquement par le moteur PL/SQL – CURSOR_ALREADY_OPEN, INVALID_CURSOR – NO_DATA_FOUND, LOGIN_DENIED, PROGRAM_ERROR – ZERO_DIVIDE, DUP_VAL_ON_INDEX… • Traitées par la procédure PL/SQL section begin end BEGIN … EXCEPTION WHEN CURSOR_ALREADY_OPEN THEN BEGIN Affiche_Err (-20000, ‘problème…’) ; END; END; 245 Exceptions prédéfinies (2) • Exemple de traitement lors de la levée de l’exception oracle DUP_VAL_ON_INDEX – Détecte les doublons sur la clé primaire dans la table BEGIN INSERT INTO employes VALUES (num, nom, salaire); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN INSERT INTO doublons VALUES (num, nom); END; 246 Exceptions définies par l’utilisateur (1) • Déclaration dans la section declare • Levée dans la section begin end par l’instruction raise • Traitement de l’exception dans la section begin end – Dans la sous section exception – Lors de la détection dans la clause when • La clause when others traite les exceptions non traitées DECLARE sal_nul EXCEPTION; … BEGIN … IF … THEN RAISE sal_nul; END IF; EXCEPTION WHEN sal_nul THEN … WHEN OTHERS THEN … END; 247 Exceptions définies par l’utilisateur (2) • Exemple – Lève une exception quand la température du four dépasse 1000°… DECLARE Err_Temp EXCEPTION; MaxTemp NUMBER; BEGIN SELECT Max(temp) INTO MaxTemp FROM Four; IF MaxTemp > 1000 THEN RAISE Err_Temp END IF; EXCEPTION WHEN Err_Temp THEN BEGIN Affiche_Err (-200, ‘le four va exploser’) ; END; END; 248 Procédures et fonctions PL/SQL (1) • Stockées sous forme compilée dans la base de données, de la même façon qu’un objet de la base – soumise aux mécanismes de sécurité ou de confidentialité • Partagées par plusieurs applications et utilisateurs – à condition d’avoir le privilège EXECUTE • Procédure – unité de traitement qui peut contenir des instructions • SQL (sauf DDL), PL/SQL, variables, constantes, curseurs et gestionnaire d’erreurs • Fonction – procédure qui retourne une valeur 249 Procédures et fonctions PL/SQL (2) • Création de procédure CREATE [OR REPLACE] PROCEDURE nom_procédure [(liste_arguments)] <IS|AS> declaration_variables bloc_PLSQL liste_arguments ::= nom_argument_1 {IN | OUT | IN OUT} type, nom_argument_2 {IN | OUT | IN OUT} type, …... nom_argument_n {IN | OUT | IN OUT} type • Création de fonction CREATE [OR REPLACE] FUNCTION nom_fonction [(liste_arguments)] RETURN type {IS | AS} declaration_variables bloc_PLSQL • Re-compilation de procédure et fonction en cas de modification du schéma de la BD ALTER <PROCEDURE | FUNCTION> nom COMPILE; • Suppression de procédure et fonction DROP {PROCEDURE | FUNCTION} nom; 250 Procédures et fonctions PL/SQL (3) • Exemple de procédure – Modifie le prix d’un article d’un certain taux CREATE PROCEDURE modif_prix (id IN NUMBER, taux IN NUMBER) IS BEGIN UPDATE article a SET a.prix_unitaire = a.prix_unitaire*(1+taux) WHERE a.id_article = id; END; • Exemple de fonction – Calcule le chiffre d’affaire CREATE FUNCTION chiffre_affaire (id IN NUMBER) RETURN NUMBER IS ca NUMBER; BEGIN SELECT SUM(montant) INTO ca FROM vendeurs WHERE id_vendeur = id; RETURN ca; END; 251 Package PL/SQL • Package – regroupement de programmes dans un objet de la BD CREATE [OR REPLACE] PACKAGE nom_Package <IS|AS> déclaration_variables déclaration_exceptions déclaration_procédures déclaration_fonctions END nom_Package; CREATE [OR REPLACE] PACKAGE BODY nom_Package <IS|AS> déclaration_variables_globales corps_procédures corps_fonctions END nom_Package Visible par l’application (public) Interne au package (privé) 252 Package PL/SQL : exemple CREATE PACKAGE traitements_vendeurs IS FUNCTION chiffre_affaire (id_Vendeur IN NUMBER) RETURN NUMBER; PROCEDURE modif_com (id IN NUMBER, tx IN NUMBER); END traitements_vendeurs; CREATE PACKAGE BODY traitements_vendeurs IS FUNCTION chiffre_affaire (id_Vendeur IN NUMBER) RETURN NUMBER IS ca NUMBER; BEGIN SELECT SUM(montant) INTO ca FROM vendeurs WHERE id_vendeur = id; RETURN ca; END; PROCEDURE modif_com (id IN NUMBER, taux IN NUMBER) IS BEGIN UPDATE vendeur v SET v.com = v.com*(1+taux) WHERE v.id_vendeur = id; END; END traitements_vendeurs; 253 Conclusion langages dédiés • Les langages de type PL/SQL constituent le mode d'utilisation le plus courant des bases de données relationnelles – Utilisé pour programmer des procédures stockées ou des triggers – Performant (réduit le transfert réseau) – Bien adapté aux clients légers (smartphone…) car déporte des traitements sur le serveur • Utilisé par les outils de développement de plus haut niveau (SQLforms) 254 API de communication SGBD Exemple de JDBC (Java DataBase Connectivity) 255 API de communication à un SGBD • SQL-CLI (Call Level Interface) – API standardisée par X/Open depuis 1994, intégrée dans SQL3 – Permet de se connecter et d’envoyer des requêtes SQL à tout serveur SQL, quel que soit son type, sa localisation, le mode de connexion ... • Popularisé par des médiateurs tels que – ODBC (Open DataBase Connectivity) • API assez complexe • interface C (nécessite l’utilisation de code natif) – JDBC (Java DataBase Connectivity) • vue “objet” des mêmes concepts que ODBC • un pont JDBC-ODBC existe 256 L’API JDBC (Java DataBase Connectivity) • Permet de simplifier l’accès à une BD depuis Java (évite l’utilisation de code natif) • JDBC fournit un ensemble de classes et d’interfaces pour l’utilisation d’un ou plusieurs SGBD à partir d’un programme Java • Intérêts de JDBC – – – – interface uniforme pour accéder à un SGBD indépendant du SGBD cible simplicité de mise en oeuvre supportant les fonctionnalités de base de SQL 257 Architecture logicielle de JDBC • Chaque source de données utilise un pilote (driver) qui lui est propre et qui convertit les requêtes JDBC dans le langage natif du SGBD Java Appli/Applet API JDBC JDBC DriverManager API du Driver JDBC Driver(pont) JDBC-ODBC driver ODBC protocole Propriétaire Oracle Sybase driver JDBC pour Oracle Driver JDBC pour Sybase driver SGF local protocole Propriétaire Fichier Oracle Sybase Différentes implémentations de JDBC 258 Exemples d’utilisations de JDBC • Dans un client léger classique API JDBC Applet JDBC Clients API Driver JDBC Driver JDBC BD Serveur • Dans une architecture J2EE API Web (TCP/IP) Applet Clients API JDBC Servlet Driver JDBC JDBC Serveur Web API Driver JDBC BD Serveur BD 259 L’API JDBC • API fournie par le package java.sql (le noyau) • Interfaces (principales) du package – Connection // gestion des connexions – Statement // exécution de requêtes classiques – PreparedStatement // préparation de requêtes dynamiques – CallableStatement // appel de procédures stockées – ResultSet // manipulation du résultat – ResultSetMetaData // description du résultat – DatabaseMetaData // description de la base – Driver // gestion des pilotes • Les extensions dans javax.sql 260 Utilisation de JDBC Etapes à suivre • • • • • • (Importer le package java.sql) Charger le driver JDBC Établir une connexion à la base de données Créer une instruction (requête/batch/procédure…) Exécuter l’instruction Traiter le résultat Fermer les différents éléments 261 Les fonctions de JDBC • Fonctions de bases – Charger le/les pilotes – Se connecter à la base de données – Créer une instruction • SQL statique, • appel à procédure stockée, • précompilée et paramétrée • Fonctions avancées – Exceptions JDBC – Accès aux méta-données • Du résultat • De la base de données – Exécution de • requêtes précompilées • requêtes paramétrées • procédures stockées – Exécuter l’instruction • LMD de consultation, • LDD et LMD de mise à jour, • procédures stockées – Traiter le résultat • Gestion de l’objet ResultSet – – – – Exécution par lots (batch) Utilisation de curseurs Transactions Extension standard (javax.sql) – Fermer les différents éléments 262 Chargement du pilote JDBC • Par invocation de la méthode Class.forName • Paramètre = chemin de la classe du driver – (fournit dans la doc. du driver) • Exemple utilisant le pont (driver) JDBC-ODBC import java.sql.*; public class ObjetUtilisantJDBC { public static void main(String[] Args) { try {Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); } catch (Exception E) {System.err.println(« Chemin du driver incorrect!");} // ... Etapes suivantes ... } } 263 Connecter la base de données • DriverManager permet d’établir la connexion avec le SGBD • Grâce à la méthode getConnection de cette classe – le driver adéquat est retrouvé parmi les drivers chargés – un objet Connection est retourné • Paramètres de la méthode – Url fournit les informations nécessaires à la connexion • débute par jdbc:, suivi du sous-protocole (par ex. odbc), suivi par des informations propres au driver (par ex: le nom de la source de données dans le cas du pont JDBC-ODBC) – Nom et mot de passe de l’utilisateur sur le SGBD • Exemple import java.sql.*; public class ObjetUtilisantJDBC { public static void main(String[] Args) { // ... Etapes précedentes... Connection con = DriverManager.getConnection(jdbc:odbc:Oracle, "scott", "tiger"); } } 264 Créer une instruction • Une instruction est représentée par une instance de la classe – Statement, (instruction SQL statique) – CallableStatement, (appeler une procédure stockée) – ou PreparedStatement (instruction SQL précompilée et paramétrée) • La création se fait à partir de l’objet Connexion en utilisant (l’objet Connexion est obtenu à la connexion) – createStatement (SQL statique) – prepareCall (procédure stockée) – prepareStatement (SQL précompilé/paramétré) • Exemple import java.sql.*; public class ObjetUtilisantJDBC { public static void main(String[] Args) { // ... Etapes précedentes... Statement req1 = con.createStatement(); CallableStatement req2 = con.prepareCall(str); PreparedStatement req3 = con.prepareStatement(str); } } 265 Exécuter l’instruction • Méthodes (principales) pour exécuter une instruction – executeQuery pour les requêtes de type SELECT ... (LMD de consultation) – executeUpdate pour les CREATE ou INSERT/DELETE/UDATE... (LDD et LMD de mise à jour) – execute pour les procédures stockées (entre autre) • Les méthodes de l’interface Statement (exécution de requêtes SQL statiques) prennent en paramètre une chaîne de caractères représentant la requête à exécuter • Exemple import java.sql.*; public class ObjetUtilisantJDBC { public static void main(String[] Args) { // ... Etapes précedentes... req1.executeUpdate("CREATE TABLE contact (nom VARCHAR(50), tel CHAR(10))"); req1.executeUpdate("INSERT INTO contact VALUES (’dupond’, ’0102030405’)"); } } 266 Traiter le résultat • La méthode executeQuery retourne un objet de type ResultSet – représente un curseur sur le résultat de la requête – Exemple // ... Etapes précedentes... ResultSet rs = req1.executeQuery("SELECT * FROM contact"); • La méthode executeUpdate retourne le nombre – de tuples affectés dans le cas d’un LMD (INSERT/DELETE/IPDATE…) – 0 dans le cas d’un LDD (CREATE…) – Exemple // ... Etapes précedentes... int nbTuples = req1.executeUpdate("UPDATE contact " + "SET nom = ’dupont’ " + "WHERE nom = ’dupond’"); 267 Accès aux résultats (curseur) • Un curseur permet de parcourir les tuples résultats d’une requête – Représenté dans JDBC par l’interface ResultSet • Fonctionnement – Le curseur d’un objet ResultSet est positionné avant le premier tuple – La méthode next permet de faire avancer ce curseur sur le tuple suivant • retour = false il n’y a plus de tuples à traiter • Les valeurs des attributs sont obtenues grâce aux méthodes getXXX( ) – Paramètre = numéro/nom de colonne dans le résultat • Exemple // ... ResultSet rs = req1.executeQuery("SELECT * FROM contact"); while(rs.next()) { String nom = rs.getString("nom"); // ou String nom = rs.getString(1); String tel = rs.getString("tel"); // ou String nom = rs.getString(2); // ... } 268 Accès aux résultats (conversion et valeur nulles) • Conversion de type – Les méthodes getXXX convertissent de SQL vers JAVA • Valeurs nulles (NULL de SQL) SQL CHAR VARCHAR LONG String Boolean Byte Short Etc… Java String String VARCHAR NUMERIC TINYINT SMALLINT INTEGER – Testé grâce à la méthode wasNull de ResultSet • wasNull est appelée après la lecture de la valeur (après l’utilisation d’une méthode getXXX) – Les méthodes getXXX convertissent les valeurs NULL de SQL en une valeur acceptable pour le type Java cible 269 Fermer les différents éléments • (préférable pour libérer la mémoire) • Les éléments concernés – – – – Curseurs Instructions Connexions Etc. • Exemple // ... Etapes précedentes... rs.close(); req1.close(); c.close(); // ... 270 Les fonctions de JDBC • Fonctions de bases – Charger le/les pilotes – Se connecter à la base de données – Créer une instruction • SQL statique, • appel à procédure stockée, • précompilée et paramétrée • Fonctions avancées – Exceptions JDBC – Accès aux méta-données • Du résultat • De la base de données – Exécution de • requêtes précompilées • requêtes paramétrées • procédures stockées – Exécuter l’instruction • LMD de consultation, • LDD et LMD de mise à jour, • procédures stockées – Traiter le résultat • Gestion de l’objet ResultSet – Fermer les différents éléments – – – – Exécution par lots (batch) Utilisation de curseurs Transactions Extension standard (javax.sql) SKIP 271 Exceptions • L’exception SQLException peut être lancée par les méthodes de JDBC • Elle permet d’obtenir des informations sur l’erreur qui s’est produite – une chaîne décrivant l’erreur (méthode getMessage) – un code d’erreur spécifique au SGBD (méthode getErrorCode) – une chaîne d’état SQL (méthode getSQLState) • Les avertissements du SGBD peuvent aussi être capturés par la classe d’exception SQLWarning – Un avertissement ne stoppe pas l’exécution du programme – On utilise la méthode getWarnings de Statement pour les récupérer 272 Accès aux méta-données • Procurent des informations (méta-données) sur – le SGBD et le driver JDBC (DatabaseMetaData), – ou sur le résultat d’une requête (ResultSetMetaData) • Exemples de méthodes de l’interface DatabaseMetaData – getSchemas pour la liste des schémas disponibles – getTables pour la liste des tables • Exemples d’utilisation de ResultSetMetaData // ... ResultSet rs = req1.executeQuery("SELECT * FROM contact"); ResultSetMetaData rsmd = rs.getMetaData(); int nbColonnes = rsmd.getColumnCount(); // ... 273 Requête précompilée (création) • Une requête qui doit être exécutée peut être précompilée en utilisant PreparedStatement – Dans un objectif de performance (temps d’exécution) – La requête est transmise au SGBD (qui la précompile) • Le plan d’exécution est préparé – Des variables peuvent être utilisées pour paramétrer la requête • Création d’une requête précompilée – A partir de l’objet Connection – Grâce à méthode prepareStatement • La requête est passée en paramètre sous forme de chaîne de caractères • Les paramètres sont représentés par des ‘?’ dans la chaîne – Le retour est un objet PreparedStatement prêt à être exécuté • Exemple // ... String req = "UPDATE contact SET tel = ? WHERE nom = ?"; PreparedStatement UpdateTel = con.prepareStatement(req); // ... 274 Requête précompilée (paramétrage) • Avant d’exécuter la requête fixer les paramètres (remplacent les ‘?’ par une valeur dans la chaîne) – Grâce aux méthodes setXXX (où XXX représente un type de données Java) – NB: setNull fixe le paramètre à la valeur NULL de SQL – Premier argument = numéro d’ordre du ‘?’ à remplacer – Deuxième argument = valeur de remplacement • Exemple // ... String req = "UPDATE contact SET tel = ? WHERE nom = ?"; PreparedStatement UpdateTel = con.prepareStatement(req); UpdateTel.setString(1, "0908070605"); UpdateTel.setString(2, "durand"); // ... 275 Requête précompilée (exécution) • Grâce aux mêmes méthodes que pour des requêtes fixes, mais sans argument – ExcuteQuery/executeUpdate – NB: l’instruction contient déjà la requête à exécuter… • Exemple // ... String req = "UPDATE repertoire SET tel = ? WHERE nom = ?"; PreparedStatement updateTel = con.prepareStatement(req); updateTel.setString(1, "0908070605"); updateTel.setString(2, "durand"); updateTel.executeUpdate(); updateTel.setString(1, "0900000000"); updateTel.executeUpdate(); // ... 276 Procédures stockées • L’interface CallableStatement permet d’invoquer une procédure stockée – NB: la procédure est stockée au niveau du SGBD… – … la création se faisant avec la méthode prepareCall de Connection // ... CallableStatement cs = c.prepareCall("{call nom_procedure_stockee}"); // ... • La syntaxe de l’appel de procédure (call ...) est traduit par le driver JDBC dans la syntaxe native du SGBD • L’exécution de l’instruction se fait de façon classique en fonction de ce que retourne la procédure • L’interface CallableStatement est sous-interface de PreparedStatement et accepte donc des paramètres 277 Transactions (portée) • Rappel = une transaction permet d’exécuter un ensemble de requêtes sur une BD, la faisant passer d’un état cohérent à un autre état cohérant, respectant les propriétés transactionnelles (ACID) • Par défaut, la connexion JDBC est en mode chaîné – chaque requête est suivie d’un ordre SQL « commit » implicite • On peut désactiver ce mode – soit lors de la création de la connexion – soit grâce à la méthode setAutoCommit – La validation doit alors se faire explicitement par la méthode commit… – … et l’annulation se fait grâce à la méthode rollback // ... c.setAutoCommit(false); // ... // ... c.commit(); // ... c.rollback(); // ... 278 Transactions (concurrence) • Le niveau d’isolation permet de déterminer les incohérences que peut subir une transaction (lecture sales, fantômes, . . .) • Les différents niveaux d’isolation (normalisés) sont – Connection.TRANSACTION_READ_UNCOMMITTED • lectures sales, non reproductibles et fantômes peuvent se produire – Connection.TRANSACTION_READ_COMMITTED • les lectures sales sont évitées – Connection.TRANSACTION_REPEATABLE_READ • les lectures sales et non reproductibles sont évitées – Connection.TRANSACTION_SERIALIZABLE • la transaction est complètement sérialisable – Connection.TRANSACTION_NONE • indique que les transactions ne sont pas supportées • Les méthodes getTransactionIsolation et setTransactionIsolation de Connection permettent de connaître 279 Apports de JDBC 2.0 et 3.0 (1/2) • Amélioration des curseurs – Parcours dans les 2 sens, positionnement sur un tuple donné,… – Méthodes nouvelles: • previous, first, beforeFirst, last, afterLast, absolute, relative, … • Mise à jour de la BD au travers des méthodes de Java – Notamment, au travers de curseurs – UPDATE • Méthode updateString de ResultSet, • puis updateRow pour reporter les changements dans la BD (avant de déplacer le curseur sur un autre tuple) – INSERT • Méthode moveToInsertRow pour déplacer le curseur vers une zone particulière d’insertion, • puis méthodes updateXXX pour fixer les valeurs des attributs du tuple, • puis méthode insertRow pour insérer le tuple dans le ResultSet et dans la BD • NB: la méthode moveToCurrentRow permet de revenir sur le tuple courant (sur lequel le curseur se trouvait avant l’appel de moveToInsertRow) – DELETE – Positionner le curseur sur le tuple à supprimer, – puis appel à la méthode deleteRow pour effectuer la suppression 280 Apports de JDBC 2.0 et 3.0 (2/2) • Envoyer plusieurs instructions à la BD en une fois – Exécution par batch – Avec la méthode addBatch de Statement ajouter une requête dans la liste à exécuter • NB: La liste peut être effacées avec la méthode clearBatch – L’exécution des requêtes est ensuite réalisée par la méthode executeBatch • Cette méthode retourne un tableau d’entiers représentant le nombre de tuples modifiés par chaque instruction • L’exception BatchUpdateException peut être lancée si une erreur se produit lors des mises à jour • Support des types SQL3 – Blob, Clob, Array, etc. et ajout des méthodes getXXX et setXXX correspondantes 281 Exercices de programmation SQL Procédures PL/SQL sur OracleXE Programmation JDBC sur OracleXE 282 Contraintes d’intégrité Triggers 283 Plan de la journée 1. Contrôle de l’intégrité des données – Typologie et spécification des contraintes d’intégrité • Norme SQL2 • Implémentation dans Oracle (bcp de différences avec la norme !) – Analyse, contrôle des contraintes par le SGBD 2. Les déclencheurs – Définition de la norme SQL2 – Implémentation dans Oracle (bcp de diff. avec la norme !) 3. Exercices sur Oracle – Création de contraintes : SQL pur, SQL check ou Trigger ? 284 Contrôle d’intégrité • But – Détecter les mises à jour erronées – Réagir • rejeter la transaction • compenser les erreurs • Mise en oeuvre – Langage de définition de contraintes d'intégrité – Processus de vérification automatique des contraintes • Bénéfice supplémentaires de cette implémentation – Simplifie le code des applications – Mise en commun des contraintes – Cohérence globale des contraintes 285 Types de contraintes • Contraintes structurelles – Spécifient la structure des données de la base • Tables, colonnes, lignes – Trois sortes de contraintes structurelles • Contrainte d’entité • Contrainte référentielle • Contrainte de domaine • Contraintes comportementales – Spécifient une règle d’évolution des données – Plusieurs sortes de contraintes comportementales • • • • Domaine de variation Contraintes temporelles Contraintes équationnelles Dépendances généralisées, … 286 Contraintes structurelles • Contrainte d‘entité – Contrainte de clé • Un groupe d’attributs clé par relation • Ex: identifiant de chaque personne – Contrainte de non nullité • Valeur d’attribut doit être renseignée • Ex: nom de chaque personne – Contrainte d’unicité • Valeur d’attribut doit être unique • Ex: numéro de sécurité sociale CREATE TABLE personne ( id INTEGER PRIMARY KEY, nom VARCHAR(15) NOT NULL, num_sécu NUMBER(15) UNIQUE NOT NULL, nom_ent VARCHAR(15) REFERENCES entreprise ); • Contrainte référentielle – Valeurs d’un d’attribut existent comme clé d’une relation • Contrainte de domaine – typage des valeurs de l’attribut 287 Contraintes comportementales (1) • Domaine de variation – Précise l’ensemble des valeurs permises d’un attribut – L’ensemble peut être une liste de valeurs discrète ou non – Ex: couleur rouge, blanc, rosé • Contraintes temporelles (SQL2) – Comparent ancienne et nouvelle valeurs d’un attribut – Ex : degré du vin ne peut décroître • Contraintes équationnelles – Comparent des expr. arithmétiques sur les données de base – Peuvent être temporelles (ancienne et nouvelle valeur) – Ex : qté produite qtés bues CREATE TABLE Vin (…, couleur VARCHAR(5) CHECK ( couleur IN ('rouge', 'blanc', 'rosé') ), …) TRIGGER degré_croissant BEFORE UPDATE OF degré ON vin REFERENCING OLD ROW AS vin_avant NEW ROW AS vin_après FOR EACH ROW WHEN (vin_après.date > vin_avant.date AND vin_après.degré < vin_avant.degré ) BEGIN lancerException END CREATE ASSERTION quantite_produite CHECK ( ( SELECT SUM(qte) FROM Vins) > ( SELECT SUM(qte) FROM Buveur) ) 288 Contraintes comportementales (2) • Dépendances fonctionnelles – valeur {attributs} valeur {autres attributs} – Ex : cru et année d’un vin degré • Dépendances multivaluées – valeur {attributs} ! {valeurs {autres attributs}} – Ex : prix vente > coût production • Dépendances d’inclusion – {valeurs {attributs}} {valeurs {autres attributs}} – Ex : client.nom personne.nom – Cas part. : dépendance référentielle • {valeurs {attributs}} {valeurs {attributs_clé}} • Ex : client.nom_ent ent.nom CREATE ASSERTION dependance_fonctionnelle CHECK( NOT EXISTS (SELECT * FROM Vin V1, Vin V2 WHERE V1.cru = V2.cru AND V1.année = V2.année AND V1.degré V2.degré )); CREATE ASSERTION dependance_nultivaluée CHECK( NOT EXISTS (SELECT * FROM Vente V1, Vente V2 WHERE V1.prix > V2.cout)); CREATE ASSERTION dependance_inclusion CHECK ( NOT EXISTS ( SELECT * FROM Cli C WHERE nom NOT IN ( SELECT C.nom FROM Pers P WHERE C.nom = P.nom) ) ); CREATE TABLE Client (…, nom_ent VARCHAR(20) REFERENCES Ent(nom), …) 289 Expression des contraintes en SQL2 • Une contrainte d’intégrité peut être – Associée à un domaine • Spécifiée – Au travers de la clause CREATE DOMAIN – Lors de la définition de l’attribut dans la clause CREATE TABLE – Associée à une relation • spécifiée – Après la définition des attributs de la clause CREATE TABLE – Après la définition de la table avec ALTER TABLE – Dissociées • Spécifiée au travers de la clause CREATE ASSERTION – Tout ceci peut être spécifié par la langage SQL2… 290 SQL2 : contrainte de domaine (1) • Définition possible dans la clause CREATE DOMAIN CREATE DOMAIN <nom> <type> [DEFAULT VALUE ‘valeur’] [CONSTRAINT <nom_contrainte> CHECK (VALUE expr) ] expr ::=[NOT|IS NOT] EXISTS|IN|BETWEEN|LIKE|NULL requete_sql | <val>* Exemple CREATE DOMAIN couleur_vins CHAR(5) DEFAULT VALUE 'rouge' CONSTRAINT couleurs_possibles CHECK ( VALUE IN ('rouge', 'blanc', 'rosé') ) • Ensuite, le domaine peut servir à typer des attributs 291 SQL2 : contrainte de domaine (2) • Spécification possible dans la clause CREATE TABLE CREATE TABLE <nom_table> ( <nom_colonne type|nom_domaine>* [<contrainte_table>*] ) [<contrainte_colonne>*] <contrainte_colonne>::= [CONSTRAINT nom_contrainte] < NOT NULL|UNIQUE|PRIMARY KEY| REFERENCES nom_table(liste_colonnes)| CHECK(expr)> [NOT] DEFERRABLE Exemple CREATE TABLE Vin ( id INTEGER PRIMARY KEY, couleur COULEURS_VINS, cru VARCHAR(20), millesime DATE, degre INTEGER CHECK(degre BETWEEN 8 AND 15) NOT DEFERRABLE, quantite INTEGER ); 292 SQL2 : contraintes de relations (1) • Spécification possible dans la clause CREATE TABLE CREATE TABLE <nom_table> ( <nom_colonne type|nom_domaine>* [<contrainte_table>*] ) [<contrainte_colonne>*] < contrainte_table > ::= [CONSTRAINT nom_contrainte ] < UNIQUE (liste_colonnes)|PRIMARY KEY (liste_colonnes)| CHECK (expr)| FOREIGN KEY (liste_colonnes) REFERENCES nom_table (liste_colonnes)> [NOT] DEFERRABLE Exemple CREATE TABLE Vin (id INTEGER PRIMARY KEY, couleur COULEURS_VINS, cru VARCHAR(20), millesime DATE, degre INTEGER CHECK(degre BETWEEN 8 AND 15) NOT DEFERRABLE, quantite INTEGER, CONSTRAINT dependance_fonctionnelle CHECK (NOT EXISTS (SELECT * FROM vins GROUP BY cru,millesime HAVING COUNT(degre) > 1) NOT DEFERRABLE); 293 SQL2 : contraintes de relations (2) • Sous-cas important : les contraintes référentielles – Caractérisent toutes les associations – Peuvent être croisées mode DEFERRABLE • En cas de violation de la contrainte – Mise à jour peut être rejetée – Action de correction peut être déclenchée • ON DELETE : action à effectuer si suppression d'un tuple référencé • ON UPDATE : action à effectuer si MAJ de la clé d'un tuple référencé FOREIGN KEY (<liste_colonnes>) REFERENCES <nom_table> (<liste_colonnes>) [ON DELETE {CASCADE | SET DEFAULT | SET NULL}] [ON UPDATE {CASCADE | SET DEFAULT | SET NULL}] [NOT] DEFERRABLE Exemple CREATE TABLE Abus ( id INTEGER NOT NULL, id_vin INTEGER NOT NULL, date DATE, CONSTRAINT référence_buveurs FOREIGN KEY id REFERENCES ON DELETE CASCADE DEFERRABLE ); Buveurs (id) 294 SQL2 : contraintes dissociées • Spécification sous forme d’assertion (règle) CREATE ASSERTION <nom_contrainte> CHECK (condition) • Ex: la quantité bue reste < 100 pour chaque buveur Exemple CREATE ASSERTION quantite_produite CHECK ( NOT EXIST (SELECT Id FROM Buveur GROUP BY Id HAVING SUM(qté)>100) • NB : les contraintes dissociées peuvent être multi-tables Exemple CREATE ASSERTION quantite_produite CHECK ( (SELECT SUM(quantite) FROM Vins) > ( SELECT SUM(quantite) FROM Abus) ) 295 Dans les SGBD commerciaux… • Contraintes de la norme SQL2 pas totalement implémentée • Oracle – Domaine et d’assertion non supportés – Clause check ne peut contenir de requête • Sybase, SQL Server – Clause check ne peut contenir de requête – Une contrainte par attribut au maximum • IBM DB2 – Contraintes de la clause create table sur les valeurs d’un même tuple – Une contrainte par attribut au maximum • MsAccess – Aucune clause check – Pas de domaine • Comment palier à ces limitations ? 296 Palier aux limitations sur les contraintes • Domaines remplacés par des tables et des clés étrangères CREATE TABLE Ville ( nom VARCHAR(128), CONSTRAINT nom PRIMARY KEY ); CREATE TABLE Personne ( …, ville VARCHAR(128) REFERENCES Ville, … ); • SQL2 introduit le concept de déclencheur (cf. partie suivante) – Si un Evènement se produit sur la base – Et qu’une Condition est vérifiée – Le moteur déclenche une Action (traitement) Les utiliser pour spécifier les contraintes complexes TRIGGER degré_croissant BEFORE UPDATE OF degré ON vin REFERENCING OLD ROW AS vin_avant NEW ROW AS vin_après FOR EACH ROW WHEN (vin_après.date > vin_avant.date AND vin_après.degré < vin_avant.degré ) BEGIN lancerException END 297 Contraintes structurelles Oracle (1) • Contrainte d‘entité (clé de relation, non nullité, unicité) – Spécifiées lors de la création de la table • En tant que contrainte de colonne CREATE TABLE Ville ( id NUMBER CONSTRAINT pk PRIMARY KEY, … ); • En tant que contrainte de table CREATE TABLE Ville ( id NUMBER, … , CONSTRAINT pk PRIMARY KEY (id) ); – Ajoutée après la création de la table (contrainte de table) ALTER TABLE Ville ADD ( CONSTRAINT pk PRIMARY KEY(id) ); – Retirées (si nommée): ALTER TABLE Ville DROP CONSTRAINT pk; 298 Contraintes structurelles Oracle (2) • Contraintes référentielles (clé étrangère) – Spécifiées lors de la création de la table • En tant que contrainte de colonne CREATE TABLE Ville ( …, dept_id NUMBER CONSTRAINT fk REFERENCES Dept(Id), … ); • En tant que contrainte de table CREATE TABLE Ville ( …, dept_id NUMBER, …, CONSTRAINT fk FOREIGN KEY (dept_id) REFERENCES Dept(Id) ); – Ajoutée après la création de la table (contrainte de table) ALTER TABLE Ville ADD ( CONSTRAINT fk FOREIGN KEY (dept_id) REFERENCES Dept(Id) ); – NB: on delete cascade OK, on update cascade non supporté – Retirées de la même manière que les contraintes d’entité 299 Contraintes structurelles Oracle (3) • Contrainte de domaine – Typage de l’attribut lors de la création de la table • Domaine de variation – Exprimée avec la clause CHECK (sans requête) • En tant que contrainte de colonne CREATE TABLE Dept ( …, code CHAR(2) CONSTRAINT code_dept CHECK (code BETWEEN 1 AND 99) … ); • En tant que contrainte de table CREATE TABLE Dept ( …, code CHAR(2) …, CONSTRAINT code_dept CHECK (code BETWEEN 1 AND 99) ); 300 Contraintes comportementales Oracle • Contraintes temporelles • Contraintes équationnelles • Dépendances généralisées Exprimées avec des triggers ? 301 Vérification des contraintes • Nécessite un travail (non trivial) de la part du SGBD – Souvent pour cela que tout SQL2 n’est pas supporté dans les produits • Lors de la création de la contrainte – Le SGBD vérifie la cohérence des contraintes – La non-redondance des contraintes – Par des mécanismes de preuve • Lors de mise à jour dans la base – On résout les transgressions de contraintes suivant 2 méthodes • Par prévention d’incohérence – Des règles empêchent les mises à jours transgressant les contraintes • Par détection d’incohérence et abandon de mise à jour – Associe un traitement de vérification sur insert, delete, update – Il s’agit autant que possible d’un test différentiel (ne pas tout re-tester!) 302 Prévention / détection de transgression • 2 méthodes pour assurer la cohérence vis à vis des contraintes • Par prévention d’incohérences – Une mise à jour m n'est exécutée que si l'état résultant de la base Dm est garanti être cohérent – Notion de pré-test • A et A' sont des assertions • A' est un pré-test pour A et m SSI (D / A Dm / A) D / A' – problèmes • Nécessité de définir toutes les mises à jour permises • Non généralité Performant, détaillé dans la suite • Par détection d’incohérence – Toute mise à jour m est exécutée – L’état D de la base devient Dm – Si Dm est détecté incohérent, on restitue D – Notion de post-test • A et A' sont des assertions • A' est un post-test pour A et m ssi (D / A Dm / A) Dm / A‘ – Difficultés • Trouver un A' plus simple à vérifier que A • Défaire la transaction en cas d'incohérence 303 Exemple de vérification préventive – Pré-test : A‘ = A Q – L’algorithme ajoute conjonctivement l’assertion A à la sous formule conditionnant l'exécution de la mise à jour – Exemple : A = salaire > SMIC UPDATE employé SET salaire = salaire * 0.9 WHERE nom = 'Raleur'; devient UPDATE employé SET salaire = salaire * 0.9 WHERE nom = 'Raleur' AND salaire * 0.9 > SMIC; 304 Notion de pré-tests différentiels • Simplification des vérifications de contraintes lors de modification de la base • Ex : la relation Abus référence la relation Vins Mises à jour de R MEM. CACHE Tuples à insérer dans R Tuples à supprimer de R R+ Pré-test commit RDISQUE • Pré-tests simplifiés – – – – Insertion (Abus) : Abus+.id_vin = Vins.id Suppression (Abus) : rien à faire Insertion (Vins) : rien à faire Suppr. (Vins) : Count (Abus.id_vin) where (Abus.id_vin = Vins-.idm) = 0 Très efficace mais complexe à implanter… 305 Exemples de pré-tests différentiels • Tests différentiels de contraintes classiques Opération insertion suppression modification K clé primaire de R Clés de R+ uniques et ne sont pas dans R Rien à faire Clés de R+ uniques et ne sont pas dans R-R- A clé étrangère de R référence RefK de S Tuples de R+ référencent un tuple de S S: clés K de S- ne figurent pas dans A de R Tuples de R+ référencent un tuple de S A domaine de R Domaine A sur R+ Rien à faire Domaine A sur R+ Non nullité Non-nullité sur R+ Rien à faire Non-nullité sur R+ Dépendance fonctionnelle AB t tuple de R+, u tuple de R tel que t.A = u.A t.B = u.B Rien à faire Pas de forme simplifiée Contrainte temporelle sur attribut Rien à faire Rien à faire Vérifier les tuples de R+ par rapport à ceux de R- Type de contrainte 306 Conclusion contraintes d’intégrité • Assurent la cohérence de la BD • Définies dans la Norme SQL2 – Du papier… • Sont vérifiées par le moteur du SGBD (sur le serveur) – Lors de la validation du traitement ou au cours du traitement – La centralisation permet • De vérifier la validité globale • Un gain en bande passante • Implantation dans les systèmes réels – Contraintes simples • Définition avec SQL associée à celle des objets de la base – Contraintes complexes ? • Par déclenchement (éventuellement conditionnels) de traitements écrits dans un langage procédural (triggers SQL2) 307 Les triggers Leur utilisation dans Oracle 308 SQL2 : déclencheurs (Triggers) • Définition – Action ou ensemble d'actions déclenchée(s) automatiquement lorsqu'une condition se trouve satisfaite après l'apparition d'un événement • Un déclencheur est une règle ECA – Événement = mise à jour d'une relation (INSERT, DELETE, UPDATE) – Condition = optionnelle, équivaut à une clause WHERE – Action = exécution de code spécifique (ordre SQL ou PL/SQL) • Requête SQL de mise à jour, exécution d'une procédure stockée, abandon d'une transaction, ... • De multiples usages sont possibles – Étendre les mécanismes de contrôle d’intégrité • Validation des données entrées, maintien de règles d’intégrité complexes – Contrôle dynamique et évolutif des manipulations de la BD • Maintien de statistiques, audit de la base (sécurité) – Duplication • Mise à jour de copies multiples, Dérivation des données additionnelles – Mise à jour au travers de vues 309 SQL2 : définition des triggers CREATE TRIGGER <nom-trigger> <événement> [<condition>] <action>* <événement> ::= BEFORE | AFTER | INSTEAD OF {INSERT | DELETE | UPDATE [OF <liste_colonnes>]} ON <nom_de_table> <condition> ::= [REFERENCING OLD AS <nom_tuple> NEW AS <nom_tuple> FOR EACH ROW] WHEN <condition_SQL> <action> ::= {requête_SQL [FOR EACH ROW] | exec_procédure | COMMIT | ROLLBACK} Exemples CREATE TRIGGER degré_croissant BEFORE UPDATE OF degre ON Vins REFERENCING OLD AS old_vin NEW AS new_vin FOR EACH ROW WHEN (new_vin.degre < old_vin.degre) ROLLBACK CREATE TRIGGER référence_vins BEFORE DELETE ON Vins FOR EACH ROW DELETE FROM Abus WHERE Abus.id_vin = Vins.id 310 SQL2 : Gestion des Triggers CREATE [OR REPLACE ] TRIGGER <nom-trigger> <événement> [<condition>] <action>* DROP TRIGGER <nom-trigger> ALTER TRIGGER <nom-trigger> ENABLE ALTER TRIGGER <nom-trigger> DISABLE • La création d’un trigger déclenche son activation • On peut remplacer la version précédente d’un trigger • On peut manuellement activer/désactiver un trigger 311 Mises en garde : interactions • L’action d’un trigger peut déclencher d’autres triggers • L’action d’un trigger peut causer la vérification des contraintes d’intégrité • Les actions de réaction aux contraintes d’intégrité peuvent déclencher des triggers – on delete cascade trigger delete – on update cascade / set default / set null on delete set default / set null trigger update 312 Triggers des SGBD commerciaux • Différences avec le standard • Oracle – 1 seul trigger déclenché par un même évènement – Condition : ne peut contenir de requête SQL – Action : • 1 bloc PL/SQL anonyme sans COMMIT/ROLLBACK • Pas de mise à jour de la table ayant levé le trigger • Pas de lecture de la table ayant levé le trigger ligne • Informix9 – Condition : ne peut contenir de requête SQL – Action • 1 seul ordre PL/SQL • ou 1 seul appel de procédure/fonction 313 Définition globale des triggers Oracle • Lors d’une requête de MAJ sur une table… – Insert, delete, update • […si la clause when est vérifiée…] – Condition sur le tuple mis à jour CREATE TRIGGER <nom-trigger> <BEFORE | AFTER> <INSERT | DELETE | UPDATE> ON <nom_de_table> [WHEN (<condition_sur_la_ligne>)] [FOR EACH <ROW|STATEMENT>] • … exécution du bloc PL/SQL – Une fois trigger ordre • Avant ou après l’ordre -- BLOC PL/SQL DECLARE … BEGIN … END; – Une fois par tuple mis à jour trigger ligne • Avant ou après chaque mise à jour – Pour la syntaxe détaillée du PL/SQL, voir cours PL/SQL… – NB : des clauses PL/SQL sont propres aux triggers • INSERTING, DELETING, UPDATING utilisables dans les IF 314 Les types de triggers Oracle Triggers ordre Triggers ligne FOR EACH STATEMENT FOR EACH ROW Before After Before Insert Update Insert Update Delete After Insert Update Insert Update Delete Instead of Delete Insert Delete Update Delete 315 Triggers ordre Oracle (1) CREATE TRIGGER <nom-trigger> <qd_executer> <ordre_sql> ON <nom_table> [FOR EACH STATEMENT] <bloc_plsql> <qd_executer> ::= BEFORE | AFTER <ordre_sql> ::= < INSERT | DELETE | UPDATE [OF<liste_colonnes>] > [OR <ordre_sql>] Exemple CREATE TRIGGER vérif_qté AFTER INSERT OR UPDATE OF qté ON Abus DECLARE qté_produite NUMBER; qté_bue NUMBER; BEGIN SELECT SUM(qté) into qté_produite FROM Vins; SELECT SUM(qté) into qté_bue FROM Abus; IF qté_produite < qté_bue THEN raise_application_error(-20001, ‘mise à jour incohérente’); END IF; END; 316 Triggers ordre Oracle (2) • Exécution du trigger avant ou après la requête (ordre) – Avant : mises à jour de l’ordre toutes invisibles – Après : mises à jour de l’ordre toutes visibles – Dans le trigger : mise à jour impossible de la table modifiée • Ex. du trigger vérif_qté SCN = 7 SCN = 8 Transaction SCN = 9 SCN = 9 SCN = 10 Insert into Abus as select… Déclenche (après insertions) Abus SCN =11 Trigger vérif_qté Chronologie SCN = 11 Requête SCN = 8 317 Exercice 1 • Créer un trigger ordre qui s’assure que les modifications sur la table Emp ont bien lieu pendant les jours ouvrables (du lundi au vendredi) – Vous pourrez utiliser la fonction TO_CHAR(SYSDATE, ‘DY’) qui retourne le jour courant ‘MON’, ‘TUE’, ‘WED’, ‘THU’, ‘FRI’, ‘SAT’, ‘SUN’ CREATE TRIGGER emp_changements_permis BEFORE DELETE OR INSERT OR UPDATE ON Emp BEGIN IF TO_CHAR(SYSDATE,‘DY’)=‘SAT’ OR TO_CHAR(SYSDATE,‘DY’)=‘SUN’ THEN raise_application_error(-20001, ‘pas de changement le week end’); END IF; END; 318 Triggers ligne Oracle (1) CREATE TRIGGER <nom-trigger> [INSTEAD OF] <qd_executer> <ordre_sql> ON <nom_table> [REFERENCING OLD AS <nom_old> NEW AS <nom_new>] FOR EACH ROW WHEN(<condition>) <bloc_plsql> <qd_executer> ::= BEFORE | AFTER <ordre_sql> ::= < INSERT | DELETE | UPDATE [OF<liste_colonnes>] > [OR <ordre_sql>] <condition> ::= <attribut> <|>|!=|=|[NOT]<IN|BETWEEN|LIKE> <valeur> • Variables de transition old et new – nommées implicitement old et new, ou explicitement dans le referencing – utilisables dans le bloc PL/SQL par :new.nom_col et :old.nom_col – NB: pas de new lors d’un delete, pas de old lors d’un insert • Clause when – Attribut = valeur du tuple avant ou après (old.nom_col ou new.nom_col) – Valeur = attribut ou ‘xxx’, mais ne peut être résultat d’une requête 319 Triggers ligne Oracle (2) • Exécution avant ou après chaque mise à jour de tuple • Difficile de gérer les requêtes sur la table en mutation – Les exécution du trigger devraient pouvoir lire la table – Mais lecture de la table mise à jour erreur ‘mutating table’ • Raison technique : mise à jour des index en fin de requête, update et delete posent des verrous sur les tuples à modifier ou supprimer, … SCN =9 SCN = 7 Trigger degrès_croissant SCN = 8 Transaction SCN = 9 SCN = 9 Requête SCN = 8 SCN = 10 SCN =9 Trigger degrès_croissant Déclenche Update from Vin where… Déclenche Vin SCN = 11 Requête Chronologie SCN = 11 320 Triggers ligne Oracle (3) • Problème de table en cours de mutation – Sélection de la table en cours de mise à jour impossible (sauf before insert avec mise à jour unitaire) Triggers ordre Before After Triggers ligne Before Insert Update Insert Update Delete Instead of Insert Update Insert Delete After Update Delete Insert Delete Update Delete 321 Exercice 2 • Créer un trigger ligne qui s’assure que lorsqu’un employé est embauché pour un type d’emploi, son salaire soit dans les bornes admises pour ce type d’emploi. Les tables ont la structure suivante : – Employés (Id, Nom, Prénom, Salaire, Emploi, Dept) – TypeEmploi (Emploi, Sal_inf, Sal_sup) CREATE TRIGGER verif_salaire BEFORE INSERT OR UPDATE OF salaire, emploi ON Emp FOR EACH ROW DECLARE min_sal NUMBER; max_sal NUMBER; BEGIN SELECT Sal_inf, Sal_sup INTO min_sal, max_sal FROM TypeEmploi WHERE Emploi = :NEW.Emploi; IF min_sal > :NEW.Salaire OR max_sal < :NEW.Salaire THEN raise_application_error(-20001, ‘Salaire hors limite pour ’|| :NEW.Nom); END IF; END; 322 Trigger instead of Oracle • Implicitement activé for each row (peut être défini for each statement) • La mise à jour sur la vue (ne peut porter sur une table dans Oracle) est remplacée par l’action PL/SQL • old et new peuvent être utilisées comme si la modification avait lieu • Utilisation principale : report dans la base des mises à jour sur une vue 323 Commandes Oracle relatives aux triggers • DROP TRIGGER… : efface le trigger • Slash (/) : permet de compiler le trigger dans SQLPlus • SHOW ERRORS – affiche les erreurs dans le cas où le trigger aurait été crée avec des erreurs de compilation dans SQLPlus • CREATE OR REPLACE TRIGGER… : permet de remplacer le trigger s’il existe déjà 324 Résumé des limites des triggers Oracle • Un trigger – Ne peut contenir de COMMIT ou de ROLLBACK – Ne peut mettre à jour la table qui le déclenche • Un trigger ordre – ne peut utiliser OLD et NEW • Un trigger ligne – Ne peut selectionner (lire) la table qui le déclenche, sauf dans le cas d’un BEFORE INSERT déclenché par un ordre SQL unitaire du type INSERT INTO <table> VALUES(…); • Un trigger INSTEAD OF – Ne peut etre déclenché lors d’un ordre sur autre chose qu’une vue (ex: une table) 325 Exercice 3 • Dire ce qu’il se passe quand on exécute les triggers suivants CREATE TRIGGER audit_temp audit_temp Insérer dans une table Audit… AFTER DELETE AFTER DELETE ON ON temp temp BEGIN BEGIN INSERT INSERT INTO INTO temp temp VALUES VALUES (‘suppression (‘suppression de de ligne:’||SYSDATE); ligne:’||SYSDATE); END; END; CREATE TRIGGER auto_count auto_count AFTER AFTER INSERT INSERT OR OR DELETE DELETE ON ON employes employes DECLARE DECLARE ii NUMBER; NUMBER; BEGIN BEGIN SELECT SELECT COUNT(*) COUNT(*) INTO INTO ii FROM FROM employes employes ;; INSERT INSERT INTO INTO stat stat VALUES VALUES (‘nombre (‘nombre d’employes = ’|| ’|| i); i); END; END; CREATE CREATE TRIGGER TRIGGER dates_commandes_croissante AFTER AFTER INSERT INSERT ON ON commande commande Comment assurer une FOR EACH ROW FOR EACH ROW DECLARE telle contrainte ? DECLARE max_date max_date NUMBER; NUMBER; BEGIN BEGIN SELECT SELECT MAX(date_com) MAX(date_com) INTO INTO max_date max_date FROM FROM commande; commande; IF :NEW.date_com < max_date THEN IF :NEW.date_com < max_date THEN RAISE_APPLICATION_ERROR(20001, RAISE_APPLICATION_ERROR(20001, ‘date ‘date invalide’); invalide’); END END IF; IF; END; END; 326 Les séquences Oracle • Syntaxe des séquences Oracle CREATE SEQUENCE <nom_sequence> [START WITH <integer>] [INCREMENT BY <integer>] [MAXVALUE <integer>] [CYCLE | NOCYCLE] [ORDER |NOORDER] • Utilisées – pour donner une valeur aux clés primaires – pour générer des timestamp • On utilise ORDER : nombres générés dans l’ordre des demandes • Attributs : – CURVAL : retourne la valeur courante – NEXTVAL : incrémente et retourne la nouvelle valeur • Exemple CREATE SEQUENCE clé_client INCREMENT BY 1; INSERT INTO client VALUES (clé_client.NEXTVAL, … 327 Oracle et les contraintes d’intégrité • Les contraintes d’intégrité dans Oracle sont exprimées – Lors de la création de la table sur un attribut ou la table entière, comme cela est prévu par la norme SQL2 – Grâce à des triggers définis sur les tables • Cependant, des limitations par rapport à la norme SQL2 – Create domain et create assertion non supportés – Le contenu des clauses check d’expression de contrainte est réduit • A une expression sur le tuple concerné par la mise à jour de la table • Les requêtes sur les objets de la base y sont proscrites – Les triggers permettent d’exprimer les autres contraintes d’intégrité mais • Pas de requête dans la clause when • Pas d’ordre commit ou rollback dans le traitement déclenché • Pas de mise à jour de la table qui déclenche le trigger au sein du traitement – Mise à jour sur une table temporaire qui déclenche un deuxième trigger • Pas de consultation de la table en cours de modification pour un trigger ligne 328 Introduction à la sécurité des bases de données Conclusion 329 Plan de la journée • Cours – – – – Les menaces et le besoin accru de protection Exemples d’attaques Qui attaque, types d’attaques Conclusion sur les différentes mesures à implanter • Bilan des compétences acquises • Révisions et examen 330 Sécurité des SI (ITSEC) Information Technology Security Evaluation Criteria • Confidentialité – Seules les personnes autorisées ont accès aux ressources • Intégrité – Les ressources du SI ne sont pas corrompues • Disponibilité – L’accès aux ressources du SI est garanti de facon permanente • BD: les ressources sont les données de la BD + traitement activables sur les données • Et dans certains contexte – Auditabilité, imputabilité 331 Besoins de protection (1) • Omniprésence des bases de données – – – – Grands systèmes d’information (publics ou privés) BD PME BD personnelles (agenda, carnet d’adresses, bookmarks …) BD "ambiantes" (capteurs, aware home …) • L’organisation des données est un facteur de risque – L’analyse d’une collection d’informations insignifiantes peut générer des résultats sensibles • L’inter-connexion croissante en est un autre – Connexions permanentes, accès ubiquitaires, objets communicants 332 Besoins de protection (2) • Sous traitance de l’hébergement de données (DSP) – Hébergement de site Web, délégation de la gestion du système d’information d’une PME, dossiers personnels … – caspio.com, quickbase.com, primadoctor.com … – Nombreuses violations de chartes d’intimité [voir AKS02] • Vulnérabilité des serveurs d’entreprise – Source CSI/FBI : coût des attaques BD $100 milliards/an, 50% des attaques sont internes – Même les plus sûrs sont attaqués (FBI, NASA, Pentagone) – Sources DatalossDB, ZATAZ (musée des sites français piratés) • Plus de 3 millions de données sensibles volées au Canada (données clients avec infos bancaires, cartes de crédit, ...) • Diffusion d'une BD contenant les informations (identités, adresses, email, ...) de 13.500 adhérents au parti d'extrême droite anglais – Source CluSSIF (Club de la Sécurité des Systèmes d'Information Français) • Modification du carnet de commande, par un concurrent, d’un équipementier automobile => ruptures de stock gravissimes • Pénétration du système de facturation de British Telecom => divulgation de listes de N° de tél (services secrets, famille royale) 333 Besoins de protection (3) • Indexation des bases de données – "Google entre vie privée et secret défense" (source : confidentiel.net) • Négligence – Mise en ligne des recherches AOL (US), perte de support CD (UK) – Craquage d'un système bancaire US (mots de passe trop courts par rapport au nb de comptes gérés) – Consultation de la facture détaillée d’autrui (télécom brésil) • Croisement de bases de données – CAPPS-II (Computer Assisted Passenger Pre-Screening System) croise des BD pour lutter contre le terrorisme Ron Rivest : « La révolution digitale inverse les défauts : ce qui était autrefois difficile à copier devient facile à dupliquer, ce qui était oublié devient mémorisé à jamais et ce qui était privé devient public » 334 Exemples d’attaques connues de SGBD • Attaque à l’anonymat • Attaques de BD statistiques • Injection de code SQL (projet) • Attaques de bases Oracles – Oracle Unbreakable – Origines des failles connues • Etc… 335 Attaque à l’anonymat 336 Quelques exemples marquants: croisement de données publiques Etude de L. Sweeney – 2002 : Sur la base du recensement de 1990 aux USA, « 87% of the population in the US had characteristics that likely made them unique based only on {5-digit Zip, gender, date of birth} » [1]. [1] L. Sweeney. k-anonymity: a model for protecting privacy. Int. J. Uncertain. Fuzziness Knowl.-Based Syst., 10(5), 2002. 337 Quelques exemples marquants : espionnage entre bons citoyens Que voulez-vous savoir sur vos amis, voisins, nourrice, employés …? 338 Quelques exemples marquants : espionnage entre bons citoyens « The data within the report is compiled from thousands of different sources that include government, property, and other public record repositories. » 339 Attaque contre des BD statistiques (1) • Base de données statistique – Permet d'évaluer des requêtes d'agrégation • totaux, moyennes, etc. • Ex. La requête « quelle est la moyenne du taux de leucocytes des patients ayant plus de 30 ans ? » est permise – Mais pas les requêtes qui dérivent des infos particulières • Ex. La requête « quel est le taux de leucocytes de Dupont ? » est interdite • Ex. de base de données statistique – Relation Analyse(Patient,H/F,Age,Mutuelle,Leucocyte) Patient H/F Age Mutuelle Leucocyte Durand F 25 LMDE 3000 Dulac F 35 MMA 7000 Duval H 45 IPECA 5500 Dubois H 55 MGEN 3500 ... ... 340 Attaque contre des BD statistiques (2) • Exemple d’attaque simple : – U veut découvrir le taux de Leucocyte de Dubois – U sait que Dubois est un adhérent masculin de la MGEN • Requête 1 SELECT COUNT ( Patient ) FROM Analyse WHERE H/F =’H' AND Mutuelle =’MGEN' ; Résultat : 1 • Requête 2 SELECT SUM ( Leucocyte ) FROM Analyse WHERE H/F =’H' AND Mutuelle =’MGEN' ; Résultat : 3500 Le système doit refuser de répondre à une requête pour laquelle la cardinalité du résultat est inférieure à une certaine borne b 341 Attaque contre des BD statistiques (3) • Requête 3 • SELECT COUNT ( Patient ) FROM Analyse Résultat : 10 • Requête 4 SELECT COUNT ( Patient ) FROM Analyse WHERE NOT ( H/F =’H' AND Mutuelle =’MGEN’ ) ; Résultat: 9 ; 10 - 9 = 1 • Conséquence : Requête 5 SELECT SUM ( Leucocyte ) FROM Analyse Résultat : 54300 • Requête 6 SELECT SUM ( Leucocyte ) FROM Analyse WHERE NOT ( H/F =’H' AND Mutuelle =’MGEN’) ; Résultat : 50800 ; 54300 – 50800 = 3500 – Le système doit aussi refuser de répondre à une requête pour laquelle la cardinalité du résultat est inférieure à N - b – N est la cardinalité de la relation initiale 342 Attaque contre des BD statistiques (4) • Problème : – On peut montrer que limiter les requêtes à celles pour lesquelles le résultat a une cardinalité c telle que b c N - b n'est pas suffisant pour éviter la compromission – Exemple : si b = 2, les requêtes auront une réponse si c est telle que 2 c 8 • Requête 7 SELECT COUNT ( Patient ) FROM Analyse WHERE H/F =’H' ; Résultat : 6 • • Requête 8 SELECT COUNT ( Patient ) FROM Analyse WHERE H/F =’H' AND NOT (Mutuelle =’MGEN’) ; Résultat : 5 Conséquence – U peut déduire qu'il existe exactement un patient masculin qui a la MGEN comme mutuelle, – Il s’agit de Dubois, puisque U sait que cette description correspond à Dubois 343 Attaque contre des BD statistiques (5) • Conséquence (suite) – Le taux de Leucocyte de Dubois est facilement découvert de la façon suivante : • Requête 9 SELECT SUM ( Leucocyte ) FROM Analyse WHERE H/F =’H' ; Résultat : 30300 • Requête 10 SELECT SUM ( Leucocyte ) FROM Analyse WHERE H/F =’H' AND NOT (Mutuelle =’MGEN’) ; Résultat : 26800 ; 30300 - 26800 = 3500 344 Les attaques de bases Oracle • peu nombreuses jusqu’à récemment – – – – plus de serveurs Web que de bases Oracle connaissance d’Oracle limitée difficile d’obtenir une version d’Oracle bases souvent protégées par un firewall • de nos jours, intérêt croissant des pirates – De plus en plus de topics Oracle sur les forums de hackers – Téléchargement et installation d’Oracle faciles • Engouement du défi « Oracle Unbreakable » 345 Protection : Solution Oracle ? • Oracle's 'Unbreakable' Boast Attracts Hackers – Hack attempts on the company's website have increased to 30,000 per week. • Some days after …. – 'When they say their software is unbreakable, they're lying.' -- Bruce Schneier – U.K. security researcher David Litchfield revealed that a common programming error -- a buffer overflow -- was present in Oracle's application server 346 Attaques : origines des failles... • Bug constructeur (corrigés par des patchs) – Ex. Oracle Listener (OL) • Ecoute les commandes des clients et les transmet au serveur Oracle • Contacte Oracle via le protocole TNS (Transparent NetWork Substrate) • Attaque du OL par buffer overflow – Si un paquet contient plus d’1 Ko de données, le OL plante – Dans certains cas, ça génère un « core dump » – .T.......6.,...............:................4.............(CONNECT_DATA=XXXXXXXX/0x5/0x34/0x12/0x54/ 0x5/0x34/0x12/0x54/0x5/0x34/0x12/0x54/0x5/0x34/0x12/0x54/0x5/0x34/0x12/0x54/0x5/0x34) • Bug configuration (pd de paramètres) – Ex. Attaque OL par script de commandes TNS • Certaines commandes ne demandent aucune autorisation – Ex. demande de version (tnscmd version -h adresse_du_serveur -p 1521); de statut (tnscmd status -h adresse_du_serveur -p 1521) • Le pirate obtient les infos suivantes: version d’Oracle, système d'exploitation, chemins vers les logs, options du Listener (Ex. option security), environnement complet (variables système) dans lequel Listener a été lancé – Pour se protéger : Activer l'option security dans OL, utilisation d’une authentification forte, etc. • • Bug architectural (pb de conception de l’appli) Etc. 347 DBMS : qui attaque ? • Pirate externe Pirate externe – capable de s’infiltrer sur le serveur BD et de lire ses fichiers – capable de casser une clé de chiffrement avec un texte connu • Pirate utilisateur – est reconnu par le SGBD et a accès à une partie des données – suivant le mode de chiffrement, il a accès à certaines clés • Pirate administrateur (DBA) – employé peu scrupuleux ou pirate s’étant octroyé ces droits – a accès à des données inaccessibles aux autres pirates (journal) – peut espionner le SGBD pendant l’exécution Pirate utilisateur Pirate administrateur Client C1 Client C2 BD BD P.M.E. Client C3 Serveur BD 348 Principales défenses 0- Tolérance aux pannes 349 Plan de la PARTIE II 9 - Législation 1 - Authentification 2 - Sécurisation des communications 0 - Tolérance aux pannes 3 - Autorisations 4 - Chiffrement de la BD 5 - Audit 8 – Anonymisation 350 PARTIE I : compétences acquises ? • Concevoir une base de données – Réaliser un modèle conceptuel avec le modèle E/A – Concevoir un modèle relationnelle de base de données • Créer une application base de données – – – – Ecrire des requêtes SQL d’interrogation/mise à jour Interfacer un programme Java/JDBC avec une base de données Ecrire et invoquer des fonctions et procédure stockées en PL/SQL Oracle Implanter des triggers sur une base de données en PL/SQL Oracle • Administrer une base de données en vue d’optimiser les performances – Optimiser une base de données multi utilisateurs (gestion de la concurrence) – Créer des index – Réécrire des requêtes SQL pour obtenir un plan d’exécution plus performant 351
