--
--ER/Studio 5.5 SQL Code Generation
-- Company :      Berkeley Seismological Laboratory
-- Project :      NCEDC/TriNet Hardware Tracking
-- Author :       Stephane Zuzlewski
--
-- Date Created : Wednesday, November 20, 2002 12:58:27
-- Target DBMS : Oracle 9i
--


DROP TABLE Datalogger CASCADE CONSTRAINTS
;
DROP TABLE Datalogger_Board CASCADE CONSTRAINTS
;
DROP TABLE Datalogger_Module CASCADE CONSTRAINTS
;
DROP TABLE Filamp CASCADE CONSTRAINTS
;
DROP TABLE Filamp_PChannel CASCADE CONSTRAINTS
;
DROP TABLE Filter CASCADE CONSTRAINTS
;
DROP TABLE Filter_FIR CASCADE CONSTRAINTS
;
DROP TABLE Filter_FIR_Data CASCADE CONSTRAINTS
;
DROP TABLE Filter_Sequence CASCADE CONSTRAINTS
;
DROP TABLE Filter_Sequence_Data CASCADE CONSTRAINTS
;
DROP TABLE Response CASCADE CONSTRAINTS
;
DROP TABLE Response_HP CASCADE CONSTRAINTS
;
DROP TABLE Response_LP CASCADE CONSTRAINTS
;
DROP TABLE Response_PN CASCADE CONSTRAINTS
;
DROP TABLE Response_PN_Data CASCADE CONSTRAINTS
;
DROP TABLE Response_PZ CASCADE CONSTRAINTS
;
DROP TABLE Sensor CASCADE CONSTRAINTS
;
DROP TABLE Sensor_Component CASCADE CONSTRAINTS
;
DROP TABLE Station CASCADE CONSTRAINTS
;
DROP TABLE Station_Datalogger CASCADE CONSTRAINTS
;
DROP TABLE Station_Datalogger_LChannel CASCADE CONSTRAINTS
;
DROP TABLE Station_Datalogger_PChannel CASCADE CONSTRAINTS
;
DROP TABLE Station_Digitizer CASCADE CONSTRAINTS
;
DROP TABLE Station_Digitizer_PChannel CASCADE CONSTRAINTS
;
DROP TABLE Station_Filamp CASCADE CONSTRAINTS
;
DROP TABLE Station_Filamp_PChannel CASCADE CONSTRAINTS
;
DROP TABLE Station_Sensor CASCADE CONSTRAINTS
;
DROP TABLE Station_Sensor_Component CASCADE CONSTRAINTS
;
-- 
-- SEQUENCE: dataseq 
--

DROP SEQUENCE dataseq
;

CREATE SEQUENCE NCEDCDBA.DATASEQ 
    START WITH 1
    INCREMENT BY 1
    NOMINVALUE
    NOMAXVALUE
    NOCYCLE
    CACHE 20
    ORDER
;

-- 
-- SEQUENCE: filampseq 
--

DROP SEQUENCE filampseq
;

CREATE SEQUENCE NCEDCDBA.FILAMPSEQ 
    START WITH 1
    INCREMENT BY 1
    NOMINVALUE
    NOMAXVALUE
    NOCYCLE
    CACHE 20
    ORDER
;

-- 
-- SEQUENCE: filterseq 
--

DROP SEQUENCE filterseq
;

CREATE SEQUENCE NCEDCDBA.FILTERSEQ 
    START WITH 1
    INCREMENT BY 1
    NOMINVALUE
    NOMAXVALUE
    NOCYCLE
    CACHE 20
    ORDER
;

-- 
-- SEQUENCE: firseq 
--

DROP SEQUENCE firseq
;

CREATE SEQUENCE NCEDCDBA.FIRSEQ 
    START WITH 1
    INCREMENT BY 1
    NOMINVALUE
    NOMAXVALUE
    NOCYCLE
    CACHE 20
    ORDER
;

-- 
-- SEQUENCE: hpseq 
--

DROP SEQUENCE hpseq
;

CREATE SEQUENCE NCEDCDBA.HPSEQ 
    START WITH 1
    INCREMENT BY 1
    NOMINVALUE
    NOMAXVALUE
    NOCYCLE
    CACHE 20
    ORDER
;

-- 
-- SEQUENCE: lpseq 
--

DROP SEQUENCE lpseq
;

CREATE SEQUENCE NCEDCDBA.LPSEQ 
    START WITH 1
    INCREMENT BY 1
    NOMINVALUE
    NOMAXVALUE
    NOCYCLE
    CACHE 20
    ORDER
;

-- 
-- SEQUENCE: pnseq 
--

DROP SEQUENCE pnseq
;

CREATE SEQUENCE NCEDCDBA.PNSEQ 
    START WITH 1
    INCREMENT BY 1
    NOMINVALUE
    NOMAXVALUE
    NOCYCLE
    CACHE 20
    ORDER
;

-- 
-- SEQUENCE: pzseq 
--

DROP SEQUENCE pzseq
;

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

-- 
-- SEQUENCE: respseq 
--

DROP SEQUENCE respseq
;

CREATE SEQUENCE NCEDCDBA.RESPSEQ 
    START WITH 1
    INCREMENT BY 1
    NOMINVALUE
    NOMAXVALUE
    NOCYCLE
    CACHE 20
    ORDER
;

-- 
-- SEQUENCE: sensorseq 
--

DROP SEQUENCE sensorseq
;

CREATE SEQUENCE NCEDCDBA.SENSORSEQ 
    START WITH 1
    INCREMENT BY 1
    NOMINVALUE
    NOMAXVALUE
    NOCYCLE
    CACHE 20
    ORDER
;

-- 
-- SEQUENCE: seqfilseq 
--

DROP SEQUENCE seqfilseq
;

CREATE SEQUENCE NCEDCDBA.SEQFILSEQ 
    START WITH 1
    INCREMENT BY 1
    NOMINVALUE
    NOMAXVALUE
    NOCYCLE
    CACHE 20
    ORDER
;

-- 
-- TABLE: Datalogger 
--

CREATE TABLE Datalogger(
    data_id        NUMBER(8, 0)    NOT NULL,
    data_type      VARCHAR2(80),
    serial_nb      VARCHAR2(80),
    firmware_nb    VARCHAR2(80),
    software       VARCHAR2(80),
    software_nb    VARCHAR2(80),
    ondate         DATE            NOT NULL,
    offdate        DATE,
    nb_board       NUMBER(8, 0),
    word_32        NUMBER(8, 0)    NOT NULL,
    word_16        NUMBER(8, 0)    NOT NULL,
    lddate         DATE            DEFAULT (SYSDATE),
    CONSTRAINT Da01 CHECK (nb_board >= 0),
    CONSTRAINT Da00 PRIMARY KEY (data_id)
) 
TABLESPACE HT_DATA
;


COMMENT ON TABLE Datalogger IS 'Summary information on a datalogger'
;
COMMENT ON COLUMN Datalogger.data_id IS 'Datalogger identifier. Each datalogger is assigned a unique integer which identifies it in a database'
;
COMMENT ON COLUMN Datalogger.data_type IS 'This attribute represents a datalogger type'
;
COMMENT ON COLUMN Datalogger.serial_nb IS 'This attribute denotes a serial number of a datalogger'
;
COMMENT ON COLUMN Datalogger.firmware_nb IS 'This attribute describes the firmware number of a datalogger'
;
COMMENT ON COLUMN Datalogger.software IS 'Datalogger’s software (mshear, ushear, ...)'
;
COMMENT ON COLUMN Datalogger.software_nb IS 'Datalogger’s software version number'
;
COMMENT ON COLUMN Datalogger.ondate IS 'This attribute denotes the start date'
;
COMMENT ON COLUMN Datalogger.offdate IS 'This attribute denotes the end date'
;
COMMENT ON COLUMN Datalogger.nb_board IS 'This attribute denotes the number of boards of a datalogger'
;
COMMENT ON COLUMN Datalogger.word_32 IS 'The swap order in which 32-bit quantities are specified in the data headers'
;
COMMENT ON COLUMN Datalogger.word_16 IS '16-bit quantity byte swapping order, for the data headers only'
;
COMMENT ON COLUMN Datalogger.lddate IS 'Load date. Date and time that the record was created or last modified, in Oracle date datatype'
;
-- 
-- TABLE: Datalogger_Board 
--

CREATE TABLE Datalogger_Board(
    data_id        NUMBER(8, 0)    NOT NULL,
    board_nb       NUMBER(8, 0)    NOT NULL,
    serial_nb      VARCHAR2(80),
    nb_module      NUMBER(8, 0)    NOT NULL,
    firmware_nb    VARCHAR2(80),
    lddate         DATE            DEFAULT (SYSDATE),
    CONSTRAINT DaB01 CHECK (board_nb >= 1),
    CONSTRAINT DaB02 CHECK (nb_module >= 0),
    CONSTRAINT DaB00 PRIMARY KEY (data_id, board_nb), 
    CONSTRAINT Dat_Boa_Dat FOREIGN KEY (data_id)
    REFERENCES Datalogger(data_id)
) 
TABLESPACE HT_DATA
;


