Principes des bases de données - (CUI) - UNIGE
publicité
Plan Principes Bases de données et bases de données spatiales Gilles Falquet, Claudine Métral Centre universitaire d'informatique Université de Genève des bases de données Le modèle relationnel de données Interrogation d’une base de données: introduction au langage SQL Intégrité des données Mise à jour des données avec SQL Conception physique et logique d’une base de données G. Falquet et C. Métral 2 Objectifs des bases de données (BD) Stockage permanent de données Description des données Consultation, sélection, modification des données Recherche de l'information par le contenu Accès multiples simultanés (concurrents) Intégrité des données Sécurité, fiabilité (reprise après pannes) Confidentialité Principes des bases de données G. Falquet et C. Métral 3 G. Falquet et C. Métral 4 Système de gestion de base de données (SGBD) utilisateur 1 application 2 Stockage permanent des données Média de stockage persistant disques application 1 requêtes réponses SGBD Toute mise à jour des données est immédiate et durable pas lectures / écritures magnétiques d'opération "Enregistrer" (Excel, Word, etc.) Structuration des données pour obtenir des performances acceptables données G. Falquet et C. Métral 5 Description des données Cas des fichiers A 1 2 2 2 2 . version 20328 KSBD 20328 KAVX 20328 KSBD 20329 KSBD . . L26 KAVX KSBD KSBD 2 0 0 1 G. Falquet et C. Métral 6 Problèmes avec les fichiers A 4 SoCal:hangarWall // polygon # 5 28.7 9.8 -43.7 28.7 0 -43.7 28.7 0 4 28.7 9.8 4 4 SoCal:hangarIn // polygon # 6 -28.6 9.8 -43.7 -28.6 0 -43.7 -28.6 0 4 -28.6 9.8 4 0.0 0.0 0.1 N199 Speed-Bird …0.1 0.1// 0.0 0.0 0.0 N9492E Speed-Bird 99 0.1 0.0 0.0 0.0 N9492E Glasair II-S 0.1 0.0 0.0 0.0 N9492E Glasair II-S Ne contiennent que les données Pas de description standardisée des contenus Chaque application/utilisateur doit connaître la structure des fichiers Où est la description ? G. Falquet et C. Métral 7 G. Falquet et C. Métral 8 Principe d’une BD: le schéma Données Un schéma décrit la structure des données Base de données = schéma + données ZK567 GVA ZRH 43 106 KL1122 AMS CDG 50 77 KL232 AMS PPP 560 230 LX441 GVA NCE 35 101 Le schéma fait partie intégrante de la base Les données ne peuvent exister sans le schéma 9 G. Falquet et C. Métral Données + schéma G. Falquet et C. Métral 10 Unicité du schéma On voit toujours la base à travers son schéma Un schéma s'exprime selon un modèle Vol Départ Arrivée Durée Passagers ZK567 GVA ZRH 43 106 KL1122 AMS CDG 50 77 KL232 AMS PPP 560 230 LX441 GVA NCE 35 101 schéma données G. Falquet et C. Métral 11 G. Falquet et C. Métral 12 Modèle relationnel de données E. F. Codd (1970) Objets simples : tables, lignes, colonnes Basé sur des objets mathématiques bien connus: relation, n-tuple, ensemble, etc. Opérations d'interrogation: sélection, Le modèle relationnel de données projection, jointure Actuellement le modèle le plus répandu (de loin) 13 G. Falquet et C. Métral G. Falquet et C. Métral Table (relation) Types (domaines) des colonnes Ensemble de rangées subdivisées en colonnes Chaque colonne porte un nom (attribut) !! Pas d'ordre (numéro) des rangée (≠ Excel) 14 Les valeurs d'une colonne sont toutes du même type Types "standard" : nombre entier, nombre réel (décimal), chaîne de caractère, date table Vins Région Année Qualité Bordeaux 1991 Excellent Bourgogne 1991 Moyen Bordeaux 1990 Bon G. Falquet et C. Métral CREATE TABLE Vins( Région varchar, Année integer, Qualité varchar) 15 G. Falquet et C. Métral 16 Base de données relationnelle Signification des données Ensemble de tables Chaque rangée représente un fait Schéma de la base de données On peut l'exprimer sous forme d'une phrase Exemple Noms des tables Noms de leurs colonnes Types des colonnes R A Q rangée de la table Vins(Région, Année, Qualité) signifie "Les vins de la région R ont eu une qualité Q pendant l'année A" Une Exemple (sans les types) Vins(région, année, qualité) Producteur(nom, commune, région, quantité) G. Falquet et C. Métral 17 G. Falquet et C. Métral 18 Exemple de base de données SubregionsRegions(SREG_NAME, REG_NAME) Countries(ID, NAME, SOVEREIGN, SREG_NAME, ISO_ 2_CODE,ISO_3_CODE, UN_CODE, GEO_NAME) ECO2(COUNTRY_GEO_NAME,YEAR, CO2_VALUE) Interrogation d’une base de données: introduction au langage SQL ForestsCountries(FOREST_ID, COUNTRY_GEO_NAME) Forests(ID, NAME,TYPE,COUNTRIES) G. Falquet et C. Métral 19 G. Falquet et C. Métral 20 Interrogation d'une BD Sélection Extraire des informations d'une base de donnée Opérations sélection, A partir d'une table ne retenir que les rangées qui satisfont une condition Résultat = une table (plus petite) projection, jointure, etc. Langage d'interrogation standard SQL (Structured Query Language langage d’interrogation structuré) table table’ 21 G. Falquet et C. Métral Exemple 1 22 G. Falquet et C. Métral Exemple 2 Région Année Qualité Région Année Qualité Bordeaux 1991 Excellent Bordeaux 1991 Excellent Bourgogne 1991 Moyen Bourgogne 1991 Moyen Valais 2000 Excellent Valais 2000 Excellent S [Année = 1991] S [Qualité = ‘Excellent’] Région Année Qualité Bordeaux 1991 Excellent Valais 2000 Excellent Valais 1991 Moyen Valais 1991 Moyen Bordeaux 1990 Bon Bordeaux 1990 Bon select * from Vins where Qualité = 'Excellent' G. Falquet et C. Métral 23 Région Année Qualité Bordeaux 1991 Excellent Bourgogne 1991 Moyen Valais 1991 Moyen G. Falquet et C. Métral select * from Vins where Année = 1991 24 Exemple 3 S [Année > 1990 et Région = ‘Bordeaux’] Région Année Qualité Bordeaux 1991 Excellent Exercice Région Année Qualité Bordeaux 1991 Excellent Bourgogne 1991 Moyen Valais 2000 Excellent Valais 1991 Moyen Bordeaux 1990 Bon select * from Vins where Année > 1990 and Région = 'Bordeaux' Lister les informations correspondant aux pays dont les ID sont inférieurs à 100 Lister les valeurs d’émission de CO2 des pays pour l’année 1990 Lister les informations correspondant à des forêts de type ‘Mangroves’ 25 G. Falquet et C. Métral Exemple Projection Supprimer des colonnes d'une table Résultat une P [Région] table En théorie les table qui a moins de colonnes doublons sont supprimés (une table est un ensemble) Région Année Qualité Bordeaux 1991 Excellent Bourgogne 1991 Moyen Valais 2000 Excellent Valais 1991 Moyen Bordeaux 1990 Bon Région Bordeaux Bourgogne En pratique les 26 G. Falquet et C. Métral Valais doublons restent (multi-ensemble) G. Falquet et C. Métral 27 G. Falquet et C. Métral 28 En SQL select Région from Vins En SQL (2) Région Année Qualité Région Année Qualité Bordeaux 1991 Excellent Bordeaux 1991 Excellent Bourgogne 1991 Moyen Bourgogne 1991 Moyen Valais 2000 Excellent Valais 2000 Excellent Valais 1991 Moyen Valais 1991 Moyen Bordeaux 1990 Bon Bordeaux 1990 Bon select distinct Région from Vins Région Bordeaux Bourgogne Région Valais Ne correspond pas à la définition théorique: les doublons sont gardés Valais Bordeaux G. Falquet et C. Métral 29 La pratique rejoint la théorie Bourgogne Valais 30 G. Falquet et C. Métral Sélection + projection en SQL Exercice Bordeaux Lister toutes les régions select colonne1, colonne2, … from table where condition Quels sont les pays possédant des forêts? table condition P[col1, col2, ...] G. Falquet et C. Métral 31 G. Falquet et C. Métral 32 Jointure Exercice Opération sur deux tables But : mettre ensemble les rangées qui correspondent selon une condition Quelles sont les sous-régions composant la région ‘Asia + Pacific’? A quelle région appartient la sous-région ‘Arctic’? Quels pays possèdent des forêts de type ‘Mangroves’ G. Falquet - Uni. Genève - CUI G. Falquet et C. Métral 33 Exemple inscription etudiant etudiant(noetd, nom) inscription(cours, noetd, date) etudiant J [noetd = noetd ] inscription noetd nom cours noetd date 6 Jean Italien 6 2001 3 Anne Chimie 3 2002 8 Sylvie Italien 7 1999 Chimie 6 2001 select * from etudiant, inscription where etudiant.noetd = inscription.noetd En SQL select * from etudiant, inscription where etudiant.noetd = inscription.noetd G. Falquet et C. Métral 34 G. Falquet et C. Métral 35 noetd nom cours noetd date 6 Jean Italien 6 2001 6 Jean Chimie 6 2001 3 Anne Chimie 3 2002 G. Falquet et C. Métral 36 Fonctionnement Exemple de base de données On peut imaginer que les choses se passent de la façon suivante: R J [condition] S SubregionsRegions(SREG_NAME, REG_NAME) Countries(ID, NAME, SOVEREIGN, SREG_NAME, ISO_ 2_CODE,ISO_3_CODE, UN_CODE, GEO_NAME) établir toutes les paires de rangées de R et S possibles sélectionner les paires qui remplissent la condition ECO2(COUNTRY_GEO_NAME,YEAR, CO2_VALUE) Remarque. Sans condition, on obtient le produit cartésien de R et S, i.e. toutes les combinaisons possibles ForestsCountries(FOREST_ID, COUNTRY_GEO_NAME) Forests(ID, NAME,TYPE,COUNTRIES) G. Falquet et C. Métral 37 38 Agrégation Exercice G. Falquet et C. Métral Dans quelle sous-région et quelle région se situe le pays dont le GEO_NAME est ‘Australia’? Indiquer la sous-région, la région et le type de forêt associés au pays dont le GEO_NAME est ‘Australia’ Calculer des valeurs dépendant de toutes les rangées sélectionnées Fonctions : sum(), avg(), count(), min(), max() Exemple: select count(noetd) from etudiant G. Falquet et C. Métral 39 G. Falquet et C. Métral 40 Groupement Requêtes avec groupement Regroupement des rangées d'un résultat Critère : même valeur pour une colonne / expression Utilité : uniquement avec des fonctions d'agrégation table jointure, sélection Exemple: groupement select count(noetd) from inscription group by cours fonctions d'agrégation / projection G. Falquet et C. Métral 41 42 Exercice Agrégation et questions imbriquées Exemple: Liste des pays (avec l’année et la valeur d’émission de CO2) dépassant la valeur moyenne d’émisssion de CO2 select COUNTRY_GEO_NAME, YEAR, CO2_VALUE from ECO2 where CO2_VALUE>(select AVG(CO2_VALUE) from ECO2) G. Falquet et C. Métral G. Falquet et C. Métral 43 Calculer le nombre de sous-régions composant la région ‘Asia + Pacific’ Calculer la valeur moyenne d’émission de CO2 Lister les valeurs cumulées d’émission de CO2 par année Lister les pays (avec l’année et la valeur d’émission de CO2) dépassant la valeur moyenne d’émisssion de CO2 G. Falquet et C. Métral 44 Intégrité des données Dans la réalité il y a des faits particuliers Et des règles générales «Une latitude est comprise entre -90 et +90» Intégrité des données «Une longitude est comprise entre -180 et +180» … ou spécifiques à un domaine «Tous les départements ont des numéros distincts» «Il ne peut pas y avoir plus de 30 inscrits au cours d'Italien» «La somme des salaires ne doit pas excéder le budget» G. Falquet et C. Métral 45 Dans la base de données G. Falquet et C. Métral Règles sur une colonne Les rangées représentent les faits particuliers inscrit(c, n, a) : l'étudiant n est inscrit au cours c pour l'année a 46 On restreint les valeurs d'une colonne interdiction des valeurs indéfinies (NOT NULL) définition d'un minimum et d'un maximum définition d'une liste fermée de valeurs admises Les contraintes d'intégrité restreignent le contenu de la base garantissent que la base respecte les règles de la réalité sont vérifiées automatiquement par le SGBD Vérifié automatiquement par le SGBD pour certaines catégories seulement ! G. Falquet et C. Métral 47 G. Falquet et C. Métral 48 Contraintes de clé (unicité) Colonne clé deux rangées ne peuvent avoir la même valeur pour cette colonne détermine les valeurs des autres colonnes ne peut être nulle Exemple: Mise à jour des données avec SQL etudiant(noetd,nom) clé: noetd Il peut y avoir plusieurs clés Et des clés formées de plusieurs colonnes G. Falquet et C. Métral 49 Mise à jour des données Evolution représente réalité change au cours du temps Il faut donc adapter les données Les données "définissent" une réalité par base de données Les données représentent une réalité Cette 50 G. Falquet et C. Métral exemple création d'un emploi du temps événements Importance de garantir le respect des règles d'intégrité adaptation opérations représente G. Falquet et C. Métral 51 G. Falquet et C. Métral 52 Ajout d'une rangée Opérations sur les données Ajouter une rangée Supprimer une rangée Modifier une rangée Opérations complexes (combinaisons) Doivent respecter les contraintes d'intégrité Ajouter une rangée dans la table avec l’attribut col1 ayant la valeur val1, l’attribut col2 ayant la valeur val2, etc. insert into table(col1, col2, …) values (val1, val2, …) Exemple insert into inscrit(noetd, cours, année) values (3, 'Physique 2', 2002) G. Falquet et C. Métral 53 Suppression de rangées 54 Modification de rangées Supprimer toutes les rangées de table qui satisfont une condition delete from table where condition G. Falquet et C. Métral Pour toutes les rangées de table qui satisfont la condition, modifier les valeurs des colonnes col1, col2, etc. update table set col1 = val1, col2 = val2, … where condition Exemple delete from inscription where cours=‘Italien’ G. Falquet et C. Métral update Vins set qualité = 'Excellent' where région = 'Valais' 55 G. Falquet et C. Métral 56 Conception physique et logique Niveau logique : schéma de la base Niveau physique : Performances et optimisation G. Falquet et C. Métral comment les données sont réellement stockées sur le disque / en mémoire structures (complexes) prévues pour obtenir des performances maximales et un encombrement minimal 57 Amélioration des performances G. Falquet et C. Métral Index Par défaut la plupart des SGBD utilisent une méthode de stockage simple mais peu efficace par exemple une sélection nécessite l'inspection de toutes les rangées Pour améliorer les performance on peut créer des index fixer divers paramètres (dépendants du SGBD) G. Falquet et C. Métral 58 59 Structure pour accélérer la recherche Dans une colonne ou un groupe de colonnes create index idx on table (col1, …) La recherche d'une valeur dans une colonne indexée prend un temps quasi constant, indépendamment de la taille de la table L'index est maintenu à jour automatiquement G. Falquet et C. Métral 60 Optimisation des requêtes Exemples select * from etudiant where nom = 'Julie' Il faut examiner toutes les (100000) rangées On peut écrire des requêtes extrêmement coûteuses à évaluer multiples jointures, groupements, etc. longtemps le problème no. 1 des SGBD create index nom_etu on etudiant(nom) l'index est créé une fois pour toutes Les (bons) SGBD optimisent les requêtes en profitant des index… pour autant qu'ils existent ! en réordonnant les opérations, etc. select * from etudiant where nom = 'Julie' la recherche via l'index des noms est très rapide G. Falquet et C. Métral 61 Conception logique Un mauvais exemple Comment concevoir un bon schéma pour ma base? Il n'y a pas de recette absolue Il y a des techniques pour évaluer la qualité d'un schéma Principes de base Eviter la redondance -> lutte contre les anomalies de mise à jour À chaque type d'objet sa table -> lutte contre les anomalies d'ajout et de suppression G. Falquet et C. Métral 62 G. Falquet et C. Métral 63 projet produit fournisseur adresse_f nb_pieces pr1 écrou Paul Genève 10 pr1 boulon Paul Genève 10 pr2 vis Pierre Lausanne 50 pr3 rondelle Jean Genève 22 pr3 boulon Paul Genève 44 pr3 écrou Pierre Lausanne 33 G. Falquet et C. Métral 64 Anomalie de mise à jour Anomalie d'insertion On veut mettre à jour l'adresse du fournisseur Paul Il faut le faire dans toutes les rangées où Paul apparaît Risque : se retrouver avec deux adresses différentes pour Paul Dû à la redondance G. Falquet et C. Métral Si Marie ne fournit actuellement aucun projet, on ne peut pas le faire 66 G. Falquet et C. Métral Une solution : la décomposition On supprime les rangées concernant le projet pr3 (il est terminé) => On perd les informations sur le fournisseur Jean (adresse) G. Falquet et C. Métral On veut enregistrer le nom et l'adresse du fournisseur Marie 65 Anomalie de suppression 67 projet produit fournisseur nb_pieces pr1 écrou Paul 10 pr1 boulon Paul 10 fournisseur adresse pr2 vis Pierre 50 Paul Genève pr3 rondelle Jean 22 Pierre Lausanne pr3 boulon Paul 44 Jean Genève pr3 écrou Pierre 33 G. Falquet et C. Métral 68
