SIR SQL for Logical Navigation and Calculated Attribute Free Queries to Base Tables

Witold Litwin

2025

Abstract

SIR SQL stands for SQL with Stored and Inherited Relations (SIRs). Every SIR SQL Create Table makes definable any base attributes one could have in an SQL Create Table at present. In addition, one can define inherited attributes (IAs), definable in SQL queries or views only up to now. One may also define foreign keys (FKs) that are SQL ones or logical pointers in Codd’s original sense. IAs in SIRs with Codd’s FKs usually provide for logical navigation free (LNF) queries, i.e., without equijoins on FKs and referenced keys. The same outcome SQL queries to the same base tables without IAs, must include LN avoided. SIR SQL Create Table may in particular include IAs definable through value expressions also possible in SQL queries or views only up to now, usually referred to as calculated attributes (CAs). CAs may involve, e.g., attributes from different tables or aggregate functions, or sub-queries. CAs in SIRs provide for CAF queries, addressing any CAs in SIRs by name only. In contrast, every SQL query to base tables needing CAs has to fully define each of these. The end result is that most of SQL base table queries, requiring LN or CAs schemes at present, become LNF or CAF queries in SIR SQL. The latter are usually substantially less procedural, i.e., by dozens of characters. They become also quasi-natural, i.e., with Select clause only naming the selected attributes, From clause naming a single base table and Where clause, if any, with short Boolean formulae over usual constraints on some attribute values, at worst. SIR SQL should accordingly significantly boost SQL clients’ productivity. Especially, since most clients are data analysts or application developers, not SQL geeks. While the problematic of LNF and CAF queries is four decades old, our solution is the first practical one, to our best knowledge. Below, we illustrate the problem of LN and of CAs in queries to SQL base tables using Codd’s original Supplier-Part DB. We then present SIR SQL. We show in depth how SIR SQL LNF and CAF queries to base tables become possible. We show in particular that Create Table statements defining an SQL DB at present, usually define also a SIR SQL DB, providing for LNF queries to base tables as free bonus. We discuss the front-end for SIR SQL that should require, for any popular SQL DBS, a few month implementation efforts only, validated by proof-of-concept prototype for SQLite3. We accordingly postulate to upgrade every popular SQL DBS to SIR SQL. 7+ million SQL clients worldwide, of the dominant DB language, providing for 31B+ US\$ market size of SQL apps, will benefit from.

Paper Citation

in Harvard Style

Litwin W. (2025). SIR SQL for Logical Navigation and Calculated Attribute Free Queries to Base Tables. In Proceedings of the 27th International Conference on Enterprise Information Systems - Volume 1: ICEIS; ISBN 978-989-758-749-8, SciTePress, pages 191-201. DOI: 10.5220/0013199900003929

in Bibtex Style

@conference{iceis25,
author={Witold Litwin},
title={SIR SQL for Logical Navigation and Calculated Attribute Free Queries to Base Tables},
booktitle={Proceedings of the 27th International Conference on Enterprise Information Systems - Volume 1: ICEIS},
year={2025},
pages={191-201},
publisher={SciTePress},
organization={INSTICC},
doi={10.5220/0013199900003929},
isbn={978-989-758-749-8},
}

in EndNote Style

TY - CONF

JO - Proceedings of the 27th International Conference on Enterprise Information Systems - Volume 1: ICEIS
TI - SIR SQL for Logical Navigation and Calculated Attribute Free Queries to Base Tables
SN - 978-989-758-749-8
AU - Litwin W.
PY - 2025
SP - 191
EP - 201
DO - 10.5220/0013199900003929
PB - SciTePress