--
--
-- SQL Script  : Create_Schema_IR.sql
--
-- Description : This SQL script creates the Instrument Responses Schema
--               version 1.4 in the Oracle RDBMS.
--
--


--
-- Delete the existing tables
--
DROP TABLE Station_Data		CASCADE CONSTRAINTS;
DROP TABLE Station_Comment	CASCADE CONSTRAINTS;
DROP TABLE Channel_Data		CASCADE CONSTRAINTS;
DROP TABLE Channel_Comment	CASCADE CONSTRAINTS;
DROP TABLE Polynomial		CASCADE CONSTRAINTS;
DROP TABLE PN			CASCADE CONSTRAINTS;
DROP TABLE PN_Data		CASCADE CONSTRAINTS;
DROP TABLE Coefficients		CASCADE CONSTRAINTS;
DROP TABLE DC			CASCADE CONSTRAINTS;
DROP TABLE DC_Data		CASCADE CONSTRAINTS;
DROP TABLE Decimation		CASCADE CONSTRAINTS;
DROP TABLE DM			CASCADE CONSTRAINTS;
DROP TABLE Poles_Zeros		CASCADE CONSTRAINTS;
DROP TABLE PZ			CASCADE CONSTRAINTS;
DROP TABLE PZ_Data		CASCADE CONSTRAINTS;
DROP TABLE Sensitivity		CASCADE CONSTRAINTS;
DROP TABLE D_Abbreviation	CASCADE CONSTRAINTS;
DROP TABLE D_Unit		CASCADE CONSTRAINTS;
DROP TABLE D_Comment		CASCADE CONSTRAINTS;
DROP TABLE D_Format		CASCADE CONSTRAINTS;
DROP TABLE D_Format_Data	CASCADE CONSTRAINTS;
DROP TABLE Simple_Response	CASCADE CONSTRAINTS;


--
-- Create the new schema
--
CREATE SCHEMA AUTHORIZATION ncedcdba;

CREATE TABLE Station_Data (
net		VARCHAR2(8)	NOT NULL,
sta		VARCHAR2(6)	NOT NULL,
lat		FLOAT,
lon		FLOAT,
elev		FLOAT,
staname		VARCHAR2(50),
net_id		NUMBER(8),
word_32		NUMBER(8)	NOT NULL,
word_16		NUMBER(8)	NOT NULL,
ondate		DATE		NOT NULL,
offdate		DATE,
lddate		DATE		DEFAULT (SYSDATE),
CONSTRAINT	StD00		PRIMARY KEY (net, sta, ondate),
-- CONSTRAINT	StD01		CHECK (elev >= -10.0 AND elev <= 10.0),
CONSTRAINT	StD02		CHECK (lat >= -90.0 AND lat <= 90.0),
CONSTRAINT	StD03		CHECK (lon >= -180.0 AND lon <= 180.0)
) TABLESPACE	IR001
  PCTFREE	5;

CREATE TABLE Station_Comment (
net		VARCHAR2(8)	NOT NULL,
sta		VARCHAR2(6)	NOT NULL,
ondate		DATE		NOT NULL,
offdate		DATE,
comment_id	NUMBER(8)	NOT NULL,
comment_level	NUMBER(8),
lddate		DATE		DEFAULT (SYSDATE),
CONSTRAINT	StC00		PRIMARY KEY (net, sta, ondate, comment_id)
) TABLESPACE	IR001
  PCTFREE       5;

CREATE TABLE Channel_Data (
net		VARCHAR2(8)	NOT NULL,
sta		VARCHAR2(6)	NOT NULL,
seedchan	VARCHAR2(3)	NOT NULL,
channel		VARCHAR2(3),
channelsrc	VARCHAR2(8),
location	VARCHAR2(2),
inid		NUMBER(8),
remark		VARCHAR2(30),
unit_signal	NUMBER(8)	NOT NULL,
unit_calib	NUMBER(8)	NOT NULL,
lat		FLOAT,
lon		FLOAT,
elev		FLOAT,
edepth		FLOAT,
azimuth		FLOAT,
dip		FLOAT,
format_id	NUMBER(8)	NOT NULL,
record_length	NUMBER(8),
samprate	FLOAT		NOT NULL,
clock_drift	FLOAT,
flags		VARCHAR2(27),
ondate		DATE		NOT NULL,
offdate		DATE,
lddate		DATE		DEFAULT (SYSDATE),
CONSTRAINT	ChD00		PRIMARY KEY (net, sta, seedchan, location, ondate),
CONSTRAINT	ChD01		CHECK (azimuth >= 0.0 AND azimuth <= 360.0),
CONSTRAINT	ChD02		CHECK (clock_drift >= 0.0),
CONSTRAINT	ChD03		CHECK (dip >= -90.0 AND dip <= 90.0),
CONSTRAINT	ChD04		CHECK (edepth >= 0.0),
-- CONSTRAINT	ChD05		CHECK (elev >= -10.0 AND elev <= 10.0),
CONSTRAINT	ChD06		CHECK (lat >= -90.0 AND lat <= 90.0),
CONSTRAINT	ChD07		CHECK (lon >= -180.0 AND lon <= 180.0),
CONSTRAINT	ChD08		CHECK (record_length >= 8 AND record_length <= 12),
CONSTRAINT	ChD09		CHECK (samprate >= 0.0)
) TABLESPACE	IR001
  PCTFREE       5;

