ANGD Mathrice – CIRM Marseille Avant-propos Avant
publicité
ANGD Mathrice – CIRM Marseille Avant-propos ● ● Introduction aux bases de données 6 novembre 2006 © 2006 Damien BRÉMONT [email protected] Avant-propos ● Damien BRÉMONT : – Administrateur Systèmes et Réseaux au CNRS ● – – Ingénieur CNAM III-SI en devenir Gestion du parc du LPTHE ● Laboratoire de Physique Théorique et des Hautes Énergies ● 120 Linux Fedora Core, 10 Mac OS X, 1 Windows Enseignant vacataire à l'IUT de Saint Malo ● Cours de Base de données de 1ère année ● Cours de Linux en licence professionnelle Ce cours est distribué sous la licence GNU FDL (Free Documentation Licence), Il peut être librement copié, modifié, distribué, tant que l'intégralité de cette licence est respectée. – ● La GNU FDL est disponible sur http://www.gnu.org/licenses/fdl.html © D. Brémont,O. Guillossou 08/2006. Plan du cours 1.Historique et concepts 2.Modélisation Conceptuelle des Données 3.Théorie de la normalisation 4.Passage au modèle relationnel 5.Stockage Physique 6.Modèle Relationnel 7.SQL 8.Contraintes et Intégrité 9.Transactions 10.Les bases de données pour un ASR Introduction ● Introduction et concepts Constats : – Les activités humaines génèrent de plus en plus de données – Les données ont souvent la même structure – On veut un accès facile à ces données – Tout le monde veut y accéder Définition ● Base de Données : (BDD - Data Base) – Structure de données permettant de recevoir, de stocker, et de fournir à la demande, des données à de multiples utilisateurs indépendants Définition ● Système de Gestion de Bases de Données – (SGBD – Data Base Management System) – Ensemble de logiciels supportant un modèle de données et permettant de mettre en oeuvre une BDD assurant : ● La description et la gestion des données ● La cohérence des données ● Gestion des accès concurrents ● La gestion des droits utilisateurs ● Retour à un état cohérent en cas d'incident Objectifs d'un SGBD Organisation SGBD ● Indépendance logique des programmes ● Indépendance physique des programmes – Interprétation syntaxique des requêtes ● Manipulation par langages non procéduraux – Présentation des données ● Facilité d'administration ● Efficacité des accès ● ● ● Architecture fonctionnelle Outils SGBD et non SGBD Analyseur de requêtes SQL Contrôle Vis-à-vis DD Optimiseur de requêtes SGBD externe SGBD interne – Traitement des requêtes – Gestion des objets Gestionnaire de fichiers L'approche client-serveur Client Serveur Application cliente SGBD Interface de programmation Interface serveur Interface de communication Interface de communication Noyau SGBD Exécution des requêtes Protocole de transport Protocole de transport SQL BD Réseau Données Historique SGBD ● Années 60 : fichiers reliés par des pointeurs – – ● Séparation description des données/langage d'accès Années 80 : BDD Objets, réparties – Représenter le plus fidèlement le réel – Répondre aux nouveaux besoins nés de l'Internet et du multimédia SGBD de type réseaux ou hiérarchiques ● ● Historique SGBD CODASYL ● Années 70 - 80 : modélisation, SQL – Objectif : représenter le réel – Visent à faciliter l'accès aux données ● Oracle, Sybase, DB2, Ingres Le marché des SGBD ● ● ObjectStore, O2, Intersystem Caché Marché SGBD 2004 : 14,9 Milliards de $ +8,3% en 2005 Parts de marché : Éditeur Produit IBM DB2 / Informix Oracle Oracle Microsoft SQL Server Sybase Sybase NCR Teradata NCR Teradata Total 2004 2005 30,60% 34,00% 41,30% 33,30% 13,40% 19,00% 3,10% 3,70% 3,10% 3,14% 91,50% 93,14% Source IDC (2004), Data Base developpement survey (2005) Les SGBD libres ● Très forte croissance du nombre d'installation – 32 % des SGBD installés ● Produits arrivés à maturité ● Très utilisés dans les applications orientées web Plan du cours 1.Historique et concepts 2.Modélisation Conceptuelle des Données 3.Théorie de la normalisation 4.Passage au modèle relationnel 5.Stockage Physique 6.Modèle Relationnel 7.SQL 8.Contraintes et Intégrité 9.Transactions 10.Les bases de données pour un ASR La modélisation conceptuelle des données (MCD) La modélisation conceptuelle des données (MCD) ● Objectif final : Modéliser le monde réel ● Objectifs MCD – Représenter de manière structurée, synthétique et fidèle les données du système d’information indépendamment de son implantation physique. La MCD ● Démarche : – ● Centrée sur le discours (parlé ou écrit) des utilisateurs en langue Naturelle Importance du MCD : – Construire le référentiel du projet – Sert à l'évaluation des coûts – Documenter le système d’information – Une erreur peut avoir des conséquences lourdes sur le projet La modélisation conceptuelle des données (MCD) ● Exemple de MCD Règles de construction ● Le modèle E/R comprend : – Le modèle Entité-Relation ou Entité-Association – Des entités – Proposé en 1976 par P. Chen – Des relations ou associations – Normalisé en 1988 par l'ANSI – Des propriétés ou attributs – Des contraintes Entité ● Relation ou association Objet ayant un existence dans le monde réel et clairement identifiable Client Commande ● Association entre entités précisant un événement du réel – Exemple de relation binaire : Cardinalités Fournisseur Client Produit Année budgétaire 0,n 1,1 Passe Date Livraison Association Pattes Commande Relation particulières ● Relation n-aires Entité faible Mariage ● Entité n'existant que si l'entité maître existe 1,1 Salarié Concluent 0,1 0,1 Homme ● 0,n Femme A Relation récursive 1,1 Enfant Personne 0,1 Mari Se marie 0,1 Femme Cardinalités / occurrences ● Occurrence – ● Synonyme : ligne, tuple, enregistrement 1,1 Est affecté Cardinalités – Cardinalité min : 0/1, Cardinalité max : 1/n Employé Nombre d'apparition dans une relation ● ● Exemple de cardinalités 1,1 Nombre de fois minimum et maximum d'une occurrence dans une relation Poste ● Facture 1,1 Porte sur 0,1 Commande Facture 1,1 Porte sur 0,n Commande Fournisseur 1,n Fournit 0,n Produit Les cardinalités doivent être vérifiées à tout instant Propriétés ● Caractéristique élémentaire décrivant : – Une entité : Client Type de propriété d'une entité ou d'une relation 0,n 1,1 ● Naturelle ● Composée Commande Passe NumClient NumCmd Nom DateCmd Adresse DateLivraison ● (le nom d'un employé) – Numéro de sécurité sociale – =>Peut alors être considérée globale ou concaténée Artificielle (NumClient) 0,n – Une relation : Produit NumProd synonyme : attribut 0,n Comprend Quantité Prix Désignation Clé d'une relation ● Combinaison minimale de propriétés permettant d'identifier une occurrence de manière unique dans une relation – Clé simple Caractéristiques d'une clé ● Univaluée ● Discriminante ● Stable ● Minimale Client NumClient Clé simple Nom Adresse – Clé composée Fournisseur Client NumFour Pays Adresse Clé composée Clé candidate ● Notation Un individu peut posséder une ou plusieurs clés distinctes appelées clés candidates ● Deux façons de représenter une entité – Textuelle ● Employé Client NumEmp Clé primaire NumSecuSoc Clé candidate – ETUDIANT(NUM_ETU, NOM, PRENOM) Graphique ETUDIANT Adresse ● NUM_ETU NOM PRENOM La clé retenue est nommée clé primaire Dans tous les cas les clés sont soulignées Contraintes inter relations ● Contraintes inter relations exemples Types de contraintes Equipe recherche Entité 2 – – Domaines ● Inclusion I ● Simultanées S ● Exclusion X Représentation ● ● Partielle P Totale T Entité 1 Est-dans Enseignant Relation A IT Enseignant chercheur ? Relation B Entité 3 Travaille Entreprise Vacataires Les sous types ● Contraintes inter entités Permet de faire apparaître une notion de spécialisation Personne Personne Matricule Matricule Nom Nom XT Étudiant Étudiant Filière Enseignant Enseignant Matière Matière Qualité d'un MCD ● Filière Lisibilité ● Expressivité ● Simplicité ● Des concepts contradictoires... Plan du cours 1.Historique et concepts 2.Modélisation Conceptuelle des Données 3.Théorie de la normalisation 4.Passage au modèle relationnel 5.Stockage Physique 6.Modèle Relationnel 7.SQL 8.Contraintes et Intégrité 9.Transactions 10.Les bases de données pour un ASR La théorie de la normalisation ● Objectif : permet de définir une méthode de conception de "bonnes" tables : La théorie de la normalisation ● Sans redondances ● Avec une bonne sémantique entre les données ● Sans valeurs nulles =>S 'appuie sur la notion de dépendances fonctionnelle entre attributs Les dépendances fonctionnelles ● On dit qu'un attribut (ou groupe d'attribut) Y est dépendant fonctionnellement d'un autre X – ● Type de DF ● – ● Si à une valeur de X est associée une valeur de Y Exemple de DF – VOITURE(nv, couleur, marque, puissance, modèle) ● ● nv->couleur ● modèle -> marque ● modèle -> puissance ● (puissance et marque) -> modèle Réflexivité : l'ensemble d'attribut Y est inclus dans X alors X -> Y Augmentation : Si X détermine Y, les deux ensembles peuvent être enrichis par un troisième Z – si X -> Y – alors XZ=> YZ Transitivité : – si X->Y et Y->Z – alors X -> Z Type de DF ● ● ● Pseudo-transitivité : – si X -> Y et Y,Z -> T – alors X,Z -> T Décomposition : – si X -> Y,Z – alors X -> Y et X -> Z Réunion : – si X -> Y et X -> Z – alors X -> Y,Z Exemple d'anomalie de DF COURS NOMPROF BREMONT BREMONT BREMONT PETIT PETIT PETIT Objectif : – – Définir des règles pour décomposer des relations tout en préservant les DF sans perte d'information Les seules DF autorisées sont celles dans lesquelles une clé détermine un attribut DEPARTEMENT NOMETU Ille & Vilaine Durand Ille & Vilaine Dupont Martin Ille & Vilaine Ille & Vilaine Durand Ille & Vilaine Dupont Ille & Vilaine Martin AGE 19 19 20 19 19 20 NOMCOURS BDD BDD BDD Maths Maths Maths NOTE 15 2 12 10 0 5 DF : NOMPROF -> NOMCOURS NOMPROF -> VILLE VILLE -> DEPARTEMENT NOMETU -> AGE NOMETU, NOMCOURS -> NOTE Formes Normales Boyce-Codd Normal Form ● VILLE St Malo St Malo St Malo St Malo St Malo St Malo 1ère Forme normale ● Une relation est en 1FN si tout attribut contient une valeur atomique (non décomposable) : ● Exemple : ● PERSONNE (NOM,TYPE_TEL_1, TEL_1,TYPE_TEL_2, TEL_2,TYPE_TEL_3, TEL_3) ● Solution : ● PERSONNE (NOM) ● TEL (NOM, TEL, TYPE_TEL) 2ème Forme normale ● ● Une relation R est en 2 FN si ● Une relation R est en 3 FN si – Elle est en 1FN – Elle est en 2FN – Tout attribut n'appartenant pas à la clé ne dépend pas d'une partie de cette clé – Tout attribut n'appartenant pas à la clé, ne dépend pas d'un attribut non clé Exemple – ● 3ème Forme normale ● COURS (NOMPROF, VILLE, NOMETU, AGE, NOMCOURS, NOTE) Solution – ENSEIGNER ( NOMETU, NOMCOURS, NOTE) – COURS (NOMCOURS, NOMPROF, VILLE) – ETUDIANT (NOMETU, AGE) Exemple – ENSEIGNER ( NOMETU, NOMCOURS, NOTE) – COURS (NOMCOURS, NOMPROF, VILLE, DEPARTEMENT) – ETUDIANT (NOMETU, AGE) 3ème Forme normale ● Solution – ENSEIGNER ( NOMETU, NOMCOURS, NOTE) – COURS (NOMCOURS, NOMPROF, VILLE) – ETUDIANT (NOMETU, AGE) – LIEU_ENSEIGNEMENT (VILLE, DEPARTEMENT) Optimisation d'un schéma ● Objectif : – Améliorer les performances du système ● – Ex : une jointure très fréquente sur de gros volumes de données On peut alors procéder à une dénormalisation Plan du cours 1.Historique et concepts 2.Modélisation Conceptuelle des Données 3.Théorie de la normalisation 4.Passage au modèle relationnel 5.Stockage Physique 6.Modèle Relationnel 7.SQL 8.Contraintes et Intégrité 9.Transactions 10.Les bases de données pour un ASR Passage au modèle relationnel Passage au modèle relationnel ● Objectif : – Passer du Modèle Conceptuel des Données au Modèle Physique des Données (MPD) Règles de construction ● Relation binaire (*,*) – (1,1) – MCD Client 0,n 1,1 Commande Passe IdClient IdCommande Nomclient Date Addrclient ● Mode opératoire : – Basé sur les cardinalités et règles de gestion – Manuel ou automatisable – Modèle relationnel Client Commande IdClient IdCommande Nomclient Date Addrclient IdClient Règles de construction ● Règles de construction Relation binaire (*,n) – (0,1) – MCD Personne IdPersonne – ● Possède Date_Achat – Voiture 0,1 Relation binaire (*,n) – (*,n) Nom Couleur DateCmd Prenom Modèle – IdPersonne Nom Immatriculation Prenom Date_Achat Commande Personne 0,1 Nom IdPersonne Nom Prenom SeMarieAvec OU Produit NumProd Couleur DateCmd Désignation Modèle DateLivraison NumProd Quantité Prix Règles de construction ● Relation réflexive (*,n) – (*,n) – Mari MCD Travaux Se marie 0,n Se décompose IdTravaux 0,1 Femme Nom 0,n responsable Modèle relationnel Personne Comprend NumCmd Prenom – Désignation NumCmd Relation réflexive (*,1) – (*,n) IdPersonne NumProd Immatriculation Règles de construction MCD 0,n Modèle relationnel Voiture Possède Produit Comprend Quantité Prix 0,n DateLivraison Modèle relationnel IdPersonne – Commande NumCmd 0,n Personne ● MCD Immatriculation – Modèle relationnel Mariage IdPersonne Personne IdPersonne Travaux IdTravaux Décompose IdTravaux SeMarieAvec Nom Nom Appartient_à Prenom responsable Règles de construction ● Garage Relation ternaire -MCD : – Règles de construction ● IdGarage Adresse Modèle relationnel Personne Hiérarchisation -MCD : Matricule Nom nbplaces XT 0,n Gare 0,n Étudiant 0,1 Enseignant Filière Chauffeur Voiture Garage IdChauffeur Immatriculation IdGarage Nom Couleur Adresse Prénom Modèle – Modèle relationnel Matière Personne Matricule nbplaces Nom Gare IdGarage Chauffeur IdChauffeur Immatriculation Voiture IdChauffeur Immatriculation Nom Couleur Prénom Modèle Conclusion Passage au modèle relationnel ● Phase importante ● 1 bug dans 1 programme – ● 1 programme impacté 1 donnée erronée – n programmes impactés Étudiant Enseignant Matricule Matricule Filière Matière Plan du cours 1.Historique et concepts 2.Modélisation Conceptuelle des Données 3.Théorie de la normalisation 4.Passage au modèle relationnel 5.Stockage Physique 6.Modèle Relationnel 7.SQL 8.Contraintes et Intégrité 9.Transactions 10.Les bases de données pour un ASR Pourquoi structurer un fichier ? ● Formes non structurées: – Cassette audio – Fichier non structuré Stockage physique Pourquoi structurer un fichier ? ● Formes structurées: – Pourquoi structurer les fichiers ? ● CD audio ● – Fichier indexé ● Dans un fichier “plat” : – La recherche d'une donnée nécessite l'analyse de l'intégralité du fichier – L'information est rarement ordonnée Besoin de mécanismes pour retrouver rapidement les informations Deux méthodes : – Hachage des fichiers – Indexation des fichiers Hachage des fichiers ● ● ● Hachage des fichiers Le hachage (“hash”) de fichier est la structuration d'un fichier en articles (“slices”), ● Exemple de structure de fichier haché clé La position de chaque article dans le fichier est calculée à l'aide d'une fonction de hachage appliquée sur une clé unique. nombre de 0 à N-1 0 1 2 ... N-1 Exemple de fonctions de hachage – MD4, MD5 – SHA1, SHA2 Hachage des fichiers ● Fct de hachage Hachage des fichiers La fonction de hachage doit être rapide – ● la plus simple : calcul de modulo. ● Exemple : dans un fichier de 35 articles, la fonction modulo 35 sera utilisée pour calculer la position de l'article. ● Clé : 127 0 %35 ... 22 ... 34 ● Opérations de lecture d'un fichier haché : – application de la fonction de calcul, – positionnement du curseur de lecture sur l'article recherché. Opérations d'écriture d'un fichier haché : – calcul de la position de l'article à l'aide de la fonction de hachage appliqué à la clé, – écriture de l'article. Exemples d'utilisation : Postfix Indexation des fichiers ● Qu'est-ce qu'un index ? – ● Indexation des fichiers ● Exemple de fichier indexé : Un index est une table de correspondance entre une clé et la position de l'article dans le fichier. clé À la différence du hachage, le calcul de l'index est réalisé lors des opérations d'écriture. 0 0 1 0 20 2 10 15 30 4 55 6 1 2 20 4 0 10 15 30 55 59 59 adresse relative Indexation des fichiers ● ● Opérations de lecture d'un fichier indexé : – lecture de l'index, – recherche dans l'index de la position de l'article, – positionnement du curseur de lecture, – lecture de l'article Opérations d'écriture des fichiers indexés : – écriture de l'article à la fin du fichier, – ajout à l'index d'une entrée (clé, position) Index hiérarchisés ● Objectif : améliorer la recherche d'informations – ● Mais mise à jour plus coûteuse Exemple – 6 les Arbres-B (“B-Tree” pour “Balanced Tree”) Index hiérarchisés – Arbres B ● Modèle d'Arbre B Index hiérarchisés – Arbres B ● 11 ● 5 1 2 3 4 6 7 8 16 9 10 21 ● On descend en comparant la clé recherchée aux valeurs inscrites dans les noeuds 12 13 14 15 17 18 19 20 22 23 24 26 Utilisation des index ● Une recherche dans un arbre B se passe toujours depuis la racine, Les méthodes d'indexation de fichiers sont utilisées de façon intensive et complexe par les SGBDR. Chaque SGBDR a son propre format de représentation des données sur le disque Plan du cours 1.Historique et concepts 2.Modélisation Conceptuelle des Données 3.Théorie de la normalisation 4.Passage au modèle relationnel 5.Stockage Physique 6.Modèle Relationnel 7.SQL 8.Contraintes et Intégrité 9.Transactions 10.Les bases de données pour un ASR Bases du modèle relationnel ● Principe – ● Modèle Relationnel ● La manipulation des données selon leur concept mathématique : l'algèbre relationnel Utilisé en général à l'intérieur de tout SGBD relationnel Un LMD algébrique est possible, mais en général peu commode pour l'homme – On préfère les requêtes SQL ... Opérations en algèbre relationnel Bases du modèle relationnel – Attribut : un identificateur décrivant une information pouvant être stockée en base. – Tuple : (N-uplet, occurrence) c'est un ensemble figuré par une relation – – Cardinalité : pour une relation, il s'agit de son nombre d'occurrences Degré : le degré d'une relation est son nombre de tuples ● Illustration des définitions : Attribut Tuple, n-uplet Marque Peugeot Renault Fiat Modèle 206 Clio Punto Année 2000 2003 1999 Couleur Vert Bleu Orange Relation Opérations en algèbre relationnel ● Union : Opérations en algèbre relationnel – – Seulement sur deux tables de même schéma, – Construction d'une table contenant toutes les lignes des deux tables primitives. – Notation : RELATION1∪RELATION2 Exemple d'union : Voiture1 Marque Peugeot Renault Fiat Modèle 206 Clio Punto Année 2000 2003 1999 Opérations en algèbre relationnel Différence : – Seulement sur deux tables de même schéma, – Construction d'une table contenant les lignes de la première table primitive qui n'appartiennent pas à la seconde table primitive. RELATION1­RELATION2 Voiture2 Marque Citroën Renault Ford Modèle C3 Clio Fiesta Année 2005 2003 1987 Couleur Noir Bleu Rouge Année 2005 2003 1987 Couleur Noir Bleu Rouge ∪ Voitures Marque Peugeot Renault Fiat Citroën Ford ● Couleur Vert Bleu Orange Modèle 206 Clio Punto C3 Fiesta Année 2000 2003 1999 2005 1987 Couleur Vert Bleu Orange Noir Rouge Opérations en algèbre relationnel – Exemple de différence : Voiture1 Marque Peugeot Renault Fiat Modèle 206 Clio Punto Année 2000 2003 1999 Couleur Vert Bleu Orange Voiture2 Marque Citroën Renault Ford ­ Voitures Marque Peugeot Fiat Modèle 206 Punto Année 2000 1999 Couleur Vert Orange Modèle C3 Clio Fiesta Opérations en algèbre relationnel ● Produit cartésien : – le schéma des tables primitives peut être différent, – addition de toutes les combinaisons des lignes et des colonnes des tables primitives Opérations en algèbre relationnel – Voiture1 Marque Peugeot Renault Fiat – Sélection d'une ou plusieurs colonnes d'une table ∏ Attribut i , Attribut j RELATION1 Couleur Vert Bleu Orange Années Voitures Marque Modèle Peugeot 206 Renault Clio Fiat Punto Peugeot 206 Renault Clio Fiat Punto Opérations en algèbre relationnel Projection : Modèle 206 Clio Punto Année 2000 2005 × RELATION1×RELATION2 ● Exemple de produit cartésien Couleur Vert Bleu Orange Vert Bleu Orange Année 2000 2000 2000 2005 2005 2005 Opérations en algèbre relationnel – Exemple de projection : Voitures Marque Peugeot Renault Fiat Peugeot Ford Modèle 206 Clio Punto 307 Fiesta ∏ marque , modèle Rés Marque Peugeot Renault Fiat Peugeot Ford Modèle 206 Clio Punto 307 Fiesta Année 2000 2003 1999 2004 1987 Couleur Vert Bleu Orange Jaune Rouge Opérations en algèbre relationnel ● Opérations en algèbre relationnel Sélection – – Extraction de lignes d'une table en fonction d'une condition. Exemple de sélection simples : Voitures Marque Peugeot Renault Fiat Peugeot Ford année2000 Modèle 206 Clio Punto 307 Fiesta Année 2000 2003 1999 2004 1987 Couleur Vert Bleu Orange Jaune Rouge condition RELATION1 Voitures Marque Peugeot Renault Peugeot Opérations en algèbre relationnel ● Rappel des notations en algèbre booléenne – – – ● ∧ ∨ ¬ « et » « ou » « non » Ces opérateurs permettent d'écrire des opérations plus complexes sur les relations Modèle 206 Clio 307 Année 2000 2003 2004 Couleur Vert Bleu Jaune Opérations en algèbre relationnel ● Exemples d'opérations : annee2000∧regulateurVitesse=vrai Voiture Marque Peugeot Renault Fiat Peugeot Ford VW BMW Modèle 206 Clio Punto 307 Fiesta Passat 320 Année RegulateurVitesse 2000 FAUX 2003 VRAI 1999 FAUX 2004 VRAI 1987 FAUX 1999 VRAI 1998 VRAI Opérations en algèbre relationnel ● La jointure : – Deux tables ayant une contrainte référentielle commune, mais des structure pouvant être différentes – Addition de toutes les combinaisons des lignes et des colonnes des tables primitives – Puis sélection sur les contraintes référentielles ● ● RELATION1ZYRELATION2 clefs RELATION1×RELATION2 Opérations en algèbre relationnel ● Exemple de jointure Voiture Immatriculation Marque Modèle Couleur Maintenance Idmaint Immatriculation Panne Km 123 ABC 75 Vert 1 123 ABC 75 Joint de culasse 200000 Peugeot 206 Renault Clio 456 ABC 75 Bleu 2 456 ABC 75 Pneus 50000 Punto Orange 789 ABC 75 Fiat 3 456 ABC 75 Embrayage 60000 × Immatriculation 123 ABC 75 456 ABC 75 789 ABC 75 123 ABC 75 456 ABC 75 789 ABC 75 123 ABC 75 456 ABC 75 789 ABC 75 Marque Peugeot Renault Fiat Peugeot Renault Fiat Peugeot Renault Fiat Modèle 206 Clio Punto 206 Clio Punto 206 Clio Punto Couleur Idmaint Immatriculation Panne Km Vert 1 123 ABC 75 Joint de culasse 200000 Bleu 1 123 ABC 75 Joint de culasse 200000 Orange 1 123 ABC 75 Joint de culasse 200000 Vert 2 456 ABC 75 Pneus 50000 Bleu 2 456 ABC 75 Pneus 50000 Orange 2 456 ABC 75 Pneus 50000 Vert 3 456 ABC 75 Embrayage 60000 Bleu 3 456 ABC 75 Embrayage 60000 Orange 3 456 ABC 75 Embrayage 60000 sélection voiture1.immatriculation=panne.immatriculation Immatriculation 123 ABC 75 456 ABC 75 456 ABC 75 Marque Modèle Couleur Idmaint Immatriculation Panne Km Vert 1 123 ABC 75 Joint de culasse 200000 Peugeot 206 Renault Clio Bleu 2 456 ABC 75 Pneus 50000 Renault Clio Bleu 3 456 ABC 75 Embrayage 60000 Plan du cours 1.Historique et concepts 2.Modélisation Conceptuelle des Données 3.Théorie de la normalisation 4.Passage au modèle relationnel 5.Stockage Physique 6.Modèle Relationnel 7.SQL 8.Contraintes et Intégrité 9.Transactions 10.Les bases de données pour un ASR SQL Structured Query Language SQL La définition de données ● A la fois LDD (Langage de Définition de Données) ● et LMD (Langage de Manipulation de Données) ● Développé par IBM (1982) ● Devenu une norme (ANSI/ISO) en 1986 (SQL1) ● Puis en 1992 sortie de SQL2 ● permet de : – ● créer ● modifier ● supprimer ● Renommer les éléments du schéma d'une base de données : – les tables – les vues – les index Exemple utilisé ● FOURNISSEUR 0,n 0,n Les tables PRODUIT FOURNIT FOURNUM PRODNUM NOMFOUR NOMPROD ● ● NOTE VILLE ● FOURNISSEUR (FOURNUM, NOMFOUR, NOTE, VILLE) ● FOURNIT (FOURNUM, PRODNUM) ● PRODUIT (PRODNUM, NOMPROD) Création CREATE TABLE nomtable ( nomcol1 typecol1 [contraintecol1], nomcol2 typecol2 [contraintecol2], ..., contraintetable1, contraintetable2,...); – CREATE TABLE `FOURNISSEUR` ( `FOURNUM` INT NOT NULL , `NOMFOUR` VARCHAR( 20 ) NOT NULL , `NOTE` TINYINT NOT NULL , `VILLE` VARCHAR( 50 ) NOT NULL , PRIMARY KEY ( `FOURNUM` )); Les types de données Nom TINYINT SMALLINT MEDIUMINT INT BIGINT FLOAT DOUBLE DATE DATETIME TIME CHAR(M) VARCHAR(M) BLOB TEXT LONGBLOB LONGTEXT Nb octets Plage 1 -128 à 127 2 -32 768 à 32 767 3 ... 4 8 4 ou 8 Précision 24 ou 53 8 Nombre flottant double précision 3 8 4 M L+1 L+1 L+2 L+4 L+4 Option Unsigned Unsigned Unsigned Unsigned Unsigned Unsigned AAAA-MM-JJ AAAA-MM-JJ HH:MM:SS HH:MM:SS <255 caractères <65535 caractères <65535 caractères <4 M caractères <4 M caractères Les types de données (MySQL) ● ● ● Sensible casse Sensible casse si binary Insensible casse Sensible casse Insensible casse Sensible casse Les listes de valeur ENUM('valeur_possible1','valeur_possible2','valeur_possible3 ',...) – 1 à 2 octets – 65535 valeurs max SET('valeur_possible1','valeur_possible2',...) – 1 à 8 octets – 64 valeurs max Les contraintes ● UNIQUE (nomcol1,nomcol2,...) ● PRIMARY KEY (nomcol1,nomcol2,..) ● NOT NULL CONSTRAINT MA_CONTRAINTE ● CHECK (AGE BETWEEN 7 AND 77) ● Clés étrangères Modification tables ● ALTER TABLE nomtable modification – Ajouter un colonne ● – – – FOREIGN KEY nomcol REFERENCES nomtable (nomcol) [ON DELETE CASCADE] FOREIGN KEY ( FOURNUM ) REFERENCES MEMBRES( FOURNUM ) Ajouter une contrainte : ● – ALTER TABLE FOURNISSEUR ADD PAYS VARCHAR(15) ALTER TABLE FOURNISSEUR ADD UNIQUE (VILLE); Modifier la définition d'une colonne : (taille, type, propriétés) ● ALTER TABLE FOURNISSEUR MODIFY VILLE VARCHAR(20) NOT NULL; Modifications table ● Modification – Supprimer des contraintes nommées : ● ● Suppression : – ● Ce sont des tables virtuelles ● Ce sont des questions stockées ● Permet l'indépendance logique des données ● Création : DROP TABLE nomtable; ● ● DROP CONSTRAINT nomcontrainte Les vues DROP TABLE FOURNISSEUR; Renommage : – RENAME anciennom TO nouveaunom; ● RENAME FOURNISSEUR TO F; Suppression : DROP VIEW nomvue; – ● DROP VIEW FOURNISSEUR_LONDRES; En interrogation ● – Comme avec une table réelle : l'ordre SQL est réexécuté à chaque référence à la vue. En mise à jour ● ● Il existe des limitations (fonction des SGBD) – exemple : Fournisseurs de Londres CREATE VIEW FOURNISSEUR_LONDRES AS SELECT FOURNUM, FNOM,STATUT FROM FOURNISSEUR WHERE VILLE="Londres"; Les index ● ● Utilisation : – CREATE VIEW nomvue [alias1,alias2,...] AS question; ● Les vues ● – Hors norme (mais implémenté sous MySQL) un index permet d'accélérer les interrogations, mais peut pénaliser les MAJ ● Pour accélérer l'accès aux données ● Peuvent être créés sur une ou plusieurs colonnes ● un index peut être unique ou non unique ● Pour garantir l'unicité de certaines données ● structure interne : B-trees Les index ● ● Création CREATE [UNIQUE] INDEX Nom_index ON nom_table (nom_colonne,...) – CREATE UNIQUE INDEX I_VILLE ON FOURNISSEUR(VILLE); – ALTER TABLE `FOURNISSEUR` ADD INDEX ( `VILLE` ) Utilisation des index ● ● Suppression : DROP INDEX nomindex; – ALTER TABLE `FOURNISSEUR` Usages : – les colonnes servant souvent de critère de recherche – les colonnes de jointure – les colonnes identifiantes on les évite pour : – les colonnes souvent modifiées – les colonnes contenant peu de valeurs distinctes DROP INDEX `I_VILLE` Manipulation des Données ● Interrogation : – ● La clause SELECT ● SELECT Interrogation simple – SELECT liste-colonnes FROM nom_table WHERE condition Limit borne_inférieur,nombre; – Remarques : Mise à jour : – INSERT ajout – UPDATE modification – DELETE suppression ● si on veut toutes les colonnes : * ● si on ne veut pas les doubles : DISTINCT ● Liste-colonnes accepte des expressions à calculer ● si la condition est complexe, on la construit à l'aide des opérateurs AND, OR et NOT Les opérateurs Exemples SELECT ● =, !=, >=, >, <= , < ● FOURNISSEUR (FOURNUM, NOMFOUR, NOTE, VILLE) ● BETWEEN Min AND Max ● Afficher le contenu de la table FOURNISSEUR ● IS NULL (ne contient pas de valeur) ● IS NOT NULL (a une valeur, même 0) ● LIKE chaîne de caractères (avec jokers) – – ● ● % remplace n'importe quelle suite de caractères ● '-' (souligné) remplace n'importe quel caractère ● IN ( , , , ) inclusion d'une valeur dans une liste ● NOT IN ● ( , , , ) Interrogation avec tri du résultat – ● SELECT NOMFOUR, NOTE, VILLE FROM FOURNISSEUR WHERE FOURNISSEUR.NOMFOUR LIKE 'Dupon%'; Interrogations avec fonctions ● SELECT liste-colonnes FROM nomtable WHERE condition ORDER BY liste-colonnes [ASC|DSC] Trier les fournisseurs de Rennes par note décroissante – SELECT FOURNUM, NOMFOUR FROM FOURNISSEUR WHERE VILLE='Paris' OR VILLE='Marseille' ; Quels sont les fournisseurs dont le nom commence par dupon ? – Opérations de tri ● Quels sont les fournisseurs de Paris ou de Marseille? – caractères jokers : SELECT FOURNUM, NOMFOUR, NOTE, VILLE FROM FOURNISSEUR WHERE 1; SELECT NOMFOUR, NOTE FROM FOURNISSEUR WHERE VILLE='Rennes' ORDER BY NOTE DSC ● Quelques opérations numériques et statistiques – ABS(n) valeur absolue – AVG(n) valeur moyenne – COUNT(n) nombre d'occurrences – FLOOR(n) partie entière – MAX, MIN valeur max, min – SQRT(n) racine carrée – SUM Somme de colonnes Fonctions sur les date – SYSDATE date système Exemples avec des fonctions ● FOURNISSEUR (FOURNUM, NOMFOUR, NOTE, VILLE) ● Combien y a t-il de fournisseurs à Rennes ayant une note supérieure à 3 ? – ● ● Permet de réaliser le produit cartésien – SELECT COUNT( FOURNUM ) FROM FOURNISSEUR WHERE VILLE = 'Rennes' AND NOTE > '3' Quel est la note moyenne des fournisseurs malouins ? – Expression des jointures ● SELECT AVG( NOTE ) FROM FOURNISSEUR WHERE VILLE = 'Saint Malo' Quels fournisseurs fournissent le produit 34 ? – SELECT FOURNIT.FOURNUM, NOMFOUR FROM FOURNISSEUR, FOURNIT WHERE FOURNISSEUR.FOURNUM=FOURNIT.FOURNUM AND FOURNI.PRODNUM ='34' – FOURNISSEUR (FOURNUM, NOMFOUR, NOTE, VILLE) FOUNRNIT (FOURNUM, PRODNUM) PRODUIT (PRODNUM, NOMPROD) Expression des unions ● ● Mot clé UNION [ALL | DISTINCT] Quels sont les fournisseurs de Rennes et quels sont ceux ayant une note supérieure à 3 – Les sous questions ● ● SELECT NOMFOUR FROM FOURNISSEUR WHERE VILLE='Rennes' UNION ALL SELECT NOMFOUR FROM FOURNISSEUR WHERE NOTE > '3'; SELECT liste-colonnes FROM nomtableA, nomtableB WHERE nomtableA.critere_jointure = nomtableB.critere_jointure ● Permet d'imbriquer des questions SELECT liste-colonnes FROM nomtable WHERE condition SELECT liste-colonnes FROM nomtable WHERE condition Exemple : – LISTEVILLE(IDVILLE, NOMVILLE) SELECT NOMFOUR FROM FOURNISSEUR WHERE VILLE IN SELECT NOMVILLE FROM LISTEVILLE Opérations de mise à jour ● insertion de lignes INSERT : – ● ● Opérations de mise à jour ● INSERT INTO nomtable [(nomcol1[,nomcol2]...)] VALUES (val1[,val2]...); INSERT INTO `FOURNISSEUR` ( `FOURNUM` , `NOMFOUR` , `NOTE` , `VILLE` ) VALUES ('1', 'Dupont', '3', 'Saint Malo'); INSERT INTO `FOURNISSEUR` VALUES ('1', 'Dupont', '3', 'Saint Malo'); Opérations de mise à jour ● suppression de lignes DELETE : – – – DELETE FROM nomtable [WHERE condition]; DELETE FROM `FOURNISSEUR` WHERE NOTE <2; modification de lignes UPDATE : – UPDATE nomtable SET nomcol1=exp1 [,nomcol2=exp2].... [WHERE condition]; – UPDATE `FOURNISSEUR` SET `NOMFOUR` = 'Dupond', `NOTE` = '4' WHERE `FOURNUM` =1 LIMIT 1 ; Autorisations d'accès ● ● ● DELETE FROM `FOURNISSEUR` WHERE `FOURNUM`=1; ● Le créateur d'une table (ou d'une vue) en est le propriétaire Par défaut, les autres utilisateurs n'ont pas accès à ces objets Le propriétaire d'une table ou d'une vue peut autoriser d'autres utilisateurs à effectuer certaines opérations Seul le droit de suppression (DROP VIEW ou DROP TABLE) ne peut se céder Autorisations d'accès ● L'autorisation – ● GRANT liste de droits ON objet TO liste d'utilisateurs [WITH GRANT OPTION]; si droit transmissible Les droits – UPDATE : peut être suivi d'une liste de colonnes – ALL : autorise toutes les opérations (sauf DROP) Suppression d'une autorisation – ● ● REVOKE liste de droits ON objet FROM liste d'utilisateurs; Un droit ne peut être retiré que par l'utilisateur qui l'a accordé (ou par le DBA) Exemple : – REVOKE ALL PRIVILEGES ON FOURNISSEUR FROM 'web'; Exemple autorisations d'accès ● ● GRANT ALL ON FOURNISSEUR TO 'web' WITH GRANT OPTION; GRANT SELECT ,INSERT ,UPDATE, ON FOURNISSEUR TO 'web' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0; Plan du cours 1.Historique et concepts 2.Modélisation Conceptuelle des Données 3.Théorie de la normalisation 4.Passage au modèle relationnel 5.Stockage Physique 6.Modèle Relationnel 7.SQL 8.Contraintes et Intégrité 9.Transactions 10.Les bases de données pour un ASR Les contraintes d'intégrité ● Dans une SGBDR, les données sont interdépendantes. – ● Contraintes et intégrité ● ● Les contraintes d'intégrité sont statiques et définies lors de la création du schéma. Les données insérées ou modifiées doivent répondre à ces critères. Ces contraintes servent à maintenir les données de la base dans un état cohérent Les contraintes d'intégrité ● Unicité de clé : – Voitures ID_voit 0 1 2 3 4 Les contraintes d'intégrité ● on oblige à rendre uniques tous les éléments d'une colonne. Chaque élément définit de manière unique l'enregistrement associé. Marque Peugeot Renault Fiat Peugeot Ford Modèle 206 Clio Punto 307 Fiesta Année 2000 2003 1999 2004 1987 Couleur Vert Bleu Orange Jaune Rouge Il faut donc garantir leur intégrité Contrainte référentielle : – indique qu'un élément d'une colonne d'une table donnée ne peut exister que si la valeur de cet élément existe en tant que clé unique d'une autre table. – On parle aussi de clé étrangère ou foreign key. Les contraintes d'intégrité – Exemple de clé étrangère : Voitures ID_voit ID_marque Modèle 0 0 206 1 1 Clio 2 2 Punto 3 0 307 4 4 Fiesta Année 2000 2003 1999 2004 1987 Les contraintes d'intégrité ● Contrainte de domaine : – Couleur Vert Bleu Orange Jaune Rouge ● Contrainte de non-nullité – Marques ID_marque 0 1 2 3 4 Marque Peugeot Renault Fiat VW Ford Pays FR FR IT DE US Les contraintes d'intégrité ● ● Lors de la définition du schéma, il faut veiller à ne pas définir des contraintes redondantes ou conflictuelles. Le système n'est pas capable de modifier la requête pour satisfaire aux conditions d'intégrité ! – il faut tester la validité de la requête avant de la faire, au risque de la voir échouer. cette contrainte oblige les éléments d'une colonne à appartenir à un intervalle donné. Oblige les éléments d'une colonne à contenir une valeur non nulle. Les contraintes d'intégrité – Pour toute application se basant sur un SGBDR, il est indispensable qu'à tout niveau les saisies des utilisateurs soient vérifiées : ● ● Au niveau client : – Vérification des champs avec JavaScript, – Vérification des données saisies dans un client lourd... Au niveau application (PHP, ASP, JSP...) : – ● Vérification et remise en forme des données reçues. Au niveau base de données : – contraintes d'intégrité. Les contraintes en SQL ● Contraintes lors de la création d'une table : ● NOT NULL ● UNIQUE ● PRIMARY KEY ● REFERENCES ● CHECK Les contraintes en SQL ● CREATE TABLE Voitures ( ID_VOIT INT NOT NULL, ID_MARQUE INT NOT NULL REFERENCES MARQUE(ID), NB_PLACES SMALLINT DEFAULT 5, CHECK NB_PLACES <= 8, PRIMARY KEY (ID_VOIT)) Contraintes - Triggers ● ● Les triggers peuvent s'activer lors d'évènement particuliers – avant ou après un INSERT, SELECT, DELETE – avant ou après une transaction – avant ou après une procédure stockée – à une date précise les déclencheurs (SQL92) ne sont pas implantés dans toutes les SGBDR – Introduits seulement dans la version 5.0.2 de MySQL (dec 2004) Exemple : Contraintes - Triggers ● Syntaxe des déclencheurs en SQL : CREATE TRIGGER nom_trigger événement ON table WHEN (condition) instructions FOR EACH ROW | STATEMENT Contraintes - Triggers ● Plan du cours Exemple d'un déclencheur : – 1.Historique et concepts 2.Modélisation Conceptuelle des Données 3.Théorie de la normalisation 4.Passage au modèle relationnel 5.Stockage Physique 6.Modèle Relationnel 7.SQL 8.Contraintes et Intégrité 9.Transactions 10.Les bases de données pour un ASR Cet exemple va automatiquement remplir la colonne “DateMAJ” avec la date actuelle CREATE TRIGGER SetEntryDate BEFORE UPDATE ON Document (UPDATE Document SET Document.DateMAJ = NOW() ); Transactions - Définition ● ● Transactions ● Une transaction est une suite d'opérations dépendantes entre elles Les Données doivent vérifier les propriétés ACID – Atomicité – Cohérence – Isolation – Durabilité Les données doivent rester cohérentes Transactions - Définition ● Atomicité : Transactions - Définition ● – Une transaction doit effectuer toutes ses mises à jour ou ne rien faire du tout. – En cas d'échec, le système doit être capable de revenir dans l'état initial. Atomicité Erreur ! Données consistantes Transaction « Recovery Manager » Transactions - Définition ● Cohérence – – En cas d'échec, l'état cohérent initial doit être rétabli. Données consistantes TRN Données consistantes Données inconsistantes Journal de transactions Transactions - Définition ● Une transaction doit faire passer la base de données d'un état cohérent à un autre état cohérent. Abandon Isolation : – Les résultats d'une transaction ne doivent être visibles qu'après l'entière exécution de celle-ci. – L'accès concurrentiel aux données remet en cause l'isolation des donneés. Transactions - Définition ● Isolation : Transactions - Définition ● – TRN 1 Données consistantes Données inconsistantes TRN 2 TRN 1 Données consistantes Durabilité : Après l'exécution d'une transaction, le système doit garantir qu'elles seront conservées en cas de panne. Données consistantes TRN 2 Contrôle de concurrence Transactions - Définition ● Durabilité Transaction – Concurrence ● Exemple de perte de cohérence Coupure électrique Données consistantes TRN Données consistantes TR Données inconsistantes A=B T1: lire A -> a T1: a+3.14 -> a T1: 3A -> A T2: lire A -> b T2: A+1 -> A T2: b*6.55957 -> b T2: écrire b -> A T2: B+1 -> B T1: écrire a -> A « Recovery Manager » TR Journal de transactions t Perte de mise à jour T1: 3B -> B t Incohérence Transactions - SQL ● ● ● Les transactions sont simples à définir en SQL : – Chaque transaction commence par « BEGIN TRANSACTION » et se finit par « COMMIT » – Il est possible de revenir dans l'état initial avec la commande « ROLLBACK » Supporté depuis version 4.0.11 (février 2003) de MySQL avec tables InnoDB Attention cependant de ne pas générer des inter-blocages Plan du cours 1.Historique et concepts 2.Modélisation Conceptuelle des Données 3.Théorie de la normalisation 4.Passage au modèle relationnel 5.Stockage Physique 6.Modèle Relationnel 7.SQL 8.Contraintes et Intégrité 9.Transactions 10.Les bases de données pour un ASR Les bases de données pour un ASR Les bases de données pour un ASR Aspects juridiques Aspects juridiques ● Loi informatique et liberté Art. 2 ● Qu'est ce qu'une donnée à caractère personnel ? ● ● ● ● " Constitue une donnée à caractère personnel toute information relative à une personne physique identifiée ou qui peut être identifiée, directement ou indirectement, par référence à un numéro d’identification ou à un ou plusieurs éléments qui lui sont propres " – Son nom apparait – Un fichier comporte des informations permettant indirectement son identification Aspects juridiques Dans la mesure où ils sont réalisés des opérations (traitements) sur les données à caractère personnel alors Déclaration – Simplifiée (fichier fournisseurs, rémunération, ...) – Normale CNIL ● – En clair une personne est identifiable si : ex. : n° d’immatriculation, adresse IP, n° de téléphone, photographie, une empreinte digitale, l’ADN, une date de naissance, ... ● Aspects juridiques ● Cf annexe et www.cnil.fr Dispense déclaration (newsletters, blogs, organigramme) Droit d'accès de la personne pour contrôler l'exactitude des données et, au besoin, de les faire rectifier ou supprimer si un intérêt légitime le justifie. – Plus d'infos : guide informatique et libertés CNRS : http://www.dsi.cnrs.fr/cnil/guideil/Guide_IetL_CNRS.pdf Les bases de données pour un ASR Le RNIPP – Répertoire National d'Identification des Personnes Physiques (ou numéro INSEE) – Numéro identifiant mais aussi signifiant – Déclaration obligatoire et usage très strict ● Usage justifié et en aucun cas systématique ● Sanctions maximales prévues par la loi – 300 000 Euros d'amende – 5 ans d'emprisonnement MySQL Vs PgSQL Interface d'administration PhpMyAdmin MySQL Vs PgSQL ● Version Langage SQL Rapidité Transactions Clés étrangères Vues Triggers Contraintes Procédures stockées Type de tables Sécurité ● PgSQL 8 SQL92/99 Oui Oui Oui Oui Oui Oui PgSQL ++ MySQL 5 (Juillet 2004) SQL92 allégé ++ Oui (InnoDB,BDB) Oui (InnoDB) Depuis 5.0 Depuis 4.0 Depuis 5.0.2 Depuis 5.0 MyISAM, InnoDB, MEMORY, BerkeleyDB, MB Archive, NDBCluster + Mais avons-nous vraiment le choix ? Interface d'administration PhpPgAdmin Les bases de données pour un ASR Administration de MySQL Administration de MySQL ● Connexion au SGBD – ● ● Administration de MySQL ● mysql -h hote -u utilisateur -p nom_de_la_base – Enter Password : – mysql> quit pour quitter – SELECT VERSION(); ● Sélectionner un base de données : USE test; ● Créer une base de données – ● ● CREATE DATABASE cirm; Supprimer une base de données – Administration de MySQL SHOW DATABASES; +----------+ | Database | +----------+ | mysql | | test | | tmp | +----------+ Connaître la version – Bases de données d'un serveur DROP DATABASE cirm; Les bases de données pour un ASR Gestion des tables : SHOW TABLES; +----------------+ | Tables_in_cirm | +----------------+ | QUESTIONS | +----------------+ Structure d'une table : describe QUESTIONS; ● +----------------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+---------+------+-----+---------+----------------+ | NUMEROQUESTION | int(11) | NO | PRI | NULL | auto_increment | | QUESTION | text | NO | | NULL | | | TYPEQUESTION | int(4) | NO | MUL | 0 | | +----------------+---------+------+-----+---------+----------------+ Gestion des utilisateurs Gestion des utilisateurs ● Les tables systèmes ● Utilisateurs – ● ● ● Création d'un utilisateur – SELECT * FROM mysql.user; +------------------------+----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+--------------+--------------+-----------+------------+-----------------+------------+------------+ | Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | +------------------------+----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+--------------+--------------+-----------+------------+-----------------+------------+------------+ |% | root | 43789327azeraz32 | Y |Y |Y |Y |Y |Y |Y |Y |Y |Y |N |Y |Y |Y | | localhost | glpi | 5ce8185f4cazer30 | N |N |N |N |N |N |N |N |N |N |N |N |N |N | +------------------------+----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+--------------+--------------+-----------+------------+-----------------+------------+------------+ ● ● Les bases de données pour un ASR – DELETE FROM mysql.db WHERE User='monuser' AND Host='localhost' AND Db='tp1'; – FLUSH PRIVILEGES; Sauvegarde ● ● Sauvegardes SELECT * FROM mysql.db WHERE User='monuser' AND Host='localhost' AND Db='tp1' ; Suppression SELECT * FROM mysql.db; +-----------+----------+----------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+ | Host | Db | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | +-----------+----------+----------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+ |% | test | |Y |Y |Y |Y |Y |Y |N |Y |Y |Y | |% | test\_% | |Y |Y |Y |Y |Y |Y |N |Y |Y |Y | | localhost | glpi | glpi |Y |Y |Y |Y |Y |Y |N |Y |Y |Y | +-----------+----------+----------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+ GRANT les_privileges ON mabase.* TO monuser@'%' IDENTIFIED BY 'motdepasse'; Vérification : – Droits sur les bases de données – ● Gestion des utilisateurs ● La sauvegarde des bases de données est un devoir pour tout DBA. Il faut aussi être capable d'exploiter les sauvegardes le moment venu... Les méthodes de sauvegarde dépendent du SGBDR utilisé. Sauvegarde ● Attention : ne jamais sauvegarder le fichier de la base alors que le SGBD est en fonctionnement normal ! – Sauvegarde base fermée ● ● Sauvegarde base fermée ● Mode opératoire : – Arrêter le démon pilotant la base, – Après s'être assuré qu'aucun processus n'a ouvert les fichiers, les copier vers leur destination, – Démarrer le démon. Avantage : – ● Nécessite une indisponibilité totale de la base de données pendant la sauvegarde. Risques d'incohérences ● ● La plus simple des méthodes, Simplicité Inconvénients : – Indisponibilité – Risque au redémarrage. Consiste à la copie de l'ensemble des fichiers de la base de données sur un disque, une bande... Sauvegarde base fermée ● Exemple : # /etc/init.d/mysql stop # tar cf /dev/st0 /var/lib/mysql # /etc/init.d/mysql start Arrêt de MySQL Utilisation de tar (Tape ARchiving) pour écrire sur un lecteur de bande le contenu du répertoire où sont tous les fichiers de base de MySQL Redémarrage de MySQL Sauvegarde base fermée ● Pour restaurer ce type de sauvegarde : – Arrêter le daemon – Recopier les fichiers depuis le média de sauvegarde, – Redémarrer le daemon. Sauvegardes SQL ● Consiste à « dumper » l'intégralité des bases. ● Mode opératoire : – ● Avantage : – ● Sauvegardes SQL ● Exemple avec MySQL : $ mysqldump –all-databases –single-transaction > sauvegarde.sql ● Restauration avec MySQL : $ cat sauvegarde.sql | mysql -u user -p La plupart des SGBDR fournissent l'utilitaire permettant de récupérer l'ensemble des données et schémas. La sauvegarde se fait base ouverte (seuls les écritures sont interdites) Inconvénients : – Propre à chaque SGBDR – Non Optimisé
