/*
-- crexperiment.sql

-- version 1.4

-- SQL File to create the EXPERIMENT

-- Programmed by Ron Yurow

-- Table 		EXPERIMENT

-- Number of Columns:	21

-- Primary Key:		INSTRUME and SEQ_NUM

-- Last Update:		7  APRIL 	1994, VERSION 1.0
			9  MARCH 	1995, VERSION 1.1
			23 MARCH	1995, VERSION 1.2
			01 AUGUST       1995, VERSION 1.3
			29 JULY		1996, VERSION 1.4

-- 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 columns of this 
table are INSTRUME and SEQ_NUM.

Associated with the EXPERIMENT catalog is a WAVELENGTHS catalog.  This contains
a listing of the wavelengths that are associated with each entry in EXPERIMENT.
For each entry in EXPERIMENT there will be one or more entries in WAVELENGTHS.

The EXPERIMENT catalog is also associated with a LINELIST catalog through 
WAVE_ID field. The EXPERIMENT records may or may not contain valid WAVE_ID. For
each WAVE_ID in the LINELIST catalog, there will be one or more EXPERIMENT 
records.
*/
CREATE TABLE experiment
(
-- Table Constraints Follow
CONSTRAINT pk_instrume_seq_num_ex PRIMARY KEY (instrume, seq_num), 
-- Column Definitions Follow
instrume	VARCHAR2(6)	--Instrument Name	
	CONSTRAINT nn_instrume_ex	NOT NULL
	CONSTRAINT mem_instrume_ex	REFERENCES instrument_type,
prog_num	NUMBER (12)	-- Program Number
	CONSTRAINT nn_prog_num_ex	NOT NULL,
seq_num		NUMBER (12)	-- Sequence Number
	CONSTRAINT nn_seq_num_ex	NOT NULL,
filename	VARCHAR2 (256)	-- Name of File Containing the Experiment
	CONSTRAINT nn_filename_ex	NOT NULL,
filepath	VARCHAR2 (256),	-- Name of Filepath 
obs_seq		VARCHAR2 (40)	-- Name of Observing Sequence
	CONSTRAINT nn_obs_seq_ex	NOT NULL,
obs_mode	VARCHAR2 (40)	-- Observing Mode
	CONSTRAINT nn_obs_mode_ex	NOT NULL,
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 '____/__/__ __:__:__.___'),
count		NUMBER (12)	-- Number of Observing Sequences in Experiment
	CONSTRAINT nn_count_ex		NOT NULL,
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
seqvalid	CHAR (1)	-- Data Valid
	CONSTRAINT nn_seqvalid_ex	NOT NULL
	CONSTRAINT mem_seqvalid_ex	CHECK (seqvalid IN ('Y', 'N')),
detector	VARCHAR2 (40)	-- Detector Used for Observation
	CONSTRAINT nn_detector_ex	NOT NULL,
exptime		FLOAT,		-- Exposure Time
expcount	NUMBER (12),	-- Number of Exposures in Observing Sequence
wave_id		NUMBER (12),	-- Wavelength ID Number
comments	CHAR (1)	-- Comments Present
	CONSTRAINT nn_comments_ex	NOT NULL
	CONSTRAINT mem_comments_ex	CHECK (comments IN ('Y', 'N')),
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.
*/
