Consistent Aggregations in Databases with Referential Integrity Errors

A data warehouse integrates tables coming from multiple source databases, where each database has different tables, columns with similar content across databases and different referential integrity constraints, enforced to different compliance levels. Some source databases may have more reliable data than others, if referential integrity is more strictly enforced or their respective logical data model is more comprehensive. Thus, a query in an integrated database is likely to refer to tables and columns with referential integrity errors. In this work, we improve aggregations to handle referential integrity errors on OLAP databases. Specifically, when two tables are joined SQL ignores those tuples with invalid foreign key values, effectively discarding potentially valuable information. We extend aggregations to return complete answer sets in the sense that no tuple is excluded. Two families of extended aggregations are proposed: weighted referential aggregations and full referential aggregations, which return an approximate answer set and perform a dynamic repair, respectively. Finally, we introduce a simple method to improve aggregation accuracy. Experiments analyze approximation accuracy and time performance of our extended aggregations on a synthetic database, comparing them with standard SQL aggregations on databases with varying referential error rates. The extra work to compute extended aggregations is reasonable and approximate answer sets are highly accurate, making our aggregations a good alternative to standard aggregations in a data warehouse.