--
-- Type: TABLE; Owner: BIOMART; Name: BIO_ASSAY_ANALYSIS_DATA
--
 CREATE TABLE "BIOMART"."BIO_ASSAY_ANALYSIS_DATA" 
  (	"BIO_ASY_ANALYSIS_DATA_ID" NUMBER NOT NULL ENABLE, 
"FOLD_CHANGE_RATIO" NUMBER, 
"R_VALUE" NUMBER(18,5), 
"RHO_VALUE" NUMBER(18,5), 
"BIO_ASSAY_ANALYSIS_ID" NUMBER(18,0) NOT NULL ENABLE, 
"ADJUSTED_P_VALUE_CODE" NVARCHAR2(100), 
"FEATURE_GROUP_NAME" VARCHAR2(100 BYTE) NOT NULL ENABLE, 
"BIO_EXPERIMENT_ID" NUMBER(18,0), 
"BIO_ASSAY_PLATFORM_ID" NUMBER(18,0), 
"ETL_ID" NVARCHAR2(100), 
"CUT_VALUE" NUMBER(18,5), 
"RESULTS_VALUE" NVARCHAR2(100), 
"NUMERIC_VALUE" NUMBER(18,5), 
"NUMERIC_VALUE_CODE" NVARCHAR2(50), 
"TEA_NORMALIZED_PVALUE" NUMBER(18,5), 
"BIO_ASSAY_FEATURE_GROUP_ID" NUMBER(18,0), 
"RAW_PVALUE" BINARY_DOUBLE, 
"PREFERRED_PVALUE" BINARY_DOUBLE, 
"ADJUSTED_PVALUE" BINARY_DOUBLE, 
"PROBESET_ID" NUMBER(22,0), 
"LSMEAN1" NUMBER(18,5), 
"LSMEAN2" NUMBER(18,5)
  ) SEGMENT CREATION IMMEDIATE
 TABLESPACE "TRANSMART" ;

--
-- Type: INDEX; Owner: BIOMART; Name: BAD_IDX13
--
CREATE INDEX "BIOMART"."BAD_IDX13" ON "BIOMART"."BIO_ASSAY_ANALYSIS_DATA" ("BIO_ASSAY_ANALYSIS_ID", "BIO_ASSAY_FEATURE_GROUP_ID")
TABLESPACE "INDX" 
PARALLEL 8 ;

--
-- Type: INDEX; Owner: BIOMART; Name: BAAD_FGN_IDX
--
CREATE INDEX "BIOMART"."BAAD_FGN_IDX" ON "BIOMART"."BIO_ASSAY_ANALYSIS_DATA" ("FEATURE_GROUP_NAME")
TABLESPACE "INDX" 
PARALLEL 8 ;

--
-- Type: REF_CONSTRAINT; Owner: BIOMART; Name: BIO_ASSAY_ANALYSIS_DATA_N_FK3
--
ALTER TABLE "BIOMART"."BIO_ASSAY_ANALYSIS_DATA" ADD CONSTRAINT "BIO_ASSAY_ANALYSIS_DATA_N_FK3" FOREIGN KEY ("BIO_ASSAY_PLATFORM_ID")
 REFERENCES "BIOMART"."BIO_ASSAY_PLATFORM" ("BIO_ASSAY_PLATFORM_ID") DISABLE;

--
-- Type: REF_CONSTRAINT; Owner: BIOMART; Name: BIO_ASSAY_ANALYSIS_DATA_N_FK2
--
ALTER TABLE "BIOMART"."BIO_ASSAY_ANALYSIS_DATA" ADD CONSTRAINT "BIO_ASSAY_ANALYSIS_DATA_N_FK2" FOREIGN KEY ("BIO_EXPERIMENT_ID")
 REFERENCES "BIOMART"."BIO_EXPERIMENT" ("BIO_EXPERIMENT_ID") DISABLE;

--
-- Type: REF_CONSTRAINT; Owner: BIOMART; Name: BIO_ASSAY_ANALYSIS_DATA_N_FK1
--
ALTER TABLE "BIOMART"."BIO_ASSAY_ANALYSIS_DATA" ADD CONSTRAINT "BIO_ASSAY_ANALYSIS_DATA_N_FK1" FOREIGN KEY ("BIO_ASSAY_ANALYSIS_ID")
 REFERENCES "BIOMART"."BIO_ASSAY_ANALYSIS" ("BIO_ASSAY_ANALYSIS_ID") ENABLE;

