Re: 3vl 2vl and NULL


Hugo Kornelis wrote:
> On 13 Dec 2005 12:37:00 -0800, dawn wrote:
>
> >Hugo Kornelis wrote:
> >> On 10 Dec 2005 05:22:38 -0800, dawn wrote:
> >>
> >> (snip)
> >> >> "Show me all family members who are NOT older than Aunt Marge" - should
> >> >> Uncle Vernon be included or not? Why?
> >> >
> >> >Two choices of how to ask the question --
> >> >1) Show me all members who have an age where that age is less than or
> >> >equal to Aunt Marge's age
> >> >
> >> >In this case Uncle Vernon does not show up because he does not have an
> >> >age
> >> >
> >> >2) Show me all family members who do not have an age greater than Aunt
> >> >Marge's
> >> >
> >> >In this case Uncle Vernon does show up
> >>
> >> Hi Dawn,
> >>
> >> So the results of the query would depend on exactly how you formulate
> >> it?
> >
> >I cannot think of any situation where that is not the case, can you ;-)
>
> Hi Dawn,
>
> I have a lot more trouble thinking of a situation where it IS the case.
>
> In SQL, the following queries will return the same results:

I'll number these

1)
> SELECT Name
> FROM MyFamily
> WHERE Age <= (SELECT Age FROM MyFamily WHERE Name = 'Aunt Marge')

2)
> SELECT Name
> FROM MyFamily
> WHERE NOT (Age > (SELECT Age FROM MyFamily WHERE Name = 'Aunt Marge'))
>
>
> >> Hmmm. AFAICT, this means that in your 2VL algebra, missing data results
> >> in ~(A) not being the same as (~A).
> >
> >No, I don't think so, but before I respond to more, can you give me an
> >example that makes you think this? Thanks. --dawn
>
> See the above queries. Rules of Booolean algebra say that
> NOT (Age(x) > Age(Aunt Marge))
> should be the same as
> (Age(x) <= Age(Aunt(Marge))
>
> Your explanation made me believe that this is not the case in your 2VL
> system.

Then I did not explain it correctly or I'm missing something now.

Let's have a data set that includes
Aunt Marge, 47
Uncle Vernon, null = null set = empty set, nada
Dale, 12
Dot, 70

1) Inner select grabs the age of 47, then selects names of everyone but
Dot as everyone else, including Uncle Vernon in this 2VL scenario, has
an age less than or equal to 47 (null is less than 47 as surely as
nothing is less than something -- that's the twist that is different
than a 3VL)

2) Inner select grabs the age of 47, then we test to see if NOT (Age >
47) is the case. That is the case for everyone but Dot.

If the data are changed so that Aunt Marge also has a null set age,
then with

1) The Age of null set is selected and both Aunt and Uncle are chosen
for this query
2) Similarly, the only ones with NOT (Age > Marge's age) are the Aunt
and Uncle.

So, the results are the same with both queries. Now, perhaps Uncle
Vernon is 110, clearly older than Aunt Marge, but that fact has not yet
been recorded since people were pouring over medical and legal records
to verify this first. He will then incorrectly be identified as
someone whose age (in this database) is less than or equal to Marge's
age. When the data are not correct in the system, the results of the
queries are not correct either.

I have not done any tests with this, but there is something about
seeing the incorrect data on a report that helps with data cleansing,
with the downside that someone might believe that Uncle Vernon is
younger than Aunt Marge and this could cause some problem. There are
pros and cons to each, but it really is far easier to work with a 2VL.
I doubt anyone who has worked extensively with both would say
otherwise.

Did that clarify? --dawn

>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)

.

Relevant Pages

  • Re: 3vl 2vl and NULL
    ... >Dot as everyone else, including Uncle Vernon in this 2VL scenario, has ... >If the data are changed so that Aunt Marge also has a null set age, ... >someone whose age (in this database) is less than or equal to Marge's ...
    (comp.databases.theory)
  • Re: 3vl 2vl and NULL
    ... It is unknown. ... > I would hasard a guess that at least Uncle Vernon knows his age. ... So the fact type for the one tuple is not the same as for the other. ...
    (comp.databases.theory)
  • Re: 3vl 2vl and NULL
    ... >> I would hasard a guess that at least Uncle Vernon knows his age. ... I object to the choice of words "It is unknown", ... Vernon's age IS known. ... >It seems, though, that you use "fact type" in a manner unfamiliar to me. ...
    (comp.databases.theory)
  • Re: So whats null then if its not nothing?
    ... so what is it's age? ... I won't tell you the age of my dog. ... Since nobody here knows, nobody will be ... Since I have four cats, you'll have to insert a row, with Hugo Kornelis ...
    (comp.databases.theory)
  • Re: 3vl 2vl and NULL
    ... >>> Uncle Vernon be included or not? ... >>1) Show me all members who have an age where that age is less than or ... >>2) Show me all family members who do not have an age greater than Aunt ...
    (comp.databases.theory)