--
-- ER/Studio 8.0 SQL Code Generation
-- Company :      Berkeley Seismological Laboratory
-- Project :      CISN Instrument Responses
-- Author :       Stephane Zuzlewski
--
-- Date Created : Friday, January 09, 2009 10:43:32
-- Target DBMS : Oracle 10g
--

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
;
DROP SEQUENCE abbseq
;
DROP SEQUENCE comseq
;
DROP SEQUENCE dcseq
;
DROP SEQUENCE dmseq
;
DROP SEQUENCE forseq
;
DROP SEQUENCE poseq
;
DROP SEQUENCE pzseq
;
DROP SEQUENCE uniseq
;
-- 
-- SEQUENCE: abbseq 
--

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

-- 
-- SEQUENCE: comseq 
--

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

-- 
-- SEQUENCE: dcseq 
--

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

-- 
-- SEQUENCE: dmseq 
--

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

-- 
-- SEQUENCE: forseq 
--

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

-- 
-- SEQUENCE: poseq 
--

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

-- 
-- SEQUENCE: pzseq 
--

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

-- 
-- 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              BINARY_FLOAT,
    lon              BINARY_FLOAT,
    elev             BINARY_FLOAT,
    edepth           BINARY_FLOAT,
    azimuth          BINARY_FLOAT,
    dip              BINARY_FLOAT,
    format_id        NUMBER(8, 0)    NOT NULL,
    record_length    NUMBER(8, 0),
    samprate         BINARY_FLOAT    NOT NULL,
    clock_drift      BINARY_FLOAT,
    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    BINARY_FLOAT    NOT NULL,
    error          BINARY_FLOAT,
    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      BINARY_FLOAT    NOT NULL,
    factor        NUMBER(8, 0)    NOT NULL,
    offset        NUMBER(8, 0),
    delay         BINARY_FLOAT,
    correction    BINARY_FLOAT    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    BINARY_FLOAT,
    upper_bound    BINARY_FLOAT,
    max_error      BINARY_FLOAT,
    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    BINARY_FLOAT,
    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            BINARY_FLOAT    NOT NULL,
    AF            BINARY_FLOAT,
    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    BINARY_FLOAT    NOT NULL,
    r_error    BINARY_FLOAT,
    i_value    BINARY_FLOAT    NOT NULL,
    i_error    BINARY_FLOAT,
    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    BINARY_FLOAT    NOT NULL,
    frequency      BINARY_FLOAT,
    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    BINARY_FLOAT,
    damping_constant     BINARY_FLOAT,
    gain                 BINARY_FLOAT,
    gain_units           VARCHAR2(20)    NOT NULL,
    low_freq_corner      BINARY_FLOAT,
    high_freq_corner     BINARY_FLOAT,
    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        BINARY_FLOAT,
    lon        BINARY_FLOAT,
    elev       BINARY_FLOAT,
    staname    VARCHAR2(60),
    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)
;