CREATE TABLE Channel_Comment (
net		VARCHAR2(8)	NOT NULL,
sta		VARCHAR2(6)	NOT NULL,
seedchan	VARCHAR2(3)	NOT NULL,
channel		VARCHAR2(3),
channelsrc	VARCHAR2(8),
location	VARCHAR2(2),
ondate		DATE		NOT NULL,
offdate		DATE,
comment_id	NUMBER(8)	NOT NULL,
comment_level	NUMBER(8)	NOT NULL,
lddate		DATE		DEFAULT (SYSDATE),
CONSTRAINT	ChC00		PRIMARY KEY (net, sta, seedchan, location, ondate, comment_id)
) TABLESPACE	IR001
  PCTFREE       5;

CREATE TABLE Polynomial (
net             VARCHAR2(8)     NOT NULL,
sta             VARCHAR2(6)     NOT NULL,
seedchan        VARCHAR2(3)     NOT NULL,
channel         VARCHAR2(3),
channelsrc      VARCHAR2(8),
location        VARCHAR2(2),
ondate          DATE            NOT NULL,
offdate         DATE,
stage_seq       NUMBER(8)       NOT NULL,
pn_key          NUMBER(8)       NOT NULL,
unit_in         NUMBER(8)       NOT NULL,
unit_out        NUMBER(8)       NOT NULL,
tf_type         VARCHAR2(1),
lddate          DATE            DEFAULT (SYSDATE),
CONSTRAINT	Po00		PRIMARY KEY (net, sta, seedchan, location, ondate, stage_seq),
CONSTRAINT      Po01            CHECK (stage_seq >= 0),
CONSTRAINT      Po02            CHECK (tf_type IN ('A','B','C','D','P'))
) TABLESPACE    IR001
  PCTFREE       5;

CREATE TABLE PN (
key             NUMBER(8)       NOT NULL,
name            VARCHAR2(80),
poly_type	VARCHAR2(1),
lower_bound	FLOAT,
upper_bound	FLOAT,
max_error	FLOAT,
lddate          DATE            DEFAULT (SYSDATE),
CONSTRAINT	PN00		PRIMARY KEY (key),
CONSTRAINT      PN01		CHECK (poly_type IN ('C','L','M'))
) TABLESPACE    IR001
  PCTFREE       5;

CREATE TABLE PN_Data (
key             NUMBER(8)       NOT NULL,
row_key         NUMBER(8)       NOT NULL,
pn_value	FLOAT,
CONSTRAINT	PND00		PRIMARY KEY (key, row_key),
CONSTRAINT      PND01           CHECK (row_key >= 0)
) TABLESPACE    IR001
  PCTFREE       5;

CREATE TABLE Coefficients (
net		VARCHAR2(8)	NOT NULL,
sta		VARCHAR2(6)	NOT NULL,
seedchan	VARCHAR2(3)	NOT NULL,
channel		VARCHAR2(3),
channelsrc	VARCHAR2(8),
location	VARCHAR2(2),
ondate		DATE		NOT NULL,
offdate		DATE,
stage_seq	NUMBER(8)	NOT NULL,
dc_key		NUMBER(8),
unit_in         NUMBER(8)       NOT NULL,
unit_out        NUMBER(8)       NOT NULL,
tf_type         VARCHAR2(1),
lddate		DATE		DEFAULT (SYSDATE),
CONSTRAINT	Co00		PRIMARY KEY (net, sta, seedchan, location, ondate, stage_seq),
CONSTRAINT	Co01		CHECK (stage_seq >= 0),
CONSTRAINT      Co02            CHECK (tf_type IN ('A','B','C','D','P'))
) TABLESPACE	IR001
  PCTFREE       5;

CREATE TABLE DC (
key		NUMBER(8)	NOT NULL,
name		VARCHAR2(80),
symmetry	VARCHAR2(1),
storage		VARCHAR2(1),
lddate		DATE		DEFAULT (SYSDATE),
CONSTRAINT	DC00		PRIMARY KEY (key),
CONSTRAINT	DC01		CHECK (storage IN ('H','F')),
CONSTRAINT	DC02		CHECK (symmetry IN ('E','O','N'))
) TABLESPACE	IR001
  PCTFREE       5;

