Bases de données

RÉFÉRENCE: ATLANTIC-83/DOCTECH /Algorithmique & Programmation / InitiationBasesDeDonnées
DOMAINE:
Algorithmique & Programmation
INITIATION AUX
BASES DE DONNEES
VERSIONS & DATES
Version 1.0 – Octobre 2016
11/10/2016
OBJET
Création
AUTEUR(S)
Bernard GIACOMONI
Initiation aux Bases de Données
1
INTRODUCTION:
Ce document en forme de diaporama constitue une initiation aux principaux concepts attachés à la notion de
BASE DE DONNÉES et aux traitements informatiques associés.
Après une rapide présentation des caractéristiques fondamentales des BASE DE DONNÉES et des différents
modèles d'organisation de celles-ci, l'ouvrage se consacre plus particulièrement à l'étude du modèle
RELATIONNEL.
Dans ce cadre, il décrit les démarches et outils employés lors des trois niveaux d'analyse utilisés pour modéliser
l'organisation des systèmes et en déduire d'une manière rationnelle la structure des données :
● Modèle Conceptuel ;
● Modèle Logique ;
● Modèle Physique.
Il aborde enfin les langages informatiques (langages de requêtes) utilisés pour communiquer avec les systèmes
de gestion des bases de données, et plus particulièrement le langage SQL et son utilisation à travers l'API
MySQL dans le cadre du développement des sites web.
11/10/2016
Initiation aux Bases de Données
2
SOMMAIRE:
I.GÉNÉRALITÉS SUR LES BASES DE DONNÉES
I.1.DÉFINITIONS
I.2.REPRÉSENTATION PHYSIQUE ET PRINCIPES DE FONCTIONNEMENT
I.3.DIFFÉRENTS TYPES DE BASES DE DONNÉES
I.4.MODÈLES D'ANALYSE DES BD RELATIONNELLES
II.LE MODÈLE CONCEPTUEL ENTITÉS-ASSOCIATIONS
II.1.LES ENTITÉS
II.2.LES ASSOCIATIONS
II.3.LES SCHÉMAS ENTITÉS - ASSOCIATIONS
II.4.EXEMPLE DE MCD D'UN SYSTÈME D'INFORMATION
III.LE MODÈLE LOGIQUE
III.1.REPRÉSENTATION D'UNE ENTITÉ SOUS FORME DE TABLE
III.2.REPRÉSENTATION D'UNE ASSOCIATION BINAIRE
III.3.REPRÉSENTATION D'UNE ASSOCIATION DE RANG > 2
III.4.LES FORMES NORMALES
III.5.NOTATIONS ALGÉBRIQUES
III.6.OPERATIONS SUR LES TABLES
III.7.EXEMPLE DE PASSAGE DU MODÈLE CONCEPTUEL AU MODÈLE LOGIQUE
IV.LE MODÈLE PHYSIQUE DES DONNÉES (MPD)
IV.1.DEFINITION DU MPD
IV.2.LES SYSTÈMES DE GESTION DE BASES DE DONNÉES:
IV.3.LES A.P.I DE GESTION DE BASES DE DONNÉES
IV.4.LES I.H.M DE GESTION DE BASES DE DONNÉES
IV.5.MODÈLE PHYSIQUE DE LA BASE DE DONNÉES BIBLIOTHÈQUE
V.APERÇU DU LANGAGE SQL:
V.1.INTRODUCTION:
V.2.CREATION DE LA STRUCTURE D'UNE BASE DE DONNÉES
V.3.MODIFICATION DE LA STRUCTURE D'UNE BASE DE DONNÉES
V.4.MODIFICATION DU CONTENU D'UNE BASE DE DONNÉES
V.5.LA REQUÊTE SELECT
V.6.LES REQUÊTES DE JOINTURE
VI.L'API MYSQL
VI.1.PRÉSENTATION DE L'API MYSQL
VI.2.FORME GÉNÉRAL DES APPELS A L'API MySQL en PHP
VI.3.RÉCUPÉRATION DU TABLEAU ASSOCIATIF APRÈS UN SELECT
VI.4.UTILISATION DE L'API MySQL
VI.5.EXEMPLES D'UTILISATION DE MySQL
11/10/2016
Initiation aux Bases de Données
3
I.GENERALITES SUR LES BASES DE DONNEES:
I.1.DEFINITIONS:
I.1.1.SYSTÈME D'INFORMATION:
Le système d'information d'un organisme quelconque (entreprise, administration, etc.) peut être divisé en deux
composants distincts :
● L'ORGANISATION HUMAINE, qui rassemble tous les collaborateurs de cet organisme qui contribuent au
recueil, à la présentation, à la préservation, à l'élaboration, au traitement et à la diffusion interne et externe des
informations utiles au fonctionnement de cet organisme ou engendrées par son activité ;
● LE SYSTÈME INFORMATIQUE, qui offre à ces collaborateurs des outils informatiques dont ils ont besoin pour
assurer leurs missions.
I.1.2.SYSTÈME INFORMATIQUE:
Il s'agit d'un ensemble de moyens informatiques (postes de travail, serveurs, moyens d'impression, moyens de
communication, logiciels, etc.), généralement connectés entre eux au moyen d'un ou plusieurs RÉSEAUX
INFORMATIQUES. Le système informatique permet aux collaborateurs, par l'intermédiaire de POSTES DE
TRAVAIL INFORMATIQUES :
● D'une part d'accéder aux données ou aux services informatiques dont ils ont besoin pour réaliser leur tâche ;
● D'autre part, de sauvegarder les résultats de leurs travaux ou de les mettre à disposition des autres
collaborateurs ou des organismes collaborant avec le leur.
L'élément CENTRAL d'un tel système informatique (hormis le réseau de communication) est la BASE DE
DONNÉES (ou l'ensemble des bases de données), qui stocke l'ensemble des données RÉMANENTES utilisées
ou produites par l'organisme. Ces données constituent un PATRIMOINE IMMATÉRIEL indispensable au
fonctionnement de l'organisme et partie intégrale de son capital, qu'il convient de ce fait de protéger de la
destruction (perte d'informations non sauvegardées), de la corruption (erreurs de saisie, intrusions
malveillantes, etc.) et du piratage (vol de données protégées).
C'est cet aspect stratégique qui justifie la mise en œuvre de Systèmes de Gestion de Bases de Données
FIABILISÉS, STANDARDISÉS et SÉCURISÉS pour stocker, gérer et utiliser ces données.
11/10/2016
Initiation aux Bases de Données
4
I.GENERALITES SUR LES BASES DE DONNEES:
I.1.DEFINITIONS (suite):
I.1.3.BASES DE DONNÉES :
Une Base de Données (BD) est un ensemble de données structurées et organisées entre elles de façon
logique. Les quatre principales caractéristiques que cette organisation doit posséder sont :
● L'UNICITÉ des données: une donnée ne doit figurer qu'une seule fois dans la BD;
● L'ABSENCE D’AMBIGUÏTÉ entre les données constituant la BD (chaque donnée doit pouvoir être repérée
sans risque de confusion avec une autre donnée de la BD) ;
● La COHÉRENCE des données entre elles (une donnée de la BD ne peut être en contradiction avec une
autre donnée de la BD);
● L'INTÉGRITÉ des données (Une donnée de la BD ne peut faire référence à une autre donnée absente de la
BD).
I.1.4.SYSTEMES DE GESTION DE BASES DE DONNÉES :
Un SYSTÈME DE GESTION DE BASES DE DONNÉES (SGBD) est un PROGICIEL qui permet de gérer et
d'utiliser des bases de données, c'est à dire:
● CRÉER une base de données ;
● ADMINISTRER sa structure interne et son contenu;
● La DÉTRUIRE ;
● Donner aux utilisateurs la possibilité d'en EXTRAIRE ou d'y INSÉRER DES INFORMATIONS ;
UTILISATEUR 1
UTILISATEUR 2
UTILISATEUR n
11/10/2016
Application Programing Interface (bibliothèque de
fonctions, dépendant du langage de programmation utilisé)
A.P.I
- Fonction 1
Appels de - Fonction 2
fonctions
…..............
- Fonction m
Requêtes
(ex : SQL)
Réponses
aux
Requêtes
SGBD
(Logiciel)
Ex : MySQL
Initiation aux Bases de Données
Base de
données
Fichiers
5
I.GENERALITES SUR LES BASES DE DONNEES:
I.2.REPRESENTATION PHYSIQUE ET PRINCIPES DE FONCTIONNEMENT:
Physiquement, une BASE DE DONNÉES se présente comme un GROUPE DE FICHIERS renfermant des
informations. Cependant, une base de données se différencie d'un groupe de fichiers indépendants les uns des
autres (fichiers dits "A PLAT") par les caractéristiques suivantes :
● Les données contenues dans un fichier donné de la BD sont reliées entre elles et à celles des autres fichiers
par des RELATIONS LOGIQUES (Organisation STRUCTURÉE) ;
● Une donnée ne doit être déclarée qu'une seule fois et dans un seul fichier de la BD, ceci évitant les
redondances et les incohérences (UNICITÉ des données) ;
● Chacune des données figurant dans les fichiers de la BD doit pouvoir être identifiée SANS AMBIGUÏTÉ;
● Une donnée contenue dans l'un quelconque des fichiers de la BD ne peut en aucun cas créer une
contradiction avec une autre donnée de la BD (c'est le critère de COHÉRENCE).
● Une donnée quelconque de la BD ne peut faire référence à une autre donnée qui ne figurerait pas dans les
fichiers de la BD (Critère d’INTÉGRITÉ).
Fichiers "a plat"
Fichier Commandes :
Commande n° 1
Date :
17/12/2015
Montant : 130 euros
Client :
N° :
2
Nom :
xxxxx
Prenoms : yyyyy
Adresse :
zzzzz
Commande n° 2
Date :
14/11/2015
Montant : 80 euros
Client :
Nom :
uuuu
11/10/2016
Fichier Clients :
Client n° 1
Nom :
mmmm
Prenoms : nnnnnn
Adresse : pppppp
Client n° 2
Nom :
xxxxx
Prenoms : yyyyy
Adresse : zzxys
Client n° 3
Nom :
abcde
Prenoms : rstuv
Adresse : axbycw
Aucune relation
logique n'est
établie.
Redondance
de la donnée
"Adresse"
avec risque
d'incohérence
Redondance
remplacée
par une
relation logique:
aucun risque
d'incohérence
Base de données
Fichier Commandes :
Fichier Clients :
Commande n° 1
Date :
17/12/2015
Montant : 130 euros
N° Client : 2
Client n° 1
Nom :
mmmm
Prenoms : nnnnnn
Adresse : pppppp
Commande n° 2
Date :
14/11/2015
Montant : 80 euros
N° client : 124
Initiation aux Bases de Données
Client n° 2
Nom :
xxxxxx
Prenoms : yyyyyy
Adresse : zzzzzz
Client n° 3
Nom :
abcde
Prenoms : rstuv
Adresse : axbycw
6
I.GENERALITES SUR LES BASES DE DONNEES:
I.2.REPRESENTATION PHYSIQUE ET PRINCIPES DE FONCTIONNEMENT (suite):
Le SGBD permet à l'utilisateur de NAVIGUER à travers les différents fichiers et les données qu'ils renferment en
FAISANT ABSTRACTION des problèmes techniques liés à cette navigation. L'utilisateur n'a à se préoccuper
que de la structure logique des données et des relations qui existent entre elles.
Un SGBD fonctionne sur le modèle CLIENT-SERVEUR: les utilisateurs (clients) adressent au SGBD (Serveur)
des requêtes dans un langage de requêtes (ex: SQL). Le serveur répond en fournissant un «service», qui peut
se traduire par la fourniture de données au client, l'insertion de données du client dans la BD, etc... La structure
physique interne de la base de données est cachée pour l'utilisateur.
11/10/2016
Initiation aux Bases de Données
7
I.GENERALITES SUR LES BASES DE DONNEES:
I.3.DIFFÉRENTS TYPES DE BASES DE DONNÉES:
L'ORGANISATION LOGIQUE DES INFORMATIONS adoptée détermine le type de base de donnée. Ainsi, il
existe :
1.Des bases de données HIÉRARCHIQUES qui organisent les données suivant une structure
ARBORESCENTE : chaque donnée à une donnée MÈRE (et une seule) et des données FILLES (par exemple,
le système de fichiers de Window ou la classification des espèces animales) ;
2.Des bases de données RÉSEAU, dans lesquelles les données sont organisées entre elles comme les nœuds
d'un réseau.
3.Des bases de données RELATIONNELLES, dans lesquelles il est possible de définir dynamiquement entre
les données des RELATIONS logiques grâce à un LANGAGE DE REQUÊTES ;
4.Des bases de données OBJET, qui correspondent à une extension des bases de données RELATIONNELLES
au cas où les données sont des entités complexes pour lesquelles il faut définir non seulement la structure,
mais aussi le type des opérations qui peuvent leur être appliquées.
BASES DE DONNÉES HIÉRARCHIQUES :
Les bases de données HIÉRARCHIQUES définissent des parcours fixes prédéfinis entre les entités qui les
composent. On dit qu'elles sont NAVIGATIONNELLES. Ce type de bases de données est actuellement
pratiquement abandonné en informatique.
ANIMAUX
invertébrés
Base de données
hiérarchique
arthropodes
insectes
11/10/2016
mollusques
vertébrés
oiseaux
poissons
mammifères
crustacés
Initiation aux Bases de Données
8
I.GENERALITES SUR LES BASES DE DONNEES:
I.3.DIFFÉRENTS TYPES DE BASES DE DONNÉES (suite):
BASES DE DONNÉES RÉSEAU :
Les bases de données RÉSEAU définissent également des parcours fixes définis par des liens entre les
entités qui les composent :
CIDRE
fabrication
Dégustation
Vente
POMMES
CONSOMMATEURS
Récolte
Vente
Achat
PRODUCTEURS
COMMANDES
BASES DE DONNÉES RELATIONNELLES :
Contrairement aux précédentes, les bases de données RELATIONNELLES permettent de définir ou redéfinir
dynamiquement les relations existant entre les entités. Un modèle RELATIONNEL permet de parcourir la
structure des données en empruntant des chemins non prédéfinis, constitués en dynamique par des
REQUÊTES. Celles-ci sont associées à des LANGAGES DE REQUÊTES qui permettent de manipuler des
TABLES ( appelées aussi RELATIONS). C'est le type de BD de loin le plus utilisé.
CLIENT
commande
PRODUIT
association
Comme nous l'avons vu précédemment, les bases de donnés OBJETS sont des évolutions des bases de
données RELATIONNELLES au cas où les entités manipulées sont des objets complexes (tableaux, objets
informatiques) . Actuellement, elles ne sont utilisées que pour des applications bien particulières.
11/10/2016
Initiation aux Bases de Données
9
I.GENERALITES SUR LES BASES DE DONNEES:
I.4.MODÈLES D'ANALYSE DES BD RELATIONNELLES:
Une BD RELATIONNELLE peut être décrite selon trois niveaux d'analyse :
● Le MODÈLE CONCEPTUEL (MCD)
● Le MODÈLE LOGIQUE (MLD)
● Le MODÈLE PHYSIQUE (MPD)
Le MODÈLE CONCEPTUEL DES DONNÉES (MCD) a pour but d'élaborer une représentation du SYSTÈME
D'INFORMATION à la fois rigoureuse et facilement compréhensible au niveau de l'UTILISATEUR. Le
système d'information est décrit à l'aide de concepts appelés ENTITÉS et ASSOCIATIONS :
● Les ENTITÉS définies représentent les données manipulées par système ;
● Les ASSOCIATIONS représentent les relations existant entre ces entités.
Le MODÈLE LOGIQUE DES DONNÉES (MLD) a pour but de décrire la structure logique des données qui
découle de l'analyse conceptuelle. Contrairement au MCD, il dépend du type de base de données utilisé. En
revanche, il est INDÉPENDANT DU LANGAGE DE PROGRAMMATION qui pourra être utilisé par le niveau
physique. Le modèle logique décrit les entités et les association sous la formes de TABLES (Tableaux à m
lignes et n colonnes).
Le MODÈLE PHYSIQUE DES DONNÉES (MPD) est représenté par l'implémentation du modèle logique dans
le SGBD choisi. Il s'agit donc de la traduction du modèle logique dans un langage de programmation approprié.
Remarque : Ces trois niveaux d'analyse ont été formalisée à l'origine par la méthode MERISE, spécialisée dans
l'analyse, la conception et la réalisation de SYSTÈMES D'INFORMATIONS. Le domaine d'application de
MERISE est donc plus large que la simple conception de bases de données. Les trois niveaux correspondent
sensiblement aux trois phases classiques de la gestion technique de projets logiciels :
MODÈLE CONCEPTUEL <=> Spécification du besoin
MODÈLE LOGIQUE <=> Conception Préliminaire
MODÈLE PHYSIQUE <=> Conception détaillée
11/10/2016
Initiation aux Bases de Données
10
II.LE MODÉLE CONCEPTUEL ENTITES-ASSOCIATIONS:
II.1.LES ENTITÉS:
II.1.1.DÉFINITION:
Une ENTITÉ définit une CLASSE d'individus ou d'objets ayant des caractéristiques communes.
Exemple: Dans la base de données d'une entreprise, on peut définir les entités «clients», «fournisseurs»,
«commandes», etc. L'entité «clients» regroupera les données relatives à tous les clients de l'entreprise, l'entité
«fournisseurs» les données relatives à tous les fournisseurs travaillant avec l'entreprise.
Les individus ou objets appartenant à une même entité sont appelés REPRÉSENTANTS de cette entité (Ainsi
un représentant de l'entité «fournisseurs» est l'un quelconque de ces fournisseurs). Une ENTITÉ doit être
nommée par un NOM COMMUN AU PLURIEL.
II.1.2.ATTRIBUTS D'UNE ENTITÉ:
Les ATTRIBUTS sont des PROPRIÉTÉS liées à chaque ENTITÉ. La valeur de ces attributs permet
d'individualiser chacun des REPRÉSENTANTS d'une même ENTITÉ.
Exemple: l'entité ÉLÈVES peut avoir pour ATTRIBUTS le nom, les prénoms et la classe d'appartenance de
chaque élève. Les REPRÉSENTANTS de cette entité sont des élèves qui se distingueront les uns des autres
par les valeurs données à ces attributs :
ÉLÈVES
ENTITE ELEVES
●
●
●
REPRESENTANTS
●
●
●
11/10/2016
DUPOND
Thomas
CM2
●
●
●
DURAND
Agnes
CE2
Nom
Prénoms
Classe
Valeurs
D'attributs
Initiation aux Bases de Données
Propriétés
(ou attributs)
●
●
●
DUVAL
Annie
CM1
11
II.LE MODÉLE CONCEPTUEL ENTITÉS-ASSOCIATIONS:
II.1.LES ENTITÉS (Suite):
II.1.3.IDENTIFIANT D'UNE ENTITÉ:
Lorsqu'un attribut (ou un groupe d'attributs) permet de distinguer sans ambiguïté chaque représentant d'une
entité, cet attribut (ou ce groupe d'attributs) peut être pris comme IDENTIFIANT de l'entité.
Exemple: Dans une entité CLIENTS, le groupe (NomClient, PrénomClient) ne peut être pris comme identifiant,
car deux clients peuvent avoir les mêmes nom et prénom. En revanche, si ces clients sont français, on pourrait
prendre comme identifiant le numéro de sécurité sociale (NumSS dans le schéma ci-dessous), car deux clients
ne peuvent avoir le même.
Remarque: En général, quand on ne peut trouver d'identifiant «naturel», on en crée un artificiellement en
attribuant à chaque représentant de l'entité un numéro (Ex : chaque nouveau client d'une entreprise se voit
attribuer un numéro de client unique, qui n'est jamais réattribué). Les systèmes de gestion de bases de
données peuvent gérer automatiquement cette numérotation.
II.1.4.REPRESENTATION GRAPHIQUE:
Dans le modèle conceptuel entité-association, une entité est représentée de la manière suivante:
<Nom d'entité>
Un attribut
identifiant
est souligné
Attribut
Identifiant
Attribut
11/10/2016
CLIENTS
→<nom attribut_1>
→<nom attribut_3>
→<nom attribut_2> EXEMPLES
.........
→<nom attribut_n>
ARTICLES
→NomClient
→PrenomClient
→AdresseClient
→NumSS
→NumArticle
→Designation
→PrixUnitaire
Attribut
Identifiant
Attribut
Identifiant
Attribut
Initiation aux Bases de Données
12
II.LE MODÈLE CONCEPTUEL ENTITÉS-ASSOCIATIONS:
II.2.LES ASSOCIATIONS:
II.2.1.DEFINITION:
Une ASSOCIATION établit un lien logique entre deux ENTITÉS de la BD. Le nom d'une association doit être un
verbe à l'infinitif (Exemple: UTILISER).
Exemple: si l'entité «CLIENTS» a pour représentants Client1, Client2, Client3 et Client4 et si l'entité ARTICLES
a pour représentants WINDOW 10, WINDOW 8, LINUX, l'association "utiliser" pourra établir les liens suivants:
Client1
Client2
Utiliser
Utiliser
WINDOW 10
Utiliser
Utiliser
WINDOW 8
Client3
Utiliser
LINUX
Client4
La signification logique sera:
● Le client Client1 utilise le système window dans sa version 10 ;
● Le client Client2 utilise le système window dans sa version 10 et dans sa version 8 ;
● Le client Client3 utilise le système window dans sa version 10 ;
● Le client Client4 utilise le système window dans sa version 8.
Remarques:
● Aucun de ces clients n'utilise Linux ;
● Par contre, tous les clients utilisent au moins un système d'exploitation, car sinon, ils ne seraient pas clients.
11/10/2016
Initiation aux Bases de Données
13
II.LE MODÈLE CONCEPTUEL ENTITÉS-ASSOCIATIONS:
II.2.LES ASSOCIATIONS (Suite):
II.2.2.ATTRIBUTS D'UNE ASSOCIATION:
Une association entre deux entités établit donc un certain nombre de liens entre les représentants de la
première entité et ceux de la seconde. Cependant, la connaissance de l'ensemble de ces liens ne suffit en
général pas à caractériser totalement l'association.
Exemple : le schéma ci-dessus permet de connaître qui utilise quoi, mais il ne permet pas de savoir en quelle
quantité.
Les associations ont donc besoin d'être caractérisées par des attributs, comme les entités. Par exemple,
l'association ACHETER pourrait se voir affecter les attributs «quantité» et «date_achat».
II.2.3.REPRESENTATION GRAPHIQUE D'UNE ASSOCIATION:
Les ASSOCIATIONS sont représentées par des schémas assez semblables aux entités (remarquer les angles
arrondis qui différencient leur graphisme de celui des entités) :
<Nom de relation>
→<nom attribut_1>
→<nom attribut_3>
→<nom attribut_2>
.........
→<nom attribut_n>
11/10/2016
ACHETER
→quantité
→date_achat
EXEMPLE
Initiation aux Bases de Données
14
II.LE MODÈLE CONCEPTUEL ENTITÉS-ASSOCIATIONS:
II.3.LES SCHEMAS ENTITÉS - ASSOCIATIONS:
II.3.1.GENERALITES :
Le MODÈLE CONCEPTUEL des bases de données RELATIONNELLES décrit les bases de données sous la
forme d'ASSOCIATIONS D'ENTITÉS.
Lorsqu'une association relie entre elles deux entités, on parle d'ASSOCIATION BINAIRE :
ENTITE 1
ENTITE 2
ASSOCIATION
Le modèle prévoit également la possibilité d'ASSOCIATIONS reliant plus de deux ENTITÉS : associations
ternaire (reliées à 3 entités), quaternaires (reliées à 4 entités), etc. Cependant, ces associations sont très
difficiles à concevoir immédiatement. Ces associations complexes sont toujours équivalentes à plusieurs
associations binaires. De ce fait, lors de la conception du modèle d'une BD, il est conseillé de n'utiliser dans
un premier temps que des associations binaires, puis de regrouper ces associations.
ENTITE 1
ASSOCIATION
Association
Ternaire
11/10/2016
ENTITE 2
ENTITE 3
Initiation aux Bases de Données
15
II.LE MODÈLE CONCEPTUEL ENTITÉS-ASSOCIATIONS:
II.3.LES SCHEMAS ENTITÉS – ASSOCIATIONS (suite):
II.3.2.LES ASSOCIATIONS BINAIRES :
Une ASSOCIATION BINAIRE relie entre elle deux ENTITÉS :
<Nom d'association>
<Nom d'entité 1>
→<nom attribut_1>
→<nom attribut_3>
→<nom attribut_2>
.........
→<nom attribut_n>
(min1,
max1)
<Nom d'entité 2>
→<nom attribut 1>
→<nom attribut 2>
→<nom attribut 3>
...............
→<nom attribut k>
(min2,
max2)
→<nom attribut_1>
→<nom attribut_2>
→<nom attribut_3>
.........
→<nom attribut_m>
Les couples de nombres qui apparaissent sur les liens indiquent la CARDINALITÉ du lien entre entité et
association. Sur chaque lien, le premier chiffre indique le nombre minimum de fois qu'un représentant de l'entité
peut être concerné par la relation, le deuxième indique le maximum de fois.
Remarque : des associations binaires différentes peuvent porter sur les mêmes entités, ce qui conduit à des
réseaux d'associations:
ENTITÉ 1
ASS1
ENTITÉ 2
ASS2
ENTITÉ 3
ASS3
11/10/2016
Initiation aux Bases de Données
16
II.LE MODÉLE CONCEPTUEL ENTITÉS-ASSOCIATIONS:
II.3.LES SCHÉMAS ENTITÉ-ASSOCIATION (suite):
II.3.3.EXEMPLE D'ASSOCIATIONS BINAIRES :
COMMANDER
CLIENTS
→NumClient
→NomClient
→PrenomClient
→AdresseClient
(1,n)
→Quantité
→DateCommande
ARTICLES
(0,m)
→NumArticle
→Designation
→PrixUnitaire
Les CLIENTS COMMANDENT des ARTICLES (ou bien : Les ARTICLES sont commandés par des CLIENTS).
II.3.4.CARDINALITE :
La cardinalité d’une association pour une des entités qu'elle met en relation est un couple de nombres entiers
positifs ou nuls :
● Le premier nombre indique le minimum de fois qu’une occurrence de l’entité participe aux occurrences de
l’association (généralement : 0 ou 1) ;
● Le deuxième nombre indique le maximum de fois qu’une occurrence de l’entité participe aux occurrences de
l’association (généralement 1 ou n) .
Dans l'exemple ci-dessus :
Pour trouver la CARDINALITÉ côté CLIENTS (1,n), on pose les questions:
● Combien d'ARTICLES un CLIENT peut-il commander au minimum ? La réponse est: au moins 1, car sinon il
n'est pas client ;
● Combien d'ARTICLES un CLIENT peut-il commander au maximum ? La réponse est n, car à priori, ce nombre
n'est limité que par le nombre d'articles proposés.
Pour trouver la CARDINALITÉ côté ARTICLES (o,m), on pose les questions:
● Par combien de CLIENTS un ARTICLE peut-il être commandé au minimum ? La réponse est: 0, car un article
peut très bien ne jamais être commandé ;
● Par combien de CLIENTS un ARTICLE peut-il être commandé au maximum ? La réponse est: m, car à priori,
rien ne limite ce nombre (sauf le nombre de clients potentiels, qui est difficile à évaluer).
11/10/2016
Initiation aux Bases de Données
17
II.LE MODÉLE CONCEPTUEL ENTITÉS-ASSOCIATIONS:
II.3.LES SCHÉMAS ENTITÉ-ASSOCIATION (suite):
II.3.4.LES TROIS TYPES D'ASSOCIATIONS BINAIRES :
TYPE UN à UN :
Lorsque, dans une association binaire, les deux cardinalités maximales sont égales à 1, l'association est de
type "un à un" (ou 1:1) . Exemple :
DIRIGER
EMPLOYÉS
SERVICES
Est de type 1:1
(0,1)
(1,1)
TYPE UN à N :
Lorsque, dans une association binaire, une et une seule des deux cardinalités maximales est supérieure à 1,
l'association est de type "un à n" (ou 1:n) . Exemple :
EMPLOYER
SERVICES
EMPLOYÉS
Est de type 1:n
(1,n)
(1,1)
TYPE M à N :
Lorsque, dans une association binaire, les deux cardinalités maximales sont supérieures à 1, l'association est
de type "m à n" (ou m:n) . Exemple :
CONCERNE
COMMANDES
Est de type m:n
(1,m)
11/10/2016
ARTICLES
(0,n)
Initiation aux Bases de Données
18
II.LE MODÉLE CONCEPTUEL ENTITÉS-ASSOCIATIONS:
II.3.LES SCHÉMAS ENTITÉ-ASSOCIATION (suite):
II.3.5.ASSOCIATIONS RELIANT PLUS DE DEUX ENTITÉS :
Une association peut relier plus de deux entités. Par exemple, la relation TERNAIRE ci-dessous:
REMARQUE :
Déterminer les cardinalités des relations reliant plus de deux
entités est assez difficile. Le principe à suivre est le suivant :
Professeurs
- id_pr
- nom_pr
- prénoms_pr
- email_pr
- etc.
0, 1
Enseigner
- matière_en
- durée_en
1, 1
Salles
- numero_sa
- bâtiment_sa
- étage_sa
- type_sa
1, n
Créneaux_Horaires
- IdCréneau_ho
- date_ho
- heure_ho
"Dans une association nere, la cardinalité relative à une entité
correspond au nombre d’occurrences possibles d'entités
associées dans la relation quand les autres ( n-1 ) valeurs sont
fixées" .
Pour l'exemple ci-contre, ceci revient à se poser les questions
suivantes :
● En ce qui concerne l'entité Créneaux_Horaires, combien de
créneaux horaires peuvent être affectées à un professeur
donné dans une salle donnée, au minimum et au maximum ?
(la réponse sera 1,n) ;
● En
ce qui concerne l'entité Professeurs, combien de
professeurs peuvent enseigner dans une salle donnée et à un
horaire donné, au minimum et au maximum ? (la réponse sera
0,1) ;
● En ce qui concerne l'entité Salles, combien de salles peuvent
héberger l'enseignement d'un professeur à un horaire donné,
au minimum et au maximum ? (laréponse sera 1,1).
Rappelons que ces associations complexes sont toujours équivalentes à plusieurs associations binaires. De
ce fait, lors de la conception du modèle d'une BD, il est conseillé de n'utiliser dans un premier temps que des
associations binaires, puis de regrouper ces associations.
11/10/2016
Initiation aux Bases de Données
19
II.LE MODÈLE CONCEPTUEL ENTITÉS-ASSOCIATIONS:
II.4.EXEMPLE DE MCD D'UN SYSTÈME D'INFORMATION:
II.4.1.Remarque préliminaire: Nommage des attributs:
Un MCD peut être constitué de nombreuses entités et associations, chacune pouvant posséder plusieurs
attributs. Il est évidemment indiqué pour la lisibilité des diagrammes de donner à ces attributs, dans la mesure
du possible, des noms évocateurs de leur nature. Cependant, si l'on n'y prend pas garde, cette démarche
pleine de bonnes intentions peut aboutir à donner les mêmes noms à des attributs appartenant à des entités
ou associations différentes.
Par exemple : Des entités CLIENTS et FOURNISSEURS risquent fort de se retrouver dotées toutes les deux
d'attributs nommés e_mail ou adresse_postale.
Ceci n'est pas grave tant qu'on s'en tient au diagramme MCD, puisque ces attributs apparaissent à l'intérieur
de leurs entités ou associations respectives. En revanche, cela pourra poser des problèmes dès qu'on
abordera les niveaux logiques et physiques (par exemple lorsqu'on définira des "clefs étrangères").
Une solution simple à ce problème est de faire référence dans le nom d'un attribut au nom de l'entité ou de
l'association auquel il appartient.
Une méthode utilisée consiste à accoler le nom de l'entité ou de l'association au nom de l'attribut. Par
exemple :
● L'attribut nom de L'entité Clients sera nommé Clients.nom ou Client_nom
● L'attribut email de L'entité Fournisseurs sera nommé Fournisseurs.email ou Fournisseurs_email
Cette méthode est très logique et rigoureuse, mais a l'inconvénient de surcharger les diagrammes et de
produire des noms très longs.
Pour plus de simplicité, on peut se contenter d'ajouter au nom de l'attribut un suffixe rappelant le nom de
l'entité ou de l'association, par exemple:
● L'attribut nom de L'entité Clients sera nommé nom_cl;
● L'attribut email de L'entité Fournisseurs sera nommé email_fo.
C'est cette méthode que nous emploierons par la suite.
11/10/2016
Initiation aux Bases de Données
20
II.LE MODÈLE CONCEPTUEL ENTITÉS-ASSOCIATIONS:
II.4.EXEMPLE DE MCD D'UN SYSTÈME D'INFORMATION (Suite):
II.4.2.Première approche du MCD:
Le modèle conceptuel ci-contre décrit le SYSTÈME D'INFORMATION gérant une BIBLIOTHÈQUE. A ce stade
d'analyse, le MCD est incomplet, puisque les identifiants des entités n'y sont pas encore identifiées.
Ce MCD traduit et synthétise le recueil de
besoins suivant :
● Un AUTEUR est quelqu'un qui écrit des
LIVRES.
● Le
système d'information doit pouvoir
fournir l'identité et la biographie des
auteurs ;
● Dans une bibliothèque, un ouvrage donné
est représenté par un certain nombre
d'EXEMPLAIRES. Il peut aussi ne plus
être représenté si tous les exemplaires
sont perdus ;
● Un
exemplaire d'un livre peut être
emprunté à une date donnée et pour une
durée déterminée. Un emprunt est toujours
effectué par un abonné à la bibliothèque.
Un emprunt concerne un seul exemplaire ;
● A un instant donné, un exemplaire peut
être emprunté au plus par un seul abonné.
11/10/2016
Auteurs
1,m
- nom_au
- prénoms_au
- biographie_au
Ecrire
1,n
Livres
- titre_li
- année_parution_li
- présentation_li
0, n
Correspondre
1,1
Abonnés
- nom_ab
- prénom_ab
- adresse_postale_ab
- email_ab
- téléphone_ab
0, n
Initiation aux Bases de Données
Emprunter
- date_em
- durée_em
0, 1
Exemplaires
- référence_ex
- éditeur_ex
- date_achat_ex
- état_ex
21
II.LE MODÈLE CONCEPTUEL ENTITÉS-ASSOCIATIONS:
II.4.EXEMPLE DE MCD D'UN SYSTÈME D'INFORMATION (Suite):
II.4.2.Première approche du MCD (suite):
Pour traduire ce recueil de besoins, nous
avons:
● Créé l'entité AUTEURS et l'entité LIVRES,
reliées par l'association ECRIRE ;
● Créé l'entité EXEMPLAIRES, distincte de
l'entité LIVRES. Ces deux entités sont
reliées par la relation CORRESPONDRE.
La cardinalité 0,N exprime que la
bibliothèque peut posséder de 0 à N
exemplaires d'un livre. La cardinalité 1.1
exprime qu'un exemplaire correspond à un
livre et un seul ;
● La cardinalité 0,N à l'entrée de la relation
EMPRUNTER exprime qu'un abonné peut
emprunter de 0 à N exemplaires
simultanément ;
● La cardinalité 0,1 à la sortie de la relation
EMPRUNTER exprime qu'un exemplaire ne
peut être emprunté simultanément par
plusieurs abonnés ;
11/10/2016
Auteurs
1,m
- nom_au
- prénoms_au
- biographie_au
Ecrire
1,n
Livres
- titre_li
- année_parution_li
- présentation_li
0, n
Correspondre
1,1
Abonnés
- nom_ab
- prénom_ab
- adresse_postale_ab
- email_ab
- téléphone_ab
Initiation aux Bases de Données
0, n
Emprunter
- date_em
- durée_em
0, 1
Exemplaires
- référence_ex
- éditeur_ex
- date_achat_ex
- état_ex
22
II.LE MODÈLE CONCEPTUEL ENTITÉS-ASSOCIATIONS:
II.4.EXEMPLE DE MCD D'UN SYSTÈME D'INFORMATION (Suite):
II.4.3.Choix des identifiants:
Rappelons que l'identifiant d'une entité:
● Doit permettre d'identifier sans ambiguïté chacune des occurrences de cette entité ;
● Est composé d'un ou plusieurs attributs de l'entité.
Si nous considérons l'entité Auteurs, il est évident que le couple (nom, prénoms) ne peut constituer un
identifiant : en effet, deux auteurs peuvent avoir même nom et même prénom.
Par exemple : Alexandre DUMAS, auteur des Trois Mousquetaires et Alexandre DUMAS, auteur de la Dame au
Camélia (fils du précédent).
Si on ajoutait aux noms et prénoms un attribut "date de naissance" (ex : jour, mois, année), la probabilité que
deux auteurs ait les mêmes valeurs de clef serait infinitésimale … mais pas nulle, et même si, à un moment
donné de l'utilisation de la BD, le triplet (Nom, Prénoms, Date de naissance) suffit pour discriminer toutes les
occurrences de l'entité Auteur, il n'est pas dit que cela suffira dans l'avenir.
Pour qu'un attribut ou un ensemble d'attributs puisse être choisie comme identifiant, il faut donc être
absolument certain que deux occurrences de cette entité ne pourront jamais avoir les mêmes valeurs pour
l'attribut ou les attributs choisis (du moins pendant toute la période d'utilisation prévue pour cette BD).
D'autre part, un identifiant favorisera d'autant plus la rapidité des recherches dans le SGBD qu'il sera simple ou
tout au moins composée d'éléments de structure simple : les identifiants composés d'attributs contenant de
longs textes (par exemple, la bio de l'auteur) auront tendance à augmenter considérablement les actions de
recherche et de sélection (certains SGBD refusent ces types d'attributs pour les clefs primaires). L'identifiant
idéal est simple, numérique et entier (composé d'un seul attribut prenant des valeurs numériques entières).
11/10/2016
Initiation aux Bases de Données
23
II.LE MODÈLE CONCEPTUEL ENTITÉS-ASSOCIATIONS:
II.4.EXEMPLE DE MCD D'UN SYSTÈME D'INFORMATION (Suite):
II.4.3.Choix des identifiants (suite):
Dans le cas (très fréquent) où aucun identifiant ne peut être formé à partir des attributs "naturels" de l'entité, une
solution fréquemment adoptée est d'ajouter à l'entité un attribut supplémentaire composé d'un seul champ entier
positif et de prendre cet attribut comme clef primaire. Ce champ constitue une NUMÉROTATION des
occurrences de l'entité. Par la suite, nous verrons que la valeur de ce champ est entièrement gérée par le SGBD
(incrémentation à chaque création d’occurrence, non ré attribution des numéros des occurrences supprimées).
EXEMPLE : on ajoute à l'entité Auteurs un attribut représentant un entier positif différent pour chaque
occurrence de l'entité (numérotation des occurences) et on prend cette attribut comme clef primaire de l'entité :
Auteurs
- Id_au
- nom_au
- prénoms_au
- biographie_au
Le nouvel attribut Id est souligné
Pour indiquer qu'il s'agit de
L'identifiant de l'entité
REMARQUE : Si cette technique permet d'obtenir à coup sûr un identifiant pour l'entité, elle ne facilite pas la
recherche d'un auteur. En effet, si l'on prend l'exemple étudié, un auteur est en général recherché à partir de
son nom et de son prénom. Si l'on recherche Alexandre DUMAS, il est probable que le SGBD donnera deux
résultats (le père et le fils), et seule la comparaison des contenus des biographies permettra de déterminer celui
que l'on recherche, ce qui, à priori, n'est pas très commode.
On aurait donc intérêt, dans l'exemple étudié et les cas analogues, à ajouter un autre attribut contenant un
élément susceptible de distinguer plus facilement les homonymes. Par exemple, un champ "surnom". Dans le
cas des deux occurrences d'Alexandre DUMAS, cet attribut pourrait prendre les valeurs "père" et "fils".
11/10/2016
Initiation aux Bases de Données
24
II.LE MODÈLE CONCEPTUEL ENTITÉS-ASSOCIATIONS:
II.4.EXEMPLE DE MCD D'UN SYSTÈME D'INFORMATION (Suite):
II.4.4.MCD définitif:
Compte tenu des recommandations énoncées dans les paragraphes I.4.2 et I.4.3, le MCD définitif pourrait se
présenter comme suit :
Auteurs
- Id_au
- nom_au
- prénoms_au
- biographie_au
1,m
Ecrire
1,n
Livres
- Id_li
- titre_li
- année_parution_li
- présentation_li
0, n
Correspondre
1,1
Abonnés
- Id_ab
- nom_ab
- prénom_ab
- adresse_postale_ab
- email_ab
- téléphone_ab
0, n
Emprunter
- date_em
- durée_em
0, 1
Exemplaires
- référence_ex
- éditeur_ex
- date_achat_ex
- état_ex
REMARQUE : Pour définir les identifiants, des attributs supplémentaires Id (numérotation des occurrences) ont
dû être ajoutés aux entités Auteurs, Livres et Abonnés. Ce n'est pas le cas pour l'entité Exemplaires : la
référence d'un exemplaire, attribuée au moment de l'achat, est choisie pour être unique (ex : 2212201507 =
date d'achat (22/12/2015)+ n° de l'achat dans la journée (07) ).
11/10/2016
Initiation aux Bases de Données
25
III.LE MODÈLE LOGIQUE:
III.1.REPRÉSENTATION D'UNE ENTITÉ SOUS FORME DE TABLE:
III.1.1.PRINCIPE :
Les différents représentants d'une entité peuvent être représentés sous forme d'un TABLEAU À DEUX
ENTRÉES dans lequel les colonnes représentent les valeurs des attributs (ou champs) et dont chaque ligne
(entrée) correspond à un représentant de l'entité :
Attribut 1
<nom entité>
Représentant 1 -->
→attribut 1
→attribut 2
→...............
→attribut n
Attribut 2
–-
Attribut n
Valeur 1,1
Représentant 2 -->
Valeur 3,2
Représentant 3 -->
Représentant n -->
III.1.2.EXEMPLE :
MLD Table CLIENTS
MCD Entité CLIENTS
CLIENTS
→Id_cl
→Nom_cl
→Prénom_cl
→Adresse_cl
11/10/2016
Id_cl
nom_cl
prénom_cl
adresse_cl
132
Pasteur
Louis
22 rue Lepic, Paris 5em
(France)
135
Einstein
Albert
32 FritzStrasse 342345
Viennes (Autriche)
137
Newton
Isaac
28 Downing Street
Londres (UK)
167
Pascal
Blaise
17 rue de Vaugirard
Paris 15e (France)
200
Pasteur
Michel
132 rue Volta
Paris 11e (France)
Initiation aux Bases de Données
26
III.LE MODÈLE LOGIQUE:
III.1.REPRÉSENTATION D'UNE ENTITÉ SOUS FORME DE TABLE (Suite):
III.1.3.NOTION DE T-Uplet :
Chaque ligne (ou ENTRÉE) de la table correspond à un représentant de l'entité. Une ligne d'une table peut être
aussi appelée T-uplé. Un T-uplé est l'ensemble des valeurs des attributs caractérisant un représentant de
l'entité.
EXEMPLE :
Id_cl
nom_cl
prénom_cl
adresse_cl
132
Pasteur
Louis
22 rue Lepic, Paris 5em
(France)
135
Einstein
Albert
32 FritzStrasse 342345
Viennes (Autriche)
137
Newton
Isaac
28 Downing Street
Londres (UK)
167
Pascal
Blaise
17 rue de Vaugirard
Paris 15e (France)
200
Pasteur
Michel
132 rue Volta
Paris 11e (France)
La 3eme entrée de cette table a pour
valeur le t-uplet:
(137, "Newton", "Isaac", "28 Downing …")
III.1.4.UNICITE DES VALEURS DES T-Uplets :
Deux entrées d'une même table ne peuvent contenir des valeurs de T-uplets identiques : en effet, si cela se
produisait, cela voudrait dire que ces deux entrées correspondent à la même occurrence de l'entité d'origine. Il
faut donc que, quel que soit le couple d'entrée que l'on choisi dans une même table, ces entrées diffèrent par
les valeurs d'au moins un des champs.
REMARQUE : Si l'entité qui est à l'origine de la table est munie d'au moins un IDENTIFIANT, cette condition est
forcément réalisée, puisque les valeurs de la colonne correspondant à cet identifiant (ici, id_cl) sont par
définition toutes différentes.
11/10/2016
Initiation aux Bases de Données
27
III.LE MODÈLE LOGIQUE:
III.1.REPRÉSENTATION D'UNE ENTITÉ SOUS FORME DE TABLE (Suite):
III.1.5.NOTION DE CLEF PRIMAIRE :
Lorsque les valeurs d'une colonne (ou champ) ou d'un groupe de colonnes (groupe de champs) permettent de
déterminer sans ambiguïté chacune des entrées d'une table, ce champ (ou ce groupe de champs) constitue une
CLEF PRIMAIRE de cette table. Une clef primaire correspond donc à un IDENTIFIANT d'une entité.
Une CLEF PRIMAIRE peut donc, comme un identifiant, être composée d'UN ou PLUSIEURS champs. Ainsi :
● Dans la table qui précède, Id_cl peut être pris comme clef primaire (Dans ce cas, la clef est dite SIMPLE) ;
● On pourrait aussi prendre pour clef primaire le couple d'arguments nom_cl, prénom_cl, si on est sûr qu'aucun
client ne possède (et ne possédera jamais) le même nom et les mêmes prénoms qu'un autre (Dans ce cas, la
clef est dite COMPOSÉE).
III.1.6.NOTION DE CLEF ÉTRANGÈRE :
On appelle CLEF ÉTRANGÈRE un champ d'une table dont les valeurs peuvent être mises en correspondance
avec celles de la CLEF PRIMAIRE d'une autre table. Par exemple:
COMMANDES
CLIENTS
id_cl
nom_cl
Prénoms_cl
adr_cl
1
Lennon
John
ddghdgh
2
Mc Cartney
Paul
fgfgfgfgf
3
Starr
Ringo
fgfgfgfg
num_co
#id_cl
montant_co
date_co
1230
2
3500
22/12/60
1325
1
2100
11/06/61
5678
3
2100
13/02/62
4321
2
1750
01/02/63
#id_cl est une clef étrangère
correspondant à la clef primaire id_cl
Nous pouvons voir dans cet exemple :
● Que Paul Mc Cartney est le titulaire des commandes n°1230 et 4321 ;
● Que #id_cl permet, pour chacune des commandes mémorisées par la table COMMANDES, de retrouver dans la
table CLIENTS l'entrée qui correspond au client concerné par cette commande.
Cet exemple montre donc qu'une CLEF ÉTRANGÈRE permet d'établir une RELATION entre les
entrées
des deux tables.
11/10/2016
Initiation aux Bases de Données
28
III.LE MODÈLE LOGIQUE:
III.1.REPRÉSENTATION D'UNE ENTITÉ SOUS FORME DE TABLE (Suite):
III.1.7.REPRÉSENTATION DES TABLES DANS LES DIAGRAMMES LOGIQUES:
La représentation d'une TABLE dans les diagrammes du MLD est très semblable à celle d'une entité dans le
MCD. La différence est que dans le MLD on ajoute la définition du DOMAINE DE DÉFINITION de chaque
ATTRIBUT. Une TABLE du MLD est souvent appelée RELATION.
Définition : Le DOMAINE d'un ATTRIBUT est l'ensemble de valeurs dans lequel cet attribut prend ses valeurs
(Ensemble de définition de l'argument). Par exemple, dans l'exemple précédent :
●
●
Les attributs nom_cl, prénoms_cl et adresse_cl sont des chaînes de caractères : ils prennent leurs valeurs
dans l'ensemble des chaînes de caractères, souvent noté « string ».
L'attribut num_cl est un nombre entier positif qui peut être assez grand: il prend ses valeurs dans l'ensemble
des entiers représentés sur 4 octets, souvent noté « long » ;
Ces ensembles de définition sont les types de données définis par les langages de programmation :
● Caractère de 1 octet :
char ; (character)
● Entier de 2 octets :
int ; (integer)
● Entier de 4 octets :
long int (ou long) ;
● Nombre avec décimales de 4 octets :
float ; dit (nombre à "virgule flottante"
● Nombre avec décimales de 8 octets :
double ;
● Chaîne de caractères :
string ; (chaîne de caractères)
● Indicateur binaire (0/1) :
bool ; (Boolean)
● Etc.
CLIENTS
Exemple : représentation
de la TABLE (ou RELATION) Clients
11/10/2016
- long
- string
- string
- string
num_cl
nom_cl
prenom_cl
adresse_cl
Initiation aux Bases de Données
Les arguments composant
la CLEF PRIMAIRE
sont soulignés
29
III.LE MODÈLE LOGIQUE:
III.2.REPRÉSENTATION D'UNE ASSOCIATION BINAIRE:
III.2.1.REPRÉSENTATION D'UNE RELATION DE TYPE (1,n) PAR AJOUT D'UNE CLEF ÉTRANGÈRE :
Lorsque une association est de type (1,n), il est possible, si l'association ne porte pas d'attribut, de
représenter simplement l'association par l'ajout d'un attribut dans la table, du côte où la cardinalité maximale est
1.
Exemple :
Le MCD
Se traduit par
Le MLD
Livres
0, n
- Id_li
- titre_li
- année_parution_li
- présentation_li
Correspondre
Livres
- int Id_li
- str titre_li
- str année_parution_li
- str présentation_li
1,1
Exemplaires
- référence_ex
- éditeur_ex
- date_achat_ex
- état_ex
Exemplaires
- str référence_ex
- str éditeur_ex
- str date_achat_ex
- str état_ex
- str #Id_li
Ici, l'attribut id_li, CLEF PRIMAIRE de la table Livres est ajouté dans la table Exemplaires. Il devient une CLEF
ÉTRANGÈRE de cette table qui permet :
● D'une part d'associer chaque exemplaire à un livre;
● D'autre part d'associer à un livre tous les exemplaires disponibles ou empruntés à un moment donné.
Le signe # au début du nom d'un attribut signale que celui-ci est une clef étrangère simple ou appartient à une
clef étrangère composée. Remarquons que la valeur de #id_li doit toujours être définie dans ce cas.
11/10/2016
Initiation aux Bases de Données
30
III.LE MODÈLE LOGIQUE:
III.2.REPRÉSENTATION D'UNE ASSOCIATION BINAIRE (Suite):
III.2.2.REPRÉSENTATION D'UNE RELATION DE TYPE (1,1) PAR AJOUT D'UNE CLEF ÉTRANGÈRE :
Lorsque une association est de type (1,1), il est également possible, si l'association ne porte pas d'attribut,
de représenter simplement l'association par l'ajout d'une clef étrangère
Exemple :
Le MCD
Se traduit par
Le MLD
Patients
- num_SS_pa
- nom_pa
- prénoms_pa
- etc.
soumis
0, 1
Patients
- num_SS_pa
- nom_pa
- prénoms_pa
- etc.
1, 1
Prescriptions
- Id_pr
- contenu_pr
- etc...
Prescriptions
- Id_pr
- contenu_pr
- etc...
- #num_SS_pa
La différence avec le cas des associations de type (1,n) est que la clef étrangère est soumise ici à une condition
d'UNICITE (c'est à dire : plusieurs champs #num_ss_pa de la table Prescription ne peuvent contenir la même
valeur). Ceci revient à dire que deux prescriptions ne peuvent être attribuées au même patient.
11/10/2016
Initiation aux Bases de Données
31
III.LE MODÈLE LOGIQUE:
III.2.REPRÉSENTATION D'UNE ASSOCIATION BINAIRE (Suite):
III.2.3.REPRÉSENTATION PAR UNE TABLE DE JOINTURE DANS LES AUTRES CAS:
Une ASSOCIATION du MCD peut toujours être remplacée par une TABLE appelée TABLE DE JOINTURE.
Exemple :
Le MCD
Se traduit par
Le MLD
Abonnés
- Id_ab
- nom_ab
- prénom_ab
- adresse_postale_ab
- email_ab
- téléphone_ab
Abonnés
- int Id_ab
- str nom_ab
- str prénom_ab
- str adresse_postale_ab
- str email_ab
- str téléphone_ab
0, n
Emprunter
- date_em
- durée_em
Emprunts
- str date_em
- int durée_em
- int #Id_ab
- str #référence_ex
0, 1
Exemplaires
- référence_ex
- éditeur_ex
- date_achat_ex
- état_ex
Exemplaires
- str référence_ex
- str éditeur_ex
- str date_achat_ex
- str état_ex
Table de jointure
La traduction a été réalisée en introduisant une table intermédiaire appelée TABLE DE JOINTURE (c'est la table
Emprunts, que l'on pourrait aussi appeler Abonnés-Exemplaires). Cette table récupère les attributs de
l'association. D'autre part, deux attributs y sont rajoutés :
● L'attribut #id_ab prend les mêmes valeurs que l'attribut id_ab de la table Abonnés: il s'agit d'une CLEF
ÉTRANGÈRE qui permet d'établir la relation entre les relations (ou tables) Abonnés et Emprunts
● De même, l'attribut #référence_ex est ajouté en tant que CLEF ÉTRANGÈRE qui référence la clef primaire
référence_ex de l'entité Exemplaires ;
● Ces clefs étrangères constituent à elles deux la clef primaire de la table de jonction.
11/10/2016
Initiation aux Bases de Données
32
III.LE MODÈLE LOGIQUE:
III.3.REPRÉSENTATION D'UNE ASSOCIATION DE RANG > 2:
En ce qui concerne les association de rang supérieur à 2 (ternaires, quaternaires), on les représente sous la
forme d'une table de jointure qui récupère toutes les clefs primaires des tables correspondant aux entités
associées pour en faire des clefs étrangères. L'ensemble de ces clefs étrangères forme la clef primaire de la
table de jonction.
Exemple (relation ternaire) :
Professeurs
- id_pr
- nom_pr
- prénoms_pr
- email_pr
- etc.
Professeurs
- int id_pr
- str nom_pr
- str prénoms_pr
- str email_pr
- etc.
0, n
Enseigner
- matière_en
- durée_en
0, n
Salles
- numero_sa
- bâtiment_sa
- étage_sa
- type_sa
11/10/2016
0, n
Horaires
- IdCréneau_ho
- date_ho
- heure_ho
Salles
- int numéro_sa
- str bâtiment_sa
- int étage_sa
- str type_sa
Horaires
- int idCréneau_ho
- str date_ho
- int heure_ho
Initiation aux Bases de Données
Cours
- str matière_en
- int durée_en
- int #id_pr
- int #numéro_sa
- int #idCréneau_ho
Table de jonction
33
III.LE MODÈLE LOGIQUE:
III.4.LES FORMES NORMALES:
TRAITEMENT DES GROUPES RÉPÉTITIFS:
Il peut arriver que certaines entités soient décrites avec des groupes d'attributs à caractère RÉPÉTITIF Par
exemple, si on veut qu'une COMMANDE puisse concerner PLUSIEURS ARTICLES, il faudrait pouvoir décrire
la table COMMANDES de la manière suivante (voir schéma de gauche) :
COMMANDES
COMMANDES
- NumCommande
- Date
- NumClient
- NumArticle_1
- QuantitéCommandéeArticle_1
- PrixUnitaireArticle_1
Le formalisme du MCD ne
prenant pas en compte cette
situation de répétitivité, on se
contentera d'écrire une seule
fois le groupe répétitif. Le
problème sera résolu lors de
l'élaboration du MLD.
- NumCommande
- Date
- NumClient
- NumArticle
- QuantitéCommandéeArticle
- PrixUnitaireArticle
- NumArticle_2
- QuantitéCommandéeArticle_2
- PrixUnitaireArticle_2
-------------------------------------------------------
Groupe répétitif
- NumArticle_n
- QuantitéCommandéeArticle_n
- PrixUnitaireArticle_n
11/10/2016
Initiation aux Bases de Données
34
III.LE MODÈLE LOGIQUE:
III.4.LES FORMES NORMALES (suite):
MISE EN PREMIÈRE FORME NORMALE:
Une TABLE est dite EN PREMIÈRE FORME NORMALE (1NF) si elle ne comprend aucun GROUPE
RÉPÉTITIF
Il est toujours possible de remplacer une TABLE (RELATION) qui n'est pas en 1NF par deux tables en 1NF.
Pour cela, il suffit de transférer chaque groupe d'attributs répétitifs dans une nouvelle table dont la clef
primaire est celle de la table d'origine.
Exemple : Lors de la traduction du MCD en MLD, le groupe répétitif (NumArticle, QuantitéCommandée,
PrixUnitaire) sera transférer dans une nouvelle table ARTICLES_COMMANDES :
COMMANDES
COMMANDES
- NumCommande
- Date
- NumClient
- NumArticle
- QuantitéCommandée
- PrixUnitaire
- NumCommande
- Date
- NumClient
Clef
composée
ARTICLES_COMMANDÉS
- NumCommande
- NumArticle
- QuantitéCommandée
- PrixUnitaire
Par cette décomposition, une commande peut donc être associée à n ARTICLES_COMMANDÉS. La répétition
des attributs NumArticle, QuantitéCommandée et PrixUnitaire a été traduite par la relation établie entre la table
COMMANDES et les ARTICLES_COMMANDÉS par le biais du NumCommande.
Les deux tables obtenues sont en PREMIÈRE FORME NORMALE.
11/10/2016
Initiation aux Bases de Données
35
III.LE MODÈLE LOGIQUE:
III.4.LES FORMES NORMALES (suite):
MISE EN DEUXIÈME FORME NORMALE :
La TABLE ARTICLES_COMMANDES, obtenue précédemment en
transformant la table COMMANDES en 1NF a la particularité d'avoir une
CLEF PRIMAIRE composée de deux arguments : NumCommande et
NumArticle. En effet, pour identifier un des articles commandés figurant
dans cette table, il faut savoir à la fois à quelle commande il appartient et
quel article est commandé.
ARTICLES_COMMANDÉS
- NumCommande
- NumArticle
- QuantitéCommandée
- PrixUnitaire
D'autre part, on peut constater que si la QuantitéCommandée dépend à la fois de NumCommande et de
NumArticle, le PrixUnitaire ne dépend que de NumArticle (quelle que soit la commande, le prix d'un article est
le même).
Or, pour qu'une table soit en DEUXIÈME FORME NORMALE (2FN), elle doit remplir les conditions suivantes :
● Être en 1FN (c'est à dire, ne pas contenir d'attributs répétitifs) ;
● Tous ses attributs qui ne sont pas des clefs primaires doivent dépendre de la totalité de la clef.
Ce n'est pas le cas ici de PrixUnitaire qui ne dépend que de NumArticle.
Pour obtenir des tables en 2NF, il suffit de transférer les arguments ne dépendant que d'une partie de la clef
dans une nouvelle table :
ARTICLES_COMMANDÉS
- NumCommande
- NumArticle
- QuantitéCommandée
- PrixUnitaire
11/10/2016
ARTICLES_COMMANDÉS
- NumCommande
- NumArticle
- QuantitéCommandée
ARTICLES_PRIX
- NumArticle
- PrixUnitaire
Initiation aux Bases de Données
36
III.LE MODÈLE LOGIQUE:
III.4.LES FORMES NORMALES (suite):
MISE EN TROISIÈME FORME NORMALE :
Pour qu'une TABLE soit en TROISIÈME FORME NORMALE (3NF), il faut et il suffit ;
● Qu'elle soit en 2NF ;
● Que ses arguments ne soient pas DÉPENDANTS TRANSITIVEMENT les uns des autres.
Explication de la DÉPENDANCE TRANSITIVE :
Supposons que nous ayons la relation suivante :
COMMANDES
- NumCommande
- Date
- NumClient
- NomClient
Le NumClient dépend de NumCommande (Une commande est faite
par un client.
● Le NomClient dépend du NumClient (Un client n'a qu'un seul nom)
De ce fait :
NumCommande ==> NumClient ==> NomClient
Il y a donc une DÉPENDANCE TRANSITIVE entre NomClient et
NumCommande
●
Élimination des dépendances transitives :
Il suffit de transférer dans une autre relation les attributs qui dépendent TRANSITIVEMENT de la clef (ici,
c'est l'attribut NomClient):
COMMANDES
- NumCommande
COMMANDES
- Date
Les
deux
tables
- NumCommande
- NumClient
obtenues sont alors en
- Date
3NF
- NumClient
COMMANDES
- NomClient
- NumClient
- NomClient
11/10/2016
Initiation aux Bases de Données
37
III.LE MODÈLE LOGIQUE:
III.4.LES FORMES NORMALES (Suite):
RÉSUMÉ SUR LES FORMES NORMALES :
- Une RELATION est en PREMIÈRE FORME NORMALE (1NF) SI :
●
Elle ne contient aucun groupe d'attributs RÉPÉTITIF.
- Une RELATION est en DEUXIÈME FORME NORMALE (2NF) SI :
●
●
Elle ne contient aucun groupe d'attributs RÉPÉTITIF (Donc, elle est en 1NF) ;
Tous ses attributs qui ne sont pas des clefs primaires dépendent de l'ensemble des attributs qui forment
cette clef primaire.
Remarque : une relation qui est en 1NF et dont la clef est SIMPLE est forcément en 2NF.
- Une RELATION est en TROISIÈME FORME NORMALE (2NF) SI :
●
●
●
Elle ne contient aucun groupe d'attributs RÉPÉTITIF (Donc, elle est en 1NF) ;
Tous ses attributs qui ne sont pas des clefs primaires dépendent de l'ensemble des attributs qui forment
cette clef primaire (donc, elle est en 2NF);
Aucun de ses attributs n'est DÉPENDANT TRANSITIVEMENT d'un autre de ses attributs.
UTILITÉ DES FORMES NORMALES :
Le passage de toutes les relations en troisième forme normale permet de rendre le MLD plus compréhensible
et plus facilement traduisible en langage de programmation.
De ce fait, l'élaboration du MLD à partir du MCD doit comprendre les étapes suivantes :
● Transformation des ENTITÉS en RELATIONS ;
● Transformation des ASSOCIATION en RELATIONS ou en ARGUMENTS SUPPLÉMENTAIRES ajoutés dans
une des deux relations concernant cette association ;
● Transformation de toutes les relations en 1NF
● Transformation des relations à clef primaire complexe en relations 2NF
● Transformation des relations dont les arguments présentent des dépendances transitives en 3NF.
11/10/2016
Initiation aux Bases de Données
38
III.LE MODÈLE LOGIQUE:
III.5.NOTATIONS ALGEBRIQUES:
III.5.1.ECRITURE ALGEBRIQUE D'UNE RELATION :
Une TABLE peut être représentée sous une forme ALGÉBRIQUE de la manière suivante :
TABLE ==> NomTable ( Attribut1, Attribut 2, ….., Attribut n ) Les attributs qui sont des CLEFS PRIMAIRES sont soulignés, tandis que les CLEFS ÉTRANGÈRES sont
précédées d'un #.
Exemples :
- La table CLIENTS s'écrira:
Clients ( NumClient, NomClient, PrenomClient, AdresseClient);
- La table CLIENTS-ARTICLES s'écrira:
ClientsArticles ( #NumClient, #NumArticle, QuantiteCommandee, Date_Commande);
III.5.2.NOTATION ALGÉBRIQUE DES ATTRIBUTS :
Pour désigner un attribut d'une table donnée, on emploie souvent la notation :
<nom de table>.<nom d'attribut>
Exemple :
L'attribut NomClient de la table CLIENTS sera noté CLIENTS.NomClient.
Cette notation est souvent employée par les langages de gestion de base de données comme SQL.
11/10/2016
Initiation aux Bases de Données
39
III.LE MODÈLE LOGIQUE:
III.6.OPERATIONS SUR LES TABLES:
III.6.1.LES PRINCIPALES OPÉRATIONS PORTANT SUR LES TABLES :
Il s'agit essentiellement des opérations suivantes :
● La recherche d'informations dans une table ;
● L'interclassement des entrées ;
● L'agrégation ;
● La projection ;
● La jointure ;
Ce qui suit décrit le principe logique de ces opérations. Le détail de leur réalisation sera traité lors de l'étude du
Modèle Physique et du langage SQL.
11/10/2016
Initiation aux Bases de Données
40
III.LE MODÈLE LOGIQUE
III.6.OPERATIONS SUR LES TABLES (Suite):
III.6.2.RECHERCHE ET EXTRACTION D'INFORMATIONS DANS UNE TABLE :
Chaque LIGNE (ou ENTRÉE) d'une table du MLD est représentative d'une OCCURRENCE d'une entité ou
d'une association du MCD, alors que chaque COLONNE correspond à une propriété (ou attribut) de cette entité
ou association.
Rechercher des informations dans une table va donc toujours consister en la SÉLECTION d'une "sous-table"
composée des valeurs correspondant à la fois à une collection de LIGNES et à une collection de COLONNES
de cette table. On peut représenter cette démarche par le schéma suivant :
SÉLECTIONNER ET EXTRAIRE LES VALEURS DES ATTRIBUTS <Liste des noms d'attributs>
DANS LES ENTRÉES SATISFAISANT A LA CONDITION <condition>
EXEMPLE :
Soit la table ci-contre :
La recherche et l'extraction des données
concernant à la fois :
● Les colonnes num_commande et nom_client ;
● Les entrées pour lesquelles les montants sont
supérieurs à 250 euros.
Donnera pour résultat la sous-table ci-contre :
Qui contient les cellules de la table qui
correspondent à l'intersection des lignes et des
colonnes sélectionnées.
num_commande
montant
nom_client
20110401
250
Jean SANTERRE
22/12/2010
20110402
329
Louis HONZE
11/08/2015
20120501
230
François POMMIER
03/09/2011
20130903
275
Jean BONNOT
27/08/2013
num_commande
date
nom_client
20110402
Louis HONZE
20130903
Jean BONNOT
Ce qui correspond à :
SÉLECTIONNER ET EXTRAIRE LES VALEURS DES ATTRIBUTS num_commande, nom_client
DANS LES ENTRÉES SATISFAISANT A LA CONDITION montant > 250
11/10/2016
Initiation aux Bases de Données
41
III.LE MODÈLE LOGIQUE
III.6.OPERATIONS SUR LES TABLES (suite):
III.6.3.INTERCLASSEMENT DES ENTRÉES D'UNE TABLE :
Cette opération consiste à modifier l'ordre des entrées d'une table en fonction des valeurs d'un ou de plusieurs
attributs. Ainsi, dans la table :
commande (num_commande, montant, nom_client, date),
Il est probable qu'en l'absence d'action sur leur classement, les entrées vont se retrouver naturellement
classées en fonction de la date de prise en compte des commandes : les premières entrées seront celles qui
ont été enregistrées aux dates les plus anciennes.
On peut, pour des raisons d'efficacité, vouloir reclasser ces entrées selon d'autres critères. Par exemple , si l'on
a besoin de retrouver des commandes en fonction du client, il peut être avantageux de disposer d'un tableau où
les entrées sont classé par ordre alphabétique en fonction des noms de clients, puis en fonction des montants
des commandes passées. Cette transformation se nommera INTERCLASSEMENT en fonction des attributs
nom_client et montant.
num_commande
montant
22/12/2010
20120501
230
François POMMIER
03/09/2011
Louis HONZE
11/08/2015
20110401
250
Jean SANTERRE
22/12/2010
François POMMIER
03/09/2011
20110402
329
Louis HONZE
11/08/2015
num_commande
montant
nom_client
date
20110401
250
Jean SANTERRE
20110402
329
20120501
230
nom_client
date
Interclassement des entrées suivant les
valeurs croissantes de l'attribut 'montant'
REMARQUE : lorsqu'il s'agit d'un traitement informatisé, l'opération d'interclassement agit en général sur la façon
dont la table va être PRÉSENTÉE à l'utilisateur et non sur l'ORDRE RÉEL des entrées : ceci revient à créer une
autre table, reclassée suivant les critères spécifiés. Cette table ne dure que le temps nécessaire à l'exécution du
traitement qui a justifié cet interclassement.
11/10/2016
Initiation aux Bases de Données
42
III.LE MODÈLE LOGIQUE
III.6.OPERATIONS SUR LES TABLES (suite):
III.6.4.AGRÉGATION DE DONNÉES D'UNE TABLE :
Ce type d'opérations a pour caractéristique de porter sur un ENSEMBLE D'ENTRÉES d'une table (les entrées
sont "agrégées" entre elles pour aboutir à un résultat). Par exemple :
● Comptage du nombre d'entrées d'une table ;
● Détermination de l'entrée contenant la valeur la plus ou la moins élevée d'un attribut (exemple : dans la table
des commandes, rechercher la commande dont le montant est le plus élevé) ;
● Détermination du total des valeurs d'un champ numérique
(exemple : dans la table des commandes, calculer
le total des montants) ;
● Etc.
III.6.5.PROJECTION D'UNE TABLE :
PROJETER une table revient à créer une autre table en supprimant certaines colonnes (attributs).
Par exemple, la table :
commande (num_commande, nom_client) est une projection de la table :
commande (num_commande, montant, nom_client, date)
num_commande
montant
20110401
250
Jean SANTERRE
22/12/2010
20110402
329
Louis HONZE
20120501
230
François POMMIER
11/10/2016
nom_client
date
num_commande
nom_client
20110401
Jean SANTERRE
11/08/2015
20110402
Louis HONZE
03/09/2011
20120501
François POMMIER
Projection
Initiation aux Bases de Données
43
III.LE MODÈLE LOGIQUE
III.6.OPERATIONS SUR LES TABLES (suite):
III.6.6.JOINTURE DE TABLES :
JOINTURE DE DEUX TABLES :
Soient deux tables T1 et T2. Supposons que ces deux tables contiennent respectivement un attribut T1.a (pour
T1) et un attribut T2.x (pour T2) correspondant à la même donnée, représentée de la même façon. Dans ce
cas, la JOINTURE de T1 et T2 consiste à juxtaposer les T-uplets des entrées de T1 et T2 pour lesquelles les
valeurs de T1.a et de T2.x sont identiques, les attributs T1.a et T2.x étant fusionnés en une même colonne.
EXEMPLE : le schéma ci-dessous représente la jointure entre les tables 'commandes' et 'clients', par
identification des attributs commandes.nom_client et clients.id_client :
num_commande
montant
nom_client
20110401
250
Jean SANTERRE
20110402
329
Louis HONZE
20120501
230
François POMMIER
Jointure
id_client
Adresse_livraison
Jean SANTERRE
5, rue des lilas ….
François POMMIER
14, impasse Heper...
Léonard DEVINCI
26, rue ...
num_commande
montant
nom_client
Adresse_livraison
20110401
250
Jean SANTERRE
5, rue des lilas
20110402
329
Louis HONZE
….............
20120501
230
François POMMIER
14, impasse Heper...
…..................
….........
Léonard DEVINCI
26, rue ...
Table de jonction
REMARQUE : La jointure représentée ici est COMPLÈTE (car tous les attributs sont repris) et de type "FULL
JOIN" (car toutes les entrées des deux tables sont représentées et les valeurs des attributs qui ne peuvent
être renseignées sont déclarées "non définies").
11/10/2016
Initiation aux Bases de Données
44
III.LE MODÈLE LOGIQUE
III.6.OPERATIONS SUR LES TABLES (suite):
III.6.6.JOINTURE DE TABLES (suite) :
JOINTURE COMPLÈTE ET JOINTURE INCOMPLÈTE :
La jointure est dite COMPLÈTE si la table de jointure reprend les attributs des deux tables. Elle est incomplète
dans le cas contraires (par exemple : Clients_commandes ( nom_client, adresse_livraison ) est une jointure
incomplète des tables clients et commandes.
DIFFÉRENTS MODES DE JOINTURE:
Le fait que les deux attributs de jointure soient identifiés l'un à l'autre de veut pas dire que toutes leurs valeurs
se retrouvent dans les deux tables. De ce fait, différents modes de jointure peuvent être définis :
● FULL JOIN (réunion) si la table de jointure comprend la totalité des entrées des deux tables initiales ;
● INNER JOIN (intersection) si seulement les entrées possédant la même valeur de l'attribut de jonction dans
les deux tables sont pris en compte dans la table de jointure ;
● LEFT JOIN si toutes les entrées de la première tables sont reprises et seulement les entrées de la seconde
possédant une valeur de l'attribut de jonction existant dans la première sont prises en compte dans la table de
jointure ;
● RIGHT JOIN si toutes les entrées de la deuxième tables sont reprises et seulement les entrées de la première
possédant une valeur de l'attribut de jonction existant dans la seconde table sont prises en compte dans la
table de jointure.
JOINTURE DE PLUS DE DEUX TABLES:
La jointure de plus de deux tables s'effectue par une démarche transitive :
Exemple pour 4 tables T1, T2, T3 et T4 :
● On joint T3 et T4 : on note J(T3,T4) la table de jointure obtenue ;
● Puis on joint T2 à J(T3,T4) : on note J(T2, J(T3, T4)) le résultat obtenu ;
● Puis on joint T1 à J(T2, J(T3,T4))
11/10/2016
Initiation aux Bases de Données
45
III.LE MODÈLE LOGIQUE
III.6.OPERATIONS SUR LES TABLES (suite):
III.6.6.JOINTURE DE TABLES (suite) :
UTILISATION DE LA JOINTURE DE DEUX TABLES:
Prenons d'abord l'exemple des deux tables suivantes :
● commandes (num_commande, #num_client, date ) ;
● clients (num_client, adresse_mail).
CLIENTS
- num_client
- adresse_mail
COMMANDES
- num_commande
- #num_client
- date
La clef étrangère #num_client de la table commandes fait référence à la clef primaire num_client de la table
clients. Elle traduit l'association "clients_commandes" entre les deux entités clients et commandes. Cette
association est de type (1, n) : elle peut donc être représentée uniquement par l'adjonction de la clef étrangère
num_client à la table commandes.
Une jointure incomplète entre les deux tables par identification des attributs commandes.#num_client et
clients.num_client permet d'obtenir la table de jointure suivante :
clients_commandes (num_client, num_commande, date )
Par une simple recherche dans cette table de jointure, il est possible de lister toutes les commandes effectuées
par un client donné. Cette table donc de mettre en œuvre l'association clients_commandes.
UTILISATION DE LA JOINTURE DE PLUS DE DEUX TABLES:
Considérons maintenant les 3 tables suivantes :
● etudiants ( id_etudiant, e_mail, password ) ;
● formations ( id_formation, contenu ) ;
● inscrits ( #id_etudiant, #id_formation ) .
ETUDIANTS
- id_etudiant
- email
- password
INSCRITS
- #id_formation
- #id_etudiant
FORMATIONS
- id_formation
- contenu
La table "inscrits" traduit l'association entre les étudiants et les formations auxquelles ils sont inscrits (cette
relation est de type (m,n) car un étudiant peut être inscrit à plusieurs formations et une formation peut être
dispensée à plusieurs étudiants). Si nous opérons la jointure des tables "formations" et "inscrits" suivant les
attributs id_formation et #id_formation, puis la jointure de la table obtenue avec la table "etudiants" (suivant
id_etudiant et # id_etudiant), nous obtenons la table de jointure (incomplète) :
etudiants_formations ( id_etudiant, id_formation, contenu )
Qui permet de retrouver toutes les formations suivies par un étudiant et leurs contenus.
11/10/2016
Initiation aux Bases de Données
46
III.LE MODÈLE LOGIQUE
III.7.EXEMPLE DE PASSAGE DU MODÉLE CONCEPTUEL AU MODÈLE LOGIQUE:
III.7.1.PROBLÊME À RÉSOUDRE :
Reprenons le schéma conceptuel précédent :
Auteurs
- id_au
- nom_au
- prénoms_au
- biographie_au
1,m
Ecrire
1,n
Livres
- id_li
- titre_li
- année_parution_li
- présentation_li
0, n
Correspondre
1,1
Abonnés
- id_ab
- nom_ab
- prénom_ab
- adresse_postale_ab
- email_ab
- téléphone_ab
0, n
Emprunter
- date_em
- durée_em
0, 1
Exemplaires
- référence_ex
- éditeur_ex
- date_achat_ex
- état_ex
D'après les paragraphes précédents, pour déduire le MLD correspondant à ce MCD, nous devons :
● Transformer les entités en TABLES ;
● Remplacer les ASSOCIATIONS de type 1 :n et 1:1 non porteuses d'attributs par des échanges de CLEFS
ÉTRANGÈRES entre les entités qu'elles relient ;
● Remplacer les autres associations par des TABLES DE JONCTION ;
● Transformer toutes les relations (tables) ainsi définies en troisième forme normale.
11/10/2016
Initiation aux Bases de Données
47
III.LE MODÈLE LOGIQUE
III.7.EXEMPLE DE PASSAGE DU MODÉLE CONCEPTUEL AU MODÈLE LOGIQUE (Suite):
III.7.2.Transformation des entités du MCD en relations (tables) du MLD :
Cette transformation ne pose, à priori, pas beaucoup de problèmes : à chaque occurrence de l'entité
correspond une LIGNE de la table alors qu'à chaque attribut correspond une colonne.
Exemple, pour l'entité Auteur:
Auteurs
- int id_au
- str nom_au
- str prénoms_au (str)
- str surnom_au
- text biographie_au
Id_au
nom_au
Prénoms-au
surnom_au
biographie_au
1
HUGO
Victor
aucun
"né à Besançon en 1802, ..."
2
DUMAS
Alexandre
Père
"écrivain prolifique, Alexandre …"
3
POQUELIN
Jean-Baptiste
Molière
"Jean Baptiste POQUELIN, dit
Molière, est …"
4
SARTRE
Jean-Paul
aucun
" …........"
REMARQUE : dans cette table, l'attribut id_au est une clef primaire auto incrémentées par le SGBD à
chaque définition d'une nouvelle entrée dans la table. Supposons que l'entrée d'Id_au = 2 soit supprimée,
puis qu'une nouvelle entrée soit créée : le numéro 2 ne sera jamais réattribué par le SGBD et le nouveau
contenu sera :
Nouvelle entrée
11/10/2016
Id_au
nom (str)
prénoms (str)
surnom (str)
biographie (txt)
1
HUGO
Victor
aucun
"né à Besançon en 1802, ..."
3
POQUELIN
Jean-Baptiste
Molière
"Jean Baptiste POQUELIN, dit
Molière, est …"
4
SARTRE
Jean-Paul
aucun
" …........"
5
BEYLE
Henri
Stendhal
" …........"
Initiation aux Bases de Données
48
III.LE MODÈLE LOGIQUE
III.7.EXEMPLE DE PASSAGE DU MODÉLE CONCEPTUEL AU MODÈLE LOGIQUE (Suite):
III.7.3.Transformation des associations du MCD:
A-RELATIONS DE TYPE 1:N NON PORTEUSES D'ATTRIBUTS :
Dans l'exemple, c'est le cas de la relation Correspondre. Cette relation peut être remplacée par l'ajout de la
clef id_li de l'entité Livre en tant que clef étrangère dans la table Exemplaire. On obtient donc :
Livres
- Id_li
- titre_li
- année_parution_li
- présentation_li
0, n
Livres
- int Id_li
- str titre_li
- str année_parution_li
- txt présentation_li
Correspondre
Exemplaires
- int référence_ex
- str éditeur_ex
- str date_achat_ex
- str état_ex
- int #Id_li
1,1
Exemplaires
- référence_ex
- éditeur_ex
- date_achat_ex
- état_ex
MLD
MCD
11/10/2016
Initiation aux Bases de Données
49
III.LE MODÈLE LOGIQUE
III.7.EXEMPLE DE PASSAGE DU MODÉLE CONCEPTUEL AU MODÈLE LOGIQUE (Suite):
III.7.4.Transformation des associations du MCD (suite):
B-RELATIONS DE TYPE 1:N PORTEUSES D'ATTRIBUTS :
Dans l'exemple, c'est le cas de la relation Emprunter. Cette relation peut être remplacée par une table de
jonction :
MCD
Abonnés
- Id_ab
- nom_ab
- prénoms_ab
- adresse_postale
- e_mail
- téléphone
0, n
Emprunter
- date_em
- durée_em
1,1
Exemplaires
- référence_ex
- éditeur_ex
- date_achat_ex
- état_ex
MLD
Abonnés
- int Id_ab
- str nom_ab
- str prénoms_ab
- str adresse_postale
- str e_mail
- str téléphone
11/10/2016
Emprunts
- str date_em
- int durée_em
- int #Id_ab
- str #référence_ex
Exemplaires
- str référence_ex
- str éditeur_ex
- str date_achat_ex
- str état_ex
Table de jonction
Initiation aux Bases de Données
50
III.LE MODÈLE LOGIQUE
III.7.EXEMPLE DE PASSAGE DU MODÉLE CONCEPTUEL AU MODÈLE LOGIQUE (Suite):
III.7.5.Transformation des associations du MCD (suite):
C-RELATIONS DE TYPE M:N:
Dans l'exemple, c'est le cas de la relation écrire. Cette relation peut également être remplacée par une table
de jonction :
MCD
Auteurs
- id_au
- nom_au
- prénoms_au
- biographie_au
Livres
1, n
Ecrire
1,n
- id_li
- titre_li
- année_parution_li
- présentation_li
MLD
Auteurs
- int id_au
- str nom_au
- str prénoms_au
- txt biographie_au
Auteurs_Livres
- int #id_au
- int #id_li
Livres
- int id_li
- str titre_li
- int année_parution_li
- txt présentation_li
Table de jonction
REMARQUE : la relation Ecrire est de type m;n car nous avons admis la possibilité qu'un livre soit écrit par
plusieurs auteurs : c'est la signification de la cardinalité 1,n côte droit. Sinon, la relation serait 1:n et nous
n'aurions pas besoin de cette table de jonction.
11/10/2016
Initiation aux Bases de Données
51
III.LE MODÈLE LOGIQUE
III.7.EXEMPLE DE PASSAGE DU MODÉLE CONCEPTUEL AU MODÈLE LOGIQUE (Suite):
III.7.6.MLD COMPLET:
Compte tenu de ce qui précède , le MLD de l'exemple peut être représenté comme suit :
Auteurs
- int id_au
- str nom_au
- str prénoms_au
- txt biographie_au
Abonnés
- int id_ab
- str nom_ab
- str prénoms_ab
- str adresse_postale_ab
- str e_mail_ab
- str téléphone-ab
11/10/2016
Auteurs_Livres
- int #id_au
- int #id_li
Emprunts
- str date_em
- int durée_em
- int #Id_ab
- str #référence_ex
Initiation aux Bases de Données
Livres
- int id_li
- str titre_li
- int année_parution_li
- txt présentation_li
Exemplaires
- int #Id_li
- str référence_ex
- str éditeur_ex
- str date_achat_ex
- str état_ex
52
IV.LE MODÈLE PHYSIQUE DES DONNÉES (MPD):
IV.1.DEFINITION DU MPD:
Le MODÈLE PHYSIQUE DES DONNÉES (MPD) est représenté par la traduction du MODÈLE LOGIQUE DE
DONNÉES (MLD) dans le langage informatique accepté par le SGBD. Il s'agit donc de l'ensemble des
REQUÊTES exprimées dans ce langage qu'il faut envoyer au SGBD pour que celui-ci crée effectivement la
base de données définie par le modèle logique sur le support de données choisi.
Quant à la base de données, elle sera effectivement créée par le SGBD, en fonction de ces requêtes, sous la
forme d'un certain nombre de fichiers contenant :
● Les données décrivant l'organisation générale de la BD ;
● Les données contenues dans chacune des tables définies par le MLD ;
● Les données décrivant l'organisation interne de ces tables (formats de données, moteurs de BD employés,
définition de clefs primaires, etc.) ;
● Les données décrivant les liens, dépendances et contraintes définies entre les tables.
MPD
BASE DE DONNÉES
SGBD
(ensemble de requêtes Requêtes
écrites dans le langage
de gestion de BD choisi)
Comptes-rendus
Interprétation des
requêtes et
exécution des
actions
correspondantes
Retour des
comptes rendus
d'exécution
(ensemble de fichiers stockés
sur disque dur)
Fichier décrivant l'organisation
générale de la BD ;
● Fichiers
représentatifs de la
structure et du contenu des
tables ;
● Fichiers
décrivant les liens,
dépendances et contraintes
définies entre les tables.
●
Le MODÈLE PHYSIQUE DES DONNÉES (MPD) peut donc être assimilé à un PROGRAMME (ou un fragment
de programme) écrit dans un langage accepté par le SGBD et dont l'exécution par celui-ci provoque la
CRÉATION de la base de données.
11/10/2016
Initiation aux Bases de Données
53
IV.LE MODÈLE PHYSIQUE DES DONNÉES (MPD):
IV.2.LES SYSTÈMES DE GESTION DE BASES DE DONNÉES:
Nous avons vu précédemment que l'IMAGE PHYSIQUE d'une base de données sur son support de stockage
est constituée d'un ensemble de FICHIERS informatiques.
Un Système de Gestion de Bases de données (SGBD) est un PROGICIEL qui encapsule l'ensemble des
fonctions qui permettent à un UTILISATEUR de gérer les différents fichiers qui constituent l'image physique
d'une base de données. L'utilisateur peut être :
● Soit une APPLICATION INFORMATIQUE (programme utilisateur), qui communique avec le SGBD par
l'intermédiaire d'une INTERFACE DE PROGRAMMATION (ou Application Programming Interface-API) ;
● Soit un OPÉRATEUR HUMAIN qui communique avec le SGBD l'intermédiaire d'un Interface Homme-Machine
(IHM).
En règle générale, un SGBD englobe son propre IHM d'utilisation. En revanche, l'API dépend à la fois du SGBD
et du langage utilisé par l'application informatique utilisatrice. Pour un même SGBD, on aura donc une API pour
le langage C, un autre pour JAVA, etc. Ces API ne sont donc pas intégrées au SGBD:
Application Utilisatrice
Opérateur Humain
A.P.I
I.H.M
Progiciel
SGBD
Moteur de Base de Données
(Fonctions de gestion de la B.D)
Support physique
de la BD
(ex : disque dur.
11/10/2016
Fichier BD
Fichier BD
Fichier BD
Fichier BD
Initiation aux Bases de Données
Image physique
De la B.D
Sur son support
de stockage
54
IV.LE MODÈLE PHYSIQUE DES DONNÉES (MPD):
IV.3.LES A.P.I DE GESTION DE BASES DE DONNEES:
Une API de gestion de bases de données se présente comme une bibliothèque de fonctions activables depuis
un logiciel écrit dans un langage de programmation donné (C++, PHP, JAVA, etc). Ces fonctions permettent à
une application utilisatrice d'expédier vers le SGBD des REQUÊTES permettant de créer des BASES DE
DONNÉES et des TABLES et d'en manipuler dynamiquement les contenus. Elles permettent également à
l'application utilisatrice de récupérer les réponses du SGBD aux requêtes envoyées.
Le langage de requêtes le plus utilisé dans le domaine des SGBD relationnels est le S.Q.L (Structured Query
Langage – Langage de requête structuré) :
APPLICATION UTILISATRICE
COMMENTAIRES :
L'application utilisatrice de la B.D. fait appel aux
fonctions offertes par l'A.P.I. (choisi en fonction
Fonction
Fonction
Fonction
du langage de programmation utilisé par
n
1
2
l'application) ;
● Ces
fonctions expédient vers le SGBG des
A.P.I.
REQUÊTES dans le LANGAGE DE REQUÊTES
Requête n
Requête 1 Requête 2
utilisé par l'API (le plus répandu est SQL) ;
● Le
composant principal d'un SGBD est le
MOTEUR DE BASE DE DONNÉES (database
S.G.B.D
engine). C'est ce composant qui se charge
Moteur de
d’enregistrer les données dans la BD ou d'en
Base de données
extraire des données. A ce titre, il assure le
respect des règles d'accès, de confidentialité, de
cohérence qui définissent le schéma conceptuel
Base de Données
TABLE
TABLE
de cette BD ;
TABLE
Base de Données
TABLE
● En fonction des requêtes reçues, le moteur de
Base de Données TABLE
TABLE
TABLE
TABLE
BD du SGBD manipule les BD et les TABLES
TABLE
TABLE
que celles-ci contiennent (création, modification,
destruction, lecture de données, etc.) et retourne
le résultat des requêtes vers les utilisateurs.
11/10/2016
Initiation aux Bases de Données
55
●
IV.LE MODÈLE PHYSIQUE DES DONNÉES (MPD):
IV.4.LES I.H.M DE GESTION DE BASES DE DONNEES:
Ces I.H.M sont, en général, intégrés à la fourniture du SGBD. Ils offrent un interface graphique qui permet de
créer des bases de données relationnelles et d'en gérer le contenu. Les principales fonctions supportées sont :
• Création, suppression d'une BASE DE DONNÉES, définition et modification du paramétrage général ;
• Création, suppression d'une TABLE, définition et modification des paramètres généraux ;
• Gestion du CONTENU D'UNE TABLE : ajout ou suppression d'entrées, définition, modification des types et
des contenus des champs, suppression de champs, définition de clefs primaires et d'index, etc.
• Définition, suppression de CONTRAINTES ENTRE LES ENTRÉES DE TABLES (clefs étrangères) ;
• Etc.
L'image ci-dessous représente l'IHM du SGBD MySQL ouvert sur l'affichage de la base de données
Bibliothèque, définie dans l'exemple utilisé précédemment :
11/10/2016
Initiation aux Bases de Données
56
IV.LE MODÈLE PHYSIQUE DES DONNÉES (MPD):
IV.4.LES I.H.M DE GESTION DE BASES DE DONNEES (Suite):
L'image ci-dessous représente l'IHM du SGBD MySQL ouvert sur l'affichage de la structure de la table "auteurs"
de la base de données Bibliothèque, définie dans l'exemple utilisé précédemment :
Permet de définir
le champ id_au
en tant que clef
primaire
Permet de définir le champ
prenoms_au en tant que
Chaine de 100 caractères
Permet de définir
le champ
id_au en tant
qu'index
Permet de définir
le champ id_au
En tant qu'entier non nul
S'auto-incrémentant
REMARQUE : les mentions "Unique", "Index" et "Spatial" qui apparaissent dans les options de définition des
champs se rapportent à la notion d'INDEXATION. Déclarer un champ en tant qu'INDEX permet de mettre en
place un mécanisme qui accélère la recherche des données dans la table en question. Un index est UNIQUE si
toutes ses valeurs sont distinctes les unes des autres. Une CLEF PRIMAIRE est un INDEX UNIQUE.
11/10/2016
Initiation aux Bases de Données
57
IV.LE MODÈLE PHYSIQUE DES DONNÉES (MPD):
IV.5.MODÈLE PHYSIQUE DE LA BASE DE DONNÉES BIBLIOTHÊQUE:
La liste de requêtes ci-dessous représente le programme SQL qui permet de créer la base de données
'bibliothèque' dont nous avons élaboré précédemment le MCD et le MLD. Les commentaires insérés donnent
une explication des traitements effectués :
--- Création de la base de données 'bibliothèque'(si elle n'existe pas), avec comme option l'utilisation
-- par défaut du jeu de caractères latin 1 (ISO-8859-1) et du type d'interclassement alphabétique
–- latin1_swedish_ci (ordre alphabétique utilisé en France).
–CREATE DATABASE IF NOT EXISTS `bibliotheque` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
-–- Spécifie que les requêtes qui suivent s'adressent à la base de données bibliothèque
-USE `bibliotheque`;
––- Première ligne: création de la table 'abonnes'
–- Lignes suivantes: définition de ses champs 'id_ab, 'nom_ab', 'prenoms_ab', 'adresse_postale-ab',
–- 'e_mail_ab' et 'telephone_ab' (champs. Pour chacun des champs, son type de donnée est défini (int(8),
–- varchar(100), etc.
–- id_ab est défini comme une clef primaire (mot clef PRIMARY KEY) autoincrémentée (mot clef
-– AUTOINCREMENT)
–- La dernière ligne définit : le moteur de BD à utiliser (ici : innoBD), le jeu de caractère par défaut
–- (latin 1) et la valeur d'auto-incrémentation à appliquer pour la table (1).
–CREATE TABLE IF NOT EXISTS `abonnes` (
`id_ab` int(8) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Id. des abonnés',
`nom_ab` varchar(100) NOT NULL COMMENT 'nom de l''abonné',
`prenoms_ab` varchar(100) NOT NULL COMMENT 'prénoms de l''abonné, séparés par des virgules',
`adresse_postale_ab` varchar(255) NOT NULL COMMENT 'adresse postale de l''abonné',
`e_mail_ab` varchar(30) NOT NULL COMMENT 'adresse e_mail de l''abonné',
`telephone_ab` int(30) NOT NULL COMMENT 'numéro de téléphone de l''abonné',
PRIMARY KEY (`id_ab`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Table des abonnée' AUTO_INCREMENT=1 ;
11/10/2016
Initiation aux Bases de Données
Suite du programme -->
58
IV.LE MODÈLE PHYSIQUE DES DONNÉES (MPD):
IV.5.MODÈLE PHYSIQUE DE LA BASE DE DONNÉES BIBLIOTHÊQUE:
La liste de requêtes ci-dessous représente le programme SQL qui permet de créer la base de données
'bibliothèque' dont nous avons élaboré précédemment le MCD et le MLD :
––- Les 3 requêtes suivantes définissent la structure des tables 'auteurs', 'auteurs_livres' et
–- 'emprunts'. Les mots clefs KEY utilisés pour les deux dernières permettent d'associer à certains
-– champs des INDEX : dans ce cas, cela est nécessaire pour que ces champs puissent être définis plus
–- tard comme des CLEFS ETRANGERES
-CREATE TABLE IF NOT EXISTS `auteurs` (
`id_au` int(8) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Clef auteurs',
`nom_au` varchar(100) NOT NULL COMMENT 'Nom de l''auteur',
`prenoms_au` varchar(100) NOT NULL COMMENT 'prénoms de l''auteur, séparés par des virgules',
`biographie_au` text NOT NULL COMMENT 'biographie de l''auteur',
PRIMARY KEY (`id_au`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Table des auteurs' AUTO_INCREMENT=1 ;
CREATE TABLE IF NOT EXISTS `auteurs_livres` (
`fk_id_au` int(8) unsigned NOT NULL,
`fk_id_li` int(8) unsigned NOT NULL,
KEY `fk_id_au` (`fk_id_au`),
KEY `fk_id_li` (`fk_id_li`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Table de jonction auteurs-livres (association "ecrire"';
CREATE TABLE IF NOT EXISTS `emprunts` (
`date_em` varchar(30) NOT NULL COMMENT 'Date d''emprunt',
`duree-em` int(2) NOT NULL COMMENT 'durée en semaines de l''emprunt',
`fk_id_ab` int(8) unsigned NOT NULL COMMENT 'Clef étrangère (id. de l''abonné emprunteur)',
`fk_reference_ex` varchar(30) NOT NULL COMMENT 'clef étrangère (référence de l''exemplaire emprunté)',
KEY `fk_id_ab` (`fk_id_ab`),
KEY `fk_reference_ex` (`fk_reference_ex`)
Suite du programme -->
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Table des emprunts en cours ';
11/10/2016
Initiation aux Bases de Données
59
IV.LE MODÈLE PHYSIQUE DES DONNÉES (MPD):
IV.5.MODÈLE PHYSIQUE DE LA BASE DE DONNÉES BIBLIOTHÊQUE(Suite):
Suite du programme :
–––-–
–--
Les 2 requêtes suivantes définissent la structure des tables 'exemplaires', et 'livres' et
'emprunts'. Les mots clefs KEY utilisés pour les deux dernières permettent d'associer à certains
champs des INDEX : dans ce cas, cela est nécessaire pour que ces champs puissent être définis plus
tard comme des CLEFS ETRANGERES
CREATE TABLE IF NOT EXISTS `exemplaires` (
`reference_ex` varchar(30) NOT NULL COMMENT 'référence de l''exemplaire: <nom livre>/<date
acquisition>/<numéro acquisition> ',
`editeur_ex` varchar(255) NOT NULL COMMENT 'éditeur de l''exemplaire',
`date_achat_ex` varchar(30) NOT NULL COMMENT 'date d''achat',
`etat_ex` varchar(20) NOT NULL COMMENT 'état (neuf, bon, moyen, médiocre, a changer',
`fk_id_li` int(8) unsigned NOT NULL COMMENT 'Clef étrangère (Id du livre correspondant à
l''exemplaire)',
PRIMARY KEY (`reference_ex`),
KEY `fk_id_li` (`fk_id_li`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Table des exemplaires de livres en circulation';
CREATE TABLE IF NOT EXISTS `livres` (
`id_li` int(8) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Id du livre',
`titre_li` varchar(255) NOT NULL COMMENT 'titre du livre',
`annee_parution_li` varchar(20) NOT NULL COMMENT 'année de première parution',
`presentation_li` text NOT NULL COMMENT 'présentation sommaire du livre',
PRIMARY KEY (`id_li`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Table des livres' AUTO_INCREMENT=1 ;
Suite du programme -->
11/10/2016
Initiation aux Bases de Données
60
IV.LE MODÈLE PHYSIQUE DES DONNÉES (MPD):
IV.5.MODÈLE PHYSIQUE DE LA BASE DE DONNÉES BIBLIOTHÊQUE(Suite):
Suite du programme :
––- Enfin, les directives qui suivent permettent de définir dans les tables 'auteurs_livres', 'emprunts'
-– et 'exemplaires' les champs qui sont des clefs étrangères (mots clefs 'ADD CONSTRAINT' et
– 'FOREIGN KEY'.
-ALTER TABLE `auteurs_livres`
ADD CONSTRAINT `auteurs_livres_ibfk_2` FOREIGN KEY (`fk_id_li`) REFERENCES `livres` (`id_li`),
ADD CONSTRAINT `auteurs_livres_ibfk_1` FOREIGN KEY (`fk_id_au`) REFERENCES `auteurs` (`id_au`);
ALTER TABLE `emprunts`
ADD CONSTRAINT `emprunts_ibfk_2` FOREIGN KEY (`fk_reference_ex`) REFERENCES `exemplaires`
(`reference_ex`),
ADD CONSTRAINT `emprunts_ibfk_1` FOREIGN KEY (`fk_id_ab`) REFERENCES `abonnes` (`id_ab`);
ALTER TABLE `exemplaires`
ADD CONSTRAINT `exemplaires_ibfk_1` FOREIGN KEY (`fk_id_li`) REFERENCES `livres` (`id_li`);
REMARQUE n°1 :
Ce programme crée la STRUCTURE de la BD et non son contenu. Les différents champs sont créés à leurs
valeurs par défaut. Ce programme est donc représentatif du M.P.D de la base ;
REMARQUE n°2 :
Cette structure de base de données peut être créée de deux façons :
● Soit en transmettant ces requêtes depuis une application utilisatrice en utilisant un API;
● Soit en utilisant
les commandes supportées par l'IHM associé au SGBD : dans ce cas, les commandes
passées via l'IHM engendrent les mêmes requêtes SQL et les transmettent au moteur de base de données du
SGBD. En général, l'IHM permet de visualiser les requêtes SQL qu'il produit lors de chaque commande. Le
code SQL étudié ci-dessus est le code engendré par l'IHM du SGD MySQL lorsqu'on crée la base de données
'bibiliotheque" ;
Très souvent, l'état initial de la base de données est créé par l'intermédiaire de l'IHM, les évolutions ultérieures
du contenu et de la structure de la BD étant gérées dynamiquement par l'application utilisatrice.
11/10/2016
Initiation aux Bases de Données
61
V.APERÇU DU LANGAGE SQL:
V.1.INTRODUCTION:
V.I.1.STRUCTURE GÉNÉRALE DES REQUÊTES SQL :
Comme nous avons pu le constater au chapitre précédent, les REQUÊTES SQL se présentent sous la forme de
chaînes de caractères constituées de la concaténation de mots clef (que l'on écrit en général en majuscules) et
de valeurs de paramètres se rapportant à ces mots clefs.
Par exemple, la requête de création de la base de données 'bibliotheque' peut s'écrire :
CREATE DATABASE IF NOT EXISTS `bibliotheque`
DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci
●
●
●
●
●
●
●
Le mot clef CREATE indique que la requête concerne la création d'un élément ;
Le mot clef DATABASE précise qu'il s'agit de la création d'une base de données (une requête CREATE peut
aussi concerner une TABLE) ;
La suite de mots clefs IF NOT EXISTS indique que la création de la BD est conditionnée au fait qu'aucune BD
de ce nom n'est connue du SGBD :
La valeur de paramètre 'bibliothèque' qui suit indique le nom de la BD à créer ;
La suite de mots clefs DEFAULT CHARACTER SET indique que ce qui la suit est le nom du jeu de caractères
associé par défaut à la BD (c'est à dire aux différents champs textuels) qui seront créés par la suite dans cette
table) ;
La valeur de paramètre latin1 spécifie le nom du jeu de caractères choisi (latin1 est une constante prédéfinie);
Le mot clef COLLATE indique que ce qui suit est le mode d'interclassement (ordre alphabétique) par défaut. Ici,
il s'agit de latin1_swedish_ci, qui est associé au jeu de caractères latin1 (jeu ISO-8859-1).
REMARQUE : Notre intention n'est pas de présenter ici un manuel de référence de SQL, que l'on peut par
ailleurs trouver facilement en ligne sur le web. Les pages qui suivent visent à donner au lecteur une
connaissance globale du langage SQL structurée en fonction des traitements à accomplir (création de la
structure d'une BD, création ou modification du contenu, recherche de données dans une BD, etc.). Les
différentes requêtes sont décrites uniquement dans leurs formes fondamentales.
11/10/2016
Initiation aux Bases de Données
62
V.APERÇU DU LANGAGE SQL:
V.1.INTRODUCTION (suite):
V.I.1.CONVENTIONS UTILISÉES POUR DÉCRIRE LA STRUCTURE DES REQUÊTES :
Pour décrire la structure générale de chaque requête, nous utiliserons les conventions suivantes :
A-Le texte inclus entre deux chevrons (< ... >) désigne un élément que l'on décrira plus tard. Exemple : dans une
requête CREATE DATABASE, '<identifiant du jeu de caractères par défaut>' peut être remplacé par 'latin1'.
B-Les crochets ([…]) désignent des éléments optionnels. Par exemple, dans une requête CREATE, on peut
omettre IF NOT EXIST : on peut donc écrire dans la structure logique de CREATE DATABASE :
CREATE DATABASE [IF NOT EXISTS] '<nom de la BD>'.
C-Le caractère / permet d'écrire des listes d'options entre lesquelles il est possible de choisir lors de l'écriture de
la requête. Par exemple, dans le schéma logique d'une requête CREATE DATABASE, on pourra trouver :
DEFAULT CHARACTER SET ascii/binary/latin1/latin2
la liste ascii, binary, latin1, latin2 indiquant les jeux de caractères qui peuvent être choisis.
D-Des crochets peuvent englober d'autres crochets. Par exemple, dans une requête CREATE DATABASE, le mot
clef COLLATE ne peut être présent que si un jeu de caractère par défaut a été défini. On écrira donc la
structure logique de la manière suivante :
[ DEFAULT CHARACTER SET <jeu de caractère> [ COLLATE <type d'interclassement> ] ]
Ce qui signifie que l'option COLLATE est une sous-option de DEFAULT CHARACTER SET.
Ceci donne, pour la description logique de CREATE DATABASE :
CREATE DATABASE [IF NOT EXISTS] '<nom de la bd>'
[DEFAULT CHARACTER SET <jeu de caractère> [ COLLATE <type d'interclassement> ]]
11/10/2016
Initiation aux Bases de Données
63
V.APERÇU DU LANGAGE SQL:
V.2.CREATION DE LA STRUCTURE D'UNE BASE DE DONNEES:
V.2.1.CRÉATION DE LA BASE DE DONNEES ELLE-MÊME :
Nous avons déjà abordé cette requête dans les pages précédentes. La structure logique d'une requête CREATE
DATABASE, réduite aux options fondamentales) peut s'écrire ainsi :
CREATE DATABASE [IF NOT EXISTS] `bibliotheque`
[DEFAULT CHARACTER SET <nom du jeu de caractères>
[COLLATE <Nom de l'interclassement utilisé>] ]
Rappellons que :
● Le mot clef CREATE indique que la requête concerne la création d'un élément ;
● Le mot clef DATABASE précise qu'il s'agit de la création d'une base de données (une requête CREATE peut
aussi concerner une TABLE) ;
● La suite de mots clefs IF NOT EXISTS indique que la création de la BD est conditionnée au fait qu'aucune BD
de ce nom n'est connue du SGBD :
● La valeur de paramètre 'bibliothèque' qui suit indique le nom de la BD à créer ;
● La suite de mots clefs DEFAULT CHARACTER SET indique que ce qui la suit est le nom du jeu de caractères
associé par défaut à la BD (c'est à dire aux différents champs textuels) qui seront créés par la suite dans cette
table) ;
● Le mot clef COLLATE indique que ce qui suit est le mode d'interclassement (ordre alphabétique) par défaut. Le
paramètre de COLLATE doit être cohérent avec le jeu de caractère choisi. Ainsi , si on choisit le jeu latin 1, le
paramètre de COLLATE devra être un paramètre compatible (par exemple, latin1_swedish_ci, qui est associé
au jeu de caractères latin1).
Exemple :
CREATE DATABASE `bibliotheque` DEFAULT CHARACTER SET ascii COLLATE ascii_general_ci
REMARQUE : Cette requête crée une base de donnée vide de toute table. Les tables sont créées par
l’intermédiaire d'une autre requête CREATE que nous allons expliquer maintenant.
11/10/2016
Initiation aux Bases de Données
64
V.APERÇU DU LANGAGE SQL:
V.2.CREATION DE LA STRUCTURE D'UNE BASE DE DONNEES (suite):
V.2.2.CRÉATION DES TABLES:
FORME GÉNÉRALE :
La forme générale de la requête de création d'une table est :
CREATE TABLE 'MaTable'
(
<déclaration du champ n° 1>,
............................
<déclaration du champ n° n>,
[PRIMARY KEY ('<nom de champ>')],
[KEY `<Nom de l'index>` (`<Nom du champ déclaré comme index>`)],
............................
[KEY `<Nom de l'index>` (`<Nom du champ déclaré comme index>`)],
)[ENGINE= MyISAM/InnoDB/MEMORY/MERGE/CSV/...]
[DEFAULT CHARSET= <jeu de caractères 1>/../<jeu de caractères n> ]
[COMMENT='<commentaire sur la table>']
[AUTO_INCREMENT=<valeur entière auto incrément>];
DÉCLARATION DES CHAMPS :
Chacune des déclarations de champs obéit à la syntaxe suivante :
'<nom du champ>' <format de donnée> [UNSIGNED]
[NOT NULL] [AUTO_INCREMENT] [COMMENT '<commentaire libre>']
Par exemple, la requête : Id_abonne int(2) UNSIGNED NOT NULL AUTO_INCREMENT
permettra de définir l'attribut Id_abonne comme étant un entier sur 2 octets (format int(2), non signé, non nul,
s'auto incrémentant automatiquement pour chaque nouvelle entrée et utilisé comme clef primaire.
11/10/2016
Initiation aux Bases de Données
65
V.APERÇU DU LANGAGE SQL:
V.2.CREATION DE LA STRUCTURE D'UNE BASE DE DONNEES (suite):
V.2.2.CRÉATION DES TABLES (Suite):
DÉCLARATION DE LA CLEF PRIMAIRE :
Pour déclarer un champ en tant que CLEF PRIMAIRE, on utilisera la déclaration :
PRIMARY KEY '<nom du champ>'
Par exemple, la déclaration PRIMARY KEY 'id_li', incluse dans la déclaration de la table 'livres' indique que le
champ id_li de cette table est une CLEF PRIMAIRE de la table.
DÉCLARATION D'UN INDEX :
Pour déclarer un champ en tant qu'INDEX, on utilisera la déclaration :
KEY '<nom de l'index>' ('<nom du champ>')
Par exemple, la déclaration KEY `IndexLivre` (`fk_id_li`) incluse dans la déclaration de la table
'exemplaire' indique que le champ fk_id_li de cette table est un INDEX de la table identifié par le nom
'IndexLivre'.
PARAMÈTRES PAR DÉFAUT DE LA TABLE :
La dernière ligne permet de spécifier divers paramètres par défaut attachés à la table. Cuux-ci, s'ils sont
spécifiés, prennent le pas sur les paramètres par défaut de la BD :
- Spécification du moteur de stockage à utiliser pour la table :
[ENGINE= MyISAM/InnoDB/MEMORY/MERGE/CSV/...]
- Spécification du jeu de caractères par défaut :
[DEFAULT CHARSET= <jeu de caractères 1>/../<jeu de caractères n> ]
- Commentaire associé à la table :
[COMMENT='<commentaire sur la table>']
- Valeur de l'auto-incrément ;
[AUTO_INCREMENT=<valeur entière auto incrément>];
11/10/2016
Initiation aux Bases de Données
66
V.APERÇU DU LANGAGE SQL:
V.2.CREATION DE LA STRUCTURE D'UNE BASE DE DONNEES (suite):
V.2.2.CRÉATION DES TABLES (Suite):
REMARQUE : REQUÊTE USE ET NOTION DE BASE DE DONNÉES ACTIVE :
Les requêtes CREATE TABLE, comme beaucoup d'autres requêtes SQL, ne spécifient pas le nom de la base de
données à laquelle elles s'appliquent (Dans un SGBD, plusieurs bases de données peuvent être définies). Le
problème est résolu par la notion de "base de donnée" active : à un instant donné, une seule base de donnée est
déclarée ACTIVE. Cette déclaration est effectuée par la requête USE, dont le schéma logique est :
USE '<nom d'une des bases de données définies dans le SGBD>'
Utiliser la requête CREATE TABLE suppose donc qu'il existe une base de données active. Sinon, il faut déclarer
celle-ci au préalable par une requête USE.
EXEMPLE DE DÉCLARATION DE TABLE :
avec 6 champs, une clef primaire (champ 'id_ab') et un index placé sur le champ 'nom_ab':
USE 'bibliotheque' ;
–- uniquement si la BD active n'est pas définie --
CREATE TABLE IF NOT EXISTS `abonnes` (
`id_ab` int(8) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Id. des abonnés',
`nom_ab` varchar(100) NOT NULL COMMENT 'nom de l''abonné',
`prenoms_ab` varchar(100) NOT NULL COMMENT 'prénoms de l'abonné',
`adresse_postale_ab` varchar(255) NOT NULL COMMENT 'adresse postale de l''abonné',
`e_mail_ab` varchar(30) NOT NULL COMMENT 'adresse e_mail de l''abonné',
`telephone_ab` int(30) NOT NULL COMMENT 'numéro de téléphone de l''abonné',
PRIMARY KEY (`id_ab`)
KEY `NomAbonnes` (`nom_ab`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Table des abonnée' AUTO_INCREMENT=1 ;
11/10/2016
Initiation aux Bases de Données
67
V.APERÇU DU LANGAGE SQL:
V.2.CREATION DE LA STRUCTURE D'UNE BASE DE DONNEES (suite):
V.2.3.SUPPRESSION DES TABLES ET DES BASES DE DONNÉES:
Ces suppressions peuvent être effectuées grâce aux requêtes DROP :
DROP TABLE '<nom de table>' ;
DROP DATABASE '<nom de bd>' ;
En ce qui concerne la requête DROP TABLE, il faut (bien sûr) que la BD active soit définie.
V.2.4.REMARQUE : DÉFINITION D'UNE CLEF ÉTRANGÈRE:
Nous pouvons constater que les requêtes CREATE DATABASE et CREATE TABLE permettent à elles-seules de
créer la STRUCTURE d'une base de données (et non son contenu), à une exception près, qui est la définition
des CLEFS ÉTRANGÈRES. Ceci est assez logique puisque la définition de clefs étrangères suppose que
l'ensemble des tables de la BD soit entièrement défini.
Nous verrons par la suite que les CLEFS ÉTRANGÈRES doivent être définies par des requêtes ALTER TABLE
que nous allons aborder maintenant.
11/10/2016
Initiation aux Bases de Données
68
V.APERÇU DU LANGAGE SQL:
V.3.MODIFICATION DE LA STRUCTURE D'UNE BASE DE DONNÉES:
V.3.1.GÉNÉRALITÉS :
Les requêtes de type ALTER TABLE permettent de modifier la structure d'une base de données existante. On
trouvera ainsi les schémas :
●
●
●
ALTER TABLE '<nom table>' ADD '<nom de l'attribut à ajouter>' <propriétés de l'attribut> : qui permet
d'ajouter un attribut (une colonne) à une table ;
ALTER TABLE '<nom table>' CHANGE '<nom de l'attribut à modifier>' '<propriétés de l'attribut>' : qui permet
de modifier un attribut d'une table ;
ALTER TABLE '<nom table>' DROP '<nom de l'attribut à supprimer>' : qui permet de supprimer un attribut
(une colonne) d'une table ;
ALTER TABLE '<nom table>' ADD CONSTRAINT `<nom de la contrainte>' FOREIGN KEY (`<nom(s) du ou
des champs constituant la clef étrangère>`) REFERENCES `<nom table>' (`<nom(s) du ou des champs
constituant la référence>`) : qui permet de définir une clef étrangère.
Avec :
<propriétés de l'attribut> = '<nouveau nom de l'attribut>' <format de donnée> [UNSIGNED] [NOT
NULL] [AUTO_INCREMENT] [COMMENT '<commentaire libre>']
●
V.3.2.EXEMPLE D'AJOUT D'ATTRIBUT :
Ajout de l'attribut 'date_f' à la table 'factures', la date_f étant le timestamp au format POSIX (date en secondes
depuis le 01/01/1970) :
ALTER TABLE 'factures' ADD 'date_f'
TIMESTAMP UNSIGNED
11/10/2016
NOT NULL] COMMENT 'Date de facturation';
Initiation aux Bases de Données
69
V.APERÇU DU LANGAGE SQL:
V.3.MODIFICATION DE LA STRUCTURE D'UNE BASE DE DONNÉES (suite):
V.3.3.EXEMPLE DE MODIFICATION D'ATTRIBUT :
Dans la table 'livres' l'attribut 'presentation_li' a dans un premier temps été défini comme une chaîne de
caractères de 255 octets ( type varchar(255)). On veut pouvoir saisir des textes beaucoup plus longs. Pour cela,
on modifie le nom de l'attribut en 'presentation_li_t et le type de l'attribut en 'TEXT' (jusqu'à 64ko) :
ALTER TABLE 'livres' CHANGE 'presentation_li' 'presentation_li_t' 'TEXT' ;
V.3.4.EXEMPLE DE SUPPRESSION D'ATTRIBUT :
ALTER TABLE 'livres' DROP 'presentation_li' ;
V.3.5.EXEMPLE DE DÉFINITION DE CLEF ÉTRANGÈRE :
Une base de données comprend une table 'livres' et une table 'exemplaires' pour créer entre les deux tables la
relation "est un exemplaire de" , on crée dans "exemplaires" la clef étrangère "fk_id_li" qui fait référence à la clef
primaire id_il de la table "livres" :
ALTER TABLE 'exemplaire' ADD CONSTRAINT `LivreExemplaire' FOREIGN KEY (`fk_id_li`) REFERENCES
`livres' (`id_il`) 11/10/2016
Initiation aux Bases de Données
70
V.APERÇU DU LANGAGE SQL:
V.4.MODIFICATION DU CONTENU D'UNE BASE DE DONNÉES:
V.4.1.GÉNÉRALITÉS :
Les principales requêtes qui permettent la modification du CONTENU d'une base de sonnées sont :
● INSERT INTO :
qui permet d'ajouter une entrée (une ligne) à une table ;
● UPDATE:
qui permet de modifier les champs d'une ou plusieurs entrées (lignes) ;
● DELETE :
qui permet de supprimer une ou plusieurs entrées (lignes) d'une table.
V.4.2.REQUÊTE INSERT INTO :
Le schéma logique de cette requête est :
INSERT INTO '<nom de BD'>.'<nom de table>' ( '<nom du champ 1>', …, '<nom du champ N>' )
VALUES ( '<valeur du champ 1>',...,'<valeur du champ N>' )
EXEMPLE :
INSERT INTO `mabase`.`abonnes` ( 'login', 'password' ) VALUES ( 'superman','xxSuPer %' );
Insère dans la table 'abonnes' de la base 'mabase' l'entrée dont les attributs login et password ont respectivement les
valeurs 'superman' et ''xxSuPer %''.
V.4.3.REQUÊTE UPDATE:
Le schéma logique de cette requête est :
UPDATE '<nom de table>'
SET '<nom champ 1>'='<valeur champ 1>',.., '<nom champ n>'='<valeur champ n>'
[WHERE <Condition>];
Si la clause WHERE est omise , toutes les entrées de la table sont modifiées (mises à jour) en fonction des
indications données par la clause SET. Si la clause WHERE est libellée, seules les entrées satisfaisant à la
condition sont modifiées.
EXEMPLE :
UPDATE 'abonnes' SET 'e-mail'='spiderman.araignee.fr' WHERE 'login'='spiderman' AND
'password'='forever';
Modifie le champ 'e_mail' de chacune des entrées de la table 'abonnes' dont le champ 'login' contient la valeur
'spiderman'.
11/10/2016
Initiation aux Bases de Données
71
V.APERÇU DU LANGAGE SQL:
V.4.MODIFICATION DU CONTENU D'UNE BASE DE DONNÉES (suite):
V.4.3.REQUÊTE DELETE FROM:
Le schéma logique de cette requête est :
DELETE FROM '<nom de table>' WHERE <Condition>
Si la clause WHERE est omise , toutes les entrées de la table sont supprimées. Si la clause WHERE est libellée,
seules les entrées satisfaisant à la condition sont supprimées.
EXEMPLE :
DELETE FROM 'abonnes' WHERE 'login'='spiderman';
Supprime toutes les entrées de la table 'abonnes' dont les champs 'login' contiennent la valeur 'spiderman'.
11/10/2016
Initiation aux Bases de Données
72
V.APERÇU DU LANGAGE SQL:
V.5.LA REQUÊTE SELECT:
V.5.1.PRINCIPE:
Cette requête permet d'extraire SÉLECTIVEMENT des données d'une base de données. Son schéma logique
(simplifié) est le suivant :
SELECT <Liste de noms d'attributs à sélectionner, séparés par des virgules>/*
FROM <nom de table>
[ WHERE <condition sur la valeur des champs de la table> ]
[ ORDER BY <nom du champ sur lequel est effectué l'ordonnancement> ] [ ASC/DESC ]
[ LIMIT <première entrée à prendre en considération>, <nombre d'entrées à traiter> ] ;
REMARQUE : le cas SELECT * correspond à la sélection de tous les attributs de la table.
La requête permet:
● De ne sélectionner que les entrées qui répondent à la condition (Clause WHERE);
● De récupérer les entrées sélectionnées dans un certain ordre opéré en référence au champ choisi (ex: ordre
alphabétique) et de spécifier ordre croissant ou décroissant (clause ASC/DESC);
● De fixer des limites à la sélection (clause LIMIT): le premier paramètre concerne la première entrée à traiter
(nombre entier), le deuxième paramètre concerne le nombre d'entrées consécutives à traiter (nombre entier).
EXEMPLE:
SELECT nom, prenom FROM eleves WHERE classe = 'CM2';
Sélectionne les valeurs des attributs 'nom' et 'prénom' des entrée de la table 'eleves' dont l'attribut 'classe' est
CM2 (on sélectionne et on extrait les noms et prénoms des élèves de la classe de CM2).
11/10/2016
Initiation aux Bases de Données
73
V.APERÇU DU LANGAGE SQL:
V.5.LA REQUÊTE SELECT (Suite)
REMARQUE :
Les fonctions des différents A.P.I. SQL retournent les résultats de la requête SELECT dans un TABLEAU
ASSOCIATIF dont les lignes correspondent aux entrées satisfaisant la condition WHERE et dont les
COLONNES correspondent aux ATTRIBUTS sélectionnés. Supposons la requête :
SELECT
nom, prenom FROM eleves WHERE prenom = 'Claude';
Si les entrées correspondant aux élèves ayant par prénom 'Claude' sont les entrées de rang x, y et z, la
requète retournera:
nom
supposons que la condition WHERE
ait sélectionné les attributs nom
et prenom des entrées x, y et Z
prenom
Entrée x
Dupond
Claude
Entrée y
Duval
Claude
Entrée Z
Legrand
Claude
Tableau associatif retourné par
la requête SELECT
11/10/2016
Initiation aux Bases de Données
74
V.APERÇU DU LANGAGE SQL:
V.6.LES REQUÊTES DE JOINTURE:
V.6.1.PRINCIPE :
Dans le langage SQL, les requêtes de jointure sont des variantes des requêtes SELECT qui agissent sur les
attributs (colonnes) et les entrées (lignes) de plusieurs tables.
V.6.2.SCHÉMA D'UNE REQUÊTE DE JOINTURE DE DEUX TABLES :
SELECT <liste d'attributs des deux tables>
FROM <nom table 1>
<LEFT/RIGHT/INNER/OUTER/FULL> JOIN <nom table 2> ON
<nom table 1>.<nom attribut table 1>
= <nom table 2>.<nom attribut table table2>
WHERE <Condition de sélection des entrées>
ORDER BY <définition de l'ordre de présentation des entrées>
REMARQUES :
● Dans la liste des attributs, les attributs de la "table 2" doivent être libellés sous la forme <nom table 2>.<nom
attribut table 2> ;
● La liste LEFT/RIGHT/INNER/OUTER/FULL permet de choisir le type de la jointure à réaliser.
● Les clauses WHERE et ORDER BY sont d'un emploi identique à ce qui se fait pour un SELECT classique ;
EXEMPLE :
Créer à partir de la table des livres et de la table des exemplaires disponibles, une table listant l'ensemble des
exemplaires disponibles avec les livres auxquels ils correspondent :
SELECT reference_ex, livres.titre_li FROM exemplaires
LEFT JOIN livres ON livres.id_li = exemplaires.fk_id_li
La table de jonction obtenue sera : exemplaires_livres ( reference_ex, titre_li ); Cette table qui
comptera autant d'entrées que d'exemplaires (LEFT JOIN), permettra d'associer à chaque exemplaire le livre
qui lui correspond.
11/10/2016
Initiation aux Bases de Données
75
V.APERÇU DU LANGAGE SQL:
V.6.LES REQUÊTES DE JOINTURE (suite):
V.6.3.SCHÉMA D'UNE REQUÊTE DE JOINTURE DE PLUSIEURS TABLES :
SELECT <liste d'attributs des tables>
FROM <nom table 1>
<LEFT/RIGHT/INNER/OUTER/FULL> JOIN <nom table 2>
ON <condition d'égalité des attributs de jonction>
<LEFT/RIGHT/INNER/OUTER/FULL> JOIN <table 3>
ON <condition d'égalité des attributs de jonction>
.....................................................
.....................................................
.....................................................
<LEFT/RIGHT/INNER/OUTER/FULL> JOIN <table n>
ON <condition d'égalité des attributs de jonction>
WHERE <condition de sélection des entrées>
ORDER BY <définition de l'ordre de présentation des entrées>
EXEMPLE : Jointure de 3 tables (auteurs, auteurs_livres, livres) :
SELECT id_au, livres.id_li
FROM auteurs
INNER JOIN auteurs_livres ON auteurs.id_au = auteurs_livres.fk_id_au
INNER JOIN livres ON livres.id_li = auteurs_livres.fk_id_li
Permet d'obtenir la table : auteurs_et_livres ( id_au, id_li) ;
11/10/2016
Initiation aux Bases de Données
76
VI.L'API MYSQL:
VI.1.PRÉSENTATION DE L'API MYSQL:
L'API MySQL permet de communiquer avec un SGBD MySQL, notamment dans un serveur APACHE. Ces API
peut être appelés notamment depuis un logiciel serveur écrit en langage PHP.
L'API MySQL permet d'expédier vers un SGBD MySQL des REQUÊTES SQL. Celles-ci permettent de
manipuler des BASES DE DONNÉES et des TABLES. C'est à dire :
- Créer, configurer, modifier, détruire des BASES DE DONNÉES
- A l'intérieur d'une base de données :
● Créer, modifier la structure, supprimer des TABLES
● Accéder au contenu des tables pour en extraire des informations ou pour mettre à jour les valeurs des
attributs ;
● Créer de nouvelles tables à partir des tables existantes par divers procédés (jonction, projection).
REMARQUE : Le SGBD MySQL est en général accompagné d'un outil de gestion manuelle des bases de
données appelé PHPMyAdmin.
VI.2.FORME GÉNÉRAL DES APPELS A L'API MySQL en PHP:
Ce sont des fonctions PHP qui permettent d'activer des requêtes SQL sur une base de données. La syntaxe
générale de ces fonctions est:
<compte-rendu booleen> = mysql_<Nom fonction> ( <paramètres> );
En particulier, la fonction:
<compte-rendu booleen> = mysql_query ( '<REQUETE SQL>' );
permet d'activer les requètes SQL supportées par l'A.P.I.
REMARQUE : Dans les environnements LAMP ou WAMP, ces fonctions sont accessibles sous PHP comme
n'importe quels autres logiciels utilitaires.
11/10/2016
Initiation aux Bases de Données
77
VI.L'API MYSQL:
VI.3.RÉCUPÉRATION DU TABLEAU ASSOCIATIF APRÈS UN SELECT:
En effet, nous avons vu que la requête SELECT retourne les valeurs des attributs sélectionnés sous la forme
d'un tableau associatif. Par exemple, la requête SELECT nom, prenom FROM Eleves WHERE classe = 'CM2',
appliquée sur la table Eleves ( Nom, Prenoms, Classe ), retourne un tableau associatif de structure:
nom
prenom
Entrée x
Dupond
Eric
Entrée y
Duval
Claude
Entrée Z
Legrand
Kevin
Ce tableau associatif peut être récupéré grâce à la fonction $Ligne = mysql_fetch_array( $R ), l'argument $R
étant le résultat retourné par l'appel de la requête SELECT :
$R = mysql_query ( 'SELECT nom, prenom FROM Eleves WHERE classe = "CM2" ' ) ;
La requête $Ligne = mysql_fetch_array($R) récupère une LIGNE du tableau associatif. Ceci veut dire que les
valeurs $Ligne['nom'], $Ligne['prenom'] et $Ligne['classe'] contiennent respectivement le nom, le prénoms et la
classe de l'élève correspondant à l'entrée lue. Pour lire toutes les lignes, il suffit de répéter l'appel à
mysql_fetch_array jusqu'à ce que la fonction réponde FALSE :
$R = mysql_query ( "SELECT nom, prenom FROM Eleves WHERE classe = 'CM2'" ) ;
$i = 0 ;
while ( ( $Ligne = mysql_fetch_array($R)!= FALSE )
{
Tableau[i] = $Ligne ;
}
11/10/2016
Initiation aux Bases de Données
78
VI.L'API MYSQL:
VI.4.UTILISATION DE L'API MySQL:
IV.6.1.CONNEXION ET DÉCONNEXION À MYSQL:
Avant tout, il faut que l'utilisateur se connecte au SGBD MySQL. La connexion utilise la fonction mysql_connect:
<compte-rendu booléen> = mysql_connect("<URL de l'host>","<Id. de connexion>","<mot de passe>");
Le compte-rendu booléen vaut «true» si la connexion a réussi, false sinon.
La déconnexion s'effectue grâce à la fonction mysql_close, qui clôt la connexion en cours:
<compte-rendu booléen> = mysql_close ();
Exemple :
$CR = mysql_connect("localhost:3306","MonSGBD","MdPSgbd");
< Utilisation de la BD>
$CR = mysql_close () ;
Remarque : MySQL peut accepter simultanément plusieurs connexions.
IV.6.2.SELECTION D'UNE BASE DE DONNÉES :
Après s'être connecté au SGBD MySQL, il faut que l'utilisateur sélectionne, parmi les bases de données
existantes, celle sur laquelle il veut travailler. Il utilise pour cela la fonction :
<Compte-rendu booléen> = mysql_select_db ( '<nom base de données>');
Exemple : connexion à la base de données MaBase : $R = mysql_select_db ( 'MaBase' );
V.6.3.MANIPULATION DES TABLES ET DES ATTRIBUTS:
On utilisera pour cela la fonction mysql_query qui permet à l'utilisateur de transmettre au SGBD des requêtes
SQL :
<compte-rendu booleen> = mysql_query ( '<REQUETE SQL>' );
Exemple : Création d'une nouvelle base de données :
$R = mysql_query ( 'CREATE DATABASE [IF NOT EXISTS]
11/10/2016
Initiation aux Bases de Données
MaBD') ;
79
VI.L'API MYSQL:
VI.5.EXEMPLES D'UTILISATION DE MySQL:
REMARQUES PRÉLIMINAIRES:
● Les exemples qui suivent, sont écrits en langage PHP. Les noms de variables, de bases de données, de
tables et d'attributs sont écrits sans accentuation pour éviter les problèmes inhérents aux jeux de caractères
employés ;
● Les bases de données sont supposées entièrement créées ;
● La valeur "localhost:3306" de l'URL correspond à celle du serveur local. Elle doit être adaptée en fonction de
la machine utilisée pour le test.
EXEMPLE N°1 ( MySQL appelé par un programme PHP) :
Soit la table eleves ( nom, prenoms, classe ) de la base de données ecole. On veut récupérer les noms et
prénoms de tous les élèves de CM2 dans le tableau associatif $T[<numero ligne>]['<nom>']['<prenoms>'] :
// Connexion au SGBD ( URL, Nom, Mot de passe )
mysql_connect( "localhost:3306","MonSGBD","MdPSgBd");
// Sélection de la base de données
mysql_select_db( 'ecole' );
// Sélection des valeurs des attributs Nom et Prénoms des Eleves de CM2
$R = mysql_query( "SELECT nom, prenoms FROM 'eleves' WHERE classe ='CM2'" );
// Récupération du tableau associatif des valeurs
$N = 0;
while ( ( $Ligne = mysql_fetch_array($R) ) != false )
{
$T[$N] = $Lignes;
$N ++;
}
// Clôture de la connexion au SGBD
mysql_close();
11/10/2016
Initiation aux Bases de Données
80
VI.L'API MYSQL:
VI.6.EXEMPLES D'UTILISATION DE MySQL (suite):
EXEMPLE N°2 :
Soit de nouveau la table eleves ( nom,prenoms, classe ) de la base de données ecole. On veut ajouter l'élève
Jean-Pierre MARTIN dans la classe de CM1 :
// Connexion au SGBD ( URL, Nom, Mot de passe )
mysql_connect( "localhost:3306","MonSGBD","MdPSgBd");
// Sélection de la base de données
mysql_select_db( 'ecole' );
// Ajout de l'élève
$R = mysql_query( "INSERT INTO ecole.eleves ( nom, prenoms, classe )
VALUES ( 'MARTIN', 'Jean-Pierre', 'CM1' )" ) ;
// Cloture de la connexion au SGBD
mysql_close();
EXEMPLE N°3 :
L'élève Jacques DURAND vient de passer en CM2. On veut donc mettre à jour la table eleves:
// Connexion au SGBD ( URL, Nom, Mot de passe )
mysql_connect( "localhost:3306","MonSGBD","MdPSgBd");
// Sélection de la base de données
mysql_select_db( 'ecole' );
// Ajout de l'élève
$R = mysql_query( "UPDATE eleves SET classe='CM2'
WHERE nom='DURAND' AND prenoms='Jacques'" ) ;
// Cloture de la connexion au SGBD
mysql_close();
11/10/2016
Initiation aux Bases de Données
81
VI.L'API MYSQL:
VI.6.EXEMPLES D'UTILISATION DE MySQL (suite):
EXEMPLE N°4:
Dans la base de données "bibliotheque", utilisée plus haut, nous avons défini les tables suivantes:
● auteurs ( id_au, nom_au, prenoms_au, biographie_au ) ;
● auteurs_livres ( fk_id_au, fk_id_li ) ;
// Table de jonction de l'association auteurs - (ecrire) - livres
● livres ( id_li, titre_li, annee_parution_li, presentation_li ) ;
Nous voulons lister tous les livres écrits par un auteur donné avec leur texte de présentation. Nous supposerons
que le nom et les prénoms de l'auteur ont été saisis préalablement dans les variables $nom et $prenoms:
// Connexion au SGBD ( URL, Nom, Mot de passe )
mysql_connect( "localhost:3306","MonSGBD","MdPSgBd");
// Sélection de la base de données
mysql_select_db( 'bibliotheque' );
// Création de la table de jointure des table auteurs, auteurs_livres et livres :
$R = SELECT livres.titre_li, livres.presentation_li
FROM auteurs
INNER JOIN auteurs_livres ON auteurs.id_au = auteurs_livres.fk_id_au
INNER JOIN livres ON livres.id_li = auteurs_livres.fk_id_li
WHERE nom_au = $nom AND prenoms_au = $prenoms
ORDER BY livres.titre_li ASC
// Récupération des valeurs sélectionnées et affichage des résultats
echo "<br />Ouvrages de l'auteur ".$nom." ".$prenoms"." ( ".$surnom.")"
while ( ( $Ligne = mysql_fetch_array($R) ) != false )
{
echo "<br />-".titre_li." ".Présentation : ".presentation_li ;
}
echo "<br />" ;
// Clôture de la connexion au SGBD
mysql_close();
11/10/2016
Initiation aux Bases de Données
82
FIN
11/10/2016
Initiation aux Bases de Données
83