Re: Can relational alegbra perform bulk operations?

Clifford Heath wrote:
> I believe you've had a good answer in the existence of NULLs and
> duplicates,

Indeed, Date gave a good discussion about why neither made sense in relations.

> but I don't know that SQL is *that* far. The relational algebra doesn't
> cover the most common four ways to implement an inner join; implementation
> isn't its concern. But choice of looping, merge joins, hash joins, etc,
> definitely affects query performance.

Yes, I may have had picked up mistaken impression from the same book thinking that relational model had something to say about optimizing the queries. It doesn't look to be the case here, and would definitely explain why SQL is fairly divorced from relational theory.

But it's not just the relational theory. Date provided an argument for a need of proper type & operator support as I told other poster in previous reply. Off the top of my head, not all vendors supports user-defined types (probably only handful) and of those that do, they don't provide a means of defining an operator for those types. Granted, they could be replicated via use of stored procedures/functions, middleware, or whatever. But what I think I had understood was that the "relational-object impedance" is fictional; impedance only exists because vendors didn't properly implement such things which would have had disposed of the problems of interacting with relations via OOP.

I think Date also argued that SQL's structure of SELECT - FROM - WHERE - ... is fairly rigid and may further complicate the expressiveness of queries and as a consequence, it's possible to write several different logically equivalent queries and have difference performance ramifications; IOW, SQL doesn't provide enough of independence from implementation. Whenever anyone has to port an application using say, SQL Server to Oracle, I would definitely expect some rewrites will be in order even if most of code was written in standard SQL and didn't rely on vendor-specific features/implementations.

One more reason also has to do with SQL's seemingly inconsistencies (to be specific here, SQL as implemented by different vendors, rather than the SQL standard). A prime example is the case of an SQL table where we have single column with UNIQUE constraint. Let's say it contains five rows consisting of { 5, 3, 4, 2, 1 } and we want to run this query:

UPDATE t SET col = col + 1;

Depending on which vendors' DBMS you use to run the query, it may suceed or fail. In Relational theory, it should succeed because the constraints should be evaluated on a per-statement basis, not per-row statement. This is a trivial example, but several more inconsistency does exists and I found that quite bizarre. I'm not a mathematician by trade but I do believe mathematics has rigid definitions so there's no "alternate version" of math, yet nobody seems to be adhering to the relational theory closely enough, as Date tries to argue in his book. Thus, my questions on this newsgroups.

> Also you have to appreciate that SQL is essentially a verbal form of
> relational calculus. That gets translated to algebra, and progressively
> refined towards physical operations in the formulation of a query plan.
> As a calculus, it isn't going to look algebraic, as, for example, QUEL did.

Interesting. IIRC, Date basically claims that SQL is a bit of relational algebra, a bit of relational calculus and a bit of neither, though I also recall that one objective of SQL was to make it easy to express without resorting to mathematics as its preprocessors required.

  > I wish more of his work was still available - I heard him speak about
> it - but he's still around, see <http://www.kenj.com.au/>. He doesn't
> live that far from me, and I've thought about looking him up. Perhaps
> he has some archived material he'd email you?

Thanks. I will look at the site.

> After MUSBUS, he continued the same kind of work at Pyramid Computers,
> with their fantastic multi-processor MIPS machines. They had a policy
> of being RDBMS agnostic, but liaising with the vendors closely on
> performance analysis so their hardware ran every RDBMS better than
> competitive h/w. I wish they were still around.

Fascinating. Received on Wed Sep 30 2009 - 14:54:31 CEST