Designer/2000 (2.1.1.) QC


Attributes with no maximum length defined.

By Hervé Deschamps.




There are a number of data model quality checks that need to be performed before transforming a logical model into a physical model. One of them is to verify that all attributes with a datatype like NUMBER, MONEY or VARCHAR2 have a maximum length specified.

We have looked for such a report in Designer/2000 2.1.1. reports with no fortune. As a result we wrote a simple SQL*Plus report to highlight the suspicious attributes.

Depending of the data modeling standards that you use, you may decide that attributes of datatypes like NUMBER need no maximum length. If that is the case, you only need to modify the query accordingly.

You will notice that in the query below we use an internal ID number for the diagram. You have several options:
  1- Use this report as is and find the ID of your diagram.
  2- Modify this report to use the diagram name instead of the diagram internal ID.
  3- Modify this report to list all entities in a given application system instead of limiting the QC to one diagram.

If you choose option 2 or 3 the author would welcome a copy of your SQL query.

As for option 1, this is how you can get the mysterious diagram internal ID:
  - Open the RON.
  - In the RON Navigator, navigate to Entity/RElationship Modeling.
  - Open Entity Relationship Diagrams.
  - Click on you diagram.
  - In the Properties Window, click on the diagram name.
  - Press F5.
  - A pop-up appears on the screen.
  - The internal ID number for the diagram is the fourth field on the pop-up.
  - In the query below, replace 438054 with the Object ID of you diagram.

The script below must be used before running the database transformer.
 
 

rem *
rem *   List Attributes with no maximum length defined.
rem *
rem *   Author: H. Deschamps, 12/19/98.
rem *
rem *

set lines 80
set pages 500

col name format a30

prompt
prompt
prompt >>> List Attributes with no maximum length defined.
prompt

select ent.name, att.name
from   ci_attributes att,
       ci_entities ent,
       ci_diagram_element_usages diag_usg
where  att.format not in ('DATE','INTEGER','TIMESTAMP','IMAGE','PHOTOGRAPH','SOUND','VIDEO') and
       nvl(att.maximum_length,0)  = 0 and
       att.entity_reference = ent.id and
       ent.id = diag_usg.cielement_reference and
       diag_usg.diagram_reference = 438054
order by ent.name, att.name
/
 

 
 

You may want to extend this report to show attributes with no average length or with no decimals where some are expected. If you do, the author would appreciate a copy of your SQL query.
 
 


Please send me comments or suggestions:  herve@iherve.com

 Take a look at my HomePage