COMMENT ON TABLE Datalogger_Board IS 'Board of a datalogger'
;
COMMENT ON COLUMN Datalogger_Board.data_id IS 'Datalogger identifier. Each datalogger is assigned a unique integer which identifies it in a database'
;
COMMENT ON COLUMN Datalogger_Board.board_nb IS 'This attribute denotes the number of boards of a datalogger'
;
COMMENT ON COLUMN Datalogger_Board.serial_nb IS 'This attribute denotes a serial number of a datalogger board'
;
COMMENT ON COLUMN Datalogger_Board.nb_module IS 'This attribute denotes the number of modules of a datalogger’s board'
;
COMMENT ON COLUMN Datalogger_Board.firmware_nb IS 'This attribute describes the firmware number of a datalogger'
;
COMMENT ON COLUMN Datalogger_Board.lddate IS 'Load date. Date and time that the record was created or last modified, in Oracle date datatype'
;
-- 
-- TABLE: Datalogger_Module 
--

CREATE TABLE Datalogger_Module(
    data_id        NUMBER(8, 0)        NOT NULL,
    board_nb       NUMBER(8, 0)        NOT NULL,
    module_nb      NUMBER(8, 0)        NOT NULL,
    serial_nb      VARCHAR2(80),
    firmware_nb    VARCHAR2(80),
    sensitivity    DOUBLE PRECISION,
    lddate         DATE                DEFAULT (SYSDATE),
    CONSTRAINT DaM01 CHECK (board_nb >= 1),
    CONSTRAINT DaM02 CHECK (module_nb >= 1),
    CONSTRAINT DaM00 PRIMARY KEY (data_id, board_nb, module_nb), 
    CONSTRAINT Dat_Mod_Dat_Boa FOREIGN KEY (data_id,board_nb)
    REFERENCES Datalogger_Board(data_id,board_nb)
) 
TABLESPACE HT_DATA
;


COMMENT ON TABLE Datalogger_Module IS 'Module of a datalogger’s board'
;
COMMENT ON COLUMN Datalogger_Module.data_id IS 'Datalogger identifier. Each datalogger is assigned a unique integer which identifies it in a database'
;
COMMENT ON COLUMN Datalogger_Module.board_nb IS 'This attribute denotes the number of boards of a datalogger'
;
COMMENT ON COLUMN Datalogger_Module.module_nb IS 'This attribute represents the module number of a datalogger’s board'
;
COMMENT ON COLUMN Datalogger_Module.serial_nb IS 'This attribute denotes a serial number of a datalogger module'
;
COMMENT ON COLUMN Datalogger_Module.firmware_nb IS 'This attribute describes the firmware number of a datalogger'
;
COMMENT ON COLUMN Datalogger_Module.sensitivity IS 'This attribute denotes the sensitivity (Sd) for a sensor or a digitizer'
;
COMMENT ON COLUMN Datalogger_Module.lddate IS 'Load date. Date and time that the record was created or last modified, in Oracle date datatype'
;
-- 
-- TABLE: Filamp 
--

CREATE TABLE Filamp(
    filamp_id      NUMBER(8, 0)    NOT NULL,
    name           VARCHAR2(80),
    serial_nb      VARCHAR2(80),
    ondate         DATE            NOT NULL,
    offdate        DATE,
    nb_pchannel    NUMBER(8, 0)    NOT NULL,
    lddate         DATE            DEFAULT (SYSDATE),
    CONSTRAINT Filamp01 CHECK (nb_pchannel >= 0),
    CONSTRAINT Filamp00 PRIMARY KEY (filamp_id)
) 
TABLESPACE HT_DATA
;


COMMENT ON TABLE Filamp IS 'Summary information on a filter-amplifier'
;
COMMENT ON COLUMN Filamp.filamp_id IS 'Filter-amplifier identifier. Each filter-amplifier is assigned a unique integer which identifies it in a database'
;
COMMENT ON COLUMN Filamp.name IS 'This attribute denotes a descriptive name for a filter-amplifier'
;
COMMENT ON COLUMN Filamp.serial_nb IS 'This attribute denotes a serial number of a filter-amplifier'
;
COMMENT ON COLUMN Filamp.ondate IS 'This attribute denotes the start date'
;
COMMENT ON COLUMN Filamp.offdate IS 'This attribute denotes the end date'
;
COMMENT ON COLUMN Filamp.nb_pchannel IS 'This attribute denotes the number of physical channels of a datalogger'
;
COMMENT ON COLUMN Filamp.lddate IS 'Load date. Date and time that the record was created or last modified, in Oracle date datatype'
;
-- 
-- TABLE: Filamp_PChannel 
--

CREATE TABLE Filamp_PChannel(
    filamp_id      NUMBER(8, 0)        NOT NULL,
    pchannel_nb    NUMBER(8, 0)        NOT NULL,
    gain           DOUBLE PRECISION,
    frequency      DOUBLE PRECISION,
    seqresp_id     NUMBER(8, 0),
    lddate         DATE                DEFAULT (SYSDATE),
    CONSTRAINT FiP01 CHECK (pchannel_nb >= 1),
    CONSTRAINT FiP00 PRIMARY KEY (filamp_id, pchannel_nb), 
    CONSTRAINT Fil_PCh_Fil FOREIGN KEY (filamp_id)
    REFERENCES Filamp(filamp_id)
) 
TABLESPACE HT_DATA
;


COMMENT ON TABLE Filamp_PChannel IS 'Physical channel of a filter-amplifier'
;
COMMENT ON COLUMN Filamp_PChannel.filamp_id IS 'Filter-amplifier identifier. Each filter-amplifier is assigned a unique integer which identifies it in a database'
;
COMMENT ON COLUMN Filamp_PChannel.pchannel_nb IS 'This attribute denotes a physical channel'
;
COMMENT ON COLUMN Filamp_PChannel.gain IS 'This attribute denotes the gain'
;
COMMENT ON COLUMN Filamp_PChannel.frequency IS 'The frequency (fs) at which the value in sensitivity/gain is correct'
;
COMMENT ON COLUMN Filamp_PChannel.seqresp_id IS 'Sequence of responses identifier. Each sequence of responses is assigned a unique integer which identifies it in a database'
;
COMMENT ON COLUMN Filamp_PChannel.lddate IS 'Load date. Date and time that the record was created or last modified, in Oracle date datatype'
;
-- 
-- TABLE: Filter 
--

CREATE TABLE Filter(
    filter_id      NUMBER(8, 0)        NOT NULL,
    gain           DOUBLE PRECISION,
    frequency      DOUBLE PRECISION,
    in_sp_rate     DOUBLE PRECISION,
    out_sp_rate    DOUBLE PRECISION,
    offset         NUMBER(8, 0),
    delay          DOUBLE PRECISION,
    correction     DOUBLE PRECISION    NOT NULL,
    seqresp_id     NUMBER(8, 0),
    lddate         DATE                DEFAULT (SYSDATE),
    CONSTRAINT Fi01 CHECK (frequency >= 0.0),
    CONSTRAINT Fi02 CHECK (in_sp_rate > 0.0),
    CONSTRAINT Fi03 CHECK (out_sp_rate > 0.0),
    CONSTRAINT Fi04 CHECK (offset >= 0),
    CONSTRAINT Fi00 PRIMARY KEY (filter_id)
) 
TABLESPACE HT_DATA
;


COMMENT ON TABLE Filter IS 'Summary information on a filter'
;
COMMENT ON COLUMN Filter.filter_id IS 'Filter identifier. Each filter is assigned a unique integer which identifies it in a database'
;
COMMENT ON COLUMN Filter.gain IS 'This attribute denotes the gain'
;
COMMENT ON COLUMN Filter.frequency IS 'The frequency (fs) at which the value in sensitivity/gain is correct'
;
COMMENT ON COLUMN Filter.in_sp_rate IS 'Sampling rate. This attribute is the input sample rate in samples/second'
;
COMMENT ON COLUMN Filter.out_sp_rate IS 'Sampling rate. This attribute is the output sample rate in samples/second'
;
COMMENT ON COLUMN Filter.offset IS 'This field determines which sample is chosen for use. The value of this field has to be greater than or equal to zero, but less than the decimation factor'
;
COMMENT ON COLUMN Filter.delay IS 'The estimated pure delay for the filter'
;
COMMENT ON COLUMN Filter.correction IS 'The time shift applied to the time tag due to delay at this stage of the filter; a negative number indicating the amount of time added to the former time tag. The actual delay is difficult to estimate, and the correction applied neglects dispersion. This field allows to know how much correction was used, in case a more accurate correction is to be applied later. A zero here implies no correction was done'
;
COMMENT ON COLUMN Filter.seqresp_id IS 'Sequence of responses identifier. Each sequence of responses is assigned a unique integer which identifies it in a database'
;
COMMENT ON COLUMN Filter.lddate IS 'Load date. Date and time that the record was created or last modified, in Oracle date datatype'
;
-- 
-- TABLE: Filter_FIR 
--

