Re: Newbie question about db normalization theory: redundant keys OK?

"raylopez99" <raylopez99_at_yahoo.com> wrote in message news:74f8b49b-95d3-4475-8d17-d156e41e2625_at_e25g2000prg.googlegroups.com...
> On Dec 14, 5:07 am, "David Cressey" <cresse..._at_verizon.net> wrote:

> > I never claimed that star schemas would be free from UPDATE anomalies.
I am
> > not sure of the case you outline above, but I most certainly can come
up
> > with update problems that affect star schemas, but do not affect fully
> > normalized databases. My claims of star schema have to do with ease of
> > access, not ease of update. And the "ease" I'm talking about is
largely a
> > matter of perceived difference rather than logical difference.
> >
>
I need to correct myself here. When I wrote the above, I had forgotten that star schemas usually only get new data INSERTed into them, and never get any UPDATES in the normal course of things. Even so, you can run into INSERT anomalies with a star schema.

> Yes, and even the textbook I was looking at by Carli Watson (quite
> good) on C# database programming has a simple example that shows what
> I had in mind as a recursive UPDATE as difficult is not difficult at
> all, as long as no key is "stranded" (i.e. dB normalized) so
> "synching" is not a problem.
>
> > 1NF, 2NF, and 3NF are not the end of the story. In addition, there
are
> > BCNF, 4NF, 5NF, and a final normal form called domain-key normal form.
> > I've een references to 6NF in this newsgroup. As far as a can tell, 6NF
is
> > the same thing as domain-key normal form.
> >
>
> OK, I wish I had a cheat sheet with examples for these configurations,
> just to satisfy my curiosity, but I'll keep them in mind.
>
> >

I have been corrected by another participant. 6NF is not the same as DKNF. I read the definition of 6NF over in Wikipedia. I have to admit it baffled me.

Someone else questioned my categorization of DKNF as "final". What I meant was this: there are no normal forms more restrictive than DKNF. This has been proven, although I have not seen the proof, and maybe wouldn't be able to follow it if I did. The other participant pointed out that further table decomposition is possible. That's true, but that doesn't mean that the resulting schema is of a higher normal form.

>
> > > * as a further practical consideration, one dB I use, personally more
> > > than anything else, is Access, which doesn't have compound keys to
> > > begin with
> >
> > I beg to differ. MS Access has supported compound keys since Access 97,
and
> > maybe earlier. (I'm just coming up to speed on Access myself, having
used
> > more classical SQL products.) If you buy the design of either the
> > "Northwind" database, or any of the databases that the wizards can
create
> > for you, you are going to learn several unfortunate design habits.
> >
> > The absence of compound keys is just one of them.
>
> OK. I am using MS Access 2003 but will update to 2007 to get rid of
> the annoying nag screen in Vista (apparently 2003 version does stuff
> that gives Vista security fits). I'm wondering outloud whether MS SQL
> Server and Access are interchangeable, I suppose they are (you can
> import the latter into the former).

MS SQL Server and MS Access have quite different origins. You need to be quite careful in regarding them as interchangeable. MS does have an interest in facilitating that interchange, but MS has other intrests that may conflict with giving you what you want.

> Also I'll keep a lookout for
> compound key, but since I'm in love with GUIDs as Primary Keys, and
> also the Long Integer seems to do a good job at generating a primary
> key, at this newbie stage I'm not in a fit to rush off and try a
> compound key, maybe later when I get a bit more experienced.
>

An example of a compound key. An enrollment system for courses, students, and enrollments.
An enrollment might be called a student-course. In the MS design pattern, they will create keys for each table:

StudentId for Students,
CourseID for Courses,
and StudentCourseID for StudentCourses.

Lets accept for now that StudentID and CourseID are good ways to identify students and courses. Other participants will object, if the past is any guide, but you can analyze their objections for yourself.

I claim, at this point, that StudentCourseID as a primary key for the third table, the StdentCourses table, is a bad idea. A better idea is to let StudentID and CourseID be a compound primary key for StudentCourses.

These two columns will be in the database you get from the wizard anyway. They have to be there to serve as foreign keys, relating the row in StudentCourses to parent rows in Students and Courses. There is little to be lost, and much to be gained, by making these two column the compound primary key of this table.

> RL
Received on Fri Dec 14 2007 - 20:49:03 CET