M&D Home » WDC for Climate » CERA Data Model » SQL Software

SQL Software

Basic Data Definition Language (DDL)

General SQL Scripts

dropAll.sql

Drops all CERA-2 Tables

5.5 K

GEN.sql

Generates all CERA-2 Tables

806

Data Definition Language of CERA Core

GENcoreVALUES.sql

Generates all lists of values (LOV)

64 K

GENcoreENTITIES.sql

Generates all other entities

8.1 K

GENcoreRELATIONS.sql

Generates all relations

4.5 K

Data Definition Language of (Sub-)Modules

	GENmodDATA_ACCESS.sql Generates module DATA_ACCESS	3.5 K
	GENmodDATA_ORGANIZATION.sql Generates module DATA_ORGANISATION	7.3 K
	GENsubmodPATCHED_DIMENSION.sql Generates submodule PATCHED_DIMENSION	1.0 K
	GENmodPROCESS_STEP.sql Generates module PROCESS_STEP	532

SQL Scripts for Certain Tables as Originally Used at PIK

TablePIKInstitute.sql

Table INSTITUTE

30 K

TablePIKLocation.sql

Table LOCATION

103 K

TablePIKTopic.sql

Table TOPIC

78 K

Basic SQL Tools

	checkEntities.sql - checkEntities.sql: check data integrity of entities (their primary keys should refer to an ENTRY)
	cleanEntities.sql - cleanEntities.sql: re-adjust data integrity of entities (=delete entity where necessary)
	checkHierarchies.sql - checkHierarchies.sql: check data integrity of hierarchies (TOPIC, LOCATION: valid pointers and levels)
	delEntry.sql - delEntry.sql: delete an entry from all CERA tables

Table Tools (tables Location andTopic)

	GENlocation_sheet.sql generates a view on the hierarchical recursive table Location that gives for every Entry its related Location with location_id and path (=all location_names on higher levels) leading to it.
	makeLOCATIONlist.sql generates the ASCII file makeLOCATIONlist1.lst
	makeLOCATIONlist_01 an AIX/ksh script converting file makeLOCATIONlist1.lst to the ASCII file makeLOCATIONlist3.lst, which gives an overview on the table Location.
	GENtopic_sheet.sql generates a view on the hierarchical recursive table Topic that gives for every Entry its related Topics with topic_id and path (=all topic_names on higher levels) leading to it.
	makeTOPIClist.sql generates the ASCII file makeTOPIClist1.lst
	makeTOPIClist_01 an AIX/ksh script converting file makeTOPIClist1.lst to the ASCII file makeTOPIClist3.lst, which gives an overview on the table Topic.
	GENcoreRELATIONS.sql

Helpful Views

views_get.sql

- views_get.sql: 14 general views on CERA-2.5 Core and some Modules, related to the relations/Blocks.

views_grants.sql

- views_grants.sql: some general views on CERA-2 tables (some of them are used for the PIK's Oracle Forms, too) as well as the necessary access permits.

latest update: 2008-11-05 10:25