Re: Idempotence and Projection

<pamelafluente_at_libero.it> wrote in message news:1158744368.333186.13750_at_k70g2000cwa.googlegroups.com...
>
> David Cressey ha scritto:
>
> > If f(x) = SELECT * FROM x GROUP BY *
> >
> > then I would expect f(x) = x provided that x is a relational table. In
> > other words, I expect that projecting a relation into (or it it onto?)
its
> > own space is an idempotent operation.
> >
> > However, if x contains duplicate rows,
>
>
> Hi David, if you want to refer to my original problem, I must warn you
> you are not intending in my same sense sense.
>
> I never talked about "duplicate rows".

You didn't talk about it, but you presented duplicate rows in your example.

>
> The problem I am talking about is that in a chain of join you may have
> some values on some field replicated. This is * by the definition * of
> join. It's not an error.
>

> In the relation 1-N things on side 1 gets "replicated" (say,
> duplicated) when you join.
>

you can get the right number of rows in your query by projection.

> This can be however a problem in a report where you have "duplication
> sensitive" functions, for some (often pathological) design.
>

If the data design is pathological, it's sometimes better to copy the data to a database whose design is not pathological first. After that, getting the right result is merely a function of applying the correct relational operations. It's not hard to fashion your SQL so that it mimics relational calculus, provided you start with relational tables to begin with. If you apply SQL to bags and expect the result that relational calculus predicts for sets, you're going to get some nasty surprises.

> In complex query done on arbitrary design you may end up with a wrong
> report and you may not be aware of the "errors". The join worked as
> intended but the result is not what was expected.
>
> I wanted to propose a general algorithm to detect that and to create
> appropriate queries by appropriate splitting in subqueries.
>
> I am talking about arbitrary design. Forget about normal forms. In my
> view the algorithm (systematic method) must be able to process
> anything. I think in terms of patterns of tables and relations (don't
> care about semantic or records)
>

OK. I'll exit this thread and let you carry on that discussion with other participants. In my view, the whole point behind mastering and using databases is to avoid the situation you wish to systematize. I don't think I can contribute anything that will be useful to you. Received on Wed Sep 20 2006 - 14:25:34 CEST