views:

148

answers:

7

Aheo asks if it is ok to have a table with just one column. How about one with no columns, or, given that this seems difficult to do in most modern "relational" DBMSes, a relation with no attributes?

+3  A: 

A table with a single column is a set -- as long as you don't care about ordering the values, or associating any other info with them, it seems fine. You can check for membership in it, and basically that's all you can do. (If you don't have a UNIQUE constraint on the single column I guess you could also count number of occurrences... a multiset).

But what in blazes would a table with no columns (or a relation with no attributes) mean -- or, how would it be any good?!

Alex Martelli
What it means and whether or not it would be any good are two quite different things...
Curt Sampson
And that's why I ask both questions (joined by an OR, too;-) -- if either question has a good answer, I want to hear it!-)
Alex Martelli
Ah, now I get to reveal the secret. :-)It means that the relation (or "table") either contains the one valid tuple (or "row") or it does not. It's the boolean type of relations.
Curt Sampson
A: 

Hm. So the lack of "real-world examples" got to me, and I tried my best. Perhaps surprisingly, I got half way there!

cjs=> CREATE TABLE D ();
CREATE TABLE
cjs=> SELECT COUNT (*) FROM D;
 count 
-------
     0
(1 row)

cjs=> INSERT INTO D () VALUES ();
ERROR:  syntax error at or near ")"
LINE 1: INSERT INTO D () VALUES ();
Curt Sampson
+2  A: 

There are exactly two relations with no attributes, one with an empty tuple, and one without. In The Third Manifesto, Date and Darwen (somewhat) humorously name them TABLE_DEE and TABLE_DUM (respectively).

They are useful to the extent that they are the identity of a variety of relational operators, playing a roles equivalent to 1 and 0 in ordinary algebra.

Doug McClean
A: 

A table with a single column would make sense as a simple lookup. Let's say you have a list of strings you want to filter against for user inputed text. That table would store the words you would want to filter out.

Babak Naffas
But that's a table with one more column than the one with no columns, which is the on of interest here. Still, you've discovered the idea of a predicate in a relational world, which is good.
Curt Sampson
+1  A: 

DEE and cartesian product form a monoid. In practice, if you have Date's relational summarize operator, you'd use DEE as your grouping relation to obtain grand-totals. There are many other examples where DEE is practically useful, e.g. in a functional setting with a binary join operator you'd get n-ary join = foldr join dee

Marten
+1  A: 

"There are exactly two relations with no attributes, one with an empty tuple, and one without. In The Third Manifesto, Date and Darwen (somewhat) humorously name them TABLE_DEE and TABLE_DUM (respectively).

They are useful to the extent that they are the identity of a variety of relational operators, playing a roles equivalent to 1 and 0 in ordinary algebra."

And of course they also play the role of "TRUE" and "FALSE" in boolean algebra. Meaning that they are useful when propositions such as "The shop is open" and "The alarm is set" are to be represented in a database.

A consequence of this is that they can also be usefully employed in any expression of the relational algebra for their properties of "acting as an IF/ELSE" : joining to TABLE_DUM means retaining no tuples at all from the other argument, joining to TABLE_DEE means retaining them all. So joining R to a relvar S which can be equal to either TABLE_DEE or TABLE_DUM, is the RA equivalent of "if S then R else FI", with FI standing for the empty relation.

Erwin Smout
A: 

It is difficult to see utility of TABLE_DEE and TABLE_DUM from SQL Database perspective. After all it is not guaranteed that your favorite db vendor allows you creating one or the other.

It is also difficult to see utility of TABLE_DEE and TABLE_DUM in relational algebra. One have to look beyond that. To get you a flavor how these constants can come alive consider relational algebra put into proper mathematical shape, that is as close as it is possible to Boolean algebra. D&D Algebra A is a step in this direction. Then, one can express classic relational algebra operations via more fundamental ones and those two constants become really handy.

Tegiri Nenashi

related questions