Tutoriel de Bases de Données Relationnelles

Tutoriel de Bases de Données Relationnelles

Accueil  > Supports pédagogiques > Exercices > Algèbre relationnelle > diner parfait

Un dîner parfait

Un diner parfait !

Sujet

Un hôte de maison veut développer un système informatique lui permettant de mieux planifier ses invitations à dîner en fonction des goûts culinaires des invités ainsi que de leurs amitiés (et inimitiées bien sûr!). Ce système doit s'appuyer sur une base de données relationnelles décrite par le schéma suivant :

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ébriqueet 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.

Corrigé

Question 1

Exprimer chacune des 2 questions suivantes, d'une part en algèbre relationnelle sous la forme d'un arbre algébriqueet 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)

[fil RSS du site]
Dernière mise à jour : 21/09/2014