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

DROP TABLE appchannels CASCADE CONSTRAINTS
;
DROP TABLE applications CASCADE CONSTRAINTS
;
DROP TABLE channelmap_ampparms CASCADE CONSTRAINTS
;
DROP TABLE channelmap_codaparms CASCADE CONSTRAINTS
;
DROP TABLE config_channel CASCADE CONSTRAINTS
;
DROP TABLE credit CASCADE CONSTRAINTS
;
DROP TABLE credit_alias CASCADE CONSTRAINTS
;
DROP TABLE gazetteerbigtown CASCADE CONSTRAINTS
;
DROP TABLE gazetteerfault CASCADE CONSTRAINTS
;
DROP TABLE gazetteerline CASCADE CONSTRAINTS
;
DROP TABLE gazetteerpt CASCADE CONSTRAINTS
;
DROP TABLE gazetteerquake CASCADE CONSTRAINTS
;
DROP TABLE gazetteerquarry CASCADE CONSTRAINTS
;
DROP TABLE gazetteertown CASCADE CONSTRAINTS
;
DROP TABLE gazetteertype CASCADE CONSTRAINTS
;
DROP TABLE jasieventlock CASCADE CONSTRAINTS
;
DROP TABLE program CASCADE CONSTRAINTS
;
DROP TABLE request_card CASCADE CONSTRAINTS
;
DROP TABLE rt_role CASCADE CONSTRAINTS
;
DROP TABLE stamapping CASCADE CONSTRAINTS
;
DROP TABLE unassocamp CASCADE CONSTRAINTS
;
-- 
-- TABLE: appchannels 
--

CREATE TABLE appchannels(
    progid      NUMBER(8, 0)    NOT NULL,
    net         VARCHAR2(8)     NOT NULL,
    sta         VARCHAR2(6)     NOT NULL,
    seedchan    VARCHAR2(3)     NOT NULL,
    location    VARCHAR2(2)     NOT NULL,
    config      VARCHAR2(64),
    ondate      DATE,
    offdate     DATE,
    lddate      DATE            DEFAULT SYSDATE,
    CONSTRAINT config_chankey01_1 PRIMARY KEY (progid, net, sta, seedchan, location)
)
;



-- 
-- TABLE: applications 
--

CREATE TABLE applications(
    progid    NUMBER(8, 0)    NOT NULL,
    name      VARCHAR2(16)    NOT NULL,
    lddate    DATE            DEFAULT SYSDATE,
    CONSTRAINT progkey01_1 PRIMARY KEY (progid)
)
;



-- 
-- TABLE: channelmap_ampparms 
--

CREATE TABLE channelmap_ampparms(
    net           VARCHAR2(8)      NOT NULL,
    sta           VARCHAR2(6)      NOT NULL,
    seedchan      VARCHAR2(3)      NOT NULL,
    location      VARCHAR2(2)      NOT NULL,
    ondate        DATE             NOT NULL,
    offdate       DATE             NOT NULL,
    channel       VARCHAR2(3),
    channelsrc    VARCHAR2(8),
    clip          NUMBER(14, 2),
    lddate        DATE             DEFAULT (SYSDATE),
    CONSTRAINT cm_amp_key PRIMARY KEY (net, sta, seedchan, location, ondate)
)
;



-- 
-- TABLE: channelmap_codaparms 
--

CREATE TABLE channelmap_codaparms(
    net           VARCHAR2(8)     NOT NULL,
    sta           VARCHAR2(6)     NOT NULL,
    seedchan      VARCHAR2(3)     NOT NULL,
    location      VARCHAR2(2)     NOT NULL,
    ondate        DATE            NOT NULL,
    offdate       DATE            NOT NULL,
    channel       VARCHAR2(3),
    channelsrc    VARCHAR2(8),
    cutoff        NUMBER(8, 2),
    gain_corr     NUMBER(6, 3),
    lddate        DATE            DEFAULT (SYSDATE),
    CONSTRAINT cm_coda_key PRIMARY KEY (net, sta, seedchan, location, ondate)
)
;



-- 
-- TABLE: config_channel 
--

CREATE TABLE config_channel(
    progid      NUMBER(8, 0)    NOT NULL,
    net         VARCHAR2(8)     NOT NULL,
    sta         VARCHAR2(6)     NOT NULL,
    seedchan    VARCHAR2(3)     NOT NULL,
    config      VARCHAR2(64),
    lddate      DATE            DEFAULT (SYSDATE),
    location    VARCHAR2(2)     NOT NULL,
    CONSTRAINT config_chankey01 PRIMARY KEY (progid, net, sta, seedchan, location)
)
;