--
-- Type: TRIGGER; Owner: BIOMART; Name: TRG_BIO_ASY_ANALYSIS_DATA_ID
--
  CREATE OR REPLACE TRIGGER "BIOMART"."TRG_BIO_ASY_ANALYSIS_DATA_ID" 
 before insert on "BIO_ASSAY_ANALYSIS_DATA"    
 for each row begin     
  if inserting then       
    if :NEW."BIO_ASY_ANALYSIS_DATA_ID" is null then          
      select SEQ_BIO_DATA_ID.nextval into :NEW."BIO_ASY_ANALYSIS_DATA_ID" from dual;       
    end if;    
  end if; 
 end;
/
ALTER TRIGGER "BIOMART"."TRG_BIO_ASY_ANALYSIS_DATA_ID" ENABLE;
 
--
-- Type: REF_CONSTRAINT; Owner: BIOMART; Name: BIO_ASY_AD_FG_FK
--
ALTER TABLE "BIOMART"."BIO_ASSAY_ANALYSIS_DATA" ADD CONSTRAINT "BIO_ASY_AD_FG_FK" FOREIGN KEY ("BIO_ASSAY_FEATURE_GROUP_ID")
 REFERENCES "BIOMART"."BIO_ASSAY_FEATURE_GROUP" ("BIO_ASSAY_FEATURE_GROUP_ID") ENABLE;

--
-- Type: INDEX; Owner: BIOMART; Name: BAAD_IDX4
--
CREATE INDEX "BIOMART"."BAAD_IDX4" ON "BIOMART"."BIO_ASSAY_ANALYSIS_DATA" ("BIO_ASSAY_PLATFORM_ID")
TABLESPACE "INDX" 
PARALLEL 8 ;

--
-- Type: INDEX; Owner: BIOMART; Name: BAAD_IDX6
--
CREATE INDEX "BIOMART"."BAAD_IDX6" ON "BIOMART"."BIO_ASSAY_ANALYSIS_DATA" ("BIO_EXPERIMENT_ID", "BIO_ASSAY_ANALYSIS_ID")
TABLESPACE "INDX" 
PARALLEL 8 ;

--
-- Type: INDEX; Owner: BIOMART; Name: BAAD_IDX7
--
CREATE INDEX "BIOMART"."BAAD_IDX7" ON "BIOMART"."BIO_ASSAY_ANALYSIS_DATA" ("BIO_ASSAY_ANALYSIS_ID", "BIO_ASY_ANALYSIS_DATA_ID")
TABLESPACE "INDX" 
PARALLEL 8 ;

--
-- Type: INDEX; Owner: BIOMART; Name: BAAD_IDX1
--
CREATE BITMAP INDEX "BIOMART"."BAAD_IDX1" ON "BIOMART"."BIO_ASSAY_ANALYSIS_DATA" ("BIO_ASSAY_FEATURE_GROUP_ID", "BIO_EXPERIMENT_ID")
TABLESPACE "INDX" 
PARALLEL 8 ;

--
-- Type: INDEX; Owner: BIOMART; Name: BAAD_IDX14
--
CREATE INDEX "BIOMART"."BAAD_IDX14" ON "BIOMART"."BIO_ASSAY_ANALYSIS_DATA" ("BIO_ASSAY_FEATURE_GROUP_ID", "BIO_ASY_ANALYSIS_DATA_ID")
TABLESPACE "INDX" 
PARALLEL 8 ;

--
-- Type: INDEX; Owner: BIOMART; Name: BAAD_IDX12
--
CREATE INDEX "BIOMART"."BAAD_IDX12" ON "BIOMART"."BIO_ASSAY_ANALYSIS_DATA" ("BIO_EXPERIMENT_ID", "BIO_ASSAY_ANALYSIS_ID", "BIO_ASSAY_FEATURE_GROUP_ID")
TABLESPACE "INDX" 
PARALLEL 8 ;

--
-- Type: INDEX; Owner: BIOMART; Name: BAAD_IDX11
--
CREATE INDEX "BIOMART"."BAAD_IDX11" ON "BIOMART"."BIO_ASSAY_ANALYSIS_DATA" ("BIO_EXPERIMENT_ID", "BIO_ASSAY_ANALYSIS_ID", "BIO_ASY_ANALYSIS_DATA_ID")
TABLESPACE "INDX" 
PARALLEL 8 ;

