--
--ER/Studio 4.2 SQL Code Generation
-- Company :      Piocon Technologies, Inc.
-- Project :      Model
-- Author :       Jeremy Simmons
--
-- Date Created : Monday, February 04, 2002 10:44:19
-- Target DBMS : Oracle 8
--


-- 
-- TABLE: ACNET_DATA 
--

CREATE TABLE ACNET_DATA(
    LBN            NUMBER(38, 0)    NOT NULL,
    NAME           VARCHAR2(240)    NOT NULL,
    DI             NUMBER           NOT NULL,
    PI             NUMBER           NOT NULL,
    VAL            NUMBER           NOT NULL,
    TIMESTAMP      NUMBER           NOT NULL,
    UNIT           VARCHAR2(240),
    DESCRIPTION    VARCHAR2(240)    NOT NULL,
    CONSTRAINT ACNET_DATA_PK PRIMARY KEY (LBN,NAME)
) 
;


-- 
-- TABLE: COMPLETE_BITS 
--

CREATE TABLE COMPLETE_BITS(
    LBN       NUMBER(38, 0)    NOT NULL,
    BIT       VARCHAR2(240)    NOT NULL,
    VALUE     NUMBER           NOT NULL,
    STATUS    VARCHAR2(240),
    CONSTRAINT COMP_BIT_PK PRIMARY KEY (LBN,BIT)
) 
;


-- 
-- TABLE: CRATES 
--

CREATE TABLE CRATES(
    LBN           NUMBER(38, 0)    NOT NULL,
    L2_BIT_NUM    NUMBER           NOT NULL,
    CRATE_LIST    VARCHAR2(240)    NOT NULL,
    CONSTRAINT CRATE_PK PRIMARY KEY (LBN,L2_BIT_NUM)
) 
;


-- 
-- TABLE: ETG_SBS 
--

CREATE TABLE ETG_SBS(
    LBN                        NUMBER(38, 0)    NOT NULL,
    L1_DISABLES_ETG            NUMBER           NOT NULL,
    L1_DISABLES_SB             NUMBER           NOT NULL,
    TOTAL_PASSED_EVENTS        NUMBER           NOT NULL,
    TOTAL_EVENTS               NUMBER           NOT NULL,
    TOTAL_INCOMPLETE_EVENTS    NUMBER           NOT NULL,
    CONSTRAINT ETG_SB_PK PRIMARY KEY (LBN)
) 
;


-- 
-- TABLE: EXP_GRP_RATES 
--

CREATE TABLE EXP_GRP_RATES(
    LBN         NUMBER(38, 0)       NOT NULL,
    QUANTITY    VARCHAR2(64)        NOT NULL,
    EXP_GRP     NUMBER(10, 0)       NOT NULL,
    TICK_NUM    NUMBER(38, 0)       NOT NULL,
    VALUE       DOUBLE PRECISION    NOT NULL,
    CONSTRAINT EXGRP_RATE_PK PRIMARY KEY (LBN,QUANTITY,EXP_GRP,TICK_NUM)
) 
;


-- 
-- TABLE: FILES 
--

CREATE TABLE FILES(
    LBN                 NUMBER(38, 0)    NOT NULL,
    FILE_NAME           VARCHAR2(240)    NOT NULL,
    NUMBER_OF_EVENTS    NUMBER           NOT NULL,
    MIN_LBN             NUMBER           NOT NULL,
    MAX_LBN             NUMBER           NOT NULL,
    RUN_NUMBER          NUMBER           NOT NULL,
    STREAM_NAME         VARCHAR2(240)    NOT NULL,
    CONSTRAINT FILE_1_PK PRIMARY KEY (LBN,FILE_NAME)
) 
;


-- 
-- TABLE: INCOMPLETE_BITS 
--

CREATE TABLE INCOMPLETE_BITS(
    LBN       NUMBER(38, 0)    NOT NULL,
    BIT       VARCHAR2(240)    NOT NULL,
    VALUE     NUMBER           NOT NULL,
    STATUS    VARCHAR2(240),
    CONSTRAINT INCOMP_BIT_PK PRIMARY KEY (LBN,BIT)
) 
;


-- 
-- TABLE: L3_SR 
--

