--
-- ER/Studio 7.1 SQL Code Generation
-- Company :      Berkeley Seismological Laboratory
-- Project :      NCEDC/TriNet Parametric Information
-- Author :       Stephane Zuzlewski
--
-- Date Created : Tuesday, February 20, 2007 09:13:33
-- Target DBMS : Oracle 9i
--

DROP TABLE amp CASCADE CONSTRAINTS
;
DROP TABLE arrival CASCADE CONSTRAINTS
;
DROP TABLE assocamm CASCADE CONSTRAINTS
;
DROP TABLE assocamo CASCADE CONSTRAINTS
;
DROP TABLE assocaro CASCADE CONSTRAINTS
;
DROP TABLE assoccom CASCADE CONSTRAINTS
;
DROP TABLE assoccoo CASCADE CONSTRAINTS
;
DROP TABLE coda CASCADE CONSTRAINTS
;
DROP TABLE event CASCADE CONSTRAINTS
;
DROP TABLE eventprefmag CASCADE CONSTRAINTS
;
DROP TABLE mec CASCADE CONSTRAINTS
;
DROP TABLE netmag CASCADE CONSTRAINTS
;
DROP TABLE origin CASCADE CONSTRAINTS
;
DROP TABLE origin_error CASCADE CONSTRAINTS
;
DROP TABLE remark CASCADE CONSTRAINTS
;
DROP TABLE significant_event CASCADE CONSTRAINTS
;
DROP TABLE stacorrections CASCADE CONSTRAINTS
;
DROP SEQUENCE AMPSEQ
;

CREATE SEQUENCE AMPSEQ
    START WITH 2
    INCREMENT BY 5
    NOMINVALUE
    NOMAXVALUE
    CACHE 20
    ORDER
;

DROP SEQUENCE ARSEQ
;

CREATE SEQUENCE ARSEQ
    START WITH 2
    INCREMENT BY 5
    NOMINVALUE
    NOMAXVALUE
    CACHE 20
    ORDER
;

DROP SEQUENCE COMMSEQ
;

CREATE SEQUENCE COMMSEQ
    START WITH 2
    INCREMENT BY 5
    NOMINVALUE
    NOMAXVALUE
    CACHE 20
    ORDER
;

DROP SEQUENCE COSEQ
;

CREATE SEQUENCE COSEQ
    START WITH 2
    INCREMENT BY 5
    NOMINVALUE
    NOMAXVALUE
    CACHE 20
    ORDER
;

DROP SEQUENCE EVSEQ
;

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

DROP SEQUENCE MAGSEQ
;

CREATE SEQUENCE MAGSEQ
    START WITH 2
    INCREMENT BY 5
    NOMINVALUE
    NOMAXVALUE
    CACHE 20
    ORDER
;

DROP SEQUENCE MECSEQ
;

CREATE SEQUENCE MECSEQ
    START WITH 2
    INCREMENT BY 5
    NOMINVALUE
    NOMAXVALUE
    CACHE 20
    ORDER
;

DROP SEQUENCE ORSEQ
;

CREATE SEQUENCE ORSEQ
    START WITH 2
    INCREMENT BY 5
    NOMINVALUE
    NOMAXVALUE
    CACHE 20
    ORDER
;

