REM Copyright (c) 2003 Konrad Buessow

REM This program is free software; you can redistribute and/or modify 
REM it under the terms of the GNU General Public Licence as published by
REM the Free Software Foundation; eiher version 2, or (at your option) any
REM later version.

REM This program is distributed in the hope that it will be useful, but 
REM WITHOUT ANY WARRANTY; without even the implied warranty of 
REM MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
REM GNU General Public Licence for more details. 

REM You should have received a copy of the GNU General Public Licence 
REM along with this program; if not, write to the Free Software 
REM Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA

--
--ER/Studio 4.2 SQL Code Generation
-- Project :      Model1.DM1
--
-- Date Created : Wednesday, October 23, 2002 19:01:23
-- Target DBMS : Oracle 8experiment
--


-- 
-- TABLE: ACTION_TYPES 
--

CREATE TABLE ACTION_TYPES(
    ACTIONTYPEID      NUMBER(11, 0)    NOT NULL,
    MNEMOTIC          VARCHAR2(25)     NOT NULL,
    DETAIL_TABLE      VARCHAR2(50),
    PLABOCONTENTID    NUMBER(11, 0)    NOT NULL,
    CONSTRAINT ACTION_TYPES_PK5999608043228 PRIMARY KEY (ACTIONTYPEID)
) 
;


-- 
-- TABLE: AMINO_ACIDS 
--

CREATE TABLE AMINO_ACIDS(
    ID                  NUMBER(11, 0)     NOT NULL,
    NAME                VARCHAR2(20)      NOT NULL,
    THREE_LETTER        VARCHAR2(3)       NOT NULL,
    ONE_LETTER          VARCHAR2(1)       NOT NULL,
    AVG_WEIGHT          NUMBER(20, 10),
    FIRST_ISOTOPIC_W    NUMBER(20, 10),
    CONSTRAINT AMINO_ACIDS_PK71002815758152 PRIMARY KEY (ID)
) 
;


-- 
-- TABLE: ANTIBIOTICS 
--

CREATE TABLE ANTIBIOTICS(
    ID               NUMBER(11, 0)       NOT NULL,
    SHORTNAME        VARCHAR2(50),
    LONGNAME         VARCHAR2(50),
    DESCRIPTION      VARCHAR2(50),
    CONCENTRATION    DOUBLE PRECISION    DEFAULT 0,
    CONSTRAINT SYS_C006259 PRIMARY KEY (ID)
) 
;


-- 
-- TABLE: BARCODE 
--

CREATE TABLE BARCODE(
    BARCODE           NUMBER(10, 0)    NOT NULL,
    CLASS             NUMBER(2, 0),
    PLATESBOXESID     NUMBER(11, 0),
    PLABO_ADMINID     NUMBER(11, 0),
    PLATE_NUMBER      NUMBER(11, 0),
    BC_DATE           DATE,
    EXPERIMENTATOR    NUMBER(11, 0),
    RECIPIENT         NUMBER(11, 0),
    COPY              NUMBER(5, 0),
    CONSTRAINT BC_BC_PRIKEY PRIMARY KEY (BARCODE)
) 
;


COMMENT ON COLUMN BARCODE.BARCODE IS 'TP 3 Barcode'
;
-- 
-- TABLE: BUFFERS 
--

CREATE TABLE BUFFERS(
    BUFFER_ID            NUMBER(11, 0)    NOT NULL,
    BUFFERNAME           VARCHAR2(50),
    BUFFERCOMPOSITION    CLOB,
    DATEENTERED          DATE             DEFAULT TO_DATE(SYSDATE),
    BUFFERCLASS          NUMBER(3, 0)     DEFAULT 0,
    PH                   NUMBER(11, 0),
    PH_TEMPERATURE       NUMBER(11, 0),
    ADJUSTPH_CHEMID      NUMBER(11, 0),
    CONSTRAINT SYS_C006261 PRIMARY KEY (BUFFER_ID)
) 
;


-- 
-- TABLE: CHROMAT 
--

CREATE TABLE CHROMAT(
    FPLC_ID              NUMBER(11, 0)       DEFAULT null NOT NULL,
    DATE_                DATE                DEFAULT TO_DATE(SYSDATE) NOT NULL,
    COLUMN_              NUMBER(11, 0),
    COLUMNVOLUME         DOUBLE PRECISION,
    BUFFERA              NUMBER(11, 0),
    BUFFERB              NUMBER(11, 0),
    SAMPLEDESCRIPTION    CLOB,
    CLONEID              NUMBER(11, 0),
    FRACTIONSIZE         DOUBLE PRECISION,
    SAMPLEVOLUME         DOUBLE PRECISION,
    AU                   DOUBLE PRECISION,
    CHARTSPEED           DOUBLE PRECISION,
    FLOWRATE             DOUBLE PRECISION,
    EXPERIMENT           NUMBER(11, 0),
    PMSTEP_ID            NUMBER(11, 0),
    TEMPERATURE          NUMBER(3, 1),
    EXPERIMENTATOR_ID    NUMBER(11, 0)       NOT NULL,
    CONSTRAINT SYS_C006262 PRIMARY KEY (FPLC_ID)
) 
;

-- 
-- TABLE: CLONE_SEQDB 
--

CREATE TABLE CLONE_SEQDB(
    CLONE_SEQDB_ID    NUMBER(11, 0)    NOT NULL,
    CLONEID           NUMBER(11, 0)    NOT NULL,
    ACC               VARCHAR2(50),
    GI                NUMBER(11, 0),
    END3OR5           NUMBER(1, 0),
    WASHU_TRACE       VARCHAR2(50),
    CONSTRAINT CLONE_SEQDB_END3OR5 CHECK (end3or5 in (3,5)),
    CONSTRAINT SYS_C006321 PRIMARY KEY (CLONE_SEQDB_ID)
) 
;


