Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> comp.databases.theory -> Re: Extending my question. Was: The relational model and relational algebra - why did SQL become the industry standard?
"Mikito Harakiri" <mikharakiri_at_ywho.com> wrote in message
news:lEz4a.23$h_3.149_at_news.oracle.com...
> "Bob Badour" <bbadour_at_golden.net> wrote in message
> news:OOy4a.31$yy3.2993280_at_mantis.golden.net...
> > > > Are you saying that a set-based relational dbms with full support
for
> > > > domains would not allow the user to freely transform data among
> > > > representations?
> > >
> > > Please, write down a query that transforms
> > >
> > > Num
> > > ---
> > > 2
> > > 5
> > >
> > > into
> > >
> > > Seq# Num
> > > ---- ---
> > > 1 2
> > > 2 2
> > > 1 5
> > > 2 5
> > > 3 5
> > > 4 5
> > > 5 5
> > >
> > > in pure relational syntax. Now, the same, please, without
> > explode/recursion
> > > operator.
> >
> > First, we will have to give a name to the original relation containing
the
> > Num column. I will use Foo for convenience. The solution follows:
> >
> > ( ( Integer RENAME Value as Seq# ) JOIN Foo ) RESTRICT Seq# BETWEEN 1
AND
> > Num )
> >
> > I assume that Integer is a named relation literal for the set of all
> > integers and that it has a single attribute, Value.
>
> The relational model is silent about the Integer Relation, of course.
Alas,
> this is something many users are constantly asking for.
>
> Is Integer a base relation or a view?
It is a named literal. It is neither stored nor derived from another relation.
> > In practice, Integer
> > would be a named relation literal for a set of integers within some
large
> > finite range of values.
>
If you prefer to completely ignore practice, simply omit the disclaimer. It is not required for the example. Otherwise, when we have an artifact capable of representing each of an infinite set of elements in finite time, let me know, and I'll get back to you.
> > Because the operands to the JOIN operation share no
> > attributes, the JOIN will, of course, evaluate to a cartesian product;
> > however, even the most primitive optimizers will evaluate the
restriction
> > before the join.
>
> Correct. The above relation should be executed as a Nested Loops where
inner
> relation -- Integer -- is range scanned (with the "restrict" predicate).
I wouldn't object if Foo were the inner relation and the outer relation were generated starting at 1 and ending when no more matches are found.
> > Mikito, I cannot imagine why you would seriously consider recursion for
> such
> > a simple expression. Was there some mistake or typo in your challenge?
>
Even with recursion, you will run into practical performance issues at some bound.
> > If you do not like the named literals, we could replace Foo and Integer
> > above with some suitable anonymous literals:
> >
> > Foo := RELATION ( TUPLE ( Num INTEGER( 2 ) ), TUPLE( Num INTEGER(
5 ) ) );
> > Integer := RELATION (
> > TUPLE ( Value INTEGER( 1 ) )
> > , TUPLE ( Value INTEGER( 2 ) )
> > , TUPLE ( Value INTEGER( 3 ) )
> > , TUPLE ( Value INTEGER( 4 ) )
> > , TUPLE ( Value INTEGER( 5 ) )
> > );
>
> Did I really have to explicitly say "the solution must also work for
numbers
> in the Foo other than 1,2,3,4,5" ?
No, which is why I chose the named literal, Integer. Since your example uses a literal relation with 2 and 5, I saw no reason to show a literal solution with superfluous tuples. I gave the reader the benefit of the doubt by assuming the reader has sufficient mental faculty to generalize the example on his or her own. Received on Tue Feb 18 2003 - 22:04:23 CST