/*
-- crexperiment.sql

-- version 1.0

-- SQL File to create the EXPERIMENT

-- Programmed by Snehavadan Macwan

-- Table 		EXPERIMENT

-- Number of Columns:	48

-- Primary Key:		SEQ_NUM

-- Last Update:		5  APRIL 	1996, VERSION 1.0

-- Description:

The EXPERIMENT table contains information about the individual observing 
sequences that make up an observation program.  Each record in this table
represents a specific data object (file).  If a file contains data from 
different observing modes, then each observing mode will have an unique record 
in this table.  For every record in the MAIN table, there will be one or more
associated records in the EXPERIMENT table.  The primary key column of this 
table is SEQ_NUM.

*/
CREATE TABLE experiment
(
-- Table Constraints Follow
CONSTRAINT pk_seq_num_ex PRIMARY KEY (seq_num), 
-- Column Definitions Follow
seq_num		NUMBER (12)	-- Sequence Number
	CONSTRAINT nn_seq_num_ex	NOT NULL,
prog_num	NUMBER (12)	-- Program Number
	CONSTRAINT nn_prog_num_ex	NOT NULL,
obs_seq		VARCHAR2 (12)	-- Name of Observing Sequence
	CONSTRAINT nn_obs_seq_ex	NOT NULL,
count		NUMBER (12)	-- Number of Repeated Observing Sequences 
	CONSTRAINT nn_count_ex		NOT NULL,
seq_ind		NUMBER (12)	-- Sequence Index
	CONSTRAINT nn_seq_ind_ex	NOT NULL,
ras_id		NUMBER (12)	-- Raster ID Number
	CONSTRAINT nn_ras_id_ex		NOT NULL,
ras_var		NUMBER (12)	-- Raster Variation Index
	CONSTRAINT nn_ras_var_ex	NOT NULL,
exptime		FLOAT,		-- Exposure Time
obs_mode	VARCHAR2 (20)	-- Observing Mode
	CONSTRAINT nn_obs_mode_ex	NOT NULL,
dw_id		NUMBER (12)	-- Data Window ID Number
	CONSTRAINT nn_dw_id_ex		NOT NULL,
obt_time        CHAR (23)       -- Onboard time of beginning of Observation 
        CONSTRAINT nn_obt_time_ex       NOT NULL
        CONSTRAINT df_obt_time_ex
                CHECK (obt_time LIKE '____/__/__ __:__:__.___'),
obt_end         CHAR (23)       -- Onboard time of end of Observation 
        CONSTRAINT nn_obt_end_ex        NOT NULL
        CONSTRAINT df_obt_end_ex
                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
ins_x0		FLOAT,		-- Instrument Origin Pointing (legs) in X
ins_y0		FLOAT,		-- Instrument Origin Pointing (legs) in Y
ins_roll	FLOAT,		-- Instrument Roll Angle
sc_x0		FLOAT,		-- Spacecraft X pointing
sc_y0		FLOAT,		-- Spacecraft Y pointing
sc_roll		FLOAT,		-- Spacecraft Roll
wavemin		FLOAT,		-- Minimum Wavelength Observed
wavemax		FLOAT,		-- Maximum Wavelength Observed
datasrc		CHAR (1)	-- Data Source (Quicklook OR Final Distribution)
	CONSTRAINT nn_datasrc_ex        NOT NULL
	CONSTRAINT mem_datasrc_ex	CHECK (datasrc IN ('Q', 'F')),
date_obs	CHAR (23)	-- Starting Date of Experiment
	CONSTRAINT nn_date_obs_ex	NOT NULL
	CONSTRAINT df_date_obs_ex	
		CHECK (date_obs LIKE '____/__/__ __:__:__.___'),
date_end	CHAR (23)	-- Ending Date of Experiment
	CONSTRAINT nn_date_end_ex	NOT NULL	
	CONSTRAINT df_date_end_ex	
		CHECK (date_end LIKE '____/__/__ __:__:__.___'),
tracking	CHAR (1)	-- Feature Tracking to be used
	CONSTRAINT nn_tracking_ex	NOT NULL
	CONSTRAINT mem_tracking_ex	CHECK (tracking IN ('T', 'F')),
ser_id		NUMBER (12)	-- On-board Sequence Load ID
	CONSTRAINT nn_ser_id_ex 	NOT NULL,
opslbits	VARCHAR2 (4)	-- OPS-L Status Bits At Start of Raster
	CONSTRAINT nn_opslbits_ex	NOT NULL,
opsrbits	VARCHAR2 (4)	-- OPS-R Status Bits At Start of Raster
	CONSTRAINT nn_opsrbits_ex	NOT NULL,
slit_pos	NUMBER (12)	-- Slit Position At Start Of Raster
	CONSTRAINT nn_slit_pos_ex	NOT NULL,
mir_pos		NUMBER (12)	-- Mirror Position At Start Of Raster
	CONSTRAINT nn_mir_pos_ex	NOT NULL,
ev_enab		CHAR (1)	-- Event Recognition
	CONSTRAINT nn_ev_enab_ex	NOT NULL
	CONSTRAINT mem_ev_enab_ex	CHECK (ev_enab IN ('T', 'F')),
comp_err	CHAR (1)	-- Data Compression Error
	CONSTRAINT nn_comp_err_ex	NOT NULL
	CONSTRAINT mem_comp_err_ex	CHECK (comp_err IN ('T', 'F')),
vds_pmcp	NUMBER (12)	-- VDS MCP Programmable Voltage Setting
	CONSTRAINT nn_vds_pmcp_ex	NOT NULL,
vds_mode	NUMBER (12)	-- VDS Readout Mode
	CONSTRAINT nn_vds_mode_ex	NOT NULL,
vds_ori		CHAR (1)	-- VDS Telemetry Data Orientation
	CONSTRAINT nn_vds_ori_ex	NOT NULL
	CONSTRAINT mem_vds_ori_ex	CHECK (vds_ori IN ('T', 'F')),
vds_acc		CHAR (1)	-- VDS Accumulation Mode
	CONSTRAINT nn_vds_acc_ex	NOT NULL
	CONSTRAINT mem_vds_acc_ex	CHECK (vds_acc IN ('T', 'F')),
seqvalid	CHAR (1)	-- Data Valid
	CONSTRAINT nn_seqvalid_ex	NOT NULL
	CONSTRAINT mem_seqvalid_ex	CHECK (seqvalid IN ('Y', 'N')),
comments	CHAR (1)	-- Comments Present
	CONSTRAINT nn_comments_ex	NOT NULL
	CONSTRAINT mem_comments_ex	CHECK (comments IN ('Y', 'N')),
gset_id		NUMBER (12)	-- GSET ID Number
	CONSTRAINT nn_gset_id_ex	NOT NULL,
detector	CHAR (1)	-- Detector (either G OR N) 
	CONSTRAINT nn_detector_ex	NOT NULL
	CONSTRAINT mem_detector_ex	REFERENCES detector_type,
zone_id		NUMBER (12)	-- Pointing Zone ID
	CONSTRAINT nn_zone_id_ex	NOT NULL,
slit_num	NUMBER (12)	-- Slit Number
	CONSTRAINT nn_slit_num_ex	NOT NULL,
filename	VARCHAR2 (15)	-- FITS Filename
	CONSTRAINT nn_filename_ex	NOT NULL,
date_mod	CHAR (23)	-- Date of Last Modification
	CONSTRAINT nn_date_mod_ex	NOT NULL
	CONSTRAINT df_date_mod_ex	
		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.
*/