CREATE TABLE DC_Data (
key		NUMBER(8)	NOT NULL,
row_key		NUMBER(8)	NOT NULL,
type		VARCHAR2(1),
coefficient	FLOAT		NOT NULL,
error		FLOAT,
CONSTRAINT	DCD00		PRIMARY KEY (key, row_key),
CONSTRAINT	DCD01		CHECK (row_key >= 0),
CONSTRAINT	DCD02		CHECK (type IN ('P','Z','N','D'))
) TABLESPACE	IR001
  PCTFREE       5;

CREATE TABLE Decimation (
net		VARCHAR2(8)	NOT NULL,
sta		VARCHAR2(6)	NOT NULL,
seedchan	VARCHAR2(3)	NOT NULL,
channel		VARCHAR2(3),
channelsrc	VARCHAR2(8),
location	VARCHAR2(2),
ondate		DATE		NOT NULL,
offdate		DATE,
stage_seq	NUMBER(8)	NOT NULL,
dm_key		NUMBER(8)	NOT NULL,
lddate		DATE		DEFAULT (SYSDATE),
CONSTRAINT      De00            PRIMARY KEY (net, sta, seedchan, location, ondate, stage_seq),
CONSTRAINT	De01		CHECK (stage_seq >= 0)
) TABLESPACE	IR001
  PCTFREE       5;

CREATE TABLE DM (
key		NUMBER(8)	NOT NULL,
name		VARCHAR2(80),
samprate	FLOAT		NOT NULL,
factor		NUMBER(8)	NOT NULL,
offset		NUMBER(8),
delay		FLOAT,
correction	FLOAT		NOT NULL,
lddate		DATE		DEFAULT (SYSDATE),
CONSTRAINT	DM00		PRIMARY KEY (key),
CONSTRAINT	DM01		CHECK (offset >= 0.0),
CONSTRAINT	DM02		CHECK (samprate >= 0.0)
) TABLESPACE	IR001
  PCTFREE       5;

CREATE TABLE Poles_Zeros (
net		VARCHAR2(8)	NOT NULL,
sta		VARCHAR2(6)	NOT NULL,
seedchan	VARCHAR2(3)	NOT NULL,
channel		VARCHAR2(3),
channelsrc	VARCHAR2(8),
location	VARCHAR2(2),
ondate		DATE		NOT NULL,
offdate		DATE,
stage_seq	NUMBER(8)	NOT NULL,
pz_key		NUMBER(8)	NOT NULL,
tf_type		VARCHAR(1),
unit_in		NUMBER(8)	NOT NULL,
unit_out	NUMBER(8)	NOT NULL,
AO		FLOAT		NOT NULL,
AF		FLOAT,
lddate		DATE		DEFAULT (SYSDATE),
CONSTRAINT      P_Z00           PRIMARY KEY (net, sta, seedchan, location, ondate, stage_seq),
CONSTRAINT	P_Z01		CHECK (AF >= 0.0),
CONSTRAINT	P_Z02		CHECK (stage_seq >= 0),
CONSTRAINT	P_Z03		CHECK (tf_type IN ('A','B','C','D','P'))
) TABLESPACE	IR001
  PCTFREE       5;

CREATE TABLE PZ (
key		NUMBER(8)	NOT NULL,
name		VARCHAR2(80),
lddate		DATE		DEFAULT (SYSDATE),
CONSTRAINT      PZ00            PRIMARY KEY (key)
) TABLESPACE	IR001
  PCTFREE       5;

CREATE TABLE PZ_Data (
key		NUMBER(8)	NOT NULL,
row_key		NUMBER(8)	NOT NULL,
type		VARCHAR2(1),
r_value		FLOAT		NOT NULL,
r_error		FLOAT,
i_value		FLOAT		NOT NULL,
i_error		FLOAT,
CONSTRAINT	PZD00		PRIMARY KEY (key, row_key),
CONSTRAINT	PZD01		CHECK (row_key >= 0),
CONSTRAINT	PZD02		CHECK (type IN ('P','Z','N','D'))
) TABLESPACE	IR001
  PCTFREE       5;

CREATE TABLE Sensitivity (
net		VARCHAR2(8)	NOT NULL,
sta		VARCHAR2(6)	NOT NULL,
seedchan	VARCHAR2(3)	NOT NULL,
channel		VARCHAR2(3),
channelsrc	VARCHAR2(8),
location	VARCHAR2(2),
ondate		DATE		NOT NULL,
offdate		DATE,
stage_seq	NUMBER(8)	NOT NULL,
sensitivity	FLOAT		NOT NULL,
frequency	FLOAT,
lddate		DATE		DEFAULT (SYSDATE),
CONSTRAINT      Se00		PRIMARY KEY (net, sta, seedchan, location, ondate, stage_seq),
CONSTRAINT	Se01		CHECK (frequency >= 0.0),
CONSTRAINT	Se02		CHECK (stage_seq >= 0)
) TABLESPACE	IR001
  PCTFREE       5;

