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(*)

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)