\documentclass[a4paper,12pt]{report}
 
\usepackage[utf8]{inputenc}
\usepackage[T1]{fontenc}
\usepackage[francais]{babel}
\usepackage{indentfirst}
%\usepackage{layout}
%\usepackage{geometry}
%\usepackage{setspace}
\usepackage{soul}
\usepackage{ulem}
%\usepackage{eurosym}
\usepackage{graphicx}
%\usepackage{bookman}
%\usepackage{charter}
%\usepackage{newcent}
%\usepackage{lmodern}
%\usepackage{mathpazo}
%\usepackage{mathptmx}
%\usepackage{url}
%\usepackage{verbatim}
%\usepackage{moreverb}
%\usepackage{listings}
%\usepackage{fancyhdr}
\usepackage{wrapfig}
%\usepackage{color}
%\usepackage{colortbl}
\usepackage{amsmath}
\usepackage{amssymb}
\usepackage{mathrsfs}
\usepackage{eurosym}
%\usepackage{asmthm}
%\usepackage{makeidx}
 
 
\begin{document}

\begin{titlepage}
\begin{figure}[h]
\begin{center}
\includegraphics[width=5cm]{img/eisti.jpg}
\end{center}
\end{figure}

\begin{center}

\begin{Huge}
\bsc{Génie Logiciel : Base de données}\\
\end{Huge}

\vspace*{1cm}

\begin{Large}
Christian \bsc{Ingouff}\\
Pierre-Alexandre \bsc{Tyndal}\\
Hugo \bsc{Dos}\\
\vspace*{1cm}
EISTI\\
\end{Large}

\vspace*{0.33cm}

2013/2014\\
Semestre 1\\
\end{center}

\end{titlepage}

\tableofcontents

\chapter{Introduction}
Ce projet de gestion de questionnaires consiste, grâce à une base de données et de scripts SQL, à récupérer les différents questionnaires et les différents calculs possibles, propres à chaque type de questionnaire.\\

Ayant au cours des derniers livrables construit au fur et à mesure la base de données, qui est désormais complète et fonctionnelle sous Oracle 10g, nous avons à notre disposition la base sur laquelle nous allons appliquer le projet que nous avons exposé par le biais du Microsoft Excel du deuxième jalon.\\

Vous trouverez dans ce rapport le détail des requêtes SQL mises en avant par notre réflexion ainsi que le développement technique et la justification de celles-ci.

\newpage

\chapter{Modèle conceptuel de données}

\begin{figure}[h]
\begin{center}
\includegraphics[width=15cm]{img/mcd.png}
\caption{Modèle conceptuel de données}
\end{center}
\end{figure}

\newpage

\begin{figure}[h]
\begin{center}
\includegraphics[width=7cm]{img/dict.png}
\caption{Dictionnaire de données}
\end{center}
\end{figure}

\newpage

Nous rappelons ici notre MCD, qui fonctionne de la même manière que dans le dernier livrable. Cependant, nous avons entre temps ajouté une entité Catégorie ainsi que 2 associations connexes pour permettre le traitement de questionnaires de ce type.

\section{Les entités}
	\begin{itemize}
		\item \textbf{Questionnaire :} Ensemble des questionnaires, contient un id unique, un titre et une date de création
		\item \textbf{QCM, Catégorisation, Evaluation:} types de questionnaires, contient un id unique.
		\item \textbf{Catégorie:} Elle appartient à une catégorisation, ce sont les résultats possibles pour ce genre de questionnaire. Contient un id unique ainsi que son intitulé.
		\item \textbf{Question :} Ensemble des questions. Ces questions appartiennent à un questionnaire. Contient un id unique, un libellé (le texte de la question) et le type de question (fermée, ouverte, etc...).
		\item \textbf{Réponse :} Ensemble des réponses. Ces réponses sont sous-jacentes à une question. Contient un id unique et le texte correspondant à la réponse.
		\item \textbf{Session :} Définie quand un élève répond à un questionnaire. Contient un id unique et une date de session.
		\item \textbf{Elève, Programme, Module, Activité:} Ces entités représentent comme leur nom l’indique l’élève, les programmes de l’EISTI et enfin les modules et les activités des différents programmes.
	\end{itemize}
