REM Tides and Water Levels Benchmarks Data Base
REM Standard tables

SET VERIFY OFF
SET PAGESIZE 100
SET LINESIZE 250

-----------------------------------------------------------------
-- SYNONYMS FOR TABLES
-----------------------------------------------------------------
CREATE SYNONYM STA FOR TWL_STATION;
CREATE SYNONYM DAT FOR DATUM_OF_ELEVATIONS;
CREATE SYNONYM MON FOR STATION_MONUMENT;
CREATE SYNONYM ELE FOR BENCHMARK_ELEVATION;
CREATE SYNONYM NRC FOR NRCAN_ELEVATIONS;
CREATE SYNONYM IMG FOR TWL_STATION_SKETCH;
CREATE SYNONYM ALT FOR ALTERNATE_STATION_NAME;
----------------------------------------------------
CREATE TABLE twl_station               --sta 
(
 account
	VARCHAR2 (16)
,
 station_number			
	NUMBER(6)			
	NOT NULL	
,
 DBE_TIME			
	DATE			
,
 DBU_TIME			
	DATE			
,
 status			
	VARCHAR2 (16)		
	NULL
	Check  (status IN (
		'ACTIVE' , 
		'TEMPORARY' , 
		'DISCONTINUED'  ) )
,
 station_name			
	VARCHAR2(32)		
,
 lat_degrees		
	NUMBER (4)		
,
 lat_minutes		
	NUMBER (4)		
,
 lat_seconds		
	NUMBER (6,2)
,
 lat_hem
	CHAR (1) 
	DEFAULT 'N'		
,
 long_degrees		
	NUMBER (4)		
,
 long_minutes		
	NUMBER (4)		
,
 long_seconds		
	NUMBER (6,2)		
,
 long_hem
	CHAR (1) 
	DEFAULT 'W'		
,
 latitude
	NUMBER (11,6)
,
 longitude
	NUMBER (11,6)
,
 established		
	NUMBER (4)		
,
 province			
	VARCHAR2 (16)		
/*	Check  (province IN (
		'NF' , 
		'PE' , 
		'NS' , 
		'NB' , 
		'QC' , 
		'ON' , 
		'MB' , 
		'SA' , 
		'AB' , 
		'BC' , 
		'NT' , 
		'YT'   ) )*/
,
 region			
	VARCHAR2 (16)		
	Check  (region IN  (
		'ATL' , 
		'NF' , 
		'LAU' , 
		'CA' , 
		'PAC'   ) )
,
 country			
	VARCHAR2 (32)		
,
 ownership			
	VARCHAR2 (32)		
,
 tide_table_volume		
	VARCHAR2 (16)		
	NULL
	Check  (tide_table_volume IN (
		'1' , 
		'2' , 
		'3' , 
		'4' , 
		'5' , 
		'6' , 
		'7'   ) )
,
 reference_station		
	NUMBER (6)		
,
 memo			
	VARCHAR2 (2000)		
,
CONSTRAINT pk_sta 
	PRIMARY KEY (station_number)
	USING Index
		PCTFREE 20
		TABLESPACE data03
		STORAGE (INITIAL 8K NEXT 8K)
);
----------------------------------------------------
CREATE TABLE datum_of_elevations               --dat     
(
 account
	VARCHAR2 (16)
,
 station_number			
	NUMBER (6)		
	NOT NULL	
,
 DBE_TIME			
	DATE			
,
 DBU_TIME			
	DATE			
,
 status			
	VARCHAR2 (16)		
	NULL
	Check  (status IN ('ACTIVE' , 'HISTORICAL')  )
,
 elevation			
	NUMBER (8,3)		
,
 epoch			
	NUMBER (4)		
,
 memo			
	VARCHAR2 (2000)		
,
CONSTRAINT fk_dat_sta 
	FOREIGN KEY (station_number)
	REFERENCES sta(station_number)
	ON DELETE CASCADE
);
/*
For a particular station number there can be several 
entries for datum of elevations although only one can 
be active. NEED A TRIGGER!
*/
----------------------------------------------------
CREATE TABLE station_monument                  --mon
(
 account
	VARCHAR2 (16)
,
 station_number		
	NUMBER (6)		
	NOT NULL	
,
 benchmark_number	
	VARCHAR2 (16)	
	NOT NULL	
,
 DBE_TIME			
	DATE			
,
 DBU_TIME			
	DATE			
,
 status			
	VARCHAR2 (16)	
,
 unique_number		
	VARCHAR2 (8)		
,
 lat_degrees		
	NUMBER (4)		
,
 lat_minutes		
	NUMBER (4)		
,
 lat_seconds		
	NUMBER (6,2)		
,
 lat_hem
	CHAR (1) 
	DEFAULT 'N'		
,
 long_degrees		
	NUMBER (4)		
,
 long_minutes		
	NUMBER (4)		
,
 long_seconds		
	NUMBER (6,2)		
,
 long_hem
	CHAR (1) 
	DEFAULT 'W'		
,
 latitude
	NUMBER (11,6)
,
 longitude
	NUMBER (11,6)
,
 year_established		
	NUMBER (4)		
,
 monument_type		
	VARCHAR2 (32)	
,
 setting			
	VARCHAR2 (32)	
,
 agency			
	VARCHAR2 (32)	
,
 benchmark_description	
	VARCHAR2 (2000)	
,
CONSTRAINT pk_mon 
	PRIMARY KEY (station_number,benchmark_number)
	USING Index
		PCTFREE 20
		TABLESPACE data03
		STORAGE (INITIAL 8K NEXT 8K)
,
CONSTRAINT fk_mon_sta 
	FOREIGN KEY (station_number)
	REFERENCES sta(station_number)
	ON DELETE CASCADE
);
----------------------------------------------------
CREATE TABLE benchmark_elevation 	       --ele
(
 account
	VARCHAR2 (16)
,
 station_number		
	NUMBER (6)		
	NOT NULL	
,
 benchmark_number		
	VARCHAR2 (16)	
	NOT NULL	
,
 DBE_TIME			
	DATE			
,
 DBU_TIME			
	DATE			
,
 status			
	VARCHAR2 (16)	
	Check  (status IN('ACTIVE' , 'HISTORICAL'))
,
 unique_number		
	VARCHAR2 (8)		
,
 datum_name			
	VARCHAR2 (16)	
	Check  (datum_name IN(
		'SD' , 
		'CD' , 
		'IGLD55' , 
		'IGLD85' , 
		'ARBIT' , 
		'ELLIPSOID' , 
		'USLS1903' , 
		'USLS1935'  , 
		'MSL' , 
		'UNKNO'  ))
,
 dn_epoch			
	NUMBER (4)		
,
 elevation			
	NUMBER (8,3)		
,
 elevation_acc		
	NUMBER (4,1)		
,
 hyd_corr			
	NUMBER (4,3)		
,
 hyd_corr_epoch		
	NUMBER (4)		
,
 holding_benchmark
	VARCHAR2(16)			
	Check  (holding_benchmark IN('YES','NO'))
,
CONSTRAINT fk_ele_mon 
	FOREIGN KEY (station_number,benchmark_number)
	REFERENCES mon(station_number,benchmark_number)
	ON DELETE CASCADE
);
/*
Note:  For a unique station number and benchmark number 
there can be multiple entries for each of the datum names 
although only one can be active.  As revised elevations 
come in for a particular datum name the status of the old 
value will change but the record will remain.  This will 
maintain in the database a history of the various elevations 
for a monument. 
THIS NEEDS A TRIGGER!
*/
----------------------------------------------------
CREATE TABLE twl_station_sketch                --img
(
 account
	VARCHAR2 (16)
,
 station_number		
	NUMBER (6)		
	NOT NULL	
,
 DBE_TIME			
	DATE			
,
 DBU_TIME			
	DATE			
,
 status			
	VARCHAR2 (16)	
	Check  (status IN ('ACTIVE','HISTORICAL'))	
,
 graphic /*(GIFF FILE OR LINK TO A FILE)*/
	LONG RAW
,
CONSTRAINT fk_img_sta 
	FOREIGN KEY (station_number)
	REFERENCES sta(station_number)
	ON DELETE CASCADE
);
----------------------------------------------------
CREATE TABLE alternate_station_name            --alt
(
 account
	VARCHAR2 (16)
,
 station_number 		
	NUMBER (6)	
	NOT NULL	
,
 DBE_TIME			
	DATE			
,
 DBU_TIME			
	DATE
,
 alt_name		
	VARCHAR2 (32)	
,
CONSTRAINT fk_alt_sta 
	FOREIGN KEY (station_number)
	REFERENCES sta(station_number)
	ON DELETE CASCADE
);
----------------------------------------------------
CREATE TABLE nrcan_elevations                  --nrc
(
 account
	VARCHAR2 (16)
,
 station_number 		
	NUMBER (6)
	NOT NULL	
,
 benchmark_number		
	VARCHAR2 (16)	
,
 DBE_TIME			
	DATE			
,
 DBU_TIME			
	DATE			
,
 unique_number		
	VARCHAR2 (8)		
,
 cgvd28_elevation
	NUMBER (8,3)
,
 navd88_elevation
	NUMBER (8,3)
,
 xorder
	VARCHAR2 (16)
,
 method
	VARCHAR2 (16)
,
 published
	NUMBER (4)
,
CONSTRAINT fk_nrc_mon 
	FOREIGN KEY (station_number,benchmark_number)
	REFERENCES mon(station_number,benchmark_number)
	ON DELETE CASCADE
);



COMMIT;
SELECT TABLE_NAME FROM USER_TABLES;

SET VERIFY ON
SET PAGESIZE 60