Logical Database Model Reports

New Report Wizard

This chapter shows you how to prepare reports from logical database models. You can print model diagrams directly, and you can use the New Report Wizard to prepare detailed reports on databases, tables and data types. There are three kinds of reports available for logical models: Statistical Report; Table Report; and Types Report.

The Statistical Report provides a brief summary of overall model. For example, it lists the total number of tables, columns , foreign keys, and indexes. The statistical report is available only from the Database Model Diagram solution . So if you want a statistical report for an ER Source model, you need to first add it to database model project. To do this from the Database Model Diagram solution, choose Database > Project > Add Existing Document from the main menu, and then add the relevant ER source model file into the project. For a detailed discussion of this process, see Section 10.7.

The Table Report provides detailed information about all aspects of your logical model, and is the most useful of the three reports. The Types Report lists details about the logical data types in your model. You can run table and type reports from either the ER Source Model solution or the Database Model Diagram solution.

You can choose the level of detail in each report by selecting from customizable options to create summary reports or reports with comprehensive detail. The dialog boxes in each report use different input but many have a common structure. To avoid repetition, this chapter describes features that are unique to logical model reports. The Object Type report in Chapter 9 gives a detailed explanation of the common dialog box functions.

The procedures in this chapter refer to models that are open in the Database Model Diagram. You must begin by ensuring that your preferred logical model is open and is the active Visio model. If you have several models open , just click on the model you want in order to make it the active model.

To start the New Report Wizard from the Database Model Diagram solution, choose Database > Report from the main menu. The New Report Wizard will open as shown in Figure 15-1. When you are working in the Database Model Diagram solution, the New Report Wizard window shows seven report options in alphabetical order. If you do this from within an ER Source Model, only two options are in the list (Table Report and Types Report).

click to expand
Figure 15-1: New Report Wizard for Database Model Diagrams.

Four of the reports in the list (Constraint, Fact, Object, and Supertype) are the same reports as those described in Chapter 9. If you run the four ORM reports from the Database Model Diagram, you get the same results as when you run them from the ORM Source Model. Please refer to Chapter 9 for a complete description of the four ORM reports.

The next sections show you how to prepare the Statistical Report, the Table Report, and the Types Report from within the Database Model Diagram solution.


Statistical Report

The statistical report summarizes a logical model's content by doing the following things:

  • Counting the number and type of constraints, foreign keys, and attributes
  • Extracting your model's source file information.

You start the Statistical Report by double clicking on "Statistical Report" in the New Report Wizard list shown in Figure 15-1. Double clicking is much faster than using the "Next" and "Finish" buttons . Even if you get it wrong, it is still faster to double click the report name , realize your error, and double click the correct report title to start again.

If you do single click Statistical Report and then click the Next button, you will see the confirmation panel shown in Figure 15-2. You can check that you have selected the correct report and then click either Back to correct your selection or Finish to confirm and continue.

click to expand
Figure 15-2: New Report Wizard for Logical Models ”Confirmation panel.

Clicking Finish will reveal the Statistical Report dialog box shown in Figure 15-3. The Statistical report Dialog Box has three tabs: Preferences, Headers, and Pagination. Click on the Preferences tab to ensure that your screen looks like Figure 15-3.

Statistical Report Bottom Row Buttons

The four buttons along the bottom of the windows shown in Figure 15-3, Figure 15-4, and Figure 15-5 (Print , Preview, Export to RTF and Close) work in exactly the same way as in all other reports . Please refer to the description of the bottom row buttons in the Object Type report in Chapter 9.

Table 15-1: Statistical Report tabs.

Preferences tab

Headers tab

Pagination tab


Figure 15-3: Preferences tab.


You use the Preferences tab to choose your preferred notation by clicking one of the radio buttons labeled Relational and IDEFIX. If you want to report on external tables, make sure that you click on the "Include external tables" check box.


Figure 15-4: Headers tab.


The headers tab works in the same way as the header tab in the Object Type report. Please refer to the description of the Object Type report in Chapter 9 for a detailed explanation.


Figure 15-5: Pagination tab.


