Re: A second qs on multivalued dependencies.

From: Kostas <noemail_at_noemail.net>
Date: Sat, 16 Oct 2004 17:40:56 -0400
Message-ID: <10n35cabf14jc02@corp.supernews.com>

Well after a little bit more thinking I realized that this is a generalization of my previous question on lookup tables. What I am doing again is building a separate entity that serves as a lookup table for a finite combination of values.

This time, however, the lookup table is not for just one field but for two (3 in my original db) which might justify the existance of a separate table.

For instance, a lookup table PHONETYPE with a phonetype attribute and values {Home, Work, Cellular} doesnt serve much purpose since I can have a field PhoneType in the table PHONE.
But in this case, leaving those fields in the original table medium would introduce some redundancy because I would have to repeat two fields (capacity and physicaltype) multiple types instead of using a mediumtypeid foreign key in their place.

I guess I am explaining this to myself in writing :-)

Cheers!

Konstantinos

PS: I got Ullman's Principles of Database Systems Second Edition for only 6.50$. It is old but well written and classic isn't it?

Whether the difference being
"Tony" <andrewst_at_onetel.com> wrote in message news:ed8a00fa.0410150538.38e802f1_at_posting.google.com...

> "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 Sat Oct 16 2004 - 16:40:56 CDT