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
- 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 * List Attributes with no maximum length defined.
rem * Author: H. Deschamps, 12/19/98.
set lines 80
col name format a30
select 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: email@example.com
Take a look at my HomePage