LES BASES DE DONNEES RELATIONNELLES 1 GENERALITES 1.1 Introduction Dans beaucoup de domaines, on manipule des données ayant la même structure. Toutes ces données ayant la même structure constituent une base de données. On utilise aussi le terme BDD ou le terme anglais Data Base. Une base de données est un ensemble structuré de données gérées à l’aide d’un ordinateur. Dans une entreprise, une base de donnée concerne • Les fournisseurs • Les clients • Les commandes • Les factures… 1.2 Le stockage des données (les tables) Des données ayant la même structure sont rangées dans un même tableau appelé table. Toutes les données d’une même colonne sont du même type. Elles sont nommées champs. Chaque ligne est appelée enregistrement ou entrée. Exemple : La table ci-dessus contient : 3 entrées (3 lignes de données). 7 champs (id, Nom, Prénom, Sexe, Adresse, Ville, Code postal). 1.3 Les principaux types de données de MySQL Pour plus d’informations: http://dev.mysql.com/doc/refman/5.0/fr/ 1.3.1 • • • • • • • Les types numériques TINYINT, BIT, BOOL, BOOLEAN : 1octet SMALLINT : 2 octets MEDIUMIN : 3 octets INT, INTEGER : 4 octets BIGINT : 64 octets FLOAT : Un petit nombre à virgule flottante, en précision simple. DOUBLE : Un nombre à virgule flottante, en précision double. • DECIMAL : Un nombre à virgule flottante littéral. Il se comporte comme une colonne de type CHAR. Cela signifie que le nombre est stocké sous forme de chaîne : chaque caractère représente un chiffre. 1.3.2 • Les types de données temporels : dates et heures DATE : MySQL affiche les valeurs de type DATE au format 'YYYY-MM-DD', mais vous permet d'assigner des valeurs DATE en utilisant plusieurs formats de chaînes et nombres. DATETIME : Une combinaison de date et heure. MySQL affiche les valeurs de type DATE au format 'YYYY-MM-DD HH:MM:SS', mais vous permet d'assigner des valeurs DATE en utilisant plusieurs formats de chaînes et nombres. TIMESTAMP : Un timestamp. L'intervalle de validité va de '1970-01-01 00:00:00' à quelque part durant l'année 2037. TIME : MySQL affiche les valeurs TIME au format 'HH:MM:SS', mais vous permet d'assigner des valeurs TIME en utilisant des nombres ou des chaînes. YEAR [(2|4)] : Une année, au format 2 ou 4 chiffres (par défaut, c'est 4 chiffres). • • • • 1.3.3 Les types de chaînes Les types CHAR et VARCHAR sont similaires, mais diffèrent dans la manière dont ils sont stockés et récupérés : • CHAR : La longueur d'une colonne est fixée à la longueur que vous avez défini lors de la création de la table. La longueur peut être n'importe quelle valeur entre 1 et 255. Quand une valeur CHAR est enregistrée, elle est complété à droite avec des espaces jusqu'à atteindre la valeur fixée. Quand une valeur de CHAR est lue, les espaces en trop sont retirés. • VARCHAR : Les valeurs contenues dans les colonnes sont de tailles variables. Vous pouvez déclarer une colonne VARCHAR pour que sa taille soit comprise entre 1 et 255, exactement comme pour les colonnes CHAR. Par contre, contrairement à CHAR, les valeurs de VARCHAR sont stockées en utilisant autant de caractères que nécessaire, plus un octet pour mémoriser la longueur. Les valeurs ne sont pas complétées. Au contraire, les espaces finaux sont supprimés avant stockage. Si vous assignez une chaîne de caractères qui dépasse la capacité de la colonne CHAR ou VARCHAR, celle ci est tronquée jusqu'à la taille maximale du champ. 1.4 Les index Les bases de données sont souvent très volumineuses. Ainsi, une recherche d’information dans une table peut prendre beaucoup de temps. L’index est l’outil qui permet de résoudre ce problème. L’index présente des avantages : • Il accélère les recherches d’informations. • Il est de taille très inférieure à celle de la table. • Il peut servir à empêcher l’opérateur de créer des enregistrements dupliqués. Mais il ne possède pas que des avantages, à chaque saisie, il faut le mettre à jour. Les index jouent un rôle discret mais important dans la gestion des bases de données. L’index rend les tris et les recherches d’informations plus rapides. La décision de créer un index résulte de l’examen des avantages et inconvénients de cette opération. Malgré tout, on utilisera presque systématiquement un index. 2 LE MECANISME RELATIONNEL 2.1 Les données redondantes Le gros problème dans la gestion des données est la saisie d’informations redondantes. A une information longue, on attribue volontiers une référence ou un code. Cela permet d’éviter des saisies répétitives et fastidieuses. Les données maniées dans une entreprise sont structurées 2.2 Etude d’un exemple 2.2.1 Notion de relation Chez un vendeur de matériaux, pour chaque produit, on enregistre : code, nom, prix unitaire, code du fournisseur, état du stock, état minimal admissible (pour ne pas être en rupture de stock), quantité minimale par commande… Ces données sont rangées dans des tables. Le gestionnaire du stock sait que le code du fournisseur relie la fiche "produit" à la table "fournisseur". Il faut donc construire une base de données relationnelle, c’est à dire capable de gérer les relations. Le schéma ci-dessus permet de constater que les informations relatives à un fournisseur ne sont saisies qu’une fois, même si ce dernier fournit plusieurs produits. C’est l’usage des codes qui permet ceci. Ici, on considère que chaque produit ne peut provenir que d’un seul fournisseur. En informatique, on parle de "relation un à plusieurs" ou relation "1-n". 2.2.2 La jonction Considérons les bons de livraison. Pour chaque commande exécutée, il faut noter le numéro de la commande, la date, le nom (ou le code) de l’entreprise, puis la liste des produits (ou leurs codes) ainsi que les quantités livrées. Un problème va se poser. Combien doit-on prévoir de colonnes pour le code du produit et la quantité correspondante ? Bons_livraisons • La prévision de beaucoup de colonnes pour le code produit et la quantité va impliquer un gaspillage de la mémoire. Inversement, si on en prévoit trop peu, il faudra éditer plusieurs bons pour une même livraison. • Une nouvelle table appelée table de jonction va solutionner le problème. Cette table va comporter 3 colonnes : • Un code assurant le lien avec la table "Bons_livraisons" • Codes des produits • Quantités livrées Un même bon de livraison peut mentionner plusieurs produits. Un même produit peut apparaître dans de nombreux bons de livraison. La création d’une table de jonction puisant ses codes dans deux autres tables "Bons_livraisons" et "Produits" permet de résoudre notre problème. On parle alors de relation "n-n" qui peut toujours être scindée en deux relations "1-n" par création d’une table supplémentaire appelée "table de jonction". 2.3 Règles générales sur le schéma relationnel de la base 2.3.1 Introduction La gestion de données courantes implique la création de plusieurs tables, reliées entre elles via des codes. Pour que le système fonctionne, il faut que les relations entre tables soient du type "1-n". Si on rencontre une relation de type "n-n", on peut toujours la scinder en deux relations de type "1-n" par création d’une table de jonction supplémentaire. 2.3.2 Les relations "1-1" Le nom d’une personne et son prénom sont liés de façon univoque. Nous dirons qu’ils sont liés par une relation « un à un » ou "1-1". On les placera dans la même table. REGLE 1: D’une manière générale, on placera dans la même table les données qui sont en relation "1-1" entre elles. 2.3.3 Les relations "1-n" Exemple : C’est le type de relation entre une personne et une entreprise. •Un personne est généralement employée par une entreprise. •Une entreprise emploie généralement plusieurs personnes. C’est une relation « un à plusieurs » ou "1-n" entre la personne et l’entreprise. Si on place le nom de l’entreprise dans la même table que le nom de la personne, il y aura obligatoirement redondance. Il faut donc placer les personnes et les entreprises dans des tables distinctes. REGLE 2: Une relation "1-n" implique la création d’une nouvelle table. REGLE 3: D’une façon générale, on recensera toutes les relations "1-n" existant entre les données. 2.3.4 Les relations "n-n" Exemple : Une personne par rapport à des organismes : •Une personne peut être affiliée par plusieurs organismes. •Un organisme gère plusieurs personnes. On se retrouve face à une relation qui semble être "1-n" dans les deux sens, ce qui signifie qu’il s’agit d’une relation « plusieurs à plusieurs » ou "n-n". REGLE 4: Pour gérer une telle relation, il faut introduire une table de jonction avec au minimum deux champs code afin d’obtenir deux relations de type "1-n". 2.3.5 Conclusion Dans le processus de création d’une base de données, l’établissement du schéma relationnel de la base de données représente l’étape fondamentale. Il est inutile d’aller plus loin (donc de commencer à coder) tant que cette étape n’est pas parfaitement maîtrisée. 3 EXEMPLE D’ELABORATION D’UNE BASE DE DONNEES 3.1 Mise en situation On souhaite gérer une base de données d’un lycée. On se limite à une section de BTS. On dispose des informations suivantes : 3.1.1 Liste des étudiants de la section On se limitera à 4 informations N° étudiant 1 2 … 3.1.2 Liste des étudiants Nom de l’étudiant Date de naissance Marcos Jean Paul 18/03/1990 Zeblouze Agathe 02/02/1991 … … Sexe H F … Liste des enseignants Pour simplifier, on considère qu’une matière n’est enseignée que par un seul enseignant. De même, on considère qu’un professeur n’enseigne qu’une seule matière. Liste des enseignants N° Enseignant Nom Grade Ancienneté Matière 10 Von Neumann John CER 10 Informatique 11 Descartes René AGR 3 Mathématiques … … … … … 3.1.3 Relevé de notes Il est établi pour chaque étudiant à partir des moyennes de chaque matière. Relevé de notes N° Etudiant : Zeblouze Agathe N° Matière Nom matière Coefficient 1 Mathématiques 3 2 Informatique 7 3 Economie 2 4 Français 1 5 Physique 2 Moyenne générale: 3.2 Note / 20 10 12 9 13 7 10.6 Analyse des données On doit extraire les informations élémentaires, c’est à dire non décomposables. Ces informations seront celles qui vont constituer la future base de données. On va affecter à chaque information élémentaire : • Un mnémonique, c’est à dire un label unique qui pourra être utilisé dans un contexte informatique (nom sans espace, sans caractères spéciaux…) • Un type de données: Entier, réel, texte… Eventuellement • Des contraintes • Une règle de calcul Mnémonique NumEtu NomEtu DateNaiss CdSexe Numéro d’étudiant Nom d’étudiant Date de naissance Code sexe LibSexe Libellé sexe NumEns NomEns Numéro d’enseignant Nom de l’enseignant Grade Grade de l’enseignant Ancien NumMat Coef Note Ancienneté dans le grade Nom de la matière enseignée Numéro de la matière Coefficient de la matière Note obtenue à la matière Moy Moyenne au diplôme NomMat 3.3 Libellé Type Contraintes Règle de calcul Entier >0 Chaîne (30) Date Chaîne (1) H ou F Homme ou Chaîne (7) Femme Entier >0 Chaîne (30) AGR, CER ou Chaîne (3) VAC Entier >= 0 Chaîne (15) Entier Entier Réel >0 > 0 et < 8 >= 0 et <= 20 Réel >= 0 et <= 20 SUM(Note*Coef)/ SUM(Coef) Structuration des données On doit maintenant structurer en paquets homogènes (en tables) ces champs de telle sorte que la redondance (la répétition d’information) soit minimale. Les attributs qui sont calculés (qui ont une règle de calcul à partir des autres données) ne doivent pas être pris en compte dans la phase de structuration des données. Puisqu’ils sont obtenus à partir d’autres champs, les inclure dans la phase de structuration introduirait une forme de redondance et donc des possibilités d’incohérence. Pour notre exemple, c’est le cas de l’attribut « moyenne ». Un ensemble de champs est homogène si tous les champs qui le compose ont un lien « direct », c’est à dire une relation de type 1-1. On parle de dépendance fonctionnelle. Exemple de DF (dépendance fonctionnelle) NumEtu → NomEtu, DateNaiss est juste En revanche, l’expression NumEtu → Note est fausse 3.4 Construction du schéma des données 3.4.1 Dépendances fonctionnelles simples NumEtu → NomEtu, DateNaiss, CdSexe, LibSexe CdSexe → LibSexe NumEns →NomEns, Grade, Ancien NumMat→NomMat, Coef, NumEns, NomEns, Grade, Ancien 3.4.2 Attributs non classés et dépendances fonctionnelles composées L’attribut « Note » n’a pas été classé dans l’étape précédente. Il intervient donc dans une dépendance fonctionnelle composée. Cette dépendance fonctionnelle est la suivante : NumEtu, NumMat → Note 3.4.3 Elimination des transitivités de l’ensemble des dépendances fonctionnelles NumEtu → NomEtu, DateNaiss, CdSexe CdSexe → LibSexe NumEns →NomEns, Grade, Ancien NumMat→NomMat, Coef, NumEns 3.4.4 Construction des tables On va donner un nom à chaque table et on sélectionnera une clé primaire (index). ETUDIANT : NumEtu, NomEtu, DateNaiss, CdSexe SEXE : CdSexe, LibSexe ENSEIGNANT : NumEns, NomEns, Grade, Ancien MATIERE : NumMat, NomMat, Coef, NumEns 3.4.5 Construction des associations : NOTES : NumEtu, NumMat, Note 3.4.6 Construction du schéma des données La dernière étape consiste à mettre en relation les tables et associations trouvées précédemment afin de construire la structure générale des données : Le schéma des données. Mise en relation des entités : Les tables sont mises en relation par l’intermédiaire des attributs qu’elles possèdent en commun. Rappelons que les deux types de relations autorisées pour relier deux tables (association) sont : Relation de type "1-1" : A un élément de l’ensemble de départ, on ne peut faire correspondre qu’un seul élément de l’ensemble d’arrivée et réciproquement. Relation de type "1-n" : A un élément de l’ensemble de départ, on peut faire correspondre plusieurs éléments de l’ensemble d’arrivée. Pour notre exemple, les relations sont : Table source Tables but SEXE ETUDIANT MATIERE ENSEIGNANT ETUDIANT NOTES NOTES MATIERE Relation sur l’attribut CdSexe NumEtu NumMat NumEns Type de relation 1-n 1-n 1-n 1-n Représentation graphique du schéma des données Pour avoir une vision synthétique de la structure de la base de données (le schéma des données), on utilise une représentation graphique du type suivant : Cette représentation est le schéma de la base de données « NOTATION ELEVES » C’est la structure de représentation optimale des données du problème : • Cohérence maximale • Redondance minimale ANNEXE Différences entre tableur et bases de données Tableur Utilisation principale Structuration des données Contrôle d’intégrité des données Accès aux données Confidentialité des données Taille des données Traitement des données Interrogation des données Calculs Aucune Aucuns Mono-utilisateur Aucun contrôle Base de données Gestion et traitement des données Structuration et cohérence forte Vérification stricte des valeurs possibles de chaque donnée Multi-utilisateurs Vérification des droits d’accès de chaque utilisateur Plusieurs tables Qualitatifs et quantitatifs Plusieurs feuilles Quantitatifs Réalisée par des fonctions Langage « universel » : SQL ou des macros spécifiques