CREATE TABLE Filter_FIR(
    fir_id      NUMBER(8, 0)        NOT NULL,
    name        VARCHAR2(80),
    symmetry    VARCHAR2(1)         NOT NULL,
    gain        DOUBLE PRECISION,
    lddate      DATE                DEFAULT (SYSDATE),
    CONSTRAINT FFIR01 CHECK (symmetry IN ('E','O','N')),
    CONSTRAINT FFIR00 PRIMARY KEY (fir_id)
) 
TABLESPACE HT_DATA
;


COMMENT ON TABLE Filter_FIR IS 'Summary information on a FIR filter'
;
COMMENT ON COLUMN Filter_FIR.fir_id IS 'FIR Filter identifier. Each FIR filter is assigned a unique integer which identifies it in a database'
;
COMMENT ON COLUMN Filter_FIR.name IS 'This attribute denotes a descriptive name for a FIR filter'
;
COMMENT ON COLUMN Filter_FIR.symmetry IS 'This attribute denotes the eventual symmetry of a response'
;
COMMENT ON COLUMN Filter_FIR.gain IS 'This attribute denotes the gain'
;
COMMENT ON COLUMN Filter_FIR.lddate IS 'Load date. Date and time that the record was created or last modified, in Oracle date datatype'
;
-- 
-- TABLE: Filter_FIR_Data 
--

CREATE TABLE Filter_FIR_Data(
    fir_id         NUMBER(8, 0)        NOT NULL,
    coeff_nb       NUMBER(8, 0)        NOT NULL,
    type           VARCHAR2(1)         NOT NULL,
    coefficient    DOUBLE PRECISION    NOT NULL,
    error          DOUBLE PRECISION,
    CONSTRAINT FFD01 CHECK (coeff_nb >= 1),
    CONSTRAINT FFD02 CHECK (type IN ('N','D')),
    CONSTRAINT FFD00 PRIMARY KEY (fir_id, coeff_nb), 
    CONSTRAINT Fil_FIR_Dat_Fil_FIR FOREIGN KEY (fir_id)
    REFERENCES Filter_FIR(fir_id)
) 
TABLESPACE HT_DATA
;


COMMENT ON TABLE Filter_FIR_Data IS 'Coefficients of a FIR filter'
;
COMMENT ON COLUMN Filter_FIR_Data.fir_id IS 'FIR Filter identifier. Each FIR filter is assigned a unique integer which identifies it in a database'
;
COMMENT ON COLUMN Filter_FIR_Data.coeff_nb IS 'The numerator/denominator number'
;
COMMENT ON COLUMN Filter_FIR_Data.type IS 'This attribute denotes whether a value is associated with a numerator/denominator'
;
COMMENT ON COLUMN Filter_FIR_Data.coefficient IS 'The numerator/denominator coefficient value'
;
COMMENT ON COLUMN Filter_FIR_Data.error IS 'This attributes denotes the numerator/denominator error'
;
-- 
-- TABLE: Filter_Sequence 
--

CREATE TABLE Filter_Sequence(
    seqfil_id    NUMBER(8, 0)        NOT NULL,
    name         VARCHAR2(32)        NOT NULL,
    nb_filter    NUMBER(8, 0)        NOT NULL,
    gain         DOUBLE PRECISION,
    frequency    DOUBLE PRECISION,
    lddate       DATE                DEFAULT (SYSDATE),
    CONSTRAINT FiS01 CHECK (frequency >= 0.0),
    CONSTRAINT FiS02 CHECK (nb_filter >= 0),
    CONSTRAINT FiS00 PRIMARY KEY (seqfil_id)
) 
TABLESPACE HT_DATA
;


COMMENT ON TABLE Filter_Sequence IS 'Summary information on a sequence of filters'
;
COMMENT ON COLUMN Filter_Sequence.seqfil_id IS 'Sequence of filters identifier. Each sequence of filters is assigned a unique integer which identifies it in a database'
;
COMMENT ON COLUMN Filter_Sequence.name IS 'This attribute denotes a descriptive name for a chain of filters'
;
COMMENT ON COLUMN Filter_Sequence.nb_filter IS 'This attribute denotes the number of filters of a sequence'
;
COMMENT ON COLUMN Filter_Sequence.gain IS 'This attribute denotes the gain'
;
COMMENT ON COLUMN Filter_Sequence.frequency IS 'The frequency (fs) at which the value in sensitivity/gain is correct'
;
COMMENT ON COLUMN Filter_Sequence.lddate IS 'Load date. Date and time that the record was created or last modified, in Oracle date datatype'
;
-- 
-- TABLE: Filter_Sequence_Data 
--

CREATE TABLE Filter_Sequence_Data(
    seqfil_id    NUMBER(8, 0)    NOT NULL,
    filter_nb    NUMBER(8, 0)    NOT NULL,
    filter_id    NUMBER(8, 0)    NOT NULL,
    CONSTRAINT FiSeD01 CHECK (filter_nb >= 1),
    CONSTRAINT FiSeD00 PRIMARY KEY (seqfil_id, filter_nb), 
    CONSTRAINT Fil_Seq_Dat_Fil FOREIGN KEY (filter_id)
    REFERENCES Filter(filter_id),
    CONSTRAINT Fil_Seq_Dat_Fil_Seq FOREIGN KEY (seqfil_id)
    REFERENCES Filter_Sequence(seqfil_id)
) 
TABLESPACE HT_DATA
;


COMMENT ON TABLE Filter_Sequence_Data IS 'Filter of a sequence'
;
COMMENT ON COLUMN Filter_Sequence_Data.seqfil_id IS 'Sequence of filters identifier. Each sequence of filters is assigned a unique integer which identifies it in a database'
;
COMMENT ON COLUMN Filter_Sequence_Data.filter_nb IS 'This attribute represents the filter number in a sequence'
;
COMMENT ON COLUMN Filter_Sequence_Data.filter_id IS 'Filter identifier. Each filter is assigned a unique integer which identifies it in a database'
;
-- 
-- TABLE: Response_HP 
--

CREATE TABLE Response_HP(
    hp_id            NUMBER(8, 0)        NOT NULL,
    filter_type      VARCHAR2(2)         NOT NULL,
    nb_pole          NUMBER(8, 0)        NOT NULL,
    corner_freq      DOUBLE PRECISION    NOT NULL,
    damping_value    DOUBLE PRECISION    NOT NULL,
    lddate           DATE                DEFAULT (SYSDATE),
    CONSTRAINT RHP01 CHECK (filter_type IN ('BW','DG','ND')),
    CONSTRAINT RHP02 CHECK (nb_pole > 0),
    CONSTRAINT RHP00 PRIMARY KEY (hp_id)
) 
TABLESPACE HT_DATA
;


COMMENT ON TABLE Response_HP IS 'Summary information on a high-pass filter'
;
COMMENT ON COLUMN Response_HP.hp_id IS 'High-pass filter identifier. Each high-pass filter is assigned a unique integer which identifies it in a database'
;
COMMENT ON COLUMN Response_HP.filter_type IS 'This attribute denotes the type of a high-pass filter'
;
COMMENT ON COLUMN Response_HP.nb_pole IS 'This attribute denotes the number of poles of a high-pass filter'
;
COMMENT ON COLUMN Response_HP.corner_freq IS 'This attribute represents the corner frequency of a high-pass filter'
;
COMMENT ON COLUMN Response_HP.damping_value IS 'This attribute represents the fraction of critical damping of a high-pass filter'
;
COMMENT ON COLUMN Response_HP.lddate IS 'Load date. Date and time that the record was created or last modified, in Oracle date datatype'
;
-- 
-- TABLE: Response_LP 
--

CREATE TABLE Response_LP(
    lp_id            NUMBER(8, 0)        NOT NULL,
    filter_type      VARCHAR2(2),
    nb_pole          NUMBER(8, 0),
    corner_freq      DOUBLE PRECISION    NOT NULL,
    damping_value    DOUBLE PRECISION    NOT NULL,
    lddate           DATE                DEFAULT (SYSDATE),
    CONSTRAINT RLP01 CHECK (filter_type IN ('BW','DG','ND')),
    CONSTRAINT RLP02 CHECK (nb_pole > 0),
    CONSTRAINT RLP00 PRIMARY KEY (lp_id)
) 
TABLESPACE HT_DATA
;


COMMENT ON TABLE Response_LP IS 'Summary information on a low-pass filter'
;
COMMENT ON COLUMN Response_LP.lp_id IS 'Low-pass filter identifier. Each low-pass filter is assigned a unique integer which identifies it in a database'
;
COMMENT ON COLUMN Response_LP.filter_type IS 'This attribute denotes the type of a low-pass filter'
;
COMMENT ON COLUMN Response_LP.nb_pole IS 'This attribute denotes the number of poles of a low-pass filter'
;
COMMENT ON COLUMN Response_LP.corner_freq IS 'This attribute represents the corner frequency of a low-pass filter'
;
COMMENT ON COLUMN Response_LP.damping_value IS 'This attribute represents the fraction of critical damping of a low-pass filter'
;
COMMENT ON COLUMN Response_LP.lddate IS 'Load date. Date and time that the record was created or last modified, in Oracle date datatype'
;
-- 
-- TABLE: Response_PN 
--

