Access as a RDBMS--why the multiple relationships?

From: raylopez99 <raylopez99_at_yahoo.com>
Date: Fri, 14 Dec 2007 12:59:02 -0800 (PST)
Message-ID: <07e02bdb-86fc-4c17-a52b-fc2dbeb52b69_at_e25g2000prg.googlegroups.com>



OK, this is about my fifth day doing databases, and I've read about 1NF, 2NF, 3NF here: http://www.utexas.edu/its-archive/windows/database/datamodeling/rm/rm7.html, 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 Received on Fri Dec 14 2007 - 21:59:02 CET

Original text of this message