Re: separation of church and state?

paul c wrote:
> I finally sprung for CJ Date's "Writings, 2000-2006" and skimming it,
> noticed this point in chapter 10. It reminded me of the recent posts
> about avoiding books that start with silly sentences even though this
> quote is from page 174:
>
> "Ordering, by contrast, is not part of the relational algebra; nor can
> it be, because its result isn't a relation. This doesn't mean you
> can't have an ORDER BY operator, of course - it just means that
> operator isn't part of the algebra as such, and it can't be used in
> an expression that's nested inside some other (relational)
> expression, or more generally in any context where the result is
> indeed required to be a relation. That's why you can't use ORDER BY
> in a view definition, for example."
>
> It seems a little doctrinaire to me. I can agree that the "result
> isn't a relation" but on the other hand a user could see such a
> result without knowing that "ORDER BY" was involved and not be
> faulted for taking it to be a relation. For that matter, in some
> apps, users take it for granted that all results are arbitrarily
> ordered and that those results can be used to produce other results.
>
> By analogy of separating the logical from physical implementation, if
> you want to declare a separation of church and state, I'd think you'd
> need to mention both. Not to tout SQL but I took the above to mean
> that if a table were declared with an "INDEX", it shouldn't be
> allowed to participate in expressions of the relational algebra,
> which seems extreme and somewhat useless to me.
>
> This is just one small paragraph and I'm not complaining about the
> rest of the book - Am I mis-interpreting?

I did not read the thread yet, ...

Yes, you are misinterpreting.

An index is a physical structure (not a logical one), used by the optimizer when determining the access path. No indices need to exist in theory for any table. This is in fact true on OracleRdb where order by, PRIMARY KEY, FOREIGN KEY etc are in fact enforced (albeit with rather sluggish results) without the need for CREATE INDEX statements.

Data in a relation is unordered by definition, as CJD points out. That the database happens to return it in some apparent order is irrelevant. Relational databases normally (and correctly) provide no guarantees of order without the ORDER BY clause.

While some products allow the definition of ORDER by in a view definition, and it is somewhat convenient for the DBA to deliver a VIEW to an app. programmer which forces ORDER BY (because app. programmers are too useless to know that they need to specify it and not rely on apparent order), it is in fact incorrect, as CJD notes.

Dr. Dweeb Received on Fri Oct 19 2007 - 16:06:28 CEST