[PDF] Introduction aux bases de données - IN207

View - Download Introduction aux bases de données - IN207

Previous PDF

Next PDF

Introduction aux bases de données -IN207 Luc BouganimLuc.Bouganim@inria.frLien web : http://petrus.inria.fr/~bouganim/ENSTA/Nature et objectifs du module•Un cours d'introduction •Objectif•Acquisition de la culture de base à l'ingénierie du SGBD •Connaissance•Fonctionnalités logicielles et raison d'être d'un SGBD•Indépendance physique/logique, vues, langage de manipulations, cohérence (contraintes d'intégrité et triggers), standards•Optimisation de questions, concurrence d'accès et gestion des pannes, gestion de la confidentialité•Connaissances techniques (et très concrètes)•Conception de BD (modèle EA, modèle relationnel) : 2 séances•Utilisation du SGBD (SQL, programmation SQL, JDBC/ODBC) : 3 séances•Notions plus avancées (transactions, sécurité) : 2 séancesObjectifs en termes de compétences•Concevoir une base de données•Réaliser un modèle conceptuel avec le modèle E/A•Concevoir un modèle relationnel de base de données•Créer une application base de données•Créer un schéma relationnel en SQL•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•Comprendre les mécanismes transactionnels et les problèmes de performance liés•Comprendre les challenges liés à la protection des donnéesPlan des sessions•Approche BD (vs. fichier)•Conception 1 (modèle EA)•Modèle relationnel et algèbre•Conception 2 (relationnel)•Vue d'ensemble SGBD•SQL 1 (LDD)•SQL 2 (LMD) et méthodologie•Programmation SQL •PL/SQL, ODBC/JDBC•Propriétés transactionnelles•Concurrence d'accès•Introduction à la sécurité des SGBD→TD : Conception (modèle EA)... et passage au relationnel→TD : Exercices d'algèbre relationnelle→TP (XE) : Création d'une base (SQL)→TP (XE) : Insertion massive (SQL loader)→TP (XE) : Interrogation (SQL)→TP (XE) : PL/SQL, OCILIB→TP (XE) : Expérience sur la concurrence →Examen

Fonctionnement•Forme•Cours sur transparents•TD (sur papier) ou TP (sur machine : Oracle XE)•Evaluation•Examen le dernier jour (à la place des TP)•Durée : 2h•Documents autorisés : supports de cours, mémentos, fiches, ...•Non autorisés : livres, ordinateur, smartphone...•Participation et/ou CR de TP à rendre en fin de scéance•Juste un "copier/coller» des instructions sur machinePosez le maximum de questions en cours !!!!56J1Systèmes de fichiersCaractéristiquesChirurgiePsychiatrieComptabilitéConsultationsProblèmesPlusieurs applicationsCaractéristiquesPlusieurs applications èplusieurs formatsèplusieurs langagesProblèmesèDifficultés de gestionDupontSymptomes : yTurlututu : sqjSymptomes : yTurlututu : sddAnalyses : xxxDupondTurlututusqjskSymptom: yyyyAnalyses xxxxTurlututudhjsdAnalyses :xxDuipontTurlututu : sqSymptomyyyyAnalysesxxxxTurlututudhjsdDuhponSymptomes : yyAnalyses : xxxxSymptomes : yy