CREATE TABLE L3_SR(
    LBN              NUMBER(38, 0)    NOT NULL,
    L1_BIT           NUMBER(10, 0)    NOT NULL,
    L2_BIT           NUMBER(10, 0)    NOT NULL,
    L2_NAME          VARCHAR2(64)     NOT NULL,
    RUN_NUMBER       NUMBER(38, 0)    NOT NULL,
    NODE_FRACTION    VARCHAR2(200)    NOT NULL,
    NODE_DECLARED    NUMBER(10, 0)    NOT NULL,
    NODE_INCLUDED    NUMBER(10, 0)    NOT NULL,
    CONSTRAINT PK23 PRIMARY KEY (LBN,L1_BIT)
) 
;


-- 
-- TABLE: LBNS 
--

CREATE TABLE LBNS(
    LBN    NUMBER(38, 0)    NOT NULL,
    CONSTRAINT LUM_BLK_PK PRIMARY KEY (LBN)
) 
;


-- 
-- TABLE: LUM_BLOCKS 
--

CREATE TABLE LUM_BLOCKS(
    LBN                       NUMBER(38, 0)    NOT NULL,
    PREV_LBN                  NUMBER(38, 0),
    INCREMENT_REASON_MASK     NUMBER(38, 0)    NOT NULL,
    MONIT_DATA_NOT_CURRENT    NUMBER(38, 0)    NOT NULL,
    TURN_NUM                  NUMBER(38, 0)    NOT NULL,
    TICK_NUM                  NUMBER(38, 0)    NOT NULL,
    BEAM_X                    NUMBER(38, 0)    NOT NULL,
    TRIGGERED_BEAM_X          NUMBER(38, 0)    NOT NULL,
    BLOCK_TYPE                NUMBER(38, 0)    NOT NULL,
    FORMAT_VERSION            NUMBER(38, 0)    NOT NULL,
    SOURCE                    VARCHAR2(240)    NOT NULL,
    TIME_STAMP                DATE             NOT NULL,
    DELTA_BEAM_X              NUMBER(38, 0),
    DELTA_TIME                NUMBER(38, 0),
    SIGMA                     NUMBER(38, 0),
    LUMINOSITY                NUMBER(38, 0),
    L1_RATE                   NUMBER(38, 0),
    L2_RATE                   NUMBER(38, 0),
    L1_TRIGGER_XS             NUMBER(38, 0),
    L2_TRIGGER_XS             NUMBER(38, 0),
    CONSTRAINT LUM_BLK_PK_1 PRIMARY KEY (LBN)
) 
;


-- 
-- TABLE: LUM_DELIVERED 
--

CREATE TABLE LUM_DELIVERED(
    LBN         NUMBER(38, 0)       NOT NULL,
    QUANTITY    VARCHAR2(64)        NOT NULL,
    TICK_NUM    NUMBER(38, 0)       NOT NULL,
    VALUE       DOUBLE PRECISION    NOT NULL,
    CONSTRAINT LUM_RATE_PK PRIMARY KEY (LBN,QUANTITY,TICK_NUM)
) 
;


-- 
-- TABLE: MISSING_CRATES 
--

CREATE TABLE MISSING_CRATES(
    LBN                 NUMBER(38, 0)    NOT NULL,
    CRATE_NUMBER        NUMBER           NOT NULL,
    NUMBER_OF_EVENTS    NUMBER           NOT NULL,
    CONSTRAINT MISS_CRATE_PK PRIMARY KEY (LBN,CRATE_NUMBER)
) 
;


-- 
-- TABLE: SP_TRIGGERS 
--

CREATE TABLE SP_TRIGGERS(
    LBN                 NUMBER(38, 0)    NOT NULL,
    L1_BIT              NUMBER(38, 0)    NOT NULL,
    L1_ACCEPT           NUMBER(38, 0)    NOT NULL,
    L2_ACCEPT           NUMBER(38, 0)    NOT NULL,
    DECORR_EXPOSED      NUMBER(38, 0)    NOT NULL,
    EXP_GRP             NUMBER(38, 0)    NOT NULL,
    PRESCALE_PERCENT    NUMBER(38, 0)    NOT NULL,
    PRESCALE_RATIO      NUMBER(38, 0)    NOT NULL,
    RUN_NUMBER          NUMBER(38, 0),
    TRIGGER_MASK        NUMBER(38, 0),
    CONSTRAINT SP_TRG_PK PRIMARY KEY (LBN,L1_BIT)
) 
;


-- 
-- TABLE: SR_L3_BITS 
--

