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.
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
etArtiste
` définit la DF :\[idFilm \to idArtiste\]On peut donc ajouter
idArtiste
à la liste des attributs dépendant deidFilm
. -
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 entreFilm
etInternaute
, sans attribut propre. Il ne faut pas oublier dans ce cas de créer une tableVu(idFilm, email)
constituée simplement de la clé. Elle représente le lien entre un film et un internaute.
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.