--
--
-- SQL Script  : Create_Schema_PIv1.5.sql
--
-- Description : This SQL script CREATEs the Parametric Information Schema
--               version 1.5 in the Oracle RDBMS.
--
--

--
-- Delete the existing tables
--
DROP TABLE event 		CASCADE CONSTRAINTS;
DROP TABLE significant_event 	CASCADE CONSTRAINTS;
DROP TABLE origin 		CASCADE CONSTRAINTS;
DROP TABLE origin_error 	CASCADE CONSTRAINTS;
DROP TABLE netmag 		CASCADE CONSTRAINTS;
DROP TABLE arrival 		CASCADE CONSTRAINTS;
DROP TABLE assocaro 		CASCADE CONSTRAINTS;
DROP TABLE amp 			CASCADE CONSTRAINTS;
DROP TABLE assocamo 		CASCADE CONSTRAINTS;
DROP TABLE assocamm 		CASCADE CONSTRAINTS;
DROP TABLE mec 			CASCADE CONSTRAINTS;
DROP TABLE remark 		CASCADE CONSTRAINTS;
DROP TABLE coda 		CASCADE CONSTRAINTS;
DROP TABLE assoccom 		CASCADE CONSTRAINTS;
DROP TABLE assoccoo 		CASCADE CONSTRAINTS;

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

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


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

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

CREATE TABLE origin (
orid 		NUMBER(15),
evid 		NUMBER(15) 	NOT NULL,
prefmag 	NUMBER(15),
prefmec 	NUMBER(15),
commid 		NUMBER(15),
bogusflag 	NUMBER(1),
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),
totalamp 	NUMBER(6),
ndef 		NUMBER(5),
nbs 		NUMBER(4),
nbfm 		NUMBER(4),
locevid 	VARCHAR(12),
quality 	NUMBER(2,1),
fdepth 		VARCHAR2(1),
fepi 		VARCHAR2(1),
ftime 		VARCHAR2(1),
vmodelid 	NUMBER(3),
cmodelid 	NUMBER(3),
lddate 		DATE 		DEFAULT (SYSDATE),
rflag 		VARCHAR2(2),
CONSTRAINT 	orkey01 	PRIMARY KEY (orid),
-- CONSTRAINT 	orkey02 	FOREIGN KEY (evid) REFERENCES event(evid) ON DELETE CASCADE,
CONSTRAINT 	origin01 	CHECK (cmodelid > 0),
CONSTRAINT 	origin02 	CHECK (datumhor in ('NAD27','WGS84')),
CONSTRAINT 	origin03 	CHECK (datumver in ('NAD27','WGS84','AVERAGE')),
CONSTRAINT 	origin04 	CHECK (depth >= -10.0 and depth <= 1000.0),
CONSTRAINT 	origin05 	CHECK (distance >= 0.0),
CONSTRAINT 	origin06 	CHECK (erhor >= 0.0),
CONSTRAINT 	origin07 	CHECK (erlat >= 0.0),
CONSTRAINT 	origin08 	CHECK (erlon >= 0.0),
CONSTRAINT 	origin09 	CHECK (fdepth in ('y','n')),
CONSTRAINT 	origin10 	CHECK (fepi in ('y','n')),
CONSTRAINT 	origin11 	CHECK (ftime in ('y','n')),
CONSTRAINT 	origin12 	CHECK (gap >= 0.0 and gap <= 360.0),
CONSTRAINT 	origin15 	CHECK (nbfm >= 0),
CONSTRAINT 	origin16 	CHECK (nbs >= 0),
CONSTRAINT 	origin17 	CHECK (ndef >= 0),
CONSTRAINT 	origin18 	CHECK (orid > 0),
CONSTRAINT 	origin19 	CHECK (quality >= 0.0 and quality <= 1.0),
CONSTRAINT 	origin20 	CHECK (type in ('H','HO','HR','HI','GO','C','A')),
CONSTRAINT 	origin21 	CHECK (stime >= 0.0),
CONSTRAINT 	origin22 	CHECK (vmodelid > 0),
CONSTRAINT 	origin23 	CHECK (wrms >= 0.0),
CONSTRAINT 	origin24 	CHECK (sdep >=0.0),
CONSTRAINT 	origin25 	CHECK (totalarr >=0),
CONSTRAINT 	origin26 	CHECK (totalamp > = 0),
CONSTRAINT 	origin28 	CHECK (rflag in ('a','h','f','A','H','F'))
) TABLESPACE PI000;

