PERSONNES(nompersonne, prénom, age)
PLATS(nomplat, catégorie, nbcalories)
PREFERENCES(nompersonne, nomplat)
REPAS(date, thème)
MENU(date, nomplat)
INVITES(date, nompersonne)
APPRECIE(nompers1, nompers2)
Les clés sont soulignées, les clés étrangères sont en gras.
La sémantique des diverses relations est la suivante :
PERSONNES(nompersonne, prénom, age) : une personne est identifiée de manière unique par son nom et est décrite par son prénom et son age,
PLATS(nomplat, catégorie, nbcalories) : un plat est identifié de manière unique par son nom et il est décrit par sa catégorie (entrée, viande, poisson, ...) et son nombre de calories,
PREFERENCES(nompersonne, nomplat) : la personne de nom nompersonne a pour plat préféré le plat de nom nomplat,
REPAS(date, thème) : à la date indiquée par date, la maîtresse de maison a organisé un repas dont le thème (soirée déguisée, soirée exotique, ...) est précisé parthème,
MENU(date, nomplat) : lors du repas identifié par date, le plat de nom nomplat a éré servi,
INVITES(date, nompersonne) : la personne de nom nompersonne a été invitée à la date indiquée par date,
APPRECIE(nompers1, nompers2): la personne de nom nompers1 apprécie la personne de nom nompers2 (on considère ici que cette relation n'est pas symétrique!).
Questions
Questions 1 - Exprimer chacune des 2 questions suivantes, d'une part en algèbre relationnelle sous la forme d'un arbre algébrique, et d'autre part en SQL.
(Q1.1) Donner le nom des personnes qui ont été invitées lors du repas qui s'est tenu le 13/12/94.
(Q1.2) Donner le nom des plats préférés (ainsi que leur nombre de calories) de la personne de nom `Dupont'.
Questions 2 - Exprimer les 2 questions suivantes en algèbre relationnelle ou en SQL :
(Q4.1) Donner la date des repas où chaque invité a eu au moins un de ses plats préférés.
(Q4.2) Donner le nom des personnes qui ont été invitées à tous les repas.
Question 1
Exprimer chacune des 2 questions suivantes, d'une part en algèbre relationnelle sous la forme d'un arbre algébrique, et d'autre part en SQL.
(Q1.1) Donner le nom des personnes qui ont été invitées lors du repas qui s'est tenu le 13/12/94.
projection(nompersonne, sélection(date=13/12/94, INVITES))
SELECT nompersonne
FROM INVITES
WHERE date=13/12/94
NB : le distinct dans la clause SELECT n'a pas d'intérêt puisque nompersonne est clé.
(Q1.2) Donner le nom des plats préférés (ainsi que leur nombre de calories) de la personne de nom `Dupont'.
projection(nomplat, nbcalories,
jointure(PLATS.nomplat=PREFERENCES.nomplat,
PLATS,
sélection(nompersonne=Dupont(PREFERENCES)))
SELECT P.nomplat, nbcalories
FROM PLATS P, PREFERENCES PR
WHERE P.nomplat = PR.nomplat AND nompersonne=Dupont
NB : le distinct dans la clause SELECT n'a pas d'intérêt puisque nomplat est clé.
Question 2
Exprimer les 2 questions suivantes en algèbre relationnelle ou en SQL :
(Q2.1) Donner la date des repas où chaque invité a eu au moins un de ses plats préférés.
T1 := projection(date, REPAS) /* ensemble des dates des repas */T2 := jointure(MENU.date=INVITES.date, MENU, INVITES)
T3 := jointure(T2.nomplat=PREFERENCES.nomplat, T2, PREFERENCES)
T4 := projection(date, nompersonne, T3) /* date et personne ayant eu un plat préféré */
T6 := INVITES - T4 /* date et personne n'ayant pas eu un plat préféré */
résultat := T1 - projection(date, T6)
SELECT M.date
FROM MENU M, INVITES I
WHERE M.date=I.date AND M.nomplat IN
( SELECT P.nomplat
FROM PREFERENCES P
WHERE P.nompersonne = I.nompersonne)
GROUP BY M.date
HAVING COUNT(*) = (SELECT COUNT(*)
FROM INVITES I1
WHERE I1.date=M.date)
(Q2.2) Donner le nom des personnes qui ont été invitées à tous les repas.
division(INVITES, projection(date, INVITES))
SELECT nompersonne
FROM INVITES
GROUP BY nompersonne
HAVING COUNT(*) = (SELECT COUNT(DISTINCT date)
FROM INVITES)
ou bien
SELECT P.nompersonne
FROM PERSONNE P
WHERE NOT EXISTS
(SELECT *
FROM REPAS R
WHERE NOT EXISTS
( SELECT *
FROM INVITES I
WHERE I.date=R.date AND I.nompersonne = P.nompersonne)