CREATE TABLE D_Abbreviation (
id		NUMBER(8)	NOT NULL,
description	VARCHAR2(70),
CONSTRAINT	D_A00		PRIMARY KEY (id)
) TABLESPACE	IR001
  PCTFREE       5
STORAGE (
INITIAL		32K
NEXT		8K
MINEXTENTS	2
MAXEXTENTS	UNLIMITED
PCTINCREASE	0);

CREATE TABLE D_Unit (
id		NUMBER(8)	NOT NULL,
name		VARCHAR2(80),
description	VARCHAR2(70),
CONSTRAINT      D_U00           PRIMARY KEY (id)
) TABLESPACE	IR001
  PCTFREE       5
STORAGE ( 
INITIAL         32K 
NEXT            8K 
MINEXTENTS      2
MAXEXTENTS      UNLIMITED 
PCTINCREASE     0);

CREATE TABLE D_Comment (
id		NUMBER(8)	NOT NULL,
class		VARCHAR2(1)	NOT NULL,
description	VARCHAR2(70),
unit		NUMBER(8)	NOT NULL,
CONSTRAINT      D_C00           PRIMARY KEY (id)
) TABLESPACE	IR001
  PCTFREE       5
STORAGE ( 
INITIAL         32K 
NEXT            8K 
MINEXTENTS      2
MAXEXTENTS      UNLIMITED 
PCTINCREASE     0);

CREATE TABLE D_Format (
id		NUMBER(8)	NOT NULL,
name		VARCHAR2(80),
family		NUMBER(8)	NOT NULL,
ms_id		NUMBER(8)	NOT NULL,
CONSTRAINT      D_F00           PRIMARY KEY (id)
) TABLESPACE	IR001
  PCTFREE       5
STORAGE ( 
INITIAL         32K 
NEXT            8K 
MINEXTENTS      2
MAXEXTENTS      UNLIMITED 
PCTINCREASE     0);

CREATE TABLE D_Format_Data (
id		NUMBER(8)	NOT NULL,
row_id		NUMBER(8)	NOT NULL,
key_d		VARCHAR2(80)	NOT NULL,
CONSTRAINT      D_F_D00         PRIMARY KEY (id, row_id),
CONSTRAINT	D_F_D01		CHECK (row_id >= 0)
) TABLESPACE	IR001
  PCTFREE       5
STORAGE ( 
INITIAL         32K 
NEXT            8K 
MINEXTENTS      2
MAXEXTENTS      UNLIMITED 
PCTINCREASE     0);

CREATE TABLE Simple_Response (
net		VARCHAR2(8)	NOT NULL,
sta		VARCHAR2(6)	NOT NULL,
seedchan	VARCHAR2(3)	NOT NULL,
channel		VARCHAR2(3),
channelsrc	VARCHAR2(8),
location	VARCHAR2(2),
inid		NUMBER(8),
frequency	FLOAT,
damping_value	FLOAT,
gen_const	FLOAT,
dig_const	FLOAT,
gain		FLOAT,
pz_key		NUMBER(8),
ml_corr		FLOAT,
me_corr		FLOAT,
ondate		DATE		NOT NULL,
offdate		DATE,
lddate		DATE		DEFAULT (SYSDATE),
CONSTRAINT	SiRe00		PRIMARY KEY (net, sta, seedchan, location, ondate),
CONSTRAINT	SiRe01		CHECK (frequency >= 0.0)
) TABLESPACE	IR001
  PCTFREE       5;


--
-- Create the sequence numbers
--
DROP SEQUENCE	poseq;
DROP SEQUENCE	dcseq;
DROP SEQUENCE	dmseq;
DROP SEQUENCE	pzseq;
DROP SEQUENCE	abbseq;
DROP SEQUENCE	uniseq;
DROP SEQUENCE	comseq;
DROP SEQUENCE	forseq;

CREATE SEQUENCE poseq	INCREMENT BY 1  START WITH 1;
CREATE SEQUENCE	dcseq	INCREMENT BY 1	START WITH 1;
CREATE SEQUENCE	dmseq	INCREMENT BY 1	START WITH 1;
CREATE SEQUENCE	pzseq	INCREMENT BY 1	START WITH 1;
CREATE SEQUENCE	abbseq	INCREMENT BY 1	START WITH 1;
CREATE SEQUENCE	uniseq	INCREMENT BY 1	START WITH 1;
CREATE SEQUENCE	comseq	INCREMENT BY 1	START WITH 1;
CREATE SEQUENCE	forseq	INCREMENT BY 1	START WITH 1;

COMMIT;
EXIT;