CREATE TABLE amp(
    commid        NUMBER(15, 0),
    ampid         NUMBER(15, 0)       NOT NULL,
    datetime      NUMBER(25, 10)      NOT NULL,
    sta           VARCHAR2(6)         NOT NULL,
    net           VARCHAR2(8),
    auth          VARCHAR2(15)        NOT NULL,
    subsource     VARCHAR2(8),
    channel       VARCHAR2(8),
    channelsrc    VARCHAR2(8),
    seedchan      VARCHAR2(3),
    location      VARCHAR2(2),
    iphase        VARCHAR2(8),
    amplitude     DOUBLE PRECISION    NOT NULL,
    amptype       VARCHAR2(8),
    units         VARCHAR2(4)         NOT NULL,
    ampmeas       VARCHAR2(1),
    eramp         NUMBER(5, 3),
    flagamp       VARCHAR2(4),
    per           NUMBER(10, 4),
    snr           DOUBLE PRECISION,
    tau           NUMBER(9, 4),
    quality       NUMBER(2, 1),
    rflag         VARCHAR2(2),
    cflag         VARCHAR2(2),
    wstart        DOUBLE PRECISION,
    duration      DOUBLE PRECISION,
    lddate        DATE,
    CHECK (ampid > 0),
    CHECK (amplitude > 0),
    CHECK (ampmeas in ('0','1')),
    CHECK (amptype in ('C','WA','WAS','PGA','PGV','PGD','WAC','WAU','IV2','SP.3','SP1.0','SP3.0','ML100','ME100','EGY')),
    CHECK (eramp >= 0.0),
    CHECK (flagamp in ('P','S','R','PP','ALL','SUR')),
    CHECK (per > 0.0),
    CHECK (tau > 0.0),
    CHECK (units in ('c','s','mm','cm','m','ms','mss','cms','cmss','mms','mmss','mc','nm','e','iovs','spa','none')),
    CHECK (quality >= 0.0 and quality <= 1.0),
    CHECK (rflag in ('a','h','f','A','H','F')),
    CHECK (cflag in ('bn', 'os','cl','BN','OS','CL')),
    CONSTRAINT ampkey01 PRIMARY KEY (ampid)
)
;



CREATE TABLE arrival(
    arid          NUMBER(15, 0)       NOT NULL,
    commid        NUMBER(15, 0),
    datetime      NUMBER(25, 10)      NOT NULL,
    sta           VARCHAR2(6)         NOT NULL,
    net           VARCHAR2(8),
    auth          VARCHAR2(15)        NOT NULL,
    subsource     VARCHAR2(8),
    channel       VARCHAR2(8),
    channelsrc    VARCHAR2(8),
    seedchan      VARCHAR2(3),
    location      VARCHAR2(2),
    iphase        VARCHAR2(8),
    qual          VARCHAR2(1),
    clockqual     VARCHAR2(1),
    clockcorr     NUMBER(15, 0),
    ccset         VARCHAR2(1),
    fm            VARCHAR2(2),
    ema           NUMBER(5, 2),
    azimuth       NUMBER(4, 1),
    slow          NUMBER(8, 4),
    deltim        NUMBER(5, 2),
    delinc        NUMBER(4, 2),
    delaz         NUMBER(5, 2),
    delslo        NUMBER(8, 4),
    quality       NUMBER(3, 2),
    snr           DOUBLE PRECISION,
    rflag         VARCHAR2(2),
    lddate        DATE,
    CHECK (arid > 0),
    CHECK (azimuth >= 0.0 and azimuth <= 360.0),
    CHECK (delaz > 0.0),
    CHECK (delinc >= 0.0),
    CHECK (delslo > 0.0),
    CHECK (deltim >= 0.0),
    CHECK (ema >= 0.0 and ema <= 90.0),
    CHECK (fm in ('..','.i','.e','.x','ci','ce','cx','c.','di','de','dx','d.')),
    CHECK (qual in ('i','e','w','I','E','W')),
    CHECK (slow >= 0.0),
    CHECK (snr > 0.0),
    CHECK (quality >= 0.0 and quality <= 1.0),
    CHECK (ccset < 1),
    CHECK (rflag in ('a','h','f','A','H','F')),
    CONSTRAINT arkey01 PRIMARY KEY (arid)
)
;



CREATE TABLE assocamm(
    magid         NUMBER(15, 0)    NOT NULL,
    ampid         NUMBER(15, 0)    NOT NULL,
    commid        NUMBER(15, 0),
    auth          VARCHAR2(15)     NOT NULL,
    subsource     VARCHAR2(8),
    weight        NUMBER(4, 3),
    in_wgt        NUMBER(4, 3),
    mag           NUMBER(5, 2),
    magres        NUMBER(5, 2),
    magcorr       NUMBER(5, 2),
    importance    NUMBER(2, 1),
    rflag         VARCHAR2(2),
    lddate        DATE,
    CHECK (mag >= -10.0 and mag <= 10.0),
    CHECK (magcorr >= -10.0 and magcorr <= 10.0),
    CHECK (magid > 0),
    CHECK (weight >= 0.0 and weight <= 1.0),
    CHECK (in_wgt >= 0.0 and in_wgt <= 1.0),
    CHECK (importance > 0.0 and importance <= 1.0),
    CHECK (rflag in ('a','h','f','A','H','F')),
    CONSTRAINT assocammkey01 PRIMARY KEY (magid, ampid)
)
;



