The Data Warehouse Definitions

The data warehouse Definitions

To create the Data Warehouse schema the Data Definition Language (DDL) scripts included below will generate the sequences, tables and constraints respectively in the first three sections. The description of the elements in the in final fourth section.

1. Sequences

PROMPT Creating sequences for schema Warehouse

CREATE SEQUENCE SEQCOMPONENT_ID

MINVALUE 50

INCREMENT BY 1

ORDER NOCACHE;

CREATE SEQUENCE SEQ_DATA_ID

MINVALUE 19000

INCREMENT BY 1

ORDER NOCACHE;

CREATE SEQUENCE SEQ_DEPLOYMENT_ID

MINVALUE 13000

INCREMENT BY 1

ORDER NOCACHE;

CREATE SEQUENCE SEQ_TEMP_TABLE

MINVALUE 0

INCREMENT BY 1

ORDER NOCACHE;

2. Warehouse Tables

PROMPT Creating tables for schema warehouse

PROMPT Creating table ADCP_SHIP_DATA

CREATE TABLE ADCP_SHIP_DATA

(

DATA_ID NUMBER(12) NOT NULL,

CALCULATED_DEPTH NUMBER(5,1) NOT NULL,

BIN_NUMBER NUMBER(4),

U_CORRECTED_VALUE NUMBER(4,2),

V_CORRECTED_VALUE NUMBER(4,2),

AV_QC NUMBER(5,2),

BIN_ATTENDANCE_PERCENTAGE NUMBER(3),

CONSTRAINT ADCP_SHIP_DATA_PK

PRIMARY KEY ( DATA_ID, CALCULATED_DEPTH )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating table ADCP_SHIP_DEPLOYMENT

CREATE TABLE ADCP_SHIP_DEPLOYMENT

(

DEPLOYMENT_ID NUMBER(8) NOT NULL,

DEPTH_CELL_LENGTH NUMBER(5),

BLANK_AFTER_TRANSMIT NUMBER(5),

NOMINAL_ADCP_DEPTH NUMBER(6),

NUMBER_DEPTH_CELLS NUMBER(4),

PINGS_PER_ENSEMBLE NUMBER(3),

TIME_BETWEEN_PINGS NUMBER(5),

MAGNETIC_DECLINATION NUMBER(4,2),

NOMINAL_SALINITY NUMBER(5,2),

ENSEMBLE_PERIOD NUMBER(10),

PROFILING_MODE NUMBER(2),

METHOD_CODE VARCHAR2(16),

CONFIGURATION_FILE VARCHAR2(40),

LOCATION_COMMENT VARCHAR2(80),

TIME_COMMENT VARCHAR2(80),

CONSTRAINT ADCP_SHIP_DEPL_PK

PRIMARY KEY ( DEPLOYMENT_ID )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating table ADCP_SHIP_HEADER

CREATE TABLE ADCP_SHIP_HEADER

(

DATA_ID NUMBER(12) NOT NULL,

DEPLOYMENT_ID NUMBER(8) NOT NULL,

START_TIME DATE,

MEAN_LAT NUMBER(7,5),

MEAN_LON NUMBER(8,5),

VELOCITY_REFERENCE VARCHAR2(10),

HEADING_TYPE VARCHAR2(10),

AVERAGING_PERIOD NUMBER(5),

BIN_LENGTH NUMBER(3),

PING_LENGTH NUMBER(3),

DELAY NUMBER(3),

PERCENT_COVER NUMBER(3),

U_SHIP NUMBER(4,2),

V_SHIP NUMBER(4,2),

PERCENT_BT_COVER NUMBER(3),

BT_DEPTH NUMBER(5),

MEAN_BT_ERROR_VELOCITY NUMBER(3),

MEAN_PERCENT_GOOD_BT_PINGS NUMBER(3),

PROJECT_ID NUMBER(7) NOT NULL,

MARLIN_ID NUMBER(7) NOT NULL,

SECURITY_CODE NUMBER(1),

LICENCE_NOTE VARCHAR(40),

CONSTRAINT ADCP_SHIP_HEADER_PK

PRIMARY KEY ( DATA_ID )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating table CAAB_GROUP

CREATE TABLE CAAB_GROUP

(

GROUP_NAME VARCHAR2(50) NOT NULL,

REGULAR_START_CODE NUMBER(8),

REGULAR_END_CODE NUMBER(8),

IRREGULAR_START_CODE NUMBER(8),

IRREGULAR_END_CODE NUMBER(8),

ASSIGNED_FAMILY_START NUMBER(3),

ASSIGNED_FAMILY_END NUMBER(3),

CONSTRAINT CAAB_GROUP_PK

PRIMARY KEY ( GROUP_NAME )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating table CAAB_SPECIES_LIST

CREATE TABLE CAAB_SPECIES_LIST

(

SPECIES_CODE NUMBER(8) NOT NULL,

SCIENTIFIC_NAME VARCHAR2(255),

COMMON_NAME VARCHAR2(255),

ASSIGNED_FAMILY_CODE NUMBER(3),

IS_CURRENT VARCHAR2(1) NOT NULL,

SUPERCEDED_BY NUMBER(8),

CONSTRAINT CAAB_SPECIES_LIST_PK

PRIMARY KEY ( SPECIES_CODE )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating table CATCH_ADD_ATTRIBUTE

CREATE TABLE CATCH_ADD_ATTRIBUTE

(

DATA_ID NUMBER(12) NOT NULL,

PARAMETER_CODE VARCHAR2(20) NOT NULL,

VALUE VARCHAR(20) NOT NULL,

CONSTRAINT CATCH_ADD_ATTRIBUTE_PK

PRIMARY KEY ( DATA_ID, PARAMETER_CODE )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating table CATCH_COMPOSITION_DATA

CREATE TABLE CATCH_COMPOSITION_DATA

(

DATA_ID NUMBER(12) NOT NULL,

QUANTITY_MEASURED_CODE VARCHAR2(16) NOT NULL,

CAAB_SPECIES_CODE NUMBER(8) NOT NULL,

ORIGINAL_SPECIES_CODE NUMBER(8),

SPECIES_ID_QC_FLAG NUMBER(3) NOT NULL,

CATCH_COUNT NUMBER(5),

CATCH_COUNT_QC_FLAG NUMBER(3),

CATCH_WEIGHT NUMBER(7,3),

CATCH_WEIGHT_QC_FLAG NUMBER(3),

SPECIMEN_DATA_FLAG VARCHAR2(1),

COMMENTS VARCHAR2(255),

CONSTRAINT CATCH_COMPOSITION_DATA_PK

PRIMARY KEY ( DATA_ID, CAAB_SPECIES_CODE, QUANTITY_MEASURED_CODE )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating table CATCH_GEOMETRY

CREATE TABLE CATCH_GEOMETRY

(

DATA_ID NUMBER(12) NOT NULL,

SURVEY_NAME VARCHAR2(12),

OPERATION_NO NUMBER(4),

SECURITY_CODE NUMBER(1),

LICENCE_NOTE VARCHAR(40),

MI_PRINX NUMBER NOT NULL,

GEOLOC MDSYS.SDO_GEOMETRY,

CONSTRAINT CATCH_GEOMETRY_PK

PRIMARY KEY ( DATA_ID )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating table CATCH_HEADER

CREATE TABLE CATCH_HEADER

(

DATA_ID NUMBER(12) NOT NULL,

DEPLOYMENT_ID NUMBER(8) NOT NULL,

COMPONENT_ID NUMBER(6) NOT NULL,

COMPONENT_NO NUMBER(2),

SURVEY_NAME VARCHAR2(12) NOT NULL,

START_TIME DATE,

END_TIME DATE,

UTC_OFFSET NUMBER(3,1),

START_LAT NUMBER(7,5),

START_LON NUMBER(8,5),

END_LAT NUMBER(7,5),

END_LON NUMBER(8,5),

START_DEPTH NUMBER(5),

END_DEPTH NUMBER(5),

MIN_DEPTH NUMBER(5),

MAX_DEPTH NUMBER(5),

TOTAL_CATCH_WT NUMBER(7,3),

FRACTION_SAMPLED NUMBER(4,1),

SAMPLED_CATCH_WT NUMBER(7,3),

SUBSAMPLED_CATCH_WT NUMBER(7,3),

DISCARDED_CATCH_WT NUMBER(7,3),

ADD_ATTRIBUTE_FLAG VARCHAR2(1),

CATCH_METHOD VARCHAR(16),

CATCH_EFFORT NUMBER(8),

COMMENTS VARCHAR2(255),

PROJECT_ID NUMBER(7) NOT NULL,

GLOBAL_PROJECT_ID NUMBER(7),

MARLIN_ID NUMBER(7) NOT NULL,

SECURITY_CODE NUMBER(1),

RELEASE_DATE DATE,

LICENCE_NOTE VARCHAR(40),

CONSTRAINT CATCH_HEADER_PK

PRIMARY KEY ( DATA_ID )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K),

CONSTRAINT CATCH_HEADER_UK1

UNIQUE ( DEPLOYMENT_ID, COMPONENT_ID )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating index CATCH_HEADER_POS_INDX

CREATE INDEX CATCH_HEADER_POS_INDX ON CATCH_HEADER

( START_LAT ASC, START_LON ASC )

PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K);

PROMPT Creating index CATCH_HEADER_TIME_INDX

CREATE INDEX CATCH_HEADER_TIME_INDX ON CATCH_HEADER

( START_TIME ASC )

PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K);

PROMPT Creating table CATCH_MEASUREMENT_DATA

CREATE TABLE CATCH_MEASUREMENT_DATA

(

DATA_ID NUMBER(12) NOT NULL,

CAAB_SPECIES_CODE NUMBER(8) NOT NULL,

ORIGINAL_SPECIES_CODE NUMBER(8),

SEX_CODE VARCHAR2(10) NOT NULL,

MEDIAN_LENGTH NUMBER(7,2) NOT NULL,

INTERVAL_WIDTH NUMBER(2,2) NOT NULL,

SPECIMEN_COUNT NUMBER(5) NOT NULL,

COUNT_QC_FLAG NUMBER(3) NOT NULL,

LENGTH_METHOD_CODE VARCHAR2(2),

COMMENTS VARCHAR2(255),

CONSTRAINT CATCH_MEASUREMENT_DATA_PK

PRIMARY KEY ( DATA_ID, CAAB_SPECIES_CODE, SEX_CODE, MEDIAN_LENGTH )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating table CATCH_SPECIMEN_DATA

CREATE TABLE CATCH_SPECIMEN_DATA

(

DATA_ID NUMBER(12) NOT NULL,

CAAB_SPECIES_CODE NUMBER(8) NOT NULL,

ORIGINAL_SPECIES_CODE NUMBER(8),

QUANTITY_MEASURED_CODE VARCHAR2(1) NOT NULL,

SPECIMEN_NUMBER NUMBER(3) NOT NULL,

SEX_CODE VARCHAR2(10),

LENGTH NUMBER(7,2),

LENGTH_METHOD_CODE VARCHAR2(2),

LENGTH_QC_FLAG NUMBER(3),

WEIGHT NUMBER(7,3),

WEIGHT_METHOD_CODE VARCHAR2(2),

WEIGHT_QC_FLAG NUMBER(3),

MATURATION_STAGE_CODE VARCHAR2(6),

MAT_STAGE_QC_FLAG NUMBER(3),

GONAD_WEIGHT NUMBER(8,4),

GONAD_WT_QC_FLAG NUMBER(3),

STOMACH_FULLNESS_CODE VARCHAR2(6),

STOMACH_FULL_QC_FLAG NUMBER(3),

COMMENTS VARCHAR2(255),

CONSTRAINT CATCH_SPECIMEN_DATA_PK

PRIMARY KEY ( DATA_ID, CAAB_SPECIES_CODE, SPECIMEN_NUMBER )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating table CODE_LIST

CREATE TABLE CODE_LIST

(

CODE_NAME VARCHAR2(16) NOT NULL,

CODE_VALUE VARCHAR2(16) NOT NULL,

CODE_VALUE_DESCRIPTION VARCHAR2(80),

CONSTRAINT CODE_LIST_PK

PRIMARY KEY ( CODE_NAME, CODE_VALUE )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

-- CONSTRAINT CODE_LIST_UK

-- UNIQUE ( CODE_NAME )

-- USING INDEX PCTFREE 5

-- TABLESPACE INDX

-- STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating table CODE_NAME_LIST

CREATE TABLE CODE_NAME_LIST

(

CODE_NAME VARCHAR2(16) NOT NULL,

CODE_NAME_DESCRIPTION VARCHAR2(80),

CONSTRAINT CODE_NAME_LIST_PK

PRIMARY KEY ( CODE_NAME )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating table COMPONENT

CREATE TABLE COMPONENT

(

COMPONENT_ID NUMBER(6) NOT NULL,

SUB_COMPONENT_OF NUMBER(6),

COMMISSION_DATE DATE,

DECOMMISSION_DATE DATE,

MANUFACTURER VARCHAR2(16),

MODEL_NO VARCHAR2(16),

SERIAL_NO VARCHAR2(16),

PARAMETER_CODE VARCHAR2(20) NOT NULL,

CSIRO_TRAWL_GEAR_CODE NUMBER(4),

NAME VARCHAR2(80),

DESCRIPTION VARCHAR2(255),

CONSTRAINT COMPONENT_PK

PRIMARY KEY ( COMPONENT_ID )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating table CTD_DATA

CREATE TABLE CTD_DATA

(

DATA_ID NUMBER(12) NOT NULL,

PRESSURE NUMBER(5,1) NOT NULL,

PARAMETER_CODE VARCHAR2(20) NOT NULL,

VALUE NUMBER(16,6),

QC_FLAG NUMBER(3),

CONSTRAINT CTD_DATA_PK

PRIMARY KEY ( DATA_ID, PRESSURE, PARAMETER_CODE )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85 STORAGE (INITIAL 512K NEXT 512K PCTINCREASE 0);

PROMPT Creating table CTD_GEOMETRY

CREATE TABLE CTD_GEOMETRY

(

DATA_ID NUMBER(12) NOT NULL,

SURVEY_NAME VARCHAR2(12) NOT NULL,

STATION_NO NUMBER(6),

MI_PRINX NUMBER NOT NULL,

GEOLOC MDSYS.SDO_GEOMETRY,

SECURITY_CODE NUMBER(1),

LICENCE_NOTE VARCHAR(40),

CONSTRAINT CTD_GEOMETRY_PK

PRIMARY KEY ( DATA_ID )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating table CTD_HEADER

CREATE TABLE CTD_HEADER

(

DATA_ID NUMBER(12) NOT NULL,

DEPLOYMENT_ID NUMBER(8) NOT NULL,

CAST_NO NUMBER(4),

SURVEY_NAME VARCHAR2(12) NOT NULL,

START_TIME DATE NOT NULL,

END_TIME DATE NOT NULL,

MIN_DEPTH NUMBER(5,1),

MAX_DEPTH NUMBER(5,1),

BOTTOM_DEPTH NUMBER(5,1),

BOTTOM_TIME DATE,

BOTTOM_LAT NUMBER(7,5),

BOTTOM_LON NUMBER(8,5),

END_LAT NUMBER(7,5),

END_LON NUMBER(8,5),

START_LAT NUMBER(7,5),

START_LON NUMBER(8,5),

PROJECT_ID NUMBER(7) NOT NULL,

GLOBAL_PROJECT_ID NUMBER(7),

MARLIN_ID NUMBER(7) NOT NULL,

SECURITY_CODE NUMBER(1),

RELEASE_DATE DATE,

LICENCE_NOTE VARCHAR(40),

COMMENTS VARCHAR2(255),

CONSTRAINT CTD_HEADER_PK

PRIMARY KEY ( DATA_ID )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K),

CONSTRAINT CTD_HEADER_UK

UNIQUE ( SURVEY_NAME, DEPLOYMENT_ID, CAST_NO )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating index CTD_HEADER_POS_INDX

CREATE INDEX CTD_HEADER_POS_INDX ON CTD_HEADER

( START_LAT ASC, START_LON ASC )

PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K);

PROMPT Creating index CTD_HEADER_TIME_INDX

CREATE INDEX CTD_HEADER_TIME_INDX ON CTD_HEADER

( START_TIME ASC ) PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K);

PROMPT Creating table DATA_SUMMARY

CREATE TABLE DATA_SUMMARY

(

DATA_ID NUMBER(12) NOT NULL,

DATA_STREAM_TYPE VARCHAR2(16) NOT NULL,

SURVEY_NAME VARCHAR2(12),

DEPLOYMENT_ID NUMBER(8),

PROJECT_ID NUMBER(7) NOT NULL,

START_TIME DATE,

END_TIME DATE,

MIN_DEPTH NUMBER(5,1),

MAX_DEPTH NUMBER(5,1),

MIN_LAT NUMBER(7,5),

MAX_LAT NUMBER(7,5),

MIN_LON NUMBER(8,5),

MAX_LON NUMBER(8,5),

DESCRIPTION VARCHAR2(200),

CONSTRAINT DATA_SUMMARY_PK

PRIMARY KEY ( DATA_ID )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating index DATA_SUMMARY_MAXPOS_INDX

CREATE INDEX DATA_SUMMARY_MAXPOS_INDX ON

DATA_SUMMARY ( MAX_LAT ASC, MAX_LON ASC )

PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K);

PROMPT Creating index DATA_SUMMARY_MINPOS_INDX

CREATE INDEX DATA_SUMMARY_MINPOS_INDX ON

DATA_SUMMARY ( MIN_LAT ASC, MIN_LON ASC )

PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K);

PROMPT Creating index DATA_SUMM_HEADER_TIME_INDX

CREATE INDEX DATA_SUMM_HEADER_TIME_INDX ON

DATA_SUMMARY ( START_TIME ASC )

PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K);

PROMPT Creating table DEPLOYMENT

CREATE TABLE DEPLOYMENT

(

DEPLOYMENT_ID NUMBER(8) NOT NULL,

DEPLOYMENT_TYPE VARCHAR2(16) NOT NULL,

SOURCE_ID NUMBER(7),

PROJECT_ID NUMBER(7),

SURVEY_NAME VARCHAR2(12) NOT NULL,

START_TIME DATE,

END_TIME DATE,

OPERATION_NO NUMBER(4),

MAX_DEPTH NUMBER(5,1),

STATION_NO NUMBER(10),

START_LAT NUMBER(7,5),

START_LON NUMBER(8,5),

END_LAT NUMBER(7,5),

END_LON NUMBER(8,5),

LOCATION_SELECTION VARCHAR2(16),

DESCRIPTION VARCHAR2(80),

COMMENTS VARCHAR2(255),

CONSTRAINT DEPLOYMENT_PK

PRIMARY KEY ( DEPLOYMENT_ID )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating index DEPLOYMENT_POS_INDX

CREATE INDEX DEPLOYMENT_POS_INDX ON DEPLOYMENT

( START_LAT ASC, START_LON ASC )

PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K);

PROMPT Creating index DEPLOYMENT_TIME_INDX

CREATE INDEX DEPLOYMENT_TIME_INDX ON DEPLOYMENT

( START_TIME ASC )

PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K);

PROMPT Creating table DEPLOYMENT_COMPONENT

CREATE TABLE DEPLOYMENT_COMPONENT

(

DEPLOYMENT_ID NUMBER(8) NOT NULL,

COMPONENT_NO NUMBER(2) NOT NULL,

COMPONENT_ID NUMBER(6) NOT NULL,

CONSTRAINT DEPLOYMENT_COMPONENT_PK

PRIMARY KEY ( DEPLOYMENT_ID, COMPONENT_ID, COMPONENT_NO )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85 ;

PROMPT Creating table DEPLOYMENT_OBSERVATION

CREATE TABLE DEPLOYMENT_OBSERVATION

(

DEPLOYMENT_ID NUMBER(8) NOT NULL,

PARAMETER_CODE VARCHAR2(20) NOT NULL,

VALUE VARCHAR2(20) NOT NULL,

QC_FLAG NUMBER(3) NOT NULL,

CONSTRAINT DEPLOYMENT_OBSERVATION_PK

PRIMARY KEY ( DEPLOYMENT_ID, PARAMETER_CODE )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating table GIS_DATA

CREATE TABLE GIS_DATA

(

DATA_ID NUMBER(12) NOT NULL,

SOURCE_FILE_NAME VARCHAR(100),

SOURCE_FILE_DESCRIPTION VARCHAR(256),

CONSTRAINT GIS_DATA_PK

PRIMARY KEY ( DATA_ID, SOURCE_FILE_NAME )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating table GIS_HEADER

CREATE TABLE GIS_HEADER

(

DATA_ID NUMBER(12) NOT NULL,

GIS_TYPE VARCHAR(10) NOT NULL,

PARAMETER_CODE VARCHAR(20),

DOWNLOAD_FILE VARCHAR(100),

LAYER_DESCRIPTION VARCHAR(200),

LAYER_LABEL VARCHAR(30),

PROJECT_ID NUMBER(7) NOT NULL,

GLOBAL_PROJECT_ID NUMBER(7),

MARLIN_ID NUMBER(7) NOT NULL,

SECURITY_CODE NUMBER(1),

RELEASE_DATE DATE,

LICENCE_NOTE VARCHAR(40),

CONSTRAINT GIS_HEADER_PK

PRIMARY KEY ( DATA_ID )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating table GLOBAL_PROJECT

CREATE TABLE GLOBAL_PROJECT

(

GLOBAL_PROJECT_ID NUMBER(7) NOT NULL,

GLOBAL_PROJECT_NAME VARCHAR2(20),

DESCRIPTION VARCHAR2(120),

CONSTRAINT PROJECT_PK

PRIMARY KEY ( GLOBAL_PROJECT_ID )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating table HYDROCHEM_ANALYTICAL_METHOD

CREATE TABLE HYDROCHEM_ANALYTICAL_METHOD

(

METHOD_CODE VARCHAR2(16) NOT NULL,

APPLICATION_DATE DATE NOT NULL,

DESCRIPTION VARCHAR2(80),

REFERENCE VARCHAR2(80),

CONSTRAINT HYDRCHEM_METHOD_PK

PRIMARY KEY ( METHOD_CODE, APPLICATION_DATE )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating table HYDROLOGY_ANALYSIS

CREATE TABLE HYDROLOGY_ANALYSIS

(

DATA_ID NUMBER(12) NOT NULL,

ANALYTE_CODE VARCHAR2(16) NOT NULL,

METHOD_CODE VARCHAR2(16) NOT NULL,

APPLICATION_DATE DATE NOT NULL,

CONSTRAINT HYDROALYSIS_METHOD_PK

PRIMARY KEY ( DATA_ID, ANALYTE_CODE, METHOD_CODE, APPLICATION_DATE )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating table HYDROLOGY_DATA

CREATE TABLE HYDROLOGY_DATA

(

DATA_ID NUMBER(12) NOT NULL,

PRESSURE NUMBER(5,1) NOT NULL,

PARAMETER_CODE VARCHAR2(20) NOT NULL,

BOTTLE_NUMBER NUMBER(6) NOT NULL,

VALUE NUMBER(16,6),

QC_FLAG NUMBER(3),

CONSTRAINT HYDROLOGY_DATA_PK

PRIMARY KEY ( DATA_ID, PRESSURE, BOTTLE_NUMBER, PARAMETER_CODE )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating table HYDROLOGY_GEOMETRY

CREATE TABLE HYDROLOGY_GEOMETRY

(

DATA_ID NUMBER(12) NOT NULL,

MI_PRINX NUMBER NOT NULL,

GEOLOC MDSYS.SDO_GEOMETRY,

SECURITY_CODE NUMBER(1),

LICENCE_NOTE VARCHAR(40),

CONSTRAINT HYDROLOGY_GEOMETRY_PK

PRIMARY KEY ( DATA_ID )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating table HYDROLOGY_HEADER

CREATE TABLE HYDROLOGY_HEADER

(

DATA_ID NUMBER(12) NOT NULL,

DEPLOYMENT_ID NUMBER(8) NOT NULL,

SURVEY_NAME VARCHAR2(12) NOT NULL,

CAST_NO NUMBER(10),

START_TIME DATE NOT NULL,

END_TIME DATE NOT NULL,

MIN_DEPTH NUMBER(5,1),

MAX_DEPTH NUMBER(5,1),

BOTTOM_DEPTH NUMBER(5,1),

BOTTOM_TIME DATE,

BOTTOM_LAT NUMBER(7,5),

BOTTOM_LON NUMBER(8,5),

END_LAT NUMBER(7,5),

END_LON NUMBER(8,5),

START_LAT NUMBER(7,5),

START_LON NUMBER(8,5),

PROJECT_ID NUMBER(7) NOT NULL,

GLOBAL_PROJECT_ID NUMBER(7),

MARLIN_ID NUMBER(7) NOT NULL,

SECURITY_CODE NUMBER(1),

RELEASE_DATE DATE,

LICENCE_NOTE VARCHAR(40),

CONSTRAINT HYDROLOGY_HEADER_PK

PRIMARY KEY ( DATA_ID )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K),

CONSTRAINT HYDROLOGY_HEADER_UK

UNIQUE ( SURVEY_NAME, CAST_NO )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating index HYDROLOGY_HEADER_POS_INDX

CREATE INDEX HYDROLOGY_HEADER_POS_INDX ON HYDROLOGY_HEADER

( START_LAT ASC, START_LON ASC )

PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K);

PROMPT Creating index HYDROLOGY_HEADER_TIME_INDX

CREATE INDEX HYDROLOGY_HEADER_TIME_INDX ON

HYDROLOGY_HEADER ( START_TIME ASC )

PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K);

PROMPT Creating table MOORED_INSTRUMENT_DATA

CREATE TABLE MOORED_INSTRUMENT_DATA

(

DATA_ID NUMBER(12) NOT NULL,

TIME_SECONDS NUMBER NOT NULL,

TIME DATE,

TIME_QC_FLAG NUMBER(3),

EAST_COMPONENT NUMBER(5,2),

EAST_COMPONENT_QC_FLAG NUMBER(3),

NORTH_COMPONENT NUMBER(5,2),

NORTH_COMPONENT_QC_FLAG NUMBER(3),

PRESSURE NUMBER(5,2),

PRESSURE_QC_FLAG NUMBER(3),

TEMPERATURE NUMBER(5,2),

TEMPERATURE_QC_FLAG NUMBER(3),

SALINITY NUMBER(4,2),

SALINITY_QC_FLAG NUMBER(3),

CONSTRAINT MOORED_INSTRUMENT_PK

PRIMARY KEY ( DATA_ID, TIME_SECONDS )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating table MOORED_INSTRUMENT_GEOMETRY

CREATE TABLE MOORED_INSTRUMENT_GEOMETRY

(

DATA_ID NUMBER(12) NOT NULL,

MI_PRINX NUMBER NOT NULL,

GEOLOC MDSYS.SDO_GEOMETRY,

SECURITY_CODE NUMBER(1),

LICENCE_NOTE VARCHAR(40),

CONSTRAINT MOORED_INSTRUMENT_GEOMETRY_PK

PRIMARY KEY ( DATA_ID )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating table MOORED_INSTRUMENT_HEADER

CREATE TABLE MOORED_INSTRUMENT_HEADER

(

DATA_ID NUMBER(12) NOT NULL,

DEPLOYMENT_ID NUMBER(8) NOT NULL,

SURVEY_NAME VARCHAR(12),

METER_TYPE VARCHAR2(20) NOT NULL,

NOMINAL_METER_DEPTH NUMBER(5),

START_TIME DATE NOT NULL,

END_TIME DATE NOT NULL,

START_LAT NUMBER(7,5),

START_LON NUMBER(8,5),

MAGNETIC_DECLINATION NUMBER(6,3),

SAMPLE_INTERVAL NUMBER(6),

FILTER_ID VARCHAR2(25),

DATA_PROCESSOR VARCHAR2(40),

COMMENTS VARCHAR2(225),

PROJECT_ID NUMBER(7) NOT NULL,

GLOBAL_PROJECT_ID NUMBER(7),

MARLIN_ID NUMBER(7) NOT NULL,

SECURITY_CODE NUMBER(1),

RELEASE_DATE DATE,

LICENCE_NOTE VARCHAR(40),

WATER_DEPTH NUMBER(5,1),

CONSTRAINT MOORED_INSTRUMENT_HEADER_PK

PRIMARY KEY ( DATA_ID )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating table NETCDF_HEADER

CREATE TABLE NETCDF_HEADER

(

DATA_ID NUMBER(12) NOT NULL,

FILENAME VARCHAR2(255),

SUMMARY VARCHAR2(2000),

PROJECT_ID NUMBER(7) NOT NULL,

GLOBAL_PROJECT_ID NUMBER(7),

MARLIN_ID NUMBER(7) NOT NULL,

SECURITY_CODE NUMBER(1),

RELEASE_DATE DATE,

LICENCE_NOTE VARCHAR(40),

CONSTRAINT NETCDF_HEADER_PK

PRIMARY KEY ( DATA_ID )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating table NETCDF_PARAMETER

CREATE TABLE NETCDF_PARAMETER

(

DATA_ID NUMBER(12) NOT NULL,

FILEVARIABLE VARCHAR2(50) NOT NULL,

PARAMETER_CODE VARCHAR2(20),

CONSTRAINT NETCDF_PARAMETER_PK

PRIMARY KEY ( DATA_ID,FILEVARIABLE )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating table PARAMETER_SET

CREATE TABLE PARAMETER_SET

(

PARAMETER_CODE VARCHAR2(20) NOT NULL,

PARAMETER_UNITS VARCHAR2(40),

PARAMETER_LABEL VARCHAR2(40),

COMMON_NAME VARCHAR2(40),

DESCRIPTION VARCHAR2(100),

MINIMUM_VALUE NUMBER(20,6),

MAXIMUM_VALUE NUMBER(20,6),

VALUE_TYPE VARCHAR2(10),

CONSTRAINT PARAMETER_CODES_PK

PRIMARY KEY ( PARAMETER_CODE )

USING INDEX PCTFREE 5

TABLESPACE INDX

) PCTFREE 5 PCTUSED 85;

PROMPT Creating table PARAMETER_SYNONYM_LIST

CREATE TABLE PARAMETER_SYNONYM_LIST

(

PARAMETER_CODE VARCHAR2(20) NOT NULL,

PARAMETER_SYNONYM VARCHAR2(40),

CONSTRAINT PARAMETER_SYNONYM_LIST_PK

PRIMARY KEY ( PARAMETER_CODE, PARAMETER_SYNONYM )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating table PHOTO_FRAME_BIOTA_COUNT

CREATE TABLE PHOTO_FRAME_BIOTA_COUNT

(

DATA_ID NUMBER(12) NOT NULL,

FRAME_NUMBER NUMBER(10) NOT NULL,

CAAB_SPECIES_CODE NUMBER(8) NOT NULL,

ORIGINAL_SPECIES_CODE NUMBER(8),

ITEM_COUNT NUMBER(7) NOT NULL,

QC_FLAG NUMBER(3) NOT NULL,

CONSTRAINT PHOTO_FRAME_BIOTA_COUNT_PK

PRIMARY KEY ( DATA_ID, FRAME_NUMBER, CAAB_SPECIES_CODE )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating table PHOTO_FRAME_BIOTA_PRES_ABS

CREATE TABLE PHOTO_FRAME_BIOTA_PRES_ABS

(

DATA_ID NUMBER(12) NOT NULL,

FRAME_NUMBER NUMBER(10) NOT NULL,

CAAB_SPECIES_CODE NUMBER(8) NOT NULL,

ORIGINAL_SPECIES_CODE NUMBER(8),

PRESENCE_CODE VARCHAR2(16) NOT NULL,

QC_FLAG NUMBER(3) NOT NULL,

CONSTRAINT PHOTO_FRAME_BIOTA_PRES_ABS_PK

PRIMARY KEY ( DATA_ID, FRAME_NUMBER, CAAB_SPECIES_CODE )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating table PHOTO_FRAME_BIOTA_SCORE

CREATE TABLE PHOTO_FRAME_BIOTA_SCORE

(

DATA_ID NUMBER(12) NOT NULL,

FRAME_NUMBER NUMBER(10) NOT NULL,

CAAB_SPECIES_CODE NUMBER(8) NOT NULL,

ORIGINAL_SPECIES_CODE NUMBER(8),

SCORE_CODE VARCHAR2(2) NOT NULL,

QC_FLAG NUMBER(3) NOT NULL,

CONSTRAINT PHOTO_FRAME_BIOTA_SCORE_PK

PRIMARY KEY ( DATA_ID, FRAME_NUMBER, CAAB_SPECIES_CODE )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating table PHOTO_FRAME_HEADER

CREATE TABLE PHOTO_FRAME_HEADER

(

DATA_ID NUMBER(12) NOT NULL,

FRAME_NUMBER NUMBER(10) NOT NULL,

LABEL VARCHAR2(20),

TIME DATE,

LAT NUMBER(7,5),

LON NUMBER(8,5),

DEPTH NUMBER(6),

LINK_URL VARCHAR2(20),

LINK_NAME VARCHAR2(30),

COMMENTS VARCHAR2(255),

CONSTRAINT PHOTO_FRAME_HEADER_PK

PRIMARY KEY ( DATA_ID, FRAME_NUMBER )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating table PHOTO_FRAME_TOPO_COUNT

CREATE TABLE PHOTO_FRAME_TOPO_COUNT

(

DATA_ID NUMBER(12) NOT NULL,

FRAME_NUMBER NUMBER(10) NOT NULL,

PARAMETER_CODE VARCHAR2(20) NOT NULL,

ITEM_COUNT NUMBER(7) NOT NULL,

QC_FLAG NUMBER(3) NOT NULL,

CONSTRAINT PHOTO_FRAME_TOPO_COUNT_PK

PRIMARY KEY ( DATA_ID, FRAME_NUMBER, PARAMETER_CODE )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating table PHOTO_FRAME_TOPO_PRES_ABS

CREATE TABLE PHOTO_FRAME_TOPO_PRES_ABS

(

DATA_ID NUMBER(12) NOT NULL,

FRAME_NUMBER NUMBER(10) NOT NULL,

PARAMETER_CODE VARCHAR2(20) NOT NULL,

PRESENCE_CODE VARCHAR2(16) NOT NULL,

QC_FLAG NUMBER(3) NOT NULL,

CONSTRAINT PHOTO_FRAME_TOPO_PRES_ABS_PK

PRIMARY KEY ( DATA_ID, FRAME_NUMBER, PARAMETER_CODE )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating table PHOTO_FRAME_TOPO_SCORE

CREATE TABLE PHOTO_FRAME_TOPO_SCORE

(

DATA_ID NUMBER(12) NOT NULL,

FRAME_NUMBER NUMBER(10) NOT NULL,

PARAMETER_CODE VARCHAR2(20) NOT NULL,

SCORE_CODE NUMBER(2) NOT NULL,

QC_FLAG NUMBER(3) NOT NULL,

CONSTRAINT PHOTO_FRAME_TOPO_SCORE_PK

PRIMARY KEY ( DATA_ID, FRAME_NUMBER, PARAMETER_CODE )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating table PHOTO_SEQUENCE_HEADER

CREATE TABLE PHOTO_SEQUENCE_HEADER

(

DATA_ID NUMBER(12) NOT NULL,

DEPLOYMENT_ID NUMBER(8) NOT NULL,

SURVEY_NAME VARCHAR(12),

PHOTO_TYPE_CODE VARCHAR2(6) NOT NULL,

LABEL VARCHAR2(20),

START_TIME DATE,

END_TIME DATE,

UTC_OFFSET NUMBER(3,1),

START_LAT NUMBER(7,5),

START_LON NUMBER(8,5),

END_LAT NUMBER(7,5),

END_LON NUMBER(8,5),

START_DEPTH NUMBER(5),

END_DEPTH NUMBER(5),

COMMENTS VARCHAR2(255),

PROJECT_ID NUMBER(7) NOT NULL,

GLOBAL_PROJECT_ID NUMBER(7),

MARLIN_ID NUMBER(7) NOT NULL,

SECURITY_CODE NUMBER(1),

RELEASE_DATE DATE,

LICENCE_NOTE VARCHAR(40),

CONSTRAINT PHOTO_SEQUENCE_HEADER_PK

PRIMARY KEY ( DATA_ID )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating table PROJECT

CREATE TABLE PROJECT

(

PROJECT_ID NUMBER(7) NOT NULL,

PROJECT_NAME VARCHAR2(100) NOT NULL,

PROJECT_DESCRIPTION VARCHAR(2000),

CONSTRAINT PARAMETER_PROJECT_PK

PRIMARY KEY ( PROJECT_ID )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating table QC_FLAGS

CREATE TABLE QC_FLAGS

(

QC_FLAG NUMBER(3),

DATA_STATE VARCHAR2(8),

OPERATION VARCHAR2(20),

ERROR_TYPE VARCHAR2(60),

CONSTRAINT QC_FLAGS_PK

PRIMARY KEY ( QC_FLAG )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 6K NEXT 6K)

) PCTFREE 5 PCTUSED 95;

PROMPT Creating table SOURCE

CREATE TABLE SOURCE

(

SOURCE_ID NUMBER(7) NOT NULL,

SOURCE_NAME VARCHAR2(40),

SOURCE_TYPE VARCHAR2(40),

CONSTRAINT SOURCE_PK

PRIMARY KEY ( SOURCE_ID )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating table SURVEY

CREATE TABLE SURVEY

(

SURVEY_NAME VARCHAR2(12) NOT NULL,

SOURCE_ID NUMBER(7) NOT NULL,

SURVEY_TYPE VARCHAR2(16) NOT NULL,

MARLIN_SURVEY_ID NUMBER(7),

PROJECT_ID NUMBER(7) NOT NULL,

START_TIME DATE NOT NULL,

END_TIME DATE NOT NULL,

START_LAT NUMBER(7,5),

START_LON NUMBER(8,5),

END_LAT NUMBER(7,5),

END_LON NUMBER(8,5),

START_LOCATION VARCHAR2(80),

END_LOCATION VARCHAR2(80),

COMMENTS VARCHAR2(255),

CONSTRAINT SURVEY_PK

PRIMARY KEY ( SURVEY_NAME )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating index SURVEY_POS_INDX

CREATE INDEX SURVEY_POS_INDX ON SURVEY

( START_LAT ASC, START_LON ASC )

PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K);

PROMPT Creating index SURVEY_TIME_INDX

CREATE INDEX SURVEY_TIME_INDX ON SURVEY

( START_TIME ASC )

PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K);

PROMPT Creating table UNDERWAY_DATA

CREATE TABLE UNDERWAY_DATA

(

DATA_ID NUMBER(12) NOT NULL,

TIME DATE NOT NULL,

PARAMETER_CODE VARCHAR2(20) NOT NULL,

VALUE NUMBER(10,5),

QC_FLAG NUMBER(3),

CONSTRAINT UNDERWAY_DATA_PK

PRIMARY KEY ( DATA_ID, TIME, PARAMETER_CODE )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 120K NEXT 120K)

) PCTFREE 5 PCTUSED 85 STORAGE (INITIAL 512K NEXT 512 K PCTINCREASE 0);

PROMPT Creating index UW_DATA_TIME_INDX

CREATE INDEX UW_DATA_TIME_INDX ON

UNDERWAY_DATA ( TIME ASC )

PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 24K NEXT 12K);

PROMPT Creating table UNDERWAY_GEOMETRY

CREATE TABLE UNDERWAY_GEOMETRY

(

DATA_ID NUMBER(12) NOT NULL,

PARAMETER_CODE VARCHAR2(20) NOT NULL,

MI_PRINX NUMBER NOT NULL,

GEOLOC MDSYS.SDO_GEOMETRY,

SECURITY_CODE NUMBER(1),

LICENCE_NOTE VARCHAR(40),

CONSTRAINT UNDERWAY_GEOMETRY_PK

PRIMARY KEY ( DATA_ID, PARAMETER_CODE )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating table UNDERWAY_GPS

CREATE TABLE UNDERWAY_GPS

(

TIME DATE NOT NULL,

LAT NUMBER(9,5) NOT NULL,

LON NUMBER(10,5) NOT NULL,

DATA_ID NUMBER(12) NOT NULL,

QC_FLAG NUMBER(3),

CONSTRAINT UNDERWAY_GPS_PK

PRIMARY KEY ( TIME, LAT, LON )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating table UNDERWAY_HEADER

CREATE TABLE UNDERWAY_HEADER

(

DATA_ID NUMBER(12) NOT NULL,

DEPLOYMENT_ID NUMBER(8) NOT NULL,

SURVEY_NAME VARCHAR2(12) NOT NULL,

START_TIME DATE NOT NULL,

END_TIME DATE NOT NULL,

START_LAT NUMBER(7,5),

START_LON NUMBER(8,5),

END_LAT NUMBER(7,5),

END_LON NUMBER(8,5),

PROJECT_ID NUMBER(7) NOT NULL,

GLOBAL_PROJECT_ID NUMBER(7),

MARLIN_ID NUMBER(7) NOT NULL,

SECURITY_CODE NUMBER(1),

RELEASE_DATE DATE,

LICENCE_NOTE VARCHAR(40),

CONSTRAINT UNDERWAY_HEADER_PK

PRIMARY KEY ( DATA_ID )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating index UNDERWAY_HEADER_POS_INDX

CREATE INDEX UW_HEADER_POS_INDX ON

UNDERWAY_HEADER ( START_LAT ASC, START_LON ASC )

PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K);

PROMPT Creating index UW_HEADER_TIME_INDX

CREATE INDEX UW_HEADER_TIME_INDX ON

UNDERWAY_HEADER ( START_TIME ASC )

PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K);

2.1 Warehouse Application specific tables

PROMPT Creating table MAP_LAYER

CREATE TABLE MAP_LAYER

(

LAYER_ID NUMBER(10) NOT NULL,

LAYER_NAME VARCHAR2(50) NOT NULL,

DRAW_ORDER NUMBER(10) NOT NULL,

SOURCE_TYPE VARCHAR2(10) NOT NULL,

CONSTRAINT MAP_LAYER_PK

PRIMARY KEY ( LAYER_ID )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating table MAP_LAYER_PARAMETER

CREATE TABLE MAP_LAYER_PARAMETER

(

LAYER_ID NUMBER(10) NOT NULL,

PARAMETER_NAME VARCHAR2(20) NOT NULL,

PARAMETER_VALUE VARCHAR2(50) NOT NULL,

CONSTRAINT MAP_LAYER_PARAMETER_PK

PRIMARY KEY ( LAYER_ID, PARAMETER_NAME )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating table APP_CATEGORY

CREATE TABLE APP_CATEGORY

(

CATEGORY_ID NUMBER(10) NOT NULL,

CATEGORY_NAME VARCHAR2(20) NOT NULL,

PARENT_CATEGORY_ID NUMBER(10),

CONSTRAINT APP_CATEGORY_PK

PRIMARY KEY ( CATEGORY_ID )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating table APP_CATEGORY_LOOKUP

CREATE TABLE APP_CATEGORY_LOOKUP

(

DATA_ID NUMBER(12) NOT NULL,

CATEGORY_ID NUMBER(10) NOT NULL,

CONSTRAINT APP_CATEGORY_LOOKUP_PK

PRIMARY KEY ( DATA_ID, CATEGORY_ID )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating table APP_USER

CREATE TABLE APP_USER

(

USER_ID NUMBER(10) NOT NULL,

LOGIN VARCHAR2(30) NOT NULL,

PASSWORD VARCHAR2(10) NOT NULL,

ADMINISTRATOR VARCHAR2(3),

CONSTRAINT APP_USER_PK

PRIMARY KEY ( USER_ID )

USING INDEX PCTFREE 5

TABLESPACE INDX

STORAGE (INITIAL 12K NEXT 6K)

) PCTFREE 5 PCTUSED 85;

PROMPT Creating table APP_ACCESS