CREATE TABLE SR_L3_BITS(
    LBN        NUMBER(38, 0)    NOT NULL,
    L1_BIT     NUMBER(10, 0)    NOT NULL,
    L3_BIT     NUMBER(10, 0)    NOT NULL,
    PASSED     NUMBER(10, 0)    NOT NULL,
    CALLED     NUMBER(10, 0)    NOT NULL,
    L3_NAME    VARCHAR2(64)     NOT NULL,
    FAILED     NUMBER(10, 0),
    CONSTRAINT PK24 PRIMARY KEY (LBN,L1_BIT,L3_BIT)
) 
;


-- 
-- TABLE: TRIGGER_BITS 
--

CREATE TABLE TRIGGER_BITS(
    TRIGGER_LEVEL       VARCHAR2(240)    NOT NULL,
    FILE_NAME           VARCHAR2(240)    NOT NULL,
    LBN                 NUMBER(38, 0)    NOT NULL,
    BIT_NUM             NUMBER           NOT NULL,
    NUMBER_OF_EVENTS    NUMBER           NOT NULL,
    CONSTRAINT TRIG_BIT_PK PRIMARY KEY (TRIGGER_LEVEL,FILE_NAME,LBN,BIT_NUM)
) 
;


-- 
-- TABLE: TRIGGERS 
--

CREATE TABLE TRIGGERS(
    FILE_NAME        VARCHAR2(240)    NOT NULL,
    LBN              NUMBER(38, 0)    NOT NULL,
    TRIGGER_LEVEL    VARCHAR2(240)    NOT NULL,
    OR_BIT_MASK      NUMBER(38, 0)    NOT NULL,
    CONSTRAINT TRIGGER_1_PK PRIMARY KEY (FILE_NAME,LBN,TRIGGER_LEVEL)
) 
;


-- 
-- INDEX: ACNET_DATA_LUM_BLK_FK 
--

CREATE INDEX ACNET_DATA_LUM_BLK_FK ON ACNET_DATA(LBN)
;
-- 
-- INDEX: COMP_BIT_LUM_BLK_FK 
--

CREATE INDEX COMP_BIT_LUM_BLK_FK ON COMPLETE_BITS(LBN)
;
-- 
-- INDEX: CRATE_LUM_BLK_FK 
--

CREATE INDEX CRATE_LUM_BLK_FK ON CRATES(LBN)
;
-- 
-- INDEX: ETG_SB_LUM_BLK_FK 
--

CREATE INDEX ETG_SB_LUM_BLK_FK ON ETG_SBS(LBN)
;
-- 
-- INDEX: EXGRP_RATE_LUM_BLK_FK 
--

CREATE INDEX EXGRP_RATE_LUM_BLK_FK ON EXP_GRP_RATES(LBN)
;
-- 
-- INDEX: FILE_1_LUM_BLK_FK 
--

CREATE INDEX FILE_1_LUM_BLK_FK ON FILES(LBN)
;
-- 
-- INDEX: INCOMP_BIT_LUM_BLK_FK 
--

CREATE INDEX INCOMP_BIT_LUM_BLK_FK ON INCOMPLETE_BITS(LBN)
;
-- 
-- INDEX: Ref1317 
--

CREATE INDEX Ref1317 ON L3_SR(LBN)
;
-- 
-- INDEX: Ref1316 
--

CREATE INDEX Ref1316 ON LUM_BLOCKS(LBN)
;
-- 
-- INDEX: LUM_RATE_LUM_BLK_FK 
--

CREATE INDEX LUM_RATE_LUM_BLK_FK ON LUM_DELIVERED(LBN)
;
-- 
-- INDEX: MISS_CRATE_LUM_BLK_FK 
--

CREATE INDEX MISS_CRATE_LUM_BLK_FK ON MISSING_CRATES(LBN)
;
-- 
-- INDEX: SP_TRG_LUM_BLK_FK 
--

CREATE INDEX SP_TRG_LUM_BLK_FK ON SP_TRIGGERS(LBN)
;
-- 
-- INDEX: Ref2318 
--

CREATE INDEX Ref2318 ON SR_L3_BITS(LBN,L1_BIT)
;
-- 
-- INDEX: TRIG_BIT_TRIGGER_1_FK 
--

