Les différentes commandes SQL cours
Les différentes commandes SQL formation complet
...
Chapitre 2 Conception des bases de données : le modèle entités-associations
2.1 Introduction
2.1.1 Pourquoi une modélisation préalable ?
Il est di cile de modéliser un domaine sous une forme directement utilisable par un SGBD. Une ou plusieurs modélisations intermédiaires sont donc utiles, le modèle entités-associations constitue l’une des premières et des plus courantes. Ce modèle, présenté par Chen (1976), permet une description naturelle du monde réel à partir des concepts d’entité et d’association1. Basé sur la théorie des ensembles et des relations, ce modèle se veut universel et répond à l’objectif d’indépendance données-programmes. Ce modèle, utilisé pour la phase de conception, s’inscrit notamment dans le cadre d’une méthode plus générale et très répandue : Merise.
2.1.2 Merise
MERISE (Méthode d’Étude et de Réalisation Informatique pour les Systèmes d’Entreprise) est certainement le langage de spécification le plus répandu dans la communauté de l’informatique des systèmes d’information, et plus particulièrement dans le domaine des bases de données. Une représentation Merise permet de valider des choix par rapport aux objectifs, de quantifier les solutions retenues, de mettre en œuvre des techniques d’optimisation et enfin de guider jusqu’à l’implémentation. Reconnu comme standard, Merise devient un outil de communication. En e et, Merise réussit le compromis di cile entre le souci d’une modélisation précise et formelle, et la capacité d’o rir un outil et un moyen de communication accessible aux non-informaticiens.
Un des concepts clés de la méthode Merise est la séparation des données et des traitements. Cette méthode est donc parfaitement adaptée à la modélisation des problèmes abordés d’un point de vue fonctionnel2. Les données représentent la statique du système d’information et les traitements sa dynamique. L’expression conceptuelle des données conduit à une modélisation des données en entités et en associations. Dans ce cours, nous écartons volontairement la modélisation des traitements puisque nous ne nous intéressons à la méthode Merise que dans la perspective de la modélisation de bases de données.
Merise propose une démarche, dite par niveaux, dans laquelle il s’agit de hiérarchiser les préoccupations de modélisation qui sont de trois ordres : la conception, l’organisation et la technique. En e et, pour aborder la modélisation d’un système, il convient de l’analyser en premier lieu de façon globale et de se concentrer sur sa fonction : c’est-à-dire de s’interroger sur ce qu’il fait avant de définir comment il le fait. Ces niveaux de modélisation sont organisés dans une double approche données/traitements. Les trois niveaux de représentation des données, puisque ce sont eux qui nous intéressent, sont détaillés ci-dessous.
Niveau conceptuel : le modèle conceptuel des données (MCD) décrit les entités du monde réel, en terme d’objets, de propriétés et de relations, indépendamment de toute technique d’organisation et d’implantation des données. Ce modèle se concrétise par un schéma entités-associations représentant la structure du système d’information, du point de vue des données.
Niveau logique : le modèle logique des données (MLD) précise le modèle conceptuel par des choix organisationnels. Il s’agit d’une transcription (également appelée dérivation) du MCD dans un formalisme adapté à une implémentation ultérieure, au niveau physique, sous forme de base de données relationnelle ou réseau, ou autres (cf. section 1.1.2). Les choix techniques d’implémentation (choix d’un SGBD) ne seront e ectués qu’au niveau suivant.
Niveau physique : le modèle physique des données (MPD) permet d’établir la manière concrète dont le système sera mis en place (SGBD retenu).
2.2 Éléments constitutifs du modèle entités-associations
La représentation du modèle entités-associations s’appuie sur trois concepts de base :
– l’objet ou entité,
– l’association,
– la propriété.
L’objet est une entité ayant une existence propre. L’association est un lien ou relation entre objets sans existence propre. La propriété est la plus petite donnée d’information décrivant un objet ou une association.
2.2.1 Entité
Fig. 2.1 – Représentation graphique d’un exemple de type-entité.
Définition 2.1 -entité- Une entité est un objet, une chose concrète ou abstraite qui peut être reconnue distinctement et qui est caractérisée par son unicité.
Exemples d’entité : Jean Dupont, Pierre Bertrand, le livre que je tiens entre les mains, la Ferrari qui se trouve dans mon garage, etc.
Les entités ne sont généralement pas représentées graphiquement.
Définition 2.2 -type-entité- Un type-entité désigne un ensemble d’entités qui possèdent une sémantique et des propriétés communes.
Les personnes, les livres et les voitures sont des exemples de type-entité. En e et, dans le cas d’une personne par exemple, les informations associées (i.e. les propriétés), comme le nom et le prénom, ne changent pas de nature.
Une entité est souvent nommée occurrence ou instance de son type-entité.
La figure 2.1 montre la représentation graphique d’un exemple de type-entité (Personne) sans ses propriétés associées.
Les type-entité Personne, caractérisé par un nom et un prénom, et Voiture, caractérisé par un nom et une puissance fiscale, ne peuvent pas être regroupés car ils ne partagent leurs propriétés (le prénom est une chaîne de caractères et la puissance fiscale un nombre). Les type-entité Personne, caractérisé par un nom et un prénom, et Livre, caractérisé un titre et un auteur, possèdent tous les deux deux attributs du type chaîne de caractères. Pourtant, ces deux type-entités ne peuvent pas être regroupés car ils ne partagent pas une même sémantique : le nom d’une personne n’a rien à voir avec le titre d’un livre, le prénom d’une personne n’a rien à voir avec un auteur.
Par abus de langage, on utilise souvent le mot entité en lieu et place du mot type-entité, il faut cependant prendre garde à ne pas confondre les deux concepts.
2.2.2 Attribut ou propriété, valeur
Fig. 2.2 – Représentation graphique d’un exemple de type-entité comportant trois attributs
Définition 2.3 -attribut, propriété- Un attribut (ou une propriété) est une caractéristique associée à un type-entité ou à un type-association.
Exemples d’attribut : le nom d’une personne, le titre d’une livre, la puissance d’une voiture.
Définition 2.4 -valeur- Au niveau du type-entité ou du type-association, chaque attribut possède un domaine qui définit l’ensemble des valeurs possibles qui peuvent être choisies pour lui (entier, chaîne de caractères, booléen, . . .). Au niveau de l’entité, chaque attribut possède une valeur compatible avec son domaine.
La figure 2.2 montre la représentation graphique d’un exemple de type-entité (Personne) avec trois attributs.
Règle 2.5 Un attribut ne peut en aucun cas être partagé par plusieurs type-entités ou type-associations.
Règle 2.6 Un attribut est une donnée élémentaire, ce qui exclut des données calculées ou dérivées.
Règle 2.7 Un type-entité et ses attributs doivent être cohérents entre eux (i.e. ne traiter que d’un seul sujet).
Par exemple, si le modèle doit comporter des informations relatives à des articles et à leur fournisseur, ces informations ne doivent pas coexister au sein d’un même type-entité. Il est préférable de mettre les informations relatives aux articles dans un type-entité Article et les informations relatives aux fournisseurs dans un type-entité Fournisseur. Ces deux type-entités seront probablement ensuite reliés par un type-association.
2.2.3 Identifiant ou clé
Fig. 2.3 – Représentation graphique d’un exemple de type-entité comportant quatre attributs dont un est un identifiant : deux personnes peuvent avoir le même nom, le même prénom et le même âge, mais pas le même numéro de sécurité sociale.
Définition 2.8 -identifiant, clé- Un identifiant (ou clé) d’un type-entité ou d’un type-association est constitué par un ou plusieurs de ses attributs qui doivent avoir une valeur unique pour chaque entité ou association de ce type.
Il est donc impossible que les attributs constituant l’identifiant d’un type-entité (respectivement type-association) prennent la même valeur pour deux entités (respectivement deux associations) distinctes. Exemples d’identifiant : le numéro de sécurité sociale pour une personne, le numéro d’immatriculation pour une voiture, le code ISBN d’un livre pour un livre (mais pas pour un exemplaire).
Règle 2.9 Chaque type-entité possède au moins un identifiant, éventuellement formé de plusieurs attri-buts.
Ainsi, chaque type-entité possède au moins un attribut qui, s’il est seul, est donc forcément l’identifiant.
Dans la représentation graphique, les attributs qui constituent l’identifiant sont soulignés et placés en tête (cf. figure 2.3).
2.2.4 Association ou relation
Définition 2.10 -association- Une association (ou une relation) est un lien entre plusieurs entités.
Exemples d’association : l’emprunt par l’étudiant Tanidute du 3e exemplaire du livre « Maî-trisez SQL ».
Les associations ne sont généralement pas représentées graphiquement.
...
Définition 2.11 -type-association- Un type-association (ou un type-relation) désigne un ensemble de relations qui possèdent les mêmes caractéristiques. Le type-association décrit un lien entre plusieurs type-entités. Les associations de ce type-association lient des entités de ces type-entités.
Comme les type-entités, les type-associations sont définis à l’aide d’attributs qui prennent leur valeur dans les associations.
Règle 2.12 Un attribut peut être placé dans un type-association uniquement lorsqu’il dépend de toutes les entités liées par le type-association.
Un type-association peut ne pas posséder d’attribut explicite et cela est relativement fréquent, mais on verra qu’il possède au moins des attributs implicites.
Exemples de type-association : l’emprunt d’un livre à la bibliothèque.
Une association est souvent nommée occurrence ou instance de son type-association.
La figure 2.4 montre la représentation graphique d’un exemple de type-association.
Par abus de langage, on utilise souvent le mot association en lieu et place du mot type-association, il faut cependant prendre garde à ne pas confondre les deux concepts.
Définition 2.13 -participant- Les type-entités intervenant dans un type-association sont appelés les participants de ce type-association.
Définition 2.14 -collection- L’ensemble des participants d’un type-association est appelé la collec-tion de ce type-association.
Cette collection comporte au moins un type-entité (cf. section 2.3.2), mais elle peut en contenir plus, on parle alors de type-association n-aire (quand n = 2 on parle de type-association binaire, quand n = 3 de type-association ternaire, . . .).
Définition 2.15 -dimension ou arité d’un type-association- La dimension, ou l’arité d’un type-association est le nombre de type-entités contenu dans la collection.
Comme un type-entité, un type-association possède forcément un identifiant, qu’il soit explicite ou non.
Règle 2.16 La concaténation des identifiants des type-entités liés à un type-association constitue un identifiant de ce type-association et cet identifiant n’est pas mentionné sur le modèle (il est implicite).
Cette règle implique que deux instances d’un même type-association ne peuvent lier un même ensemble d’entités.
Souvent, un sous-ensemble de la concaténation des identifiants des type-entités liés su t à identifier le type-association.
On admet également un identifiant plus naturel et explicite, à condition qu’il ne soit qu’un moyen d’exprimer plus simplement cette concaténation.
2.2.5 Cardinalité
Fig. 2.5 – Représentation graphique des cardinalités d’un type-association. Dans cet exemple pédagogique, on suppose qu’un livre ne peut posséder qu’un auteur.
Définition 2.17 -cardinalité- La cardinalité d’une patte reliant un type-association et un type-entité précise le nombre de fois minimal et maximal d’interventions d’une entité du type-entité dans une association du type-association. La cardinalité minimale doit être inférieure ou égale à la cardinalité maximale.
Exemple de cardinalité : une personne peut être l’auteur de 0 à n livre, mais un livre ne peut être écrit que par une personne (cf. figure 2.5).
Règle 2.18 L’expression de la cardinalité est obligatoire pour chaque patte d’un type-association.
Règle 2.19 Une cardinalité minimal est toujours 0 ou 1 et une cardinalité maximale est toujours 1 ou n.
Ainsi, si une cardinalité maximale est connue et vaut 2, 3 ou plus, alors nous considérons qu’elle est indéterminée et vaut n. En e et, si nous connaissons n au moment de la conception, il se peut que cette valeur évolue au cours du temps. Il vaut donc mieux considérer n comme inconnue dès le départ. De la même manière, on ne modélise pas des cardinalités minimales qui valent plus de 1 car ces valeurs sont également susceptibles d’évoluer. Enfin, une cardinalité maximale de 0 n’a pas de sens car elle rendrait le type-association inutile.
Les seuls cardinalités admises sont donc :
0,1 : une occurrence du type-entité peut exister tout en étant impliquée dans aucune association et peut être impliquée dans au maximum une association.
0,n : c’est la cardinalité la plus ouverte ; une occurrence du type-entité peut exister tout en étant impliquée dans aucune association et peut être impliquée, sans limitation, dans plusieurs associations.
1,1 : une occurrence du type-entité ne peut exister que si elle est impliquée dans exactement (au moins et au plus) une association.
1,n : une occurrence du type-entité ne peut exister que si elle est impliquée dans au moins une association.
Une cardinalité minimale de 1 doit se justifier par le fait que les entités du type-entité en questions ont besoin de l’association pour exister. Dans tous les autres cas, la cardinalité minimale vaut 0. Ceci dit, la discussion autour d’une cardinalité minimale de 0 ou de 1 n’est intéressante que lorsque la cardinalité maximale est 1. En e et, nous verrons que, lors de la traduction vers un schéma relationnel (cf. section 3.1.3), lorsque la cardinalité maximale est n, nous ne ferons pas la di érence entre une cardinalité minimale de 0 ou de 1.
Remarques
La seule di culté pour établir correctement les cardinalités est de se poser les question dans le bon sens. Pour augmenter le risque d’erreurs, il faut noter que, pour les habitués, ou les futurs habitués, du modèle UML, les cardinalités d’un type-association sont « à l’envers » (par référence à UML) pour les type-associations binaires et « à l’endroit » pour les n-aires avec n > 2.
La notion de cardinalité n’est pas définie de la même manière dans le modèle Américain et dans le modèle Européen (Merise). Dans le premier n’existe que la notion de cardinalité maximale.
Avec un SGBD relationnel, nous pourrons contraindre des cardinalités à des valeurs comme 2, 3 ou plus en utilisant des déclencheurs (trigger, cf. section ??).
2.3 Compléments sur les associations
2.3.1 Associations plurielles
Fig. 2.6 – Exemple d’associations plurielles entre un type-entité Personne et un type-entité Livre. Sur ce schéma, un type-association permet de modéliser que des personnes écrivent des livres et un autre que des personnes critiquent (au sens de critique littéraire) des livres.
Deux mêmes entités peuvent être plusieurs fois en association (c’est le cas sur la figure 2.6).
2.3.2 Association réflexive
Fig. 2.7 – Exemple d’associations reflexives sur le type-entité Personne. Le premier type-association permet de modéliser la relation parent/enfant et le deuxième type-association la relation de fraternité.
Les type-associations réflexifs sont présents dans la plupart des modèles.
Définition 2.20 -Type-association réflexif- Un type-association est qualifié de réflexif quand il matérialise une relation entre un type-entité et lui-même (cf. figure 2.7).
Une occurrence de ce type-association (i.e. une association) associe généralement une occurrence du type-association (i.e. une entité) à une autre entité du même type. Cette relation peut être symétrique, c’est le cas du type-association Etre frère sur la figure 2.7, ou ne pas l’être, comme le type-association Etre parent sur cette même figure. Dans le cas où la relation n’est pas symétrique, on peut préciser les rôles sur les pattes du type-association comme pour la relation Etre parent de la figure 2.7. L’ambiguïté posée par la non-symétrie d’un type-association réflexif sera levée lors du passage au modèle relationnel (cf. section 3.1.3).
2.3.3 Association n-aire (n > 2)
Dans la section 2.2.4 nous avons introduit la notion de type-association n-aire. Ce type-association met en relation n type-entités. Même s’il n’y a, en principe, pas de limite sur l’arité d’un type-association, dans la pratique on ne va rarement au-delà de trois. Les associations de degré supérieur à deux sont plus di ciles à manipuler et à interpréter, notamment au niveau des cardinalités.
Exemple d’association n-aire inappropriée
Fig. 2.8 – Exemple de type-association ternaire inapproprié.
Le type-association ternaire Contient associant les type-entités Facture, Produit et Client repré-senté sur la figure 2.8 est inapproprié puisqu’une facture donnée est toujours adressée au même client. En e et, cette modélisation implique pour les associations (instances du type-association) Contient une répétition du numéro de client pour chaque produit d’une même facture.
La solution consiste à éclater le type-association ternaire Contient en deux type-associations binaires comme représenté sur la figure 2.9.
Décomposition d’une association n-aire
La figure 2.10 nous montre un exemple de type-association ternaire entre les type-entités Créneau horaire, Salle et Film. Il est toujours possible de s’a ranchir d’un type-association n-aire
...
Fig. 2.10 – Exemple de type association ternaire entre des type-entités Créneau horaire, Salle et Film.
(n > 2) en se ramenant à des type-associations binaires de la manière suivante :
– On remplace le type-association n-aire par un type-entité et on lui attribut un identifiant.
– On crée des type-associations binaire entre le nouveau type-entité et tous les type-entités de la collection de l’ancien type-association n-aire.
– La cardinalité de chacun des type-associations binaires créés est 1; 1 du côté du type-entité créé (celui qui remplace le type-association n-aire), et 0; n ou 1; n du côté des type-entités de la collection de l’ancien type-association n-aire.
La figure 2.11 illustre le résultat de cette transformation sur le schéma de la figure 2.10. L’avantage du schéma de la figure 2.11 est de rendre plus intelligible la lecture des cardinalités. Il ne faut surtout pas le voir comme un aboutissement mais comme une étape intermédiaire avant d’aboutir au schéma de la figure 2.10 (cf. règle 2.27). Ainsi, le mécanisme, que nous ve-nons de détailler ci-dessus, de passage d’un type-association n-aire (n > 2) à un type-entité et n type-associations binaires est tout à fait réversible à condition que :
– toutes les pattes des type-associations binaires autour du type-entité central ont une cardinalité maximale de 1 au centre et de n à l’extérieur ;
...
2.4 Travaux Dirigés – Modèle entités-associations (1repartie)
2.4.1 Attention aux attributs multiples
Fig. 2.15 – Modélisation incorrecte d’un enseignement.
On désire modéliser par un modèle entités-associations le fait qu’un enseignement est dispensé par un enseignant à plusieurs étudiants qui ne suivent qu’un enseignement. On vous propose la modélisation représentée sur la figure 2.15.
- Critiquez cette modélisation.
- Proposez-en une correcte.
2.4.2 Étudiants, cours, enseignants, salles, . . .
Modélisez indépendamment les situations suivantes :
- Plusieurs cours sont o erts. Un cours peut être suivi par plusieurs étudiants et un étudiant peut s’inscrire à plusieurs cours. Pour chaque cours, on veut connaître la liste des étudiants et leur note (chaque cours ne comporte qu’une seule évaluation).
- Plusieurs cours sont o erts. Un cours est dispensé par un seul enseignant et un enseignant peut dispenser plusieurs cours. Pour chaque cours, on veut connaître l’enseignant qui le dispense.
On s’intéresse maintenant à la modélisation d’une situation globale et plus complexe :
– Il existe plusieurs matières (mathématiques, sciences-physiques, français, anglais, philo-sophie).
– Plusieurs cours sont o erts et il peut y avoir plusieurs cours de la même matière.
– Un cours est dispensé par un, et un seul, enseignant et correspond à une matière.
– Un enseignant peut dispenser plusieurs cours dans la même matière ou dans des matières di érentes.
– Un étudiant peut s’inscrire à plusieurs cours.
– Un cours est toujours dispensé dans une même salle, mais une salle peut recevoir plusieurs cours (successivement).
– Chaque cours ne comporte qu’une seule évaluation.
- Proposez un modèle entités-associations permettant de modéliser la situation décrite ci-dessus.
2.4.3 Deux instances d’un même type-association ne peuvent lier un même en-semble d’entités
Considérons la modélisation de la figure 2.16 qui exprime qu’un client commande des produits chez un fournisseur.
Fig. 2.16 – Le type-association Commande lie les type-entités Produit, Client et Fournisseur.
- Imaginons qu’un même client commande un même produit chez un même fournisseur plus d’une fois. Cette situation est-elle compatible avec le modèle ?
- Proposez une amélioration de ce modèle.
2.4.4 Comprenez-vous les type-associations n-aire ?
Fig. 2.17 – Modélisation des résidences principales et secondaires d’un ensemble de personnes.
On désire créer une base de données sur les résidences principales et secondaires d’un échantillon de la population possédant exactement une résidence principale et une résidence secondaire. Dans cette base, si une personne ne peut posséder plus d’une résidence, une rési-dence peut très bien appartenir à plusieurs personnes. Pour modéliser cette situation, on vous propose le modèle de la figure 2.17.
- Expliquez la cardinalité 1 1 de l’une des pattes du type-association ternaire.
- Critiquez cette solution.
- Proposez un modèle corrigé.
- Les deux modèles de la figure 2.18 ne sont pas équivalents. Expliquez pourquoi.
- Dans le cours, section 2.3.3, est exposé un exemple de détection d’une erreur de modéli-sation grâce à la décomposition d’une association n-aire. Discutez avec le chargé de TD du problème exposé dans cette section et illustré par le modèle de la figure 2.12.
2.4.5 Cas d’une bibliothèque (1re partie)
Une petite bibliothèque souhaite informatiser la gestion de son fonds documentaire et de ses emprunts. Dans cette perspective, le bibliothécaire, qui n’est pas un informaticien, a rédigé le texte suivant :
Grâce à cette informatisation, un abonné devra pouvoir retrouver un livre en connaissant son titre. Il doit aussi pouvoir connaître la liste des livres d’un auteur. Un abonné a le droit d’emprunter au maximum dix ouvrages simultanément. Les prêts sont accordés pour une durée de quinze jours. La gestion des prêts doit permettre de connaître, à tout moment, la liste des livres détenus par un abonné, et inversement, de retrouver le nom des abonnés détenant un livre absent des rayons. Un livre peut être écrit par plusieurs auteurs. Chaque livre est acheté en un ou plusieurs exemplaires.
- Identifiez, dans le texte ci-dessus, les mots devant se concrétiser par des entités, des associations ou des attributs.
- Proposez un modèle entités-associations permettant de modéliser la situation décrite ci-dessus.
2.5 Règles de bonne formation d’un modèle entités-associations
La bonne formation d’un modèle entités-associations permet d’éviter une grande partie des sources d’incohérences et de redondance. Pour être bien formé, un modèle entités-associations doit respecter certaines règles et les type-entités et type-associations doivent être normalisées. Un bon principe de conception peut être formulé ainsi : « une seule place pour chaque fait ».
Bien que l’objectif des principes exposés dans cette section soit d’aider le concepteur à obtenir un diagramme entités-associations bien formé, ces principes ne doivent pas être interprété comme des lois. Qu’il s’agisse des règles de bonne formation ou des règles de normalisation, il peut exister, très occasionnellement, de bonnes raisons pour ne pas les appliquer.
2.5.1 Règles portant sur les noms
Règle 2.21 Dans un modèle entités-associations, le nom d’un type-entité, d’un type-association ou d’un attribut doit être unique.
Fig. 2.19 – La présence des deux type-entités Enseignant et Etudiant est symptomatique d’une modélisation inachevée. A terme, ces deux type-entités doivent être fusionnés en un unique type-entité Personne. Référez vous à la règle 2.25 pour plus de précisions concernant cette erreur de modélisation.
Fig. 2.20 – Ici, les attributs Adresse de facturation sont redondants.
Cette situation doit être évitée à tout prix car elle entraîne un gaspillage d’espace mémoire mais aussi et surtout un grand risque d’incohérence. En e et, que faire si, dans le cadre d’une occurrence du type-association Correspondre, la valeurs des deux attributs Adresse de facturation di èrent ?
Lorsque des attributs portent le même nom, c’est parfois le signe d’une modélisation inache-vée (figure 2.19) ou d’une redondance (figure 2.20). Sinon, il faut simplement ajouter au nom de l’attribut le nom du type-entité ou du type-association dans lequel il se trouve (figure 2.21). Il faut toutefois remarquer que le dernier cas décrit n’est pas rédhibitoire et que les SGDB Rela-tionnel s’accommodent très bien de relations comportant des attributs de même nom. L’écriture des requêtes sera tout de même plus lisible si les attributs ont tous des noms di érents.
