Visual Paradigm logo
Jump to Menu

Generating Database Specification from Database

Imagine one day, the database administrator in your company has retired and you are appointed to take care of your corporate database. There is no documentation available and everything is in the administrator's mind. Now, your boss asks you to make some changes on the database, what can you do?

Compatible edition(s): Enterprise, Professional, Standard

  • January 20, 2016
  • Views: 20,778
  • PDF
  • Video

With Visual Paradigm, you can reverse your database to Entity-Relationship (ER) models which allows you to analyze and document them by creating context-sensitive Entity-Relationship Diagram (ERD). Then, you can create a detailed database specification out of your model.

00 illustration

With the database specification, your database becomes understandable. In this tutorial, we will use the Online Shop with Microsoft SQL Server database as an example to show you how it works.

Reverse your database to Entity Relationship (ER) Model

The first step to do when creating specification for our database is to reverse it to an ER Model. To do this:

  1. Go to Tools > DB > Reverse Database...
    01 reverse db
  2. In the Select Language page of the Database to Data Model window, select Popup entities tree (can drag entities to diagram on demand) in the Result field. Click Next to proceed.
    Select the result of reversal
  3. Select MS SQL Server (jDTS Driver) in the Driver field.
    Select MS SQL
  4. Press the green down arrow button next to Driver file field to download the driver in case you don't have one.
    Download JDBC driver
    • Note that you need to have Internet connection as well as administrator permission in order to download and install the driver file.
  5. Fill in the Hostname, port number, Database name as well as the User name and Password for connecting to your database server.
    Specify database connection
  6. Click Test Connection to confirm the correctness of connection settings. Click Next to proceed.
  7. Choose Selected Schema then choose dbo In the Selecting Schemas page. Click Next to proceed.
    Select schema
  8. Click Next again in the Selecting Tables page to reverse all the tables in our database to ER model.
    Select Tables
  9. Have a review on the entities and columns which have been generated, then click Finish to complete the process.
    Preview tables to reverse

Create Context Sensitive ERDs

Now, your database has been reversed to an ER model in the project. We can then move on to visualize them into an ERD, so that we can have better understanding of it. We can create multiple context sensitive ERDs and each focus on a small part of the database, so that they can be read and understood more easily. To create context sensitive ERDs:

  1. By default, a new ERD has been automatically created. Let's rename it to Order and use it to model the entities related to the order processing of our shop. With the Reversed Entities window opened, double click on the diagram name in breadcrumb and enter Order as new name.
    Rename diagram
  2. Press Enter to confirm the change.
  3. Uncheck the Remove selection after drag to diagram in the Reversed Entities window.
    Uncheck remove selection in Reversed Entities window
  4. Drag and drop the entities related to order processing, including Customer, Order, OrderLine, Payment and Product into diagram.
    Drag entities to ERD
  5. The ERD related to order processing is created and we can touch up its layout.
    Updated ERD
  6. Now let's move on to create the other ERDs. Click Create ERD in the Reversed Entities window to create a new ERD.
    Create ERD
  7. Drag and drop the entities again to create the following ERDs.
    ERD Entities
    Product Brand Brand, Product
    Admin Staff, Role
  8. Close the Reversed Entities window.

Document the details of database

After creating the context sensitive ERDs, we now have a good understanding of our database. We can then start to document its details. To document the database, select the entities in the ERD and then enter the description for the entity in the Description pane. You can show the Description pane by clicking the Show Description button on the right of the status bar.

Show Description pane

Then, select the entities one by one and enter their description.

Describe entity

Repeat the steps to fill in the documentation for the following entities.

Table Description
Brand The brand table stores the information about brands, which links to products.
Customer This table stores the contact information for customers.
Order The order table stores the details about the different orders, including the date and time the orders were placed, as well as the special remark(s) of orders.
OrderLine This is a link entity between order(s) and product(s).
Payment Each customer can register for multiple credit cards for making payment but each order can only be settled by a single credit card.
Product This table records all the information about products, including its name and description.
Role This table stores the user role information.
Staff This table stores the contact information for all staff.

Produce database specification

Now, we are ready to produce the specification for our database. To produce the specification as PDF:

  1. Select Tools > Doc. Composer from the application toolbar.
    Open Doc. Composer
  2. Click on Build Doc from Scratch.
    Select 'Build from Scratch'
  3. You are presented the Diagram Navigator, Template Pane and an empty document (editor). Let's create content in the document. Select the ERD Order in Diagram Navigator. Then, drag the Basic template out from the Template Pane and release it on the document to create content. You should see an image of your ERD with its name under it.
    Create content in Doc. Composer
  4. Keep Order selected in Diagram Navigator. This time, drag the Data Dictionary template onto the document, below the image.
    Drag Data Dictionary template to document
  5. Repeat the previous two steps on diagram Product Brand and Admin.
  6. As you can see the table is a bit narrow to fit the entity description nicely. Let's change the orientation of document to landscape. To do this, click on Document Properties... above the document editor.
    Select Document Properties
  7. Open the Page Setup tab and then select Landscape for Page Orientation.
    Change document to show in landscape
  8. Click OK to confirm.
  9. Refresh the document content by clicking on the Refresh button above the document editor.
    Refresh document
  10. Let's export the document as a PDF. Click on the Export button at the top right of the document editor and then select PDF Document... from the popup menu.
    Export document
  11. In the Export PDF Doc. window, enter the output path of the PDF file and the Document Info such as Title, Author and Subject.
    Export PDF Doc window
  12. Click Export.

After that, the details of your database are generated to a PDF document, which allows you to have further study or share with your colleagues.

Document generated

Download Sample Database

You can download the sample database script to walk through this tutorial.

Watch this Tutorial on YouTube

Here is the video version of this tutorial.



Turn every software project into a successful one.