Re: Proposal: 6NF

From: Brian Selzer <brian_at_selzer-software.com>
Date: Wed, 04 Oct 2006 06:28:59 GMT
Message-ID: <LwIUg.8793$vJ2.8398@newssvr12.news.prodigy.com>

"J M Davitt" <jdavitt_at_aeneas.net> wrote in message news:OLnUg.7759$OE1.6886_at_tornado.ohiordc.rr.com...

> Brian Selzer wrote:

>> "J M Davitt" <jdavitt_at_aeneas.net> wrote in message
>> news:60iUg.3585$Cq3.515_at_tornado.ohiordc.rr.com...
>>
>>>Brian Selzer wrote:
>>>
>>>>"Cimode" <cimode_at_hotmail.com> wrote in message
>>>>news:1159797534.237592.159150_at_e3g2000cwe.googlegroups.com...
>>>>
>>>>
>>>>>Brian Selzer wrote:
>>>>>
>>>>>
>>>>>>"Roy Hann" <specially_at_processed.almost.meat> wrote in message
>>>>>>news:P-Sdnd58Trp7GYLYnZ2dnUVZ8tGdnZ2d_at_pipex.net...
>>>>>>
>>>>>>
>>>>>>>"David Portas" <REMOVE_BEFORE_REPLYING_dportas_at_acm.org> wrote in
>>>>>>>message
>>>>>>>news:1159692483.421785.264660_at_c28g2000cwb.googlegroups.com...
>>>>>>>
>>>>>>>
>>>>>>>>Brian Selzer wrote:
>>>>>>>>
>>>>>>>>
>>>>>>>>>The argument JOG made focused only on recording information, not
>>>>>>>>>retrieving
>>>>>>>>>it. Why would anyone abandon a sound mechanism that can
>>>>>>>>>significantly
>>>>>>>>>reduce the computing capacity required to answer a query?
>>>>>>>>
>>>>>>>>Because your argument is merely an assumption based on what some
>>>>>>>>systems of today are capable of.
>>>>>>>
>>>>>>>It's worse. His entire position is based on not knowing even what
>>>>>>>some of
>>>>>>>today's products are already capable of. For example, he seems
>>>>>>>unaware of
>>>>>>>the role of the optimizer.
>>>>>>>
>>>>>>
>>>>>>I understand fully the role of the optimizer. That's one of my
>>>>>>points. If
>>>>>>you arbitrarily split a table with a nullable column, then you're
>>>>>>robbing
>>>>>>the optimizer of possible execution plans. It may make sense to split
>>>>>>a
>>>>>>table, for example, removing non-key columns that are seldom used in
>>>>>>queries
>>>>>>into another table in order to boost the performance of all other
>>>>>>queries.
>>>>>>The point I'm trying to make is that the decision should not be
>>>>>>arbitrary.
>>>>>
>>>>>Consequences of NULL uses goes far above he simple problem of
>>>>>execution. It hinders performance at all levels (response time,
>>>>>concurrency) and introduces a very strong part of subjectivity in
>>>>>interpretation of data presented to users.
>>>>>
>>>>
>>>>
>>>>I don't agree. Judicious use of NULL can improve performance, response
>>>>time, and concurrency.
>>>
>>>A wrong answer isn't always better because it cost less.
>>>
>>
>>
>> But an incomplete answer may be better than nothing. For example, if you
>> delay arresting a suspected terrorist until you have enough evidence to
>> convict, you get 9/11; if you pick him up and then develop the evidence
>> through interrogation, search and seizure, you may not get enough for a
>> conviction, but you might prevent an attack.
>>

>
> So, let me get this straight: we round up all the NULLs,
> pack 'em together in Guantanamo Bay, and the world will
> be a better place?
>

The moon might be better than Camp Delta--or maybe Bikini island. The problem is that not all NULLs want to kill us and are willing to die in the process.

>>
>>> In addition, when used correctly, no subjectivity is
>>>
>>>>introduced. For every database schema with NULLable attributes, there
>>>>is always an equivalent schema without.
>>>
>>>Well, being the god* you seem to be, I won't doubt
>>>that solely because you said so.
>>>
>>>* Genius of Design
>>>
>>
>>
>> I'm not sure I understand your point.

>
> How are two schemas - one "with NULLable attributes" and
> one "without" - equivalent?
>

Two database schemata are equivalent if all of the information contained in an instance of one can be derived from the information contained in an instance of the other. Database schemata A and B are equivalent if and only if a pair of inverse mappings exist between A and B: one mapping from A to B such that for every legal instance of A, there exists one and only one legal instance of B, and one mapping from B to A such that for every legal instance of B, there exists one and only one legal instance of A. In other words, given any instance of A, only one instance of B can be derived using the mapping from A to B such that when the mapping from B to A is applied to that derived instance, the result is the original instance for A, and given any instance of B, only one instance of A can be derived using the mapping from B to A such that when the mapping from A to B is applied to that derived instance, the result is the original instance for B. Note that this applies to database schemata with and without nullable attributes. For example, a database schema D consisting of a single relation,

R {A, B, C}

that satisfies the functional dependency A --> B is not equivalent to a database schema D' consisting of two relations,

S {A, B}, T {A, C}

without the circular inclusion dependency S[A] <--> T[A] because for any given instance of D, there can be a huge number (infinite if any domain for A, B or C is infinite) of instances of D' where the natural join of the extensions of S and T is equal to the extension of R. The circular inclusion dependency ensures that the projections of S JOIN T over {A, B} and {A, C} are equal to the extensions of S and T respectively.

I hope that the above description and example illustrate what I mean by equivalent database schemata. For a database schema without nullable attributes, the mappings involve sets of projections and natural joins. For a database schema with nullable attributes, the operations are different. A simple projection would still include nullable attributes. To eliminate them, the decomposition operation must also restrict the projection to eliminate any tuple with a null. As a consequence, the inverse operation must be an outer join which restores the eliminated tuples. In addition, instead of a circular inclusion dependency, all that is needed is a single foreign key constraint. There is no need to maintain a 1:1 correspondence between tuples from each projection because since the original attribute is nullable, the 1:1 correspondence didn't exist to begin with.

>   Are you asking for proof that a

>> schema with nullable attributes can always be transformed into one
>> without, or are you trying to put me back in my place because you don't
>> think I know what I'm talking about? It's obvious to me that an outer
>> join of a set of restricted projections of a table with nullable columns
>> is equivalent the the original table, provided that each projection does
>> not allow nulls, that each projection includes at least one key and
>> either a subset of the columns that allow nulls or a subset of the
>> columns that don't, and that each column is represented in at least one
>> of the projections.

>
> What does SQL's OUTER JOIN have to do with what you described?
>
> How does a description of COMPOSE make your point?
>

>>
>>
>>> Any subjectivity would therefore be
>>>
>>>>independent of the database schema. On the other hand, arbitrary or
>>>>incorrect use of NULL can cause all of the problems you describe.
>>>>
>>>>
>>>>
>>>>>>>Roy.
>>>>>>>
>>>>>
>>
Received on Wed Oct 04 2006 - 01:28:59 CDT