Advertisement

On Repairing Referential Integrity Constraints in Relational Databases

  • Raji GhawiEmail author
Conference paper
  • 317 Downloads
Part of the Communications in Computer and Information Science book series (CCIS, volume 1018)

Abstract

Integrity constraints (ICs) are semantic conditions that a database should satisfy in order to be in a consistent state. Typically, ICs are declared with the database schema and enforced by the database management system (DBMS). However, in practice, ICs may not be specified to the DBMS along with the schema, this is considered a bad database design and may lead to many problems such as inconsistency and anomalies. In this paper, we present a method to identify and repair missing referential integrity constraints (foreign keys). Our method comprises three steps of verification of candidate foreign keys: data-based, model-based, and brute-force.

Keywords

Relational databases Integrity constraints SQL Validation Verification 

References

  1. 1.
    Abiteboul, S., Hull, R., Vianu, V.: Foundations of Databases. Addison-Wesley, Boston (1995). http://webdam.inria.fr/Alice/zbMATHGoogle Scholar
  2. 2.
    Arenas, M., Bertossi, L., Chomicki, J.: Consistent query answers in inconsistent databases. In: Proceedings of the 18th Symposium on Principles of Database Systems, PODS 1999, pp. 68–79. ACM, New York (1999)Google Scholar
  3. 3.
    Barry, D., Stanienda, T.: Solving the Java object storage problem. Computer 31(11), 33–40 (1998)CrossRefGoogle Scholar
  4. 4.
    Bauckmann, J., Leser, U., Naumann, F., Tietz, V.: Efficiently detecting inclusion dependencies. In: ICDE, pp. 1448–1450. IEEE Computer Society (2007)Google Scholar
  5. 5.
    Bertossi, L.: Consistent query answering in databases. SIGMOD Rec. 35(2), 68–76 (2006)CrossRefGoogle Scholar
  6. 6.
    Bertossi, L.: Database Repairing and Consistent Query Answering. Morgan & Claypool Publishers (2011)Google Scholar
  7. 7.
    Bravo, L., Bertossi, L.: Semantically correct query answers in the presence of null values. In: Grust, T., et al. (eds.) EDBT 2006. LNCS, vol. 4254, pp. 336–357. Springer, Heidelberg (2006).  https://doi.org/10.1007/11896548_27CrossRefGoogle Scholar
  8. 8.
    Calì, A., Lembo, D., Rosati, R.: On the decidability and complexity of query answering over inconsistent and incomplete databases. In: Proceedings of the 22nd Symposium on Principles of Database Systems, PODS 2003, pp. 260–271. ACM, New York (2003)Google Scholar
  9. 9.
    Chomicki, J.: Consistent query answering: opportunities and limitations. In: 17th International Workshop on Database and Expert Systems Applications (DEXA 2006), 4–8 September 2006, Krakow, Poland, pp. 527–531 (2006)Google Scholar
  10. 10.
    Chomicki, J.: Consistent query answering: five easy pieces. In: Schwentick, T., Suciu, D. (eds.) ICDT 2007. LNCS, vol. 4353, pp. 1–17. Springer, Heidelberg (2006).  https://doi.org/10.1007/11965893_1CrossRefGoogle Scholar
  11. 11.
    Chomicki, J., Marcinkowski, J.: Minimal-change integrity maintenance using tuple deletions. Inf. Comput. 197(1–2), 90–121 (2005)MathSciNetCrossRefGoogle Scholar
  12. 12.
    Chomicki, J., Marcinkowski, J., Staworko, S.: Hippo: a system for computing consistent answers to a class of SQL queries. In: Bertino, E., et al. (eds.) EDBT 2004. LNCS, vol. 2992, pp. 841–844. Springer, Heidelberg (2004).  https://doi.org/10.1007/978-3-540-24741-8_53CrossRefGoogle Scholar
  13. 13.
    Elmasri, R., Navathe, S.: Fundamentals of Database Systems, 6th edn. Addison-Wesley Publishing Company, Boston (2010)zbMATHGoogle Scholar
  14. 14.
    Fuxman, A., Fazli, E., Miller, R.J.: ConQuer: efficient management of inconsistent databases. In: Proceedings of the 2005 ACM SIGMOD International Conference on Management of Data, pp. 155–166. ACM, New York (2005)Google Scholar
  15. 15.
    Kruse, S., et al.: Fast approximate discovery of inclusion dependencies. In: Datenbanksysteme für Business, Technologie und Web (BTW 2017) (2017)Google Scholar
  16. 16.
    Kruse, S., Papenbrock, T., Naumann, F.: Scaling out the discovery of inclusion dependencies. In: Datenbanksysteme für Business. Technologie und Web (BTW 2015), pp. 445–454. Gesellschaft für Informatik e.V, Bonn (2015)Google Scholar
  17. 17.
    Marchi, F.D., Lopes, S., Petit, J.M.: Unary and N-ary inclusion dependency discovery in relational databases. J. Intell. Inf. Syst. 32(1), 53–73 (2009)CrossRefGoogle Scholar
  18. 18.
    Molinaro, C., Greco, S.: Polynomial time queries over inconsistent databases with functional dependencies and foreign keys. Data Knowl. Eng. 69(7), 709–722 (2010)CrossRefGoogle Scholar
  19. 19.
    Papenbrock, T., Kruse, S., Quiané-Ruiz, J.A., Naumann, F.: Divide & conquer-based inclusion dependency discovery. VLDB Endow. 8(7), 774–785 (2015)CrossRefGoogle Scholar
  20. 20.
    Rahm, E., Do, H.H.: Data cleaning: problems and current approaches. IEEE Data Eng. Bull. 23, 2000 (2000)Google Scholar
  21. 21.
    Rostin, A., Albrecht, O., Bauckmann, J., Naumann, F., Leser, U.: A machine learning approach to foreign key discovery. In: 12th International Workshop on the Web and Databases, WebDB 2009, Rhode Island, USA (2009)Google Scholar
  22. 22.
    Wijsen, J.: Consistent query answering under primary keys: a characterization of tractable queries. In: Database Theory - ICDT 2009, 12th International Conference, St. Petersburg, Russia, 23–25 March 2009, Proceedings, pp. 42–52 (2009)Google Scholar
  23. 23.
    Zhang, M., Hadjieleftheriou, M., Ooi, B.C., Procopiuc, C.M., Srivastava, D.: On multi-column foreign key discovery. VLDB Endow. 3(1–2), 805–814 (2010)CrossRefGoogle Scholar

Copyright information

© Springer Nature Switzerland AG 2019

Authors and Affiliations

  1. 1.Bavarian School of Public PolicyTechnical University of MunichMunichGermany

Personalised recommendations