Wiki source code of Database schema

Last modified by Pasi Kolari on 2024/04/17 16:25
Show last authors
1
2
3 [[SQL export of database structure>>attach:Dump20150216.sql]] as of 16 Feb 2015.
4
5 === Data description tables ===
6
7
8 [[image:attach:smear_schema.PNG||height="472" width="1015"]]
9
10
11 ==== Stations ====
12
13 Example:
14
15 (% class="wrapped" %)
16 |=(((
17 stationID
18 )))|=(((
19 dcmipoint
20 )))|=(((
21 name
22 )))|=(((
23
24 )))
25 |(((
26 2
27 )))|(((
28 name=Hyytiälä; east=24.294795; north=61.847463; elevation=179
29 )))|(((
30 Hyytiälä
31 )))|(((
32
33 )))
34
35 ==== Tags ====
36
37 Tags are used to link variables/columns to vocabularies or other standards.
38
39 Example:
40
41 (% class="wrapped" %)
42 |=(((
43 tagID
44 )))|=(((
45 vocabulary
46 )))|=(((
47 tag
48 )))|=(((
49 displaykeyword
50 )))
51 |(((
52 1
53 )))|(((
54 [[http:~~/~~/cf-pcmdi.llnl.gov/documents/cf-standard-names/standard-name-table/23/cf-standard-name-table.html#>>url:http://cf-pcmdi.llnl.gov/documents/cf-standard-names/standard-name-table/23/cf-standard-name-table.html||rel="nofollow" shape="rect" class="external-link"]]
55 )))|(((
56 air_temperature
57 )))|(((
58 ilma - lämpötila
59 )))
60 |(((
61 2
62 )))|(((
63 [[http:~~/~~/www.yso.fi/onto/yso/>>url:http://www.yso.fi/onto/yso/||rel="nofollow" shape="rect" class="external-link"]]
64 )))|(((
65 p19483
66 )))|(((
67 ilma - lämpötila
68 )))
69
70 (% style="font-size:10.0pt; line-height:13.0pt" %)VariableTags are used to link tags to columns.
71
72
73 ==== Events ====
74
75 (% style="font-size:10.0pt; line-height:13.0pt" %)Changes in data time series can be described with Events. They are linked to variables/columns via variableEvents that combine eventID and variableID.
76
77 (% style="font-size:10.0pt; line-height:13.0pt" %)Examples from some events linked to columns in table VAR_TREE:
78
79 (% class="wrapped" %)
80 |=(((
81 eventID
82 )))|=(((
83 event_type
84 )))|=(((
85 event
86 )))|=(% colspan="1" %)(% colspan="1" %)
87 (((
88 period_start
89 )))|=(% colspan="1" %)(% colspan="1" %)
90 (((
91 period_end
92 )))|=(% colspan="1" %)(% colspan="1" %)
93 (((
94 who
95 )))|=(((
96 etimestamp
97 )))
98 |(((
99 1
100 )))|(((
101 ObjectChange
102 )))|(((
103 2001-04-23 chambers installed on new pine shoots
104 )))|(% colspan="1" %)(% colspan="1" %)
105 (((
106 2001-04-23 00:00:00
107 )))|(% colspan="1" %)(% colspan="1" %)
108 (((
109 2001-10-05 00:00:00
110 )))|(% colspan="1" %)(% colspan="1" %)
111 (((
112 Pertti Hari
113 )))|(((
114 2014-11-24 10:17:49
115
116
117 )))
118 |(% colspan="1" %)(% colspan="1" %)
119 (((
120 19
121 )))|(% colspan="1" %)(% colspan="1" %)
122 (((
123 InstrumentConfiguration
124
125
126
127 )))|(% colspan="1" %)(% colspan="1" %)
128 (((
129 URAS 3 CO2 and H2O analyzers changed to Li-Cor Li-840
130 )))|(% colspan="1" %)(% colspan="1" %)
131 (((
132 2010-04-16 00:00:00
133 )))|(% colspan="1" %)(% colspan="1" %)
134 (((
135 NULL
136 )))|(% colspan="1" %)(% colspan="1" %)
137 (((
138 Toivo Pohja,
139 Petri Keronen
140 )))|(% colspan="1" %)(% colspan="1" %)
141 (((
142 2014-11-14 08:54:34
143 )))
144 |(% colspan="1" %)(% colspan="1" %)
145 (((
146 14
147 )))|(% colspan="1" %)(% colspan="1" %)
148 (((
149 NewVersionRelease
150 )))|(% colspan="1" %)(% colspan="1" %)
151 (((
152 Post-calibration correction to 1999-2008 H2O concentration and transpiration data using tower RH vs recently calibrated chamber H2O analyser regression
153 )))|(% colspan="1" %)(% colspan="1" %)
154 (((
155 1999-05-07 00:00:00
156 )))|(% colspan="1" %)(% colspan="1" %)
157 (((
158 2009-01-01 00:00:00
159 )))|(% colspan="1" %)(% colspan="1" %)
160 (((
161 Tapani Hopkins,
162 Pasi Kolari
163 )))|(% colspan="1" %)(% colspan="1" %)
164 (((
165 2014-06-16 12:00:00
166 )))
167
168
169 Fields period_start and period_end indicate the time frame when the event affects the data. Period_end may be left empty which means that the event is still "on". New event may or may not override older event of the same type even if period_end of the previous event is not determined, some responsibility for event interpretation is left to the data user...
170
171 Field etimestamp indicates when the event was inserted to the database or (in case of NewVersionRelease) when the data was updated.
172
173 It is possible to indicate the data life cycle events relevant to the database with very small number of event types:
174
175 ~1. Changes in data collection
176
177 a) change in the object being measured without setting up new column in the database, for instance, moving a sensor to another location in the forest (in many atmospheric/meteorological variables small horizontal movements are irrelevant and often not reported at all) or moving shoot chamber x to another pine shoot
178 b) sensor/instrument change or other significant change in the measurement configuration
179 c) calibration
180
181 2. Changes in data processing
182
183 a) change in data processing from period_start on while keeping older data as is, for instance, soil temperature until 2004 was calculated as average of 4 sensors, since 2005 average of 5 sensors
184 b) reprocessing of data, for instance, applying different quality check.
185 \\Corresponding event types:
186 1a) ObjectChange
187 1b) InstrumentConfiguration
188 1c) Calibration
189 2a) DataProcessing
190 2b) NewVersionRelease
191
192 The vocabulary is based on DDI data lifecycle events and ENVRI reference model.