Re: Is possible to conserve the dependencies here?
Date: Mon, 18 Nov 2002 00:01:55 -0800
Message-ID: <ar973b$380$1_at_news.etf.bg.ac.yu>
Decompose into what (3NF, BCNF, ...)? If you are talking about decomposing into 3NF, then the answer is (always) yes (because there exists an algorithm for decomposing into 3NF that preserves functional dependencies):
PROVIDER1(PROVIDER_ID, CITY, STATE, STREET) PROVIDER2(PROVIDER_ID, ZIP_CODE) PROVIDER3(CITY, STATE, STREET, ZIP_CODE)
All three relations are in 3NF and functional dependencies are preserved. Note that aside the two functional dependencies you stated, also holds (if I understand your model):
PROVIDER_ID -> STREET, CITY, STATE, ZIP_CODE If you are decomposing into BCNF then preserving functional dependences is a new assignement for Tom Cruise (MI-3). Simply, if you wish to preserve the functional dependence
CITY, STATE, STREET -> ZIP_CODE then you must have a relation XXX that will contain CITY, STATE, STREET, ZIP_CODE. Relation XXX will not be in BCNF because of the functional dependence ZIP_CODE -> CITY, STATE
-- Regards. Damjan S. Vujnovic University of Belgrade School of Electrical Engineering Department of Computer Engineering & Informatics Belgrade, Yugoslavia http://galeb.etf.bg.ac.yu/~damjan/ "Juan Pardillos" <sicotom_at_eresmas.com> wrote:Received on Mon Nov 18 2002 - 09:01:55 CET
> Hi,
>
> in the relation:
>
> PROVIDER(PROVIDER_ID, STREET, CITY, STATE, ZIP_CODE)
>
> there are the following dependencies:
>
> CITY, STATE, STREET -> ZIP_CODE
> ZIP_CODE -> CITY, STATE
>
> Is it possible to decompose the previous relation and automatically
> preserve both functional dependencies?.
>
> Thanks in advance