\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.\\

Sachant qu’un QCM est défini dans un module et peut être utilisé dans toute activité (TD, TP, Projet,...) du module, qu’une fiche d’évaluation est définie dans un programme (CPI1, CPI2, ING1,...) et peut être utilisée dans les modules du programme, qu’une fiche de catégorisation permet de catégoriser les interviewés et que tous ces questionnaires peuvent être réutilisés chaque année scolaire, nous avons donc, en suivant ces contraintes, crée un modèle conceptuel de données (MCD) afin d'illustrer au mieux la structuration de notre base de données. Grace à la création d'un jeu d’essai par le biais d'Excel permettant de réaliser une première approche du projet, et la réalisation de ce MCD, nous pouvons déjà visualiser comment fonctionnera et s'établira la base de données.\\

Suite à un entretien avec le professeur référent (Besma ZEDDINI), nous avons choisi de revoir notre MCD. La poursuite du projet se basera donc maintenant sur le MCD noté "Modèle conceptuel de données 4" sur le précédent livrable. Nous allons par ce rapport retranscrire le modèle conceptuel des données sous forme de modèle logique des données (MLD) et expliquer le script de création des tables et des contraintes associées (primary key, not null, foreign key, default, check, unique) ainsi que le jeu de test qui l'accompagne, basé sur la maquette au préalable réalisée sous Microsoft Excel.

\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/dictionnaire.png}
\caption{Dictionnaire de données}
\end{center}
\end{figure}

\newpage

\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{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
\end{itemize}

\section{Précisions}

Par rapport au MCD du dernier rapport, nous avons enlevé l'association \textit{elv\_intégrer} car nous nous sommes rendus compte que le programme qu'intègre l'élève n'est pas pertinent à la finalité de notre projet.\\

La manière dont nous enregistrons les réponses des élèves est la suivante : une session est créée lorsqu'un élève veut répondre à un questionnaire. Ainsi, nous aurons un lien entre l'élève et le questionnaire auquel il veut répondre. De ce questionnaire, on fait découler les questions lui appartenant à l'élève et on lui propose de choisir une réponse (question fermée) ou d'en entrer une (question ouverte). On enregistre finalement les réponses choisies ou entrées par l'élève avec \textit{rep\_valider}.\\

L'intérêt de cette structure est la possibilité de pouvoir répondre plusieurs fois au même questionnaire. De ce fait, les questionnaires sont indéfiniment réutilisables. La définition de la date de session nous permet en plus de trier chronologiquement les opérations.

\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 Session(\underline{id}, ses\_date, \#elv\_id, \#qa\_id);
\item rep\_valider(\underline{\#ses\_id, \#rep\_id});\\
\end{itemize}

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 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 trois 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\\
\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}

\newpage

\section{Création et suppression de tables}

La création de tables suit rigoureusement l'ordre que nous avons donné dans le MLD. En effet, la définition de certaines tables implique la définition de clés étrangères, et il est impossible de définir une clé étrangère faisant référence à une table non existante.\\

La suppression de tables suit l'ordre inverse pour des raisons analogues : si on supprime une table et qu'un autre attribut y fait référence, il est impossible de supprimer cette table.\\

Les \textit{NOT NULL} insérés dénotent des attributs obligatoires lors d'insertion de données. Autrement, des valeurs par défaut (notamment pour les dates : Questionnaire.qa\_date et Session.ses\_date ; par défaut : current\_date) ont été définies pour le confort de l'utilisateur.\\

Des \textit{ON DELETE SET NULL/CASCADE} ont été également mis en place pour l'utilisateur. Ainsi, il peut aisément supprimer un questionnaire et les questions/réponses seront automatiquement supprimées (\textit{ON DELETE CASCADE}). Les attributs QCM.act\_id et Evaluation.mod\_id sont \textit{ON DELETE SET NULL} pour une ultérieure et éventuelle mise à jour de référence.

\section{Jeu d'essai}

Nous fournissons dans ce jeu d'essai une liste d'élèves, une liste de programmes (ainsi que des modules et activités sous-jacentes), des questionnaires prédéfinis (reprise partielle du jeu de test effectué sur Excel) ainsi que des exemples de sessions.\\

Nous faisons apparaître les résultats des requêtes suivantes pour un aperçu non exhaustif et rapide du jeu d'essai :

\newpage

\begin{verbatim}
SELECT * FROM Eleve;
\end{verbatim}

\begin{figure}[h]
\begin{center}
\includegraphics[width=15cm]{img/elv_list.png}
\caption{Liste d'élèves}
\end{center}
\end{figure}


\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}

\begin{figure}[h]
\begin{center}
\includegraphics[width=15cm]{img/scol_list.png}
\caption{Listes des activités, modules et programmes correspondants}
\end{center}
\end{figure}

\newpage

\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}

\begin{figure}[h]
\begin{center}
\includegraphics[width=5cm]{img/qu14.png}
\caption{Enoncé de la question 14}
\end{center}
\end{figure}

\newpage

\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}

\begin{figure}[h]
\begin{center}
\includegraphics[width=7cm]{img/session.png}
\caption{Réponses enregistrées dans la session 2}
\end{center}
\end{figure}

\newpage

\chapter{Conclusion}

Nous avons désormais au terme de ce livrable une base de données concrète et complète qui nous permettra d'effectuer diverses opérations dans le prochain livrable, à savoir les requêtes d'interrogation.\\

Il est intéressant de noter la progression depuis la maquette Excel à la base de données Oracle, qui n'est pas forcément comme on aurait pu se l'imaginer, mais dont les résultats, au final, se conforment à nos attentes.\\

Finalement, c'est notre deuxième occasion (après celle rencontrée en CPI) de manipuler le langage SQL, ce qui ravive nos doigts et notre esprit technique.

\end{document}