CREATE TABLE origin_error(
orid 		NUMBER(15),
sxx 		NUMBER(5,3),
syy 		NUMBER(5,3),
szz 		NUMBER(5,3),
stt 		NUMBER(5,3),
sxy 		NUMBER(5,3), 
sxz 		NUMBER(5,3),
syz 		NUMBER(5,3),
stx 		NUMBER(5,3),
sty 		NUMBER(5,3),
stz 		NUMBER(5,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 		DEFAULT (SYSDATE),
CONSTRAINT 	orerrkey01 	PRIMARY KEY (orid),
CONSTRAINT 	orig_error01	CHECK (aziinter >= 0.0 and aziinter <= 360.0),
CONSTRAINT 	orig_error02 	CHECK (azilarge >= 0.0 and azilarge <= 360.0),
CONSTRAINT 	orig_error03 	CHECK (azismall >= 0.0 and azismall <= 360.0),
CONSTRAINT 	orig_error04 	CHECK (dipinter >= -90.0 and dipinter <= 90.0),
CONSTRAINT 	orig_error05 	CHECK (diplarge >= -90.0 and diplarge <= 90.0),
CONSTRAINT 	orig_error06 	CHECK (dipsmall >= -90.0 and dipsmall <= 90.0),
CONSTRAINT 	orig_error07 	CHECK (maginter >= 0.0),
CONSTRAINT 	orig_error08 	CHECK (maglarge >= 0.0),
CONSTRAINT 	orig_error09 	CHECK (magsmall >= 0.0),
CONSTRAINT 	orig_error10 	CHECK (stx > 0.0),
CONSTRAINT 	orig_error11 	CHECK (sty > 0.0),
CONSTRAINT 	orig_error12 	CHECK (stz > 0.0),
CONSTRAINT 	orig_error13 	CHECK (sxx > 0.0),
CONSTRAINT 	orig_error14 	CHECK (sxy > 0.0),
CONSTRAINT 	orig_error15 	CHECK (sxz > 0.0),
CONSTRAINT 	orig_error16 	CHECK (syy > 0.0),
CONSTRAINT 	orig_error17 	CHECK (syz > 0.0),
CONSTRAINT 	orig_error18 	CHECK (stt > 0.0),
CONSTRAINT 	orig_error19 	CHECK (szz > 0.0)
) TABLESPACE PI000;

CREATE TABLE netmag (
magid 		NUMBER(15),
orid 		NUMBER(15) 	NOT NULL,
commid 		NUMBER(15),
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),
uncertainty 	NUMBER(5,3),
gap 		NUMBER(4,1),
distance 	NUMBER(7,3),
quality 	NUMBER(2,1),
rflag 		VARCHAR2(2),
lddate 		DATE 		DEFAULT (SYSDATE),
CONSTRAINT 	magkey01 	PRIMARY KEY (magid),
-- CONSTRAINT 	magkey02 	FOREIGN KEY (orid) REFERENCES origin(orid) ON DELETE CASCADE,
-- CONSTRAINT 	magkey04 	FOREIGN KEY (commid) REFERENCES remark (commid) ON DELETE CASCADE,
CONSTRAINT 	netmag01 	CHECK (magnitude >= -10.0 and magnitude <= 10.0),
CONSTRAINT 	netmag02 	CHECK (magtype in ('a','b','e','l','l1','l2','lg','c','s','w','z','B','un','d','h','n')),
CONSTRAINT 	netmag03 	CHECK (nsta >= 0),
CONSTRAINT 	netmag04 	CHECK (uncertainty >= 0.0),
CONSTRAINT 	netmag05 	CHECK (quality >= 0.0 and quality < =1.0),
CONSTRAINT 	netmag06 	CHECK (magid > 0),
CONSTRAINT 	netmag07 	CHECK (rflag in ('a','h','f','A','H','F'))
) TABLESPACE PI000;

