Re: Examples of SQL anomalies?

"JOG" <jog_at_cs.nott.ac.uk> wrote in message news:6c63d103-9c6d-457d-a502-005aa5166407_at_m3g2000hsc.googlegroups.com...
> On Jun 28, 8:48 pm, Rob <rmpsf..._at_gmail.com> wrote:
> > 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.
>
> Yes and it was twaddle then, as it is twaddle now. Foreign key
> references provide all the "structure" one would need. Please don't
> see this as a request for a debate. I am merely pointing out that the
> OP should not waste his time trying to decipher your sales-pitch. If
> you have no useful answers to a genuine question on the OP's part then
> it should not be seen as another opportunity to sell your chocolate
> teapots.
>

This debate (the one you didn't want to start) is further confused by the overloading of the term "relationships". On the one hand, the distinction between "relations" and "relationships" is whether the tuple components are identified by position or by name within the tuple. On another level, "relationships" is used to refer to the connection between foreign keys, and the key that they reference (usually, but not necessarily, a "primary key"). On yet a third level, relationships are used in ER models to refer to associations between "entities". These three meanings are very closely related, but they aren't exactly the same.

The biggest problem here with the relational model, as implemented in SQL and as used by the thundering herd, is that many relationships are undeclared in the schema. In general, the only way to declare a relationship in SQL DDL is to establish a referential integrity constraint. sometime in the 90s, some dialects of SQL added the capability of declaring an unenforced relational integrity constraint. This amounts to declaring a relationship, and permitting a rogue process to break it.

The discovery of useful relationships after the schema is built and the database is populated is one of the "flexibility" features of the relational model and the SQL implementation that has made them so attractive to the pragmatists among the thundering herd. I think they have a point. Received on Wed Jul 02 2008 - 14:44:42 CEST