Discussion :

[BobaL]

Bonjour,

J'ai besoin de vos lumières pour réaliser une requête SQL...
Voici mes tables concernées :

Je cherche à remonter les nom des clubs de toutes les rencontres dont un club précis fait partie.

Moi j'en suis là :

Code :

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

1
2
3
4
5
6
7
SELECT club.nom AS 'Club Visité', club2.nom AS 'Club Visiteur', rencontre_has_equipe_visite.equipe_id_equipe, rencontre_has_equipe_visiteuse.equipe_id_equipe
FROM equipe, equipe AS equipe2, club, club AS club2, club_has_equipe, rencontre_has_equipe_visite, rencontre_has_equipe_visiteuse
WHERE club.nom = 'TOTO'
AND equipe.categorie = 'Nationale'
AND club_has_equipe.club_id_club = club.id_club AND club_has_equipe.club_id_club = club2.id_club
AND club_has_equipe.equipe_id_equipe = equipe.id_equipe AND club_has_equipe.equipe_id_equipe = equipe2.id_equipe
AND (rencontre_has_equipe_visite.equipe_id_equipe != rencontre_has_equipe_visiteuse.equipe_id_equipe AND (rencontre_has_equipe_visite.equipe_id_equipe = equipe.id_equipe OR rencontre_has_equipe_visiteuse.equipe_id_equipe = equipe.id_equipe));

Sauf que je n'arrive jamais à obtenir les bons noms de clubs alors que les id des equipes sont corrects (le nom du club auquel est associé l'equipe 3 est TITI):
TOTO TOTO 1 3
TOTO TOTO 3 1

Une idée..?

Merci de votre aide.

[CinePhil]

Je cherche à remonter les nom des clubs de toutes les rencontres dont un club précis fait partie.

D'après ta requête, tu ne cherches que les équipes de catégorie 'Nationale'.
D'après la structure de la table 'equipe', il peut y avoir une équipe 'Nationale' en 2007, une autre en 2008...
Il y a donc déjà redondance de données puisqu'on répète le mot 'Nationale' pour des saisions différentes, et que la saison ('2008-2009') est répétée pour plusieurs équipes.

Je serais plutôt parti sur la structure suivante :
Club -1,n----Avoir----1,1- Equipe -1,1----Représenter----0,n- Catégorie
Saison -0,n----Jouer----0,n---|

Ce qui se lit :
1) Un club a de 1 à plusieurs équipes et une équipe est dans un seul club.
2) Une équipe représente une catégorie et une catégorie peut être représentée par plusieurs équipes.
3) Une équipe peut jouer plusieurs saisons et une saison peut voir jouer plusieurs équipes.

Ceci étant dit, revenons à ta structure...
Il manque semble t-il un morceau de ton schéma. La table 'rencontre_has_equipe_visite' laisse supposer qu'il existe une table 'rencontre'.
Cela suppose aussi le schéma suivant :
Rencontre -1,n----ComprendreVisiteur----0,n- Equipe

Comme il y a le complément dans la requête, je complète le schéma :
Rencontre -1,n----ComprendreVisitée----0,n- Equipe
|----------------1,n----ComprendreVisiteuse----0,n---|

Autrement dit, une rencontre rassemble plusieurs équipes visitées et plusieurs équipes visiteuses.
C'est quel sport ?
La catégorie 'Nationale' m'a fait penser au foot mais peut-être ai-je interprété trop vite ?

Venons-en à la requête qui est pour le moins rébarbative à lire et à comprendre parce qu'elle est écrite avec la syntaxe de jointure obsolète depuis 1992...
J'ai essayé de la réécrire avec la syntaxe normalisée et j'ai découvert que dans la dernière parenthèse du WHERE, on s'adresse deux fois à la table equipe alors que je pense qu'il aurait fallu s'adresser aux deux instances différentes de cette table.
Ca donneriat ce qui suit, avec des alias pour alléger la requête et une mise en forme qui facilite la lecture.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT c1.nom AS 'Club Visité', 
  club2.nom AS 'Club Visiteur', 
  r1.equipe_id_equipe, 
  r2.equipe_id_equipe
