Re: Newbie - what's all this BCNF stuff anyway?

> This isn't necessarily a good thing. Some will argue that
> auto-increments are pure evil. I'm much more moderate about
> it, but I definitely wouldn't use them "like crazy."

Well, I wouldn't use them in a join table, say when I'm joining two tables in a many-to-many, but I would use the auto-incremented ID fields from the two joining tables as FKs. Why would someone say they are pure evil?

> BCNF addresses a shortcoming of 3NF. 3NF designs can have
> anomalies if there are functional dependencies where the
> determinants are not candidate keys. BCNF closes this
> loophole.

This may just further place my ignorace out on display, but wouldn't the WHERE clause in your SQL query address this?

> Whichever candidate key is chosen as the primary key, the
> table will be in 3NF (I'll let you verify this for yourself).
> However, it's not in BCNF because teacher is a determinant
> but not a candidate key. An example anomaly that can result
> is when we delete the row (Jones, Physics, Brown) -- we lose
> the fact that Brown teaches Physics, which is a deletion
> anomaly.

YIKES! I though this meant it was NOT 3nf. Therefore, I would have moved teacher into a new table, added the evil auto-incremented primary key and created this:

           CLASS
 student subject teacherID

• ------- ------- Smith Math 1 Smith Physics 2 Jones Math 1 Jones Physics 3

           TEACHER
 teacherID name

• ------- 1 White 2 Green 3 Brown

Am I missing the concept completely? If so, please slap me up side the head and send me somewhere that and idiot can go to learn more about this.

Thanks very much for your reply.
Seth

"Larry Coon" <larry_at_assist.org> wrote in message news:3DD95934.7C4A_at_assist.org...
> Seth Brewer wrote:
>
> > Hi, I've been putting together some relational DBs for use in small
> > websites. Some of these DBs even got kinda complex. After I built a few
and
> > implemented successful applications driven by them, I began thinking I
was
> > the man. Then I found this newsgroup.
> >
> > So, please excuse my ignorance, but I'm seeing all these recent posts
about
> > BCNF and 3nf, and I'm all confused. My question is: why BCNF? I thought
3nf
> > (or 4nf, or 5nf) was the ultimate goal. I've been assigning
auto-increment
> > primary keys like crazy in every table I've created and using them for
all
> > relationships.
>
> This isn't necessarily a good thing. Some will argue that
> auto-increments are pure evil. I'm much more moderate about
> it, but I definitely wouldn't use them "like crazy."
>
> > What's the practical (or real-world) application of BCNF over
> > 3nf?
>
> BCNF addresses a shortcoming of 3NF. 3NF designs can have
> anomalies if there are functional dependencies where the
> determinants are not candidate keys. BCNF closes this
> loophole.
>
> You can get a design that's 3NF but not BCNF when a table:
> 1. Has multiple candidate keys;
> 2. Has composite candidate keys; AND
> 3. The candidate keys have attributes in common.
>
> A common example is something like:
>
> CLASS
> student subject teacher
> ------- ------- -------
> Smith Math White
> Smith Physics Green
> Jones Math White
> Jones Physics Brown
>
> The dependencies are as follows:
>
> 1. For each subject, each student in that subject is taught
> by only one teacher (student + subject --> teacher)
>
> 2. Each teacher teaches only one subject (teacher --> subject)
>
> 3. Each subject may be taught by many teachers.
>
> The candidate keys are therefore:
> student + subject
> student + teacher
>
> Whichever candidate key is chosen as the primary key, the
> table will be in 3NF (I'll let you verify this for yourself).
> However, it's not in BCNF because teacher is a determinant
> but not a candidate key. An example anomaly that can result
> is when we delete the row (Jones, Physics, Brown) -- we lose
> the fact that Brown teaches Physics, which is a deletion
> anomaly.
>
>
> Larry Coon
> University of California
> larry_at_assist.org
> and lmcoon_at_home.com
Received on Mon Nov 18 2002 - 23:23:16 CET