Editing Data dictionary

{{Short description|Set of metadata that contains definitions and representations of data elements}}
{{Distinguish|Dictionary (data structure)}}
[[File:Data dictionary.png|thumb|300x300px|A simple layout of a data dictionary]]
{{Use dmy dates|date=October 2023}}
A '''data dictionary''', or [[metadata repository]], as defined in the ''IBM Dictionary of Computing'', is a "centralized repository of information about data such as meaning, relationships to other data, origin, usage, and format".<ref>ACM, [http://portal.acm.org/citation.cfm?id=541721 IBM Dictionary of Computing], 10th edition, 1993</ref> ''[[Oracle Corporation|Oracle]]'' defines it as a collection of tables with metadata. The term can have one of several closely related meanings pertaining to [[database]]s and [[database management system]]s (DBMS):

* A [[document]] describing a database or collection of databases
* An integral [[software component|component]] of a [[Database management system|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 [[Data definition language|DDL]] and [[Data manipulation language|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.<ref>Ramez Elmasri, Shamkant B. Navathe: ''Fundamentals of Database Systems'', 3rd. ed. sect. 17.5, p. 582</ref>

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 [[User (computing)|users]] and [[Application software|application]] developers can benefit from an authoritative data dictionary document that catalogs the organization, contents, and conventions of one or more databases.<ref>TechTarget, ''SearchSOA'', [http://searchsoa.techtarget.com/sDefinition/0,,sid26_gci211896,00.html What is a data dictionary?] {{Webarchive|url=https://web.archive.org/web/20090212170420/http://searchsoa.techtarget.com/sDefinition/0,,sid26_gci211896,00.html |date=12 February 2009 }}</ref> This typically includes the names and descriptions of various [[Table (database)|tables]] ([[Row (database)|records]] or [[Entity (computer science)|entities]]) and their contents ([[Column (database)|fields]]) plus additional details, like the [[Data type|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 model|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 <code>CREATE TABLE</code>, <code>CREATE UNIQUE INDEX</code>, <code>ALTER TABLE</code> (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. [[middleware]], which communicates with the underlying DBMS data dictionary. Such a "high-level" data dictionary may offer additional features and a degree of flexibility that goes beyond the limitations of the native "low-level" data dictionary, whose primary purpose is to support the basic functions of the DBMS, not the requirements of a typical application. For example, a high-level data dictionary can provide alternative [[entity-relationship model]]s tailored to suit different applications that share a common database.<ref>U.S. Patent 4774661, [http://www.freepatentsonline.com/4774661.html Database management system with active data dictionary], 19 November 1985, AT&T</ref> Extensions to the data dictionary also can assist in [[query optimization]] against [[distributed database]]s.<ref>U.S. Patent 4769772, [http://www.freepatentsonline.com/4769772.html Automated query optimization method using both global and parallel local optimizations for materialization access planning for distributed databases], 28 February 1985, Honeywell Bull</ref> Additionally, DBA functions are often automated using restructuring tools that are tightly coupled to an active data dictionary.

[[Software framework]]s aimed at [[rapid application development]] sometimes include high-level data dictionary facilities, which can substantially reduce the amount of programming required to build [[Menu (computing)|menus]], [[Form (programming)|forms]], reports, and other components of a database application, including the database itself. For example, PHPLens includes a [[PHP]] [[class library]] to automate the creation of tables, indexes, and [[foreign key]] constraints [[Portability (software)|portably]] for multiple databases.<ref>PHPLens, [http://phplens.com/lens/adodb/docs-datadict.htm ADOdb Data Dictionary Library for PHP] {{Webarchive|url=https://web.archive.org/web/20071107170943/http://www.phplens.com/lens/adodb/docs-datadict.htm |date=7 November 2007 }}</ref> Another PHP-based data dictionary, part of the RADICORE toolkit, automatically generates program [[Object (computer science)|objects]], [[Scripting language|scripts]], and SQL code for menus and forms with [[data validation]] and complex [[join (SQL)|joins]].<ref>RADICORE, [http://www.radicore.org/viewarticle.php?article_id=5 What is a Data Dictionary?]</ref> For the [[ASP.NET]] environment, [[Base One International|Base One's]] data dictionary provides cross-DBMS facilities for automated database creation, data validation, performance enhancement ([[Cache (computing)|caching]] and index utilization), [[application security]], and extended [[data type]]s.<ref>Base One International Corp., [http://www.boic.com/b1ddic.htm Base One Data Dictionary]</ref> [[Visual DataFlex]] features<ref>VISUAL DATAFLEX,[http://www.visualdataflex.com/features.asp?pageid=1030 features] {{Webarchive|url=https://web.archive.org/web/20180405135738/http://www.visualdataflex.com/features.asp?pageid=1030 |date=5 April 2018 }}</ref> 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 [https://ddwiki.reso.org RESO's Data Dictionary] to which the [[National Association of Realtors|National Association of REALTORS]] mandates<ref>{{Cite web|title=Real Estate Transaction Standards (RETS) Web API|url=https://www.nar.realtor/about-nar/policies/mls-policy/real-estate-transaction-standards-rets-web-api|access-date=2020-10-11|website=nar.realtor|date=23 January 2015 |language=en}}</ref> its [[Multiple listing service|MLSs]] comply with through its policy handbook.<ref>{{Cite web|title=Handbook on Multiple Listing Policy|url=https://www.nar.realtor/handbook-on-multiple-listing-policy|access-date=2020-10-11|website=nar.realtor|date=January 2015 |language=en}}</ref> 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]].<ref>{{cite web |url=http://publib.boulder.ibm.com/infocenter/iseries/v5r3/topic/dds/rbafpddsmain.htm |title=DDS documentation for IBM System i V5R3}}</ref> 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.<ref>{{Cite web|url=http://www.dba-oracle.com/concepts/data_dictionary.htm|title=Oracle Concepts - Data Dictionary|website=dba-oracle.com|access-date=2017-02-13}}</ref> 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:

* Entity or form name or their ID (EntityID or FormID). The group this field belongs to.
* Field name, such as [[RDBMS]] field name
* Displayed field title. May default to field name if blank.
* Field [[data type|type]] (string, integer, date, etc.)
* [[Measure (data warehouse)|Measures]] such as min and max values, display width, or number of decimal places. Different field types may interpret this differently. An alternative is to have different attributes depending on field type.
* Field display order or tab order
* Coordinates on screen (if a positional or grid-based UI)
* Default value
* Prompt type, such as drop-down list, combo-box, check-boxes, range, etc.
* Is-required (Boolean) - If 'true', the value can not be blank, null, or only white-spaces
* Is-read-only (Boolean)
* Reference table name, if a foreign key. Can be used for validation or selection lists.
* Various event handlers or references to. Example: "on-click", "on-validate", etc. See [[event-driven programming]].
* Format code, such as a [[regular expression]] or COBOL-style "PIC" statements
* Description or synopsis
* [[Database index]] characteristics or specification

==See also==
*[[Data hierarchy]]
*[[Data modeling]]
*[[Database catalog]]
*[[Database schema]]
*[[ISO/IEC 11179]]
*[[Metadata registry]]
*[[Semantic spectrum]]
*[[Vocabulary OneSource]]
*[[Metadata repository]]

==References==
{{Reflist}}

==External links==
{{Commons category|Data dictionary}}
*Yourdon, ''Structured Analysis Wiki'', [https://web.archive.org/web/20120412150613/http://yourdon.com/strucanalysis/wiki/index.php?title=Chapter_10 Data Dictionaries (Web archive)]
*Octopai, [https://www.octopai.com/data-dictionary-vs-business-glossary/ Data Dictionary vs. Business Glossary]

{{Data warehouse}}

{{Authority control}}

{{DEFAULTSORT:Data Dictionary}}
[[Category:Data management]]
[[Category:Data modeling]]
[[Category:Knowledge representation]]
[[Category:Metadata]]