Re: Why does 1:3 relationsihp require another table?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Anith Sen (anith_at_bizdatasolutions.com)
Date: 05/16/04 

Date: Sun, 16 May 2004 00:12:07 -0500

>> ..one can build a theory like relational algebra basedon tuples that can
contain that symbol in place of any domain value <<

Perhaps one can, provided the symbol can handle all kinds of missing
information without violating any existing 3VL tautologies. And it is
practically difficult if not impossible and that is why NULLs or any such
markers in place of values cannot be valid in a real-world proposition and
thus violates 1NF.

>> Correct, but is it fair to say the development of SQL was guided by a
desire to make NULL a good representation of a real-world unknown more than
it was guided by a desire to make NULL a good representation of NoValueHere?
<<

I doubt it. If representation of unknown value is the idea behind the
implementation of NULLs in SQL, then SQL avoids far too many kinds of
missing information which is quite common in the real world. Given an
attribute a, there are many interpretations for the statement "a is null":
1. Inapplicable value (Codd's I-mark)
2. Non-existent value
3. Unknown value (Codd's A-mark, which is claimed to be in SQL)
4. Undefined value
5. Not supplied value (variations: not yet, since then etc.)
6. Invalid value
7. Empty set (hint: outer joins)
8. and several more...

By assuming support for just one kind of missing information (Unknown value
in our case), support for NULLs in SQL or any such markers, may have to
ignore all other kinds of missing information handling. Given no NULLs, the
system can live with the traditional 2VL and with an additional single kind
of "null" (to handle the meaning Unknown) it requires 3VL and so on. In
other words, to deal with the 7 kinds of "nulls" above one would need a
logic of 9 values (9VL). In general, given N kinds of "nulls", one would
need (N+2)valued logic, which is quite impossible with the existing naivete
approach in SQL. 

-- 
Anith

Relevant Pages

  • Re: So whats null then if its not nothing?
    ... two nulls are not distinct; ... it means effectively that unknown is treated as ... constraints leads to tuples being rejected if false; ... The SQL unique constraint does not consider two ...
    (comp.databases.theory)
  • Re: All hail Neo!
    ... Since SQL and RM are both theory and practical, ... If the practitioner never considers where the pitfalls lie, he will only discover them from the bottom of the pit. ... They have correctly identified that missing information as of yet has no theory to address it. ... If one takes the average age of a group of people where some of the ages are unknown, ...
    (comp.databases.theory)
  • Re: When is a NULL Not a NULL ?
    ... I would always *try* to avoid them, Nulls that is, with careful database ... you are speaking only of a convention among SQL users. ... something that is unknown. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: So whats null then if its not nothing?
    ... Does SQL's definition of null (unknown) include the null ... > whether it's null (as in an empty string) or not. ... Part of the problem with undertsanding nulls is that conceptually SQL ...
    (comp.databases.theory)
  • Re: So whats null then if its not nothing?
    ... Does SQL's definition of null (unknown) include the null ... > whether it's null (as in an empty string) or not. ... Part of the problem with undertsanding nulls is that conceptually SQL ...
    (comp.databases.theory)