Mercurial > dive4elements > river

comparison flys-backend/doc/schema/oracle-minfo.sql @ 2805:a55b7b35cc3a

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
First things for MINFO specific database schema. flys-backend/trunk@4210 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Ingo Weinzierl <ingo.weinzierl@intevation.de>
date Wed, 11 Apr 2012 09:23:10 +0000
parents
children 5ac1db5156be
comparison
equal deleted inserted replaced
2804:68d1661a57cf 2805:a55b7b35cc3a
1 SET AUTOCOMMIT ON;
2
3 CREATE SEQUENCE LOCATION_SYSTEM_SEQ;
4
5 CREATE TABLE location_system (
6 id NUMBER(38,0) NOT NULL,
7 name VARCHAR(32) NOT NULL,
8 description VARCHAR(255),
9 PRIMARY KEY(id)
10 );
11
12
13 CREATE SEQUENCE ELEVATION_MODEL_SEQ;
14
15 CREATE TABLE elevation_model (
16 id NUMBER(38,0) NOT NULL,
17 name VARCHAR(32) NOT NULL,
18 unit_id NUMBER(38,0) NOT NULL,
19 PRIMARY KEY(id),
20 CONSTRAINT fk_unit FOREIGN KEY (unit_id) REFERENCES units(id)
21 );
22
23 CREATE SEQUENCE BED_HEIGHT_TYPE_SEQ;
24
25 CREATE TABLE bed_height_type (
26 id NUMBER(38,0) NOT NULL,
27 name VARCHAR(16) NOT NULL,
28 description VARCHAR(255),
29 PRIMARY KEY(id)
30 );
31
32
33
34 CREATE SEQUENCE BED_HEIGHT_SINGLE_ID_SEQ;
35
36 CREATE TABLE bed_height_single (
37 id NUMBER(38,0) NOT NULL,
38 year NUMBER(38,0) NOT NULL,
39 sounding_with NUMBER(38,0) NOT NULL,
40 type_id NUMBER(38,0) NOT NULL,
41 cur_elevation_model_id NUMBER(38,0) NOT NULL,
42 old_elevation_model_id NUMBER(38,0),
43 range_id NUMBER(38,0) NOT NULL,
44 evaluation_by VARCHAR(255),
45 description VARCHAR(255),
46 PRIMARY KEY(id),
47 CONSTRAINT fk_type FOREIGN KEY (type_id) REFERENCES bed_height_type(id),
48 CONSTRAINT fk_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id),
49 CONSTRAINT fk_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id),
50 CONSTRAINT fk_range FOREIGN KEY (range_id) REFERENCES ranges(id)
51 );
52
53
54 CREATE SEQUENCE BED_HEIGHT_EPOCH_ID_SEQ;
55
56 CREATE TABLE bed_height_epoch (
57 id NUMBER(38,0) NOT NULL,
58 time_interval_id NUMBER(38,0) NOT NULL,
59 -- sounding_with NUMBER(38,0) NOT NULL,
60 -- type_id NUMBER(38,0) NOT NULL,
61 cur_elevation_model_id NUMBER(38,0) NOT NULL,
62 old_elevation_model_id NUMBER(38,0),
63 range_id NUMBER(38,0) NOT NULL,
64 evaluation_by VARCHAR(255),
65 description VARCHAR(255),
66 PRIMARY KEY(id),
67 CONSTRAINT fk_time_interval FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id),
68 CONSTRAINT fk_epoch_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id),
69 CONSTRAINT fk_epoch_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id),
70 CONSTRAINT fk_epoch_range FOREIGN KEY (range_id) REFERENCES ranges(id)
71 );
72
73
74 CREATE SEQUENCE BED_SINGLE_VALUES_ID_SEQ;
75
76 CREATE TABLE bed_height_single_values (
77 id NUMBER(38,0) NOT NULL,
78 bed_height_single_id NUMBER(38,0) NOT NULL,
79 station NUMBER(38,2) NOT NULL,
80 height NUMBER(38,2),
81 uncertainty NUMBER(38,2),
82 data_gap NUMBER(38,2) NOT NULL,
83 width NUMBER(38,2) NOT NULL,
84 PRIMARY KEY(id),
85 CONSTRAINT fk_bed_single_values_parent FOREIGN KEY (bed_height_single_id) REFERENCES bed_height_single(id)
86 );
87
88
89 CREATE SEQUENCE BED_EPOCH_VALUES_ID_SEQ;
90
91 CREATE TABLE bed_height_epoch_values (
92 id NUMBER(38,0) NOT NULL,
93 bed_height_epoch_id NUMBER(38,0) NOT NULL,
94 station NUMBER(38,2) NOT NULL,
95 height NUMBER(38,2),
96 PRIMARY KEY(id),
97 CONSTRAINT fk_bed_epoch_values_parent FOREIGN KEY (bed_height_epoch_id) REFERENCES bed_height_epoch(id)
98 );
99
http://dive4elements.wald.intevation.org