Re: Help needed with normalisation

On Fri, 19 Mar 2004 20:52:49 -0000, "Evil Monkey" <a_at_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

1. 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".
2. 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".
3. Ditto for City and State.
4. 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