Integridad referencias

Available online at www.sciencedirect.com

Decision Support Systems 44 (2008) 495 – 508 www.elsevier.com/locate/dss

Referential integrity quality metrics
Carlos Ordonez a,⁎, Javier García-García b,1
b a University of Houston, Department of Computer Science, Houston, TX 77204, USA Universidad Nacional Autónoma de México, Facultad de Ciencias, UNAM, Mexico City, CU 04510, Mexico

Received12 September 2006; received in revised form 7 May 2007; accepted 18 June 2007 Available online 23 June 2007

Abstract Referential integrity is an essential global constraint in a relational database, that maintains it in a complete and consistent state. In this work, we assume the database may violate referential integrity and relations may be denormalized. We propose a set of quality metrics,defined at four granularity levels: database, relation, attribute and value, that measure referential completeness and consistency. Quality metrics are efficiently computed with standard SQL queries, that incorporate two query optimizations: left outer joins on foreign keys and early foreign key grouping. Experiments evaluate our proposed metrics and SQL query optimizations on real and syntheticdatabases, showing they can help in detecting and explaining referential errors. © 2007 Elsevier B.V. All rights reserved.
Keywords: Referential integrity; Foreign key; Metric; Database integration

1. Introduction Referential integrity is a fundamental global constraint in a relational database [8], that basically ensures a foreign key value exists in the referenced relation. Referentialintegrity issues are found in database integration, data quality assurance, data warehousing and data modeling. Referential integrity is violated or relaxed for practical reasons. Database integration represents a common scenario where similar tables coming from multiple source databases (OLTP systems) have different referential integrity constraints and each

⁎ Corresponding author. E-mail addresses:ordonezc@cs.uh.edu (C. Ordonez), javgar@servidor.unam.mx (J. García-García). 1 The second author was sponsored by the UNAM IT project “Macroproyecto de Tecnologías para la Universidad de la Información y la Computación". 0167-9236/$ - see front matter © 2007 Elsevier B.V. All rights reserved. doi:10.1016/j.dss.2007.06.004

DBMS provides distinct mechanisms and rules to enforce referentialintegrity [20]. Therefore, source databases may violate referential integrity and their integration may uncover additional referential integrity problems. Performance is the second common reason, where referential integrity checking is disabled to allow fast insertions in batch mode. Finally, the logical data model behind a relational database evolves, incorporating new attributes and new relations notdefined before, causing old data to violate new referential integrity constraints. In short, referential integrity is an important broad problem in modern relational databases. The issues outlined above motivated us to revisit the fundamental concept of referential integrity. We propose several Quality Metrics (QMs) that measure completeness and consistency with respect to referential integrity.Our QMs not only cover normalized databases whose relations are incomplete when they have missing foreign key values, but also measure the inconsistency that arises when a relation is not normalized and an attribute

496

C. Ordonez, J. García-García / Decision Support Systems 44 (2008) 495–508

value, determined by a foreign key, does not match the corresponding attribute value in thereferenced relation. This is common when tables are denormalized, views are materialized or similar tables, from different source databases, are integrated. This article continues our work on referential integrity [20] (see Section 5). The article is organized as follows. Section 2 introduces definitions on relational databases, referential integrity and denormalized databases. Section 3 presents our...