--
--ER/Studio 5.5 SQL Code Generation
-- Company :      Berkeley Seismological Laboratory
-- Project :      NCEDC/TriNet Waveform Schema
-- Author :       Stephane Zuzlewski
--
-- Date Created : Wednesday, November 20, 2002 13:29:24
-- Target DBMS : Oracle 9i
--


DROP TABLE Pathname CASCADE CONSTRAINTS
;
DROP TABLE AssocWaE CASCADE CONSTRAINTS
;
DROP TABLE Filename CASCADE CONSTRAINTS
;
DROP TABLE Subdir CASCADE CONSTRAINTS
;
DROP TABLE Waveform CASCADE CONSTRAINTS
;
-- 
-- SEQUENCE: fiseq 
--

DROP SEQUENCE fiseq
;

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

-- 
-- SEQUENCE: sdseq 
--

DROP SEQUENCE sdseq
;

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

-- 
-- SEQUENCE: waseq 
--

DROP SEQUENCE waseq
;

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

-- 
-- TABLE: Pathname 
--

CREATE TABLE Pathname(
    pathname    VARCHAR2(255)    NOT NULL,
    net         VARCHAR2(8)      NOT NULL,
    wave_fmt    NUMBER(2, 0)     NOT NULL,
    CONSTRAINT PKPathname2 PRIMARY KEY (pathname, net, wave_fmt)
) 
TABLESPACE WF_DATA
;


COMMENT ON TABLE Pathname IS 'Summary information on possible path options of waveforms for a given network'
;
COMMENT ON COLUMN Pathname.pathname IS 'Path name'
;
COMMENT ON COLUMN Pathname.net IS 'Unique network identifier. This character string is the name of a seismic network'
;
COMMENT ON COLUMN Pathname.wave_fmt IS 'Waveform type'
;
-- 
-- TABLE: Subdir 
--

CREATE TABLE Subdir(
    subdirid      NUMBER(15, 0)    NOT NULL,
    subdirname    VARCHAR2(64)     NOT NULL,
    lddate        DATE             DEFAULT (SYSDATE) NOT NULL,
    CONSTRAINT Sub00 PRIMARY KEY (subdirid)
) 
TABLESPACE WF_DATA
;


COMMENT ON TABLE Subdir IS 'Summary information on a directory name'
;
COMMENT ON COLUMN Subdir.subdirid IS 'Directory identifier. The key field is a unique identifier for a directory name on a disk'
;
COMMENT ON COLUMN Subdir.subdirname IS 'Path name. This is the name of a disk-based directory'
;
COMMENT ON COLUMN Subdir.lddate IS 'Load date. Date and time that the record was created or last modified
'
;
-- 
-- TABLE: Filename 
--

CREATE TABLE Filename(
    fileid          NUMBER(15, 0)     NOT NULL,
    dfile           VARCHAR2(32)      NOT NULL,
    datetime_on     NUMBER(25, 10)    NOT NULL,
    datetime_off    NUMBER(25, 10)    NOT NULL,
    nbytes          NUMBER(10, 0),
    subdirid        NUMBER(15, 0)     NOT NULL,
    lddate          DATE              DEFAULT (SYSDATE) NOT NULL,
    CONSTRAINT Fil00 PRIMARY KEY (fileid), 
    CONSTRAINT Filename_PartialPath FOREIGN KEY (subdirid)
    REFERENCES Subdir(subdirid)
) 
TABLESPACE WF_DATA
;


COMMENT ON TABLE Filename IS 'Summary information on a waveform filenames'
;
COMMENT ON COLUMN Filename.fileid IS 'Filename identifier. The key field is a unique identifier for a physical file on a disk'
;
COMMENT ON COLUMN Filename.dfile IS 'Data file. This is the file name of a disk-based waveform file'
;
COMMENT ON COLUMN Filename.datetime_on IS 'Time of first sample in the waveform file'
;
COMMENT ON COLUMN Filename.datetime_off IS 'Time of last sample in the waveform file'
;
COMMENT ON COLUMN Filename.nbytes IS 'Number of bytes. This quantity is the number of bytes in a waveform segment or a file'
;
COMMENT ON COLUMN Filename.subdirid IS 'Directory identifier. The key field is a unique identifier for a directory name on a disk'
;
COMMENT ON COLUMN Filename.lddate IS 'Load date. Date and time that the record was created or last modified'
;
-- 
-- TABLE: Waveform 
--