FROM club_has_equipe AS che
INNER JOIN club AS c1 ON che.club_id_club = c1.id_club
INNER JOIN club AS c2 ON che.club_id_club = c2.id_club
INNER JOIN equipe AS e1 ON che_id_equipe = e1.id_equipe
  INNER JOIN rencontre_has_equipe_visite AS r1 ON r1.equipe_id_equipe = e1.id_equipe
INNER JOIN equipe AS e2 ON che.equipe_id_equipe = e2.id_equipe
  INNER JOIN rencontre_has_equipe_visiteuse AS r2 ON r2.equipe_id_equipe = e2.id_equipe
WHERE club.nom = 'TOTO'
  AND equipe.categorie = 'Nationale'
  AND r1.equipe_id_equipe <> r2.equipe_id_equipe

Maintenant, de là à savoir si ça marche... La structure de la BDD me semble bizarre alors difficile de se prononcer.

[BobaL]

Merci de ton aide CinePhil

Je sais que ma structure de bd n'est pas logique...
Je l'ai créé avec DBDesigner et j'ai du mal à construire les tables telles que je souhaiterai les écrire à la main. Du coup j'ai mis des tables d'association un peut partout pour arriver à mes fins

Autrement je suis d'accord avec les cardinalités et la redondance de la catégorie... Par contre comment conserver un historique par saison si une équipe peut jouer plusieurs saisons ?

En ce qui concerne la requête j'ai passé "club2.nom AS 'Club Visiteur'" en "c2.nom AS 'Club Visiteur'" et "che_id_equipe" en "che.equipe_id_equipe" mais j'ai ensuite des erreurs sur :
- Unknown column 'club.nom' in 'where clause'
- Unknown column 'equipe.categorie' in 'where clause'

Si je les transforme en c1.nom et e1.categorie et bien plus d'erreur mais pas de résultat...

Penses-tu que je doive refaire ma structure de db ou cela peut-il fonctionner comme cela ?
Et est-tu disposé à me donner un coup de main pour le faire ?

Merci encore.

[CinePhil]

Je pense que tu devrais refaire la structure de la base si ce n'est pas trop tard (pas déjà exploité).

Par contre comment conserver un historique par saison si une équipe peut jouer plusieurs saisons ?