-- 
-- TABLE: CLONEIMAGE 
--

CREATE TABLE CLONEIMAGE(
    CLONEIMAGEID             NUMBER(11, 0)    NOT NULL,
    CLONEID                  NUMBER(11, 0)    DEFAULT NULL,
    SEQUENCED                NUMBER(1, 0),
    SEQMATCHESEST            NUMBER(1, 0),
    WRONGCLONE               NUMBER(1, 0),
    ORDERED                  NUMBER(1, 0),
    ORDEREDDATE              DATE             DEFAULT TO_DATE(SYSDATE),
    RZPD_IMAGE_LIB           NUMBER(10, 0),
    RZPD_CLONE_REQUEST_NO    NUMBER(11, 0),
    RZPD_AVAILABLE           NUMBER(1, 0),
    CONSTRAINT SYS_C006263 PRIMARY KEY (CLONEIMAGEID)
) 
;


-- 
-- TABLE: CLONEINDEX 
--

CREATE TABLE CLONEINDEX(
    CLONEID           NUMBER(11, 0)    NOT NULL,
    INDEXCLONENAME    VARCHAR2(50)     NOT NULL,
    RZPDCLONE         NUMBER(1, 0)     DEFAULT null,
    LIGCLONE          NUMBER(1, 0),
    SKCLONE           NUMBER(1, 0),
    CONSTRAINT CLONEINDEX_NAME_NN CHECK ("INDEXCLONENAME" IS NOT NULL),
    CONSTRAINT SYS_C006265 PRIMARY KEY (CLONEID)
) 
;

-- 
-- TABLE: CLONELIGATION 
--

CREATE TABLE CLONELIGATION(
    LIGCLONEID             NUMBER(11, 0)     NOT NULL,
    TRANSFORMANT           NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    CLONEID                NUMBER(11, 0)     DEFAULT NULL NOT NULL,
    LIGCLONENAME           VARCHAR2(50),
    IDENTICALTOEXPECTED    NUMBER(1, 0),
    COMMENT_               VARCHAR2(2000),
    SILENT_MUTATION        NUMBER(1, 0),
    POLYMORPH_TEMPLATE     NUMBER(1, 0),
    TRAFO_ID               NUMBER(11, 0)     NOT NULL,
    SEQ_HOW                NUMBER(1, 0)      NOT NULL,
    SEQ_RESULT             NUMBER(2, 0)      DEFAULT 7 NOT NULL,
    INSERT_FLAG            NUMBER(1, 0),
    INSERTSIZE_BP          NUMBER(6, 0),
    EXPERIMENTATOR_ID      NUMBER(11, 0)     NOT NULL,
    DATE_IN                DATE              DEFAULT sysdate NOT NULL,
    CONSTRAINT CLONELIG_SEQHOW_CHECK CHECK (seq_how in (1,2,3)),
    CONSTRAINT CLONELIG_SEQRESULT_CHECK CHECK (seq_result in (1,2,3,4,5,6,7)),
    CONSTRAINT CLONELIG_CLONEID_NN CHECK ("CLONEID" IS NOT NULL),
    CONSTRAINT SYS_C006272 PRIMARY KEY (LIGCLONEID)
) 
;


-- 
-- TABLE: CLONEOTHER 
--

CREATE TABLE CLONEOTHER(
    CLONEID              NUMBER(11, 0)    DEFAULT NULL NOT NULL,
    CLONEOTHERID         NUMBER(11, 0)    NOT NULL,
    CLONENAME            VARCHAR2(255),
    OBTAINEDFROM         VARCHAR2(50),
    MADEBY               VARCHAR2(50),
    DESCRIPTION          CLOB,
    DATE_                DATE             DEFAULT TO_DATE(SYSDATE),
    STRAINID             NUMBER(11, 0)    DEFAULT NULL,
    VECTORID             NUMBER(11, 0)    DEFAULT NULL,
    HELPERPLASMIDID      NUMBER(11, 0)    DEFAULT NULL,
    EMPTY_VECTOR         NUMBER(1, 0),
    EMPTY_STRAIN         NUMBER(1, 0),
    EXPERIMENTATOR_ID    NUMBER(11, 0)    NOT NULL,
    CONSTRAINT CO_CLONEID_PRIKEY PRIMARY KEY (CLONEID)
) 
;


-- 
-- TABLE: CLONESRZPD 
--

CREATE TABLE CLONESRZPD(
    LIBNO        NUMBER(5, 0)     DEFAULT NULL NOT NULL,
    ROW_         VARCHAR2(1)      NOT NULL,
    COLUMN_      NUMBER(5, 0)     NOT NULL,
    PLATE        NUMBER(5, 0)     NOT NULL,
    CLONEID      NUMBER(11, 0)    DEFAULT NULL NOT NULL,
    CLONENAME    VARCHAR2(50)     NOT NULL,
    ID           NUMBER(11, 0),
    CONSTRAINT CR_CNAME_NN CHECK ("CLONENAME" IS NOT NULL),
    CONSTRAINT NN_CLONEID CHECK ("CLONEID" IS NOT NULL),
    CONSTRAINT SYS_C006296 PRIMARY KEY (LIBNO,ROW_,COLUMN_,PLATE)
) 
;


-- 
-- TABLE: COLUMNS_ 
--

CREATE TABLE COLUMNS_(
    COLUMN_ID          NUMBER(11, 0)       NOT NULL,
    COLUMNNAME         VARCHAR2(50),
    EMPTYCOLUMNTYPE    NUMBER(11, 0)       DEFAULT NULL,
    MEDIUM             NUMBER(11, 0)       DEFAULT NULL,
    ELUTIONVOLUME      DOUBLE PRECISION    DEFAULT null,
    VOIDVOLUME         DOUBLE PRECISION    DEFAULT null,
    TOTALVOLUME        DOUBLE PRECISION    DEFAULT Null,
    CONSTRAINT SYS_C006327 PRIMARY KEY (COLUMN_ID)
) 
;


