Jump to Menu

How to Keep ERD and Data Dictionary Synchronized?

Data modeling is often the first step in database design as the developers will typically create a conceptual model of how data items relate to each other. Data modeling involves a progression from conceptual model to logical model to physical schema. While a conceptual or logical Entity Relationship Diagram (ERD) will focus on the high-level business concepts, a Data Dictionary will provide more detail about a business concept, such as standard definitions of data elements, their meanings, and allowable values.

Compatible edition(s): Enterprise, Professional, Standard

  • January 20, 2014
  • Views: 139,851
  • PDF

What is Data Modeling?

Data modeling is the process of representing data objects and their relationships to other objects. Data modeling is performed to define data requirements of an information system in supporting business processes.

data model sample

What is Data Dictionary?

Data dictionary is the textual description of data objects and their inter-relationships. It is commonly used in confirming data requirements and for database developers to create and maintain a database system. A data dictionary describes the physical attributes of a data element. What is the data type (string, boolean, integer, etc.)? What is the maximum length? What is the format?

sample data dictionary

What is this Tutorial about?

This is data modeling tutorial written to outline the steps you need to take to perform data modeling and producing data dictionary in Visual Paradigm.

Preparation

In order complete this tutorial, make sure you have Visual Paradigm downloaded and installed. Click here to download Visual Paradigm if you do not have it installed.

Part I - Data Modeling

In this section, we are going to use the ERD tools to create a simple database design.

  1. Create a new project in Visual Paradigm. You can create project by selecting File > New Project from the main menu.
  2. To create an Entity Relationship Diagram, select Diagram > New from the toolbar.
  3. In the New Diagram window, select Entity Relationship Diagram and click Next. Give the diagram a name and click OK to confirm.
    create entity relationship diagram
  4. Draw a Company entity. To draw an entity, select Entity from the diagram toolbar (on the left hand side of the diagram) and click on the blank area of the ERD to create one.
    created company entity
  5. Right-click on the Company entity and select New Column from the popup menu.
    creating new column in entity
  6. Enter + ID : integer. "+" stands for a primary key, while ID is the name of the column and integer is its type.
    adding primary key column to entity
  7. Press Enter and create another column name : varchar(255). This column is not a primary key so you don't need to add "+" before it. It's a varchar, with 255 as type length.
    adding name column to entity
  8. Press Enter to confirm editing.
  9. Press Esc to complete column creation.
  10. Let's create one more entity. Move your mouse pointer over the Company entity. Press on the Resource Catalog icon at top right and drag it out.
    create shape with resource catalog
  11. Release the mouse button at the empty space on the right hand side. Select One-to-Many Relationship -> Entity from the Resource Catalog.
    create one to many entity
  12. Enter Employee as entity name.
    created employee entity
  13. Right-click on the Employee entity and select New Column from the popup menu.
  14. Again, enter +ID : integer to create a primary key for this entity.
  15. Press Enter and create another column name : varchar(255).
  16. Press Enter and create another column jobtitle : varchar(50).
  17. Press Enter to confirm editing.
  18. Press Esc to complete column creation.
    columns added to employee entity
  19. Let's describe the entities and columns in detail. Open the Description Pane by clicking on Show Description button at the bottom right of the application window.
    open description pane
  20. Click on the Company entity and enter its description: A company is a business unit that provides good or service.
    describe company entity
  21. Similarly, click on Employee and enter its description: An employee is someone who work in a company.
  22. Repeat the steps for columns in Company entity:
    ID - For the unique identification of company records.
    name - Name of the company.
    defined columns in company entity
  23. Repeat the steps for columns in Employee entity:
    ID - For the unique identification of employee records.
    name - Name of the employee.
    jobtitle - The position of the employee in a company.

Part II - Producing Data Dictionary

Time to produce a data dictionary! There are several reporting tools you can select in producing a data dictionary. In this tutorial, let's try Doc. Composer.

  1. Select Tools > Doc. Composer from the toolbar.
  2. Select Build Doc from Scratch in Doc. Composer.
    select build doc from scratch
  3. This creates an empty report. What you have to do is to fill it up with contents. In this tutorial, the data dictionary. Now, click on the ERD listed under the Diagram Navigator.
    selected erd in diagram navigator
  4. The Template Pane (at bottom left of the application window) lists the available templates of ERD. Select Data Dictionary and drag it to the document.
    drag data dictionary template to document
  5. Release your mouse button to produce the content of data dictionary.
    data dictionary
  6. Are you happy with the content? Let's try something different. Create a new document. Again, select Tools > Doc. Composer from the toolbar and then select Build Doc from Scratch in Doc. Composer.
  7. Click on the ERD listed under the Diagram Navigator.
  8. This time, select Basic template and drag it onto the document.
    erd image in doc composer
  9. Select the Data Dictionary 2 template and drag it onto the document, under the ERD image.
    data dictionary created
  10. Let's change the pages to show in landscape. Click on Document Properties... in the toolbar.
    edit doc properties
  11. In the Document Properties window, open the Page Setup tab.
  12. Select Landscape for Page Orientation.
    set page to landscape
  13. Click OK to return to Doc. Composer. Look pretty nice now?
  14. Now, let's produce a Word document from Doc. Composer. Click on the Export button at the top right corner of Doc. Composer, and then select Word Document... from the popup menu.
    to export word document
  15. In the Export Word Doc. window, specify the output path of the Word file and click Export.
    data dictionary exported to word
    You now got a Word document of your data dictionary. In practice, you can send this to your client, the database administrator or anyone who need to refer to it.

Part III - Keeping Data Dictionary Up-to-Date

One of the most important, and yet challenging part of documenting a design is to keep the document up-to-date, showing the latest changes made in the design. Imagine if you have added more entities in ERD, or have updated the definition of some entities, you probably need to update the data dictionary to reflect the latest changes. The Doc. Composer provides you a 'refresh' function to accomplish this. Let's try it.

  1. Open your ERD.
  2. Add an entity Project from the Employee entity, with a One-to-Many relationship in between.
    erd updated
  3. Now, go back to your document with data dictionary.
    On top of the document there is a toolbar. Click on the Refresh button.
    refresh document in doc composer
  4. You should see the document content updated by showing the new entity in image.
    document updated
    So now, you can export an updated data dictionary, and send it to anyone who need it. :-)



Turn every software project into a successful one.

We use cookies to offer you a better experience. By visiting our website, you agree to the use of cookies as described in our Cookie Policy.

OK