Re: Examples of SQL anomalies?

"Rob" <rmpsfdbs_at_gmail.com> wrote in message news: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.
>

I think you've missed what underlies the Relational Model. If attributes from two different relations share the same domain and can share some of the same values from that domain, then there is an implicit relationship between those relations--in other words, if two relations can be joined on a common attribute or set of attributes, then there is already a relationship between those two relations. Referential constraints simply describe its character. Database schemata with some more complex referential constraints can be simplified--that is, replaced by a schemata that includes an additional junction relation and simpler referential constraints, but ultimately, the junction relation along with the simpler referential constraints boils down to just a more complex description of a single relationship. I just can't see any practical use for reifying descriptions of relationships.

On the fasttrack page you speak of breaking up the query processing for Query 3 into two separate queries and then evaluating them in sequence. If you can't see the problem with that, then perhaps you should go back to school. (Hint: Suppose an update by another user changes a row in A between the select into in Query 4 and the selecct in Query 5.) Just that causes me to question whether your idea merits consideration. Received on Mon Jun 30 2008 - 05:55:56 CEST