CREATE TABLE arrival (
arid 		NUMBER(15),
commid 		NUMBER(15),
datetime 	NUMBER(25,10) 	NOT NULL,
sta 		VARCHAR2(6) 	NOT NULL,
net 		VARCHAR2(8),
auth 		VARCHAR2(15),	NOT NULL,
subsource 	VARCHAR2(8),
channel 	VARCHAR2(3),
channelsrc 	VARCHAR2(8),
seedchan 	VARCHAR2(3),
location 	VARCHAR2(2),
iphase 		VARCHAR2(8),
qual 		VARCHAR2(1),
clockqual 	VARCHAR2(1),
clockcorr 	NUMBER(15),
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(2,1),
snr 		FLOAT,
rflag 		VARCHAR2(2),
lddate 		DATE 		DEFAULT (SYSDATE),
CONSTRAINT 	arkey01 	PRIMARY KEY (arid),
-- CONSTRAINT 	arkey03 	FOREIGN KEY (commid) REFERENCES remark (commid) ON DELETE CASCADE,
CONSTRAINT 	arrival01 	CHECK (arid > 0),
CONSTRAINT 	arrival02 	CHECK (azimuth >= 0.0 and azimuth <= 360.0),
CONSTRAINT 	arrival03 	CHECK (delaz > 0.0),
CONSTRAINT 	arrival04 	CHECK (delinc >= 0.0),
CONSTRAINT 	arrival05 	CHECK (delslo > 0.0),
CONSTRAINT 	arrival06 	CHECK (deltim >= 0.0),
-- CONSTRAINT 	arrival07 	CHECK (ema >= 0.0 and ema <= 90.0),
-- CONSTRAINT 	arrival08 	CHECK (fm in ('..','.i','.e','.x','ci','ce','cx','c.','di','de','dx','d.')),
-- CONSTRAINT 	arrival09 	CHECK (qual in ('i','e','w','I','E','W')),
CONSTRAINT 	arrival10 	CHECK (slow >= 0.0),
CONSTRAINT 	arrival11 	CHECK (snr > 0.0),
-- CONSTRAINT 	arrival12 	CHECK (quality >=0.0 and quality <=1.0),
CONSTRAINT 	arrival13 	CHECK (ccset < 1),
CONSTRAINT 	arrival14 	CHECK (rflag in ('a','h','f','A','H','F'))
) TABLESPACE PI000;

CREATE TABLE assocaro (
orid 		NUMBER(15),
arid 		NUMBER(15),
commid 		NUMBER(15),
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),
azres 		NUMBER(5,3),
emares 		NUMBER(5,3),
slores 		NUMBER(8,4),
vmodelid 	NUMBER(3),
scorr 		NUMBER(6,4),
sdelay 		NUMBER(7,4),
rflag 		VARCHAR2(2),
ccset 		VARCHAR2(1),
lddate 		DATE 		DEFAULT (SYSDATE),
CONSTRAINT 	assocarokey01 	PRIMARY KEY (orid, arid),
-- CONSTRAINT 	assocarokey02 	FOREIGN KEY (orid) REFERENCES origin (orid) ON DELETE CASCADE,
-- CONSTRAINT 	assocarokey03 	FOREIGN KEY (arid) REFERENCES arrival (arid) ON DELETE CASCADE,
-- CONSTRAINT 	assocarokey04 	FOREIGN KEY (commid) REFERENCES remark (commid) ON DELETE CASCADE,
CONSTRAINT 	assocaro01 	CHECK (azres >= -180.0 and azres <= 180.0),
CONSTRAINT 	assocaro02 	CHECK (delta >= 0.0),
CONSTRAINT 	assocaro03 	CHECK (emares >= -90 and emares <= 90),
CONSTRAINT 	assocaro04 	CHECK (importance >= 0.0 and importance < 1.0),
CONSTRAINT 	assocaro05 	CHECK (seaz >= 0.0 and seaz <= 360.0),
CONSTRAINT 	assocaro06 	CHECK (slores >= 0.0),
-- CONSTRAINT 	assocaro07 	CHECK (timeres >= 0.0),
-- CONSTRAINT 	assocaro08 	CHECK (wgt >= 0.0 and wgt <= 1.0),
CONSTRAINT 	assocaro09 	CHECK (ccset < 1),
CONSTRAINT 	assocaro10 	CHECK (rflag in ('a','h','f','A','H','F'))
) TABLESPACE PI000;