CREATE TABLE Response_PN(
    pn_id          NUMBER(8, 0)        NOT NULL,
    name           VARCHAR2(80),
    poly_type      VARCHAR2(1)         NOT NULL,
    lower_bound    DOUBLE PRECISION,
    upper_bound    DOUBLE PRECISION,
    max_error      DOUBLE PRECISION,
    nb_coeff       NUMBER(8, 0),
    lddate         DATE                DEFAULT (SYSDATE),
    CONSTRAINT RPN01 CHECK (nb_coeff >= 0),
    CONSTRAINT RPN02 CHECK (poly_type IN ('C','L','M')),
    CONSTRAINT RPN00 PRIMARY KEY (pn_id)
) 
TABLESPACE HT_DATA
;


COMMENT ON TABLE Response_PN IS 'Summary information on a polynomial'
;
COMMENT ON COLUMN Response_PN.pn_id IS 'Polynomial identifier. Each polynomial is assigned a unique integer which identifies it in a database'
;
COMMENT ON COLUMN Response_PN.name IS 'This attribute denotes a descriptive name for a polynomial'
;
COMMENT ON COLUMN Response_PN.poly_type IS 'This attribute represents the type of polynomial approximation'
;
COMMENT ON COLUMN Response_PN.lower_bound IS 'This attribute denotes the polynomial lower bound'
;
COMMENT ON COLUMN Response_PN.upper_bound IS 'This attribute denotes the polynomial upper bound'
;
COMMENT ON COLUMN Response_PN.max_error IS 'This attribute represents the maximum error of polynomial approximation'
;
COMMENT ON COLUMN Response_PN.nb_coeff IS 'This attribute denotes the number of polynomial coefficients'
;
COMMENT ON COLUMN Response_PN.lddate IS 'Load date. Date and time that the record was created or last modified, in Oracle date datatype'
;
-- 
-- TABLE: Response 
--

CREATE TABLE Response(
    seqresp_id    NUMBER(8, 0)    NOT NULL,
    resp_nb       NUMBER(8, 0)    NOT NULL,
    resp_type     VARCHAR2(1)     NOT NULL,
    resp_id       NUMBER(8, 0)    NOT NULL,
    unit_in       NUMBER(8, 0)    NOT NULL,
    unit_out      NUMBER(8, 0)    NOT NULL,
    r_type        VARCHAR2(1),
    lddate        DATE            DEFAULT (SYSDATE),
    CONSTRAINT Re01 CHECK (r_type IN ('A','B','C','D','P')),
    CONSTRAINT Re02 CHECK (resp_nb >= 1),
    CONSTRAINT Re03 CHECK (resp_type IN ('H','L','P','Z','F','N')),
    CONSTRAINT Re00 PRIMARY KEY (seqresp_id, resp_nb), 
    CONSTRAINT Res_Fil_FIR FOREIGN KEY (resp_id)
    REFERENCES Filter_FIR(fir_id),
    CONSTRAINT Res_Res_HP FOREIGN KEY (resp_id)
    REFERENCES Response_HP(hp_id),
    CONSTRAINT Res_Res_LP FOREIGN KEY (resp_id)
    REFERENCES Response_LP(lp_id),
    CONSTRAINT Res_Res_PN FOREIGN KEY (resp_id)
    REFERENCES Response_PN(pn_id)
) 
TABLESPACE HT_DATA
;


COMMENT ON TABLE Response IS 'Summary information on a sequence of responses'
;
COMMENT ON COLUMN Response.seqresp_id IS 'Sequence of responses identifier. Each sequence of responses is assigned a unique integer which identifies it in a database'
;
COMMENT ON COLUMN Response.resp_nb IS 'This attribute denotes the response number of a sequence'
;
COMMENT ON COLUMN Response.resp_type IS 'This attribute denotes the response type'
;
COMMENT ON COLUMN Response.resp_id IS 'Response identifier. Each response is assigned a unique integer which identifies it in a database'
;
COMMENT ON COLUMN Response.unit_in IS 'A unit lookup key that refers to the Units Abbreviation Dictionary for the units of the incoming to this stage of the filter. This signal will usually be ground motion, volts, or counts, depending on where it is in the filter system'
;
COMMENT ON COLUMN Response.unit_out IS 'A unit lookup key that refers to the Units Abbreviation Dictionary for the stage’s output signal. Analog filters usually emit volts, and digital filters usually emit counts'
;
COMMENT ON COLUMN Response.r_type IS 'A single character describing the type of response'
;
COMMENT ON COLUMN Response.lddate IS 'Load date. Date and time that the record was created or last modified, in Oracle date datatype'
;
-- 
-- TABLE: Response_PN_Data 
--

CREATE TABLE Response_PN_Data(
    pn_id       NUMBER(8, 0)        NOT NULL,
    pn_nb       NUMBER(8, 0)        NOT NULL,
    pn_value    DOUBLE PRECISION    NOT NULL,
    CONSTRAINT RPND01 CHECK (pn_nb >= 1),
    CONSTRAINT RPND00 PRIMARY KEY (pn_id, pn_nb), 
    CONSTRAINT Res_PN_Dat_Res_PN FOREIGN KEY (pn_id)
    REFERENCES Response_PN(pn_id)
) 
TABLESPACE HT_DATA
;


COMMENT ON TABLE Response_PN_Data IS 'Coefficients of a polynomial'
;
COMMENT ON COLUMN Response_PN_Data.pn_id IS 'Polynomial identifier. Each polynomial is assigned a unique integer which identifies it in a database'
;
COMMENT ON COLUMN Response_PN_Data.pn_nb IS 'This attribute denotes the coefficient number of a polynomial'
;
COMMENT ON COLUMN Response_PN_Data.pn_value IS 'This attribute denotes the coefficient value of a polynomial'
;
-- 
-- TABLE: Response_PZ 
--

CREATE TABLE Response_PZ(
    pz_id      NUMBER(8, 0)        NOT NULL,
    pz_nb      NUMBER(8, 0)        NOT NULL,
    type       VARCHAR2(1)         NOT NULL,
    r_value    DOUBLE PRECISION    NOT NULL,
    r_error    DOUBLE PRECISION,
    i_value    DOUBLE PRECISION    NOT NULL,
    i_error    DOUBLE PRECISION,
    lddate     DATE                DEFAULT (SYSDATE),
    CONSTRAINT RPZ01 CHECK (pz_nb >= 1),
    CONSTRAINT RPZ02 CHECK (type IN ('P','Z')),
    CONSTRAINT RPZ00 PRIMARY KEY (pz_id, pz_nb)
) 
TABLESPACE HT_DATA
;


COMMENT ON TABLE Response_PZ IS 'Summary information on poles & zeros'
;
COMMENT ON COLUMN Response_PZ.pz_id IS 'Poles & zeros set identifier. Each poles & zeros set is assigned a unique integer which identifies it in a database'
;
COMMENT ON COLUMN Response_PZ.pz_nb IS 'This attribute denotes the pole/zero number of a response'
;
COMMENT ON COLUMN Response_PZ.type IS 'This attribute denotes whether a value is associated with a pole/zero'
;
COMMENT ON COLUMN Response_PZ.r_value IS 'The real portion of a complex pole/zero'
;
COMMENT ON COLUMN Response_PZ.r_error IS 'The error value for the real portion of a complex pole/zero'
;
COMMENT ON COLUMN Response_PZ.i_value IS 'The imaginary portion of a complex pole/zero'
;
COMMENT ON COLUMN Response_PZ.i_error IS 'The error value for the imaginary portion of a complex pole/zero'
;
COMMENT ON COLUMN Response_PZ.lddate IS 'Load date. Date and time that the record was created or last modified, in Oracle date datatype'
;
-- 
-- TABLE: Sensor 
--

CREATE TABLE Sensor(
    sensor_id       NUMBER(8, 0)    NOT NULL,
    name            VARCHAR2(80),
    serial_nb       VARCHAR2(80),
    ondate          DATE            NOT NULL,
    offdate         DATE,
    nb_component    NUMBER(8, 0)    NOT NULL,
    lddate          DATE            DEFAULT (SYSDATE),
    CONSTRAINT Sensor01 CHECK (nb_component >= 0),
    CONSTRAINT Sensor00 PRIMARY KEY (sensor_id)
) 
TABLESPACE HT_DATA
;


COMMENT ON TABLE Sensor IS 'Summary information on a sensor'
;
COMMENT ON COLUMN Sensor.sensor_id IS 'Sensor identifier. Each sensor is assigned a unique integer which identifies it in a database'
;
COMMENT ON COLUMN Sensor.name IS 'This attribute denotes a descriptive name for a sensor'
;
COMMENT ON COLUMN Sensor.serial_nb IS 'This attribute denotes a serial number of a sensor'
;
COMMENT ON COLUMN Sensor.ondate IS 'This attribute denotes the start date'
;
COMMENT ON COLUMN Sensor.offdate IS 'This attribute denotes the end date'
;
COMMENT ON COLUMN Sensor.nb_component IS 'This attribute denotes the number of components of a sensor'
;
COMMENT ON COLUMN Sensor.lddate IS 'Load date. Date and time that the record was created or last modified, in Oracle date datatype'
;
-- 
-- TABLE: Sensor_Component 
--

