ESA - Namur Structure des bases de données Y. Mine

ESA - Namur
Structure des bases de données
1 Bac en Comptabilité
2010 – 2011
Y. Mine
Table des matières
Chapitre 1 Les bases de données relationnelles
1.1
1.2
1.3
1.4
1.5
1.6
1.7
1.8
1.9
1.10
Introduction
Pourquoi une base de données
Différentes formes
Le stockage des données (les tables)
L’algèbre relationnel
Le SGBD
Le matériel
Les différents modèles de bases de données
Les identificateurs
Notions de clé primaire
Chapitre 2 Le modèle entité/associations
2.1
2.2
2.3
2.4
Chapitre 3
Les entités et leurs attributs
Les associations
Les cardinalités
Traduction en mode relationnel
Les attributs
3.1 Les types d’attributs
3.2 Les entiers
3.3 Les flottants
3.4 Les chaines
3.5 Dates et heures
Chapitre 4
Les index
4.1 Avantages
4.2 Inconvénients
4.3 Index sur plusieurs champs
Exercices
Chapitre 1 : Les bases de données relationnelles
1.1 Introduction
Une base de données (bd) est un système d'organisation de l'information, conçu pour une
localisation et une mise à jour rapide et facile des données. Une bd organise l'information
qu'elle contient en tables, en champs (les colonnes) et en enregistrements (les lignes).
Chaque enregistrement correspond à un item stocké dans la base de données.
Réduire le plus possible la saisie d'informations redondantes est l'un des gros problèmes
auquel se sont heurtés les gestionnaires de données.
En pratique, on manipule souvent des données ayant la même structure (ex. : liste des
membres du personnel : pour chaque personne, on enregistre le nom, le prénom, le sexe, la
date de naissance, l'adresse, la fonction dans l'entreprise, etc.) Toutes ces données ont la
même structure et peuvent être facilement gérées par des moyens informatiques. Dans le
même ordre d'idée, l'encodage d'un achat doit automatiquement afficher dans le masque de
saisie toutes les coordonnées du fournisseur dès que l'on a encodé son nom ou son
identifiant unique.
une base de données est un ensemble structuré de données,
géré à l'aide de l'informatique
•
Une collection de données qui supporte les définitions de
– données de la base
• Structure intégrée
• Lien sémantiques
• Contraintes d’intégrité
• Contraintes de sécurité
– vues de la base
•
Une collection de données qui supporte les manipulations de
– recherche de données
• Interactive
• Logiquement et physiquement performante
– insertion, Mise à jour, Suppression de données
• Cohérente
• Partagée
• Fiable
En outre, il y a aussi les notions de :
exhaustivité : la base contient toutes les informations requises
unicité : la même information n'est présente qu'une seule fois
Exemples d'utilisation :
les fournisseurs
les clients
les contacts
les commandes
les factures …
1.2 Pourquoi une base de données ?
•
•
•
•
•
•
Intégration de données
– Moins de duplications
Partage de données
Fiabilité de données
– Transactions, reprises sur pannes, tolérance de pannes
Sécurité de données
Langages de requêtes
– SQL, QBE
Interfaces conviviales
– Web
1.3 Différentes formes :
•
•
•
BDs personnelles
– MsAccess etc.
– 10 KO – 100 KO
BDs professionnelles typiques
– 100 KO – 100 GO
BDs professionnelles très grandes
– Very Large Databases (VLDB) > 100 GO
1.4 Le stockage des données (les tables)
Dans une base de données, le stockage se fait de manière organisée. On distingue plusieurs
éléments constitutifs.
Il y a tout d'abord des tables. Elles portent un nom qui généralement correspondra aux
informations contenues. Pour un carnet de contacts, on aura une table appelée Contacts qui
contiendra toutes les personnes que l'on connaît.
Une table contient des enregistrements. En voyant la table comme un tableau, un
enregistrement correspond à une ligne. On pourra dire dans notre exemple, que les
différentes personnes constituent les enregistrements de la table Contacts.
Un enregistrement est fait de plusieurs champs. Ces derniers correspondent aux
informations contenues. Ce sont les enregistrements qui contiennent effectivement des
données. Ils correspondront aux colonnes du tableau. Un champ est de plus défini par un
type selon les données qui pourront y être enregistrées. Celui-ci indiquera par exemple que
le contenu du champ pourra être de un ou plusieurs caractères, un ou plusieurs chiffres, une
date, ...
Des données ayant une même structure peuvent être rangées dans un même tableau. Dans
le cas ci-dessous, la première colonne (appelée aussi attribut) contiendra les noms, la
seconde les prénoms, la troisième le sexe, la quatrième la date de naissance, etc. La
caractéristique d'un tel tableau est que toutes les données d'une même colonne sont du
même type. Dans une base de données, un tel tableau s'appelle une table.
Cette table, nommée "personnes" aura pour attributs (colonnes) : nom, prénom, sexe,
adresse, ville et code postal. Les lignes que contiendra cette table seront appelées
enregistrements ou tuples.
Nom
Prénom
Sexe Adresse
Durand Pierre
Chose
M
Stéphanie F
Trombe Jean
M
Ville
Code postal
16, rue de Fer Namur
5000
3, Bld d'Avroy Liège
4000
3, rue Neuve
Bruxelles 1000
…
Dans une table, les termes ligne et enregistrement sont synonymes. Il en est de même
pour les termes colonnes et champs. (row) et (column).
1.5 L'algèbre relationnel
L’algèbre relationnel regroupe toutes les opérations possibles sur les relations.
Projection : on ne sélectionne qu’un ou plusieurs attributs d’une relation (on ignore les
autres). Par exemple n’afficher que les colonnes nom et prénom de la table Personnes.
Personnes
nom
prénom
adresse
téléphone
Martin
Pierre
7 allée des vers 0258941236
Dupond
Jean
32 allé Poivrot
0526389152
Dupond
Marc
8 rue de l’octet
0123456789
SELECT nom, prénom
FROM Personnes
nom
prénom
Martin
Pierre
Dupond
Jean
Dupond
Marc
Jointure : on fabrique une nouvelle relation à partir de 2 ou plusieurs autres en prenant
comme pivot 1 ou plusieurs attributs. Par exemple, on concatène la table du carnet
d’adresse et celle des inscrits à la bibliothèque en fonction du nom de famille (c’est
typiquement du recoupement de fichiers).
Bibliothèque
Personnes
nom
prénom
adresse
nom
Dernierlivre
Martin
Pierre
7 allée des vers 0258941236
Dupond
Robinson
Dupond
Jean
32 allé Poivrot
Jospin
Faust
Martin
Misère
téléphone
0526389152
On joint les deux tables, grâce à
la colonne nom.
SELECT Personnes.prénom, dernierlivre
FROM Personnes, Bibliothèque
WHERE Personnes.nom = Bibliothèque.nom
prénom
Dernierlivre
Jean
Robinson
Pierre
Misère
Et on combine cette jointure à
une projection sur les attributs
nom et dernierlivre.
Attention à lever toute ambiguïté sur les noms d’attribut
dans le cas où deux tables
possèdent des colonnes de
même nom.
Sélection : on sélectionne tous les tuples ou bien seulement une partie en fonction de
critères de sélection qui portent sur les valeurs des attributs. Par exemple n’afficher que les
lignes de la table Personnes qui vérifient la condition suivante : le nom est ‘Dupond’.
Personnes
nom
prénom
adresse
téléphone
Martin
Pierre
7 allée des vers 0258941236
Dupond
Jean
32 allé Poivrot
0526389152
Dupond
Marc
8 rue de l’octet
0123456789
SELECT *
FROM Personnes
WHERE nom = ‘’Dupond’’
On ne sélectionne que les
tuples dont l’attribut nom
est égale à ‘Dupond’.
nom
prénom
adresse
téléphone
Dupond
Jean
32 allé Poivrot
0526389152
Dupond
Marc
8 rue de l’octet
0123456789
Cette algèbre est facilement possible avec les commandes de MySQL (SELECT… FROM…
WHERE…).
1.6 Le logiciel (SGBD)
•
Système de Gestion de Base de Données (SGBD)
– Système logiciel gérant une BD
• Peut avoir des composants matériels
–
–
–
Mono ou multiposte
En général, peut gérer plusieurs BDs
Peut aussi accéder aux BDs d’autres SGBDs
Le logiciel qui gère une base de données s'appelle un système de gestion de base de
données. (SGBD - DBMS pour Data Base Management System).
Tous les SGBD présentent à peu près les mêmes fonctionnalités. Ils se distinguent par leur
coût, par le volume de données qu'ils sont capables de gérer, par le nombre d'utilisateurs qui
peuvent interroger la base simultanément, par la facilité avec laquelle ils s'interfacent avec
d'autres logiciels, etc.
Une BDD peut servir à une seule personne, ou être à la disposition de dizaines de milliers
d'agents (systèmes de réservation des billets d'avion).
Un SGBD est principalement constitué d'un moteur et d'une interface graphique. Le
moteur assure les fonctions essentielles : saisir les données, les stocker, les manipuler, etc.
L'interface graphique permet à l'utilisateur de communiquer avec le logiciel.
Pour dialoguer avec les SGBD qui ne sont pas équipés d'une interface graphique, il faut
utiliser le langage SQL (Structured Query Language), et introduire les instructions à l'aide
d'un éditeur de lignes.
Langage normalisé de manipulation des bases de données, SQL est utilisable avec
pratiquement tous les SGBD du marché. Cependant, chaque éditeur ayant développé son
propre "dialecte" il faut pouvoir disposer d'un "dictionnaire" pour transporter une BDD d'un
SGBD à l'autre. Ce "dictionnaire" a été développé par Microsoft sous le nom ODBC (Open
Data Base Connectivity).
1.7 Le matériel (serveur de BDD)
Dépend du volume des données stockées dans la base et du nombre maximum d'utilisateurs
simultanés.
Lorsque le nombre d'enregistrements par table n'excède pas le million, et que le nombre
d'utilisateurs varie de une à quelques personnes, un pc actuel de bonnes performances, un
logiciel système pour poste de travail, et un SGBD "bureautique" suffisent. Exemple :
Microsoft Access 2007, installé sur un PC récent, doté de 2 Go de mémoire vive et
fonctionnant sous Windows XP ou Seven.
Si ces chiffres sont dépassés, il faut utiliser un serveur de BDD, dont les accès aux disques
sont nettement plus rapides. Le système client doit être remplacé par un système serveur
(multi-utilisateurs), et le SGBD bureautique par un SGBD prévu pour les grosses BDD multiclients.
1.8 Les différents modèles de bases de données
Les bases de données du modèle "relationnel"* sont les plus répandues, car elles
conviennent bien à la majorité des besoins des entreprises. Le SGBD qui gère une BDD
relationnelle est appelé "SGBD relationnel" (SGBDR).
* Base de données qui contient des tables et autres sujets, et dont l'information est
organisée par différentes relations entre tables.
D'autres modèles de bases de données ont été proposés : hiérarchique, en réseau, orienté
objet, relationnel objet. Aucun d'entre eux n'a pu détrôner le modèle relationnel (sauf le
relationnel objet - Oracle).
Malgré sa généralité, le modèle relationnel ne convient pas à toutes les BDD rencontrées en
pratique. Il existe donc des SGBD spécialisés. Les deux exemples les plus connus
concernent la gestion des BDD bibliographiques (ou documentaires), et celle des BDD
géographiques gérées à l'aide d'un SIG (Système d'Information Géographique).
1.9 Les identificateurs
Les noms des bases, relations, attributs, index et alias sont constitués de caractères
alphanumériques et des caractères _ et $.
Un nom comporte au maximum 64 caractères
Comme les bases de données et les relations sont codées directement dans le système de
fichiers, la sensibilité à la casse de MySQL dépend de celle du système d’exploitation sur
lequel il repose. Sous Windows, la casse n’a pas d’importance ; alors que sous Unix, elle en
a.
Le point . est un caractère réservé utilisé comme séparateur entre le nom d’une base et celui
d’une relation, entre le nom d’une relation et celui d’un attribut.
Exemple :
SELECT base1.table25.attribut5
FROM base1.table25
1.10 Notions de clé primaire
L’option PRIMARY KEY qui regroupe les contraintes NOT NULL (ne peut pas être vide) et
UNIQUE (pas de doublon) va permettre d'identifier tous les enregistrements d'une table.
nom
prénom
Dupond
Marc
Dupont
Pierre
Martin
Marc
Martin
Pierre
Martin
Marc
enregistrement interdit car le
couple (‘Martin’, ‘Marc’) est un
doublon du couple (nom,‘prénom’)
Chapitre 2 : Le modèle entité / associations
Le modèle Entité – Association – Cardinalité
Ce modèle compte 4 concepts principaux : entité, attribut, association et cardinalité.
Entité = classe générique d'individus ou d'objets ayant les mêmes caractéristiques.
Ex: les entités "clients", "livres" ou "fournisseurs", dans une base de données de
librairie.
Association = classe générique de liens reconnus ou possibles entre individus ou
objets. Ex: l'association "achète" lie les clients et les livres d'une librairie.
Attribut = propriété distinctive d'une entité ou d'une association. Ex: le nom d'un client
est un attribut de l'entité "clients".
Cardinalité = caractérisation de l'implication de l'association (p.ex. l'expression (1,N),
où 1 représente le minimum et N le maximum d'occurrences dans l'association), qui
permet de dénombrer les éléments de l'entité d'arrivée en relation avec un élément
de l'entité de départ
Exemple d'entité : une base de livres qui sont caractérisés par un titre, un auteur principal,
un ISBN, un éditeur, une année de parution et un nombre de pages. Ces différents éléments,
(qui sont les attributs de l'entité) sont communs à tous les livres. Pour chaque livre, leur
contenu est unique et ne changera pas.
2.1 Les entités et leurs attributs
Les entités seront représentées de la manière suivante :
Voici un système composé de 3 entités, chacune possède ses propres attributs
2.2 Les associations
Ces entités sont liées par des associations à qui on peut donner un nom, afin de clarifier
leur rôle : acheter, livrer, … et qui reflètent les interactions présentes entre ces entités.
2.3 Les cardinalités
Une cardinalité, dans une association, exprime le nombre de participations possibles d'une
occurrence de chaque entité à l'association. Ce nombre étant variable, on note la cardinalité
minimum et la cardinalité maximum. Comme il y a deux entités (au moins) associées, la
cardinalité est précisée pour chaque entité.
Il existe différents types de cardinalités:
modèle "1 - 1":
à une occurrence de l'entité E1 peut correspondre au maximum une occurrence de
l'entité E2.
Ex: l'association "vente" liant les entités "livres" et "fournisseurs" : un livre ne peut
être vendu que par un seul fournisseur à la fois.
modèle "1 - n":
à une occurrence de l'entité E1 peuvent correspondre de une à plusieurs occurrences
de l'entité E2,
Ex: l'association "posséder" liant les entités "propriétaires" et "voitures" : un
propriétaires peut posséder une ou plusieurs voiture.
modèle "n - n":
à plusieurs occurrences de l'entité E1 peuvent correspondre plusieurs occurrences
de l'entité E2.
Ex: l'association "achète" liant les entités "clients" et "produits" d'un supermarché
modèles "0 - 1", "0 - N"
Exercice 01 : établir des modèles Entité – Association – Cardinalité
2.4 Traduction en mode relationnel
Après avoir établi le modèle entité/association, il faut traduire le schéma en mode relationnel,
c'est-à-dire dans un format réellement exploitable par la base de données qui va être créée.
Cette traduction peut s'opérer selon 2 règles différentes, en fonction de la cardinalité
présente sur l'association.
Chaque association va être examinée afin de voir si on y trouve des cardinalités (1-1) ou (01)
1ère règle : il n'y a PAS de cardinalité (1-1) ou (0-1) :
L'association disparaît
On crée une relation (une table) "achats"
On crée une contrainte d'intégrité référentielle * entre clé primaire et secondaire
* contraintes d'intégrité référentielle
Une contrainte d'intégrité est une clause permettant de contraindre la
modification de tables, faite par l'intermédiaire de requêtes d'utilisateurs, afin
que les données saisies dans la base soient conformes aux données attendues.
Ces contraintes doivent être exprimées dès la création de la table grâce aux
mots clés suivants:
*
*
*
*
*
CONSTRAINT
DEFAULT
NOT NULL
UNIQUE
CHECK
DEFAULT Le langage SQL permet de définir une valeur par défaut lorsqu'un champ de la
base n'est pas renseigné grâce à la clause DEFAULT. Cela permet notamment de faciliter
la création de tables, ainsi que de garantir qu'un champ ne sera pas vide.
La clause DEFAULT doit être suivie par la valeur à affecter. Cette valeur peut être un des
types suivants:
*
*
*
*
*
*
*
constante
constante
le mot clé
le mot clé
le mot clé
le mot clé
le mot clé
numérique
alphanumérique (chaîne de caractères)
USER (nom de l'utilisateur)
NULL
CURRENT_DATE (date de saisie)
CURRENT_TIME (heure de saisie)
CURRENT_TIMESTAMP (date et heure de saisie)
NOT NULL vérifie que le contenu du champ n'est pas nul
Unique Une table peut posséder une ou plusieurs contraintes d'unicité, chacune étant
composée d'une ou plusieurs colonnes de la table.
La clause UNIQUE permet de vérifier que la valeur saisie pour un champ n'existe pas déjà
dans la table. Cela permet de garantir que toutes les valeurs d'une colonne d'une table
seront différentes.
CHECK définit une condition que chaque rang doit satisfaire
Les index sont utilisés pour trouver des lignes de résultat avec une valeur spécifique,
très rapidement. Sans index, MySQL doit lire successivement toutes les lignes, et à
chaque fois, faire les comparaisons nécessaires pour extraire un résultat pertinent. Plus
la table est grosse, plus c'est long. Si la table dispose d'un index pour les colonnes
utilisées, MySQL peut alors trouver rapidement les positions des lignes dans le fichier de
données, sans avoir à fouiller toute la table. Si une table à 1000 lignes, l'opération sera
alors 100 fois plus rapide qu'une lecture séquentielle. Notez que si vous devez lire la
presque totalité des 1000 lignes, la lecture séquentielle se révélera alors plus rapide,
malgré tout.
Les index de MySQL sopnt PRIMARY , UNIQUE et INDEX.
En MySQL, il est possible de définir des clés, c'est-à-dire spécifier la (ou les) colonne(s)
dont la connaissance permet de désigner précisément un et un seul tuple (ligne).
Primary Key (clé primaire). un ou plusieurs champs qui définissent de manière unique
un enregistrement.. Elle peut être composée d'une ou plusieurs colonnes (champs) de la
table. Toutes les lignes (enregistrements) de la table doivent satisfaire aux conditions
suivantes :
Toutes
les
colonnes
des
clés
primaires
doivent
ne
pas
Toutes les lignes doivent posséder une valeur de clé primaire différente.
Une et une seule clé primaire par table
être
nulles.
Foreign key (clé étrangère ou secondaire) Les systèmes de base de données utilisés
sont le plus souvent de type relationnel. Par ce terme est exprimé le fait que des
relations entre les tables vont exister. On aura alors la présence de clés étrangères.
Une clé étrangère est une clé primaire pour une table qui est stockée dans une autre
table afin d'établir un lien entre les deux. Une table dépendante peut disposer d'une ou
plusieurs clés étrangères, chacune étant constituée d'une ou plusieurs colonnes de la
table. Chaque clé étrangère fait référence à une (ou plusieurs) colonne(s)
correspondante(s) d'une clé primaire dans la table parent. Pour chaque clé étrangère,
toutes les lignes de la table dépendante doivent posséder une valeur correspondante
dans la table parent ou avoir une ou plusieurs colonnes de la clé étrangère nulles.
N'est supporté qu'à partir de MySQL 5.0
Trigger (gâchette): garantie de l'intégrité référentielle
Les clés étrangères permettent de définir les colonnes d'une table garantissant la validité
d'une autre table. Ainsi, il existe des éléments (appelés triggers, ou en français gâchettes
ou déclencheurs) permettant de garantir l'ensemble de ces contraintes que l'on désigne
par le terme d'intégrité référentielle, c'est-à-dire notamment de s'assurer qu'un tuple
utilisé à partir d'une autre table existe réellement.
Ces triggers sont ON DELETE et ON UPDATE:
* ON DELETE est suivi d'arguments entre accolades permettant de spécifier l'action à
réaliser en cas d'effacement d'une ligne de la table faisant partie de la clé étrangère:
o CASCADE indique la suppression en cascade des lignes de la table étrangère
dont les clés étrangères correspondent aux clés primaires des lignes effacées
o RESTRICT indique une erreur en cas d'effacement d'une valeur correspondant à
la clé
o SET NULL place la valeur NULL dans la ligne de la table étrangère en cas
d'effacement d'une valeur correspondant à la clé
o SET DEFAULT place la valeur par défaut (qui suit ce paramètre) dans la ligne de
la table étrangère en cas d'effacement d'une valeur correspondant à la clé
* ON UPDATE est suivi d'arguments entre accolades permettant de spécifier l'action à
réaliser en cas de modification d'une ligne de la table faisant partie de la clé étrangère:
o CASCADE indique la modification en cascade des lignes de la table étrangères
dont les clé primaires correspondent aux clés étrangères des lignes modifiées
o RESTRICT indique une erreur en cas de modification d'une valeur correspondant
à la clé
o SET NULL place la valeur NULL dans la ligne de la table étrangère en cas de
modification d'une valeur correspondant à la clé
o SET DEFAULT place la valeur par défaut (qui suit ce paramètre) dans la ligne de
la table étrangère en cas de modification d'une valeur correspondant à la clé
2ème règle : il y a une ou des cardinalités (1-1) ou (0-1) :
L'association disparaît
On crée une contrainte d'intégrité référentielle entre clé primaire et secondaire
One ne crée PAS de relation (table) supplémentaire
Dans notre exemple :
La relation clients-livres appartenant à la 1ère règle :
on crée une clé primaire dans la table "Clients" via un n° unique nommé IDclient
on crée une nouvelle table nommée "Achats"
on crée une clé primaire dans la table "Achats" via un attribut à n° unique nommé
N°Achat
on crée une clé primaire dans la table "Livres" sur l'attribut ISBN car il est unique
on crée une clé secondaire IDclient dans la table "Achats" liée à la clé primaire
IDclient de la table "Clients"
on crée une clé secondaire ISBN dans la table "Achats" liée à la clé primaire ISBN de
la table "Livres"
La relation fournisseurs-livres appartenant à la 2ère règle :
on crée une clé primaire dans la table "Fournisseurs" sur l'attribut TVA car il est
unique
on crée une clé secondaire TVA_fourn dans la table "Livres" liée à la clé primaire
TVA_fourn de la table "Fournisseurs"
Les clés secondaires se placent en principe du côté de la cardinalité (1-1). Attention toutefois
aux logiciels de design de DB qui placent les cardinalités "à l'envers".
Une fois le schéma relationnel complété et validé, on peut commencer à créer la db.
Chapitre 3 : Les attributs
3.1 Les types d’attributs
Les types des attributs peuvent être de types différents :
•
Nombre entier (quantité commandée, âge)
•
Nombre à virgule (prix)
•
Chaîne de caractères (nom, adresse)
•
Date et heure (date de naissance, heure de parution)
•
Énumération (une couleur parmi une liste prédéfinie)
•
Ensemble (une ou des monnaies parmi une liste prédéfinie)
Il s’agit de choisir le plus adapté.
Ces types requièrent une plus ou moins grande quantité de données à stocker. Par exemple,
ne pas choisir un LONGTEXT pour stocker un prénom mais plutôt un VACHAR(40).
3.2 Les entiers
nom
borne inférieure
borne supérieure
TINYINT
-128
127
TINYINT UNSIGNED 0
255
SMALLINT
-32768
32767
SMALLINT
UNSIGNED
MEDIUMINT
0
65535
-8388608
8388607
MEDIUMINT
UNSIGNED
INT*
0
16777215
-2147483648
2147483647
INT* UNSIGNED
0
4294967295
BIGINT
9223372036854775807
9223372036854775808
BIGINT UNSIGNED 0
18446744073709551615
(*) : INTEGER est un synonyme de INT
UNSIGNED permet d’avoir un type non signé
ZEROFILL : remplissage des zéros non significatifs
3.3 Les flottants
nom
domaine négatif :
borne inférieure
borne supérieure
Domaine positif :
borne inférieure
borne supérieure
FLOAT
-3.402823466E+38
-1.175494351E-38
1.175494351E-38
3.402823466E+38
DOUBLE
ou REAL
-1.7976931348623157E+308 2.2250738585072014E-308
-2.2250738585072014E-308 1.7976931348623157E+308
3.4 Les chaînes
nom
longueur
CHAR(M)
VARCHAR(M)
BINARY
TINYTEXT
Chaîne de taille fixée à M, où 1<M<255,
complétée avec des espaces si nécessaire.
Idem, mais insensible à la casse lors des tris et
recherches.
Chaîne de taille variable, de taille maximum M, où
1<M<255, complété avec des espaces si
nécessaire.
Idem, mais insensible à la casse lors des tris et
recherches.
Longueur maximale de 255 caractères.
TEXT
Longueur maximale de 65535 caractères.
MEDIUMTEXT
Longueur maximale de 16777215 caractères.
LONGTEXT
Longueur maximale de 4294967295 caractères.
DECIMAL(M,D)
ou NUMERIC
Simule un nombre flottant de D chiffres après la
virgule et de M chiffres au maximum. Chaque
chiffre ainsi que la virgule et le signe moins (pas
le plus) occupe un caractère.
CHAR(M) BINARY
VARCHAR(M)
Les types TINYTEXT, TEXT, MEDIUMTEXT et LONGTEXT peuvent être judicieusement
remplacés respectivement par TINYBLOB, BLOB, MEDIUMBLOB et LONGBLOB.
Ils ne diffèrent que par la sensibilité à la casse qui caractérise la famille des BLOB. Alors que
la famille des TEXT sont insensibles à la casse lors des tris et recherches.
Les BLOB peuvent être utilisés pour stocker des données binaires.
Les VARCHAR, TEXT et BLOB sont de taille variable. Alors que les CHAR et DECIMAL sont
de taille fixe.
3.5 Dates et heures
nom
description
DATE
Date au format anglophone AAAA-MM-JJ.
DATETIME
Date et heure au format anglophone AAAA-MM-JJ
HH:MM:SS.
TIMESTAMP
Affiche la date et l’heure sans séparateur :
AAAAMMJJHHMMSS.
TIMESTAMP(M) Idem mais M vaut un entier pair entre 2 et 14. Affiche les M
premiers caractères de TIMESTAMP.
TIME
Heure au format HH:MM:SS.
YEAR
Année au format AAAA.
nom
description
TIMESTAMP(2) AA
TIMESTAMP(4) AAMM
TIMESTAMP(6) AAMMJJ
TIMESTAMP(8) AAAAMMJJ
TIMESTAMP(10) AAMMJJHHMM
TIMESTAMP(12) AAMMJJHHMMSS
TIMESTAMP(14) AAAAMMJJHHMMSS
Les formats DATETIME, DATE et TIMESTAMP
DATETIME est utilisé pour indiquer la date et l'heure.
MySQL affiche les valeurs DATETIME sous la forme 'YYYY-MM-DD HH:MM:SS'. Les limites
en sont : '1000-01-01 00:00:00' à '9999-12-31 23:59:59'.
DATE est utilisé pour indiquer la date, sans plus. MySQL affiche les valeurs DATE sous la
forme 'YYYY-MM-DD' format. Les limites en sont '1000-01-01' à '9999-12-31'.
TIMESTAMP possède diverses propriétés, liées à la version de MySQL et au mode SQL
utilisé par le serveur.
Des valeurs hors limites dans les champs DATETIME, DATE ou TIMESTAMP seraient
converties en ``zero'' ('0000-00-00 00:00:00', '0000-00-00', or 00000000000000).
Chapitre 4 : Les index
Lors de la recherche d’informations dans une relation, MySQL parcours la table
correspondante dans n’importe quel ordre. S’il y a un grand nombre de lignes, cette
recherche est très longue.
Pour y remédier, une optimisation possible et recommandée, est d’utiliser des index.
La création d’un index associé à un attribut ou à un ensemble ordonné d’attributs va créer
une liste ordonnée des valeurs de ces attributs et de l’adresse de la ligne associée. C’est sur
les valeurs de cette liste que se feront les recherches et les tris. Les algorithmes de
recherche et de tri sur des ensembles ordonnés sont beaucoup plus rapides.
Un index offre toutefois l'inconvénient de ralentir les mises à jour (insertion, suppression,
modification de clé).
On choisira de créer des index sur les attributs qui seront les plus sollicités par les
recherches ou utilisés comme critère de jointure. Par contre, on épargnera les attributs qui
contiennent peu de valeurs différentes les unes des autres et ceux dont les valeurs sont très
fréquemment modifiées.
Un index peut porter sur 15 colonnes maximum.
Une table peut posséder au maximum 16 index
Un index peut avoir une taille maximale de 256 octets et ne doit porter que sur des attributs
NOT NULL.
Il faut suffixer l’attribut (CHAR, VARCHAR) pour ne prendre que les M premiers caractères
pour l’indexation
N° Titre
Auteur Éditeur Année ISBN
etc.
1 Mon jardin J. Machin Eyrolles 1998 5-1234-4321-8 ...
2 Access
A. Chose Dunod 2002 3-6789-9876-2 ...
3 Les écoles S. Truc Lattès 2001 4-1985-5891-3 ...
4 etc.
Un index est représenté par une table à une seule colonne. Dans le premier index (index sur
le titre), le premier titre par ordre alphabétique correspond au livre n° 2 (Access), suivi du
livre n° 3 (Les écoles) et du livre n° 1 (Mon jardin). Les autres index s'interprètent de la
même façon.
2
3
1
..
Index titre
2
1
3
..
Index auteur
2
1
3
..
Index éditeur
1
3
2
..
Index année
2
3
1
..
Index ISBN
4.1 Avantages :
accélérer les recherches d'information, car l'index est une représentation de la table,
triée sur un champ donné. On peut donc lui appliquer les méthodes de recherche
rapide sur un ensemble ordonné (c'est le SGBD qui se charge de l'opération,
transparente pour l'opérateur) ;
vu sa taille très inférieure à celle de la table : on peut le remettre à jour en temps réel
à chaque modification de cette dernière ;
empêcher la redondance de l'information.
4.2 Inconvénients :
la création et la mise à jour d'un index devant être mis à jour immédiatement
augmente la charge de travail, et par conséquent freine le système, surtout sur des
bases de grande taille.
un index occupe de la place sur le disque
4.3 L'index sur plusieurs champs
Imaginons que nous ayons séparé le nom et le prénom dans le masque de saisie. Un index
sur les deux champs nom et prénom correspond en fait à l'index créé sur un champ unique
dans lequel seront concaténés le nom et le prénom.
On appelle "doublon" une information qui apparaît au moins deux fois dans une table (erreur
de saisie ou manipulation de l'information). La notion de doublons s'applique à une colonne
donnée, ou à plusieurs colonnes, ou à la totalité des colonnes d'une même table. Dans ce
dernier cas, nous avons affaire à deux enregistrements (ou plus) identiques.
A
1
2
3
1
B
aa
bb
cc
dd
C
$
%
+
-
Doublon sur
1 colonne
A
1
2
3
1
B
aa
bb
cc
aa
C
$
%
+
-
Doublon sur
2 colonnes
A
1
2
3
1
B
aa
bb
cc
aa
C
$
%
+
$
Enregistrement
dupliqué
Lorsque nous introduisons de l'information dans une table pourvue d'un index, le SGBD met
ce dernier à jour en temps réel. Au cours de cette opération, il peut détecter facilement si
cette nouvelle information constitue un doublon sur les champs concernés. Il est donc aisé
de doter le SGBD d'une fonction permettant, si on le désire, d'empêcher la validation de la
saisie d'un enregistrement constituant un doublon.
Chapitre 5 : Détermination du contenu des bases et des tables
Le schéma heuristique, vision globale et synthétique
Au début des années 70, Tony Buzan, à la suite de ses recherches sur l'apprentissage et le
cerveau humain, a donné naissance à une méthode d'organisation des idées, sous forme de
dessin ou d'arborescence, d'où découle le concept de MindMap.
Le Mind Map (ou schéma heuristique) apparaît fort utile pour aider à produire une image
visuelle de ce que l'on sait ou pour tracer un plan d'ensemble de ce que l'on a appris ou
retenu sur un sujet.
Par le pouvoir d'association, de regroupement et de visualisation des idées d'un schéma
heuristique, il devient facile de constituer un éventail d’idées et d'obtenir une image globale
de notre pensée sur un sujet. De plus, les structures schématiques obtenues améliorent les
capacités d'organisation et de compréhension du système.
Heuristique signifie apprentissage par la prise en compte
des activités antérieures
Pour ces différentes raisons, son utilisation pour la structuration des relations au sein d’une
base de données, surtout si elle est complexe, apparaît judicieuse.
Carte heuristique
Une carte heuristique (mind map), également appelée carte des idées, carte conceptuelle
ou carte mentale, est un diagramme qui représente les connexions sémantiques (signification des
mots composés, rapports de sens entre les mots) entre différentes idées, les liens hiérarchiques
entre différents concepts intellectuels. À la base, il s'agit d'une représentation principalement
arborescente des données, basée sur les mêmes principes que l'organigramme (l'un étant en fait
une variante de l'autre et réciproquement).
Une carte heuristique met en œuvre différentes composantes améliorant son exploitation :
utilisation de formes, de couleurs et de graphismes (illustrations, symboles) qui permettent
théoriquement une compréhension aisée par un fonctionnement optimal et conjoint des
hémisphères cérébraux.
Les Mind Maps (schémas heuristiques) servent à représenter graphiquement le schéma de
la pensée d'une personne – ou d’un groupe de personnes - et le réseau d'association de ses
idées. Il faut cependant respecter un minimum de règles de manière à faciliter la création du
schéma heuristique, l'association des mots et des images, l'organisation des idées et la
présentation du contenu.
* Placer le support papier à l'horizontale, dessiner l'image principale évocatrice du sujet
au centre de la page
* Noter les idées à partir d'un mot qui exprime concrètement l'essentiel
* Écrire sous forme de Mots-Clés pour favoriser les associations
* Utiliser des images pour augmenter le pouvoir d'expression, d'association et de
mémorisation
* Générer des ramifications à partir de l'image principale pour poser les images ou les
mots associés
* Inscrire un mot unique par branche pour plus de fluidité (plus grand nombre
d'associations)
* Tracer les lignes de même longueur que les mots pour un meilleur rapprochement entre
les mots, faciliter l'association et faire un gain d'espace
* Tracer les branches centrales (lignes) plus épaisses et les branches périphériques
(lignes) plus fines
* Appliquer la mise en exergue du contenu en donnant l'illusion du volume, du mouvement
et de la profondeur (Variation de la taille des caractères, volume des ramifications
sinueuses ou grosseur des images)
* Ajouter des signes ou symboles pour créer l'illusion du mouvement
* Utiliser des codes de couleurs ou des formes pour désigner les personnes, tâches,
projets, éléments ou procédés
Exercices et solutions
Exercice 01 : établir des modèles Entité – Association – Cardinalité
A/ Soient un ensemble de personnes et un ensemble de voitures. Une personne est
caractérisée par un numéro qui l'identifie et par les voitures dont elle est l'unique propriétaire.
Une voiture est caractérisée par un numéro de plaque, une marque et une date de mise en
circulation.
--------------------------------------------------------------------------------------------------------------------------B/ Les différents départements d'une entreprise occupent des employés. Un employé est
décrit par son numéro matricule (unique dans l'entreprise), son nom, son grade et le
département dans lequel il travaille. Un département est décrit par son numéro dans
l'entreprise et par son directeur qui doit être un de ses employés.
--------------------------------------------------------------------------------------------------------------------------C/ Un exploitant possède plusieurs salles de cinéma. Un film fait généralement l'objet de
plusieurs séances par jour. Décrire un schéma qui permette à l'exploitant d'obtenir des
renseignements sur le chiffre d'affaire d'un film, d'une salle, d'une séance où d'un jour
déterminé (un seul film par salle à un même endroit).
--------------------------------------------------------------------------------------------------------------------------D/ Définir un schéma décrivant une nomenclature de type de pièces. Chaque type de pièce
est caractérisé par son numéro, les types de pièces qui la composent et ceux dont il est le
composant.
--------------------------------------------------------------------------------------------------------------------------E/ Définir un schéma décrivant les liens familiaux d'une population de personnes identifiables
par un numéro.
--------------------------------------------------------------------------------------------------------------------------F/ Gestion de la production et des stocks
Une société a décidé de concevoir une base de données pour l'assister dans la gestion de
sa production et des ses stocks.
Une unité de fabrication est identifiée par un numéro est caractérisée par un nom, une
localisation et une capacité globale de production. Elle peut fabriquer plusieurs produits.
A tout moment, on connaît, pour une unité de fabrication et pour un produit que cette unité
fabrique, la quantité en cours de fabrication et les capacités minimum et maximum de
fabrication.
Un dépôt est également identifié par un numéro et possède un nom, une localisation et une
capacité globale de stockage. Un dépôt peut stocker plusieurs produits. A chaque instant, on
connaît, pour un dépôt et pour un produit qui y est stocké, la quantité due.
Un produit peut-être fabriqué par plusieurs unités de fabrication et stocké dans plusieurs
dépôts. A chaque produit est associé un numéro qui l'identifie. Un produit possède
également un nom, une unité de mesure et un prix. Une quantité unitaire de produit fini ou
semi-fini est toujours fabriquée à partir de quantités déterminées d'autres produits semi-finis
et/ou de matières premières qu'une unité de fabrication commande à ce dépôt.
Une commande d'une unité de fabrication est adressée à un seul dépôt, est identifiée par un
numéro, possède une date et concerne un ou plusieurs produits dont elle spécifie la quantité
commandée.
--------------------------------------------------------------------------------------------------------------------------G/ Gestion d'employés, de locaux, de départements et de projets
Une société a décidé de construire une base de données regroupant ses départements, ses
bureaux (locaux), ses projets et ses employés.
Un département est identifié par un numéro (unique dans la société). Il est dirigé par un chef
de département - qui est un employé de ce département - et dispose d'un certain budget. Un
département fait travailler des employés sur des projets dont il est responsable, et dispose
d'un certains nombre de locaux.
Un bureau, identifié par un numéro (unique dans la société) et caractérisé par une surface
utile (exprimée en m2 , est mis à disposition d'un département. Un ou plusieurs postes
téléphoniques sont raccordés dans un bureau. Un poste téléphonique, placés dans un
bureau, possède un seul numéro d'appel.
Un projet est géré par un département et est identifié par un numéro (unique dans un
département). Chaque projet possède un budget de fonctionnement propre.
Un employé est identifié par un numéro matricule (unique dans la société). Un employé
travaille pour un seul département; il ne peut dès lors être le chef que d'un seul département.
Un employé est affecté à un projet, dispose d'un bureau et peut être accessible par un seul
numéro d'appel. Par contre plusieurs employés d'un même bureau peuvent être appelés par
le même numéro de téléphone.
La société a établi un inventaire des fonctions (concepteur, analyste, programmeur, ...)
identifiées par un numéro et caractérisées par un salaire plancher et un salaire plafond. La
société souhaite mémoriser l'historique des fonctions de chaque employé avec la date
d'entrée en fonction et le salaire initial attribué.
--------------------------------------------------------------------------------------------------------------------------H/ Octroi d'un prêt dans un organisme bancaire.
Les activités liées à l'octroi d'un prêt d'un prêt dans la banque X sont décrites dans ce qui
suit.
Le client se rend à une agence de la banque X et soumet sa demande de prêt à un employé
en lui indiquant le montant qu'il souhaite emprunter, la durée, le motif du prêt ainsi que des
renseignements généraux tels que son nom, son adresse, et son revenu mensuel.
L'employé de l'agence constitue un dossier avec ses différents renseignements et les
transmet au service crédit du siège de la banque.
Un préposé de celui-ci parcourt le dossier et décide sur base du revenu mensuel et du motif
du prêt de refuser la demande ou de l'étudier de façon plus approfondie.
Dans ce cas, il émet une demande de renseignement auprès du service d'informations.
Ces renseignements portent d'une part sur le comportement du client vis-à-vis d'éventuels
autres prêts qu'il aurait contracté antérieurement et d'autre part sur l'identité du client, son
revenu mensuel, ...
Dans l'attente de ces informations, le préposé établit un premier plan de financement: le taux
à appliquer, le montant et la durée du prêt puis, quand il a reçu le réponse du service
d'informations, il peut formuler une ou plusieurs propositions à présenter au client ou refuser
la demande de prêt. Les propositions sont adjointes à la demande qui retourne à l'agence.
A la réception du dossier, le gérant de l'agence examine les propositions avec le client : soit
le client accepte une des propositions, soit il n'en accepte aucune, il en accepte une sous
réserve de modifications (rectification de la durée, diminution du montant emprunté, ...).
Dans ce dernier cas, le gérant établit, avec le client, une nouvelle proposition qui tient
compte des changements demandés.
Une proposition de prêt acceptée par le client doit encore être approuvée par la direction.
Celle-ci 'occupe des dossiers de prêt en fin de semaine ou lorsqu'elle en a au moins vingt en
attente. Ensuite les dossiers approuvés retournent en agence où ils donnent lieu à
l'établissement du contrat de prêt.
On connaît le nom, l'adresse, le salaire mensuel d'un client et les prêts qu'il a déjà
éventuellement contractés auprès de la banque X ou d'une autre institution financière ainsi
que pour chacun de ceux-ci, la façon dont il rembourse (régulier, tardif, mauvais).
On connaît également les demandes de prêt qu'un client a déjà éventuellement émises
auprès de la banque X et, si elles ont été acceptées, les différentes propositions auxquelles
elles ont donnés lieu.
Une demande de prêt comporte les renseignements suivants ; montant demandé, durée
demandée, date d'introduction de la demande e motif du prêt.
Pour une institution financière, on connaît son nom, son type (banque, organisme privé,
organisme public) et les différents prêt contractés par les clients de la banque X auprès de
cette institution.
Pour un prêt, on connaît son numéro, son montant, sa durée, la personne qui l'a contracté,
l'organisme auprès duquel il a été contracté, le type d'échéance,(mensuelle, trimestrielle,
annuelle), les date de début et de fin de remboursement, le taux d'intérêt appliqué à
l'emprunt et le montant à rembourser (montant prêté + intérêts).
Une proposition de prêt est caractérisée par sa date, la durée et le montant du prêt proposé,
le taux d'intérêt. Si elle a effectivement donné lieu à un prêt, on connaît le prêt
correspondant.
On enregistre quotidiennement le taux d'escompte qui permet à la banque X de calculer le
taux d'intérêt applicable. En effet, le taux d'intérêt est déterminé par en fonction du motif, du
montant, de la durée du prêt et du taux d'escompte.
Les valeurs des différentes informations relatives à un prêt sont nécessairement identiques
aux informations correspondantes de la proposition qui lui a donné lieu (si elle existe).
Exercice 02 : traduire en mode relationnel
G/ Gestion d'employés, de locaux, de départements et de projets
Exercice 04 : création DB #1
1/ Existant
Une organisation non gouvernementale d’aide humanitaire aux pays du tiers-monde effectue
des interventions d’aide à l’éducation (enseignements professionnels, alphabétisation,
puériculture,...), au développement (agriculture, irrigation,...), à la prévention sanitaire
(vaccinations, mise en service de dispensaires, ...) dans divers pays.
Ses interventions sont programmées pour une durée limitée (de quelques mois à deux ans).
Chaque intervention possède un responsable sur place nommé en principe pour toute la
durée de l’intervention. Pour chaque intervention, sont également définis un site principal
(base d’intervention) et des sites secondaires, lieux géographiques dans lesquels
l’organisation est présente au titre de cette intervention.
Des intervenants bénévoles ou rémunérés sont affectés aux différentes interventions pour
des missions de quelques jours ou de quelques mois.
Le siège européen de l’organisation souhaite rassembler dans une base de données les
informations relatives aux interventions, aux responsables, aux sites d’intervention et aux
intervenants (actuellement en mission ou disponibles pour une nouvelle affectation). Il pense
ainsi pouvoir accéder facilement à des informations telles que :
- la liste des interventions, avec les coordonnées de leurs responsables ;
- la liste des intervenants possibles ;
- la liste des personnes intervenant dans une mission donnée à une date donnée ;
- des pages d’information avec, pour chaque mission, la liste des personnes qui y
interviennent ou y sont intervenus, etc.
2/ analyse et mise en place du modèle Entité - Attributs - Association – Cardinalité
3/ traduction en mode relationnel
4/ création des tables