--
-- ER/Studio 7.1 SQL Code Generation
-- Company :      Berkeley Seismological Laboratory
-- Project :      NCEDC/TriNet Instrument Responses
-- Author :       Stephane Zuzlewski
--
-- Date Created : Tuesday, February 20, 2007 11:21:27
-- Target DBMS : Oracle 9i
--

DROP TABLE Channel_Comment CASCADE CONSTRAINTS
;
DROP TABLE Channel_Data CASCADE CONSTRAINTS
;
DROP TABLE Coefficients CASCADE CONSTRAINTS
;
DROP TABLE D_Abbreviation CASCADE CONSTRAINTS
;
DROP TABLE D_Comment CASCADE CONSTRAINTS
;
DROP TABLE D_Format CASCADE CONSTRAINTS
;
DROP TABLE D_Format_Data CASCADE CONSTRAINTS
;
DROP TABLE D_Unit CASCADE CONSTRAINTS
;
DROP TABLE DC CASCADE CONSTRAINTS
;
DROP TABLE DC_Data CASCADE CONSTRAINTS
;
DROP TABLE Decimation CASCADE CONSTRAINTS
;
DROP TABLE DM CASCADE CONSTRAINTS
;
DROP TABLE PN CASCADE CONSTRAINTS
;
DROP TABLE PN_Data CASCADE CONSTRAINTS
;
DROP TABLE Poles_Zeros CASCADE CONSTRAINTS
;
DROP TABLE Polynomial CASCADE CONSTRAINTS
;
DROP TABLE PZ CASCADE CONSTRAINTS
;
DROP TABLE PZ_Data CASCADE CONSTRAINTS
;
DROP TABLE Sensitivity CASCADE CONSTRAINTS
;
DROP TABLE Simple_Response CASCADE CONSTRAINTS
;
DROP TABLE Station_Comment CASCADE CONSTRAINTS
;
DROP TABLE Station_Data CASCADE CONSTRAINTS
;
-- 
-- SEQUENCE: abbseq 
--

DROP SEQUENCE abbseq
;

CREATE SEQUENCE ABBSEQ
    START WITH 1
    INCREMENT BY 1
    NOMINVALUE
    NOMAXVALUE
    CACHE 20
    ORDER
;

-- 
-- SEQUENCE: comseq 
--

DROP SEQUENCE comseq
;

CREATE SEQUENCE COMSEQ
    START WITH 1
    INCREMENT BY 1
    NOMINVALUE
    NOMAXVALUE
    CACHE 20
    ORDER
;

-- 
-- SEQUENCE: dcseq 
--

DROP SEQUENCE dcseq
;

CREATE SEQUENCE DCSEQ
    START WITH 1
    INCREMENT BY 1
    NOMINVALUE
    NOMAXVALUE
    CACHE 20
    ORDER
;

-- 
-- SEQUENCE: dmseq 
--

DROP SEQUENCE dmseq
;

CREATE SEQUENCE DMSEQ
    START WITH 1
    INCREMENT BY 1
    NOMINVALUE
    NOMAXVALUE
    CACHE 20
    ORDER
;

-- 
-- SEQUENCE: forseq 
--

DROP SEQUENCE forseq
;

CREATE SEQUENCE FORSEQ
    START WITH 1
    INCREMENT BY 1
    NOMINVALUE
    NOMAXVALUE
    CACHE 20
    ORDER
;

-- 
-- SEQUENCE: poseq 
--

DROP SEQUENCE poseq
;

CREATE SEQUENCE POSEQ
    START WITH 1
    INCREMENT BY 1
    NOMINVALUE
    NOMAXVALUE
    CACHE 20
    ORDER
;

-- 
-- SEQUENCE: pzseq 
--

DROP SEQUENCE pzseq
;

CREATE SEQUENCE PZSEQ
    START WITH 1
    INCREMENT BY 1
    NOMINVALUE
    NOMAXVALUE
    CACHE 20
    ORDER
;

-- 
-- SEQUENCE: uniseq 
--

DROP SEQUENCE uniseq
;

CREATE SEQUENCE UNISEQ
    START WITH 1
    INCREMENT BY 1
    NOMINVALUE
    NOMAXVALUE
    CACHE 20
    ORDER
;

-- 
-- TABLE: Channel_Comment 
--

