Database Visualization

Database Visualization

Contents

• Let's Begin . . .
• Abstract of this Article
• Visualization Concepts
• Planar Graph Theory
• A Note about SVG


• Flow of the tool
• Step 1: Database Connectivity
• Step 2: Selecting the Needed Data
• Step 3: Selecting Relationships
• Step 4: Visualization and Code Generation
• The CVisualization Class


• Almost Done . . .
• Relevant Links
• Conclusion
• Current Limitations
• Proposed Features
• Disclaimer

Abstract of this Article

When handling a project with hundreds of tables, it takes some time to understand the flows within the database. This is especially so if there is no document available. A possible scenario is legacy code up for maintenance or migration. After going through just a few tables, it becomes too cumbersome to maintain the relationships in your mind. This article aims to create a simple tool for visualizing database tables and relations and a database map to refer to.

The main aim was to generate the documentation automatically for the database relationships as and when required. The reason for using the WinGraphViz library was to make sure that the database relationships did not have much crossing and that the end result looked clutter free and presentable.

Visualization Concepts

"A picture is worth a thousand words." As the saying goes, it is better to have a picture of any complex entity to understand it easily, especially in the areas where a lot of operations are done on a logical level rather than handling physical objects. Entity relationship diagrams, use case diagrams, database diagrams, organization hierarchies, and even a family tree are candidates for a a visualization method that takes care of laying out the relationships so that they are easily distinguishable.

A good visualization algorithm allows the user to see the pattern that may be difficult or impossible to see when the same data is presented in raw form. Such a method is known as a "Planar Graph." There are three main points to note when developing a visualization tool. They are:

• Graph layout
• Interaction with the user and
• Presentation

You will use the WinGraphViz library to solve all the above mentioned problems. WinGraphviz is a free software based on the Graphviz project by AT&T. The graph layout is taken care by the algorithm and you'll generate the output in SVG for handling the Interaction and Presentation parts.

Planar Graph Theory

In graph theory, a planar graph is a graph that can be drawn so that no edges intersect. For those interested in the details, please download this file. A graph consists of two components: vertices and edges. Vertices are simply labels such as numbers, and edges are the connections between the vertices. Edges also can have direction that creates a directed graph, or Digraph for short. The WinGraphViz library attempts to create such a Directed Planar Graph. Because it is available as a COM library, you'll use it to create your database visualization tool. For a sample of what graph optimization can do, check out the following diagram.

The image was taken from the paper Automatic Graph Drawing. The site also mentions the Automatic Graph Library or AGD Library for short; it also can be used for visualization purposes.

You will use a similar graph drawing algorithm to make your output diagram clutter free and neat.

A Note about SVG

SVG is a language for describing two-dimensional graphics and graphical applications in XML. SVG files are compact and provide high-quality graphics on the Web, in print, and on resource-limited handeld devices. In addition, SVG supports scripting and animation, so is ideal for interactive graphics. The Adobe SVG Viewer is free and is available as an ActiveX control that enables you to see the output in a Web browser or a Windows application. SVG output is highly recommended due to its zooming capabilities. Because SVG is vector based, the clarity of the output would remain constant no matter whether you zoom in or zoom out.

Hints about using the Adobe SVG Viewer:

• Use the Ctrl key to zoom in.
• Use the Shift + Ctrl keys to zoom out.
• Use the Alt key to pan.

Step 1: Database Connectivity

To implement this part, you will use the "SQLSrvEnumerator class" available here. On selection of the server combo box, the list of servers available on the network is enumerated and displayed. The user credentials are obtained to log in to the selected server. The list of databases is enumerated and is automatically populated in a combo box; you will select one from the list. The database connectivity is handled using ODBC here. The remaining operations are done using ADO. Although the connection can also be done using ADO, I was plainly too lazy to change the code after deciding to use ADO instead of ODBC.

Step 2: Selecting the Needed Data

In this simple operation, you will select the needed columns. When the page is loaded, the "Tables" combo is automatically populated based on selection of the database in the previous step. On selecting a table from the list, the "Columns" combo box is populated with all the columns in the selected table. Selecting the "All" value from the combo selects all the columns in the table. You can move the column names up or down based on how you want your diagram to look. This option is purely for cosmetic purposes.

Step 3: Selecting Relationships

This operation is crucial to how your diagram is generated. You need to select the relationships between tables in this step. Select the tables first and then select the columns by which they are actually related to each other. For example, Table1, PK; and Table2, FK. The relations entered here are used to generate your visualization.

Step 4: Visualization and Code Generation

You've now reached the final step. You can enter comments if needed. The "Generate Query" button does exactly what it says. It generates a SQL query based on your selection of tables, columns, and relationships. It generates only a simple query now. In the very first upgrade of this article, I promise to do something better. It was added as an afterthought because this article only tries to focus on the visualizing part. The "Image Type" combo supports various image formats such as JPG, SVG, PNG, GIF, and EMF. As per your selection, the output diagram is generated. The "Show Relationship Labels" check box displays the selected relationships in the diagram.

The CVisualization Class

The CVisualization class encapsulates the functionalities of the Graph drawing. The available methods are:

void AddComment(CString strComment)

void AddTable(CString strTableName,
              CStringArray
              &strArrayFields)

void AddLink(CString strNodeOne,
             CString strNodeTwo,
             CString strLabel,
             CString strColor="black")

BOOL SaveToFile(int nImageFormat,
                BOOL bOpenFile)

Relevant Links

• About WinGraphViz
• Download WinGraphViz
• A Technique for Drawing Directed Graphs
• The Boost Graph Library (BGL)
• Graphviz - Graph Visualization Software
• The dot guide
• Drawing graphs with Graphviz
• Automatic Graph Drawing Algorithms
• Download Adobe SVG Viewer
• SQL Server and Database Enumerator by Santhosh Rao
• A VisualStudio-like ListBox class by Stefano Passiglia

Conclusion

The CVisualization class available with the source code encapsulates all the drawing functions and output functionality needed for your database visualization tool. Please install the Wingraphviz library to execute your tool properly.

Current Limitations

This tool can only handle simple relations. It cannot handle nested Select statements. The method used to select the tables and the columns is also quite crude. The visualization generated only links the tables and not the actual keys.

This tool has only been tested with SQL Server 2000 and has not been tested with other databases.

Proposed Features

1. Handling nested Select statements
2. Handling complex relationships
3. More visual representation of the table and column selection
4. Code generation (Client and Server) based on the selected relationships
5. Complex SQL query generation

You can find the latest copy of this tool at My Web site, too.

Disclaimer

This tool is not meant to replace the SQL Server Database Diagram Designer. It is only complementary. In situations where the relationships are not maintained properly in the Database and when a better visual representation is needed, this tool can be used.