Designer 2000 QA

Ghost Relationships

By Hervé Deschamps.

This is a simple utility very useful to Data Modelers. It should be used before running the database design wizad.

When there are several entity-relationship diagrams for one application system, it is very easy to have realtionships defined in the repository but not shown on any diagram. These ghost relationships cause errors and delays in the database design phase.

This simple SQL*Plus utility produces a list of such relationships for you to work on. This should be done before running the database design wizard!

rem *
rem *   List of relationships not included on any diagram.
rem *
rem *   Author: H. Deschamps, 5/8/97.
rem *
rem *

set pagesize 50

col relation_name format a25
col entity_from format a25
col entity_to format a25

prompt >>> List of relationships not included on any diagram.

select relation_name, 
       lower( entity_from, 
       lower( entity_to
from   ci_relationship_ends rel,
       ci_entities sfrom,
       ci_entities sto
where  rel.application_system_owned_by = 57834 and
       not exists (select id 
                   from ci_diagram_element_usages el_u
                   where el_u.cielement_reference = and
       rel.from_entity_reference = and
       rel.to_entity_reference =
order  by,,

One note about the SQL*Plus script above. It works on one application system at a time. Before
running it, we update the script with the relevant internal application ID in Designer 2000. In the
example above, we're looking at application 57834. In order to make it work for you, you can edit
the script and search/replace 57834 by whatever appropriate application system ID. You can obtain
this ID from the RON by highlighting an entity, going over to the properties window, clicking on the
name of the application that owns the entity and pressing key F5. The application ID is displayed in
field 'Internal value'. Of course you could also obtain this ID by querying the Designer 2000 view
ci_application_systems. Even better, you could enhance this script to obtain the application name
from the user at runtime and getting the corresponding ID.

The scripts published on this web site are as the author use them. They all have a lot of room for enhancement. The author would be glad to recieve your enhancements and would give you the credit next time this page is updated.

Please send me comments or suggestions:

 Take a look at my HomePage