Which of the following is not stored in the data dictionary of a dbms?

Ironically, because of its importance, it is invisible to most database users. Typically, only database administrators interact with the data dictionary.

A data dictionary is also called a metadata repository.

Advertisement

Techopedia Explains Data Dictionary

A Data Dictionary contains information about attributes or fields of a certain data set. In a relational database, the metadata in the data dictionary includes the following:

• Names of all tables in the database and their owners.

• Names of all indexes and the columns to which the tables in those indexes relate.

• Constraints defined on tables, including primary keys, foreign-key relationships to other tables, and not-null constraints.

• Additional physical information about the tables including their storage location, storage method, etc.

For example, a commercial bank's database containing information about clients can have attributes for client name, birth date, street address, financial savings, account and credit card number, loans, etc.

Each attribute occupies a row in a spreadsheet, while various columns provide additional elements that describe that attribute (whether it’s optional or required for a record, the type of data, its location, etc.).

A data dictionary might look like the one below:

For most relational database management systems (RDBMS), the database management system software needs the data dictionary to access the data within a database. For example, the Oracle DB software has to read and write to an Oracle DB. However, it can only do this via the data dictionary created for that particular database.

For instance, following the above bank's database example, the administrator wants to determine which table holds information about loans. Making an educated guess that the table most likely has the word "LOAN" in it, he would issue the following query on the data dictionary (the first query is for an Oracle DB, while the second is for an SQL Server DB):

• SELECT * FROM DBA_TABLES WHERE TABLE_NAME LIKE '%LOAN%';

• SELECT * FROM SYSOBJECTS WHERE TYPE='U' AND NAME LIKE '%LOAN%';

A data dictionary can be either active or passive. It may happen that the structure of the database has to be changed, such as to add new attributes or to remove some obsolete ones. If those changes are updated automatically in the data dictionary by the database management dictionary, then the data dictionary is an active one.

Conversely, if the database manager maintains the data dictionary as a separate entity that has to be updated manually, it is referred to as a passive data dictionary. Other than requiring additional work to be synced, passive data dictionaries are prone to errors when data in the database and dictionary do not match anymore.

A data dictionary contains metadata i.e data about the database. The data dictionary is very important as it contains information such as what is in the database, who is allowed to access it, where is the database physically stored etc. The users of the database normally don't interact with the data dictionary, it is only handled by the database administrators.

The data dictionary in general contains information about the following −

• Names of all the database tables and their schemas.
• Details about all the tables in the database, such as their owners, their security constraints, when they were created etc.
• Physical information about the tables such as where they are stored and how.
• Table constraints such as primary key attributes, foreign key information etc.
• Information about the database views that are visible.

This is a data dictionary describing a table that contains employee details.

Field Name
Data Type
Field Size for display
Description
Example
Employee
Number
Integer
10
Unique ID of each employee
1645000001
Name
Text
20
Name of the employee
David Heston
Date of Birth
Date/Time
10
DOB of Employee
08/03/1995
Phone Number
Integer
10
Phone number of employee
6583648648

The different types of data dictionary are −

Active Data Dictionary

If the structure of the database or its specifications change at any point of time, it should be reflected in the data dictionary. This is the responsibility of the database management system in which the data dictionary resides.

So, the data dictionary is automatically updated by the database management system when any changes are made in the database. This is known as an active data dictionary as it is self updating.

Passive Data Dictionary

This is not as useful or easy to handle as an active data dictionary. A passive data dictionary is maintained separately to the database whose contents are stored in the dictionary. That means that if the database is modified the database dictionary is not automatically updated as in the case of Active Data Dictionary. 

So, the passive data dictionary has to be manually updated to match the database. This needs careful handling or else the database and data dictionary are out of sync.

Which of the following is not an example of a database system?

Where are metadata and data dictionary stored?

What does the data dictionary identify Mcq?

What does the data dictionary identify?