Data dictionary - what should be in it?

Author:   Sakari Mattila
Updated:  15-May-2001 (B)

Data dictionary may cover the whole organisation, a part of the organisation or a database. In its simplest form, the data dictionary is only a collection of data element definitions, according to descriptions below. More advanced data dictionary contains database schema with reference keys, still more advanced data dictionary contains entity-relationship model of the data elements or objects. The term "data element" is used below. It is the same concept as "data object" or "object" in some database texts. This document is derived from database text books, existing data dictionaries and practical experience with Oracle, Ingres, Access, UniData and Burroughs systems.

1. Data element definitions

Data element definitions may be independent of table definitions or a part of each table definition

• Data element number
  Data element number is used in the technical documents.
• Data element name (caption)
  Commonly agreed, unique data element name from the application domain. This is the real life name of this data element.
• Short description
  Description of the element in the application domain.
• Security classification of the data element
  Organisation-specific security classification level or possible restrictions on use. This may contain technical links to security systems.
• Related data elements
  List of closely related data element names when the relation is important.
• Field name(s)
  Field names are the names used for this element in computer programs and database schemas. These are the technical names, often limited by the programming languages and systems.
• Code format
  Data type (characters, numeric, etc.), size and, if needed, special representation. Common programming language notation, input masks, etc. can be used.
• Null value allowed
  Null or non-existing data value may be or may not be allowed for an element. Element with possible null values needs special considerations in reports and may cause problems, if used as a key.
• Default value
  Data element may have a default value. Default value may be a variable, like current date and time of the day (DoD).
• Element coding (allowed values) and intra-element validation details or reference to other documents
  Explanation of coding (code tables, etc.) and validation rules when validating this element alone in the application domain.
• Inter-element validation details or reference to other documents
  Validation rules between this element and other elements in the data dictionary.
• Database table references
  Reference to tables the element is used and the role of the element in each table. Special indication when the data element is the key for the table or a part of the key.
• Definitions and references needed to understand the meaning of the element
  Short application domain definitions and references to other documents needed to understand the meaning and use of the data element.
• Source of the data in the element
  Short description in application domain terms, where the data is coming. Rules used in calculations producing the element values are usually written here.
• Validity dates for the data element definition
  Validity dates, start and possible end dates, when the element is or was used. There may be several time periods the element has been used.
• History references
  Date when the element was defined in present form, references to superseded elements, etc.
• External references
  References to books, other documents, laws, etc.
• Version of the data element document
  Version number or other indicator. This may include formal version control or configuration management references, but such references may be hidden, depending on the system used.
• Date of the data element document
  Writting date of this version of the data element document.
• Quality control references
  Organisation-specific quality control endorsements, dates, etc.
• Data element notes
  Short notes not included in above parts.

2. Table definitions

Table definition is usually available with SQL command help table

tablename

• Table name
• Table owner or database name
• List of data element (column) names and details
• Key order for all the elements, which are possible keys
• Possible information on indexes
• Possible information on table organisation
  Technical table organisation, like hash, heap, B+ -tree, AVL -tree, ISAM, etc. may be in the table definition.
• Duplicate rows allowed or not allowed
• Possible detailed data element list with complete data element definitions
• Possible data on the current contents of the table
  The size of the table and similar site specific information may be kept with the table definition.
• Security classification of the table
  Security classification of the table is usually same or higher than its elements. However, there may be views accessing parts of the table with lower security.

3. Database schema

Database schema is usually graphical presentation of the whole database. Tables are connected with external keys and key colums. When accessing data from several tables, database schema will be needed in order to find joining data elements and in complex cases to find proper intermediate tables.

Some database products use the schema to join the tables automatically.

4. Entity-relationship model of data

Entity-relationship model is database analysis and design tool. It lists real-life application entities, attributes of entities and relationships amongst entities. The type of each relationship is also indicated. Entity-relationship model is represented in graphical form.

5. Database security model

Database security model associates users, groups of users or applications (programs) with database access rights.

Related documents:

Tasks of the database administrator.