Re: Fabian Pascal and RDBMS deficiencies in fully

I'm reading, and enjoying immensely, Fabial Pascal's book "Practical
Issues in Database Management."

Though I've just gotten started with the book, he seems to be saying
that modern RDBMSs aren't as faithful to relational theory as they
ought to be, and that this has many *practical* consequences, e.g. lack
of functionality.

Given that PostgreSQL is open source, it seems a more likely candidate
for addressing Pascal's concerns. At least the potential is there.

Some questions:

1) Is PostgreSQL more faithful to relational theory? If so, do you find
yourself using the additional functionality afforded by this? e.g. does
it really matter to what you do in your daily work.

2) If PostgreSQL is *not* significantly more faithful to relational
theory than commercial RDBMSs, is it at least on the road to becoming
more faithful?

3) If PostgreSQL is not on the road to becoming more faithful to
relational theory and purity, why not? Is it due to the fact that
various SQL standards are themselves not fully faithful to relational
theory, and most RDBMSs have as a primary design goal to be faithful to
standards (which Pascal implies *are* relationally deficient)?

4) Which database, commercial or non-commercial, is most faithful to
relational theory itself, or is headed in that direction the quickest?

Dana

In what way could a database like PostgreSQL not be "faithful to
relational theory"? Does he give any explanation as to what that means?

Some people mistake the word relational for the meaning of
normalization, but they do not have the same meaning. If Fabial is
mistaking relational for normalization then that would make sense
because there is nothing to force the use of normalization.

On Thu, 2006-06-08 at 05:21 -0700, dananrg(at)yahoo(dot)com wrote:

> I'm reading, and enjoying immensely, Fabial Pascal's book "Practical
> Issues in Database Management."
>
> Though I've just gotten started with the book, he seems to be saying
> that modern RDBMSs aren't as faithful to relational theory as they
> ought to be, and that this has many *practical* consequences, e.g. lack
> of functionality.
>
> Given that PostgreSQL is open source, it seems a more likely candidate
> for addressing Pascal's concerns. At least the potential is there.
>
> Some questions:
>
> 1) Is PostgreSQL more faithful to relational theory? If so, do you find
> yourself using the additional functionality afforded by this? e.g. does
> it really matter to what you do in your daily work.
>
> 2) If PostgreSQL is *not* significantly more faithful to relational
> theory than commercial RDBMSs, is it at least on the road to becoming
> more faithful?
>
> 3) If PostgreSQL is not on the road to becoming more faithful to
> relational theory and purity, why not? Is it due to the fact that
> various SQL standards are themselves not fully faithful to relational
> theory, and most RDBMSs have as a primary design goal to be faithful to
> standards (which Pascal implies *are* relationally deficient)?
>
> 4) Which database, commercial or non-commercial, is most faithful to
> relational theory itself, or is headed in that direction the quickest?
>
> Dana
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster

Royce Souther
www.SiliconTao.com
Let Open Source help your business move beyond.

For security this message is digitally authenticated by GnuPG.

On Thu, 2006-06-08 at 07:21, dananrg(at)yahoo(dot)com wrote:
> I'm reading, and enjoying immensely, Fabial Pascal's book "Practical
> Issues in Database Management."
>
> Though I've just gotten started with the book, he seems to be saying
> that modern RDBMSs aren't as faithful to relational theory as they
> ought to be, and that this has many *practical* consequences, e.g. lack
> of functionality.

I generally find the arguments that modern RDBMSes aren't faithful to
relational theory to be much like the old arguments about how many
angels could dance on the head of a pin.

The single most commonly trotted out one is that in relational and set
theory, duplicates are not allowed, but modern RDBMS systems allow
duplicates, unless you design your schema to reject duplicates on
purpose.

While it would sometimes be useful to have my database automatically
declare all tables to have a unique index across every field in a table,
I know that performance wise this could be a disaster for some data
sets, and often it would have no real value. Not that they don't have a
valid argument, I just think it's a kind of petty argument.

The other arguments that show up generally have to do with having the
ability to get the same data results in more than one way (i.e. a
non-deterministic methodology) which other query languages seemed to
have solved in the past. OTOH, PostgreSQL descended from a database
that used the QUEL query language, and it seems a bit hard to find other
QUEL databases. SQL may not be perfect, but it is fairly ubiquitous,
and that makes porting to or from one SQL db to another moderately easy.

To me, the real argument is, "Is SQL so lacking that it should be
replaced". In what REAL measurable ways is SQL lacking so badly we
should toss it and start over? It's not perfect, that's for sure. But
what's the investment on starting over, and the possible traction of a
non-SQL database in a largely SQL driven market?

In the last exciting episode, dananrg(at)yahoo(dot)com wrote:
> I'm reading, and enjoying immensely, Fabial Pascal's book "Practical
> Issues in Database Management."
>
> Though I've just gotten started with the book, he seems to be saying
> that modern RDBMSs aren't as faithful to relational theory as they
> ought to be, and that this has many *practical* consequences, e.g. lack
> of functionality.
>
> Given that PostgreSQL is open source, it seems a more likely candidate
> for addressing Pascal's concerns. At least the potential is there.
>
> Some questions:
>
> 1) Is PostgreSQL more faithful to relational theory? If so, do you find
> yourself using the additional functionality afforded by this? e.g. does
> it really matter to what you do in your daily work.

PostgreSQL's fidelity is more to the SQL standards than to relational
theory, per se, but there are places where it does "play better" than
some of its competitors with regard to relational behaviour.

Oracle, for instance, has the trouble that it does not generally
perceive NULL as being distinct from empty strings.

> 2) If PostgreSQL is *not* significantly more faithful to relational
> theory than commercial RDBMSs, is it at least on the road to
> becoming more faithful?

It's probably fairer to say that it isn't on any road to become "less
faithful."

The tendancy for products like Oracle and DB2 has been to hearken down
roads with XML on the signs, and to lose rational behaviour on that
basis.

> 3) If PostgreSQL is not on the road to becoming more faithful to
> relational theory and purity, why not? Is it due to the fact that
> various SQL standards are themselves not fully faithful to relational
> theory, and most RDBMSs have as a primary design goal to be faithful to
> standards (which Pascal implies *are* relationally deficient)?

There are two big bits of trouble to SQL:

1. NULL is a problem (where there isn't any choice of implementation
strategies that *won't* lead to considerable pain), which is why the
would-be relational successor, Tutorial D, rejects having NULL
altogether.

2. Features get added to SQL standards without much regard to
fidelity to a relational model. Often, features get added because
they seem cool, and because Microsoft or IBM figure the features can
get them some sales. That doesn't guarantee fidelity to much of
anything :-(.

> 4) Which database, commercial or non-commercial, is most faithful to
> relational theory itself, or is headed in that direction the
> quickest?

The product that appears to have greatest fidelity to SQL standards is
Mimer SQL. That's not quite the same thing as fidelity to relational
theory, of course.

One of the interesting edges, with PostgreSQL, is the whole
"object/relational" side of it. Darwen and Date were highly critical
of O/R; they felt that "doing relational right" was generally a better
thing.

I'd argue that with the extensible type system, PostgreSQL does parts
of this quite right.

The place where there is controversy is in how PostgreSQL should deal
with things like inheritance, when inheriting attributes from a table,
and the lack of agreement seems to me to demonstrate that there's not
any particular will to continue down the "O/R dogma" road...
--
let name="cbbrowne" and tld="gmail.com" in name ^ "@" ^ tld;;
http://linuxdatabases.info/info/nonrdbms.html
"I will not send lard through the mail" ^ 100 -- Bart Simpson

smarlowe(at)g2switchworks(dot)com (Scott Marlowe) writes:
> To me, the real argument is, "Is SQL so lacking that it should be
> replaced". In what REAL measurable ways is SQL lacking so badly we
> should toss it and start over? It's not perfect, that's for sure.
> But what's the investment on starting over, and the possible
> traction of a non-SQL database in a largely SQL driven market?

The only visible alternative, at this point, is Tutorial D, and it
doesn't particularly excite me...
--
"cbbrowne","@","ntlug.org"
http://www3.sympatico.ca/cbbrowne/languages.html
A mathematician is a machine for converting caffeine into theorems.

On Thu, 2006-06-08 at 16:17, Chris Browne wrote:
> smarlowe(at)g2switchworks(dot)com (Scott Marlowe) writes:
> > To me, the real argument is, "Is SQL so lacking that it should be
> > replaced". In what REAL measurable ways is SQL lacking so badly we
> > should toss it and start over? It's not perfect, that's for sure.
> > But what's the investment on starting over, and the possible
> > traction of a non-SQL database in a largely SQL driven market?
>
> The only visible alternative, at this point, is Tutorial D, and it
> doesn't particularly excite me...

On the other hand, it's nice to know it's dragged down by a moniker even
more awkward than PostgreSQL's!

Honestly, Tutorial D does not make me think database. It makes me think
Calculus help line.

On Thu, Jun 08, 2006 at 05:21:07AM -0700, dananrg(at)yahoo(dot)com wrote:
> I'm reading, and enjoying immensely, Fabial Pascal's book "Practical
> Issues in Database Management."

Be aware that Pascal, along with Date and Darwen, are...how do I put
this gently...cranks. They've been getting more strident and
irrational as the decades go by.

Pascal, Date, and Darwen have been alleging for years, with increasing
shrillness, that DBMSs should be based on set theory and 2-value
logic. I say "alleging" because they have not backed up this idea
with any actual software that others could test. SQL DBMSs are based
on bag theory[1] and 3-value logic[2]. Until they come up with a
testable system, or Hell freezes over, whichever comes first, Pascal's
book will make a good companion on your shelf to books on
Phlogiston[3] theory, or a decent doorstop, whichever you prefer.

Cheers,
D

[1] Basically set theory with repetition of elements allowed. This in
turn makes aggregates easy and natural to implement.

[2] A boolean value can take the values true, false and unknown.

[3] http://en.wikipedia.org/wiki/Phlogiston

--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!

On Thu, 2006-06-08 at 17:14, David Fetter wrote:

> Pascal, Date, and Darwen have been alleging for years, with increasing
> shrillness, that DBMSs should be based on set theory and 2-value
> logic. I say "alleging" because they have not backed up this idea
> with any actual software that others could test. SQL DBMSs are based
> on bag theory[1] and 3-value logic[2]. Until they come up with a
> testable system, or Hell freezes over, whichever comes first, Pascal's
> book will make a good companion on your shelf to books on
> Phlogiston[3] theory, or a decent doorstop, whichever you prefer.

Now you're just being ridiculous.

Most of those books are nowhere near heavy enough to be a good door
stop.

And I don't know what your problem with Phlogiston is, it's a perfectly
cromulent thoery. (see
http://en.wikipedia.org/wiki/Cromulent#Cromulent)

On Thu, Jun 08, 2006 at 05:22:46PM -0500, Scott Marlowe wrote:
> On Thu, 2006-06-08 at 17:14, David Fetter wrote:
>
> > Pascal, Date, and Darwen have been alleging for years, with
> > increasing shrillness, that DBMSs should be based on set theory
> > and 2-value logic. I say "alleging" because they have not backed
> > up this idea with any actual software that others could test. SQL
> > DBMSs are based on bag theory[1] and 3-value logic[2]. Until they
> > come up with a testable system, or Hell freezes over, whichever
> > comes first, Pascal's book will make a good companion on your
> > shelf to books on Phlogiston[3] theory, or a decent doorstop,
> > whichever you prefer.
>
> Now you're just being ridiculous.
>
> Most of those books are nowhere near heavy enough to be a good door
> stop.

I'm sorry I exaggerated here. You're right, of course. Most of them
would make only half-way decent doorstops. Date's latest from
O'Reilly isn't even that.

> And I don't know what your problem with Phlogiston is, it's a
> perfectly cromulent thoery. (see
> http://en.wikipedia.org/wiki/Cromulent#Cromulent)

The Simpsons is almost enough to make me get a working TV and watch
Fox on it, but not quite :)

Cheers,
D
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!

To balance the discussion, I would like to say that I thoroughly
enjoyed Date's latest "Database In Depth". It gave me a strong
foundation in relational theory and I can say that I think more about
my schema designs thanks to the advice in the text. Just because SQL
may allow something, doesn't make it good. If you don't use NULL, then
you don't come across 3-valued logic--problem solved.

Some Tutorial D notions really make sense; I would love to be able to
rely on every function returning a relation.

Everything is OK until he starts peddling TransRelational™ software...

On Jun 8, 2006, at 6:14 PM, David Fetter wrote:

> On Thu, Jun 08, 2006 at 05:21:07AM -0700, dananrg(at)yahoo(dot)com wrote:
>> I'm reading, and enjoying immensely, Fabial Pascal's book "Practical
>> Issues in Database Management."
>
> Be aware that Pascal, along with Date and Darwen, are...how do I put
> this gently...cranks. They've been getting more strident and
> irrational as the decades go by.
>
> Pascal, Date, and Darwen have been alleging for years, with increasing
> shrillness, that DBMSs should be based on set theory and 2-value
> logic. I say "alleging" because they have not backed up this idea
> with any actual software that others could test. SQL DBMSs are based
> on bag theory[1] and 3-value logic[2]. Until they come up with a
> testable system, or Hell freezes over, whichever comes first, Pascal's
> book will make a good companion on your shelf to books on
> Phlogiston[3] theory, or a decent doorstop, whichever you prefer.
>
> Cheers,
> D

> ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬
AgentM
agentm(at)themactionfaction(dot)com
¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬

On Thursday 2006-06-08 15:14, David Fetter wrote:
> On Thu, Jun 08, 2006 at 05:21:07AM -0700, dananrg(at)yahoo(dot)com wrote:

> on bag theory[1] and 3-value logic[2]. Until they come up with a
> testable system, or Hell freezes over, whichever comes first, Pascal's
> book will make a good companion on your shelf to books on
> Phlogiston[3] theory, or a decent doorstop, whichever you prefer.

I have encountered at least two commercial database products that declared
every column "NOT NULL". I have always assumed that this was defensive,
preventing stupid programmer mistakes.

I recall reading somewhere that Codd proposed multiple flavors of nullity.
Are there theoretical proposals for databases with logical systems having
more than three values?

On Thu, Jun 08, 2006 at 06:09:21PM -0700, Trent Shipley wrote:
> On Thursday 2006-06-08 15:14, David Fetter wrote:
> > On Thu, Jun 08, 2006 at 05:21:07AM -0700, dananrg(at)yahoo(dot)com wrote:
>
> > on bag theory[1] and 3-value logic[2]. Until they come up with a
> > testable system, or Hell freezes over, whichever comes first,
> > Pascal's book will make a good companion on your shelf to books on
> > Phlogiston[3] theory, or a decent doorstop, whichever you prefer.
>
> I have encountered at least two commercial database products that
> declared every column "NOT NULL". I have always assumed that this
> was defensive, preventing stupid programmer mistakes.

It's not that simple. If there are no NULLs allowed anywhere, that
means that you can't even have them as the output of a SELECT
statement, which means no OUTER JOINs. No repetitions means none
anywhere, which means that they can't be the output of a query either,
and makes it complicated at best to do aggregates. The whole thing is
just ridiculous on its face.

> I recall reading somewhere that Codd proposed multiple flavors of
> nullity. Are there theoretical proposals for databases with logical
> systems having more than three values?

Codd proposed two different NULLs, if I recall right, and some people
have come up with tens of different meanings that NULL might have,
which leads to some major silliness wherein your "truth table" is a
grid that doesn't legibly fit on a piece of A4 paper.

Cheers,
D
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!

A long time ago, in a galaxy far, far away, tshipley(at)deru(dot)com (Trent Shipley) wrote:
> On Thursday 2006-06-08 15:14, David Fetter wrote:
>> On Thu, Jun 08, 2006 at 05:21:07AM -0700, dananrg(at)yahoo(dot)com wrote:
>
>> on bag theory[1] and 3-value logic[2]. Until they come up with a
>> testable system, or Hell freezes over, whichever comes first,
>> Pascal's book will make a good companion on your shelf to books on
>> Phlogiston[3] theory, or a decent doorstop, whichever you prefer.
>
> I have encountered at least two commercial database products that
> declared every column "NOT NULL". I have always assumed that this
> was defensive, preventing stupid programmer mistakes.
>
> I recall reading somewhere that Codd proposed multiple flavors of
> nullity. Are there theoretical proposals for databases with logical
> systems having more than three values?

Darwen did a paper where he described how you'd cope with not having
any nulls. It amounted to having a whole bunch of views that would
have a whole host of special indicator values to replace the multiple
meanings of NULL...
--
let name="cbbrowne" and tld="gmail.com" in String.concat "@" [name;tld];;
http://linuxdatabases.info/info/lisp.html
Including a destination in the CC list that will cause the recipients'
mailer to blow out is a good way to stifle dissent.
-- from the Symbolics Guidelines for Sending Mail

Agent M wrote:
> If you don't use NULL, then you don't
> come across 3-valued logic--problem solved.

So was does "SELECT sum(1) FROM dual WHERE false" return?

/Nis

Well, the Date argument against NULLs (and he never endorsed them, or
so he claims) is that they are not data- they represent the absence of
data- so why put non-data in a _data_base.

If you are asking yourself the question how you can have support
multiple meanings in a column, normalize. Then, baldness is just
another value and you don't have to guess if the hair color is unknown,
undefined, or missing.

On Jun 8, 2006, at 10:10 PM, Christopher Browne wrote:

> A long time ago, in a galaxy far, far away, tshipley(at)deru(dot)com (Trent
> Shipley) wrote:
>> On Thursday 2006-06-08 15:14, David Fetter wrote:
>>> On Thu, Jun 08, 2006 at 05:21:07AM -0700, dananrg(at)yahoo(dot)com wrote:
>>
>>> on bag theory[1] and 3-value logic[2]. Until they come up with a
>>> testable system, or Hell freezes over, whichever comes first,
>>> Pascal's book will make a good companion on your shelf to books on
>>> Phlogiston[3] theory, or a decent doorstop, whichever you prefer.
>>
>> I have encountered at least two commercial database products that
>> declared every column "NOT NULL". I have always assumed that this
>> was defensive, preventing stupid programmer mistakes.
>>
>> I recall reading somewhere that Codd proposed multiple flavors of
>> nullity. Are there theoretical proposals for databases with logical
>> systems having more than three values?
>
> Darwen did a paper where he described how you'd cope with not having
> any nulls. It amounted to having a whole bunch of views that would
> have a whole host of special indicator values to replace the multiple
> meanings of NULL...
> --
> let name="cbbrowne" and tld="gmail.com" in String.concat "@"
> [name;tld];;
> http://linuxdatabases.info/info/lisp.html
> Including a destination in the CC list that will cause the recipients'
> mailer to blow out is a good way to stifle dissent.
> -- from the Symbolics Guidelines for Sending Mail
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that
> your
> message can get through to the mailing list cleanly
>
¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬
AgentM
agentm(at)themactionfaction(dot)com
¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬

On Jun 8, 2006, at 9:32 PM, David Fetter wrote:

> On Thu, Jun 08, 2006 at 06:09:21PM -0700, Trent Shipley wrote:
>> On Thursday 2006-06-08 15:14, David Fetter wrote:
>>> On Thu, Jun 08, 2006 at 05:21:07AM -0700, dananrg(at)yahoo(dot)com wrote:
>>
>>> on bag theory[1] and 3-value logic[2]. Until they come up with a
>>> testable system, or Hell freezes over, whichever comes first,
>>> Pascal's book will make a good companion on your shelf to books on
>>> Phlogiston[3] theory, or a decent doorstop, whichever you prefer.
>>
>> I have encountered at least two commercial database products that
>> declared every column "NOT NULL". I have always assumed that this
>> was defensive, preventing stupid programmer mistakes.
>
> It's not that simple. If there are no NULLs allowed anywhere, that
> means that you can't even have them as the output of a SELECT
> statement, which means no OUTER JOINs. No repetitions means none
> anywhere, which means that they can't be the output of a query either,
> and makes it complicated at best to do aggregates. The whole thing is
> just ridiculous on its face.

Yes, no repetition. The point is that all functions should return
actual relations, so no ordering either. This makes it trivial to chain
the operations which is painful to do in SQL because you have to pay
attention to return types *and* whether or not the subquery returns one
or more rows or just one "value".

The OUTER JOIN stuff is a limitation of SQL. In the relational model,
there is no reason a column type cannot be a relation itself, however
SQL can't cope well with that. Aggregates can return relations as
header types so all the applicable tuples are returned. I have to do
that in PostgreSQL with arrays but there is no trivial way to convert
between arrays and a fake view or table or a VALUES() construct :-(

¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬
AgentM
agentm(at)themactionfaction(dot)com
¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬

# nis(at)superlativ(dot)dk / 2006-06-09 10:12:21 +0200:
> Agent M wrote:
> > If you don't use NULL, then you don't
> > come across 3-valued logic--problem solved.
>
> So was does "SELECT sum(1) FROM dual WHERE false" return?

You stripped this:

> > Some Tutorial D notions really make sense; I would love to be able
> > to rely on every function returning a relation.

So your query would prolly return a table with one (INT) column,
but no rows.

--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man. You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991

dananrg(at)yahoo(dot)com wrote:

>I'm reading, and enjoying immensely, Fabial Pascal's book "Practical
>Issues in Database Management."
>
>
I also found this book very useful when I first started doing serious
database work. For a more thorough treatment of many of these issues,
see An Introduction to Database Systems by Chris Date. The latter book
is so full of detail that it is sometimes hard to follow, but it's worth
the effort.

>Though I've just gotten started with the book, he seems to be saying
>that modern RDBMSs aren't as faithful to relational theory as they
>ought to be, and that this has many *practical* consequences, e.g. lack
>of functionality.
>
>Given that PostgreSQL is open source, it seems a more likely candidate
>for addressing Pascal's concerns. At least the potential is there.
>
>
Although some DBMSs have invented new ways to break the relational
model, the fundamental problems are in SQL. No DBMS based on SQL is
going to be able to support RM correctly.

>Some questions:
>
>1) Is PostgreSQL more faithful to relational theory? If so, do you find
>yourself using the additional functionality afforded by this? e.g. does
>it really matter to what you do in your daily work.
>
Within the limitations imposed by the SQL standard, PostgreSQL seems to
do about as well as could be expected, but falls short as all SQL DBMSs
must. For example, PostgreSQL allows NULLs and duplicate rows (there
are preventive measures against both of these but you have to be careful
to avoid them, and sometimes you can't). One feature of RM PostgreSQL
lacks are nested relations (a bad idea for base table design but useful
in query results).

Regards,

--
--------------------------------------------------------------------
Aaron Bingham
Senior Software Engineer
Cenix BioScience GmbH
--------------------------------------------------------------------

Roman Neuhauser wrote:
> # nis(at)superlativ(dot)dk / 2006-06-09 10:12:21 +0200:
>> Agent M wrote:
>>> If you don't use NULL, then you don't
>>> come across 3-valued logic--problem solved.
>> So was does "SELECT sum(1) FROM dual WHERE false" return?
>
> You stripped this:
>
>>> Some Tutorial D notions really make sense; I would love to be able
>>> to rely on every function returning a relation.

Yes. I didn't see its relevance to the claim that "if you don't use
null, you will not come across 3-valued logic". My point being that you
need to make sure that your queries don't generate them behind your back
either.

> So your query would prolly return a table with one (INT) column,
> but no rows.

Well, if you are not bound by the SQL standard and its mistakes, the
"obvious" return value would be one row, one column, containing 0.

/Nis

Also, Date mentions the notion that tables don't have to be mapped to
individual files. For example, if the types of queries are known in
advance, it could be possible to rearrange the data to be optimal for
those queries. Currently, tables are just big serialized arrays.

On Fri, June 9, 2006 9:55 am, Aaron Bingham wrote:
> dananrg(at)yahoo(dot)com wrote:
>
>> I'm reading, and enjoying immensely, Fabial Pascal's book "Practical
>> Issues in Database Management."
>>
>>
>>
> I also found this book very useful when I first started doing serious
> database work. For a more thorough treatment of many of these issues, see
> An Introduction to Database Systems by Chris Date. The latter book
> is so full of detail that it is sometimes hard to follow, but it's worth
> the effort.
>
>> Though I've just gotten started with the book, he seems to be saying
>> that modern RDBMSs aren't as faithful to relational theory as they ought
>> to be, and that this has many *practical* consequences, e.g. lack of
>> functionality.
>>
>> Given that PostgreSQL is open source, it seems a more likely candidate
>> for addressing Pascal's concerns. At least the potential is there.
>>
>>
> Although some DBMSs have invented new ways to break the relational
> model, the fundamental problems are in SQL. No DBMS based on SQL is going
> to be able to support RM correctly.
>
>> Some questions:
>>
>>
>> 1) Is PostgreSQL more faithful to relational theory? If so, do you find
>> yourself using the additional functionality afforded by this? e.g.
>> does it really matter to what you do in your daily work.
>>
> Within the limitations imposed by the SQL standard, PostgreSQL seems to
> do about as well as could be expected, but falls short as all SQL DBMSs
> must. For example, PostgreSQL allows NULLs and duplicate rows (there are
> preventive measures against both of these but you have to be careful to
> avoid them, and sometimes you can't). One feature of RM PostgreSQL lacks
> are nested relations (a bad idea for base table design but useful in query
> results).
>
> Regards,
>
>
> --
> --------------------------------------------------------------------
> Aaron Bingham
> Senior Software Engineer
> Cenix BioScience GmbH
> --------------------------------------------------------------------
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>
> http://www.postgresql.org/docs/faq
>
>

On Fri, Jun 09, 2006 at 07:09:12AM -0400, Agent M wrote:
> Well, the Date argument against NULLs (and he never endorsed them, or
> so he claims) is that they are not data- they represent the absence of
> data- so why put non-data in a _data_base.

At this point you could start a whole philosophical discussion about
whether knowing you don't know something is a fact worth storing.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

On Fri, Jun 09, 2006 at 03:55:04PM +0200, Aaron Bingham wrote:
> dananrg(at)yahoo(dot)com wrote:

> >I'm reading, and enjoying immensely, Fabial Pascal's book
> >"Practical Issues in Database Management."

If you're interested in the theory of RDBMSs, you can start with the
papers on Leonid Libkin's page and the books and papers they
reference. :)

http://www.cs.toronto.edu/~libkin/publ.html

> I also found this book very useful when I first started doing
> serious database work. For a more thorough treatment of many of
> these issues, see An Introduction to Database Systems by Chris Date.
> The latter book is so full of detail that it is sometimes hard to
> follow,

It certainly has an elaborate and well-thought-out system of ideas.
As an empiricist, I find it crucially important that despite decades
of whining about it, no one has come up with an actual *computer*
system which implements this /gedankenexperiment/. This means to me
that we ought at least to consider the idea that Date, Darwen and
Pascal got some of their premises wrong and that reasoning from them
is as silly as reasoning based on the Phlogiston theory I mentioned
earlier.

> but it's worth the effort.

Why? There are much more entertaining cranks out there if crank study
is your thing.

> >Though I've just gotten started with the book, he seems to be
> >saying that modern RDBMSs aren't as faithful to relational theory
> >as they ought to be, and that this has many *practical*
> >consequences, e.g. lack of functionality.
> >
> >Given that PostgreSQL is open source, it seems a more likely
> >candidate for addressing Pascal's concerns. At least the potential
> >is there.
>
> Although some DBMSs have invented new ways to break the relational
> model, the fundamental problems are in SQL.

Um, no. As I'll demonstrate below, it's the model that's broken and
SQL that got it right.

> No DBMS based on SQL is going to be able to support RM correctly.

Aha! I spy, with my little eye, a fanboy. You have to be a bit of a
theory wonk to call Date's stuff "RM." You seem to be implying here
that Date's "RM" is somehow more desirable than what SQL actually
provides. To be more desirable, I don't think it's unreasonable to
say that it should be more powerful in some essential way.

In SQL, you can do this (this example condensed from Libkin's
"Expressive Power of SQL" on the page above):

SELECT
(SELECT count(*) FROM table_1) <
(SELECT count(*) FROM table_2)
AS "Can't compare cardinalities in first order logic";

Note the name of the output column. It's important and true, as you
can verify if you care to do your homework on foundations of
mathematics. Relational algebra is a subset of first-order logic
<http://en.wikipedia.org/wiki/Relational_algebra>, and as a direct
consequence, you can't do this simple but interesting thing with it.

What say we just stop right there and call Date's Relational Model
what it is: a silly edifice built atop wrong premises.

Cheers,
D
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!

On Fri, Jun 09, 2006 at 05:20:46PM +0200, Martijn van Oosterhout wrote:
> On Fri, Jun 09, 2006 at 07:09:12AM -0400, Agent M wrote:
> > Well, the Date argument against NULLs (and he never endorsed them,
> > or so he claims) is that they are not data- they represent the
> > absence of data- so why put non-data in a _data_base.
>
> At this point you could start a whole philosophical discussion about
> whether knowing you don't know something is a fact worth storing.

And to me, the answer is an unqualified "yes." A state of ignorance
is an important piece of information by itself.

For example, that I don't know someone's birthdate is important. When
I'm trying to figure out when to send a birthday card, knowing that I
don't know this piece of information means that I take a different
action "decide whether to try to find out what the birthdate is." from
the action I would take if I didn't know that I don't know the
birthdate, which is "rummage through all my records trying to find the
birthdate."

Cheers,
D
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!

On Fri, June 9, 2006 11:45 am, David Fetter wrote:
> On Fri, Jun 09, 2006 at 05:20:46PM +0200, Martijn van Oosterhout wrote:
>
>> On Fri, Jun 09, 2006 at 07:09:12AM -0400, Agent M wrote:
>>
>>> Well, the Date argument against NULLs (and he never endorsed them,
>>> or so he claims) is that they are not data- they represent the absence
>>> of data- so why put non-data in a _data_base.
>>
>> At this point you could start a whole philosophical discussion about
>> whether knowing you don't know something is a fact worth storing.
>
> And to me, the answer is an unqualified "yes." A state of ignorance
> is an important piece of information by itself.
>
> For example, that I don't know someone's birthdate is important. When
> I'm trying to figure out when to send a birthday card, knowing that I
> don't know this piece of information means that I take a different action
> "decide whether to try to find out what the birthdate is." from
> the action I would take if I didn't know that I don't know the birthdate,
> which is "rummage through all my records trying to find the birthdate."

So you should normalize and add relations to represent the state
adequately. NULL doesn't give you enough information anyway- does NULL in
a birthday header mean "no birthday", "n/a" (a business doesn't have a
birthday), "not born yet", etc... Using real data, you can represent any
of these states.

>> Yes, and all SQL products worth their salt include some languages to
>> provide iteration and other processing that SQL can't do or doesn't do
>> well. Why must the rules be different for a truly relational db. (see
>> http://dbappbuilder.sourceforge.net/Rel.html)
> I may get interested if some actual software which implements Date's
> Relational Model ever comes out. Or I may not, as I am getting lots
> of useful work done using SQL and friends. We empiricists are like that.

You mean like the Java software I pointed out in the link above? It's an
implementation of Tutorial D.

>>> What say we just stop right there and call Date's Relational Model
>>> what it is: a silly edifice built atop wrong premises.
>>
>> Using that logic, we should kick SQL to the curb too.
>>
>
> Um, no. You haven't actually used the logic. You're just saying you
> did, which is different. I've got to say you're reminding me of just about
> every Libertarian, Communist, or other kind of doctrinaire moonbat I've
> run across. Having a theory is nice, but when reality bumps up against
> it, that means the theory, not reality, is wrong.

What's with the insults? Cool off or something...

-M

On Fri, Jun 09, 2006 at 12:29:59PM -0400, A.M. wrote:
> >> Yes, and all SQL products worth their salt include some languages
> >> to provide iteration and other processing that SQL can't do or
> >> doesn't do well. Why must the rules be different for a truly
> >> relational db. (see http://dbappbuilder.sourceforge.net/Rel.html)
> > I may get interested if some actual software which implements
> > Date's Relational Model ever comes out. Or I may not, as I am
> > getting lots of useful work done using SQL and friends. We
> > empiricists are like that.
>
> You mean like the Java software I pointed out in the link above?
> It's an implementation of Tutorial D.

Do let me know when somebody uses it. :)

> >>> What say we just stop right there and call Date's Relational
> >>> Model what it is: a silly edifice built atop wrong premises.
> >>
> >> Using that logic, we should kick SQL to the curb too.
> >
> > Um, no. You haven't actually used the logic. You're just saying
> > you did, which is different. I've got to say you're reminding me
> > of just about every Libertarian, Communist, or other kind of
> > doctrinaire moonbat I've run across. Having a theory is nice, but
> > when reality bumps up against it, that means the theory, not
> > reality, is wrong.
>
> What's with the insults? Cool off or something...

I sent you private mail in response to your private email. You
decided to send it to a public list, moonbat. And you still haven't
actually used any logic. You've just alleged that you have.

Cheers,
D
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!

On Fri, Jun 09, 2006 at 12:01:07PM -0400, A.M. wrote:
> So you should normalize and add relations to represent the state
> adequately. NULL doesn't give you enough information anyway- does NULL in
> a birthday header mean "no birthday", "n/a" (a business doesn't have a
> birthday), "not born yet", etc... Using real data, you can represent any
> of these states.

What's makes you think I'm interested in storing the distinctions?
Either I know the birthday or I don't. If I want to work out why I
don't know it, yeah, it's worth storing the state. There are other ways
of finding that out (for example you can readily tell if you're looking
at a business and I know the system doesn't have people that aren't
born yet). But mostly I just need to track that it's not known.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Lots of great conversation here. Thanks to all for participating.

David, you wrote:
>Be aware that Pascal, along with Date and Darwen, are...how do I put
>this gently...cranks. They've been getting more strident and
>irrational as the decades go by.

I can't speak to that statement directly. Indirectly, however, the
sources Pascal cites in the body of the text (if I had to guess, and I
do because I'm too lazy to count them all) seem to be ~90% works by
C.J. Date. So it seems chiefly to be a distillation of Date's ideas,
e.g. potentially a cloistered treatise.

Is Pascal an academic who doesn't have real world knowledge gained from
having logically and physically designed, then brought to production,
monitored, refined, and tuned databases, and has answered to end users,
for a wide variety of customers and projects?

His book's subtitle is: "A Reference for the Thinking Practitioner."
If he is not himself a practitioner out there in the trenches, doing
the best with whatever technology presently exists, then I'm slightly
less interested in what he has to say, although I appreciate what he's
trying to do. But I don't want to dwell on the OUGHT in terms of future
technology; I want to dwell on the IS. Pleas that we ought to demand
better technology are all to the good -

Is there a good book out there about DB design written *by* real-world
practitioners, *for* real-world practitioners that addresses Pascal and
Date's concerns, yet focuses on the technology we have to live with
today? Something vendor-neutral if possible.

Dana

On Friday 09 June 2006 12:39, David Fetter wrote:
> On Fri, Jun 09, 2006 at 12:29:59PM -0400, A.M. wrote:
> > >> Yes, and all SQL products worth their salt include some languages
> > >> to provide iteration and other processing that SQL can't do or
> > >> doesn't do well. Why must the rules be different for a truly
> > >> relational db. (see http://dbappbuilder.sourceforge.net/Rel.html)
> > >
> > > I may get interested if some actual software which implements
> > > Date's Relational Model ever comes out. Or I may not, as I am
> > > getting lots of useful work done using SQL and friends. We
> > > empiricists are like that.
> >
> > You mean like the Java software I pointed out in the link above?
> > It's an implementation of Tutorial D.
>
> Do let me know when somebody uses it. :)
>

So "as an empiricist", you have derived that programming in PHP, hitting a
mysql backend, atop a Windows OS, is far better than that wonky
perl/postgresql/linux stuff you normally work with, right?

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

On Fri, Jun 09, 2006 at 03:18:54PM -0400, Robert Treat wrote:
> On Friday 09 June 2006 12:39, David Fetter wrote:
> > On Fri, Jun 09, 2006 at 12:29:59PM -0400, A.M. wrote:
> > > >> Yes, and all SQL products worth their salt include some languages
> > > >> to provide iteration and other processing that SQL can't do or
> > > >> doesn't do well. Why must the rules be different for a truly
> > > >> relational db. (see http://dbappbuilder.sourceforge.net/Rel.html)
> > > >
> > > > I may get interested if some actual software which implements
> > > > Date's Relational Model ever comes out. Or I may not, as I am
> > > > getting lots of useful work done using SQL and friends. We
> > > > empiricists are like that.
> > >
> > > You mean like the Java software I pointed out in the link above?
> > > It's an implementation of Tutorial D.
> >
> > Do let me know when somebody uses it. :)
>
> So "as an empiricist", you have derived that programming in PHP,
> hitting a mysql backend, atop a Windows OS, is far better than that
> wonky perl/postgresql/linux stuff you normally work with, right?

One important difference is that I can (and have) done head-to-head
comparisons of actual systems such as you describe, and thence can
draw some conclusions about what's appropriate for the organization.
Can't really say the same about Rel's attempt at Tutorial D.

Cheers,
D
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!

On Friday 2006-06-09 09:50, Martijn van Oosterhout wrote:
> On Fri, Jun 09, 2006 at 12:01:07PM -0400, A.M. wrote:
> > So you should normalize and add relations to represent the state
> > adequately. NULL doesn't give you enough information anyway- does NULL in
> > a birthday header mean "no birthday", "n/a" (a business doesn't have a
> > birthday), "not born yet", etc... Using real data, you can represent any
> > of these states.
>
> What's makes you think I'm interested in storing the distinctions?
> Either I know the birthday or I don't. If I want to work out why I
> don't know it, yeah, it's worth storing the state. There are other ways
> of finding that out (for example you can readily tell if you're looking
> at a business and I know the system doesn't have people that aren't
> born yet). But mostly I just need to track that it's not known.
>
> Have a nice day,

More importantly, it is usually not possible to list all the reasons
information might be absent. Nullity is a semantically empty residual
category. It can and does mean all unspecified (or accidental) forms of
information absence. You need null when you model data because real data is
not always amenable to an exhaustive partition.

On 6/8/06, Chris Browne <cbbrowne(at)acm(dot)org> wrote:
> smarlowe(at)g2switchworks(dot)com (Scott Marlowe) writes:
> > To me, the real argument is, "Is SQL so lacking that it should be
> > replaced". In what REAL measurable ways is SQL lacking so badly we
> > should toss it and start over? It's not perfect, that's for sure.
> > But what's the investment on starting over, and the possible
> > traction of a non-SQL database in a largely SQL driven market?
>
> The only visible alternative, at this point, is Tutorial D, and it
> doesn't particularly excite me...

Alphora. I don't think it meets the definition of market traction
(and it overlays on top of sql), but it's pretty neat.

Merlin

On 8 Jun 2006 05:21:07 -0700, dananrg(at)yahoo(dot)com <dananrg(at)yahoo(dot)com> wrote:
> I'm reading, and enjoying immensely, Fabial Pascal's book "Practical
> Issues in Database Management."
> Some questions:
>
> 1) Is PostgreSQL more faithful to relational theory? If so, do you find
> yourself using the additional functionality afforded by this? e.g. does
> it really matter to what you do in your daily work.

Modern PostgreSQL is a SQL dbms. What makes pg great is that it is so
expandable...it really leverages the power of SQL.

> 2) If PostgreSQL is *not* significantly more faithful to relational
> theory than commercial RDBMSs, is it at least on the road to becoming
> more faithful?

I would say less faithful. For example 'quel' was dropped in favor of
sql quite some time ago.

> 3) If PostgreSQL is not on the road to becoming more faithful to
> relational theory and purity, why not? Is it due to the fact that
> various SQL standards are themselves not fully faithful to relational
> theory, and most RDBMSs have as a primary design goal to be faithful to
> standards (which Pascal implies *are* relationally deficient)?

> 4) Which database, commercial or non-commercial, is most faithful to
> relational theory itself, or is headed in that direction the quickest?