Une rencontre entre deux équipes (c'est bien du foot ou un sport à une équipe contre une autre ?) a lieu à une certaine date, donc lors d'une certaine saison.

L'historique de l'équipe est conservé dans la table des rencontres.

Je reprends ma structure :
Club -1,n----Avoir----1,1- Equipe -1,1----Représenter----0,n- Catégorie
Saison -0,n----Jouer----0,n---|

La relation entre Saison et équipe indique seulement que l'équipe existe lors de cette saison. Il se pourrait en effet que, faute d'effectif suffisant, une équipe soit supprimée lors d'une saison.

Passons à la modélisation des rencontres...
Ca va être difficile en format texte !

Saison -1,n----Comprendre----1,1- Rencontre
Equipe -0,n----Visiter----1,1-----------|
|-------0,n----Recevoir----1,1------------|

Il faut ajouter une exclusion entre les associations Visiter et Recevoir (se représente par un X dans un cercle relié aux deux associations par un pointillé).

Traduction :
- Une saison comprend plusieurs rencontres et une rencontre est comprise dans une seule saison.
- Une équipe peut visiter lors de plusieurs rencontres et une rencontre n'a qu'une équipe visiteuse.
- Une équipe peut recevoir lors de plusieurs rencontres et une rencontre n'a qu'une équipe receveuse.
- L'équipe qui visite n'est pas la même que l'équipe qui reçoit.

Ceci nous donnerait les tables :
Club (CLU_Id, CLU_Nom...)
Categorie (CAT_Id, CAT_Libelle...)
Saison (SAI_Id, SAI_Annees...)
Equipe (EQU_Id, EQU_Nom, EQU_IdCategorie, EQU_IDClub...)
Rencontre (RCN_Id, RCN_IdSaison, RCN_IdEquipeReceveuse, RCN_IdEquipeVisiteuse...)

On ajoute une contrainte d'unicité sur le triplet (RCN_IdSaison, RCN_IdEquipeReceveuse, RCN_IdEquipeVisiteuse) et ça traduit correctement le schéma.

Je reprends ton besoin :

Je cherche à remonter les nom des clubs de toutes les rencontres dont un club précis fait partie.

Ca donnerait la requête :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT cr.CLU_Nom AS Club_receveur, er.EQU_Nom AS Equipe_receveuse,
  cv.CLU_Nom AS Club_visiteur, ev.EQU_Nom AS Equipe_Visiteuse
FROM Rencontres AS r
INNER JOIN Saison AS s ON r.RCN_IdSaison = s.SAI_Id
INNER JOIN Equipe AS er ON r.RCN_IdEquipeReceveuse = er.EQU_Id
  INNER JOIN Club AS cr ON er.EQU_IDClub = cr.CLU_Id
  INNER JOIN Categorie AS ctr ON er.EQU_IdCategorie = ctr.CAT_Id
INNER JOIN Equipe AS ev ON r.RCN_IdEquipeVisiteuse = ev.EQU_Id
  INNER JOIN Club AS cv ON ev.EQU_IDClub = cv.CLU_Id
  INNER JOIN Categorie AS ctv ON ev.EQU_IdCategorie = ctv.CAT_Id
 
WHERE (ctr.CAT_Libelle = 'National' OR ctv.CAT_Libelle = 'National')
  AND (cr.CLU_Nom = 'TFC' OR cv.CLU_Nom = 'TFC')
  AND s.SAI_Annees = '2008-2009'

[BobaL]

Merci pour toutes ces précisions

Mon problème est que dans DBDesigner je n'arrive pas à créer 2 clés étrangères entre équipe et rencontre... Je vais devoir les faire à la main ou connais-tu un autre éditeur de MCD pouvant générer directement les tables pour MySql ?


Pour info, c'est pour le site d'un club de volley...

[CinePhil]

Essaie avec MySQL Workbench. Ce n'est pas du MCD mais directement du modèle entity/relation.
Commence par faire les entités puis tu les relies en choisisant le type de relation et ça crée automatiquement les tables associatives si nécessaire.
Par contre, la version gratuite de MySQL Workbench ne te donnera pas le lien avec la BDD mais peut te générer les requêtes de création qu'il faut ensuite lancer dans MySQL.

Et pense à choisir le type de tables InnoDB si tu veux bénéficier des contraintes de clés étrangères.

[BobaL]

Merci encore...

Que penses-tu de cette structure ?



Je n'ai renseigné que les clés étrangères pour plus de clarté.
Le soft m'avais mis idclub en clé étrangère dans toutes les tables ou idequipe apparaissait... Je les ai supprimées... Qu'en penses-tu ?

[CinePhil]

Le soft m'avais mis idclub en clé étrangère dans toutes les tables ou idequipe apparaissait... Je les ai supprimées... Qu'en penses-tu ?

C'est probablement parce que tu as utilisé des liens de type identifying relationship. Utilise les non-identifying relationship.

Passons au schéma...

Entre membre et joueur, tu as une relation 1-1. Nomalement, tu passes ça au rasoir d'Okham et tu rassembles tout dans une seule entité.

Entre equipe et rencontre, tu en as cette fois deux. Ce qui signifie qu'une équipe ne peut disputer qu'une seule rencontre !

Les relations entre joueur et club d'une part, joueur et équipe (via joueur-has-equipe) d'autre part, sont redondantes. Une équipe appartenant à un seul club, on peut retrouver le club du joueur via l'équipe.
==> Supprimer la relation entre joueur et club.

Une équipe ne joue qu'une saison ?

Une équipe peut jouer dans plusieurs championnats ?

[BobaL]

On remet ça..?



Je ne sais pas si je laisse le fait qu'une équipe puisse jouer plusieurs championnats (championnat régional, championnat de France, coupe de France...) parce qu'il y a souvent des joueurs surclassés qui revienne dans leur catégorie propre pour disputer la coupe de France par exemple. Donc l'équipe est différente dans ce cas là...

Qu'en penses-tu ?

Et encore une fois : merci !

[CinePhil]

Je ne sais pas si je laisse le fait qu'une équipe puisse jouer plusieurs championnats (championnat régional, championnat de France, coupe de France...) parce qu'il y a souvent des joueurs surclassés qui revienne dans leur catégorie propre pour disputer la coupe de France par exemple. Donc l'équipe est différente dans ce cas là...

Ce sont deux choses différentes qui compliquent un peu les choses.

D'abord, à partir de ce que tu dis, on a la règle de gestion suivante :
"Une équipe peut disputer plusieurs championnats et un championat est disputé par plusieurs équipes."
Il faut donc changer ta relation car elle dit plutôt ceci :
"Une équipe peut disputer plusieurs championnats et un championnat est disputé par une seule équipe."

D'ailleurs, je dirais plutôt "compétition" que "championnat" parce qu'une coupe n'est pas un championnat.

Ensuite, une compétition est composée de une à plusieurs rencontres et une rencontre n'est que dans une seule compétition. Il manque donc une relation entre Compétition (championnat si tu préfères) et Rencontre (MCD Merise) :
Compétition -1,n----Composer----1,1- Rencontre

Attention si tu veux enregistrer des rencontres hors compétition (matches amicaux par exemples), la relation devient la suivante :
Compétition -1,n----Composer----0,1- Rencontre

Dans le premier cas, il y aura une clé étrangère dans la table des rencontres faisant référence à l'identifiant de la compétition alors que dans le second cas il faut normalement une table associative entre les deux (que Workbench appellera probablement Competition_has_rencontre).

Venons-en aux joueurs qui changent exceptionnellement d'équipe pour une compétition. On aborde là le sujet de la composition de l'équipe lors d'une rencontre, qui est différente de l'affectation d'un joueur à une équipe par défaut (en raison de son âge ou de son niveau). ce n'est pas parce qu'un joueur fait partie d'une équipe qu'il va jouer tous les matches. Il semble donc que tu veuilles enregistrer les compositions des équipes participant à une rencontre. Alors il faut une relation entre Joueur et Rencontre :
Joueur -0,n----Participer----0,n- Rencontre

Si deux équipes du même club peuvent se rencontrer, il faut alors ajouter à la relation précédente l'équipe dans laquelle joue le joueur pour cette rencontre.

[BobaL]

Encore moi...

Alors voici ma dernière version de db...


et lorsque je lance la requête :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT cr.nom AS 'Club receveur', cv.nom AS 'Club visiteur'
FROM rencontre AS r
INNER JOIN competition AS c ON r.competition_idcompetition = c.idcompetition
INNER JOIN equipe AS er ON r.equipe_idequipe_visite = er.idequipe
INNER JOIN equipe AS ev ON r.equipe_idequipe_visiteur = ev.idequipe
INNER JOIN club AS cr ON er.club_idclub = cr.idclub
INNER JOIN club AS cv ON ev.club_idclub = cv.idclub
INNER JOIN saison_has_competition AS shc ON c.idcompetition = shc.competition_idcompetition
INNER JOIN saison AS s ON s.idsaison = shc.saison_idsaison
 
WHERE (c.nom = 'Nationale 1')
  AND (cr.nom = 'Club1' OR cv.nom = 'Club1')
  AND s.libelle = '2009-2010'

et bien j'obtiens le résultat suivant :
Club receveur Club visiteur
Club1 Club1
Club1 Club1

Vois-tu ce qui cloche encore ..?

Merci.

[CinePhil]

Tu peux poster les requêtes de création des tables (SHOW CREATE TABLE) et un exemple de données STP ?

[BobaL]

Désolé de ne pas avoir réagi plus tôt...

Voici les requêtes (en pièces jointes)

[CinePhil]

Les idequipe 1 et 2 qui se rencontrent font partie toutes les deux du club 1 !

[BobaL]

Merci beaucoup pour toute ton aide
J'aurai pu encore chercher longtemps avant de voir que les deux équipes appartenaient au même club...

Je pense encore avoir de nombreuses questions mais je peux déjà continuer mes tests...

Je vais essayer d'agrémenter mon jeu d'essai et faire la requête permettant de remonter le classement d'un championnat.
Un exemple ICI

Comment mettre une contrainte d'unicité dans MySQL Workbench ?