The pagination tab works in the same way as the pagination tab the Object Type report. Please refer to the description of the Object Type report in Chapter 9 for a detailed explanation.


Table Reports

You can start the Table Report by double clicking on "Table Report" in the New Report Wizard list shown in Figure 15-1. Visio will then reveal a tabbed dialog box similar to Figure 15-6. The following paragraphs describe each of the functions in the table report.

click to expand
Figure 15-6: Table Report Preferences tab.

Table Report Preferences Tab

Click on the Preferences tab to make sure that your screen looks similar to Figure 15-6.

Notation: You must choose one of the Notation radio buttons for your report. Even if you defined your logical model using relational notation, you can still switch to IDEFIX here.

Names : You must choose either conceptual or physical. If you want to review a logical schema, choose conceptual. If you want to review a physical schema, choose physical.

Show Annotations: Check this if you wish to include annotations.

Include External Objects: This checkbox will add objects that your model references but that are defined in a different model.

Predefined logical/physical reports ”Default To: Button. When you open the Preferences tab shown in Figure 15-6, the Default To: button label, will display the name of one of the predefined reports. For example, it may read "Default To: General Report." You can change the default report by clicking the Default To: button and selecting a report from the drop down list.

Figure 15-6 and Figure 15-7, show the states of the Default To button before and after a report selection. The text label on the Default To button in Figure 15-6 reads "Detailed Column Report." After clicking the button to reveal the drop-down list and then clicking "Table Report," the label on the "Default to:" button changes to "Table Report" as shown in Figure 15-7. When you choose a default report, you are choosing a predefined subset of the 61 attributes in the attribute tab (see Table 15-5 through Table 15-9).

click to expand
Figure 15-7: Default To: button label.

You can use the default reports as they are. Table 15-2 gives a summary of the contents of each of the ten default reports. The numbers in parentheses after each report name in the column headed "Predefined Report name" indicates the number of pages generated from the sample model Patient_LS.vsd. For example: Combined(11) against report number 2, means that eleven pages are generated when you run the Combined report against the sample model Patient_LS.vsd.

Table 15-2: Predefined Report content.

Report Number

Predefined Report Name

What You Get with Each Report

1

General(8)

The first section of this report gives a summary of your model. Each of the remaining sections gives a detailed report on each table.

2

Combined(11)

This report combines the database summary report with the table report.

3

Database(1)

Summarizes the model. Notes are generated for each table. The Notes are derived from the primary key notes.

4

Table(2)

Gives a short summary of each table including Notes and Foreign keys.

5

Column Summary(1)

Gives a list of columns grouped by table number. Shows data type, nulls, and value/range.

6

Detailed Column(5)

For each table shows the column name, physical data type, allowable nulls, and Notes. Prints a group header for each table.

7

Index Summary(1)

Lists each index showing its column name and sort order. Groups by table name.

8

Detailed Index(4)

Expands the index details. Groups by table name with each table beginning on a new page.

9

Foreign Key Summary(1)

Shows foreign key with child column name and parent column name. Groups by table name.

10

Detailed Foreign Key(4)

Shows foreign key name, definition, relationship type, cardinality, nullability, verb phrase, inverse verb phrase, and referential integrity on update and referential integrity on delete. Each table grouped to start on a new page.

You can use the default reports as an initial attribute selection and then change any combination of the checkboxes in the attribute tabs shown in Table 15-5 through Table 15-9. Each default report uses different attributes. Table 15-3 shows you how many attributes each default report uses for input. The numbers above the column names in the five columns on the right indicate the number of attribute checkboxes there are in each table attribute category. The numbers in each report row show how may of the possible attributes are used by each report. For example, the Foreign Key Summary (report 9) uses only two attributes out of a possible 22 table attributes and 61 total database attributes.

Table 15-3: Predefined reports “attribute content.

Report Number

Predefined Report Name

9
Data base

22
Table

12
Column

5
Index

13
Foreign Key

1

General

9

11

7

3

11

2

Combined

9

15

10

4

12

3

Database

9

4

Table

11

5

Column Summary

1

6

Detailed Column

