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

"raylopez99" <raylopez99_at_yahoo.com> wrote in message news: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

I'm not sure how what follows applies to MS Access, but there can certainly be two references between one table and another.

The simplest case I can come up with is the "Bill of Materials" case.

create table PARTS

    (PartNo Integer primary key,
      PartDesc Char (40));

create table COMPONENTS

    (CompositePartNo Integer references PARTS.PartNo,

      ComponentPartnentNo  Integer references PARTS.PartNo,
      Quantity integer,
      primary key (CompositePartNo, ComponentPartNo));

I left out a whole lot of other columns that don't pertain. The Quantity column is there for the situation where a given part contains more than one of the same component , like a car has four wheels. This simple design can accomodate any number of levels parts that contain parts that in turn contain yet other parts, etc..

Here there will be two lines in the relationship window between PARTS and COMPONENTS. I'm not sure how this plays out in MS Access. In particular, I'm not sure how it figures out the natural joins. You may end up having to erase some of the lines in the query design window. Received on Sat Dec 15 2007 - 14:30:22 CET