Re: Access as a RDBMS--why the multiple relationships?

From: David Cressey <>
Date: Sat, 15 Dec 2007 13:40:24 GMT
Message-ID: <cPQ8j.80$Vg1.60_at_trndny04>

"Bob Badour" <> wrote in message news:4762f9cb$0$5297$
> raylopez99 wrote:
> > OK, this is about my fifth day doing databases, and I've read about
> > 1NF, 2NF, 3NF here:,
> > and I have a crappy book by a guy named Louis Davidson (APress), full
> > of typos, bloated, and light on theory, but here's my question
> > concerning Microsoft Access.
> >
> > I notice in Microsoft Access the relationship chart can, unlike most
> > textbooks, have TWO, not just one, relationship arrows between
> > tables. But I think (and I just want confirmation of this) that one
> > of these two relationship arrows is bogus, and more like a query than
> > a true relationship.
> >
> > Here goes:
> >
> > relationship arrow one (sorry I can't do the SQL statement thing from
> > my head, like some of you can, so I'll use prose instead):
> >
> > entity TABLE A has Primary Key PKA that migrates to entity TABLE B as
> > a (manditory, and non-exclusive, but doesn't matter) Foreign Key FKB.
> > In the "relationship diagram" (Access likes to use diagrams), this is
> > properly shows as a 1 to infinity symbol, no problem, this is also in
> > my textbook.
> >
> > But I also using Access can set up ANOTHER SECOND?! relationship
> > comprising a non-primary, non-key, non-unique field in TABLE A with a
> > non-unique field in TABLE B. In the relationship diagram this shows
> > up as "indeterminite" (no infinite or numbered symbols) and further
> > you cannot check the box for "enforce referential integrity" because
> > you get the error message (which makes sense) of "No unique index
> > found for the referenced field of the primary table" (since the
> > referenced field is not a primary key, or any kind of key).
> >
> > I just want confirmation that the SECOND relationship above is simply
> > a bogus construct of Access, akin to a query constraint of some sort,
> > and not really a 'relationship' as defined by RDBMS theory.
> >
> > RL
> RDBMS doesn't really define 'relationship'. It's true Codd mentioned the
> name in an early paper, but I doubt his use of the word in any way
> resembles the crap that MSFT has been feeding you.

When Ray mentions the "Relationship Window", he's talking about a specific tool that Access
offers the designer. The tool basically allows foreign key references to be catalogued for later use by Access. Foreign key references can have enforced referential integrity but this is not mandatory. There is a check box.

So what are foreign key references used for when referential integrity is not enforced?

They are used by MS Access for figuring out the "on" conditions for INNER JOINS. The typical MS Access user doesn't even speak SQL, never mind relational speak. The graphical interface takes care of all of that.

I have mixed feelings about the above features. On the one hand, it gets the newbie off the starting gate relatively quickly, and gets him to the point where he can begin to appreciate the power of joins without having to learn so much up front. On the other hand, it makes the whole field of database design and application development look easier than it really is. When a person reaches the upper limits of what the GUIs and the wizards can do for him, he's likely to "hit the wall" travelling at a fairly high speed.

> Relational is not named for referential integrity. It is named for
> relations as in those things you may have studied in a math course on
> functions and relations.
Received on Sat Dec 15 2007 - 14:40:24 CET

Original text of this message