M1104 : Introduction aux bases de données Dut Alternance 2015-2016 Faten ATIGUI, Maître de Conférences [email protected] Plan Introduction – Bases de Données et Système de Gestion de Bases de Données – Place des Bases de Données dans les Systèmes d’Information Modélisation de Bases de Données – Démarche de modélisation – Modèle Entité Association – Modèle Relationnel Définir une BD en SQL Interrogation de Bases de Données – Algèbre Relationnelle – SQL Normaliser une BD en 3ème forme normale 2 Le modèle Entité/Association Démarche de construction des BD Interview Documents Modélisation Entité association Schéma logique en Entité-Association Modèle Physique de Données (MPD) BD Modèle Conceptuel de Données (MCD) Transformation en relationnel Schéma relationnel Modèle Logique de Données (MLD) 4 Modélisation de Bases de Données On dit Modèle Entité/Association ou formalisme E/A. Un formalisme est un système formel composé d'un langage formel et d'une sémantique représentée par un système déductif ou calculatoire. Un formalisme a pour objectif de représenter de manière non-ambiguë un objet d'étude en science. 5 Modélisation de Bases de Données Un schéma E/A décrit une application visée, c'est-à-dire une abstraction d'un domaine d'étude, de manière pertinente relativement aux objectifs visés. Une abstraction consiste à choisir certains aspects de la réalité perçue (et donc à éliminer les autres). Cette sélection se fait en fonction de certains besoins qui doivent être précisément défini. 6 Modélisation de Bases de Données Le Modèle Entité Association – Proposé en 1976 par Chen d’une part et d’autre part Tardieu – Avantages du modèle Entité Association • représentation graphique des informations manipulées dans la base de données • vue conceptuelle de ces informations, indépendante de tout système de gestion de base de données • représentation facilement compréhensible par les acteurs de l’entreprise, non spécialistes en informatique • intègre des concepts simples à appréhender : Entité – Association • utilisé dans les méthodes d’analyse et de conception de système d’information (MERISE) 7 Modèle Entité/Association - Définition Le modèle Entité/Association (E/A) est utilisé à peu près universellement pour la conception de bases de données (relationnelles principalement). – La conception d'un schéma correct est essentielle pour le développement d'une application viable. – Dans la mesure où la base de données est le fondement de tout le système, une erreur pendant sa conception est difficilement récupérable par la suite. Le modèle E/A a l’avantage d'être simple et suffisamment riche pour représenter des structures relationnelles. – Surtout, il repose sur une représentation graphique qui facilite considérablement sa compréhension. 8 Modèle Entité/Association - Définition Les Concepts du Modèle Entité Association – des cardinalités sont précisées pour les classes d’associations ; elles indiquent la nature du lien entre les entités concernées qui peut être : • Simple : cardinalité 0,1 ou 1,1 – 0,1 pour indiquer qu’une entité d’une classe est liée à au moins 0 entité de l’autre classe et au maximum 1 – 1,1 pour indiquer qu’une entité d’une classe est liée à exactement 1 entité de l’autre classe • Multiple : cardinalité 0,n ou 1,n – 0,n pour indiquer qu’une entité d’une classe est liée à au moins 0 entité de l’autre classe et au maximum plusieurs (représenté par n) – 1,1 pour indiquer qu’une entité d’une classe est liée à au moins 1 entité de l’autre classe et au maximum plusieurs 9 Modèle Entité/Association - Définition 10 Modèle Entité/Association - Définition Les Concepts du Modèle Entité Association – Entité : objet du monde réel (Titanic, F872), événement (2/10/20111), personne (Léonardi Di Caprio, James Cameron) – Classe d’entités : toutes les entités de même nature sont regroupées en classes, représentées par des rectangles (Film, Date, Acteur, Metteur en Scène) – Association : lien entre entités (Léonardo Di Caprio joue dans Titanic, Titanic a été mis en scène par James Cameron) – Classe d’associations : toutes les associations de même nature sont regroupées en classe, représentée par des cercles (Jouer, Mettre en scène) 11 Modèle Entité/Association - Définition Les Concepts du Modèle Entité Association – Propriétés • Informations indécomposables et non calculables définies dans les classes d’entités ou les classes d’associations • caractérisant les entités ou les associations de la classe – Film : titre, année, thème, – Acteur :numéro, nom, prénom • prenant des valeurs pour chaque entité ou association – Film : titre :Titanic, thème : Drame – Acteur : numéro:13, nom:Di Caprio, prénom:Léonardo – Identifiant : propriété soulignée permettant d’identifier sans ambigüité les entités ou les associations d’une classe • Film : titre, tous les films ont des titres différents 12 • Acteur : numéro, tous les acteurs ont des numéros différents Modèle E/A – Entités (Entités-Type) Classe d’entités : – Concept : pourvu d'une existence propre conforme aux besoins de gestion de l'entreprise – Elle peut représenter une notion concrète : CLIENT ou une notion abstraite : PORTEFEUILLE D'ACTIONS – Synonymes : INDIVUDU, OBJET 13 Modèle E/A – Associations (Association-Type) ASSOCIATION : – Lien sémantique entre deux ou plusieurs entités. – Le lien n'est pas orienté : les commandes comportent des produits veut dire également que les produits peuvent être commandés. – Souvent nommé par un verbe ou un substantif – Synonyme : RELATION 14 Modèle E/A – Entités-Type vs Association-Type Il est difficile de faire une distinction entre les 2 concepts. Le même objet peut être vu comme entité par certains utilisateurs et comme association par d'autres. Exemple : le mariage – association entre deux personnes – une entité à part entière (si l'on veut connaître, par exemple, le nombre de mariages célébrés dans cette église depuis le début de l'année) 15 Modèle E/A – Entités-Type vs Associations-Type PROPRIETE : – Donnée élémentaire permettant de décrire une entité ou une association. – Cette donnée peut se mesurer par une valeur. – Synonyme : ATTRIBUT REGLES DE BASE : – Une propriété ne peut pas figurer sur deux objets différents. – Une entité possède au moins une propriété (son identifiant : par exemple le N° de commande). – Une association peut ne pas avoir de propriété 16 Modèle E/A – Illustration 17 Modèle E/A – Occurrence OCCURRENCE : – Réalisation particulière d'une entité, propriété ou association. – Synonyme : INSTANCE Le mot "commande" est donc ambigu : ou bien le concept ou bien l'instance. Si l'on souhaite l'éviter, on peut employer pour les concepts les mots de : ENTITETYPE et ASSOCIATION-TYPE. En pratique, et en l'absence de précision, un mot sera relatif à un concept et lorsque l'on voudra parler d'un individu, on dira occurrence de ... 18 Modèle E/A – Cardinalités 1 La cardinalité est une notion OBLIGATOIRE du modèle qui permet de résoudre la question de l'anomalie d'une commande qui aurait pris la liberté de ne point comporter de produits. C'est donc l'expression d'une CONTRAINTE (une "loi") perçue sur le monde, et que l'on écrit dans le modèle. Par exemple, "il n'est pas possible qu'une commande ne concerne aucun produit". Comme il s'agit d'exprimer des lois, on ne peut pour ce faire qu'utiliser une autre loi : – Pour une occurence de cette entité, combien y a-t-il d'occurrences de l'association auxquelles cette occurrence d'entité participe, au plus et au moins ? 19 Modèle E/A – Cardinalités 2 Association 1,1 – Un client donné ne commande qu'un seul produit. – Un produit donné n'est commandé que par un seul client. CLIENT 1,1 Commande 1,1 PRODUIT 20 Modèle E/A – Cardinalités 3 Association 1,N – Un client donné commande plusieurs produits. – Un produit donné n'est commandé que par un seul client. CLIENT 1,N Commande 1,1 PRODUIT 21 Modèle E/A – Cardinalités 4 Association 0,1 – Un client donné commande plusieurs produits. – Un produit donné est commandé au maximum par un seul client mais peut ne pas être commandé. CLIENT 1,N Commande 0,1 PRODUIT 22 Modèle E/A – Cardinalités 5 Recette : – Pour calculer la cardinalité, se POSITIONNER sur l'entité concernée et regarder EN FACE combien de fois l'une de ses occurrences participe à l'association. – Puis se DEPLACER du côté de l'autre entité et faire la même chose dans l'autre sens. 23 Modèle E/A – Cardinalités 6 CARDINALITES MINIMUM : Valeur Définition O Une occurrence de l'entité peut exister sans participer à l'association 1 Une occurrence de l'entité participe nécessairement au moins une fois à une occurrence d'association CARDINALITES MAXIMUM : Valeur Définition 1 Une occurrence de l'entité participe au plus une fois N Une occurrence de l'entité peut participer plusieurs fois Exemple un produit peut ne pas être commandé toute commande concerne au moins un produit Exemple un employé travaille au plus dans un service une commande peut concerner plusieurs produits 24 Modèle E/A – Cardinalités 7 O,1 1,1 0,N 1,N CONFIGURATIONS POSSIBLES : Une occurrence participe au moins 0 fois et au plus 1 fois à l'assocciation Une occurrence participe exactement 1 fois à l'assocciation Une occurrence peut ne pas participer ou participer plusieurs fois Une occurrence participe au moins 1 fois, voire plusieurs 25 Modèle E/A – Les identifiants 1 IDENTIFIANT D'ENTITE : – Propriété PARTICULIERE de l'entité telle que pour chacune des valeurs de cette propriété, il existe une occurrence UNIQUE de l'entité. Remarque : Si l'on ne sait pas trouver d'identifiant à une entité, c'est qu'elle n'a peut être pas d'existence propre. Il pourrait donc s'agir d'une association. Présentation : L'identifiant est inscrit en tête de la liste des propriétés et souligné. Dans les modèles très denses il peut suffire à résumer les autres propriétés, pour faciliter la lecture. 26 Modèle E/A – Les identifiants 2 27 Modèle E/A – Les identifiants 3 IDENTIFIANT D'ASSOCIATION : – Une association N'A PAS D'IDENTIFIANT explicite : l'association dépend des entités qu'elle relie. Son identifiant se déduit par calcul du produit cartésien des identifiants des entités associées. Exemple : – Pour l'association CONCERNE qui relie COMMANDE à PRODUIT, l'identifiant est le produit cartésien de N° Commande et N°Produit. 28 Modèle E/A – Dimension d’association 1 On appelle DIMENSION d'une association le nombre d'entités qu'elle relie. On dit souvent : son nombre de "pattes". – Remarques : Il n'existe pas de limite au nombre de pattes d'une association. Cependant, un nombre de pattes élevé est un indice que l'étude a été superficielle et approximative. Une association "réflexive" est une association qui lie des occurrences d'une même entité entre elles (c'est un cas particulier de la dimension 2) . 29 Modèle E/A – Dimension d’association 2 Supposons une société immobilière dont l'activité consiste à louer des locaux commerciaux Remplacer les points d'interrogation par des cardinalités : 30 Modèle E/A – Contraintes d’intégrité (CI) But – spécifier des propriétés sémantiques du réel perçu qui ne sont pas exprimables avec le modèle E.A. Définition – “Une contrainte d’intégrité (C.I.) est une propriété non représentée par les concepts de base du modèle E.A. que doivent satisfaire les données appartenant à la base de données”. Effet – limiter les occurrences possibles des structures d’information. 31 Modèle E/A – CI statique / dynamique Statique – propriété qui doit être vérifiée à tout moment. – Ex. un auteur doit écrire au moins un article (cardinalité) date de mariage d’une personne >date de naissance Dynamique – propriété que doit respecter tout changement d’état de la base de données –> définit les séquences possibles des changements d’état de la BD – Ex. le salaire d’un employé ne peut que croître le changement d’état civil d’une personne doit respecter le graphe de transition suivant: 32 Modèle Entité/Association – Exemple Démarche de production d’un Modèle Entité Association – Suivre quelques conseils • Entités : sujets, compléments d’objets directs ou indirects • Associations : verbes liant sujets et compléments d’objets • Propriétés : introduites par des verbes – Exemple La BD stocke des informations sur les films, les metteurs en scène et les acteurs jouant des rôles dans les films. Les acteurs sont identifiés par un numéro et ont un nom, un prénom et une date de naissance. Les acteurs jouent un rôle dans des films. Les films sont identifiés par un titre, et on conserve une année de sortie, et un thème qui peut être tragédie, comédie, musical, aventure, espionnage, fantastique… Les films sont mis en scène par des metteurs en scène, identifiés par un numéro et ayant un nom et un prénom. On souhaite également conserver la nationalité des acteurs et metteurs en scène. 33 Modèle Entité/Association – Exemple Promouvoir Faire suite 0,1 0,n être à l’affiche DateDébut DateFin 0,n 1,n NuméroCiné Nom Adresse Ville Film 1,n Titre Annee Theme Duree SortieDVD 0,n Cinema 0,1 0,n 1,n Jouer Role 1,n Acteur NoActeur Nom Prénom DateNaissance 1,1 1,1 0,n sponsoriser montant Nationalité Diriger 0,n 1,n 0,n Metteur en scène Numéro Nom Prénom est de NuméroNat Nom a pour 1,1 34 Modèle Entité/Association – Association réflexive 35 Modèle Entité/Association – Modélisation du temps Objet NumObj DesObj TypeObj DateObj Musee 0,n Exposer 0,n DateExpo Objet NumObj DesObj TypeObj DateObj CodeMus NomMus Identifiant de Exposer = {NumObj, CodeMus, DateExpo} Musee 0,n Exposer DATE jj mm aa 0,n CodeMus NomMus Identifiant de Exposer = {NumObj, CodeMus, DateExpo} 36 Modèle Entité/Association – Démarche Démarche de production d’un Modèle Entité Association – Se poser quelques questions • Les cardinalités sont elles spécifiées pour chaque classe d’associations ? • Y a t’il un identifiant pour chaque classe d’entités? • Les classes d’entités contiennent elles au moins deux propriétés ? • Les propriétés sont elles atomiques, i.e. non décomposables et non calculables ? • Avez vous oublié des éléments dans le texte ? 37 Modèle Entité/Association – Normalisation COMMANDE COMMANDE N°Commande Montant ……………. N°Commande Montant Qté 1,1 1,1 concerner concerner Qté 1,n 1,n ARTICLE N°Article désignation ARTICLE N°Article désignation prixA 38 Modèle Entité/Association – Normalisation • Propriétés multivaluées : plusieurs auteurs pour un livre 1,n LIVRE LIVRE Référence Titre Auteurs Référence Titre avoir AUTEUR idAuteur nom 1,n 39 Modèle Entité/Association – Contrainte d’Intégrité Fonctionnelle (CIF) Une CONTRAINTE D’INTEGRITE FONCTIONNELLE, CIF, est un cas particulier d’association binaire (1,1 – 1,n ou 1,1 – 0,n), dite ASSOCIATION HIERARCHIQUE, non porteuse de données, qui exprime que la connaissance d’une occurrence de L’UNE DES ENTITES PARTICIPANT A L’ASSOCIATION (côté 1,n) EST TOTALEMENT DETERMINEE PAR LA CONNAISSANCE de l’occurrence D’UNE AUTRE ENTITE (côté 1,1) Une CIF existe entre deux entités A et B si tout occurrence de l’une détermine obligatoirement une et une seule occurrence de l’autre 40 Modèle Entité/Association – Contrainte d’Intégrité Fonctionnelle (CIF) CIF sur une relation binaire Cardinalité maximale ..,1 Exemple : 1 membre personnel est affecté à 1 seul établissement (affecter ne renvoie qu’une valeur) La flèche traduit ici une dépendance de Personnel vers Etablissement La flèche n’indique donc pas un sens de lecture mais le sens de la dépendance CIF Etablissement 0,n IdEtab nomEtab villeEtab Affecter 1,1 Personnel idPersonnel nomP prenomP adresseP 41 Modèle Entité/Association – Contrainte d’Intégrité Fonctionnelle (CIF) CIF : Pour une période d’emploi du temps, un professeur ne fait un cours que dans une salle 0,n Professeur CIF IdProf NomProf avoir 0,n Salle idSalle Période idPeriode Durée 0,n 42 Modèle Entité/Association – Contrainte d’Intégrité Fonctionnelle (CIF) CIF : Pour une course, un jockey, un seul cheval ne peut participer Co1 J1 Ch1 Co1 J2 Ch2 Co2 J2 Ch1 Co1 J1 Ch2 …………. 43 Démarche de construction des BD Interview Documents Modélisation Entité association Schéma logique en Entité-Association Modèle Physique de Données (MPD) BD F. Atigui Modèle Conceptuel de Données (MCD) Transformation en relationnel Schéma relationnel Modèle Logique de Données (MLD) 44 Du modèle E/A vers le modèle relationnel QUEL EST LE PROBLEME ? On ne sait pas implémenter aisément un modèle Entité-Association dans une machine : – une association de dimension supérieure à 2 doit être transformée – une association porteuse de données n'est pas toujours implémentable telle qu’elle est QU'EST-CE QUE LE NIVEAU LOGIQUE ? – Le niveau logique est une REPRESENTATION du système tel qu'il sera implémenté dans des ordinateurs. Vu qu'il s'agit d'une représentation, on utilisera également un modèle et : – il ne faut pas confondre le modèle conceptuel (entité- association par ex.) avec le modèle logique (relationnel par exemple) – il ne faut pas confondre le modèle logique (relationnel par ex. ) avec son implémentation physique en machine (avec MySQL ou Oracle par ex.) 45 Du modèle E/A vers le modèle relationnel On passe du modèle conceptuel au modèle logique par une opération de TRADUCTION 46 Du modèle E/A vers le modèle relationnel Une Base de Données Relationnelle (BDR) peut être vue par l’utilisateur comme un ensemble de tableaux ou de tables. Une table est un ensemble de lignes et de colonnes Exemple de BDR : fournisseur est une table contenant le numéro (nof), le nom (nomf) et la ville (ville) de chaque fournisseur pièce est une table contenant le numéro (nop), le nom (nomp) et le prix (prix) de chaque pièce vente est une table indiquant qu’une pièce (nop) est vendue par un fournisseur (nof) 47 Du modèle E/A vers le modèle relationnel Schéma d’une table : Ensemble d’attributs Obligatoirement : clé primaire composée de 1 ou plusieurs attributs (attribut souligné) Eventuellement : une ou plusieurs clés étrangères (attribut suivi par #) Exemple Fournisseur (NoF, nomF, ville) Pièce (NoP, nomP, prix) Ventes (NoP#, NoF#, ville) 48 Du modèle E/A vers le modèle relationnel Fournisseur (NoF, nomF, ville) Schéma Données Ventes (NoP#, NoF#, ville) nof nomf ville nop nof 1 Girard Lyon 1 1 2 Blanc Paris 1 2 3 Merlin Nancy 2 2 2 3 3 1 3 2 3 3 Pièce (NoP, nomP, prix) nop nomp prix 1 vis 1.5 2 écrou 2 3 boulon 2.5 Une base de données relationnelle 49 Du modèle E/A vers le modèle relationnel REGLE N°1 : TOUTE ENTITE DEVIENT UNE RELATION dans laquelle : – les attributs traduisent les propriétés de l'entité – la clé primaire traduit l'identifiant de l'entité 50 Du modèle E/A vers le modèle relationnel REGLE N°2 : UNE ASSOCIATION DE DIMENSION 2 AVEC CARDINALITE 1 à plusieurs SE REECRIT EN : – portant dans la relation du coté du plusieurs la clé primaire de la relation dont le nombre d’occurrence est 1. – L'attribut ainsi ajouté s'appelle clé étrangère. Symbole : #. Pour les associations de dimension 2 avec une cardinalité de 1 à 1, la migration des clés est au choix. 51 Du modèle E/A vers le modèle relationnel REGLE N°3 : UNE ASSOCIATION DE DIMENSION 2 AVEC CARDINALITE PLUSIEURS A PLUSIEURS SE REECRIT EN : – créant une relation particulière qui contient comme attributs les identifiants des 2 entités associées – ces attributs constituent à eux 2 la clé primaire de la relation ils sont individuellement clés étrangères ajoutant la ou les éventuelles propriétés de l'association à cette relation. 52 Du modèle E/A vers le modèle relationnel REGLE N°4 : UNE ASSOCIATION DE DIMENSION SUPERIEURE A 2 SE REECRIT SELON LA REGLE 3 53 Exemple : donner le modèle relationnel équivalent Promouvoir Faire suite 0,1 0,n être à l’affiche DateDébut DateFin 0,n 1,n NuméroCiné Nom Adresse Ville Film 1,n Titre Annee Theme Duree SortieDVD 0,n Cinema 0,1 0,n 1,n Jouer Role 1,n Acteur NoActeur Nom Prénom DateNaissance 1,1 1,1 0,n sponsoriser montant Nationalité Diriger 0,n 1,n 0,n Metteur en scène Numéro Nom Prénom est de NuméroNat Nom a pour 1,1 54 Dépendance fonctionnelle 1 Definition – B dépend fonctionnellement de A si, étant donné une valeur de A, il lui correspond une unique valeur de B (quel que soit l'extension) – A et B sont des ensembles d'attributs – On lit : A détermine B – Notation A → B 55 Dépendance fonctionnelle 2 Exemple BUVEURS(nb, nom, prénom, ville) COMMANDES(nc, datec, nv, qtéc, nb) EXPEDITIONS(nc, dateexp, qtéexp) 56 Dépendance fonctionnelle 3 Les DFs – – – – – – – – NB → NOM NB → PRENOM NB → VILLE NC → DATEC NC → NB NC → NV NC → QTEC NC, DATEEXP → QTEEXP 57 Dépendance fonctionnelle 4 (Axiomes d’Armstrong) Réflexivité – Y⊂X X→Y Augmentation – X→Y X,Z → Y,Z Transitivité – X → Y et Y → Z X → Z Union – X → Y et X → Z X → YZ Pseudo-transitivité – X → Y et Y,W → Z X,W → Z Décomposition – X → Y et Z ⊂ Y X → Z 58 Normalisation des relations - 1 1ère Forme normale – Une relation est en 1FN si tout attribut est atomique (non décomposable) – Contre-exemple ELEVE (no_elv, nom, prenom, liste_notes) – Un attribut ne peut pas être un ensemble de valeurs – Décomposition ELEVE (no_elv, nom, prenom) NOTE (#no_elv, #no_matiere, note) 59 Normalisation des relations - 2 2ème Forme normale – Une relation est en 2FN si elle est en 1FN si tout attribut n’appartenant pas à la clé ne dépend pas d’une partie de la clé – C’est la phase d’identification des clés – Cette étape évite certaines redondances – Tout attribut doit dépendre fonctionnellement de la totalité de la clé 60 Normalisation des relations - 3 2ème Forme normale – Contre-exemple une relation en 1FN qui n'est pas en 2FN – COMMANDE (date, #no_cli, #no_pro, qte, prixUHT) – elle n'est pas en 2FN car la clé = (date, no_cli, no_pro), et le prixUHT ne dépend que de no_pro – Décomposition COMMANDE (date, #no_cli, #no_pro, qte) PRODUIT (no_pro, prixUHT) 61 Normalisation des relations - 4 3ème Forme normale – Une relation est en 3FN si elle est en 2FN si tout attribut n’appartenant pas à la clé ne dépend pas d’un attribut non clé – Ceci correspond à la non transitivité des D.F. ce qui évite les redondances. – En 3FN une relation préserve les D.F. et est sans perte 62 Normalisation des relations - 5 3ème Forme normale – Contre-exemple une relation en 2FN qui n'est pas en 3FN – VOITURE (matricule, marque, modèle, puissance) – on vérifie qu'elle est en 2FN ; elle n'est pas en 3FN car la clé = matricule, et la puissance dépend de (marque, modèle) – Décomposition VOITURE (matricule, marque, modèle) MODELE (marque, modèle, puissance) 63 Normalisation des relations - 6 Exercice – Soit la relation Equipe(noEquipe, nom, entraineurChef, entraineursAdjoints, joueurs) – En quelle forme normale est cette relation, justifiez votre réponse. Normalisez la relation en 3FN si elle ne l’est pas. 64