Discussion :

[REALIZE]

Bonjour,

Je révise mon cours sur la modélisation et la normalisation, et je ne comprends pas comment résoudre cette question :



Pourquoi le schéma n'est-il pas normalisé ? Apppliquer les transformations nécessaires à l'association "participe" afin de normaliser le schéma. En cas de création d'entités ou d'associations, précisier les attributs et identificateurs des entités, ainsi que les cardinalités des associations.
Remarque : un même coureur peut participer à plusieurs épreuves (par exemples "100 m haies" et "3000m steeple") lors de la même compétition.

Elle n'est pas normalisée car position_arrivee est la clé externe de 3 entités en même temps ?

Merci beaucoup.

[Gannox]

Bonjour,

Pour ce qui est de la normalisation, son but ultime est d’arriver à un MCD valide. Il faut donc garder en tête certaines règles de conception évidentes :

- Le nom d’un objet, d’une association ou d’un attribut doit être unique
- Chaque objet doit posséder un identifiant
- Un objet possède au moins une propriété
- Une association peut ne posséder aucune propriété
- Les propriétés ne doivent pas être redondantes

Aussi, pour l'association ternaire, cela dépend de l'ensemble de tes règles de gestion.
Le sujet suivant devrait te permettre de trouver une ou deux pistes : http://www.developpez.net/forums/d58...ons-ternaires/

[Richard_35]

Bonjour REALIZE et Gannox,

Je me permets de m'immiscer, Gannox...

Les spécialistes "merisiens" d'apporteront, sans doute, des éclaircissements précieux sur la théorie pure (CinePhil, Fsmrel, ...).

Pour ce cas particulier, je dirais que ce schéma ne spécifie, nulle part, qu'une épreuve est valide pour une compétition. Une sorte de catalogue des épreuves par compétition. Par exemple, via la ternaire, COUREUR/COMPETITION/EPREUVE, rien n'empêche d'enregistrer la position d'arrivée d'un coureur sur un "3000m steeple" dans la compétition "Open de Bercy", même si "l'Open de Bercy" n'a pas organisé de "3000m steeple"...

Mais bon, je ne sais pas si c'est la réponse à l'exercice (ou ce qui en découlerait). Néanmoins, concrètement, il me semble qu'il faut bien définir des couples COMPETITION/EPREUVE quelque part (de même que COUREUR/COMPETITION, via une inscription de l'équipe et/ou du coureur, mais c'est une autre histoire...).

[REALIZE]

Merci pour ta réponse.


C'est surtout cette remarque qui me gene.

Remarque : un même coureur peut participer à plusieurs épreuves (par exemples "100 m haies" et "3000m steeple") lors de la même compétition.

Entre COUREUR et EPREUVE, j'ai ajouté une association "cours" avec la propriété "position arrivée". Entre COMPETITION ET EPREUVE "contient". Entre COUREUR et COMPETITION "concours".

[Richard_35]

Nous avons évoqué :

• le catalogue des épreuves d'une compétition (COMPETITION/EPREUVE) ;
• le catalogue des coureurs d'une compétition (COMPETITION/COUREUR).

Tu abordes une autre partie de la ternaire :

• le catalogue des coureurs d'une épreuve (EPREUVE/COUREUR).

qui est tout aussi juste (conceptuellement).

[fsmrel]

Bonsoir,

Pourquoi le schéma n'est-il pas normalisé ?

La question porte-t-elle sur la normalisation au sens de Merise ? au sens du Modèle Relationnel de Données ?

Comme vous présentez un MCD, on va supposer dans un 1er temps qu’il s’agit de la normalisation au sens de Merise.

Dans ce cas-là, l’association-type PARTICIPER n’est pas normalisée si l’on démontre que la propriété Position_arrivée ne dépend que d’un sous-ensemble des entités-types participant à cette association-type (c’est en fait une paraphrase de ce que l’on appelle deuxième forme normale selon le Modèle Relationnel de Données (alias théorie relationnelle)).

