Re: Examples of SQL anomalies?

From: Rob <rmpsfdbs_at_gmail.com>
Date: Sat, 28 Jun 2008 12:48:48 -0700 (PDT)
Message-ID: <0945cd7b-5199-44f3-b230-9027b4ec1177_at_f36g2000hsa.googlegroups.com>

On Jun 26, 4:01 pm, Gene Wirchenko <ge..._at_ocis.net> wrote:
> Let me add another item.
>
> Joins will often give trouble with ambiguity of names. Never
> mind that I am joining on the two columns having the same value. If
> they have the same name, I have to prefix them with the table name.
>
> Sincerely,
>
> Gene Wirchenko
>
On January first of this year, I pointed cdt to

http://www.sfdbs.com/toplevel/fasttrack/fasttrack.shtml

in which I described the Aggregate-Link schema, a new way to represent relationships in relational databases.

I freely admit that I was intentionally vague about what one could do with it, but you (Gene Wirchenko) have given me a lead-in I cannot ignore because it leads in exactly to one important value of the Aggregate-Link schema: The separation of structure from data in relational databases.

If you look about 3/7ths down the page under "Aggregate-Link Structure Isolation", you'll see the following quote:

"Beginning with the introduction of the Relational Model in 1970 [Codd 1970],
all approaches to relational system design have been entity oriented -- that
is, oriented to the representation of entities. Relationships are unnamed and
for the most part, invisible. Yet relationships provide the structure that
associates tuples in one relation to tuples in another. The join operator
is the principal means for retrieving related tuples and their attribute data.
Relationships have never been treated as first-class components."

I was genuinely hoping that someone would pick up the gauntlet and run with
it, but nobody has. People are busy, they have plenty of their own projects.
My expectations were unrealistic.

I am working on the next iteration of the website, one that will "reveal"
several values in the Aggregate-Link schema, but it is taking alot more
time than I expected and won't be ready much before September'08. In the
meantime, consider the following observation:

---

If a relationship were a first-class object in SQL, one would necessarily
have to specify all of the following when declaring a named relationship:
a. the relationship name and cardinality, b. 2 relations (I call them "parent" and "child" because the interesting

   relationships are asymmetric),
c. the join "columns" (I use the terms "primary key" and "foreign key"), and
d. whether childless parents and parentless childs (orphans) are to be considered

   part of the relationship.

---

As a consequence, by specifying a relationship by name in a query (in SQL
or some other language), the problem you stated ("ambiguity of names") would
disappear. In fact, the join as an explicit operator would practically disappear.

More than one relationship between the parent- and child relations?

• Not ambiguous because different relationships have different names. Multiple relationships (i.e., more than 1 join)?
• Composition. Intersection, union, relative difference between relationships?
• Think. Can't these be resolved entirely from the relationship without access to the entities?

I cannot get involved in lengthy discussions here -- I'm overwhelmed with the effort to create the next iteration of the website (www.sfdbs.com).
I am interested to see if anyone else finds any merit in the relationship-oriented
approach.

Rob Received on Sat Jun 28 2008 - 21:48:48 CEST