CREATE TABLE assocamo(
    orid         NUMBER(15, 0)    NOT NULL,
    ampid        NUMBER(15, 0)    NOT NULL,
    commid       NUMBER(15, 0),
    auth         VARCHAR2(15)     NOT NULL,
    subsource    VARCHAR2(8),
    delta        NUMBER(5, 1),
    seaz         NUMBER(4, 1),
    rflag        VARCHAR2(2),
    lddate       DATE,
    CHECK (seaz >= 0.0 and seaz <= 360.0),
    CHECK (delta >= 0.0),
    CHECK (rflag in ('a','h','f','A','H','F')),
    CONSTRAINT assocamokey01 PRIMARY KEY (orid, ampid)
)
;



CREATE TABLE assocaro(
    orid          NUMBER(15, 0)    NOT NULL,
    arid          NUMBER(15, 0)    NOT NULL,
    commid        NUMBER(15, 0),
    auth          VARCHAR2(15)     NOT NULL,
    subsource     VARCHAR2(8),
    iphase        VARCHAR2(8),
    importance    NUMBER(2, 1),
    delta         NUMBER(5, 1),
    seaz          NUMBER(4, 1),
    in_wgt        NUMBER(4, 3),
    wgt           NUMBER(4, 3),
    timeres       NUMBER(5, 2),
    ema           NUMBER(5, 2),
    slow          NUMBER(8, 4),
    vmodelid      NUMBER(3, 0),
    scorr         NUMBER(6, 4),
    sdelay        NUMBER(7, 4),
    rflag         VARCHAR2(2),
    ccset         VARCHAR2(1),
    lddate        DATE,
    CHECK (delta >= 0.0),
    CHECK (ema >= 0 and ema <= 180),
    CHECK (importance >= 0.0 and importance < 1.0),
    CHECK (seaz >= 0.0 and seaz <= 360.0),
    CHECK (slow >= 0.0),
    CHECK (timeres >= 0.0),
    CHECK (ccset < 1),
    CHECK (rflag in ('a','h','f','A','H','F')),
    CONSTRAINT assocarokey01 PRIMARY KEY (orid, arid)
)
;



CREATE TABLE assoccom(
    magid        NUMBER(15, 0)    NOT NULL,
    coid         NUMBER(15, 0)    NOT NULL,
    commid       NUMBER(15, 0),
    auth         VARCHAR2(15)     NOT NULL,
    subsource    VARCHAR2(8),
    weight       NUMBER(4, 3),
    in_wgt       NUMBER(4, 3),
    mag          NUMBER(7, 4),
    magres       NUMBER(7, 4),
    magcorr      NUMBER(7, 4),
    rflag        VARCHAR2(2),
    lddate       DATE,
    CHECK (weight >= 0.0 and weight <= 1.0),
    CHECK (in_wgt >= 0.0 and in_wgt <= 1.0),
    CHECK (rflag in ('a','h','f','A','H','F')),
    CONSTRAINT assoccomkey01 PRIMARY KEY (magid, coid)
)
;



CREATE TABLE assoccoo(
    orid         NUMBER(15, 0)    NOT NULL,
    coid         NUMBER(15, 0)    NOT NULL,
    commid       NUMBER(15, 0),
    auth         VARCHAR2(15)     NOT NULL,
    subsource    VARCHAR2(8),
    delta        NUMBER(7, 4),
    seaz         NUMBER(7, 4),
    rflag        VARCHAR2(2),
    lddate       DATE,
    CHECK (rflag in ('a','h','f','A','H','F')),
    CONSTRAINT assoccookey01 PRIMARY KEY (orid, coid)
)
;



