OK, this is about my fifth day doing databases, and I've read about
1NF, 2NF, 3NF here: http://www.utexas.edu/its-archive/wi...ng/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

"raylopez99" <raylopez99 (AT) yahoo (DOT) com> wrote

A relationship in an RDBMS is represented in only one way: as values within
tuples within relations. That is all relational theory has to say about it.

Access is an application development tool and file sharing program that
incorporates a poor imitation of a SQL (not relational) data model. Any
pictures it draws have little to do with relational theory and I don't think
you should expect to learn anything useful from them. A picture of a thing
is not the same as a thing.

--
David Portas

raylopez99 schreef:
[snip]

I think you have either misread/misinterpreted the textbook or have
the wrong text book. I don't remember Access that well - does it
really say "relationships"? Anyway the arrows depict foreign keys.
There is nothing wrong with having more than one arrow between tables.





migrate?


You really should start reading.

You are making another serious beginners mistake: mixing terms from
different realms so you end up with an incoherent mess before you get
to your real question.

--
What you see depends on where you stand.

raylopez99 wrote:

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.

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.

"raylopez99" <raylopez99 (AT) yahoo (DOT) com> wrote

My 2 cents. In my experience, when you have more than one relationship among
tables you are really describing roles. For example: Professor tutors
Student and Professor advises Student, where tutor and advisor are roles.

Evan

Evan Keel schreef:

2 cents more. You just do not run into this
non-problem when you think of roles.
You are describing roles a lot of the time, period.
That is, not only when you have more than one relationship.

http://orm.net


--
What you see depends on where you stand.

"raylopez99" <raylopez99 (AT) yahoo (DOT) com> wrote

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.

"Bob Badour" <bbadour (AT) pei (DOT) sympatico.ca> wrote

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.

David Cressey wrote:

Yes, I assumed something like that. My response was to his statement "a
'relationship' as defined by RDBMS theory." If all he has it MSFT as an
information source, he has no doubt been fed the bullshit ignorance that
relational databases are named for referential integrity constraints.

That's a misconception MSFT promotes to sell shit to the unsuspecting,
and it's a misconception that severely impedes learning.

The sooner he understands what relations are, the sooner he will develop
a correct and empowered conceptual model of what is going on.

On Dec 15, 5:40 am, "David Cressey" <cresse... (AT) verizon (DOT) net> wrote:

Yes, you're right Dave. THat's what it is, and now I see it's OK to
have two such arrows, no big deal.

RL