CREATE TABLE Channel_Comment(
    net              VARCHAR2(8)     NOT NULL,
    sta              VARCHAR2(6)     NOT NULL,
    seedchan         VARCHAR2(3)     NOT NULL,
    location         VARCHAR2(2)     NOT NULL,
    ondate           DATE            NOT NULL,
    comment_id       NUMBER(8, 0)    NOT NULL,
    channel          VARCHAR2(8),
    channelsrc       VARCHAR2(8),
    offdate          DATE,
    comment_level    NUMBER(8, 0)    NOT NULL,
    lddate           DATE            DEFAULT (SYSDATE),
    CONSTRAINT ChC00 PRIMARY KEY (net, sta, seedchan, location, ondate, comment_id)
)
TABLESPACE IR_DATA
;



-- 
-- TABLE: Channel_Data 
--

CREATE TABLE Channel_Data(
    net              VARCHAR2(8)         NOT NULL,
    sta              VARCHAR2(6)         NOT NULL,
    seedchan         VARCHAR2(3)         NOT NULL,
    location         VARCHAR2(2)         NOT NULL,
    ondate           DATE                NOT NULL,
    channel          VARCHAR2(8),
    channelsrc       VARCHAR2(8),
    inid             NUMBER(8, 0),
    remark           VARCHAR2(30),
    unit_signal      NUMBER(8, 0)        NOT NULL,
    unit_calib       NUMBER(8, 0)        NOT NULL,
    lat              DOUBLE PRECISION,
    lon              DOUBLE PRECISION,
    elev             DOUBLE PRECISION,
    edepth           DOUBLE PRECISION,
    azimuth          DOUBLE PRECISION,
    dip              DOUBLE PRECISION,
    format_id        NUMBER(8, 0)        NOT NULL,
    record_length    NUMBER(8, 0),
    samprate         DOUBLE PRECISION    NOT NULL,
    clock_drift      DOUBLE PRECISION,
    flags            VARCHAR2(27),
    offdate          DATE,
    lddate           DATE                DEFAULT (SYSDATE),
    CONSTRAINT ChD00 PRIMARY KEY (net, sta, seedchan, location, ondate)
)
TABLESPACE IR_DATA
;



-- 
-- TABLE: Coefficients 
--

CREATE TABLE Coefficients(
    net           VARCHAR2(8)     NOT NULL,
    sta           VARCHAR2(6)     NOT NULL,
    seedchan      VARCHAR2(3)     NOT NULL,
    location      VARCHAR2(2)     NOT NULL,
    ondate        DATE            NOT NULL,
    stage_seq     NUMBER(8, 0)    NOT NULL,
    channel       VARCHAR2(8),
    channelsrc    VARCHAR2(8),
    offdate       DATE,
    dc_key        NUMBER(8, 0),
    unit_in       NUMBER(8, 0)    NOT NULL,
    unit_out      NUMBER(8, 0)    NOT NULL,
    tf_type       VARCHAR2(1),
    lddate        DATE            DEFAULT (SYSDATE),
    CONSTRAINT Co00 PRIMARY KEY (net, sta, seedchan, location, ondate, stage_seq)
)
TABLESPACE IR_DATA
;



-- 
-- TABLE: D_Abbreviation 
--

CREATE TABLE D_Abbreviation(
    id             NUMBER(8, 0)    NOT NULL,
    description    VARCHAR2(70),
    CONSTRAINT D_A00 PRIMARY KEY (id)
)
TABLESPACE IR_DATA
;



-- 
-- TABLE: D_Comment 
--

CREATE TABLE D_Comment(
    id             NUMBER(8, 0)    NOT NULL,
    class          VARCHAR2(1)     NOT NULL,
    description    VARCHAR2(70),
    unit           NUMBER(8, 0)    NOT NULL,
    CONSTRAINT D_C00 PRIMARY KEY (id)
)
TABLESPACE IR_DATA
;



-- 
-- TABLE: D_Format 
--

CREATE TABLE D_Format(
    id        NUMBER(8, 0)    NOT NULL,
    name      VARCHAR2(80),
    family    NUMBER(8, 0)    NOT NULL,
    ms_id     NUMBER(8, 0)    NOT NULL,
    CONSTRAINT D_F00 PRIMARY KEY (id)
)
TABLESPACE IR_DATA
;



-- 
-- TABLE: D_Format_Data 
--

CREATE TABLE D_Format_Data(
    id        NUMBER(8, 0)    NOT NULL,
    row_id    NUMBER(8, 0)    NOT NULL,
    key_d     VARCHAR2(80)    NOT NULL,
    CONSTRAINT D_F_D00 PRIMARY KEY (id, row_id)
)
TABLESPACE IR_DATA
;



-- 
-- TABLE: D_Unit 
--

CREATE TABLE D_Unit(
    id             NUMBER(8, 0)    NOT NULL,
    name           VARCHAR2(80),
    description    VARCHAR2(70),
    CONSTRAINT D_U00 PRIMARY KEY (id)
)
TABLESPACE IR_DATA
;



-- 
-- TABLE: DC 
--

CREATE TABLE DC(
    key         NUMBER(8, 0)    NOT NULL,
    name        VARCHAR2(80),
    symmetry    VARCHAR2(1),
    storage     VARCHAR2(1),
    lddate      DATE            DEFAULT (SYSDATE),
    CONSTRAINT DC00 PRIMARY KEY (key)
)
TABLESPACE IR_DATA
;



-- 
-- TABLE: DC_Data 
--

CREATE TABLE DC_Data(
    key            NUMBER(8, 0)        NOT NULL,
    row_key        NUMBER(8, 0)        NOT NULL,
    type           VARCHAR2(1),
    coefficient    DOUBLE PRECISION    NOT NULL,
    error          DOUBLE PRECISION,
    CONSTRAINT DCD00 PRIMARY KEY (key, row_key)
)
TABLESPACE IR_DATA
;



-- 
-- TABLE: Decimation 
--

CREATE TABLE Decimation(
    net           VARCHAR2(8)     NOT NULL,
    sta           VARCHAR2(6)     NOT NULL,
    seedchan      VARCHAR2(3)     NOT NULL,
    location      VARCHAR2(2)     NOT NULL,
    ondate        DATE            NOT NULL,
    stage_seq     NUMBER(8, 0)    NOT NULL,
    channel       VARCHAR2(8),
    channelsrc    VARCHAR2(8),
    offdate       DATE,
    dm_key        NUMBER(8, 0)    NOT NULL,
    lddate        DATE            DEFAULT (SYSDATE),
    CONSTRAINT De00 PRIMARY KEY (net, sta, seedchan, location, ondate, stage_seq)
)
TABLESPACE IR_DATA
;



-- 
-- TABLE: DM 
--

CREATE TABLE DM(
    key           NUMBER(8, 0)        NOT NULL,
    name          VARCHAR2(80),
    samprate      DOUBLE PRECISION    NOT NULL,
    factor        NUMBER(8, 0)        NOT NULL,
    offset        NUMBER(8, 0),
    delay         DOUBLE PRECISION,
    correction    DOUBLE PRECISION    NOT NULL,
    lddate        DATE                DEFAULT (SYSDATE),
    CONSTRAINT DM00 PRIMARY KEY (key)
)
TABLESPACE IR_DATA
;



-- 
-- TABLE: PN 
--

CREATE TABLE PN(
    key            NUMBER(8, 0)        NOT NULL,
    name           VARCHAR2(80),
    poly_type      VARCHAR2(1),
    lower_bound    DOUBLE PRECISION,
    upper_bound    DOUBLE PRECISION,
    max_error      DOUBLE PRECISION,
    lddate         DATE                DEFAULT (SYSDATE),
    CONSTRAINT PN00 PRIMARY KEY (key)
)
TABLESPACE IR_DATA
;



-- 
-- TABLE: PN_Data 
--

CREATE TABLE PN_Data(
    key         NUMBER(8, 0)        NOT NULL,
    row_key     NUMBER(8, 0)        NOT NULL,
    pn_value    DOUBLE PRECISION,
    CONSTRAINT PND00 PRIMARY KEY (key, row_key)
)
TABLESPACE IR_DATA
;



-- 
-- TABLE: Poles_Zeros 
--

CREATE TABLE Poles_Zeros(
    net           VARCHAR2(8)         NOT NULL,
    sta           VARCHAR2(6)         NOT NULL,
    seedchan      VARCHAR2(3)         NOT NULL,
    location      VARCHAR2(2)         NOT NULL,
    ondate        DATE                NOT NULL,
    stage_seq     NUMBER(8, 0)        NOT NULL,
    channel       VARCHAR2(8),
    channelsrc    VARCHAR2(8),
    offdate       DATE,
    pz_key        NUMBER(8, 0)        NOT NULL,
    tf_type       VARCHAR2(1),
    unit_in       NUMBER(8, 0)        NOT NULL,
    unit_out      NUMBER(8, 0)        NOT NULL,
    AO            DOUBLE PRECISION    NOT NULL,
    AF            DOUBLE PRECISION,
    lddate        DATE                DEFAULT (SYSDATE),
    CONSTRAINT P_Z00 PRIMARY KEY (net, sta, seedchan, location, ondate, stage_seq)
)
TABLESPACE IR_DATA
;



-- 
-- TABLE: Polynomial 
--

CREATE TABLE Polynomial(
    net           VARCHAR2(8)     NOT NULL,
    sta           VARCHAR2(6)     NOT NULL,
    seedchan      VARCHAR2(3)     NOT NULL,
    location      VARCHAR2(2)     NOT NULL,
    ondate        DATE            NOT NULL,
    stage_seq     NUMBER(8, 0)    NOT NULL,
    channel       VARCHAR2(8),
    channelsrc    VARCHAR2(8),
    offdate       DATE,
    pn_key        NUMBER(8, 0)    NOT NULL,
    unit_in       NUMBER(8, 0)    NOT NULL,
    unit_out      NUMBER(8, 0)    NOT NULL,
    tf_type       VARCHAR2(1),
    lddate        DATE            DEFAULT (SYSDATE),
    CONSTRAINT Po00 PRIMARY KEY (net, sta, seedchan, location, ondate, stage_seq)
)
TABLESPACE IR_DATA
;



-- 
-- TABLE: PZ 
--

CREATE TABLE PZ(
    key       NUMBER(8, 0)    NOT NULL,
    name      VARCHAR2(80),
    lddate    DATE            DEFAULT (SYSDATE),
    CONSTRAINT PZ00 PRIMARY KEY (key)
)
TABLESPACE IR_DATA
;



-- 
-- TABLE: PZ_Data 
--

CREATE TABLE PZ_Data(
    key        NUMBER(8, 0)        NOT NULL,
    row_key    NUMBER(8, 0)        NOT NULL,
    type       VARCHAR2(1),
    r_value    DOUBLE PRECISION    NOT NULL,
    r_error    DOUBLE PRECISION,
    i_value    DOUBLE PRECISION    NOT NULL,
    i_error    DOUBLE PRECISION,
    CONSTRAINT PZD00 PRIMARY KEY (key, row_key)
)
TABLESPACE IR_DATA
;



-- 
-- TABLE: Sensitivity 
--

CREATE TABLE Sensitivity(
    net            VARCHAR2(8)         NOT NULL,
    sta            VARCHAR2(6)         NOT NULL,
    seedchan       VARCHAR2(3)         NOT NULL,
    location       VARCHAR2(2)         NOT NULL,
    ondate         DATE                NOT NULL,
    stage_seq      NUMBER(8, 0)        NOT NULL,
    channel        VARCHAR2(8),
    channelsrc     VARCHAR2(8),
    offdate        DATE,
    sensitivity    DOUBLE PRECISION    NOT NULL,
    frequency      DOUBLE PRECISION,
    lddate         DATE                DEFAULT (SYSDATE),
    CONSTRAINT Se00 PRIMARY KEY (net, sta, seedchan, location, ondate, stage_seq)
)
TABLESPACE IR_DATA
;



-- 
-- TABLE: Simple_Response 
--

CREATE TABLE Simple_Response(
    net                  VARCHAR2(8)         NOT NULL,
    sta                  VARCHAR2(6)         NOT NULL,
    seedchan             VARCHAR2(3)         NOT NULL,
    location             VARCHAR2(2)         NOT NULL,
    ondate               DATE                NOT NULL,
    channel              VARCHAR2(8),
    channelsrc           VARCHAR2(8),
    natural_frequency    DOUBLE PRECISION,
    damping_constant     DOUBLE PRECISION,
    gain                 DOUBLE PRECISION,
    gain_units           VARCHAR2(20)        NOT NULL,
    low_freq_corner      DOUBLE PRECISION,
    high_freq_corner     DOUBLE PRECISION,
    offdate              DATE,
    lddate               DATE                DEFAULT (SYSDATE),
    dlogsens             NUMBER(8, 0),
    CONSTRAINT SiRe00 PRIMARY KEY (net, sta, seedchan, location, ondate)
)
TABLESPACE IR_DATA
;



