GUS-3.6-SQL-New Tables
Work in Progress, Jan 23 2008; Check for updates.
IMPORTANT - Do not modify this page!
Please submit suggestions on the GUS-3.6 Schema Change Request wiki page, or send an email to frank@pcbi.upenn.edu
For MODIFIED Tables, View and Materialized Views see (GUS-3.6-SQL-Mod Tables)
Back to Proposed Schema Changes
New Tables
DoTS.DBRefAAFeature DoTS.MassSpecSummary DoTS.PlasmoMap STUDY.StudyBibliographicReference STUDY.StudyBioMaterial *Note: Moved table RAD.StudyBioMaterial to STUDY TESS.AnalysisInput TESS.AnalysisParam TESS.AnalysisQCParam TESS.LogicalGroup TESS.LogicalGroupLink TESS.ModelPredecessorGraph TESS.ModelRelation TESS.Parameter TESS.PerformancePoint TESS.PerformancePtParamValue TESS.PerformanceResult TESS.PerformanceSummaryStat TESS.Protocol TESS.ProtocolParam TESS.ProtocolQCParam TESS.ProtocolStep
New Views
DoTS.CpgIslandNAFeature RAD.RTPCRELEMENT RAD.RTPCRELEMENTRESULT
New Materialized Views
RAD.COMPOSITEELEMENTASSEMBLY_MV RAD.ELEMENTASSEMBLYACCESSION_MV RAD.ELEMENTASSEMBLY_MV
SQL for New Tables, Views, and Materialized Views
Note: SQL to create indexes included for new tables and materialized views
DoTS
CREATE TABLE "DOTS"."DBREFAAFEATURE"
( "DB_REF_AA_FEATURE_ID" NUMBER(10,0) NOT NULL ENABLE,
"AA_FEATURE_ID" NUMBER(10,0) NOT NULL ENABLE,
"DB_REF_ID" NUMBER(10,0) NOT NULL ENABLE,
"MODIFICATION_DATE" DATE NOT NULL ENABLE,
"USER_READ" NUMBER(1,0) NOT NULL ENABLE,
"USER_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"GROUP_READ" NUMBER(1,0) NOT NULL ENABLE,
"GROUP_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"OTHER_READ" NUMBER(1,0) NOT NULL ENABLE,
"OTHER_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"ROW_USER_ID" NUMBER(12,0) NOT NULL ENABLE,
"ROW_GROUP_ID" NUMBER(4,0) NOT NULL ENABLE,
"ROW_PROJECT_ID" NUMBER(4,0) NOT NULL ENABLE,
"ROW_ALG_INVOCATION_ID" NUMBER(12,0) NOT NULL ENABLE,
CONSTRAINT "DRAF_PK" PRIMARY KEY ("DB_REF_AA_FEATURE_ID") TABLESPACE "GUS" ENABLE,
CONSTRAINT "DRAF_FK1" FOREIGN KEY ("AA_FEATURE_ID")
REFERENCES "DOTS"."AAFEATUREIMP" ("AA_FEATURE_ID") ENABLE,
CONSTRAINT "DRAF_FK2" FOREIGN KEY ("DB_REF_ID")
REFERENCES "SRES"."DBREF" ("DB_REF_ID") ENABLE
) TABLESPACE "GUS";
CREATE INDEX "DOTS"."DRAF_IND1" ON "DOTS"."DBREFAAFEATURE" ("DB_REF_ID", "DB_REF_AA_FEATURE_ID") TABLESPACE "GUS";
CREATE INDEX "DOTS"."DRAF_IND2" ON "DOTS"."DBREFAAFEATURE" ("AA_FEATURE_ID", "DB_REF_ID") TABLESPACE "GUS";
CREATE UNIQUE INDEX "DOTS"."DRAF_PK" ON "DOTS"."DBREFAAFEATURE" ("DB_REF_AA_FEATURE_ID") TABLESPACE "GUS";
CREATE TABLE "DOTS"."MASSSPECSUMMARY"
( "MASS_SPEC_SUMMARY_ID" NUMBER(12,0) NOT NULL ENABLE,
"AA_SEQUENCE_ID" NUMBER(12,0) NOT NULL ENABLE,
"PREDICTION_ALGORITHM_ID" NUMBER(12,0) NOT NULL ENABLE,
"EXTERNAL_DATABASE_RELEASE_ID" NUMBER(12,0),
"DEVELOPMENTAL_STAGE" VARCHAR2(20) NOT NULL ENABLE,
"IS_EXPRESSED" NUMBER(1,0) NOT NULL ENABLE,
"NUMBER_OF_SPANS" NUMBER(12,0) NOT NULL ENABLE,
"SEQUENCE_COUNT" NUMBER(12,0) NOT NULL ENABLE,
"SPECTRUM_COUNT" NUMBER(12,0) NOT NULL ENABLE,
"AA_SEQ_PERCENT_COVERED" FLOAT(126) NOT NULL ENABLE,
"AA_SEQ_LENGTH" NUMBER(12,0) NOT NULL ENABLE,
"AA_SEQ_MOLECULAR_WEIGHT" NUMBER(12,0) NOT NULL ENABLE,
"AA_SEQ_PI" FLOAT(126) NOT NULL ENABLE,
"MODIFICATION_DATE" DATE NOT NULL ENABLE,
"USER_READ" NUMBER(1,0) NOT NULL ENABLE,
"USER_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"GROUP_READ" NUMBER(1,0) NOT NULL ENABLE,
"GROUP_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"OTHER_READ" NUMBER(1,0) NOT NULL ENABLE,
"OTHER_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"ROW_USER_ID" NUMBER(12,0) NOT NULL ENABLE,
"ROW_GROUP_ID" NUMBER(3,0) NOT NULL ENABLE,
"ROW_PROJECT_ID" NUMBER(4,0) NOT NULL ENABLE,
"ROW_ALG_INVOCATION_ID" NUMBER(12,0) NOT NULL ENABLE,
CONSTRAINT "MASSSPECSUMMARY_PK" PRIMARY KEY ("MASS_SPEC_SUMMARY_ID")
TABLESPACE "GUS" ENABLE,
CONSTRAINT "MASSSPECSUMMARY_FK02" FOREIGN KEY ("PREDICTION_ALGORITHM_ID")
REFERENCES "CORE"."ALGORITHM" ("ALGORITHM_ID") ENABLE,
CONSTRAINT "MASSSPECSUMMARY_FK03" FOREIGN KEY ("EXTERNAL_DATABASE_RELEASE_ID")
REFERENCES "SRES"."EXTERNALDATABASERELEASE" ("EXTERNAL_DATABASE_RELEASE_ID") ENABLE,
CONSTRAINT "MASSSPECSUMMARY_FK01" FOREIGN KEY ("AA_SEQUENCE_ID")
REFERENCES "DOTS"."AASEQUENCEIMP" ("AA_SEQUENCE_ID") ENABLE
) TABLESPACE "GUS";
CREATE UNIQUE INDEX "DOTS"."MASSSPECSUMMARY_PK" ON "DOTS"."MASSSPECSUMMARY" ("MASS_SPEC_SUMMARY_ID") TABLESPACE "GUS";
CREATE TABLE "DOTS"."PLASMOMAP"
( "PLASMOMAP_ID" NUMBER(12,0) NOT NULL ENABLE,
"EXTERNAL_DATABASE_RELEASE_ID" NUMBER(5,0),
"SOURCE_ID" VARCHAR2(15) NOT NULL ENABLE,
"MARKER_NAME" VARCHAR2(25) NOT NULL ENABLE,
"MARKER_SYNONYM" VARCHAR2(15),
"ACCESSION" VARCHAR2(10),
"CHROMOSOME" VARCHAR2(2) NOT NULL ENABLE,
"CENTIMORGANS" FLOAT(126),
"FRAMEWORK" VARCHAR2(15),
"FORWARD_PRIMER" VARCHAR2(50) NOT NULL ENABLE,
"REVERSE_PRIMER" VARCHAR2(50) NOT NULL ENABLE,
"PRODUCT_LENGTH" NUMBER(12,0) NOT NULL ENABLE,
"TAXON_ID" NUMBER(12,0) NOT NULL ENABLE,
"CROSS" VARCHAR2(30) NOT NULL ENABLE,
"MODIFICATION_DATE" DATE NOT NULL ENABLE,
"USER_READ" NUMBER(1,0) NOT NULL ENABLE,
"USER_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"GROUP_READ" NUMBER(1,0) NOT NULL ENABLE,
"GROUP_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"OTHER_READ" NUMBER(1,0) NOT NULL ENABLE,
"OTHER_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"ROW_USER_ID" NUMBER(12,0) NOT NULL ENABLE,
"ROW_GROUP_ID" NUMBER(3,0) NOT NULL ENABLE,
"ROW_PROJECT_ID" NUMBER(4,0) NOT NULL ENABLE,
"ROW_ALG_INVOCATION_ID" NUMBER(12,0) NOT NULL ENABLE,
CONSTRAINT "PK_PLASMOMAP" PRIMARY KEY ("PLASMOMAP_ID") TABLESPACE "GUS" ENABLE,
CONSTRAINT "PLASMOMAP_FK01" FOREIGN KEY ("EXTERNAL_DATABASE_RELEASE_ID")
REFERENCES "SRES"."EXTERNALDATABASERELEASE" ("EXTERNAL_DATABASE_RELEASE_ID")ENABLE,
CONSTRAINT "PLASMOMAP_FK02" FOREIGN KEY ("TAXON_ID")
REFERENCES "SRES"."TAXON" ("TAXON_ID") ENABLE
) TABLESPACE "GUS";
CREATE UNIQUE INDEX "DOTS"."PK_PLASMOMAP" ON "DOTS"."PLASMOMAP" ("PLASMOMAP_ID") TABLESPACE "GUS";
CREATE INDEX "DOTS"."PLASMOMAP_IND01" ON "DOTS"."PLASMOMAP" ("SOURCE_ID") TABLESPACE "GUS";
CREATE INDEX "DOTS"."PLASMOMAP_IND02" ON "DOTS"."PLASMOMAP" ("ACCESSION") TABLESPACE "GUS";
CREATE INDEX "DOTS"."PLASMOMAP_IND03" ON "DOTS"."PLASMOMAP" ("CHROMOSOME", "CENTIMORGANS", "TAXON_ID") TABLESPACE "GUS";
CREATE INDEX "DOTS"."PLASMOMAP_IND04" ON "DOTS"."PLASMOMAP" ("EXTERNAL_DATABASE_RELEASE_ID") TABLESPACE "GUS";
CREATE INDEX "DOTS"."PLASMOMAP_IND05" ON "DOTS"."PLASMOMAP" ("TAXON_ID") TABLESPACE "GUS";
TESS
CREATE TABLE "STUDY"."STUDYBIBLIOGRAPHICREFERENCE"
( "STUDY_BIBLIO_REFERENCE_ID" NUMBER(12,0) NOT NULL ENABLE,
"STUDY_ID" NUMBER(4,0) NOT NULL ENABLE,
"BIBLIOGRAPHIC_REFERENCE_ID" NUMBER(10,0) NOT NULL ENABLE,
"ORDER_NUM" NUMBER(8,0),
"MODIFICATION_DATE" DATE NOT NULL ENABLE,
"USER_READ" NUMBER(1,0) NOT NULL ENABLE,
"USER_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"GROUP_READ" NUMBER(1,0) NOT NULL ENABLE,
"GROUP_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"OTHER_READ" NUMBER(1,0) NOT NULL ENABLE,
"OTHER_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"ROW_USER_ID" NUMBER(12,0) NOT NULL ENABLE,
"ROW_GROUP_ID" NUMBER(3,0) NOT NULL ENABLE,
"ROW_PROJECT_ID" NUMBER(4,0) NOT NULL ENABLE,
"ROW_ALG_INVOCATION_ID" NUMBER(12,0) NOT NULL ENABLE,
PRIMARY KEY ("STUDY_BIBLIO_REFERENCE_ID")
TABLESPACE "GUS" ENABLE,
FOREIGN KEY ("STUDY_ID")
REFERENCES "STUDY"."STUDY" ("STUDY_ID") ENABLE,
FOREIGN KEY ("BIBLIOGRAPHIC_REFERENCE_ID")
REFERENCES "SRES"."BIBLIOGRAPHICREFERENCE" ("BIBLIOGRAPHIC_REFERENCE_ID") ENABLE
);
CREATE UNIQUE INDEX "STUDY"."SYS_C00148027" ON "STUDY"."STUDYBIBLIOGRAPHICREFERENCE" ("STUDY_BIBLIO_REFERENCE_ID");
CREATE TABLE "TESS"."ANALYSISINPUT"
( "ANALYSIS_INPUT_ID" NUMBER(8,0) NOT NULL ENABLE,
"ANALYSIS_ID" NUMBER(8,0) NOT NULL ENABLE,
"LOGICAL_GROUP_ID" NUMBER(8,0) NOT NULL ENABLE,
"ROLE_NAME" VARCHAR2(100) NOT NULL ENABLE,
"ORDER_NUM" NUMBER(8,0),
"MODIFICATION_DATE" DATE NOT NULL ENABLE,
"USER_READ" NUMBER(1,0) NOT NULL ENABLE,
"USER_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"GROUP_READ" NUMBER(1,0) NOT NULL ENABLE,
"GROUP_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"OTHER_READ" NUMBER(1,0) NOT NULL ENABLE,
"OTHER_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"ROW_USER_ID" NUMBER(12,0) NOT NULL ENABLE,
"ROW_GROUP_ID" NUMBER(3,0) NOT NULL ENABLE,
"ROW_PROJECT_ID" NUMBER(4,0) NOT NULL ENABLE,
"ROW_ALG_INVOCATION_ID" NUMBER(12,0) NOT NULL ENABLE,
CONSTRAINT "PK_ANALYSISINPUT" PRIMARY KEY ("ANALYSIS_INPUT_ID")
TABLESPACE "GUS" ENABLE,
CONSTRAINT "ANALYSISINPUT_FK01" FOREIGN KEY ("LOGICAL_GROUP_ID")
REFERENCES "TESS"."LOGICALGROUP" ("LOGICAL_GROUP_ID") ENABLE,
CONSTRAINT "ANALYSISINPUT_FK02" FOREIGN KEY ("ANALYSIS_ID")
REFERENCES "TESS"."ANALYSIS" ("ANALYSIS_ID") ENABLE
);
CREATE INDEX "TESS"."ANALYSISINPUT_IND01" ON "TESS"."ANALYSISINPUT" ("ANALYSIS_ID");
CREATE INDEX "TESS"."ANALYSISINPUT_IND02" ON "TESS"."ANALYSISINPUT" ("LOGICAL_GROUP_ID");
CREATE UNIQUE INDEX "TESS"."PK_ANALYSISINPUT" ON "TESS"."ANALYSISINPUT" ("ANALYSIS_INPUT_ID");
CREATE TABLE "TESS"."ANALYSISPARAM"
( "ANALYSIS_PARAM_ID" NUMBER(8,0) NOT NULL ENABLE,
"ANALYSIS_ID" NUMBER(8,0) NOT NULL ENABLE,
"PROTOCOL_PARAM_ID" NUMBER(8,0) NOT NULL ENABLE,
"VALUE" VARCHAR2(256) NOT NULL ENABLE,
"MODIFICATION_DATE" DATE NOT NULL ENABLE,
"USER_READ" NUMBER(1,0) NOT NULL ENABLE,
"USER_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"GROUP_READ" NUMBER(1,0) NOT NULL ENABLE,
"GROUP_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"OTHER_READ" NUMBER(1,0) NOT NULL ENABLE,
"OTHER_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"ROW_USER_ID" NUMBER(12,0) NOT NULL ENABLE,
"ROW_GROUP_ID" NUMBER(3,0) NOT NULL ENABLE,
"ROW_PROJECT_ID" NUMBER(4,0) NOT NULL ENABLE,
"ROW_ALG_INVOCATION_ID" NUMBER(12,0) NOT NULL ENABLE,
CONSTRAINT "PK_ANALYSISPARAM" PRIMARY KEY ("ANALYSIS_PARAM_ID") TABLESPACE "GUS" ENABLE,
CONSTRAINT "ANALYSISPARAM_FK01" FOREIGN KEY ("ANALYSIS_ID")
REFERENCES "TESS"."ANALYSIS" ("ANALYSIS_ID") ENABLE,
CONSTRAINT "ANALYSISPARAM_FK02" FOREIGN KEY ("PROTOCOL_PARAM_ID")
REFERENCES "TESS"."PROTOCOLPARAM" ("PROTOCOL_PARAM_ID") ENABLE
);
CREATE INDEX "TESS"."ANALYSISPARAM_IND01" ON "TESS"."ANALYSISPARAM" ("ANALYSIS_ID");
CREATE INDEX "TESS"."ANALYSISPARAM_IND02" ON "TESS"."ANALYSISPARAM" ("PROTOCOL_PARAM_ID");
CREATE UNIQUE INDEX "TESS"."PK_ANALYSISPARAM" ON "TESS"."ANALYSISPARAM" ("ANALYSIS_PARAM_ID");
CREATE TABLE "TESS"."ANALYSISQCPARAM"
( "ANALYSIS_QC_PARAM_ID" NUMBER(8,0) NOT NULL ENABLE,
"ANALYSIS_ID" NUMBER(8,0) NOT NULL ENABLE,
"PROTOCOL_QC_PARAM_ID" NUMBER(8,0) NOT NULL ENABLE,
"VALUE" VARCHAR2(256) NOT NULL ENABLE,
"MODIFICATION_DATE" DATE NOT NULL ENABLE,
"USER_READ" NUMBER(1,0) NOT NULL ENABLE,
"USER_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"GROUP_READ" NUMBER(1,0) NOT NULL ENABLE,
"GROUP_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"OTHER_READ" NUMBER(1,0) NOT NULL ENABLE,
"OTHER_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"ROW_USER_ID" NUMBER(12,0) NOT NULL ENABLE,
"ROW_GROUP_ID" NUMBER(3,0) NOT NULL ENABLE,
"ROW_PROJECT_ID" NUMBER(4,0) NOT NULL ENABLE,
"ROW_ALG_INVOCATION_ID" NUMBER(12,0) NOT NULL ENABLE,
CONSTRAINT "ANALYSISQCPARAM_FK01" FOREIGN KEY ("ANALYSIS_ID")
REFERENCES "TESS"."ANALYSIS" ("ANALYSIS_ID") ENABLE,
CONSTRAINT "ANALYSISQCPARAM_FK02" FOREIGN KEY ("PROTOCOL_QC_PARAM_ID")
REFERENCES "TESS"."PROTOCOLQCPARAM" ("PROTOCOL_QC_PARAM_ID") ENABLE
);
CREATE TABLE "TESS"."LOGICALGROUP"
( "LOGICAL_GROUP_ID" NUMBER(8,0) NOT NULL ENABLE,
"CATEGORY" VARCHAR2(50) NOT NULL ENABLE,
"NAME" VARCHAR2(100) NOT NULL ENABLE,
"DESCRIPTION" VARCHAR2(1000) NOT NULL ENABLE,
"MODIFICATION_DATE" DATE NOT NULL ENABLE,
"USER_READ" NUMBER(1,0) NOT NULL ENABLE,
"USER_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"GROUP_READ" NUMBER(1,0) NOT NULL ENABLE,
"GROUP_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"OTHER_READ" NUMBER(1,0) NOT NULL ENABLE,
"OTHER_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"ROW_USER_ID" NUMBER(12,0) NOT NULL ENABLE,
"ROW_GROUP_ID" NUMBER(3,0) NOT NULL ENABLE,
"ROW_PROJECT_ID" NUMBER(4,0) NOT NULL ENABLE,
"ROW_ALG_INVOCATION_ID" NUMBER(12,0) NOT NULL ENABLE,
CONSTRAINT "PK_LOGICALGROUP" PRIMARY KEY ("LOGICAL_GROUP_ID")
TABLESPACE "GUS" ENABLE
);
CREATE UNIQUE INDEX "TESS"."PK_LOGICALGROUP" ON "TESS"."LOGICALGROUP" ("LOGICAL_GROUP_ID");
CREATE TABLE "TESS"."LOGICALGROUPLINK"
( "LOGICAL_GROUP_LINK_ID" NUMBER(8,0) NOT NULL ENABLE,
"LOGICAL_GROUP_ID" NUMBER(8,0) NOT NULL ENABLE,
"PARENT_LINK_ID" NUMBER(8,0),
"TABLE_ID" NUMBER(5,0) NOT NULL ENABLE,
"ROW_ID" NUMBER(12,0) NOT NULL ENABLE,
"ORDER_NUM" NUMBER(8,0) NOT NULL ENABLE,
"MODIFICATION_DATE" DATE NOT NULL ENABLE,
"USER_READ" NUMBER(1,0) NOT NULL ENABLE,
"USER_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"GROUP_READ" NUMBER(1,0) NOT NULL ENABLE,
"GROUP_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"OTHER_READ" NUMBER(1,0) NOT NULL ENABLE,
"OTHER_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"ROW_USER_ID" NUMBER(12,0) NOT NULL ENABLE,
"ROW_GROUP_ID" NUMBER(3,0) NOT NULL ENABLE,
"ROW_PROJECT_ID" NUMBER(4,0) NOT NULL ENABLE,
"ROW_ALG_INVOCATION_ID" NUMBER(12,0) NOT NULL ENABLE,
CONSTRAINT "PK_LOGICALGROUPLINK" PRIMARY KEY ("LOGICAL_GROUP_LINK_ID")
TABLESPACE "GUS" ENABLE,
CONSTRAINT "LOGICALGROUPLINK_FK01" FOREIGN KEY ("LOGICAL_GROUP_ID")
REFERENCES "TESS"."LOGICALGROUP" ("LOGICAL_GROUP_ID") ENABLE,
CONSTRAINT "LOGICALGROUPLINK_FK02" FOREIGN KEY ("PARENT_LINK_ID")
REFERENCES "TESS"."LOGICALGROUPLINK" ("LOGICAL_GROUP_LINK_ID") ENABLE,
CONSTRAINT "LOGICALGROUPLINK_FK03" FOREIGN KEY ("TABLE_ID")
REFERENCES "CORE"."TABLEINFO" ("TABLE_ID") ENABLE
);
CREATE TABLE "TESS"."MODELPREDECESSORGRAPH"
( "MODEL_PREDECESSOR_GRAPH_ID" NUMBER(12,0) NOT NULL ENABLE,
"ANALYSIS_ID" NUMBER(12,0) NOT NULL ENABLE,
"MODIFICATION_DATE" DATE NOT NULL ENABLE,
"USER_READ" NUMBER(1,0) NOT NULL ENABLE,
"USER_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"GROUP_READ" NUMBER(1,0) NOT NULL ENABLE,
"GROUP_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"OTHER_READ" NUMBER(1,0) NOT NULL ENABLE,
"OTHER_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"ROW_USER_ID" NUMBER(12,0) NOT NULL ENABLE,
"ROW_GROUP_ID" NUMBER(3,0) NOT NULL ENABLE,
"ROW_PROJECT_ID" NUMBER(4,0) NOT NULL ENABLE,
"ROW_ALG_INVOCATION_ID" NUMBER(12,0) NOT NULL ENABLE,
CONSTRAINT "PK_MODELPREDECESSORGRAPH" PRIMARY KEY ("MODEL_PREDECESSOR_GRAPH_ID")
TABLESPACE "GUS" ENABLE,
CONSTRAINT "MODELPREDECESSORGRAPH_FK01" FOREIGN KEY ("ANALYSIS_ID")
REFERENCES "TESS"."ANALYSIS" ("ANALYSIS_ID") ENABLE
);
CREATE INDEX "TESS"."MODELPREDECESSORGRAPH_IND01" ON "TESS"."MODELPREDECESSORGRAPH" ("ANALYSIS_ID");
CREATE UNIQUE INDEX "TESS"."PK_MODELPREDECESSORGRAPH" ON "TESS"."MODELPREDECESSORGRAPH" ("MODEL_PREDECESSOR_GRAPH_ID");
CREATE TABLE "TESS"."MODELRELATION"
( "MODEL_RELATION_ID" NUMBER(12,0) NOT NULL ENABLE,
"MODEL_PREDECESSOR_GRAPH_ID" NUMBER(12,0) NOT NULL ENABLE,
"MODEL_ID" NUMBER(12,0) NOT NULL ENABLE,
"PREDECESSOR_ID" NUMBER(12,0) NOT NULL ENABLE,
"MODIFICATION_DATE" DATE NOT NULL ENABLE,
"USER_READ" NUMBER(1,0) NOT NULL ENABLE,
"USER_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"GROUP_READ" NUMBER(1,0) NOT NULL ENABLE,
"GROUP_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"OTHER_READ" NUMBER(1,0) NOT NULL ENABLE,
"OTHER_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"ROW_USER_ID" NUMBER(12,0) NOT NULL ENABLE,
"ROW_GROUP_ID" NUMBER(3,0) NOT NULL ENABLE,
"ROW_PROJECT_ID" NUMBER(4,0) NOT NULL ENABLE,
"ROW_ALG_INVOCATION_ID" NUMBER(12,0) NOT NULL ENABLE,
CONSTRAINT "PK_MODELRELATION" PRIMARY KEY ("MODEL_RELATION_ID")
TABLESPACE "GUS" ENABLE,
CONSTRAINT "MODELRELATION_FK01" FOREIGN KEY ("MODEL_PREDECESSOR_GRAPH_ID")
REFERENCES "TESS"."MODELPREDECESSORGRAPH" ("MODEL_PREDECESSOR_GRAPH_ID") ENABLE,
CONSTRAINT "MODELRELATION_FK02" FOREIGN KEY ("MODEL_ID")
REFERENCES "TESS"."MODELIMP" ("MODEL_ID") ENABLE,
CONSTRAINT "MODELRELATION_FK03" FOREIGN KEY ("PREDECESSOR_ID")
REFERENCES "TESS"."MODELIMP" ("MODEL_ID") ENABLE
);
CREATE INDEX "TESS"."MODELRELATION_IND01" ON "TESS"."MODELRELATION" ("MODEL_PREDECESSOR_GRAPH_ID");
CREATE INDEX "TESS"."MODELRELATION_IND02" ON "TESS"."MODELRELATION" ("MODEL_ID");
CREATE INDEX "TESS"."MODELRELATION_IND03" ON "TESS"."MODELRELATION" ("PREDECESSOR_ID");
CREATE UNIQUE INDEX "TESS"."PK_MODELRELATION" ON "TESS"."MODELRELATION" ("MODEL_RELATION_ID");
CREATE TABLE "TESS"."PARAMETER"
( "PARAMETER_ID" NUMBER(12,0) NOT NULL ENABLE,
"NAME" VARCHAR2(255) NOT NULL ENABLE,
"PARAMETER_GROUP_ID" NUMBER(12,0) NOT NULL ENABLE,
"DATA_TYPE_ID" NUMBER(12,0) NOT NULL ENABLE,
"MODIFICATION_DATE" DATE NOT NULL ENABLE,
"USER_READ" NUMBER(1,0) NOT NULL ENABLE,
"USER_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"GROUP_READ" NUMBER(1,0) NOT NULL ENABLE,
"GROUP_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"ROW_USER_ID" NUMBER(1,0) NOT NULL ENABLE,
"ROW_GROUP_ID" NUMBER(1,0) NOT NULL ENABLE,
"ROW_PROJECT_ID" NUMBER(1,0) NOT NULL ENABLE,
"ROW_ALG_INVOCATION_ID" NUMBER(1,0) NOT NULL ENABLE,
CONSTRAINT "PK_PARAMETER" PRIMARY KEY ("PARAMETER_ID")
TABLESPACE "GUS" ENABLE,
CONSTRAINT "PARAMETER_FK02" FOREIGN KEY ("DATA_TYPE_ID")
REFERENCES "STUDY"."ONTOLOGYENTRY" ("ONTOLOGY_ENTRY_ID") ENABLE
);
CREATE UNIQUE INDEX "TESS"."PK_PARAMETER" ON "TESS"."PARAMETER" ("PARAMETER_ID") TABLESPACE "GUS";
CREATE TABLE "TESS"."PERFORMANCEPOINT"
( "PERFORMANCE_POINT_ID" NUMBER(12,0) NOT NULL ENABLE,
"PERFORMANCE_RESULT_ID" NUMBER(12,0) NOT NULL ENABLE,
"TRUE_POSITIVE_COUNT" NUMBER(12,0) NOT NULL ENABLE,
"FALSE_POSITIVE_COUNT" NUMBER(12,0) NOT NULL ENABLE,
"MODIFICATION_DATE" DATE NOT NULL ENABLE,
"USER_READ" NUMBER(1,0) NOT NULL ENABLE,
"USER_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"GROUP_READ" NUMBER(1,0) NOT NULL ENABLE,
"GROUP_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"OTHER_READ" NUMBER(1,0) NOT NULL ENABLE,
"OTHER_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"ROW_USER_ID" NUMBER(12,0) NOT NULL ENABLE,
"ROW_GROUP_ID" NUMBER(3,0) NOT NULL ENABLE,
"ROW_PROJECT_ID" NUMBER(4,0) NOT NULL ENABLE,
"ROW_ALG_INVOCATION_ID" NUMBER(12,0) NOT NULL ENABLE,
CONSTRAINT "PK_PERFORMANCEPOINT" PRIMARY KEY ("PERFORMANCE_POINT_ID")
TABLESPACE "GUS" ENABLE,
CONSTRAINT "PERFORMANCEPOINT_FK01" FOREIGN KEY ("PERFORMANCE_RESULT_ID")
REFERENCES "TESS"."PERFORMANCERESULT" ("PERFORMANCE_RESULT_ID") ENABLE
) TABLESPACE "GUS"
;
CREATE INDEX "TESS"."PERFORMANCEPOINT_IND01" ON "TESS"."PERFORMANCEPOINT"
("PERFORMANCE_RESULT_ID") TABLESPACE "GUS";
CREATE UNIQUE INDEX "TESS"."PK_PERFORMANCEPOINT" ON "TESS"."PERFORMANCEPOINT"
("PERFORMANCE_POINT_ID") TABLESPACE "GUS";
CREATE TABLE "TESS"."PERFORMANCEPTPARAMVALUE"
( "PERFORMANCE_PT_PARAM_VALUE_ID" NUMBER(12,0) NOT NULL ENABLE,
"PERFORMANCE_POINT_ID" NUMBER(12,0) NOT NULL ENABLE,
"PARAMETER_ID" NUMBER(12,0) NOT NULL ENABLE,
"NUMERIC_VALUE" FLOAT(126),
"STRING_VALUE" VARCHAR2(100),
"MODIFICATION_DATE" DATE NOT NULL ENABLE,
"USER_READ" NUMBER(1,0) NOT NULL ENABLE,
"USER_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"GROUP_READ" NUMBER(1,0) NOT NULL ENABLE,
"GROUP_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"OTHER_READ" NUMBER(1,0) NOT NULL ENABLE,
"OTHER_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"ROW_USER_ID" NUMBER(12,0) NOT NULL ENABLE,
"ROW_GROUP_ID" NUMBER(3,0) NOT NULL ENABLE,
"ROW_PROJECT_ID" NUMBER(4,0) NOT NULL ENABLE,
"ROW_ALG_INVOCATION_ID" NUMBER(12,0) NOT NULL ENABLE,
CONSTRAINT "PK_PERFORMANCEPTPARAMVALUE" PRIMARY KEY ("PERFORMANCE_PT_PARAM_VAL
UE_ID") TABLESPACE "GUS" ENABLE,
CONSTRAINT "PERFORMANCEPTPARAMVALUE_FK01" FOREIGN KEY ("PERFORMANCE_POINT_ID")
REFERENCES "TESS"."PERFORMANCEPOINT" ("PERFORMANCE_POINT_ID") ENABLE,
CONSTRAINT "PERFORMANCEPTPARAMVALUE_FK02" FOREIGN KEY ("PARAMETER_ID")
REFERENCES "TESS"."PARAMETER" ("PARAMETER_ID") ENABLE
) TABLESPACE "GUS";
CREATE INDEX "TESS"."PERFORMANCEPTPARAMVALUE_IND01" ON "TESS"."PERFORMANCEPTPARAMVALUE"
("PERFORMANCE_POINT_ID") TABLESPACE "GUS";
CREATE UNIQUE INDEX "TESS"."PK_PERFORMANCEPTPARAMVALUE" ON "TESS"."PERFORMANCEPTPARAMVALUE"
("PERFORMANCE_PT_PARAM_VALUE_ID") TABLESPACE "GUS";
CREATE TABLE "TESS"."PERFORMANCERESULT"
( "PERFORMANCE_RESULT_ID" NUMBER(12,0) NOT NULL ENABLE,
"ANALYSIS_ID" NUMBER(12,0) NOT NULL ENABLE,
"MODEL_ID" NUMBER(12,0) NOT NULL ENABLE,
"NAME" VARCHAR2(100),
"DESCRIPTION" VARCHAR2(1000),
"NUMBER_OF_POINTS" NUMBER(12,0) NOT NULL ENABLE,
"ACTUAL_POSITIVE_COUNT" NUMBER(12,0) NOT NULL ENABLE,
"ACTUAL_NEGATIVE_COUNT" NUMBER(12,0) NOT NULL ENABLE,
"MODIFICATION_DATE" DATE NOT NULL ENABLE,
"USER_READ" NUMBER(1,0) NOT NULL ENABLE,
"USER_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"GROUP_READ" NUMBER(1,0) NOT NULL ENABLE,
"GROUP_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"OTHER_READ" NUMBER(1,0) NOT NULL ENABLE,
"OTHER_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"ROW_USER_ID" NUMBER(12,0) NOT NULL ENABLE,
"ROW_GROUP_ID" NUMBER(3,0) NOT NULL ENABLE,
"ROW_PROJECT_ID" NUMBER(4,0) NOT NULL ENABLE,
"ROW_ALG_INVOCATION_ID" NUMBER(12,0) NOT NULL ENABLE,
CONSTRAINT "PK_PERFORMANCERESULT" PRIMARY KEY ("PERFORMANCE_RESULT_ID")
TABLESPACE "GUS" ENABLE,
CONSTRAINT "PERFORMANCERESULT_FK01" FOREIGN KEY ("ANALYSIS_ID")
REFERENCES "TESS"."ANALYSIS" ("ANALYSIS_ID") ENABLE,
CONSTRAINT "PERFORMANCERESULT_FK02" FOREIGN KEY ("MODEL_ID")
REFERENCES "TESS"."MODELIMP" ("MODEL_ID") ENABLE
) TABLESPACE "GUS";
CREATE INDEX "TESS"."PERFORMANCERESULT_IND01" ON "TESS"."PERFORMANCERESULT" ("ANALYSIS_ID") TABLESPACE "GUS";
CREATE INDEX "TESS"."PERFORMANCERESULT_IND02" ON "TESS"."PERFORMANCERESULT" ("MODEL_ID") TABLESPACE "GUS";
CREATE UNIQUE INDEX "TESS"."PK_PERFORMANCERESULT" ON "TESS"."PERFORMANCERESULT" ("PERFORMANCE_RESULT_ID")TABLESPACE "GUS";
CREATE TABLE "TESS"."PERFORMANCESUMMARYSTAT"
( "PERFORMANCE_SUMMARY_STAT_ID" NUMBER(12,0) NOT NULL ENABLE,
"PERFORMANCE_RESULT_ID" NUMBER(12,0) NOT NULL ENABLE,
"STATISTIC_NAME" VARCHAR2(32) NOT NULL ENABLE,
"STATISTIC_VALUE" FLOAT(126) NOT NULL ENABLE,
"MODIFICATION_DATE" DATE NOT NULL ENABLE,
"USER_READ" NUMBER(1,0) NOT NULL ENABLE,
"USER_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"GROUP_READ" NUMBER(1,0) NOT NULL ENABLE,
"GROUP_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"OTHER_READ" NUMBER(1,0) NOT NULL ENABLE,
"OTHER_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"ROW_USER_ID" NUMBER(12,0) NOT NULL ENABLE,
"ROW_GROUP_ID" NUMBER(3,0) NOT NULL ENABLE,
"ROW_PROJECT_ID" NUMBER(4,0) NOT NULL ENABLE,
"ROW_ALG_INVOCATION_ID" NUMBER(12,0) NOT NULL ENABLE,
CONSTRAINT "PK_PERFORMANCESUMMARYSTAT" PRIMARY KEY ("PERFORMANCE_SUMMARY_STAT_
ID") TABLESPACE "GUS" ENABLE,
CONSTRAINT "PERFORMANCESUMMARYSTAT_FK01" FOREIGN KEY ("PERFORMANCE_RESULT_ID")
REFERENCES "TESS"."PERFORMANCERESULT" ("PERFORMANCE_RESULT_ID") ENABLE
) TABLESPACE "GUS";
CREATE INDEX "TESS"."PERFORMANCESUMMARYSTAT_IND01" ON "TESS"."PERFORMANCESUMMARYSTAT"
("PERFORMANCE_RESULT_ID") TABLESPACE "GUS";
CREATE UNIQUE INDEX "TESS"."PK_PERFORMANCESUMMARYSTAT" ON "TESS"."PERFORMANCESUMMARYSTAT"
("PERFORMANCE_SUMMARY_STAT_ID") TABLESPACE "GUS";
CREATE TABLE "TESS"."PROTOCOL"
( "PROTOCOL_ID" NUMBER(10,0) NOT NULL ENABLE,
"PROTOCOL_TYPE_ID" NUMBER(10,0) NOT NULL ENABLE,
"SOFTWARE_TYPE_ID" NUMBER(10,0),
"HARDWARE_TYPE_ID" NUMBER(10,0),
"BIBLIOGRAPHIC_REFERENCE_ID" NUMBER(10,0),
"EXTERNAL_DATABASE_RELEASE_ID" NUMBER(4,0),
"SOURCE_ID" VARCHAR2(100),
"NAME" VARCHAR2(100) NOT NULL ENABLE,
"URI" VARCHAR2(100),
"PROTOCOL_DESCRIPTION" VARCHAR2(4000),
"HARDWARE_DESCRIPTION" VARCHAR2(500),
"SOFTWARE_DESCRIPTION" VARCHAR2(500),
"MODIFICATION_DATE" DATE NOT NULL ENABLE,
"USER_READ" NUMBER(1,0) NOT NULL ENABLE,
"USER_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"GROUP_READ" NUMBER(1,0) NOT NULL ENABLE,
"GROUP_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"OTHER_READ" NUMBER(1,0) NOT NULL ENABLE,
"OTHER_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"ROW_USER_ID" NUMBER(12,0) NOT NULL ENABLE,
"ROW_GROUP_ID" NUMBER(3,0) NOT NULL ENABLE,
"ROW_PROJECT_ID" NUMBER(4,0) NOT NULL ENABLE,
"ROW_ALG_INVOCATION_ID" NUMBER(12,0) NOT NULL ENABLE,
CONSTRAINT "PK_PROTOCOL" PRIMARY KEY ("PROTOCOL_ID") TABLESPACE "GUS" ENABLE,
CONSTRAINT "FK_BIBLIOGRAPHIC_REFERENCE_ID" FOREIGN KEY ("BIBLIOGRAPHIC_REFERENCE_ID")
REFERENCES "SRES"."BIBLIOGRAPHICREFERENCE" ("BIBLIOGRAPHIC_REFERENCE_ID") ENABLE,
CONSTRAINT "FK_PROTOCOL_HDWTYPE_OE" FOREIGN KEY ("HARDWARE_TYPE_ID")
REFERENCES "STUDY"."ONTOLOGYENTRY" ("ONTOLOGY_ENTRY_ID") ENABLE,
CONSTRAINT "FK_PROTOCOL_PRTTYPE_OE" FOREIGN KEY ("PROTOCOL_TYPE_ID")
REFERENCES "STUDY"."ONTOLOGYENTRY" ("ONTOLOGY_ENTRY_ID") ENABLE,
CONSTRAINT "FK_PROTOCOL_SFWTYPE_OE" FOREIGN KEY ("SOFTWARE_TYPE_ID")
REFERENCES "STUDY"."ONTOLOGYENTRY" ("ONTOLOGY_ENTRY_ID") ENABLE,
CONSTRAINT "PROTOCOL_FK05" FOREIGN KEY ("EXTERNAL_DATABASE_RELEASE_ID")
REFERENCES "SRES"."EXTERNALDATABASERELEASE" ("EXTERNAL_DATABASE_RELEASE_ID") ENABLE
) TABLESPACE "GUS";
CREATE TABLE "TESS"."PROTOCOLPARAM"
( "PROTOCOL_PARAM_ID" NUMBER(10,0) NOT NULL ENABLE,
"PROTOCOL_ID" NUMBER(10,0) NOT NULL ENABLE,
"NAME" VARCHAR2(100) NOT NULL ENABLE,
"DATA_TYPE_ID" NUMBER(5,0),
"UNIT_TYPE_ID" NUMBER(5,0),
"VALUE" VARCHAR2(100),
"MODIFICATION_DATE" DATE NOT NULL ENABLE,
"USER_READ" NUMBER(1,0) NOT NULL ENABLE,
"USER_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"GROUP_READ" NUMBER(1,0) NOT NULL ENABLE,
"GROUP_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"OTHER_READ" NUMBER(1,0) NOT NULL ENABLE,
"OTHER_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"ROW_USER_ID" NUMBER(12,0) NOT NULL ENABLE,
"ROW_GROUP_ID" NUMBER(3,0) NOT NULL ENABLE,
"ROW_PROJECT_ID" NUMBER(4,0) NOT NULL ENABLE,
"ROW_ALG_INVOCATION_ID" NUMBER(12,0) NOT NULL ENABLE,
CONSTRAINT "PK_PROTOCOLPARAM" PRIMARY KEY ("PROTOCOL_PARAM_ID")
TABLESPACE "GUS" ENABLE,
CONSTRAINT "FK_PROTOCOLPARAM_PROTO" FOREIGN KEY ("PROTOCOL_ID")
REFERENCES "TESS"."PROTOCOL" ("PROTOCOL_ID") ENABLE,
CONSTRAINT "FK_PROTOCOLPARAM_ONTO2" FOREIGN KEY ("UNIT_TYPE_ID")
REFERENCES "STUDY"."ONTOLOGYENTRY" ("ONTOLOGY_ENTRY_ID") ENABLE,
CONSTRAINT "PROTOCOLPARAM_FK02" FOREIGN KEY ("DATA_TYPE_ID")
REFERENCES "STUDY"."ONTOLOGYENTRY" ("ONTOLOGY_ENTRY_ID") ENABLE
) TABLESPACE "GUS";
CREATE TABLE "TESS"."PROTOCOLQCPARAM"
( "PROTOCOL_QC_PARAM_ID" NUMBER(5,0) NOT NULL ENABLE,
"PROTOCOL_ID" NUMBER(10,0) NOT NULL ENABLE,
"NAME" VARCHAR2(100) NOT NULL ENABLE,
"DATA_TYPE_ID" NUMBER(5,0),
"UNIT_TYPE_ID" NUMBER(5,0),
"VALUE" VARCHAR2(100),
"MODIFICATION_DATE" DATE NOT NULL ENABLE,
"USER_READ" NUMBER(1,0) NOT NULL ENABLE,
"USER_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"GROUP_READ" NUMBER(1,0) NOT NULL ENABLE,
"GROUP_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"OTHER_READ" NUMBER(1,0) NOT NULL ENABLE,
"OTHER_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"ROW_USER_ID" NUMBER(12,0) NOT NULL ENABLE,
"ROW_GROUP_ID" NUMBER(3,0) NOT NULL ENABLE,
"ROW_PROJECT_ID" NUMBER(4,0) NOT NULL ENABLE,
"ROW_ALG_INVOCATION_ID" NUMBER(12,0) NOT NULL ENABLE,
CONSTRAINT "PK_PROTOCOLQCPARAM" PRIMARY KEY ("PROTOCOL_QC_PARAM_ID")
TABLESPACE "GUS" ENABLE,
CONSTRAINT "FK_PROTOCOLQCPARAM_PROTO" FOREIGN KEY ("PROTOCOL_ID")
REFERENCES "RAD"."PROTOCOL" ("PROTOCOL_ID") ENABLE,
CONSTRAINT "FK_PROTOCOLQCPARAM_ONTO1" FOREIGN KEY ("DATA_TYPE_ID")
REFERENCES "STUDY"."ONTOLOGYENTRY" ("ONTOLOGY_ENTRY_ID") ENABLE,
CONSTRAINT "FK_PROTOCOLQCPARAM_ONTO2" FOREIGN KEY ("UNIT_TYPE_ID")
REFERENCES "STUDY"."ONTOLOGYENTRY" ("ONTOLOGY_ENTRY_ID") ENABLE
) TABLESPACE "GUS";
CREATE UNIQUE INDEX "TESS"."PK_PROTOCOLQCPARAM" ON "TESS"."PROTOCOLQCPARAM" ("PROTOCOL_QC_PARAM_ID") TABLESPACE "GUS";
CREATE TABLE "TESS"."PROTOCOLSTEP"
( "PROTOCOL_STEP_ID" NUMBER(10,0) NOT NULL ENABLE,
"PARENT_PROTOCOL_ID" NUMBER(10,0) NOT NULL ENABLE,
"CHILD_PROTOCOL_ID" NUMBER(10,0) NOT NULL ENABLE,
"ORDER_NUM" NUMBER(10,0) NOT NULL ENABLE,
"MODIFICATION_DATE" DATE NOT NULL ENABLE,
"USER_READ" NUMBER(1,0) NOT NULL ENABLE,
"USER_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"GROUP_READ" NUMBER(1,0) NOT NULL ENABLE,
"GROUP_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"OTHER_READ" NUMBER(1,0) NOT NULL ENABLE,
"OTHER_WRITE" NUMBER(1,0) NOT NULL ENABLE,
"ROW_USER_ID" NUMBER(12,0) NOT NULL ENABLE,
"ROW_GROUP_ID" NUMBER(3,0) NOT NULL ENABLE,
"ROW_PROJECT_ID" NUMBER(4,0) NOT NULL ENABLE,
"ROW_ALG_INVOCATION_ID" NUMBER(12,0) NOT NULL ENABLE,
CONSTRAINT "PK_PROTOCOLSTEP" PRIMARY KEY ("PROTOCOL_STEP_ID")
TABLESPACE "GUS" ENABLE,
CONSTRAINT "FK_PROTOCOLSTEP_PARENT" FOREIGN KEY ("PARENT_PROTOCOL_ID")
REFERENCES "TESS"."PROTOCOL" ("PROTOCOL_ID") ENABLE,
CONSTRAINT "FK_PROTOCOLSTEP_CHILD" FOREIGN KEY ("CHILD_PROTOCOL_ID")
REFERENCES "TESS"."PROTOCOL" ("PROTOCOL_ID") ENABLE
) TABLESPACE "GUS";
New Views (Incomplete)
CREATE OR REPLACE FORCE VIEW "DOTS"."CPGISLANDNAFEATURE"
("NA_FEATURE_ID", "NA_SEQUENCE_ID", "SUBCLASS_VIEW", "NAME", "SEQUENCE_ONTOLOGY_ID", "PARENT_ID",
"EXTERNAL_DATABASE_RELEASE_ID", "SOURCE_ID", "PREDICTION_ALGORITHM_ID", "IS_PREDICTED",
"REVIEW_STATUS_ID", "CG_COUNT", "CG_FRACTION", "CPG_COUNT", "CPG_FRACTION",
"CPG_OBSERVED_TO_EXPECTED_RATIO", "ALGORITHM_INVOCATION_ID", "MODIFICATION_DATE",
"USER_READ", "USER_WRITE", "GROUP_READ", "GROUP_WRITE", "OTHER_READ", "OTHER_WRITE",
"ROW_USER_ID", "ROW_GROUP_ID", "ROW_PROJECT_ID", "ROW_ALG_INVOCATION_ID"
) AS
SELECT NA_Feature_ID, NA_SEQUENCE_ID, SUBCLASS_VIEW, NAME, SEQUENCE_ONTOLOGY_ID, PARENT_ID,
EXTERNAL_DATABASE_RELEASE_ID, SOURCE_ID, PREDICTION_ALGORITHM_ID,IS_PREDICTED,
REVIEW_STATUS_ID,
/* number of c or g bases */ int1 as cg_count,
/* redundant: fraction of length that is c or g base */ float1 as cg_fraction,
/* number of cg dinucleotides */ int2 as cpg_count,
/* redundant: fraction of (length - 1) dinucleotides that are cg */ float2 as cpg_fraction,
/* ratio of observed cg dinucs to expected cg dinucs */ float3 as cpg_observed_to_expected_ratio,
int3 as algorithm_invocation_id,
/* GUS overhead */
modification_date,
user_read,
user_write,
group_read,
group_write,
other_read,
other_write,
row_user_id,
row_group_id,
row_project_id,
row_alg_invocation_id
FROM NAFeatureImp WHERE subclass_view = 'CpgIslandNAFeature';
CREATE OR REPLACE FORCE VIEW "RAD"."RTPCRELEMENT" ("ELEMENT_ID", "SUBCLASS_VIEW", "COMPOSITE_ELEMENT_ID",
"ARRAY_DESIGN_ID", "DESIGN_ELEMENT_TYPE_ID", "EXTERNAL_DATABASE_RELEASE_ID", "SOURCE_ID", "PHYSICAL_BIOSEQUENCE_TYPE_ID",
"POLYMER_TYPE_ID", "NAME", "DESCRIPTION", "MODIFICATION_DATE", "USER_READ", "USER_WRITE", "GROUP_READ", "GROUP_WRITE",
"OTHER_READ", "OTHER_WRITE", "ROW_USER_ID", "ROW_GROUP_ID", "ROW_PROJECT_ID", "ROW_ALG_INVOCATION_ID") AS
SELECT
RAD.ElementImp.element_id,
RAD.ElementImp.subclass_view,
RAD.ElementImp.composite_element_id,
RAD.ElementImp.array_design_id,
RAD.ElementImp.design_element_type_id,
RAD.ElementImp.external_database_release_id,
RAD.ElementImp.source_id,
RAD.ElementImp.physical_biosequence_type_id,
RAD.ElementImp.polymer_type_id,
RAD.ElementImp.string1 as name,
RAD.ElementImp.string2 as description,
modification_date,
user_read,
user_write,
group_read,
group_write,
other_read,
other_write,
row_user_id,
row_group_id,
row_project_id,
row_alg_invocation_id
FROM RAD.ElementImp
WHERE subclass_view = 'RTPCRElement'
WITH CHECK OPTION;
CREATE OR REPLACE FORCE VIEW "RAD"."RTPCRELEMENTRESULT" ("ELEMENT_RESULT_ID", "ELEMENT_ID", "SUBCLASS_VIEW",
"QUANTIFICATION_ID", "SEMI_QUANTITATIVE", "QUANTITATIVE", "QUALITATIVE", "MODIFICATION_DATE", "USER_READ",
"USER_WRITE", "GROUP_READ", "GROUP_WRITE", "OTHER_READ", "OTHER_WRITE", "ROW_USER_ID", "ROW_GROUP_ID",
"ROW_PROJECT_ID", "ROW_ALG_INVOCATION_ID") AS
SELECT
element_result_id,
element_id,
subclass_view,
quantification_id,
int1 as semi_quantitative,
float1 as quantitative,
string1 as qualitative,
modification_date,
user_read,
user_write,
group_read,
group_write,
other_read,
other_write,
row_user_id,
row_group_id,
row_project_id,
row_alg_invocation_id
FROM RAD.ElementResultImp
WHERE subclass_view = 'RTPCRElementResult'
WITH CHECK OPTION
New Materialized Views (Incomplete)
CREATE MATERIALIZED VIEW RAD.COMPOSITEELEMENTASSEMBLY_MV
TABLESPACE "GUS"
AS (
select sf.composite_element_id, a.na_sequence_id as assembly_na_sequence_id
from rad.spotfamily sf, SREs.DBREf d, dots.assembly a, dots.dbrefnasequence s
where sf.array_design_id = 1980
and sf.name = d.gene_symbol
and s. db_ref_id=d.db_ref_id
and s.na_sequence_id = a.na_sequence_id
and d.external_database_release_id = 10154
union
select ms.composite_element_id,
a.na_sequence_id as assembly_na_sequence_id
from rad.mpsstag ms,
RAD.CompositeElementDbRef c,
SREs.DBREf d,
SREs.DBREf d2,
dots.dbrefnasequence f2,
dots.assembly a
where c.db_ref_id=d.db_ref_id
and d2.external_database_release_id = 10154
and d.external_database_release_id = 11695
and c.composite_element_id = ms.composite_element_id
and f2.db_ref_id =d2.db_ref_id
and a.na_sequence_id = f2.na_sequence_id
and d.primary_identifier = d2.primary_identifier
union
select e.composite_element_id,
a.assembly_na_sequence_id
from dots.assemblysequence a,
dots.externalnasequence na,
rad.ShortOligofamily e
where a.na_sequence_id = na.na_sequence_id
and e.source_id = na.source_id
and a.assembly_na_sequence_id is not null );
CREATE INDEX "RAD"."COMPOSITEELEMENTASSEMBLY_IND01" ON "RAD"."COMPOSITEELEMENTASSEMBLY_MV"
("ASSEMBLY_NA_SEQUENCE_ID") TABLESPACE "GUS";
CREATE INDEX "RAD"."COMPOSITEELEMENTASSEMBLY_IND02" ON "RAD"."COMPOSITEELEMENTASSEMBLY_MV"
("COMPOSITE_ELEMENT_ID") TABLESPACE "GUS";
CREATE MATERIALIZED VIEW "RAD"."ELEMENTASSEMBLY_MV"
TABLESPACE "GUS"
AS (
select e.element_id, a.na_sequence_id as assembly_na_sequence_id
from rad.rtpcrElement e,
RAD.ElementDbRef c,
SREs.DBREf d,
SREs.DBREf d2,
dots.dbrefnasequence f2,
dots.assembly a
where c.db_ref_id=d.db_ref_id
and d.external_database_release_id=11695
and c.element_id = e.element_id
and f2.db_ref_id =d2.db_ref_id
and d2.external_database_release_id = 10154
and a.na_sequence_id = f2.na_sequence_id
and d.primary_identifier = d2.primary_identifier
/* Genbank accessions */
UNION
select e.element_id , a.assembly_na_sequence_id
from dots.assemblysequence a,dots.externalnasequence na, rad.Spot e
where na.na_sequence_id = a.na_sequence_id
and e.source_id = na.source_id
and na.external_database_release_id in (2,78,6573,6519)
and e.external_database_release_id in (2,78,6573,6519)
and a.assembly_na_sequence_id is not null
UNION
select e.element_id , a.assembly_na_sequence_id
from dots.assemblysequence a, dots.externalnasequence na,
rad.Spot e, rad.elementannotation ea
where na.na_sequence_id = a.na_sequence_id
and na.source_id = ea.value
and lower(ea.name) like 'genbank accession'
and ea.element_id = e.element_id
and na.external_database_release_id in (2,78,6573,6519)
and a.assembly_na_sequence_id is not null
/* dbEST IMAGE ID information */
UNION
select e.element_id , a.assembly_na_sequence_id
from dots.assemblysequence a, dots.est de,
dots.clone c, rad.Spot e
where de.na_sequence_id = a.na_sequence_id
and de.clone_id = c.clone_id
and c.image_id = e.source_id
and e.external_database_release_id = 13
and a.assembly_na_sequence_id is not null
UNION
select e.element_id , a.assembly_na_sequence_id
from dots.assemblysequence a, dots.est de,
dots.clone c, rad.Spot e , rad.elementannotation ea
where de.na_sequence_id = a.na_sequence_id
and de.clone_id = c.clone_id
and ea.value = c.image_id
and ea.element_id = e.element_id
and lower(ea.name) like 'old image id'
and e.sequence_verified = 1
and a.assembly_na_sequence_id is not null
);
CREATE INDEX "RAD"."ELEMENTASSEMBLY_IND01" ON "RAD"."ELEMENTASSEMBLY_MV" ("ASSEMBLY_NA_SEQUENCE_ID") TABLESPACE "GUS";
CREATE INDEX "RAD"."ELEMENTASSEMBLY_IND02" ON "RAD"."ELEMENTASSEMBLY_MV" ("ELEMENT_ID") TABLESPACE "GUS";
CREATE MATERIALIZED VIEW RAD.ELEMENTASSEMBLYACCESSION_MV
TABLESPACE "GUS"
AS select s.element_id , a.assembly_na_sequence_id
from dots.assemblysequence a,dots.externalnasequence na, rad3.Spot s,
rad3.ElementAnnotation ea
where ea.element_id=s.element_id
and na.na_sequence_id = a.na_sequence_id
and ea.name='Primary_3p_GenBank_Accession'
and ea.value = na.source_id
and na.external_database_release_id in (2,78,6573,6519)
and a.assembly_na_sequence_id is not null
UNION
select s.element_id , a.assembly_na_sequence_id
from dots.assemblysequence a,dots.externalnasequence na, rad3.Spot s,
rad3.ElementAnnotation ea
where ea.element_id=s.element_id
and na.na_sequence_id = a.na_sequence_id
and ea.name='Primary_5p_GenBank_Accession'
and ea.value = na.source_id
and na.external_database_release_id in (2,78,6573,6519)
and a.assembly_na_sequence_id is not null;
Note: Schemas of materialized views as tables (code not executed)
CREATE TABLE "RAD"."COMPOSITEELEMENTASSEMBLY_MV"
( "COMPOSITE_ELEMENT_ID" NUMBER(10,0),
"ASSEMBLY_NA_SEQUENCE_ID" NUMBER(10,0)
) TABLESPACE "GUS";
CREATE TABLE "RAD"."ELEMENTASSEMBLYACCESSION_MV"
( "ELEMENT_ID" NUMBER(10,0),
"ASSEMBLY_NA_SEQUENCE_ID" NUMBER(10,0)
) TABLESPACE "GUS";
CREATE TABLE "RAD"."ELEMENTASSEMBLY_MV"
( "ELEMENT_ID" NUMBER(10,0),
"ASSEMBLY_NA_SEQUENCE_ID" NUMBER(10,0)
) TABLESPACE "GUS";
Back to Proposed Schema Changes
