# Re: ADR's Normalization question

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