Pour des raisons de confort et au besoin prendre en compte les sauteurs et lanceurs, je renomme COUREUR en ATHLETE.

Les sous-ensembles candidats sont les suivants :

E1 - {ATHLETE, COMPETITION},
E2 - {ATHLETE, EPREUVE},
E3 - { COMPETITION, EPREUVE}.

Prenons l’exemple tout à fait fictif suivant :

L’athlète Albert a participé aux Jeux du Relationland de 1994, où il a terminé 7e de la finale du 100 mètres et 8e au 110 haies, tandis que son co-équipier Bernard y a terminé 3e du 100 mètres. Albert a aussi participé Jeux du Relationland de 1999 où il a terminé 5e du 100 mètres.

E1 ne suffit pas pour déterminer une place à l’arrivée, puisqu’aux Jeux de 1994 Albert a fini 7e d’une part (100 mètres) et 8e d’autre part (110 haies).

E2 ne suffit pas pour déterminer une place à l’arrivée, puisqu’au 100 mètres Albert a fini 7e d’une part (Jeux de 1994) et 5e d’autre part (Jeux de 1999).

E3 ne suffit pas pour déterminer une place à l’arrivée, parce qu’aux Jeux du Relationland de 1994 il y avait plus d’un concurrent au 100 mètres, dont Albert et Bernard (même qu’en l'absence d'Usain Bolt encore un peu trop jeune, ils étaient 8 en finale à se disputer la 1re place...)

A l'aide de ces contre-exemples, on voit qu'au sens de Merise l’association-type PARTICIPER est normalisée.

Cela dit, l’exemple proposé est vicieux, car l’identifiant de l’entité-type COMPETITION est composé de deux propriétés, le nom de la compétition et l’année à laquelle elle s’est déroulée.

Autrement dit, en Merise l’année devrait être « expulsée » de cette entité-type et faire l’objet d’une entité-type ANNEE de plein droit (n’en déplaise à Richard pour qui la paire {COMPETITION, ANNEE} constituerait un catalogue, mais ceci est parfaitement orthogonal au problème qui nous occupe). Mais même dans ces conditions, l’association-type PARTICIPER (qui passe du statut de ternaire à celui de quaternaire) est normalisée, ce qu’on peut montrer.


Pour des raisons de confort, je renomme ANNEE en DATE.

Les entités-types parties prenantes sont les suivantes : COMPETITION, DATE, EPREUVE, ATHLETE. Partant de là, on peut échafauder le tableau suivant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
COMPETITION             DATE    EPREUVE       ATHLETE    Place

Jeux du Relationland    1994    100 mètres    Albert     7e
Jeux du Relationland    1994    110 haies     Albert     8e
Jeux du Relationland    1994    100 mètres    Bernard    3e
Jeux du Relationland    1999    100 mètres    Albert     5e
Jeux du Meriseland      1994    100 mètres    Albert     1er

Au sens Merise, l’association-type PARTICIPER n’est pas normalisée si l’on démontre que la propriété Place ne dépend que d’un sous-ensemble du quadruple {COMPETITION, DATE, EPREUVE, ATHLETE}.

Si je n’en oublie pas, les sous-ensembles (au moins les triples) sont les suivants :

E11 - {COMPETITION, DATE, EPREUVE},
E12 - {COMPETITION, DATE, ATHLETE},
E13 - {COMPETITION, EPREUVE, ATHLETE},
E14 - {DATE, EPREUVE, ATHLETE}.

Au sens Merise, E11 ne suffit pas pour normaliser l’association-type PARTICIPER :

Pour la valeur <Jeux du Relationland, 1994, 100 mètres> on a plus d’une valeur pour la propriété Place (même qu’en réalité, il y avait 8 places...)

E12 ne suffit pas pour normaliser l’association-type PARTICIPER :

