Re: ADR's Normalization question

"Mr. Scott" <do_not_reply_at_noone.com> wrote in message news: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

Sorry, BCNF.

> there are functional dependencies that are not implied by the key.
>
> {CITY,CLASS} -> STATUS
>
> does not imply
>
> CITY -> STATUS or CLASS -> STATUS.
>
>
Received on Sun Nov 15 2009 - 14:16:30 CET