CREATE TABLE coda(
    coid          NUMBER(15, 0)     NOT NULL,
    commid        NUMBER(15, 0),
    sta           VARCHAR2(6)       NOT NULL,
    net           VARCHAR2(8),
    auth          VARCHAR2(15)      NOT NULL,
    subsource     VARCHAR2(8),
    channel       VARCHAR2(8),
    channelsrc    VARCHAR2(8),
    seedchan      VARCHAR2(3),
    location      VARCHAR2(2),
    codatype      VARCHAR2(3),
    afix          NUMBER(7, 4),
    afree         NUMBER(7, 4),
    qfix          NUMBER(7, 4),
    qfree         NUMBER(7, 4),
    tau           NUMBER(7, 4),
    nsample       NUMBER(6, 0),
    rms           NUMBER(5, 3),
    durtype       VARCHAR2(3),
    iphase        VARCHAR2(8),
    eramp         NUMBER(5, 3),
    units         VARCHAR2(4)       NOT NULL,
    time1         NUMBER(10, 0),
    amp1          NUMBER(10, 0),
    time2         NUMBER(10, 0),
    amp2          NUMBER(10, 0),
    time3         NUMBER(10, 0),
    amp3          NUMBER(10, 0),
    time4         NUMBER(10, 0),
    amp4          NUMBER(10, 0),
    time5         NUMBER(10, 0),
    amp5          NUMBER(10, 0),
    time6         NUMBER(10, 0),
    amp6          NUMBER(10, 0),
    quality       NUMBER(3, 2),
    datetime      NUMBER(25, 10),
    algorithm     VARCHAR2(15),
    winsize       NUMBER(7, 4),
    rflag         VARCHAR2(2),
    lddate        DATE,
    CHECK (afix >= 0.0),
    CHECK (afree >= 0.0),
    CHECK (amp1 > 0),
    CHECK (amp2 > 0),
    CHECK (amp3 > 0),
    CHECK (amp4 > 0),
    CHECK (amp5 > 0),
    CHECK (amp6 > 0),
    CHECK (codatype in ('P','S')),
    CHECK (coid > 0),
    CHECK (nsample >= 0),
    CHECK (rms >= 0.0),
    CHECK (time1 > 0),
    CHECK (time2 > 0),
    CHECK (time3 > 0),
    CHECK (time4 > 0),
    CHECK (time5 > 0),
    CHECK (time6 > 0),
    CHECK (rflag in ('a','h','f','A','H','F')),
    CHECK (quality >= 0.0 and quality <= 1.0),
    CONSTRAINT codakey01 PRIMARY KEY (coid)
)
;



CREATE TABLE event(
    evid          NUMBER(15, 0)    NOT NULL,
    prefor        NUMBER(15, 0),
    prefmag       NUMBER(15, 0),
    prefmec       NUMBER(15, 0),
    commid        NUMBER(15, 0),
    auth          VARCHAR2(15)     NOT NULL,
    subsource     VARCHAR2(8),
    etype         VARCHAR2(7),
    selectflag    NUMBER(1, 0),
    lddate        DATE,
    version       NUMBER(3, 0),
    CHECK (etype in ('le','re','ts','qb','nt', 'ca','sn','uk','st')),
    CHECK (evid > 0),
    CONSTRAINT evkey01 PRIMARY KEY (evid)
)
;



CREATE TABLE eventprefmag(
    type      VARCHAR2(6)      NOT NULL,
    evid      NUMBER(15, 0)    NOT NULL,
    magid     NUMBER(15, 0)    NOT NULL,
    lddate    DATE,
    CHECK (type in ('a','b','e','l','l1','l2','lg','c','s','w','z','B', 'un','d','h','n')),
    CONSTRAINT EventPrefMag_PK PRIMARY KEY (type, evid)
)
;