-- 
-- TABLE: credit 
--

CREATE TABLE credit(
    id       NUMBER(15, 0)    NOT NULL,
    tname    VARCHAR2(30)     NOT NULL,
    refer    VARCHAR2(80)     NOT NULL,
    CONSTRAINT pk_credit PRIMARY KEY (id, tname)
)
;



-- 
-- TABLE: credit_alias 
--

CREATE TABLE credit_alias(
    alias    VARCHAR2(32)    NOT NULL,
    keyid    VARCHAR2(32)    NOT NULL,
    CONSTRAINT pk_alias PRIMARY KEY (alias, keyid)
)
;



-- 
-- TABLE: gazetteerbigtown 
--

CREATE TABLE gazetteerbigtown(
    gazid     NUMBER(16, 0)    NOT NULL,
    pop       NUMBER(10, 0),
    remark    VARCHAR2(80),
    lddate    DATE             DEFAULT (SYSDATE),
    CONSTRAINT gazbigtown_pk_gazid PRIMARY KEY (gazid)
)
;



-- 
-- TABLE: gazetteerfault 
--

CREATE TABLE gazetteerfault(
    gazid     NUMBER          NOT NULL,
    remark    VARCHAR2(80),
    lddate    DATE            DEFAULT (SYSDATE),
    CONSTRAINT gazfault_pk_gazid PRIMARY KEY (gazid)
)
;



-- 
-- TABLE: gazetteerline 
--

CREATE TABLE gazetteerline(
    gazid     NUMBER          NOT NULL,
    type      NUMBER(3, 0)    NOT NULL,
    name      VARCHAR2(80)    NOT NULL,
    line      NUMBER          NOT NULL,
    format    VARCHAR2(16),
    points    BLOB,
    lddate    DATE            DEFAULT (SYSDATE),
    CONSTRAINT gazline_pk_gazid PRIMARY KEY (gazid)
)
;



-- 
-- TABLE: gazetteerpt 
--

CREATE TABLE gazetteerpt(
    gazid     NUMBER(16, 0)    NOT NULL,
    type      NUMBER(3, 0)     NOT NULL,
    lat       NUMBER(9, 7)     NOT NULL,
    lon       NUMBER(10, 7)    NOT NULL,
    z         NUMBER(7, 0),
    name      VARCHAR2(48)     NOT NULL,
    state     VARCHAR2(2),
    county    VARCHAR2(32),
    map       VARCHAR2(48),
    datumh    VARCHAR2(8),
    datumv    VARCHAR2(8),
    lddate    DATE             DEFAULT (SYSDATE),
    CONSTRAINT gazpt_pk_gazid PRIMARY KEY (gazid)
)
;



-- 
-- TABLE: gazetteerquake 
--

CREATE TABLE gazetteerquake(
    gazid     NUMBER(16, 0)    NOT NULL,
    remark    VARCHAR2(80)     NOT NULL,
    lddate    DATE             DEFAULT (SYSDATE),
    CONSTRAINT gazeq_pk_gazid PRIMARY KEY (gazid)
)
;



-- 
-- TABLE: gazetteerquarry 
--

CREATE TABLE gazetteerquarry(
    gazid     NUMBER(16, 0)    NOT NULL,
    remark    VARCHAR2(80)     NOT NULL,
    lddate    DATE             DEFAULT (SYSDATE),
    CONSTRAINT gazqry_pk_gazid PRIMARY KEY (gazid)
)
;



-- 
-- TABLE: gazetteertown 
--

CREATE TABLE gazetteertown(
    gazid     NUMBER(16, 0)    NOT NULL,
    pop       NUMBER(10, 0),
    remark    VARCHAR2(80),
    lddate    DATE             DEFAULT (SYSDATE),
    CONSTRAINT gaztown_pk_gazid PRIMARY KEY (gazid)
)
;



-- 
-- TABLE: gazetteertype 
--

CREATE TABLE gazetteertype(
    code      NUMBER(3, 0)    NOT NULL,
    name      VARCHAR2(32)    NOT NULL,
    lddate    DATE            DEFAULT (SYSDATE),
    CONSTRAINT gaztype_pk_code PRIMARY KEY (code)
)
;



-- 
-- TABLE: jasieventlock 
--

CREATE TABLE jasieventlock(
    evid           NUMBER          NOT NULL,
    hostname       VARCHAR2(40),
    application    VARCHAR2(20),
    username       VARCHAR2(20),
    lddate         DATE,
    CONSTRAINT PK3 PRIMARY KEY (evid)
)
;



