Re: A second qs on multivalued dependencies.
Date: Sat, 16 Oct 2004 17:40:56 -0400
Message-ID: <10n35cabf14jc02_at_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 - 23:40:56 CEST