right now the commercial market is firmly rooted in sql. open-source
is pretty much the same, although there is less of an emphasis on
tools and more of an emphasis on versatlity through programming
interfaces. Most people don't understand the difference between SQL
and relational and the advantages and disadvantages of each. This is
a failure of academia and is one of the central themes of Fabian's
books.

merlin

On Sun, Jun 11, 2006 at 11:03:57AM -0400, Merlin Moncure wrote:
> On 6/8/06, Chris Browne <cbbrowne(at)acm(dot)org> wrote:
> >smarlowe(at)g2switchworks(dot)com (Scott Marlowe) writes:
> >> To me, the real argument is, "Is SQL so lacking that it should be
> >> replaced". In what REAL measurable ways is SQL lacking so badly
> >> we should toss it and start over? It's not perfect, that's for
> >> sure. But what's the investment on starting over, and the
> >> possible traction of a non-SQL database in a largely SQL driven
> >> market?
> >
> >The only visible alternative, at this point, is Tutorial D, and it
> >doesn't particularly excite me...
>
> Alphora. I don't think it meets the definition of market traction
> (and it overlays on top of sql), but it's pretty neat.

It may be neat, but it's not Date's Relational Model because it allows
NULLs.

Cheers,
D
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!

On Fri, Jun 09, 2006 at 09:54:56AM -0700, dananrg(at)yahoo(dot)com wrote:
> Lots of great conversation here. Thanks to all for participating.
>
> David, you wrote:
> >Be aware that Pascal, along with Date and Darwen, are...how do I put
> >this gently...cranks. They've been getting more strident and
> >irrational as the decades go by.
>
> I can't speak to that statement directly. Indirectly, however, the
> sources Pascal cites in the body of the text (if I had to guess, and I
> do because I'm too lazy to count them all) seem to be ~90% works by
> C.J. Date. So it seems chiefly to be a distillation of Date's ideas,
> e.g. potentially a cloistered treatise.

Good eye :)

