sascha@2823: BEGIN;
sascha@2823: 
sascha@2823: CREATE SEQUENCE LOCATION_SYSTEM_SEQ;
sascha@2823: 
sascha@2823: CREATE TABLE location_system (
sascha@2823:     id          int NOT NULL,
sascha@2823:     name        VARCHAR(32)  NOT NULL,
sascha@2823:     description VARCHAR(255),
sascha@2823:     PRIMARY KEY(id)
sascha@2823: );
sascha@2823: 
sascha@2823: 
sascha@2823: CREATE SEQUENCE ELEVATION_MODEL_SEQ;
sascha@2823: 
sascha@2823: CREATE TABLE elevation_model (
sascha@2823:     id          int NOT NULL,
sascha@2823:     name        VARCHAR(32)  NOT NULL,
sascha@2823:     unit_id     int NOT NULL,
sascha@2823:     PRIMARY KEY(id),
sascha@2823:     CONSTRAINT fk_unit FOREIGN KEY (unit_id) REFERENCES units(id)
sascha@2823: );
sascha@2823: 
sascha@2823: CREATE SEQUENCE BED_HEIGHT_TYPE_SEQ;
sascha@2823: 
sascha@2823: CREATE TABLE bed_height_type (
sascha@2823:     id          int NOT NULL,
sascha@2823:     name        VARCHAR(16)  NOT NULL,
sascha@2823:     description VARCHAR(255),
sascha@2823:     PRIMARY KEY(id)
sascha@2823: );
sascha@2823: 
sascha@2823: 
sascha@2823: 
sascha@2823: CREATE SEQUENCE BED_HEIGHT_SINGLE_ID_SEQ;
sascha@2823: 
sascha@2823: CREATE TABLE bed_height_single (
sascha@2823:     id                      int NOT NULL,
sascha@2823:     river_id                int NOT NULL,
sascha@2823:     year                    int NOT NULL,
sascha@2823:     sounding_width          int NOT NULL,
sascha@2823:     type_id                 int NOT NULL,
sascha@2823:     location_system_id      int NOT NULL,
sascha@2823:     cur_elevation_model_id  int NOT NULL,
sascha@2823:     old_elevation_model_id  int,
sascha@2823:     range_id                int NOT NULL,
sascha@2823:     evaluation_by           VARCHAR(255),
sascha@2823:     description             VARCHAR(255),
sascha@2823:     PRIMARY KEY(id),
sascha@2823:     CONSTRAINT fk_bed_single_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
sascha@2823:     CONSTRAINT fk_type FOREIGN KEY (type_id) REFERENCES bed_height_type(id),
sascha@2823:     CONSTRAINT fk_location_system FOREIGN KEY (location_system_id) REFERENCES location_system(id),
sascha@2823:     CONSTRAINT fk_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id),
sascha@2823:     CONSTRAINT fk_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id),
sascha@2823:     CONSTRAINT fk_range FOREIGN KEY (range_id) REFERENCES ranges(id)
sascha@2823: );
sascha@2823: 
sascha@2823: 
sascha@2823: CREATE SEQUENCE BED_HEIGHT_EPOCH_ID_SEQ;
sascha@2823: 
sascha@2823: CREATE TABLE bed_height_epoch (
sascha@2823:     id                      int NOT NULL,
sascha@2823:     river_id                int NOT NULL,
sascha@2823:     time_interval_id        int NOT NULL,
sascha@2823:     -- sounding_with           int NOT NULL,
sascha@2823:     -- type_id                 int NOT NULL,
sascha@2823:     cur_elevation_model_id  int NOT NULL,
sascha@2823:     old_elevation_model_id  int,
sascha@2823:     range_id                int NOT NULL,
sascha@2823:     evaluation_by           VARCHAR(255),
sascha@2823:     description             VARCHAR(255),
sascha@2823:     PRIMARY KEY(id),
sascha@2823:     CONSTRAINT fk_time_interval FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id),
sascha@2823:     CONSTRAINT fk_epoch_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id),
sascha@2823:     CONSTRAINT fk_epoch_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id),
sascha@2823:     CONSTRAINT fk_epoch_range FOREIGN KEY (range_id) REFERENCES ranges(id)
sascha@2823: );
sascha@2823: 
sascha@2823: 
sascha@2823: CREATE SEQUENCE BED_SINGLE_VALUES_ID_SEQ;
sascha@2823: 
sascha@2823: CREATE TABLE bed_height_single_values (
sascha@2823:     id                      int NOT NULL,
sascha@2823:     bed_height_single_id    int NOT NULL,
sascha@2823:     station                 NUMERIC NOT NULL,
sascha@2823:     height                  NUMERIC,
sascha@2823:     uncertainty             NUMERIC,
sascha@2823:     data_gap                NUMERIC NOT NULL,
sascha@2823:     sounding_width          NUMERIC NOT NULL,
sascha@2823:     width                   NUMERIC NOT NULL,
sascha@2823:     PRIMARY KEY(id),
sascha@2823:     CONSTRAINT fk_bed_single_values_parent FOREIGN KEY (bed_height_single_id) REFERENCES bed_height_single(id)
sascha@2823: );
sascha@2823: 
sascha@2823: 
sascha@2823: CREATE SEQUENCE BED_EPOCH_VALUES_ID_SEQ;
sascha@2823: 
sascha@2823: CREATE TABLE bed_height_epoch_values (
sascha@2823:     id                      int NOT NULL,
sascha@2823:     bed_height_epoch_id     int NOT NULL,
sascha@2823:     station                 NUMERIC NOT NULL,
sascha@2823:     height                  NUMERIC,
sascha@2823:     PRIMARY KEY(id),
sascha@2823:     CONSTRAINT fk_bed_epoch_values_parent FOREIGN KEY (bed_height_epoch_id) REFERENCES bed_height_epoch(id)
sascha@2823: );
sascha@2823: 
sascha@2823: 
sascha@2823: CREATE SEQUENCE DEPTHS_ID_SEQ;
sascha@2823: 
sascha@2823: CREATE TABLE depths (
sascha@2823:     id      int NOT NULL,
sascha@2823:     lower   NUMERIC NOT NULL,
sascha@2823:     upper   NUMERIC NOT NULL,
sascha@2823:     unit_id int NOT NULL,
sascha@2823:     PRIMARY KEY(id),
sascha@2823:     CONSTRAINT fk_depths_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
sascha@2823: );
sascha@2823: 
sascha@2823: 
sascha@2823: CREATE SEQUENCE SEDIMENT_DENSITY_ID_SEQ;
sascha@2823: 
sascha@2823: CREATE TABLE sediment_density (
sascha@2823:     id          int NOT NULL,
sascha@2823:     river_id    int NOT NULL,
sascha@2823:     depth_id    int NOT NULL,
sascha@2823:     unit_id     int NOT NULL,
sascha@2823:     description VARCHAR(256),
sascha@2823:     PRIMARY KEY(id),
sascha@2823:     CONSTRAINT fk_sd_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
sascha@2823:     CONSTRAINT fk_sd_depth_id FOREIGN KEY (depth_id) REFERENCES depths(id),
sascha@2823:     CONSTRAINT fk_sd_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
sascha@2823: );
sascha@2823: 
sascha@2823: 
sascha@2823: CREATE SEQUENCE SEDIMENT_DENSITY_VALUES_ID_SEQ;
sascha@2823: 
sascha@2823: CREATE TABLE sediment_density_values (
sascha@2823:     id                  int NOT NULL,
sascha@2823:     sediment_density_id int NOT NULL,
sascha@2823:     station             NUMERIC NOT NULL,
sascha@2823:     density             NUMERIC NOT NULL,
sascha@2823:     description         VARCHAR(256),
sascha@2823:     PRIMARY KEY(id),
sascha@2823:     CONSTRAINT fk_sdv_sediment_density_id FOREIGN KEY(sediment_density_id) REFERENCES sediment_density(id)
sascha@2823: );
sascha@2823: 
sascha@2823: 
sascha@2823: CREATE SEQUENCE MORPHOLOGIC_WIDTH_ID_SEQ;
sascha@2823: 
sascha@2823: CREATE TABLE morphologic_width (
sascha@2823:     id          int NOT NULL,
sascha@2823:     river_id    int NOT NULL,
sascha@2823:     unit_id     int NOT NULL,
sascha@2823:     PRIMARY KEY(id),
sascha@2823:     CONSTRAINT fk_mw_river_id FOREIGN KEY(river_id) REFERENCES rivers(id),
sascha@2823:     CONSTRAINT fk_mw_unit_id FOREIGN KEY(unit_id) REFERENCES units(id)
sascha@2823: );
sascha@2823: 
sascha@2823: 
sascha@2823: CREATE SEQUENCE MORPH_WIDTH_VALUES_ID_SEQ;
sascha@2823: 
sascha@2823: CREATE TABLE morphologic_width_values (
sascha@2823:     id                      int NOT NULL,
sascha@2823:     morphologic_width_id    int NOT NULL,
sascha@2823:     station                 NUMERIC NOT NULL,
sascha@2823:     width                   NUMERIC NOT NULL,
sascha@2823:     description             VARCHAR(256),
sascha@2823:     PRIMARY KEY(id),
sascha@2823:     CONSTRAINT fk_mwv_morphologic_width_id FOREIGN KEY (morphologic_width_id) REFERENCES morphologic_width(id)
sascha@2823: );
sascha@2823: 
sascha@2823: COMMIT;