CREATE TABLE amp (
ampid 		NUMBER(15),
commid 		NUMBER(15),
datetime 	NUMBER(25,10) 	NOT NULL,
sta 		VARCHAR2(6) 	NOT NULL,
net 		VARCHAR2(8),
auth 		VARCHAR2(15) 	NOT NULL,
subsource 	VARCHAR2(8),
channel 	VARCHAR2(3),
channelsrc 	VARCHAR2(8),
seedchan 	VARCHAR2(3),
location 	VARCHAR2(2),
iphase 		VARCHAR2(8),
amplitude 	FLOAT 		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 		FLOAT,
tau 		NUMBER(7,4),
quality 	NUMBER(2,1),
rflag 		VARCHAR2(2),
cflag 		VARCHAR2(2),
wstart 		FLOAT,
duration 	FLOAT,
lddate 		DATE 		DEFAULT (SYSDATE),
CONSTRAINT 	ampkey01 	PRIMARY KEY (ampid),
-- CONSTRAINT 	ampkey02 	FOREIGN KEY (commid) REFERENCES remark (commid) ON DELETE CASCADE,
CONSTRAINT 	amp01 		CHECK (ampid > 0),
CONSTRAINT 	amp02 		CHECK (amplitude > 0),
CONSTRAINT 	amp03 		CHECK (ampmeas in ('0','1')),
-- CONSTRAINT 	amp04 		CHECK (amptype in ('C','WA','WAS','PGA','PGV','PGD','WAC','WAU','IV2','SP.3','SP1.0','SP3.0','ML100','ME100','EGY')),
CONSTRAINT 	amp06 		CHECK (eramp >= 0.0),
CONSTRAINT 	amp07 		CHECK (flagamp in ('P','S','R','PP','ALL','SUR')),
CONSTRAINT 	amp08 		CHECK (per > 0.0),
CONSTRAINT 	amp09 		CHECK (tau > 0.0),
CONSTRAINT 	amp10 		CHECK (units in ('c','s','mm','cm','m','ms','mss','cms','cmss','mms','mmss','mc','nm','e','iovs','spa','none')),
CONSTRAINT 	amp11 		CHECK (quality >=0.0 and quality <=1.0),
CONSTRAINT 	amp12 		CHECK (rflag in ('a','h','f','A','H','F')),
CONSTRAINT 	amp13 		CHECK (cflag in ('bn', 'os','cl','BN','OS','CL'))
) TABLESPACE PI000;

CREATE TABLE assocamo (
orid 		NUMBER(15),
ampid 		NUMBER(15),
commid 		NUMBER(15),
auth 		VARCHAR2(15) 	NOT NULL,
subsource 	VARCHAR2(8),
delta 		NUMBER(5,1),
seaz 		NUMBER(4,1),
rflag 		VARCHAR2(2),
lddate 		DATE 		DEFAULT (SYSDATE),
CONSTRAINT 	assocamokey01 	PRIMARY KEY (orid, ampid),
-- CONSTRAINT 	assocamokey02 	FOREIGN KEY (orid) REFERENCES origin (orid) ON DELETE CASCADE,
-- CONSTRAINT 	assocamokey03 	FOREIGN KEY (ampid) REFERENCES amp (ampid) ON DELETE CASCADE,
-- CONSTRAINT 	assocamokey04 	FOREIGN KEY (commid) REFERENCES remark (commid) ON DELETE CASCADE,
CONSTRAINT 	assocamo01 	CHECK (seaz >= 0.0 and seaz <= 360.0),
CONSTRAINT 	assocamo02 	CHECK (delta >=0.0),
CONSTRAINT 	assocamo03 	CHECK (rflag in ('a','h','f','A','H','F'))
) TABLESPACE PI000;