CREATE TABLE mec(
    mecid          NUMBER(15, 0)       NOT NULL,
    oridin         NUMBER(15, 0),
    oridout        NUMBER(15, 0),
    magid          NUMBER(15, 0),
    commid         NUMBER(15, 0),
    mechtype       VARCHAR2(2),
    mecalgo        VARCHAR2(15),
    scalar         DOUBLE PRECISION,
    erscalar       DOUBLE PRECISION,
    tft            VARCHAR2(8),
    tfd            DOUBLE PRECISION,
    mxx            DOUBLE PRECISION,
    myy            DOUBLE PRECISION,
    mzz            DOUBLE PRECISION,
    mxy            DOUBLE PRECISION,
    mxz            DOUBLE PRECISION,
    myz            DOUBLE PRECISION,
    smxx           DOUBLE PRECISION,
    smyy           DOUBLE PRECISION,
    smzz           DOUBLE PRECISION,
    smxy           DOUBLE PRECISION,
    smxz           DOUBLE PRECISION,
    smyz           DOUBLE PRECISION,
    srcduration    NUMBER(6, 3),
    auth           VARCHAR2(15)        NOT NULL,
    subsource      VARCHAR2(8),
    strike1        NUMBER(3, 0),
    dip1           NUMBER(3, 0),
    rake1          NUMBER(4, 0),
    strike2        NUMBER(3, 0),
    dip2           NUMBER(2, 0),
    rake2          NUMBER(4, 0),
    unstrike1      NUMBER(6, 3),
    undip1         NUMBER(5, 3),
    unrake1        NUMBER(6, 3),
    unstrike2      NUMBER(6, 3),
    undip2         NUMBER(5, 3),
    unrake2        NUMBER(6, 3),
    eigenp         DOUBLE PRECISION,
    plungep        NUMBER(2, 0),
    strikep        NUMBER(3, 0),
    eigenn         DOUBLE PRECISION,
    plungen        NUMBER(2, 0),
    striken        NUMBER(3, 0),
    eigent         DOUBLE PRECISION,
    plunget        NUMBER(2, 0),
    striket        NUMBER(3, 0),
    nsta           NUMBER(5, 0),
    pvr            NUMBER(5, 0),
    quality        NUMBER(2, 1),
    pdc            NUMBER(3, 0),
    pclvd          NUMBER(3, 0),
    piso           NUMBER(3, 0),
    datetime       NUMBER(25, 10)      NOT NULL,
    rflag          VARCHAR2(2),
    lddate         DATE,
    CHECK (dip1 >= -90 and dip1 <= 90),
    CHECK (dip2 >= -90 and dip2 <= 90),
    CHECK (erscalar >= 0.0),
    CHECK (mecid > 0),
    CHECK (mechtype in ('C','C0','F','F0','H','N','S','S0')),
    CHECK (plungen >= 0 and plungen <= 90),
    CHECK (plungep >= 0 and plungep <= 90),
    CHECK (plunget >= 0 and plunget <= 90),
    CHECK (pclvd >= 0 and pclvd <= 100),
    CHECK (pdc >= 0 and pdc <= 100),
    CHECK (piso >= 0 and piso <= 100),
    CHECK (pvr >= 0 and pvr <= 100),
    CHECK (rake1 >= -180 and rake1 <= 180),
    CHECK (rake2 >= -180 and rake2 <= 180),
    CHECK (srcduration >= 0.0 and srcduration <= 100.0),
    CHECK (striken >= 0 and striken <= 360),
    CHECK (strikep >= 0 and strikep <= 360),
    CHECK (striket >= 0 and striket <= 360),
    CHECK (strike1 >= 0 and strike1 <= 360),
    CHECK (strike2 >= 0 and strike2 <= 360),
    CHECK (tfd > 0),
    CHECK (undip1 >= -180 and undip1 <= 180),
    CHECK (undip2 >= -180 and undip2 <= 180),
    CHECK (unrake1 >= -180.0 and unrake1 <= 180.0),
    CHECK (unrake2 >= -180.0 and unrake2 <= 180.0),
    CHECK (unstrike1 >= -180.0 and unstrike1 <= 180.0),
    CHECK (unstrike2 >= -180.0 and unstrike2 <= 180.0),
    CHECK (quality >= 0.0 and quality <= 1.0),
    CONSTRAINT meckey01 PRIMARY KEY (mecid)
)
;



