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.
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é.
(Q2.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 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'.
SELECT COUNT(*)
FROM PREFERENCES
WHERE nompersonne=Martin
(Q3.2) Donner pour chaque date de repas le nombre total
d'invités.
SELECT date, COUNT(*)
FROM INVITES
GROUP BY date
(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 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.
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)
(Q4.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)
Posté le 4 septembre 2009