-- 
-- TABLE: program 
--

CREATE TABLE program(
    progid    NUMBER(8, 0)    NOT NULL,
    name      VARCHAR2(16)    NOT NULL,
    lddate    DATE            DEFAULT (SYSDATE),
    CONSTRAINT progkey01 PRIMARY KEY (progid)
)
;



-- 
-- TABLE: request_card 
--

CREATE TABLE request_card(
    evid            NUMBER(15, 0),
    auth            VARCHAR2(15)      NOT NULL,
    subsource       VARCHAR2(8)       NOT NULL,
    net             VARCHAR2(8)       NOT NULL,
    sta             VARCHAR2(8)       NOT NULL,
    seedchan        VARCHAR2(8)       NOT NULL,
    staauth         VARCHAR2(15)      NOT NULL,
    channel         VARCHAR2(8)       NOT NULL,
    datetime_on     NUMBER(25, 10)    NOT NULL,
    datetime_off    NUMBER(25, 10)    NOT NULL,
    request_type    VARCHAR2(1)       NOT NULL,
    lddate          DATE              DEFAULT (SYSDATE),
    rcid            NUMBER(15, 0)     NOT NULL,
    location        VARCHAR2(2)       NOT NULL,
    retry           NUMBER(38, 0),
    lastretry       DATE,
    priority        NUMBER,
    CONSTRAINT request_cardkey01 PRIMARY KEY (rcid)
)
;



-- 
-- TABLE: rt_role 
--

CREATE TABLE rt_role(
    primary_system       VARCHAR2(5),
    modification_time    DATE           DEFAULT (SYSDATE)
)
;



-- 
-- TABLE: stamapping 
--

CREATE TABLE stamapping(
    net          VARCHAR2(8)     NOT NULL,
    sta          VARCHAR2(6)     NOT NULL,
    stanumber    NUMBER(8, 0)    NOT NULL,
    lddate       DATE,
    locdescr     VARCHAR2(40),
    CONSTRAINT stamapkey01 PRIMARY KEY (net, sta)
)
;



-- 
-- TABLE: unassocamp 
--

CREATE TABLE unassocamp(
    ampid         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),
    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    NOT NULL,
    duration      DOUBLE PRECISION    NOT NULL,
    lddate        DATE                DEFAULT (SYSDATE),
    CONSTRAINT unassocampkey01 PRIMARY KEY (ampid)
)
;



-- 
-- TABLE: appchannels 
--

ALTER TABLE appchannels ADD CONSTRAINT Refapplications121 
    FOREIGN KEY (progid)
    REFERENCES applications(progid)
;


-- 
-- TABLE: config_channel 
--

ALTER TABLE config_channel ADD CONSTRAINT Refprogram81 
    FOREIGN KEY (progid)
    REFERENCES program(progid)
;


-- 
-- TABLE: gazetteerbigtown 
--

ALTER TABLE gazetteerbigtown ADD CONSTRAINT gazbigtown_fk_gazid 
    FOREIGN KEY (gazid)
    REFERENCES gazetteerpt(gazid)
;


-- 
-- TABLE: gazetteerfault 
--

ALTER TABLE gazetteerfault ADD CONSTRAINT gazfault_fk_gazid 
    FOREIGN KEY (gazid)
    REFERENCES gazetteerline(gazid)
;


-- 
-- TABLE: gazetteerline 
--

ALTER TABLE gazetteerline ADD CONSTRAINT gazline_fk_type 
    FOREIGN KEY (type)
    REFERENCES gazetteertype(code)
;


-- 
-- TABLE: gazetteerpt 
--

ALTER TABLE gazetteerpt ADD CONSTRAINT gazpt_fk_type 
    FOREIGN KEY (type)
    REFERENCES gazetteertype(code)
;


-- 
-- TABLE: gazetteerquake 
--

ALTER TABLE gazetteerquake ADD CONSTRAINT gazeq_fk_gazid 
    FOREIGN KEY (gazid)
    REFERENCES gazetteerpt(gazid)
;


-- 
-- TABLE: gazetteerquarry 
--

ALTER TABLE gazetteerquarry ADD CONSTRAINT gazqry_fk_gazid 
    FOREIGN KEY (gazid)
    REFERENCES gazetteerpt(gazid)
;


-- 
-- TABLE: gazetteertown 
--

ALTER TABLE gazetteertown ADD CONSTRAINT gaztown_fk_gazid 
    FOREIGN KEY (gazid)
    REFERENCES gazetteerpt(gazid)
;