DROP TABLE Participe;
DROP TABLE ChoisitReponse;
DROP TABLE ContientEval;
DROP TABLE ContientQCM;	
DROP TABLE ContientActivite;
DROP TABLE ContientCategorisation;	
DROP TABLE EstDansActivite;	
DROP TABLE EstDansModule;
DROP TABLE EstDansSession;	
DROP TABLE Utilisateur;
DROP TABLE Reponse;
DROP TABLE Activite;
DROP TABLE Categorie;
DROP TABLE Categorisation;
DROP TABLE Questionnaire;
DROP TABLE QCM;
DROP TABLE Evaluation;
DROP TABLE SessionQ;
DROP TABLE ModuleQ;
DROP TABLE Programme;

CREATE TABLE Programme(idProgramme NUMBER CONSTRAINT pk_Programme PRIMARY KEY, libelleProgramme VARCHAR2(30));

CREATE TABLE ModuleQ(idModule NUMBER CONSTRAINT pk_ModuleQ PRIMARY KEY, 
					 libelleModule VARCHAR2(30),
					 idProgramme CONSTRAINT fk1_modq REFERENCES Programme(idProgramme));

CREATE TABLE SessionQ(idSession NUMBER CONSTRAINT pk_SessionQ PRIMARY KEY, dateSession DATE DEFAULT SYSDATE);
CREATE TABLE Evaluation(idEvaluation NUMBER CONSTRAINT pk_Evaluation PRIMARY KEY, libelleEval VARCHAR2(255));
CREATE TABLE QCM(idQCM NUMBER CONSTRAINT pk_QCM PRIMARY KEY, libelleQCM VARCHAR2(255));
CREATE TABLE Questionnaire(idQuest NUMBER CONSTRAINT pk_Questionnaire PRIMARY KEY, libelleQuestionnaire VARCHAR2(255));
CREATE TABLE Categorisation(idCategorisation NUMBER CONSTRAINT pk_Categorisation PRIMARY KEY, libelleCatego VARCHAR2(255));
CREATE TABLE Categorie(idCateg NUMBER CONSTRAINT pk_Categorie PRIMARY KEY, libelleCategorie VARCHAR2(255));
CREATE TABLE Activite(idActivite NUMBER CONSTRAINT pk_Activite PRIMARY KEY, libelleActivite VARCHAR2(255));

CREATE TABLE Reponse(idReponse NUMBER CONSTRAINT pk_Reponse PRIMARY KEY, 
					 typeReponse VARCHAR2(30),
					 libelleReponse VARCHAR2(255),
					 idQuest CONSTRAINT fk1_rep REFERENCES Questionnaire(idQuest),
					 idEval DEFAULT NULL CONSTRAINT fk4_rep REFERENCES Evaluation(idEvaluation),
					 idQCM DEFAULT NULL CONSTRAINT fk3_rep REFERENCES QCM(idQCM),
					 idCateg DEFAULT NULL CONSTRAINT fk2_rep REFERENCES Categorie(idCateg),
					 idCategorisation DEFAULT NULL CONSTRAINT fk5_rep REFERENCES Categorisation(idCategorisation));

CREATE TABLE Utilisateur(idUtilisateur NUMBER CONSTRAINT pk_User PRIMARY KEY, nom VARCHAR2(30), prenom VARCHAR2(30));

CREATE TABLE EstDansSession(idQuest CONSTRAINT fk1_sess REFERENCES Questionnaire(idQuest),
							idSession CONSTRAINT fk2_sess REFERENCES SessionQ(idSession));
					
CREATE TABLE EstDansModule(idQuest CONSTRAINT fk1_modu REFERENCES Questionnaire(idQuest),
						   idModule CONSTRAINT fk2_modu REFERENCES ModuleQ(idModule));
						   
CREATE TABLE EstDansActivite(idActivite CONSTRAINT fk1_qcma REFERENCES Activite(idActivite),
						     idQCM CONSTRAINT fk2_qcma REFERENCES QCM(idQCM));
							 
CREATE TABLE ContientCategorisation(idQuest CONSTRAINT fk1_cate REFERENCES Questionnaire(idQuest),
								   idCategorisation CONSTRAINT fk2_cate REFERENCES Categorisation(idCategorisation));
						   
CREATE TABLE ContientActivite(idActivite CONSTRAINT fk1_acti REFERENCES Activite(idActivite),
							  idModule CONSTRAINT fk2_acti REFERENCES ModuleQ(idModule));
							  
CREATE TABLE ContientQCM(idQuest CONSTRAINT fk1_cqc REFERENCES Questionnaire(idQuest),
						 idQCM CONSTRAINT fk2_cqc REFERENCES QCM(idQCM));
						 
CREATE TABLE ContientEval(idQuest CONSTRAINT fk1_cev REFERENCES Questionnaire(idQuest),
						  idEvaluation CONSTRAINT fk2_cev REFERENCES Evaluation(idEvaluation));
							  
CREATE TABLE ChoisitReponse(idUtilisateur CONSTRAINT fk1_chrep REFERENCES Utilisateur(idUtilisateur),
							idReponse CONSTRAINT fk2_chrep REFERENCES Reponse(idReponse),
							valeur INTEGER DEFAULT 0,
							textVARCHAR2(4000) DEFAULT NULL,
							idSession CONSTRAINT fk3_chrep REFERENCES SessionQ(idSession));
							
CREATE TABLE Participe(idUtilisateur CONSTRAINT fk1_part REFERENCES Utilisateur(idUtilisateur),
					   idSession CONSTRAINT fk2_part REFERENCES SessionQ(idSession));