> Is Pascal an academic who doesn't have real world knowledge gained
> from having logically and physically designed, then brought to
> production, monitored, refined, and tuned databases, and has
> answered to end users, for a wide variety of customers and projects?

Yes.

> Is there a good book out there about DB design written *by*
> real-world practitioners, *for* real-world practitioners that
> addresses Pascal and Date's concerns, yet focuses on the technology
> we have to live with today? Something vendor-neutral if possible.

Any random book by Joe Celko is pretty good on this, especially the
recent ones. Celko was on the committee that did the SQL92
standard--the last one in EBNF, alas--and has been out in the trenches
for decades. He knows about mathematics and frequently cites math
papers :)

Cheers,
D
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!

David Fetter wrote:

>On Fri, Jun 09, 2006 at 03:55:04PM +0200, Aaron Bingham wrote:
>
>
>>dananrg(at)yahoo(dot)com wrote:
>>
>>
>>>I'm reading, and enjoying immensely, Fabial Pascal's book "Practical Issues in Database Management."
>>>
>>>
>
>If you're interested in the theory of RDBMSs, you can start with the papers on Leonid Libkin's page and the books and papers they reference. :)
>
>http://www.cs.toronto.edu/~libkin/publ.html
>
>
Thanks for the pointer, I will look into it later.