-- 
-- TABLE: DNA_ACTION 
--

CREATE TABLE DNA_ACTION(
    DNAACTIONID         NUMBER    NOT NULL,
    TIMESTAMP           DATE      NOT NULL,
    PLABOID             NUMBER    NOT NULL,
    EXPERIMENTATORID    NUMBER,
    ACTIONTYPEID        NUMBER,
    CONSTRAINT DNA_ACTIONID PRIMARY KEY (DNAACTIONID)
) 
;


-- 
-- TABLE: DNADIGEST 
--

CREATE TABLE DNADIGEST(
    DNA_ACTIONID    NUMBER(11, 0)    NOT NULL,
    FIRSTENZYME     NUMBER(11, 0),
    SECONDENZYME    NUMBER(11, 0),
    CONSTRAINT DNADIGEST_PK5999604719479 PRIMARY KEY (DNA_ACTIONID)
) 
;


-- 
-- TABLE: DNALANES 
--

CREATE TABLE DNALANES(
    ID                           NUMBER(11, 0)       NOT NULL,
    PCRID                        NUMBER(11, 0)       DEFAULT 0,
    DESCRIPTION                  VARCHAR2(50),
    LOADED                       DOUBLE PRECISION    DEFAULT 5,
    AGAROSEGELID                 NUMBER(11, 0)       DEFAULT NULL,
    LANENO                       NUMBER(5, 0)        DEFAULT 0,
    ISMARKER                     NUMBER(1, 0),
    MARKERID                     NUMBER(11, 0)       DEFAULT 0,
    CLONEID                      NUMBER(11, 0),
    SUBGEL                       VARCHAR2(1),
    EXPERIMENTGROUPIDENTIFIER    VARCHAR2(32),
    PLATESBOXESID                NUMBER(11, 0),
    CONSTRAINT SYS_C006341 PRIMARY KEY (ID)
) 
;


-- 
-- TABLE: DNAPURIFY 
--

CREATE TABLE DNAPURIFY(
    DNA_ACTIONID        NUMBER(11, 0)    NOT NULL,
    CLEANINGMETHODID    NUMBER(11, 0),
    CONSTRAINT DNAPURIFY_PK4999604512131 PRIMARY KEY (DNA_ACTIONID)
) 
;


-- 
-- TABLE: DNAQUANTIFICATION 
--

CREATE TABLE DNAQUANTIFICATION(
    DNA_ACTIONID     NUMBER(11, 0)    NOT NULL,
    OD260            NUMBER(11, 5),
    CONCENTRATION    NUMBER(11, 5),
    CONSTRAINT DNAQUANTIFICA_PK2999608738188 PRIMARY KEY (DNA_ACTIONID)
) 
;


-- 
-- TABLE: EMPTYCOLUMNTYPES 
--

CREATE TABLE EMPTYCOLUMNTYPES(
    ID          NUMBER(11, 0)       NOT NULL,
    TYPE_       VARCHAR2(50),
    DIAMETRE    DOUBLE PRECISION    DEFAULT 0,
    HEIGHT      DOUBLE PRECISION    DEFAULT 0,
    CONSTRAINT SYS_C006350 PRIMARY KEY (ID)
) 
;


-- 
-- TABLE: EXPERIMENTATOR 
--

CREATE TABLE EXPERIMENTATOR(
    ID                 NUMBER(11, 0)    NOT NULL,
    SHORTNAME          VARCHAR2(10)     NOT NULL,
    LONGNAME           VARCHAR2(50)     NOT NULL,
    TEILPROJEKT        NUMBER(2, 0),
    LOGINNAME          VARCHAR2(64),
    LABORATORY         VARCHAR2(255),
    TOMCAT_PASSWORD    VARCHAR2(20),
    TOMCAT_ROLE        NUMBER(3, 0),
    SEC_KEY            NUMBER(2, 0),
    CONSTRAINT SYS_C006354 PRIMARY KEY (ID)
) 
;


-- 
-- TABLE: EXPERIMENTS 
--

CREATE TABLE EXPERIMENTS(
    EXP_ID            NUMBER(11, 0)    NOT NULL,
    DESCRIPTION       VARCHAR2(255),
    EXPERIMENTATOR_ID NUMBER(11, 0)    NOT NULL,
    EXP_DATE          DATE             DEFAULT TO_DATE(SYSDATE),
    LABBOOK_NO        NUMBER(6, 0),
    CONSTRAINT EXPERIMENT_ID_PRIKEY PRIMARY KEY (EXP_ID)
) 
;


-- 
-- TABLE: EXPRESSIONCLONES 
--

CREATE TABLE EXPRESSIONCLONES(
    CLONEID                    NUMBER(11, 0)       NOT NULL,
    ID                         NUMBER(11, 0),
    INSERTKB                   DOUBLE PRECISION,
    FRAME                      DOUBLE PRECISION,
    EXPRPEPSIZE                DOUBLE PRECISION,
    PREDICTEDPEPTIDESIZE       DOUBLE PRECISION,
    PREDPEPAA                  DOUBLE PRECISION,
    WHOLEPROTSTRENGTH          NUMBER(5, 0)        DEFAULT Null,
    SOLUBLEPROTSTRENGTH        NUMBER(5, 0)        DEFAULT Null,
    WHOLENINTASTRENGTH         NUMBER(5, 0)        DEFAULT Null,
    SOLUBLENINTASTRENGTH       NUMBER(5, 0)        DEFAULT Null,
    DATE_                      DATE                DEFAULT TO_DATE(SYSDATE),
    SOLUBLESTRENGTH_GBUFFER    NUMBER(5, 0),
    COMMENT_                   VARCHAR2(2000),
    INDUCTION_TEMPERATURE      NUMBER(5, 0),
    CONSTRAINT SYS_C006357 PRIMARY KEY (CLONEID)
) 
;

