Conception d’une BD
introduction
BD
Exemples
MLD
Modèle relationnel
MCD et MCD
(Merise)
MESSAGE (idMessage, titre, contenu, date, idExpediteur)
idMessage : clé primaire
idExpediteur : clé étrangère en réf. à idPersonne de PERSONNE
MESSAGE_UNIQUE (idMessageUnique, date_reponse, idDestinataire)
idMessageUnique : clé primaire
idMessageUnique : clé étrangère en réf. à idMessage de MESSAGE
idDestinataire : clé étrangère en réf. à idPersonne de PERSONNE
DIFFUSION (idDiffusion)
idDiffusion : clé primaire
idDiffusion : clé étrangère en réf. à idMessage de MESSAGE
PERSONNE (idPersonne, nom)
idPersonne : clé primaire
DESTINATAIRE (idDiffusion, idDestinataire, date_reponse)
idDiffusion, idDestinataire : clé primaire
idDiffusion : clé étrangère en réf. à idDiffusion de DIFFUSION
idDestinataire : clé étrangère en réf. à idPersonne de PERSONNE
Introduction
introduction But d'une application
BD Permettre de réaliser des traitements automatisés et facilités.
Permettre d'exploiter des informations.
Exemples
MLD
Persistance des informations
Mémorisation des informations pour une exploitation ultérieure.
MCD
Exemples : articles, clients, commandes…
Exploitation des informations (création, lecture, modification, suppression).
Supports de stockage
Supports magnétiques (disque dur, clé usb...)
Supports optiques (cd, dvd)
Formats de stockage
Fichier texte
Fichier de format spécifique (tableur...)
Fichier XML (format standard du net)
Fichier binaire
Base de données relationnelle
Base de données relationnelle
introduction Qu'est-ce que c'est ?
BD Une base de données est un ensemble d'informations dont l'organisation
respecte des règles précises et qui peut être exploitée avec le langage SQL
Exemples (Structured Query Language).
MLD Quels outils pour la créer ?
MCD Utilisation d'un logiciel spécifique de type SGBDR (Système de Gestion de
Bases de Données Relationnelles) acceptant le langage SQL.
Exemples : MySQL, PostgreSQL, SQL Serveur, Oracle, Access...
Quel outil pour l'exploiter ?
Avec le langage SQL :
- directement dans le SGBDR (en mode graphique ou en requête SQL)
- dans une application connectée au SGBDR (à l'aide d'un driver)
Pourquoi des règles à respecter ?
Pour optimiser l'organisation des données dans la base de données :
- respect du modèle relationnel
- utilisation d'une méthode de conception (exemple : Modèle Conceptuel
de Données de la méthode Merise)
Exemple de MCD
introduction Le MCD permet une représentation graphique de l'organisation optimisée
des données qui sont regroupées en entités, et mettant en évidence des
BD relations entre ces entités.
Exemples
COMMANDE LIGNE_COMMANDE ARTICLE
MLD 1,n 0,n
n°commande qté_commandée n°article
MCD date_paiement nom
description
1,1 photo
prix
1,n stock
CLIENT 1,1
n°client CATEGORIE
0,n
nom n°categorie
adresse libellé
Le MCD s'appuie sur une méthode de conception et permet ainsi d'obtenir
facilement le modèle relationnel qui servira à la construction de la base de
données dans le SGBDR.
Exemple de modèle relationnel
introduction Une fois le MCD construit, il est facile d'obtenir le modèle relationnel :
BD
ARTICLE (n°article, nom, description, photo, prix, stock, n°categorie)
Exemples n°article : clé primaire
n°categorie : clé étrangère en référence à n°categorie de
MLD CATEGORIE
MCD CATEGORIE (n°categorie, libellé)
n°categorie : clé primaire
COMMANDE (n°commande, date_paiement, n°client)
n°commande : clé primaire
n°client : clé étrangère en référence à n°client de CLIENT
CLIENT (n°client, nom, adresse)
n°client : clé primaire
LIGNE_COMMANDE (n°commande, n°article, qte_commandée)
n°commande, n°article : clé primaire
n°commande : clé étrangère en référence à n°commande de
COMMANDE
n°article : clé étrangère en référence à n°article de ARTICLE
Ces "relations" vont permettre de créer les "tables" de la base de données.
modèle relationnel
introduction Qu'est-ce que c'est ?
BD Le modèle relationnel permet d'organiser les données (attributs) en
"relations" qui se transformeront en "tables" dans la base de données.
Exemples
Il est basé sur le concept de dépendances fonctionnelles.
MLD Il est aussi appelé Modèle Logique de Données (MLD).
MCD
Dépendance fonctionnelle
Soit 2 attributs A et B, on dit que B dépend fonctionnellement de A (A → B)
si, à chaque valeur de A ne correspond qu’une seule valeur de B.
Exemples :
n°insee → nom
car il n’y a pas 2 personnes ayant le même n°insee
par contre
nom → n°insee
car il peut y avoir des homonymes
modèle relationnel
introduction Dépendance fonctionnelle (DF) élémentaire
BD Soient 2 attributs A et B, la DF (A → B) est élémentaire s’il n’existe pas C
contenu dans A tel que (C → B).
Exemples
MLD Exemples :
MCD n°commande, n°article → nom_article (non élémentaire)
avec les 2 premières informations, on obtient la dernière
mais on n’a pas besoin des 2 informations
n°article → nom_article (élémentaire)
par contre, dans l’exemple suivant, les 2 sont nécessaires :
n°commande, n°article → qté_commandée (élémentaire)
modèle relationnel
introduction Dépendance fonctionnelle (DF) directe
BD Soient 2 attributs A et B, la DF (A → B) est directe s’il n’existe pas C tel que
(A → C) et (C → B).
Exemples
MLD Exemples :
MCD nous possédons, entre autres, les attributs suivants :
n°client, nom_client, n°commande
n°commande → nom_client (non directe)
la première information donne bien la seconde,
mais un intermédiaire parait logique :
n°commande → n°client (directe)
n°client → nom_client (directe)
modèle relationnel
introduction Exercice sur les Dépendances fonctionnelles :
BD Voici les attributs disponibles : n°client, nom_client, adr_client, n°article,
nom_article, prix, n°commande, date_commande, qte_commandée
Exemples
MLD DF élém. directe
MCD nom_client → adr_client non
n°client → adr_client oui oui oui
n°commande, n°client → qte_commandée non
n°commande, n°article → prix oui non oui
n°commande, n°article → qte_commandée oui oui oui
n°commande → date_commande oui oui oui
n°commande → nom_client oui oui non
n°commande → nom_article non
modèle relationnel
introduction Relation
BD Une relation est un sous-ensemble du produit cartésien d’une liste
d’attributs. Elle possède un nom et contient une liste d'attributs.
Exemples
Exemple de représentation en compréhension :
MLD Client (n°client, nom_client, adr_client)
MCD Commande (n°commande, date_commande, n°client)
Article (n°article, nom_article)
Ligne_commande (n°commande, n°article, qté_commandée)
Exemple de représentation en extension :
n°client nom_client adr_client
45 BERTRAND Nice TUPLE
(ligne)
62 DUPOND Marseille
78 MARTIN Montpellier
... ... ...
ATTRIBUT
(colonne)
modèle relationnel
introduction Clé primaire d'une relation (identifiant)
BD La clé primaire d'une relation est l'attribut ou l'ensemble d’attributs dont
tous les autres attributs de la relation sont en DF.
Exemples
Une relation ne possède qu'une seule clé primaire.
MLD
Clé(s) étrangère(s) d'une relation
MCD Une clé étrangère d'une relation est un attribut ou un ensemble d'attributs
qui a un rôle de clé primaire dans une autre relation.
Une relation possède 0 à plusieurs clés étrangères.
→ Ce sont les clés étrangères qui permettent de faire les liens entre les
relations.
Exemple :
Client (n°client, nom_client, adr_client)
n°client : clé primaire
Commande (n°commande, date_commande, n°client)
n°commande : clé primaire
n°client : clé étrangère en référence avec n°client de Client
modèle relationnel
introduction Formes normales d'une relation
BD Première forme normale (1NF) : la relation possède une clé (donc tous les
autres attributs de la relation sont en DF avec la clé).
Exemples
Exemple :
MLD Commande (n°commande, n°article, qte, date, nom_client)
n°commande, n°article : clé primaire
MCD
Deuxième forme normale (2NF) : la relation est en 1NF et les DF entre les
attributs et la clé sont élémentaires.
Exemple :
Commande (n°commande, date, nom_client)
n°commande : clé primaire
Troisième forme normale (3NF) : la relation est en 2NF et les DF entre les
attributs et la clé sont directes.
Exemple :
Client (n°client, nom_client)
n°client : clé primaire
Commande (n°commande, date, n°_client)
n°commande : clé primaire
n°client : clé étrangère en référence à n°client de Client
modèle relationnel
introduction Exercice
Ecrire les relations en 3NF à partir de la liste d’attributs suivante :
BD
n°etudiant, n°classe, n°matière, nom_etudiant, nom_classe, nom_matière,
Exemples coef
Vous connaissez aussi les règles de gestion suivantes :
MLD - un étudiant est inscrit dans une classe
MCD - un coefficient dépend de la matière et de la classe
Solution
MATIERE (n°matière, nom_matière)
n°matière : clé primaire
CLASSE (n°classe, nom_classe)
n°classe : clé primaire
ETUDIANT (n°etudiant, nom_etudiant, n°classe)
n°etudiant : clé primaire
n°classe : clé étrangère en référence à n°classe de CLASSE
MATIERE_CLASSE (n°classe, n°matière, coefficient)
n°classe, n°matière : clé primaire
n°classe : clé étrangère en référence à n°classe de CLASSE
n°matière : clé étrangère en référence à n°matière de MATIERE
modèle conceptuel de données
introduction Qu'est-ce que c'est ?
BD C'est un modèle visuel permettant de schématiser les dépendances
fonctionnelles.
Exemples
Quelles sont ses règles ?
MLD Il respecte les règles du modèle relationnel
MCD Il ajoute des règles de représentation
Quel est l’intérêt de passer par un MCD ?
→ Il est beaucoup plus facile à construire
→ Il est visuel, donc beaucoup plus facile à lire
Les outils du MCD
Les entités :
relation en 3NF dont la clé primaire n’est composée que d’un seul
attribut
Les associations :
relation en 3NF dont la clé primaire est composée de plusieurs
attributs
Tous les autres éléments du MCD sont des dérivés de ces 2 éléments.
modèle conceptuel de données
introduction Les entités
BD
Exemple :
Exemples
nom entité
MLD ARTICLE
MCD clé primaire
n°article
attribut 1
nom_article
...
prix
attribut N
✓ Une seule clé primaire donc un seul attribut souligné
✓ La clé primaire est en tête de l’entité
✓ 0 à N attributs
✓ Les clés étrangères ne sont pas écrites dans l’entité
modèle conceptuel de données
introduction Les associations
BD
entité 1 nom association entité 2
Exemples attribut 1
clé 1 ... clé 2
MLD
... attribut N ...
MCD
Exemple :
ARTICLE
COMMANDE LIGNE_COM
n°article
n°commande qté_commandée nom_article
date
prix
✓ Une association a obligatoirement plusieurs clés primaires
✓ Les clés primaires sont représentées par les liens directs
✓ Il y a au minimum 2 liens directs autour de l’association
✓ 0 à N attributs
✓ Les clés étrangères ne sont pas écrites dans l’association
modèle conceptuel de données
introduction Les DF entre entités
BD Représente une DF entre les clés primaires des 2 objets (entité ou
association).
Exemples
On parle de Contrainte d’Intégrité Fonctionnelle = CIF.
MLD
entité 1 entité 2
MCD
clé 1 clé 2
... ...
Exemple :
CLIENT
COMMANDE
n°client
n°commande
nom_client
date
adr_client
✓ La flèche n’est pas obligatoire mais elle est plus visuelle
✓ La représentation sous forme d'association est acceptée
modèle conceptuel de données
introduction Les cardinalités
BD Permet d’exprimer le nombre minimum et maximum d’occurrences d’une
entité par rapport à une autre (liées par une association).
Exemples
Exemple :
MLD
COMMANDE ARTICLE
MCD LIGNE_COM
1,n 0,n n°article
n°commande
qté_commandée nom_article
date
prix
1,1
CLIENT
pourquoi min = 1 ?
1,n n°client on ne crée le client que s'il
nom_client a passé au moins une
adr_client commande.
une commande
ne concerne un client peut avoir
qu'un client passé 1 à n commandes
modèle conceptuel de données
introduction Les associations vides
Une CIM (Contrainte d’Intégrité Multiple) est une association non porteuse
BD
de données (association vide).
Exemples Elle ne doit son existence que par un besoin au niveau des traitements.
MLD
Exemple :
MCD Un livre peut avoir plusieurs auteurs. Il sera nécessaire d’éditer un
catalogue de livres classés par auteur.
LIVRE AUTEUR_LIVRE AUTEUR
1,n 0,n
n°livre n°auteur
titre nom
modèle conceptuel de données
introduction Les CIF sur association
BD Elles traduisent une DF entre la clé primaire d’une association et la clé
primaire d’une entité.
Exemples
MLD
Exemple :
MCD
LIEU
DATE
n°lieu SEJOUR
0,n 0,n date
nom_lieu durée_séjour
adr_lieu
1,1
CATEGORIE
0,n n°catégorie
nom
tarif
modèle conceptuel de données
introduction Les CIF sur association
BD Elles traduisent une DF entre la clé primaire d’une association et la clé
primaire d’une entité.
Exemples
Voici une autre représentation :
MLD
Exemple :
MCD
LIEU
DATE
n°lieu SEJOUR
0,n 0,n date
nom_lieu durée_séjour
adr_lieu
1,1
CATEGORIE
0,n n°catégorie
nom
tarif
modèle conceptuel de données
introduction Notion d'intégrité fonctionnelle
BD Une intégrité fonctionnelle traduit une dépendance forte entre des objets :
elle permet d'éviter les données parasites.
Exemples
Exemple :
MLD ARTICLE
COMMANDE LIGNE_COM
MCD 1,n 0,n n°article
n°commande qté_commandée nom_article
date
prix
1,1
CLIENT la suppression d'un commande
1,n doit entraîner la suppression de
n°client tous les tuples correspondants
nom_client dans lign_com
adr_client
une commande est
obligatoirement
liée à un client un client ne peut être supprimé si des
existant commandes sont rattachées à lui
modèle conceptuel de données
introduction Exercice
Tracer le MCD correspondant aux règles suivantes :
BD
Un film est repéré par un numéro. Il possède un titre, un genre, un ou
Exemples plusieurs acteurs et une date de parution. Les films sont classés par genre
mais aussi par acteur
MLD
MCD Solution
FILM ACTEUR_FILM ACTEUR
1,n 0,n
n°film n°acteur
titre nom
date_parution
1,1
GENRE
1,n n°genre
nom
modèle conceptuel de données
introduction Les historiques
BD Gérer un historique suppose que l'on désire garder des informations dans le
temps. Il impose l'intervention d'une entité temporelle.
Exemples
Exemple :
MLD Le but est de mémoriser les locations des films avec, à chaque fois, la
MCD date de location et la date de retour. Il faut aussi savoir qui a loué le
film.
FILM LOCATION
0,n 0,n DATE
n°film date_retour date_location
titre
1,1
CLIENT
0,n n°client
une entité temporelle nom_client
n'existe que pour la adr_client
création d'une association.
modèle conceptuel de données
introduction Les numérotations
BD Certaines informations nécessitent une numérotation en fonction d'une
autre.
Exemples
Exemple :
MLD Un dossier concerne un client et est constitué de fiches d'intervention.
MCD Les fiches sont numérotés à partir de 1 par dossier (1ère fiche du
dossier, 2ème fiche du dossier...).
DOSSIER
0,n FICHE 0,n NUMERO
n°dossier n°fiche
date_ouverture description
1,1
CLIENT
0,n n°client
nom_client une entité numéro n'existe
adr_client que pour la création d'une
association.
modèle conceptuel de données
introduction Les liens identifiants
BD Ils permettent d'éliminer les entités temporelles et de numérotation. Ils ont
été créés avec la version Merise/2.
Exemples
Exemple avec Merise (sans lien identifiant) :
MLD FILM LOCATION DATE
MCD 0,n 0,n
n°film date_retour date_location
titre
Le même avec Merise/2 (avec lien identifiant) :
FILM LOCATION
0,n (1,1) date_location
n°film
titre date_retour
FILM (n°film, titre)
n°film : clé primaire
Cela ne change LOCATION (n°film, date_location, date_retour)
rien au niveau n°film, date_location : clé primaire
relationnel. n°film : clé étrangère en référence à n°film de FILM
modèle conceptuel de données
introduction Les liens identifiants
BD Voici un exemple éliminant une entité de numérotation.
Exemples
Exemple avec Merise (sans lien identifiant) :
MLD DOSSIER FICHE NUMERO
MCD 0,n 0,n
n°dossier description n°fiche
date_ouverture
Le même avec Merise/2 (avec lien identifiant) :
DOSSIER FICHE
0,n (1,1) n°fiche
n°dossier
date_ouverture description
DOSSIER (n°dossier, date_ouverture)
n°dossier : clé primaire
FICHE (n°dossier, n°fiche, description)
n°dossier, n°fiche : clé primaire
n°dossier : clé étrangère en référence à n°dossier de DOSSIER
modèle conceptuel de données
introduction Exercice
Voici un ensemble de relations :
BD
DOSSIER (idDossier, dateOuverture, idClient)
Exemples idDossier : clé primaire
idClient : clé étrangère en référence à idClient de Client
MLD CLIENT (idClient, nom, adresse, tel)
MCD idClient : clé primaire
FICHE_DOSSIER (idDossier, numFiche, dateFiche, description)
idDossier, numFiche : clé primaire
idDossier : clé étrangère en référence à idDossier de DOSSIER
Pourquoi numFiche n'est pas aussi clé étrangère dans la relation
FICHE_DOSSIER ?
→ numFiche n'est pas clé étrangère car il n'est pas clé primaire ailleurs
Comment appelle-t-on le lien qui unit FICHE_DOSSIER à DOSSIER ?
→ Le lien entre FICHE_DOSSIER et DOSSIER est un lien identifiant
Dessiner le SCD (Schéma Conceptuel de Données) à l'origine de ces
relations.
modèle conceptuel de données
introduction Exercice
Dire si les affirmations suivantes
BD
sont vraies :
Exemples 1. une fiche n'appartient qu'à un
seul dossier
MLD VRAI : la cardinalité 1,1 montre
MCD bien qu'une fiche n'est rattachée
qu'à un seul dossier.
Le lien identifiant rend cette
affirmation encore plus forte :
la fiche dépend du dossier.
2. un dossier peut comporter plusieurs fiches
VRAI : du côté de DOSSIER, la cardinalité est de 0,n. Cela prouve que pour
un dossier, il peut y avoir 0 à n fiches.
3. une fiche peut concerner plusieurs clients
FAUX : une fiche n'appartient qu'à un dossier qui lui-même n'appartient
qu'à un seul client. Donc une fiche ne peut concerner qu'un seul client.
4. un client peut posséder plusieurs fiches
VRAI : un client peut posséder plusieurs dossiers (cardinalité 0,n du côté de
client) et un dossier peut posséder plusieurs fiches. Donc un client peut
posséder plusieurs fiches.
modèle conceptuel de données
introduction La double CIF
BD Elle représente la nécessité d'obtenir 2 informations provenant d'une même
entité.
Exemples
Exemple :
MLD Un vol a une ville de départ et une ville d'arrivée.
MCD départ
VOL
1,1 0,n VILLE
n°vol
date arrivée n°ville
heure nom
1,1 0,n
Au niveau relationnel, il faut faire attention aux noms.
VILLE (n°ville, nom)
n°ville : clé primaire
VOL (n°vol, date, heure, n°ville_depart, n°ville_arrivee)
n°vol : clé primaire
n°ville_depart : clé étrangère en référence à n°ville de VILLE
n°ville_arrivee : clé étrangère en référence à n°ville de VILLE
modèle conceptuel de données
introduction La CIF + association
BD Il est parfois nécessaire de faire ressortir un cas particulier d'une liste.
Exemple :
Exemples
Un projet est géré par des développeurs dont l'un est le chef.
MLD chef
MCD PROJET 1,1 0,n DEVELOPPEUR
n°projet
date n°dev
DEV_PROJET
description 0,n 0,n nom
DEVELOPPEUR (n°dev, nom)
n°dev : clé primaire
PROJET (n°projet, date, description, n°chef)
n°projet : clé primaire
n°chef : clé étrangère en référence à n°dev de DEVELOPPEUR
DEV_PROJET (n°projet, n°dev)
n°projet, n°dev : clé primaire
n°projet : clé étrangère en référence à n°projet de PROJET
n°dev : clé étrangère en référence à n°dev de DEVELOPPEUR
modèle conceptuel de données
introduction L'association réflexive
BD Elle est nécessaire lorsqu'il faut lier une information avec elle-même.
Exemple :
Exemples
Il faut mémoriser les distances entre les villes.
MLD
0,n
MCD
VILLE DISTANCE
n°ville distance
nom
0,n
Au niveau relationnel, il faut faire attention aux noms.
VILLE (n°ville, nom)
n°ville : clé primaire
DISTANCE (n°ville_depart, n°ville_arrivee, distance)
n°ville_depart, n°ville_arrivee : clé primaire
n°ville_depart : clé étrangère en référence à n°ville de VILLE
n°ville_arrivee : clé étrangère en référence à n°ville de VILLE
modèle conceptuel de données
introduction Exercice
Construire le schéma conceptuel des données à partir des relations
BD
suivantes :
Exemples
Affaire (n°affaire, date_debut, date_cloture, lieu, description)
MLD n°affaire : clé primaire
MCD Personne (n°personne, nom, adresse, informations)
n°personne : clé primaire
PersonneAffaire (n°affaire, n°personne)
n°affaire, n°personne : clé primaire
n°affaire : clé étrangère en référence à n°affaire de Affaire
n°personne : clé étrangère en référence à n°personne de Personne
LienAffaire (n°affaire1, n°affaire2, n°raison)
n°affaire1, n°affaire2 : clé primaire
n°affaire1 : clé étrangère en référence à n°affaire de Affaire
n°affaire2 : clé étrangère en référence à n°affaire de Affaire
n°raison : clé étrangère en référence à n°raison de Raison
Raison (n°raison, libellé)
n°raison : clé primaire
modèle conceptuel de données
introduction Solution
BD
Exemples
MLD
MCD
modèle conceptuel de données
introduction L'héritage
BD Il intervient pour distinguer des attributs communs et d'autres spécifiques.
Exemple :
Exemples
Il faut gérer les personnes (nom, tel, mail) avec le nombre d'heures
MLD d'enseignement pour les profs et le service pour les administratifs.
MCD PERSONNE PROF
n°personne nb_heures
nom SERVICE
tel ADMIN
1,1 1,n n°service
mail nom
Au niveau relationnel, on peut changer les noms des clés.
PERSONNE (n°personne, nom, tel, mail)
n°personne : clé primaire
PROF (n°prof, nb_heures)
n°prof : clé primaire
n°prof : clé étrangère en référence à n°personne de PERSONNE
...
modèle conceptuel de données
introduction Exercice
Voici un schéma conceptuel de données. Ecrire les relations
BD
correspondantes.
Exemples remarque : le triangle est la représentation de l'héritage sous Win'design.
MLD
MCD
modèle conceptuel de données
introduction Solution
BD
MESSAGE (idMessage, titre, contenu, date, idExpediteur)
Exemples idMessage : clé primaire
idExpediteur : clé étrangère en réf. à idPersonne de PERSONNE
MLD MESSAGE_UNIQUE (idMessageUnique, date_reponse, idDestinataire)
MCD idMessageUnique : clé primaire
idMessageUnique : clé étrangère en réf. à idMessage de MESSAGE
idDestinataire : clé étrangère en réf. à idPersonne de PERSONNE
DIFFUSION (idDiffusion)
idDiffusion : clé primaire
idDiffusion : clé étrangère en réf. à idMessage de MESSAGE
PERSONNE (idPersonne, nom)
idPersonne : clé primaire
DESTINATAIRE (idDiffusion, idDestinataire, date_reponse)
idDiffusion, idDestinataire : clé primaire
idDiffusion : clé étrangère en réf. à idDiffusion de DIFFUSION
idDestinataire : clé étrangère en réf. à idPersonne de PERSONNE
