Subject: IRIS seed header table schemas

drop table b30;
create table b30
(
	id          number(4),
	family		number(3)	 constraint nn_b30_family not null,
	numkeys		number(2)    constraint nn_b30_numkeys not null,
	name		varchar2(50) constraint nn_b30_name null,
	key			varchar2(2000),
	key2		varchar2(2000),
	constraint pk_b30
		primary key ( id )
		using index tablespace ind_dictionary
		storage( initial 40K next 40K minextents 1 maxextents unlimited 
			pctincrease 0 )
)
storage (initial 4M next 4M minextents 1 maxextents unlimited pctincrease 0)
tablespace dictionary
pctfree 5 pctused 75
;
grant select on B30 to public;
drop table b31;
create table b31
( 
	id      number(4),
	class   char(1)      constraint nn_b31_class not null,
	text    varchar2(70) constraint nn_b31_text  not null,
	b34_id  number(3) ,
	constraint pk_b31
		primary key ( id )
		using index tablespace ind_dictionary
		storage( initial 2M next 2M minextents 1 maxextents unlimited
			pctincrease 0 )
)
storage (initial 4M next 4M minextents 1 maxextents unlimited pctincrease 0)
tablespace dictionary
;
drop table b31_temp;
create table b31_temp
( 
	id          number(4)    constraint nn_b31_temp_id    not null,
	class       char(1)      constraint nn_b31_temp_class not null,
	text        varchar2(70) constraint nn_b31_temp_text  not null,
	b34_id      number(3),
	pid         number
)
storage (initial 1M next 1M minextents 1 maxextents unlimited pctincrease 0)
tablespace dictionary
;
drop view new_b31;
create view new_b31 ( class, text, b34_id ) as
	select distinct class, text, b34_id from b31_temp
	minus
	select class, text, b34_id from b31;
grant select on B31 to public;
grant select on B31_temp to public;
drop table b32;
create table b32
(
	id          number,
	name        varchar2(70) constraint nn_b32_name not null,
	date_pub    varchar2(70) constraint nn_b32_date_pub not null,
	publisher   varchar2(50) constraint nn_b32_publisher not null,
	constraint pk_b32
		primary key ( id )
		using index tablespace ind_dictionary
		storage ( initial 2M next 2M minextents 1 maxextents unlimited
		pctincrease 0 )
)
storage (initial 1M next 1M minextents 1 maxextents unlimited pctincrease 0)
tablespace dictionary
pctfree 5 pctused 75
;
grant select on B32 to public ;

alter table station drop constraint fk_b33;
drop table b33;
create table b33
(
	id          number,
	text        varchar2(50) constraint nn_b33_text not null,
	constraint pk_b33
		primary key ( id )
		using index tablespace ind_dictionary
		storage( initial 2M next 2M minextents 1 maxextents unlimited
			pctincrease 0 )
)
storage (initial 4M next 4M minextents 1 maxextents unlimited pctincrease 0)
tablespace dictionary
pctfree 5 pctused 75
;
grant select on B33 to public;
drop table b34;
create table b34
(
	id			number,
	name		varchar2(20) constraint nn_b34_name not null,
	text		varchar2(50),
	constraint pk_b34
		primary key ( id )
		using index tablespace ind_dictionary
		storage( initial 2M next 2M minextents 1 maxextents unlimited
			pctincrease 0 )
)
storage (initial 4M next 4M minextents 1 maxextents unlimited pctincrease 0)
tablespace dictionary
pctfree 5 pctused 75
;

grant select on B34 to public;
drop table channel_comment;
create table channel_comment
(
	network     varchar2(2)   constraint nn_channel_comments_network not null,
	station     varchar2(5)   constraint nn_channel_comments_station not null,
	location    varchar2(2)   constraint nn_channel_comments_location not null,
	channel     varchar2(3)   constraint nn_channel_comments_channel not null,
	starttime   date          constraint nn_channel_comments_sdate   not null,
	endtime     date          constraint nn_channel_comments_edate   not null,
	b31_id		number(4)     constraint nn_channel_comments_lookup  not null,
	value       number(6),
	constraint fk_ccomment_cd
		foreign key (b31_id) references b31(id)
)
tablespace channel
storage (initial 11M next 11M minextents 1 maxextents unlimited pctincrease 0)
pctfree 5 pctused 75;
--
create index ind_channel_comment
on channel_comment( station, channel, network, starttime, location )
tablespace ind_channel
storage (initial 5M next 5M minextents 1 maxextents unlimited 
		pctincrease 0)
