Mercurial > dive4elements > river

view flys-backend/doc/schema/oracle-minfo.sql @ 4798:39885bdfc6fc

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added calculation of the "Umhuellende" to calculation of "W fuer ungleichwertige Abfluesse". This is done by figuring out the WST columns that imfold the data and then do simple "gleichwertige" calculations from the start of the interval. This is too much because only the Qs are needed for the "Umhuellende".
author Sascha L. Teichmann <teichmann@intevation.de>
date Sun, 13 Jan 2013 16:18:28 +0100
parents 504cd5801785
children 8667f629d238
line wrap: on
line source
SET AUTOCOMMIT ON;

CREATE SEQUENCE LOCATION_SYSTEM_SEQ;

CREATE TABLE location_system (
    id          NUMBER(38,0) NOT NULL,
    name        VARCHAR(32)  NOT NULL,
    description VARCHAR(255),
    PRIMARY KEY(id)
);


CREATE SEQUENCE ELEVATION_MODEL_SEQ;

CREATE TABLE elevation_model (
    id          NUMBER(38,0) NOT NULL,
    name        VARCHAR(32)  NOT NULL,
    unit_id     NUMBER(38,0) NOT NULL,
    PRIMARY KEY(id),
    CONSTRAINT fk_unit FOREIGN KEY (unit_id) REFERENCES units(id)
);

CREATE SEQUENCE BED_HEIGHT_TYPE_SEQ;

CREATE TABLE bed_height_type (
    id          NUMBER(38,0) NOT NULL,
    name        VARCHAR(16)  NOT NULL,
    description VARCHAR(255),
    PRIMARY KEY(id)
);



CREATE SEQUENCE BED_HEIGHT_SINGLE_ID_SEQ;

CREATE TABLE bed_height_single (
    id                      NUMBER(38,0) NOT NULL,
    river_id                NUMBER(38,0) NOT NULL,
    year                    NUMBER(38,0) NOT NULL,
    sounding_width          NUMBER(38,0) NOT NULL,
    type_id                 NUMBER(38,0) NOT NULL,
    location_system_id      NUMBER(38,0) NOT NULL,
    cur_elevation_model_id  NUMBER(38,0) NOT NULL,
    old_elevation_model_id  NUMBER(38,0),
    range_id                NUMBER(38,0) NOT NULL,
    evaluation_by           VARCHAR(255),
    description             VARCHAR(255),
    PRIMARY KEY(id),
    CONSTRAINT fk_bed_single_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
    CONSTRAINT fk_type FOREIGN KEY (type_id) REFERENCES bed_height_type(id),
    CONSTRAINT fk_location_system FOREIGN KEY (location_system_id) REFERENCES location_system(id),
    CONSTRAINT fk_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id),
    CONSTRAINT fk_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id),
    CONSTRAINT fk_range FOREIGN KEY (range_id) REFERENCES ranges(id)
);


CREATE SEQUENCE BED_HEIGHT_EPOCH_ID_SEQ;

CREATE TABLE bed_height_epoch (
    id                      NUMBER(38,0) NOT NULL,
    river_id                NUMBER(38,0) NOT NULL,
    time_interval_id        NUMBER(38,0) NOT NULL,
    -- sounding_with           NUMBER(38,0) NOT NULL,
    -- type_id                 NUMBER(38,0) NOT NULL,
    cur_elevation_model_id  NUMBER(38,0) NOT NULL,
    old_elevation_model_id  NUMBER(38,0),
    range_id                NUMBER(38,0) NOT NULL,
    evaluation_by           VARCHAR(255),
    description             VARCHAR(255),
    PRIMARY KEY(id),
    CONSTRAINT fk_time_interval FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id),
    CONSTRAINT fk_epoch_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id),
    CONSTRAINT fk_epoch_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id),
    CONSTRAINT fk_epoch_range FOREIGN KEY (range_id) REFERENCES ranges(id)
);


CREATE SEQUENCE BED_SINGLE_VALUES_ID_SEQ;

