Relations With No Attributes

Question

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?

Piacular

What does that mean? "What's a relation with no attributes?" Can you give any kind of real-world example? – Sonny

I think the problem is that there are no real-world examples... – Wiggly

"CREATE TABLE dee ()" followed by "INSERT INTO DEE VALUES ()" should do the trick, if your DBMS is truly relational. – Piacular

Answer 1

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

Sherard

Answer 2

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 roles equivalent to 1 and 0 in ordinary algebra.

Gnawing

Answer 3

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?!

Meridel

What it means and whether or not it would be any good are two quite different things... – Piacular

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!-) – Meridel

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. – Piacular

Answer 4

"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.

Teddy

Answer 5

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 ();

Piacular

I've noticed that some database systems won't tolerate the no-column CREATE statement, and some do but then don't tolerate the zero-value INSERT statement. – Rheingold

Answer 6

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.

Hawkeyed

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. – Piacular

Answer 7

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.

Triggerfish