1 4 2 3 exemples schémas
Le modèle relationnel. 3/3 : deux exemples de schémas normalisés¶
Supports complémentaires
- Diapositives: deux schémas normalisés.
- Vidéo sur les schémas normalisés.
- Schéma de la base des voyageurs et base des voyageurs (si vous souhaitez les installer dans votre environnement).
- Schéma de la base des films et base des films (si vous souhaitez les installer dans votre environnement).
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èrecodeLogement
n'a pas la même nom que la clé primaire dont elle reprend les valeurs (code
danslogrement
). 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.
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.cnam.fr/bd/tp.