drop table "RELS" cascade constraints PURGE;
drop table "MATIERES" cascade constraints PURGE;
drop table "ACTIVITES" cascade constraints PURGE;
drop table "RELS_RELATIONS" cascade constraints PURGE;
drop table "DOCUMENTS" cascade constraints PURGE;
drop table "RELS_TYPES" cascade constraints PURGE;
drop table "RELS_SOUS_TYPES" cascade constraints PURGE;


CREATE CLUSTER id_rels(idrels NUMBER);


CREATE TABLE "RELS_TYPES" 
   (	"P_ID" NUMBER NOT NULL ENABLE, 
	"LIBELLE" VARCHAR2(50 BYTE) NOT NULL ENABLE, 
	 CONSTRAINT "PK_RELS_TYPES" PRIMARY KEY ("P_ID")	 
	 );
	 
 CREATE TABLE "RELS_SOUS_TYPES" 
   (	"P_ID" NUMBER NOT NULL ENABLE, 
	"LIBELLE" VARCHAR2(50 BYTE) NOT NULL ENABLE, 
	 CONSTRAINT "PK_RELS_SOUS_TYPES" PRIMARY KEY ("P_ID")
	); 


CREATE TABLE "RELS" 
   (	"P_ID" NUMBER NOT NULL ENABLE, 
	"LIBELLE" VARCHAR2(110 BYTE) NOT NULL ENABLE, 
	"DATE_MAJ" DATE DEFAULT sysdate NOT NULL ENABLE, 
	"DATE_CRE" DATE DEFAULT sysdate NOT NULL ENABLE, 
	"ETAT" VARCHAR2(20 BYTE) NOT NULL ENABLE, 
	"LIBELLE_COURT" VARCHAR2(15 BYTE), 
	"DATE_PUBLICATION" DATE DEFAULT sysdate NOT NULL ENABLE, 
	"F_TYPE" NUMBER, -- les types des rels
	"F_SOUS_TYPE" NUMBER, -- les sous types des rels
	 CONSTRAINT "CK_RELS_2" CHECK (etat IN('BROUILLON','PUBLIEE','ATTENTE')) ENABLE, 
	 CONSTRAINT "PK_RELS" PRIMARY KEY ("P_ID"),
	 CONSTRAINT "FK_RELS_2" FOREIGN KEY ("F_TYPE")
	  REFERENCES "RELS_TYPES" ("P_ID") ENABLE, 
	 CONSTRAINT "FK_RELS_3" FOREIGN KEY ("F_SOUS_TYPE")
	  REFERENCES "RELS_SOUS_TYPES" ("P_ID") ENABLE
) cluster id_rels(P_ID);

CREATE TABLE "MATIERES" 
   (	"PF_ID" NUMBER NOT NULL ENABLE, 
	"PRESENTATION" CLOB, 
	"SYLLABUS" CLOB, 
	 CONSTRAINT "PK_MATIERES" PRIMARY KEY ("PF_ID"),
	 CONSTRAINT "FK_MATIERES_1" FOREIGN KEY ("PF_ID")
	  REFERENCES "RELS" ("P_ID") ON DELETE CASCADE ENABLE
	 )cluster id_rels(PF_ID);
	 
CREATE TABLE "ACTIVITES" 
   (	"PF_ID" NUMBER NOT NULL ENABLE, 
	"ORGANISATION_TRAVAIL" VARCHAR2(50 BYTE) NOT NULL ENABLE, 
	"DESCRIPTION" CLOB, 
	"MODE_EVALUATION" NUMBER(1,0), 
	 CONSTRAINT "CK_ACTIVITES_1" CHECK (organisation_travail IN ('INDIVIDUEL','GROUPE','PROMO')) ENABLE, 
	 CONSTRAINT "PK_ACTIVITES" PRIMARY KEY ("PF_ID"),
	 CONSTRAINT "FK_ACTIVITES_1" FOREIGN KEY ("PF_ID")
	  REFERENCES "RELS" ("P_ID") ON DELETE CASCADE ENABLE
	 )cluster id_rels(PF_ID);

CREATE INDEX idx_cluster_id_rels ON CLUSTER id_rels;

CREATE TABLE "RELS_RELATIONS" 
   (	"PF_PERE" NUMBER NOT NULL ENABLE, 
	"PF_FILS" NUMBER NOT NULL ENABLE, 
	"RELATION" varchar2(50), 
	"P_ID" NUMBER NOT NULL ENABLE, 
	 CONSTRAINT "PK_RELS_RELATIONS" PRIMARY KEY ("P_ID")
);

CREATE TABLE "DOCUMENTS" 
   (	"PF_ID" NUMBER NOT NULL ENABLE, 
	"F_TYPE_XML" NUMBER, 
	"MD5" NUMBER NOT NULL ENABLE, 
	"FILENAME" CLOB, 
	 CONSTRAINT "PK_DOCUMENTS" PRIMARY KEY ("PF_ID"),
	 CONSTRAINT "FK_DOCUMENTS_1" FOREIGN KEY ("PF_ID")
	  REFERENCES "RELS" ("P_ID") ON DELETE CASCADE ENABLE
	 );