ingo@2805: SET AUTOCOMMIT ON;
ingo@2805: 
ingo@2805: CREATE SEQUENCE LOCATION_SYSTEM_SEQ;
ingo@2805: 
ingo@2805: CREATE TABLE location_system (
ingo@2805:     id          NUMBER(38,0) NOT NULL,
ingo@2805:     name        VARCHAR(32)  NOT NULL,
ingo@2805:     description VARCHAR(255),
ingo@2805:     PRIMARY KEY(id)
ingo@2805: );
ingo@2805: 
ingo@2805: 
ingo@2805: CREATE SEQUENCE ELEVATION_MODEL_SEQ;
ingo@2805: 
ingo@2805: CREATE TABLE elevation_model (
ingo@2805:     id          NUMBER(38,0) NOT NULL,
ingo@2805:     name        VARCHAR(32)  NOT NULL,
ingo@2805:     unit_id     NUMBER(38,0) NOT NULL,
ingo@2805:     PRIMARY KEY(id),
ingo@2805:     CONSTRAINT fk_unit FOREIGN KEY (unit_id) REFERENCES units(id)
ingo@2805: );
ingo@2805: 
ingo@2805: CREATE SEQUENCE BED_HEIGHT_TYPE_SEQ;
ingo@2805: 
ingo@2805: CREATE TABLE bed_height_type (
ingo@2805:     id          NUMBER(38,0) NOT NULL,
ingo@2805:     name        VARCHAR(16)  NOT NULL,
ingo@2805:     description VARCHAR(255),
ingo@2805:     PRIMARY KEY(id)
ingo@2805: );
ingo@2805: 
ingo@2805: 
ingo@2805: 
ingo@2805: CREATE SEQUENCE BED_HEIGHT_SINGLE_ID_SEQ;
ingo@2805: 
ingo@2805: CREATE TABLE bed_height_single (
ingo@2805:     id                      NUMBER(38,0) NOT NULL,
ingo@2809:     river_id                NUMBER(38,0) NOT NULL,
ingo@2805:     year                    NUMBER(38,0) NOT NULL,
ingo@2807:     sounding_width          NUMBER(38,0) NOT NULL,
ingo@2805:     type_id                 NUMBER(38,0) NOT NULL,
ingo@2807:     location_system_id      NUMBER(38,0) NOT NULL,
ingo@2805:     cur_elevation_model_id  NUMBER(38,0) NOT NULL,
ingo@2805:     old_elevation_model_id  NUMBER(38,0),
ingo@2805:     range_id                NUMBER(38,0) NOT NULL,
ingo@2805:     evaluation_by           VARCHAR(255),
ingo@2805:     description             VARCHAR(255),
ingo@2805:     PRIMARY KEY(id),
ingo@2809:     CONSTRAINT fk_bed_single_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
ingo@2805:     CONSTRAINT fk_type FOREIGN KEY (type_id) REFERENCES bed_height_type(id),
ingo@2807:     CONSTRAINT fk_location_system FOREIGN KEY (location_system_id) REFERENCES location_system(id),
ingo@2805:     CONSTRAINT fk_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id),
ingo@2805:     CONSTRAINT fk_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id),
ingo@2805:     CONSTRAINT fk_range FOREIGN KEY (range_id) REFERENCES ranges(id)
ingo@2805: );
ingo@2805: 
ingo@2805: 
ingo@2805: CREATE SEQUENCE BED_HEIGHT_EPOCH_ID_SEQ;
ingo@2805: 
ingo@2805: CREATE TABLE bed_height_epoch (
ingo@2805:     id                      NUMBER(38,0) NOT NULL,
ingo@2810:     river_id                NUMBER(38,0) NOT NULL,
ingo@2805:     time_interval_id        NUMBER(38,0) NOT NULL,
ingo@2805:     -- sounding_with           NUMBER(38,0) NOT NULL,
ingo@2805:     -- type_id                 NUMBER(38,0) NOT NULL,
ingo@2805:     cur_elevation_model_id  NUMBER(38,0) NOT NULL,
ingo@2805:     old_elevation_model_id  NUMBER(38,0),
ingo@2805:     range_id                NUMBER(38,0) NOT NULL,
ingo@2805:     evaluation_by           VARCHAR(255),
ingo@2805:     description             VARCHAR(255),
ingo@2805:     PRIMARY KEY(id),
ingo@2805:     CONSTRAINT fk_time_interval FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id),
ingo@2805:     CONSTRAINT fk_epoch_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id),
ingo@2805:     CONSTRAINT fk_epoch_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id),
ingo@2805:     CONSTRAINT fk_epoch_range FOREIGN KEY (range_id) REFERENCES ranges(id)
ingo@2805: );
ingo@2805: 
ingo@2805: 
ingo@2805: CREATE SEQUENCE BED_SINGLE_VALUES_ID_SEQ;
ingo@2805: 
ingo@2805: CREATE TABLE bed_height_single_values (
ingo@2805:     id                      NUMBER(38,0) NOT NULL,
ingo@2805:     bed_height_single_id    NUMBER(38,0) NOT NULL,
ingo@2805:     station                 NUMBER(38,2) NOT NULL,
ingo@2805:     height                  NUMBER(38,2),
ingo@2805:     uncertainty             NUMBER(38,2),
ingo@2805:     data_gap                NUMBER(38,2) NOT NULL,
ingo@2807:     sounding_width          NUMBER(38,2) NOT NULL,
ingo@2805:     width                   NUMBER(38,2) NOT NULL,
ingo@2805:     PRIMARY KEY(id),
ingo@2805:     CONSTRAINT fk_bed_single_values_parent FOREIGN KEY (bed_height_single_id) REFERENCES bed_height_single(id)
ingo@2805: );
ingo@2805: 
ingo@2805: 
ingo@2805: CREATE SEQUENCE BED_EPOCH_VALUES_ID_SEQ;
ingo@2805: 
ingo@2805: CREATE TABLE bed_height_epoch_values (
ingo@2805:     id                      NUMBER(38,0) NOT NULL,
ingo@2805:     bed_height_epoch_id     NUMBER(38,0) NOT NULL,
ingo@2805:     station                 NUMBER(38,2) NOT NULL,
ingo@2805:     height                  NUMBER(38,2),
ingo@2805:     PRIMARY KEY(id),
ingo@2805:     CONSTRAINT fk_bed_epoch_values_parent FOREIGN KEY (bed_height_epoch_id) REFERENCES bed_height_epoch(id)
ingo@2805: );
ingo@2805: