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

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

Yeah, I was wondering about that based on his responses to other posts I've seen. I just thought he didn't see me as someone worth wasting his time on.

> 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?

I know that was defined in the beginning, but I have found myself in the postion where a client has assured me something like "We will never have a teacher teaching more than one subject." Then, a week later that very same person says "I was trying to enter Prof. Jones as a Math teacher and a Science teacher, but I keep getting some kind of error." This is usually just after the database has been populated with a few hundred thousand records or so and hours have been spent on front-end coding.

I've been doing some extensive searching on google and read a whole lot of stuff. One thing I've noticed which I find interesting is that nearly every article I've found relies heavily on IDENTITY columns in their examples. It's seems that if Celko's statement...

"Then along comes the relational model. It is based on sets; a set is a completed whole, without any ordering to it. No sequences! Very abstract! Programmers did not know how to cope, so the vendors exposed the physical implementation [IDENTITY column] and called these things "features" and locked their products to particular architectures."

...is true (and I have absolutly no reason to even sugest that it isn't) we seem to have gotten to a state where even if the IDENTITY column could be physically removed from [insert common dbms here], the DBA at large would be lost without it.

So this is where I am after all this:

1. I have to buy a book. Probably one by C.J. Date(?) which includes a whole lot about BCNF, normalization algorythms, and decomposition.
2. I'll try to avoid the use of IDENTITY columns whenever and wherever I can.
3. I'll hang out in this newsgroup a lot.
4. I'll buy more coffee.

Seth

"Larry Coon" <larry_at_assist.org> wrote in message news:3DDD71C0.2E73_at_assist.org...
> 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 Fri Nov 22 2002 - 16:13:32 CET