>>I also found this book very useful when I first started doing serious database work. For a more thorough treatment of many of these issues, see An Introduction to Database Systems by Chris Date. The latter book is so full of detail that it is sometimes hard to follow,
>>
>>
>It certainly has an elaborate and well-thought-out system of ideas. As an empiricist, I find it crucially important that despite decades of whining about it, no one has come up with an actual *computer* system which implements this /gedankenexperiment/.
>
>
Whether or not there will ever by an implementation of their definition
of the RM, their ideas provide useful guidance in designing real-world
databases today. The lack of an implementation of this RM is
unfortunate, but there are many possible explanations for this lack
other than faults in the theory itself. The theory _could_ be flawed,
of course, but I have not seen sufficient evidence to support that
conclusion.

>>but it's worth the effort.
>>
>>
>Why? There are much more entertaining cranks out there if crank study is your thing.
>
>
I'm not at all into crank study. I'm interested in basing design
decisions on a solid foundation, grounded in logic.

"An Introduction to Database Systems" is not a thorough exposition of
relational theory, as you seem to imply, but an _introduction_ to
database fundamentals and the application thereof to SQL-DBMSs. Were
you thinking of "The Third Manifesto" by Date and Darwen?

>>>Though I've just gotten started with the book, he seems to be saying that modern RDBMSs aren't as faithful to relational theory as they ought to be, and that this has many *practical* consequences, e.g. lack of functionality.
>>>
>>>Given that PostgreSQL is open source, it seems a more likely candidate for addressing Pascal's concerns. At least the potential is there.
>>>
>>>
>>Although some DBMSs have invented new ways to break the relational model, the fundamental problems are in SQL.
>>
>>
>
>Um, no. As I'll demonstrate below, it's the model that's broken and SQL that got it right.
>
>
I'm unconvinced. See below.

