Skip to content

1 4 2 3 exemples schémas

Le modèle relationnel. 3/3 : deux exemples de schémas normalisés

Supports complémentaires

Dans l'ensemble du cours nous allons utiliser quelques bases de données, petites, simples, à des fins d'illustration, pour les langages d'interrogation notamment. Elles sont présentées ci-dessous, avec quelques commentaires sur le schéma, que nous considérons comme donné pour l'instant. Si vous vous demandez par quelle méthode on en est arrivé à ces schémas, reportez-vous au chapitre Conception d'une base de données

La base des voyageurs

Notre première base de données décrit les pérégrinations de quelques voyageurs plus ou moins célèbres. Ces voyageurs occupent occasionnellement des logements pendant des périodes plus ou moins longues, et y exercent (ou pas) quelques activités.

Voici le schéma de la base. Les clés primaires sont en gras, les clés étrangères en italiques. Essayez de vous figurer les dépendances fonctionnelles et la manière dont elles permettent de rassembler des informations réparties dans plusieurs tables.

  • Voyageur (idVoyageur, nom, prénom, ville, région)
  • Séjour (idSéjour, idVoyageur, codeLogement, début, fin)
  • Logement (code, nom, capacité, type, lieu)
  • Activité (codeLogement, codeActivité, description)

La table des voyageurs

La table Voyageur ne comprend aucune clé étrangère. Les voyageurs sont identifiés par un numéro séquentiel nommé idVoyageur, incrémenté de 10 en 10 (on aurait pu incrémenter de 5, ou de 100, ou changer à chaque fois : la seule chose qui compte est que chaque identifiant soit unique). On indique la ville et la région de résidence.


idVoyageur nom prénom ville région
10 Fogg Phileas Ajaccio Corse
20 Bouvier Nicolas Aurillac Auvergne
30 David-Néel Alexandra Lhassa Tibet
40 Stevenson Robert Louis Vannes Bretagne

Remarquez que nos régions ne sont pas des régions administratives au sens strict : cette base va nous permettre d'illustrer l'interrogation de bases relationnelles, elle n'a aucune prétention à l'exactitude.

La table Logement

La table Logement est également très simple, son schéma ne contient pas de clé étrangère. La clé est un code synthétisant le nom du logement. Voici son contenu.


code nom capacité type lieu
pi U Pinzutu 10 Gîte Corse
ta Tabriz 34 Hôtel Bretagne
ca Causses 45 Auberge Cévennes
ge Génépi 134 Hôtel Alpes

L'information nommée région dans la table des voyageurs s'appelle maintenant lieu dans la table Logement. Ce n'est pas tout à fait cohérent, mais corrrespond à des situations couramment rencontrées où la même information apparaît sous des noms différents. Nous verrons que le modèle relationnel est équipé pour y faire face.

La table des séjours

Les séjours sont identifiés par un numéro séquentiel incrémenté par unités. Le début et la fin sont des numéros de semaine dans l'année (on fait simple, ce n'est pas une base pour de vrai).

idSéjour idVoyageur codeLogement début fin
1 10 pi 20 20
2 20 ta 21 22
3 30 ge 2 3
4 20 pi 19 23
5 20 ge 22 24
6 10 pi 10 12
7 30 ca 13 18
8 20 ca 21 22

Séjour contient deux clés étrangères : l'une référençant le logement, l'autre le voyageur. On peut voir que la valeur de idVoyageur (ou codeLogement) dans cette relation est toujours la valeur de l'une des clés primaire de Voyageur (respectivement Logement). Si ce n'est pas clair, vous pouvez revoir la définition des clés étrangères et méditer dessus le temps qu'il faudra.

Note
La clé étrangère codeLogement n'a pas la même nom que la clé primaire dont elle reprend les valeurs (code dans logrement). Au contraire, idVoyageur[ est aussi bien le nom de la clé primaire (dans)]{.title-ref}[Voyageur]{.title-ref}[) que de la clé étrangère (dans]{.title-ref}[Séjour]{.title-ref}`). Les deux situations sont parfaitement correctes et acceptables. Nous verrons comment spécifier avec SQL le rôle des attributs, indépendamment du nommage.

Connaissant un séjour, je connais donc les valeurs de clé du logement et du voyageur, et je peux trouver la description complète de ces derniers dans leur table respective. ce schéma, comme tous les bons schémas, élimine donc les redondances sans perte d'information.

La table Activité

Cette table contient les activités associées aux logements. La clé est la paire constituée de (codeLogement, codeActivité).


codeLogement codeActivité description
pi Voile Pratique du dériveur et du catamaran
pi Plongée Baptèmes et préparation des brevets
ca Randonnée Sorties d'une journée en groupe
ge Ski Sur piste uniquement
ge Piscine Nage loisir non encadrée

Le schéma de cette table a une petite particularité : la clé étrangère codeLogement fait partie de la clé primaire. Tout se passe dans ce cas comme si on identifiait les activités relativement au logement auquel elle sont associées. Il s'agit encore une fois d'une situation normale, issue d'un de choix de conception assez courant.

Réflechissez bien à ce schéma, nous allons l'utiliser intensivement par la suite pour l'interrogation.

La base des films

La seconde base représente des films, leur metteur en scène, leurs acteurs. Les films sont produit dans un pays, avec une table représentant la liste des pays. De plus des internautes peuvent noter des films. Le schéma est le suivant :

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

Quelques choix simplificateurs ont été faits qui demanderaient sans doute à être reconsidérés pour une base réelle. La clé étrangère idRéalisateur dans Film par exemple implique que connaissant le film, je connais son réalisateur (dépendance fonctionnelle), ce qui exclut donc d'avoir deux réalisateurs ou plus pour un même film. C'est vrai la plupart du temps, mais pas toujours.

La clé primaire de la table Rôle est la paire (idFilm, idActeur), ce qui interdirait à un même acteur de jouer plusieurs rôles dans un même film. Là aussi, on pourrait trouver des exceptions qui rendraient ce schéma impropre à représenter tous les cas de figure. 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);

Un même acteur peut figurer plusieurs fois dans la table Rôle (mais pas associé au même film), ainsi qu'un même film (mais pas associé au même acteur). Voici un exemple concis de contenu de cette base montrant les liens établis par les associations (clé primaire, clé étrangère). Commençons par 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éeNaissance
130 Eastwood Clint 1930
131 Hackman Gene 1930
132 Scott Tony 1930
133 Smith Will 1968

En voici enfin la table des rôles, qui consiste ensentiellement 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. 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

La compréhension du schéma relationnel de la base sur laquelle nous travaillons est indispensable car elle sert de support à l'expression des requêtes SQL. Il est impossible d'interroger correctement une base si l'on ne sait pas comment elle est conçue, et notamment si l'on n'a pas en tête les liens définis par les clés étrangères. Comme nous le verrons, la conception d'une requête SQL s'appuie sur cette connaissance, qui peut être représentée graphiquement comme le montre la figure 6. Les tables y sont visualisées, et les liens entre nuplets représentés par des arêtes. On y voit par exemple qu'un rôle est lié à un film et un artiste, qu'un même film peut être lié à plusieurs rôles, qu'un artiste peut être réalisateur dans un film et acteur dans un autre, etc.

figure 6

Fig. 6 La base des films "vue" comme un graphe dont les arêtes sont les liens clé étrangère - clé primaire.

Nous nous appuierons sur cette représentation pour expliquer le raisonnement à mettre en œuvre quand on conçoit une requête SQL.

Cette base est disponible en ligne à http://deptfod.lecnam.net/bd/tp.