Redondance (données)CaractéristiquesPlusieurs applications èplusieurs formatsèplusieurs langagesRedondance de donnéesProblèmesèDifficultés de gestionèIncohérence des donnéesDupontSymptomes : yTurlututu : sqjSymptomes : yTurlututu : sddAnalyses : xxxDupondTurlututusqjskSymptom: yyyyAnalyses xxxxTurlututudhjsdAnalyses :xxDuipontTurlututu : sqSymptomyyyyAnalysesxxxxTurlututudhjsdDuhponSymptomes : yyAnalyses : xxxxSymptomes : yyInterrogations CaractéristiquesPlusieurs applications èplusieurs formatsèplusieurs langagesRedondance de donnéesPas de facilité d'interrogation èQuestion ÞdéveloppementProblèmesèDifficultés de gestionèIncohérence des donnéesèCoûts élevésèMaintenance difficileDupontSymptomes : yTurlututu : sqjSymptomes : yTurlututu : sddAnalyses : xxxDupondTurlututusqjskSymptom: yyyyAnalyses xxxxTurlututudhjsdAnalyses :xxDuipontTurlututu : sqSymptomyyyyAnalysesxxxxTurlututudhjsdDuhponSymptomes : yyAnalyses : xxxxSymptomes : yyChiruSoftConsultSoftPsychiaSoftComptaSoftPannes ???CaractéristiquesPlusieurs applications èplusieurs formatsèplusieurs langagesRedondance de donnéesPas de facilité d'interrogation èQuestion ÞdéveloppementRedondance de codeProblèmesèDifficultés de gestionèIncohérence des donnéesèCoûts élevésèMaintenance difficileèGestion de pannes ???DupontSymptomes : yTurlututu : sqjSymptomes : yTurlututu : sddAnalyses : xxxDupondTurlututusqjskSymptom: yyyyAnalyses xxxxTurlututudhjsdAnalyses :xxDuipontTurlututu : sqSymptomyyyyAnalysesxxxxTurlututudhjsdDuhponSymptomes : yyAnalyses : xxxxSymptomes : yyChiruSoftConsultSoftPsychiaSoftComptaSoftPartage de donnéesCaractéristiquesPlusieurs applications èplusieurs formatsèplusieurs langagesRedondance de donnéesPas de facilité d'interrogation èQuestion ÞdéveloppementRedondance de codeProblèmesèDifficultés de gestionèIncohérence des donnéesèCoûts élevésèMaintenance difficileèGestion de pannes ???èPartage des données ???DupontSymptomes : yTurlututu : sqjSymptomes : yTurlututu : sddAnalyses : xxxDupondTurlututusqjskSymptom: yyyyAnalyses xxxxTurlututudhjsdAnalyses :xxDuipontTurlututu : sqSymptomyyyyAnalysesxxxxTurlututudhjsdDuhponSymptomes : yyAnalyses : xxxxSymptomes : yyChiruSoftConsultSoftPsychiaSoftComptaSoft

ConfidentialitéCaractéristiquesPlusieurs applications èplusieurs formatsèplusieurs langagesRedondance de donnéesPas de facilité d'interrogation èQuestion ÞdéveloppementRedondance de codeProblèmesèDifficultés de gestionèIncohérence des donnéesèCoûts élevésèMaintenance difficileèGestion de pannes ???èPartage des données ???èConfidentialité ???DupontSymptomes : yTurlututu : sqjSymptomes : yTurlututu : sddAnalyses : xxxDupondTurlututusqjskSymptom: yyyyAnalyses xxxxTurlututudhjsdAnalyses :xxDuipontTurlututu : sqSymptomyyyyAnalysesxxxxTurlututudhjsdDuhponSymptomes : yyAnalyses : xxxxSymptomes : yyChiruSoftConsultSoftPsychiaSoftComptaSoftL'approche ''Bases de données'' (1)•Modélisation des données •Eliminer 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ées14L'approche ''Bases de données'' (2)BDVIII -Concurrence d'accèsVII -Gestion des pannesI-Indépendance PhysiqueIX -Gestion de la confidentialitéII-Indépendance LogiqueVI -Gestion de la cohérenceX -StandardsV -Optimisation des questionsIII -Langage de manipulationIV -Gestion des vues15RéelModèle conceptuel•Indépendant du modèle de données•Indépendant du SGBDModèle logique•Dépendant du modèle de données•Indépendant du SGBDCodasylRelationnelObjetXMLModèle Physique•Dépendant du modèle de données•Dépendant du SGBD•Organisation physique des données•Structures de stockage des données•Structures accélératrices (index)Modélisation du réelMédecineffectueVisite16

Méthode de conception ?•Plusieurs façons d'aborder la conception d'une BD•Intuition + création directe de la BD L•Suivre une méthode de conception (MCDàMLDàMPD) J•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écieuse17Exemple de mauvaise conception (1)•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 ?

N°NomPrénomVille PaysImmatriculationMarqueCouleur

1BarJoeParisFrance125PP75RenaultRouge

2DeanPascalVenceFrance453LL06PeugeotVert

3BenZoeLyonFrance526UU69RenaultRouge

4BarJoeParisFrance257LPM75RenaultJaune

18Exemple de mauvaise conception (2)•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 ?

N°NomPrénomVille PaysEnfant 1Enfant2Enfant3NbEnfants

1BarJoeParisFrancePaulZoe2

2DeanPascalVenceFranceLili1

3BenZoeLyonFranceSamTorTur3

4CatTomLensFrance0

