Hierarchical Relational

In general, RelationalWeenies consider the ObjectWeenies a young generation of HierarchicalWeenies, and are fast to point to the LimitsOfHierarchies, but today, while rereading one of my old relational theory books I found this:

A possible solution to avoid in a outer join between a Supplier and Parts where Supplier may or many not have Parts could be to represent the resulting projection as:

 Supplier {SupplierId:Integer, SupplierName:CHAR, Parts:TABLE}

That way, instead of using nulls for the case where the Supplier has no customers, we use an empty table. (This example is from AnIntroductionToDatabaseSystems by ChrisDate eighth edition page 589)

That answer, is, of course impossible in the typical commercial database.... but it got me to think... isn't a kind of hierarchical relationship where the Supplier has Parts? and that got me to think if this would be relational or hierarchical:

 VAR Animals REAL RELATION {
name CHAR
 } KEY {name}

 VAR Persons REAL RELATION {
name CHAR,
birthday DATE,
pets Animals 
 } KEY {name}

Did you notice that the relation "Persons" has a "column" of type "Animals" ? Now... does this violate relational? does this violate Hierarchical? or is this perfectly fine for both? In Date example this kind of structure seems to be a perfectly fine relationship if used as a result of an outer join between Suppliers and Parts... my question is... is this "relation nesting/table nesting" also valid as a starting point? And my question after that is... if it were common that commercial PseudoRelational? databases supported this... would that make make easier (or harder) to overcome the ObjectRelationalImpedanceMismatch?

There is no fundamental problem with nested relations... so long as they have the correct meaning of relating the relations. I.e. person of 'name' is related to a relation of animals, not to any individual animal. If you meant to say that each animal in the relation of animals was somehow related to the person, that isn't being expressed here. There would be a severe semantic disconnect with DatabaseIsRepresenterOfFacts in your above approach.

For queries, hierarchical relations are convenient ways to return the results of group by operations. Of course, with group by the intended denormalization is made explicit for viewing purposes, and isn't really a problem from any purity standpoint.

Use of nested relations is covered in TheThirdManifesto. However, the example shown above -- in which the 'pets' attribute's type is specified (apparently) as RelVar 'Animals' -- does not make sense. A RelVar is not a type. The above should be expressed as:

 VAR Persons REAL RELATION {
name CHAR,
birthday DATE,
pets RELATION {name CHAR} 
 } KEY {name}

Some have argued that nested relations are never required in the database schema, and that the semantics they express are better handled via relationships between RelVars. Nested relations may, however, effectively express the results of certain queries, as noted in the sections above. Either way, they do not represent a violation of the RelationalModel. An attribute type of RELATION (or TUPLE) is as legitimate as any other type.

By the way, RelationalWeeniesEmbraceOo.

I don't think many will have a problem with a hierarchical "view" of something. It's the hard-wiring of a hierarchy that will anger the relational side of some. As far as the difference between a type name and a table name, this is mostly an implementational issue. Personally, I'd like some indirection between type name and table name.

I know of no implementation that treats type names and table names as intercheangeable or equivalent, which is implied by their use being "an implementational issue". To do so would be effectively treating a variable as a type. "Some indirection" between type name and "table name" (i.e., variable or RelVar) is the usual approach.

By the way, the original example is TutorialDee, in which type names and RelVar names are not interchangeable. A RelVar name cannot be used as a type name, though there is a SAME_HEADING_AS operator to obtain the heading (meta-data related to the type) of a tuple or relation, and some implementations (e.g., the RelProject) provide a TYPE_OF operator to obtain the type of a RelVar or other object. In the original example, 'pets TYPE_OF(Animals)' or 'pets RELATION {SAME_HEADING_AS(Animals)}' would have been essentially correct, though Animals would have had no relationship to Persons.

[Quibble: The meta-data retrieved by SAME_HEADING_AS is not just related to a relation value's type, it is the type. Relvars and relation values do have a type, D&D just declined to give relation types explicit names. I believe that they acknowledge this as a fairly arbitrary choice somewhere in TheThirdManifesto.]

FebruaryZeroNine