Re: We claim that delete anomality is due to table not being in 3NF, but...

From: Walter Mitty <>
Date: Fri, 31 Oct 2008 10:47:03 GMT
Message-ID: <HmBOk.1577$>

"Hugo Kornelis" <> wrote in message
> On Wed, 29 Oct 2008 22:59:25 GMT, paul c wrote:
>>Hugo Kornelis wrote:
>>> Functional dependencies stem from reality. Whether or not you choose to
>>> include B in your model does not change the situation where, apparently,
>>> C depends on A through some intermediary B (that is not in the DB).
>>> In a DB that stores PersonID and EyeColour, one might argue that the
>>> actual dependency goes back to the parents of the person and their
>>> genetic patterns - but those will typically not be stored, and yet the
>>> EyeColour still depends on PersonID.
>>> ...
>>Assuming you're saying it's improper to depend on any notion of absolute
>>reality, I think I'd agree. Doesn't a db aimed toward aiding some
>>present function necessarily stand for a very fractional/partial (or
>>even distorted) reality? Eg., if it's not fractional it's probably
>>unwieldy and untoward. Seems to me that the EyeColour dependency hints
>>at this - when the purpose of a particular set of tables isn't concerned
>>with dna, one likely ignores blood lines. Further, I suspect that no db
>>ought to introduce fd's that aren't patently implicit in the user's
>>requirements/biz rules/application intent. In the USA, I gather that an
>>address that is complete enough for a mailman to deliver to, along with
>>a city and a state will determine zipcode, yet I suspect there are many
>>tables in non-postal db's that have a column set such as (Customer,
>>unit, streetaddress, city, state, zip).
> Hi Paul,
> This is cdt, not alt.philosophy. I was not after an existential
> discussion on absolute reality. :)
> You're reading way more into my example than I intended. The point I
> tried to make is that functional dependencies are not determined by what
> is or is not stored in the database, but by how entities and their
> interactions in reality. The DB is a model of reality and changing the
> model won't change reality.

Even your comment above is way too philosophical for me.

An FD that involves data not stored in the database should NOT be used when evaluating conformance to normal forms. There are any number of columns of dependent data that are transitively dependent on the primary key via intermediate data that is not stored in the DB. As long as there are not any transitive dependencies IN THE TABLE, you will not experience any of the update anomalies caused by deviation from 3NF.

As far as you can see, looking at the data in the table, the FD is not transitive. Whether the FD is transitive in the real world is beyond the purview of data normalization. Received on Fri Oct 31 2008 - 11:47:03 CET

Original text of this message