Re: SQL (was: Why using "Group By")

"Peter Koch Larsen" <pkl_at_mailme.dk> wrote in message news:3e74628d$0$32000$edfadb0f_at_dread12.news.tele.dk...
>
> "Bob Badour" <bbadour_at_golden.net> skrev i en meddelelse
> news:xtIca.87$Cl6.10485819_at_mantis.golden.net...
> > "Peter Koch Larsen" <pkl_at_mailme.dk> wrote in message
> > news:3e732023$0$32010$edfadb0f_at_dread12.news.tele.dk...
> > >
>
> [snip]
>
> > > > > >
> > > > > > Forcing users to perform easily automated tasks? Do you really
> have
> > to
> > > > > ask?
> > > > > >
> > > > > Allow me to join this thread. So far as I understand the "D"
> > > meta-language
> > > > > and the Alphora implementation, a join is based on the naming of
the
> > > > columns
> > > > > in the respective relations. This is something that is not to my
> > liking:
> > > > my
> > > > > feeling is that naming might not be sufficiently consistent to use
> > that
> > > > > approach.
> > > >
> > > > Except for carelessness, why would it not be sufficiently
consistent?
> > >
> > > So You expect everything to be "created" by just one authority?
> >
> > No. I expect the people performing logical and physical design to have a
> > clue about the stored relations in the database. How else can they do
> their
> > jobs?
> >
> >
> > > This might
> > > not always be the case. As an example, consider buying a
shrink-wrapped
> > > product where all data is predefined. You might want to integrate the
> > tables
> > > defined by that product together with your own application. But two
> > > different entities did create the two parts of the DBMS - with noone
> > having
> > > a chance to coordinate anything.
> >
> > Nobody except the dba for the company who purchased the product. Are you
> > seriously suggesting that such a dba should perform logical and physical
> > design without any knowledge of the existing product's design or at
least
> of
> > its published application view?
> >
> > Are you suggesting the purchasing company should use a foreign data
model
> > without any kind of application view for the company's additions?
>
> I am not. Views would probably be added for the additions. That does not
> mean, however, that the original (base)relations will disappear.

Since the original product will not reference anything added by the purchasing company and since the purchasing company's code will reference its application views, I don't see a problem.

As far as the original product's published views, they will disappear for any user without access rights granted to them.

> > > > > Perhaps the idea would be okay if the same team of people
developed
> > the
> > > > > entire database, but in the not so uncommon situations where two
> > groups
> > > of
> > > > > people - say one frommanufacturing and one from accounting - each
> > > develop
> > > > > their part, naming might give false joins.
> > > >
> > > > Since they would use different views of the database constructed by
> the
> > > DBA
> > > > who completes the logical and physical designs, I don't know why
this
> > > would
> > > > happen.
> > >
> > > Because tou assume there is such an entity as "the DBA". Might not
> always
> > be
> > > realistic.
> >
> > The conceptual, logical and physical designs happen by themselves?!? Who
> > does them? Elves?
>
> Several independent DBA's working on each their problem domain.

Totally independent without any communication among them at all? Well, if some company is stupid enough to design a single database this way, they will get what they deserve. "Too many cooks spoil the broth."

> > > > > This is particularly so since
> > > > > most languages have the same word meaning different things
according
> > to
> > > > > context.
> > > >
> > > > Yes. Context is important. A name in one application view can mean
> > > something
> > > > different in another application view.
> > > >
> > > >
> > > > > If I were to develop a relational queru language, I would prefer a
> > > natural
> > > > > join command to depend not on names but on the logical
dependencies
> in
> > > the
> > > > > DBMS. Thus R join S would be meaningful if and only if the was a
> > foreing
> > > > key
> > > > > constraint from R to S or vice versa.
> > > >
> > > > What if there are multiple references? How would you handle the
> > works_for
> > > > example with two references to an employee relation?
> > >
> > > So relation R has two foreign keys on relation S? In that case the
JOIN
> > > would be ambigeous and you would have to revert to the explicit
method.

So, do you agree that the dbms must support some explicit method and that foreign keys alone to not suffice to specify join conditions? It seems above that you do agree.

> > > It might
> > > be slightly more inconvenient to use in some instances, but there are
no
> > > limitations per se.
> >
> > I disagree. The SQL way of doing things lacks cohesiveness, conceptual
> > clarity, and it puts an inappropriate burden on users.
>
> Im not a big fan of SQL, either. But expressing the general join as
> R {product} S {where} <cond> is not counter-intuitive in my opinion.
(names
> in {} are keywords of some kind). And if you do - out of convenience -
> define a {join}, I believe the appropriate definition would be to have R
> {join} S depend on the functional dependencies of that system, not on the
> names of the individual columns of R and S.

In a sensible system, {product} and {join} are the same operation and <cond> has no part in it--that's restrict. Cartesian product is simply a join between two relations without any common attributes. The extended cartesian product of SQL lacks cohesiveness, lacks conceptual clarity and puts an inappropriate burden on users. I never claimed it was counter-intuitive. I don't think anyone can credibly claim that natural join is counter-intuitive either.

Certainly, extended cartesian product is no more intuitive than natural join. Given the problems with cohesiveness, conceptual clarity and human burden, one would have to demonstrate some clear advantage for extended cartesian product to compensate.

The fact is, SQL must go to some additional trouble renaming columns according to arbitrary rules to turn what would sensibly be a more general natural join into a cartesian product. This arbitrary renaming puts an inappropriate burden on users (who at the very minimum must learn the arbitrary renaming rules and who generally will have to undo the effects of the renaming), reduces cohesion and obfuscates the underlying concepts. Received on Sun Mar 16 2003 - 17:55:10 CET