-- 
-- TABLE: Station_Comment 
--

CREATE TABLE Station_Comment(
    net              VARCHAR2(8)     NOT NULL,
    sta              VARCHAR2(6)     NOT NULL,
    ondate           DATE            NOT NULL,
    comment_id       NUMBER(8, 0)    NOT NULL,
    offdate          DATE,
    comment_level    NUMBER(8, 0),
    lddate           DATE            DEFAULT (SYSDATE),
    CONSTRAINT StC00 PRIMARY KEY (net, sta, ondate, comment_id)
)
TABLESPACE IR_DATA
;



-- 
-- TABLE: Station_Data 
--

CREATE TABLE Station_Data(
    net        VARCHAR2(8)         NOT NULL,
    sta        VARCHAR2(6)         NOT NULL,
    ondate     DATE                NOT NULL,
    lat        DOUBLE PRECISION,
    lon        DOUBLE PRECISION,
    elev       DOUBLE PRECISION,
    staname    VARCHAR2(50),
    net_id     NUMBER(8, 0),
    word_32    NUMBER(8, 0)        NOT NULL,
    word_16    NUMBER(8, 0)        NOT NULL,
    offdate    DATE,
    lddate     DATE                DEFAULT (SYSDATE),
    CONSTRAINT StD00 PRIMARY KEY (net, sta, ondate)
)
TABLESPACE IR_DATA
;



-- 
-- TABLE: Channel_Comment 
--

ALTER TABLE Channel_Comment ADD CONSTRAINT Cha_Com_Cha_Dat 
    FOREIGN KEY (net, sta, seedchan, location, ondate)
    REFERENCES Channel_Data(net, sta, seedchan, location, ondate)
;

ALTER TABLE Channel_Comment ADD CONSTRAINT Cha_Com_D_Com 
    FOREIGN KEY (comment_id)
    REFERENCES D_Comment(id)
;


-- 
-- TABLE: Channel_Data 
--

ALTER TABLE Channel_Data ADD CONSTRAINT Cha_Dat_D_Abb 
    FOREIGN KEY (inid)
    REFERENCES D_Abbreviation(id)
;

ALTER TABLE Channel_Data ADD CONSTRAINT Cha_Dat_D_For 
    FOREIGN KEY (format_id)
    REFERENCES D_Format(id)
;

ALTER TABLE Channel_Data ADD CONSTRAINT Cha_Dat_D_Uni 
    FOREIGN KEY (unit_signal)
    REFERENCES D_Unit(id)
;

ALTER TABLE Channel_Data ADD CONSTRAINT Cha_Dat_D_Uni2 
    FOREIGN KEY (unit_calib)
    REFERENCES D_Unit(id)
;

ALTER TABLE Channel_Data ADD CONSTRAINT Cha_Dat_Sta_Dat 
    FOREIGN KEY (net, sta, ondate)
    REFERENCES Station_Data(net, sta, ondate)
;


-- 
-- TABLE: Coefficients 
--

ALTER TABLE Coefficients ADD CONSTRAINT Coe_Cha_Dat 
    FOREIGN KEY (net, sta, seedchan, location, ondate)
    REFERENCES Channel_Data(net, sta, seedchan, location, ondate)
;

ALTER TABLE Coefficients ADD CONSTRAINT Coe_D_Uni 
    FOREIGN KEY (unit_in)
    REFERENCES D_Unit(id)
;

ALTER TABLE Coefficients ADD CONSTRAINT Coe_D_Uni2 
    FOREIGN KEY (unit_out)
    REFERENCES D_Unit(id)
;

ALTER TABLE Coefficients ADD CONSTRAINT Coe_DC 
    FOREIGN KEY (dc_key)
    REFERENCES DC(key)
;


-- 
-- TABLE: D_Format_Data 
--

