Corrigé TP SQL 2 et 3
Corrigé TP SQL N° 2 et 3
R11)) Quelles sont les personnes, les cafés qu'ils fréquentent, et les boissons servies par ces cafés.
R
Deux tables suffisent: frequente et sert (colonne commune cafe)
select [Link], [Link], [Link]
from frequente f, sert s
where [Link]=[Link]
order by 1,2;
+----------+----------+----------+
| personne | cafe | boisson |
+----------+----------+----------+
| ali | atlas | 7up |
| ali | atlas | oulmes |
| ali | rif | coca |
| amine | commerce | coca |
| amine | commerce | oulmes |
| amine | commerce | 7up |
| amine | commerce | orangina |
| aziz | atlas | 7up |
| aziz | atlas | oulmes |
| aziz | commerce | orangina |
| aziz | commerce | coca |
| aziz | commerce | oulmes |
| aziz | commerce | 7up |
| aziz | rif | coca |
| said | atlas | 7up |
| said | atlas | oulmes |
+----------+----------+----------+
Order by pour la présentation.
R22)) Quelles sont les personnes qui fréquentent des cafés qui servent des boissons qu'ils aiment.
R
Il faut trois tables: jointure sur toutes les 3 colonnes communes
select [Link], [Link], [Link]
from frequente f, sert s, aime a
where [Link] = [Link]
and [Link] = [Link]
and [Link] = [Link]
order by 1,2
+----------+----------+---------+
| personne | cafe | boisson |
+----------+----------+---------+
| ali | atlas | 7up |
| ali | atlas | oulmes |
| ali | rif | coca |
| amine | commerce | oulmes |
| amine | commerce | coca |
| aziz | atlas | oulmes |
| aziz | atlas | 7up |
| aziz | commerce | 7up |
| aziz | commerce | oulmes |
+----------+----------+---------+
9 rows in set (0.00 sec)
Comparer par rapport à 1)
Amine fréquente commerce, mais n'aime que oulmes et coca.
Said ne fréquente pas de café qui sert une boisson qu'il aime.
Ali a le même nombre de lignes dans les deux résultats. Il ne fréquente que les cafés qui servent des boissons qu'il aime.
Question subsidiaire:
R22'')) Quelles sont les personnes qui fréquentent des cafés (au moins un) qui servent des boissons qu'ils n'aiment pas.
R
Idem que ci-dessus, mais avec la relation aimePas.
R
R22'' -- aa)) Créer la relation aimePas.
Il faut faire les hypothèses :
1) dans la table aime toutes les personnes sont listées et toutes les boissons aussi,
2) si une personne p n'est pas dans la table aime avec une boisson b, alors p n'aime pas b. (Hypothèse du monde clos, tout ce qui n'est
pas énoncé est faux)
Expression
DIFF ( CART (PROJECT (aime, personne), PROJECT (aime, boisson)), aime)
algébrique :
On créera une vue pour cela :
create view aimePas as
select distinct [Link], [Link]
[Link] 1 of 7
Corrigé TP SQL 2 et 3
from aime a1, aime a2
where not exists (select * from aime a3
where [Link]=[Link]
and [Link]=[Link] )
select * from aimePas order by 1;
+----------+----------+
| personne | boisson |
+----------+----------+
| ali | orangina |
| amine | orangina |
| amine | 7up |
| aziz | coca |
| aziz | orangina |
| said | 7up |
| said | oulmes |
+----------+----------+
R22'' -- bb)) Résultat : on crée une relation X qui répond à la requête « Quelles sont les personnes qui fréquentent des cafés (au moins un) qui
R
servent des boissons qu'ils n'aiment pas ».
create view X as select [Link], [Link], [Link]
from frequente f, sert s, aimePas a
where [Link] = [Link]
and [Link] = [Link]
and [Link] = [Link];
select * from X;
+----------+----------+----------+
| personne | cafe | boisson |
+----------+----------+----------+
| amine | commerce | orangina |
| amine | commerce | 7up |
| aziz | commerce | coca |
| aziz | rif | coca |
| aziz | commerce | orangina |
| said | atlas | 7up |
| said | atlas | oulmes |
+----------+----------+----------+
Où on voit que, par rapport à la requête RR22, Ali n'appartient pas à X (ne fréquente que les cafés qui servent des boissons qu'il aime :-)), et
Said appartient à X mais pas à RR22 (ne fréquente que les cafés qui servent des boissons qu'il n'aime pas :-( )
R33)) Quels sont les café servant toutes les boissons.
R
Ici, c'est une simple division relationnelle.
Expression algébrique: DIV ( sert , PROJECT (sert, boisson) )
select distinct cafe
from sert x
where not exists (select * from sert y
where not exists (select * from sert z
where [Link] = [Link]
and [Link] = [Link]));
+----------+
| cafe |
+----------+
| commerce |
+----------+
1 row in set (0.00 sec)
R44)) Quelles sont les personnes qui ne fréquentent que les cafés qui servent des boissons qu'ils aiment (Ali).
R
Soit:
X = Personnes fréquentant un café servant au moins une boisson qu'elles n'aiment pas (R2') et
Y = Personnes fréquentant un café servant au moins une boisson qu'elles aiment (R2)
réponse = Y - X (opérateur de différence, NOT EXiSTS)
select distinct personne
from Y
where not exists (select *
from X
where [Link] = [Link])
+----------+
| personne |
+----------+
| ali |
+----------+
R55)) Quelles sont les personnes qui ne fréquentent que les cafés qui servent des boissons qu'ils n'aiment pas (Said).
R
réponse = X - Y
select distinct personne
from X
where not exists (select *
from Y
where [Link] = [Link])
+----------+
[Link] 2 of 7
Corrigé TP SQL 2 et 3
| personne |
+----------+
| said |
+----------+
/fin
Corrigé TP SQL N° 3
R
Reellaattiioonn parent iinniittiiaallee
parent
+--------+--------+
| parent | enfant |
+--------+--------+
| Ali | Fatima |
| Ali | Kacem |
| Fatima | Amina |
| Fatima | Aziz |
| Kacem | Aziza |
| Aziz | Saida |
| Saida | Farid |
+--------+--------+
L
Laa rreellaattiioonn ffrreerree
create view frere (f1, f2) as
select [Link], [Link]
from parent a, parent b
where [Link] = [Link]
and [Link] > [Link];
select * from frere;
+-------+--------+
| f1 | f2 |
+-------+--------+
| Kacem | Fatima |
| Aziz | Amina |
+-------+--------+
R
Reellaattiioonn ccoouussiinn
create view cousin (c1, c2) as
select [Link], [Link]
from parent a, parent b, frere f
where [Link] = f.f2
and [Link] = f.f1;
select * from cousin;
+-------+-------+
| c1 | c2 |
+-------+-------+
| Amina | Aziza |
| Aziz | Aziza |
+-------+-------+
R
Reellaattiioonn oonnccllee ((oouu ttaannttee))
create view oncle (o, n) as
select [Link], [Link]
from parent a, parent b, frere f
where ([Link] = f.f2
and [Link] = f.f1)
or ([Link] = f.f1
and [Link] = f.f2);
select distinct * from oncle;
+--------+-------+
| o | n |
+--------+-------+
| Kacem | Amina |
| Kacem | Aziz |
| Fatima | Aziza |
+--------+-------+
Pour avoir les oncles rajouter dans la clause where "and [Link] in (select * from male);"
create view oncle (o, n) as
select [Link], [Link]
from parent a, parent b, frere f
where ([Link] = f.f2
and [Link] = f.f1)
or ([Link] = f.f1
and [Link] = f.f2)
[Link] 3 of 7
Corrigé TP SQL 2 et 3
and [Link] in (select * from male);
mysql> select * from oncle;
+-------+-------+
| o | n |
+-------+-------+
| Kacem | Amina |
| Kacem | Aziz |
+-------+-------+
2 rows in set (0.00 sec)
R
Reellaattiioonn G
Grraanndd ppaarreenntt GP
create view gp (gp, pf) as
select [Link], [Link]
from parent a, parent b
where [Link] = [Link];
select * from gp;
+--------+-------+
| gp | pf |
+--------+-------+
| Ali | Amina |
| Ali | Aziz |
| Ali | Aziza |
| Fatima | Saida |
| Aziz | Farid |
+--------+-------+
R
Reellaattiioonn aannccêêttrree
Rappel: Relation ancêtre définie récursivement par :
ancêtre (x, y) = parent (x, y) ou z , ancêtre (x, z) et parent (z, y)
Ici, nous avons affaire à une relation obtenue par fermeture transitive. un ancêtre est un parent ou un grand-parent ou un arrière grand-
parent à un niveau quelconque. C'est ce niveau quelconque qui est indéfini à l'avance.
M
Méétthhooddee ddee ccaallccuullee
Le calcul consiste donc à progresser, chercher le grand-parent, ensuite le parent du grand-parent, le parent de ce dernier, etc. Jusqu'à ne
plus rien obtenir quand on a atteint le dernier ancêtre connu qui n'a donc pas de parent (connu) dans la base.
Reprenons la table parent ci-dessus et cherchons un à un les ancêtres, en commençant par le parent.
Cela donne :
Ancêtre niveau 0, c'est à dire parent
CREATE VIEW anc0 (anc, des) AS SELECT * FROM parent;
+--------+--------+
| anc | des |
+--------+--------+
| Ali | Fatima |
| Ali | Kacem |
| Aziz | Saida |
| Fatima | Amina |
| Fatima | Aziz |
| Kacem | Aziza |
| Saida | Farid |
+--------+--------+
7 rows in set (0.00 sec)
Ancêtre niveau 1, grand-parent ( ou parent de anc0)
CREATE VIEW anc1 (anc, des) AS
SELECT [Link], [Link]
FROM parent g, anc0 p
WHERE [Link] = [Link];
+--------+-------+
| anc | des |
+--------+-------+
| Ali | Amina |
| Ali | Aziz |
| Ali | Aziza |
| Aziz | Farid |
| Fatima | Saida |
+--------+-------+
5 rows in set (0.00 sec)
Ancêtre niveau 2, grand-grand-parent ( ou parent de anc1)
CREATE VIEW anc2 (anc, des) AS
SELECT [Link], [Link]
FROM parent g, anc1 p
WHERE [Link] = [Link];
+--------+-------+
| anc | des |
[Link] 4 of 7
Corrigé TP SQL 2 et 3
+--------+-------+
| Ali | Saida |
| Fatima | Farid |
+--------+-------+
2 rows in set (0.00 sec)
Ancêtre niveau 3, parent de anc2
CREATE VIEW anc3 (anc, des) AS
SELECT [Link], [Link]
FROM parent g, anc2 p
WHERE [Link] = [Link];
+-----+-------+
| anc | des |
+-----+-------+
| Ali | Farid |
+-----+-------+
1 row in set (0.00 sec)
Ancêtre niveau 4 parent de anc3
CREATE VIEW anc4 (anc, des) AS
SELECT [Link], [Link]
FROM parent g, anc3 p
WHERE [Link] = [Link];
Empty set (0.00 sec)
Pas d'ancêtre niveau 4.
La liste finale de tous les ancêtres est l'union de anc0 à anc4
SELECT * FROM anc0
UNION SELECT * FROM anc1
UNION SELECT * FROM anc2
UNION SELECT * FROM anc3
UNION SELECT * FROM anc4
order by 1;
+--------+--------+
| anc | des |
+--------+--------+
| Ali | Fatima |
| Ali | Saida |
| Ali | Kacem |
| Ali | Amina |
| Ali | Aziz |
| Ali | Farid |
| Ali | Aziza |
| Aziz | Saida |
| Aziz | Farid |
| Fatima | Farid |
| Fatima | Amina |
| Fatima | Aziz |
| Fatima | Saida |
| Kacem | Aziza |
| Saida | Farid |
+--------+--------+
15 rows in set (0.00 sec)
En fait, c'est l'union ensembliste des relations aannccêêttrreei où :
ancêtre1 de niveau 1 (le parent du parent)
ancêtre2 de niveau 2 (le parent du ancêtre1)
...
ancêtren de niveau n (le parent du ancêtren-1)
jusqu'à ce qu'on obtienne une relation vide, i.e. on a atteint le dernier ancêtre connu.
F
Foorrm
mee A
Allggéébbrriiqquuee
Soit aannccêêttrreei (anc, desc) la table mettant en relation an ancêtre avec un descendant.
aannccêêttrree00 = parent
aannccêêttrree11 = project ( join (parent a, parent b, [Link] = [Link]), [Link], [Link])
aannccêêttrree22 = project ( join (aannccêêttrree11 a, parent b, [Link] = [Link]), [Link], [Link])
...
aannccêêttrreenn = Project ( join (aannccêêttrreenn--11 a, parent b, [Link] = [Link]), [Link], [Link])
Résultat = i=0..n aannccêêttrreenn .
P
Prrooggrraam
mmmaattiioonn aavveecc S
SQQL
L
Il faut faire un programme (e.g. PLSQL, ESQL ou php-MySQL) pour calculer cette relation, par récursion ou boucle while.
---> anc_i parent
| \ /
| \ /
| \ /
| JOIN
[Link] 5 of 7
Corrigé TP SQL 2 et 3
(U) |
| v
| anc_i+1
| |
<---------------
On va initialement créer la table finale ancetre (ancetre, descendant), qui sera alimentée au fur et à mesure par les tuples de anci
calculés à chaque itération. Représenté par (U) sur la figure.
Voici un programme PHP-MySQL qui calcule cette table ancêtre. Dans ce listing, last_anc et new_anc sont respectivement les deux table
"variables de contrôle" anc_i et anc_i+1 de la figure ci-dessus.
<?php
//
// On se connecte au serveur
//
$link = mysql_connect('localhost', 'Najib');
//
// On choisit la base
//
$c = mysql_select_db("Famille",$link);
//
// On Traite
//
mysql_query("drop table if exists ancetres;", $link);
mysql_query("drop table if exists last_anc;", $link);
mysql_query("drop view if exists new_anc;", $link);
/********* table ancetres initialement vide ************/
mysql_query("create table ancetres (ancetre text(10), descendant text(10));", $link);
/**************** initialisation: last_anc := parent **********************/
mysql_query(" create table last_anc (anc text(10), des text(10));", $link);
mysql_query(" insert into last_anc select * from parent;",$link);
/* A cumuler sur ancetre */
mysql_query("insert into ancetres select * from last_anc; ",$link);
/**************** boucle **********************/
/***** On calcule new_anc *****/
while (1) {
mysql_query("drop table if exists new_anc; ", $link);
mysql_query("create table new_anc (anc text(10), des text(10));", $link);
mysql_query("insert into new_anc select [Link], [Link] from parent g, last_anc p where [Link] = [Link];", $link);
/***** on teste si le résultat new_anc n'est pas vide **********/
$result = mysql_query("SELECT count(*) as cpt FROM new_anc", $link);
$n = mysql_result($result, 0, cpt); // $n contient le nombre de tuples trouvés
if ($n > 0) {
/** il y a résultat **/
/*** On cumule sur ancetres ***/
mysql_query("insert into ancetres select * from new_anc;", $link);
/*** last_anc := new_anc, et on continue ***/
mysql_query("delete from last_anc;", $link);
mysql_query("insert into last_anc select * from new_anc;", $link);
} else {
break; /* resultat: ancetres */
}
}
/********* fin boucle **********/
/******* affichage *******/
afficherRelation ("ancetres", $link);
/******** Fonction afficherRelation ********/
function afficherRelation($table, $link)
{ /*** affiche une table new_anc (anc, des) ***/
//
// On interroge
//
echo "<b>$table</b>";
$result = mysql_query("SELECT * FROM $table order by 1",$link);
//
// On presente le resultat en table
// Usage de fetch_row et de row[n° colonne]
//
printf( " <table border=1 cellspacing=0 cellpadding=3>");
printf("<tr>\n");
printf( "<th>anc</th><th>des</th>");
printf( "</tr>\n");
while ($row = mysql_fetch_row($result)) {
printf("<tr><td>%s</td><td>%s</td></tr><br />\n",
$row[0], $row[1]);
}
printf( "</table>");
return;
[Link] 6 of 7
Corrigé TP SQL 2 et 3
}
?>
Dans ce programme, le résultat est affiché dans une page HTML. Seule la partie <table>...</table> est considérée.
/That's all folks.
[Link] 7 of 7