Data Dictionary in DBMS

Why Use a Data Dictionary?

Data Dictionary is made up of two words, data which means the collected information through multiple sources, and dictionary meaning the place where all this information is made available. 

A data dictionary is a crucial part of a relational database as it provides additional information about the relationships between multiple tables in a database. The data dictionary in DBMS helps the user to arrange data in a neat and well-organized way, thus preventing data redundancy. 

Below is a data dictionary describing the table containing employee details. 

Some advantages of using a data dictionary are:

1. Data models in DBMS provide very little information about the database, so a data dictionary is very essential to have proper knowledge about entities, relationships, and attributes that are present in a data model.

2. The Data Dictionary provides consistency by reducing data redundancy in the collection and use of data across various members of a team.

3. The Data Dictionary provides structured analysis and design tools by enforcing the use of data standards. Data standards are the set of rules that govern the way data is collected, recorded, and represented.

4. Using a Data Dictionary helps to define naming conventions that are used in a model.

Types of Data Dictionary in DBMS

There are mainly two types of data dictionary in a database management system:

1. Integrated Data Dictionary
2. Stand Alone Data Dictionary

1. Integrated Data Dictionary

Every relational database has an Integrated Data Dictionary contained within the DBMS. This integrated data dictionary acts as a system catalog that is accessed and updated by the relational database. In older databases, they did not include an integrated data dictionary, so in that case, the database administrator had to use Stand Alone Data Dictionary. In DBMS, an Integrated Data Dictionary can bind metadata to data.

The Integrated Data Dictionary can be further classified into two types:

• Active: An active data dictionary is updated automatically by the DBMS whenever any changes are made to the database. This is also known as a self-updating dictionary as it keeps the information up-to-date.

• Passive: In contrast to an active dictionary, a passive dictionary needs to be updated manually whenever any changes are made to the database. This type of data dictionary is difficult to handle as it requires proper handling. Otherwise, the database and the data dictionary will get unsynchronized.

2. Stand Alone Data Dictionary

In DBMS, this type of data dictionary is very flexible as it allows the Database Administrator to define and manage all the confidential data. It doesn't matter whether the data is computerized or not. A stand-alone data dictionary allows database designers to interact with end-users regardless of the data dictionary format.

There is no standard format for a data dictionary. Below given are some of the common elements:

1. Data Elements: The Data Dictionary stores the definition of all the data elements such as name, datatype, storage formats, and validation rules.

2. Tables: All information regarding the table, such as the user who created the table, the number of rows and columns, the date on which the table was created and accessed, etc.

3. Index: Indexes for defined database tables are stored in the data dictionary. DBMS stores the index name used by the attributes, location, and characteristics of the index, as well as the date of creation, in each index.

4. Programs: Programs defined to access the database, including reports, application and screen formats, SQL queries, etc., are also stored in the data dictionary.

5. Relationship between data elements: The Data Dictionary stores the type of relationship; for example, if it is compulsory or optional, the cardinality of the relationship and connectivity, etc.

6. Administrations and End-Users: The Data Dictionary stores all the information of the administration along with the end-users.

The metadata in DBMS, which is stored in the Data Dictionary, is similar to a monitor that monitors the use of the database and the allocation of permission to access the database by the users.

How to Create a Data Dictionary?

As discussed above, most businesses rely on database management systems having an integrated data dictionary as they are updated automatically and are easy to maintain. Documentation for a data dictionary can be generated in various types of relational databases like MySQL, SQL Server, Oracle, etc. 

While creating a stand-alone data dictionary, the database administrator can take the help of a template in SQL Server, Oracle, or even Microsoft Excel.

Various notations used to create a data dictionary are:

Challenges with Data Dictionary

In the above sections, we discussed the advantages of a data dictionary, but dealing with a data dictionary has its challenges. 

A data dictionary can be difficult and time-consuming to create if we have not done any kind of data preparation. Without proper data preparation, a data dictionary might only standardize a part of a database. While doing data preparation for large-scale data can be a huge maintenance burden for little value and quickly becomes outdated,

Read More:

• Data Manipulation Language in DBMS.