\section{Les associations}
\begin{itemize}
	\item \textbf{rep\_associer :} Une question proposant des réponses, cette association fait le lien entre la réponse et son unique (cardinalité 1,1) question correspondante. Si une question est ouverte, les réponses qui lui sont associées vont être les réponses entrées par les utilisateurs lors des sessions.
	\item \textbf{qu\_associer :} Un questionnaire proposant des questions, cette association fait le lien entre la question et le questionnaire où elle figure.
	\item \textbf{qcm\_être, eval\_être, ctg\_être :} Définition du type de questionnaire
	\item \textbf{qcm\_poser, eval\_poser :} Définition du contexte dans lequel le questionnaire est posé (dans quelle activité pour les QCM, dans quel module pour les évaluations)
	\item \textbf{act\_associer, mod\_correspondre :} Association hiérarchique de données : une activité est contenue dans un module, un module est contenu dans un programme
	\item \textbf{elv\_répondre :} Quand un élève répond à un questionnaire, il génère une session. elv\_répondre fait le lien entre la session et l'élève.
	\item \textbf{qaire\_choisir :} Une session choisit forcément un et un seul questionnaire
	\item \textbf{rep\_valider :} Une session enregistrera des réponses choisies
	\item \textbf{ctge\_concerner :} Lien entre la catégorie et sa catégorisation
	\item \textbf{rep\_catégoriser :} Les réponses qui vont correspondre à une catégorie en particulier vont être reliées par cette association. C'est ainsi que l'on identifiera plusieurs catégories dans un questionnaire de catégorisation.
\end{itemize}

\chapter{Modèle logique de données}