CREATE TABLE bed_height_single_values (
    id                      NUMBER(38,0) NOT NULL,
    bed_height_single_id    NUMBER(38,0) NOT NULL,
    station                 NUMBER(38,2) NOT NULL,
    height                  NUMBER(38,2),
    uncertainty             NUMBER(38,2),
    data_gap                NUMBER(38,2),
    sounding_width          NUMBER(38,2),
    width                   NUMBER(38,2),
    PRIMARY KEY(id),
    CONSTRAINT fk_bed_single_values_parent FOREIGN KEY (bed_height_single_id) REFERENCES bed_height_single(id)
);


CREATE SEQUENCE BED_EPOCH_VALUES_ID_SEQ;

CREATE TABLE bed_height_epoch_values (
    id                      NUMBER(38,0) NOT NULL,
    bed_height_epoch_id     NUMBER(38,0) NOT NULL,
    station                 NUMBER(38,2) NOT NULL,
    height                  NUMBER(38,2),
    PRIMARY KEY(id),
    CONSTRAINT fk_bed_epoch_values_parent FOREIGN KEY (bed_height_epoch_id) REFERENCES bed_height_epoch(id)
);


CREATE SEQUENCE DEPTHS_ID_SEQ;

CREATE TABLE depths (
    id      NUMBER(38,0) NOT NULL,
    lower   NUMBER(38,2) NOT NULL,
    upper   NUMBER(38,2) NOT NULL,
    unit_id NUMBER(38,0) NOT NULL,
    PRIMARY KEY(id),
    CONSTRAINT fk_depths_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
);


CREATE SEQUENCE SEDIMENT_DENSITY_ID_SEQ;

CREATE TABLE sediment_density (
    id          NUMBER(38,0) NOT NULL,
    river_id    NUMBER(38,0) NOT NULL,
    depth_id    NUMBER(38,0) NOT NULL,
    unit_id     NUMBER(38,0) NOT NULL,
    description VARCHAR(256),
    PRIMARY KEY(id),
    CONSTRAINT fk_sd_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
    CONSTRAINT fk_sd_depth_id FOREIGN KEY (depth_id) REFERENCES depths(id),
    CONSTRAINT fk_sd_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
);


CREATE SEQUENCE SEDIMENT_DENSITY_VALUES_ID_SEQ;

CREATE TABLE sediment_density_values (
    id                  NUMBER(38,0) NOT NULL,
    sediment_density_id NUMBER(38,0) NOT NULL,
    station             NUMBER(38,2) NOT NULL,
    density             NUMBER(38,2) NOT NULL,
    description         VARCHAR(256),
    year                NUMBER(38,0),
    PRIMARY KEY(id),
    CONSTRAINT fk_sdv_sediment_density_id FOREIGN KEY(sediment_density_id) REFERENCES sediment_density(id)
);


CREATE SEQUENCE MORPHOLOGIC_WIDTH_ID_SEQ;

CREATE TABLE morphologic_width (
    id          NUMBER(38,0) NOT NULL,
    river_id    NUMBER(38,0) NOT NULL,
    unit_id     NUMBER(38,0) NOT NULL,
    PRIMARY KEY(id),
    CONSTRAINT fk_mw_river_id FOREIGN KEY(river_id) REFERENCES rivers(id),
    CONSTRAINT fk_mw_unit_id FOREIGN KEY(unit_id) REFERENCES units(id)
);


CREATE SEQUENCE MORPH_WIDTH_VALUES_ID_SEQ;

CREATE TABLE morphologic_width_values (
    id                      NUMBER(38,0) NOT NULL,
    morphologic_width_id    NUMBER(38,0) NOT NULL,
    station                 NUMBER(38,3) NOT NULL,
    width                   NUMBER(38,3) NOT NULL,
    description             VARCHAR(256),
    PRIMARY KEY(id),
    CONSTRAINT fk_mwv_morphologic_width_id FOREIGN KEY (morphologic_width_id) REFERENCES morphologic_width(id)
);


CREATE SEQUENCE DISCHARGE_ZONE_ID_SEQ;

CREATE TABLE discharge_zone (
    id                      NUMBER(38,0) NOT NULL,
    river_id                NUMBER(38,0) NOT NULL,
    gauge_name              VARCHAR(64)  NOT NULL, -- this is not very proper, but there are gauges with no db instance
    value                   NUMBER(38,3) NOT NULL,
    lower_discharge         VARCHAR(16)  NOT NULL,
    upper_discharge         VARCHAR(16),
    PRIMARY KEY(id),
    CONSTRAINT fk_dz_river_id FOREIGN KEY (river_id) REFERENCES rivers(id)
);


