Re: A second qs on multivalued dependencies.

"Kostas" <noemail_at_noemail.net> wrote in message news:<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

That works, but you don't HAVE to instroduce a surrogate key into every table. You could just do this:

create table MediumType (PhysicalType, Capacity, primary key (PhysicalType, Capacity));

create table OpticalMedium (MediumID primary key, PhysicalType, Capacity,
foreign key (PhysicalType, Capacity) references MediumType); Received on Fri Oct 15 2004 - 15:38:23 CEST