CREATE TABLE netmag(
    magid          NUMBER(15, 0)    NOT NULL,
    orid           NUMBER(15, 0)    NOT NULL,
    commid         NUMBER(15, 0),
    magnitude      NUMBER(5, 2)     NOT NULL,
    magtype        VARCHAR2(6)      NOT NULL,
    auth           VARCHAR2(15)     NOT NULL,
    subsource      VARCHAR2(8),
    magalgo        VARCHAR2(15),
    nsta           NUMBER(5, 0),
    uncertainty    NUMBER(5, 3),
    gap            NUMBER(4, 1),
    distance       NUMBER(7, 3),
    quality        NUMBER(2, 1),
    rflag          VARCHAR2(2),
    lddate         DATE,
    CHECK (quality >= 0.0 and quality <= 1.0),
    CHECK (magid > 0),
    CHECK (rflag in ('a','h','f','A','H','F')),
    CHECK (magnitude >= -10.0 and magnitude <= 10.0),
    CHECK (magtype in ('p', 'a','b','e','l','l1','l2','lg','c','s','w','z','B','un','d','h','n')),
    CHECK (nsta >= 0),
    CHECK (uncertainty >= 0.0),
    CONSTRAINT magkey01 PRIMARY KEY (magid)
)
;



CREATE TABLE origin(
    orid          NUMBER(15, 0)     NOT NULL,
    evid          NUMBER(15, 0)     NOT NULL,
    prefmag       NUMBER(15, 0),
    prefmec       NUMBER(15, 0),
    commid        NUMBER(15, 0),
    bogusflag     NUMBER(1, 0),
    datetime      NUMBER(25, 10)    NOT NULL,
    lat           NUMBER(9, 7)      NOT NULL,
    lon           NUMBER(10, 7)     NOT NULL,
    depth         NUMBER(7, 3),
    type          VARCHAR2(2),
    algorithm     VARCHAR2(15),
    algo_assoc    VARCHAR2(80),
    auth          VARCHAR2(15)      NOT NULL,
    subsource     VARCHAR2(8),
    datumhor      VARCHAR2(8),
    datumver      VARCHAR2(8),
    gap           NUMBER(4, 1),
    distance      NUMBER(7, 3),
    wrms          NUMBER(5, 3),
    stime         NUMBER(6, 3),
    erhor         NUMBER(7, 3),
    sdep          NUMBER(7, 3),
    erlat         NUMBER(7, 3),
    erlon         NUMBER(7, 3),
    totalarr      NUMBER(5, 0),
    totalamp      NUMBER(6, 0),
    ndef          NUMBER(5, 0),
    nbs           NUMBER(4, 0),
    nbfm          NUMBER(4, 0),
    locevid       VARCHAR2(12),
    quality       NUMBER(2, 1),
    fdepth        VARCHAR2(1),
    fepi          VARCHAR2(1),
    ftime         VARCHAR2(1),
    vmodelid      VARCHAR2(2),
    cmodelid      VARCHAR2(2),
    rflag         VARCHAR2(2),
    lddate        DATE,
    CHECK (datumhor in ('NAD27','WGS84')),
    CHECK (datumver in ('NAD27','WGS84','AVERAGE')),
    CHECK (depth >= -10.0 and depth <= 1000.0),
    CHECK (distance >= 0.0),
    CHECK (erhor >= 0.0),
    CHECK (erlat >= 0.0),
    CHECK (erlon >= 0.0),
    CHECK (fdepth in ('y','n')),
    CHECK (fepi in ('y','n')),
    CHECK (ftime in ('y','n')),
    CHECK (gap >= 0.0 and gap <= 360.0),
    CHECK (nbfm >= 0),
    CHECK (nbs >= 0),
    CHECK (ndef >= 0),
    CHECK (orid > 0),
    CHECK (quality >= 0.0 and quality <= 1.0),
    CHECK (type in ('H','HO','HR','HI','GO','C','A')),
    CHECK (stime >= 0.0),
    CHECK (wrms >= 0.0),
    CHECK (sdep >=0.0),
    CHECK (totalarr >=0),
    CHECK (totalamp >= 0),
    CHECK (rflag in ('a','h','f','A','H','F')),
    CONSTRAINT orkey01 PRIMARY KEY (orid)
)
;