CREATE SEQUENCE FLOW_VELOCITY_MODEL_ID_SEQ;

CREATE TABLE flow_velocity_model (
    id                  NUMBER(38,0) NOT NULL,
    river_id            NUMBER(38,0) NOT NULL,
    discharge_zone_id   NUMBER(38,0) NOT NULL,
    description         VARCHAR(256),
    PRIMARY KEY (id),
    CONSTRAINT fk_fvm_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
    CONSTRAINT fk_fvm_discharge_zone_id FOREIGN KEY (discharge_zone_id) REFERENCES discharge_zone (id)
);


CREATE SEQUENCE FLOW_VELOCITY_M_VALUES_ID_SEQ;

CREATE TABLE flow_velocity_model_values (
    id                      NUMBER(38,0) NOT NULL,
    flow_velocity_model_id  NUMBER(38,0) NOT NULL,
    station                 NUMBER(38,3) NOT NULL,
    q                       NUMBER(38,3) NOT NULL,
    total_channel           NUMBER(38,3) NOT NULL,
    main_channel            NUMBER(38,3) NOT NULL,
    shear_stress            NUMBER(38,3) NOT NULL,
    PRIMARY KEY(id),
    CONSTRAINT fk_fvv_flow_velocity_model_id FOREIGN KEY (flow_velocity_model_id) REFERENCES flow_velocity_model(id)
);



CREATE SEQUENCE FV_MEASURE_ID_SEQ;

CREATE TABLE flow_velocity_measurements (
    id          NUMBER(38,0) NOT NULL,
    river_id    NUMBER(38,0) NOT NULL,
    description VARCHAR(256),
    PRIMARY KEY (id),
    CONSTRAINT fk_fvm_rivers_id FOREIGN KEY (river_id) REFERENCES rivers(id)
);

CREATE SEQUENCE FV_MEASURE_VALUES_ID_SEQ;

CREATE TABLE flow_velocity_measure_values (
    id              NUMBER(38,0) NOT NULL,
    measurements_id NUMBER(38,0) NOT NULL,
    station         NUMBER(38,3) NOT NULL,
    datetime        TIMESTAMP,
    w               NUMBER(38,3) NOT NULL,
    q               NUMBER(38,3) NOT NULL,
    v               NUMBER(38,3) NOT NULL,
    description     VARCHAR(256),
    PRIMARY KEY (id),
    CONSTRAINT fk_fvmv_measurements_id FOREIGN KEY (measurements_id) REFERENCES flow_velocity_measurements (id)
);


CREATE SEQUENCE GRAIN_FRACTION_ID_SEQ;

CREATE TABLE grain_fraction (
    id      NUMBER(38,0)   NOT NULL,
    name    VARCHAR(64)    NOT NULL,
    lower   NUMBER(38,3),
    upper   NUMBER(38,3),
    unit_id NUMBER(38,0),
    PRIMARY KEY (id),
    CONSTRAINT fk_gf_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
);


CREATE SEQUENCE SEDIMENT_YIELD_ID_SEQ;

CREATE TABLE sediment_yield (
    id                  NUMBER(38,0) NOT NULL,
    river_id            NUMBER(38,0) NOT NULL,
    grain_fraction_id   NUMBER(38,0),
    unit_id             NUMBER(38,0) NOT NULL,
    time_interval_id    NUMBER(38,0) NOT NULL,
    description         VARCHAR(256),
    PRIMARY KEY (id),
    CONSTRAINT fk_sy_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
    CONSTRAINT fk_sy_grain_fraction_id FOREIGN KEY (grain_fraction_id) REFERENCES grain_fraction(id),
    CONSTRAINT fk_sy_unit_id FOREIGN KEY (unit_id) REFERENCES units(id),
    CONSTRAINT fk_sy_time_interval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id)
);


CREATE SEQUENCE SEDIMENT_YIELD_VALUES_ID_SEQ;

