Subject: Seed Database

Create Table DCC.SEED_Station_Info (
	Station		varchar2(5)
				constraint nn_SSI_Station	not null
				constraint ck_SST_Station
					check (station = upper(station)),
	Local_GPN	varchar2(100)	/* Local Geopolitical Place Name/City*/
	SubRegion_GPN	varchar2(100)	/* Area Name */
	Regional_GPN	varchar2(100)	/* State/Province/Oblast */
	SubNational_GPN	varchar2(100)	/* Sub-National area name */
	National_GPN	varchar2(100)	/* Name of Country */
	Country_Code	char(2)		/* Standard Country Code */
	Flinn_Engdall	varchar2(30)	/* Name of region */
	World_Region	varchar2(30)	/* Name of region */
	Observ_Name	varchar2(100)
	Observ_Address	varchar2(2000)	/* Address (multi-line) */
	Observ_Phones	varchar2(200)	/* Numb <desc> \n next... */
	Observ_Fax	varchar2(200)	/* ... */
	Observ_Telex	varchar2(30)
	Observ_Email	varchar2(2000)	/* email <who> \n next... */
	Observ_Contacts	varchar2(2000)	/* name <info> \n next... */
	Director_Name	varchar2(100)
	Director_Address varchar2(2000)	
	Director_Phone	varchar2(100)
	Director_Fax	varchar2(100)
	Director_Telex	varchar2(100)
	Director_Email	varchar2(300)	/* Multi-line */
	Parent_Orgs	varchar2(100)
	Network_Afil	varchar2(2000)	/* Multi-line */
	Geology		varchar2(2000)
	Modems		varchar2(2000)	/* numb <modm_type,drm_proto> \n ... */
	Network		varchar2(2000)	/* address <access_proto> \n ... */
) Tablespace Maint_1;


Create Table DCC.SEED_Station (
	Network		varchar2(2)
				constraint nn_SST_Network	not null
				constraint fk_SST_Network
					references DCC.Networks(network),
	Station		varchar2(5)
				constraint nn_SST_Station	not null
				constraint ck_SST_Station
					check (station = upper(station)),
	Beginning 	date
				constraint nn_SST_Beginning	not null,
	Ending		date,
	Modified	date,
		/* Default location for station - location of sensors */
	Latitude	number(7,5)
				constraint nn_SST_Latitude	not null
				constraint ck_SST_Latitude
					check (Latitude between -90 and 90),
	Lat_Prec	number(1)	/* Precision of Latitude
					   24.22 is 2,40.342 is 3,0 unknown */
				constraint ck_SST_Lat_Prec
					check (Lat_Prec between 0 and 5),
	Longitude	number(8,5)
				constraint nn_SST_Longitude	not null
				constraint ck_SST_Longitude
					check (Longitude between -180 and 180),
	Long_Prec	number(1)	/* Precision of Longitude */
				constraint ck_SST_Long_Prec
					check (Lat_Prec between 0 and 5),
	Coord_Type	varchar2(11)	/* Map Source coords from */
					/* Abbreviation class CT */
				constraint fk_SST_Coord_Type
					references DCC.SEED_Abbreviation(id),
	Coord_Map	varchar2(100)	/* Map citation/source */
	Elevation	number(4)
				constraint nn_SST_Elevation	not null,
	Elev_Prec	number(1)
				constraint ck_SST_Elev_Prec
					check (Elev_Prec between -3 and 1),
	Elev_Type	varchar2(11)	/* Source of elevation map */
					/* Abbreviation class CT */
				constraint fk_SST_Elev_Type
					references DCC.SEED_Abbreviation(id),
	Elev_Map	varchar2(100)	/* Map citation/source */
		/* */
	Net_Id		varchar2(11)	/* Network owner */
					/* Abbreviation class NO */
				constraint nn_SST_Net_Id	not null
				constraint fk_SST_Net_Id
					references DCC.SEED_Abbreviation(id),
	Stage_Format	varchar2(11)	/* Default staging format */
				constraint nn_SST_Stage_Format	not null
				constraint fk_SST_Stage_Format
					references DCC.Stage_Formats(format),
	Successor_Net	varchar2(2)
	Successor_Station varchar2(5)
	Word_Ord_32	number(4)
	Word_Ord_16	number(2)
	Clock_Type	varchar2(11)	/* Abbreviation class CL */
				constraint fk_SST_Clock_Type
					references DCC.SEED_Abbreviation(id),
	Station_Type	varchar2(11)	/* Abbreviation class ST */
				constraint fk_SST_Station_Type
					references DCC.SEED_Abbreviation(id),
	Software_Revs	varchar2(2000)	/* dev <rev> \n next... */
	Vault_Cond	varchar2(2000)	/* Current vault conditions */
	DescType	char(4)		/* Type of data in description field */
				constraint nn_SST_DescType	not null
				constraint ck_SST_DescType
					check (DescType in ('TEXT')),
	Description	long,		/* Description record for data */
    constraint pk_SST__Primary_Key
		Primary Key (Network, Station, Beginning),
    constraint unq_SST__Primary_Key
		Unique (Network, Station, Beginning),
    constraint fk_SST__Successor
		Foreign key (successor_net, successor_station)
			references DCC.SEED_Station(network,station)
)  Tablespace Maint_1;


