Software QA FYI - SQAFYI

Ensuring Database Quality

By: Scott Ambler

Last month, I summarized a July 2006 survey exploring the current state of data management within IT organizations. The area of greatest concern revealed by this survey is the abysmal level of database testing: 96 percent of organizations considered data to be a corporate asset and 64 percent implemented mission-critical functionality within the database, yet only 40 percent had tests to validate the data and 46 percent the functionality. Worse yet, the survey revealed a lack of recognition that we need to be doing database testing at all—only 32 percent of organizations not testing for data quality, and 39 percent of organizations not testing database functionality realized that they needed to do so. Clearly something is amiss.

There are three fundamental reasons why you need to develop a comprehensive testing strategy for your RDBMS:
* First, data is an important corporate asset and you must ensure that its quality is sufficient for your needs.
* Second, because it is common to implement functionality within a database in the form of stored procedures or stored functions, you must validate this functionality.
* Third, a regression test suite for your database enables modern evolutionary development practices such as database refactoring. Because all modern software development processes are evolutionary in nature, I believe that data professionals must adopt evolutionary techniques such as regression testing and refactoring.

What Is There to Test?
When I describe database testing to people, they're often puzzled—what could there be to test? The answer is that there is quite a bit, and it's all very important. Figure 1 uses threat boundaries (the dashed lines) to indicate that there are two categories of database testing—database interface testing and internal database testing. Database interface testing is focused on ensuring that correct data is being put into the database and taken out of it, whereas internal database testing ensures that the database runs as expected.

Common database interface tests include validating data values before saving them into the database and validating the data values coming back from the database. SQL code is still code, therefore you should test it. If your team uses a persistence framework such as Hibernate or Genome, then you'll want to test your mappings as well.

Internal database testing isn't as common as database interface testing, likely due to a current dearth of testing tools, although it is arguably more important. The most obvious need is for unit testing your stored procedures and functions—not-so-obvious tests that validate your referential integrity (RI) rules. Because RI is typically implemented by triggers, and triggers can get updated and/or dropped, you'll want tests in place to ensure that your database is still working properly. Tests that validate your view definitions are also important because they often implement critical calculations and data combinations. Finally, data quality tests such as validating the default value of a column and ensuring invariants of a single data column, invariants between columns, and invariants between rows should also be performed.

Databases are shared resources, therefore there should ideally be a common database test suite that can be invoked by any application team. A single test suite would enable your organization to support consistent database testing between teams and ensure that your testing investment is spent wisely—do you really want 50 application teams writing the same basic interface tests yet ignoring internal testing?

Full article...

Other Resource

... to read more articles, visit

Ensuring Database Quality