CREATE TABLE Sensor_Component(
    sensor_id         NUMBER(8, 0)        NOT NULL,
    component_nb      NUMBER(8, 0)        NOT NULL,
    channel_comp      VARCHAR2(2),
    component_type    VARCHAR2(1),
    sensitivity       DOUBLE PRECISION    NOT NULL,
    frequency         DOUBLE PRECISION,
    seqresp_id        NUMBER(8, 0),
    lddate            DATE                DEFAULT (SYSDATE),
    CONSTRAINT SeC01 CHECK (component_nb >= 1),
    CONSTRAINT SeC02 CHECK (frequency >= 0.0),
    CONSTRAINT SeC00 PRIMARY KEY (sensor_id, component_nb), 
    CONSTRAINT Sen_Com_Sen FOREIGN KEY (sensor_id)
    REFERENCES Sensor(sensor_id)
) 
TABLESPACE HT_DATA
;


COMMENT ON TABLE Sensor_Component IS 'Component of a sensor'
;
COMMENT ON COLUMN Sensor_Component.sensor_id IS 'Sensor identifier. Each sensor is assigned a unique integer which identifies it in a database'
;
COMMENT ON COLUMN Sensor_Component.component_nb IS 'This attribute represents the component number'
;
COMMENT ON COLUMN Sensor_Component.channel_comp IS 'Component channel'
;
COMMENT ON COLUMN Sensor_Component.component_type IS 'This attribute denotes the component type'
;
COMMENT ON COLUMN Sensor_Component.sensitivity IS 'This attribute denotes the sensitivity (Sd) for a sensor or a digitizer'
;
COMMENT ON COLUMN Sensor_Component.frequency IS 'The frequency (fs) at which the value in sensitivity/gain is correct'
;
COMMENT ON COLUMN Sensor_Component.seqresp_id IS 'Sequence of responses identifier. Each sequence of responses is assigned a unique integer which identifies it in a database'
;
COMMENT ON COLUMN Sensor_Component.lddate IS 'Load date. Date and time that the record was created or last modified, in Oracle date datatype'
;
-- 
-- TABLE: Station 
--

CREATE TABLE Station(
    sta          VARCHAR2(6)         NOT NULL,
    net          VARCHAR2(8)         NOT NULL,
    lat          DOUBLE PRECISION,
    lon          DOUBLE PRECISION,
    elev         DOUBLE PRECISION,
    staname      VARCHAR2(50),
    nb_sensor    NUMBER(8, 0),
    nb_filamp    NUMBER(8, 0),
    nb_digi      NUMBER(8, 0)        NOT NULL,
    nb_data      NUMBER(8, 0)        NOT NULL,
    datumhor     VARCHAR2(8),
    datumver     VARCHAR2(8),
    ondate       DATE                NOT NULL,
    offdate      DATE,
    lddate       DATE                DEFAULT (SYSDATE),
    CONSTRAINT St03 CHECK (elev >= -5000.0 AND elev <= 5000.0),
    CONSTRAINT St04 CHECK (lat >= -90.0 AND lat <= 90.0),
    CONSTRAINT St05 CHECK (lon >= -180.0 AND lon <= 180.0),
    CONSTRAINT St06 CHECK (nb_data >= 0),
    CONSTRAINT St07 CHECK (nb_digi >= 0),
    CONSTRAINT St08 CHECK (nb_filamp >= 0),
    CONSTRAINT St09 CHECK (nb_sensor >= 0),
    CONSTRAINT St01 CHECK (datumhor IN ('NAD27','WGS84')),
    CONSTRAINT St02 CHECK (datumver IN ('NAD27','WGS84','AVERAGE')),
    CONSTRAINT St00 PRIMARY KEY (sta, net, ondate)
) 
TABLESPACE HT_DATA
;


COMMENT ON TABLE Station IS 'Summary information on a station'
;
COMMENT ON COLUMN Station.sta IS 'Station code. This is the common code-name of a seismic observatory. Generally only three or four characters are used'
;
COMMENT ON COLUMN Station.net IS 'Unique network identifier. This character string is the name of a seismic network'
;
COMMENT ON COLUMN Station.lat IS 'Latitude. This attribute is the geographic latitude. Locations north of the equator have positive latitudes'
;
COMMENT ON COLUMN Station.lon IS 'Longitude. This attribute is the geographic longitude. Longitudes are measured positive east of the Greenwich meridian'
;
COMMENT ON COLUMN Station.elev IS 'Elevation. This attribute is the elevation of a seismic station relative to mean sea level'
;
COMMENT ON COLUMN Station.staname IS 'Station name/description. This is the full name of the station whose code-name is in sta'
;
COMMENT ON COLUMN Station.nb_sensor IS 'This attribute denotes the number of sensors of a station'
;
COMMENT ON COLUMN Station.nb_filamp IS 'This attribute denotes the number of filter-amplifiers of a station'
;
COMMENT ON COLUMN Station.nb_digi IS 'This attribute denotes the number of digitizers of a station'
;
COMMENT ON COLUMN Station.nb_data IS 'This attribute denotes the number of dataloggers of a station'
;
COMMENT ON COLUMN Station.datumhor IS 'Datum type for horizontal (latitude and longitude) coordinates'
;
COMMENT ON COLUMN Station.datumver IS 'Datum type for depth or elevation'
;
COMMENT ON COLUMN Station.ondate IS 'This attribute denotes the start date'
;
COMMENT ON COLUMN Station.offdate IS 'This attribute denotes the end date'
;
COMMENT ON COLUMN Station.lddate IS 'Load date. Date and time that the record was created or last modified, in Oracle date datatype'
;
-- 
-- TABLE: Station_Datalogger 
--

CREATE TABLE Station_Datalogger(
    sta            VARCHAR2(6)     NOT NULL,
    net            VARCHAR2(8)     NOT NULL,
    data_nb        NUMBER(8, 0)    NOT NULL,
    ondate         DATE            NOT NULL,
    data_id        NUMBER(8, 0)    NOT NULL,
    nb_pchannel    NUMBER(8, 0)    NOT NULL,
    offdate        DATE,
    lddate         DATE            DEFAULT (SYSDATE),
    CONSTRAINT StDa01 CHECK (data_nb >= 1),
    CONSTRAINT StDa02 CHECK (nb_pchannel >= 0),
    CONSTRAINT StDa00 PRIMARY KEY (sta, net, data_nb, ondate), 
    CONSTRAINT Sta_Dat_Dat FOREIGN KEY (data_id)
    REFERENCES Datalogger(data_id),
    CONSTRAINT Sta_Dat_Sta FOREIGN KEY (sta,net,ondate)
    REFERENCES Station(sta,net,ondate)
) 
TABLESPACE HT_DATA
;


COMMENT ON TABLE Station_Datalogger IS 'Data associating a station with a datalogger'
;
COMMENT ON COLUMN Station_Datalogger.sta IS 'Station code. This is the common code-name of a seismic observatory. Generally only three or four characters are used'
;
COMMENT ON COLUMN Station_Datalogger.net IS 'Unique network identifier. This character string is the name of a seismic network'
;
COMMENT ON COLUMN Station_Datalogger.data_nb IS 'This attribute represents the datalogger number'
;
COMMENT ON COLUMN Station_Datalogger.ondate IS 'This attribute denotes the start date'
;
COMMENT ON COLUMN Station_Datalogger.data_id IS 'Datalogger identifier. Each datalogger is assigned a unique integer which identifies it in a database'
;
COMMENT ON COLUMN Station_Datalogger.nb_pchannel IS 'This attribute denotes the number of physical channels of a datalogger'
;
COMMENT ON COLUMN Station_Datalogger.offdate IS 'This attribute denotes the end date'
;
COMMENT ON COLUMN Station_Datalogger.lddate IS 'Load date. Date and time that the record was created or last modified, in Oracle date datatype'
;
-- 
-- TABLE: Station_Datalogger_PChannel 
--

CREATE TABLE Station_Datalogger_PChannel(
    sta             VARCHAR2(6)     NOT NULL,
    net             VARCHAR2(8)     NOT NULL,
    data_nb         NUMBER(8, 0)    NOT NULL,
    pchannel_nb     NUMBER(8, 0)    NOT NULL,
    ondate          DATE            NOT NULL,
    board_type      VARCHAR2(1)     NOT NULL,
    channel_type    VARCHAR2(1)     NOT NULL,
    seed_io         VARCHAR2(2)     NOT NULL,
    nb_lchannel     NUMBER(8, 0)    NOT NULL,
    offdate         DATE,
    lddate          DATE            DEFAULT (SYSDATE),
    CONSTRAINT StDaP01 CHECK (data_nb >= 1),
    CONSTRAINT StDaP02 CHECK (nb_lchannel >= 1),
    CONSTRAINT StDaP03 CHECK (pchannel_nb >= 1),
    CONSTRAINT StDaP04 CHECK (board_type IN ('P', 'A', 'E', 'D')),
    CONSTRAINT StDaP05 CHECK (channel_type IN ('P', 'S')),
    CONSTRAINT StDaP00 PRIMARY KEY (sta, net, data_nb, pchannel_nb, ondate), 
    CONSTRAINT Sta_Dat_PCh_Sta_Dat FOREIGN KEY (sta,net,data_nb,ondate)
    REFERENCES Station_Datalogger(sta,net,data_nb,ondate)
) 
TABLESPACE HT_DATA
;