7

7

Index Summary

2

8

Detailed Index

3

9

Foreign Key Summary

2

10

Detailed Foreign Key

11

Table Report Attributes Tab

You use the attributes tab to set checkboxes for up to 61 attributes. The attributes are classified into five mutually exclusive categories: Database, Table, Column, Index and Foreign Key. You can use the Category: drop down list shown in Figure 15-8, to review the state of each checkbox in each attribute category. You can see the five attribute checkboxes for the index category on the left of Figure 15-8.

click to expand
Figure 15-8: Table Report “Attributes tab categories.

You can choose to report some or all of the attributes by selecting each category in turn and selecting or deselecting each attribute checkbox within that category. Table 15-4 shows the scope of each category and the number of attributes it controls.

Table 15-4: Table report attributes category scope.

Category

Category Scope

Number of Attributes

Database

The physical target RDBMS, for example Microsoft SQL Server)

9

Table

each table in the schema

22

Column

every column in the schema

12

Index

every index in the schema

5

Foreign Key

every relation in the schema

13

Caution

Do not hunt and peck among the 61 checkboxes in the five attributes tabs, or you might miss lunch . The number of possible sets of four attributes selected from 61 possible attributes gives you over 5 million options. (Tip: try COMBIN(61,4) in an Excel spreadsheet).

You will find it best to develop and follow a structured attribute selection procedure. Your first step is to make sure that you understand what each attribute can give to you. You could start by trying the following procedure to help you to understand VEA's awesome reporting power.

  1. Make sure that you understand the meaning of each attribute (see Table 15-5 through Table 15-9).
  2. Load the sample model Patient_LS.vsd
  3. Choose "General Report" from the "Default to" list shown in Figure 15-6
  4. Open each attribute category in turn and check all the checkboxes. (Tip: Use the Select All buttons).
  5. Export the twelve-page report as an RTF file.
  6. Load the RTF file into your word processor.
  7. Add footers to show at least file name and page number.
  8. Print the report.
  9. Take the report and this book to a quiet place.
  10. Study the report.
  11. As you study the report, write down every report related question that occurs to you.
  12. Return to your PC and explore VEA to find the answers to your questions.

Database attributes category

You can use the database attributes tab to select or deselect any of nine attributes shown in Figure 15-9. Table 15-5 shows the meaning of each database attribute.

Table 15-5: Database attributes meaning.

Database Attribute Name

Attribute Meaning

Target DBMS

Shows the type of RDBMS driver you have selected, (e.g., Oracle).

Number of tables

Gives the total number of tables in your model.

Number of columns

The total number of columns across all tables in your model.

Number of indexes

The total number of indexes across all columns in your model.

Number of foreign keys

The total number of foreign keys across columns in your model.

Last build date

The date of the most recent physical database build.

Extended attributes

Extended database attributes.

Table stats summary

Gives each table name with Notes from the primary key plus, for each table, the number of columns, indexes, and foreign keys.

Include view details

Shows the SQL code used to generate a view that is included in your model.

click to expand
Figure 15-9: Database Attributes.

Table attributes category

You can use the table attributes tab to select or deselect any of the 22 attributes shown in Figure 15-10. Table 15-6 shows the meaning of each table attribute.

Table 15-6: Table attributes “attributes category meaning.

Table Attribute Name

Attribute Meaning

Record

The number of each data record in your report.

Physical name

The physical name of each table plus table constraints and code.

Conceptual name

The conceptual name of each table plus table constraints and code.

ID

The internal Visio number for each table plus constraints and code.

Name space

The unique name that you have chosen for this table.

External

Tables that are not external show "No." External tables show "Yes."

Notes

Shows the Notes associated with the tables' primary key. If the primary key is a composite key then no Notes are shown.

Owner

Table owner.

Target DB name

The filename that you have chosen for your target physical database.
See Chapters 7 and 11.

Independent

Tables that do not have foreign keys.

Number of columns

The total number of columns in a table.

Number of indexes

The total number of indexes in a table.

Number of foreign keys

The total number of foreign keys in a table.

Primary key