-- 
-- TABLE: FLAG_COL 
--

CREATE TABLE FLAG_COL(
    ID                  NUMBER(9, 0)     NOT NULL,
    TABLE_NAME          VARCHAR2(50),
    COL_NAME            VARCHAR2(50),
    FLAG_VALUE          NUMBER(5, 0),
    FLAG_DESCRIPTION    VARCHAR2(255),
    CONSTRAINT FLAGCOL_PRIKEY PRIMARY KEY (ID)
) 
;


-- 
-- TABLE: GENBANKENTRIES 
--

CREATE TABLE GENBANKENTRIES(
    ID                  NUMBER(11, 0)       NOT NULL,
    GENBANKACC          VARCHAR2(50)        NOT NULL,
    GENENAME            VARCHAR2(255),
    LENGTH_             DOUBLE PRECISION    DEFAULT null NOT NULL,
    CELLLOC             VARCHAR2(50),
    COMPLETECDS         NUMBER(5, 0),
    SPECIESNO           NUMBER(11, 0),
    TYPE_               VARCHAR2(50)        DEFAULT null NOT NULL,
    CDSSTART            DOUBLE PRECISION,
    CDSEND              DOUBLE PRECISION,
    MRNAAVAILABLE       NUMBER(1, 0)        DEFAULT null,
    GENEDESCRIPTION     VARCHAR2(255)       NOT NULL,
    SEQ_DB              VARCHAR2(50)        NOT NULL,
    GI                  NUMBER(11, 0),
    FILENAME            VARCHAR2(255),
    ORF_FILE            VARCHAR2(255),
    VERSION             NUMBER(5, 0),
    LAST_UPDATE         DATE,
    MOL_WEIGHT          DOUBLE PRECISION,
    INTERESTING_0TO2    NUMBER(1, 0),
    SEQDNA_ID           NUMBER(11, 0),
    SEQPEP_ID           NUMBER(11, 0),
    DATE_IN             DATE                DEFAULT sysdate NOT NULL,
    DATE_UPDATE         DATE                DEFAULT sysdate NOT NULL,
    ORFER_PROBLEM       NUMBER(3, 0),
    CONSTRAINT NN_GBENTR_ID CHECK ("ID" IS NOT NULL),
    CONSTRAINT GBCHECK_CDSSTART CHECK (cdsstart > 0),
    CONSTRAINT GBCHECK_CDSEND CHECK (cdsend > 0),
    CONSTRAINT GENBANKENTRIE_CK5995387438438 CHECK (INTERESTING_0TO2 IN (0,1,2)),
    CONSTRAINT GENBANKENTRIE_PK8995447983637 PRIMARY KEY (ID)
) 
;


-- 
-- TABLE: IMAGE_LIB 
--

CREATE TABLE IMAGE_LIB(
    ID_LIB       NUMBER(10, 0)    NOT NULL,
    LIBRARY      VARCHAR2(150)    NOT NULL,
    LIB_URL      VARCHAR2(150),
    VECTOR       VARCHAR2(150),
    HOST         VARCHAR2(150),
    VECTOR_ID    NUMBER(11, 0),
    DUMMY        NUMBER(11, 0),
    CONSTRAINT IMAGELIB_IDLIB_PRIMKEY PRIMARY KEY (ID_LIB)
) 
;

-- 
-- TABLE: LIBRARIES 
--

CREATE TABLE LIBRARIES(
    LIBNO            NUMBER(5, 0)     DEFAULT 0 NOT NULL,
    ENTERDATE        DATE,
    LIBNAME          VARCHAR2(255),
    SHORTNAME        VARCHAR2(50),
    PREFIX           VARCHAR2(50),
    DESCRIPTION      CLOB,
    VECTOR_ID        NUMBER(11, 0),
    HELPER_VEC_ID    NUMBER(11, 0),
    STRAIN_ID        NUMBER(11, 0),
    RE1              NUMBER(11, 0),
    RE2              NUMBER(11, 0),
    DIRECTIONAL      NUMBER(1, 0),
    CONSTRAINT SYS_C006384 PRIMARY KEY (LIBNO)
) 
;


-- 
-- TABLE: LIGATION 
--

CREATE TABLE LIGATION(
    LIGATIONID               NUMBER(11, 0)       NOT NULL,
    DATE_                    DATE                DEFAULT TO_DATE(SYSDATE),
    INSERTCLONEID            NUMBER(11, 0)       DEFAULT NULL,
    PCREXPID                 NUMBER(11, 0)       DEFAULT null,
    FOUNDOKCLONE             NUMBER(1, 0)        DEFAULT null,
    INSERTCLONEWRONG         NUMBER(1, 0)        DEFAULT null,
    PREDICTEDPEPTIDESIZE     DOUBLE PRECISION,
    RE1VECTOR                NUMBER(11, 0),
    RE2VECTOR                NUMBER(11, 0),
    RE1INSERT                NUMBER(11, 0),
    RE2INSERT                NUMBER(11, 0),
    VECTORID                 NUMBER(11, 0),
    TRANSFORMANTSTESTED      NUMBER(5, 0)        DEFAULT null,
    TRANSFORMANTSINSERT      NUMBER(5, 0)        DEFAULT null,
    COMMENT_                 VARCHAR2(2000),
    INSERT_IS_RE_FRAGMENT    NUMBER(1, 0),
    EXPERIMENTATORID         NUMBER(11, 0)       NOT NULL,
    CONSTRAINT LIGID_PRIMKEY PRIMARY KEY (LIGATIONID)
) 
;


-- 
-- TABLE: OLIGOCLEANINGKITS 
--

CREATE TABLE OLIGOCLEANINGKITS(
    ID              NUMBER(11, 0)    NOT NULL,
    MANUFACTURER    VARCHAR2(50),
    KIT_NAME        VARCHAR2(50),
    COMMENTS        CLOB,
    CONSTRAINT SYS_C006530 PRIMARY KEY (ID)
) 
;