COMMENT ON TABLE Station_Datalogger_PChannel IS 'Data associating channels'
;
COMMENT ON COLUMN Station_Datalogger_PChannel.sta IS 'Station code. This is the common code-name of a seismic observatory. Generally only three or four characters are used'
;
COMMENT ON COLUMN Station_Datalogger_PChannel.net IS 'Unique network identifier. This character string is the name of a seismic network'
;
COMMENT ON COLUMN Station_Datalogger_PChannel.data_nb IS 'This attribute represents the datalogger number'
;
COMMENT ON COLUMN Station_Datalogger_PChannel.pchannel_nb IS 'This attribute denotes a physical channel'
;
COMMENT ON COLUMN Station_Datalogger_PChannel.ondate IS 'This attribute denotes the start date'
;
COMMENT ON COLUMN Station_Datalogger_PChannel.board_type IS 'This attribute describes the board type on the datalogger where the physical channel resides. Regular seismic channels are usually on a primary board. Auxiliary channels (internal temperature, external power, …) are usually on an auxiliary board. A value of external is used to represent a digitizer that is not part of the datalogger (for example an embedded digitizer in a digital pressure sensor). A digital board is used to represent truly digital sensors which don’t actually have any digitizing stage from Volts to Counts (for example a rainfall sensor that uses a digital counter)'
;
COMMENT ON COLUMN Station_Datalogger_PChannel.channel_type IS 'This attribute describes the type of the input channel. An input channel is usually classified as physical when the data is digitized (velocity, acceleration, ...). An input channel is defined as synthetic when the data is not derived directly from a sensor (clock frequency, available telemetry buffer channel, clock quality, ...)'
;
COMMENT ON COLUMN Station_Datalogger_PChannel.seed_io IS 'This attribute represents the SEED instrument and orientation'
;
COMMENT ON COLUMN Station_Datalogger_PChannel.nb_lchannel IS 'This attribute denotes the number of logical channels of a datalogger'
;
COMMENT ON COLUMN Station_Datalogger_PChannel.offdate IS 'This attribute denotes the end date'
;
COMMENT ON COLUMN Station_Datalogger_PChannel.lddate IS 'Load date. Date and time that the record was created or last modified, in Oracle date datatype'
;
-- 
-- TABLE: Station_Datalogger_LChannel 
--

CREATE TABLE Station_Datalogger_LChannel(
    sta            VARCHAR2(6)         NOT NULL,
    net            VARCHAR2(8)         NOT NULL,
    data_nb        NUMBER(8, 0)        NOT NULL,
    pchannel_nb    NUMBER(8, 0)        NOT NULL,
    lchannel_nb    NUMBER(8, 0)        NOT NULL,
    ondate         DATE                NOT NULL,
    seqfil_id      NUMBER(8, 0),
    seedchan       VARCHAR2(3),
    channel        VARCHAR2(3),
    channelsrc     VARCHAR2(8),
    location       VARCHAR2(2),
    rgain          DOUBLE PRECISION,
    rfrequency     DOUBLE PRECISION,
    samprate       DOUBLE PRECISION    NOT NULL,
    clock_drift    DOUBLE PRECISION,
    flags          VARCHAR2(27),
    data_format    VARCHAR2(80)        NOT NULL,
    comp_type      NUMBER(8, 0)        NOT NULL,
    unit_signal    NUMBER(8, 0)        NOT NULL,
    unit_calib     NUMBER(8, 0)        NOT NULL,
    block_size     NUMBER(8, 0)        NOT NULL,
    offdate        DATE,
    remark         VARCHAR2(30),
    lddate         DATE                DEFAULT (SYSDATE),
    CONSTRAINT StDaL01 CHECK (block_size >= 256 AND block_size <= 4096),
    CONSTRAINT StDaL02 CHECK (clock_drift >= 0.0),
    CONSTRAINT StDaL03 CHECK (data_nb >= 1),
    CONSTRAINT StDaL04 CHECK (lchannel_nb >= 1),
    CONSTRAINT StDaL05 CHECK (pchannel_nb >= 1),
    CONSTRAINT StDaL06 CHECK (rfrequency > 0.0),
    CONSTRAINT StDaL00 PRIMARY KEY (sta, net, data_nb, pchannel_nb, lchannel_nb, ondate), 
    CONSTRAINT Sta_Dat_LCh_Fil_Seq FOREIGN KEY (seqfil_id)
    REFERENCES Filter_Sequence(seqfil_id),
    CONSTRAINT Sta_Dat_LCh_Sta_Dat_PCh FOREIGN KEY (sta,net,data_nb,pchannel_nb,ondate)
    REFERENCES Station_Datalogger_PChannel(sta,net,data_nb,pchannel_nb,ondate)
) 
TABLESPACE HT_DATA
;


COMMENT ON TABLE Station_Datalogger_LChannel IS 'Summary information on a channel'
;
COMMENT ON COLUMN Station_Datalogger_LChannel.sta IS 'Station code. This is the common code-name of a seismic observatory. Generally only three or four characters are used'
;
COMMENT ON COLUMN Station_Datalogger_LChannel.net IS 'Unique network identifier. This character string is the name of a seismic network'
;
COMMENT ON COLUMN Station_Datalogger_LChannel.data_nb IS 'This attribute represents the datalogger number'
;
COMMENT ON COLUMN Station_Datalogger_LChannel.pchannel_nb IS 'This attribute denotes a physical channel'
;
COMMENT ON COLUMN Station_Datalogger_LChannel.lchannel_nb IS 'This attribute denotes a logical channel of a datalogger'
;
COMMENT ON COLUMN Station_Datalogger_LChannel.ondate IS 'This attribute denotes the start date'
;
COMMENT ON COLUMN Station_Datalogger_LChannel.seqfil_id IS 'Sequence of filters identifier. Each sequence of filters is assigned a unique integer which identifies it in a database'
;
COMMENT ON COLUMN Station_Datalogger_LChannel.seedchan IS 'SEED channel name. The first character denotes the band code, the second for the instrument code and the third represents the component code'
;
COMMENT ON COLUMN Station_Datalogger_LChannel.channel IS 'Channel name'
;
COMMENT ON COLUMN Station_Datalogger_LChannel.channelsrc IS 'Domain for channel. This specifies what naming convention is used for the channel name (i.e. SEED, USGS, ...)'
;
COMMENT ON COLUMN Station_Datalogger_LChannel.location IS 'Describes the individual sites on an array station, operated by the same network operator'
;
COMMENT ON COLUMN Station_Datalogger_LChannel.rgain IS 'This attribute denotes the gain at the response frequency'
;
COMMENT ON COLUMN Station_Datalogger_LChannel.rfrequency IS 'The frequency (fs) at which the value in rgain is correct'
;
COMMENT ON COLUMN Station_Datalogger_LChannel.samprate IS 'Sampling rate. This attribute is the sample rate in samples/second'
;
COMMENT ON COLUMN Station_Datalogger_LChannel.clock_drift IS 'A tolerance value, measured in seconds per sample, used as a thresold for time error detection in the data. The number of samples in a record are multiplied by this value to calculate a maximum drift allowed for the time in the next record. If the difference in time is less than this drift, consider them in the same time series'
;
COMMENT ON COLUMN Station_Datalogger_LChannel.flags IS 'Channel flags'
;
COMMENT ON COLUMN Station_Datalogger_LChannel.data_format IS 'This attribute represents the data format'
;
COMMENT ON COLUMN Station_Datalogger_LChannel.comp_type IS 'Compression type. A lookup key that refers to the Format Dictionary'
;
COMMENT ON COLUMN Station_Datalogger_LChannel.unit_signal IS 'A unit lookup key that refers to the Units Abbreviation Dictionary for the signal response of the instrument – usually the ground motion response, or something else for non-seismic devices'
;
COMMENT ON COLUMN Station_Datalogger_LChannel.unit_calib IS 'A unit lookup key that refers to the Units Abbreviation Dictionary for the units of calibration input, usually volts or amps'
;
COMMENT ON COLUMN Station_Datalogger_LChannel.block_size IS 'The record length of the data'
;
COMMENT ON COLUMN Station_Datalogger_LChannel.offdate IS 'This attribute denotes the end date'
;
COMMENT ON COLUMN Station_Datalogger_LChannel.remark IS 'An optional comment given to the instrument. It can be anything, including a serial number or a notation of special modifications'
;
COMMENT ON COLUMN Station_Datalogger_LChannel.lddate IS 'Load date. Date and time that the record was created or last modified, in Oracle date datatype'
;
-- 
-- TABLE: Station_Digitizer 
--

