Mercurial > dive4elements > river

annotate 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
rev   line source
2805
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
1 SET AUTOCOMMIT ON;
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
2
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
3 CREATE SEQUENCE LOCATION_SYSTEM_SEQ;
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
4
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
5 CREATE TABLE location_system (
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
6 id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
7 name VARCHAR(32) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
8 description VARCHAR(255),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
9 PRIMARY KEY(id)
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
10 );
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
11
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
12
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
13 CREATE SEQUENCE ELEVATION_MODEL_SEQ;
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
14
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
15 CREATE TABLE elevation_model (
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
16 id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
17 name VARCHAR(32) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
18 unit_id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
19 PRIMARY KEY(id),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
20 CONSTRAINT fk_unit FOREIGN KEY (unit_id) REFERENCES units(id)
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
21 );
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
22
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
23 CREATE SEQUENCE BED_HEIGHT_TYPE_SEQ;
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
24
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
25 CREATE TABLE bed_height_type (
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
26 id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
27 name VARCHAR(16) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
28 description VARCHAR(255),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
29 PRIMARY KEY(id)
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
30 );
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
31
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
32
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
33
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
34 CREATE SEQUENCE BED_HEIGHT_SINGLE_ID_SEQ;
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
35
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
36 CREATE TABLE bed_height_single (
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
37 id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
38 year NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
39 sounding_with NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
40 type_id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
41 cur_elevation_model_id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
42 old_elevation_model_id NUMBER(38,0),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
43 range_id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
44 evaluation_by VARCHAR(255),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
45 description VARCHAR(255),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
46 PRIMARY KEY(id),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
47 CONSTRAINT fk_type FOREIGN KEY (type_id) REFERENCES bed_height_type(id),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
48 CONSTRAINT fk_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
49 CONSTRAINT fk_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
50 CONSTRAINT fk_range FOREIGN KEY (range_id) REFERENCES ranges(id)
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
51 );
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
52
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
53
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
54 CREATE SEQUENCE BED_HEIGHT_EPOCH_ID_SEQ;
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
55
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
56 CREATE TABLE bed_height_epoch (
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
57 id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
58 time_interval_id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
59 -- sounding_with NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
60 -- type_id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
61 cur_elevation_model_id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
62 old_elevation_model_id NUMBER(38,0),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
63 range_id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
64 evaluation_by VARCHAR(255),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
65 description VARCHAR(255),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
66 PRIMARY KEY(id),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
67 CONSTRAINT fk_time_interval FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
68 CONSTRAINT fk_epoch_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
69 CONSTRAINT fk_epoch_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
70 CONSTRAINT fk_epoch_range FOREIGN KEY (range_id) REFERENCES ranges(id)
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
71 );
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
72
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
73
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
74 CREATE SEQUENCE BED_SINGLE_VALUES_ID_SEQ;
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
75
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
76 CREATE TABLE bed_height_single_values (
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
77 id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
78 bed_height_single_id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
79 station NUMBER(38,2) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
80 height NUMBER(38,2),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
81 uncertainty NUMBER(38,2),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
82 data_gap NUMBER(38,2) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
83 width NUMBER(38,2) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
84 PRIMARY KEY(id),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
85 CONSTRAINT fk_bed_single_values_parent FOREIGN KEY (bed_height_single_id) REFERENCES bed_height_single(id)
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
86 );
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
87
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
88
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
89 CREATE SEQUENCE BED_EPOCH_VALUES_ID_SEQ;
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
90
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
91 CREATE TABLE bed_height_epoch_values (
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
92 id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
93 bed_height_epoch_id NUMBER(38,0) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
94 station NUMBER(38,2) NOT NULL,
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
95 height NUMBER(38,2),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
96 PRIMARY KEY(id),
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
97 CONSTRAINT fk_bed_epoch_values_parent FOREIGN KEY (bed_height_epoch_id) REFERENCES bed_height_epoch(id)
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
98 );
a55b7b35cc3a First things for MINFO specific database schema.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
99
http://dive4elements.wald.intevation.org