CREATE TABLE origin_error(
    orid        NUMBER(15, 0)    NOT NULL,
    sxx         NUMBER(7, 3),
    syy         NUMBER(7, 3),
    szz         NUMBER(7, 3),
    stt         NUMBER(7, 3),
    sxy         NUMBER(7, 3),
    sxz         NUMBER(7, 3),
    syz         NUMBER(7, 3),
    stx         NUMBER(7, 3),
    sty         NUMBER(7, 3),
    stz         NUMBER(7, 3),
    azismall    NUMBER(5, 2),
    dipsmall    NUMBER(4, 2),
    magsmall    NUMBER(7, 5),
    aziinter    NUMBER(5, 2),
    dipinter    NUMBER(4, 2),
    maginter    NUMBER(7, 5),
    azilarge    NUMBER(5, 2),
    diplarge    NUMBER(4, 2),
    maglarge    NUMBER(7, 5),
    lddate      DATE,
    CHECK (aziinter >= 0.0 and aziinter <= 360.0),
    CHECK (azilarge >= 0.0 and azilarge <= 360.0),
    CHECK (azismall >= 0.0 and azismall <= 360.0),
    CHECK (dipinter >= -90.0 and dipinter <= 90.0),
    CHECK (diplarge >= -90.0 and diplarge <= 90.0),
    CHECK (dipsmall >= -90.0 and dipsmall <= 90.0),
    CHECK (maginter >= 0.0),
    CHECK (maglarge >= 0.0),
    CHECK (magsmall >= 0.0),
    CHECK (stx > 0.0),
    CHECK (sty > 0.0),
    CHECK (stz > 0.0),
    CHECK (sxx > 0.0),
    CHECK (sxy > 0.0),
    CHECK (sxz > 0.0),
    CHECK (syy > 0.0),
    CHECK (syz > 0.0),
    CHECK (stt > 0.0),
    CHECK (szz > 0.0),
    CONSTRAINT orerrkey01 PRIMARY KEY (orid)
)
;



CREATE TABLE remark(
    commid    NUMBER(15, 0)    NOT NULL,
    lineno    NUMBER(15, 0)    NOT NULL,
    remark    VARCHAR2(80),
    lddate    DATE,
    CHECK (commid > 0),
    CHECK (lineno > 0),
    CONSTRAINT remkey01 PRIMARY KEY (commid, lineno)
)
;



CREATE TABLE significant_event(
    evid       NUMBER(15, 0)    NOT NULL,
    evname     VARCHAR2(80),
    remarks    VARCHAR2(2),
    nfelt      NUMBER(8, 0),
    mmi        NUMBER(2, 0),
    pga        NUMBER(6, 4),
    lddate     DATE,
    CHECK (mmi >= 1 and mmi <= 12),
    CHECK (nfelt > 0),
    CHECK (pga > 0.0),
    CHECK (remarks in ('f','d','fd','df')),
    CONSTRAINT sigevkey01 PRIMARY KEY (evid)
)
;



CREATE TABLE stacorrections(
    net           VARCHAR2(8)         NOT NULL,
    sta           VARCHAR2(6)         NOT NULL,
    seedchan      VARCHAR2(3)         NOT NULL,
    channel       VARCHAR2(8),
    channelsrc    VARCHAR2(8),
    location      VARCHAR2(2)         NOT NULL,
    auth          VARCHAR2(15),
    corr          DOUBLE PRECISION,
    corr_flag     VARCHAR2(1),
    corr_type     VARCHAR2(3),
    ondate        DATE                NOT NULL,
    offdate       DATE,
    lddate        DATE,
    CHECK (corr_type in ('ml','me','mca','mh','md','mw','mb','m0','vel','acc')),
    CHECK (corr_flag in ('D','C','F')),
    CONSTRAINT stacorrectionskey01 PRIMARY KEY (net, sta, seedchan, location, ondate)
)
;



