Re: ADR's Normalization question

From: Mr. Scott <do_not_reply_at_noone.com>
Date: Sat, 14 Nov 2009 10:08:50 -0500
Message-ID: <4OOdnRtZFLCeVWPXnZ2dnUVZ_uSdnZ2d_at_giganews.com>


"paul c" <toledobythesea_at_oohay.ac> wrote in message news:o_tLm.52824$PH1.5089_at_edtnps82...
> Mr. Scott wrote:

>>> ...
>>> If I'm not mistaken Armstrong's axioms can be applied to show that
>>>
>>>   { CITY, CLASS } -> { STATUS },
>>>
>>> so the CTS relation isn't needed if the CLS relation is replaced by
>>>
>>>   CLS { CITY, CLASS, STATUS }
>>>       KEY { CITY, CLASS }
>>>
>>> Have I got this right?  If so, I think ADR's suggested rule does apply 
>>> to Date's example.
>>>
>>
>> Sorry, you've got it wrong.  {CITY,CLASS,STATUS} satisfies the 
>> multi-valued dependency,
>>
>>     STATUS ->-:> CITY | CLASS
>>
>> which is not implied by the key
>>
>>     {CITY,CLASS}
>>
>> {CITY,CLASS,STATUS} is therefore not in 4NF let alone 5NF.
>> The projections, {CITY,STATUS} and {CLASS,STAUTS}, are in 5NF, though.
>>
>> While AB -> C can be inferred from A -> C and B -> C, that is,
>> a table that satisfies A -> C and B -> C also satisfies AB -> C, but
>> A -> C and B -> C cannot be inferred from AB -> C.
>> ...
>

> Thanks, I shouldn't have dropped those two dependencies and I take back
> the strawman crack.
>

> But I don't get why { CITY, CLASS, STATUS} isn't 4NF, a similar MVD could
> be claimed for any relation that has at least one key attribute and one
> non-key attribute.

The claim is due to Date's "otherwise quite independent" criterion. Whenever there is a functional dependency from A to B, for each B value there is a disjoint subset of A values. The set of A values is effectively "partitioned" by the set of B values. In {CITY,CLASS,STATUS}, there are two distinct functional dependencies,

CITY -> STATUS and CLASS -> STATUS;

consequently, for each STATUS value there is a disjoint subset of CITY values and a disjoint subset of CLASS values. In order for those subsets to be "otherwise quite independent," the multtivalued dependency,

STATUS ->-> CITY | CLASS must hold. In the case of a table that satisfies a functional dependency like

K -> A,

the degenerate multivalued dependency,

A ->-> K | nil,

is due to the functional dependency K -> A. It is therefore safe to say that it is implied by the key.

It is also safe to say that {CITY,CLASS,STATUS} isn't even in 3NF, since there are functional dependencies that are not implied by the key.

{CITY,CLASS} -> STATUS does not imply

CITY -> STATUS or CLASS -> STATUS. Received on Sat Nov 14 2009 - 09:08:50 CST

Original text of this message