Data dictionary

Source: Wikipedia, the free encyclopedia.
Not to be confused with
Dictionary (data structure)
.
A simple layout of a data dictionary

A data dictionary, or

database management systems
(DBMS):

  • A document describing a database or collection of databases
  • An integral
    DBMS
    that is required to determine its structure
  • A piece of middleware that extends or supplants the native data dictionary of a DBMS

Documentation

The terms data dictionary and data repository indicate a more general software utility than a catalogue. A catalogue is closely coupled with the DBMS software. It provides the information stored in it to the user and the DBA, but it is mainly accessed by the various software modules of the DBMS itself, such as DDL and DML compilers, the query optimiser, the transaction processor, report generators, and the constraint enforcer. On the other hand, a data dictionary is a data structure that stores metadata, i.e., (structured) data about information. The software package for a stand-alone data dictionary or data repository may interact with the software modules of the DBMS, but it is mainly used by the designers, users and administrators of a computer system for information resource management. These systems maintain information on system hardware and software configuration, documentation, application and users as well as other information relevant to system administration.[2]

If a data dictionary system is used only by the designers, users, and administrators and not by the DBMS Software, it is called a passive data dictionary. Otherwise, it is called an active data dictionary or data dictionary. When a passive data dictionary is updated, it is done so manually and independently from any changes to a DBMS (database) structure. With an active data dictionary, the dictionary is updated first and changes occur in the DBMS automatically as a result.

Database

entities) and their contents (fields) plus additional details, like the type and length of each data element. Another important piece of information that a data dictionary can provide is the relationship between tables. This is sometimes referred to in entity-relationship
diagrams (ERDs), or if using set descriptors, identifying which sets database tables participate in.

In an active data dictionary constraints may be placed upon the underlying data. For instance, a range may be imposed on the value of numeric data in a data element (field), or a record in a table may be forced to participate in a set relationship with another record-type. Additionally, a distributed DBMS may have certain location specifics described within its active data dictionary (e.g. where tables are physically located).

The data dictionary consists of record types (tables) created in the database by systems generated command files, tailored for each supported back-end DBMS. Oracle has a list of specific views for the "sys" user. This allows users to look up the exact information that is needed. Command files contain SQL Statements for CREATE TABLE, CREATE UNIQUE INDEX, ALTER TABLE (for referential integrity), etc., using the specific statement required by that type of database.

There is no universal standard as to the level of detail in such a document.

Middleware

In the construction of database applications, it can be useful to introduce an additional layer of data dictionary software, i.e.

entity-relationship models tailored to suit different applications that share a common database.[4] Extensions to the data dictionary also can assist in query optimization against distributed databases.[5]
Additionally, DBA functions are often automated using restructuring tools that are tightly coupled to an active data dictionary.

Visual DataFlex features[9]
provides the ability to use DataDictionaries as class files to form middle layer between the user interface and the underlying database. The intent is to create standardized rules to maintain data integrity and enforce business rules throughout one or more related applications.

Some industries use generalized data dictionaries as technical standards to ensure interoperability between systems. The real estate industry, for example, abides by a RESO's Data Dictionary to which the National Association of REALTORS mandates[10] its MLSs comply with through its policy handbook.[11] This intermediate mapping layer for MLSs' native databases is supported by software companies which provide API services to MLS organizations.

Platform-specific examples

Developers use a

data description specification (DDS) to describe data attributes in file descriptions that are external to the application program that processes the data, in the context of an IBM i.[12] The sys.ts$ table in Oracle stores information about every table in the database. It is part of the data dictionary that is created when the Oracle Database is created.[13] Developers may also use DDS context from free and open-source software
(FOSS) for structured and transactional queries in open environments.

Typical attributes

Here is a non-exhaustive list of typical items found in a data dictionary for columns or fields:

See also

References

  1. ^ ACM, IBM Dictionary of Computing, 10th edition, 1993
  2. ^ Ramez Elmasri, Shamkant B. Navathe: Fundamentals of Database Systems, 3rd. ed. sect. 17.5, p. 582
  3. ^ TechTarget, SearchSOA, What is a data dictionary? Archived 12 February 2009 at the Wayback Machine
  4. ^ U.S. Patent 4774661, Database management system with active data dictionary, 19 November 1985, AT&T
  5. ^ U.S. Patent 4769772, Automated query optimization method using both global and parallel local optimizations for materialization access planning for distributed databases, 28 February 1985, Honeywell Bull
  6. ^ PHPLens, ADOdb Data Dictionary Library for PHP Archived 7 November 2007 at the Wayback Machine
  7. ^ RADICORE, What is a Data Dictionary?
  8. ^ Base One International Corp., Base One Data Dictionary
  9. ^ VISUAL DATAFLEX,features Archived 5 April 2018 at the Wayback Machine
  10. ^ "Real Estate Transaction Standards (RETS) Web API". nar.realtor. 23 January 2015. Retrieved 11 October 2020.
  11. ^ "Handbook on Multiple Listing Policy". nar.realtor. January 2015. Retrieved 11 October 2020.
  12. ^ "DDS documentation for IBM System i V5R3".
  13. ^ "Oracle Concepts - Data Dictionary". dba-oracle.com. Retrieved 13 February 2017.
Wikimedia Commons has media related to Data dictionary.
Retrieved from "https://en.wikipedia.org/w/index.php?title=Data_dictionary&oldid=1248213717"