CREATE INDEX TRIG_BIT_TRIGGER_1_FK ON TRIGGER_BITS(LBN,FILE_NAME,TRIGGER_LEVEL)
;
-- 
-- INDEX: TRIGGER_1_FILE_1_FK 
--

CREATE INDEX TRIGGER_1_FILE_1_FK ON TRIGGERS(FILE_NAME,LBN)
;
-- 
-- TABLE: ACNET_DATA 
--

ALTER TABLE ACNET_DATA ADD CONSTRAINT ACNET_DATA_LUM_BLK_FK 
    FOREIGN KEY (LBN)
    REFERENCES LBNS(LBN)
;


-- 
-- TABLE: COMPLETE_BITS 
--

ALTER TABLE COMPLETE_BITS ADD CONSTRAINT COMP_BIT_LUM_BLK_FK 
    FOREIGN KEY (LBN)
    REFERENCES LBNS(LBN)
;


-- 
-- TABLE: CRATES 
--

ALTER TABLE CRATES ADD CONSTRAINT CRATE_LUM_BLK_FK 
    FOREIGN KEY (LBN)
    REFERENCES LBNS(LBN)
;


-- 
-- TABLE: ETG_SBS 
--

ALTER TABLE ETG_SBS ADD CONSTRAINT ETG_SB_LUM_BLK_FK 
    FOREIGN KEY (LBN)
    REFERENCES LBNS(LBN)
;


-- 
-- TABLE: EXP_GRP_RATES 
--

ALTER TABLE EXP_GRP_RATES ADD CONSTRAINT EXGRP_RATE_LUM_BLK_FK 
    FOREIGN KEY (LBN)
    REFERENCES LBNS(LBN)
;


-- 
-- TABLE: FILES 
--

ALTER TABLE FILES ADD CONSTRAINT FILE_1_LUM_BLK_FK 
    FOREIGN KEY (LBN)
    REFERENCES LBNS(LBN)
;


-- 
-- TABLE: INCOMPLETE_BITS 
--

ALTER TABLE INCOMPLETE_BITS ADD CONSTRAINT INCOMP_BIT_LUM_BLK_FK 
    FOREIGN KEY (LBN)
    REFERENCES LBNS(LBN)
;


-- 
-- TABLE: L3_SR 
--

ALTER TABLE L3_SR ADD CONSTRAINT RefLBNS17 
    FOREIGN KEY (LBN)
    REFERENCES LBNS(LBN)
;


-- 
-- TABLE: LUM_BLOCKS 
--

ALTER TABLE LUM_BLOCKS ADD CONSTRAINT RefLBNS16 
    FOREIGN KEY (LBN)
    REFERENCES LBNS(LBN)
;


-- 
-- TABLE: LUM_DELIVERED 
--

ALTER TABLE LUM_DELIVERED ADD CONSTRAINT LUM_RATE_LUM_BLK_FK 
    FOREIGN KEY (LBN)
    REFERENCES LBNS(LBN)
;


-- 
-- TABLE: MISSING_CRATES 
--

ALTER TABLE MISSING_CRATES ADD CONSTRAINT MISS_CRATE_LUM_BLK_FK 
    FOREIGN KEY (LBN)
    REFERENCES LBNS(LBN)
;


-- 
-- TABLE: SP_TRIGGERS 
--

ALTER TABLE SP_TRIGGERS ADD CONSTRAINT SP_TRG_LUM_BLK_FK 
    FOREIGN KEY (LBN)
    REFERENCES LBNS(LBN)
;


-- 
-- TABLE: SR_L3_BITS 
--

ALTER TABLE SR_L3_BITS ADD CONSTRAINT RefL3_SR18 
    FOREIGN KEY (LBN,L1_BIT)
    REFERENCES L3_SR(LBN,L1_BIT)
;


-- 
-- TABLE: TRIGGER_BITS 
--

ALTER TABLE TRIGGER_BITS ADD CONSTRAINT TRIG_BIT_TRIGGER_1_FK 
    FOREIGN KEY (FILE_NAME,LBN,TRIGGER_LEVEL)
    REFERENCES TRIGGERS(FILE_NAME,LBN,TRIGGER_LEVEL)
;


-- 
-- TABLE: TRIGGERS 
--

ALTER TABLE TRIGGERS ADD CONSTRAINT TRIGGER_1_FILE_1_FK 
    FOREIGN KEY (LBN,FILE_NAME)
    REFERENCES FILES(LBN,FILE_NAME)
;


