This article is a field report from a data quality assessment of customer databases. The process it describes combines three automated database audits: one of the database schema, one of the database structure and one of the database content. The schema audit looks for design flaws and rule violations. The structure audit measures the size, complexity and quality of the database model. The content audit works on the data itself and uncovers invalid values, missing records and redundant records. Together, the three audits show how good a database really is and whether a data reengineering or data cleansing project is needed.
Why Data Quality Declines in Existing Databases
Most organizations run databases that are the result of a long history. It started when a database designer drew up the design based on the information available at the time. Then a database schema reflecting that design was written or generated. The database went live and applications started using it. As more applications came along, the structure was changed or extended to serve them. These changes were often made ad hoc, so the structure grew more complex and its quality declined, much like software systems do under the laws of software evolution. On top of this structural decay, faulty programs corrupted the content by storing wrong values and deleting records that were still needed. In large databases this kind of erosion is hard to spot, but over time it spreads like a cancer and causes more and more system failures. Erosion hits the data just as hard as it hits the programs.
Failing to Anticipate How the Data Will Evolve
At the start of a database’s life cycle, the designer has to make assumptions about the volume of data and how it will be used. They also have to predict how the data will evolve. The database design rests on those assumptions, and the same assumptions carry over into the schema, where the tables are defined and linked by foreign keys and indexes. If the assumptions hold, the database can evolve without trouble. If they don’t, the structure soon becomes outdated and the form no longer fits the content. It also becomes harder and harder to adapt. When the data volume grows far beyond expectations and the data is used in ways nobody planned for, the database has to be reengineered.
Missing Data Independence
More often than not, database designers can neither predict how much a database will actually grow nor foresee how it will really be used. When they design it for the first time, they usually look at it from the point of view of a single application, rarely from a global view that covers every possible application. So from day one, the data is not independent of the applications that use it. Later the structure has to be adapted to accommodate more and different applications, and the further the design is stretched, the more brittle it becomes. Eventually it suits none of the applications.
Violating the Normal Forms
Every makeshift patch to the database structure has a cost. Instead of creating new subtables when additional attributes are needed, the local database administrator simply adds them to existing tables. The rows get longer and harder to handle. When new keys are needed, they are added as indexes, so the number of indexes keeps growing. Data groups are added to existing rows, which violates the second normal form, and the same data elements are repeated to avoid creating new subtables, which violates the first normal form. Extra foreign keys increase the number of dependencies between tables, and subkeys are added to the primary key that often don’t relate to the row as a whole. That amounts to a violation of the third normal form. In the end the database no longer conforms to the normal forms for relational databases, and the result is lower quality and higher complexity.
Misusing Stored Procedures
Stored procedures are another factor that erodes portability and data independence. Used only for access operations and to protect data integrity, they would be fine. But they are often misused to hold processing logic. Instead of putting business rules into separate rule modules or check routines into the client components, developers hide them in stored procedures, where the tools that process the programs can no longer see them. Stored procedures full of selections and loops, sets and declarations are a clear sign of misuse. They tie the database to one specific application.
Inconsistent and Incorrect Data Updates
A database’s content suffers not only from errors in the programs that run against it, but also from inconsistent changes. The type of a data attribute that appears in two tables may be changed in one table but not in the other, for example, or the value of an attribute may no longer match the type that attribute has in the program using it. The Frankfurt Stock Exchange once had to close for half a day because an invalid value in the database triggered a data exception in the processing job. Existing databases contain far more bad data than their users assume, and any of it can bring a system down at any time.
Missing and redundant records are another content problem. Missing records are records that are still referenced but have been deleted, on purpose or by accident. One application may have deleted them while another still needs them. Redundant records are records that should have been deleted but, for whatever reason, weren’t. They linger in the database like dead bodies and take up valuable storage space even though nothing uses them anymore.
The flaws described here are only a subset of the many quality problems in application databases. Poor database quality has become a major concern for IT users, and it is the motivation for the three audit methods presented in this article:
- Analyzing the database schema
- Measuring the database structure
- Validating the database content
Database Audit: Static Analysis of the Schema
To detect quality defects and to measure the size, complexity and quality of the database structure, the database model has to be analyzed statically. That calls for an automated audit process.
The first step in a database audit is a static analysis of the database schema to find rule violations. Just as with program code, schema code needs rules that enforce good coding practice. Some of these rules serve specific data quality goals such as security, compatibility, performance and portability. Others are meant to make the database easier to change and extend.
To weight rule violations differently, you first have to classify them. The database auditor distinguishes between major, medium and minor violations. Major violations, such as breaking the first normal form, are a serious obstacle to evolving and reusing the data, and they can also cause errors. Medium violations, such as exceeding a defined size limit, reduce the modularity and maintainability of the database. Minor violations, such as ignoring naming conventions, make it harder to maintain the data and keep it consistent with the code. Major defects are weighted 2, medium defects 1 and minor defects 0.5.
Checking Vendor-Specific Features
Many database features are vendor-specific, meaning they are not part of standard SQL. They should be used with care. Some options should be banned because they hurt performance or make it harder to move the data. Others, such as the NULL option, should be mandatory for the same reasons. It is up to the responsible database analyst to decide which features are banned and which are mandatory.
Checking the Normal Forms
Avoiding vendor-specific features may cost some performance, but it makes the database less dependent on the vendor, so the data becomes more portable. SQL may not require a primary key in every record, but it can still be a rule worth enforcing. A foreign key is not required either, but if a table depends on another table, at least one should be defined. Under no circumstances should a table contain repeated data of the same type or subgroups of data. Static analysis can detect such obvious violations of the normal forms and flag them as bad practice.
Setting Limits
There may also be limits on how many attributes a record may have and how long a row may be. These limits need to be checked. It can also make sense to ban certain data types, such as packed decimal, binary, floating point or timestamp data, to stay compatible with databases from other vendors. Incompatible data types are one of the main causes of errors in data migration.
There should be a set of rules for database design, and those rules should be enforced. Typical rule violations include:
- Table has no unique identifier
- Dependent table has no foreign key
- Table contains a repeated data attribute
- Table contains a subgroup
- Table has no external view defined
- Table has no indexes
- Primary key contains too many subkeys
- NULL option is missing
- DELETE option is missing
- Table has an incompatible data type
- Number of attributes exceeds the maximum
- Row length exceeds the maximum permitted length
- Schema is insufficiently commented
The schema auditing tool checks these rules and reports every violation. Correcting the schema is then the job of the database administrator.
Measuring the Database Structure: Size and Complexity Metrics
To assess the architecture of a database, you first have to measure it, so the assessment rests on solid numbers. Without numbers you can’t compare, and without comparison you can’t judge. The measurements of an existing database can be compared with those of a benchmark database or simply with those of another database. Data measurements can be simple counts or complex metrics for size, complexity and quality.
Database Size Metrics
As with the rule check, you need a tool that performs the measurement, counts the database attributes and calculates the metrics. Several database attributes have to be counted. In an SQL database, they include:
- Lines of SQL code
- Number of tables
- Number of attributes
- Number of keys
- Number of views
- Number of relationships
- Number of stored procedures
- Number of stored access operations
- Number of stored procedure statements
- Number of integrity rules
- Number of stored procedure rules
- Number of test cases required to cover the database
From these counts, two size metrics are calculated:
- Function points
- Data points
A database scores 15, 10 or 5 function points, depending on its number of attributes and keys. Databases with more than 50 attributes or two keys get 15 function points, databases with 20 to 50 attributes or more than one key get 10, and databases with fewer than 20 attributes get 5. Data points are 1 for each attribute, 2 for each key and index, and 4 for each view. These metrics are used to estimate the cost of evolution projects that involve changes to the data.
Database Complexity Metrics
Beyond size, complexity and quality metrics should be calculated as well. All of them are ratio metrics, derived by relating one set of attributes to another. They are calculated with algorithms that compare actual values against benchmark values based on empirical studies of similar databases. Each complexity metric has a lower and an upper bound, and medium complexity is the geometric mean of the two. The six complexity metrics are:
- Content complexity: the ratio of data types and data keys to the number of data attributes
- View complexity: the ratio of views to the number of tables
- Access complexity: the ratio of access paths to the number of access objects (views and tables)
- Relational complexity: the ratio of table relationships to the number of tables
- Structural complexity: the ratio of structural elements (tables, views and indexes) to the number of basic elements (attributes and keys)
- Storage complexity: the ratio of stored attributes to the size of the storage area in bytes
The weighted average of these six metrics gives the overall complexity of the database on a ratio scale from 0 to 1. The lower the complexity ratio, the better.
Database Quality Metrics
The literature has long promoted the properties a good database should have, in particular J-L. Hainaut, Peter Aiken and Michael Blaha. These and other authors stress data independence, data flexibility, accessibility and data portability. One of the biggest obstacles to software migration is business logic tangled up with access logic. One of the biggest obstacles to data evolution is an inflexible database structure. In the 1980s, Dominique Warnier and Michael Jackson both promoted data-driven software design, a method that derives the program structure from the data structure. The resulting programs mirrored the hierarchical and network data models of the time. When the move to relational databases came, those program structures no longer fit and had to be redeveloped at great cost. What had once been a good idea for a quick solution turned out to be the biggest obstacle to modernizing the systems. Even today, the structure of many relational databases is still based on the requirements of the original application. Nobody planned for how the data might be used in the future.
The answer to the data independence problem is for every application to have its own views of the data, and that is what views were introduced for. This adds complexity to the database, of course, but there is always a trade-off. Here the trade-off is between data independence and data complexity. The same applies to data accessibility, which needs more keys and indexes so the data can be accessed in many different ways. Data flexibility means the database is easy to change and extend. For that, many smaller tables are better than a few large ones. As in object-oriented programming, however, this leads to more relationships between tables and therefore more complexity. Storage efficiency means storing more data in less space, and that adds complexity too. So there is hardly a way to raise one quality attribute of a database without adding complexity or giving up quality somewhere else. That is the dilemma database designers have to resolve. Achieving high quality with low complexity is an optimization problem that depends on the context in which the data is used. If the data is used in several contexts, you have to find the best possible compromise.
Each quality metric also has a lower and an upper bound, derived by comparing different databases. Medium quality is the geometric mean of the highest and the lowest quality value. The quality metrics are:
- Data independence: the ratio of data views and indexes to the number of data tables
- Data accessibility: the ratio of access paths to the number of stored attributes
- Data flexibility: the ratio of tables, keys and indexes to the number of data attributes
- Data storage efficiency: the inverse ratio of stored attributes to the size of the storage area in bytes
- Data conformity: the ratio of rule violations to the number of schema lines
The weighted average of these quality metrics gives the quality coefficient for the whole database, and it should be as high as possible. On the quality scale of ISO standard 9126, values above 0.8 are excellent, 0.6 to 0.8 good, 0.4 to 0.6 satisfactory and below 0.4 unsatisfactory.
Data Quality Testing: Validating the Database Content
Validating the content of a database requires an oracle: someone or something that defines what belongs in the columns of a table in general and in each row in particular. That oracle can be a person, a program, an interpretable specification or another database. A person could browse the database and visually check the values of every row, or of selected columns or rows. There are browsers that support this, but even with a good one the work is tedious and slow, and with very large databases it is practically impossible. That is why data validation has to be automated.
Validating Data Content With a Custom Program
One option is to write a program that reads the database and fetches the next record, or selects records according to certain search criteria. It then compares the content of each record with rules coded into the program, or with the content of another table used as a reference. This works, but it takes time and effort to prepare. The program has to be written by a programmer who knows the database and how to access it. And the program itself can contain errors and has to be debugged.
Validating Data Content With a Test Script
The other option is an interpretable specification in the form of a script that someone familiar with the data can put together easily. The script states rules for validating the values of selected attributes in selected records. This assumes, of course, that the auditor writing the script knows what should be in the database. The auditor can use another database as a point of comparison, but then has to establish the links between the two databases. To write an interpretable specification, the auditor needs a language that can:
- specify which records with which keys should be in a table (the keys can be taken from the foreign keys of related tables),
- specify which records with which keys should not be in a table (this can be done by comparing tables with each other),
- compare the content of a selected column in one table with the content of a corresponding column in another table,
- compare columns of the same table with each other,
- compare the values of a column with a given constant,
- compare the values of a column with a range of values (boundary value analysis),
- compare the values of a column with a set of expected values (an equivalence class),
- compare the values of a column with the result of an arithmetic or logical expression,
- compare the values of a column with the concatenated values of other columns and constants.
Any of these assertions can be made conditional on a logical expression, so it is only executed if the condition is met. The condition makes the values of one column depend on the values of another column or on the primary key, for example:
This_Column E RANGE (1:50) if (Mainkey > 1000);
These assertions can be phrased in many ways. The auditor can define discrete values as well as value ranges and equivalence classes, relationships between data values and the results of calculations. The assertion language is based on the Schematron schema validation rules defined in ISO/IEC standard 19757.
Running the Data Validation
The data auditor writes the assertions about the data into an assertion script for each database table and passes it to the compiler. The compiler checks the syntax and verifies the data names against the database schema. If it finds errors, the auditor has to correct the script. Once the script is correct, the compiler generates intermediate code that references the qualified data names and constant values. A validation run is then started for each target table. The validation job works through the target table sequentially and checks one row after another against the specified rules. Any row that is missing, redundant or contains invalid values is listed by key in the validation report, together with the expected values. With this report, the auditor can easily spot incorrect content and write the corresponding defect reports. The data correctness rate is the inverse ratio of the number of incorrect attributes to the total number of attributes in the database.
Data Quality Assessment: Summary
Both approaches to assessing data quality, static schema analysis and content validation, have already been used with good results in a number of measurement and audit projects. Until now, however, they had never been used together. The data assessment process proposes combining them. First, the rules are defined together with the users, and the metrics are weighted according to the users’ goals. Second, the database schemas are analyzed statically and the structures are measured. Third, the validation scripts are put together with the customer, based on what the customer believes the column values should be. Finally, the database content is examined and the exceptions are documented. After that, a report on the state of data quality can be written.
The outcome of a data quality assessment depends heavily on how the benchmark values are set and how the metric values are weighted. Both can be adjusted before the measurement jobs run. A user may care more about data accessibility than about data independence, or consider data flexibility more important than testability. Users also differ in how much incorrect data content they will tolerate. For one, a correctness rate of 95% is good enough; for another, nothing below 100% is acceptable.
These processes grew out of practical experience from many projects over recent years. Because tools for assessing the various aspects were lacking, a set of in-house tools grew along with these projects. The tools DLIAudit, ADAAudit and SQLAudit, for example, check the rules for IMS, ADABAS, DB2, Oracle and MS SQL databases. The result is a defect report on schema rule violations. SQLAudit also counts about 35 database attributes and calculates 6 complexity and 6 quality metrics. All 12 are ratio metrics, derived by relating one set of attributes to another. The database metrics are collected in a metrics report and also exported as an XML file, so anyone can store them in a metrics database or process them further. These metrics can form the basis of a benchmark, and without a benchmark nothing can really be evaluated. The Datatest tool offers an assertion language based on predicate logic that the auditor can use to specify the data content. It has often been used to validate content.
In the end, quality is relative. It is relative to the quality goals that have been set. That is why the Goal Question Metric model that Basili and Rombach introduced long ago still holds. The user first sets goals, then asks how to judge whether they have been met, and only then defines metrics to measure the degree to which they are met. The method works just as well for assessing data quality. The data analyst first sets goals such as portability, extensibility and flexibility. Then metrics are defined to measure how well the current database meets them. Finally, the database is measured and the results are evaluated. Without clearly defined, measurable goals, you cannot assess anything.
Frequently Asked Questions
Why does the quality of a database deteriorate over the years?
Because its structure and content erode independently of one another. New applications force structural changes that are often made on an ad hoc basis: complexity increases, quality declines, much like in software systems governed by the laws of software evolution. At the same time, faulty programs corrupt the content by storing incorrect values and deleting necessary data records. In large databases, this erosion goes undetected for a long time.
Is it a problem to embed business logic in stored procedures?
Yes. Stored procedures are acceptable for access operations and for ensuring data integrity. If business rules and validation routines are hidden there instead of being located in rule modules or client components, they are invisible to the tools that process the programs. Procedures full of SELECT statements, loops, SET statements, and declarations indicate misuse: They tie the database to a specific application.
How can you tell in everyday use that a database violates the normal forms?
By the traces of makeshift patches. Additional attributes are appended to existing tables instead of being moved to sub-tables, causing the rows to grow longer and longer. Data groups within existing rows violate the second normal form, while repeated data elements violate the first. If subkeys are added to the primary key that do not refer to the row as a whole, the third normal form is violated.
How do you weight rule violations in a database schema?
They are classified into three categories based on severity: severe defects are assigned a score of 2, moderate defects a score of 1, and minor defects a score of 0.5. For example, a violation of the first normal form is considered severe because it hinders further development and reuse of the data and can cause errors. Exceeding a size limit counts as a moderate defect; violations of naming conventions as a minor one.
Can the quality of a database be improved without increasing its complexity?
Hardly. Data independence requires separate views for each application; accessibility requires more keys and indexes; flexibility requires many small tables rather than a few large ones; and even storage efficiency drives up complexity. Each of these properties comes at the cost of higher complexity or a loss of quality elsewhere. The best compromise depends on the contexts in which the data is used.
At what threshold is a database’s quality considered good?
A weighted average is calculated from the individual quality metrics to form a quality coefficient, which should be as high as possible. In 2012, the article used the quality measurement scale from ISO Standard 9126 as a basis: values above 0.8 were considered excellent, 0.6 to 0.8 as good, 0.4 to 0.6 as satisfactory, and below 0.4 as unsatisfactory.
How do you check the contents of very large databases for incorrect values?
Only automatically, because every validation requires an “oracle” that defines what should be in a column or row. Visually reviewing the data with a browser is tedious and practically impossible with very large datasets. That leaves two options: a custom-written validation program (which may itself contain errors) or a script with assertions written by someone familiar with the data.
Where should a data quality assessment begin?
With goals, not with measurements. Quality is relative to the established quality goals, such as portability, extensibility, and flexibility. Next comes the question of how to assess whether these goals have been met; only then are metrics defined and measured. Tolerance levels must also be clarified in advance: For one user, a data accuracy rate of 95 percent may be sufficient, while for another, only 100 percent is acceptable.
