Exercice : Questionnaires [Imbrication en UML et en relationnel]

Questionnaires

[45 min]

On souhaite construire une base de données de questions pour réaliser une application permettant à des utilisateurs de tester leurs connaissances, sur divers sujets.

On renseigne pour chaque question un niveau de difficulté indiqué par un entier entre 1 et 5, ainsi que la date de création de la question. Chaque question est dotée d'un numéro unique pour toute la base. De plus chaque question est unique, il n'existe pas deux questions avec le même intitulé : par exemple il n'est pas possible d'avoir deux fois la question : « quelle est la capitale de la France ».

Il existe deux types de questions :

Reconnaître : l'utilisateur doit reconnaître un élément sur une image. Il faut donc stocker dans la base de données un chemin vers l'image à afficher et la réponse attendue.
Exemple d'intitulé : « Sur le diagramme UML ci-joint, donnez les noms des éléments qui sont des classes associations » (titre du thème : conception, intitulé de la catégorie : classes associations, difficulté 2).
Vrai/Faux : l'utilisateur doit dire si l'intitulé de la question est vrai ou faux. On doit enregistrer la réponse attendue (vrai ou faux).
Exemple d'intitulé : « Dans un schéma relationnel, on fait apparaître les attributs dérivés » (titre du thème : schéma relationnel, intitulé de la catégorie : traduction UML/relationnel, difficulté 2).

Un thème possède un titre et un domaine. Un thème est composé de plusieurs catégories (on utilisera une composition). Chaque catégorie a un intitulé. Il existe plusieurs catégories avec un même intitulé, mais jamais à l'intérieur d'un même thème.

Toutes les questions relèvent d'une unique catégorie.

Chaque fois qu'un utilisateur répond à une question, on enregistre le résultat (bonne ou mauvaise réponse).

On souhaite également conserver des informations sur les utilisateurs : son login (qui est une clé), son nom et son prénom. Par ailleurs, on veut calculer le score d'un utilisateur : chaque question à laquelle il a répondu correctement rapporte autant de points que le niveau de difficulté.

Question

Proposez un modèle conceptuel en UML répondant à ces besoins.

Vous veillerez à expliciter les hypothèses faites et à faire apparaître les clés candidates et les autres contraintes sur le schéma.

Solution

Remarque :

On peut accepter l'hypothèse que Thème.titre est une clé.

Question

Proposez un modèle relationnel à partir du schéma UML. Vous justifierez les choix non triviaux. Vous proposerez un modèle en 3NF. Vous exprimerez les contraintes issues de la modélisation UML.

Solution

Remarque :

On fait l'hypothèse que Thème.titre est une clé.

Solution par classe mère

