INSTITUT NATIONAL DES TELECOMMUNICATIONS
CONTROLE DES CONNAISSANCES
Bloc : Bases de données Code : BD21
Durée : 1h30 Date : 16/12/97
Coordonnateur :
Mr Defude
____________________________________________________
Avertissements
1/ Lisez attentivement le sujet.
2/ Les questions sont indépendantes les unes des autres
3/ Essayez d'être clair et précis dans vos réponses.
4/ Soignez la présentation, dans la mesure du possible.
5/ Barême indicatif
Question 1 : 4 points
Question 2 : 4 points (Q2.1 = 2 pt, Q2.1 = 2 pt)
Question 3 : 8 points (Q3.1 = 2 pts, Q3.2 = 2 pts, Q3.3 = 2 pts, Q3.4 = 2 pts)
Question 4 : 4 points (Q4.1 = 2 pts, Q4.2 = 2 pts)
Une société
d'organisation de congrès vous demande d'informatiser la gestion des
inscriptions des congressistes à un congrès. Ce système doit s'appuyer
sur une base de données relationnelles décrite par le schéma suivant :
CONGRESSISTES(nopers, nom, prénom, adresse, nocat)
CATEGORIES(nocatégorie, prixc)
EVENEMENTS(nomeven, date, prixe)
INVITES(nopers, nominvité, adresse)
PARTICIPE(nopers, nomeven, nbpersonnes)
Les clés sont soulignées et les clés étrangères sont mises en gras.
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 entités faibles
(s'il y a lieu), des associations (avec leurs propriétés) ainsi que les
cardinalités minimum et maximum des associations binaires.
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 prix à payer pour son inscription au congrès par le congressiste de numéro 10.
SELECT C.prixc
FROM CATEGORIES C, CONGRESSISTES CO
WHERE C.nocategorie = CO.nocat AND CO.nopers=10
(Q2.2) Donner le nom des événements se déroulant le 10-01-98 auquels s'est inscrit le congressiste de numéro 20.
SELECT E.nomeven
FROM EVENEMENTS E, PARTICIPE P
WHERE E.nomeven = P.nomeven AND P.nopers = 20 AND E.date = 100198
Question 3
Exprimer les 4 questions suivantes en SQL :
(Q3.1) Donner le prix à payer par le
congressiste de numéro 15 pour sa participation aux événements (le prix
à payer comprend les participations de ses invités aux événements).
SELECT SUM(E.prixe * P.mbpersonnes)
FROM EVENEMENTS E, PARTICIPE P
WHERE E.nomeven = P.nomeven AND P.nopers = 15
(Q3.2) Donner pour chaque congressiste (nopers et nom) le nombre total d'événements auquels il est inscrit.
SELECT CO.nopers, CO.nom, count(*)
FROM CONGRESSISTES CO, PARTICIPE P
WHERE CO.nopers = P.nopers
GROUP BY CO.nopers, CO.nom
Ne pas oublier de mettre CO.nom comme critère de groupement.
(Q3.3) Donner le nom des congressistes qui ont plus de deux invités.
SELECT CO.nom
FROM CONGRESSISTES CO, INVITES I
WHERE I.nopers = CO.nopers
GROUP BY CO.nopers, CO.nom
HAVING COUNT(*) >= 2
Penser à grouper par nopers puisque c'est ce qui identifie un
congressiste. On peut avoir plusieurs fois le même nom en réponse s'il
y a des homonymes.
(Q3.4) Donner le nom de l'événement qui a le plus d'inscrits (congressistes et invités).
SELECT P.nomeven
FROM PARTICIPE P
GROUP BY P.nomeven
HAVING SUM(nbpersonnes) = (SELECT MAX (SUM(nbpersonnes)) FROM PARTICIPE P1
GROUP BY P1.nomeven)
Question 4
Exprimer les 2 questions suivantes en algèbre relationnelle ou en SQL :
(Q4.1) Donner le numéro des congressistes qui n'ont aucun invité.
SELECT CO.nopers
FROM CONGRESSISTES CO
MINUS
SELECT I.nopers
FROM INVITES I
(Q4.2) Donner le numéro des congressistes qui participent à tous les événements.
SELECT P.nopers
FROM PARTICIPE P
GROUP BY P.nopers
HAVING COUNT(*) = (SELECT COUNT(*) FROM EVENEMENTS)
Posté le 4 septembre 2009