Fichier de rejeu Close
Indication Close
A propos de... Close
Commentaire Close
Systèmes d'Information
- Notions mathématiques
- Calcul relationnel
- Algèbre relationnelle
- Langage de requêtes
- Arbre de requêtes
- Exercices
- Introduction
- Commandes de bases
- Langage de définition de données (LDD)
- Langage de manipulation de données (LMD)
- Types de données
- Exercice
- Dépendances fonctionnelles
- Décomposition de relations
- Inférence logique
- Normalisation
- Aux pays des bières
- Modélisation
- Exercices
© Your Copyright
Aide
Au tournoi des Six Nations
Modèle de données
Représentation, en UML, du modèle de données à étudier.
Représentation, en SQL, du modèle de données à étudier.
DROP TABLE IF EXISTS equipes;
DROP TABLE IF EXISTS joueurs;
DROP TABLE IF EXISTS matchs;
DROP TABLE IF EXISTS selections;
CREATE TABLE equipes (
id_equipe CHAR(3) PRIMARY KEY,
nom_equipe TEXT,
pays TEXT,
coach TEXT
);
CREATE TABLE joueurs (
id_joueur INTEGER PRIMARY KEY AUTOINCREMENT,
id_equipe CHAR(3),
nom TEXT,
prenom TEXT,
position TEXT,
nationalite TEXT,
naissance DATE,
taille FLOAT CHECK (1 < taille and taille < 3) DEFAULT 1.80,
poids FLOAT CHECK (50 < poids and poids < 150) DEFAULT 80,
FOREIGN KEY(id_equipe) REFERENCES equipes(id_equipe)
);
CREATE TABLE matchs (
id_match INTEGER PRIMARY KEY AUTOINCREMENT,
id_locaux CHAR(3),
id_visiteurs CHAR(3),
jour DATE,
horaire TIME,
locaux_score INTEGER,
visiteurs_score INTEGER,
stade TEXT,
spectateurs INTEGER,
FOREIGN KEY(id_locaux) REFERENCES equipes(id_equipe),
FOREIGN KEY(id_visiteurs) REFERENCES equipes(id_equipe)
);
CREATE TABLE selections (
id_joueur INTEGER NOT NULL,
id_match INTEGER NOT NULL,
essais SMALLINT DEFAULT 0,
transformations SMALLINT DEFAULT 0,
penalites SMALLINT DEFAULT 0,
PRIMARY KEY(id_joueur,id_match),
FOREIGN KEY(id_match) REFERENCES matchs(id_match),
FOREIGN KEY(id_joueur) REFERENCES joueurs(id_joueur)
);
Exemples d’instances de tables dans une base de données SQLite.
| Table « equipes » | ||||||||||||||||||||||||||||
|
| Table « joueurs » | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
| Table « matchs » | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
| Table « selections » | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Recherches d’informations
Sur une table
L’objectif de ces exercices est de savoir formuler les requêtes de base (SELECT,FROM,WHERE)
sur une seule table de la base de données « Au tournoi des Six Nations ».
-
vérification du contenu de la table 'equipes'
- Ecrire une requête SQL répondant à la question :
- \(Q_1\) : « Rechercher les
equipesdu tournoi des six nations ».
- \(Q_1\) : « Rechercher les
Votre réponse :
sql : sixnations.sqlOutputUne solution possible :
SELECT * FROM equipes;
-
Vérification du contenu de la table 'joueurs'
- Ecrire une requête SQL répondant à la question :
- \(Q_1\) : « Rechercher les joueurs du tournoi des six nations ».
Votre réponse :
sql : sixnations.sqlOutputUne solution possible :
SELECT * FROM joueurs;
-
Vérification du contenu de la table 'matchs'
- Ecrire une requête SQL répondant à la question :
- \(Q_1\) : « Rechercher les matchs du tournoi des six nations ».
Votre réponse :
sql : sixnations.sqlOutputUne solution possible :
SELECT * FROM matchs;
-
Vérification du contenu de la table 'selections'
- Ecrire une requête SQL répondant à la question :
- \(Q_1\) : « Rechercher les selections de joueurs pour les matchs du tournoi des six nations ».
Votre réponse :
sql : sixnations.sqlOutputUne solution possible :
SELECT * FROM selections;
-
Projection
- Ecrire une requête SQL répondant à la question :
- \(Q_1\) : « Rechercher les nom et prénom des joueurs du tournoi des six nations ».
Votre réponse :
sql : sixnations.sqlOutputUne solution possible :
On applique une projection (\(\Pi\)) sur les colonnes
(nom,prenom)de la table desjoueurs.Ecriture en algèbre relationnelle :
- \(Q_1 = \Pi_{(nom,prenom)}(joueurs)\)
Requête SQL correspondante :
SELECT nom,prenom FROM joueurs;
-
Restriction
Ecrire une requête SQL répondant à la question :
- \(Q_1\) : « Rechercher les joueurs de l’équipe de France ».
Votre réponse :
sql : sixnations.sqlOutputUne solution possible :
On applique une restriction (\(\sigma\)) sur la table
joueursavec la condition[id_equipe='FRA'].Ecriture en algèbre relationnelle :
- \(Q_1 = \sigma_{[id\_equipe='FRA']}(joueurs)\)
Requête SQL correspondante :
SELECT * FROM joueurs WHERE id_equipe='FRA';
-
Projection et Restriction
Ecrire une requête SQL répondant à la question :
- \(Q_1\) : « Rechercher les nom et prénom des joueurs de l’équipe de France ».
Votre réponse :
sql : sixnations.sqlOutputUne solution possible :
On applique une projection (\(\Pi\)) sur les colonnes
(nom,prenom)et une restriction (\(\sigma\)) sur la condition[id_equipe='FRA']de la tablejoueurs.Ecriture en algèbre relationnelle :
- \(Q_1 = \Pi_{(nom,prenom)}(\sigma_{[id\_equipe='FRA']}(joueurs))\).
Requête SQL correspondante :
SELECT nom,prenom FROM joueurs WHERE id_equipe='FRA';
-
Connecteur logique
Ecrire une requête SQL répondant à la question :
- \(Q_1\) : « Rechercher les matchs joués par l’équipe de France ».
Votre réponse :
sql : sixnations.sqlOutputUne solution possible :
On vérifie (\(\sigma\)) sur la table
matchsque l’équipe qui joue à domicile ou (\(\lor\) : ou logique) l’équipe des visiteurs est bien l’équipe de France ([id_locaux='FRA' OR id_visiteurs='FRA'])Ecriture en algèbre relationnelle :
- \(Q_1 = \sigma_{[id\_locaux='FRA' \; \lor \; id\_visiteurs='FRA']}(matchs)\).
Requête SQL correspondante :
SELECT * FROM matchs WHERE id_locaux='FRA' OR id_visiteurs='FRA';
-
Connecteurs logiques
Ecrire une requête SQL répondant à la question :
- \(Q_1\) : « Rechercher les matchs joués par l’équipe de France au mois de mars ».
Votre réponse :
sql : sixnations.sqlOutputUne solution possible :
On vérifie (\(\sigma\)) sur la table
matchsque l’équipe qui joue à domicile ou (\(\lor\) : ou logique) l’équipe des visiteurs est bien l’équipe de France et (\(\land\) : et logique) que les matchs sont ceux du mois de mars.Ecriture en algèbre relationnelle :
- \(Q_1 = \sigma_{[(id\_locaux='FRA' \; \lor \; id\_visiteurs='FRA') \; \land \; month(jour)='03']}(matchs)\).
Attention à la priorité du connecteur logique \(\land\) sur le \(\lor\) (de même que la multiplication l’est sur l’addition)
- La requête :
- \(Q_1 = \sigma_{[id\_locaux='FRA' \; \lor \; id\_visiteurs='FRA' \; \land \; month(jour)='03']}(matchs)\)
donnerai comme résultat :
- les matchs à domicile de l’équipe de France et (\(\lor\) : ou logique) les matchs que l’équipe de France a joué à l’extérieur au mois de mars.
Remarque : en SQLite strftime(“%m”, jour) donnera la mois de la date du
jour.Requête SQL correspondante :
SELECT id_match,id_locaux,id_visiteurs,jour FROM matchs WHERE (id_locaux='FRA' OR id_visiteurs='FRA') AND strftime('%m', jour)='03';
Sur plusieurs tables
L’objectif de ces exercices est de savoir formuler une requête sur plusieurs tables et de créer une vue sur les informations « métier » « Au tournoi des Six Nations ».
-
Produit cartésien et restriction
Ecrire une requête SQL répondant à la question :
- \(Q_1\) : « Rechercher les informations sur les matchs et les joueurs des équipes qui ont marqués (essais,transformations, penalites) ».
Le résultat sera trié par l’identifiant des équipes qui jouent à domicile et le nom de joueurs qui ont marqués
Votre réponse :
sql : sixnations.sqlOutputUne solution possible :
On peut faire un produit cartésien (\(\times\)) entre les tables
matchs,selections,joueursen vérifiant que chaque joueur de l’ensemble desjoueursest bien lié auxmatchspar lesselections. On récupère seulement les joueurs qui ont marqués des essais,transformations ou penalités.Ecriture en algèbre relationnelle :
- \(R_1 = \sigma_{[essais > 0 \lor transformations > 0 \lor penalites > 0]}(selections)\)
- \(T_1 = \times(R_1,joueurs)\)
- \(R_2 = \sigma_{[R_1.id\_joueur=joueurs.id\_joueur]}(T_1)\)
- \(T_2 = \times(matchs,R_2)\)
- \(Q_1 = \sigma_{[matchs.id\_match=R_2.id\_match]}(T_2)\)
On peut aussi faire une jointure naturelle (\(\Join_{[]}\)) puisque les
matchssont liés auxjoueurspar des noms de colonnes communes (id_match,id_joueur) sur la tableselections.Ecriture en algèbre relationnelle :
- \(R_1 = \sigma_{[essais > 0 \lor transformations > 0 \lor penalites > 0]}(selections)\)
- \(NJ_1 = \Join_{[]}(R_1,joueurs)\)
- \(NJ_2 = \Join_{[]}(matchs,NJ_1)\)
Requête SQL correspondante :
SELECT "" AS "Produit cartésien"; SELECT * FROM matchs m,selections s, joueurs j WHERE m.id_match=s.id_match AND s.id_joueur=j.id_joueur AND (essais > 0 OR transformations > 0 OR penalites > 0) ORDER BY id_locaux,nom; SELECT "" AS "Jointure naturelle"; SELECT * FROM matchs NATURAL JOIN selections NATURAL JOIN joueurs WHERE essais > 0 OR transformations > 0 OR penalites > 0 ORDER BY id_locaux,nom;
-
Création de vues
- Créer une vue
resultatssur les principales informations permettant de visualiser les résultats des matchs du tournoi des six nations : - les matchs :
(id_locaux,id_visiteurs,jour,locaux_score, visiteurs_score) - les joueurs des équipes :
(nom, id_equipe) - qui ont marqués :
essais,transformations, penalites.
- les matchs :
Le résultat de la requête sera trié par l’identifiant des équipes qui jouent à domicile et le nom de joueurs qui ont marqués.
Votre réponse :
sql : sixnations.sqlOutputUne solution possible :
On “encapsule” dans une vue (
CREATE VIEW resultats AS) une requête SQL pour consulter plus simplement les informations sur les résulats intéressants des matchs :(id_locaux,id_visiteurs,jour,locaux_score, visiteurs_score)
des joueurs des équipes :
(nom, id_equipe)
qui ont marqués :
essais,transformations, penalites.
Ecriture en algèbre relationnelle de la requête à mettre dans une vue :
- \(NJ_1 = \Join_{[]}(selections,joueurs)\)
- \(NJ_2 = \Join_{[]}(matchs,NJ_1)\)
- \(R_1 = \sigma_{[essais > 0 \lor transformations > 0 \lor penalites > 0]}(NJ_2)\)
- \(Q_1 = \Pi_{(id\_locaux, id\_visiteurs, jour, .., essais,transformations, penalites)}(R_1)\)
Requête SQL correspondante :
CREATE VIEW resultats AS SELECT id_locaux, id_visiteurs, jour,locaux_score,visiteurs_score, nom, id_equipe, essais,transformations, penalites FROM matchs NATURAL JOIN selections NATURAL JOIN joueurs WHERE essais > 0 OR transformations > 0 OR penalites > 0 ORDER BY id_locaux,nom; SELECT "" AS "Resultats"; SELECT * FROM resultats;
- Créer une vue
Opérations ensemblistes
L’objectif de ces exercices est de savoir formuler des opérations ensemblistes (\(\cup,\cap,\setminus\)) « Au tournoi des six nations ».
-
Union
- Ecrire une requête SQL répondant à la question :
- \(Q_1\) : « récupérer les noms du coach et des joueurs du XV de France ».
Votre réponse :
sql : sixnations.sqlOutputUne solution possible :
On fait une union (\(\cup\)) entre la requête permettant de récupérer le nom du coach du XV de France dans l’ensemble des
equipeset celle pour rechercher les joueurs de l’équipe du XV de France dans l’ensemble desjoueurs. Pour obtenir d’abord le nom du coach on pourra faire unUNION ALLqui concatène le résultat des deux requêtes.Ecriture en algèbre relationnelle :
- \(R_1 = \sigma_{[id\_equipe='FRA']}(equipes)\)
- \(P_1 = \Pi_{(coach)}(R_1)\)
- \(R_2 = \sigma_{[id\_equipe='FRA']}(joueur)\)
- \(P_2 = \Pi_{(nom)}(R_2)\)
- \(Q_1 = \cup(P_1,P_2)\)
Requête SQL correspondante :
SELECT coach AS "noms des joueurs et du coach" FROM equipes WHERE id_equipe='FRA' UNION SELECT nom FROM joueurs WHERE id_equipe='FRA'; SELECT coach AS "nom du coach et noms des joueurs" FROM equipes WHERE id_equipe='FRA' UNION ALL SELECT nom FROM joueurs WHERE id_equipe='FRA';
-
Intersection
Ecrire une requête SQL répondant à la question :
- \(Q_1\) : « récupérer les joueurs qui ont été selectionnés dans le XV de France pendant le tournoi des 6 nations ».
Votre réponse :
sql : sixnations.sqlOutputUne solution possible :
On fait une intersection (\(\cap\)) entre la requête permettant de récupérer le nom des joueurs du XV de France dans
joueurset celle permettant de récupérer le nom des joueurs qui ont été sélectionnés (jointure naturelle entre les tablesjoueurs,selections).Ecriture en algèbre relationnelle :
- \(R_1 = \sigma_{[id\_equipe='FRA']}(joueurs)\)
- \(P_1 = \Pi_{(nom)}(R_1)\)
- \(NJ_1 = \Join_{[]}(joueurs,selections)\)
- \(P_2 = \Pi_{(nom)}(NJ_1)\)
- \(Q_1 = \cap(P_1,P_2)\)
On pourra faire une requête imbriquée avec un IN (\(\in\)).
On pourra aussi faire une requête imbriquée avec un EXISTS (\(\exists\)).
Enfin on pourra faire une jointure naturelle entre les tables
joueurs,selectionsen récupérant les joueurs sélectionnés dans le XV de France.Requête SQL correspondante :
SELECT nom FROM joueurs WHERE id_equipe='FRA' INTERSECT SELECT nom FROM joueurs NATURAL JOIN selections; SELECT nom FROM joueurs WHERE id_equipe='FRA' AND id_joueur IN (SELECT id_joueur FROM selections); SELECT nom FROM joueurs j WHERE id_equipe='FRA' AND EXISTS (SELECT * FROM selections WHERE id_joueur=j.id_joueur); SELECT nom FROM joueurs NATURAL JOIN selections WHERE id_equipe='FRA';
-
Différence
Ecrire une requête SQL répondant à la question :
- \(Q_1\) : « récupérer les joueurs qui n’ont pas été selectionnés dans le XV de France pendant le tournoi des 6 nations ».
Votre réponse :
sql : sixnations.sqlOutputUne solution possible :
On fait une différence (\(\setminus\)) entre la requête permettant de récupérer le nom des joueurs du XV de France dans
joueurset celle permettant de récupérer le nom des joueurs qui ont été sélectionnés (jointure naturelle entre les tablesjoueurs,selections).Ecriture en algèbre relationnelle :
- \(R_1 = \sigma_{[id\_equipe='FRA']}(joueurs)\)
- \(P_1 = \Pi_{(nom)}(R_1)\)
- \(NJ_1 = \Join_{[]}(joueurs,selections)\)
- \(P_2 = \Pi_{(nom)}(NJ_1)\)
- \(Q_1 = \setminus(P_1,P_2)\)
On pourra faire une requête imbriquée avec un NOT IN (\(\notin\)).
On pourra aussi faire une requête imbriquée avec un NOT EXISTS (\(\notexists\)).
On ne pourra pas faire une jointure naturelle entre les tables
joueurs,selectionsen faisant une restriction sur les joueurs du XV de France et en faisant une diffférence entre l’identifiant du joueur dans l’ensemble des joueurs et l’identifiant du joueur dans l’ensemble des sélections.Requête SQL correspondante :
SELECT "" AS "EXCEPT"; SELECT nom FROM joueurs WHERE id_equipe='FRA' EXCEPT SELECT nom FROM joueurs NATURAL JOIN selections; SELECT "" AS "NOT IN"; SELECT nom FROM joueurs WHERE id_equipe='FRA' AND id_joueur NOT IN (SELECT id_joueur FROM selections); SELECT "" AS "NOT EXISTS"; SELECT nom FROM joueurs j WHERE id_equipe='FRA' AND NOT EXISTS (SELECT * FROM selections WHERE id_joueur=j.id_joueur); /* SELECT "" AS "CROSS JOIN, RESTRICT"; SELECT nom FROM joueurs j, selections s WHERE j.id_equipe='FRA' AND j.id_joueur<>s.id_joueur; */
Division relationnelle
L’objectif de ces exercices est de savoir répondre « Au tournoi des six nations » à des questions du type :
- “trouve-moi les \(x\) qui sont associés à tous (\(\forall\)) les \(y\)“
-
NOT EXISTS,EXCEPT
Ecrire une requête SQL répondant à la question :
- \(Q_1\) : « Rechercher le nom des joueurs qui ont été sélectionnés pour tous les matchs de leur équipe ».
Votre réponse :
sql : sixnations.sqlOutputUne solution possible :
On pourra mettre en oeuvre l’opération de division relationnelle par les opérateurs :
NOT EXISTS,EXCEPT.L’idée est de rechercher les matchs de l’équipe du joueur et les matchs pour lesquels il est sélectionné.
Si la différence entre ces deux ensembles est nulle alors le joueur est sélectionné pour tous les matchs de son équipe.
Requête SQL correspondante :
SELECT j.nom FROM joueurs j WHERE NOT EXISTS ( SELECT id_match FROM matchs m WHERE j.id_equipe=m.id_locaux OR j.id_equipe=m.id_visiteurs EXCEPT SELECT id_match FROM selections s WHERE j.id_joueur=s.id_joueur);
-
GROUP BY, COUNT
Ecrire une requête SQL répondant à la question :
- \(Q_1\) : « Rechercher le nom des joueurs qui ont été sélectionnés pour tous les matchs de leur équipe ».
Votre réponse :
sql : sixnations.sqlOutputUne solution possible :
On pourra met en œuvre l’opération de division relationnelle en reformulant la question pour compter le nombre de sélections des joueurs :
- trouver le nom des joueurs qui ont un nombre de sélections égal au nombre total de match joués par leur équipe (
joueurs.id_equipe).
Requête SQL correspondante :
SELECT id_joueur,nom FROM joueurs NATURAL JOIN selections GROUP BY id_joueur,nom HAVING COUNT(DISTINCT id_match) = ( SELECT COUNT(id_match) FROM matchs WHERE joueurs.id_equipe=matchs.id_locaux OR joueurs.id_equipe=matchs.id_visiteurs );
Fonctions d’agrégat
L’objectif de ces exercices est de savoir appliquer des fonctions d’agrégat sur les recherches « Au tournoi des six nations ».
-
Sur une requête simple
Ecrire une requête SQL répondant à la question :
- \(Q_1\) : «Quel a été l’affluence moyenne sur tous les matchs du tournoi des six nations ? ».
Votre réponse :
sql : sixnations.sqlOutputUne solution possible :
On pourra appliquer la fonction
AVG()sur la table desmatchs.On pourrait aussi calculer la somme (
SUM()) des affluences divisée par le nombre (COUNT()) de matchs joués.Requête SQL correspondante :
SELECT "" AS "AVG"; SELECT AVG(spectateurs) AS "Affluence moyenne" FROM matchs; SELECT "" AS "SUM()/COUNT()"; SELECT SUM(spectateurs)/COUNT(*) AS "Affluence moyenne" FROM matchs WHERE spectateurs IS NOT NULL;
-
Sur une requête imbriquée (1/2)
Ecrire une requête SQL répondant à la question :
- \(Q_1\) : « Rechercher le nom des équipes jouant à domicile, le nom de leur stade et le nombre de spectateurs pour les matchs qui ont une affluence supérieure à la moyenne de fréquentation lors des matchs».
Votre réponse :
sql : sixnations.sqlOutputUne solution possible :
On pourra calculer dans une requête imbriquée la fréquentation moyenne (
AVG()) de spectateurs lors des matchs puis tester pour chaque match de l’équipe de France si le nombre de spectateurs est supérieur à cette moyenne.Requête SQL correspondante :
SELECT id_locaux, stade, spectateurs FROM matchs WHERE id_locaux='FRA' AND spectateurs > (SELECT AVG(spectateurs) FROM matchs);
-
Sur une requête imbriquée (2/2)
Ecrire une requête SQL répondant à la question :
- \(Q_1\) : « rechercher le nom,prenom et poste des joueurs irlandais qui ont été sélectionnés pour les matchs de leur equipe à l’extérieur et qui ont marqués plus d’essais que les joueurs sélectionnés pour les matchs de l’équipe d’Ecosse ».
Votre réponse :
sql : sixnations.sqlOutputUne solution possible :
On cherchera d’abord, dans une requête imbriquée, la valeur maximale
MAX()desessaismarqués par les joueurs écossais sélectionnés pour les matchs de leur équipe.On peut utiliser ensuite la vue
resultatspour récupérer les joueurs de l’équipe d’Irlande qui auraient marqués plus d’essais à l’extérieur que cette valeur maximale.- Remarques :
- on peut utiliser la vue
resultatsdans la requête externe qui a besoin d’informations liées aux tables de cette vue. - il ne faut pas utiliser la vue
resultatsdans la requête imbriquée. Les informations utiles pour trouver le maximum d’essais marqués par les écossais peuvent se trouver dans les tablesjoueurs, selections. La requête imbriquée n’a donc pas besoin d’utiliser la tablematchs.
Requête SQL correspondante :
- on peut utiliser la vue
CREATE VIEW resultats AS SELECT id_locaux,id_visiteurs,jour,locaux_score,visiteurs_score,nom,prenom,position,id_equipe,essais,transformations, penalites FROM matchs NATURAL JOIN selections NATURAL JOIN joueurs WHERE essais > 0 OR transformations > 0 OR penalites > 0; SELECT * FROM resultats; SELECT nom,prenom,position FROM resultats r WHERE r.id_visiteurs='IRL' AND r.id_equipe=r.id_visiteurs AND r.essais > (SELECT MAX(essais) FROM selections NATURAL JOIN joueurs WHERE joueurs.id_equipe='ECO');
Groupements
L’objectif de ces exercices est de savoir regrouper le informations et appliquer des fonctions d’agrégat sur ces regroupements « Au tournoi des six nations ».
-
Groupement
Ecrire une requête SQL répondant à la question :
- \(Q_1\) : « nom des joueurs, leur position et le nombre total de points marqués par les joueurs ».
Votre réponse :
sql : sixnations.sqlOutputUne solution possible :
On fait d’abord un regroupement (
GROUP BY) d’information par joueur (id_joueur)sélectionnés.On calcule ensuite la somme des points qu’ils ont marqués (essais:5 points,transformations:2 points, penalites:3 points).
On retient dans le résultat final les
nom,positiondu joueur et la somme des points qu’il a marqué.Requête SQL correspondante :
SELECT nom, position, SUM(5*essais)+ SUM(2*transformations) + SUM(3*penalites) AS points FROM joueurs NATURAL JOIN selections GROUP BY id_joueur;
-
Groupement et restriction
Ecrire une requête SQL répondant à la question :
- \(Q_1\) : « nom des joueurs, leur position et le nombre total de points marqués pour les joueurs ayant fait au moins 2 matchs ».
Votre réponse :
sql : sixnations.sqlOutputUne solution possible :
On fait un regroupement (
GROUP BY) par joueur (id_joueur) sélectionné.On ne retient dans ces regroupements que les joueurs ayant été sélectionnées pour plus d’un match de leur équipe (
HAVING COUNT(id_match) > 1).On récupère dans le résultat final le nom des joueurs, leur poste et les points qu’ils ont marqués (essais:5 points,transformations:2 points,penalites:3 points).
Requête SQL correspondante :
SELECT nom, position, COUNT(id_match) AS matchs, SUM(5*essais)+ SUM(2*transformations) + SUM(3*penalites) AS points FROM joueurs NATURAL JOIN selections GROUP BY id_joueur HAVING COUNT(id_match) > 1;
-
Groupement et restriction avec requête imbriquée
- Ecrire une requête SQL répondant à la question :
- \(Q_1\) : « nom des joueurs, leur position et le nombre total de points marqués pour les joueurs ayant marqués le plus d’essais ».
Votre réponse :
sql : sixnations.sqlOutputUne solution possible :
On fait un regroupement (
GROUP BY) par joueur (id_joueur) sélectionnés.On ne retient dans ces regroupements que les joueurs ayant marqué le nombre maximal d’essais.
On récupère dans le résultat final les
nom,prenom,positionet points marqués (essais:5 points,transformations:2 points,penalites:3 points) par ces joueurs.Requête SQL correspondante :
SELECT nom, prenom,position, SUM(5*essais)+ SUM(2*transformations) + SUM(3*penalites) AS points FROM joueurs NATURAL JOIN selections GROUP BY id_joueur HAVING essais = (SELECT MAX(essais) FROM selections);
-
Restriction, groupement et restriction
- Ecrire une requête SQL répondant à la question :
- \(Q_1\) : « Rechercher le nom,prenom, position et score des joueurs de l’équipe d’Irlande ou d’Ecosse ayant fait au moins 2 matchs dans le tournoi des six nations ».
Votre réponse :
sql : sixnations.sqlOutputUne solution possible :
On vérifie d’abord que ce sont bien des joueurs sélectionnés en équipe d’Irlande iu d’Ecosse.
On fait ensuite un regroupement(
GROUP BY) par joueur (id_joueur).On ne retient dans ces regroupements que les joueurs ayant était sélection plus d’une fois.
On récupère dans le résultat final les noms d’équipes (
id_equipe) et lesnom,prenom,positionet points marqués (essais:5 points,transformations:2 points,penalites:3 points) par les joueurs de ces équipes.Requête SQL correspondante :
SELECT id_equipe,nom, prenom, position,SUM(5*essais)+ SUM(2*transformations) + SUM(3*penalites) AS points FROM joueurs NATURAL JOIN selections WHERE id_equipe IN ('IRL','ECO') GROUP BY id_joueur HAVING COUNT(id_match) > 1;
Arbres de requêtes
Sur une table
L’objectif de ces exercices est de savoir représenter sous forme d’arbre de requêtes les requêtes de base sur une seule table de la base de données « Au Tournoi des six nations ».
-
Vérification du contenu de la table 'equipes'
- Construire un arbre de requêtes répondant à la question :
- \(Q_1\) : « Rechercher les
equipesdu tournoi des six nations ».
- \(Q_1\) : « Rechercher les
Votre réponse :
Arbre de requêtes : graph363.jsonTables
Opérateurs unaires
Opérateurs binaires
Vue d'ensemble id_equipe nom_equipe pays coach FRA XV de France France Noves ANG XV de la Rose Angleterre Jones GAL XV du Poireau Pays de Galles Howley IRL XV du Trèfle Irlande Schmidt ECO XV du Chardon Ecosse Cotter ITA Azzuri Italie O'shea Une solution possible :
Récupérer les équipes : \(\displaystyle Q_1 = equipes\)
Arbre de requêtes : sixnations-0-1.jsonTables
Opérateurs unaires
Opérateurs binaires
Vue d'ensemble id_equipe nom_equipe pays coach FRA XV de France France Noves ANG XV de la Rose Angleterre Jones GAL XV du Poireau Pays de Galles Howley IRL XV du Trèfle Irlande Schmidt ECO XV du Chardon Ecosse Cotter ITA Azzuri Italie O'shea -
Vérification du contenu de la table 'joueurs'
- Construire un arbre de requêtes répondant à la question :
- \(Q_1\) : « Rechercher les joueurs du tournoi des six nations ».
Votre réponse :
Arbre de requêtes : graph370.jsonTables
Opérateurs unaires
Opérateurs binaires
Vue d'ensemble id_joueur id_equipe nom prenom position nationalite naissance taille poids 1 ECO Laidlaw Greg 1/2 melee Ecosse 1985-10-12 1.75 80 2 ECO Hogg Stuart Arriere Ecosse 1992-06-24 1.8 80 3 ECO Dunbar Alex 3/4 centre Ecosse 1990-04-23 1.91 80 4 ECO Russell Finn 1/2 ouverture Ecosse 1992-09-23 1.83 80 5 ECO Swinson Tim 2eme ligne Angleterre 1987-02-17 1.95 80 6 ECO Gray Richie 2eme ligne Ecosse 1989-08-24 2.06 80 7 IRL Earls Keith 3/4 centre Irlande 1987-10-02 1.8 80 8 IRL Henderson Iain 2eme ligne Irlande du Nord 1992-02-21 1.98 80 9 IRL Jackson Paddy 1/2 ouverture Irlande du Nord 1990-04-05 1.78 80 10 IRL Stander Christiaan Johan 3eme ligne centre Afrique du Sud 1992-01-05 1.89 80 11 IRL Gilroy Craig 3/4 aile Irlande du Nord 1991-03-11 1.83 80 12 IRL Ringrose Garry 3/4 centre Irlande 1995-01-26 1.87 80 13 IRL Best Rory Talonneur Irlande du Nord 1982-08-15 1.8 80 14 ANG Te'o Ben centre Nouvelle-Zélande 1987-01-27 1.89 80 15 ANG Farrell Owen 3/4 centre Angleterre 1991-09-24 1.88 80 16 ANG Daly Elliot 3/4 centre Angleterre 1992-10-08 1.84 80 17 ANG Youngs Tom Talonneur Angleterre 1987-01-28 1.75 80 18 ANG Cole Dan Pilier Angleterre 1987-05-09 1.91 80 19 FRA Slimani Rabah Pilier France 1989-10-18 1.78 80 20 FRA Lopez Camille 1/2 ouverture France 1989-04-03 1.75 80 21 FRA Fickou Gael 3/4 centre France 1994-03-29 1.9 80 22 FRA Spedding Scott Arriere Afrique du Sud 1986-05-04 1.88 80 23 ITA Gori Edoardo 1/2 melee Italie 1990-03-05 1.78 80 24 ITA Canna Carlo 1/2 ouverture Italie 1992-08-25 1.91 80 25 ITA Parisse Sergio 3eme ligne Argentine 1983-09-12 1.96 80 26 GAL Davies Jonathan 3/4 centre Angleterre 1988-04-05 1.86 80 27 GAL Williams Liam 3/4 aile Pays de Galles 1991-04-09 1.88 80 28 GAL North George 3/4 aile Angleterre 1992-04-13 1.92 80 29 GAL Halfpenny Leigh Arriere Pays de Galles 1988-12-22 1.78 80 30 GAL Biggar Dan 1/2 ouverture Pays de Galles 1989-10-16 1.88 80 Une solution possible :
Récupérer les joueurs : \(\displaystyle Q_1 = joueurs\)
Arbre de requêtes : sixnations-0-2.jsonTables
Opérateurs unaires
Opérateurs binaires
Vue d'ensemble id_joueur id_equipe nom prenom position nationalite naissance taille poids 1 ECO Laidlaw Greg 1/2 melee Ecosse 1985-10-12 1.75 80 2 ECO Hogg Stuart Arriere Ecosse 1992-06-24 1.8 80 3 ECO Dunbar Alex 3/4 centre Ecosse 1990-04-23 1.91 80 4 ECO Russell Finn 1/2 ouverture Ecosse 1992-09-23 1.83 80 5 ECO Swinson Tim 2eme ligne Angleterre 1987-02-17 1.95 80 6 ECO Gray Richie 2eme ligne Ecosse 1989-08-24 2.06 80 7 IRL Earls Keith 3/4 centre Irlande 1987-10-02 1.8 80 8 IRL Henderson Iain 2eme ligne Irlande du Nord 1992-02-21 1.98 80 9 IRL Jackson Paddy 1/2 ouverture Irlande du Nord 1990-04-05 1.78 80 10 IRL Stander Christiaan Johan 3eme ligne centre Afrique du Sud 1992-01-05 1.89 80 11 IRL Gilroy Craig 3/4 aile Irlande du Nord 1991-03-11 1.83 80 12 IRL Ringrose Garry 3/4 centre Irlande 1995-01-26 1.87 80 13 IRL Best Rory Talonneur Irlande du Nord 1982-08-15 1.8 80 14 ANG Te'o Ben centre Nouvelle-Zélande 1987-01-27 1.89 80 15 ANG Farrell Owen 3/4 centre Angleterre 1991-09-24 1.88 80 16 ANG Daly Elliot 3/4 centre Angleterre 1992-10-08 1.84 80 17 ANG Youngs Tom Talonneur Angleterre 1987-01-28 1.75 80 18 ANG Cole Dan Pilier Angleterre 1987-05-09 1.91 80 19 FRA Slimani Rabah Pilier France 1989-10-18 1.78 80 20 FRA Lopez Camille 1/2 ouverture France 1989-04-03 1.75 80 21 FRA Fickou Gael 3/4 centre France 1994-03-29 1.9 80 22 FRA Spedding Scott Arriere Afrique du Sud 1986-05-04 1.88 80 23 ITA Gori Edoardo 1/2 melee Italie 1990-03-05 1.78 80 24 ITA Canna Carlo 1/2 ouverture Italie 1992-08-25 1.91 80 25 ITA Parisse Sergio 3eme ligne Argentine 1983-09-12 1.96 80 26 GAL Davies Jonathan 3/4 centre Angleterre 1988-04-05 1.86 80 27 GAL Williams Liam 3/4 aile Pays de Galles 1991-04-09 1.88 80 28 GAL North George 3/4 aile Angleterre 1992-04-13 1.92 80 29 GAL Halfpenny Leigh Arriere Pays de Galles 1988-12-22 1.78 80 30 GAL Biggar Dan 1/2 ouverture Pays de Galles 1989-10-16 1.88 80 -
Vérification du contenu de la table 'matchs'
Construire un arbre de requêtes répondant à la question :
- \(Q_1\) : « Rechercher les matchs du tournoi des six nations ».
Votre réponse :
Arbre de requêtes : graph377.jsonTables
Opérateurs unaires
Opérateurs binaires
Vue d'ensemble id_match id_locaux id_visiteurs jour horaire locaux_score visiteurs_score stade spectateurs 1 ECO IRL 2017-02-04 15:25:00 27 22 Murrayfield 67144 2 ANG FRA 2017-02-04 17:50:00 19 16 Twickenham 81902 3 ITA GAL 2017-02-05 15:00:00 7 33 Stadio olympico 40986 4 ITA IRL 2017-02-11 15:25:00 10 63 Stadio olympico 50197 5 GAL ANG 2017-02-11 17:50:00 16 21 Millennium Stadium 74500 6 FRA ECO 2017-02-12 16:00:00 22 16 Stade de France 75283 7 ECO GAL 2017-02-25 15:25:00 Murrayfield 8 IRL FRA 2017-02-25 17:50:00 Aviva Stadium 9 ANG ITA 2017-02-26 16:00:00 Twickenham 10 GAL IRL 2017-03-10 21:05:00 Millennium Stadium 11 ITA FRA 2017-03-11 14:30:00 Stadio olympico 12 ANG ECO 2017-03-11 17:00:00 Twickenham 13 ECO ITA 2017-03-18 13:30:00 Murrayfield 14 FRA GAL 2017-03-18 15:45:00 Stade de France 15 IRL ANG 2017-03-18 18:00:00 Aviva Stadium Une solution possible :
Récupérer les matchs : \(\displaystyle Q_1 = matchs\)
Arbre de requêtes : sixnations-0-3.jsonTables
Opérateurs unaires
Opérateurs binaires
Vue d'ensemble id_match id_locaux id_visiteurs jour horaire locaux_score visiteurs_score stade spectateurs 1 ECO IRL 2017-02-04 15:25:00 27 22 Murrayfield 67144 2 ANG FRA 2017-02-04 17:50:00 19 16 Twickenham 81902 3 ITA GAL 2017-02-05 15:00:00 7 33 Stadio olympico 40986 4 ITA IRL 2017-02-11 15:25:00 10 63 Stadio olympico 50197 5 GAL ANG 2017-02-11 17:50:00 16 21 Millennium Stadium 74500 6 FRA ECO 2017-02-12 16:00:00 22 16 Stade de France 75283 7 ECO GAL 2017-02-25 15:25:00 Murrayfield 8 IRL FRA 2017-02-25 17:50:00 Aviva Stadium 9 ANG ITA 2017-02-26 16:00:00 Twickenham 10 GAL IRL 2017-03-10 21:05:00 Millennium Stadium 11 ITA FRA 2017-03-11 14:30:00 Stadio olympico 12 ANG ECO 2017-03-11 17:00:00 Twickenham 13 ECO ITA 2017-03-18 13:30:00 Murrayfield 14 FRA GAL 2017-03-18 15:45:00 Stade de France 15 IRL ANG 2017-03-18 18:00:00 Aviva Stadium -
Vérification du contenu de la table 'selections'
Construire un arbre de requêtes répondant à la question :
- \(Q_1\) : « Rechercher les selections de joueurs pour les matchs du tournoi des six nations ».
Votre réponse :
Arbre de requêtes : graph384.jsonTables
Opérateurs unaires
Opérateurs binaires
Vue d'ensemble id_joueur id_match essais transformations penalites 2 1 2 0 0 3 1 1 0 0 1 1 0 3 2 7 1 1 0 0 8 1 1 0 0 9 1 1 2 1 14 2 1 0 0 15 2 0 1 3 16 2 0 0 1 19 2 1 0 0 20 2 0 1 3 23 3 1 0 0 24 3 0 1 0 26 3 1 0 0 27 3 1 0 0 28 3 1 0 0 29 3 0 3 4 27 5 1 0 0 29 5 0 1 3 16 5 1 0 0 17 5 1 0 0 15 5 0 1 3 24 4 0 1 1 7 4 2 0 0 10 4 3 0 0 11 4 3 0 0 12 4 1 0 0 9 4 0 9 0 21 6 1 0 0 20 6 0 1 5 2 6 1 0 0 5 6 1 0 0 4 6 0 0 2 20 8 0 0 0 20 11 0 0 0 20 14 0 0 0 Une solution possible :
Récupérer les sélections : \(\displaystyle Q_1 = selections\)
Arbre de requêtes : sixnations-0-4.jsonTables
Opérateurs unaires
Opérateurs binaires
Vue d'ensemble id_joueur id_match essais transformations penalites 2 1 2 0 0 3 1 1 0 0 1 1 0 3 2 7 1 1 0 0 8 1 1 0 0 9 1 1 2 1 14 2 1 0 0 15 2 0 1 3 16 2 0 0 1 19 2 1 0 0 20 2 0 1 3 23 3 1 0 0 24 3 0 1 0 26 3 1 0 0 27 3 1 0 0 28 3 1 0 0 29 3 0 3 4 27 5 1 0 0 29 5 0 1 3 16 5 1 0 0 17 5 1 0 0 15 5 0 1 3 24 4 0 1 1 7 4 2 0 0 10 4 3 0 0 11 4 3 0 0 12 4 1 0 0 9 4 0 9 0 21 6 1 0 0 20 6 0 1 5 2 6 1 0 0 5 6 1 0 0 4 6 0 0 2 20 8 0 0 0 20 11 0 0 0 20 14 0 0 0 -
Projection
Construire un arbre de requêtes répondant à la question :
- \(Q_1\) : « Rechercher les nom et prénom des joueurs du tournoi des six nations ».
Votre réponse :
Arbre de requêtes : graph391.jsonTables
Opérateurs unaires
Opérateurs binaires
Vue d'ensemble nom prenom Laidlaw Greg Hogg Stuart Dunbar Alex Russell Finn Swinson Tim Gray Richie Earls Keith Henderson Iain Jackson Paddy Stander Christiaan Johan Gilroy Craig Ringrose Garry Best Rory Te'o Ben Farrell Owen Daly Elliot Youngs Tom Cole Dan Slimani Rabah Lopez Camille Fickou Gael Spedding Scott Gori Edoardo Canna Carlo Parisse Sergio Davies Jonathan Williams Liam North George Halfpenny Leigh Biggar Dan Une solution possible :
On applique une projection (\(\Pi\)) sur les colonnes
(nom,prenom)de la table desjoueurs.Ecriture en algèbre relationnelle :
Arbre de requêtes : sixnations-1.json- \(Q_1 = \Pi_{(nom,prenom)}(joueurs)\)
Tables
Opérateurs unaires
Opérateurs binaires
Vue d'ensemble nom prenom Laidlaw Greg Hogg Stuart Dunbar Alex Russell Finn Swinson Tim Gray Richie Earls Keith Henderson Iain Jackson Paddy Stander Christiaan Johan Gilroy Craig Ringrose Garry Best Rory Te'o Ben Farrell Owen Daly Elliot Youngs Tom Cole Dan Slimani Rabah Lopez Camille Fickou Gael Spedding Scott Gori Edoardo Canna Carlo Parisse Sergio Davies Jonathan Williams Liam North George Halfpenny Leigh Biggar Dan -
Restriction
Construire un arbre de requêtes répondant à la question :
- \(Q_1\) : « Rechercher les joueurs de l’équipe de France ».
Votre réponse :
Arbre de requêtes : graph398.jsonTables
Opérateurs unaires
Opérateurs binaires
Vue d'ensemble id_joueur id_equipe nom prenom position nationalite naissance taille poids 19 FRA Slimani Rabah Pilier France 1989-10-18 1.78 80 20 FRA Lopez Camille 1/2 ouverture France 1989-04-03 1.75 80 21 FRA Fickou Gael 3/4 centre France 1994-03-29 1.9 80 22 FRA Spedding Scott Arriere Afrique du Sud 1986-05-04 1.88 80 Une solution possible :
On applique une restriction (\(\sigma\)) sur la table
joueursavec la condition[id_equipe='FRA'].- Ecriture en algèbre relationnelle :
- \(Q_1 = \sigma_{[id\_equipe='FRA']}(joueurs)\)
Tables
Opérateurs unaires
Opérateurs binaires
Vue d'ensemble id_joueur id_equipe nom prenom position nationalite naissance taille poids 19 FRA Slimani Rabah Pilier France 1989-10-18 1.78 80 20 FRA Lopez Camille 1/2 ouverture France 1989-04-03 1.75 80 21 FRA Fickou Gael 3/4 centre France 1994-03-29 1.9 80 22 FRA Spedding Scott Arriere Afrique du Sud 1986-05-04 1.88 80 -
Projection et Restriction
Construire un arbre de requêtes répondant à la question :
- \(Q_1\) : « Rechercher les nom et prénom des joueurs de l’équipe de France ».
Votre réponse :
Arbre de requêtes : graph405.jsonTables
Opérateurs unaires
Opérateurs binaires
Vue d'ensemble nom prenom Slimani Rabah Lopez Camille Fickou Gael Spedding Scott Une solution possible :
On applique une projection (\(\Pi\)) sur les colonnes
(nom,prenom)et une restriction (\(\sigma\)) sur la condition[id_equipe='FRA']de la tablejoueurs.Ecriture en algèbre relationnelle :
Arbre de requêtes : sixnations-3.json- \(Q_1 = \Pi_{(nom,prenom)}(\sigma_{[id\_equipe='FRA']}(joueurs))\).
Tables
Opérateurs unaires
Opérateurs binaires
Vue d'ensemble nom prenom Slimani Rabah Lopez Camille Fickou Gael Spedding Scott -
Connecteur logique
Construire un arbre de requêtes répondant à la question :
- \(Q_1\) : « Rechercher les matchs joués par l’équipe de France ».
Votre réponse :
Arbre de requêtes : graph412.jsonTables
Opérateurs unaires
Opérateurs binaires
Vue d'ensemble id_match id_locaux id_visiteurs jour horaire locaux_score visiteurs_score stade spectateurs 2 ANG FRA 2017-02-04 17:50:00 19 16 Twickenham 81902 6 FRA ECO 2017-02-12 16:00:00 22 16 Stade de France 75283 8 IRL FRA 2017-02-25 17:50:00 Aviva Stadium 11 ITA FRA 2017-03-11 14:30:00 Stadio olympico 14 FRA GAL 2017-03-18 15:45:00 Stade de France Une solution possible :
On vérifie (\(\sigma\)) sur la table
matchsque l’équipe qui joue à domicile ou (\(\lor\) : ou logique) l’équipe des visiteurs est bien l’équipe de France ([id_locaux='FRA' OR id_visiteurs='FRA'])Ecriture en algèbre relationnelle :
Arbre de requêtes : sixnations-4.json- \(Q_1 = \sigma_{[id\_locaux='FRA' \; \lor \; id\_visiteurs='FRA']}(matchs)\).
Tables
Opérateurs unaires
Opérateurs binaires
Vue d'ensemble id_match id_locaux id_visiteurs jour horaire locaux_score visiteurs_score stade spectateurs 2 ANG FRA 2017-02-04 17:50:00 19 16 Twickenham 81902 6 FRA ECO 2017-02-12 16:00:00 22 16 Stade de France 75283 8 IRL FRA 2017-02-25 17:50:00 Aviva Stadium 11 ITA FRA 2017-03-11 14:30:00 Stadio olympico 14 FRA GAL 2017-03-18 15:45:00 Stade de France -
Connecteurs logiques
Construire un arbre de requêtes répondant à la question :
- \(Q_1\) : « Rechercher les matchs joués par l’équipe de France au mois de mars ».
Votre réponse :
Arbre de requêtes : graph419.jsonTables
Opérateurs unaires
Opérateurs binaires
Vue d'ensemble id_match id_locaux id_visiteurs jour horaire locaux_score visiteurs_score stade spectateurs 11 ITA FRA 2017-03-11 14:30:00 Stadio olympico 14 FRA GAL 2017-03-18 15:45:00 Stade de France Une solution possible :
On vérifie (\(\sigma\)) sur la table
matchsque l’équipe qui joue à domicile ou (\(\lor\) : ou logique) l’équipe des visiteurs est bien l’équipe de France et (\(\land\) : et logique) que les matchs sont ceux du mois de mars.- Ecriture en algèbre relationnelle :
- \(Q_1 = \sigma_{[(id\_locaux='FRA' \; \lor \; id\_visiteurs='FRA') \; \land \; month(jour)='03']}(matchs)\).
Attention à la priorité du connecteur logique \(\land\) sur le \(\lor\) (de même que la multiplication l’est sur l’addition)
- La requête :
- \(Q_1 = \sigma_{[id\_locaux='FRA' \; \lor \; id\_visiteurs='FRA' \; \land \; month(jour)='03']}(matchs)\)
donnerai comme résultat … les matchs à domicile de l’équipe de France et (\(\lor\) : ou logique) les matchs que l’équipe de France a joué à l’extérieur au mois de mars.
Arbre de requêtes : sixnations-5.jsonTables
Opérateurs unaires
Opérateurs binaires
Vue d'ensemble id_match id_locaux id_visiteurs jour horaire locaux_score visiteurs_score stade spectateurs 11 ITA FRA 2017-03-11 14:30:00 Stadio olympico 14 FRA GAL 2017-03-18 15:45:00 Stade de France
Sur plusieurs tables
L’objectif de ces exercices est de savoir représenter sous forme d’arbre de requêtes une requête sur une plusieurs tables de la base de données « Au Tournoi des six nations ».
-
Produit cartésien et restriction
Construire un arbre de requêtes répondant à la question :
- \(Q_1\) : « Rechercher les informations sur les matchs et les joueurs des équipes qui ont marqués (essais,transformations, penalites) ».
Le résultat sera trié par l’identifiant des équipes qui jouent à domicile et le nom de joueurs qui ont marqués
Votre réponse :
Arbre de requêtes : graph427.jsonTables
Opérateurs unaires
Opérateurs binaires
Vue d'ensemble id_match id_locaux id_visiteurs jour horaire locaux_score visiteurs_score stade spectateurs id_joueur id_equipe nom prenom position nationalite naissance taille poids essais transformations penalites 1 ECO IRL 2017-02-04 15:25:00 27 22 Murrayfield 67144 2 ECO Hogg Stuart Arriere Ecosse 1992-06-24 1.8 80 2 0 0 1 ECO IRL 2017-02-04 15:25:00 27 22 Murrayfield 67144 3 ECO Dunbar Alex 3/4 centre Ecosse 1990-04-23 1.91 80 1 0 0 1 ECO IRL 2017-02-04 15:25:00 27 22 Murrayfield 67144 1 ECO Laidlaw Greg 1/2 melee Ecosse 1985-10-12 1.75 80 0 3 2 1 ECO IRL 2017-02-04 15:25:00 27 22 Murrayfield 67144 7 IRL Earls Keith 3/4 centre Irlande 1987-10-02 1.8 80 1 0 0 1 ECO IRL 2017-02-04 15:25:00 27 22 Murrayfield 67144 8 IRL Henderson Iain 2eme ligne Irlande du Nord 1992-02-21 1.98 80 1 0 0 1 ECO IRL 2017-02-04 15:25:00 27 22 Murrayfield 67144 9 IRL Jackson Paddy 1/2 ouverture Irlande du Nord 1990-04-05 1.78 80 1 2 1 2 ANG FRA 2017-02-04 17:50:00 19 16 Twickenham 81902 14 ANG Te'o Ben centre Nouvelle-Zélande 1987-01-27 1.89 80 1 0 0 2 ANG FRA 2017-02-04 17:50:00 19 16 Twickenham 81902 15 ANG Farrell Owen 3/4 centre Angleterre 1991-09-24 1.88 80 0 1 3 2 ANG FRA 2017-02-04 17:50:00 19 16 Twickenham 81902 16 ANG Daly Elliot 3/4 centre Angleterre 1992-10-08 1.84 80 0 0 1 2 ANG FRA 2017-02-04 17:50:00 19 16 Twickenham 81902 19 FRA Slimani Rabah Pilier France 1989-10-18 1.78 80 1 0 0 2 ANG FRA 2017-02-04 17:50:00 19 16 Twickenham 81902 20 FRA Lopez Camille 1/2 ouverture France 1989-04-03 1.75 80 0 1 3 3 ITA GAL 2017-02-05 15:00:00 7 33 Stadio olympico 40986 23 ITA Gori Edoardo 1/2 melee Italie 1990-03-05 1.78 80 1 0 0 3 ITA GAL 2017-02-05 15:00:00 7 33 Stadio olympico 40986 24 ITA Canna Carlo 1/2 ouverture Italie 1992-08-25 1.91 80 0 1 0 3 ITA GAL 2017-02-05 15:00:00 7 33 Stadio olympico 40986 26 GAL Davies Jonathan 3/4 centre Angleterre 1988-04-05 1.86 80 1 0 0 3 ITA GAL 2017-02-05 15:00:00 7 33 Stadio olympico 40986 27 GAL Williams Liam 3/4 aile Pays de Galles 1991-04-09 1.88 80 1 0 0 3 ITA GAL 2017-02-05 15:00:00 7 33 Stadio olympico 40986 28 GAL North George 3/4 aile Angleterre 1992-04-13 1.92 80 1 0 0 3 ITA GAL 2017-02-05 15:00:00 7 33 Stadio olympico 40986 29 GAL Halfpenny Leigh Arriere Pays de Galles 1988-12-22 1.78 80 0 3 4 5 GAL ANG 2017-02-11 17:50:00 16 21 Millennium Stadium 74500 27 GAL Williams Liam 3/4 aile Pays de Galles 1991-04-09 1.88 80 1 0 0 5 GAL ANG 2017-02-11 17:50:00 16 21 Millennium Stadium 74500 29 GAL Halfpenny Leigh Arriere Pays de Galles 1988-12-22 1.78 80 0 1 3 5 GAL ANG 2017-02-11 17:50:00 16 21 Millennium Stadium 74500 16 ANG Daly Elliot 3/4 centre Angleterre 1992-10-08 1.84 80 1 0 0 5 GAL ANG 2017-02-11 17:50:00 16 21 Millennium Stadium 74500 17 ANG Youngs Tom Talonneur Angleterre 1987-01-28 1.75 80 1 0 0 5 GAL ANG 2017-02-11 17:50:00 16 21 Millennium Stadium 74500 15 ANG Farrell Owen 3/4 centre Angleterre 1991-09-24 1.88 80 0 1 3 4 ITA IRL 2017-02-11 15:25:00 10 63 Stadio olympico 50197 24 ITA Canna Carlo 1/2 ouverture Italie 1992-08-25 1.91 80 0 1 1 4 ITA IRL 2017-02-11 15:25:00 10 63 Stadio olympico 50197 7 IRL Earls Keith 3/4 centre Irlande 1987-10-02 1.8 80 2 0 0 4 ITA IRL 2017-02-11 15:25:00 10 63 Stadio olympico 50197 10 IRL Stander Christiaan Johan 3eme ligne centre Afrique du Sud 1992-01-05 1.89 80 3 0 0 4 ITA IRL 2017-02-11 15:25:00 10 63 Stadio olympico 50197 11 IRL Gilroy Craig 3/4 aile Irlande du Nord 1991-03-11 1.83 80 3 0 0 4 ITA IRL 2017-02-11 15:25:00 10 63 Stadio olympico 50197 12 IRL Ringrose Garry 3/4 centre Irlande 1995-01-26 1.87 80 1 0 0 4 ITA IRL 2017-02-11 15:25:00 10 63 Stadio olympico 50197 9 IRL Jackson Paddy 1/2 ouverture Irlande du Nord 1990-04-05 1.78 80 0 9 0 6 FRA ECO 2017-02-12 16:00:00 22 16 Stade de France 75283 21 FRA Fickou Gael 3/4 centre France 1994-03-29 1.9 80 1 0 0 6 FRA ECO 2017-02-12 16:00:00 22 16 Stade de France 75283 20 FRA Lopez Camille 1/2 ouverture France 1989-04-03 1.75 80 0 1 5 6 FRA ECO 2017-02-12 16:00:00 22 16 Stade de France 75283 2 ECO Hogg Stuart Arriere Ecosse 1992-06-24 1.8 80 1 0 0 6 FRA ECO 2017-02-12 16:00:00 22 16 Stade de France 75283 5 ECO Swinson Tim 2eme ligne Angleterre 1987-02-17 1.95 80 1 0 0 6 FRA ECO 2017-02-12 16:00:00 22 16 Stade de France 75283 4 ECO Russell Finn 1/2 ouverture Ecosse 1992-09-23 1.83 80 0 0 2 Une solution possible :
On peut faire un produit cartésien (\(\times\)) entre les tables
matchs,selections,joueursen vérifiant que chaque joueur de l’ensemble desjoueursest bien lié auxmatchspar lesselections. On récupère seulement les joueurs qui ont marqués des essais,transformations ou penalités.Ecriture en algèbre relationnelle :
- \(R_1 = \sigma_{[essais > 0 \lor transformations > 0 \lor penalites > 0]}(selections)\)
- \(T_1 = \times(R_1,joueurs)\)
- \(R_2 = \sigma_{[R_1.id\_joueur=joueurs.id\_joueur]}(T_1)\)
- \(T_2 = \times(matchs,R_2)\)
- \(Q_1 = \sigma_{[matchs.id\_match=R_2.id\_match]}(T_2)\)
N.B. : Lors de l’écriture de la requête, attention à la priorité du \(\land\) logique sur le \(\lor\) logique .
On peut aussi faire une jointure naturelle (\(\Join_{[]}\)) puisque les
matchssont liés auxjoueurspar des noms de colonnes communes (id_match,id_joueur) sur la tableselections.Ecriture en algèbre relationnelle :
Arbre de requêtes : sixnations-6.json- \(R_1 = \sigma_{[essais > 0 \lor transformations > 0 \lor penalites > 0]}(selections)\)
- \(NJ_1 = \Join_{[]}(R_1,joueurs)\)
- \(NJ_2 = \Join_{[]}(matchs,NJ_1)\)
Tables
Opérateurs unaires
Opérateurs binaires
Vue d'ensemble id_match id_locaux id_visiteurs jour horaire locaux_score visiteurs_score stade spectateurs id_joueur id_equipe nom prenom position nationalite naissance taille poids essais transformations penalites 1 ECO IRL 2017-02-04 15:25:00 27 22 Murrayfield 67144 2 ECO Hogg Stuart Arriere Ecosse 1992-06-24 1.8 80 2 0 0 1 ECO IRL 2017-02-04 15:25:00 27 22 Murrayfield 67144 3 ECO Dunbar Alex 3/4 centre Ecosse 1990-04-23 1.91 80 1 0 0 1 ECO IRL 2017-02-04 15:25:00 27 22 Murrayfield 67144 1 ECO Laidlaw Greg 1/2 melee Ecosse 1985-10-12 1.75 80 0 3 2 1 ECO IRL 2017-02-04 15:25:00 27 22 Murrayfield 67144 7 IRL Earls Keith 3/4 centre Irlande 1987-10-02 1.8 80 1 0 0 1 ECO IRL 2017-02-04 15:25:00 27 22 Murrayfield 67144 8 IRL Henderson Iain 2eme ligne Irlande du Nord 1992-02-21 1.98 80 1 0 0 1 ECO IRL 2017-02-04 15:25:00 27 22 Murrayfield 67144 9 IRL Jackson Paddy 1/2 ouverture Irlande du Nord 1990-04-05 1.78 80 1 2 1 2 ANG FRA 2017-02-04 17:50:00 19 16 Twickenham 81902 14 ANG Te'o Ben centre Nouvelle-Zélande 1987-01-27 1.89 80 1 0 0 2 ANG FRA 2017-02-04 17:50:00 19 16 Twickenham 81902 15 ANG Farrell Owen 3/4 centre Angleterre 1991-09-24 1.88 80 0 1 3 2 ANG FRA 2017-02-04 17:50:00 19 16 Twickenham 81902 16 ANG Daly Elliot 3/4 centre Angleterre 1992-10-08 1.84 80 0 0 1 2 ANG FRA 2017-02-04 17:50:00 19 16 Twickenham 81902 19 FRA Slimani Rabah Pilier France 1989-10-18 1.78 80 1 0 0 2 ANG FRA 2017-02-04 17:50:00 19 16 Twickenham 81902 20 FRA Lopez Camille 1/2 ouverture France 1989-04-03 1.75 80 0 1 3 3 ITA GAL 2017-02-05 15:00:00 7 33 Stadio olympico 40986 23 ITA Gori Edoardo 1/2 melee Italie 1990-03-05 1.78 80 1 0 0 3 ITA GAL 2017-02-05 15:00:00 7 33 Stadio olympico 40986 24 ITA Canna Carlo 1/2 ouverture Italie 1992-08-25 1.91 80 0 1 0 3 ITA GAL 2017-02-05 15:00:00 7 33 Stadio olympico 40986 26 GAL Davies Jonathan 3/4 centre Angleterre 1988-04-05 1.86 80 1 0 0 3 ITA GAL 2017-02-05 15:00:00 7 33 Stadio olympico 40986 27 GAL Williams Liam 3/4 aile Pays de Galles 1991-04-09 1.88 80 1 0 0 3 ITA GAL 2017-02-05 15:00:00 7 33 Stadio olympico 40986 28 GAL North George 3/4 aile Angleterre 1992-04-13 1.92 80 1 0 0 3 ITA GAL 2017-02-05 15:00:00 7 33 Stadio olympico 40986 29 GAL Halfpenny Leigh Arriere Pays de Galles 1988-12-22 1.78 80 0 3 4 5 GAL ANG 2017-02-11 17:50:00 16 21 Millennium Stadium 74500 27 GAL Williams Liam 3/4 aile Pays de Galles 1991-04-09 1.88 80 1 0 0 5 GAL ANG 2017-02-11 17:50:00 16 21 Millennium Stadium 74500 29 GAL Halfpenny Leigh Arriere Pays de Galles 1988-12-22 1.78 80 0 1 3 5 GAL ANG 2017-02-11 17:50:00 16 21 Millennium Stadium 74500 16 ANG Daly Elliot 3/4 centre Angleterre 1992-10-08 1.84 80 1 0 0 5 GAL ANG 2017-02-11 17:50:00 16 21 Millennium Stadium 74500 17 ANG Youngs Tom Talonneur Angleterre 1987-01-28 1.75 80 1 0 0 5 GAL ANG 2017-02-11 17:50:00 16 21 Millennium Stadium 74500 15 ANG Farrell Owen 3/4 centre Angleterre 1991-09-24 1.88 80 0 1 3 4 ITA IRL 2017-02-11 15:25:00 10 63 Stadio olympico 50197 24 ITA Canna Carlo 1/2 ouverture Italie 1992-08-25 1.91 80 0 1 1 4 ITA IRL 2017-02-11 15:25:00 10 63 Stadio olympico 50197 7 IRL Earls Keith 3/4 centre Irlande 1987-10-02 1.8 80 2 0 0 4 ITA IRL 2017-02-11 15:25:00 10 63 Stadio olympico 50197 10 IRL Stander Christiaan Johan 3eme ligne centre Afrique du Sud 1992-01-05 1.89 80 3 0 0 4 ITA IRL 2017-02-11 15:25:00 10 63 Stadio olympico 50197 11 IRL Gilroy Craig 3/4 aile Irlande du Nord 1991-03-11 1.83 80 3 0 0 4 ITA IRL 2017-02-11 15:25:00 10 63 Stadio olympico 50197 12 IRL Ringrose Garry 3/4 centre Irlande 1995-01-26 1.87 80 1 0 0 4 ITA IRL 2017-02-11 15:25:00 10 63 Stadio olympico 50197 9 IRL Jackson Paddy 1/2 ouverture Irlande du Nord 1990-04-05 1.78 80 0 9 0 6 FRA ECO 2017-02-12 16:00:00 22 16 Stade de France 75283 21 FRA Fickou Gael 3/4 centre France 1994-03-29 1.9 80 1 0 0 6 FRA ECO 2017-02-12 16:00:00 22 16 Stade de France 75283 20 FRA Lopez Camille 1/2 ouverture France 1989-04-03 1.75 80 0 1 5 6 FRA ECO 2017-02-12 16:00:00 22 16 Stade de France 75283 2 ECO Hogg Stuart Arriere Ecosse 1992-06-24 1.8 80 1 0 0 6 FRA ECO 2017-02-12 16:00:00 22 16 Stade de France 75283 5 ECO Swinson Tim 2eme ligne Angleterre 1987-02-17 1.95 80 1 0 0 6 FRA ECO 2017-02-12 16:00:00 22 16 Stade de France 75283 4 ECO Russell Finn 1/2 ouverture Ecosse 1992-09-23 1.83 80 0 0 2 -
Création de vue
- Construire un arbre de requêtes pour créer une vue (\(resultats\)) sur les informations “métier” permettant de visualiser les résultats des matchs du tournoi des six nations :
- les matchs :
(id_locaux,id_visiteurs,jour,locaux_score, visiteurs_score) - les joueurs des équipes :
(nom, id_equipe) - qui ont marqués :
essais,transformations, penalites.
- les matchs :
Votre réponse :
Arbre de requêtes : graph434.jsonTables
Opérateurs unaires
Opérateurs binaires
Vue d'ensemble id_locaux id_visiteurs jour locaux_score visiteurs_score nom id_equipe essais transformations penalites ECO IRL 2017-02-04 27 22 Hogg ECO 2 0 0 ECO IRL 2017-02-04 27 22 Dunbar ECO 1 0 0 ECO IRL 2017-02-04 27 22 Laidlaw ECO 0 3 2 ECO IRL 2017-02-04 27 22 Earls IRL 1 0 0 ECO IRL 2017-02-04 27 22 Henderson IRL 1 0 0 ECO IRL 2017-02-04 27 22 Jackson IRL 1 2 1 ANG FRA 2017-02-04 19 16 Te'o ANG 1 0 0 ANG FRA 2017-02-04 19 16 Farrell ANG 0 1 3 ANG FRA 2017-02-04 19 16 Daly ANG 0 0 1 ANG FRA 2017-02-04 19 16 Slimani FRA 1 0 0 ANG FRA 2017-02-04 19 16 Lopez FRA 0 1 3 ITA GAL 2017-02-05 7 33 Gori ITA 1 0 0 ITA GAL 2017-02-05 7 33 Canna ITA 0 1 0 ITA GAL 2017-02-05 7 33 Davies GAL 1 0 0 ITA GAL 2017-02-05 7 33 Williams GAL 1 0 0 ITA GAL 2017-02-05 7 33 North GAL 1 0 0 ITA GAL 2017-02-05 7 33 Halfpenny GAL 0 3 4 GAL ANG 2017-02-11 16 21 Williams GAL 1 0 0 GAL ANG 2017-02-11 16 21 Halfpenny GAL 0 1 3 GAL ANG 2017-02-11 16 21 Daly ANG 1 0 0 GAL ANG 2017-02-11 16 21 Youngs ANG 1 0 0 GAL ANG 2017-02-11 16 21 Farrell ANG 0 1 3 ITA IRL 2017-02-11 10 63 Canna ITA 0 1 1 ITA IRL 2017-02-11 10 63 Earls IRL 2 0 0 ITA IRL 2017-02-11 10 63 Stander IRL 3 0 0 ITA IRL 2017-02-11 10 63 Gilroy IRL 3 0 0 ITA IRL 2017-02-11 10 63 Ringrose IRL 1 0 0 ITA IRL 2017-02-11 10 63 Jackson IRL 0 9 0 FRA ECO 2017-02-12 22 16 Fickou FRA 1 0 0 FRA ECO 2017-02-12 22 16 Lopez FRA 0 1 5 FRA ECO 2017-02-12 22 16 Hogg ECO 1 0 0 FRA ECO 2017-02-12 22 16 Swinson ECO 1 0 0 FRA ECO 2017-02-12 22 16 Russell ECO 0 0 2 Une solution possible :
Ecriture en algèbre relationnelle de la requête à mettre dans une vue :
Arbre de requêtes : sixnations-7.json- \(NJ_1 = \Join_{[]}(selections,joueurs)\)
- \(NJ_2 = \Join_{[]}(matchs,NJ_1)\)
- \(R_1 = \sigma_{[essais > 0 \lor transformations > 0 \lor penalites > 0]}(NJ_2)\)
- \(Q_1 = \Pi_{(id\_locaux, id\_visiteurs, jour, .., essais,transformations, penalites)}(R_1)\)
Tables
Opérateurs unaires
Opérateurs binaires
Vue d'ensemble id_locaux id_visiteurs jour locaux_score visiteurs_score nom id_equipe essais transformations penalites ECO IRL 2017-02-04 27 22 Hogg ECO 2 0 0 ECO IRL 2017-02-04 27 22 Dunbar ECO 1 0 0 ECO IRL 2017-02-04 27 22 Laidlaw ECO 0 3 2 ECO IRL 2017-02-04 27 22 Earls IRL 1 0 0 ECO IRL 2017-02-04 27 22 Henderson IRL 1 0 0 ECO IRL 2017-02-04 27 22 Jackson IRL 1 2 1 ANG FRA 2017-02-04 19 16 Te'o ANG 1 0 0 ANG FRA 2017-02-04 19 16 Farrell ANG 0 1 3 ANG FRA 2017-02-04 19 16 Daly ANG 0 0 1 ANG FRA 2017-02-04 19 16 Slimani FRA 1 0 0 ANG FRA 2017-02-04 19 16 Lopez FRA 0 1 3 ITA GAL 2017-02-05 7 33 Gori ITA 1 0 0 ITA GAL 2017-02-05 7 33 Canna ITA 0 1 0 ITA GAL 2017-02-05 7 33 Davies GAL 1 0 0 ITA GAL 2017-02-05 7 33 Williams GAL 1 0 0 ITA GAL 2017-02-05 7 33 North GAL 1 0 0 ITA GAL 2017-02-05 7 33 Halfpenny GAL 0 3 4 GAL ANG 2017-02-11 16 21 Williams GAL 1 0 0 GAL ANG 2017-02-11 16 21 Halfpenny GAL 0 1 3 GAL ANG 2017-02-11 16 21 Daly ANG 1 0 0 GAL ANG 2017-02-11 16 21 Youngs ANG 1 0 0 GAL ANG 2017-02-11 16 21 Farrell ANG 0 1 3 ITA IRL 2017-02-11 10 63 Canna ITA 0 1 1 ITA IRL 2017-02-11 10 63 Earls IRL 2 0 0 ITA IRL 2017-02-11 10 63 Stander IRL 3 0 0 ITA IRL 2017-02-11 10 63 Gilroy IRL 3 0 0 ITA IRL 2017-02-11 10 63 Ringrose IRL 1 0 0 ITA IRL 2017-02-11 10 63 Jackson IRL 0 9 0 FRA ECO 2017-02-12 22 16 Fickou FRA 1 0 0 FRA ECO 2017-02-12 22 16 Lopez FRA 0 1 5 FRA ECO 2017-02-12 22 16 Hogg ECO 1 0 0 FRA ECO 2017-02-12 22 16 Swinson ECO 1 0 0 FRA ECO 2017-02-12 22 16 Russell ECO 0 0 2
Opérations ensemblistes
L’objectif de ces exercices est de savoir représenter sous forme d’arbre de requêtes des opérations ensemblistes (\(\cup,\cap,\setminus\)) « Au Tournoi des six nations ».
-
Union
Construire un arbre de requêtes répondant à la question :
- \(Q_1\) : « récupérer les noms du coach et des joueurs du XV de France ».
Votre réponse :
Arbre de requêtes : graph442.jsonTables
Opérateurs unaires
Opérateurs binaires
Vue d'ensemble coach Fickou Lopez Noves Slimani Spedding Une solution possible :
On fait une union (\(\cup\)) entre la requête permettant de récupérer le nom du coach du XV de France dans l’ensemble des
equipeset celle pour rechercher les joueurs de l’équipe du XV de France dans l’ensemble desjoueurs.Ecriture en algèbre relationnelle :
Arbre de requêtes : sixnations-8.json- \(R_1 = \sigma_{[id\_equipe='FRA']}(equipes)\)
- \(P_1 = \Pi_{(coach)}(R_1)\)
- \(R_2 = \sigma_{[id\_equipe='FRA']}(joueur)\)
- \(P_2 = \Pi_{(nom)}(R_2)\)
- \(Q_1 = \cup(P_1,P_2)\)
Tables
Opérateurs unaires
Opérateurs binaires
Vue d'ensemble coach Fickou Lopez Noves Slimani Spedding -
Intersection
Construire un arbre de requêtes répondant à la question :
- \(Q_1\) : « récupérer les joueurs qui ont été selectionnés dans le XV de France pendant le tournoi des 6 nations ».
Votre réponse :
Arbre de requêtes : graph449.jsonTables
Opérateurs unaires
Opérateurs binaires
Vue d'ensemble nom Fickou Lopez Slimani Une solution possible :
On fait une intersection (\(\cap\)) entre la requête permettant de récupérer le nom des joueurs du XV de France dans
joueurset celle permettant de récupérer le nom des joueurs qui ont été sélectionnés (jointure naturelle entre les tablesjoueurs,selections).Ecriture en algèbre relationnelle :
Arbre de requêtes : sixnations-9.json- \(R_1 = \sigma_{[id\_equipe='FRA']}(joueurs)\)
- \(P_1 = \Pi_{(nom)}(R_1)\)
- \(NJ_1 = \Join_{[]}(joueurs,selections)\)
- \(P_2 = \Pi_{(nom)}(NJ_1)\)
- \(Q_1 = \cap(P_1,P_2)\)
Tables
Opérateurs unaires
Opérateurs binaires
Vue d'ensemble nom Fickou Lopez Slimani -
Différence
Construire un arbre de requêtes répondant à la question :
- \(Q_1\) : « récupérer les joueurs qui n’ont pas été selectionnés dans le XV de France pendant le tournoi des 6 nations ».
Votre réponse :
Arbre de requêtes : graph456.jsonTables
Opérateurs unaires
Opérateurs binaires
Vue d'ensemble nom Spedding Une solution possible :
On fait une différence (\(\setminus\)) entre la requête permettant de récupérer le nom des joueurs du XV de France dans
joueurset celle permettant de récupérer le nom des joueurs qui ont été sélectionnés (jointure naturelle entre les tablesjoueurs,selections).Ecriture en algèbre relationnelle :
Arbre de requêtes : sixnations-10.json- \(R_1 = \sigma_{[id\_equipe='FRA']}(joueurs)\)
- \(P_1 = \Pi_{(nom)}(R_1)\)
- \(NJ_1 = \Join_{[]}(joueurs,selections)\)
- \(R_2 = \sigma_{[id\_equipe='FRA']}(NJ_1)\)
- \(P_2 = \Pi_{(nom)}(R_2)\)
- \(Q_1 = \setminus(P_1,P_2)\)
Tables
Opérateurs unaires
Opérateurs binaires
Vue d'ensemble nom Spedding
Division relationnelle
-
GROUP BY, HAVING, COUNT
Construire un arbre de requêtes répondant à la question :
- \(Q_1\) : « Rechercher le nom des joueurs qui ont été sélectionnés pour tous les matchs de leur équipe ».
Votre réponse :
Arbre de requêtes : graph464.jsonTables
Opérateurs unaires
Opérateurs binaires
Vue d'ensemble id_equipe nom FRA Lopez Une solution possible :
On peut mettre en œuvre la division relationnelle pour récupérer les noms des joueurs qui ont été sélectionnées pour tous les matchs de leur équipe en récupérant d’abord les joueurs ayant été sélectionnés :
- \(NJ=\Join_{[]}(joueurs,selections)\)
puis en les regroupant par leur nom et leur identifiant d’équipe :
GROUP BY id_equipe,nom
puis en vérifiant (clause
HAVING) que le nombre de matchs (id_match) des joueurs sélectionnés est égal au nombre total de matchs de leur équipe :COUNT(id_match) = ( SELECT COUNT(id_match) FROM matchs WHERE id_locaux=id_equipe OR id_visiteurs=id_equipe )
Ecriture en algèbre relationnelle :
- \(Q = G_{[COUNT(id\_match) = (SELECT \; COUNT(id\_match) \; FROM \; matchs \; WHERE \; id\_locaux=id\_equipe \; OR \; id\_visiteurs=id\_equipe)]}^{(id_equipe,nom)}(\Join_{[]}(joueurs,selections))\).
Le symbole \(G\) représente l’opérateur de regroupement appliqué sur la table
services:
Arbre de requêtes : sixnations-11-3.json- en exposant : attributs de regroupement (
GROUP BY) - en indice : critère de restriction (clause
HAVING) sur les regroupements.
Tables
Opérateurs unaires
Opérateurs binaires
Vue d'ensemble id_equipe nom FRA Lopez
Fonctions d’agrégat
L’objectif de ces exercices est de savoir appliquer des fonctions d’agrégat sur les recherches « Au tournoi des six nations ».
-
Sur une requête simple
Construire un arbre de requêtes répondant à la question :
- \(Q_1\) : «Quel a été l’affluence moyenne sur tous les matchs du tournoi des six nations ? ».
Votre réponse :
Arbre de requêtes : graph472.jsonTables
Opérateurs unaires
Opérateurs binaires
Vue d'ensemble SUM(spectateurs)/COUNT(*) 65002 Une solution possible :
On pourra appliquer la fonction
AVG()sur la table desmatchs.On pourrait aussi calculer la somme (
SUM()) des affluences divisée par le nombre (COUNT()) de matchs joués.Ecriture en algèbre relationnelle :
Arbre de requêtes : sixnations-13.json- \(R = \sigma_{[spectateurs \; IS \; NOT \; NULL]}(matchs)\)
- \(Q_1 = \Pi_{(SUM(spectateurs)/COUNT(*))}(R)\)
Tables
Opérateurs unaires
Opérateurs binaires
Vue d'ensemble SUM(spectateurs)/COUNT(*) 65002 -
Sur une requête imbriquée (1/2)
Construire un arbre de requêtes répondant à la question :
- \(Q_1\) : « Rechercher le nom des équipes jouant à domicile, le nom de leur stade et le nombre de spectateurs pour les matchs qui ont une affluence supérieure à la moyenne de fréquentation lors des matchs».
Votre réponse :
Arbre de requêtes : graph479.jsonTables
Opérateurs unaires
Opérateurs binaires
Vue d'ensemble id_locaux stade spectateurs FRA Stade de France 75283 Une solution possible :
On pourra calculer dans une requête imbriquée la fréquentation moyenne (
AVG()) de spectateurs lors des matchs puis tester pour chaque match de l’équipe de France si le nombre de spectateurs est supérieur à cette moyenne.Ecriture en algèbre relationnelle :
Arbre de requêtes : sixnations-14.json- \(AVG = \Pi_{AVG(spectateurs)}(matchs)\)
- \(R = \sigma_{[spectateurs > AVG ]}(matchs)\)
- \(Q_1 = \Pi_{(id_locaux, stade, spectateurs)}(R)\)
Tables
Opérateurs unaires
Opérateurs binaires
Vue d'ensemble id_locaux stade spectateurs FRA Stade de France 75283
Groupements
L’objectif de ces exercices est de savoir regrouper le informations et appliquer des fonctions d’agrégat sur ces regroupements « Au tournoi des six nations ».
-
Groupement
Construire un arbre de requêtes répondant à la question :
- \(Q_1\) : « nom des joueurs, leur position et le nombre total de points marqués par les joueurs ».
Votre réponse :
Arbre de requêtes : graph487.jsonTables
Opérateurs unaires
Opérateurs binaires
Vue d'ensemble id_joueur nom position points 1 Laidlaw 1/2 melee 12 2 Hogg Arriere 15 3 Dunbar 3/4 centre 5 4 Russell 1/2 ouverture 6 5 Swinson 2eme ligne 5 7 Earls 3/4 centre 15 8 Henderson 2eme ligne 5 9 Jackson 1/2 ouverture 30 10 Stander 3eme ligne centre 15 11 Gilroy 3/4 aile 15 12 Ringrose 3/4 centre 5 14 Te'o centre 5 15 Farrell 3/4 centre 22 16 Daly 3/4 centre 8 17 Youngs Talonneur 5 19 Slimani Pilier 5 20 Lopez 1/2 ouverture 28 21 Fickou 3/4 centre 5 23 Gori 1/2 melee 5 24 Canna 1/2 ouverture 7 26 Davies 3/4 centre 5 27 Williams 3/4 aile 10 28 North 3/4 aile 5 29 Halfpenny Arriere 29 Une solution possible :
On fait d’abord un regroupement (
GROUP BY) d’information par joueur (id_joueur)sélectionnés.On calcule ensuite la somme des points qu’ils ont marqués (essais:5 points,transformations:2 points, penalites:3 points).
On retient dans le résultat final les
nom,positiondu joueur et la somme des points qu’il a marqué.Ecriture en algèbre relationnelle :
Arbre de requêtes : sixnations-16.jsonTables
Opérateurs unaires
Opérateurs binaires
Vue d'ensemble id_joueur nom position points 1 Laidlaw 1/2 melee 12 2 Hogg Arriere 15 3 Dunbar 3/4 centre 5 4 Russell 1/2 ouverture 6 5 Swinson 2eme ligne 5 7 Earls 3/4 centre 15 8 Henderson 2eme ligne 5 9 Jackson 1/2 ouverture 30 10 Stander 3eme ligne centre 15 11 Gilroy 3/4 aile 15 12 Ringrose 3/4 centre 5 14 Te'o centre 5 15 Farrell 3/4 centre 22 16 Daly 3/4 centre 8 17 Youngs Talonneur 5 19 Slimani Pilier 5 20 Lopez 1/2 ouverture 28 21 Fickou 3/4 centre 5 23 Gori 1/2 melee 5 24 Canna 1/2 ouverture 7 26 Davies 3/4 centre 5 27 Williams 3/4 aile 10 28 North 3/4 aile 5 29 Halfpenny Arriere 29 -
Groupement et restriction
Construire un arbre de requêtes répondant à la question :
- \(Q_1\) : « nom des joueurs, leur position et le nombre total de points marqués pour les joueurs ayant fait au moins 2 matchs ».
Votre réponse :
Arbre de requêtes : graph494.jsonTables
Opérateurs unaires
Opérateurs binaires
Vue d'ensemble id_joueur nom position matchs points 2 Hogg Arriere 2 15 7 Earls 3/4 centre 2 15 9 Jackson 1/2 ouverture 2 30 15 Farrell 3/4 centre 2 22 16 Daly 3/4 centre 2 8 20 Lopez 1/2 ouverture 5 28 24 Canna 1/2 ouverture 2 7 27 Williams 3/4 aile 2 10 29 Halfpenny Arriere 2 29 Une solution possible :
On fait un regroupement (
GROUP BY) par joueur (id_joueur) sélectionné.On ne retient dans ces regroupements que les joueurs ayant été sélectionnées pour plus d’un match de leur équipe.
On récupère dans le résultat final le nom des joueurs, leur poste et les points qu’ils ont marqués (essais:5 points,transformations:2 points,penalites:3 points).
Ecriture en algèbre relationnelle :
Arbre de requêtes : sixnations-17.jsonTables
Opérateurs unaires
Opérateurs binaires
Vue d'ensemble id_joueur nom position matchs points 2 Hogg Arriere 2 15 7 Earls 3/4 centre 2 15 9 Jackson 1/2 ouverture 2 30 15 Farrell 3/4 centre 2 22 16 Daly 3/4 centre 2 8 20 Lopez 1/2 ouverture 5 28 24 Canna 1/2 ouverture 2 7 27 Williams 3/4 aile 2 10 29 Halfpenny Arriere 2 29