Create Table DCC.SEED_Channel (
	Network		varchar2(2)
				constraint nn_SCN_Network	not null,
	Station		varchar2(5)
				constraint nn_SCN_Station	not null,
	Location	varchar2(2)
				constraint ck_SCN_Location
					check (Location = upper(Location)),
	Channel		varchar2(3)
				constraint nn_SCN_Channel	not null
				constraint ck_SCN_Channel
					check (Channel = upper(Channel)),
	Beginning	date
				constraint nn_SCN_Beginning	not null,
	Ending		date,
	Modified	date,
	Instrument	varchar2(11)	/* Abbreviation class IT */
				constraint nn_SCN_Instrument	not null,
				constraint fk_SCN_Instrument
					references DCC.SEED_Abbreviation(id),
	ChanComment	varchar2(31),
	Signal_Response	varchar2(11)
				constraint nn_SCN_Signal_Response not null,
				constraint fk_SCN_Signal_Response
					references DCC.SEED_Unit(id),
	Coord_Set	number(1)	/* 0 - Use Coordinates from here 
					   1 - Use Coordinated from station 
					   2 - Station-depth */
				constraint ck_SCN_Coord_Set
					check (Coord_Set between 0 and 2),
		/* Channel location (of seis) if Coord_Set is 0 */
	Latitude	number(7,5)
				constraint nn_SCN_Latitude	not null
				constraint ck_SCN_Latitude
					check (Latitude between -90 and 90),
	Lat_Prec	number(1)	/* Precision of Latitude
					   24.22 is 2,40.342 is 3,0 unknown */
				constraint ck_SCN_Lat_Prec
					check (Lat_Prec between 0 and 5),
	Longitude	number(8,5)
				constraint nn_SCN_Longitude	not null
				constraint ck_SCN_Longitude
					check (Longitude between -180 and 180),
	Long_Prec	number(1)	/* Precision of Longitude */
				constraint ck_SCN_Long_Prec
					check (Lat_Prec between 0 and 5),
	Coord_Type	varchar2(11)	/* Map Source coords from */
					/* Abbreviation class CT */
				constraint fk_SCN_Coord_Type
					references DCC.SEED_Abbreviation(id),
	Coord_Map	varchar2(100)	/* Map citation/source */
	Elevation	number(5,1)
				constraint nn_SCN_Elevation	not null,
	Elev_Prec	number(1)
				constraint ck_SCN_Elev_Prec
					check (Elev_Prec between -3 and 1),
	Elev_Type	varchar2(11)	/* Source of elevation map */
					/* Abbreviation class CT */
				constraint fk_SCN_Elev_Type
					references DCC.SEED_Abbreviation(id),
	Elev_Map	varchar2(100)	/* Map citation/source */
		/* */
	Local_Depth	number(4,1)
				constraint nn_SCN_Local_Depth	not null,
	Azimuth		number(4,1)
				constraint nn_SCN_Azimuth	not null
				constraint ck_SCN_Azimuth
					check (Azimuth between 0 and 360),
	Dip		number(3,1)
				constraint nn_SCN_Dip		not null
				constraint ck_SCN_Dip
					check (Dip between -90 and 90),
	SEED_Format	varchar2(11)
				constraint nn_SCN_SEED_Format
				constraint fk_SCN_SEED_Format
					references DCC.SEED_Format(id),
	Exponent 	number(2)
				constraint nn_SCN_Exponent	not null
				constraint ck_SCN_Exponent
					check (Exponent between 8 and 12),
	Sample_Rate	float
				constraint nn_SCN_Sample_Rate	not null,
	Clock_Drift	float
				constraint nn_SCN_Clock_Drift	not null,
	Flags		varchar2(27)
				constraint ck_SCN_Flags
					check (Flags = upper(Flags)),
	Derived_Location varchar2(2),
	Derived_Channel	varchar2(5),
	Calibration_Location varchar2(2),
	Calibration_Channel varchar2(5),
	Clock_Type 	varchar2(11)	/* Abbreviation class CL */
				constraint fk_SCN_Clock_Type
					references dcc.seed_abbreviation(id),
	Digitizer_Type	varchar2(11)	/* Abbreviation class DT */
				constraint fk_SCN_Digitizer_Type
					references dcc.seed_abbreviation(id),
	DescType	char(4)		/* Type of data in description field */
				constraint nn_SCN_DescType	not null
				constraint ck_SCN_DescType
					check (DescType in ('TEXT')),
	Description	long,		/* Description record for data */
   constraint pk_SCN__Primary_Key
		Primary Key (Networ, Station, Location, Channel, Beginning),
   constraint fk_SCN__Station_Key
		Foreign Key (network, station) 
			references dcc.seed_station(network,station),
   constraint fk_SCN__Derived_Key
		foreign key (network, station, 
			derived_location, derived_channel)
		references
			dcc.seed_channel( network, station, channel, location),
   constraint fk_SCN__Cal_Key
		foreign key (network, station, 
			calibration_location, calibration_channel)
		references
			dcc.seed_channel( network, station, channel, location)
)  TABLESPACE maint_1;


