Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: Is possible to conserve the dependencies here?

Re: Is possible to conserve the dependencies here?

From: Damjan S. Vujnovic <damjan_at_galeb.etf.bg.ac.yu>
Date: Mon, 18 Nov 2002 00:01:55 -0800
Message-ID: <ar973b$380$1@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:

> 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
Received on Mon Nov 18 2002 - 02:01:55 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US