Updated March 10, 2023
Introduction to DBMS Data Dictionary
DBMS data dictionary is handled and managed by the database administrator because it contains the high-level information about the database, which developers do not usually interact with. It contains information such as the physical location of the database, access to the database, users, data in the database etc. This is often termed as metadata about the database, which simply means it contains the data about the database, which is called metadata. It is not used frequently, it uses by the administrator of the database. But it is a very important part of the dbms which contain the important information.
How to Use?
As we know that what kind of information it contains, it basically contains the detailed information about our database which can be used at a high level. So let’s see about how to use data dictionaries in an SQL server.
- Whenever we try to execute any SQL statement the SQL server makes use of a data dictionary to verify it.
- If we write any statement in sql, and try to execute it, first it parses that statement, and try to verify if the statement is valid or not.
- While parsing it checks the syntax, valid reference fields, attributes and many more things. It should be valid else we will get error but the not desired record we want to fetch.
- To verify all this things and the statement it quickly refers to the data dictionary, which contains all the information about the database, that is metadata.
- So generally it contains the definition of the database, we can use this to gain more information or we can say detailed information about the database. To get the internal details about the database we should go for a data dictionary.
- To get the information we generally write the queries, and this queries get verify by the data dictionary against the definition define them.
So by all these points, we know that how we can access and use the data dictionary in general. Data dictionaries are divided into two types.
Types of Data Dictionary
Given below are the types of data dictionaries:
- Integrated data dictionary
- Standalone data dictionary
These two types are further divided into many parts. But first, we can see information that is contained by the data dictionary in detail see below.
- It contains the physical information about the database, where it is stored or located.
- It contains the names of all tables which are present inside the database, also the name of the schema.
- It also contains the information about the constraints present in the table that is the primary key, foreign key etc.
- It contains information about the view as well.
- Also contain the information about the tables, who created them, who is the owner, security, when the tables have been created etc.
Now the Standalone data dictionary this type of data dictionary is more helpful than integrated one, because they are more flexible, by the use of this we can easily communicate with each other.
Some of the features that data dictionary, offer which are mentioned below. This is not all of them but a few which shows the information about the database.
- End user and administrator
- Relationship between data elements
Active DBMS Data Dictionary
- It is one of the types of data dictionaries, which falls under the integrated data dictionary.
- If we are trying to make any changes to the database or any of its property then it should be reflected into the data dictionary, because it contains the metadata about the database so it should always be updated. But this duration is the responsibility of the database management system that the data dictionary should be updated according to the changes we made to it else we will get wrong information.
- So this part of updating is taking care of by the active data dictionary, hence it updates itself automatically. In short, this is the data dictionary which automatically gets updated when any king of changes is detected at the database, and it is updated by the DBMS, hence keep every update of the database.
Passive Data Dictionary
- This type of data dictionary is not easy to handle because it requires extra effort to maintain it, but this was not the case inactive data dictionary. It basically does not get automatically updated whenever any kind of changes occur in the database. This has to be maintained separately and update manually.
- While updating passive data dictionary when have to be very careful because if any changes miss or wrong entered the data dictionary and database will not be in sync, and if anyone in the future referee to the data dictionary to get the information about the database they will get wrong information.
- This type of data dictionary can be updated using the batches, or manually. So they do not prefer to use, because we need to keep track of the modification that has been done to the database, after that this data dictionary can be updated using the batch method, which means we can run a batch program to update this data dictionary correctly.
By the use of a data dictionary, we can have metadata information about the database. We can decide which one to use, based on the requirement. Easy to use, handle and maintainable by the administrator. All the features and types have been described above for usage.
This is a guide to DBMS Data Dictionary. Here we discuss the introduction, use, types, active DBMS and passive data dictionary respectively. You may also have a look at the following articles to learn more –