--
-- Type: TABLE; Owner: DEAPP; Name: DE_CHROMOSOMAL_REGION
--
 CREATE TABLE "DEAPP"."DE_CHROMOSOMAL_REGION" 
  (	"REGION_ID" NUMBER NOT NULL ENABLE, 
"GPL_ID" VARCHAR2(50 BYTE), 
"CHROMOSOME" VARCHAR2(2 BYTE), 
"START_BP" NUMBER, 
"END_BP" NUMBER, 
"NUM_PROBES" NUMBER(*,0), 
"REGION_NAME" VARCHAR2(100 BYTE), 
"CYTOBAND" VARCHAR2(100 BYTE), 
"GENE_SYMBOL" VARCHAR2(100 BYTE), 
"GENE_ID" NUMBER, 
"ORGANISM" VARCHAR2(200 BYTE), 
 CONSTRAINT "DE_CHROMOSOMAL_REGION_PKEY" PRIMARY KEY ("REGION_ID")
 USING INDEX
 TABLESPACE "TRANSMART"  ENABLE
  ) SEGMENT CREATION IMMEDIATE
 TABLESPACE "TRANSMART" ;

--
-- Type: SEQUENCE; Owner: DEAPP; Name: DE_CHROMO_REGION_ID_SEQ
--
CREATE SEQUENCE  "DEAPP"."DE_CHROMO_REGION_ID_SEQ"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE ;

--
-- Type: TRIGGER; Owner: DEAPP; Name: TRG_DE_CHROMOSOMAL_REGION_ID
--
  CREATE OR REPLACE TRIGGER "DEAPP"."TRG_DE_CHROMOSOMAL_REGION_ID" 
before insert on "DEAPP"."DE_CHROMOSOMAL_REGION"
for each row begin
       	if inserting then
               	if :NEW."REGION_ID" is null then
                       	select DE_CHROMO_REGION_ID_SEQ.nextval into :NEW."REGION_ID" from dual;
               	end if;
       	end if;
end;
/
ALTER TRIGGER "DEAPP"."TRG_DE_CHROMOSOMAL_REGION_ID" ENABLE;
 
--
-- Type: REF_CONSTRAINT; Owner: DEAPP; Name: DE_CHROM_REGION_GPL_ID_FKEY
--
ALTER TABLE "DEAPP"."DE_CHROMOSOMAL_REGION" ADD CONSTRAINT "DE_CHROM_REGION_GPL_ID_FKEY" FOREIGN KEY ("GPL_ID")
 REFERENCES "DEAPP"."DE_GPL_INFO" ("PLATFORM") ENABLE;

