LES BASES DE DONNEES RELATIONNELLES

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