-- 
-- TABLE: PAGE 
--

CREATE TABLE PAGE(
    PAGEID               NUMBER(11, 0)       NOT NULL,
    PERCPAA              DOUBLE PRECISION,
    DATE_                DATE                DEFAULT sysdate,
    FPLCRUN_ID           NUMBER(11, 0),
    EXPERIMENTID         NUMBER(11, 0),
    PERCPAA_HIGH         DOUBLE PRECISION,
    EXPERIMENTATOR_ID    NUMBER(11, 0)       NOT NULL,
    JPG_ID               NUMBER(11, 0),
    DESCRIPTION          VARCHAR2(255),
    CONSTRAINT PAGE_PRIKEY PRIMARY KEY (PAGEID)
) 
;


-- 
-- TABLE: PAGELANES 
--

CREATE TABLE PAGELANES(
    ID             NUMBER(11, 0)       NOT NULL,
    PAGE           NUMBER(11, 0)       DEFAULT NULL NOT NULL,
    LANENO         NUMBER(5, 0)        DEFAULT null,
    VOLUME         DOUBLE PRECISION    DEFAULT 0,
    CLONEID        NUMBER(11, 0),
    DESCRIPTION    VARCHAR2(255),
    CONSTRAINT NN_PAGEID CHECK ("PAGE" IS NOT NULL),
    CONSTRAINT SYS_C006537 PRIMARY KEY (ID)
) 
;


-- 
-- TABLE: PCR_ACTION 
--

CREATE TABLE PCR_ACTION(
    DNA_ACTIONID       NUMBER    NOT NULL,
    PCREXPERIMENTID    NUMBER    NOT NULL
) 
;


-- 
-- TABLE: PCREXPERIMENT 
--

CREATE TABLE PCREXPERIMENT(
    PCREXPERIMENTID        NUMBER(11, 0)       NOT NULL,
    ENDPRIMERID5           NUMBER(11, 0)       NOT NULL,
    ENDPRIMERID3           NUMBER(11, 0)       NOT NULL,
    TEMPLATECLONEID        NUMBER(11, 0),
    PCRPROTOCOLID          NUMBER(11, 0),
    PRODUCTAMOUNT          NUMBER(3, 0)        DEFAULT Null,
    PRODUCTSIZE            DOUBLE PRECISION,
    DATE_                  DATE                DEFAULT TO_DATE(SYSDATE) NOT NULL,
    CLEANED_WITH           NUMBER(11, 0),
    TEMPLATETYPE           NUMBER(3, 0),
    EXPERIMENTATORID       NUMBER(11, 0)       NOT NULL,
    ASSUMEDTOBEOK          NUMBER(1, 0),
    SERIAL_NUMBER          NUMBER(11, 0),
    OD260                  NUMBER(4, 3),
    CONCENTRATION          NUMBER(7, 3),
    GENERIC_TEMPLATE_ID    NUMBER(11, 0),
    TEMPLATE_PCRID         NUMBER(11, 0),
    CONSTRAINT SYS_C006538 PRIMARY KEY (PCREXPERIMENTID)
) 
;


-- 
-- TABLE: PCRPRIMER 
--

CREATE TABLE PCRPRIMER(
    ID                  NUMBER(11, 0)    NOT NULL,
    PRIMERNAME          VARCHAR2(100),
    PRPRIMERNO          NUMBER(11, 0)    DEFAULT Null,
    GENBANKACC          VARCHAR2(50),
    PROTACC             VARCHAR2(50),
    PRIMERSEQ           VARCHAR2(50)     NOT NULL,
    UPPERLOWERSTRAND    VARCHAR2(50),
    DATE_               DATE             DEFAULT TO_DATE(SYSDATE),
    APRIMEMOD           NUMBER(11, 0)    DEFAULT NULL,
    MANUFACTURERID      NUMBER(11, 0)    DEFAULT NULL,
    VECTORPRIMER        NUMBER(1, 0)     DEFAULT 0 NOT NULL,
    CLONEID             NUMBER(11, 0),
    ENZYMEID            NUMBER(11, 0),
    EXPERIMENTATOR      NUMBER(11, 0),
    CONSTRAINT PCRPRIMER_CK51000881161957 CHECK (vectorprimer in (0,1,-1)),
    CONSTRAINT SYS_C006542 PRIMARY KEY (ID)
) 
;


-- 
-- TABLE: PCRPROT 
--

CREATE TABLE PCRPROT(
    PCRPRID       NUMBER(11, 0)    NOT NULL,
    SEQDNA_ID     NUMBER(11, 0)    NOT NULL,
    PR_STARTBP    NUMBER(11, 0)    NOT NULL,
    PR_STOPBP     NUMBER(11, 0)    NOT NULL,
    G_STARTBP     NUMBER(11, 0)    NOT NULL,
    G_STOPBP      NUMBER(11, 0)    NOT NULL,
    CONSTRAINT PCRPROT_CK61001063097100 CHECK (PR_STARTBP + 10 < PR_STOPBP),
    CONSTRAINT PCRPROT_PK41031663691089 PRIMARY KEY (PCRPRID,SEQDNA_ID)
) 
;


-- 
-- TABLE: PCRPROTOCOL 
--