ALTER TABLE D_Format_Data ADD CONSTRAINT D_For_Dat_D_For 
    FOREIGN KEY (id)
    REFERENCES D_Format(id)
;


-- 
-- TABLE: DC_Data 
--

ALTER TABLE DC_Data ADD CONSTRAINT DC_Dat_DC 
    FOREIGN KEY (key)
    REFERENCES DC(key)
;


-- 
-- TABLE: Decimation 
--

ALTER TABLE Decimation ADD CONSTRAINT Dec_Cha_Dat 
    FOREIGN KEY (net, sta, seedchan, location, ondate)
    REFERENCES Channel_Data(net, sta, seedchan, location, ondate)
;

ALTER TABLE Decimation ADD CONSTRAINT Dec_DM 
    FOREIGN KEY (dm_key)
    REFERENCES DM(key)
;


-- 
-- TABLE: PN_Data 
--

ALTER TABLE PN_Data ADD CONSTRAINT PN_Data_PN 
    FOREIGN KEY (key)
    REFERENCES PN(key)
;


-- 
-- TABLE: Poles_Zeros 
--

ALTER TABLE Poles_Zeros ADD CONSTRAINT Pol_Zer_Cha_Dat 
    FOREIGN KEY (net, sta, seedchan, location, ondate)
    REFERENCES Channel_Data(net, sta, seedchan, location, ondate)
;

ALTER TABLE Poles_Zeros ADD CONSTRAINT Pol_Zer_D_Uni 
    FOREIGN KEY (unit_in)
    REFERENCES D_Unit(id)
;

ALTER TABLE Poles_Zeros ADD CONSTRAINT Pol_Zer_D_Uni2 
    FOREIGN KEY (unit_out)
    REFERENCES D_Unit(id)
;

ALTER TABLE Poles_Zeros ADD CONSTRAINT Pol_Zer_PZ 
    FOREIGN KEY (pz_key)
    REFERENCES PZ(key)
;


-- 
-- TABLE: Polynomial 
--

ALTER TABLE Polynomial ADD CONSTRAINT Pol_Cha_Dat 
    FOREIGN KEY (net, sta, seedchan, location, ondate)
    REFERENCES Channel_Data(net, sta, seedchan, location, ondate)
;

ALTER TABLE Polynomial ADD CONSTRAINT Pol_D_Uni 
    FOREIGN KEY (unit_in)
    REFERENCES D_Unit(id)
;

ALTER TABLE Polynomial ADD CONSTRAINT Pol_D_Uni2 
    FOREIGN KEY (unit_out)
    REFERENCES D_Unit(id)
;

ALTER TABLE Polynomial ADD CONSTRAINT Pol_PN 
    FOREIGN KEY (pn_key)
    REFERENCES PN(key)
;


-- 
-- TABLE: PZ_Data 
--

ALTER TABLE PZ_Data ADD CONSTRAINT PZ_Dat_PZ 
    FOREIGN KEY (key)
    REFERENCES PZ(key)
;


-- 
-- TABLE: Sensitivity 
--

ALTER TABLE Sensitivity ADD CONSTRAINT Sen_Cha_Dat 
    FOREIGN KEY (net, sta, seedchan, location, ondate)
    REFERENCES Channel_Data(net, sta, seedchan, location, ondate)
;


-- 
-- TABLE: Simple_Response 
--

ALTER TABLE Simple_Response ADD CONSTRAINT Sim_Res_Cha_Dat 
    FOREIGN KEY (net, sta, seedchan, location, ondate)
    REFERENCES Channel_Data(net, sta, seedchan, location, ondate)
;


-- 
-- TABLE: Station_Comment 
--

ALTER TABLE Station_Comment ADD CONSTRAINT Sta_Com_D_Com 
    FOREIGN KEY (comment_id)
    REFERENCES D_Comment(id)
;

ALTER TABLE Station_Comment ADD CONSTRAINT Sta_Com_Sta_Dat 
    FOREIGN KEY (net, sta, ondate)
    REFERENCES Station_Data(net, sta, ondate)
;


-- 
-- TABLE: Station_Data 
--

ALTER TABLE Station_Data ADD CONSTRAINT Sta_Dat_D_Abb 
    FOREIGN KEY (net_id)
    REFERENCES D_Abbreviation(id)
;