A second qs on multivalued dependencies.

From: Kostas <noemail_at_noemail.net>
Date: Fri, 15 Oct 2004 01:47:49 -0400
Message-ID: <10mupanj91aio15_at_corp.supernews.com>



During the design of a database about my cd's and dvd's I came across the following observation.
(The example below is a simplified version of my original relation serving just to make my point)
I originally had a table

OpticalMedium (MediumID, PhysicalType, Capacity)

In this table MediumID is a system-identifier PhysicalType has values {CD, CDR, DVD, DVD-R} and Capacity has values {650MB, 700MB, 4.7GB, 9.4GB, 18GB) (ignore the mix of sizes and units)

Obviously there is some sort of dependency here (multivalued?) because if a medium's physical type is cd or cdr then its capacity will either be 650MB or 700MB.
If it is a DVD or DVD-R, then its capacity will be either 4.7GB, 9.4GB, or 18GB.

But anywhere I read about functional dependencies they define an FD between two attributes for single values. A-->B if for a certain value of A, B will always have the same value. This is not the case here. The question is, therefore, if the above relation is normalized up to Boyce/Codd or not?

Will the following design be a better case and if yes how is it theoretically justified?

Alternative design (move the interdependent attributes in a separate MediumType entity):

Medium (MediumID, MediumTypeID(foreign key)) MediumTypeID (MediumTypeID, PhysicalType, Capacity)

The second table now will contain exhaustive enumerations of all possible cases and the combination of PhysicalType and Capacity will be essentially a candidate key.

Out of intuition I believe the second design is better than the first but theoretically I am not sure why.

Please help me understand better
Thank you.

Konstantinos Received on Fri Oct 15 2004 - 07:47:49 CEST

Original text of this message