Abstract
A stored and inherited relation (SIR) is 1NF stored relation enlarged with inherited attributes (IAs). The latter make SIRs as base tables the only known view-savers for logical navigation free (LNF) or calculated attribute free (CAF) queries, without any denormalization. Recall that LN means joins among base tables, while calculated attributes serve as the virtual ones do at some popular DBSs, but can be more general, e.g., with aggregate functions or sub-queries. The overall advantage of SIRs is substantially less procedural queries and DB schemes. We now show that usual schemes of stored relations with foreign keys implicitly define IAs forming so-called natural SIRs. The exclusive advantage of the latter is the LNF queries with zero procedurality to define the IAs. We then discuss SIRs with FKs and explicit IAs, the calculated ones especially. We show that generalizing a typical relational DBS to SIR-enabled one should be simple. Preexisting applications could remain not affected, while new ones could profit from LNF and CAF queries. We conclude that major relational DBS should evolve to SIR-enabled “better sooner than later”. To make LNF and CAF queries standard, at last, for the benefit of, likely, millions of SQL users.
Similar content being viewed by others
References
Litwin, W.: SQL for stored and inherited relations. In: 21st International Conference on Enterprise Information Systems, (ICEIS 2019) (2019). 12 p. http://www.iceis.org/?y=2019
Litwin, W.: Manifesto for improved foundations of relational model (EICN-2019). Proc. Comput. Sci. 160, 624–628 (2019)
Litwin, W.: Natural stored and inherited relations. In: (EUSPN-ICTH 2021), November 1-4, Procedia Computer Science, vol. 198. Elsevier, pp. 171–178 (2022).
Litwin, W.: Stored and Inherited Relations. arXiv preprint arXiv:1703.09574 (2017)
Litwin, W.: Supplier-Part Databases with Stored and Inherited Relations Simulated on MS Access. Lamsade Tech. E-Note (2016)
Codd, E.F.: Derivability, Redundancy and Consistency of Relations Stored in Large Data Banks. IBM Res. Rep. RJ 599 #12343 (1969)
Codd, E.F.: A relational model of data for large shared data banks. CACM 13, 6 (1970)
Date, C.J.: An Introduction to Database Systems. Pearson Education Inc. (2004). ISBN 0-321-18956-6
Date, C.J., Darwen, H.: Watch out for outer join. In: Date and Darwen Relational Database Writings (1991)
Date, C.J.: View Updating and Relational Theory. O’Reilly (2012)
Date, C.J.: Type Inheritance & Relational Theory. O’Reilly (2016)
Date, C.J.: E.F. Codd and Relational Theory. Lulu (2019)
Goldstein, J., Larson, P.: Optimizing Queries Using Materialized Views: A Practical, Scalable Solution. ACM SIGMOD (2001)
Halevy, A.Y.: Answering queries using views: a survey. VLDB J. 10, 270–294 (2001)
Jajodia, S., Springsteel, F.N.: Lossless outer joins with incomplete information. BIT 30(1), 34–41 (1990)
Larson, P., Zhou J.: Efficient Maintenance of Materialized Outer-Join Views. ICDE (2007)
Litwin, W., Ketabchi, M., Risch, T.: Relations with Inherited Attributes. HPL. Palo Alto, CA. Tech. Rep. HPL-DTD-92-45, 30 (1992)
Maier, D., Ullman, J.D., Vardi, M.Y.: On the foundations of the universal relation model. ACM-TODS 9(2), 283–308 (1984)
Postgres SQL. https://www.postgresql.org/
Stonebraker, M., Moore, D.: Object-Relational DBMSs: The Next Great Wave, 2nd edn. Morgan Kaufmann (1998)
Litwin, W.: Stored and Inherited Relations with Natural or Declared Foreign Keys. Lamsade Res. Rep. Report June 16, 2020. Latest update: 11 August 2022. 12p. https://www.lamsade.dauphine.fr/~litwin/Designing%20an%20RDB%20with%20SIRs.pdf
Acknowledgments
We are grateful to Ron Fagin for invitation to present this material at IBM Almaden Research Cntr., March 2020. Likewise, we thank Darrell Long for his March 2020 invitation to talk about at UCSC Eng. as well.
Author information
Authors and Affiliations
Corresponding author
Editor information
Editors and Affiliations
Rights and permissions
Copyright information
© 2022 Springer Nature Switzerland AG
About this paper
Cite this paper
Litwin, W. (2022). Stored and Inherited Relations with PKN Foreign Keys. In: Chiusano, S., et al. New Trends in Database and Information Systems. ADBIS 2022. Communications in Computer and Information Science, vol 1652. Springer, Cham. https://doi.org/10.1007/978-3-031-15743-1_45
Download citation
DOI: https://doi.org/10.1007/978-3-031-15743-1_45
Published:
Publisher Name: Springer, Cham
Print ISBN: 978-3-031-15742-4
Online ISBN: 978-3-031-15743-1
eBook Packages: Computer ScienceComputer Science (R0)