WorkingGrid/DataProviderSetup/PMLOct06: ndg_db.sql

File ndg_db.sql, 15.0 KB (added by mggr, 13 years ago)

NDG MOLES db schema, version 5

Line 
1-- NDG Database Schema
2--
3-- Author(s):  mggr, mhen
4-- Date:       2007-07-19
5--
6-- SQL file describing the ndg schema
7
8
9--\connect - postgres
10
11CREATE SCHEMA ndg;
12REVOKE ALL ON SCHEMA ndg FROM PUBLIC;
13REVOKE ALL ON SCHEMA ndg FROM postgres;
14
15--\connect - rsguser
16
17SET search_path = public, ndg, pg_catalog;
18
19
20CREATE TABLE ndg.metadata_desc (
21    metadata_desc_id integer UNIQUE NOT NULL,
22    abstract character varying(32767) NOT NULL,
23    description_section character varying(32767),
24    last_updated timestamp with time zone NOT NULL,
25
26    PRIMARY KEY (metadata_desc_id)
27);
28REVOKE ALL ON TABLE ndg.metadata_desc FROM PUBLIC;
29
30CREATE TABLE ndg.structured_keyword (
31    structured_keyword_id integer NOT NULL,
32    parent_keyword_id     integer,
33
34    valid_term            character varying(1024) NOT NULL,
35    valid_term_abbrev     character varying(1024),
36    parent_list_id        character varying(1024) NOT NULL,
37    term_id               character varying(64) NOT NULL,
38   
39    PRIMARY KEY (structured_keyword_id)
40);
41REVOKE ALL ON TABLE ndg.structured_keyword FROM PUBLIC;
42
43CREATE TABLE ndg.contact_details (
44    contact_details_id integer UNIQUE NOT NULL,
45    email character varying(100),
46    fax character varying(25),
47    telephone character varying(25),
48    address character varying(200),
49    postcode character varying(10),
50    uri character varying(100),
51    city character varying(50),
52    country character varying(50),
53
54    PRIMARY KEY (contact_details_id)
55);
56REVOKE ALL ON TABLE ndg.contact_details FROM PUBLIC;
57
58CREATE TABLE ndg.person (
59    person_id integer UNIQUE NOT NULL,
60    contact_details_id integer NOT NULL,
61    title character varying(5),
62    known_as character varying(50),
63    personal_name character varying(50),
64    initials character varying(10) NOT NULL,
65    family_name character varying(50) NOT NULL,
66    name_order character varying(10),
67
68    PRIMARY KEY (person_id),
69    FOREIGN KEY (contact_details_id) REFERENCES contact_details
70);
71REVOKE ALL ON TABLE ndg.person FROM PUBLIC;
72
73CREATE TABLE ndg.organisation (
74    organisation_id integer UNIQUE NOT NULL,
75    contact_details_id integer NOT NULL,
76    name character varying(100) NOT NULL,
77    abbreviation character varying(20) NOT NULL,
78
79    PRIMARY KEY (organisation_id),
80    FOREIGN KEY (contact_details_id) REFERENCES ndg.contact_details
81);
82REVOKE ALL ON TABLE ndg.organisation FROM PUBLIC;
83
84CREATE TABLE ndg.role (
85    role_id integer UNIQUE NOT NULL,
86    role_name character varying(150) NOT NULL,
87    abbreviation character varying(75) NOT NULL,
88    contact_details_id integer NOT NULL,
89
90    PRIMARY KEY (role_id),
91    FOREIGN KEY (contact_details_id) REFERENCES ndg.contact_details
92);
93REVOKE ALL ON TABLE ndg.role FROM PUBLIC;
94
95CREATE TABLE ndg.organisation_role (
96    organisation_id integer NOT NULL,
97    role_id integer NOT NULL,
98
99    PRIMARY KEY (organisation_id, role_id),
100    FOREIGN KEY (organisation_id) REFERENCES ndg.organisation,
101    FOREIGN KEY (role_id) REFERENCES ndg.role
102);
103REVOKE ALL ON TABLE ndg.organisation_role FROM PUBLIC;
104
105
106CREATE TABLE ndg.role_holder (
107    role_holder_id integer UNIQUE NOT NULL,
108    role_id integer NOT NULL,
109    start_date date NOT NULL,
110    end_date date,
111    organisation_id integer,
112    person_id integer,
113
114    PRIMARY KEY (role_holder_id),
115    FOREIGN KEY (role_id) REFERENCES ndg.role,
116    FOREIGN KEY (organisation_id) REFERENCES ndg.organisation,
117    FOREIGN KEY (person_id) REFERENCES ndg.person
118);
119REVOKE ALL ON TABLE ndg.role_holder FROM PUBLIC;
120
121CREATE TABLE ndg.scheme_type (
122    scheme_type_id integer UNIQUE NOT NULL,
123    scheme_type character varying(100) NOT NULL,
124
125    PRIMARY KEY (scheme_type_id)
126);
127REVOKE ALL ON TABLE ndg.scheme_type FROM PUBLIC;
128
129
130-- We dont really need this, as the XML schema defines units as structured keywords... (mhen)
131--CREATE TABLE ndg.unit (
132--    unit_id integer NOT NULL,
133--    name character varying(100) NOT NULL,
134--    abbreviation character varying(50) NOT NULL,
135--    unit_list character varying(256),
136--    unit_list_id character varying(12),
137--
138--    PRIMARY KEY (unit_id)
139--);
140--REVOKE ALL ON TABLE ndg.unit FROM PUBLIC;
141
142CREATE TABLE ndg.spatial_coverage (
143    spatial_coverage_id integer UNIQUE NOT NULL,
144    area_code character varying(20) NOT NULL,
145
146    PRIMARY KEY (spatial_coverage_id)
147);
148REVOKE ALL ON TABLE ndg.spatial_coverage FROM PUBLIC;
149
150
151CREATE TABLE ndg.temporal_coverage (
152    temporal_coverage_id integer UNIQUE NOT NULL,
153
154    PRIMARY KEY (temporal_coverage_id)
155);
156REVOKE ALL ON TABLE ndg.temporal_coverage FROM PUBLIC;
157
158CREATE TABLE ndg.date_range (
159    date_range_id integer UNIQUE NOT NULL,
160    date_start timestamp without time zone NOT NULL,
161    date_end timestamp without time zone,
162    temporal_coverage_id integer NOT NULL,
163
164    PRIMARY KEY (date_range_id),
165    FOREIGN KEY (temporal_coverage_id) REFERENCES ndg.temporal_coverage
166);
167REVOKE ALL ON TABLE ndg.date_range FROM PUBLIC;
168
169CREATE TABLE ndg.date_instance (
170    date_instance timestamp without time zone NOT NULL,
171    temporal_coverage_id integer NOT NULL,
172
173    PRIMARY KEY(date_instance, temporal_coverage_id),
174    FOREIGN KEY(temporal_coverage_id) REFERENCES ndg.temporal_coverage
175);
176REVOKE ALL ON TABLE ndg.date_instance FROM PUBLIC;
177
178
179CREATE TABLE ndg.coverage (
180    coverage_id integer UNIQUE NOT NULL,
181    spatial_coverage_id integer,
182    temporal_coverage_id integer,
183
184    PRIMARY KEY (coverage_id),
185    FOREIGN KEY (spatial_coverage_id) REFERENCES ndg.spatial_coverage,
186    FOREIGN KEY (temporal_coverage_id) REFERENCES ndg.temporal_coverage
187);
188REVOKE ALL ON TABLE ndg.coverage FROM PUBLIC;
189
190CREATE TABLE ndg.parameter (
191    parameter_id      integer UNIQUE NOT NULL,
192    is_output         boolean                NOT NULL,
193    standard_unit     integer                NOT NULL,
194    high_value        real                   NOT NULL,
195    low_value         real                   NOT NULL,
196    parameter_name    character varying(100) NOT NULL,
197    parameter_abbrev  character varying(32)  NOT NULL,
198
199    PRIMARY KEY (parameter_id),
200    FOREIGN KEY (standard_unit) REFERENCES ndg.structured_keyword
201);
202REVOKE ALL ON TABLE ndg.parameter FROM PUBLIC;
203
204CREATE TABLE ndg.parameter_structured_keyword (
205    structured_keyword_id integer NOT NULL,
206    parameter_id integer NOT NULL,
207
208    PRIMARY KEY (structured_keyword_id, parameter_id),
209    FOREIGN KEY (structured_keyword_id) REFERENCES ndg.structured_keyword,
210    FOREIGN KEY (parameter_id) REFERENCES ndg.parameter
211);
212REVOKE ALL ON TABLE ndg.parameter_structured_keyword FROM PUBLIC;
213
214
215CREATE TABLE ndg.activity (
216    activity_id integer UNIQUE NOT NULL,
217    description character varying(50),
218    start_date date NOT NULL,
219    end_date date,
220    coverage_id integer,
221
222    PRIMARY KEY (activity_id),
223    FOREIGN KEY (coverage_id) REFERENCES ndg.coverage
224);
225REVOKE ALL ON TABLE ndg.activity FROM PUBLIC;
226
227CREATE TABLE ndg.activity_type_list (
228    activity_id integer UNIQUE NOT NULL,
229    activity_type character varying(40) NOT NULL,
230
231    PRIMARY KEY(activity_id, activity_type),
232    FOREIGN KEY(activity_id) REFERENCES ndg.activity,
233    CHECK (activity_type IN ('dgActivityDataCollection',
234                             'dgActivityDataProject',
235                             'dgActivityDataCampaign',
236                             'dgActivityDataInvestigation_Cruise',
237                             'dgActivityDataInvestigation_Flight'))
238);
239REVOKE ALL ON TABLE ndg.activity_type_list FROM PUBLIC;
240
241
242CREATE TABLE ndg.activity_role (
243    activity_id integer NOT NULL,
244    role_id integer NOT NULL,
245    role_type character varying(50),
246
247    PRIMARY KEY (activity_id, role_id),
248    FOREIGN KEY (activity_id) REFERENCES activity,
249    FOREIGN KEY (role_id) REFERENCES ndg.role
250);
251REVOKE ALL ON TABLE ndg.activity_role FROM PUBLIC;
252
253CREATE TABLE ndg.activity_relationships (
254    activity_id integer NOT NULL,
255    related_activity_id integer NOT NULL,
256    relation_keyword_id integer NOT NULL,
257
258    PRIMARY KEY(activity_id, related_activity_id),
259    FOREIGN KEY(activity_id) REFERENCES ndg.activity,
260    FOREIGN KEY(related_activity_id) REFERENCES ndg.activity,
261    FOREIGN KEY(relation_keyword_id) REFERENCES ndg.structured_keyword
262);
263REVOKE ALL ON TABLE ndg.activity_relationships FROM PUBLIC;
264
265CREATE TABLE ndg.observation_stn (
266    observation_stn_id integer UNIQUE NOT NULL,
267    description character varying(50) NOT NULL,
268    contact_details_id integer,
269
270    PRIMARY KEY (observation_stn_id),
271    FOREIGN KEY (contact_details_id) REFERENCES ndg.contact_details
272);
273REVOKE ALL ON TABLE ndg.observation_stn FROM PUBLIC;
274
275CREATE TABLE ndg.production_tool (
276    production_tool_id integer UNIQUE NOT NULL,
277    description character varying(50) NOT NULL,
278    contact_details_id integer,
279
280    PRIMARY KEY (production_tool_id),
281    FOREIGN KEY (contact_details_id) REFERENCES ndg.contact_details
282);
283REVOKE ALL ON TABLE ndg.production_tool FROM PUBLIC;
284
285
286CREATE TABLE ndg.deployment (
287    deployment_id integer UNIQUE NOT NULL,
288    descriptive_name character varying(50),
289    date_start date NOT NULL,
290    date_end date,
291    activity_id integer,
292    production_tool_id integer,
293    observation_stn_id integer,
294
295    PRIMARY KEY (deployment_id),
296    FOREIGN KEY (activity_id) REFERENCES ndg.activity,
297    FOREIGN KEY (production_tool_id) REFERENCES ndg.production_tool,
298    FOREIGN KEY (observation_stn_id) REFERENCES ndg.observation_stn
299);
300REVOKE ALL ON TABLE ndg.deployment FROM PUBLIC;
301
302
303--CREATE TABLE ndg.dataset_type (
304--    dataset_type_id integer UNIQUE NOT NULL,
305--    dataset_type_name character varying(50) NOT NULL,
306--
307--    PRIMARY KEY (dataset_type_id)
308--);
309--REVOKE ALL ON TABLE ndg.dataset_type FROM PUBLIC;
310--INSERT INTO ndg.dataset_type VALUES (0, 'dgSimulation');
311--INSERT INTO ndg.dataset_type VALUES (1, 'dgAnalysis');
312--INSERT INTO ndg.dataset_type VALUES (2, 'dgMeasurement');
313
314
315--CREATE TABLE ndg.object_type (
316--    object_type_id   integer               NOT NULL,
317--    object_type_name character varying(50) NOT NULL,
318--    derived_type     boolean               NOT NULL,
319--
320--    PRIMARY KEY (object_type_id)
321--);
322--REVOKE ALL ON TABLE ndg.object_type FROM PUBLIC;
323--INSERT INTO ndg.object_type VALUES(0, 'dgEnsemble',            false);
324--INSERT INTO ndg.object_type VALUES(1, 'dgLaGrangianPath',      false);
325--INSERT INTO ndg.object_type VALUES(2, 'dgN-DimensionalDataset',false);
326--INSERT INTO ndg.object_type VALUES(3, 'dgSample',              false);
327--INSERT INTO ndg.object_type VALUES(4, 'dgProfile',             false);
328--INSERT INTO ndg.object_type VALUES(5, 'dgSection',             false);
329--INSERT INTO ndg.object_type VALUES(6, 'dgClimatology',         true);
330--INSERT INTO ndg.object_type VALUES(7, 'dgTimeSeries',          true);
331--INSERT INTO ndg.object_type VALUES(8, 'dgIntegration',         true);
332
333
334--CREATE TABLE ndg.dataset_status (
335--   dataset_status_id    integer               NOT NULL,
336--    dataset_status_desc  character varying(32) NOT NULL,
337--
338--    PRIMARY KEY(dataset_status_id)
339--);
340--REVOKE ALL ON TABLE ndg.dataset_status FROM PUBLIC;
341--INSERT INTO ndg.dataset_status VALUES (0, 'no_data');
342--INSERT INTO ndg.dataset_status VALUES (1, 'complete');
343--INSERT INTO ndg.dataset_status VALUES (2, 'updating');
344--INSERT INTO ndg.dataset_status VALUES (3, 'incomplete');
345
346-- Todo: Data Granule stuff
347CREATE TABLE ndg.data_entity (
348    data_entity_id integer UNIQUE NOT NULL,
349    dataset_type character varying(50),
350    object_type character varying(50),
351    dataset_status character varying(50),
352    coverage_id integer,
353
354    PRIMARY KEY (data_entity_id),
355    FOREIGN KEY (coverage_id) REFERENCES ndg.coverage
356);
357REVOKE ALL ON TABLE ndg.data_entity FROM PUBLIC;
358
359CREATE TABLE ndg.data_entity_parameter (
360    data_entity_id integer NOT NULL,
361    parameter_id integer NOT NULL,
362   
363    PRIMARY KEY (data_entity_id, parameter_id),
364    FOREIGN KEY (data_entity_id) REFERENCES ndg.data_entity,
365    FOREIGN KEY (parameter_id) REFERENCES ndg.parameter
366);
367REVOKE ALL ON TABLE ndg.data_entity_parameter FROM PUBLIC;
368
369CREATE TABLE ndg.data_entity_deployment (
370    data_entity_id integer NOT NULL,
371    deployment_id integer NOT NULL,
372
373    PRIMARY KEY (data_entity_id, deployment_id),
374    FOREIGN KEY (data_entity_id) REFERENCES ndg.data_entity,
375    FOREIGN KEY (deployment_id) REFERENCES ndg.deployment
376);
377REVOKE ALL ON TABLE ndg.data_entity_deployment FROM PUBLIC;
378
379CREATE TABLE ndg.data_entity_role (
380    data_entity_id integer NOT NULL,
381    role_id integer NOT NULL,
382    role_type character varying(50),
383   
384
385    PRIMARY KEY (data_entity_id, role_id),
386    FOREIGN KEY (data_entity_id) REFERENCES ndg.data_entity,
387    FOREIGN KEY (role_id) REFERENCES ndg.role
388);
389REVOKE ALL ON TABLE ndg.data_entity_role FROM PUBLIC;
390
391CREATE TABLE ndg.metadata_record (
392    metadata_record_id  integer UNIQUE NOT NULL,
393    metadata_desc_id    integer,
394    name                character varying(250) NOT NULL,
395    abbreviation        character varying(50),
396   
397    activity_id         integer,
398    data_entity_id      integer,
399    production_tool_id  integer,
400    observation_stn_id  integer,
401
402    PRIMARY KEY (metadata_record_id),
403    FOREIGN KEY (metadata_desc_id)   REFERENCES ndg.metadata_desc,
404    FOREIGN KEY (activity_id)        REFERENCES ndg.activity,
405    FOREIGN KEY (data_entity_id)     REFERENCES ndg.data_entity,
406    FOREIGN KEY (production_tool_id) REFERENCES ndg.production_tool,
407    FOREIGN KEY (observation_stn_id) REFERENCES ndg.observation_stn
408);
409REVOKE ALL ON TABLE ndg.metadata_record FROM PUBLIC;
410
411
412CREATE TABLE ndg.metadata_structured_keyword (
413    metadata_record_id integer NOT NULL,
414    structured_keyword_id integer NOT NULL,
415
416    PRIMARY KEY (metadata_record_id, structured_keyword_id),
417    FOREIGN KEY (metadata_record_id) REFERENCES ndg.metadata_record,
418    FOREIGN KEY (structured_keyword_id) REFERENCES ndg.structured_keyword
419);
420REVOKE ALL ON TABLE ndg.metadata_structured_keyword FROM PUBLIC;
421
422-- Todo: Granule stuff.
423--CREATE TABLE ndg.data_granules (
424--    granule_id integer NOT NULL,
425--    access_control_policy_id integer,
426--    unused integer
427--);
428--REVOKE ALL ON TABLE ndg.data_granules FROM PUBLIC;
429
430-- Todo: I dont think we need this. :- mhen
431-- CREATE TABLE ndg.roles_role_holders (
432--    role_id integer NOT NULL,
433--    role_holder_id integer NOT NULL
434-- );
435-- REVOKE ALL ON TABLE ndg.roles_role_holders FROM PUBLIC;
436
437-- provides string representation for each role type (creator, curator, project manager, etc.)
438
439-- Todo: Sort out granule stuff.
440--CREATE TABLE ndg.data_granule (
441--    data_granule_id integer NOT NULL,
442--    URI character varying(256) NOT NULL,
443--    scheme_type_id integer NOT NULL,
444--    id_of_parent_granule integer NOT NULL
445--);
446--REVOKE ALL ON TABLE ndg.data_granule FROM PUBLIC;
447
448-- Todo: ACP stuff
449--CREATE TABLE ndg.access_control_policies (
450--    policy_id integer NOT NULL,
451--    policy_url character varying NOT NULL,
452--    policy_text character varying NOT NULL
453--);
454--REVOKE ALL ON TABLE ndg.access_control_policies FROM PUBLIC;
455
456-- Todo: Data Granule Stuff
457--CREATE TABLE ndg.data_entity_data_granule (
458--    data_entity_id integer NOT NULL,
459--    data_granule_id integer NOT NULL,
460
461--    PRIMARY KEY (data_entity_id, data_granule_id),
462--    FOREIGN KEY (data_entity_id) REFERENCES ndg.data_entity,
463--    FOREIGN KEY (data_grangule_id) REFERENCES ndg.data_granule
464--);
465--REVOKE ALL ON TABLE ndg.data_entity_data_granule FROM PUBLIC;