/*
-- crmain.sql


-- SQL File to create the MAIN table for CDS database

-- Programmed by Snehavadan Macwan

-- Table 		MAIN

-- Number of Columns:	25

-- Primary Key:		PROG_NUM

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

-- Last Update:		4  APRIL 1996, VERSION 1.0

-- 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 field for this catalog is 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_prog_num_mn PRIMARY KEY (prog_num), 
-- Column Definitions Follow
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,
prog_ind	NUMBER (12)	-- Program index
	CONSTRAINT nn_prog_ind_mn	NOT NULL,
study_id	NUMBER (12)	-- Study ID number
	CONSTRAINT nn_study_id_mn	NOT NULL,
studyvar	NUMBER (12)	-- Study variation index
	CONSTRAINT nn_studyvar_mn	NOT NULL,
obs_prog	VARCHAR2 (8)	-- Observation Program
	CONSTRAINT nn_obs_prog_mn	NOT NULL,
detector	CHAR (1)	-- Detector used for observation
	CONSTRAINT nn_detector_mn	NOT NULL
        CONSTRAINT mem_detector_mn      REFERENCES detector_type,
sci_obj		VARCHAR2 (50)	-- Science Objective
	CONSTRAINT nn_sci_obj_mn	NOT NULL,
sci_spec	VARCHAR2 (50),	-- Description of Science Objective
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 '____/__/__ __:__:__.___'),
obt_time	CHAR (23)	-- Onboard time of beginning of Observation 
	CONSTRAINT nn_obt_time_mn	NOT NULL	
	CONSTRAINT df_obt_time_mn	
		CHECK (obt_time LIKE '____/__/__ __:__:__.___'),
obt_end		CHAR (23)	-- Onboard time of end of Observation 
	CONSTRAINT nn_obt_end_mn	NOT NULL	
	CONSTRAINT df_obt_end_mn	
		CHECK (obt_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.
*/