pctfree 5;
grant select on channel_comment to public;
drop table channel;
create table channel
(
	network 		varchar2(2)	constraint nn_channel_network       not null,
	station 		varchar2(5)	constraint nn_channel_station       not null,
	channel 		varchar2(3)	constraint nn_channel_channel       not null,
	latitude		number(8,6)	constraint nn_channel_latitude      not null,
	longitude		number(9,6)	constraint nn_channel_longitude     not null,
	elevation		number(7,1) constraint nn_channel_elevation     not null,
	depth			number(5,1) constraint nn_channel_depth         not null,
	azimuth			number(5,1)	constraint nn_channel_azimuth       not null,
	dip				number(5,1)	constraint nn_channel_dip           not null,
	record_length	number(2)	constraint nn_channel_record_length not null,
	sample_rate		number		constraint nn_channel_sample_rate   not null,
	max_drift		number		constraint nn_channel_max_drift     not null,
	starttime  		date		constraint nn_channel_sdate         not null,
	endtime 		date		constraint nn_channel_edate         not null,
	lookup_dfd		number(4)	constraint nn_channel_lookup_dfd    not null,
	lookup_ga 		number(3)	constraint nn_channel_lookup_ga     not null,
	location		varchar2(2) default '  ',
	subchannel 		varchar2(4),
	comments		varchar2(30),
	flags			varchar2(26),
	lookup_units_in number(3),
	lookup_units_out number(3),
	update_flag		varchar2(1),
	constraint pk_channel primary key 
		( station, channel, starttime, endtime, network )
		using index tablespace ind_channel
		storage(initial 5M next 5M minextents 1 maxextents unlimited 
				pctincrease 0),
	constraint fk_channel_units_in
		foreign key (lookup_units_in) references b34(id),
	constraint fk_channel_units_out
		foreign key (lookup_units_out) references b34(id),
	constraint fk_channel_lookup_dfd
		foreign key (lookup_dfd) references b30(id),
	constraint fk_channel_lookup_ga
		foreign key (lookup_ga) references b33(id)
)
tablespace channel
storage (initial 11M next 11M minextents 1 maxextents unlimited pctincrease 0)
pctfree 5 pctused 75 
;
--
drop view chantimes;
create view chantimes ( network, station, channel, starttime , endtime  )
as select network, station, channel, starttime , endtime  from channel;
--
grant select on channel to public;
drop table chanresp;
create table chanresp
(
	network 		varchar2(2),
	station 		varchar2(5),
	channel 		varchar2(3),
	starttime  		date,
	endtime 		date,
	id              number,
	stage			number		constraint nn_chanresp_stage   not null,
	seq				number		constraint nn_chanresp_seq     not null,
	location        varchar2(2) default '  ',
	constraint pk_chanresp primary key
		(station, channel, starttime , endtime , network, id, seq)
		using index tablespace ind_channel
		storage(initial 25M next 25M minextents 1 maxextents unlimited 
				pctincrease 0)
)
tablespace channel
storage (initial 55M next 11M minextents 1 maxextents unlimited 
		pctincrease 0)
pctfree 5 pctused 75
;
grant select on chanresp to public;
drop table lookups;
create table lookups
(
	btype      number(3),
	vol_id     number(4),
	db_id      number,
	pid        number,
	constraint pk_lookups
		primary key ( vol_id, btype, db_id, pid )
		using index tablespace ind_dictionary
		storage( initial 1M next 1M minextents 1 maxextents unlimited
			pctincrease 0 )
)
tablespace dictionary
storage (initial 1M next 1M minextents 1 maxextents unlimited pctincrease 0)
;
grant select on lookups to public;
alter table repeat drop constraint fk_repeat_id;
alter table raw_response drop constraint fk_raw_response_id;
drop table response;
create table response
( 
	id              number(9),                /* unique id for this response */
	blen   			number(4)	              /* length of the seed blockette*/
                         constraint nn_response_blen not null,     
	btype           number(3)                 /* SEED blockette type (53,58) */
                         constraint nn_response_btype not null,     
	lookup_units_in number(3),                /* units ID for signal in      */
	lookup_units_out number(3),               /* units ID for signal out     */
	transfer_type   char,
	repeat1			number,                   /* 1st repeating field (pole)  */
	repeat2			number,                   /* 2nd repeating field (zero)  */
	n_factor        number,
	n_freq          number,
	sample_rate     number,
	d_factor        number(5),
	d_offset        number(5),
	est_delay       number,
	correction      number,
	sens_gain       number,
	frequency       number,
	name            varchar(25),
	symmetry        char,
	constraint pk_response primary key ( id )
		using index tablespace ind_response
		storage(initial 20M next 20M minextents 1 maxextents unlimited
			pctincrease 0),
	constraint fk_response_units_in
		foreign key (lookup_units_in) references b34(id),
	constraint fk_response_units_out
		foreign key (lookup_units_out) references b34(id)
)
tablespace response
storage (initial 30M next 30M minextents 1 maxextents unlimited 
		pctincrease 0)
