Re: NULLs

On Dec 30, 10:46 pm, "Brian Selzer" <br..._at_selzer-software.com> wrote:
> "JOG" <j..._at_cs.nott.ac.uk> wrote in message
>
> news:c9ec9790-f950-4407-b1f8-e5b8f4d07e91_at_v4g2000hsf.googlegroups.com...
>
>
>
> > On Dec 30, 7:23 pm, "Brian Selzer" <br..._at_selzer-software.com> wrote:
> >> "stevedtrm" <steved..._at_hotmail.com> wrote in message
>
> >>news:a3716365-7bc4-4a75-ab58-ee7ac957c299_at_n20g2000hsh.googlegroups.com...
>
> >> >> > If everyone is clear NULLS shouldn't be used, why the debate as to
> >> >> > what
> >> >> > to do about them ?
>
> >> >> Because SQL allows NULL and even promotes the idea that NULL solves
> >> >> some
> >> >> problem instead of introducing many.
>
> >> > So everyone is agreed that NULLs shouldn't appear anywhere, and its
> >> > just a matter of time before NULLS become a legacy problem and a
> >> > relational language supercedes SQL?
>
> >> No, Steve, not everyone is agreed.
>
> >> > Are the two solutions I suggested before the widely accepted as
> >> > resolutions to the two problems NULLs were introduced to eradicate?
>
> >> >>Missing information? Then surely there should just be no tuple?
>
> >> A row may contain a lot more information that would otherwise be useful.
> >> Would you tell a potential customer that you won't serve them because you
> >> don't know everything about them? I think not! Haven't you been asked
> >> for
> >> your e-mail address at a retailer? What would you think if the cashier
> >> told
> >> you to get lost for refusing to supply it?
>
> >> >>To indicate that there can be no value? Why not a seperate table with
> >> >>a boolean value in the non-key column?
>
> >> In my opinion, null should only ever be used to indicate "There should be
> >> a
> >> value here, but it hasn't been supplied." That there should be a value
> >> or
> >> that there must be a value are structural issues, and therefore should
> >> not
> >> be subject to interpretation. "No value here" invites interpretation,
> >> because it begs the question, "Should there have even been a value?"
>
> >> Structural information is lost by separating a nullable column into its
> >> own
> >> table: the fact that there should always be a value, even if it hasn't
> >> been
> >> supplied is lost.
>
> > I think this could be put better. It is not that there should always
> > be a value, but rather that "a value exists in the real world".
> > However I don't really see what is structurally lost by complete
> > decomposition to eliminate nulls, that can't be remedied via
> > constraints.
>
> If you complete the interpretation such that all missing values are
> supplied, then the resulting database should be a set of relations. When
> there is a nullable column on a table, there will a 1:1 correspondence
> between values in the completed relation. The problem with separating a
> column that can contain nulls into another table is that when all missing
> values are supplied, there is no requirement that for every tuple in the
> referenced relation there must be one and only one tuple in the referencing
> relation. Adding a constraint to guarantee that 1:1 correspondence would be
> the same as not permitting the information to be missing in the first place.
> Thus the only ways that I can see to remedy that would be either to add a
> boolean column, or to add another table.

Could you clarify why you think using nulls maintain structural information? If one is saying that a datum /must/ exist as part of the proposition, why is there a nullable column allowing it not to? Perhaps you are saying that the nullable column is stating that the value "should be" part of the proposition, but "we're being a bit lax about it" whereas a decomposed version does not?

If so, I am unclear at the moment what the value of this is - perhaps you could refer me to an example of when it would be necessary?

>
> >> That's not to say that it isn't possible to eliminate
> >> nulls altoghether, it's just that you need several separate tables
> >> instead
> >> of one: one table for the values that must always be present, one table
> >> for
> >> each nullable column (or set of columns in those cases where whenever one
> >> value is supplied, another value must also be supplied) that indicates
> >> whether or not there should be a value (or set of values), and one table
> >> with a column (or set of columns) for those values that just happen to
> >> have
> >> been supplied.
>
> >> Note also that the spectre of 3VL disappears when null indicates only
> >> "there
> >> should be a value here, but it hasn't been supplied." Since there should
> >> be
> >> a value, and only one value, a null can simply be replaced by an
> >> exclusive
> >> disjunction that encompasses all of the values in the attribute's domain.
> >> So there is no need for 3VL, 2VL suffices. (For infinite domains, a
> >> quantifier could be used that means instead of "there is...," "there is
> >> exactly one....")
Received on Mon Dec 31 2007 - 00:23:18 CET