Thread: Fabian Pascal and RDBMS deficiencies in fully implementing the relational model
Fabian Pascal and RDBMS deficiencies in fully implementing the relational model
From
dananrg@yahoo.com
Date:
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@yahoo.com wrote:
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@yahoo.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
|
Attachment
On Thu, 2006-06-08 at 07:21, dananrg@yahoo.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?
Re: Fabian Pascal and RDBMS deficiencies in fully implementing the relational model
From
Christopher Browne
Date:
In the last exciting episode, dananrg@yahoo.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@g2switchworks.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@g2switchworks.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.
Re: Fabian Pascal and RDBMS deficiencies in fully implementing the relational model
From
David Fetter
Date:
On Thu, Jun 08, 2006 at 05:21:07AM -0700, dananrg@yahoo.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@fetter.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)
Re: Fabian Pascal and RDBMS deficiencies in fully implementing the relational model
From
David Fetter
Date:
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@fetter.org> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote!
Re: Fabian Pascal and RDBMS deficiencies in fully implementing the relational model
From
Agent M
Date:
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@yahoo.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@themactionfaction.com ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬
Re: Fabian Pascal and RDBMS deficiencies in fully implementing the relational model
From
Trent Shipley
Date:
On Thursday 2006-06-08 15:14, David Fetter wrote: > On Thu, Jun 08, 2006 at 05:21:07AM -0700, dananrg@yahoo.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?
Re: Fabian Pascal and RDBMS deficiencies in fully implementing the relational model
From
David Fetter
Date:
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@yahoo.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@fetter.org> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote!
Re: Fabian Pascal and RDBMS deficiencies in fully implementing the relational model
From
Christopher Browne
Date:
A long time ago, in a galaxy far, far away, tshipley@deru.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@yahoo.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
Re: Fabian Pascal and RDBMS deficiencies in fully implementing the relational model
From
Agent M
Date:
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@deru.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@yahoo.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@postgresql.org so that > your > message can get through to the mailing list cleanly > ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ AgentM agentm@themactionfaction.com ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬
Re: Fabian Pascal and RDBMS deficiencies in fully implementing the relational model
From
Agent M
Date:
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@yahoo.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@themactionfaction.com ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬
# nis@superlativ.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
Roman Neuhauser wrote: > # nis@superlativ.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
dananrg@yahoo.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 --------------------------------------------------------------------
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@yahoo.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 > >
Re: Fabian Pascal and RDBMS deficiencies in fully implementing the relational model
From
Martijn van Oosterhout
Date:
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@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
On Fri, Jun 09, 2006 at 03:55:04PM +0200, Aaron Bingham wrote: > dananrg@yahoo.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@fetter.org> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote!
Re: Fabian Pascal and RDBMS deficiencies in fully implementing the relational model
From
David Fetter
Date:
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@fetter.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@fetter.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@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
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@fetter.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.
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 6/8/06, Chris Browne <cbbrowne@acm.org> wrote: > smarlowe@g2switchworks.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
Re: Fabian Pascal and RDBMS deficiencies in fully implementing the relational model
From
"Merlin Moncure"
Date:
On 8 Jun 2006 05:21:07 -0700, dananrg@yahoo.com <dananrg@yahoo.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@acm.org> wrote: > >smarlowe@g2switchworks.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@fetter.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@yahoo.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@fetter.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@yahoo.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 papersthey 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 ofmany of these issues, see An Introduction to Database Systems by Chris Date. The latter book is so full of detail thatit 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 thatdespite 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 relationaltheory 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 thepotential 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 unreasonableto 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 --------------------------------------------------------------------
RES: Fabian Pascal and RDBMS deficiencies in fully
From
"Alejandro Michelin Salomon \( Adinet \)"
Date:
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@postgresql.org -->[mailto:pgsql-general-owner@postgresql.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@killerbytes.com http://www.killerbytes.com/ (303) 722-0567 voice
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 -- David Fetter <david@fetter.org> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote!
> 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@fetter.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@fetter.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@fetter.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@fetter.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@cheapcomplexdevices.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@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
kleptog@svana.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@dementia.mishnet (jedi)
On Wed, 2006-06-14 at 18:34 -0400, Chris Browne wrote: > kleptog@svana.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