CREATE TABLE Station_Digitizer(
    sta                VARCHAR2(6)     NOT NULL,
    net                VARCHAR2(8)     NOT NULL,
    digi_nb            NUMBER(8, 0)    NOT NULL,
    ondate             DATE            NOT NULL,
    serial_nb          VARCHAR2(80)    NOT NULL,
    nb_pri_pchannel    NUMBER(8, 0)    NOT NULL,
    nb_aux_pchannel    NUMBER(8, 0)    NOT NULL,
    offdate            DATE,
    lddate             DATE            DEFAULT (SYSDATE),
    CONSTRAINT StDi01 CHECK (digi_nb >= 1),
    CONSTRAINT StDi02 CHECK (nb_aux_pchannel >= 0),
    CONSTRAINT StDi03 CHECK (nb_pri_pchannel >= 0),
    CONSTRAINT StDi00 PRIMARY KEY (sta, net, digi_nb, ondate), 
    CONSTRAINT Sta_Dig_Sta FOREIGN KEY (sta,net,ondate)
    REFERENCES Station(sta,net,ondate)
) 
TABLESPACE HT_DATA
;


COMMENT ON TABLE Station_Digitizer IS 'Data associating a station with a digitizer'
;
COMMENT ON COLUMN Station_Digitizer.sta IS 'Station code. This is the common code-name of a seismic observatory. Generally only three or four characters are used'
;
COMMENT ON COLUMN Station_Digitizer.net IS 'Unique network identifier. This character string is the name of a seismic network'
;
COMMENT ON COLUMN Station_Digitizer.digi_nb IS 'This attribute represents the digitizer number'
;
COMMENT ON COLUMN Station_Digitizer.ondate IS 'This attribute denotes the start date'
;
COMMENT ON COLUMN Station_Digitizer.serial_nb IS 'This attribute denotes a serial number of a datalogger board'
;
COMMENT ON COLUMN Station_Digitizer.nb_pri_pchannel IS 'This attribute denotes the number of primary physical channels of a digitizer'
;
COMMENT ON COLUMN Station_Digitizer.nb_aux_pchannel IS 'This attribute denotes the number of auxiliary physical channels of a digitizer'
;
COMMENT ON COLUMN Station_Digitizer.offdate IS 'This attribute denotes the end date'
;
COMMENT ON COLUMN Station_Digitizer.lddate IS 'Load date. Date and time that the record was created or last modified, in Oracle date datatype'
;
-- 
-- TABLE: Station_Digitizer_PChannel 
--

CREATE TABLE Station_Digitizer_PChannel(
    sta              VARCHAR2(6)     NOT NULL,
    net              VARCHAR2(8)     NOT NULL,
    digi_nb          NUMBER(8, 0)    NOT NULL,
    pchannel_nb      NUMBER(8, 0)    NOT NULL,
    ondate           DATE            NOT NULL,
    data_nb          NUMBER(8, 0)    NOT NULL,
    data_pchannel    NUMBER(8, 0)    NOT NULL,
    digi_type        VARCHAR2(3)     NOT NULL,
    digi_polarity    VARCHAR2(1)     NOT NULL,
    digi_channel     NUMBER(8, 0)    NOT NULL,
    offdate          DATE,
    lddate           DATE            DEFAULT (SYSDATE),
    CONSTRAINT StDiP01 CHECK (data_nb >= 1),
    CONSTRAINT StDiP02 CHECK (data_pchannel >= 1),
    CONSTRAINT StDiP03 CHECK (digi_nb >= 1),
    CONSTRAINT StDiP05 CHECK (pchannel_nb >= 1),
    CONSTRAINT StDiP00 PRIMARY KEY (sta, net, digi_nb, pchannel_nb, ondate), 
    CONSTRAINT Sta_Dig_PCh_Sta_Dig FOREIGN KEY (sta,net,digi_nb,ondate)
    REFERENCES Station_Digitizer(sta,net,digi_nb,ondate)
) 
TABLESPACE HT_DATA
;


COMMENT ON TABLE Station_Digitizer_PChannel IS 'Data associating physical channels'
;
COMMENT ON COLUMN Station_Digitizer_PChannel.sta IS 'Station code. This is the common code-name of a seismic observatory. Generally only three or four characters are used'
;
COMMENT ON COLUMN Station_Digitizer_PChannel.net IS 'Unique network identifier. This character string is the name of a seismic network'
;
COMMENT ON COLUMN Station_Digitizer_PChannel.digi_nb IS 'This attribute represents the digitizer number'
;
COMMENT ON COLUMN Station_Digitizer_PChannel.pchannel_nb IS 'This attribute denotes a physical channel'
;
COMMENT ON COLUMN Station_Digitizer_PChannel.ondate IS 'This attribute denotes the start date'
;
COMMENT ON COLUMN Station_Digitizer_PChannel.data_nb IS 'This attribute represents the datalogger number'
;
COMMENT ON COLUMN Station_Digitizer_PChannel.data_pchannel IS 'This attribute represents a datalogger physical channel'
;
COMMENT ON COLUMN Station_Digitizer_PChannel.digi_type IS 'This attribute represents the digitizer type'
;
COMMENT ON COLUMN Station_Digitizer_PChannel.digi_polarity IS 'This attribute represents the digitizer polarity'
;
COMMENT ON COLUMN Station_Digitizer_PChannel.digi_channel IS 'This attribute represents a digitizer module on a board'
;
COMMENT ON COLUMN Station_Digitizer_PChannel.offdate IS 'This attribute denotes the end date'
;
COMMENT ON COLUMN Station_Digitizer_PChannel.lddate IS 'Load date. Date and time that the record was created or last modified, in Oracle date datatype'
;
-- 
-- TABLE: Station_Filamp 
--

CREATE TABLE Station_Filamp(
    sta            VARCHAR2(6)     NOT NULL,
    net            VARCHAR2(8)     NOT NULL,
    filamp_nb      NUMBER(8, 0)    NOT NULL,
    ondate         DATE            NOT NULL,
    filamp_id      NUMBER(8, 0)    NOT NULL,
    nb_pchannel    NUMBER(8, 0)    NOT NULL,
    offdate        DATE,
    lddate         DATE            DEFAULT (SYSDATE),
    CONSTRAINT StFi01 CHECK (filamp_nb >= 1),
    CONSTRAINT StFi02 CHECK (nb_pchannel >= 0),
    CONSTRAINT StFi00 PRIMARY KEY (sta, net, filamp_nb, ondate), 
    CONSTRAINT Sta_Fil_Fil FOREIGN KEY (filamp_id)
    REFERENCES Filamp(filamp_id),
    CONSTRAINT Sta_Fil_Sta FOREIGN KEY (sta,net,ondate)
    REFERENCES Station(sta,net,ondate)
) 
TABLESPACE HT_DATA
;


COMMENT ON TABLE Station_Filamp IS 'Data associating a station with a filter-amplifier'
;
COMMENT ON COLUMN Station_Filamp.sta IS 'Station code. This is the common code-name of a seismic observatory. Generally only three or four characters are used'
;
COMMENT ON COLUMN Station_Filamp.net IS 'Unique network identifier. This character string is the name of a seismic network'
;
COMMENT ON COLUMN Station_Filamp.filamp_nb IS 'This attribute represents the filter-amplifier number'
;
COMMENT ON COLUMN Station_Filamp.ondate IS 'This attribute denotes the start date'
;
COMMENT ON COLUMN Station_Filamp.filamp_id IS 'Filter-amplifier identifier. Each filter-amplifier is assigned a unique integer which identifies it in a database'
;
COMMENT ON COLUMN Station_Filamp.nb_pchannel IS 'This attribute denotes the number of physical channels of a datalogger'
;
COMMENT ON COLUMN Station_Filamp.offdate IS 'This attribute denotes the end date'
;
COMMENT ON COLUMN Station_Filamp.lddate IS 'Load date. Date and time that the record was created or last modified, in Oracle date datatype'
;
-- 
-- TABLE: Station_Filamp_PChannel 
--

CREATE TABLE Station_Filamp_PChannel(
    sta                   VARCHAR2(6)     NOT NULL,
    net                   VARCHAR2(8)     NOT NULL,
    filamp_nb             NUMBER(8, 0)    NOT NULL,
    pchannel_nb           NUMBER(8, 0)    NOT NULL,
    ondate                DATE            NOT NULL,
    next_hard_type        VARCHAR2(1)     NOT NULL,
    next_hard_nb          NUMBER(8, 0)    NOT NULL,
    next_hard_pchannel    NUMBER(8, 0)    NOT NULL,
    offdate               DATE,
    lddate                DATE            DEFAULT (SYSDATE),
    CONSTRAINT StFiP01 CHECK (filamp_nb >= 1),
    CONSTRAINT StFiP02 CHECK (next_hard_nb >= 1),
    CONSTRAINT StFiP03 CHECK (next_hard_pchannel >= 1),
    CONSTRAINT StFiP04 CHECK (next_hard_type IN ('F','D')),
    CONSTRAINT StFiP05 CHECK (pchannel_nb >= 1),
    CONSTRAINT StFiP00 PRIMARY KEY (sta, net, filamp_nb, pchannel_nb, ondate), 
    CONSTRAINT Sta_Fil_PCh_Sta_Fil FOREIGN KEY (sta,net,filamp_nb,ondate)
    REFERENCES Station_Filamp(sta,net,filamp_nb,ondate)
) 
TABLESPACE HT_DATA
;


