Re: Help needed with normalisation - Mailing list pgsql-general

On Fri, 19 Mar 2004 20:52:49 -0000, "Evil Monkey" <a@b.com> wrote:

>I have a relational model  that I need to normalise to check there will
>be no update anomalies and get it to Boyce Codd normal form.

Hmmm.

>Could some please help me fully understand the concept of a functional
>dependency?

Informally, if you're given a value in one column, do you know one and
only one value in another column?  If so, there's a functional
dependency between the two.  For example,

 City           State  ZIP
 --
 Oakland        CA     94608
 San Francisco  CA     94101
 San Francisco  CA     94102

a. If you know a value in the column "ZIP", do you know the value for
the column "State"?  If this sample data is representative, you do.
So you might say "there's a functional dependency from ZIP to State",
or "the value of ZIP determines the value of State".

b. If you know a value in the column "ZIP", do you know the value for
the column "City"?  Again, if this sample data is representative, you
do.  So you might say "there's a functional dependency from ZIP to
City".

c. Ditto for City and State.

d. But not for City and Zip.  Knowing "San Francisco" gets you two
values--"94101" and "94102".

*If* the sample data were representative.  It's not.  This sample is
more realistic.

 City           State  ZIP
 --
 Emeryville     CA     94608
 Oakland        CA     94608
 San Francisco  CA     94101
 San Francisco  CA     94102
 Kansas City    KS     66101
 Kansas City    MO     64101

Your turn.  Identify the functional dependencies.

>I dont quite understand how to approach converting my relational model into
>BCNF.

You don't convert a relational model into BCNF.  You normalize a table
to BCNF.  That is, the normal forms refer to individual tables.

>Do I just assume the funtional depndancies based on what the context of the
>overall system is or is there a way to correctly go through each relation
>and check for FD's?

Assuming functional dependencies strikes me as fairly risky.  Even if
I knew what you meant by "the context of the overall system".

>I undertsand FD's to some degree and the purpose of normlisations but im a
>little vague on how to get started

One way is to just pick a table.  Determine whether it conforms to 1NF
according to your text book's definition of 1NF.  Then determine
whether it conforms to 2NF.  Then 3NF.  Then BCNF.

Repeat until you're out of tables.

>If there is can some one help me to understand one of my relations below? I
>really want to understand the process.
>
>relation name:VENUE
>
>CK VenueID
>
>(VenueID,NameOfVenue, Address, Telephone ,EntryCost ,OpenTime
>,CloseTime,Website,VenueBudget)

No sample data.  Besides their names, no hints about what the columns
mean.  (Experience will teach you not to rely much on column names.)

At this point, all you have to go on is guesswork.  Experienced
designers might employ educated guesswork based on their understanding
of what "venue" means, what often happens at venues (performances),
how businesses generally work, and the presence of "venue" in several
of the column names, but much of that is still guesswork.

So go ahead and make a guess, post your conclusions and your
rationale, and I'm sure you'll find a lot of help here.  But people
who know what they're doing won't do your homework for you.

--
Mike Sherrill
Information Management Systems