19RéelDONNEESTRAITEMENTModèle conceptuelMCDQuelles données ? Quelle organisation ?MCTQuels traitements ?Modèle logiqueMLDModèle logique (e.g, relationnel)MLTStructuration en procédureModèle PhysiqueMPDCréation de la base de donnéeMPTDescription de l'architecture des traitements, algorithmesMéthodes de conception : Exemple MeriseObjectif du cours : E/A, Merise, UML ? èE/A light, Merise ultra-light20

GENERATION AUTOMATIQUE PARTIELLE POSSIBLE!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 MPD21Dé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, commandesProfsBouganimLuc.....ProfsCrennIsabelle....ProfsNomPrénomAdresseEntitésType d'entité22Déf°(2) : 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•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 produit23Déf°(3) : 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 •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•Question : quid de visite : entité ou association ???•Un docteur visite un patient èassociation (attention une seule visite !)•Un docteur effectue une visite concernant un patient24

Exemple : Profs et cours...ProfsCodeProfNomPrénomAdresseCoursCodeCoursNomCours...enseigneNbreHeuresProfs3LewisJerry....Profs1CrennIsabelle....Profs2BouganimLuc.....Cours1Maths.....Cours2Infos...enseigne55enseigne16enseigne2425Dé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•Au maximum : Max=n•èUn prof enseigne de 0 à n cours•Pour un cours donné, combien d'enseignements ?•Au minimum : 0•Au maximum : 1•Un cours est enseigné par 0 à 1 prof 0,n0,1ProfsCoursenseigneProfsCoursenseigne26Comment produire le MCD ? (1/3)•''Énoncer le réel'' à modéliser avec des phrases •Exemple pour la gestion de salles•Les profs enseignent des cours•Un prof peut enseigner plusieurs cours (voire aucun) à0,n•Un cours est enseigné par plusieurs profs (voire aucun) à0,n•Un cours se passe dans une salle•Un cours ne se passe que dans une seule salle à1,1•Une salle peut accueillir plusieurs cours à1,nSalles1,11,nCourseffectuéProfCours0,n0,nEnseigne27Comment produire le MCD ? (2/3)•On obtient : •Pour connaître la salle, pour un prof, on passe par le cours•èCe MCD est bon •Et si maintenant le cours peut être effectué dans plusieurs salles ?•Comment connaître la salle d'un prof ? •èCe MCD n'est pas bon !ProfCoursenseigne0,n0,nSalleeffectué1,n1,nProfCoursenseigne0,n0,nSalleeffectué1,11,n28

