Exploring an Unfamiliar Database with DatabaseSpy

Software developers working on a new app, data professionals in a variety of enterprises, and even database administrators often encounter unfamiliar databases and need a database tool to quickly explore tables and relationships.

Altova DatabaseSpy is a unique multi-database query, design, and comparison tool with a graphical database design editor that empowers users exploring an unfamiliar database to quickly visualize tables, relationships, and even datatype definitions that may be unique among database types.

DatabaseSpy supports all popular databases:

• Firebird
• IBM DB2®
• Informix®
• Microsoft Access™
• Microsoft® Azure SQL
• Microsoft® SQL Server®
• MySQL®
• Oracle®
• PostgreSQL
• Progress OpenEdge
• SQLite
• Sybase®

Chinook is an open source sample database available for SQL Server, Oracle, MySQL, DB2, PostgreSQL, SQLite, and more, implementing a data model for a digital media store. Let’s explore the SQLite version with DatabaseSpy.

The DatabaseSpy Connection Wizard quickly connects to SQLite and adds the database to a new DatabaseSpy project:

A DatabaseSpy project can contain connections to more than one database, even databases of different types. The Online Browser helper window lets us immediately view the list of tables and generate row counts:

For a more complex database, the Online Browser also shows multiple database schemas, views, procedures, functions, etc. as in this view of the SQL Server AdventureWorks sample database:

We are interested in the digital music store database because we also have a CSV file containing metadata from an additional library of media files we would like to add to the database. The CSV file has these fields:

We can click the plus button next to the Track table in the Online Browser window, or better yet, open the table in a new Design Editor window:

The Track table includes keys that reference other tables. A right-click context menu lets us add the referenced tables and illustrates their relationships to build a more complete view in the style of an entity relationship diagram.

Comparing this diagram to our new fields in the CSV file reveals an issue. Our data includes separate fields for the track artist and the album artist. This is meant to accommodate compilations such as movie soundtracks and others where the album might be credited generically with a reference like Various Artists, but we still want to record the correct musician for each track.

The current Track table does not identify the musician for each track, instead it references the AlbumID, which in turn references the Artist table to for one album artist.

Graphically Edit Database Tables

No problem! We can click the plus sign icon in the Track table just below the UnitPrice column to add a new column to the table. DatabaseSpy adds the column with default values:

First, we can rename the new column and assign its datatype. Note that DatabaseSpy conveniently offers a pop-up list to let us choose any legal datatype for this database. The new column for the track artist will rarely be used, since it is only needed when we want to override the ArtistId from the Album. We can simply leave it as a text datatype with a limited length, like the Composer column.

Note the plus sign next to the TrackArtist column name in the screenshot above. DatabaseSpy does not actually modify the database as we edit the diagram. Instead, DatabaseSpy automatically creates a database change script in a separate window.

This lets users edit multiple changes in the Design Editor without risk. When all the edits are complete, we can either execute the script directly, open the script for editing in a new SQL window, save the script in a file, or abandon all the edits and discard the script.

We will open the script in a SQL Editor window to add a limit to the text field length, matching the limit for the Composer column:

We can execute the script from the editor window and reload the database to see the update in the Design Editor.

The Design Editor is just one convenient, intuitive feature to assist exploring an unfamiliar database. Download a free trial to see for yourself how DatabaseSpy simplifies querying, visualizing, managing, comparing, and charting database query results.