Jeanne et Serge

[30 minutes]

Vous avez en charge la réalisation d'une base de données pour gérer un tournoi de Volley-Ball. Les équipes, identifiées par un nom, sont composées de deux à six joueurs identifiés par leur prénom (étant donnée l'équipe à laquelle ils appartiennent). Un match oppose deux équipes, à une date donnée et toujours renseignée, dont une est gagnante, et sont joués en deux sets gagnants (le score est donc toujours de 2 pour le gagnant et 0 ou 1 pour le perdant, on décide donc de ne gérer que le score du perdant). On notera que deux équipes se rencontrent au plus une fois.

Question

Réaliser le modèle conceptuel de la BD en utilisant le formalisme UML. Énoncer les éventuelles contraintes non représentées sur le schéma.

Solution

UML model

Contraintes supplémentaires :

  • Les deux équipes impliquées dans l'association Match sont différentes.

  • Une équipe de la relation Match gagne et l'autre perd.

Question

Traduire le MCD en modèle logique relationnel. Énoncer les éventuelles contraintes non représentables en relationnel.

Solution

1
Team (#teamName:string)
2
Player (#firstName:string, #team=>Team(teamName))
3
Match (#winner=>Team(teamName), #looser=>Team(teamName), date:date, score:integer) WITH (score < 2)

Contraintes supplémentaires :

  • Les attributs Match.winner et Match.looser ont des valeurs différentes.

  • L'attribut Match.score vaut 0 ou 1.

  • 2 à 6 joueurs composent une équipe.

Question

Proposer une implémentation SQL du modèle relationnel, en intégrant le maximum de contraintes. Énoncer les éventuelles contraintes non représentables en SQL.

Solution

1
CREATE TABLE Team (
2
  teamName VARCHAR PRIMARY KEY
3
) ;
4
5
CREATE TABLE Player (
6
  firstName VARCHAR, 
7
  team VARCHAR REFERENCES Team(teamName),
8
  PRIMARY KEY (firstName, team)
9
) ;
10
11
CREATE TABLE Match (
12
  winner VARCHAR REFERENCES Team(teamName), 
13
  looser VARCHAR REFERENCES Team(teamName),
14
  date date NOT NULL,
15
  score INTEGER CHECK (score < 2),
16
  PRIMARY KEY (winner, looser),
17
  CHECK (winner <> looser)
18
) ;
Contrainte supplémentaire :
  • 2 à 6 joueurs composent une équipe.
Question
Écrivez en algèbre relationnel l'expression permettant de sélectionner les équipes ayant gagné au moins un match et perdu au moins un match. 
Solution
1
R1 = Projection (Match, Match.winner)
2
R2 = Projection (Match, Match.looser)
3
R = Jointure (R1, R2, R1.winner = R2.looser)
ou
1
R1 = Projection (Match, Match.winner)
2
R2 = Projection (Match, Match.looser)
3
R = Intersection (R1, R2)
Question
Écrire une requête SQL permettant de lister toutes les équipes ayant gagné un match, avec leur nombre de points (un match gagné valant 2 points et un match perdu 0 point), triées par leur nombre de points (avec l'équipe ayant le plus de points en premier).
Solution
1
SELECT M.winner, COUNT(M.winner)*2 AS points
2
FROM Match M
3
GROUP BY M.winner
4
ORDER BY points DESC ;