How to Generating DB Specification from Database>

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.

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:

Go to Tools > DB > Reverse Database...
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 MS SQL Server (jDTS Driver) in the Driver field.
Press the green down arrow button next to Driver file field to download the driver in case you don't have one.
- Note that you need to have Internet connection as well as administrator permission in order to download and install the driver file.
Fill in the Hostname, port number, Database name as well as the User name and Password for connecting to your database server.
Click Test Connection to confirm the correctness of connection settings. Click Next to proceed.
Choose Selected Schema then choose dbo In the Selecting Schemas page. Click Next to proceed.
Click Next again in the Selecting Tables page to reverse all the tables in our database to ER model.
Have a review on the entities and columns which have been generated, then click Finish to complete the process.

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:

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.
Press Enter to confirm the change.
Uncheck the Remove selection after drag to diagram in the Reversed Entities window.
Drag and drop the entities related to order processing, including Customer, Order, OrderLine, Payment and Product into diagram.
The ERD related to order processing is created and we can touch up its layout.
Now let's move on to create the other ERDs. Click Create ERD in the Reversed Entities window to create a new ERD.
Drag and drop the entities again to create the following ERDs.

ERD Entities

Product Brand Brand, Product

Admin Staff, Role
Close the Reversed Entities window.

ERD	Entities
Product Brand	Brand, Product
Admin	Staff, Role

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.

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

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:

Select Tools > Doc. Composer from the application toolbar.
Click on Build Doc from Scratch.
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.
Keep Order selected in Diagram Navigator. This time, drag the Data Dictionary template onto the document, below the image.
Repeat the previous two steps on diagram Product Brand and Admin.
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.
Open the Page Setup tab and then select Landscape for Page Orientation.
Click OK to confirm.
Refresh the document content by clicking on the Refresh button above the document editor.
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.
In the Export PDF Doc. window, enter the output path of the PDF file and the Document Info such as Title, Author and Subject.
Click Export.