INSTITUT NATIONAL DES TELECOMMUNICATIONS
CONTROLE DES CONNAISSANCES
Bloc : Bases de données Code : GBD20
Durée : 1h30 Date : 09/06/99
Documents autorisés : Coordonnateur :
ceux distribués en cours 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 : 5 points
Question 2 : 2 points
Question 3 : 3 points (Q3.1 = 1 pt, Q3.2 = 1 pt, Q3.3 = 1 pt)
Question 4 : 6 points (Q4.1 = 2 pts, Q4.2 = 2 pts, Q4.3 = 2 pts)
Question 5 : 4 points (Q5.1 = 1,5 pts, Q5.2 = 2,5 pts)
L’école de management de l’INT nous demande de l’aider à mettre en
place une application permettant de gérer des questionnaires destinés à
tester les connaissances des étudiants. Le schéma relationnel suivant
décrit la base de données QUESTIONNAIRE (les clés sont en gras) :
QUESTIONNAIRE(noquestionnaire, objectif, nomenseignant)
QUESTION(noquestionnaire, noquestion, libellé, réponse)
ELEVES(noélève, nom, prénom)
INSCRIPTION(noélève, noquestionnaire, date)
RESULTAT(noélève, noquestionnaire, noquestion, proposition)
La sémantique des diverses relations est la suivante :
QUESTIONNAIRE(noquestionnaire, objectif,
nomenseignant) : un questionnaire est identifié de manière unique par
un numéro et est décrit par son objectif et par le nom de l’enseignant
qui l’a conçu,
QUESTION(noquestionnaire, noquestion,
libellé, réponse) : un questionnaire se compose de questions. Chaque
question d’un questionnaire est identifiée par un numéro et est décrite
par un libellé et la réponse associée. L’attribut réponse peut
prendre deux valeurs, vrai ou faux. On peut donc voir un questionnaire
comme une liste d’affirmations dont l’étudiant doit dire si elles sont
vraies ou fausses,
ELEVES(noélève, nom, prénom) : un élève est identifié de manière unique par son numéro. Il est décrit par son nom et son prénom,
INSCRIPTION(noélève, noquestionnaire, date)
: une ligne dans cette relation représente le fait qu’un élève
(identifié par son numéro) a répondu à un questionnaire (identifié par
son numéro) à une date donnée,
RESULTAT(noélève, noquestionnaire, noquestion, proposition) : une ligne dans cette relation signifie qu’un élève a proposé une réponse (valeur de l’attribut proposition) à une question (identifiée par son numéro) d’un questionnaire (identifié par son numéro). Le domaine de l’attribut proposition est le même que celui de l’attribut réponse de la relation QUESTION.
Question 1
Donner le schéma Entité-Association correspondant à ce schéma relationnel. Vous préciserez bien les entités, entités faibles (s’il y a lieu), associations, propriétés, clés et cardinalités des associations.
Question 2
Donner la définition complète de la relation
QUESTION dans le langage SQL d'Oracle. Par définition complète, on
entend la définition des attributs et de leur domaine, mais aussi la
définition de toutes les contraintes d'intégrité associées.
CREATE TABLE QUESTION (
NOQUESTIONNAIRE INTEGER,
NOQUESTION INTEGER,
LIBELLE CHAR(256),
REPONSE CHAR(4) CONSTRAINT CREP
(CHECK REPONSE IN (‘VRAI’, ‘FAUX’)
)
ALTER TABLE QUESTION ADDPRIMARY KEY (NOQUESTIONNAIRE, NOQUESTION)
ALTER TABLE QUESTION ADD CONSTRAINT REFQUEST FOREIGN KEY (NOQUESTIONNAIRE) REFERENCES QUESTIONNAIRE(NOQUESTIONNAIRE)
Question 3
Exprimer chacune des 3 questions suivantes en algèbre relationnelle.
(Q3.1) Donner le nom et le numéro des
élèves qui ont proposé comme réponse la valeur " vrai " à la question
numéro 10 du questionnaire de numéro 5.
(Q3.2) Donner la liste des questions
auxquelles les étudiants de nom ‘Defude’ ont répondu. Pour chaque
question, on veut le noquestionnaire, le noquestion et le libellé.
(Q3.3) Donner les questionnaires (noquestionnaire et objectif) auxquels aucun étudiant n’a répondu.
On fait la différence entre l’ensemble des
questionnaires (sous-arbre de gauche) et l’ensemble des questionnaires
qui ont obtenu une réponse (sous-arbre de droite).
Question 4
Exprimer les trois requêtes suivantes en SQL. Attention, la requête 4.3 est en fait une vue relationnelle.
(Q4.1) Donner le nombre de questions du questionnaire de numéro 5.
SELECT COUNT(*)
FROM QUESTION
WHERE NOQUESTIONNAIRE=5
(Q4.2) Donner le numéro et le nom des élèves qui ont répondu à toutes les questions du questionnaire de numéro 10
SELECT E.NOELEVE, E.NOM
FROM RESULTAT R, ELEVE E
WHERE E.NOELEVE=R.NOELEVE AND R.NOQUESTIONNAIRE=10
GROUP BY E.NOELEVE, E.NOM
HAVING COUNT(*) =
(SELECT COUNT(*) FROM QUESTION
WHERE NOQUESTIONNAIRE=10)
Attention à
grouper par noélève et nom puisqu’on les veut tous les deux dans le
SELECT. Ne pas oublier de filtrer le questionnaire numéro 10 dans le
FROM.
(Q4.3)
Donner la définition de la vue BONNEREPONSE(noélève, noquestionnaire,
nb-rép-bonne) qui donne pour chaque élève (désigné par son numéro) et
chaque questionnaire (désigné par son numéro) le nombre de bonnes réponses proposées par l’élève à ce questionnaire.
CREATE VIEW BONNEREPONSE(NOELEVE, NOQUESTIONNAIRE, NBREPBONNE) AS
SELECT R.NOELEVE, R.NOQUESTIONNAIRE, COUNT(*)
FROM RESULTAT R, QUESTION Q
WHERE R.NOQUESTIONNAIRE=Q.NOQUESTIONNAIRE AND
R.NOQUESTION=Q.NOQUESTION
AND R.PROPOSITION=Q.REPONSE
GROUP BY R.NOELEVE, R.NOQUESTIONNAIRE
La condition de
jointure entre RESULTAT et QUESTION porte sur noquestionnaire et
noquestion mais aussi sur l’égalité entre la proposition faite par
l’élève et le corrigé de la question (spécifié par l’attribut réponse).
Question 5
Soit le schéma relationnel M(NE, NQ, NS, D, P, O, M) avec les dépendances fonctionnelles suivantes (il s’agit de la fermeture transitive) :
NQ ->
O; NQ ->
NS; NS ->
M; NS ->
P; NQ ->
P;
NQ ->
M ; NE, NQ ->
D
(Q5.1) Donner la(les) clé(s) de ce schéma relationnel.
De l’étude de la fermeture transitive on voit
qu’aucun attribut tout seul ne dérive tous les autres. Il faut prendre
deux attributs pour dériver tous les autres qui sont NQ et NE (ce sont
d’ailleurs deux attributs qui ne sont jamais en partie droite des DF).
La clé est donc le couple (NQ, NE)
(Q5.2) Donner la forme normale de ce schéma et s’il n’est pas en 3FN proposer une décomposition en 3FN.
Le schéma est en 1FN par définition. Il est en
2FN si aucun attribut non clé ne dépend d’une partie de la clé. Ici les
attributs clés sont NQ et NE et les attributs non clés sont D, O, NS, M
et P.
Ici on a NQ qui donne O qui est un exemple
d’attribut non clé dépendant partiellement d’une clé. Donc le schéma
n’est pas en 2FN donc il est en 1FN.
Une décomposition possible du schéma (mais non l’unique) est la suivante :
R1(NS, M, P) en utilisant la DF : NS donne M, P avec NS clé
R2(NQ, O, NS) en utilisant la DF : NQ donne O, NS avec NQ clé
R3(NQ, NE, D) en utilisant la DF : NQ, NE donne D avec NQ, NE clé.
Posté le 4 septembre 2009