Tutoriel de Bases de Données Relationnelles
Corrigé du controle pour les EI 94/95
INSTITUT NATIONAL DES TELECOMMUNICATIONS
CORRIGE CONTROLE DES CONNAISSANCES
Bloc : Bases de données Code : BD22
Durée : 1h30 Date : 19/12/94
Documents autorisés :
ceux distribués en cours
Question 1
En vous aidant des règles de passage d'une modélisation Entité/association à une modélisation relationnelle "à l'envers", proposer un schéma Entité/Association équivalent à ce schéma relationnel. Ce schéma devra comporter la description des entités (avec leurs propriétés), des associations (avec leurs propriétés) ainsi que les cardinalités minimum et maximum des associations binaires.
(R1), (R2) et (R4) se traduisent directement par des ensembles d'entités. Les autres se traduisent par des associations. Comme les clés des relations d'origine sont la concaténation des clés des relations qu'elles associent, ce sont des associations multivaluées dans les deux sens.
Question 2
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.
(Q2.1) Donner le nom des personnes qui ont été invitées lors du repas qui s'est tenu le 13/12/94.
NB : le distinct dans la clause SELECT n'a pas d'intérêt puisque nompersonne est clé.
projection(nompersonne, sélection(date=13/12/94, INVITES))
SELECT nompersonne
FROM INVITES
WHERE date=13/12/94
(Q2.2) Donner le nom des plats préférés (ainsi que leur nombre de calories) de la personne de nom `Dupont'.
NB : le distinct dans la clause SELECT n'a pas d'intérêt puisque nomplat est clé.
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
Question 3
Exprimer les 3 questions suivantes en SQL :
(Q3.1) Donner le nombre de plats préférés de la personne de nom `Martin'.
(Q3.2) Donner pour chaque date de repas le nombre total d'invités.
SELECT COUNT(*)
FROM PREFERENCES
WHERE nompersonne=Martin
(Q3.3) Donner la date du "plus gros" repas (c'est à dire celui dont la somme des calories des différents plats servis est la plus grande) .
SELECT date, COUNT(*)
FROM INVITES
GROUP BY date
SELECT M.date
FROM PLATS P, MENU M
WHERE P.nomplat=M.nomplat
GROUP BY M.date
HAVING SUM(nbcalories) = (SELECT MAX(SUM(nbcalories))
FROM MENU M1, PLATS P1
WHERE M1.nomplat=P1.nomplat
GROUP BY M1.DATE)
ou bien
SELECT M.date
FROM PLATS P, MENU M
WHERE P.nomplat=M.nomplat
GROUP BY M.date
HAVING SUM(nbcalories) >= ALL (SELECT SUM(nbcalories)
FROM MENU M1, PLATS P1
WHERE M1.nomplat=P1.nomplat
GROUP BY M1.DATE)
Question 4
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.
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)
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)