Data Dictionary in DBMS

Till now, we learned and understood about relations and its representation. In the relational database system, it maintains all information of a relation or table, from its schema to the applied constraints. All the metadata is stored. In general, metadata refers to the data about data. So, storing the relational schemas and other metadata about the relations in a structure is known as Data Dictionary or System Catalog.

A data dictionary is like the A-Z dictionary of the relational database system holding all information of each relation in the database.

The types of information a system must store are:

  • Name of the relations
  • Name of the attributes of each relation
  • Lengths and domains of attributes
  • Name and definitions of the views defined on the database
  • Various integrity constraints

With this, the system also keeps the following data based on users of the system:

  • Name of authorized users
  • Accounting and authorization information about users.
  • The authentication information for users, such as passwords or other related information.

In addition to this, the system may also store some statistical and descriptive data about the relations, such as:

  • Number of tuples in each relation
  • Method of storage for each relation, such as clustered or non-clustered.

A system may also store the storage organization, whether sequential, hash, or heap. It also notes the location where each relation is stored:

  • If relations are stored in the files of the operating system, the data dictionary note, and stores the names of the file.
  • If the database stores all the relations in a single file, the data dictionary notes and store the blocks containing records of each relation in a data structure similar to a linked list.

At last, it also stores the information regarding each index of all the relations:

  • Name of the index.
  • Name of the relation being indexed.
  • Attributes on which the index is defined.
  • The type of index formed.

All the above information or metadata is stored in a data dictionary. The data dictionary also maintains updated information whenever they occur in the relations. Such metadata constitutes a miniature database. Some systems store the metadata in the form of a relation in the database itself. The system designers design the way of representation of the data dictionary. Also, a data dictionary stores the data in a non-formalized manner. It does not use any normal form so as to fastly access the data stored in the dictionary.

For example, in the data dictionary, it uses underline below the value to represent that the following field contains a primary key.

So, whenever the database system requires fetching records from a relation, it firstly finds in the relation of data dictionary about the location and storage organization of the relation. After confirming the details, it finally retrieves the required record from the database.