CREATE TABLE sediment_yield_values (
    id                  NUMBER(38,0) NOT NULL,
    sediment_yield_id   NUMBER(38,0) NOT NULL,
    station             NUMBER(38,3) NOT NULL,
    value               NUMBER(38,3) NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT fk_syv_sediment_yield_id FOREIGN KEY (sediment_yield_id) REFERENCES sediment_yield(id)
);


CREATE SEQUENCE WATERLEVEL_ID_SEQ;

CREATE TABLE waterlevel (
    id          NUMBER(38,0) NOT NULL,
    river_id    NUMBER(38,0) NOT NULL,
    unit_id     NUMBER(38,0) NOT NULL,
    description VARCHAR(256),
    PRIMARY KEY (id),
    CONSTRAINT fk_w_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
    CONSTRAINT fk_w_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
);


CREATE SEQUENCE WATERLEVEL_Q_RANGES_ID_SEQ;

CREATE TABLE waterlevel_q_range (
    id              NUMBER(38,0) NOT NULL,
    waterlevel_id   NUMBER(38,0) NOT NULL,
    q               NUMBER(38,2) NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT fk_wqr_waterlevel_id FOREIGN KEY (waterlevel_id) REFERENCES waterlevel(id)
);


CREATE SEQUENCE WATERLEVEL_VALUES_ID_SEQ;

CREATE TABLE waterlevel_values (
    id                      NUMBER(38,0) NOT NULL,
    waterlevel_q_range_id   NUMBER(38,0) NOT NULL,
    station                 NUMBER(38,3) NOT NULL,
    w                       NUMBER(38,2) NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT fk_wv_waterlevel_q_range_id FOREIGN KEY (waterlevel_q_range_id) REFERENCES waterlevel_q_range(id)
);


CREATE SEQUENCE WATERLEVEL_DIFFERENCE_ID_SEQ;

CREATE TABLE waterlevel_difference (
    id          NUMBER(38,0) NOT NULL,
    river_id    NUMBER(38,0) NOT NULL,
    unit_id     NUMBER(38,0) NOT NULL,
    description VARCHAR(256),
    PRIMARY KEY (id),
    CONSTRAINT fk_wd_river_id FOREIGN KEY (river_id) REFERENCES rivers (id),
    CONSTRAINT fk_wd_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
);


CREATE SEQUENCE WATERLEVEL_DIFF_COLUMN_ID_SEQ;

CREATE TABLE waterlevel_difference_column (
    id              NUMBER(38,0) NOT NULL,
    difference_id   NUMBER(38,0) NOT NULL,
    description     VARCHAR(256),
    PRIMARY KEY (id),
    CONSTRAINT fk_wdc_difference_id FOREIGN KEY (difference_id) REFERENCES waterlevel_difference (id)
);


CREATE SEQUENCE WATERLEVEL_DIFF_VALUES_ID_SEQ;

CREATE TABLE waterlevel_difference_values (
    id          NUMBER(38,0) NOT NULL,
    column_id   NUMBER(38,0) NOT NULL,
    station     NUMBER(38,3) NOT NULL,
    value       NUMBER(38,2) NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT fk_wdv_column_id FOREIGN KEY (column_id) REFERENCES waterlevel_difference_column (id)
);


CREATE SEQUENCE SQ_RELATION_ID_SEQ;

CREATE TABLE sq_relation (
    id               NUMBER(38,0) NOT NULL,
    river_id         NUMBER(38,0) NOT NULL,
    time_interval_id NUMBER(38,0) NOT NULL,
    description      VARCHAR(256),
    PRIMARY KEY (id),
    CONSTRAINT fk_sqr_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
    CONSTRAINT fk_sqr_tinterval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id)
);


CREATE SEQUENCE SQ_RELATION_VALUES_ID_SEQ;

CREATE TABLE sq_relation_value (
    id             NUMBER(38,0) NOT NULL,
    sq_relation_id NUMBER(38,0) NOT NULL,
    parameter      VARCHAR(16)  NOT NULL,
    fraction       VARCHAR(32)  NOT NULL,
    function       VARCHAR(32)  NOT NULL,
    km             NUMBER(38,3) NOT NULL,
    a              NUMBER(38, 3) NOT NULL,
    b              NUMBER(38,3) NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT fk_sqr_id FOREIGN KEY (sq_relation_id) REFERENCES sq_relation(id)
);
http://dive4elements.wald.intevation.org