Skip to content

1 4 6 4 schema

Conception d'une base de données ⅘ : du schéma E/A au schéma relationnel

Supports complémentaires

La création d'un schéma de base de données est simple une fois que le schéma entité/association est finalisé. Il suffit d'appliquer l'algorithme de normalisation vu en début de chapitre. Cette session est essentiellement une illustration de cet algorithme appliqué à la base de films, agrémentée d'une discussion sur quelques cas particuliers.

Application de la normalisation

Pour rappel, voici le schéma E/A de la base des films Fig.33, discuté précédemment.

figure 33

Fig. 33 Le schéma E/A des films

Ce schéma donne toutes les informations nécessaires pour appliquer l'algorithme de normalisation vu en début de chapitre.

  • Chaque entité définit une dépendance fonctionnelle minimale et directe de l'identifiant vers l'ensemble des attributs. On a par exemple pour l'entité Film :

    \[idFilm \to titre, année, genre, résumé\]
  • Chaque association plusieurs-à-un correspond à une dépendance fonctionnelle minimale et directe entre l'identifiant de la première entité et l'identifiant de la seconde. Par exemple, l'association "Réalise" entre Film et Artiste` définit la DF :

    \[idFilm \to idArtiste\]

    On peut donc ajouter idArtiste à la liste des attributs dépendant de idFilm.

  • Enfin chaque association (binaire) plusieurs-à-plusieurs correspond à une dépendance fonctionnelle minimale et directe entre l'identifiant de l'association (qui est la paire des identifiants provenant des entités liées par l'association) et les attributs propres à l'association.

    Par exemple, l'association Joue définit la DF

    \[(idFilm, idArtiste) \to rôle\]

Important
Si une association plusieurs-à-plusieurs n'a pas d'attribut propre, il faut quand même penser à créer une relation avec la clé de l'association (autrement dit la paire des identifiants d'entité) pour conserver l'information sur les liens entre ces entités.
Exemple : la Fig. 34 montre une association plusieurs-plusieurs entre Film et Internaute, sans attribut propre. Il ne faut pas oublier dans ce cas de créer une table Vu(idFilm, email) constituée simplement de la clé. Elle représente le lien entre un film et un internaute.

figure 34

Fig. 34. L'association "Un internaute a vu un film"

Et c'est tout. En appliquant l'algorithme de normalisation à ces DF, on obtient le schéma normalisé suivant :

  • Film (idFilm, titre, année, genre, résumé, idArtiste, codePays)
  • Artiste (idArtiste, nom, prénom, annéeNaissance)
  • Pays (code, nom, langue)
  • Role (idFilm, idActeur, nomRôle)
  • Notation (email, idFilm, note)

Les clés primaires sont en gras : ce sont les identifiants des entités ou des associations plusieurs-à-plusieurs.

Les attributs qui proviennent d'une DF définie par une association, comme par exemple \(idFilm \to idArtiste\), sont en italique pour indiquer leur statut particulier : ils servent de référence à une entité representée par un autre nuplet. Ces attributs sont les clé étrangères de notre schéma.

Comment nommer la clé étrangère ? Ici nous avons adopté une convention simple en concaténant id et le nom de la table référencée. On peut souvent faire mieux. Par exemple, dans le schéma de la table Film, le rôle précis tenu par l'artiste référencé dans l'association n'est pas induit par le nom idArtiste. L'artiste dans Film a un rôle de metteur en scène, mais il pourrait tout aussi bien s'agir du décorateur ou de l'accessoiriste : rien dans le nom de l'attribut ne le précise.

On peut donner un nom plus explicite à l'attribut. Il n'est pas du tout obligatoire en fait que les attributs constituant une clé étrangère aient le même nom que ceux de le clé primaire auxquels ils se réfèrent. Voici le schéma de la table Film, dans lequel la clé étrangère pour le metteur en scène est nommée idRéalisateur.

  • Film (idFilm, titre, année, genre, résumé, idRéalisateur, codePays)

