What is the format of a data dictionary?

Question

Are there any standard examples/ samples of data dictionaries that document a database.

Answer 1

I don't think there is a standard way of doing it.

Oracle uses a series of objects all starting with DBA_*. For example, DBA_TABLES, DBA_TAB_COLUMNS, etc. You can do SELECT TABLE_NAME FROM ALL_TABLES WHERE TABLE_NAME LIKE 'DBA%' to get a list.

If you don't have privs, there is an analogous series of objects for users, called USER_TABLES, USER_VIEWS, USER_TAB_COLS, etc.

Answer 2

The official standard is ISO/IEC 11179 ... which will make your head hurt and refers to semantic elements of the documented data-model when we usually need both semantic and physical (tables, fields etc.) documentation in a 'real world' data dictionary.

Personally, I favour (however you implement this is up to you) physical and optionally logical Entity-Relationship models on the front-page, entry screen or wherever a user of the Data Dictionary first 'hits' (This provides a meta layer to the detail beneath).

Then for each table:

Table Name [Physical | Logical]

Table description (content, granularity, 'periodicity' (if there is one, the time period which applies to the data contained in the table), source)

Then for each column:

Column Name

Column Description

Column Datatype

Column Size in Bytes

[Optional] Other Column Details... nullable, triggers, source

Relationships Constraint name | 'Other' Table | Cardinality | Type etc.

Indexes Name | Column Membership | Type etc.

...Obviously, that's a 'less is more' approach to defining a data dictionary! I think the key is providing an easy way in (through the ER) model, rather than just a long, labourious list of tables and columns.