Institut National des Télécommunications
Contrôle des connaissances
|
Bases de données |
Code : BD21 |
|
Date : 05/11/2004 |
Durée : 1h30 |
|
Coordonnateur : C. Lecocq et S. Tata |
Documents autorisés: ceux distribués en cours |
____________________________________________________
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 : 5 points (Q1 = 5
pts)
Question 2 : 4 points (Q2.1 = 2
pts, Q2.2 = 2 pts)
Question 3 : 6 points (Q3.1 = 2
pts, Q3.2 = 2 pts, Q3.3 = 2 pts)
Question 4 : 3 points (Q4.1 = 2
pts, Q4.2 = 2 pts)
Question 5 : 1 point (Q5.1 =
0,5pt, Q5.2 = 0,5pt)
Un établissement public organise régulièrement des consultations
de son personnel (élections de représentant, enquêtes). Afin de faciliter la
gestion de ces consultations, cet établissement souhaite proposer un système de
vote électronique ; il a donc défini le schéma de bases de données suivant
:
Votants (noVotant, nomVotant,
email)
SessionsDeVote (nomSession,
dateSession, heureDébut, heureFin, typeDeVote, question, idAssesseur)
ListeElectorale (noVotant,
nomSession, aVoté)
ChoixDeVote (nomSession,
choix)
Les clés sont soulignées et les clés étrangères sont en
gras.
La sémantique des diverses relations est la suivante :
Votants (noVotant, nomVotant, email) : un votant
est identifié par son numéro « noVotant ».
Il possède un nom et une adresse électronique « email ».
SessionsDeVote (nomSession, dateSession, heureDebut,
heureFin, typeDeVote, question, idAssesseur) : une session de vote
est identifiée par un nom : « nomSession ».
Elle a lieu à une date (« dateSession »)
pendant des heures prédéfinies (entre « heureDébut »
et « heureFin »). Les
consultations sont d’un certain type « typeDeVote »
qui peut prendre les valeurs « Election », « Consultation »,
« Referendum » par exemple. Nous supposerons qu’une consultation ne
contient qu’une et une seule question dont le libellé est mémorisé dans
« question ». Un assesseur (« idAssesseur »,
clé étrangère sur la relation Votants), nommé parmi la liste des votants, est
garant du bon déroulement de cette consultation.
ListeElectorale (noVotant, nomSession, aVoté) :
pour chaque session de vote, une « liste électorale » est définie.
Elle permet de connaître la liste des personnes dans la liste des votants
effectivement autorisées à voter pour cette session de vote. Elle contient une
liste de votants (« noVotant »,
clé étrangère sur la relation Votants) ayant le droit de vote pour une session
de vote (« nomSession », clé
étrangère sur la relation SessionsDeVote). La valeur par défaut de « aVoté » est 0 (le votant a le droit
de voter mais n’a pas encore exercé ce droit). Lorsque le votant a
effectivement voté, « aVoté »
prend la valeur 1. Remarque : la somme des « aVoté » permet d’obtenir le nombre de votants ayant
effectivement voté.
ChoixDeVote (nomSession, choix) : une
session de vote correspond à une seule question posée. Les réponses à cette
question sont prédéfinies et en nombre limité (comme dans un QCM par exemple).
Un choix possible à une question de vote est identifié par le libellé de ce
choix (« choix ») et la
session de vote de la question à laquelle il répond (« nomSession », clé étrangère sur la
relation SessionsDeVote).
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) Quelles sont les sessions
de vote (nom de session et date de session de vote) ayant eu lieu en 2003 ?
Remarque : vous supposerez
que les opérateurs de comparaison classiques sont applicables aux types date.
Il est donc possible d’écrire :
dateSession > ‘01/01/2003’
SELECT nomSession, dateSession
FROM SessionsDeVote
WHERE dateSession
>= ‘01/01/2003’ AND dateSession <= ‘31/12/2003’
(Q2.2) Quelles sont les sessions
de vote (nom de session) de type « Election » pour lesquelles le
votant numéro 1234 a voté en 2003 ?
SELECT nomSession
FROM SessionsDeVote
S, ListeElectorale L
WHERE S.nomSession =
L.nomSession
AND typeDeVote =
‘Election’ AND aVote = 1
Question 3
Exprimer les 3 questions suivantes en SQL :
(Q3.1) Quel est le taux de
participation (nombre de votes / nombre de votants) aux consultations de type
« Election » ?
Rappel : les valeurs possibles
de « aVoté » sont 0 (votant
n’ayant pas voté) et 1 (votant ayant voté).
SELECT SUM(aVote)/COUNT(*)
// == AVG(aVote)
FROM ListeElectorale
WHERE typeDeVote =’Election’
(Q3.2) Donner pour chaque session
de vote le nom de la session de vote, la date de cette session, le nombre de
votants, le taux de participation (moyenne des valeurs de « aVoté ») uniquement si le nombre de
votants est supérieur à 500.
SELECT nomSession, dateSession,
COUNT(*), AVG(aVote)
FROM ListeElectorale
GROUP BY nomSession,
dateSession
HAVING COUNT(*)
>= 500
(Q3.3) Quelle est la
consultation (nom de Session) ayant recueilli le nombre de votes maximum ?
SELECT nomSession
FROM ListeElectorale
GROUP BY nomSession
HAVING SUM(aVote)
>= Select Max (SUM(aVote))
FROM
ListeElectorale
GROUP
BY nomSession
Question 4
Exprimer les 2 questions suivantes en algèbre relationnelle ou
en SQL :
(Q4.1) Quels sont les
votants (numéro de votant, nom du
votant) qui ont voté pour toutes les sessions de vote ?
SELECT noVotant, nomVotant
FROM ListeElectorale
L, Votants V
WHERE L.noVotant = V.noVotant
GROUP BY noVotant, nomVotant
HAVING SUM(aVote) = SELECT
COUNT(*)
FROM
SessionsDeVote
(Q4.2) Quels sont les sessions
de vote (nom de session) qui ne possèdent ni le choix de réponse
« oui », ni le choix de réponse « non » à leur
question ?
SELECT nomSession
FROM SessionsDeVote
MINUS
(
SELECT nomSession
FROM ChoixDeVote
WHERE choix = ‘oui’
UNION
SELECT nomSession
FROM ChoixDeVote
WHERE choix = ‘non’
)
Question 5
Répondez au QCM suivant. Entourez les bonnes réponses.
Plusieurs propositions peuvent être vraies. Toutes les questions s’inscrivent
dans le cadre du TP3-4 (partie application web).
Q5.1) Un serveur Web :
|
1) |
peut traduire des requêtes HTTP |
|
2) |
peut traduire le résultat d’une requête SQL en document |
|
3) |
est un processus qui tourne sur une plate-forme et qui |
Réponse correcte : 3
Q5.2) Un client Web :
|
1) |
envoie des requêtes SQL |
|
2) |
envoie des requêtes HTTP |
|
3) |
interprète graphiquement des documents HTML |
|
4) |
interagit directement avec un |
Réponses correctes : 2 et
3
Posté le 4 septembre 2009