Pour la valeur <Jeux du Relationland, 1994, Albert> on a plus d’une valeur pour la propriété Place (100 mètres et 110 haies).

E13 ne suffit pas pour normaliser l’association-type PARTICIPER :

Pour la valeur <Jeux du Relationland, 100 mètres, Albert> on a plus d’une valeur pour la propriété Place (1994 et 1999).

E14 ne suffit pas pour normaliser l’association-type PARTICIPER :

Pour la valeur <1994, 100 mètres, Albert> on a plus d’une valeur pour la propriété Place (7e aux Jeux du Relationland et 1er aux Jeux du Meriseland).

A coups de contre-exemples on peut ainsi montrer que l’association-type PARTICIPER est une fois de plus normalisée au sens Merise du terme.


Je traiterai un peu plus tard de la normalisation au sens du Modèle Relationnel de Données.

[fsmrel]

Suite du feuilleton...


Dans le contexte du Modèle Relationnel de Données, l’association-type PARTICIPER fait l’objet de la relvar (variable relationnelle, dont l'avatar SQL est la table) PARTICIPER :

PARTICIPER {nom_competition, année, nom_epreuve, num_coureur, position_arrivée}.

Pour simplifier, je renomme nom_competition en C, année en D, nom_epreuve en E, num_coureur en A, position_arrivée en P. La relvar et son en-tête deviennent :

PARTICIPER {C, D, E, A, P}

Pour savoir si cette relvar est normalisée, disons en BCNF (forme normale de Boyce-Codd), il faut s’assurer que le déterminant de chaque dépendance fonctionnelle non triviale constitue une clé candidate.

Le quintuple {C, D, E, A, P} constitue de facto une surclé, mais il ne constitue une clé candidate que s’il est irréductible, c'est-à-dire si aucun des quadruples qui en sont les sous-ensembles n’est surclé.

En conséquence, les quadruples à examiner sont les suivants :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
Q1 - {C, D, E, P}
Q2 - {C, D, E, A}
Q3 - {C, D, A, P}
Q4 - {C, E, A, P}
Q5 - {D, E, A, P}

Avant d’examiner chaque cas, histoire de corser, on va préciser la règle suivante :

Dans une épreuve, il n’y a pas d’ex-æquo.

On verra plus tard le cas où les ex-æquo sont possibles.


Cas de Q1 : Le quadruple {C, D, E, P} est-il surclé ? si oui, est-il clé candidate ?

Considérons le tableau suivant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
C                         D       E             P          A

Jeux du Relationland      1994    100 mètres    7e         Albert
Jeux du Relationland      1994    100 mètres    7e         Usain

Étant donné que les ex-æquo ne sont pas possibles, cette situation est illégale : A l’occasion des Jeux du Relationland 1994, seul un athlète a terminé 7e du 100 mètres. Ceci se traduit par la dépendance fonctionnelle :

DF1 : {C, D, E, P} -> {A}

Puisque chaque attribut de l’en-tête de la relvar y est représenté (soit à gauche soit à droite), le déterminant {C, D, E, P} de DF1 constitue une surclé. S’il est irréductible, alors ce déterminant est clé candidate.

Cherchons donc à réduire le déterminant {C, D, E, P}. Les triples que l’ont peut produire à partir du quadruple sont les suivants :

{C, D, E}, {C, D, P}, {D, E, P}.

{C, D, E} n’est le déterminant d’aucune dépendance fonctionnelle comme le montre l’exemple suivant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
C                         D       E             P

Jeux du Relationland      1994    100 mètres    1er
Jeux du Relationland      1994    100 mètres    2e
Jeux du Relationland      1994    100 mètres    ...
Jeux du Relationland      1994    100 mètres    8e

Ou celui-ci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
C                         D       E             A

Jeux du Relationland      1994    100 mètres    Jesse
Jeux du Relationland      1994    100 mètres    Bobby
Jeux du Relationland      1994    100 mètres    ...
Jeux du Relationland      1994    100 mètres    Albert

De même, le triple {C, D, P} n’est le déterminant d’aucune dépendance fonctionnelle, sinon à l’occasion des Jeux du Relationland en 1994, la place de 1er n'aurait pu être possible que pour une épreuve et pour un athlète.

De même, le triple {D, E, P} n’est le déterminant d’aucune dépendance fonctionnelle, sinon en 1994 au 100m la place de 1er n'aurait pu revenir qu’à un seul athlète toutes compétitions confondues : si Jesse a fini 1er d’un 100 mètres en 1994, disons aux Jeux du Relationland, un autre athlète n’aurait pas eu le droit de finir 1er du 100 mètres aux Jeux du Meriseland qui eux aussi ont eu lieu en 1994. De même, si en 1994 il y a eu un 1er au 100 mètres, alors c’était à l’occasion d’une seule compétition : s’il s’agissait des Jeux du Relationland, alors c’était râpé pour les Jeux du Meriseland...

Ainsi, aucun des triples {C, D, E}, {C, D, P}, {D, E, P} n’est le déterminant d’une dépendance fonctionnelle (non triviale), il en est de même a fortiori pour les paires que l’on peut en inférer.

Conclusion :

Le déterminant {C, D, E, P} de la dépendance fonctionnelle DF1 : {C, D, E, P} -> {A} vérifie les contraintes d’unicité et d’irréductibilité des clés candidates : il constitue une clé candidate de la relvar PARTICIPER.

Cas de Q2 : Le quadruple {C, D, E, A} est-il surclé ? si oui, est-il clé candidate ?

La question est celle-ci : existe-t-il la dépendance fonctionnelle DF2 : {C, D, E, A} -> {P} ?

Il n’est pas possible que lors d’une compétition d’une année donnée un athlète soit classé à deux places distinctes dans une même course. Lors de la finale du 100 mètres aux Jeux du Relationland en 1994, Albert n’a pas pu être à la fois 6e et 7e :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
C                         D       E             A         P

Jeux du Relationland      1994    100 mètres    Albert    7e
Jeux du Relationland      1994    100 mètres    Albert    6e

Il existe donc bien la dépendance fonctionnelle :

{C, D, E, A} -> {P}

Comme ci-dessus, il faut se poser la question de sa réductibilité.


Les triples que l’ont peut produire à partir du quadruple et qu'il faut examiner sont les suivants :

{C, D, E}, {C, D, A}, {D, E, A}.

On sait déjà que {C, D, E} n’est le déterminant d’aucune dépendance fonctionnelle. Reste le cas de {C, D, A} et celui de {D, E, A}.

Les possibilités sont les suivantes : {C, D, A} -> {E}, {C, D, A} -> {P}, {D, E, A} -> {C}, {D, E, A} -> {P}.

Le cas {C, D, A} -> {E} n’est pas possible car par exemple lors des Jeux du Relationland en 1994, Albert a légalement participé à plus d’une épreuve (100 mètres et 110 haies).

Le cas {C, D, A} -> {P} n’est pas possible car par exemple lors des Jeux du Relationland en 1994, Albert a été classé à des places diverses (7e au 100 mètres et 8e au 110 haies).

Le cas {D, E, A} -> {C} n’est pas possible car par exemple en 1994, Albert a couru sur 100 mètres aux Jeux du Relationland et à ceux de Meriseland.

Le cas {D, E, A} -> {P} n’est pas possible car par exemple en 1994, Albert a fini 7e (c’était sur 100 mètres) et 8e (110 haies).

Ainsi, aucun des triples {C, D, E}, {C, D, A}, {D, E, A} n’est le déterminant d’une dépendance fonctionnelle (non triviale), il en est de même a fortiori pour les paires que l’on peut en inférer.

Conclusion :

Le déterminant {C, D, E, A} de la dépendance fonctionnelle DF2 : {C, D, E, A} -> {P} vérifie les contraintes d’unicité et d’irréductibilité des clés candidates : il constitue une clé candidate de la relvar PARTICIPER.

Cas de Q3 : Le quadruple {C, D, A, P} est-il surclé ? si oui, est-il clé candidate ?

La question est celle-ci : existe-t-il la dépendance fonctionnelle {C, D, A, P} -> {E} ?

Albert a aussi couru le 200 mètres lors des Jeux du Relationland en 1994, et comme au 100 mètres, il a fini 7e de la finale :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
C                         D       A         P         E

Jeux du Relationland      1994    Albert    7e        100 mètres
Jeux du Relationland      1994    Albert    7e        200 mètres
Jeux du Relationland      1994    Albert    8e        110 haies

La dépendance fonctionnelle {C, D, A, P} -> {E} n’existe donc pas.


Cas de Q4 : Le quadruple {C, E, A, P} est-il surclé ? si oui, est-il clé candidate ?

La question est celle-ci : existe-t-il la dépendance fonctionnelle {C, E, A, P} -> {D} ?

Albert a couru le 100 mètres des Jeux du Relationland en 1990 et en 1994, les deux fois il a terminé 7e :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
C                         E             A         P         D

Jeux du Relationland      100 mètres    Albert    7e        1990
Jeux du Relationland      100 mètres    Albert    7e        1994

La dépendance fonctionnelle {C, E, A, P} -> {D} n’existe donc pas.


Cas de Q5 : Le quadruple {D, E, A, P} est-il surclé ? si oui, est-il clé candidate ?

La question est celle-ci : existe-t-il la dépendance fonctionnelle {D, E, A, P} -> {C} ?

En 1990 Albert a fini 7e sur 100 mètres aux Jeux du Relationland et la même année 7e sur 100 mètres aux Jeux du Meriseland :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
D         E             A         P         C

1994      100 mètres    Albert    7e        Jeux du Relationland
1994      100 mètres    Albert    7e        Jeux du Meriseland

La dépendance fonctionnelle {D, E, A, P} -> {C} n’existe donc pas.


En résumé :

La relvar PARTICIPER vérifie les dépendances fonctionnelles non triviales suivantes et aucune autre :

DF1 : {C, D, E, P} -> {A},
DF2 : {C, D, E, A} -> {P}.

On peut maintenant répondre à la question :

La relvar PARTICIPER respecte-t-elle la BCNF (forme normale de Boyce-Codd) ?

La réponse est affirmative.

Rappelons la définition de la BCNF (par exemple celle de Chris Date) :

Une relvar R est en forme normale de Boyce-Codd (BCNF) si et seulement si pour chaque dépendance fonctionnelle non triviale A -> B qui doit être vérifiée par R, A est une surclé de R.

(A et B sont des sous-ensembles d'attributs de l'en-tête de la relvar R).

Les attributs C, D, E, A, P constituent l’en-tête de la relvar et figurent tous dans DF1 : dans le déterminant pour une partie d’entre eux (à savoir C, D, E, P) et dans le dépendant pour l’autre partie (à savoir A).

Le déterminant {C, D, E, P} de la dépendance fonctionnelle non triviale DF1 constitue donc une clé candidate (donc une surclé).

Les attributs C, D, E, A, P constituent l’en-tête de la relvar et figurent tous dans DF2 : dans le déterminant pour une partie d’entre eux (à savoir C, D, E, A) et dans le dépendant pour l’autre partie (à savoir P).

Le déterminant {C, D, E, A} de la dépendance fonctionnelle non triviale DF2 constitue donc une clé candidate (donc une surclé).

DF1 et DF2 sont les seules dépendances fonctionnelles non triviales pour la relvar PARTICIPER et le déterminant de chacune d’elles est une surclé :

La relvar PARTICIPER respecte la BCNF.

Cas des ex-aequo

On a supposé qu’il n’y avait pas d’ex-aequo dans les épreuves. On sait qu’en athlétisme c’est en fait possible, compte tenu d'un chronométrage au 1/100e. Si l’on préfère coller à la réalité et en faire une règle de gestion, alors la dépendance fonctionnelle DF1 n’existe pas et l’ensemble des dépendances fonctionnelles non triviales vérifiées par PARTICIPER est réduit à {DF2} : il n’en demeure pas moins que la relvar continue à respecter la BCNF.

[fsmrel]

Une dernière pour cette nuit...

Elle n'est pas normalisée car position_arrivee est la clé externe de 3 entités en même temps ?

Indépendamment du fait que la relvar PARTICIPER est bien normalisée, je suppose que par clé externe vous voulez dire clé étrangère. Au niveau du Modèle Relationnel de Données (ou de son avatar SQL), position_arrivee est un attribut qui ne participe à aucune clé étrangère.

En effet, le code (en Tutorial D) pour décrire les structures est le suivant (je reprends les noms d’origine) :

Relvars issues des entités-types :

Code D :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
EQUIPE {nom_equipe, pays}
    KEY {nom_equipe} ;

COUREUR {num_coureur, nom_coureur, naissance, nom_equipe}
    KEY {num_coureur}
    FOREIGN KEY {nom_equipe} REFERENCES EQUIPE ;

EPREUVE {nom_epreuve, catégorie}
    KEY {nom_epreuve} ;

COMPETITION {nom_competition, année, lieu}
    KEY {nom_competition, année} ;

Relvar issue de l'association-type PARTICIPER (clés candidates conformes aux conclusions du message précédent, selon le scénario dans lequel il n'y a pas d'ex-aequo) :

Code D :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
PARTICIPER {nom_competition, année, nom_epreuve, num_coureur, position_arrivee}
    KEY {nom_competition, année, nom_epreuve, num_coureur}
    KEY {nom_competition, année, nom_epreuve, position_arrivee}
    FOREIGN KEY {nom_competition, année} REFERENCES COMPETITION
    FOREIGN KEY {nom_epreuve} REFERENCES EPREUVE
    FOREIGN KEY {num_coureur} REFERENCES COUREUR
 ;

Les trois pattes de l’association-type merisienne PARTICIPER ont fait l’objet de trois contraintes référentielles, chacune exprimée par une clé étrangère.


Une remarque en passant :

L’excellentissime Yves Tabourier a énoncé il y a plus de 25 ans une recommandation que l’on peut élever au rang de règle d’or (De l’autre côté de MERISE page 80) :

« ... la fonction d’une propriété est de décrire les objets (et les rencontres), alors que l’identifiant ne décrit rien. Son rôle fondamental est d’être sûr de distinguer deux jumeaux parfaits, malgré des descriptions identiques.
L’expérience montre d’ailleurs que l’usage des “identifiants significatifs” (ou “codes significatifs”) a pu provoquer des dégâts tellement coûteux que la sagesse est d’éviter avec le plus grand soin de construire des identifiants décrivant les objets ou, pis encore, leurs liens avec d’autres objets... »

Vous direz à l’auteur du MCD que ses identifiants ne sont pas bien conformes...

[fsmrel]

Bonsoir,


Voici un script permettant de faire des tests en relation avec la BCNF. A noter que je n’avais pas vu qu’un athlète (alias coureur) pouvait faire partie de plus d’une équipe, je rectifie en conséquence en ajoutant une table ATHLETE_EQUIPE, mais ceci n’a aucune incidence sur ce dont nous avons traité.

J’ai conservé le type d’origine (CHAR) pour les attributs qui participent aux clés primaires, mais il est évident que pour une modélisation satisfaisante, il faudra ajouter des attributs ad-hoc, pour qu’on puisse mettre en œuvre des clés invariantes et non significatives !


Les tables :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
CREATE TABLE EQUIPE
(
        nom_equipe            CHAR(32)           NOT NULL
      , pays                  CHAR(32)           NOT NULL
    , CONSTRAINT EQUIPE_PK PRIMARY KEY (nom_equipe) 
) ;
CREATE TABLE ATHLETE    --   initialement COUREUR 
(
        num_athlete           INT                NOT NULL
      , nom_athlete           CHAR(32)           NOT NULL
      , naissance             DATE               NOT NULL
    , CONSTRAINT ATHLETE_PK PRIMARY KEY (num_athlete) 
) ;
CREATE TABLE ATHLETE_EQUIPE 
(
        num_athlete           INT                NOT NULL
      , nom_equipe            CHAR(32)           NOT NULL
    , CONSTRAINT ATHLETE_EQUIPE_PK PRIMARY KEY (num_athlete, nom_equipe) 
    , CONSTRAINT ATHLETE_EQUIPE_ATHLETE_FK FOREIGN KEY (num_athlete) REFERENCES ATHLETE
    , CONSTRAINT ATHLETE_EQUIPE_EQUIPE_FK FOREIGN KEY (nom_equipe) REFERENCES EQUIPE
) ;
CREATE TABLE EPREUVE
(
        nom_epreuve           CHAR(32)           NOT NULL
      , catégorie             CHAR(32)           NOT NULL
    , CONSTRAINT EPREUVE_PK PRIMARY KEY (nom_epreuve) 
) ;
CREATE TABLE COMPETITION
(
        nom_competition       CHAR(32)           NOT NULL
      , année                 INT                NOT NULL
      , lieu                  CHAR(32)           NOT NULL
    , CONSTRAINT COMPETITION_PK PRIMARY KEY (nom_competition, année) 
) ;
CREATE TABLE PARTICIPER
(
        nom_competition       CHAR(32)           NOT NULL
      , année                 INT                NOT NULL
      , nom_epreuve           CHAR(32)           NOT NULL
      , num_athlete           INT                NOT NULL
      , position_arrivee      INT                NOT NULL
    , CONSTRAINT PARTICIPER_PK PRIMARY KEY (nom_competition, année, nom_epreuve, num_athlete) 
    , CONSTRAINT PARTICIPER_AK UNIQUE (nom_competition, année, nom_epreuve, position_arrivee) 
    , CONSTRAINT PARTICIPER_COMPETITION_FK FOREIGN KEY (nom_competition, année) REFERENCES COMPETITION
    , CONSTRAINT PARTICIPER_EPREUVE_FK FOREIGN KEY (nom_epreuve) REFERENCES EPREUVE
    , CONSTRAINT PARTICIPER_ATHLETE_FK FOREIGN KEY (num_athlete) REFERENCES ATHLETE
) ;

Un jeu d’essai :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
INSERT INTO EQUIPE (nom_equipe, pays) VALUES ('E1', 'P1') ;
INSERT INTO EQUIPE (nom_equipe, pays) VALUES ('E2', 'P1') ;
INSERT INTO EQUIPE (nom_equipe, pays) VALUES ('E3', 'P2') ;
INSERT INTO EQUIPE (nom_equipe, pays) VALUES ('E4', 'P1') ;
INSERT INTO EQUIPE (nom_equipe, pays) VALUES ('E5', 'P3') ;
 
SELECT '' AS EQUIPE, * FROM EQUIPE
 
INSERT INTO ATHLETE (num_athlete, nom_athlete, naissance) VALUES (1, 'Albert', '1985-07-14') ;
INSERT INTO ATHLETE (num_athlete, nom_athlete, naissance) VALUES (2, 'Bernard', '1985-04-01') ;
INSERT INTO ATHLETE (num_athlete, nom_athlete, naissance) VALUES (21, 'Usain', '1986-08-21') ;
 
SELECT '' AS ATHLETE, * FROM ATHLETE
 
INSERT INTO COMPETITION (nom_competition, année, lieu) VALUES ('Jeux du Relationland', 1990, 'Raymond Boyce City') ;
INSERT INTO COMPETITION (nom_competition, année, lieu) VALUES ('Jeux du Relationland', 1994, 'Ted Codd City') ;
INSERT INTO COMPETITION (nom_competition, année, lieu) VALUES ('Jeux du Relationland', 1999, 'Jim Gray City') ;
INSERT INTO COMPETITION (nom_competition, année, lieu) VALUES ('Jeux du Meriseland', 1994, 'TabourierVille') ;
 
SELECT '' AS COMPETITION, * FROM COMPETITION
 
INSERT INTO EPREUVE (nom_epreuve, catégorie) VALUES ('100 mètres', 'courses') ;
INSERT INTO EPREUVE (nom_epreuve, catégorie) VALUES ('200 mètres', 'courses') ;
INSERT INTO EPREUVE (nom_epreuve, catégorie) VALUES ('110 mètres haies', 'courses') ;
INSERT INTO EPREUVE (nom_epreuve, catégorie) VALUES ('Longueur', 'sauts') ;
INSERT INTO EPREUVE (nom_epreuve, catégorie) VALUES ('Perche', 'sauts') ;
 
SELECT '' AS EPREUVE, * FROM EPREUVE
 
INSERT INTO PARTICIPER (nom_competition, année, nom_epreuve, num_athlete, position_arrivee) VALUES ('Jeux du Relationland', 1994, '100 mètres', 1, 7) ;
INSERT INTO PARTICIPER (nom_competition, année, nom_epreuve, num_athlete, position_arrivee) VALUES ('Jeux du Relationland', 1994, '110 mètres haies', 1, 8) ;
INSERT INTO PARTICIPER (nom_competition, année, nom_epreuve, num_athlete, position_arrivee) VALUES ('Jeux du Relationland', 1994, '100 mètres', 2, 3) ;
INSERT INTO PARTICIPER (nom_competition, année, nom_epreuve, num_athlete, position_arrivee) VALUES ('Jeux du Relationland', 1999, '100 mètres', 1, 5) ;
INSERT INTO PARTICIPER (nom_competition, année, nom_epreuve, num_athlete, position_arrivee) VALUES ('Jeux du Meriseland', 1994, '100 mètres', 1, 1) ;
-- INSERT INTO PARTICIPER (nom_competition, année, nom_epreuve, num_athlete, position_arrivee) VALUES ('Jeux du Relationland', 1994, '100 mètres', 21, 7) ; -- doublon !
-- INSERT INTO PARTICIPER (nom_competition, année, nom_epreuve, num_athlete, position_arrivee) VALUES ('Jeux du Relationland', 1994, '100 mètres', 1, 6) ; -- doublon !
INSERT INTO PARTICIPER (nom_competition, année, nom_epreuve, num_athlete, position_arrivee) VALUES ('Jeux du Relationland', 1994, '200 mètres', 1, 7) ;
INSERT INTO PARTICIPER (nom_competition, année, nom_epreuve, num_athlete, position_arrivee) VALUES ('Jeux du Meriseland', 1994, '200 mètres', 1, 7) ;
 
SELECT '' AS PARTICIPER, * FROM PARTICIPER

Accessoirement, je relève que, côté modélisation, il y a du mou dan la corde à nœuds. Étant donné qu'un athlète peut faire partie de plusieurs équipes, quel drapeau faire grimper en haut du mât quand un athlète est sur la plus haute marche du podium ? (Selon le MCD initial, un athlète peut faire partie de plus d'une équipe, chacune appartenant à son pays...)

Et bien entendu, tout en étant a priori indépendantes, orthogonales à la normalisation des tables, les observations de Richard sont à prendre en compte (cas par exemple du catalogue des compétions et épreuves).

Bref il reste encore du boulot côté modélisation.