Le schéma E/A nous fournit donc une sorte de résumé des spécifications suffisantes pour un schéma normalisé. Il n'y a pas grand chose de plus à savoir. Ce qui suit donne une illustration des caractéristiques de la base obtenue, et quelques détails secondaires mais pratiques.

Illustration avec la base des films

Les tables ci-dessous montrent un exemple de la représentation des associations entre Film et Artiste d'une part, Film et Pays d'autre part (on a omis le résumé du film).

id nom prénom année
101 Scott Ridley 1943
102 Hitchcock Alfred 1899
103 Kurosawa Akira 1910
104 Woo John 1946
105 Tarantino Quentin 1963
106 Cameron James 1954
107 Tarkovski Andrei 1932

Noter que l'on ne peut avoir qu'un artiste dont l'id est 102 dans la table Artiste, puisque l'attribut idArtiste ne peut prendre qu'une valeur. Cela correspond à la contrainte, identifiée pendant la conception et modélisée dans le schéma E/A de la films2, qu'un film n'a qu'un seul réalisateur.

En revanche rien n'empêche cet artiste 102 de figurer plusieurs fois dans la colonne idRéalisateur de la table Film puisqu'il n'y a aucune contrainte d'unicité sur cet attribut. On a donc bien l'équivalent de l'association un-à-plusieurs élaborée dans le schéma E/A.

Et voici la table des films. Remarquez que chaque valeur de la colonne idRéalisateur est l'identifiant d'un artiste.

id titre année genre idRéalisateur codePays
1 Alien 1979 Science-Fiction 101 USA
2 Vertigo 1958 Suspens 102 USA
3 Psychose 1960 Suspense 102 USA
4 Kagemusha 1980 Drame 103 JP
5 Volte-face 1997 Policier 104 USA
6 Pulp Fiction 1995 Policier 105 USA
7 Titanic 1997 Policier 106 USA
8 Sacrifice 1986 Policier 107 FR

Note
Les valeurs des clés primaires et étrangères sont complètement indépendantes l'une de l'autre. Nous avons identifié les films en partant de 1 et les artistes en partant de 101 pour des raisons de clarté, mais en pratique rien n'empêche de trouver une ligne comme :
(63, Gravity, 2014, SF, 63, USA)

Il n'y a pas d'ambiguité : le premier '63' est l'identifiant du film, le second est l'identifiant du réalisateur.

Et voici, pour compléter, la table des pays.

code nom langue
USA Etats-Unis anglais
FR. France français
JP Japon japonais

