-- This SQL-script is to clean up dead records of some CERA entity tables.
--   When a record is deleted manually from CERA.Entry, most of the related 
--   child records from the relations are automatically deleted, too. However,
--   in entities Summary, Quality, Spatial_coverage, and Temporal_coverage 
--   may remain unwanted records. These are deleted by this script.
--   (This script is to be run from time to time only to save storage).
spool cleanEntities
delete from SUMMARY           
 where summary_id           not in 
       (select summary_id           from ENTRY     where summary_id!=NULL;
   and summary_id <> 0;
delete from QUALITY    
 where quality_id           not in 
       (select quality_id           from ENTRY     where quality_id!=NULL)
   and quality_id <> 0;
delete from SPATIAL_COVERAGE  
 where spatial_coverage_id  not in 
       (select spatial_coverage_id  from COVERAGE  where spatial_coverage_id!=NULL)
   and spatial_coverage_id <> 0;
delete from TEMPORAL_COVERAGE
 where temporal_coverage_id not in 
       (select temporal_coverage_id from COVERAGE  where temporal_coverage_id!=NULL)
   and temporal_coverage_id <> 0;
delete from DATA_ACCESS      
 where data_access_id       not in 
       (select data_access_id       from PARAMETER where data_access_id!=NULL)
   and data_access_id <> 0;
spool off
