Tutoriel de Bases de Données Relationnelles

Corrigé du contrôle pour les EI 04/05

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 en requêtes SQL et vice-versa
2)	peut traduire le résultat d’une requête SQL en document HTML
3)	est un processus qui tourne sur une plate-forme et qui attend des requêtes HTTP

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 SGBD

Réponses correctes : 2 et 3