CREATE TABLE PCRPROTOCOL(
    ID                      NUMBER(11, 0)       NOT NULL,
    CYCLES                  NUMBER(5, 0)        DEFAULT 0,
    DENATURETEMP            DOUBLE PRECISION    DEFAULT 94,
    ANNEALTEMP              DOUBLE PRECISION    DEFAULT 0,
    ELONGATETEMP            DOUBLE PRECISION    DEFAULT 72,
    FINALEXTENSIONTEMP      DOUBLE PRECISION    DEFAULT 72,
    HOLD                    NUMBER(1, 0),
    HOLD_AT                 DOUBLE PRECISION    DEFAULT 12,
    DNAPOLYMERASEID         NUMBER(11, 0)       DEFAULT NULL,
    PCRMACHINEID            NUMBER(11, 0)       DEFAULT NULL,
    EXPERIMENTIDENTIFIER    VARCHAR2(50),
    ELONGATETIME_           DATE,
    ANNEALTIME_             DATE,
    FINALEXTENSIONTIME_     DATE,
    STARTDENATURETIME_      DATE,
    DENATURETIME_           DATE,
    CONSTRAINT SYS_C006553 PRIMARY KEY (ID)
) 
;


-- 
-- TABLE: PLABO_CONTENT 
--

CREATE TABLE PLABO_CONTENT(
    CONTENT_ID      NUMBER(11, 0)    NOT NULL,
    CONTENT_NAME    VARCHAR2(50)     NOT NULL,
    CONSTRAINT PLA_CON_PRI PRIMARY KEY (CONTENT_ID)
) 
;


-- 
-- TABLE: PLABO_LOC 
--

CREATE TABLE PLABO_LOC(
    PLABO_LOC_ID           NUMBER(11, 0)    NOT NULL,
    RZPD_LIB               NUMBER(11, 0),
    PLATE_NUMBER           NUMBER(11, 0),
    COPY_Q                 NUMBER(11, 0),
    FREEZER                NUMBER(11, 0),
    DOOR                   NUMBER(11, 0),
    CHEST_OF_DRAWERS       NUMBER(11, 0),
    CHEST_ROW              VARCHAR2(1),
    CHEST_COL              NUMBER(4, 0),
    COMMENT_               VARCHAR2(50),
    PLATESBOXESADMIN_ID    NUMBER(11, 0),
    FINDABILITY            NUMBER(2, 0)     DEFAULT 1 NOT NULL,
    CONSTRAINT PL_THEKEY PRIMARY KEY (PLABO_LOC_ID)
) 
;


-- 
-- TABLE: PLATESBOXES 
--

