Conception d’une base de données Merise (Méthode d’Etude et de Réalisation Informatique pour les Systèmes d’Entreprise) BD02: Bases de données avancées 1 Le Modèle Logique des données: BD02: Bases de données avancées MLD 2 1. Rappel : Étapes de la création d’une base de données Analyse des besoins de l’entreprise Dictionnaire de Données Modèle Physique de Données BD02: Bases de données avancées Modèle Conceptuel de Données Modèle Logique de Données 3 Analyse des besoins DD MCD MLD MPD A - Analyse des besoins de l’entreprise • Cas pratique : L’entreprise a besoin d’informatiser le suivi de ses commandes clients. Un client est identifié par son numéro, et caractérisé par son nom et son adresse. Le client est intégré au système lors de sa première commande. Une commande est identifiée par un numéro de commande, et caractérisée par une date de commande et un montant. • Réalisez le dictionnaire de données BD02: Bases de données avancées 4 Analyse des besoins DD MCD MLD MPD B. Le dictionnaire de données Code Désignation Type Taille NumCli Numéro du client Entier 5 NomCli Nom du client AN 20 AdresseCli Adresse du client AN 50 CPCli Code postal du client Entier 6 VilleCli Ville du client AN 25 NumCde Numéro de la commande Entier 5 DateCde Date de la commande Date 8 MontantCde Montant de la commande Réel 6,2 Commentaires Date au format JJ/MM/AAAA Réalisez le modèle conceptuel de données (MCD) correspondant. BD02: Bases de données avancées 5 Analyse des besoins DD MCD MLD MPD C. Le Modèle Conceptuel de Données COMMANDE CLIENT NumCli NomCli AdresseCli CpCli VilleCli 1,n PASSER BD02: Bases de données avancées 1,1 NumCde DateCde MontantCde 6 Analyse des besoins DD MCD MLD MPD D. Objectif : création de la base données avec le Modèle Physique de Données COMMANDE CLIENT NumCli NomCli AdresseCli CpCli VilleCli 1,n PASSER 1,1 NumCde DateCde MontantCde Le MLD : CLIENT ( NumCli , NomCli, AdresseCli, CPCli, VilleCli ) COMMANDE ( NumCde , DateCde, MontantCde, #NumCli ) BD02: Bases de données avancées 7 Le modèle logique des données Définition formalisme des tables logiques est toujours basé sur un MCD donné Un MLD est essentiellement composé de tables logiques reliées entre elles par des flèches. 8 BD02: Bases de données avancées Méthode de modélisation des données Le modèle logique des données Ex: MCD MLD 9 BD02: Bases de données avancées Méthode de modélisation des données Règles de transformation du MCD au MLD Transformation des entités Toute entité est transformée en table. Les propriétés de l'entité deviennent les attributs de la table. L'identifiant de l'entité devient la clé primaire de la table. Ex: Entité "Entreprise" Lettres majuscules BD02: Bases de10 données avancées Table "Entreprise" Méthode de modélisation des données Règles de transformation du MCD au MLD Transformation des relations binaires du type (x,n) – (x,1) Afin de représenter la relation, on duplique la clé primaire de la table basée sur l'entité à cardinalité (x,n) dans la table basée sur l'entité à cardinalité (x,1). Cet attribut est appelé clé étrangère. Les deux tables sont liées par une flèche nommée selon la relation, qui pointe de la table à clé étrangère vers la table qui contient la clé primaire correspondante. x peut prendre les valeurs 0 ou 1 BD02: Bases de 11 données avancées Méthode de modélisation des données Règles de transformation du MCD au MLD Transformation des relations binaires du type (x,n) – (x,1) Ex: L'attribut No_Auteur qui est clé primaire de la table Auteur, devient clé étrangère dans la table Livre. BD02: Bases de12 données avancées Méthode de modélisation des données Règles de transformation du MCD au MLD Transformation des relations binaires du type (x,1) – (x,1) Nous devons distinguer plusieurs cas. Sachant qu'une relation binaire du type (1,1)-(1,1) ne doit pas exister il nous reste les 2 cas suivants: Relation binaire (0,1)-(1,1) Relation binaire (0,1)-(0,1) BD02: Bases de13 données avancées Méthode de modélisation des données Règles de transformation du MCD au MLD Relation binaire (0,1)-(1,1) On duplique la clé de la table basée sur l'entité à cardinalité (0,1) dans la table basée sur l'entité à cardinalité (1,1). Ex: Le No_Client, qui est clé primaire de la table Client, devient clé étrangère dans la table Carte_Membre BD02: Bases de données avancées Méthode de modélisation des données 14 Règles de transformation du MCD au MLD Relation binaire (0,1)-(0,1) On duplique la clé d'une des tables dans l'autre. Lorsque la relation contient ellemême des propriétés, celles-ci deviennent également attributs de la table dans laquelle a été ajoutée la clé étrangère. BD02: Bases de données avancées Méthode de modélisation des données 15 Règles de transformation du MCD au MLD Relation binaire (0,1)-(0,1) Ex: ou Soit on migre la clé primaire de la table Entreprise dans la table Salarié, soit on fait l'inverse BD02: Bases de données avancées Méthode de modélisation des données 16 Règles de transformation du MCD au MLD Transformation des relations binaires du type (x,n) – (x,n) On crée une table supplémentaire ayant comme clé primaire une clé composée des clés primaires des 2 tables. Lorsque la relation contient elle-même des propriétés, celles-ci deviennent attributs de la table supplémentaire. Une propriété de la relation qui est soulignée devra appartenir à la clé primaire composée de la table supplémentaire. BD02: Bases de17 données avancées Méthode de modélisation des données Règles de transformation du MCD au MLD Transformation des relations binaires du type (x,n) – (x,n) Ex: On crée une table Porter, qui contient comme clé primaire une clé composée de No-Commande et Code_Article. Elle contient également la propriété Quantité issue de la relation Porter BD02: Bases de18 données avancées Méthode de modélisation des données Règles de transformation du MCD au MLD Transformation des relations ternaires On crée une table supplémentaire ayant comme clé primaire une clé composée des clés primaires de toutes les tables reliées. Cette règle s'applique de façon indépendante des différentes cardinalités. Lorsque la relation contient elle-même des propriétés, celles-ci deviennent attributs de la table supplémentaire. Une propriété de la relation qui est soulignée devra appartenir à la clé primaire composée de la table supplémentaire. BD02: Bases de19 données avancées Méthode de modélisation des données Règles de transformation du MCD au MLD Transformation des relations ternaires (associations entre pls entités) Ex: La table Enseigner contient une clé composée de No_Enseignant, Code_Matière et Nom_Classe. BD02: Bases de20 données avancées Méthode de modélisation des données Règles de transformation du MCD au MLD Transformation de plusieurs relations entre 2 entités Les règles générales s’appliquent Ex: BD02: Bases de données avancées Méthode de modélisation des données 21 Règles de transformation du MCD au MLD Transformation des relations réflexives (association d’une entité avec elle-même) Ex 1: Nous appliquons les règles générales avec la seule différence que la relation est 2 fois reliée à la même entité BD02: Bases de données avancées Méthode de modélisation des données 22 Règles de transformation du MCD au MLD Transformation des relations réflexives Ex 1: Nous appliquons les règles générales avec la seule différence que la relation est 2 fois reliée à la même entité BD02: Bases de données avancées Méthode de modélisation des données 23 Les règles de normalisation BD02: Bases de données avancées 24 La normalisation • La conception d’un MCD représente la vision de la réalité de l’analyste. Le formalisme obtenu, établi avec une méthode, ne garantit pas justesse et optimisation. • La justesse dépend de la compétence et de l’expérience de l’analyste. • L’optimisation est obtenue par les mécanismes de la normalisation. BD02: Bases de données avancées 25 Normalisation Définition: Le processus de restructurer le modèle de données logiques pour : • Eliminer les redondances, • Organiser les données efficacement, • Réduire le potentiel d'anomalies pendant les opérations sur les données. • C’est vérifier que la structure devant recevoir des données est organisée pour éviter: • des redondances d’informations et • des anomalies de conception. BD02: Bases de données avancées 26 ARTICLE refart designart descriart marqueart 1,n Commandes qute 1,n Fournisseur Fno nom adresse La normalisation Les besoins de normalisation : 3 problèmes de cohérence lors de mises à jours de la BD: anomalie d'insertion (si on rajoute une commande il faut que l'article existe) anomalie de suppression (si on enlève un fournisseur il faut enlever toutes les commandes de ce fournisseur) anomalie de modification (si on modifie le numéro d'un article il faut modifier toutes les commandes avec ce numéro d'article Fournissuer Article Commandes Refart Designart Refart Fno Descriart qute Fno Nom adresse marqueart BD02: Bases de données avancées 27 La normalisation • La théorie de la normalisation repose sur l'analyse de dépendances entre attributs qui sont à l'origine de phénomènes de redondance. • La normalisation consiste en des méthodes de décomposition des relations. • Les classifications formelles utilisées pour décrire le niveau de normalisation d'une base de données relationnelle sont appelées les formes normales (FN) • Il existe huit formes normales. Les trois premières sont les plus pratiques et sont à connaître. BD02: Bases de données avancées 28 Normalisation • Chaque nouvelle forme normale marque une étape supplémentaire de progression vers des relations présentant de moins en moins de redondance • Le but.. est d'obtenir une représentation des données présentant un minimum de redondance à l'intérieur de chaque relation et un maximum d'indépendance entre les différentes relations BD02: Bases de données avancées 29 Les règles de Normalisation -1- Un bon schéma entités-associations doit répondre à 9 règles de normalisation, que le concepteur doit connaître par cœur. BD02: Bases de données avancées 30 Les règles de Normalisation -21. Normalisation des entités (importante) : toutes les entités qui sont remplaçables par une association doivent être remplacées. 2. Normalisation des noms : le nom d’une entité, d’une association ou d’un attribut doit être unique. 3. Normalisation des identifiants : chaque entité doit posséder un identifiant. 4. Normalisation des attributs (importante) : remplacer les attributs en plusieurs exemplaires en une association supplémentaire de cardinalités maximales n et ne pas ajouter d’ attribut calculable à partir d’autres attributs. 5. Normalisation des associations (importante) : il faut éliminer les associations fantômes redondantes ou en plusieurs exemplaires . 6. Normalisation des cardinalités : une cardinalité minimale est toujours 0 ou 1 (et pas 2, 3 ou n) et une cardinalité maximale est toujours 1 ou n (et pas 2, 3, ...). BD02: Bases de données avancées 31 Les règles de Normalisation -3A ces 6 règles de normalisation, il convient d’ajouter les 3 premières formes normales traditionnellement énoncées pour les schémas relationnels, mais qui trouvent tout aussi bien leur place en ce qui concerne les schémas entitésassociations. Rappel : Chaque nouvelle forme normale marque une étape supplémentaire de progression vers des relations présentant de moins en moins de redondance BD02: Bases de données avancées 32 Première forme normale 1NF (First Normal Form) • Une relation est dite normalisée ou en première forme normale si : • aucun attribut qui la compose n'est lui-même une relation, c'est-à-dire si tout attribut est atomique (non décomposable). Qui ne peut avoir qu'une seule valeur. • Cette forme n'utilise que les structures de base d'une relation, elle ne résout pas le problème de la redondance. Exemple : • La relation suivante n'est pas en 1NF : • Personne(noPers, nom, prénoms) • Il faut faire : • Personne(noPers, nom, prénom1, prénom2, prénom3) BD02: Bases de données avancées 33 Deuxième forme normale 2NF • Une relation est dite en deuxième forme normale si et seulement si : • Elle est en première forme normale ; • les attributs non clé dépendent de toute la clé et non d'une partie de la clé. • Exemple : La relation suivante n'est pas en 2FN : Commande(noFournisseur, refArticle, raisonSocialeFou, adresseFou, quantité, prix) Décomposer la relation : Fournisseur(noFournisseur, raisonSociale, adresse) Commande(noFournisseur#, refArticle#, quantité, prix) BD02: Bases de données avancées 34 Troisième forme normale 3NF • Une relation est en troisième forme normale si et seulement si : • elle est en 2NF; • Tout attribut n’appartenant pas à une clé dépend de façon direct d’une clé Exemple : La relation suivante n'est pas en 3FN : VéhiculeLoué(numVéhicule, numClient, codeCat, catégorie, kilométrage) Décomposer la relation en : VéhiculeLoué(numVéhicule, numClient, codeCat#, kilométrage) TypeCatégorie(codeCat, catégorie) BD02: Bases de données avancées 35 Exemple • Fournisseur(Nom, Pays, Ville) • NomVille, Pays • VillePays (pas de ville homonyme dans des pays différents) • 2NF ? • Oui car l’attribut Nom est la seule clé • 3NF ? • Non car NomPays n’est pas direct (on peut passer par Ville). Il y a donc des répétitions de noms de pays. • Décomposition • Fournisseur(Nom, Ville) • Géographie(Ville, Pays) BD02: Bases de données avancées 36 Exercice • Normaliser la relation suivante COMMANDE(N°Commande, date, N°Client, nom, N°Article, désignation, quantité) BD02: Bases de données avancées 37 Corrigé COMMANDE(N°Commande, date, N°Client, nom, N°Article, désignation, quantité) BD02: Bases de données avancées 38 Le Modèle Physique des données: BD02: Bases de données avancées MPD 39 Le modèle physique des données Définition Le modèle physique des données (MPD) est la traduction du modèle logique des données (MLD) dans une structure de données spécifique au système de gestion de bases de données (SGBD) utilisé. BD02: Bases de40 données avancées Méthode de modélisation des données Passage du MLD au MPD Le passage MLD à MPD se fait par les étapes suivantes: Implémentation physique de chaque table du MLD dans le SGBD utilisé. Pour chaque table, indiquer au SGBD quel(s) champ(s) constitue(nt) la clé primaire. Pour chaque table, indiquer au SGBD la (les) clé(s) étrangère(s), et la (les) clé(s) primaire(s) correspondante(s). BD02: Bases de données avancées Méthode de modélisation des données 41 Passage du MLD au MPD Ex 1: Implémentation du modèle logique suivant BD02: Bases de42 données avancées Méthode de modélisation des données 3. Récapitulatif du vocabulaire MCD MLD MPD Entité Table Table Association N,N Relation Table Association 1,N Clé étrangère Clé étrangère Identifiants Clé primaire Clé primaire Propriété Attributs Champs BD02: Bases de données avancées 43 Exercices : Retrouvez le MLD correspondant TYPE_APPAREIL CodeType NomType X OPERATION 1,N PREVOIR Nbhvol 0,N CodeOpe X LibelleOpé 1,N 0,N AVOIR POUR COMPORTER 1,1 APPAREIL Matricule DateMiseService X 1,N 0,N X X 1,1 CONCERNER REVISION NumRevision DateRevision X Observation TYPE_APPAREIL ( CodeType , NomType ) OPERATION ( CodeOpé , LibelléOpé ) REVISION ( NumRevision , DateRevision , Observation , #Matricule APPAREIL ( Matricule , DateMiseService , #CodeType ) PREVOIR (#CodeType , #CodeOpé , Nbhvol ) COMPORTER (#CodeOpé , #NumRevision ) BD02: Bases de données avancées ) 44 NATIONALITE GENRE CodeGenre LibelléGenre CodeNationalité LibelléNationalité 1,n 0,n Etre Appartenir 1,1 AUTEUR N° Auteur NomAuteur Prénom Auteur Date_nais s ance Date_décès 1,1 LIVRE 1,n Ecrire N° Livre 1,1 TitreLivre AnnéeLivre Nombre_exemplaires 0,n Corres pondre 1,n Propos er Prix_vente ABONNE N° Abonné NomAbonné Prénom Abonné RueAbonné CPAbonné VilleAbonné 1,n 0,n Faire EMPRUNT 1,1 N° Em prunt Date_Emprunt Date_retour_prévue 1,n EDITEUR N° Editeur NomEditeur RueEditeur CPEditeur VilleEditeur Retrouvez le MLD correspondant. BD02: Bases de données avancées 45 TAF 3 : Rétro conception Soit le Mld suivant : ADHERENT ( NumAdh, NomAdh, AdrsAdh, NbLocEff, DateDernLoc) BATEAU ( NumBat, NomBat, AnHa, NumPlace, #NumQuai, #NumTypeBat) LOUER ( #NumAdh, #NumBat, DateDebLoc, NbJLoc) QUAI (NumQuai, NomQuai, NbPlaces) TYPEBATEAU ( NumTypeBat, LibTypeBat, CapaTypeBat, PxLocJ, #NumCons) CONSTRUCTEUR ( NumCons, NomCons) Retrouvez le Mcd correspondant. BD02: Bases de données avancées 46