CREATE TABLE Waveform(
    wfid            NUMBER(15, 0)     NOT NULL,
    net             VARCHAR2(8)       NOT NULL,
    sta             VARCHAR2(6)       NOT NULL,
    auth            VARCHAR2(15)      NOT NULL,
    subsource       VARCHAR2(8),
    channel         VARCHAR2(3),
    channelsrc      VARCHAR2(8),
    seedchan        VARCHAR2(3)       NOT NULL,
    location        VARCHAR2(2),
    archive         VARCHAR2(8)       NOT NULL,
    datetime_on     NUMBER(25, 10)    NOT NULL,
    datetime_off    NUMBER(25, 10)    NOT NULL,
    samprate        NUMBER(12, 8)     NOT NULL,
    wavetype        VARCHAR2(1),
    fileid          NUMBER(15, 0)     NOT NULL,
    foff            NUMBER(12, 0),
    nbytes          NUMBER(10, 0),
    traceoff        NUMBER(10, 0),
    tracelen        NUMBER(10, 0),
    status          VARCHAR2(1)       NOT NULL,
    wave_fmt        NUMBER(2, 0),
    format_id       NUMBER(2, 0),
    wordorder       NUMBER(1, 0),
    recordsize      NUMBER(5, 0),
    locevid         VARCHAR2(12),
    lddate          DATE              DEFAULT (SYSDATE) NOT NULL,
    CONSTRAINT WF00 PRIMARY KEY (wfid), 
    CONSTRAINT Waveform_Filename FOREIGN KEY (fileid)
    REFERENCES Filename(fileid)
) 
TABLESPACE WF_DATA
PARTITION BY RANGE (datetime_on)(
PARTITION Wf1986
VALUES LESS THAN (536457613)
TABLESPACE PWF1986,
PARTITION Wf1987
VALUES LESS THAN (567993614)
TABLESPACE PWF1987,
PARTITION Wf1988
VALUES LESS THAN (599616014)
TABLESPACE PWF1988,
PARTITION Wf1989
VALUES LESS THAN (631152015)
TABLESPACE PWF1989,
PARTITION Wf1990
VALUES LESS THAN (662688016)
TABLESPACE PWF1990,
PARTITION Wf1991
VALUES LESS THAN (694224016)
TABLESPACE PWF1991,
PARTITION Wf1992
VALUES LESS THAN (725846417)
TABLESPACE PWF1992,
PARTITION Wf1993
VALUES LESS THAN (757382418)
TABLESPACE PWF1993,
PARTITION Wf1994
VALUES LESS THAN (788918419)
TABLESPACE PWF1994,
PARTITION Wf1995
VALUES LESS THAN (820454420)
TABLESPACE PWF1995,
PARTITION Wf1996
VALUES LESS THAN (852076820)
TABLESPACE PWF1996,
PARTITION Wf1997
VALUES LESS THAN (883612821)
TABLESPACE PWF1997,
PARTITION Wf1998
VALUES LESS THAN (915148821)
TABLESPACE PWF1998,
PARTITION Wf1999
VALUES LESS THAN (946684821)
TABLESPACE PWF1999
)
;