Create Table DCC.SEED_Pole_Zero (
	Network		varchar(2)
				constraint nn_SPZ_Network	not null,
	Station		varchar(5)
				constraint nn_SPZ_Station	not null,
	Location	varchar(2),
	Channel		varchar(3)
				constraint nn_SPZ_Channel	not null,
	Beginning	date
				constraint nn_SPZ_Beginning	not null,
	Ending		date,
	Modified	date,
	Stage_Seq	number(2)
				constraint nn_SPZ_Stage_Seq	not null,
	PZKey		varchar2(40)
				constraint nn_SPZ_PZKey		not null
				constraint fk_SPZ_PZKey
					references dcc.SEED_PZ(Key),
	DescType	char(4)		/* Type of data in description field */
				constraint nn_SPZ_DescType	not null
				constraint ck_SPZ_DescType
					check (DescType in ('TEXT')),
	Description	long,		/* Description record for data */
    constraint pk_SPZ__Primary_Key
		Primary Key (Network, Station, Location, Channel,
			Beginning, Stage_Seq),
    constraint fk_SPZ__Main_Key
		Foreign Key (Network, Station, Location, Channel)
			references
				dcc.SEED_Channel(Network, Station,
					Location, Channel),
)	tablespace maint_1;


Create Table DCC.SEED_Coefficient (
	Network		varchar(2)
				constraint nn_SDC_Network	not null,
	Station		varchar(5)
				constraint nn_SDC_Station	not null,
	Location	varchar(2),
	Channel		varchar(3)
				constraint nn_SDC_Channel	not null,
	Beginning	date
				constraint nn_SDC_Beginning	not null,
	Ending		date,
	Modified	date,
	Stage_Seq	number(2)
				constraint nn_SDC_Stage_Seq	not null,
	DCKey 		varchar2(40)
				constraint nn_SDC_DCKey		not null
				constraint fk_SDC_DCKey
					references dcc.Seed_DC(Key),
	DescType	char(4)		/* Type of data in description field */
				constraint nn_SDC_DescType	not null
				constraint ck_SDC_DescType
					check (DescType in ('TEXT')),
	Description	long,		/* Description record for data */
    constraint pk_SDC__Primary_Key
		Primary Key (Network, Station, Location, Channel,
			Beginning, Stage_Seq),
    constraint fk_SDC__Main_Key
		Foreign Key (Network, Station, Location, Channel)
			references
				dcc.SEED_Channel(Network, Station,
					Location, Channel),
)	tablespace maint_1;


