Dynamic Relational Query Examples

Moved from MultiParadigmDatabaseQuestions

A DynamicRelational database can be conceptually modeled via XML statements[1] where blank attributes (zero-length values) are not stored. (Assume different databases are stored in different XML files[2].) Out of the box, the only required attribute is "row_id", which is automatically generated, read-only, and unique for the file. For DynamicRelational, the "entity" attribute is also required, and the row_id would be unique per entity:

 <row entity="my_table" row_id="1" a="1" b="2" c="foo"/>  // see above example
 <row entity="my_table" row_id="2" b="blah"/>
 <row entity="my_table" row_id="3"/>
 <row entity="tableFoo" row_id="1" c="44" x="7"/>  // different entity (line04)

Keep mind this is not an implementation suggestion, only an equivalent "storage" model (although could be used for a reference or training implementation, ignoring most machine performance issues.) As far as what to call that kind of "structure", I'll leave it to those well-versed in academic language.

  <row entity="tableFoo" row_id="1" c="44" x=""/>   // INVALID

The above would not be valid. Any "empty" values would not have ("store") a name/value pair. If one attempted something like the following statement on line04 from above...

  UPDATE tableFoo SET x='' WHERE row_id=1   // save a zero-length string to x

The XML reference model result would be...

  <row entity="tableFoo" row_id="1" c="44"/>

(Note: the "row_id=1" clause in the sample SQL does not need a comparison type specifier (#,@,$) because it's predefined as numeric as a system default[3].)

If one then did this...

  SELECT length(x) FROM tableFoo WHERE row_id=1

they would get zero (0) as an answer. One could also do this:

  SELECT length(randomName), length(asdfadsf), length(zzzzzz) FROM tableFoo WHERE row_id=1

and get three zeros.

These 3 columns are not "in" the table, at least in the traditional sense, although we can't verify this in the traditional sense, unless they have data somewhere (as illustrated later). This "fuzzy existence" feature is probably one of the most foreign features of these proposed DBMS. It is a LiberatingConstraint in my opinion that better facilitates dynamicness. If somebody disagrees, they are free to describe an "empty-friendly" alternative, as long as they give the proposed gizmo a different topic name. -t

If one needs nulls for compatibility with other sub-systems, then one can store the string "[nul]" as a convention. The built-in numeric type, if explicitly specified for a given column, accepts "[nul]".[4]

If one wanted to know if a given column existed and had (non-empty) data anywhere in the table, they could do this:

  SELECT x FROM tableFoo WHERE length(x) > 0

--top

---

Footnotes

[1] Although XML imposes an ordering of attributes (left to right), perhaps as an accident of being text-based, generally this ordering is not assumed to be recognized as having "data" meaning. Perhaps alphabetic column output ordering should be assumed if wild-cards or similar are used to select columns, although DDL-defined columns should probably be on the left in their usual order, and non-DDL columns on the right (if any). Note that XML does not permit duplicate attributes per statement. Also, the comment style is shown as C style instead of XML for a better page fit in illustrations.

[2] Another possible arrangement is a folder per database and a different file per table. Thus, the directory hierarchy would be: database-root > database-instance > table-files. But since using XML is only intended as a conceptual or experimental or training model, this hierarchy is not intended as a production implementation requirement.

[3] For an explanation of the type indicator symbols, see PageAnchor "Comparison-Symbols" at DynamicRelational.

[4] Whether any queries that generate null in traditional databases would also generate "[nul]" here is an open question. I would prefer the default be blank rather than produce nulls. Thus, "avg(x)" over zero rows would return a blank. But perhaps a compatibility option would help with those used to traditional databases. And perhaps a second optional parameter on functions like avg() can be used to specify the value if a null-like condition occurs. Example: SELECT avg(x, 'value if no data') FROM foo WHERE 1 = 2", which would produce "value if no data".

---

CategoryDatabase, CategoryExample, CategorySpeculative