Re: ADR's Normalization question

"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.
>> ...
>

>

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 - 16:08:50 CET