Shows the table's primary key. If the primary key is a composite key, all the columns in the composite key are listed. Code is also shown.

Extended attributes

Extended table attributes.

User defined types

User defined data types.

Summary of columns

Shows the following information about each column in each table:
Column named, data type, nullability, value/range.

Code details

Shows the constraint verbalization, type of code, and the SQL code.

Summary of alternate keys

Shows one line for each alternate key in each table.

Summary of non-unique indexes

Shows one line for each non-unique key in each table.

Summary of foreign keys (Child)

Shows: Foreign key name (table pair), child key and parent key.

Summary of foreign keys (Parent)

Shows Foreign key name (table pair), parent key and child key.

click to expand
Figure 15-10: Table Report “Attributes tab.

Column attributes category

You can use the column attributes tab to select or deselect any of the twelve attributes shown in Figure 15-11. The meaning of each column attribute is shown in Table 15-7.

Table 15-7: Column attributes meaning.

Column Attribute Name

Attribute Meaning

Physical name

Physical column name (to be used in the physical database).

Conceptual name

Conceptual column name (derived from the domain name).

Physical data type

Product specific data type (e.g., Oracle).

Portable data type

One of the 24 built in data types.

Allow NULLs

Defines the nullability of a column.

Value/Range

Shows the permissible values that have been set for each column.

External

Shows if a column that has been defined in a different schema.

Notes

Shows the Notes for each column. (derived from domain notes)

Foreign key role name

Shows all columns with annotations (e.g., index or foreign key).

Extended attributes

Shows all columns plus any extended attributes.

Default value

Shows all columns with any default values that have been assigned.

Check clause

Check clause.

click to expand
Figure 15-11: Column Attributes.

Index attributes category

You can use the index attributes tab to select or deselect any of the five attributes shown in Figure 15-12. Table 15-8 shows the meaning of each index attribute.

Table 15-8: Index attributes meaning.

Index Attribute Name

Attribute Meaning

Columns

Shows all columns plus indexes.

Sort order

Shows if the index is ascending or descending.

Unique

Shows all columns, with a summary of the indexes in each table.

Extended attributes

Extended index attributes.

Verbalization

Shows all columns, plus an index summary, verbalization and sort direction.

click to expand
Figure 15-12: Index Attributes.

Foreign key attributes category

You can use the checkboxes in the tab header to choose to include Child foreign keys, Parent foreign keys, or both. If you leave both checkboxes unchecked, your foreign key report will be blank.

You can use the foreign key attributes tab to select or deselect any combination from the thirteen attributes shown in Figure 15-13. Table 15-9 shows the meaning of each foreign key attribute.

Table 15-9: Foreign key attributes meaning.

Foreign Key Attribute

Attribute Meaning

Definition

Shows each foreign key name by table, with child column name and parent column name.

Category is complete

Shows foreign key names by table.

Category discriminator

Shows foreign key names by table.

Relationship type

Shows foreign key names by table and whether the foreign key is "identifying" or "non-identifying." (These are IDEF1X terms.)

Cardinality

Shows the foreign keys by table and the verbalized cardinality.

Allow NULLs

Shows nullability of each foreign key.

Verb phrase

Shows the verb phrase that links the parent and child keys.

Inverse phrase

Shows the inverse verb phrase that links the parent and child keys.

Physical name

Shows all foreign keys by table with their physical names.

Notes

Shows notes in the foreign keys' database properties.

Referential on update Integrity

Shows the action to be taken when the foreign key is updated.

Referential Integrity on delete

Shows the action to be taken when the foreign key is deleted.

Verbalization

Gives a full description of all foreign keys.

click to expand
Figure 15-13: Foreign Key Attributes tab.

Table Report Sort Group tab

The Sort/Group tab works in exactly the same way as the Sort/Group tab described in the Object Type report in Chapter 9. You can choose to group your report by one of the three options shown in the drop down menu in Figure 15-14. The default sort key is Physical table name.

click to expand
Figure 15-14: Sort/Group.

