/*
MLD de base :
Questionnaire(\underline{id}, titre, qa_date);
Question(\underline{id}, libelle, qu_type, #qa_id);
Reponse(\underline{id}, libelle, #qu_id);

Eleve(\underline{id}, nom, prenom, datenaiss);
Programme(\underline{id}, libelle);
Module(\underline{id}, libelle, #prg_id);
Activite(\underline{id}, libelle, #mod_id);

QCM(\underline{id}, #qa_id, #act_id);
Evaluation(\underline{id}, #qa_id, #mod_id);
Categorisation(\underline{id}, #qa_id);
Session_(\underline{id}, ses_date, #elv_id, #qa_id);
rep_valider(\underline{#ses_id, #rep_id});
*/

/* Structure questions */

CREATE TABLE Questionnaire (
id NUMBER NOT NULL CONSTRAINT pk_questionnaire PRIMARY KEY,
titre VARCHAR2(30) NOT NULL,
qa_date DATE DEFAULT current_date
);

CREATE TABLE Question (
id NUMBER NOT NULL CONSTRAINT pk_question PRIMARY KEY,
libelle VARCHAR2(90) NOT NULL,
qu_type VARCHAR2(20) NOT NULL,
qa_id NUMBER NOT NULL CONSTRAINT fk_associer_1 REFERENCES Questionnaire(id) ON DELETE CASCADE
);

CREATE TABLE Reponse (
id NUMBER NOT NULL CONSTRAINT pk_reponse PRIMARY KEY,
libelle VARCHAR2(90) NOT NULL,
qu_id NUMBER NOT NULL CONSTRAINT fk_reponse REFERENCES Question(id) ON DELETE CASCADE
);

/* Structure scolarité */

CREATE TABLE Eleve (
id NUMBER NOT NULL CONSTRAINT pk_eleve PRIMARY KEY,
nom VARCHAR2(20) NOT NULL,
prenom VARCHAR2(20) NOT NULL,
datenaiss DATE NOT NULL
);

CREATE TABLE Programme (
id NUMBER NOT NULL CONSTRAINT pk_programme PRIMARY KEY,
libelle VARCHAR2(20) NOT NULL
);

CREATE TABLE Module (
id NUMBER NOT NULL CONSTRAINT pk_module PRIMARY KEY,
libelle VARCHAR2(20) NOT NULL,
prg_id NUMBER CONSTRAINT fk_module REFERENCES Programme(id) ON DELETE CASCADE
);

CREATE TABLE Activite (
id NUMBER NOT NULL CONSTRAINT pk_activite PRIMARY KEY,
libelle VARCHAR2(20) NOT NULL,
mod_id NUMBER NOT NULL CONSTRAINT fk_activite REFERENCES Module(id) ON DELETE CASCADE
);

/* Relations scolaire/questionnaires */

CREATE TABLE QCM (
id NUMBER NOT NULL CONSTRAINT pk_qcm PRIMARY KEY,
qa_id NUMBER NOT NULL CONSTRAINT fk_qcm_1 REFERENCES Questionnaire(id) ON DELETE CASCADE,
act_id NUMBER NOT NULL CONSTRAINT fk_qcm_2 REFERENCES Activite(id) ON DELETE SET NULL
);

CREATE TABLE Evaluation (
id NUMBER NOT NULL CONSTRAINT pk_evaluation PRIMARY KEY,
qa_id NUMBER NOT NULL CONSTRAINT fk_evaluation_1 REFERENCES Questionnaire(id) ON DELETE CASCADE,
mod_id NUMBER NOT NULL CONSTRAINT fk_evaluation_2 REFERENCES Module(id) ON DELETE SET NULL
);

CREATE TABLE Categorisation (
id NUMBER NOT NULL CONSTRAINT pk_categorisation PRIMARY KEY,
qa_id NUMBER NOT NULL CONSTRAINT fk_categorisation REFERENCES Questionnaire(id) ON DELETE CASCADE
);

CREATE TABLE Session_ (
id NUMBER NOT NULL CONSTRAINT pk_session PRIMARY KEY,
ses_date DATE DEFAULT current_date,
elv_id NUMBER NOT NULL CONSTRAINT fk_session_1 REFERENCES Eleve(id) ON DELETE CASCADE,
qa_id NUMBER NOT NULL CONSTRAINT fk_session_2 REFERENCES Questionnaire(id) ON DELETE CASCADE
);

CREATE TABLE rep_valider (
ses_id NUMBER NOT NULL CONSTRAINT fk_valider_1 REFERENCES Session_(id) ON DELETE CASCADE,
rep_id NUMBER NOT NULL CONSTRAINT fk_valider_2 REFERENCES Reponse(id) ON DELETE CASCADE,
PRIMARY KEY (ses_id, rep_id)
);
