/*
-- crconstant.sql

-- version 1.1

-- SQL File to create the constants table

-- Programmed by Ron Yurow

-- Last Update:		7  APRIL	1994, VERSION 1.0
			14 MARCH	1995, VERSION 1.1

-- ===========================================================================

The Constants Tables:

The constants tables contain sets of ascii values which are used to implement 
speciallized data types for different columns.  This is accomplished by having 
each column that requires a special data type reference the appropiate 
constant table as a foreign key.  The column will therefore be able to
take on any value that is in the referenced constant table, but no other.  
This is extremely useful where the same data type needs to be implemented on 
serveral different tables.

Every different datatype has its own table.  Each table consists of just one 
column, contianing all the different possible values for that datatype.  This
is done so that it is easier to add new values to a datatype in the future. 
The single column of every constant table is designated that tables primary
key.  This allows columns wich reference that table not to have specify a 
column name.  

In addition to creating each constant table, this script also adds an initial
set of values to each table.  

-- ===========================================================================
*/
-- Create the INSTRUMENT_TYPE constant definition.
/*
-- Table 		INSTRUMENT_TYPE

-- Number of Columns:	1

-- Primary Key:		INSTRUMENT_NAME

-- Description:

The INSTRUMENT_TYPE table constains a list of the different instruments on the
SOHO spacecraft.
*/
CREATE TABLE instrument_type
(
-- Table Constraints Follow 
-- Column Definitions Follow
instrument_name		VARCHAR2(6)	-- Instrument Names	
	CONSTRAINT nn_instrument_name_cn	NOT NULL
	CONSTRAINT pk_instrument_name_cn	PRIMARY KEY
) ;
-- Add constant values to into INSTRUMENT_TYPE table.
INSERT INTO instrument_type 	
	(instrument_name) 	
	VALUES ('CDS') ;
INSERT INTO instrument_type 	
	(instrument_name) 	
	VALUES ('LASCO') ;
INSERT INTO instrument_type 	
	(instrument_name) 	
	VALUES ('SUMER') ;
INSERT INTO instrument_type 	
	(instrument_name) 	
	VALUES ('SWAN') ;
INSERT INTO instrument_type 	
	(instrument_name) 	
	VALUES ('GOLF') ;
INSERT INTO instrument_type 	
	(instrument_name) 	
	VALUES ('VIRGO') ;
INSERT INTO instrument_type 	
	(instrument_name) 	
	VALUES ('MDI') ;
INSERT INTO instrument_type 	
	(instrument_name) 	
	VALUES ('EIT') ;
INSERT INTO instrument_type 	
	(instrument_name) 	
	VALUES ('UVCS') ;
INSERT INTO instrument_type 	
	(instrument_name) 	
	VALUES ('CELIAS') ;
INSERT INTO instrument_type 	
	(instrument_name) 	
	VALUES ('COSTEP') ;
INSERT INTO instrument_type 	
	(instrument_name) 	
	VALUES ('ERNE') ;

-- -----------------------------------------------------------------------------
-- Create the  EVENT_CLASS_TYPE constant definition.
-- -----------------------------------------------------------------------------

/*
-- Table 		EVENT_CLASS_TYPE

-- Number of Columns:	1

-- Primary Key:		EVENT_CLASS_NAME

-- Description:

The EVENT_CLASS_TYPE table constains a list of the different classes of events
which are described in the EVENTS table.
*/
CREATE TABLE event_class_type
(
-- Table Constraints Follow 
-- Column Definitions Follow
event_class_name	VARCHAR2(10)	-- Event Class Names	
	CONSTRAINT nn_event_class_name_cn	NOT NULL
	CONSTRAINT pk_event_class_name_cn	PRIMARY KEY
) ;
-- Add constant values to into EVENT_CLASS_TYPE table.
INSERT INTO event_class_type 	
	(event_class_name) 	
	VALUES ('SOLAR') ;
INSERT INTO event_class_type 	
	(event_class_name) 	
	VALUES ('SPACECRAFT') ;

-- -----------------------------------------------------------------------------
-- Create the EVENT_NAME_TYPE constant definition.
-- -----------------------------------------------------------------------------

/*
-- Table 		EVENT_NAME_TYPE

-- Number of Columns:	1

-- Primary Key:		EVENT_NAME

-- Description:

The EVENT_NAME_TYPE table constains a list of the different solar and 
spacecraft events which might affect the the data being gathered by the 
SOHO spacecraft. 
*/
CREATE TABLE event_name_type
(
-- Table Constraints Follow 
-- Column Definitions Follow
event_name		VARCHAR2(40)	-- Solar and Spacecraft Event Names
	CONSTRAINT nn_event_name_cn		NOT NULL
	CONSTRAINT pk_event_name_cn		PRIMARY KEY
) ;
-- Add constant values to into EVENT_NAME_TYPE table.
INSERT INTO event_name_type 	
	(event_name) 	
	VALUES ('ROLL') ;
INSERT INTO event_name_type 	
	(event_name) 	
	VALUES ('FLARE') ;

-- -----------------------------------------------------------------------------
-- Create the OBSERVATORY_TYPE constant definition.
-- -----------------------------------------------------------------------------

/*
-- Table 		OBSERVATORY_TYPE

-- Number of Columns:	1

-- Primary Key:		OBSERVATORY_NAME

-- Description:

The OBSERVATORY_TYPE table constains a list of the different observatories
and other institutions which might serve as originators of solar events. 
*/
CREATE TABLE observatory_type
(
-- Table Constraints Follow 
-- Column Definitions Follow
observatory_name	VARCHAR2(40)	-- Observatory Names	
	CONSTRAINT nn_observatory_name_cn	NOT NULL
	CONSTRAINT pk_observatory_name_cn	PRIMARY KEY
) ;
-- Add constant values to into OBSERVATORY_TYPE table.
INSERT INTO observatory_type 	
	(observatory_name) 	
	VALUES ('BIG BEAR') ;
INSERT INTO observatory_type 	
	(observatory_name) 	
	VALUES ('KITT PEAK') ;
INSERT INTO observatory_type 	
	(observatory_name) 	
	VALUES ('SMOCC') ;