COMMENT ON TABLE Waveform IS 'Summary information on a waveform'
;
COMMENT ON COLUMN Waveform.wfid IS 'Waveform identifier. The key field is a unique identifier for a segment of digital waveform data'
;
COMMENT ON COLUMN Waveform.net IS 'Unique network identifier. This character string is the name of a seismic network'
;
COMMENT ON COLUMN Waveform.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 Waveform.auth IS 'The auth field specifies the source of the information. This may be a network identifier (FDSN or otherwise), or any other string indicating the source of the information in the record
'
;
COMMENT ON COLUMN Waveform.subsource IS 'A second identifier to specify a unique source within the domain specified by auth'
;
COMMENT ON COLUMN Waveform.channel IS 'Channel name'
;
COMMENT ON COLUMN Waveform.channelsrc IS 'Domain for channel. This specifies what naming convention is used for the channel name (i.e. SEED, USGS, etc.)'
;
COMMENT ON COLUMN Waveform.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 Waveform.location IS 'Describes the individual sites on an array station, operated by the same network operator'
;
COMMENT ON COLUMN Waveform.archive IS 'This field specifies the place where the seismogram is archived'
;
COMMENT ON COLUMN Waveform.datetime_on IS 'Time of first datum'
;
COMMENT ON COLUMN Waveform.datetime_off IS 'Time of last datum'
;
COMMENT ON COLUMN Waveform.samprate IS 'Sampling rate. This attribute is the sample rate in samples/second'
;
COMMENT ON COLUMN Waveform.wavetype IS 'This attribute denotes the type of waveform'
;
COMMENT ON COLUMN Waveform.fileid IS 'Filename identifier. The key field is a unique identifier for a physical file on a disk'
;
COMMENT ON COLUMN Waveform.foff IS 'File offset. This is the byte offset of a waveform segment within a data file'
;
COMMENT ON COLUMN Waveform.nbytes IS 'Number of bytes. This quantity is the number of bytes in a waveform segment or a file'
;
COMMENT ON COLUMN Waveform.traceoff IS 'File offset. This is the byte offset of the entire trace'
;
COMMENT ON COLUMN Waveform.tracelen IS 'Number of bytes. This quantity represents the length of the entire trace'
;
COMMENT ON COLUMN Waveform.status IS 'This attribute denotes the status of the waveform in the file'
;
COMMENT ON COLUMN Waveform.wave_fmt IS 'Waveform type'
;
COMMENT ON COLUMN Waveform.format_id IS 'This attribute denotes a code indicating the encoding format. This number is assigned by the FDSN Data Exchange Working Group'
;
COMMENT ON COLUMN Waveform.wordorder IS 'Word order'
;
COMMENT ON COLUMN Waveform.recordsize IS 'Record size for data. For MiniSEED, the value is the MiniSEED record size, eg. 512, 4096, etc'
;
COMMENT ON COLUMN Waveform.locevid IS 'Local event identifier. This attribute describes the original event identifier of the event. In combination with auth and subsource, it can be used to uniquely identify the original event identifier from that source. For example, waveforms from the original source may be associated with this identifier'
;
COMMENT ON COLUMN Waveform.lddate IS 'Load date. Date and time that the record was created or last modified
'
;
-- 
-- TABLE: AssocWaE 
--

CREATE TABLE AssocWaE(
    wfid            NUMBER(15, 0)     NOT NULL,
    evid            NUMBER(15, 0)     NOT NULL,
    datetime_on     NUMBER(25, 10)    NOT NULL,
    datetime_off    NUMBER(25, 10)    NOT NULL,
    lddate          DATE              DEFAULT (SYSDATE) NOT NULL,
    CONSTRAINT AWE00 PRIMARY KEY (wfid, evid), 
    CONSTRAINT Waveform_Event FOREIGN KEY (wfid)
    REFERENCES Waveform(wfid)
) 
TABLESPACE WF_DATA
;


COMMENT ON TABLE AssocWaE IS 'Data associating waveforms with events'
;
COMMENT ON COLUMN AssocWaE.wfid IS 'Waveform identifier. The key field is a unique identifier for a segment of digital waveform data'
;
COMMENT ON COLUMN AssocWaE.evid IS 'Event identifier.  Each event is assigned a unique integer which identifies it in a database. It is possible for several records in the Origin relation to have the same evid. This indicates there are several opinions about the location of the event. The evid does not have to be identical to the local network''s event identifier, which is stored in the Origin table. This allows to merge data for a single event from multiple sources without insisting that database evid is identical to the source''s local event identifier
'
;
COMMENT ON COLUMN AssocWaE.datetime_on IS 'Time of first datum'
;
COMMENT ON COLUMN AssocWaE.datetime_off IS 'Time of last datum'
;
COMMENT ON COLUMN AssocWaE.lddate IS 'Load date. Date and time that the record was created or last modified'
;
-- 
-- INDEX: wffileind 
--

DROP INDEX wffileind
;

CREATE INDEX wffileind ON Filename(dfile)
;
-- 
-- INDEX: wfdonind 
--

DROP INDEX wfdonind
;

CREATE INDEX wfdonind ON Waveform(datetime_on)
LOCAL
;
-- 
-- INDEX: wfdoffind 
--

DROP INDEX wfdoffind
;

CREATE INDEX wfdoffind ON Waveform(datetime_off)
LOCAL
;