CREATE TABLE PLATESBOXES(
    PLATESBOXESID         NUMBER(11, 0)    NOT NULL,
    PLATE_NUMBER          NUMBER(11, 0)    NOT NULL,
    PLATESBOXESADMINID    NUMBER(11, 0)    NOT NULL,
    COLUMN_               NUMBER(5, 0),
    CLONEID               NUMBER(11, 0),
    DATE_                 DATE,
    DNAPREPID             NUMBER(11, 0),
    PCRPRIMERID           NUMBER(11, 0),
    PCRPRODUCTID          NUMBER(11, 0),
    GLYCEROL_ID           NUMBER(11, 0),
    ROW_                  VARCHAR2(1),
    PROTEIN_ID            NUMBER(11, 0),
    VOLUME_UL             NUMBER(8, 5),
    CONCENTRATION         NUMBER(8, 5),
    LIGATIONID            NUMBER(11, 0),
    CONSTRAINT PB_COL_CHECK CHECK ((column_ >= 1) and (column_ <= 24)),
    CONSTRAINT PB_ROW_CHECK CHECK (row_ in ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P')),
    CONSTRAINT PLABO_ID_PRIMKEY PRIMARY KEY (PLATESBOXESID)
) 
;


-- 
-- TABLE: PLATESBOXESADMIN 
--

CREATE TABLE PLATESBOXESADMIN(
    PLATESBOXESADMINID    NUMBER(11, 0)    NOT NULL,
    PLATE_SUFFIX          VARCHAR2(50),
    CONTENTID             NUMBER(5, 0),
    PLATENAME             VARCHAR2(255),
    DESCRIPTION           VARCHAR2(255),
    DATE_                 DATE             DEFAULT sysdate,
    MAXROW                VARCHAR2(1)      DEFAULT 'H',
    MAXCOL                NUMBER(5, 0)     DEFAULT 12,
    CONTENTFIELDNAME      VARCHAR2(50)     DEFAULT 'CLONEID' NOT NULL,
    CONSTRAINT PLABOADMIN_PRIKEY PRIMARY KEY (PLATESBOXESADMINID),
		CONSTRAINT PLATESBOXESAD_UK UNIQUE(PLATE_SUFFIX)
) 
;


-- 
-- TABLE: PROT_EXPRESS 
--

CREATE TABLE PROT_EXPRESS(
    PROTEXPRESS_ID                NUMBER(10, 0)       NOT NULL,
    CLONEID                       NUMBER(10, 0)       NOT NULL,
    DATE_PE                       DATE                DEFAULT sysdate,
    EXP_ID                        NUMBER(10, 0),
    PAGE_ID                       NUMBER(10, 0)       DEFAULT null,
    PROTOCOL_ID                   NUMBER(10, 0),
    LYSISBUFFER_ID                NUMBER(10, 0),
    CULTUREVOL_ML                 NUMBER(10, 0),
    INCUB_TEMP_AFTER_INDUCTION    NUMBER(10, 0),
    EXPRPEPSIZE_KD                NUMBER(10, 0),
    WPS_0TO3                      NUMBER(10, 0),
    SPS_0TO3                      NUMBER(10, 0),
    WNS_0TO3                      NUMBER(10, 0),
    SNS_0TO3                      NUMBER(10, 0),
    WPS_PAGELANE                  NUMBER(10, 0),
    SPS_PAGELANE                  NUMBER(10, 0),
    WNS_PAGELANE                  NUMBER(10, 0),
    SNS_PAGELANE                  NUMBER(10, 0),
    WNS_MICROG                    DOUBLE PRECISION,
    SNS_MICROG                    DOUBLE PRECISION,
    EXPERIMENTATOR_ID             NUMBER(11, 0)       NOT NULL,
    PROTEXPRESS_COMMENT           VARCHAR2(255),
    CONSTRAINT PROT_EXPRESS_CK221000469470001 CHECK (NOT (wps_0to3 > 3 OR wps_0to3 < 0)),
    CONSTRAINT PROT_EXPRESS_CK241000469503098 CHECK (NOT (sps_0to3 > 3 OR sps_0to3 < 0)),
    CONSTRAINT PROT_EXPRESS_CK261000469609171 CHECK (NOT (wns_0to3 > 3 OR wns_0to3 < 0)),
    CONSTRAINT PROT_EXPRESS_CK281000469635208 CHECK (NOT (sns_0to3 > 3 OR sns_0to3 < 0)),
    CONSTRAINT PROTEXPRESS_PRIMKEY PRIMARY KEY (PROTEXPRESS_ID)
) 
;


-- 
-- TABLE: PROT_PREPS 
--

CREATE TABLE PROT_PREPS(
    PROTEIN_ID              NUMBER(11, 0)    NOT NULL,
    CLONEID                 NUMBER(11, 0),
    DATE_IN                 DATE,
    EXPERIMENTATOR          NUMBER(11, 0),
    EXPERIMENT_ID           NUMBER(11, 0),
    CONCENTRATION_UMOLAR    NUMBER(7, 0),
    CONC_METHOD             NUMBER(5, 0),
    VOLUME_UL               NUMBER(8, 0),
    BUFFER                  NUMBER(11, 0),
    PROTOCOL_ID             NUMBER(11, 0),
    PURITY_ETC              VARCHAR2(255),
    CONSTRAINT PROTID_PRIKEY PRIMARY KEY (PROTEIN_ID)
) 
;

-- 
-- TABLE: RESIN 
--

CREATE TABLE RESIN(
    ID            NUMBER(11, 0)    NOT NULL,
    MEDIUMNAME    VARCHAR2(50),
    MEDIUMTYPE    VARCHAR2(50),
    COMMENT_      VARCHAR2(255),
    CONSTRAINT SYS_C006628 PRIMARY KEY (ID)
) 
;


-- 
-- TABLE: RESTRICTIONENZYMES 
--

CREATE TABLE RESTRICTIONENZYMES(
    ENZYMEID             NUMBER(11, 0)       NOT NULL,
    ENZYMENAME           VARCHAR2(50)        NOT NULL,
    SITESEQUENCE         VARCHAR2(50),
    CUTUPPERSTRAND       NUMBER(5, 0)        DEFAULT 0,
    LENGTH5PROVERHANG    DOUBLE PRECISION    DEFAULT 0,
    INSTOCK              NUMBER(1, 0),
    FREQUENTLYUSED       NUMBER(1, 0),
    CONSTRAINT SYS_C006630 PRIMARY KEY (ENZYMEID)
) 
;


-- 
-- TABLE: ROBOT_JOBS 
--

CREATE TABLE ROBOT_JOBS(
    ID                  NUMBER(11, 0)    NOT NULL,
    DATE_               DATE,
    EXPERIMENTATORID    NUMBER(11, 0),
    DESCRIPTION         VARCHAR2(80),
    EXPERIMENTID        NUMBER(11, 0),
    JOBNAME             VARCHAR2(40),
    CONSTRAINT ROBOTJOBS_PRIMARY_KEY PRIMARY KEY (ID)
) 
;


-- 
-- TABLE: ROBOT_JOBTYPE 
--

CREATE TABLE ROBOT_JOBTYPE(
    ID                     NUMBER(11, 0)    NOT NULL,
    DESCRIPTION            VARCHAR2(32),
    COMPATIBILITY_CLASS    NUMBER(11, 0),
    LAYOUT                 NUMBER(11, 0),
    CONSTRAINT ROBOTJOBTYPEID PRIMARY KEY (ID)
) 
;


-- 
-- TABLE: ROBOT_LAYOUT 
--

CREATE TABLE ROBOT_LAYOUT(
    ID                 NUMBER(11, 0)    NOT NULL,
    SAMPLE_RACKS       NUMBER(11, 0),
    DILUENT_RACKPOS    NUMBER(11, 0),
    BLEACH_RACKPOS     NUMBER(11, 0),
    ROBOT_NAME         VARCHAR2(20),
    BLEACH_WELLS       NUMBER(11, 0),
    DILUENT_WELLS      NUMBER(11, 0),
    CONSTRAINT SYS_C006639 PRIMARY KEY (ID)
) 
;


-- 
-- TABLE: ROBOT_REARRAY 
--

CREATE TABLE ROBOT_REARRAY(
    ID                         NUMBER(11, 0)    NOT NULL,
    SRC_PLATENUMBER            NUMBER(11, 0)    NOT NULL,
    SRC_BARCODE                NUMBER(11, 0),
    SRC_POSITION               NUMBER(11, 0),
    SRC_PLATESBOXESADMINID     NUMBER(11, 0),
    DEST_PLATENUMBER           NUMBER(11, 0)    NOT NULL,
    DEST_BARCODE               NUMBER(11, 0),
    DEST_POSITION              NUMBER(11, 0),
    DEST_PLATESBOXESADMINID    NUMBER(11, 0),
    SAMPLEVOLUME               NUMBER(8, 4),
    DILUENTVOLUME              NUMBER(8, 4),
    ACCESSED                   NUMBER(2, 0),
    JOB_TYPE                   NUMBER(11, 0)    NOT NULL,
    PLATESBOXESID              NUMBER(11, 0),
    JOB_ID                     NUMBER(11, 0),
    SRC_PLATESBOXESID          NUMBER(11, 0),
    CONSTRAINT ROBOT_REARRAY_PK81030438156619 PRIMARY KEY (ID)
) 
;


-- 
-- TABLE: SEQ_DNA 
--

CREATE TABLE SEQ_DNA(
    SEQDNA_ID    NUMBER(11, 0)    NOT NULL,
    SEQ          CLOB,
    CONSTRAINT SEQDNA_PK PRIMARY KEY (SEQDNA_ID)
) 
;


-- 
-- TABLE: SEQ_PEP 
--

CREATE TABLE SEQ_PEP(
    SEQPEP_ID    NUMBER(11, 0)    NOT NULL,
    SEQ          CLOB             NOT NULL,
    CONSTRAINT SEQDNA_PK_1 PRIMARY KEY (SEQPEP_ID)
) 
;


-- 
-- TABLE: SEQUENCING 
--

CREATE TABLE SEQUENCING(
    SEQEXPID                   NUMBER(11, 0)    NOT NULL,
    SEQUENCE_DATE              DATE             DEFAULT sysdate NOT NULL,
    CLONEID                    NUMBER(11, 0)    NOT NULL,
    PCR_OK                     NUMBER(1, 0),
    SEQUENCE_OK                NUMBER(1, 0),
    MATCHSWPLUS                NUMBER(1, 0),
    SEQFILE                    VARCHAR2(255),
    CLSEQ_NAME                 VARCHAR2(30),
    PRIMER_ID                  NUMBER(11, 0),
    ENDOF_LASTVECTORCODON      NUMBER(4, 0),
    SEQDNA_ID                  NUMBER(11, 0),
    MASKED_ID                  NUMBER(11, 0),
    PHRED_BP_CALLED            NUMBER(4, 0),
    PHRED_TRIMMED_BEGINNING    NUMBER(4, 0),
    PHRED_BP_GOOD              NUMBER(4, 0),
    CONSTRAINT SYS_C006658 PRIMARY KEY (SEQEXPID)
) 
;


-- 
-- TABLE: SPECIES 
--

CREATE TABLE SPECIES(
    SPECIESNO        NUMBER(11, 0)    NOT NULL,
    SPECIES          VARCHAR2(50),
    COMMENT_         CLOB,
    GENUS            VARCHAR2(50),
    GENUS_SPECIES    VARCHAR2(50)     NOT NULL,
    CONSTRAINT SYS_C006672 PRIMARY KEY (SPECIESNO)
) 
;


-- 
-- TABLE: STRAINS 
--

CREATE TABLE STRAINS(
    STRAINID          NUMBER(11, 0)    NOT NULL,
    STRAINNAME        VARCHAR2(50),
    STRAINGENOTYPE    VARCHAR2(255),
    COMPANY           VARCHAR2(50),
    COMMENT_          VARCHAR2(255),
    DATE_             DATE             DEFAULT TO_DATE(SYSDATE),
    CLONEID           NUMBER(11, 0),
    ECOLI_ISOLATE     VARCHAR2(5),
    CONSTRAINT STRAINS_ISO_CHECK CHECK (ecoli_isolate in ('B','K-12')),
    CONSTRAINT SYS_C006673 PRIMARY KEY (STRAINID)
) 
;


-- 
-- TABLE: TRANSFORMATIONMETHOD 
--

CREATE TABLE TRANSFORMATIONMETHOD(
    METHOD_ID           NUMBER(11, 0)    NOT NULL,
    IDENTIFIER          VARCHAR2(10),
    DESCRIPTIVE_NAME    VARCHAR2(127),
    REFERENCE           VARCHAR2(127),
    PROTOCOL_URL        VARCHAR2(127),
    CONSTRAINT METHOD_ID PRIMARY KEY (METHOD_ID)
) 
;


-- 
-- TABLE: TRANSFORMATIONS 
--

CREATE TABLE TRANSFORMATIONS(
    TRAFO_ID                  NUMBER(11, 0)    NOT NULL,
    TRAFO_DATE                DATE             DEFAULT sysdate NOT NULL,
    METHOD_ID                 NUMBER(11, 0),
    TRANSFORMANTS_NO          NUMBER(11, 0),
    LIGATION_ID               NUMBER(11, 0),
    PLASMID_CLONEID_SOURCE    NUMBER(11, 0),
    EXPERIMENT_ID             NUMBER(11, 0),
    HELPERPLASMID_ID          NUMBER(11, 0),
    STRAINID                  NUMBER(11, 0),
    LIGTRAFO_NO               NUMBER(11, 0),
    EXPERIMENTATOR_ID         NUMBER(11, 0)    NOT NULL,
    CONSTRAINT TRAFOID_PRIMKEY PRIMARY KEY (TRAFO_ID)
) 
;


-- 
-- TABLE: VECTORS 
--

CREATE TABLE VECTORS(
    VECTORID            NUMBER(11, 0)     NOT NULL,
    VECTORNAME          VARCHAR2(50),
    VECTORACCESSION     VARCHAR2(50),
    SEQFILE             VARCHAR2(255),
    DEFAULTRE1          NUMBER(11, 0),
    DEFAULTRE2          NUMBER(11, 0),
    RESISTENCEMARKER    NUMBER(11, 0)     DEFAULT NULL,
    DESCRIPTION         VARCHAR2(2000),
    NTERM_TAG           VARCHAR2(50),
    CTERM_TAG           VARCHAR2(50),
    CLONEID             NUMBER(11, 0),
    ORIGIN              NUMBER(5, 0),
    SEQDNA_ID           NUMBER,
    STARTCODON_POS      NUMBER(5, 0),
    STOPCODON_POS       NUMBER(5, 0),
    PROTEASE_SITE       NUMBER(2, 0),
    CONSTRAINT CHECK_NTERM_2 CHECK (nterm_tag in ('GST','His6','Strep-tagII','MBD','CBP','NusA','Intein','His6+GST')),
    CONSTRAINT CHECK_CTERM_2 CHECK (cterm_tag in ('GST','His6','Strep-tagII','MBD','CBP','NusA','Intein')),
    CONSTRAINT SYS_C006693 PRIMARY KEY (VECTORID)
) 
;