Physical table names are often short and cryptic. Conceptual names tend to be longer and more descriptive. You can sort by conceptual table name to help you to review database semantics, homonyms, and synonyms. Sorting by physical table name will help you to review your physical naming convention to ensure support for good system performance and ease of use by database performance specialists.

Table Report Header Tab

The header tab works in exactly the same way as the other headers. Please refer to the Object Type report in Chapter 9 for a complete description of the header tab.

Table Report Pagination Tab

The pagination tab works in exactly the same way as the other pagination tabs. Please refer to the Object Type report in Chapter 9 for a complete description of the pagination tab.

Table Report Bottom Row Buttons

The four buttons along the bottom of the window (Print ,Preview, Export to RTF and Close) work in exactly the same way as in all other reports. Please refer to the Object Type report description in Chapter 9 for a detailed explanation of the function of each button.


Data Types Report

You can use the Types report to report on all aspects of the data types in your database model diagram.

Data Types Report Selection Tab

You can choose from three data type categories: composite, user -defined, and built-in, as shown in Figure 15-15. If relevant, you can also include any referenced external types that are defined elsewhere. By default, your report sorts by the class of data type and then by type name . You can easily change this.

click to expand
Figure 15-15: Data Types Report “Selection tab.

Table 15-10 summarizes the meaning of these options.

Table 15-10: Data types report “selection tab meaning.

Selection

Meaning

Composite types

Types with a composite structure.

User defined types

Shows any user defined data types. With physical data type, Notes, and a summary of any related method.

Built in types

Predefined data types for the specific DBMS.

Include external types

External types are data types that you have referenced in your model, but that are defined in a different model.

Data Types Report Attributes Tab

You can select attribute information, such as data type names , base data types, and derived data types, by using the Attributes tab shown in Figure 15-16.

click to expand
Figure 15-16: Data Types Report “Attributes tab.

Data Types Report Sort Group Tab

The Sort/Group tab works similarly to the Sort/Group tab described in the Object Type report in Chapter 9. Please refer to the detailed description of the Sort/Group tab in the Object Type report in Chapter 9.

Data Types Report Header Tab

The header tab works in exactly the same way as the other headers. Please refer to the detailed description of the header tab in the Object Type report in Chapter 9.

Data Types Report Pagination Tab

The pagination tab works in exactly the same way as the other pagination tabs. Please refer to the detailed description of the pagination tab in the Object Type report in Chapter 9.

Data Types Report Bottom Row Buttons

The four buttons along the bottom of the window (Print , Preview, Export to RTF , and Close) work in exactly the same way as in all other reports . Please refer to the detailed description of the bottom row buttons in the Object Type report in Chapter 9.

Table 15-11: Data types report types attribute.

Attribute

Meaning

Show type

Creates a placeholder for each individual data type instance. Not for use on its own.

Type name

The data types reported here are the DBMS-specific physical data types.

Attributes

Shows the data type names and their physical types. You must also select at least "Show type" and "Type name" checkboxes to get anything on your report.

Derived types

Derived types.

Base type

Base type.

Notes

Notes for user defined types.

Type summary

Type summary.

Verbalization

Verbalization.

click to expand
Figure 15-17: Data Types Report “Sort/Group Tab.

Table 15-12: Data types report sort group.

Sort Key

Meaning

Class of Data Type

Sorts your report into the four classes shown on the selection tab in Figure 15-15.

Type Name

Sorts your report into alphabetical order of data type name.

Table 15-13: Data Types report-grouping.

Sort Key

Meaning

Class of Data Type

Groups your data types into the four classes shown in Figure 15-15. (e.g., If you sort by data type name and group by class of data type, your report will have four subsections with data types sorted alphabetically within each subsection).

Type Name

If you group by this sort key, then you will get one report page for each type of data type in your report. For example, if you select only built in types, you will get at least 24 pages in your report “one page for each of the 24 built in data types.




Database Modeling with Microsoft Visio for Enterprise Architects
Database Modeling with MicrosoftВ® Visio for Enterprise Architects (The Morgan Kaufmann Series in Data Management Systems)
ISBN: 1558609199
EAN: 2147483647
Year: 2002
Pages: 150

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net