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

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 - 22:18:44 CET