Path: utzoo!utgpu!jarvis.csri.toronto.edu!cs.utexas.edu!swrinde!zaphod.mps.ohio-state.edu!uakari.primate.wisc.edu!aplcen!uunet!dev!dgis!jkrueger
From: jkrueger@dgis.dtic.dla.mil (Jon)
Newsgroups: comp.databases
Subject: Re: Automatic Query Generation
Keywords: Query, automatic, SQL, join
Message-ID: <774@dgis.dtic.dla.mil>
Date: 26 Feb 90 23:31:07 GMT
References: <5668@star.cs.vu.nl>
Organization: Defense Technical Information Center (DTIC), Alexandria VA
Lines: 30

bagron@cs.vu.nl (Rene Baart) writes:

>The information available to the program is the set of tables in the
>database (T1..Tn), the set of tables from which attributes are being
>queried (Q1..Qm, with m<n) and a list of "arrows" which indicate
>that a certain table has a foreign key in another table (Tx-->Ty).

>I am in need of a general algorithm for finding the set of tables that
>is needed to perform the join. What's more, if there is more than one
>possible join, the system should provide a list of alternatives for the
>user to choose from. 

Table definitions are generated from database design, you're trying to
"decompile" the other way.  You lack master-detail information,
referential integrities, domains, etc.  You can make some good guesses
but in general you can't be sure.  This is as much a measure of the
power of the relational model to ask interesting questions as it is of
the poverty of commercial products to express them.  Consider how
aggregates aren't handled in typical forms-based canned query tools,
and when they are, how much the user must know about the underlying
database design.  Or consider questions answerable only by queries
involving self-joins: how will this possibility be spotted by your
tool?  But don't feel bad, most third-party front ends don't try
anything of the sort; in general, they're tabular, not relational.

-- Jon
-- 
Jonathan Krueger    jkrueger@dtic.dla.mil   uunet!dgis!jkrueger
The Philip Morris Companies, Inc: without question the strongest
and best argument for an anti-flag-waving amendment.