pctfree 5 pctused 75
;
grant select on response to public;
commit;
-----------------------------------------------------------------------------
drop table repeat;
create table repeat
( 
	btype           number(3),            /* SEED blockette type (53,58) */
	id              number(9),            /* id of response record       */
	seq				number(9),            /* sequence number             */
	rtype           char,                 /* P=pole, Z=zero              */
	value1			number,
	value2			number,
	value3			number,
	value4			number,
	value5			number,
	above_calib		varchar2(25),
	constraint pk_response_repeat primary key ( id, seq, btype )
		using index tablespace ind_response
		storage(initial 10M next 10M minextents 1 maxextents unlimited
			pctincrease 0),
	constraint fk_repeat_id 
		foreign key (id) references response(id) on delete cascade
)
tablespace response
storage (initial 10M next 10M minextents 1 maxextents unlimited 
		pctincrease 0)
pctfree 5 pctused 75;
-----------------------------------------------------------------------------
drop table raw_response;
create table raw_response
( 
	id               number(9),               /* unique id for this response */
	blen   			 number(4)	              /* length of the seed blockette*/
                         constraint nn_raw_response_blen not null,
	btype            number(3)                /* SEED blockette type (53,58) */
                         constraint nn_raw_response_btype not null,
	lookup_units_in  number(3),               /* units ID for signal in      */
	lookup_units_out number(3),               /* units ID for signal out     */
	blockette		 long                     /* entire SEED blockette       */
                         constraint nn_raw_response_blockette not null,
	constraint pk_raw_response primary key ( id )
		using index tablespace ind_response
		storage(initial 10M next 10M minextents 1 maxextents unlimited
			pctincrease 0),
	constraint fk_raw_response_id 
		foreign key (id) references response(id) on delete cascade,
	constraint fk_raw_units_in
		foreign key (lookup_units_in) references b34(id),
	constraint fk_raw_units_out
		foreign key (lookup_units_out) references b34(id)
)
tablespace response
storage (initial 40M next 40M minextents 1 maxextents unlimited pctincrease 0)
pctfree 5 pctused 75
;
grant select on response to public;
grant select on repeat to public;
grant select on raw_response to public;
drop table station_comment;
create table station_comment
(
	network 		varchar2(2) constraint nn_station_comment_network not null,
	station 		varchar2(5) constraint nn_station_comment_station not null,
	starttime 		date        constraint nn_station_comment_sdate   not null,
	endtime 		date        constraint nn_station_comment_edate   not null,
	b31_id			number(4)   constraint nn_station_comment_lookup  not null,
	value			number(6),
	constraint fk_scomment_cd
		foreign key (b31_id) references b31(id)
)
tablespace station
storage (initial 10M next 10M minextents 1 maxextents unlimited 
		pctincrease 0)
pctfree 5 pctused 75;
--
create index ind_station_comment
on station_comment( station, network, starttime )
tablespace ind_station
storage (initial 5M next 5M minextents 1 maxextents unlimited 
                pctincrease 0)
pctfree 5;
grant select on station_comment to public;
drop sequence seq_b30;
create sequence seq_b30 increment by 1 minvalue 1;
drop sequence seq_b31;
create sequence seq_b31 increment by 1 minvalue 1;
drop sequence seq_b32;
create sequence seq_b32 increment by 1 minvalue 1;
drop sequence seq_b33;
create sequence seq_b33 increment by 1 minvalue 1;
drop sequence seq_b34;
create sequence seq_b34 increment by 1 minvalue 1;
drop sequence seq_resp;
create sequence seq_resp increment by 1 minvalue 1;
drop table station;
create table station
(
	-- NOT NULL not needed for network, station, starttime , endtime  cause
	-- part of primary key
	network 		varchar2(2),
	station 		varchar2(5),
	latitude        number(10,6)  constraint nn_station_latitude not null,
	longitude		number(11,6)  constraint nn_station_longitude not null,
	elevation 		number(7,1)   constraint nn_station_elevation not null,
	site			varchar2(60)  constraint nn_station_site      not null,
	long_word		number(4)     constraint nn_station_long_word not null,
	word			number(2)     constraint nn_station_word      not null,
	starttime 		date,
	endtime 		date,
	lookup_ga		number(3)     constraint fk_b33 references b33(id),
	update_flag		varchar2(1),
	constraint pk_station primary key (station, starttime , endtime , network)
		using index tablespace ind_station
				storage(initial 10M next 10M minextents 1 maxextents 1 
				pctincrease 0),
	constraint fk_station_lookup_ga
		foreign key (lookup_ga) references b33(id)
)
storage (initial 10M next 10M minextents 1 maxextents unlimited 
		pctincrease 0)
tablespace station
pctfree 5 pctused 75
;
grant select on station to public;


----- End Included Message -----


