Re: Is possible to conserve the dependencies here?

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:


> 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