CREATE TABLE assocamm (
magid 		NUMBER(15),
ampid 		NUMBER(15),
commid 		NUMBER(15),
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 		DEFAULT (SYSDATE),
CONSTRAINT 	assocammkey01 	PRIMARY KEY (magid, ampid),
-- CONSTRAINT 	assocammkey02 	FOREIGN KEY (magid) REFERENCES netmag(magid) ON DELETE CASCADE,
-- CONSTRAINT 	assocammkey03 	FOREIGN KEY (ampid) REFERENCES amp(ampid) ON DELETE CASCADE,
-- CONSTRAINT 	assocammkey04 	FOREIGN KEY (commid) REFERENCES remark(commid) ON DELETE CASCADE,
CONSTRAINT 	assocamm01 	CHECK (mag >= -10.0 and mag <= 10.0),
CONSTRAINT 	assocamm02 	CHECK (magcorr >= -10.0 and magcorr <= 10.0),
CONSTRAINT 	assocamm03 	CHECK (magid > 0),
CONSTRAINT 	assocamm04 	CHECK (magres >= 0.0),
CONSTRAINT 	assocamm05 	CHECK (weight >= 0.0 and weight <= 1.0),
CONSTRAINT 	assocamm06 	CHECK (in_wgt >= 0.0 and in_wgt <=1.0),
CONSTRAINT 	assocamm07 	CHECK (importance > 0.0 and importance <=1.0),
CONSTRAINT 	assocamm08 	CHECK (rflag in ('a','h','f','A','H','F'))
) TABLESPACE PI000;