ALTER TABLE assocamm ADD CONSTRAINT Aam_Amp 
    FOREIGN KEY (ampid)
    REFERENCES amp(ampid)
;

ALTER TABLE assocamm ADD CONSTRAINT Aam_Net 
    FOREIGN KEY (magid)
    REFERENCES netmag(magid)
;


ALTER TABLE assocamo ADD CONSTRAINT Amo_Amp 
    FOREIGN KEY (ampid)
    REFERENCES amp(ampid)
;

ALTER TABLE assocamo ADD CONSTRAINT Amo_Ori 
    FOREIGN KEY (orid)
    REFERENCES origin(orid)
;


ALTER TABLE assocaro ADD CONSTRAINT Aao_Arr 
    FOREIGN KEY (arid)
    REFERENCES arrival(arid)
;

ALTER TABLE assocaro ADD CONSTRAINT Aao_Ori 
    FOREIGN KEY (orid)
    REFERENCES origin(orid)
;


ALTER TABLE assoccom ADD CONSTRAINT Aom_Cod 
    FOREIGN KEY (coid)
    REFERENCES coda(coid)
;

ALTER TABLE assoccom ADD CONSTRAINT Aom_Net 
    FOREIGN KEY (magid)
    REFERENCES netmag(magid)
;


ALTER TABLE assoccoo ADD CONSTRAINT Aco_Cod 
    FOREIGN KEY (coid)
    REFERENCES coda(coid)
;

ALTER TABLE assoccoo ADD CONSTRAINT Aco_Ori 
    FOREIGN KEY (orid)
    REFERENCES origin(orid)
;


ALTER TABLE event ADD CONSTRAINT Eve_Mec 
    FOREIGN KEY (prefmec)
    REFERENCES mec(mecid)
;

ALTER TABLE event ADD CONSTRAINT Eve_Net 
    FOREIGN KEY (prefmag)
    REFERENCES netmag(magid)
;

ALTER TABLE event ADD CONSTRAINT Eve_Ori 
    FOREIGN KEY (prefor)
    REFERENCES origin(orid)
;


ALTER TABLE eventprefmag ADD CONSTRAINT Epm_Eve 
    FOREIGN KEY (evid)
    REFERENCES event(evid)
;

ALTER TABLE eventprefmag ADD CONSTRAINT Epm_Net 
    FOREIGN KEY (magid)
    REFERENCES netmag(magid)
;


ALTER TABLE mec ADD CONSTRAINT Mec_Net 
    FOREIGN KEY (magid)
    REFERENCES netmag(magid)
;

ALTER TABLE mec ADD CONSTRAINT Mec_Ori 
    FOREIGN KEY (oridin)
    REFERENCES origin(orid)
;

ALTER TABLE mec ADD CONSTRAINT Mec_Ori2 
    FOREIGN KEY (oridout)
    REFERENCES origin(orid)
;


ALTER TABLE netmag ADD CONSTRAINT Net_Ori 
    FOREIGN KEY (orid)
    REFERENCES origin(orid)
;


ALTER TABLE origin ADD CONSTRAINT Ori_Eve 
    FOREIGN KEY (evid)
    REFERENCES event(evid)
;

ALTER TABLE origin ADD CONSTRAINT Ori_Mec 
    FOREIGN KEY (prefmec)
    REFERENCES mec(mecid)
;

ALTER TABLE origin ADD CONSTRAINT Ori_Net 
    FOREIGN KEY (prefmag)
    REFERENCES netmag(magid)
;


ALTER TABLE origin_error ADD CONSTRAINT Ori__Err_Ori 
    FOREIGN KEY (orid)
    REFERENCES origin(orid)
;


ALTER TABLE significant_event ADD CONSTRAINT Sig_Eve_Eve 
    FOREIGN KEY (evid)
    REFERENCES event(evid)
;