>>No DBMS based on SQL is going to be able to support RM correctly.
>>
>>
>
>Aha! I spy, with my little eye, a fanboy.
>
I may have come across as over-enthusiastic late on Friday afternoon; I
was attempting to counter over-enthusiastic claims in the other
direction. I find your choice of words insulting, but I won't hold it
against you.

I appreciate Date and Pascal's work because it is well reasoned and
grounded in mathematics and logic. I do not consider their work final
in any way. They themselves are quick to acknowledge gaps in their
understanding. I have not seen a better conceptual framework for
thinking about databases. If I were presented with one, I would not
cling to Date's or Pascal's views.

>You have to be a bit of a theory wonk to call Date's stuff "RM." You seem to be implying here
>that Date's "RM" is somehow more desirable than what SQL actually provides. To be more desirable, I don't think it's unreasonable to say that it should be more powerful in some essential way.
>
>
We were discussing Fabian Pascal's book. His book is based on his
definition of "RM", which is largely similar to (though not identical
with) Date's. My above claim applies to "RM" as defined by Date or
Pascal, not some other "RM". If you could point me to alternate
definitions of "RM" which are not in conflict with SQL, I would be
curious to see them.; a better framework for thinking about SQL
databases would be invaluable.

>In SQL, you can do this (this example condensed from Libkin's
>"Expressive Power of SQL" on the page above):
>
>SELECT
> (SELECT count(*) FROM table_1) <
> (SELECT count(*) FROM table_2)
> AS "Can't compare cardinalities in first order logic";
>
>Note the name of the output column. It's important and true, as you
>can verify if you care to do your homework on foundations of
>mathematics. Relational algebra is a subset of first-order logic
><http://en.wikipedia.org/wiki/Relational_algebra>, and as a direct
>consequence, you can't do this simple but interesting thing with it.
>
>
I must be missing something important. What aspect of the above query
is supposedly impossible in relational algebra and/or relational calculus?

>What say we just stop right there and call Date's Relational Model
>what it is: a silly edifice built atop wrong premises.
>
>
I'm unwilling to accept that claim without better evidence.

Regards,

--
--------------------------------------------------------------------
Aaron Bingham
Senior Software Engineer
Cenix BioScience GmbH
--------------------------------------------------------------------

Aaron Bingham wrote:

> David Fetter wrote:
>
>> In SQL, you can do this (this example condensed from Libkin's
>> "Expressive Power of SQL" on the page above):
>>
>> SELECT
>> (SELECT count(*) FROM table_1) <
>> (SELECT count(*) FROM table_2)
>> AS "Can't compare cardinalities in first order logic";
>>
>> Note the name of the output column. It's important and true, as you
>> can verify if you care to do your homework on foundations of
>> mathematics. Relational algebra is a subset of first-order logic
>> <http://en.wikipedia.org/wiki/Relational_algebra>, and as a direct
>> consequence, you can't do this simple but interesting thing with it.
>>
>>
> I must be missing something important. What aspect of the above query
> is supposedly impossible in relational algebra and/or relational
> calculus?

Having looked at this again, I now see that your statement above is
strictly correct, but misleading. Relational algebra consists of a
limited number of operators on relations. As such, relational algebra
says nothing about aggregate functions such as COUNT, or how to build a
relation from scaler values. Relational algebra is, however, only part
of the relational model as defined by Date, and Tutorial D includes all
the previsions we need to re-write the above query. The above query
could be expressed in Tutorial D more-or-less as follows (I'm not sure
if arbitrary strings are allowed as column names in Tutorial D, but
that's beside the point):

RELATION { TUPLE { "Can't compare cardinalities in first order logic"
(COUNT(table_1) < COUNT(table_2)) } }

Placing the result of the comparison in a relation seems unnecessary,
but I have done so for equivalence to your example. Or did I miss the
point?

Regards,

--
--------------------------------------------------------------------
Aaron Bingham
Senior Software Engineer
Cenix BioScience GmbH
--------------------------------------------------------------------

What is real data for birthday -> no birthday, n/a in date datatype
representation ?
For mysql is 0000-00-00 and invalid date.
For me is simple null, you have no data to put in the field.

For me null is good in some situation, and bad in ohters.
Just you have to think if you permit or not this value.

Alejandro Michelin Salomon
Porto Alegre
Brasil
-->-----Mensagem original-----
-->De: pgsql-general-owner(at)postgresql(dot)org
-->[mailto:pgsql-general-owner(at)postgresql(dot)org] Em nome de A.M.
-->Enviada em: sexta-feira, 9 de junho de 2006 13:01
-->Para: Postgres general mailing list
-->Assunto: Re: [GENERAL] Fabian Pascal and RDBMS deficiencies in fully
-->
-->
-->On Fri, June 9, 2006 11:45 am, David Fetter wrote:
-->> On Fri, Jun 09, 2006 at 05:20:46PM +0200, Martijn van Oosterhout
-->> wrote:
-->>
-->>> On Fri, Jun 09, 2006 at 07:09:12AM -0400, Agent M wrote:
-->>>
-->>>> Well, the Date argument against NULLs (and he never
-->endorsed them,
-->>>> or so he claims) is that they are not data- they represent the
-->>>> absence of data- so why put non-data in a _data_base.
-->>>
-->>> At this point you could start a whole philosophical
-->discussion about
-->>> whether knowing you don't know something is a fact worth storing.
-->>
-->> And to me, the answer is an unqualified "yes." A state of
-->ignorance
-->> is an important piece of information by itself.
-->>
-->> For example, that I don't know someone's birthdate is
-->important. When
-->> I'm trying to figure out when to send a birthday card,
-->knowing that I
-->> don't know this piece of information means that I take a different
-->> action "decide whether to try to find out what the
-->birthdate is." from
-->> the action I would take if I didn't know that I don't know the
-->> birthdate, which is "rummage through all my records trying
-->to find the
-->> birthdate."
-->
-->So you should normalize and add relations to represent the
-->state adequately. NULL doesn't give you enough information
-->anyway- does NULL in a birthday header mean "no birthday",
-->"n/a" (a business doesn't have a birthday), "not born yet",
-->etc... Using real data, you can represent any of these states.
-->
-->
-->---------------------------(end of
-->broadcast)---------------------------
-->TIP 4: Have you searched our list archives?
-->
--> http://archives.postgresql.org
-->
-->
-->--
-->No virus found in this incoming message.
-->Checked by AVG Free Edition.
-->Version: 7.1.394 / Virus Database: 268.8.3/359 - Release
-->Date: 8/6/2006
-->
-->
-->--
-->No virus found in this incoming message.
-->Checked by AVG Free Edition.
-->Version: 7.1.394 / Virus Database: 268.8.3/359 - Release
-->Date: 8/6/2006
-->
-->

--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.8.3/360 - Release Date: 9/6/2006

--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.8.3/360 - Release Date: 9/6/2006

* Roy Souther:

> In what way could a database like PostgreSQL not be "faithful to
> relational theory"? Does he give any explanation as to what that means?

My guess: In SQL (and in PostgreSQL as a result), relations aren't
sets, aren't first-class, and the underlying logic is not Boolean.

> What say we just stop right there and call Date's Relational Model
> what it is: a silly edifice built atop wrong premises.

SQL was a quick and dirty hack (Systems R and R* needed some way to
interface with data) with multiple deficiencies recognized and documented
right within the very first paper by its own authors. To hold it up as any
kind of paradigm is really misinformed.

--
Scott Ribe
scott_ribe(at)killerbytes(dot)com
http://www.killerbytes.com/
(303) 722-0567 voice

> Now, there's another thing that makes it amazingly hard to displace:
> imagining what would be better *enough* to justify the many millions of
> people-years and even more billions of dollars needed to move away from
> it. Despite Date's many whines over the decades, his still-vaporware
> Relational Model doesn't even vaguely approximate that
> criterion.

1) Please understand Date is not a programmer, he is a lecturer,
therefore, he is not undertaking nor does he wish to undertake any
implementation. Ideally, he wouldn't endorse any particular implementation
(but he does- that's his option).

2) Re: "still-vaporware Relational Model"- the relational model is a
mathematical model for data representation. Your comment makes as much
sense as claiming that "Newtonian physics" is vaporware.

3) From your comments, it is clear that you wish to only consider existing
software as proof of usefulness and you are not interested in considering
alternative ideas. This is precisely the difference between a researcher
and a rote programmer. I would rather be someone in between.