Comment produire le MCD ? (3/3)Différences fonctionnelles(A)(B)(C)Connaître la salle pour 1 cours donnéNonOuiOuiPré-allouer des salles aux CoursNonNonOuiProfCoursenseigne0,n0,nSalle1,nProfCoursenseigne0,n0,nSalleeffectué1,n1,n(B)1,nProfCoursenseigne0,n0,nSalleeffectué1,n1,n(C)(A)29Rè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...30Règles (2) : élémentaire, calculée, constante•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é dans son intégralité(on ne cherchera jamais a 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 ! (rejoint la règle 8)•Sinon, c'est redondant èsource d'incohérence•Une propriété constante n'est pas à stocker (rejoint la règle 8)•Sinon, c'est redondant èsource d'incohérence•On utilisera une table de constantes ...31Rè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.32

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...33Rè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 co-exister. En choisir un...•Si vous ne trouvez aucun identifiant, votre "entité» est sans doute une association ...34Règles (6) : Dépendance pleine des entités•Les propriétés d'une association doivent dépendre de la totalité des entités associées. Si certaines propriétés ne dépendent que d'un sous ensemble des entités, on devra •les rattacher à une des entités•créer une nouvelle association associant ce sous ensemble•Exemple:•1/ Un prof enseigne toujours dans la même salle èdans prof•2/ La salle dépend du prof et du cours èNouvelle association entre prof et coursProfGroupeCoursenseigneHeureSalle35Rè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équentGroupeEnseigneHeure?SalleProfCours36

37Rè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.CodeProfNomPrénomVillePaysProfCodeProfNomPrénomProfest dansNomVillePaysVille1,10,nPremière modélisation 'restreinte'•Gérer les notes des étudiants veut dire:•Hypothèses :•une base de données pour chaque promo et pour chaque semestre•Données : •Etudiants (nom, prénom, groupe)•Cours (nom, pôle, coefficient)•Notes (pour un étudiant et un cours: DS1, DS2, Participation, Examen)•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.38Modèle entité-associationCommentairesJLe schéma est simple, il répond au problèmeJOn a un minimum de donnéesKOn ne peut pas faire de suivi sur une promo KOn ne peut pas faire de suivi par profKPas de statistiques sur plusieurs annéesLProblème de gestion: on aura 4 fois les mêmes programmes EtudiantN°NomPrénomGroupeCoursNomCoursPôleCoefficienta obtenuDS1DS2ParticipationExamen0,n0,n39Modèle entité-associationCommentairesJLe schéma est simple, il répond au problèmeJOn a un minimum de donnéesEtudiantN°NomPrénomCoursNomCoursPôleCoefficient1,n1,na obtenuNoteTypeNoteTypeCoefficient1,n40

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...41Modèle entité-associationEtudiantN°NomPrénomCoursNomCoursPôleCoefficient1,n1,nPériodeCodeAnnéeSemestreProfsNomPrénomAdresseGroupeCodea obtenuNoteEst dansEnseigneNb heures1,n1,n1,n1,n1,n1,n1,n1,nTypeNoteTypeCoefficient1,n1,n4243J2Le modèle relationnel•En 1970, Codd, mathématicien, chercheur chez IBM, propose le Modèle Relationnel, basé sur le concept de Relation de la théorie des ensembles•A Relational Model of Data for Large Shared Data Banks,•CACM 13, No. 6, June 1970 •Il définit l' Algèbre Relationnelle sur laquelle est basé SQL (Structured Query Language), le langage standard de manipulation (LMD) et de description des données (LDD) de tous les SGBD Relationnels actuels44

Domaine•ENSEMBLE DE VALEURS•Exemples: •ENTIER•REEL•CHAINES DE CARACTERES•EUROS•SALAIRE = {4 000..100 000}•COULEUR= {BLEU, BLANC, ROUGE}45Produit cartésien•LE PRODUIT CARTESIEN D1x D2x ... x Dn EST L'ENSEMBLE DES TUPLES (N-UPLETS) TELS QUE ViÎDi•Exemple:•D1 = {Bleu,Blanc,Rouge}•D2 = {Vrai, Faux}Bleu VraiBleu FauxBlanc VraiBlanc FauxRouge VraiRouge Faux46Relation, attribut•SOUS-ENSEMBLE DU PRODUIT CARTESIEN D'UNE LISTE DE DOMAINES•Une relation est caractérisée par un nom•Exemple:•D1 = COULEUR•D2 = BOOLEEN•Plus simplement ...•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 domaineBleu FauxBlanc VraiRouge VraiCoulVins Coul Choix47Champs, attributs, colonnesChamps, attributs, colonnesChamps, attributs, colonnesExemple de relationId-DNomPrénom1DupontPierre2DurandPaul3MasseJean..................Relation ou tableTuples, lignes ou n-upletsTuples, lignes ou n-upletsTuples, lignes ou n-upletsTuples, lignes ou n-uplets48

Clé•GROUPE D'ATTRIBUTS MINIMUM QUI DETERMINE UN TUPLE UNIQUE DANS UNE RELATION •Exemples:•{CRU,MILLESIME} DANS VINS ==> NV•NSS DANS PERSONNE•CONTRAINTE D'ENTITE•Toute relation doit posséder au moins une clé documentée49Clé Etrangère•GROUPE D'ATTRIBUTS DEVANT APPARAITRE COMME CLE 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 entité-association obligatoires50Modélisation Relationnelle (2)DocteursId-DNomPrénom1DupontPierre2DurandPaul3MasseJean..................VisitesId-DId-PId-VDatePrix12115 juin25011212 août18022313 juillet3502341 mars250PatientsId-PNomPrénomVille1LebeauJacquesParis2TrogerZoeEvry3DoeJohnParis4PerryPauleValenton.........................PrescriptionsId-VLigneId-MPosologie11121 par jour12510 gouttes2182 par jour22121 par jour2332 gouttes........................MédicamentsId-MNomDescription1Aspegic 1000...................................2Fluisédal...................................3Mucomyst..................................................................................51Clés primairesDocteursId-DNomPrénom1DupontPierre2DurandPaul3MasseJean..................VisitesId-DId-PId-VDatePrix12115 juin25011212 août18022313 juillet3502341 mars250PatientsId-PNomPrénomVille1LebeauJacquesParis2TrogerZoeEvry3DoeJohnParis4PerryPauleValenton.........................PrescriptionsId-VLigneId-MPosologie11121 par jour12510 gouttes2182 par jour22121 par jour2332 gouttes........................MédicamentsId-MNomDescription1Aspegic 1000...................................2Fluisédal...................................3Mucomyst..................................................................................52

Clés étrangèresDocteursId-DNomPrénom1DupontPierre2DurandPaul3MasseJean..................VisitesId-DId-PId-VDatePrix12115 juin25011212 août18022313 juillet3502341 mars250PatientsId-PNomPrénomVille1LebeauJacquesParis2TrogerZoeEvry3DoeJohnParis4PerryPauleValenton.........................PrescriptionsId-VLigneId-MPosologie11121 par jour12510 gouttes2182 par jour22121 par jour2332 gouttes........................MédicamentsId-MNomDescription1Aspegic 1000...................................2Fluisédal...................................3Mucomyst..................................................................................53Schéma relationnel•Docteurs(Id-D, Nom, Prénom)•Visites(Id-D, Id-P, Id-V, Date, Prix)•Prescriptions (Id-V, Ligne, Id-M, Posologie)•Patients(Id-P, Nom, Prénom, Ville)•Médicaments(Id-M, Nom, Description)•En souligné : clés primaires•En italiques : clés étrangères•Il faudrait indiquer les types (domaines) de chaque attribut54Métabase•DICTIONNAIRE DE DONNEES, ORGANISE SOUS FORME RELATIONNELLE, CONTENANT LA DESCRIPTION DES RELATIONS, ATTRIBUTS, DOMAINES, CLES, etc.RELATIONS12141PERSOPERSOSYS454NUMBASENOMNBATTEMPLOYEDEPARTEMENTRELATIONSATTRIBUTSENTIERTEXTETEXTE121212NUMTYPENOMNUMRELNUMNOMPNOM12355Passage 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îne des schémas trop complexes•On ''tolère'' un peu de redondance ou quelques valeurs nulles....56

Propriétés, Entités•Régle1 : 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é '' par l'une ou l'autre des entité•devenir une table.57Exemple 1•Un prof enseigne un et un seul cours•Un cours est enseigné par un et un seul prof

Nom Prénom Adresse NomCours Description NbreHeures Bouganim Luc Paris Info Informatique 44 Crenn Isabelle Paris Math Mathématiques 78 Rousseau Martine Versailles Droit Droit 26 Solution 1Solution 2

NomPrénomAdresse

BouganimLucParis

CrennIsabelleParis

RousseauMartineVersailles

NomCoursDescription

InfoInformatique

MathMathématiques

DroitDroit

NomNomCoursNbreheures

BouganimInfo44

CrennMath78

RousseauDroit26

ProfsNomPrénomAdresseCoursNomCoursDescriptionEnseigneNbreHeures1,11,158Exemple 2•Un prof enseigne un et un seul cours•Un cours est enseigné par un prof ou n'est pas enseigné

BouganimLucParisInfoInformatique44

CrennIsabelleParisMathMathématiques78

DroitDroit

NomPrénomAdresseNomCoursNbreHeures

BouganimLucParisInfo44

CrennIsabelleParisMath78

NomCoursDescription

InfoInformatique

MathMathématiques

DroitDroit

Solution 1Solution 2ProfsNomPrénomAdresseCoursNomCoursDescriptionEnseigneNbreHeures1,10,159Exemple 3•Un prof enseigne un ou plusieurs cours•Un cours est enseigné par un et un seul prof

Nom Prénom Adresse NomCours Description NbreHeures Bouganim Luc Paris Info Informatique 20 Crenn Isabelle Paris Math Mathématique 48 Crenn Isabelle Paris Droit Droit 26 NomPrénomAdresse

BouganimLucParis

CrennIsabelleParis

Solution 1Solution 2

NomNomCoursDescriptionNbreHeures

BouganimInfoInformatique20

CrennMathMathématique48

CrennDroitDroit26

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'association61Algèbre relationnelle•Ensemble d'opérateurs pour manipuler les relations•Restriction, Projection, Produit cartésien, Jointure, Union, Intersection, Différence•Avantages•Concis•Sémantique simple•Représentation graphique•Utile pour raisonner•Utile pour l'optimisation de requêtes•C'est ce que génère un compilateur de requêtes SQL62Sélection :

s

critères(R)•Patients de la ville de ParisPatientsId-PNomPrénomVille1LebeauJacquesParis2TrogerZoeEvry3DoeJohnParis4PerryPauleValentonPatientsId-PNomPrénomVille1LebeauJacquesParis2TrogerZoeEvry3DoeJohnParis4PerryPauleValenton

s p

A1,A2,...Ap(R)Nom et prénom des patientsPatientsId-PNomPrénomVille1LebeauJacquesParis2TrogerZoeEvry3DoeJohnParis4PerryPauleValenton

p

PatientsId-PNomPrénomVille1LebeauJacquesParis2TrogerZoeEvry3DoeJohnParis4PerryPauleValenton•Elimination des attributs non désirés•Suppression des tuplesen double64

69Intersection (Ç)INTERSECTION ENSEMBLISTE POUR DES RELATIONS DE MEME SCHEMAExemple: VINS1

VINS2VINSCRUMILLREGIONCOULEURTokay1980AlsaceBlanc70Différence (-)DIFFERENCE ENSEMBLISTE POUR DES RELATIONS DE MEME SCHEMAExemple : VINS1 -VINS2VINSCRUMILLREGIONCOULEURChenas1983Beaujolais RougeTavel1986RhôneRoseDivision (÷)•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.71nomAgencenomClientParisAuteuilDupontNantesCentreQueffelecBellecourtGirardBrotteauxLetailleurLaDouaGirardParisDupleixDutourNantesCentrePassardNantesCentreMartinBellecourtLetailleurBrotteauxGirardLaDouaLetailleurLaDouaLagaffeNomAgenceBellecourtBrotteauxLaDouanomClientGirardLetailleurRésultat Final

Division -Exemple72

Représentation graphiqueUnion-DifférenceJointurecritères´pProjectionsÇ

ProduitcartésienDivisionIntersectionRestrictionÇ73Récapitulatifabcxya xa yb xb yc xc ySélectionProjectionProduitUnionIntersectionDifferencea1 b1a2 b1a3 b2b1 c1b2 c2b3 c3a1 b1 c1a2 b1 c1a3 b2 c2aaabcxyzxyxyaJointure NaturelleDivision74Autres opérateursPermet de renommer des attributs Notation : ra1:b1, ..., an:bn(Rel)Affectation(ß)Affecte une expression à une relation temporaireNotation : Temp ßExpression_relationnelle Exemple : Temp ßr -s Renomage (

r )SemiJoin, AntiJoin, OuterJoin•SemiJoin•AntiJoin•Left OuterJoin•Right OuterJoin

Vue d'ensemble des SGBDBDVIII -Concurrence d'accèsVII -Gestion des pannesI-Indépendance PhysiqueIX -Gestion de la confidentialitéII-Indépendance LogiqueVI -Gestion de la cohérenceX -StandardsV -Optimisation des questionsIII -Langage de manipulationIV -Gestion des vues77Modélisation Relationnelle (2)DocteursId-DNomPrénom1DupontPierre2DurandPaul3MasseJean..................VisitesId-DId-PId-VDatePrix12115 juin25011212 août18022313 juillet3502341 mars250PatientsId-PNomPrénomVille1LebeauJacquesParis2TrogerZoeEvry3DoeJohnParis4PerryPauleValenton.........................PrescriptionsId-VLigneId-MPosologie11121 par jour12510 gouttes2182 par jour22121 par jour2332 gouttes........................MédicamentsId-MNomDescription1Aspegic 1000...................................2Fluisédal...................................3Mucomyst..................................................................................78I -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.79II -Indépendance LogiqueLes applications peuvent définir des vues logiquesde la BDGestion des médicamentsCabinet du Dr. MasseNombre_MédicamentsId-MNomDescriptionNombre1Aspegic 1000...................................302Fluisédal...................................203Mucomyst...................................230....................................................

PrénomNomId-D

JeanMasse3

PaulDurand2

PierreDupont1

Docteurs

PrénomNomId-D

JeanMasse3

PaulDurand2

PierreDupont1

Docteurs

Patients

Paule John Zoe

Jacques

Prénom

ValentonPerry4

VilleNomId-P

ParisDoe3

EvryTroger2

ParisLebeau1

Patients

Paule John Zoe

Jacques

Prénom

ValentonPerry4

VilleNomId-P

ParisDoe3

EvryTroger2

ParisLebeau1

2 gouttes332

12 8 5 12 Id-M

1 par jour

2 par jour

10 gouttes

1 par jour

Posologie

2 1 2 1 Ligne 2 2 1 1 Id-V

Prescriptions

2 gouttes332

12 8 5 12 Id-M

1 par jour

2 par jour

10 gouttes

1 par jour

Posologie

2 1 2 1 Ligne 2 2 1 1 Id-V

Prescriptions

DescriptionNomId-M

...................................Aspegic 10001

Médicaments

DescriptionNomId-M

...................................Aspegic 10001

Médicaments

Visites

2 2 1 1 Id-D

1 mars

13 juillet

12 août

15 juin

Date 250
350
180
250
Prix 4 3 2 1 Id-V 3 2 1 2 Id-P

Visites

2 2 1 1 Id-D

1 mars

13 juillet

12 août

15 juin

Date 250
350
180
250
Prix 4 3 2 1 Id-V 3 2 1 2 Id-P

Patients

Zoe

Jacques

PrénomVilleNomId-P

EvryTroger2

ParisLebeau1

Patients

Zoe

Jacques

PrénomVilleNomId-P

EvryTroger2

ParisLebeau1

DescriptionNomId-M

...................................Aspegic 10001

Médicaments

DescriptionNomId-M

...................................Aspegic 10001

Médicaments

Visites

2 2 1 1 Id-D

1 mars

13 juillet

12 août

15 juin

Date 250
350
180
250
Prix 4 3 2 1 Id-V 3 2 1 2 Id-P

Visites

2 2 1 1 Id-D

1 mars

13 juillet

12 août

15 juin

Date 250
350
180
250
Prix 4 3 2 1 Id-V 3 2 1 2 Id-P 12 8 5 12 Id-M

1 par jour

2 par jour

10 gouttes

1 par jour

Posologie

2 1 2 1 Ligne 2 2 1 1 Id-V

Prescriptions

12 8 5 12 Id-M

1 par jour

2 par jour

10 gouttes

1 par jour

Posologie

2 1 2 1 Ligne 2 2 1 1 Id-V

Prescriptions

80

Avantages de l'indépendance logique•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.81III -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 82Exemple de question SQL (1)•Nom et description des médicaments de type aspirineSelect Nom, Description FromMédicamentsWhereType = 'Aspirine'83Exemple de question SQL (2)•Patients parisiens ayant effectués une visite le 15 juin Select Patients.Nom, Patients.PrénomFromPatients, VisitesWherePatients.Id-P= Visites.Id-Pand Patients.Ville= 'Paris'and Visites.Date= '15 juin'84

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•Syntaxe (exemple)CreateViewParisiensas(Select*FromPatientsWherePatients.Ville= 'Paris' )85Les vues :des relations virtuelles !Le SGBD transformela question sur les vues en question sur les relations de baseQuestion Q sur des vuesDéfinition des vuesGestionnaire de VuesQuestion Q' sur les relations de base

PrénomNomId-D

JeanMasse3

PaulDurand2

PierreDupont1

Docteurs

PrénomNomId-D

JeanMasse3

PaulDurand2

PierreDupont1

Docteurs

Patients

Paule John Zoe

Jacques

Prénom

ValentonPerry4

VilleNomId-P

ParisDoe3

EvryTroger2

ParisLebeau1

Patients

Paule John Zoe

Jacques

Prénom

ValentonPerry4

VilleNomId-P

ParisDoe3

EvryTroger2

ParisLebeau1

2 gouttes332

12 8 5 12 Id-M

1 par jour

2 par jour

10 gouttes

1 par jour

Posologie

2 1 2 1 Ligne 2 2 1 1 Id-V

Prescriptions

2 gouttes332

12 8 5 12 Id-M

1 par jour

2 par jour

10 gouttes

1 par jour

Posologie

2 1 2 1 Ligne 2 2 1 1 Id-V

Prescriptions

DescriptionNomId-M

...................................Aspegic 10001

Médicaments

DescriptionNomId-M

...................................Aspegic 10001

Médicaments

Visites

2 2 1 1 Id-D

1 mars

13 juillet

12 août

15 juin

Date 250
350
180
250
Prix 4 3 2 1 Id-V 3 2 1 2 Id-P

Visites

2 2 1 1 Id-D

1 mars

13 juillet

12 août

15 juin

Date 250
350
180
250
Prix 4 3 2 1 Id-V 3 2 1 2 Id-P

86Les 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 SGBDssupportent plus de mises à jour•Clause "Withcheck option»•Le SGBD vérifie que les tuplesinsérés ou mis à jour correspondent à la définition de la vue87Les 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 asSelectId-M, Nom, Description, count(*) From Médicaments M, Prescriptions PWhere M.Id-M = P.Id-Mrefresh every day•Objectif principal : la performanceII.88

89J3Vue d'ensemble des SGBDBDVIII -Concurrence d'accèsVII -Gestion des pannesI-Indépendance PhysiqueIX -Gestion de la confidentialitéII-Indépendance LogiqueVI -Gestion de la cohérenceX -StandardsV -Optimisation des questionsIII -Langage de manipulationIV -Gestion des vues90V -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.91Exemple de plan d'exécutionSelectPatients.Nom, Patients.PrénomFromPatients, VisitesWherePatients.Id-P= Visites.Id-PandPatients.Ville= 'Paris'andVisites.Date= '15 juin'PatientsVisites

ps 92
Plan d'exécution optimiséPatientsVisitesPatientsVisites pspspsp

93VI -(a) 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 électorale94Contraintes 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 contraintesBDBD95Contraintes d'intégrité : Syntaxecreate table ( [], (mono-attribut) [], ...[]) (mono ou multi-attributs)•Différent types de contraintes :•Non nullité : not null•Unicité : unique•Vérification : check •Clé primaire : primary key•Contrainte d'intégrité référentielle : references ()•on delete / on update

cascade, set null, set default•On peut nommer les contraintes II.96

Contraintes d'intégrité : les CI RéférentiellesCIR = Contraintes d'intégrité référentiellesDocteursId-DNomPrénom1DupontPierre2DurandPaul3MasseJean..................VisitesId-DId-PId-VDatePrix12115 juin25011212 août18022313 juillet3502341 mars250PrescriptionsId-VLigneId-MPosologie11121 par jour12510 gouttes2182 par jour22121 par jour2332 gouttes........................•Vérification lors de l'insertion, la suppression, la modification•Propagation des suppression, modification en cascade possible (on delete cascade)II.97Contraintes d'intégrité : Remarques finales•Contraintes supportées (exemple d'Oracle)•Les contraintes faisant intervenir un ou plusieurs attributs d'une seule table et d'un seul tuple •Contraintes d'intégrité référentielles avec cascade•Mécanisme puissant : •Exemple : supprimer les "anciens» patients (ceux qui ont consulté il y a au moins 2 ans) DocteursVisitesPrescriptionsMédicamentsPatientsII.98VI -(b) Déclencheurs : Définition•Définition : Déclencheurs ou Triggers•Règle E -C -A : Évènement -Condition -ActionLorsque l'évènementse produit•Insert / Update / Delete pour une relation donnéesi la condition est remplie•Prédicat SQL optionnel alors exécuter l'action•Code à exécuter (ex. PLSQL sous Oracle)•Pour chaque tuple concerné ou une fois pour l'évènementII.99Déclencheurs : Objectifs•Objectif : une base de données 'active'•valider les données entrées, dériver des données additionnelles•maintenir des règles d'intégrité complexes•créer un audit de la base de données•implanter des règles métier (exemple: envoi d'alertes par e-mails)•Gains•simplification du code des applications•sécurité renforcée par l'automatisation•les déclencheurs sont stockées dans la base•Cohérence globale des déclencheursBDBDII.100

quotesdbs_dbs25.pdfusesText_31

[PDF] BD - Quartier lointain - Insuf-FLE

[PDF] BD - Saint

[PDF] BD - «La femme est l`avenir de l`homme

[PDF] BD : Malade d`amour, à Bombay

[PDF] BD Astérix, Lucky Luke et Tintin - Figurines

[PDF] BD de filles - Pointe

[PDF] Bd de Strasbourg BP 465 – MORONI - Comores - Gestion De Projet

[PDF] BD drôles et drôles de BD - Détail Club BD du mercredi 30 - Anciens Et Réunions

[PDF] BD du 8 mai 1945, 03300, CUSSET, Auvergne Tel: 0470314455 - France

[PDF] BD ecologie 3 - Pays Sud

[PDF] BD ecologie 4 - Pays Sud

[PDF] BD Emerald™ Syringe For General Purpose Use - Conception

[PDF] BD Enfants - Activités et sorties du Clas Inserm de Lille - Généalogie

[PDF] BD et dessin vectoriel

[PDF] BD et écologie : une bibliographie - Blog des bibliothèques de Saint - Anciens Et Réunions