Data dictionary in database — Active and passive data dictionary | 100% Practical based

Data Dictionary its types and practical working of Data Dictionary

Topics covered in this articles:

1. Why we are covering this in the Software Engineering?

2. What is Data Dictionary?

3. What is Passive Data Dictionary?

4. What is Active data dictionary?

5. Practical making of passive Data Dictionary?

6. Practical of active data dictionary using MS-SQL Server?

1.Why this topic in Software Engineering?

This topic is relevant to Database and usually we study its practical working in database subjects only but we also cover this topic in software engineering because when you do the project documentation (Software Engineering) of the project you have to add the data dictionary of the data base of your project into the project documentation.

2.What is Data Dictionary?

Data dictionary is a Dictionary which keeps the meta data in it and you can access when ever it is needed.

What is metadata?

MetaData is the data about some data, Means if you have a table in the database that is the data and the further data about the table i.e. Number of attributes in the table, Data types of attributes in the table is the meta data of the table.

So the Data Dictionary of a database keeps the record of the tables or say information of the tables of that data.

further there are two kind of Data Dictionaries:

1. Passive Data Dictionary

2. Active Data Dictionary

3. What is Passive Data Dictionary?

This is the data dictionary of the Database which made manually and manage manually, you may made a passive data dictionary of the database into the MS Word, MS Excel or in any other word processing tool of your choice and manage it manually.

- What is meant by managing manually?

That means if you add or manipulate anything in the database tables you have to update the latest information in the Data Dictionary manually by going there.

It also have a disadvantage i.e. You may forget some time to manage the data dictionary and it may create the records conflict in the future.

Lets look at the example of a table and its data Dictionary:

This is the simple table of the database Named Student and have 2 records of the students this is the data for you.

Now Look at the MetaData of this table which a passive data dictionary contains:

This is the metadata of the student table and if you are planing to opt. the passive data dictionary for your project due to any reasson you may manage and make tables like this in Excel or MS Word file amd if you make some changes in the database you have to update those changes here as well.

4.What is active data dictionary?

That’s my choice, this is something you don’t need to manage manually, you don’t need to create excel and word file, this get created automatiicaly and keep it self up to date with your database design and you can access it when ever needed.

Different tools have different ways to access it but here we are going to check it with MS-SQL Server how it works.

All you need to open your MS SQL server and go to new query than you have to follow the steps shown in the video embedded below in this article.

The queries I used in the video I am going to provide you here so you can easily copy and paste them in your MS SQL Server.

To check attribute name availability in different tables of the database let suppose attribute is “Name”

To learn the use of above mention queries and see the result follow the tutorial given below