Regardless of what you think of the relational model, I would urge you to
be more open-minded, even about "vaporware". Much of the world's most
interesting software has not yet been written.

I'm done with this thread. Good luck.

-M

On 6/13/06, David Fetter <david(at)fetter(dot)org> wrote:
> > SQL was a quick and dirty hack (Systems R and R* needed some way to
> > interface with data) with multiple deficiencies recognized and
> > documented right within the very first paper by its own authors.
>
> Perfection isn't a human attribute. There isn't a whole lot of
> convincing evidence that it's a divine attribute. Did you have a
> point to make?

so your logic is that since perfection is not a human attribute it
follows that it is not worthwhile finding better alternatives to
existing methods of data management?

> SQL had something that relational algebra/relational calculus did not
> have, which is that somebody without a math degree can stare at it a
> short while and *do* something with it right away. That it also has
> other properties that are extremely useful and powerful (the ability
> to specify states of ignorance using NULL, do arithmetic, use
> aggregates, etc.) is what has made it such a smashing success.

SQL is a smashing success because at the time it was invention it was
better than it's alternatives. It also received heavy backing from
major software shops of the time. It's relitive merit to relational
applications were not a factor here. Compared to ISAM, for example,
SQL is an improvement for most applications. Also, I think the
relational model is easier to understand precisely because it is so
grounded in mathematics...the terse mathematical notation commonly
used may be difficult for some to follow but it could be 'dumbed down'
as it were for easier consumption.

> Now, there's another thing that makes it amazingly hard to displace:
> imagining what would be better *enough* to justify the many millions
> of people-years and even more billions of dollars needed to move away
> from it. Despite Date's many whines over the decades, his
> still-vaporware Relational Model doesn't even vaguely approximate that
> criterion.

So you are justifying investment in 'A' as not to consider application
or consideration of 'B'. While this may be an agument not to
drop everything and move to 'B', 'B' should still be considered for
long term advantages it might provide.

Anyways, I think Date and Pascal are pragmatic about this particular
point. I think what they are concerned about it the combination of
social factors which cause illogical arguments such as the above to
get so much traction.

Merlin

On Tue, Jun 13, 2006 at 12:51:57PM -0400, Merlin Moncure wrote:
> On 6/13/06, David Fetter <david(at)fetter(dot)org> wrote:
> >> SQL was a quick and dirty hack (Systems R and R* needed some way
> >> to interface with data) with multiple deficiencies recognized and
> >> documented right within the very first paper by its own authors.
> >
> >Perfection isn't a human attribute. There isn't a whole lot of
> >convincing evidence that it's a divine attribute. Did you have a
> >point to make?
>
> so your logic is that since perfection is not a human attribute it
> follows that it is not worthwhile finding better alternatives to
> existing methods of data management?

If there are better alternatives, they will need to show some
real-world attributes, not mathematically-inspired fantasies, because
they're not "better" unless actual people in real-world situations can
use them.

> >SQL had something that relational algebra/relational calculus did
> >not have, which is that somebody without a math degree can stare at
> >it a short while and *do* something with it right away. That it
> >also has other properties that are extremely useful and powerful
> >(the ability to specify states of ignorance using NULL, do
> >arithmetic, use aggregates, etc.) is what has made it such a
> >smashing success.
>
> SQL is a smashing success because at the time it was invention it
> was better than it's alternatives.

And it still is. If you have evidence to the contrary that you can
point to in real-world software that's actually deployed, please show
it.

> It also received heavy backing from major software shops of the
> time. It's relitive merit to relational applications were not a
> factor here. Compared to ISAM, for example, SQL is an improvement
> for most applications. Also, I think the relational model is easier
> to understand precisely because it is so grounded in
> mathematics...

Date's Relational Model is not the only one out there. Codd wrote
about one which was different, as have Stonebraker, Libkin, etc. That
Date and his dour crew have spent more time yelling louder is not by
itself (or any other way) a recommendation for the model they endorse.
It's *certainly* not a logical argument for that model.

> the terse mathematical notation commonly used may be difficult for
> some to follow but it could be 'dumbed down' as it were for easier
> consumption.

Again, if you have a piece of software you can point to that does this
thing, please do so. What might be done is an interesting question,
but what hasn't been done despite hugely many opportunities is also a
significant piece of information.

> >Now, there's another thing that makes it amazingly hard to
> >displace: imagining what would be better *enough* to justify the
> >many millions of people-years and even more billions of dollars
> >needed to move away from it. Despite Date's many whines over the
> >decades, his still-vaporware Relational Model doesn't even vaguely
> >approximate that criterion.
>
> So you are justifying investment in 'A' as not to consider
> application or consideration of 'B'. While this may be an agument
> not to drop everything and move to 'B', 'B' should still be
> considered for long term advantages it might provide.

Please feel free.

I've read Date, Darwen and Pascal's stuff over time, and it looks to
me like the increasingly strident whines of other frustrated
ideologues whose theories don't match reality. I take it as
significant that nobody's managed to implement this extreme purist
model in actual software, as the computing world--academic, commercial
and FLOSS--has had decades to do it.

> Anyways, I think Date and Pascal are pragmatic about this particular
> point.

Could you quote me something somewhere that shows evidence of
pragmatism on either of their parts? Preferably something from the
current decade, although something from earlier would be OK, too.

> I think what they are concerned about it the combination of social
> factors which cause illogical arguments such as the above to get so
> much traction.

"Social factors," as you call them, are some of the principle
differences between an abstruse theory that nobody cares about and a
useful tool that people actually get work done with.

Cheers,
D
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!

> 2) Re: "still-vaporware Relational Model"- the relational model is a
> mathematical model for data representation. Your comment makes as much
> sense as claiming that "Newtonian physics" is vaporware.
>

If we're discussing the "luminiferous aether", then, yes, vaporware
seems /somewhat/ appropriate.

Thanks - i'll be here all week.

b

David Fetter wrote:
> On Tue, Jun 13, 2006 at 12:51:57PM -0400, Merlin Moncure wrote:
>> On 6/13/06, David Fetter <david(at)fetter(dot)org> wrote:
>>>> SQL was a quick and dirty hack...
>
> If there are better alternatives, they will need to show some
> real-world attributes, not mathematically-inspired fantasies, because
> they're not "better" unless actual people in real-world situations can
> use them.... that you can point to in real-world software that's
> actually deployed, please show it.

KDB [1] from KX Systems has both terse mathematical notations
to access data directly as well as an awkwardly(?) extended
SQL syntax to handle queries not easily handled by SQL alone.
Time-series related queries are one such case [2,3].
Examples can be seen various places on the web [3].

It has plenty of real-world usage on large databases at places
including Deutsche Bank, Fidelity Investments, JP Morgan,
Merrill Lynch, the US Army, etc.

>> the terse mathematical notation commonly used...
>
> Again, if you have a piece of software you can point to that does this
> thing, please do so.

I seriously doubt it follows Date or Pascal religiously, but
it does have a convenient and very terse mathematical notation
so might count as a real-world piece of software that you were
asking for.

[1] http://www.kx.com/news/press-releases/arthur-interview.php
[2] http://www.kx.com/news/in-the-news/sql-timeseries.php
[3] http://www.intelligententerprise.com/010327/celko_online.jhtml;jsessionid=NDIHEWXGL4TNKQSNDBNSKHSCJUMEKJVN
[4] http://www.cs.nyu.edu/courses/fall02/G22.3033-007/kintro.html

Ron Mayer <rm_pg(at)cheapcomplexdevices(dot)com> wrote:
> David Fetter wrote:
>>> the terse mathematical notation commonly used...
>> Again, if you have a piece of software you can point to that does
>> this
>> thing, please do so.
>
> I seriously doubt it follows Date or Pascal religiously, but
> it does have a convenient and very terse mathematical notation
> so might count as a real-world piece of software that you were
> asking for.
>
> [1] http://www.kx.com/news/press-releases/arthur-interview.php
> [2] http://www.kx.com/news/in-the-news/sql-timeseries.php
> [3] http://www.intelligententerprise.com/010327/celko_online.jhtml;jsessionid=NDIHEWXGL4TNKQSNDBNSKHSCJUMEKJVN
> [4] http://www.cs.nyu.edu/courses/fall02/G22.3033-007/kintro.html