Create Table DCC.SEED_Decimation (
	Network		varchar(2)
				constraint nn_SDM_Network	not null,
	Station		varchar(5)
				constraint nn_SDM_Station	not null,
	Location	varchar(2),
	Channel		varchar(3)
				constraint nn_SDM_Channel	not null,
	Beginning	date
				constraint nn_SDM_Beginning	not null,
	Ending		date,
	Modified	date,
	Stage_Seq	number(2)
				constraint nn_SDM_Stage_Seq	not null,
	DMKey		varchar(40)
				constraint nn_SDM_DMKey		not null
				constraint fk_SDM_DMKey
					references DCC.SEED_DM(Key),
	DescType	char(4)		/* Type of data in description field */
				constraint nn_SDM_DescType	not null
				constraint ck_SDM_DescType
					check (DescType in ('TEXT')),
	Description	long,		/* Description record for data */
    constraint pk_SDM__Primary_Key
		Primary Key (Network, Station, Location, Channel,
			Beginning, Stage_Seq),
    constraint fk_SDM__Main_Key
		Foreign Key (Network, Station, Location, Channel)
			references
				dcc.SEED_Channel(Network, Station,
					Location, Channel),
)	tablespace maint_1;


Create Table DCC.SEED_Sensitivity (
	Network		varchar(2)
				constraint nn_SSN_Network	not null,
	Station		varchar(5)
				constraint nn_SSN_Station	not null,
	Location	varchar(2),
	Channel		varchar(3)
				constraint nn_SSN_Channel	not null,
	Beginning	date
				constraint nn_SSN_Beginning	not null,
	Ending		date,
	Modified	date,
	Stage_Seq	number(2)
				constraint nn_SSN_Stage_Seq	not null,
	Sensitivity	float
				constraint nn_SSN_Sensitivity	not null,
	Frequency	float
				constraint nn_SSN_Frequency	not null,
	DescType	char(4)		/* Type of data in description field */
				constraint nn_SSN_DescType	not null
				constraint ck_SSN_DescType
					check (DescType in ('TEXT')),
	Description	long,		/* Description record for data */
    constraint pk_SSN__Primary_Key
		Primary Key (Network, Station, Location, Channel,
			Beginning, Stage_Seq),
    constraint fk_SSN__Main_Key
		Foreign Key (Network, Station, Location, Channel)
			references
				dcc.SEED_Channel(Network, Station,
					Location, Channel),
)	tablespace maint_1;



Create Table DCC.SEED_Station_Comment (
	Network		varchar(2)
				constraint nn_SSC_Network	not null,
	Station		varchar(5)
				constraint nn_SSC_Station	not null,
	Beginning	date
				constraint nn_SSC_Beginning	not null,
	Ending		date,
	Modified	date,
	Comment_ID	number
				constraint nn_SSC_Comment_ID	not null
				constraint fk_SSC_Comment_ID
				    references DCC.SEED_Comment_Definition(ID),
	Value		varchar2(20),
	DescType	char(4)		/* Type of data in description field */
				constraint nn_SSC_DescType	not null
				constraint ck_SSC_DescType
					check (DescType in ('TEXT')),
	Description	long,		/* Description record for data */
    constraint pk_SSC__Primary_Key
		Primary Key (Network, Station, Beginning),
    constraint fk_SSC__Main_Key
		Foreign Key (Network, Station)
			references
				dcc.SEED_Station(Network, Station)
)	tablespace maint_1;


Create Table DCC.SEED_Channel_Comment (
	Network		varchar(2)
				constraint nn_SCC_Network	not null,
	Station		varchar(5)
				constraint nn_SCC_Station	not null,
	Location	varchar(2),
	Channel		varchar(3)
				constraint nn_SCC_Channel	not null,
	Beginning	date
				constraint nn_SCC_Beginning	not null,
	Ending		date,
	Modified	date,
	Comment_ID	number
				constraint nn_SCC_Comment_ID	not null
				constraint fk_SCC_Comment_ID
				    references DCC.SEED_Comment_Definition(ID),
	Value		varchar2(20),
	DescType	char(4)		/* Type of data in description field */
				constraint nn_SCC_DescType	not null
				constraint ck_SCC_DescType
					check (DescType in ('TEXT')),
	Description	long,		/* Description record for data */
    constraint pk_SCC__Primary_Key
		Primary Key (Network, Station, Location, Channel,
			Beginning, Stage_Seq),
    constraint fk_SCC__Main_Key
		Foreign Key (Network, Station, Location, Channel)
			references
				dcc.SEED_Channel(Network, Station,
					Location, Channel),
)	tablespace maint_1;




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