COMMENT ON TABLE Station_Filamp_PChannel IS 'Data associating physical channels'
;
COMMENT ON COLUMN Station_Filamp_PChannel.sta IS 'Station code. This is the common code-name of a seismic observatory. Generally only three or four characters are used'
;
COMMENT ON COLUMN Station_Filamp_PChannel.net IS 'Unique network identifier. This character string is the name of a seismic network'
;
COMMENT ON COLUMN Station_Filamp_PChannel.filamp_nb IS 'This attribute represents the filter-amplifier number'
;
COMMENT ON COLUMN Station_Filamp_PChannel.pchannel_nb IS 'This attribute denotes a physical channel'
;
COMMENT ON COLUMN Station_Filamp_PChannel.ondate IS 'This attribute denotes the start date'
;
COMMENT ON COLUMN Station_Filamp_PChannel.next_hard_type IS 'This attribute denotes the next hardware type'
;
COMMENT ON COLUMN Station_Filamp_PChannel.next_hard_nb IS 'This attribute denotes the next hardware number'
;
COMMENT ON COLUMN Station_Filamp_PChannel.next_hard_pchannel IS 'This attribute denotes the next hardware physical channel'
;
COMMENT ON COLUMN Station_Filamp_PChannel.offdate IS 'This attribute denotes the end date'
;
COMMENT ON COLUMN Station_Filamp_PChannel.lddate IS 'Load date. Date and time that the record was created or last modified, in Oracle date datatype'
;
-- 
-- TABLE: Station_Sensor 
--

CREATE TABLE Station_Sensor(
    sta             VARCHAR2(6)         NOT NULL,
    net             VARCHAR2(8)         NOT NULL,
    sensor_nb       NUMBER(8, 0)        NOT NULL,
    ondate          DATE                NOT NULL,
    sensor_id       NUMBER(8, 0)        NOT NULL,
    lat             DOUBLE PRECISION,
    lon             DOUBLE PRECISION,
    elev            DOUBLE PRECISION,
    edepth          DOUBLE PRECISION,
    nb_component    NUMBER(8, 0)        NOT NULL,
    datumhor        VARCHAR2(8),
    datumver        VARCHAR2(8),
    offdate         DATE,
    lddate          DATE                DEFAULT (SYSDATE),
    CONSTRAINT StSe01 CHECK (datumhor IN ('NAD27','WGS84')),
    CONSTRAINT StSe02 CHECK (datumver IN ('NAD27','WGS84','AVERAGE')),
    CONSTRAINT StSe03 CHECK (edepth >= 0.0),
    CONSTRAINT StSe04 CHECK (elev >= -5000.0 AND elev <= 5000.0),
    CONSTRAINT StSe05 CHECK (lat >= -90.0 AND lat <= 90.0),
    CONSTRAINT StSe06 CHECK (lon >= -180.0 AND lon <= 180.0),
    CONSTRAINT StSe07 CHECK (nb_component >= 0),
    CONSTRAINT StSe08 CHECK (sensor_nb >= 1),
    CONSTRAINT StSe00 PRIMARY KEY (sta, net, sensor_nb, ondate), 
    CONSTRAINT Sta_Sen_Sen FOREIGN KEY (sensor_id)
    REFERENCES Sensor(sensor_id),
    CONSTRAINT Sta_Sen_Sta FOREIGN KEY (sta,net,ondate)
    REFERENCES Station(sta,net,ondate)
) 
TABLESPACE HT_DATA
;


COMMENT ON TABLE Station_Sensor IS 'Data associating a station with a sensor'
;
COMMENT ON COLUMN Station_Sensor.sta IS 'Station code. This is the common code-name of a seismic observatory. Generally only three or four characters are used'
;
COMMENT ON COLUMN Station_Sensor.net IS 'Unique network identifier. This character string is the name of a seismic network'
;
COMMENT ON COLUMN Station_Sensor.sensor_nb IS 'This attribute represents the sensor number'
;
COMMENT ON COLUMN Station_Sensor.ondate IS 'This attribute denotes the start date'
;
COMMENT ON COLUMN Station_Sensor.sensor_id IS 'Sensor identifier. Each sensor is assigned a unique integer which identifies it in a database'
;
COMMENT ON COLUMN Station_Sensor.lat IS 'Latitude. This attribute is the geographic latitude. Locations north of the equator have positive latitudes'
;
COMMENT ON COLUMN Station_Sensor.lon IS 'Longitude. This attribute is the geographic longitude. Longitudes are measured positive east of the Greenwich meridian'
;
COMMENT ON COLUMN Station_Sensor.elev IS 'Elevation. This attribute is the elevation of a seismic station relative to mean sea level'
;
COMMENT ON COLUMN Station_Sensor.edepth IS 'Emplacement depth. This attribute gives the depth at which the instrument is positioned, relative to the value of elev'
;
COMMENT ON COLUMN Station_Sensor.nb_component IS 'This attribute denotes the number of components of a sensor'
;
COMMENT ON COLUMN Station_Sensor.datumhor IS 'Datum type for horizontal (latitude and longitude) coordinates'
;
COMMENT ON COLUMN Station_Sensor.datumver IS 'Datum type for depth or elevation'
;
COMMENT ON COLUMN Station_Sensor.offdate IS 'This attribute denotes the end date'
;
COMMENT ON COLUMN Station_Sensor.lddate IS 'Load date. Date and time that the record was created or last modified, in Oracle date datatype'
;
-- 
-- TABLE: Station_Sensor_Component 
--

CREATE TABLE Station_Sensor_Component(
    sta                   VARCHAR2(6)         NOT NULL,
    net                   VARCHAR2(8)         NOT NULL,
    sensor_nb             NUMBER(8, 0)        NOT NULL,
    component_nb          NUMBER(8, 0)        NOT NULL,
    ondate                DATE                NOT NULL,
    next_hard_type        VARCHAR2(1)         NOT NULL,
    next_hard_nb          NUMBER(8, 0)        NOT NULL,
    next_hard_pchannel    NUMBER(8, 0)        NOT NULL,
    azimuth               DOUBLE PRECISION,
    dip                   DOUBLE PRECISION,
    offdate               DATE,
    lddate                DATE                DEFAULT (SYSDATE),
    CONSTRAINT StSeC01 CHECK (azimuth >= 0.0 AND azimuth <= 360.0),
    CONSTRAINT StSeC02 CHECK (component_nb >= 1),
    CONSTRAINT StSec03 CHECK (dip >= -90.0 AND dip <= 90.0),
    CONSTRAINT StSec04 CHECK (next_hard_nb >= 1),
    CONSTRAINT StSec05 CHECK (next_hard_pchannel >= 1),
    CONSTRAINT StSec06 CHECK (next_hard_type IN ('F','D')),
    CONSTRAINT StSec07 CHECK (sensor_nb >= 1),
    CONSTRAINT StSeC00 PRIMARY KEY (sta, net, sensor_nb, component_nb, ondate), 
    CONSTRAINT Sta_Sen_Com_Sta_Sen FOREIGN KEY (sta,net,sensor_nb,ondate)
    REFERENCES Station_Sensor(sta,net,sensor_nb,ondate)
) 
TABLESPACE HT_DATA
;


COMMENT ON TABLE Station_Sensor_Component IS 'Data associating physical channels'
;
COMMENT ON COLUMN Station_Sensor_Component.sta IS 'Station code. This is the common code-name of a seismic observatory. Generally only three or four characters are used'
;
COMMENT ON COLUMN Station_Sensor_Component.net IS 'Unique network identifier. This character string is the name of a seismic network'
;
COMMENT ON COLUMN Station_Sensor_Component.sensor_nb IS 'This attribute represents the sensor number'
;
COMMENT ON COLUMN Station_Sensor_Component.component_nb IS 'This attribute represents the component number'
;
COMMENT ON COLUMN Station_Sensor_Component.ondate IS 'This attribute denotes the start date'
;
COMMENT ON COLUMN Station_Sensor_Component.next_hard_type IS 'This attribute denotes the next hardware type'
;
COMMENT ON COLUMN Station_Sensor_Component.next_hard_nb IS 'This attribute denotes the next hardware number'
;
COMMENT ON COLUMN Station_Sensor_Component.next_hard_pchannel IS 'This attribute denotes the next hardware physical channel'
;
COMMENT ON COLUMN Station_Sensor_Component.azimuth IS 'The azimuth of the instrument from north, clockwise'
;
COMMENT ON COLUMN Station_Sensor_Component.dip IS 'The dip of the instrument, down from horizontal'
;
COMMENT ON COLUMN Station_Sensor_Component.offdate IS 'This attribute denotes the end date'
;
COMMENT ON COLUMN Station_Sensor_Component.lddate IS 'Load date. Date and time that the record was created or last modified, in Oracle date datatype'
;