CREATE TABLE mec (
mecid 		NUMBER(15),
oridin 		NUMBER(15),
oridout 	NUMBER(15),
magid 		NUMBER(15),
commid 		NUMBER(8),
mechtype 	VARCHAR2(2),
mecalgo 	VARCHAR2(15),
scalar 		FLOAT,
erscalar 	FLOAT,
tft 		VARCHAR2(8),
tfd 		FLOAT,
mxx 		FLOAT,
myy 		FLOAT,
mzz 		FLOAT,
mxy 		FLOAT,
mxz 		FLOAT,
myz 		FLOAT,
smxx 		FLOAT,
smyy 		FLOAT,
smzz 		FLOAT,
smxy 		FLOAT,
smxz 		FLOAT,
smyz 		FLOAT,
srcduration 	NUMBER(6,3),
auth 		VARCHAR2(15) 	NOT NULL,
subsource 	VARCHAR2(8),
strike1 	NUMBER(3),
dip1 		NUMBER(3),
rake1 		NUMBER(4),
strike2 	NUMBER(3),
dip2 		NUMBER(2),
rake2 		NUMBER(4),
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 		NUMBER(7,5),
plungep 	NUMBER(2),
strikep 	NUMBER(3),
eigenn 		NUMBER(7,5),
plungen 	NUMBER(2),
striken 	NUMBER(3),
eigent 		NUMBER(7,5),
plunget 	NUMBER(2),
striket 	NUMBER(3),
nsta 		NUMBER(5),
pvr 		NUMBER(5),
quality 	NUMBER(2,1),
pdc 		NUMBER(3),
pclvd 		NUMBER(3),
piso 		NUMBER(3),
datetime 	NUMBER(25,10) 	NOT NULL,
lddate 		DATE 		DEFAULT (SYSDATE),
CONSTRAINT 	meckey01 	PRIMARY KEY (mecid),
-- CONSTRAINT 	meckey02 	FOREIGN KEY (oridin) REFERENCES origin(orid) ON DELETE CASCADE,
-- CONSTRAINT 	meckey03 	FOREIGN KEY (oridout) REFERENCES origin(orid) ON DELETE CASCADE,
-- CONSTRAINT 	meckey05 	FOREIGN KEY (magid) REFERENCES netmag(magid) ON DELETE CASCADE,
-- CONSTRAINT 	meckey06 	FOREIGN KEY (commid) REFERENCES remark(commid) ON DELETE CASCADE,
CONSTRAINT 	mec01 		CHECK (dip1 >= -90 and dip1 <= 90),
CONSTRAINT 	mec02 		CHECK (dip2 >= -90 and dip2 <= 90),
CONSTRAINT 	mec03 		CHECK (erscalar >= 0.0),
CONSTRAINT 	mec05 		CHECK (mecid > 0),
CONSTRAINT 	mec06 		CHECK (mechtype in ('C','C0','F','F0','H','N','S','S0')),
CONSTRAINT 	mec07 		CHECK (mxx >= -10.0 and mxx <= 10.0),
CONSTRAINT 	mec08 		CHECK (myy >= -10.0 and myy <= 10.0),
CONSTRAINT 	mec09 		CHECK (mzz >= -10.0 and mzz <= 10.0),
CONSTRAINT 	mec10 		CHECK (mxy >= -10.0 and mxy <= 10.0),
CONSTRAINT 	mec11 		CHECK (mxz >= -10.0 and mxz <= 10.0),
CONSTRAINT 	mec12 		CHECK (myz >= -10.0 and myz <= 10.0),
CONSTRAINT 	mec13 		CHECK (plungen >= 0 and plungen <= 90),
CONSTRAINT 	mec14 		CHECK (plungep >= 0 and plungep <= 90),
CONSTRAINT 	mec15 		CHECK (plunget >= 0 and plunget <= 90),
CONSTRAINT 	mec16 		CHECK (pclvd >= 0 and pclvd <= 100),
CONSTRAINT 	mec17 		CHECK (pdc >= 0 and pdc <= 100),
CONSTRAINT 	mec18 		CHECK (piso >= 0 and piso <= 100),
CONSTRAINT 	mec19 		CHECK (pvr >= 0 and pvr <= 100),
CONSTRAINT 	mec20 		CHECK (rake1 >= -180 and rake1 <= 180),
CONSTRAINT 	mec21 		CHECK (rake2 >= -180 and rake2 <= 180),
CONSTRAINT 	mec22 		CHECK (scalar >= -10.0 and scalar <= 10.0),
CONSTRAINT 	mec23 		CHECK (srcduration >= 0.0 and srcduration <= 100.0),
CONSTRAINT 	mec24 		CHECK (striken >= 0 and striken <= 360),
CONSTRAINT 	mec25 		CHECK (strikep >= 0 and strikep <= 360),
CONSTRAINT 	mec26 		CHECK (striket >= 0 and striket <= 360),
CONSTRAINT 	mec27 		CHECK (strike1 >= 0 and strike1 <= 360),
CONSTRAINT 	mec28 		CHECK (strike2 >= 0 and strike2 <= 360),
CONSTRAINT 	mec29 		CHECK (tfd > 0),
CONSTRAINT 	mec30 		CHECK (undip1 >= -180 and undip1 <= 180),
CONSTRAINT 	mec31 		CHECK (undip2 >= -180 and undip2 <= 180),
CONSTRAINT 	mec32 		CHECK (smxx >= -10.0 and smxx <= 10.0),
CONSTRAINT 	mec33 		CHECK (smyy >= -10.0 and smyy <= 10.0),
CONSTRAINT 	mec34 		CHECK (smzz >= -10.0 and smzz <= 10.0),
CONSTRAINT 	mec35 		CHECK (smxy >= -10.0 and smxy <= 10.0),
CONSTRAINT 	mec36 		CHECK (smxz >= -10.0 and smxz <= 10.0),
CONSTRAINT 	mec37 		CHECK (smyz >= -10.0 and smyz <= 10.0),
CONSTRAINT 	mec38 		CHECK (unrake1 >= -180.0 and unrake1 <= 180.0),
CONSTRAINT 	mec39 		CHECK (unrake2 >= -180.0 and unrake2 <= 180.0),
CONSTRAINT 	mec40 		CHECK (unstrike1 >= -180.0 and unstrike1 <= 180.0),
CONSTRAINT 	mec41 		CHECK (unstrike2 >= -180.0 and unstrike2 <= 180.0)
) TABLESPACE PI000;