Thème (#titre:string, domaine:string)
Contraintes:domaine NOT NULL
Catégorie (#thème=>Thèmes.titre, #intitule:string)
Question (#numéro:integer, intitulé:string, difficulté:integer, date_creation:date, image:string, réponse:string, intitulé_correct:bool, type:{"R", "VF"}, fk_cat_thème=>Catégorie.thème, fk_cat_intitulé=>Catégorie.intitule)
Contraintes : intitulé key, difficulté >= 1 AND difficulté <= 5 ; date_creation NOT NULL
Contraintes liées à l'héritage : type="R" XOR (image IS NULL AND réponse IS NULL) ; (type="VF" XOR intitulé_correct IS NULL)
Personne (#login:string, nom:string, prenom:string)
Contraintes : nom, prénom NOT NULL
Réponse(#question=>Question.numéro, #personne=>Personne.login, reussite:bool)

Thème (#titre:string, domaine:string)
Contraintes:domaine NOT NULL

Catégorie (#thème=>Thèmes.titre, #intitule:string)

Question (#numéro:integer, intitulé:string, difficulté:integer, date_creation:date, image:string, réponse:string, intitulé_correct:bool, type:{"R", "VF"}, fk_cat_thème=>Catégorie.thème, fk_cat_intitulé=>Catégorie.intitule)
Contraintes : intitulé key, difficulté >= 1 AND difficulté <= 5 ; date_creation NOT NULL
Contraintes liées à l'héritage : type="R" XOR (image IS NULL AND réponse IS NULL) ; (type="VF" XOR intitulé_correct IS NULL)

Personne (#login:string, nom:string, prenom:string)
Contraintes : nom, prénom NOT NULL

Réponse(#question=>Question.numéro, #personne=>Personne.login, reussite:bool)

Les tables Thème, Catégorie, Personne et Réponse sont en BCNF car les seules DFE proviennent de clés. Elles sont donc en 3NF.

La table Question n'est pas en 3NF car l'attribut "type" peut être déterminé par d'autres attributs :

image -> type
réponse -> type
intitulé_correct -> type

Pour arriver à une 3NF, l'attribut type peut être supprimé car il peut se déduire à partir d'autres attributs. On peut récupérer le type à travers une vue.

Question (#numéro:integer, intitulé:string, difficulté:integer, date_creation:date, image:string, réponse:string, intitulé_correct:bool, fk_cat_thème=>Catégorie.thème, fk_cat_intitulé=>Catégorie.intitule)
Contraintes : intitulé key, difficulté >= 1 AND difficulté <= 5 ; date_creation NOT NULL
Contraintes liées à l'héritage : (image IS NULL AND réponse IS NULL) XOR (intitulé_correct IS NULL)
vQuestion (numéro, intitulé, difficulté, date_creation, image, réponse, intitulé_correct, fk_cat_thème, fk_cat_intitulé, type:{"R", "VF"})
avec type="R" SI (intitulé_correct IS NULL) SINON type="VF"

Question (#numéro:integer, intitulé:string, difficulté:integer, date_creation:date, image:string, réponse:string, intitulé_correct:bool, fk_cat_thème=>Catégorie.thème, fk_cat_intitulé=>Catégorie.intitule)
Contraintes : intitulé key, difficulté >= 1 AND difficulté <= 5 ; date_creation NOT NULL
Contraintes liées à l'héritage : (image IS NULL AND réponse IS NULL) XOR (intitulé_correct IS NULL)
vQuestion (numéro, intitulé, difficulté, date_creation, image, réponse, intitulé_correct, fk_cat_thème, fk_cat_intitulé, type:{"R", "VF"})
avec type="R" SI (intitulé_correct IS NULL) SINON type="VF"

Remarque :

La transformation par la classe mère est plus simple (cinq relations au lieu de sept), mais on peut accepter la solution par référence (plus rigoureuse car elle demande un peu moins de contraintes).

Question (#numéro:integer, intitulé:string, difficulté:integer, date_creation:date, fk_cat_thème=>Catégorie.thème, fk_cat_intitulé=>Catégorie.intitule)
Contraintes : intitulé key, difficulté >= 1 AND difficulté <= 5 ; date_creation NOT NULL
Reconnaitre(#numero=>Question, image:string,reponse:string)
Contraintes : image NOT NULL
vReconnaitre = JointureNaturelle(Question,Reconnaitre)
QCM(#numero=>Question, intitulé_correct:bool)
Contraintes : intitulé_correct NOT NULL
vQCM = JointureNaturelle(Question,QCM)
Contrainte générale liée à la classe mère abstraite : Union(Projection(Reconnaitre,numero),Projection(QCM,numero) = Projection(Question,Numero) 

Question (#numéro:integer, intitulé:string, difficulté:integer, date_creation:date, fk_cat_thème=>Catégorie.thème, fk_cat_intitulé=>Catégorie.intitule)
Contraintes : intitulé key, difficulté >= 1 AND difficulté <= 5 ; date_creation NOT NULL

Reconnaitre(#numero=>Question, image:string,reponse:string)
Contraintes : image NOT NULL
vReconnaitre = JointureNaturelle(Question,Reconnaitre)

QCM(#numero=>Question, intitulé_correct:bool)
Contraintes : intitulé_correct NOT NULL
vQCM = JointureNaturelle(Question,QCM)

Contrainte générale liée à la classe mère abstraite : Union(Projection(Reconnaitre,numero),Projection(QCM,numero) = Projection(Question,Numero)

Les tables Question, Reconnaître et QCM sont en BCNF et donc en 3NF.

Question

Écrivez le code SQL LDD permettant la création de la table Catégorie.

Solution

CREATE TABLE Categories (
    theme VARCHAR (20),
    intitule VARCHAR(20),
    PRIMARY KEY (theme, intitule),
    FOREIGN KEY theme REFERENCES Themes(titre)
);

CREATE TABLE Categories (
    theme VARCHAR (20),
    intitule VARCHAR(20),
    PRIMARY KEY (theme, intitule),
    FOREIGN KEY theme REFERENCES Themes(titre)
);

Question

Dessinez tous les tableaux permettant de représenter les deux questions présentées en exemple dans l'énoncé. Remplissez avec une information pertinente à votre convenance les champs pour lesquels vous n'avez pas d'information.

Solution

Thème
titre	domaine
conception	base de données
schéma relationnel	base de données

Catégorie
thème	intitulé
conception	classes associations
schéma relationnel	traduction UML/relationnel

Solution avec héritage par classe mère

Question
numéro	intitulé	difficulté	date_creation	image	réponse	intitulé_correct	fk_cat_thème	fk_cat_intitulé
1	"Sur le diagramme..."	2	01/05/2017	http://xxx/image.jpg	Réponse	NULL	conception	classes associations
2	"Dans un schéma..."	2	02/05/2017	NULL	NULL	Vrai	schéma relationnel	traduction UML/relationnel

Solution avec héritage par référence

Question
numéro	intitulé	difficulté	date_creation	fk_cat_thème	fk_cat_intitulé
1	"Sur le diagramme..."	2	01/05/2017	conception	classes associations
2	"Dans un schéma..."	2	02/05/2017	schéma relationnel	traduction UML/relationnel

Reconnaitre
numéro	image	réponse
1	http://xxx/image.jpg	Réponse

QCM
numéro	intitulé_correct
2	Vrai