The sample problem in [3] is one that shows pretty nicely a
significant SQL weakness; it's very painful to build SQL to do complex
things surrounding cumulative statistics.

Unfortunately, across that set of URLs, I don't actually see a single
presentation of their terse notation for handling time series, so it's
not quite an answer, either.

I should probably try to take another look at Tutorial D to see if it
actually *does* provide something that would help make aggregates
"play better." I'm not certain it tries nearly hard enough...

I'm not sure what the Right Answer would be. I'm not certain there
necessarily is one, short of either programming using vector
statements (ala K) or lambda expressions (Lisp), neither of which are
likely to be considered "generally acceptable." I'd rather see some
attempts at it than the "head in sand" of present...
--
output = ("cbbrowne" "@" "gmail.com")
http://cbbrowne.com/info/finances.html
If the automobile had improved as much as the computer in the last 25
years, it would get 1,000,000 miles to the gallon, cost $1, have a top
speed of 30,000 mph, and explode at least once a year, killing all
passengers.

On Tuesday 2006-06-13 09:26, David Fetter wrote:
> On Tue, Jun 13, 2006 at 09:18:17AM -0600, Scott Ribe wrote:
> > > What say we just stop right there and call Date's Relational Model
> > > what it is: a silly edifice built atop wrong premises.
> >
> > SQL was a quick and dirty hack (Systems R and R* needed some way to
> > interface with data) with multiple deficiencies recognized and
> > documented right within the very first paper by its own authors.
>
> Perfection isn't a human attribute. There isn't a whole lot of
> convincing evidence that it's a divine attribute. Did you have a
> point to make?
>
> > To hold it up as any kind of paradigm is really misinformed.
>
> SQL had something that relational algebra/relational calculus did not
> have, which is that somebody without a math degree can stare at it a
> short while and *do* something with it right away. That it also has
> other properties that are extremely useful and powerful (the ability
> to specify states of ignorance using NULL, do arithmetic, use
> aggregates, etc.) is what has made it such a smashing success.
>
> Now, there's another thing that makes it amazingly hard to displace:
> imagining what would be better *enough* to justify the many millions
> of people-years and even more billions of dollars needed to move away
> from it. Despite Date's many whines over the decades, his
> still-vaporware Relational Model doesn't even vaguely approximate that
> criterion.
>
> Cheers,
> D

COBOL and VisualBasic are better than Haskell by the same argument.

(SQL always reminds me a lot of COBOL.)

On Tue, Jun 13, 2006 at 05:23:56PM -0400, Christopher Browne wrote:
> > [3] http://www.intelligententerprise.com/010327/celko_online.jhtml;jsessionid=NDIHEWXGL4TNKQSNDBNSKHSCJUMEKJVN
>
> The sample problem in [3] is one that shows pretty nicely a
> significant SQL weakness; it's very painful to build SQL to do complex
> things surrounding cumulative statistics.

I havn't managed to wrap my brain around them yet, but this seems like
something that SQL WINDOW functions would be able to do. For each row
define the window frame to be all the preceding rows, do a SUM() and
divide that over the total. Or perhaps the PERCENT_RANK() function does
this already, not sure.

Mind you, postgres doesn't support them yet, but it's interesting that
it may be possible at all...

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

kleptog(at)svana(dot)org (Martijn van Oosterhout) writes:

> On Tue, Jun 13, 2006 at 05:23:56PM -0400, Christopher Browne wrote:
>> > [3] http://www.intelligententerprise.com/010327/celko_online.jhtml;jsessionid=NDIHEWXGL4TNKQSNDBNSKHSCJUMEKJVN
>>
>> The sample problem in [3] is one that shows pretty nicely a
>> significant SQL weakness; it's very painful to build SQL to do complex
>> things surrounding cumulative statistics.
>
> I havn't managed to wrap my brain around them yet, but this seems like
> something that SQL WINDOW functions would be able to do. For each row
> define the window frame to be all the preceding rows, do a SUM() and
> divide that over the total. Or perhaps the PERCENT_RANK() function does
> this already, not sure.
>
> Mind you, postgres doesn't support them yet, but it's interesting that
> it may be possible at all...

Yes, you are exactly right; I have seen a couple references to OVER
and PARTITION BY which look as though they are the relevant SQL
additions...

http://blogs.ittoolbox.com/database/technology/archives/olap-sql-part-5-windowing-aggregates-8373
http://www.sqljunkies.com/HowTo/4E65FA2D-F1FE-4C29-BF4F-543AB384AFBB.scuk
http://sqljunkies.com/Article/4E65FA2D-F1FE-4C29-BF4F-543AB384AFBB.scuk
http://www.experts-exchange.com/Databases/Oracle/Q_21793507.html

I'm not sure the degree to which these are standardized, but they are
available in some form or another in late-breaking versions of Oracle,
DB2, and Microsoft SQL Server.

I'm not quite sure how to frame this so as to produce something that
should go on the TODO list, but it looks like there's a possible TODO
here...
--
let name="cbbrowne" and tld="cbbrowne.com" in name ^ "@" ^ tld;;
http://cbbrowne.com/info/sap.html
"The newsreader abuse likely stems from more fundamental, than merely
just the UI, design disagreements. Requests from Unix programmers to
replicate Free Agent rightfully so should trigger the throwing of
sharp heavy objects at the requesting party."
-- jedi(at)dementia(dot)mishnet (jedi)

On Wed, 2006-06-14 at 18:34 -0400, Chris Browne wrote:
> kleptog(at)svana(dot)org (Martijn van Oosterhout) writes:
>
> > On Tue, Jun 13, 2006 at 05:23:56PM -0400, Christopher Browne wrote:
> >> > [3] http://www.intelligententerprise.com/010327/celko_online.jhtml;jsessionid=NDIHEWXGL4TNKQSNDBNSKHSCJUMEKJVN
> >>
> >> The sample problem in [3] is one that shows pretty nicely a
> >> significant SQL weakness; it's very painful to build SQL to do complex
> >> things surrounding cumulative statistics.
> >
> > I havn't managed to wrap my brain around them yet, but this seems like
> > something that SQL WINDOW functions would be able to do. For each row
> > define the window frame to be all the preceding rows, do a SUM() and
> > divide that over the total. Or perhaps the PERCENT_RANK() function does
> > this already, not sure.
> >
> > Mind you, postgres doesn't support them yet, but it's interesting that
> > it may be possible at all...
>
> Yes, you are exactly right; I have seen a couple references to OVER
> and PARTITION BY which look as though they are the relevant SQL
> additions...
>
> http://blogs.ittoolbox.com/database/technology/archives/olap-sql-part-5-windowing-aggregates-8373
> http://www.sqljunkies.com/HowTo/4E65FA2D-F1FE-4C29-BF4F-543AB384AFBB.scuk
> http://sqljunkies.com/Article/4E65FA2D-F1FE-4C29-BF4F-543AB384AFBB.scuk
> http://www.experts-exchange.com/Databases/Oracle/Q_21793507.html
>
> I'm not sure the degree to which these are standardized, but they are
> available in some form or another in late-breaking versions of Oracle,
> DB2, and Microsoft SQL Server.
>
> I'm not quite sure how to frame this so as to produce something that
> should go on the TODO list, but it looks like there's a possible TODO
> here...

Yes, SQL Window Functions should be explicitly part of the TODO. They
are already described in detail as part of SQL:2003.

Window functions allow you to work with ordered result sets, moving
averages etc.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

Trent Shipley wrote:
> On Tuesday 2006-06-13 09:26, David Fetter wrote:
>> On Tue, Jun 13, 2006 at 09:18:17AM -0600, Scott Ribe wrote:
>>> To hold it up as any kind of paradigm is really misinformed.
>> SQL had something that relational algebra/relational calculus did not
>> have, which is that somebody without a math degree can stare at it a
>> short while and *do* something with it right away. That it also has
>> other properties that are extremely useful and powerful (the ability
>> to specify states of ignorance using NULL, do arithmetic, use
>> aggregates, etc.) is what has made it such a smashing success.
>>
>> Now, there's another thing that makes it amazingly hard to displace:
>> imagining what would be better *enough* to justify the many millions
>> of people-years and even more billions of dollars needed to move away
>> from it. Despite Date's many whines over the decades, his
>> still-vaporware Relational Model doesn't even vaguely approximate that
>> criterion.

> COBOL and VisualBasic are better than Haskell by the same argument.
Well, VisualBasic really sucks IMHO, but if I had to choose between
taking over a 100.000-line VB Project, or a 10.000 line Haskhell
Project, I'm not sure if I wouldn't choose the VB one.

Haskhell has very nice properties, but there are haskhell onelines
which I can stare at for hours, and am still not exactly sure what they
do ;-)

I normally prefer languages with a terse syntax, but haskhell is
sometimes too much even for me ;-)

greetings, Florian Pflug