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

Seth Brewer wrote:

> O.K. I think I'm begining to see the light. Thanks for sticking with me! I
> thought I might be mixing up concepts! I'm going to start watching for this
> and using it in practice when I recognize it.
>
> Here is the way I would have approached the data we have defined. If you are
> a believer in the evil of identies it is a nightmare,

I think Celko said he was moving, or some such, otherwise I'm sure you'd be getting a lecture from him about now.

> but I believe it to be
> 3nf and BCNF (there are no overlapping CKs). I will warrent that it makes
> things more complex and scripting querys might be a little scary, but I feel
> more comfortable with it because it eliminates the possibilty of
> encountering two teachers with the same first and last name. I don't see any
> way to define PKs and CKs within this and eliminate any of the identity
> columns. Of course if the teachers and students had SSNs in their tuples
> then we could eliminate the identity field in those tables (am I anywhere
> near getting it?)
>
> TEACHERS
> TchID name
> ---- ----
> 1 White
> 2 Green
> 3 Brown
>
> STUDENTS
> StuID name
> ---- ----
> 1 Smith
> 2 Jones
>
> SUBJECTS
> SubID name
> ---- ----
> 1 Math
> 2 Physics
>
> JOIN_TEACHERS_SUBJECTS
> TSID TchID SubID
> ---- ---- ----
> 1 1 1
> 2 2 2
> 3 3 2

This is the kind of place I wouldn't use a surrogate key. You already know that a teacher teaches only one subject, so TchID is a CK. Why add another surrogate on top of this?

> JOIN_STUDENTS
> StuID TSID
> ---- ----
> 1 1
> 1 2
> 2 1
> 2 3

And therfore this table would become students-teachers, or (StuID, TchID).

> Again, thanks so much Larry, this has been very enlightening.

No problem.

Larry Coon
University of California
larry_at_assist.org
and lmcoon_at_home.com Received on Thu Nov 21 2002 - 17:52:32 CST