Le modèle conceptuel de données décrit ci-avant se traduit ainsi en modèle logique de données :
\begin{itemize}
\item Questionnaire(\underline{id}, titre, qa\_date);
\item Question(\underline{id}, libelle, qu\_type, \#qa\_id);
\item Reponse(\underline{id}, libelle, \#qu\_id);\\

\item Eleve(\underline{id}, nom, prenom, datenaiss);
\item Programme(\underline{id}, libelle);
\item Module(\underline{id}, libelle, \#prg\_id);
\item Activite(\underline{id}, libelle, \#mod\_id);\\

\item QCM(\underline{id}, \#qa\_id, \#act\_id);
\item Evaluation(\underline{id}, \#qa\_id, \#mod\_id);
\item Categorisation(\underline{id}, \#qa\_id);
\item Categorie(\underline{id}, libelle, \#ctgn\_id);
\item rep\_categoriser(\underline{\#rep\_id, \#ctg\_id});\\

\item Session(\underline{id}, ses\_date, \#elv\_id, \#qa\_id);
\item rep\_valider(\underline{\#ses\_id, \#rep\_id});\\
\end{itemize}

\newpage

Pour les références des clés étrangères, on se réfère au préfixe de l'attribut en question, avec :
\begin{itemize}
	\item qa : Questionnaire
	\item qu : Question
	\item ctgn : Catégorisation
	\item ctg : Catégorie
	\item prg : Programme
	\item mod : Module
	\item act : Activité
	\item elv : Elève
	\item ses : Session
	\item rep : Réponse
\end{itemize}

\chapter{Opérations SQL}

\section{Utilisation}

Nous fournissons dans le dossier \textit{src} de ce livrable quatre fichiers .sql :
\begin{itemize}
	\item create.sql : Ce fichier permet de créer les tables de notre base de données
	\item insert.sql : Ce fichier permet d'insérer les données de notre jeu d'essai dans la base de données
	\item clean.sql : Ce fichier permet de supprimer les tables (et leurs données) de notre base de données
	\item queries.sql : Ce fichier contient l'ensemble des requêtes SQL développées dans ce livrable.\\
\end{itemize}

Nous avons effectué notre travail à l'aide d'Oracle 10g : il est donc recommandé d'utiliser cette version pour utiliser nos fichiers. Dedans, on crée et on remplit la base de données avec ces deux commandes successives :
\begin{verbatim}
start <chemin du fichier>/create.sql
start <chemin du fichier>/insert.sql
\end{verbatim}

On supprime les tables quand on en a plus besoin avec la commande :
\begin{verbatim}
start <chemin du fichier>/clean.sql
\end{verbatim}

Il est recommandé de copier et coller chaque requête présente depuis le fichier queries.sql jusqu'à la console SQL : ainsi, le résultat est facilement visible.

\newpage

\section{Requêtes SQL}

\subsection{Requêtes simples}

\begin{verbatim}
SELECT *
FROM Eleve
ORDER BY datenaiss ASC;
\end{verbatim}

Simple affichage de la liste des élèves du plus vieux au plus jeune (avec ORDER BY).\\

\begin{verbatim}
SELECT *
FROM Eleve
WHERE datenaiss = (
   SELECT min(datenaiss)
   FROM Eleve
);
\end{verbatim}

Recherche de l'élève le plus âgé, avec la fonction min() qui cherche le minimum.\\

\begin{verbatim}
SELECT count(id)
FROM Questionnaire
WHERE id IN (
    SELECT id
    FROM Categorisation
);
\end{verbatim}

Affichage du nombre de questionnaires appartenant au type catégorisation. On note ici le lien entre les deux tables avec une sous-requête et l'utilisation de la fonction count().\\

\begin{verbatim}
SELECT qu.libelle as question, r.libelle as reponse
FROM Question qu, Reponse r
WHERE r.qu_id = qu.id
AND qu.id = 14;
\end{verbatim}

Cette requête affiche l'énoncé de la question numéro 14. Comme on veut afficher à la fois la question et la réponse, on a recours ici à une jointure plutôt qu'à une sous-requête. On voit également la notion de restriction.\\

\begin{verbatim}
SELECT P.libelle AS programme, M.libelle AS module, A.libelle AS activite
FROM Activite A, Module M, Programme P
WHERE A.mod_id = M.id
AND M.prg_id = P.id
ORDER BY P.id ASC;
\end{verbatim}

Sous le même principe, on fait la liste des programmes, des modules et des activités. Comme on peut le voir, les jointures ne sont pas limitées à deux tables.\\

\begin{verbatim}
SELECT e.nom as eleve, q.libelle as question, r.libelle as reponse
FROM Eleve e, Question q, Reponse r, Session_ s, rep_valider v
WHERE e.id = s.elv_id
AND v.ses_id = s.id
AND v.rep_id = r.id
AND r.qu_id = q.id
AND s.id = 2;
\end{verbatim}

Nous avons les réponses enregistrées dans la session 2 avec leurs questions correspondantes. Ceci est un exemple de requête avec de multiples jointures, et il est bien évidemment possible d'en faire davantage.\\

\begin{verbatim}
SELECT e.nom, e.prenom, count(s.id) AS nombre
FROM Eleve e LEFT JOIN Session_ s
ON s.elv_id = e.id
GROUP BY e.nom, e.prenom;
\end{verbatim}

Affichage du nombre de questionnaires auxquels chaque élève a répondu. On note l'utilisation du LEFT JOIN qui permet d'afficher les élèves qui n'ont répondu à aucun questionnaire. On utilise également GROUP BY avec count() pour compter selon chaque élève.\\

\newpage

\begin{verbatim}
SELECT libelle
FROM Reponse
WHERE qu_id = (
    SELECT id
    FROM Question
    WHERE qu_type = 'ouverte'
    AND qa_id = (
        SELECT id
        FROM Questionnaire
        WHERE id = (
            SELECT qa_id
            FROM Evaluation
            WHERE id = 1
        )
    )
);
\end{verbatim}

Liste des remarques en question ouverte itérées pour l'évaluation numéro 1. On note l'utilisation du type de question, qui permet de différencier les questions fermées aux questions ouvertes. Les sous-requêtes à répétition sont aussi une alternative plus optimisée en mémoire que les jointures si on peut se le permettre.\\

\begin{verbatim}
SELECT qu.libelle, count(r.id)
FROM Question qu, Reponse r
WHERE r.qu_id = qu.id
AND qu.qa_id = (
    SELECT id
    FROM Questionnaire
    WHERE id = (
        SELECT qa_id
        FROM QCM
        WHERE id = 1
    )
)
GROUP BY qu.libelle;
\end{verbatim}

Nombre de réponses, avec les questions, proposées pour chaque question dans le QCM numéro 1. Ceci est un exemple de combinaison de plusieurs procédés vus précédemment.

\begin{verbatim}
SELECT qu.libelle AS question, r.libelle AS reponse
FROM Question qu, Reponse r
WHERE r.qu_id = qu.id
AND qu.qa_id IN (
    SELECT id
    FROM Questionnaire
    WHERE id IN (
        SELECT qa_id
        FROM Evaluation
        WHERE mod_id IN (
            SELECT id
            FROM Module
            WHERE prg_id = (
                SELECT id
                FROM Programme
                WHERE libelle = 'ING 1'
            )
        )
    )
)
AND r.id IN (
    SELECT rep_id
    FROM rep_valider
    WHERE ses_id IN (
        SELECT id
        FROM Session_
        WHERE elv_id = (
            SELECT id
            FROM Eleve
            WHERE lower(nom) = 'tyndal'
            AND lower(prenom) = 'pierre-alexandre'
        )
    )
);
\end{verbatim}

On récupére ici les réponses (avec questions) données par TYNDAL Pierre-Alexandre dans les évaluations du programme ING 1. On peut voir ici le parcours hiérarchique des tables composant notre base de données.

\subsection{Requêtes techniques}

Les requêtes suivantes vont directement traiter de l'énoncé du projet qui nous a été donné. Il est également à mettre en parallèle avec la modélisation que nous avons faite du projet sur Microsoft Excel.\\

\begin{verbatim}
SELECT qu.libelle AS question, r.libelle AS reponse, count(rv.rep_id) AS voix
FROM Question qu, Reponse r LEFT JOIN rep_valider rv
ON r.id = rv.rep_id
WHERE r.qu_id = qu.id
AND qu.qa_id = (
    SELECT qa_id
    FROM Evaluation
    WHERE id = 1
)
GROUP BY qu.libelle, r.libelle
ORDER BY qu.libelle;
\end{verbatim}

Cette requête permet de visualiser les statistiques de l'évaluation numéro 1, c'est-à-dire combien de personnes ont choisi telle réponse parmi celles disponibles.

\newpage

\begin{verbatim}
SELECT categ, score FROM (
    SELECT c.libelle AS categ, count(rc.rep_id) AS score
    FROM Categorie c LEFT JOIN rep_categoriser rc
    ON c.id = rc.ctg_id
    AND rc.rep_id IN (
        SELECT rep_id
        FROM rep_valider
        WHERE ses_id = (
            SELECT id
            FROM Session_
            WHERE elv_id = (
                SELECT id
                FROM Eleve
                WHERE lower(nom) = 'dos'
                AND lower(prenom) = 'hugo'
            )
            AND qa_id = (
                SELECT qa_id
                FROM Categorisation
                WHERE id = 1
            )
        )
    )
    GROUP BY c.libelle
    ORDER BY score DESC
)
WHERE ROWNUM = 1;
\end{verbatim}

On obtient la catégorie à laquelle appartient DOS Hugo selon la catégorisation numéro 1. En effet, la catégorie à laquelle cet élève appartient correspond à celle avec laquelle il a le plus d'affinités, c'est-à-dire de réponses communes avec celles enregistrées dans \textit{rep\_categoriser} pour une catégorie. Ainsi, on fait le compte de réponses correspondantes avec toutes les catégories, puis on prend le maximum (ORDER BY décroissant, puis prise du premier résultat).\\

\newpage

\begin{verbatim}
SELECT qu.libelle as question, r.libelle as reponse
FROM Question qu, reponse r
WHERE r.qu_id = qu.id
AND r.id IN (
    SELECT rep_id
    FROM rep_valider
    WHERE ses_id = (
        SELECT id
        FROM Session_
        WHERE elv_id IS NULL
        AND qa_id = (
            SELECT id
            FROM Questionnaire
            WHERE id = (
                SELECT qa_id
                FROM QCM
                WHERE id = 1
            )
        )
    )
)
ORDER BY qu.id ASC;
\end{verbatim}

On affiche ici les bonnes réponses du QCM numéro 1. Il est intéressant de noter ici notre gestion des bonnes réponses d'un QCM. En effet, nous générons une session sans id d'élève pour signifier qu'il s'agit d'une solution de QCM et non d'un élève qui y répond.

\newpage

\begin{verbatim}
SELECT rep_id
FROM rep_valider
WHERE ses_id IN (
    SELECT id
    FROM Session_
    WHERE elv_id IS NULL
)
INTERSECT
SELECT rep_id
FROM rep_valider
WHERE ses_id IN (
    SELECT id
    FROM Session_
    WHERE elv_id = (
        SELECT id
        FROM Eleve
        WHERE lower(nom) = 'van damme'
        AND lower(prenom) = 'jean-claude'
    )
    AND qa_id = (
        SELECT qa_id
        FROM QCM
        WHERE act_id IN (
            SELECT id
            FROM Activite
            WHERE mod_id = (
                SELECT id
                FROM Module
                WHERE libelle = 'Transverse'
                AND prg_id = (
                    SELECT id
                    FROM Programme
                    WHERE libelle = 'ING 1'
                )
            )
        )
    )
);
\end{verbatim}

Ceci est une manière d'obtenir les bonnes réponses de Jean-Claude Van Damme au QCM se rapportant à la Transverse des ING 1. Il utilise l'intersection entre deux résultats : l'ensemble des bonnes réponses de tous les QCM avec l'ensemble des réponses données par l'élève dans le QCM précisé. La comparaison se fait avec l'ensemble des bonnes réponses de tous les QCM pour éviter d'appeler inutilement des tables en plus : si on précise d'un côté les modalités du QCM, l'autre côté sera filtré par l'intersection.\\

Egalement, on peut obtenir les mauvaises réponses de l'élève si on fait la négation pour la liste des bonnes réponses (donc si on obtient la liste des mauvaises réponses des QCM).\\

En algèbre relationnelle, la requête se traduit ainsi :\\

Bonnes réponses des QCM :
\[
\begin{array}{lcl}
R_{1} & = & \pi_{\text{id}}(\sigma_{\text{elv\_id = NULL}}(\text{Session})) \\
R_{\text{réponses}} & = & \pi_{\text{rep\_id}}(\sigma_{\text{ses\_id} = R_{1}}(\text{rep\_valider})) \\
\end{array}
\]

Sélection du bon questionnaire :
\[
\begin{array}{lcl}
R_{1} & = & \pi_{\text{id}}(\sigma_{\text{libelle = 'ING 1'}}(\text{Programme})) \\
R_{2} & = & \pi_{\text{id}}(\sigma_{\text{libelle = 'Transverse' ET prg\_id} = R_{1}}(\text{Module})) \\
R_{3} & = & \pi_{\text{id}}(\sigma_{\text{mod\_id} = R_{2}}(\text{Activité})) \\
R_{\text{QCM}} & = & \pi_{\text{qa\_id}}(\sigma_{\text{act\_id} = R_{3}}(\text{QCM})) \\
\end{array}
\]

Sélection de Jean-Claude Van Damme :
\[
R_{\text{JCVD}} = \pi_{\text{id}}(\sigma_{\text{nom = 'Van Damme' ET prenom = 'Jean-Claude'}}(\text{Elève}))
\]

Les réponses de Jean-Claude Van Damme :
\[
R_{rep\_JCVD} = \pi_{\text{id}}(\sigma_{\text{elv\_id} = R_{JCVD} \text{ET} \text{qa\_id} = R_{QCM}}(\text{rep\_valider}))
\]

Résultat final :
\[
R = R_{\text{réponses}} \cap R_{\text{rep\_JCVD}}
\]

\newpage

\begin{verbatim}
SELECT e.nom, e.prenom, count(rv.rep_id) AS score
FROM Eleve e, Session_ s, rep_valider rv
WHERE e.id = s.elv_id
AND s.id = rv.ses_id
AND rv.rep_id IN (
    SELECT rep_id
    FROM rep_valider
    WHERE ses_id = (
        SELECT id
        FROM Session_
        WHERE elv_id IS NULL
        AND qa_id = (
            SELECT qa_id
            FROM QCM
            WHERE id = 1
        )
    )
)
GROUP BY e.nom, e.prenom
HAVING count(rv.rep_id) >= (
    SELECT avg(score) FROM (
        SELECT s.elv_id, count(rv.rep_id) AS score
        FROM Session_ s, rep_valider rv
        WHERE s.id = rv.ses_id
        AND rv.rep_id IN (
            SELECT rep_id
            FROM rep_valider
            WHERE ses_id = (
                SELECT id
                FROM Session_
                WHERE elv_id IS NULL
                AND qa_id = (
                    SELECT qa_id
                    FROM QCM
                    WHERE id = 1
                )
            )
        )
        GROUP BY s.elv_id
    )
);
\end{verbatim}

Finalement, cette requête renvoie la liste des élèves ayant davantage de bonnes réponses que la moyenne des répondants au QCM numéro 1. Elle fait d'abord la liste des élèves avec leur nombre de bonnes réponses puis filtre avec HAVING pour placer la barre à la moyenne des répondants.\\

\newpage

\chapter{Conclusion}

C'est ainsi que nous pouvons contempler la finalisation de notre projet sur l'aménagement de questionnaires sur Oracle 10g. Nous avons ainsi constaté qu'avec une base de données à première vue relativement simple, on pouvait effectuer une multitude d'opérations, et ceci avec le SQL.\\

Ainsi, nous voyons clairement le potentiel d'un tel aménagement dans des projets de plus grande envergure. De plus, nous avons pris conscience qu'une bonne organisation de la base de données était primordiale dans l'aménagement des requêtes. Il va de soi que la tâche n'est pas forcément simple, ni pratique, en notant l'absence d'une réelle interface pratique.\\

Néanmoins, cela a développé notre esprit débrouillard et notre esprit d'équipe. Il ne reste plus qu'à résumer nos efforts dans ce dernier livrable qui portera de la synthèse et du parachèvement de notre projet.

\end{document}