mschaefer@9008: SET AUTOCOMMIT ON; mschaefer@8990: mschaefer@8990: mschaefer@9008: --Add new column wsts.sinfo_selection mschaefer@9008: mschaefer@9032: ALTER TABLE wsts ADD ( mschaefer@9032: sinfo_selection CHAR(1) CHECK((sinfo_selection IS NULL) OR (sinfo_selection IN ('W','Q'))) mschaefer@9032: ); mschaefer@9008: COMMENT ON COLUMN wsts.sinfo_selection IS 'Whether and how the WST series is selected within S-INFO calculation types ("W"=without-Q-group, "Q"=with-Q-group, NULL=not-selectable)' ; mschaefer@9008: mschaefer@9008: mschaefer@9038: --Add new columns bed_height.sounding_width_info and bed_height.notes mschaefer@9008: mschaefer@9032: ALTER TABLE bed_height ADD ( mschaefer@9032: sounding_width_info VARCHAR2(256), mschaefer@9038: notes VARCHAR2(256) mschaefer@9032: ); mschaefer@9008: COMMENT ON COLUMN bed_height.sounding_width_info IS 'File header line info "ausgewertete Peilbreite"' ; mschaefer@9038: COMMENT ON COLUMN bed_height.notes IS 'File header line info "weitere Bemerkungen"' ; mschaefer@9008: mschaefer@9008: mschaefer@9008: --Add new columns bed_height_values.min_height (Talweg) and bed_height_values.max_height (Kammlage) (same type as existing height column) mschaefer@9008: mschaefer@9032: ALTER TABLE bed_height_values ADD ( mschaefer@9032: min_height FLOAT(126), mschaefer@9032: max_height FLOAT(126), mschaefer@9032: height01 FLOAT(32), mschaefer@9032: height02 FLOAT(32), mschaefer@9032: height03 FLOAT(32), mschaefer@9032: height04 FLOAT(32), mschaefer@9032: height05 FLOAT(32), mschaefer@9032: height06 FLOAT(32), mschaefer@9032: height07 FLOAT(32), mschaefer@9032: height08 FLOAT(32), mschaefer@9032: height09 FLOAT(32), mschaefer@9032: height10 FLOAT(32) mschaefer@9032: ); mschaefer@9008: COMMENT ON COLUMN bed_height_values.min_height IS 'Minimum bed height (Talweg) in m' ; mschaefer@9008: COMMENT ON COLUMN bed_height_values.max_height IS 'Maximum bed height (Kammlage) in m' ; mschaefer@9032: COMMENT ON COLUMN bed_height_values.height01 IS 'Bed height of section 1 in m' ; mschaefer@9032: COMMENT ON COLUMN bed_height_values.height02 IS 'Bed height of section 2 in m' ; mschaefer@9032: COMMENT ON COLUMN bed_height_values.height03 IS 'Bed height of section 3 in m' ; mschaefer@9032: COMMENT ON COLUMN bed_height_values.height04 IS 'Bed height of section 4 in m' ; mschaefer@9032: COMMENT ON COLUMN bed_height_values.height05 IS 'Bed height of section 5 in m' ; mschaefer@9032: COMMENT ON COLUMN bed_height_values.height06 IS 'Bed height of section 6 in m' ; mschaefer@9032: COMMENT ON COLUMN bed_height_values.height07 IS 'Bed height of section 7 in m' ; mschaefer@9032: COMMENT ON COLUMN bed_height_values.height08 IS 'Bed height of section 8 in m' ; mschaefer@9032: COMMENT ON COLUMN bed_height_values.height09 IS 'Bed height of section 9 in m' ; mschaefer@9032: COMMENT ON COLUMN bed_height_values.height10 IS 'Bed height of section 10 in m' ; mschaefer@9008: mschaefer@9008: mschaefer@9008: --Bed mobility mschaefer@9008: mschaefer@8990: CREATE TABLE bed_mobility ( mschaefer@8990: id NUMBER(9,0) PRIMARY KEY, mschaefer@8990: river_id NUMBER(38,0) NOT NULL CONSTRAINT cBedMobilityRivers REFERENCES rivers(id) ON DELETE CASCADE, mschaefer@8990: kmrange_info VARCHAR2(32), mschaefer@8990: filename VARCHAR2(256) NOT NULL, mschaefer@9038: notes VARCHAR2(256) mschaefer@8990: ); mschaefer@8990: COMMENT ON TABLE bed_mobility IS 'Longitudinal section of the bed mobility of a river' ; mschaefer@8990: COMMENT ON COLUMN bed_mobility.kmrange_info IS 'File header line info "Strecke"' ; mschaefer@8990: COMMENT ON COLUMN bed_mobility.filename IS 'Name without type extension of the imported file' ; mschaefer@9038: COMMENT ON COLUMN bed_mobility.notes IS 'File header line info "weitere Bemerkungen"' ; mschaefer@8990: mschaefer@8990: CREATE SEQUENCE BED_MOBILITY_ID_SEQ ; mschaefer@8990: mschaefer@8990: mschaefer@8990: CREATE TABLE bed_mobility_values ( mschaefer@8990: id NUMBER(9,0) PRIMARY KEY, mschaefer@8990: bed_mobility_id NUMBER(9,0) NOT NULL CONSTRAINT cBedMobilityValuesBedMobility REFERENCES bed_mobility(id) ON DELETE CASCADE, mschaefer@8990: station NUMBER(7,3) NOT NULL, mschaefer@8990: moving NUMBER(1,0) DEFAULT 0 NOT NULL CHECK(moving IN (0,1)) mschaefer@8990: ); mschaefer@8990: COMMENT ON TABLE bed_mobility_values IS 'Bed mobility of a km' ; mschaefer@8990: COMMENT ON COLUMN bed_mobility_values.station IS 'River km' ; mschaefer@8990: COMMENT ON COLUMN bed_mobility_values.moving IS 'Whether the river bed at the station is moving' ; mschaefer@8990: mschaefer@8990: CREATE SEQUENCE BED_MOBILITY_VALUES_ID_SEQ ; mschaefer@8990: mschaefer@8990: mschaefer@9008: --Infrastructure mschaefer@9008: mschaefer@8990: CREATE TABLE infrastructure ( mschaefer@8990: id NUMBER(9,0) PRIMARY KEY, mschaefer@8990: river_id NUMBER(38,0) NOT NULL CONSTRAINT cInfrastructureRivers REFERENCES rivers(id) ON DELETE CASCADE, mschaefer@8990: annotation_type_id NUMBER(38,0) NOT NULL CONSTRAINT cInfrastructureAnnotationType REFERENCES annotation_types(id), mschaefer@8990: year NUMBER(4,0) CHECK((year >= 1700) AND (year <= 2199)), mschaefer@8990: dataprovider VARCHAR2(256), mschaefer@8990: evaluation_by VARCHAR2(256), mschaefer@8990: kmrange_info VARCHAR2(32), mschaefer@8990: filename VARCHAR2(256) NOT NULL, mschaefer@9038: notes VARCHAR2(256) mschaefer@8990: ); mschaefer@8990: COMMENT ON TABLE infrastructure IS 'Longitudinal section of infrastructures of a river and a type' ; mschaefer@8990: COMMENT ON COLUMN infrastructure.year IS 'File header line info "Stand"' ; mschaefer@8990: COMMENT ON COLUMN infrastructure.dataprovider IS 'File header line info "Datenherkunft"' ; mschaefer@8990: COMMENT ON COLUMN infrastructure.evaluation_by IS 'File header line info "Auswerter"' ; mschaefer@8990: COMMENT ON COLUMN infrastructure.kmrange_info IS 'File header line info "Strecke"' ; mschaefer@8990: COMMENT ON COLUMN infrastructure.filename IS 'Name without type extension of the imported file' ; mschaefer@9038: COMMENT ON COLUMN infrastructure.notes IS 'File header line info "weitere Bemerkungen"' ; mschaefer@8990: mschaefer@8990: CREATE SEQUENCE INFRASTRUCTURE_ID_SEQ ; mschaefer@8990: mschaefer@8990: mschaefer@8990: CREATE TABLE infrastructure_values ( mschaefer@8990: id NUMBER(9,0) PRIMARY KEY, mschaefer@9032: infrastructure_id NUMBER(9,0) CONSTRAINT cInfrastructureValuesInfrastru REFERENCES infrastructure(id) ON DELETE CASCADE, mschaefer@8990: station NUMBER(7,3) NOT NULL, mschaefer@9032: attribute_id NUMBER(38,0) CONSTRAINT cInfrastructureValuesAttrib REFERENCES attributes(id), mschaefer@8990: height NUMBER(6,2) mschaefer@8990: ); mschaefer@8990: COMMENT ON TABLE infrastructure_values IS 'Infrastructure at a river station' ; mschaefer@8990: COMMENT ON COLUMN infrastructure_values.station IS 'River km' ; mschaefer@8990: COMMENT ON COLUMN infrastructure_values.height IS 'Geodetic height of the top of the infrastructure in m' ; mschaefer@8990: mschaefer@8990: CREATE SEQUENCE INFRASTRUCTURE_VALUES_ID_SEQ ; mschaefer@8990: mschaefer@8990: mschaefer@9008: --Channel mschaefer@9008: mschaefer@8990: CREATE TABLE channel ( mschaefer@8990: id NUMBER(9,0) PRIMARY KEY, mschaefer@8990: river_id NUMBER(38,0) NOT NULL CONSTRAINT cChannelRivers REFERENCES rivers(id) ON DELETE CASCADE, mschaefer@8990: kmrange_info VARCHAR2(32), mschaefer@8990: filename VARCHAR2(256) NOT NULL, mschaefer@9038: notes VARCHAR2(256), mschaefer@8990: year_from NUMBER(4,0), mschaefer@8990: year_to NUMBER(4,0) mschaefer@8990: ); mschaefer@8990: COMMENT ON TABLE channel IS 'Longitudinal section of the navigable channel of a river' ; mschaefer@8990: COMMENT ON COLUMN channel.kmrange_info IS 'File header line info "Strecke"' ; mschaefer@8990: COMMENT ON COLUMN channel.filename IS 'Name without type extension of the imported file' ; mschaefer@9038: COMMENT ON COLUMN channel.notes IS 'File header line info "weitere Bemerkungen"' ; mschaefer@8990: COMMENT ON COLUMN channel.year_from IS 'Start year of the period for which the channel values are valid, NULL when valid for the whole past'; mschaefer@8990: COMMENT ON COLUMN channel.year_to IS 'End year of the period for which the channel values are valid, NULL when unlimited'; mschaefer@8990: mschaefer@8990: mschaefer@8990: CREATE SEQUENCE CHANNEL_ID_SEQ ; mschaefer@8990: mschaefer@8990: mschaefer@8990: CREATE TABLE channel_values ( mschaefer@8990: id NUMBER(9,0) PRIMARY KEY, mschaefer@8990: channel_id NUMBER(9,0) CONSTRAINT cChannelValuesChannel REFERENCES channel(id) ON DELETE CASCADE, mschaefer@8990: station NUMBER(7,3) NOT NULL, mschaefer@8990: width NUMBER(6,2), mschaefer@8990: depth NUMBER(6,2) mschaefer@8990: ); mschaefer@8990: COMMENT ON TABLE channel_values IS 'Nominal size of the navigable channel at a river station' ; mschaefer@8990: COMMENT ON COLUMN channel_values.station IS 'River km' ; mschaefer@8990: COMMENT ON COLUMN channel_values.width IS 'Nominal width of the channel in m' ; mschaefer@8990: COMMENT ON COLUMN channel_values.depth IS 'Nominal depth of the channel in m' ; mschaefer@8990: mschaefer@8990: CREATE SEQUENCE CHANNEL_VALUES_ID_SEQ ; mschaefer@8990: mschaefer@8990: mschaefer@9008: --Collision mschaefer@9008: mschaefer@8990: CREATE TABLE collision_type ( mschaefer@8990: id NUMBER(9,0) PRIMARY KEY, mschaefer@8990: name VARCHAR2(64) NOT NULL UNIQUE mschaefer@8990: ); mschaefer@8990: COMMENT ON TABLE collision_type IS 'Type of a ship collision' ; mschaefer@8990: COMMENT ON COLUMN collision_type.name IS 'Name of the collision type' ; mschaefer@8990: mschaefer@8990: CREATE SEQUENCE COLLISION_TYPE_ID_SEQ ; mschaefer@8990: mschaefer@8990: mschaefer@8990: CREATE TABLE collision ( mschaefer@8990: id NUMBER(9,0) PRIMARY KEY, mschaefer@8990: river_id NUMBER(38,0) NOT NULL CONSTRAINT cCollisionRivers REFERENCES rivers(id) ON DELETE CASCADE, mschaefer@8990: year NUMBER(4,0) NOT NULL, mschaefer@8990: kmrange_info VARCHAR2(32), mschaefer@8990: filename VARCHAR2(256) NOT NULL, mschaefer@9038: notes VARCHAR2(256) mschaefer@8990: ); mschaefer@8990: COMMENT ON TABLE collision IS 'Longitudinal section of ship collisions with the river bed' ; mschaefer@8990: COMMENT ON COLUMN collision.year IS 'File header line info "Jahr"' ; mschaefer@8990: COMMENT ON COLUMN collision.kmrange_info IS 'File header line info "Strecke"' ; mschaefer@8990: COMMENT ON COLUMN collision.filename IS 'Name without type extension of the imported file' ; mschaefer@9038: COMMENT ON COLUMN collision.notes IS 'File header line info "weitere Bemerkungen"' ; mschaefer@8990: mschaefer@8990: CREATE SEQUENCE COLLISION_ID_SEQ ; mschaefer@8990: mschaefer@8990: mschaefer@8990: CREATE TABLE collision_values ( mschaefer@8990: id NUMBER(9,0) PRIMARY KEY, mschaefer@8990: collision_id NUMBER(9,0) NOT NULL CONSTRAINT cCollisionValueCollision REFERENCES collision(id) ON DELETE CASCADE, mschaefer@8990: station NUMBER(7,3) NOT NULL, mschaefer@8990: event_date DATE NOT NULL, mschaefer@8990: gauge_w NUMBER(6,2), mschaefer@8990: gauge_name VARCHAR2(64), mschaefer@8990: collision_type_id NUMBER(9,0) NOT NULL CONSTRAINT cCollisionValuesCollisionType REFERENCES collision_type(id) mschaefer@8990: ); mschaefer@8990: COMMENT ON TABLE collision_values IS 'Collision event' ; mschaefer@8990: COMMENT ON COLUMN collision_values.station IS 'River km' ; mschaefer@8990: COMMENT ON COLUMN collision_values.event_date IS 'Date of the collision' ; mschaefer@8990: COMMENT ON COLUMN collision_values.gauge_w IS 'Waterlevel during the collision' ; mschaefer@8990: COMMENT ON COLUMN collision_values.gauge_name IS 'Name of the gauge' ; mschaefer@8990: mschaefer@8990: CREATE SEQUENCE COLLISION_VALUES_ID_SEQ ; mschaefer@8990: mschaefer@8990: mschaefer@9008: --Tkh mschaefer@9008: mschaefer@8990: CREATE TABLE tkh ( mschaefer@8990: id NUMBER(9,0) PRIMARY KEY, mschaefer@8990: river_id NUMBER(38,0) NOT NULL CONSTRAINT cTkhRivers REFERENCES rivers(id) ON DELETE CASCADE, mschaefer@9008: day DATE, mschaefer@8990: kmrange_info VARCHAR2(32), mschaefer@8990: filename VARCHAR2(256) NOT NULL, mschaefer@9032: groupname VARCHAR2(32) NOT NULL CHECK(groupname IN ('messung','berechnung')), mschaefer@8990: sounding_info VARCHAR2(64), mschaefer@8990: evaluation_by VARCHAR2(256), mschaefer@9038: notes VARCHAR2(256) mschaefer@8990: ); mschaefer@9032: COMMENT ON TABLE tkh IS 'Longitudinal section of computed Transportkoerperhoehe of a river' ; mschaefer@9008: COMMENT ON COLUMN tkh.day IS 'File header line info "Datum"' ; mschaefer@8990: COMMENT ON COLUMN tkh.kmrange_info IS 'File header line info "Strecke"' ; mschaefer@8990: COMMENT ON COLUMN tkh.filename IS 'Name without type extension of the imported file' ; mschaefer@9032: COMMENT ON COLUMN tkh.groupname IS 'Group the file belongs to' ; mschaefer@8990: COMMENT ON COLUMN tkh.sounding_info IS 'File header line info "Peilung"' ; mschaefer@8990: COMMENT ON COLUMN tkh.evaluation_by IS 'File header line info "Auswerter"' ; mschaefer@9038: COMMENT ON COLUMN tkh.notes IS 'File header line info "weitere Bemerkungen"' ; mschaefer@8990: mschaefer@8990: CREATE SEQUENCE TKH_ID_SEQ ; mschaefer@8990: mschaefer@8990: mschaefer@8990: CREATE TABLE tkh_column ( mschaefer@8990: id NUMBER(9,0) PRIMARY KEY, mschaefer@8990: tkh_id NUMBER(9,0) NOT NULL CONSTRAINT cTkhColumnTkh REFERENCES tkh(id) ON DELETE CASCADE, mschaefer@8990: name VARCHAR2(64) NOT NULL mschaefer@8990: ); mschaefer@9032: COMMENT ON TABLE tkh_column IS 'Longitudinal section of computed Transportkoerperhoehe for a waterlevel series' ; mschaefer@8990: COMMENT ON COLUMN tkh_column.name IS 'Name of the tkh computation column' ; mschaefer@8990: mschaefer@8990: CREATE SEQUENCE TKH_COLUMN_ID_SEQ ; mschaefer@8990: mschaefer@8990: mschaefer@8990: CREATE TABLE tkh_values ( mschaefer@8990: id NUMBER(9,0) PRIMARY KEY, mschaefer@8990: tkh_column_id NUMBER(9,0) NOT NULL CONSTRAINT cTkhValuesTkhColumn REFERENCES tkh_column(id) ON DELETE CASCADE, mschaefer@8990: station NUMBER(7,3) NOT NULL, mschaefer@8990: tkheight NUMBER(7,3) mschaefer@8990: ); mschaefer@9032: COMMENT ON TABLE tkh_values IS 'Transportkoerperhoehe of a river station and referenced file column' ; mschaefer@8990: COMMENT ON COLUMN tkh_values.station IS 'River km' ; mschaefer@9032: COMMENT ON COLUMN tkh_values.tkheight IS 'Transportkoerperhoehe of a river station computed for a waterlevel in m' ; mschaefer@8990: mschaefer@8990: CREATE SEQUENCE TKH_VALUES_ID_SEQ ; mschaefer@8990: mschaefer@8990: mschaefer@9008: --Flow depth mschaefer@9008: mschaefer@8990: CREATE TABLE flow_depth ( mschaefer@8990: id NUMBER(9,0) PRIMARY KEY, mschaefer@8990: river_id NUMBER(38,0) NOT NULL CONSTRAINT cFlowDepthRivers REFERENCES rivers(id) ON DELETE CASCADE, mschaefer@8990: year NUMBER(4,0) NOT NULL, mschaefer@8990: kmrange_info VARCHAR2(32), mschaefer@8990: filename VARCHAR2(256) NOT NULL, mschaefer@8990: sounding_info VARCHAR2(64), mschaefer@8990: evaluation_by VARCHAR2(255), mschaefer@9038: notes VARCHAR2(256) mschaefer@8990: ); mschaefer@8990: mschaefer@8990: COMMENT ON TABLE flow_depth IS 'Longitudinal section of computed flow depth of a river' ; mschaefer@8990: COMMENT ON COLUMN flow_depth.year IS 'File header line info "Bezugsjahr"' ; mschaefer@8990: COMMENT ON COLUMN flow_depth.kmrange_info IS 'File header line info "Strecke"' ; mschaefer@8990: COMMENT ON COLUMN flow_depth.filename IS 'Name without type extension of the imported file' ; mschaefer@8990: COMMENT ON COLUMN flow_depth.sounding_info IS 'File header line info "Peilung"' ; mschaefer@8990: COMMENT ON COLUMN flow_depth.evaluation_by IS 'File header line info "Auswerter"' ; mschaefer@9038: COMMENT ON COLUMN flow_depth.notes IS 'File header line info "weitere Bemerkungen"' ; mschaefer@8990: mschaefer@8990: CREATE SEQUENCE FLOW_DEPTH_ID_SEQ ; mschaefer@8990: mschaefer@8990: mschaefer@8990: CREATE TABLE flow_depth_column ( mschaefer@8990: id NUMBER(9,0) PRIMARY KEY, mschaefer@8990: flow_depth_id NUMBER(9,0) NOT NULL CONSTRAINT cFlowDepthColumnFlowDepth REFERENCES flow_depth(id) ON DELETE CASCADE, mschaefer@8990: name VARCHAR2(64) NOT NULL mschaefer@8990: ); mschaefer@8990: mschaefer@8990: COMMENT ON TABLE flow_depth_column IS 'Longitudinal section of computed flow depth for a waterlevel series' ; mschaefer@8990: COMMENT ON COLUMN flow_depth_column.name IS 'Name of the flow depth computation column' ; mschaefer@8990: mschaefer@8990: CREATE SEQUENCE FLOW_DEPTH_COLUMN_ID_SEQ ; mschaefer@8990: mschaefer@8990: mschaefer@8990: CREATE TABLE flow_depth_values ( mschaefer@8990: id NUMBER(9,0) PRIMARY KEY, mschaefer@9032: flow_depth_column_id NUMBER(9,0) NOT NULL CONSTRAINT cFlowDepthValuesFlowDepthCol REFERENCES flow_depth_column(id) ON DELETE CASCADE, mschaefer@8990: station NUMBER(7,3) NOT NULL, mschaefer@8990: depth NUMBER(7,3) mschaefer@8990: ); mschaefer@8990: mschaefer@8990: COMMENT ON TABLE flow_depth_values IS 'Flow depth of a river station and referenced file column' ; mschaefer@8990: COMMENT ON COLUMN flow_depth_values.station IS 'River km' ; mschaefer@8990: COMMENT ON COLUMN flow_depth_values.depth IS 'Flow depth of a river station computed for a waterlevel in m' ; mschaefer@8990: mschaefer@8990: CREATE SEQUENCE FLOW_DEPTH_VALUES_ID_SEQ ; mschaefer@8990: mschaefer@8990: mschaefer@9008: --Depth evolution mschaefer@9008: mschaefer@8990: CREATE TABLE depth_evolution ( mschaefer@8990: id NUMBER(9,0) PRIMARY KEY, mschaefer@8990: river_id NUMBER(38,0) NOT NULL CONSTRAINT cDepthEvolutionRivers REFERENCES rivers(id) ON DELETE CASCADE, mschaefer@8990: reference_year NUMBER(4,0) NOT NULL, mschaefer@8990: start_year NUMBER(4,0) NOT NULL, mschaefer@8990: curr_sounding VARCHAR2(64) NOT NULL, mschaefer@8990: old_sounding VARCHAR2(64) NOT NULL, mschaefer@8990: kmrange_info VARCHAR2(32), mschaefer@8990: curr_glw VARCHAR2(64) NOT NULL, mschaefer@8990: old_glw VARCHAR2(64) NOT NULL, mschaefer@8990: filename VARCHAR2(256) NOT NULL, mschaefer@9032: groupname VARCHAR2(32) NOT NULL CHECK(groupname IN ('aktuell','etappe')), mschaefer@9038: notes VARCHAR2(256) mschaefer@8990: ); mschaefer@8990: COMMENT ON TABLE depth_evolution IS 'Longitudinal section of the evolution of the flow depth of a river' ; mschaefer@8990: COMMENT ON COLUMN depth_evolution.reference_year IS 'File header line info "Bezugsjahr" (GlW)' ; mschaefer@8990: COMMENT ON COLUMN depth_evolution.start_year IS 'File header line info "Ausgangsjahr" (GlW)' ; mschaefer@8990: COMMENT ON COLUMN depth_evolution.curr_sounding IS 'File header line info "Aktuelle Peilung / Epoche"' ; mschaefer@8990: COMMENT ON COLUMN depth_evolution.old_sounding IS 'File header line info "Historische Peilung / Epoche"' ; mschaefer@8990: COMMENT ON COLUMN depth_evolution.kmrange_info IS 'File header line info "Strecke"' ; mschaefer@8990: COMMENT ON COLUMN depth_evolution.curr_glw IS 'File header line info "Aktuelle Wasserspiegellage"' ; mschaefer@8990: COMMENT ON COLUMN depth_evolution.old_glw IS 'File header line info "Historische Wasserspiegellage"' ; mschaefer@8990: COMMENT ON COLUMN depth_evolution.filename IS 'Name without type extension of the imported file' ; mschaefer@9032: COMMENT ON COLUMN depth_evolution.groupname IS 'Group the file belongs to' ; mschaefer@9038: COMMENT ON COLUMN depth_evolution.notes IS 'File header line info "weitere Bemerkungen"' ; mschaefer@8990: mschaefer@8990: CREATE SEQUENCE DEPTH_EVOLUTION_ID_SEQ ; mschaefer@8990: mschaefer@8990: mschaefer@8990: CREATE TABLE depth_evolution_values ( mschaefer@8990: id NUMBER(9,0) PRIMARY KEY, mschaefer@9032: depth_evolution_id NUMBER(9,0) NOT NULL CONSTRAINT cDepthEvolutionValuesDepthEvol REFERENCES depth_evolution(id) ON DELETE CASCADE, mschaefer@8990: station NUMBER(7,3) NOT NULL, mschaefer@8990: total_change NUMBER(8,4), mschaefer@8990: change_per_year NUMBER(8,4) mschaefer@8990: ); mschaefer@8990: COMMENT ON TABLE depth_evolution_values IS 'Evolution of the flow depth of a river station in a referenced period of time' ; mschaefer@8990: COMMENT ON COLUMN depth_evolution_values.station IS 'River km' ; mschaefer@8990: COMMENT ON COLUMN depth_evolution_values.total_change IS 'Flow depth change of the whole period of time in m' ; mschaefer@8990: COMMENT ON COLUMN depth_evolution_values.change_per_year IS 'Average yearly flow depth change in the referenced period of time in m' ; mschaefer@8990: mschaefer@8990: CREATE SEQUENCE DEPTH_EVOLUTION_VALUES_ID_SEQ ; mschaefer@8990: mschaefer@8990: mschaefer@9008: --Daily discharge mschaefer@9008: mschaefer@8990: CREATE TABLE daily_discharge ( mschaefer@8990: id NUMBER(9,0) PRIMARY KEY, mschaefer@8990: gauge_id NUMBER(38,0) NOT NULL CONSTRAINT cDailyDischargeGauge REFERENCES gauges(id) ON DELETE CASCADE, mschaefer@8990: filename VARCHAR2(256) NOT NULL mschaefer@8990: ); mschaefer@8990: mschaefer@8990: COMMENT ON TABLE daily_discharge IS 'Daily discharge value series' ; mschaefer@8990: COMMENT ON COLUMN daily_discharge.filename IS 'Name without type extension of the imported file' ; mschaefer@8990: mschaefer@8990: CREATE SEQUENCE DAILY_DISCHARGE_ID_SEQ ; mschaefer@8990: mschaefer@8990: mschaefer@8990: CREATE TABLE daily_discharge_values ( mschaefer@8990: id NUMBER(9,0) PRIMARY KEY, mschaefer@9032: daily_discharge_id NUMBER(9,0) NOT NULL CONSTRAINT cDailyDischargeValueDailyDisch REFERENCES daily_discharge(id) ON DELETE CASCADE, mschaefer@8990: day DATE NOT NULL, mschaefer@8990: discharge NUMBER(8,3) NOT NULL mschaefer@8990: ); mschaefer@8990: mschaefer@8990: COMMENT ON TABLE daily_discharge_values IS 'Mean daily discharge of a date and a gauge' ; mschaefer@8990: COMMENT ON COLUMN daily_discharge_values.day IS 'Date of the discharge value' ; mschaefer@8990: COMMENT ON COLUMN daily_discharge_values.discharge IS 'Mean daily discharge in m^3/s' ; mschaefer@8990: mschaefer@8990: CREATE SEQUENCE DAILY_DISCHARGE_VALUES_ID_SEQ ; mschaefer@8990: mschaefer@8990: mschaefer@9008: --Salix line mschaefer@9008: mschaefer@8990: CREATE TABLE salix ( mschaefer@8990: id NUMBER(9,0) PRIMARY KEY, mschaefer@8990: river_id NUMBER(38,0) NOT NULL CONSTRAINT cSalixRivers REFERENCES rivers(id) ON DELETE CASCADE, mschaefer@8990: evaluation_by VARCHAR2(255), mschaefer@8990: kmrange_info VARCHAR2(32), mschaefer@8990: filename VARCHAR2(256) NOT NULL, mschaefer@9038: notes VARCHAR2(256) mschaefer@8990: ); mschaefer@8990: mschaefer@8990: COMMENT ON TABLE salix IS 'Longitudinal section of the salix line parameters of a river' ; mschaefer@8990: COMMENT ON COLUMN salix.evaluation_by IS 'File header line info "Auswerter"' ; mschaefer@8990: COMMENT ON COLUMN salix.kmrange_info IS 'File header line info "Strecke"' ; mschaefer@8990: COMMENT ON COLUMN salix.filename IS 'Name without type extension of the imported file' ; mschaefer@9038: COMMENT ON COLUMN salix.notes IS 'File header line info "weitere Bemerkungen"' ; mschaefer@8990: mschaefer@8990: CREATE SEQUENCE SALIX_ID_SEQ ; mschaefer@8990: mschaefer@8990: mschaefer@8990: CREATE TABLE salix_values ( mschaefer@8990: id NUMBER(9,0) PRIMARY KEY, mschaefer@8990: salix_id NUMBER(9,0) NOT NULL CONSTRAINT cSalixValuesSalix REFERENCES salix(id) ON DELETE CASCADE, mschaefer@8990: station NUMBER(7,3) NOT NULL, mschaefer@8990: factor NUMBER(6,2) NOT NULL, mschaefer@8990: mnw_mw_diff NUMBER(6,2) mschaefer@8990: ); mschaefer@8990: mschaefer@8990: COMMENT ON COLUMN salix_values.station IS 'River km' ; mschaefer@8990: COMMENT ON COLUMN salix_values.factor IS 'Salix "factor" of the station in m' ; mschaefer@8990: COMMENT ON COLUMN salix_values.mnw_mw_diff IS 'Difference between MNW and MW in m (less than zero)' ; mschaefer@8990: mschaefer@8990: CREATE SEQUENCE SALIX_VALUES_ID_SEQ ; mschaefer@8990: mschaefer@8990: mschaefer@8990: CREATE TABLE salix_rank ( mschaefer@8990: id NUMBER(9,0) PRIMARY KEY, mschaefer@8990: min_value NUMBER(6,2), mschaefer@8990: max_value NUMBER(6,2), mschaefer@8990: name VARCHAR2(16) NOT NULL mschaefer@8990: ); mschaefer@8990: INSERT INTO salix_rank (id, min_value, max_value, name) VALUES (1, NULL, -0.3, 'invalid'); mschaefer@8990: INSERT INTO salix_rank (id, min_value, max_value, name) VALUES (2, -0.3, 0.3, 'very good'); mschaefer@8990: INSERT INTO salix_rank (id, min_value, max_value, name) VALUES (3, 0.3, 0.5, 'good'); mschaefer@8990: INSERT INTO salix_rank (id, min_value, max_value, name) VALUES (4, 0.5, 1.0, 'moderate'); mschaefer@8990: INSERT INTO salix_rank (id, min_value, max_value, name) VALUES (5, 1.0, 1.5, 'bad'); mschaefer@8990: INSERT INTO salix_rank (id, min_value, max_value, name) VALUES (6, 1.5, NULL, 'very bad'); mschaefer@8990: mschaefer@8990: mschaefer@9008: --Vegetation zone mschaefer@9008: mschaefer@8990: CREATE TABLE vegetation_type ( mschaefer@8990: id NUMBER(9,0) PRIMARY KEY, mschaefer@8990: name VARCHAR2(256) NOT NULL mschaefer@8990: ); mschaefer@8990: INSERT INTO vegetation_type (id, name) VALUES (1, 'zonal forest'); mschaefer@8990: INSERT INTO vegetation_type (id, name) VALUES (2, 'dry hartwood forest floodplain'); mschaefer@8990: INSERT INTO vegetation_type (id, name) VALUES (3, 'wet hartwood forest floodplain'); mschaefer@8990: INSERT INTO vegetation_type (id, name) VALUES (4, 'salix alba forest'); mschaefer@8990: INSERT INTO vegetation_type (id, name) VALUES (5, 'salix shrubs'); mschaefer@8990: INSERT INTO vegetation_type (id, name) VALUES (6, 'reed bed'); mschaefer@8990: INSERT INTO vegetation_type (id, name) VALUES (7, 'bank pioneers'); mschaefer@8990: INSERT INTO vegetation_type (id, name) VALUES (8, 'no vegetation'); mschaefer@8990: INSERT INTO vegetation_type (id, name) VALUES (9, 'water'); mschaefer@8990: mschaefer@8990: mschaefer@8990: CREATE TABLE vegetation ( mschaefer@8990: id NUMBER(9,0) PRIMARY KEY, mschaefer@8990: river_id NUMBER(38,0) NOT NULL CONSTRAINT cVegetationRivers REFERENCES rivers(id) ON DELETE CASCADE, mschaefer@8990: name VARCHAR2(256), mschaefer@8990: filename VARCHAR2(256) NOT NULL, mschaefer@9038: notes VARCHAR2(256) mschaefer@8990: ); mschaefer@8990: mschaefer@8990: COMMENT ON TABLE vegetation IS 'Classification of the vegetation of a river' ; mschaefer@8990: COMMENT ON COLUMN vegetation.name IS 'File header line info "Einteilung"' ; mschaefer@8990: COMMENT ON COLUMN vegetation.filename IS 'Name without type extension of the imported file' ; mschaefer@9038: COMMENT ON COLUMN vegetation.notes IS 'File header line info "weitere Bemerkungen"' ; mschaefer@8990: mschaefer@8990: CREATE SEQUENCE VEGETATION_ID_SEQ ; mschaefer@8990: mschaefer@8990: mschaefer@8990: CREATE TABLE vegetation_zone ( mschaefer@8990: id NUMBER(9,0) PRIMARY KEY, mschaefer@8990: vegetation_id NUMBER(9,0) NOT NULL CONSTRAINT cVegetationZoneVegetation REFERENCES vegetation(id) ON DELETE CASCADE, mschaefer@8990: vegetation_type_id NUMBER(9,0) NOT NULL CONSTRAINT cVegetationZoneVegetationType REFERENCES vegetation_type(id) ON DELETE CASCADE, mschaefer@8990: min_overflow_days NUMBER(3,0) NOT NULL, mschaefer@9401: max_overflow_days NUMBER(3,0) NOT NULL, mschaefer@9401: color_r NUMBER(3,0) NOT NULL, mschaefer@9401: color_g NUMBER(3,0) NOT NULL, mschaefer@9401: color_b NUMBER(3,0) NOT NULL mschaefer@8990: ); mschaefer@8990: mschaefer@8990: COMMENT ON TABLE vegetation_zone IS 'Vegetation zone of a river station' ; mschaefer@8990: COMMENT ON COLUMN vegetation_zone.min_overflow_days IS 'Minimum number (inclusive) of overflow days in a year for the zone type' ; mschaefer@9401: COMMENT ON COLUMN vegetation_zone.max_overflow_days IS 'Maximum number (inclusive) of overflow days in a year for the zone type' ; mschaefer@9401: COMMENT ON COLUMN vegetation_zone.color_r IS 'Red value (0-255) of the zone color' ; mschaefer@9401: COMMENT ON COLUMN vegetation_zone.color_g IS 'Green value (0-255) of the zone color' ; mschaefer@9401: COMMENT ON COLUMN vegetation_zone.color_b IS 'Blue value (0-255) of the zone color' ; mschaefer@8990: mschaefer@8990: CREATE SEQUENCE VEGETATION_ZONE_ID_SEQ ;