Discussion :

[Jesspredator]

Bonjour tout le monde !

Je me dirige vers vous pour avoir des avis et une aide à propos de l'élaboration d'un schéma MCD en vue de mettre au point une BD sur laquelle reposera un programme de gestion de tournoi de football.
Je suis pas trop sûr de ce que j'ai fais jusqu'à présent, je me sens pas encore très a l'aise avec l'analyse. J'espère que vous pourrez m'aider à avancer

Le but donc, c'est de faire tourner un programme pour gérer des match de foot dans un tournoi (PS: système de point, donc chaque équipe joue contre toutes les autres équipes 1 fois)

• En gros, un tournoi par année, divisé en deux saisons (semestre) et compte un nombre d'équipe à définir au préalable.
  
• Une équipe a 5 joueurs minimum et 10 maximum (contrainte à préciser dans la DB
  
• Un joueur joue que dans une équipe mais peut à l'intersaison ou dans un autre tournoi, changer d'équipe(bizarre je sais). Et comme contrainte, il peut changer que dans les trois dernières équipes auxquelles il a déjà participé.
  
• Une feuille de match regroupe toutes les informations à propos d'un match donné.
  Avant chaque match, on inscrit les équipes, la date du match, les joueurs qui jouent.
  Après le match, on y inscrit le résultat, les goals par joueur, les cartons par joueur.
  
• Un match est joué par 5 joueurs/équipe minimum ou 7 joueurs/équipe maximum.
  
• Carton jaune = 1 exclusion d'un match sur le semestre, carton rouge = 3 trois exclusions de match dès le match suivant. Attention, les cartons sont cumulables.

Voilà j'espère que c'est pas trop ennuyeux à lire ... Je vous met le schéma que j'ai effectué jusqu'à présent :

[fsmrel]

Bonsoir Jesspredator,

Avant examen du diagramme, une question relative à ceci :

En gros, un tournoi par année, divisé en deux saisons (semestre).

Si deux équipes se sont rencontrées au cours du 1er semestre, elles ne pourront pas se rencontrer au cours du 2e semestre. C’est bien ça ?

 

[fsmrel]

Bonsoir Jesspredator,

Je suis parti du principe qu'à la question que j'ai posée, vous avez répondu positivement.

Votre entité-type championship comporte un attribut team_nbr qui est à faire disparaître, car pour chaque année on en obtiendra la valeur par comptage dans la table participate.

Les attributs team_1 et team_2 n’ont rien à faire dans l’entité-type match, par contre il doit y avoir une association réflexive entre team et match.

Pour ce qui constitue le coeur du modèle, je verrais plutôt les choses ainsi, avec l’outil Looping, gracieusement proposé par le professeur Patrick Bergougnoux (j’ai francisé les noms que vous avez fournis) :

Pour aboutir après quelques retouches manuelles au code SQL suivant (avec SQL Server), dont on peut débattre !

CREATE TABLE TOURNOI
(
        tournoiId           INT            NOT NULL IDENTITY(1,1)
      , tournoiAnnee        INT            NOT NULL          
      , tournoiCommentaire  VARCHAR(255)   NOT NULL DEFAULT ''     
    , CONSTRAINT TOURNOI_PK PRIMARY KEY (tournoiId)
    , CONSTRAINT TOURNOI_AK UNIQUE (tournoiAnnee)
) ;
CREATE TABLE SAISON
(
        tournoiId           INT            NOT NULL
      , saisonId            INT            NOT NULL    IDENTITY(1,1)
      , saisonSemestre      DECIMAL(1,0)   NOT NULL
    , CONSTRAINT SAISON_PK PRIMARY KEY (tournoiId, saisonId)
    , CONSTRAINT SAISON_AK UNIQUE (tournoiId, saisonSemestre)
    , CONSTRAINT SAISON_TOURNOI_FK FOREIGN KEY (tournoiId)
          REFERENCES TOURNOI (tournoiId)
    , CONSTRAINT SAISON_SEMESTRE CHECK (saisonSemestre IN (1, 2))
) ;
CREATE TABLE EQUIPE
(
        equipeId            INT            NOT NULL IDENTITY(1,1)
      , equipeMatricule     VARCHAR(8)     NOT NULL  
      , equipeNom           VARCHAR(32)    NOT NULL  
    , CONSTRAINT EQUIPE_PK PRIMARY KEY (equipeId)
    , CONSTRAINT EQUIPE_MAT_AK UNIQUE (equipeMatricule)
    , CONSTRAINT EQUIPE_NOM_AK UNIQUE (equipeNom)
) ;

CREATE TABLE JOUEUR
(
        joueurId            INT            NOT NULL IDENTITY(1,1)
      , joueurMatricule     VARCHAR(8)     NOT NULL  
      , joueurNom           VARCHAR(32)    NOT NULL          
    , CONSTRAINT JOUEUR_PK PRIMARY KEY (joueurId)
    , CONSTRAINT JOUEUR_MAT_AK UNIQUE (joueurMatricule)
) ;

CREATE TABLE JOUEUR_EQUIPE
(
        joueurId            INT            NOT NULL
      , equipeId            INT            NOT NULL 
      , tournoiId           INT            NOT NULL 
      , saisonId            INT            NOT NULL 
    , CONSTRAINT JOUEUR_EQUIPE_PK PRIMARY KEY (joueurId, tournoiId, saisonId)
    , CONSTRAINT JOUEUR_EQUIPE_JOUEUR_FK FOREIGN KEY (joueurId)
          REFERENCES JOUEUR (joueurId)
    , CONSTRAINT JOUEUR_EQUIPE_EQUIPE_FK FOREIGN KEY (equipeId)
          REFERENCES EQUIPE (equipeId)
    , CONSTRAINT JOUEUR_EQUIPE_SAISON_FK FOREIGN KEY (tournoiId, saisonId)
          REFERENCES SAISON (tournoiId, saisonId)
) ;

CREATE TABLE RENCONTRE
(
        equipeAId           INT            NOT NULL 
      , equipeBId           INT            NOT NULL 
      , tournoiAnnee        INT            NOT NULL 
      , equipeAbuts         INT            NOT NULL DEFAULT 0 
      , equipeBbuts         INT            NOT NULL DEFAULT 0 
    , CONSTRAINT RENCONTRE_PK PRIMARY KEY (equipeAId, equipeBId, tournoiAnnee)
    , CONSTRAINT RENCONTRE_EQUIPE_A_FK FOREIGN KEY (equipeAId) REFERENCES EQUIPE (equipeId)
    , CONSTRAINT RENCONTRE_EQUIPE_B_FK FOREIGN KEY (equipeBId) REFERENCES EQUIPE (equipeId)
    , CONSTRAINT RENCONTRE_ANNEE_FK FOREIGN KEY (tournoiAnnee) REFERENCES TOURNOI (tournoiAnnee)
    , CONSTRAINT RECONTRE_CHCK CHECK (equipeAId < equipeBId) 
) ;

CREATE TABLE JOUEUR_RENCONTRE
(
        joueurId            INT            NOT NULL
      , equipeAId           INT            NOT NULL
      , equipeBId           INT            NOT NULL
      , tournoiId           INT            NOT NULL 
      , saisonId            INT            NOT NULL
    , CONSTRAINT JOUEUR_RENCONTRE_PK PRIMARY KEY (joueurId, equipeAId, tournoiId, saisonId) 
    , CONSTRAINT JOUEUR_RENCONTRE_AK UNIQUE (joueurId, equipeBId, tournoiId, saisonId) 
    , CONSTRAINT JOUEUR_RENCONTRE_EQUIPE_FK FOREIGN KEY (joueurId, tournoiId, saisonId) 
          REFERENCES JOUEUR_EQUIPE (joueurId, tournoiId, saisonId)  
) ;

 

[Jesspredator]

Bonsoir Jesspredator,

Avant examen du diagramme, une question relative à ceci :



Si deux équipes se sont rencontrées au cours du 1er semestre, elles ne pourront pas se rencontrer au cours du 2e semestre. C’est bien ça ?

 

Effectivement, le système de point permet seulement une rencontre entre deux mêmes équipes par année.

Je suppose que "JOUEUR_EQUIPE" correspond à la relation "inscription" qui devient une table de relation, "JOUEUR_RECONTRE" correspond à "match sheet ou feuille de match en fr" et "RENCONTRE" à "match", c'est bien ça ?

Pourrais-tu un peu m'expliquer l'association réflective du coup ? Elle prend quoi en compte ?
Parce que effectivement je ne l'avais pas précisé au début, mais un goal est marqué par un joueur d'une équipe qui est inscrit à la feuille de match (car un joueur d'une équipe peut ne pas jouer et qu'un goal forcément, est marqué par un joueur qui joue).

Information supplémentaire, de base je voulais garder le projet simple ici pour que ce soit pas trop prise de tête, mais je vais devoir aussi crée un planning des matchs.
Les matchs se jouent automatiquement les samedi et dimanche. si il y a trop de match, on doit pouvoir choisir des jours différents dans la semaine.
Je pensait à générer un calendrier automatiquement et au niveau business, permettre de sélectionner un date pour un match. En aillant comme règle qu'il y a un match par jour maximum (ou deux je verrai bien)

La date du match et le calendrier devront être au niveau de "JOUEUR_RENCONTRE" ?

[fsmrel]

Bonsoir Jesspredator,

Je suppose que "JOUEUR_EQUIPE" correspond à la relation "inscription"

C’est bien ça.

"RENCONTRE" à "match"

C’est bien ça.

"JOUEUR_RENCONTRE" correspond à "match sheet"

A condition de transformer INSCRIPTION en entité-type (JOUEUR_EQUIPE dans le MCD que je propose).


Pour traiter des buts marqués, j’ai été amené à transformer l’association JOUEUR_RENCONTRE en entité-type :

Dans la structure de la table RENCONTRE, j’ai remplacé tournoiAnnee par tournoiId :

CREATE TABLE RENCONTRE
(
        equipeAId           INT            NOT NULL 
      , equipeBId           INT            NOT NULL 
      , tournoiId           INT            NOT NULL 
      , equipeAbuts         INT            NOT NULL DEFAULT 0 
      , equipeBbuts         INT            NOT NULL DEFAULT 0 
    , CONSTRAINT RENCONTRE_PK PRIMARY KEY (equipeAId, equipeBId, tournoiId)
    , CONSTRAINT RENCONTRE_EQUIPE_A_FK FOREIGN KEY (equipeAId) REFERENCES EQUIPE (equipeId)
    , CONSTRAINT RENCONTRE_EQUIPE_B_FK FOREIGN KEY (equipeBId) REFERENCES EQUIPE (equipeId)
    , CONSTRAINT RENCONTRE_ANNEE_FK FOREIGN KEY (tournoiId) REFERENCES TOURNOI (tournoiId)
    , CONSTRAINT RECONTRE_CHCK CHECK (equipeAId < equipeBId) 
) ;

A noter la contrainte RECONTRE_CHCK : « equipeAId < equipeBId » qui permet d’autoriser les paires (a,b) et d’interdire les paires (a,a) et (b,a), à savoir interdire de jouer contre soi-même et interdire les matchs retour.

Les colonnes equipeAbuts et equipeBbuts ne sont pas a priori nécessaires car calculables. On verra.


Déclaration de la table JOUEUR_RENCONTRE. Avec cette structure, on sait que tel joueur a participé à telle rencontre, mais sans préciser s’il est de l’équipe A ou de l’équipe B.

CREATE TABLE JOUEUR_RENCONTRE
(
        joueurId            INT            NOT NULL
      , equipeAId           INT            NOT NULL
      , equipeBId           INT            NOT NULL
      , tournoiId           INT            NOT NULL 
      , saisonId            INT            NOT NULL
    , CONSTRAINT JOUEUR_RENCONTRE_PK PRIMARY KEY (joueurId, equipeAId, tournoiId, saisonId) 
    , CONSTRAINT JOUEUR_RENCONTRE_AK UNIQUE (joueurId, equipeBId, tournoiId, saisonId) 
    , CONSTRAINT JOUEUR_RENCONTRE_EQUIPE_FK FOREIGN KEY (joueurId, tournoiId, saisonId) 
          REFERENCES JOUEUR_EQUIPE (joueurId, tournoiId, saisonId)  
    , CONSTRAINT JOUEUR_RENCONTRE_RENCONTRE_FK FOREIGN KEY (equipeAId, equipeBId, tournoiId) 
          REFERENCES RENCONTRE (equipeAId, equipeBId, tournoiId)  
) ;

La table JOUEUR_BUT permet de savoir quel joueur a marqué un but à quel moment. Ce joueur ne fait pas forcément partie de l’équipe A (qui ne sert que de référence vers JOUEUR_RENCONTRE) mais peut faire partie de l’équipe B. Structure de la table :

CREATE TABLE JOUEUR_BUT
(
        joueurId            INT            NOT NULL
      , equipeAId           INT            NOT NULL
      , tournoiId           INT            NOT NULL 
      , saisonId            INT            NOT NULL
      , butMinute           INT            NOT NULL
    , CONSTRAINT JOUEUR_BUT_PK PRIMARY KEY (joueurId, equipeAId, tournoiId, saisonId, butMinute) 
    , CONSTRAINT JOUEUR_BUT_RENCONTRE_FK FOREIGN KEY (joueurId, equipeAId, tournoiId, saisonId) 
          REFERENCES JOUEUR_RENCONTRE (joueurId, equipeAId, tournoiId, saisonId)  
) ;

Par exemple, le joueur Raoul, dont le matricule est 'rav' et se trouve faire partie de l’équipe B adverse de l’équipe A, a marqué à la 20e et à la 42e minutes :

INSERT INTO JOUEUR_BUT (joueurId, equipeAId, tournoiId, saisonId, butMinute) 
SELECT DISTINCT joueurId, equipeAId, tournoiId, saisonId, 20
FROM   JOUEUR_RENCONTRE
WHERE  joueurid = (SELECT joueurId FROM JOUEUR WHERE joueurMatricule = 'rav')
;  
INSERT INTO JOUEUR_BUT (joueurId, equipeAId, tournoiId, saisonId, butMinute) 
SELECT DISTINCT joueurId, equipeAId, tournoiId, saisonId, 42
FROM   JOUEUR_RENCONTRE
WHERE  joueurid = (SELECT joueurId FROM JOUEUR WHERE joueurMatricule = 'rav')
;  

Reste à peaufiner, définir si c’est sur penalty, contre son camp, etc.

La modélisation des cartons devrait ressembler à celle des buts marqués.

Dans votre diagramme, l’association HAPPEN doit disparaître, car manifestement redondante. En effet, on obtient l’information but > rencontre via le chemin JOUEUR_BUT > JOUEUR_RENCONTRE > RENCONTRE.

La date du match et le calendrier devront être au niveau de "JOUEUR_RENCONTRE" ?

Pour le calendrier, je regarderai, mais en tout cas c’est l’entité-type RENCONTRE qui est impliquée dans cette affaire, les joueurs ne sont pas parties prenantes.

Pourrais-tu un peu m'expliquer l'association réflective du coup ? Elle prend quoi en compte ?
Parce que effectivement je ne l'avais pas précisé au début, mais un goal est marqué par un joueur d'une équipe qui est inscrit à la feuille de match (car un joueur d'une équipe peut ne pas jouer et qu'un goal forcément, est marqué par un joueur qui joue).

Je reviendrai sur ça, mais en tout cas l’entité-type JOUEUR_BUT permet de limiter les marqueurs de buts à ceux qui jouent.


 

[Jesspredator]

Ok un tout grand merci fsmrel.

Je vais déjà essayer de mettre le tout en œuvre, placer les contraintes éventuelles et voir si j'arrive à tout retrouver comme il faut avec les relations.
Merci de ton temps et ton aide précieuse


MAJ: Voilà, j'ai un peu testé le tout, ça m'a permis de mieux comprendre aussi. C'est plutôt pas mal tout ça

Pour les cartons, vu que lors de "JOUEUR_RENCONTRE", j'inscris les différents joueur pour le match, il serait pas mieux de mettre un attribut "carton_rouge" et "carton_jaune" au niveau de "JOUEUR" ?
Si le joueur a un carton, il se peut qu'il ne puisse pas s'inscrire ou jouer le match actuel. Donc il faudrait savoir à l'avance si un joueur possède déjà des cartons ou pas (Je sais pas si tu me comprend).

[fsmrel]

Bonsoir Jesspredator,


Où en êtes-vous quant à votre compréhension de la réflexive RENCONTRE ? C’est une association entre EQUIPE et ... EQUIPE, puisqu’il s’agit de mettre en relation des paires d’équipes, un 1ere équipe jouant le rôle disons A et l’autre le rôle B. De leur côté, les joueurs viennent ensuite s’agglutiner sur RENCONTRE (cf. table JOUEUR_RENCONTRE), qu’ils soient de l’équipe A ou de l’équipe B. L’objet de JOUEUR_RENCONTRE est de permettre de savoir qui a participé au match entre l’équipe A et l’équipe B..

En ce qui concerne les dates, arrivez-vous à stabiliser votre réflexion ?

Quant aux cartons, évitons de faire un big mac, séparons les jaunes des rouges. On tient compte du fait qu’un joueur ne peut prendre qu’un carton rouge au cours d’un match :

D’où le code SQL, où la structure des tables reprend celle de la table JOUEUR_BUT :

CREATE TABLE JOUEUR_CARTON_JAUNE
(
        joueurId            INT            NOT NULL
      , equipeAId           INT            NOT NULL
      , tournoiId           INT            NOT NULL 
      , saisonId            INT            NOT NULL
      , cartonMinute        INT            NOT NULL
    , CONSTRAINT JOUEUR_CARTON_JAUNE_PK PRIMARY KEY (joueurId, equipeAId, tournoiId, saisonId, cartonMinute) 
    , CONSTRAINT JOUEUR_CARTON_JAUNE_RENCONTRE_FK FOREIGN KEY (joueurId, equipeAId, tournoiId, saisonId) 
          REFERENCES JOUEUR_RENCONTRE (joueurId, equipeAId, tournoiId, saisonId)  
) ;

CREATE TABLE JOUEUR_CARTON_ROUGE
(
        joueurId            INT            NOT NULL
      , equipeAId           INT            NOT NULL
      , tournoiId           INT            NOT NULL 
      , saisonId            INT            NOT NULL
      , cartonMinute        INT            NOT NULL
    , CONSTRAINT JOUEUR_CARTON_ROUGE_PK PRIMARY KEY (joueurId, equipeAId, tournoiId, saisonId) 
    , CONSTRAINT JOUEUR_CARTON_ROUGE_RENCONTRE_FK FOREIGN KEY (joueurId, equipeAId, tournoiId, saisonId) 
          REFERENCES JOUEUR_RENCONTRE (joueurId, equipeAId, tournoiId, saisonId)  
) ; 

Par exemple, le joueur Raoul, dont le matricule est 'rav' et se trouve faire partie de l’équipe B adverse de l’équipe A (c’est lui qui a marqué à la 20e et à la 42e minutes), s’est pris un carton jaune à la 57e minute :

INSERT INTO JOUEUR_CARTON_JAUNE(joueurId, equipeAId, tournoiId, saisonId, cartonMinute) 
SELECT DISTINCT joueurId, equipeAId, tournoiId, saisonId, 57
FROM   JOUEUR_RENCONTRE
WHERE  joueurid = (SELECT joueurId FROM JOUEUR WHERE joueurMatricule = 'rav')
; 

Mais son adversaire Jean (matricule 'jea') s’est pris un carton rouge à la 65e minute, tandis que son partenaire 'pav' s’en est pris un à la 67e minute, tout ça dans le même match :

INSERT INTO JOUEUR_CARTON_ROUGE (joueurId, equipeAId, tournoiId, saisonId, cartonMinute) 
SELECT DISTINCT joueurId, equipeAId, tournoiId, saisonId, 65
FROM   JOUEUR_RENCONTRE
WHERE  joueurid = (SELECT joueurId FROM JOUEUR WHERE joueurMatricule = 'jea')
; 
INSERT INTO JOUEUR_CARTON_ROUGE (joueurId, equipeAId, tournoiId, saisonId, cartonMinute) 
SELECT DISTINCT joueurId, equipeAId, tournoiId, saisonId, 67
FROM   JOUEUR_RENCONTRE
WHERE  joueurid = (SELECT joueurId FROM JOUEUR WHERE joueurMatricule = 'pav')
; 

N.B. Si tel ou tel post a pu vous aider, n’hésitez pas à liker


 

[Jesspredator]

Bonsoir Jesspredator,
En ce qui concerne les dates, arrivez-vous à stabiliser votre réflexion ?

Je pensais à mettre DATE au niveau de RENCONTRE. Il ne peut y avoir que un match par DATE, qui fera partie de la primary key. RENCONTRE reprend le nom des équipes participantes au match, ainsi que le tournois en question.
Et JOUEUR_RENCONTRE aura DATE en FK qui fera référence donc à RENCONTRE.

C'est jouable ?

Où en êtes-vous quant à votre compréhension de la réflexive RENCONTRE ? C’est une association entre EQUIPE et ... EQUIPE, puisqu’il s’agit de mettre en relation des paires d’équipes, un 1ere équipe jouant le rôle disons A et l’autre le rôle B. De leur côté, les joueurs viennent ensuite s’agglutiner sur RENCONTRE (cf. table JOUEUR_RENCONTRE), qu’ils soient de l’équipe A ou de l’équipe B. L’objet de JOUEUR_RENCONTRE est de permettre de savoir qui a participé au match entre l’équipe A et l’équipe B..

Je comprend mieux la réflexive, merci pour l'explication

Quant aux cartons, évitons de faire un big mac, séparons les jaunes des rouges. On tient compte du fait qu’un joueur ne peut prendre qu’un carton rouge au cours d’un match :

Je pensais effectivement faire la même chose, un carton rouge au maximum pour éviter les complications inutiles. Je vois en effet que le comportement des cartons est similaire à celui de JOUEUR_BUT.

N.B. Si tel ou tel post a pu vous aider, n’hésitez pas à liker

C'est fait ^^

 

[fsmrel]

Bonsoir Jesspredator,

Il ne peut y avoir que un match par DATE

Donc la date du match devient clé candidate de la table RENCONTRE.

CREATE TABLE RENCONTRE
(
        equipeAId           INT            NOT NULL 
      , equipeBId           INT            NOT NULL 
      , tournoiId           INT            NOT NULL 
      , rencontreDate       DATE           NOT NULL 
      , equipeAbuts         INT            NOT NULL DEFAULT 0 
      , equipeBbuts         INT            NOT NULL DEFAULT 0
    , CONSTRAINT RENCONTRE_PK PRIMARY KEY (equipeAId, equipeBId, tournoiId)
    , CONSTRAINT RENCONTRE_AK UNIQUE (rencontreDate)
    , CONSTRAINT RENCONTRE_EQUIPE_A_FK FOREIGN KEY (equipeAId) 
          REFERENCES EQUIPE (equipeId)  
    , CONSTRAINT RENCONTRE_EQUIPE_B_FK FOREIGN KEY (equipeBId) 
          REFERENCES EQUIPE (equipeId)
    , CONSTRAINT RENCONTRE_ANNEE_FK FOREIGN KEY (tournoiId) 
          REFERENCES TOURNOI (tournoiId)
    , CONSTRAINT RENCONTRE_CHCK CHECK (equipeAId < equipeBId)
) ;

Cette date peut-elle devenir clé primaire ?

Attention au jour où il sera décidé qu’on peut avoir plus d’un match le même jour ! La mise à niveau de la base de données sera délicate (conséquence par exemple sur la table JOUEUR_RENCONTRE outre la table RENCONTRE)...

Et puis rien n’empêchera des situations fâcheuses, par exemple que Freddy, joueur de l’équipe de Cancale, a participé au match opposant Montauban et Palaiseau :

EQUIPE {équipe}
        Montauban
        Palaiseau
        Cancale  

JOUEUR_EQUIPE {joueur    équipe}
               Fernand   Montauban
               Jean      Montauban
               Raoul     Palaiseau
               Freddy    Cancale  

RENCONTRE {rencontreDate   équipe A    équipe B}
           2020-03-01      Montauban   Palaiseau
           2020-03-08      Montauban   Cancale

JOUEUR_RENCONTRE {joueur    rencontreDate}
                  Freddy    2020-03-01

Du trapèze sans filet. Un doigt qui fourche sur une touche et c’est le bug...

Pour ma part, je préfère très nettement conserver le filet.

Vous me direz qu’en conservant la clé primaire actuelle de la table JOUEUR_RENCONTRE on n’est pas mieux loti, et c’est exact ! J’installe donc un filet. C’est un peu tarabiscoté comme manip, mais au moins le contrôle est effectif sans qu’on ait rien à programmer. Le but de la manœuvre est de propager l’équipe du joueur participant à une rencontre (attribut equipeId) jusque dans la structure de la table JOUEUR_RENCONTRE à des fins de contrôle par rapport à la table RENCONTRE.

Pour cela, je commence par ajouter une surclé {joueurId, equipeId, tournoiId, saisonId} à la table JOUEUR_EQUIPE :

CREATE TABLE JOUEUR_EQUIPE
(
        joueurId            INT            NOT NULL
      , equipeId            INT            NOT NULL 
      , tournoiId           INT            NOT NULL 
      , saisonId            INT            NOT NULL 
    , CONSTRAINT JOUEUR_EQUIPE_PK PRIMARY KEY (joueurId, tournoiId, saisonId)
    , CONSTRAINT JOUEUR_EQUIPE_AK UNIQUE (joueurId, equipeId, tournoiId, saisonId)  
    , CONSTRAINT JOUEUR_EQUIPE_JOUEUR_FK FOREIGN KEY (joueurId)
          REFERENCES JOUEUR (joueurId)
    , CONSTRAINT JOUEUR_EQUIPE_EQUIPE_FK FOREIGN KEY (equipeId)
          REFERENCES EQUIPE (equipeId)
    , CONSTRAINT JOUEUR_EQUIPE_SAISON_FK FOREIGN KEY (tournoiId, saisonId)
          REFERENCES SAISON (tournoiId, saisonId)
) ;

Dans un 2e temps, dans la table JOUEUR_RENCONTRE j’enrichis la clé étrangère JOUEUR_RENCONTRE_EQUIPE_FK et lui fais référencer non pas la clé primaire, mais la surclé qu’on vient de déclarer pour la table JOUEUR_EQUIPE.

Et, kolossale finesse, j’ajoute les épingles à nourrice qui vont permettre d’empêcher que Freddy vienne participer à une rencontre entre des clubs dont il ne fait pas partie. Ces épingles sont représentées par la contrainte JOUEUR_RENCONTRE_CHK qui dit que l’équipe de Freddy doit être une des protagonistes de la rencontre entre les équipes A et B : « equipeId = equipeAId OR equipeId = equipeBId ».

CREATE TABLE JOUEUR_RENCONTRE
(
        joueurId            INT            NOT NULL
      , equipeId            INT            NOT NULL
      , equipeAId           INT            NOT NULL
      , equipeBId           INT            NOT NULL
      , tournoiId           INT            NOT NULL 
      , saisonId            INT            NOT NULL
    , CONSTRAINT JOUEUR_RENCONTRE_PK PRIMARY KEY (joueurId, equipeAId, tournoiId, saisonId) 
    , CONSTRAINT JOUEUR_RENCONTRE_AK UNIQUE (joueurId, equipeBId, tournoiId, saisonId) 
    , CONSTRAINT JOUEUR_RENCONTRE_EQUIPE_FK FOREIGN KEY (joueurId, equipeId, tournoiId, saisonId) 
          REFERENCES JOUEUR_EQUIPE (joueurId, equipeId, tournoiId, saisonId)   
    , CONSTRAINT JOUEUR_RENCONTRE_RENCONTRE_FK FOREIGN KEY (equipeAId, equipeBId, tournoiId) 
          REFERENCES RENCONTRE (equipeAId, equipeBId, tournoiId)
    , CONSTRAINT JOUEUR_RENCONTRE_CHK CHECK (equipeId = equipeAId OR equipeId = equipeBId)    
) ;

Le jour où les SGBD SQL proposeront l’instruction CREATE ASSERTION proposée par la norme SQL, on pourra simplifier...

Peut-on faire la même chose si rencontreDate devient clé primaire de la table RENCONTRE ? Non, car les colonnes equipeAId et equipeBId sont alors définitivement absentes de la table JOUEUR_RENCONTRE.

Bon, ça n’est du SQL classique, c’est du brutal, mais ça marche...

Votre point de vue ? Trapèze avec filet ? Sans filet ?

En tout cas, merci pour les votes ! ^^

 

[Jesspredator]

Désolé de mon absence, j'étais assez pris ces derniers jours.

Bonsoir Jesspredator,

Votre point de vue ? Trapèze avec filet ? Sans filet ?

 

Je préfère nettement avec filet. J'ai pas envie de mettre le bordel par accident dans la DB

En tout cas, merci pour les votes ! ^^

 

Avec plaisir, c'est normal quand on reçoit de l'aide et du temps d'une personne ^^


Je me rend compte que je manque pas mal d'expérience en base de donnée, voir vos présentations de solution sont un apprentissage pour moi et ça fait du bien.

Autre question : Si par exemple j'aimerai mettre deux contraintes pour les équipes. Du genre, une équipe doit avoir au minimum 4 joueurs et maximum 8 joueurs pour pouvoir participer à un match.
Je peux mettre cela en place en créant une fonction qui sera appelée dans une contrainte CHECK ou il est préférable d'utiliser un trigger ?

[Mat.M]

bonjour jess ne pas perdre de vue qu'un MCD n'est pas figé vous serez amené à la re-modifier ( ajout de champs dans les tables,ajout/suppression de tables)
Donc lorsqu'on est arrivé à un résultat un peu probant il faut passer à la mise en pratique, faire du code pour gérer les données quitte à ajuster après

Je préfère nettement avec filet. J'ai pas envie de mettre le bordel par accident dans la DB

il faut alors travailler avec une base de "production" et avoir une stratégie de réplication des données

[Jesspredator]

Merci Mat.M, tu as complètement raison.
En pensant au niveau code, je me suis trouvé une solution pour ma précèdente question.

[fsmrel]

Bonsoir Jesspredator,


Table JOUEUR_RENCONTRE : A y regarder de plus près, la clé primaire actuelle est trop restrictive, la colonne equipeBId doit en faire partie. Cela vaut donc pour les clés étrangères dans les tables qui s’y attachent (JOUEUR_BUT, JOUEUR_CARTON_JAUNE et JOUEUR_CARTON_ROUGE).

CREATE TABLE JOUEUR_RENCONTRE
(
        joueurId            INT            NOT NULL
      , equipeId            INT            NOT NULL
      , equipeAId           INT            NOT NULL
      , equipeBId           INT            NOT NULL
      , tournoiId           INT            NOT NULL 
      , saisonId            INT            NOT NULL
    , CONSTRAINT JOUEUR_RENCONTRE_PK PRIMARY KEY 
          (joueurId, equipeAId, equipeBId, tournoiId, saisonId)  
    , CONSTRAINT JOUEUR_RENCONTRE_EQUIPE_FK 
          FOREIGN KEY (joueurId, equipeId, tournoiId, saisonId) 
          REFERENCES JOUEUR_EQUIPE (joueurId, equipeId, tournoiId, saisonId)  
    , CONSTRAINT JOUEUR_RENCONTRE_RENCONTRE_FK 
          FOREIGN KEY (equipeAId, equipeBId, tournoiId) 
          REFERENCES RENCONTRE (equipeAId, equipeBId, tournoiId)
    , CONSTRAINT JOUEUR_RENCONTRE_CHK CHECK (equipeId = equipeAId OR equipeId = equipeBId)    
) ;

CREATE TABLE JOUEUR_BUT
(
        joueurId            INT            NOT NULL
      , equipeAId           INT            NOT NULL
      , equipeBId           INT            NOT NULL  
      , tournoiId           INT            NOT NULL 
      , saisonId            INT            NOT NULL
      , butMinute           INT            NOT NULL
    , CONSTRAINT JOUEUR_BUT_PK PRIMARY KEY 
          (joueurId, equipeAId, tournoiId, saisonId, butMinute) 
    , CONSTRAINT JOUEUR_BUT_RENCONTRE_FK 
          FOREIGN KEY (joueurId, equipeAId, equipeBId, tournoiId, saisonId) 
          REFERENCES JOUEUR_RENCONTRE (joueurId, equipeAId, equipeBId, tournoiId, saisonId)    
) ;

CREATE TABLE JOUEUR_CARTON_JAUNE
(
        joueurId            INT            NOT NULL
      , equipeAId           INT            NOT NULL
      , equipeBId           INT            NOT NULL  
      , tournoiId           INT            NOT NULL 
      , saisonId            INT            NOT NULL
      , cartonMinute        INT            NOT NULL
    , CONSTRAINT JOUEUR_CARTON_JAUNE_PK PRIMARY KEY (joueurId, equipeAId, tournoiId, saisonId, cartonMinute) 
    , CONSTRAINT JOUEUR_CARTON_JAUNE_RENCONTRE_FK 
          FOREIGN KEY (joueurId, equipeAId, equipeBId, tournoiId, saisonId) 
          REFERENCES JOUEUR_RENCONTRE (joueurId, equipeAId, equipeBId, tournoiId, saisonId)   
) ;

CREATE TABLE JOUEUR_CARTON_ROUGE
(
        joueurId            INT            NOT NULL
      , equipeAId           INT            NOT NULL
      , equipeBId           INT            NOT NULL  
      , tournoiId           INT            NOT NULL 
      , saisonId            INT            NOT NULL
      , cartonMinute        INT            NOT NULL
    , CONSTRAINT JOUEUR_CARTON_ROUGE_PK PRIMARY KEY 
         (joueurId, equipeAId, tournoiId, saisonId) 
    , CONSTRAINT JOUEUR_CARTON_ROUGE_RENCONTRE_FK 
          FOREIGN KEY (joueurId, equipeAId, equipeBId, tournoiId, saisonId) 
          REFERENCES JOUEUR_RENCONTRE (joueurId, equipeAId, equipeBId, tournoiId, saisonId)   
) ;

j'aimerai mettre deux contraintes pour les équipes. Du genre, une équipe doit avoir au minimum 4 joueurs et maximum 8 joueurs pour pouvoir participer à un match.

Je peux mettre cela en place en créant une fonction qui sera appelée dans une contrainte CHECK ou il est préférable d'utiliser un trigger ?

Je suppose que ces limites concernent le nombre de joueurs par match. En effet, vous aviez écrit :

Un match est joué par 5 joueurs/équipe minimum ou 7 joueurs/équipe maximum.

Comment envisagez-vous l’utilisation d’une fonction ici ? En attendant, il n’est pas très compliqué de développer un trigger pour INSERT, UPDATE portant sur la table JOUEUR-RENCONTRE (et bien entendu son pendant pour DELETE). Dans un 1er temps, on ajoute une colonne — appelons-la feuVert — à la table RENCONTRE, et dont la valeur par défaut est 0 :

CREATE TABLE RENCONTRE
(
        equipeAId           INT            NOT NULL 
      , equipeBId           INT            NOT NULL 
      , tournoiId           INT            NOT NULL 
      , rencontreDate       DATE           NOT NULL 
      , feuVert             TINYINT        NOT NULL DEFAULT 0  
     ,  equipeAbuts         INT            NOT NULL DEFAULT 0 
      , equipeBbuts         INT            NOT NULL DEFAULT 0
    , CONSTRAINT RENCONTRE_PK PRIMARY KEY (equipeAId, equipeBId, tournoiId)
    , CONSTRAINT RENCONTRE_AK UNIQUE (rencontreDate)
    , CONSTRAINT RENCONTRE_EQUIPE_A_FK FOREIGN KEY (equipeAId) REFERENCES EQUIPE (equipeId)
    , CONSTRAINT RENCONTRE_EQUIPE_B_FK FOREIGN KEY (equipeBId) REFERENCES EQUIPE (equipeId)
    , CONSTRAINT RENCONTRE_ANNEE_FK FOREIGN KEY (tournoiId) REFERENCES TOURNOI (tournoiId)
    , CONSTRAINT RENCONTRE_CHCK CHECK (equipeAId < equipeBId) --SQL and RT page 191
) ; 

On définit un trigger pour les INSERT, UPDATE. Ce trigger fait passer à 1 la valeur de la colonne feuVert de la table RENCONTRE si le nombre de joueurs est dans les bornes <4,8> pour les deux équipes. Si pour l’une d’elles on inscrit un 9e joueur, le trigger rouspète. On suppose ici que les deux équipes doivent aligner le même nombre de joueurs lors d’un match. En l’état (inserts mono-lignes) :

--------------------------------------------------
-- Contrôle du nombre de joueurs des 2 équipes
-- pour un match.
---------------------------------------------------

CREATE TRIGGER JOUEUR_RENCONTRE_INSERT_TR ON JOUEUR_RENCONTRE AFTER INSERT, UPDATE 
AS
DECLARE @thestatut as TINYINT = 0 ;
DECLARE @min as TINYINT = 4 ;
DECLARE @max as TINYINT = 8 ;
DECLARE @Akount as TINYINT  ;
DECLARE @Bkount as TINYINT  ;
DECLARE @equipeId as INT ;
DECLARE @equipeAId as INT ;
DECLARE @equipeBId as INT ;
DECLARE @tournoiId as INT ;
DECLARE @saisonIdd as INT ;
DECLARE @engueulade as VARCHAR(128) ;

SET @equipeId = (SELECT equipeId FROM INSERTED) ;
SET @equipeAId = (SELECT equipeAId FROM INSERTED) ;
SET @equipeBId = (SELECT equipeBId FROM INSERTED) ;

IF @equipeAId = @equipeId
    BEGIN
        SET @Akount = (SELECT  COUNT(*) FROM JOUEUR_RENCONTRE 
                       WHERE equipeId IN (SELECT equipeId FROM INSERTED)   
                         AND equipeId IN (SELECT equipeAId FROM INSERTED)
                         AND equipeBId IN (SELECT equipeBId FROM INSERTED) 
                         AND tournoiId IN (SELECT tournoiId FROM INSERTED)
                         AND saisonId IN (SELECT saisonId FROM INSERTED)
                      )
        SET @Bkount = (SELECT  COUNT(*) FROM JOUEUR_RENCONTRE 
                       WHERE equipeId IN (SELECT equipeBId FROM INSERTED)
                         AND equipeAId IN (SELECT equipeAId FROM INSERTED) 
                         AND tournoiId IN (SELECT tournoiId FROM INSERTED)
                         AND saisonId IN (SELECT saisonId FROM INSERTED)
                      )
    END

IF @equipeBId = @equipeId
    BEGIN
        SET @Bkount = (SELECT  COUNT(*) FROM JOUEUR_RENCONTRE 
                       WHERE equipeId IN (SELECT equipeId FROM INSERTED)   
                         AND equipeId IN (SELECT equipeBId FROM INSERTED)
                         AND equipeAId IN (SELECT equipeAId FROM INSERTED) 
                         AND tournoiId IN (SELECT tournoiId FROM INSERTED)
                         AND saisonId IN (SELECT saisonId FROM INSERTED)
                      )
        SET @Akount = (SELECT  COUNT(*) FROM JOUEUR_RENCONTRE 
                       WHERE equipeId IN (SELECT equipeBId FROM INSERTED)
                         AND equipeAId IN (SELECT equipeAId FROM INSERTED) 
                         AND tournoiId IN (SELECT tournoiId FROM INSERTED)
                         AND saisonId IN (SELECT saisonId FROM INSERTED)
                      )
    END
;
-----------------------------------------------------------
-- Si les deux équipes ont un nombre de joueurs
-- dans les bornes, on donne le feu vert pour la rencontre.
-----------------------------------------------------------
IF @Akount >= @min AND @Akount <= @max 
  AND @Bkount >= @min AND @Bkount <= @max
  AND @Akount = @Bkount  -- les deux équipes doivent aligner le même nombre de joueurs
    BEGIN
        SET @thestatut = 1 ;

        UPDATE RENCONTRE
            SET feuVert = @thestatut
            WHERE FeuVert <> @thestatut 
              AND equipeAId IN (SELECT equipeAId FROM INSERTED) 
              AND equipeBId IN (SELECT equipeBId FROM INSERTED) 
              AND tournoiId IN (SELECT tournoiId FROM INSERTED) 
        ;
    END  
ELSE
    IF @Akount > @max OR @Bkount > @max
        BEGIN
            SET @Engueulade = 'Pas plus de ' + CAST(@max AS VARCHAR) + ' joueurs par équipe !'
            ROLLBACK ;
            THROW 50001, @Engueulade, 1
        END
GO

A secouer !

Reste à développer le trigger pour DELETE, au cas où on retirerait des joueurs et qu’on sorte des bornes (la colonne feuVert de la table RENCONTRE doit repasser à 0).


 

[fsmrel]

Bonsoir Jesspredator,

Un jeu d’essai avec les tables dans leur état.


Table TOURNOI

INSERT INTO TOURNOI (tournoiAnnee)
VALUES 
     (2017), (2018), (2019)
; 

Table SAISON

INSERT INTO SAISON (tournoiId, saisonSemestre) 
SELECT (SELECT tournoiId FROM tournoi where tournoiAnnee = 2017), 1
;
INSERT INTO SAISON (tournoiId, saisonSemestre) 
SELECT (SELECT tournoiId FROM tournoi where tournoiAnnee = 2017), 2
;
INSERT INTO SAISON (tournoiId, saisonSemestre) 
SELECT (SELECT tournoiId FROM tournoi where tournoiAnnee = 2018), 1
;
INSERT INTO SAISON (tournoiId, saisonSemestre) 
SELECT (SELECT tournoiId FROM tournoi where tournoiAnnee = 2018), 2
;
INSERT INTO SAISON (tournoiId, saisonSemestre) 
SELECT (SELECT tournoiId FROM tournoi where tournoiAnnee = 2019), 1
;
INSERT INTO SAISON (tournoiId, saisonSemestre) 
SELECT (SELECT tournoiId FROM tournoi where tournoiAnnee = 2019), 2
;
SELECT '' as SAISON, * FROM SAISON as x JOIN TOURNOI as y on x.tournoiId = y.tournoiId ;

Table EQUIPE

INSERT INTO EQUIPE (equipeMatricule, equipeNom)
VALUES
    ('mo1', 'Montauban') , ('pa1', 'Palaiseau') 
  , ('to1', 'Toulouse'), ('na1', 'Nantes'), ('pa2', 'Pau'), ('li1', 'Lille')
  , ('pa3', 'Paname'), ('ca1', 'Cancale')
;

Table JOUEUR

INSERT INTO JOUEUR (joueurMatricule, joueurNom)
VALUES 
    ('fen', 'Fernand') , ('mef', 'Folace'), ('jea', 'Jean'), ('ant', 'Antoine')
  , ('pam', 'Paul'), ('hen', 'Henri'), ('lou', 'Louis'), ('ame', 'Amédée')
  , ('mic', 'Michel')

  , ('rav', 'Raoul'), ('pav', 'Paul'), ('bas', 'Bastien'), ('pas', 'Pascal')
  , ('fre', 'Freddy') , ('dom', 'Domino'), ('the', 'Théo'), ('tom', 'Tomate')
  , ('vin', 'Vincent')

  , ('fra', 'Franck'), ('ren', 'René'), ('leo', 'Léon'), ('luc', 'Luc')
  , ('jac', 'Jacques'), ('jul', 'Jules')
;

Table JOUEUR_EQUIPE

Fernand joue à Montauban :

INSERT INTO JOUEUR_EQUIPE (joueurId, equipeId, tournoiId, saisonId)
SELECT
    (SELECT joueurId 
     FROM   JOUEUR 
     WHERE  joueurMatricule = 'fen') 
  , (SELECT equipeId 
     FROM   EQUIPE 
     WHERE  equipeMatricule = 'mo1')
  , (SELECT DISTINCT y.tournoiId 
     FROM   SAISON AS x 
       JOIN TOURNOI AS y ON x.tournoiId = y.tournoiId 
     WHERE  tournoiAnnee = 2018) 
  , (SELECT saisonId 
     FROM   SAISON AS x 
       JOIN TOURNOI AS y ON x.tournoiId = y.tournoiId 
     WHERE  tournoiAnnee = 2018 AND saisonSemestre = 1) 
;

La saisie étant vite pénible, on peut préférer effectuer les inserts dans une vue, avec interception de ces inserts au moyen d’un trigger pour les répercuter dans la table.


Vue JOUEUR_EQUIPE_V sur la table JOUEUR_EQUIPE

DROP VIEW IF EXISTS JOUEUR_EQUIPE_V ;

GO

CREATE VIEW JOUEUR_EQUIPE_V (joueurMatricule, equipeMatricule, tournoiAnnee, saisonSemestre) 
AS 
SELECT joueurMatricule, equipeMatricule, tournoiAnnee, saisonSemestre 
FROM   JOUEUR_EQUIPE AS x 
  JOIN JOUEUR AS y ON x.joueurId = y.joueurId
  JOIN EQUIPE AS z ON x.equipeId = z.equipeId
  JOIN TOURNOI AS t ON x.tournoiId = t.tournoiId
  JOIN SAISON AS u ON x.saisonId = u.saisonId

GO 

Trigger pour interception des inserts dans la vue :

---------------------------------------------------------
-- Répercussion danx la table JOUEUR_EQUIPE des inserts 
-- dans la vue JOUEUR_EQUIPE_V
--------------------------------------------------------

DROP TRIGGER IF EXISTS JOUEUR_EQUIPE_V_INSERT_TR ;

GO

----------------------------------------------------
-- Le seul moyen ici de faire des inserts de masse
-- est d'utiliser un curseur parcourant les lignes
-- d'INSERTED une à une.
----------------------------------------------------

CREATE TRIGGER JOUEUR_EQUIPE_V_INSERT_TR ON JOUEUR_EQUIPE_V INSTEAD OF INSERT
AS

DECLARE @joueurMatricule AS VARCHAR(48) ;
DECLARE @equipeMatricule AS VARCHAR(48) ;
DECLARE @tournoiAnnee AS INT ; ;
DECLARE @saisonSemestre AS DECIMAL(1,0) ;

DECLARE theCurseur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY 
    FOR SELECT joueurMatricule, equipeMatricule, tournoiAnnee, saisonSemestre
        FROM INSERTED
;
OPEN theCurseur

-----------------------------------------
-- Lecture de la 1re ligne du curseur 
-----------------------------------------

FETCH theCurseur INTO 
    @joueurMatricule, @equipeMatricule, @tournoiAnnee, @saisonSemestre
;
WHILE @@FETCH_STATUS = 0

    BEGIN

        INSERT INTO JOUEUR_EQUIPE (joueurId, equipeId, tournoiId, saisonId) 
            SELECT
                (SELECT DISTINCT joueurId 
                 FROM   JOUEUR 
                 WHERE  joueurMatricule = @joueurMatricule) 
              , (SELECT DISTINCT equipeId 
                 FROM   EQUIPE 
                 WHERE  equipeMatricule = @equipeMatricule) 
              , (SELECT DISTINCT tournoiId 
                 FROM   TOURNOI
                 WHERE  tournoiAnnee = @tournoiAnnee)
              , (SELECT DISTINCT saisonId 
                 FROM   SAISON
                 WHERE  saisonSemestre = @saisonSemestre
                    AND tournoiId = (SELECT tournoiId 
                                     FROM   tournoi 
                                     WHERE  tournoiAnnee = @tournoiAnnee))
        ;

        -----------------------------------------
        -- Lecture de la ligne suivante 
        -----------------------------------------

        FETCH theCurseur INTO 
            @joueurMatricule, @equipeMatricule, @tournoiAnnee, @saisonSemestre

    END 

CLOSE theCurseur ;
DEALLOCATE theCurseur ;

GO 

Jeu d’essai (c’est quan même moins pénible de cette façon !)

INSERT  INTO JOUEUR_EQUIPE_V (joueurMatricule, equipeMatricule
                                 , tournoiAnnee, saisonSemestre)
VALUES 
    ('fen', 'mo1', 2018, 1), ('mef', 'mo1', 2018, 1), ('jea', 'mo1', 2018, 1)
  , ('ant', 'mo1', 2018, 1), ('hen', 'mo1', 2018, 1), ('lou', 'mo1', 2018, 1)
  , ('pam', 'mo1', 2018, 1), ('ame', 'mo1', 2018, 1), ('mic', 'mo1', 2018, 1)

  , ('rav', 'pa1', 2018, 1), ('pav', 'pa1', 2018, 1), ('pas', 'pa1', 2018, 1)
  , ('bas', 'pa1', 2018, 1), ('tom', 'pa1', 2018, 1), ('the', 'pa1', 2018, 1)
  , ('fre', 'pa1', 2018, 1), ('dom', 'pa1', 2018, 1), ('vin', 'pa1', 2018, 1)

  , ('fra', 'ca1', 2018, 1), ('ren', 'ca1', 2018, 1), ('leo', 'ca1', 2018, 1)
  , ('luc', 'ca1', 2018, 1), ('jac', 'ca1', 2018, 1), ('jul', 'ca1', 2018, 1)
-- 2019
  , ('fen', 'mo1', 2019, 1), ('mef', 'mo1', 2019, 1), ('jea', 'mo1', 2019, 1)
;

Pour voir :

SELECT y.joueurMatricule, joueurNom, z.equipeMatricule, equipeNom, tournoiAnnee, saisonSemestre
FROM   JOUEUR_EQUIPE_V AS x
  JOIN JOUEUR AS y ON x.joueurMatricule= y.joueurMatricule  
  JOIN EQUIPE AS z ON x.equipeMatricule = z.equipeMatricule ; 

=>

joueurMatricule  joueurNom  equipeMatricule  equipeNom   tournoiAnnee  saisonSemestre
fra              Franck     ca1              Cancale     2018          1
jac              Jacques    ca1              Cancale     2018          1
jul              Jules      ca1              Cancale     2018          1
leo              Léon       ca1              Cancale     2018          1
luc              Luc        ca1              Cancale     2018          1
ren              René       ca1              Cancale     2018          1
ame              Amédée     mo1              Montauban   2018          1
ant              Antoine    mo1              Montauban   2018          1
fen              Fernand    mo1              Montauban   2018          1
hen              Henri      mo1              Montauban   2018          1
jea              Jean       mo1              Montauban   2018          1
lou              Louis      mo1              Montauban   2018          1
mef              Folace     mo1              Montauban   2018          1
mic              Michel     mo1              Montauban   2018          1
pam              Paul       mo1              Montauban   2018          1
bas              Bastien    pa1              Palaiseau   2018          1
dom              Domino     pa1              Palaiseau   2018          1
fre              Freddy     pa1              Palaiseau   2018          1
pas              Pascal     pa1              Palaiseau   2018          1
pav              Paul       pa1              Palaiseau   2018          1
rav              Raoul      pa1              Palaiseau   2018          1
the              Théo       pa1              Palaiseau   2018          1
tom              Tomate     pa1              Palaiseau   2018          1
vin              Vincent    pa1              Palaiseau   2018          1
fen              Fernand    mo1              Montauban   2019          1
jea              Jean       mo1              Montauban   2019          1
mef              Folace     mo1              Montauban   2019          1

A secouer pour débusquer les erreurs...

A suivre : les rencontres, etc.

 

[fsmrel]

Les rencontres.

Saisie de la rencontre à venir Toulouse - Cancale du 1er juillet 2018

INSERT INTO RENCONTRE (equipeAId, equipeBId, tournoiId, rencontreDate)
SELECT 
    (SELECT equipeId FROM EQUIPE where equipeMatricule = 'to1')
  , (SELECT equipeId FROM EQUIPE where equipeMatricule = 'ca1')
  , (SELECT tournoiId FROM TOURNOI where tournoiAnnee = 2018)
  , (SELECT '2018-07-01')
;

Pour simplifier la saisie, là encore on peut passer par une vue :

CREATE VIEW RENCONTRE_V (equipeAMatricule, equipeBMatricule
                            , tournoiAnnee, rencontreDate, feuVert
                            , equipeAbuts, equipeBbuts) 
AS 
SELECT y.equipeMatricule, z.equipeMatricule
     , tournoiAnnee, rencontreDate, feuVert
     , equipeAbuts, equipeBbuts 
FROM   RENCONTRE AS x
       JOIN EQUIPE AS y ON x.equipeAId = y.equipeId 
       JOIN EQUIPE AS z ON x.equipeBId = z.equipeId  
       JOIN TOURNOI AS t ON x.tournoiId = t.tournoiId
;

Le trigger pour intercepter les inserts dans la vue et mettre la table à jour. On en profite pour s’assurer que la date de rencontre est cohérente avec la date du tournoi :

---------------------------------------------------------
-- Répercussion danx la table RENCONTRE des inserts 
-- dans la vue RENCONTRE_V
--------------------------------------------------------

DROP TRIGGER IF EXISTS RENCONTRE_V_INSERT_TR ;

GO

CREATE TRIGGER RENCONTRE_V_INSERT_TR ON RENCONTRE_V INSTEAD OF INSERT
AS

DECLARE @equipeAMatricule AS VARCHAR(48) ;
DECLARE @equipeANom AS VARCHAR(48) ;
DECLARE @equipeBMatricule AS VARCHAR(48) ;
DECLARE @equipeBNom AS VARCHAR(48) ;
DECLARE @tournoiAnnee AS INT ;
DECLARE @rencontreDate AS DATE ;
DECLARE @rencontreDateAnnee AS INT ;
DECLARE @feuVert AS TINYINT ;
DECLARE @equipeAbuts AS INT ;
DECLARE @equipeBbuts AS INT ;
DECLARE @engueulade AS VARCHAR(255) ;

DECLARE theCurseur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY 
    FOR SELECT equipeAMatricule, equipeBMatricule
             , tournoiAnnee, rencontreDate, feuVert
             , equipeAbuts, equipeBbuts
        FROM INSERTED
;
----------------------------------------------------
-- pour les inserts de masse, utilisation
-- d'un curseur parcourant les lignes
-- d'INSERTED une à une.
----------------------------------------------------
OPEN theCurseur

-----------------------------------------
-- Lecture de la 1re ligne du curseur 
-----------------------------------------

FETCH theCurseur INTO 
    @equipeAMatricule, @equipeBMatricule
  , @tournoiAnnee, @rencontreDate, @feuVert
  , @equipeAbuts, @equipeBbuts
;
WHILE @@FETCH_STATUS = 0

    BEGIN

        ------------------------------------
        -- La date de la encontre doit être
        --compatible avec l'année du tournoi
        ------------------------------------
        SET @rencontreDateAnnee = YEAR(@rencontreDate) 

        IF  @rencontreDateAnnee <> @tournoiAnnee
            BEGIN

                SET @equipeANom = (SELECT equipeNom 
                                   FROM   EQUIPE 
                                   WHERE  equipeMatricule = @equipeAMatricule)  ;
                SET @equipeBNom = (SELECT equipeNom FROM EQUIPE WHERE equipeMatricule = @equipeBMatricule)  ;

                SET @engueulade = 'Rencontre ' 
                                + @equipeANom + ' - ' + @equipeBNom
                                + ' ; tournoi ' + CAST(@tournoiAnnee as VARCHAR)
                                + ' : date rencontre incomatible ('
                                + CAST(@rencontreDate AS VARCHAR)  + ').' 
                ROLLBACK ;
                THROW 50002, @Engueulade, 1                   
            END 
 
        INSERT INTO RENCONTRE (equipeAId, equipeBId, tournoiId, rencontreDate, equipeAbuts, equipeBbuts) 
            SELECT
                (SELECT equipeId 
                 FROM   EQUIPE 
                 WHERE  equipeMatricule = @equipeAMatricule) 
              , (SELECT equipeId 
                 FROM   EQUIPE 
                 WHERE  equipeMatricule = @equipeBMatricule) 
              , (SELECT DISTINCT tournoiId 
                 FROM   TOURNOI
                 WHERE  tournoiAnnee = @tournoiAnnee)
              , @rencontreDate
              , @equipeAbuts
              , @equipeBbuts
        ;

        -----------------------------------------
        -- Lecture de la ligne suivante 
        -----------------------------------------

        FETCH theCurseur INTO 
            @equipeAMatricule, @equipeBMatricule
          , @tournoiAnnee, @rencontreDate, @feuVert
          , @equipeAbuts, @equipeBbuts

    END 

CLOSE theCurseur ;
DEALLOCATE theCurseur ;

GO

Calendrier des rencontres prévues en 2018, saison 1

INSERT INTO RENCONTRE_V (equipeAMatricule, equipeBMatricule
                            , tournoiAnnee, rencontreDate, feuVert
                            , equipeAbuts, equipeBbuts)
VALUES 
    ('mo1', 'pa1', 2018, '2018-05-05', 0, 0, 0)
  , ('mo1', 'to1', 2018, '2018-05-06', 0, 0, 0)
  , ('mo1', 'na1', 2018, '2018-05-12', 0, 0, 0)
  , ('mo1', 'pa2', 2018, '2018-05-13', 0, 0, 0)
  , ('mo1', 'li1', 2018, '2018-05-19', 0, 0, 0)
  , ('mo1', 'pa3', 2018, '2018-05-20', 0, 0, 0)
  , ('mo1', 'ca1', 2018, '2018-05-26', 0, 0, 0)

  , ('pa1', 'to1', 2018, '2018-05-27', 0, 0, 0)
  , ('pa1', 'na1', 2018, '2018-06-02', 0, 0, 0)
  , ('pa1', 'pa2', 2018, '2018-06-03', 0, 0, 0)
  , ('pa1', 'li1', 2018, '2018-06-09', 0, 0, 0)
  , ('pa1', 'pa3', 2018, '2018-06-10', 0, 0, 0)
  , ('pa1', 'ca1', 2018, '2018-06-16', 0, 0, 0)

  , ('to1', 'na1', 2018, '2018-06-17', 0, 0, 0)
  , ('to1', 'pa2', 2018, '2018-06-23', 0, 0, 0)
  , ('to1', 'li1', 2018, '2018-06-24', 0, 0, 0)
  , ('to1', 'pa3', 2018, '2018-06-30', 0, 0, 0)
  , ('to1', 'ca1', 2018, '2018-07-01', 0, 0, 0)

  , ('na1', 'pa2', 2018, '2018-07-07', 0, 0, 0)
  , ('na1', 'li1', 2018, '2018-07-08', 0, 0, 0)
  , ('na1', 'pa3', 2018, '2018-07-14', 0, 0, 0)
  , ('na1', 'ca1', 2018, '2018-07-15', 0, 0, 0)

  , ('pa2', 'li1', 2018, '2018-07-21', 0, 0, 0)
  , ('pa2', 'pa3', 2018, '2018-07-22', 0, 0, 0)
  , ('pa2', 'ca1', 2018, '2018-07-28', 0, 0, 0)

  , ('li1', 'pa3', 2018, '2018-07-29', 0, 0, 0)
  , ('li1', 'ca1', 2018, '2018-08-04', 0, 0, 0)

  , ('pa3', 'ca1', 2018, '2018-08-05', 0, 0, 0)

A suivre...

 

[fsmrel]

Les participations es joueurs aux rencontres

Participation de Fernand à la rencontre entre Montauban et Palaiseau, ça ressemble à quelque chose comme ça :

INSERT INTO JOUEUR_RENCONTRE (joueurId, equipeId
                            , equipeAId, equipeBId
                            , tournoiId, saisonId) 
SELECT
    (SELECT distinct y.joueurId 
     FROM   JOUEUR_EQUIPE AS x 
       JOIN JOUEUR AS y ON x.joueurId = y.joueurId 
     WHERE y.joueurMatricule = 'fen')
  , (SELECT DISTINCT x.equipeId 
     FROM   JOUEUR_EQUIPE AS x   
       JOIN EQUIPE AS y ON x.equipeId = y.equipeId
     WHERE  y.equipeMatricule = 'mo1')
  , (SELECT DISTINCT y.equipeId 
     FROM   RENCONTRE AS x 
       JOIN JOUEUR_EQUIPE AS y ON x.equipeAId = y.equipeId 
       JOIN EQUIPE AS z ON y.equipeId = z.equipeId 
     WHERE z.equipeMatricule = 'mo1')
  , (SELECT DISTINCT y.equipeId 
     FROM   RENCONTRE AS x 
       JOIN EQUIPE AS y ON x.equipeBId = y.equipeId 
     WHERE y.equipeMatricule = 'pa1') 
  , (SELECT DISTINCT y.tournoiId 
     FROM   RENCONTRE AS x 
       JOIN TOURNOI AS y ON x.tournoiId = y.tournoiId 
     WHERE y.tournoiAnnee = 2018)
  , (SELECT DISTINCT x.saisonId
     FROM   JOUEUR_EQUIPE AS x
       JOIN SAISON  AS z ON x.saisonId = 
            (SELECT saisonid
             FROM   SAISON
             WHERE  saisonSemestre = @saisonSemestre
               AND  tournoiId = 
                    (SELECT tournoiId
                     FROM   tournoi 
                     WHERE  tournoiAnnee = @tournoiAnnee)))   

Pour un joueur ça va, mais on finit par se lasser...
C’est reparti pour une vue :

GO

CREATE VIEW JOUEUR_RENCONTRE_V (joueurMatricule, equipeMatricule
                              , equipeAMatricule, equipeBMatricule
                              , tournoiAnnee, saisonSemestre) 
AS 
SELECT DISTINCT joueurMatricule, z.equipeMatricule
              , t.equipeMatricule, u.equipeMatricule
              , tournoiAnnee, saisonSemestre 
FROM   JOUEUR_RENCONTRE AS x
       JOIN JOUEUR AS y ON x.joueurId = y.joueurId
       JOIN EQUIPE AS z ON x.equipeId = z.equipeId 
       JOIN EQUIPE AS t ON x.equipeAId = t.equipeId  
       JOIN EQUIPE AS u ON x.equipeBId = u.equipeId  
       JOIN TOURNOI AS v ON x.tournoiId = v.tournoiId
       JOIN SAISON AS w ON x.tournoiId = w.tournoiId
                       AND x.saisonId = w.saisonId
;
GO 

Et le trigger correspondant :

CREATE TRIGGER JOUEUR_RENCONTRE_V_INSERT_TR ON JOUEUR_RENCONTRE_V INSTEAD OF INSERT 
AS
DECLARE @joueurMatricule AS VARCHAR(48) ;
DECLARE @equipeMatricule AS VARCHAR(48) ;
DECLARE @equipeAMatricule AS VARCHAR(48) ;
DECLARE @equipeBMatricule AS VARCHAR(48) ;
DECLARE @tournoiAnnee AS INT ;
DECLARE @saisonSemestre AS DECIMAL(1,0) ;
 
DECLARE theCurseur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY 
    FOR SELECT joueurMatricule, equipeMatricule
             , equipeAMatricule, equipeBMatricule
             , tournoiAnnee, saisonSemestre
        FROM INSERTED
;

OPEN theCurseur

-----------------------------------------
-- Lecture de la 1re ligne du curseur 
-----------------------------------------

FETCH theCurseur INTO 
    @joueurMatricule, @equipeMatricule
  , @equipeAMatricule, @equipeBMatricule
  , @tournoiAnnee, @saisonSemestre
;
WHILE @@FETCH_STATUS = 0

    BEGIN

        INSERT INTO JOUEUR_RENCONTRE (joueurId, equipeId
                                    , equipeAId, equipeBId
                                    , tournoiId, saisonId)
            SELECT
                (SELECT DISTINCT y.joueurId 
                 FROM   JOUEUR_EQUIPE AS x 
                   JOIN JOUEUR AS y ON x.joueurId = y.joueurId 
                 WHERE  y.joueurMatricule = @joueurMatricule)
              , (SELECT DISTINCT x.equipeId 
                 FROM   JOUEUR_EQUIPE AS x   
                   JOIN EQUIPE AS y ON x.equipeId = y.equipeId
                 WHERE  y.equipeMatricule = @equipeMatricule)
              , (SELECT DISTINCT y.equipeId 
                 FROM   RENCONTRE AS x 
                   JOIN JOUEUR_EQUIPE AS y ON x.equipeAId = y.equipeId 
                   JOIN EQUIPE AS z ON y.equipeId = z.equipeId 
                 WHERE  z.equipeMatricule = @equipeAMatricule) 
              , (SELECT DISTINCT y.equipeId 
                 FROM   RENCONTRE AS x 
                   JOIN EQUIPE AS y ON x.equipeBId = y.equipeId 
                 WHERE  y.equipeMatricule = @equipeBMatricule) 
              , (SELECT DISTINCT y.tournoiId 
                 FROM   RENCONTRE AS x 
                   JOIN TOURNOI AS y ON x.tournoiId = y.tournoiId 
                 WHERE  y.tournoiAnnee = @tournoiAnnee) 
             , (SELECT DISTINCT x.saisonId
                FROM   JOUEUR_EQUIPE AS x
                  JOIN SAISON  AS z ON x.saisonId = 
                      (SELECT saisonid
                       FROM   SAISON
                       WHERE  saisonSemestre = @saisonSemestre
                         AND  tournoiId = 
                              (SELECT tournoiId
                               FROM   tournoi 
                               WHERE  tournoiAnnee = @tournoiAnnee)))   
        ; 

        -----------------------------------------
        -- Lecture des lignes suivantes 
        -----------------------------------------

        FETCH theCurseur INTO  
            @joueurMatricule, @equipeMatricule
          , @equipeAMatricule, @equipeBMatricule
          , @tournoiAnnee, @saisonSemestre

    END 

CLOSE theCurseur ;
DEALLOCATE theCurseur ;

GO

Quelques participations :

INSERT INTO JOUEUR_RENCONTRE_V (joueurMatricule, equipeMatricule
                                   , equipeAMatricule, equipeBMatricule
                                   , tournoiAnnee, saisonSemestre)
VALUES
    ('fen', 'mo1', 'mo1', 'pa1', 2018, 1), ('jea', 'mo1', 'mo1', 'pa1', 2018, 1)
  , ('mef', 'mo1', 'mo1', 'pa1', 2018, 1), ('ant', 'mo1', 'mo1', 'pa1', 2018, 1)
  , ('rav', 'pa1', 'mo1', 'pa1', 2018, 1)
  , ('hen', 'mo1', 'mo1', 'pa1', 2018, 1), ('tom', 'pa1', 'mo1', 'pa1', 2018, 1);

 

[Jesspredator]

Salut fsmrel !

Je me suis remis sur mon projet aujourd'hui, désolé de mon absence.
J'ai pu un peu tester la base de donnée, pour l'instant je ne suis pas encore tombé sur un soucis.

Vous en avez fais du code en absence haha Je viens de le parcourir, c'est plutôt bien complet. Je dois encore tester vos propositions d'insertions, triggers et vues.
Je vous remercie de votre dévouement, je n'en imaginait pas tant :p

Je vais regarder tout ça de près dès demain, aujourd’hui malheureusement je suis épuisé. Bonne soirée

[fsmrel]

Bonsoir Jesspredator,

Progressons tranquillement, de toute façon les matchs risquent être remis aux calendes…

Que cela ne vous empêche pas de prendre le temps de m’attribuer des médailles (profil pro, onglet "Compétences")

 

[Jesspredator]

Petite question en passant :

Si dans un futur je désire autoriser des matchs Aller/Retour et non pas uniquement Aller, il me suffirait de changer la contrainte RECONTRE_CHCK : « equipeAId < equipeBId » par « equipeAId != equipeBId » ?

[fsmrel]

Si dans un futur je désire autoriser des matchs Aller/Retour et non pas uniquement Aller, il me suffirait de changer la contrainte RECONTRE_CHCK : « equipeAId < equipeBId » par « equipeAId != equipeBId » ?

Si vous procédez ainsi, une équipe A et une équipe B peuvent effectivement se rencontrer deux fois. Il y a quand même un petit problème : les deux rencontres peuvent avoir lieu au cours du même semestre…