Re: So what's null then if it's not nothing?

On Fri, 9 Dec 2005 12:08:52 +0100, Jon Heggland wrote:

(snip)
>I really didn't know that truth valued attributes were so abhorred,
>though. Interesting.

Hi Jon,

I wouldn't call them "abhorred". But AFAIK, there's no way to incorporate "truth value" columns in a SQL database without breaking some of the foundations of SQL.

We'll first have to agree on what the foundations are. To me, the following rules are part of SQL's foundation:

1. NULLs propagate - any operation that has NULL as one of it's operands results in NULL.
2. Each column must be able to hold NULLs, unless explicitly constrained to be NOT NULL or PRIMARY KEY. As a consequence, each datatype supported by a SQL database should accomodate NULLs.
3. Each predicate evaluates to one of the three values defined in 3VL and each operation with 3VL valued operands results in a 3VL valued result according to the 3VL logic tables.

If you disagree with me that all the above is part of the foundation of SQL, then you'll disagree that storing truth values in a column would break this foundation - no need to read on.

The first thing to decide of you propose to store truth values in a column is which logic system to use. I don't think that there is any problem with storing a proper Boolean (2VL) truth value. But that choice would defy the purpose of being able to materialize the result of a predicate, since these results are 3VL, and conversion from 3VL to 2VL will always cause data loss. So I'll interpret the idea to store truth valued attrbiutes as 3VL valued attributes.

According to rule 2 above, a column with datatype TruthValue should not only accomodate the three values {TRUE, FALSE, UNKNOWN}, but it should also accomodate the NULL marker to denote absence of a value.

Now what about the expression (TruthValue1 AND TruthValue2)? If TruthValue1 is FALSE and TruthValue2 is UNKNOWN, the result should be FALSE (according to rule 3 above). If TruthValue1 is FALSE and TruthValue2 is NULL, then the result should be (according to rule 1 above) NULL. This proves that the suggestion to treat UNKNOWN and NULL in a truth valued domain as being equal is not possible. (Yes, I have seen people make that suggestion - I don't believe it was in this group, though).

But if we have to define seperate results for FALSE AND TRUE, FALSE AND UNKNOWN, FALSE AND FALSE, and FALSE AND NULL, then we are clearly no longer dealing with the original 3VL logic tables that are mentioned in rule 3 above. Rather, we are extending them.

To wrap it up:
Is it possible to store *boolean* attributes in a SQL database? Yes, I think so - but it would be nearly useless, since you can't store the result of the evaluation of a predicate in it; you could only store what the client passes.
Is it possible to store 3VL truth valued attributes in a SQL database? Yes, this too is possible, but not within the foundation for SQL databases as it's currently defined. You'll have to extend the existing 3VL logic tables to something that I hesitate to call 4VL (since NULL is not a value, but rather the absence of a value). These tables would look like this:

(Note: T = True, F = False, U = Unknown, - = NULL)

 | AND | T | U | F | - |   | OR  | T | U | F | - |   |  NOT  |
 +-----+---+---+---+---+   +-----+---+---+---+---+   +---+---+
 |  T  | T | U | F | - |   |  T  | T | T | T | - |   | T | F |
 |  U  | U | U | F | - |   |  U  | T | U | U | - |   | U | U |
 |  F  | F | F | F | - |   |  F  | T | U | F | - |   | F | T |
 |  -  | - | - | - | - |   |  -  | - | - | - | - |   | - | - |

While this might theoratically and technically work, I don't like the idea. There are already too many people flabbergasted by 3VL - what would "4VL" do for them? There are heaps of people who just don't get the difference between NULL and "unknown" - how would a domain that allows both NULL and UNKNOWN help them?

For me, it's just too many problems for too little gain.

Best, Hugo

-- 

(Remove _NO_ and _SPAM_ to get my e-mail address)