CREATE TABLE coda (
coid 		NUMBER(15),
commid 		NUMBER(15),
sta 		VARCHAR2(6) 	NOT NULL,
net 		VARCHAR2(8),
auth 		VARCHAR2(15) 	NOT NULL,
subsource 	VARCHAR2(8),
channel 	VARCHAR2(3),
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),
amp 		FLOAT,
rms 		NUMBER(5,3),
durtype 	VARCHAR2(3),
iphase 		VARCHAR2(8),
eramp 		NUMBER(5,3),
units 		VARCHAR2(4) 	NOT NULL,
time1 		NUMBER(10),
amp1 		NUMBER(10),
time2 		NUMBER(10),
amp2 		NUMBER(10),
time3 		NUMBER(10),
amp3 		NUMBER(10),
time4 		NUMBER(10),
amp4 		NUMBER(10),
time5 		NUMBER(10),
amp5 		NUMBER(10),
time6 		NUMBER(10),
amp6 		NUMBER(10),
quality 	NUMBER(2,1),
rflag 		VARCHAR2(2),
lddate 		DATE 		DEFAULT (SYSDATE),
CONSTRAINT 	codakey01 	PRIMARY KEY (coid),
-- CONSTRAINT 	codakey03 	FOREIGN KEY (commid) REFERENCES remark(commid) ON DELETE CASCADE,
CONSTRAINT 	coda01 		CHECK (afix >= 0.0),
CONSTRAINT 	coda02 		CHECK (afree >= 0.0),
CONSTRAINT 	coda03 		CHECK (amp1 > 0),
CONSTRAINT 	coda04 		CHECK (amp2 > 0),
CONSTRAINT 	coda05 		CHECK (amp3 > 0),
CONSTRAINT 	coda06 		CHECK (amp4 > 0),
CONSTRAINT 	coda07 		CHECK (amp5 > 0),
CONSTRAINT 	coda08 		CHECK (amp6 > 0),
CONSTRAINT 	coda09 		CHECK (codatype in ('P','S')),
CONSTRAINT 	coda10 		CHECK (coid > 0),
CONSTRAINT 	coda11 		CHECK (nsample >= 0),
CONSTRAINT 	coda12 		CHECK (rms >= 0.0),
CONSTRAINT 	coda13 		CHECK (time1 > 0),
CONSTRAINT 	coda14 		CHECK (time2 > 0),
CONSTRAINT 	coda15 		CHECK (time3 > 0),
CONSTRAINT 	coda16 		CHECK (time4 > 0),
CONSTRAINT 	coda17 		CHECK (time5 > 0),
CONSTRAINT 	coda18 		CHECK (time6 > 0),
CONSTRAINT 	coda19 		CHECK (rflag in ('a','h','f','A','H','F')) 
) TABLESPACE PI000;