Pour bien comprendre le mécanisme de representation des entités et associations grâce aux clés primaires et étrangères, examinons les tables suivantes montrant un exemple de représentation de Rôle. On peut constater le mécanisme de référence unique obtenu grâce aux clés des tables. Chaque rôle correspond à un unique acteur et à un unique film. De plus on ne peut pas trouver deux fois la même paire (idFilm, idActeur) dans cette table (c'est un choix de conception qui découle du schéma E/A sur lequel nous nous basons). En revanche un même acteur peut figurer plusieurs fois (mais pas associé au même film), ainsi qu'un même film (mais pas associé au même acteur).

Voici tout d'abord la table des films.

id titre année genre idRéalisateur codePays
20 Impitoyable 1992 Western 130 USA
21 Ennemi d’état 1998 Action 132 USA

Puis la table des artistes.

id nom prénom année
130 Eastwood Clint 1930
131 Hackman Gene 1930
132 Scott Tony 1930
133 Smith Will 1968

Et voici la table des rôles, qui consiste essentiellement en identifiants établissant des liens avec les deux tables précédentes. À vous de les décrypter pour comprendre comment toute l'information est représentée, et conforme aux choix de conception issus du schéma E/A. Que peut-on dire de l'artiste 130 par exemple ? Peut-on savoir dans quels films joue Gene Hackman ? Qui a mis en scène Impitoyable ?

idFilm idArtiste nomRôle
20 130 William Munny
20 131 Little Bill
21 131 Bril
21 133 Robert Dean

On peut donc remarquer que chaque partie de la clé de la table Rôle est elle-même une clé étrangère qui fait référence à une ligne dans une autre table :

  • l'attribut idFilm fait référence à une ligne de la table Film (un film) ;
  • l'attribut idActeur fait référence à une ligne de la table Artiste (un acteur).

Le même principe de référencement et d'identification des tables s'applique à la table Notation. Il faut bien noter que, par choix de conception, on a interdit qu'un internaute puisse noter plusieurs fois le même film, de même qu'un acteur ne peut pas jouer plusieurs fois dans un même film. Ces contraintes ne constituent pas des limitations, mais des décisions prises au moment de la conception sur ce qui est autorisé, et sur ce qui ne l'est pas.

Associations avec type d'entité faible

Une entité faible est toujours identifiée par rapport à une autre entité. C'est le cas par exemple de l'association entre Cinéma et Salle (voir session précédente). Cette association est de type "un à plusieurs" car l'entité faible (une salle) est liée à une seule autre entité (un cinéma) alors que, en revanche, un cinéma peut être lié à plusieurs salles.

Le passage à un schéma relationnel est donc identique à celui d'une association 1-n classique. On utilise un mécanisme de clé étrangère pour référencer l'entité forte dans l'entité faible. La seule nuance est que la clé étrangère est une partie de l'identifiant de l'entité faible.

Regardons notre exemple pour bien comprendre. Voici le schéma obtenu pour représenter l'association entre les types d'entité Cinéma et Salle.

  • Cinéma (id, nom, numéro, rue, ville)
  • Salle (idCinéma, no, capacité)

On note que l'identifiant d'une salle est constitué de l'identifiant du cinéma et d'un numéro complémentaire permettant de distinguer les salles au sein d'un même cinéma. Mais l'identifiant du cinéma dans Salle est aussi une clé étrangère référençant une ligne de la table Cinéma. En d'autres termes, la clé étrangère est une partie de la clé primaire.

Cette modélisation simplifie l'attribution de l'identifiant à une nouvelle entité Salle puisqu'il suffit de reprendre l'identifiant du composé (le cinéma) et de numéroter les composants (les salles) relativement au composé. Il ne s'agit pas d'une différence vraiment fondamentale avec les associations 1-n mais elle peut clarifier le schéma.

Spécialisation

Note
La spécialisation est une notion avancée dont la représentation en relationnel n'est pas immédiate. Vous pouvez omettre d'étudier cette partie dans un premier temps.

Pour obtenir un schéma relationnel représentant la spécialisation, il faut trouver un contournement. Voici les trois solutions possibles pour notre spécialisation Vidéo-Film-Reportage. Aucune n'est idéale et vous trouverez toujours quelqu'un pour argumenter en faveur de l'une ou l'autre. Le mieux est de vous faire votre propre opinion (je vous donne la mienne un peu plus loin).

  • Une table pour chaque classe. C'est la solution la plus directe, menant pour notre exemple à créer des tables Vidéo, Film et Reportage. Remarque très importante : on doit dupliquer dans la table d'une sous-classe les attributs persistants de la super-classe. Le titre et l'année doivent donc être dupliqués dans, respectivement, Film et Reportage. Cela donne des tables indépendantes, chaque objet étant complètement représenté par une seule ligne.

    Remarque annexe : si on considère que Vidéo est une classe abstraite qui ne peut être instanciée directement, on ne crée pas de table Vidéo.

  • Une seule table pour toute la hiérarchie d'héritage. On créerait donc une table Vidéo, et on y placerait tous les attributs persistants de toutes les sous-classes. La table Vidéo aurait donc un attribut id_realisateur (venant de Film), et un attribut lieu (venant de Reportage).

    Les instances de Vidéo, Film et Reportage sont dans ce cas toutes stockées dans la même table Vidéo, ce qui nécessite l'ajout d'un attribut, dit discriminateur, pour savoir à quelle classe précise correspondent les données stockées dans une ligne de la table. L'inconvénient évident, surtout en cas de hiérarchie complexe, est d'obtenir une table fourre-tout contenant des données difficilement compréhensibles.

  • Enfin, la troisième solution est un mixte des deux précédentes, consistant à créer une table par classe (donc, trois tables pour notre exemple), tout en gardant la spécialisation propre au modèle d'héritage : chaque table ne contient que les attributs venant de la classe à laquelle elle correspond, et une jointure permet de reconstituer l'information complète.

    Par exemple : un film serait représenté partiellement (pour le titre et l'année) dans la table Vidéo, et partiellement (pour les données qui lui sont spécifiques, comme id_realisateur) dans la table Film.

Aucune solution n'est totalement satisfaisante, pour les raisons indiquées ci-dessus. Voici une petite discussion donnant mon avis personnel.

La duplication introduite par la première solution semble source de problèmes à terme, et je ne la recommande vraiment pas. Tout changement dans la super-classe devrait être répliqué dans toutes les sous-classes, ce qui donne un schéma douteux et peu contrôlable.

Tout placer dans une même table se défend, et présente l'avantage de meilleures performances puisqu'il n'y a pas de jointure à effectuer. On risque de se retrouver en revanche avec une table dont la structure est peu compréhensible.

Enfin la troisième solution (table reflétant exactement chaque classe de la hiérarchie, avec jointure(s) pour reconstituer l'information) est la plus séduisante intellectuellement (de mon point de vue). Il n'y a pas de redondance, et il est facile d'ajouter de nouvelles sous-classes. L'inconvénient principal est la nécessité d'effectuer autant de jointures qu'il existe de niveaux dans la hiérarchie des classes pour reconstituer un objet.

Nous aurons alors les trois tables suivantes :

  • Video (id_video, titre, annee)
  • Film (id_video, genre, pays, id_realisateur)
  • Reportage(id_video, lieu)

Nous avons nommé les identifiants id_video pour mettre en évidence une contrainte qui n'apparaît pas clairement dans ce schéma (mais qui est spécificiable en SQL) : comme un même objet est représenté dans les lignes de plusieurs tables, son identifiant est une valeur de clé primaire commune à ces lignes.

Un exemple étant plus parlant que de longs discours, voici comment nous représentons deux objets vidéos, dont l'un est un un film et l'autre un reportage.

Tableau 3 : la table Vidéo

id_video titre année
1 Gravity 2013
2 Messner, profession alpiniste 2014

Rien n'indique dans cette table est la catégorie particulière des objets représentés. C'est conforme à l'approche objet : selon le point de vue on peut très bien se contenter de voir les objets comme instances de la super-classe. De fait, Gravity et Messner sont toutes deux des vidéos.

Voici maintenant la table Film, contenant la partie de la description de Gravity spécifique à sa nature de film.

Tableau 4 : la table Film

id_video genre pays id_realisateur
1 Science-fiction USA 59

Notez que l'identifiant de Gravity (la valeur de id_video) est le même que pour la ligne contenant le titre et l'année dans Vidéo. C'est logique puisqu'il s'agit du même objet. Dans Film, id_video est à la fois la clé primaire, et une clé étrangère référençant une ligne de la table Video. On voit facilement quelle requête SQL permet de reconstituer l'ensemble des informations de l'objet.

select * from Video as v, FilmV as f 
where v.id_video=f.id_video
and titre='Gravity'

Dans le même esprit, voici la table Reportage.

Tableau 5 : la table Reportage

id_video lieu
2 Tyroll du sud

En résumé, avec cette approche, l'information relative à un même objet est donc éparpillée entre différentes tables. Comme souligné ci-dessus, cela mène à une particularité originale : la clé primaire d'une table pour une sous-classe est aussi clé étrangère référençant une ligne dans la table représentant la super-classe.