Tutoriel de Bases de Données Relationnelles
Corrigé du contrôle 2 pour les EI 97/98
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(*)Ne pas oublier de mettre CO.nom comme critère de groupement.
FROM CONGRESSISTES CO, PARTICIPE P
WHERE CO.nopers = P.nopers
GROUP BY CO.nopers, CO.nom
(Q3.3) Donner le nom des congressistes qui ont plus de deux invités.
SELECT CO.nomPenser à 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.
FROM CONGRESSISTES CO, INVITES I
WHERE I.nopers = CO.nopers
GROUP BY CO.nopers, CO.nom
HAVING COUNT(*) >= 2
(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)