CREATE TABLE assoccom (
magid 		NUMBER(15),
coid 		NUMBER(15),
commid 		NUMBER(15),
auth 		VARCHAR2(15),	NOT NULL,
subsource 	VARCHAR2(8),
weight 		NUMBER(4,3),
in_wgt 		NUMBER(4,3),
rflag 		VARCHAR2(2),
lddate 		DATE 		DEFAULT (SYSDATE),
CONSTRAINT 	assoccomkey01 	PRIMARY KEY (magid, coid),
-- CONSTRAINT 	assoccomkey02 	FOREIGN KEY (magid) REFERENCES netmag(magid) ON DELETE CASCADE,
-- CONSTRAINT 	assoccomkey03 	FOREIGN KEY (coid) REFERENCES coda(coid) ON DELETE CASCADE,
CONSTRAINT 	assoccomkey04 	CHECK (weight >= 0.0 and weight <= 1.0),
CONSTRAINT 	assoccomkey05 	CHECK (in_wgt >= 0.0 and in_wgt <= 1.0),
CONSTRAINT 	assoccomkey06 	CHECK (rflag in ('a','h','f','A','H','F'))
) TABLESPACE PI000;

CREATE TABLE assoccoo (
orid 		NUMBER(15),
coid 		NUMBER(15),
commid 		NUMBER(15),
auth 		VARCHAR2(15),	NOT NULL,
subsource 	VARCHAR2(8),
rflag 		VARCHAR2(2),
lddate 		DATE 		DEFAULT (SYSDATE),
CONSTRAINT 	assoccookey01 	PRIMARY KEY (orid, coid),
-- CONSTRAINT 	assoccookey02 	FOREIGN KEY (orid) REFERENCES origin(orid) ON DELETE CASCADE,
-- CONSTRAINT 	assoccookey03 	FOREIGN KEY (coid) REFERENCES coda(coid) ON DELETE CASCADE,
CONSTRAINT 	assoccookey04 	CHECK (rflag in ('a','h','f','A','H','F'))
) TABLESPACE PI000;

----
---- Add table CONSTRAINTs
---
-- ALTER TABLE event 	ADD CONSTRAINT evkey02 FOREIGN KEY (prefor) 	REFERENCES origin(orid) 	ON DELETE CASCADE;
-- ALTER TABLE event 	ADD CONSTRAINT evkey03 FOREIGN KEY (prefmag) 	REFERENCES netmag(magid) 	ON DELETE CASCADE;
-- ALTER TABLE event 	ADD CONSTRAINT evkey04 FOREIGN KEY (prefmec) 	REFERENCES mec(mecid) 		ON DELETE CASCADE;
-- ALTER TABLE event 	ADD CONSTRAINT evkey05 FOREIGN KEY (commid) 	REFERENCES remark(commid) 	ON DELETE CASCADE;
-- ALTER TABLE origin 	ADD CONSTRAINT orkey03 FOREIGN KEY (prefmag) 	REFERENCES netmag(magid) 	ON DELETE CASCADE;
-- ALTER TABLE origin 	ADD CONSTRAINT orkey04 FOREIGN KEY (prefmec) 	REFERENCES mec(mecid) 		ON DELETE CASCADE;
-- ALTER TABLE origin 	ADD CONSTRAINT orkey05 FOREIGN KEY (commid) 	REFERENCES remark(commid) 	ON DELETE CASCADE;

--
-- Create the sequence numbers
--
DROP SEQUENCE   evseq;
DROP SEQUENCE   orseq;
DROP SEQUENCE   magseq;
DROP SEQUENCE   arseq;
DROP SEQUENCE   ampseq;
DROP SEQUENCE   commseq;
DROP SEQUENCE   mecseq;
DROP SEQUENCE   coseq;
 
CREATE SEQUENCE evseq   INCREMENT BY 1  START WITH 1;
CREATE SEQUENCE orseq   INCREMENT BY 1  START WITH 1;
CREATE SEQUENCE magseq  INCREMENT BY 1  START WITH 1;
CREATE SEQUENCE arseq   INCREMENT BY 1  START WITH 1;
CREATE SEQUENCE ampseq  INCREMENT BY 1  START WITH 1;
CREATE SEQUENCE commseq INCREMENT BY 1  START WITH 1;
CREATE SEQUENCE mecseq  INCREMENT BY 1  START WITH 1;
CREATE SEQUENCE coseq   INCREMENT BY 1  START WITH 1;

COMMIT;
EXIT;

