/*
-- crmain.sql


-- SQL File to create the MAIN table

-- Programmed by Ron Yurow

-- Table 		MAIN

-- Number of Columns:	20

-- Primary Key:		INSTRUME and PROG_NUM

-- Foriegn Key:		INSTRUME 	(From INSTRUMENT_TYPE table)
			CMP_NO		(From CAMPAIGN table)
			OBJECT		(From OBJECTS table)

-- Last Update:		7  APRIL 1994, VERSION 1.0
			9  MARCH 1995, VERSION 1.1
			14 MARCH 1995, VERSION 1.2
			16 MARCH 1995, VERSION 1.3
			21 MARCH 1995, VERSION 1.31
			23 MARCH 1995, VERSION 1.4
			3  APRIL 1996, VERSION 1.5
			29 JULY	 1996, VERSION 1.6

-- Description:

The MAIN catalog contains information about observing programs and targets.  
Each entry in this catalog represents a unique observing program and target 
combination.  In other words, if an observing program covered several similar
targets, then each target would rate a seperate entry in the catalog.

The primary key fields for this catalog are INSTRUME and PROG_NUM.

Two keywords which are very similar are PROG_NUM and PROG_ID.  Both are numbers
associated with catalog entries.  The difference is that PROG_NUM is required 
to be unique for each record with the same value of INSTRUME, since the 
combination of the two make up the primary key.  On the other hand, PROG_ID is
used to associate individual records together as being part of the same 
observing program.  In particular, an observing program that is not contiguous
in time, for instance an observation made at the same time each day, or once 
every solar rotation, could be identified by giving all the records from this
observing program the same value for PROG_ID.

Some columns may not be applicable toall instruments.  In particular, the 
fields XCEN, YCEN, ANGLE, IXWIDTH, and IYWIDTH may not pertain to the SWAN 
instrument, since it uses a different coordinate system.  

Please note that the column names have been selected with imaging experiments
in mind.  This is primarily because the keywords for the other kind of 
experiments have not been defined yet.  When they are defined, they will be 
added to the table as well.
*/
CREATE TABLE main
(
-- Table Constraints Follow
CONSTRAINT pk_instrume_prog_num_mn PRIMARY KEY (instrume, prog_num), 
--CONSTRAINT fk_cmp_no_cmp_name_mn REFERENCES campaign (cmp_no, cmp_name),
-- Column Definitions Follow
instrume	VARCHAR2(6)	--Instrument Name	
	CONSTRAINT nn_instrume_mn	NOT NULL
	CONSTRAINT mem_instrume_mn	REFERENCES instrument_type,
prog_num	NUMBER (12)	-- Program Number
	CONSTRAINT nn_prog_num_mn	NOT NULL,
prog_id		NUMBER (12)	-- Program ID
	CONSTRAINT nn_prog_id_mn	NOT NULL,
obs_prog	VARCHAR2 (80)	-- Observation Program
	CONSTRAINT nn_obs_prog_mn	NOT NULL,
sci_obj		VARCHAR2 (80)	-- Science Objective
	CONSTRAINT nn_sci_obj_mn	NOT NULL,
sci_spec	VARCHAR2 (160),	-- Description of Science Objective
cmp_name        VARCHAR2 (120),  -- Campaign Name
--	CONSTRAINT fk_cmp_name_mn	REFERENCES campaign (cmp_name),
cmp_no		NUMBER (12)	-- Campaign Number
	CONSTRAINT fk_cmp_no_mn		REFERENCES campaign (cmp_no),
object		VARCHAR2 (3)	-- Abbreviation of object type being observed
--	CONSTRAINT nn_object_mn		NOT NULL
	CONSTRAINT mem_object_mn	REFERENCES objects,
obj_id		VARCHAR2 (12),	-- Unique ID of Object to be observed
--	CONSTRAINT nn_object_id_mn	NOT NULL,
date_obs	CHAR (23)	-- Starting Date of Observation Program
	CONSTRAINT nn_date_obs_mn	NOT NULL
	CONSTRAINT df_date_obs_mn	
		CHECK (date_obs LIKE '____/__/__ __:__:__.___'),
date_end	CHAR (23)	-- Ending Date of Observation Program
	CONSTRAINT nn_date_end_mn	NOT NULL	
	CONSTRAINT df_date_end_mn	
		CHECK (date_end LIKE '____/__/__ __:__:__.___'),
xcen		FLOAT,		-- Center of Instrument Field of View
ycen		FLOAT,		-- Center of Instrument Field of View
angle		FLOAT,		-- Orientation of Instrument 
ixwidth		FLOAT,		-- Size of Instrument Field of View
iywidth		FLOAT,		-- Size of Instrument Field of View
seq_from	NUMBER (12)	-- First Observing Sequence Number
	CONSTRAINT nn_seq_from_mn	NOT NULL,
seq_to		NUMBER (12)	-- Last Observing Sequence Number
	CONSTRAINT nn_seq_to_mn		NOT NULL,
comments	CHAR (1)	-- Comments Present
	CONSTRAINT nn_comments_mn	NOT NULL
	CONSTRAINT mem_comments_mn	CHECK (comments IN ('Y', 'N')),
date_mod	CHAR (23)	-- Date of Last Modification
	CONSTRAINT nn_date_mod_mn	NOT NULL
--	CONSTRAINT df_date_mod_mn	
--		CHECK (date_mod LIKE '____/__/__ __:__:__.___')
) ;
/*
Notes:

The constraints on date format and time format are very weak.  They should
be replaced by stronger constraints in the future.

The column 'prog_num' may need the unique constraint.
*/




