I frequently hear that there isn't a commercially available dbms that fully
implements the relational model. Why not? And which product comes closest.
Mike MacSween
49  3125 
Mike MacSween previously wrote: I frequently hear that there isn't a commercially available dbms that
fully
implements the relational model. Why not? And which product comes
closest.
Mike MacSween
Been on holiday Mike?
See this thread - over 100 posts- from a couple of weeks ago. http://groups.google.com/groups?q=g:...&lr=&ie=UTF-8&
oe=UTF-8&selm=GID2b.559%24yJ3.174534%40news.uswest.net
Regards
Peter Russell
"Mike MacSween" <mi******************@btinternet.com> wrote in message
news:3f*********************@pubnews.gradwell.net. .. I frequently hear that there isn't a commercially available dbms that
fully implements the relational model. Why not? And which product comes closest.
My guess is that no one really needs the full relational model. Take MySQL
for example. It lacks some pretty basic relational features, but a lot of
people, including me, find it good enough for many tasks.
So, even if Oracle ( which I guess is the most feature-rich DBMS out there )
isn't really the whole relational model, no one seems to care. Users would
rather have features like clustering, fail-over and load balancing, so
that's what Oracle is implementing.
I'm thinking the relational model is an abstract-academic-mathematical
thing, and we only need to use parts of it to get some value out of it.
Steve Lefevre
On Thu, 18 Sep 2003 19:37:36 +0100 in comp.databases.ms-access, "Mike
MacSween" <mi******************@btinternet.com> wrote: The main thrust in that thread that actually had anything meaningful to do
with a product 'being' relational or not was to do with allowing/not
allowing duplicate rows and allowing nulls. Obviously if there isn't a
primary key (or at least a candidate key) it's not a relation. I don't see
the problem with nulls. I'm not aware that they are somehow 'banned' from
the relational model. After all, in a relation heading 'allowed null' is the
default for a field isn't it?
Anyway, I was wondering if anybody had anything more concrete than those 2
old chestnuts as to why there is no such thing as a truly relational dbms.
Well according to Bob, to be a rdbms it has to enforce the relational
rules, there are two in the (I think, long time since I looked) first
normal form that IMHO would be impossible for a dbms to enforce,
namely repeating groups and the storage or non atomic data.
Anyway Bob blew it when he advocated the use of a zero length string
in Address2 as this was clearly a repeating group (and usually non
atomic) and was thus not relational, I would have pulled him up on it
but there was no point since he was unable to answer many questions
and just resorted to insulting people when he couldn't think of an
answer or a smart-ass reply.
IMHO a database is relational if *you* make it so, regardless of the
dbms that manages it, if *you* have put the rules in and followed the
relational model, whether in Access, SQL Server, Oracle or a home
grown system then your database is relational.
Whether or not the dbms enforces that model is not important, it's not
the dbms' job to design the database. There are degrees to which a
dbms will enforce or even support such concepts, e.g. DataEase had
relationships, later versions had a laughable attempt at RI on cascade
deletes with a choice of delete the childen, disallow the delete, set
the foreign key to null or do nothing, obviously the last two choices
are as much use as a fishnet condom but a user could skip around the
first two by hitting shift+F1 then deleting.
If you take a line of dbmss in varying degrees of relational support
from none at all, through the pathetic like DataEase, to the better
like Jet and all the way up to the most uptight esoteric system and
was asked to draw a line that said all those to the left are
non-relational, those to the right are relational, where would you
draw that line? Probably in a different place to someone else who was
asked to do the same.
If Codd hadn't died recently then perhaps he could have posted
something in that thread that would have shut the lot of us up (apart
from Bob who would probably have bad-mouthed him) but I suppose we'll
never hear it straight from the horse's mouth now.
--
A)bort, R)etry, I)nfluence with large hammer.
--
A)bort, R)etry, I)nfluence with large hammer.
"Mike MacSween" <mi******************@btinternet.com> wrote in message
news:3f*********************@pubnews.gradwell.net. .. I frequently hear that there isn't a commercially available dbms that
fully implements the relational model. Why not?
Insufficient demand due to consumer ignorance.
And which product comes closest.
I notice you included comp.databases.ms-access in the cross-post. Access is
an end-user query tool making the issue of relational fidelity irrelevant. I
changed the follow-up to just include comp.databases.
I have never performed a comprehensive survey so please forgive me if I omit
any important products. Among the SQL dbmses, FirstSQL has the greatest
relational fidelity. Alphora's Dataphor purports to realize a valid D
according to Date's and Darwen's _The Third Manifesto_.
"Steve Lefevre" <le********@osu.edu> wrote in message
news:bk**********@charm.magnus.acs.ohio-state.edu...
"Mike MacSween" <mi******************@btinternet.com> wrote in message
news:3f*********************@pubnews.gradwell.net. .. I frequently hear that there isn't a commercially available dbms that fully implements the relational model. Why not? And which product comes
closest.
My guess is that no one really needs the full relational model.
The ignorant are forced to guess because they lack the tools to know. They
generally guess wrong.
I'm thinking the relational model is an abstract-academic-mathematical
thing, and we only need to use parts of it to get some value out of it.
No doubt, this is why you remain obstinately ignorant of the foundational
knowledge of your craft. In real professions, they jail people for
practicing without such knowledge.
"Bob Badour" <bb*****@golden.net> wrote in news:CHvab.230$uU1.20498561
@mantis.golden.net: The ignorant are forced to guess because they lack the tools to know. They
generally guess wrong.
Please, tell us of your outstanding scholarly achievements.
--
Lyle
What I really meant was, why do people say that no commercially available
product is fully relational? In what ways are the things we can buy not
relational.
Yours, Mike MacSween
"Steve Lefevre" <le********@osu.edu> wrote in message
news:bk**********@charm.magnus.acs.ohio-state.edu...
"Mike MacSween" <mi******************@btinternet.com> wrote in message
news:3f*********************@pubnews.gradwell.net. .. I frequently hear that there isn't a commercially available dbms that fully implements the relational model. Why not? And which product comes
closest.
My guess is that no one really needs the full relational model. Take MySQL
for example. It lacks some pretty basic relational features, but a lot of
people, including me, find it good enough for many tasks.
So, even if Oracle ( which I guess is the most feature-rich DBMS out
there ) isn't really the whole relational model, no one seems to care. Users would
rather have features like clustering, fail-over and load balancing, so
that's what Oracle is implementing.
I'm thinking the relational model is an abstract-academic-mathematical
thing, and we only need to use parts of it to get some value out of it.
Steve Lefevre
si******@besty.org.uk (Trevor Best) wrote in
<rs********************************@4ax.com>: Whether or not the dbms enforces that model is not important, it's
not the dbms' job to design the database. There are degrees to
which a dbms will enforce or even support such concepts, e.g.
DataEase had relationships, later versions had a laughable attempt
at RI on cascade deletes with a choice of delete the childen,
disallow the delete, set the foreign key to null or do nothing,
obviously the last two choices are as much use as a fishnet condom
but a user could skip around the first two by hitting shift+F1
then deleting.
SET TO NULL on delete is actually useful.
Say you have two tables, Person and Company. In the Person field is
a CompanyID foreign key that points to the employer. Say a company,
oh, how about, Enron, has CompanyID 1234 and there are 1,000 people
who are employed there. Say Enron goes bankrupt and ceases to
exist, so you delete Enron from the company table. That means all
the people with CompanyID 1234 are unemployed, so setting their
CompanyID to Null is exactly the right thing to do.
Now, that's an oversimplified example, as with the entities
described you're much more likely to want to preserve more data
than that. You'd probably implement as a table that is a
many-to-many join between Person and Company. You probably won't
use PersonID + CompanyID as primary key, because you'll probably
want to allow for someone to leave a company and then return later,
perhaps after having another job. And I can't think of a valid
applicaton of SET TO NULL in that schema.
But the point is: in any case where the FK is not required, and you
are allowed to delete the parent record, there is possibly a real
use for SET TO NULL.
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
On Fri, 19 Sep 2003 17:40:25 GMT in comp.databases.ms-access, dX********@bway.net (David W. Fenton) wrote: si******@besty.org.uk (Trevor Best) wrote in
<rs********************************@4ax.com>:
SET TO NULL on delete is actually useful.
Say you have two tables, Person and Company. In the Person field is
a CompanyID foreign key that points to the employer. Say a company,
oh, how about, Enron, has CompanyID 1234 and there are 1,000 people
who are employed there. Say Enron goes bankrupt and ceases to
exist, so you delete Enron from the company table. That means all
the people with CompanyID 1234 are unemployed, so setting their
CompanyID to Null is exactly the right thing to do.
Not really, if you were interested at all in these unemployed
layabouts <g> then you'd have a header reacord for say "DSS" (or
whatever you call it in your country) and move those people to there
before deleting the company. Whether you did nothing or set to null
you'd still orphan the records, they'll most likely not come out
anywhere on any reports, etc.
Now, that's an oversimplified example, as with the entities
described you're much more likely to want to preserve more data
than that. You'd probably implement as a table that is a
many-to-many join between Person and Company. You probably won't
use PersonID + CompanyID as primary key, because you'll probably
want to allow for someone to leave a company and then return later,
perhaps after having another job. And I can't think of a valid
applicaton of SET TO NULL in that schema.
That's a possibility, good for moonlighters too :-)
But the point is: in any case where the FK is not required, and you
are allowed to delete the parent record, there is possibly a real
use for SET TO NULL.
Again I'd disagree, if you really wanted to set the FKs to null then
do it in code, I couldn't think of a reason why you would want to do
that *every* time you deleted a record. It just looked like a feature
that no-one asked for.
--
A)bort, R)etry, I)nfluence with large hammer.
"Mike MacSween" <mi******************@btinternet.com> wrote in message
news:3f*********************@pubnews.gradwell.net. .. I frequently hear that there isn't a commercially available dbms that
fully implements the relational model. Why not? And which product comes closest.
Mike MacSween
You're a moron, MacSween. You never hear anything. Get off the air.
"Mike MacSween" <mi******************@btinternet.com> wrote in message
news:3f*********************@pubnews.gradwell.net. .. "David W. Fenton" <dX********@bway.net> wrote in message
news:93*********************@24.168.128.74... si******@besty.org.uk (Trevor Best) wrote in
<rs********************************@4ax.com>:
SET TO NULL on delete is actually useful.
Off the top of my head I can't think many situations where allowed null in a
foreign key would be a good idea. At least not in one to many, and
especially in the ON DELETE of the record in the one table. But I'm sure
there are some.
I don't know how useful it is either but I can tell you that Universal Database for
the IBM AS400 has this option when a parent is deleted, so it's not restricted to
"Mickey Mouse" DBMS.
"XMVP" <ac***********@hotmail.com> wrote: You're a moron, *******. You never hear anything. Get off the air.
Don P Mellon
Go away. Get lost. We don't want you here.
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm
"XMVP" <ac***********@hotmail.com> wrote in message
news:cf******************************@news.teranew s.com...
"Mike MacSween" <mi******************@btinternet.com> wrote in message
news:3f*********************@pubnews.gradwell.net. .. I frequently hear that there isn't a commercially available dbms that
fully implements the relational model. Why not? And which product comes
closest.
Mike MacSween
You're a moron, MacSween. You never hear anything. Get off the air.
Come back to hassle us eh? Marsha's time of the month I guess.
On Fri, 19 Sep 2003 14:37:56 -0500 in comp.databases.ms-access, "Rick
Brandt" <RB*****@Hunter.Com> wrote: "Mike MacSween" <mi******************@btinternet.com> wrote in message
news:3f*********************@pubnews.gradwell.net ... "David W. Fenton" <dX********@bway.net> wrote in message
news:93*********************@24.168.128.74... > si******@besty.org.uk (Trevor Best) wrote in
> <rs********************************@4ax.com>:
> SET TO NULL on delete is actually useful.
Off the top of my head I can't think many situations where allowed null in a
foreign key would be a good idea. At least not in one to many, and
especially in the ON DELETE of the record in the one table. But I'm sure
there are some.
I don't know how useful it is either but I can tell you that Universal Database for
the IBM AS400 has this option when a parent is deleted, so it's not restricted to
"Mickey Mouse" DBMS.
I don't think a FK should ever be null (or at least most of the time),
I think even Bob in the other group would agree with me for once. The
schema could be better designed, e.g. if a relation was optionally
related to another relation then a link table would do it as David
alluded to before however I can think of an exception I would use and
have indeed used. In a disk catalogue I have tblDir with DirID,
DirName & ParentDirID utilising a self join, the root dir has no
parent and is thus null but then my model follows real life, if you
delete a Dir then all it's children wil go too, never set to null. Of
course I could have designed this using a link table and never had a
null FK but I was lazy :-)
Most people I know (including me) who create databases in Jet or SQL
Server generally go for 3rd normal form and end up with about 90% fit,
e.g. if using a modeller it may create one address table and a link
table to entities such as vendor, client, person, etc, I find that a
PITA and prefer to de-normalise at that point.
--
A)bort, R)etry, I)nfluence with large hammer.
"Tony Toews" <tt****@telusplanet.net> wrote in message
news:2l********************************@4ax.com... "XMVP" <ac***********@hotmail.com> wrote:
You're a moron, *******. You never hear anything. Get off the air.
Don P Mellon
Go away. Get lost. We don't want you here.
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Please leave this trash on the ms-access group.
"Mike MacSween" <mi******************@btinternet.com> wrote: Off the top of my head I can't think many situations where allowed null in a
foreign key would be a good idea. At least not in one to many, and
especially in the ON DELETE of the record in the one table. But I'm sure
there are some.
I would think this situation exists many times. It certainly does in many of my
apps. One example in recent apps include custom manufactured items some of which
belong to larger modules and some of which don't. Those that don't don't have a
module number.
Especially when the information just isn't available right then but will be filled in
later. Which happens in one of my apps where the time from receipt of engineering
drawing to creating a shop drawing to shipping the custom manufactored item out the
door can take many months.
Another example I can think of would include a person registering at an emergency
room who doesn't have a family doctor.
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm
On Fri, 19 Sep 2003 21:28:11 GMT in comp.databases.ms-access, Tony
Toews <tt****@telusplanet.net> wrote: "Mike MacSween" <mi******************@btinternet.com> wrote:
Off the top of my head I can't think many situations where allowed null in a
foreign key would be a good idea. At least not in one to many, and
especially in the ON DELETE of the record in the one table. But I'm sure
there are some.
I would think this situation exists many times. It certainly does in many of my
apps. One example in recent apps include custom manufactured items some of which
belong to larger modules and some of which don't. Those that don't don't have a
module number.
Especially when the information just isn't available right then but will be filled in
later. Which happens in one of my apps where the time from receipt of engineering
drawing to creating a shop drawing to shipping the custom manufactored item out the
door can take many months.
Another example I can think of would include a person registering at an emergency
room who doesn't have a family doctor.
But you wouldn't want your dbms to null out the FKs if you delete
something. Deleting a module may mean you are getting rid of the
componants as well, maybe not, you would control that through your
front-end. Likewise if you delete a doctor, the patients will need
another doctor.
OK I can see more instances where a null FK may occur but I still
wouldn't want a dbms to blindly update records willy nilly.
--
A)bort, R)etry, I)nfluence with large hammer. si******@besty.org.uk (Trevor Best) wrote in
<qm********************************@4ax.com>: On Fri, 19 Sep 2003 17:40:25 GMT in comp.databases.ms-access,
dX********@bway.net (David W. Fenton) wrote:
si******@besty.org.uk (Trevor Best) wrote in
<rs********************************@4ax.com>:
SET TO NULL on delete is actually useful.
Say you have two tables, Person and Company. In the Person field
is a CompanyID foreign key that points to the employer. Say a
company, oh, how about, Enron, has CompanyID 1234 and there are
1,000 people who are employed there. Say Enron goes bankrupt and
ceases to exist, so you delete Enron from the company table. That
means all the people with CompanyID 1234 are unemployed, so
setting their CompanyID to Null is exactly the right thing to do.
Not really, if you were interested at all in these unemployed
layabouts <g> then you'd have a header reacord for say "DSS" (or
whatever you call it in your country) and move those people to
there before deleting the company. Whether you did nothing or set
to null you'd still orphan the records, they'll most likely not
come out anywhere on any reports, etc.
I don't think that's a valid choice, because that is *not* their
employer. You'd be creating a fake record to group people together
that was not actually their employer. That's a workaround, but it's
bad schema design, in my opinion.
And it doesn't work that way in the US, in any event, as being
unemployment does not entititle you to anything unless you apply
for it, and only then when you qualify for it. Now, that's an oversimplified example, as with the entities
described you're much more likely to want to preserve more data
than that. You'd probably implement as a table that is a
many-to-many join between Person and Company. You probably won't
use PersonID + CompanyID as primary key, because you'll probably
want to allow for someone to leave a company and then return
later, perhaps after having another job. And I can't think of a
valid applicaton of SET TO NULL in that schema.
That's a possibility, good for moonlighters too :-)
But the point is: in any case where the FK is not required, and
you are allowed to delete the parent record, there is possibly a
real use for SET TO NULL.
Again I'd disagree, if you really wanted to set the FKs to null
then do it in code, I couldn't think of a reason why you would
want to do that *every* time you deleted a record. It just looked
like a feature that no-one asked for.
It's a feature that Jet has not had, but that many server databases
do, in fact, have. I learned about it in a meta-discussion on
databases with a Sybase guy about 2 years ago. I was flabbergasted
at the time, and it took some explaining on his part for me to
grasp why it would be useful.
And your argument could just as easily be applied to CASCADE
DELETE.
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc mi******************@btinternet.com (Mike MacSween) wrote in
<3f*********************@pubnews.gradwell.net>: "David W. Fenton" <dX********@bway.net> wrote in message
news:93*********************@24.168.128.74... si******@besty.org.uk (Trevor Best) wrote in
<rs********************************@4ax.com>:
SET TO NULL on delete is actually useful.
Say you have two tables, Person and Company. In the Person field
is a CompanyID foreign key that points to the employer. Say a
company, oh, how about, Enron, has CompanyID 1234 and there are
1,000 people who are employed there. Say Enron goes bankrupt and
ceases to exist, so you delete Enron from the company table.
That means all the people with CompanyID 1234 are unemployed, so
setting their CompanyID to Null is exactly the right thing to
do.
No. A one to many join, Company to Person (assuming the Person
table doesn't actually mean 'Employee') sounds like a really bad
structure.
Criticizing the specifics of my example is not the same as
criticizing the feature the example was designed to illustrate. Now, that's an oversimplified example, as with the entities
described you're much more likely to want to preserve more data
than that. You'd probably implement as a table that is a
many-to-many join between Person and Company. You probably won't
use PersonID + CompanyID as primary key, because you'll probably
want to allow for someone to leave a company and then return
later, perhaps after having another job. And I can't think of a
valid applicaton of SET TO NULL in that schema.
Yes, that's the way to do it. In which case you just delete the
record in the junction table.
I agree that the specific example was a poor example. The basic set
of relationships, however, is valid for any number of entities, and
it is perfectly conceivable that there are entities for which a 1:N
relationship is the correct model, and for which the FK value is
not required (i.e., can be Null) and in which the parent records
are deletable. But the point is: in any case where the FK is not required, and
you are allowed to delete the parent record, there is possibly a
real use for SET TO NULL.
Off the top of my head I can't think many situations where allowed
null in a foreign key would be a good idea. At least not in one to
many, and especially in the ON DELETE of the record in the one
table. But I'm sure there are some.
I have plenty of foreign keys that are not required. I'll be you
do, too.
Mostly, though, I don't want the parent records to be deletable, as
there are often other layers of relationships involved, too.
But there is nothing at all conceptually wrong with SET TO NULL on
deletions. It is really not much different from CASCADE DELETE.
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
bouncer@localhost (Trevor Best) wrote in
<kf********************************@4ax.com>: On Fri, 19 Sep 2003 14:37:56 -0500 in comp.databases.ms-access,
"Rick Brandt" <RB*****@Hunter.Com> wrote:
"Mike MacSween" <mi******************@btinternet.com> wrote in
message news:3f*********************@pubnews.gradwell.net. .. "David W. Fenton" <dX********@bway.net> wrote in message
news:93*********************@24.168.128.74...
> si******@besty.org.uk (Trevor Best) wrote in
> <rs********************************@4ax.com>:
> SET TO NULL on delete is actually useful.
Off the top of my head I can't think many situations where
allowed null in a foreign key would be a good idea. At least
not in one to many, and especially in the ON DELETE of the
record in the one table. But I'm sure there are some.
I don't know how useful it is either but I can tell you that
Universal Database for the IBM AS400 has this option when a
parent is deleted, so it's not restricted to "Mickey Mouse" DBMS.
I don't think a FK should ever be null (or at least most of the
time), . . .
I disagree. The relationship is properly designated 0:N, if I'm
recalling correctly, and is part of long-established relational
design principles. I was taught it by someone who understood SQL
from a set theory point of view, and 0:N is valid.
. . . I think even Bob in the other group would agree with me for
once. The schema could be better designed, e.g. if a relation was
optionally related to another relation then a link table would do
it as David alluded to before however I can think of an exception
I would use and have indeed used. In a disk catalogue I have
tblDir with DirID, DirName & ParentDirID utilising a self join,
the root dir has no parent and is thus null but then my model
follows real life, if you delete a Dir then all it's children wil
go too, never set to null. Of course I could have designed this
using a link table and never had a null FK but I was lazy :-)
This was my argument with the SQL guy who took me to task on this a
couple of years ago, that the 0:N was better modelled in an
intermediate table. However, given that it's valid set theory, I
think it's a perfectly valid (though not essential) feature for
database engines. I would put CASCADE DELETE in the same
classification. I would say that CASCADE UPDATE is the only one
that is worthless.
Most people I know (including me) who create databases in Jet or
SQL Server generally go for 3rd normal form and end up with about
90% fit, e.g. if using a modeller it may create one address table
and a link table to entities such as vendor, client, person, etc,
I find that a PITA and prefer to de-normalise at that point.
I tend to do the same for that kind of entity, because having
multiple parents for one address causes severe UI problems because
it introduces way too many dependencies. It can also lead to
circular relationships which can cause problems with propagating
deletions in a replicated database.
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
bouncer@localhost (Trevor Best) wrote in
<k6********************************@4ax.com>: On Fri, 19 Sep 2003 21:28:11 GMT in comp.databases.ms-access, Tony
Toews <tt****@telusplanet.net> wrote:
"Mike MacSween" <mi******************@btinternet.com> wrote:
Off the top of my head I can't think many situations where
allowed null in a foreign key would be a good idea. At least not
in one to many, and especially in the ON DELETE of the record in
the one table. But I'm sure there are some.
I would think this situation exists many times. It certainly
does in many of my apps. One example in recent apps include
custom manufactured items some of which belong to larger modules
and some of which don't. Those that don't don't have a module
number.
Especially when the information just isn't available right then
but will be filled in later. Which happens in one of my apps
where the time from receipt of engineering drawing to creating a
shop drawing to shipping the custom manufactored item out the
door can take many months.
Another example I can think of would include a person registering
at an emergency room who doesn't have a family doctor.
But you wouldn't want your dbms to null out the FKs if you delete
something. Deleting a module may mean you are getting rid of the
componants as well, maybe not, you would control that through your
front-end. Likewise if you delete a doctor, the patients will need
another doctor.
OK I can see more instances where a null FK may occur but I still
wouldn't want a dbms to blindly update records willy nilly.
It's an option, not a requirement.
I mostly don't use CASCADE DELETE, but I'm glad it's available in
Jet for the few cases where I consider it perfectly valid (the
classic case being the Invoice/Invoice Detail relationship).
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
"XMVP" <ac***********@hotmail.com> wrote
<immaterial what he wrote>
Hey, Don... judging from what he has to say, I think Bob Badour's your
kindred soul. Visit him at the databases.elitist.clique, aka comp.databases,
and give _him_ the benefit of your expertise.
"David W. Fenton" <dX********@bway.net> wrote in message
news:93*********************@24.168.128.74... bouncer@localhost (Trevor Best) wrote in
I disagree. The relationship is properly designated 0:N,
Only if it's designated 0:N. 0:N isn't the same as 1:N is it? It depends on
what participation conditions you've decided on for each end of that
relationship. That symbolic representation (x:x) can't fully express the
meaning of the relationship. If your business rules say that you can't have
an order without a customer for that order then the field order.customerID,
a foreign key, can't be null. And the (harder to implement) other
restriction, you can't have a customer who hasn't placed an order.
recalling correctly, and is part of long-established relational
design principles. I was taught it by someone who understood SQL
from a set theory point of view, and 0:N is valid.
I understand the usual meaning of 1:N as 'one occurence of the entity type
on the left hand side may be associated with _zero_, one or many occurences
of the entity type of the right hand side'. So if the 'set theory' has left
you with a relation heading that includes 'CustomerID not allowed null...
foreign key CustomerID references Customer' then you've got what is
colloquially referred to as a 'one to many'. But that's all it is, a short
hand that we bandy about.
Correct me if I'm wrong (as I know you will!) but what you seem to be saying
is that 1:n is actually 0(or1):n meaning that the entity on the left hand
side _may_ participate in a relationship with an entity on the right hand
side. Sure, that's what it's often taken to mean. A teacher may (or may not,
yet) have some students. A customer may (or may not, yet) have placed some
orders. But that's got _nothing_ to do with whether the foreign key in the
'many' table is allowed null or not. It's got to do with whether the primary
key from the 'one' table occurs _at least once_ as a foreign key in the
table from the 'many' side. And because it's hard to enforce, especially in
Access, 1:n is often taken as meaning 0:n. Think about it, you need a
relation heading which includes 'constraint (project Customer over
CustomerID) difference (project Order over CustomerID) is empty' which can
be expressed in SQL as:
CREATE TABLE Customer
(CustomerID
..
..
CHECK CustomerID IN
(SELECT DISTINCT CustomerID
FROM Order))
Sure, in 'relational theory' foreign keys seem to be, by default, allowed
null. I can't think that I'd often want to allow that in practice though.
However, given that it's valid set theory...
I don't know what 'valid set theory' means. Perhaps you could explain.
Cheers, Mike
"Tony Toews" <tt****@telusplanet.net> wrote in message
news:jr********************************@4ax.com... "Mike MacSween" <mi******************@btinternet.com> wrote:
Off the top of my head I can't think many situations where allowed null
in aforeign key would be a good idea. At least not in one to many, and
especially in the ON DELETE of the record in the one table. But I'm sure
there are some.
I would think this situation exists many times. It certainly does in many
of my apps. One example in recent apps include custom manufactured items some
of which belong to larger modules and some of which don't. Those that don't don't
have a module number.
Good example, but isn't that probably many to many? Though of course I don't
know the app.
Especially when the information just isn't available right then but will
be filled in later. Which happens in one of my apps where the time from receipt of
engineering drawing to creating a shop drawing to shipping the custom manufactored
item out the door can take many months.
Another example I can think of would include a person registering at an
emergency room who doesn't have a family doctor.
Superb example, thank you.
Of course some people advocate a new table to represent 1:n relationships,
in the same way that that's the only way to implement a many to many. I've
never tried it. It means you can avoid null foreign keys.
Yours, Mike MacSween
"David W. Fenton" <dX********@bway.net> wrote in message
news:93*********************@24.168.128.74... Criticizing the specifics of my example is not the same as
criticizing the feature the example was designed to illustrate.
I wasn't criticizing David. But it wasn't the best example was it, as you
went on to point out immediately. I don't 'criticize' people who are
interested in honest discussion, like you.
Yes, that's the way to do it. In which case you just delete the
record in the junction table.
I have plenty of foreign keys that are not required. I'll be you
do, too.
Mostly, though, I don't want the parent records to be deletable, as
there are often other layers of relationships involved, too.
But there is nothing at all conceptually wrong with SET TO NULL on
deletions. It is really not much different from CASCADE DELETE.
Yes, I can see it might be useful. And if I imagine the situations in which
it would be useful I think it actually is different to CASCADE DELETE. You
have a Customer (big company, one table) and CustomerContact (people, many
table). And in the CustomerContact table you hold all sorts of details, name
and the usual but also partner's name, favourite food, hobbies, contact
history (well, another table probably). When the Customer they work for goes
under the fk in CustomerContact is set null, so you can't see them any more,
but then when they start working for a new Customer the history is all
there. Which you may or may not want. Yes, it's a good idea, sometimes.
Yours, Mike MacSween
On Fri, 19 Sep 2003 23:27:39 GMT in comp.databases.ms-access, dX********@bway.net (David W. Fenton) wrote: I mostly don't use CASCADE DELETE, but I'm glad it's available in
Jet for the few cases where I consider it perfectly valid (the
classic case being the Invoice/Invoice Detail relationship).
I use it sparingly, e.g. Supplier -> Supplier Products, but definately
not from Suppleir to Orders.
--
A)bort, R)etry, I)nfluence with large hammer. mi******************@btinternet.com (Mike MacSween) wrote in
<3f*********************@pubnews.gradwell.net>: "David W. Fenton" <dX********@bway.net> wrote in message
news:93*********************@24.168.128.74... bouncer@localhost (Trevor Best) wrote in I disagree. The relationship is properly designated 0:N,
Only if it's designated 0:N. 0:N isn't the same as 1:N is it?
It is conventionally interpreted to mean 0|1:N.
. . . It
depends on what participation conditions you've decided on for
each end of that relationship. That symbolic representation (x:x)
can't fully express the meaning of the relationship. If your
business rules say that you can't have an order without a customer
for that order then the field order.customerID, a foreign key,
can't be null. And the (harder to implement) other restriction,
you can't have a customer who hasn't placed an order.
Well, the difference is 1:N where the FK is required vs. 1:N where
the FK is not required. The latter means that there may be no
paretn record connected to the child record, which is 0:N. My
understanding is that 0:N has been the way of distinguishing the
non-required FK from the required one (1:N), though to me, it's not
something that I worry about being that picky with.
recalling correctly, and is part of long-established relational
design principles. I was taught it by someone who understood SQL
from a set theory point of view, and 0:N is valid.
I understand the usual meaning of 1:N as 'one occurence of the
entity type on the left hand side may be associated with _zero_,
one or many occurences of the entity type of the right hand side'.
So if the 'set theory' has left you with a relation heading that
includes 'CustomerID not allowed null... foreign key CustomerID
references Customer' then you've got what is colloquially referred
to as a 'one to many'. But that's all it is, a short hand that we
bandy about.
Correct me if I'm wrong (as I know you will!) but what you seem to
be saying is that 1:n is actually 0(or1):n meaning that the entity
on the left hand side _may_ participate in a relationship with an
entity on the right hand side. . . .
It depends on whether the FK on the N side is required or not. If
it's required, it's definitely 1:N. If it's not, it's 1|0:N, which
is often notated as 0:N.
. . . Sure, that's what it's often taken
to mean. A teacher may (or may not, yet) have some students. A
customer may (or may not, yet) have placed some orders. But that's
got _nothing_ to do with whether the foreign key in the 'many'
table is allowed null or not. . . .
You're mixing things up. The N means there are many records in the
child table that are related to a certain number of records in the
parent table. N:N means many-to-many, which can only be done in Jet
with a join table (I don't know that there's any other way to do it
in any db, but I'm not broadly experienced). 1:N means that there
can be many child records attached to a parent record. It doesn't
mean that there *are* child records attached to the parent record.
As a validation rule it could be expressed as "PK of tblParent,"
meaning that the value must be drawn from the primary key of a
particular table.
0:N means that there can be orphaned child records not attached to
any parent record because the FK in the child table is not
required. As a validation rule it could be expressed as "Is Null or
PK of tblParent," which means that the field can be empty, but if
it's *not* empty, the only valid values are drawn from the PK of
another table.
In this newsgroup we have tended to use 1:N to mean both 1:N in the
strict sense (FK required) and 0:N (FK not required).
. . . It's got to do with whether the
primary key from the 'one' table occurs _at least once_ as a
foreign key in the table from the 'many' side. . . .
Nope, that's not what 0:N means. The lefthand side refers to the
parent table, the righthand side to the child table. There is no
relation in any db engine that I know of that requires that there
be a child record of the parent. Indeed, it would be impossible to
have such a thing as you can't simultaneously add both records and
have the conditions met at the time of insert in both tables. If
the child record gets inserted before the parent, there's no parent
PK yet. If the parent gets inserted before the child, there's no
child attached to it.
. . . And because it's
hard to enforce, especially in Access, 1:n is often taken as
meaning 0:n. Think about it, you need a relation heading which
includes 'constraint (project Customer over CustomerID) difference
(project Order over CustomerID) is empty' which can be expressed
in SQL as:
CREATE TABLE Customer
(CustomerID
.
.
CHECK CustomerID IN
(SELECT DISTINCT CustomerID
FROM Order))
Sure, in 'relational theory' foreign keys seem to be, by default,
allowed null. I can't think that I'd often want to allow that in
practice though.
The allowance of NULL in the FK is not part of the relationship --
it is a function of whether or not the FK is required or not. However, given that it's valid set theory...
I don't know what 'valid set theory' means. Perhaps you could
explain.
The mathematical field of which SQL is a part is called "set
theory." 0:N is one valid set relationship.
Or so I was told by someone who is an expert in the field (he
teaches it).
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
bouncer@localhost (Trevor Best) wrote in
<7e********************************@4ax.com>: On Fri, 19 Sep 2003 23:27:39 GMT in comp.databases.ms-access,
dX********@bway.net (David W. Fenton) wrote:
I mostly don't use CASCADE DELETE, but I'm glad it's available in
Jet for the few cases where I consider it perfectly valid (the
classic case being the Invoice/Invoice Detail relationship).
I use it sparingly, e.g. Supplier -> Supplier Products, but
definately not from Suppleir to Orders.
Except, don't you have a link from Supplier Products to Inventory?
In that case, you couldn't delete the products without invalidating
your inventory.
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
"David W. Fenton" <dX********@bway.net> wrote in message
news:93*********************@24.168.128.78... mi******************@btinternet.com (Mike MacSween) wrote in
<3f*********************@pubnews.gradwell.net>:
[]
You're probably right. I'll look at it.
The allowance of NULL in the FK is not part of the relationship --
it is a function of whether or not the FK is required or not.
I can't agree with that though. The allowance of NULL in the FK is very
definitely part of the relationship, as it specifies (one of) the
participation conditions, optional or mandatory.
Or so I was told by someone who is an expert in the field (he
teaches it).
Er, that follows does it? <g>
Cheers, Mike
"David W. Fenton" <dX********@bway.net> wrote in message
news:93*********************@24.168.128.78... mi******************@btinternet.com (Mike MacSween) wrote in
Upshot of all this is that saying "1:n" isn't really enough. It doesn't tell
us everything about the relationship. We're left assuming certain things.
That's what E-R models and relational models are for, I guess.
Yours, Mike
On Sat, 20 Sep 2003 17:55:19 GMT in comp.databases.ms-access, dX********@bway.net (David W. Fenton) wrote: bouncer@localhost (Trevor Best) wrote in
<7e********************************@4ax.com>:
On Fri, 19 Sep 2003 23:27:39 GMT in comp.databases.ms-access,
dX********@bway.net (David W. Fenton) wrote:
I mostly don't use CASCADE DELETE, but I'm glad it's available in
Jet for the few cases where I consider it perfectly valid (the
classic case being the Invoice/Invoice Detail relationship).
I use it sparingly, e.g. Supplier -> Supplier Products, but
definately not from Suppleir to Orders.
Except, don't you have a link from Supplier Products to Inventory?
In that case, you couldn't delete the products without invalidating
your inventory.
No, the link goes like:
tblSupplier -1:n- tblSupplierProduct -n:1- tblProduct.
tblSupplierProduct is just a link table and tells what product a
supplier supplies, if you delete the supplier, the link table's
related records should go too.
Product in this case is a product category. The inventory will be
unaffected by the delete, if products were bought and delivered from a
supplier then that supplier would not be able to be deleted.
--
A)bort, R)etry, I)nfluence with large hammer.
Trevor Best <bouncer@localhost> wrote: But you wouldn't want your dbms to null out the FKs if you delete
something.
Agreed. If the client decides, for whatever reason, that there is no need for a
module they are going to have to either remove the module ID from the component table
manually or get me to run a quick update query.
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm
"Larry Linson" <la**********@ntpcug.org> wrote: "XMVP" <ac***********@hotmail.com> wrote
<immaterial what he wrote>
Hey, Don... judging from what he has to say, I think Bob Badour's your
kindred soul. Visit him at the databases.elitist.clique, aka comp.databases,
and give _him_ the benefit of your expertise.
<chuckle>
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm mi******************@btinternet.com (Mike MacSween) wrote in
<3f*********************@pubnews.gradwell.net>: "David W. Fenton" <dX********@bway.net> wrote in message
news:93*********************@24.168.128.78... mi******************@btinternet.com (Mike MacSween) wrote in
<3f*********************@pubnews.gradwell.net>:
[]
You're probably right. I'll look at it.
The allowance of NULL in the FK is not part of the relationship
-- it is a function of whether or not the FK is required or not.
I can't agree with that though. The allowance of NULL in the FK is
very definitely part of the relationship, as it specifies (one of)
the participation conditions, optional or mandatory.
In the human sense, yes, it's part of the relationship. But it is
not a property of the relation in Jet. Or so I was told by someone who is an expert in the field (he
teaches it).
Er, that follows does it? <g>
Not sure what you mean. He's someone whose credibility is very,
very high in my book, as he is very intelligent, writes extremely
well and explains things in a very straightforward fashion. He also
had web-based documentation for all his comments on how it all
worked, and all his documentation backed up his claims about the
situation.
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc mi******************@btinternet.com (Mike MacSween) wrote in
<3f*********************@pubnews.gradwell.net>: "David W. Fenton" <dX********@bway.net> wrote in message
news:93*********************@24.168.128.78... mi******************@btinternet.com (Mike MacSween) wrote in
<3f*********************@pubnews.gradwell.net>:
>Of course some people advocate a new table to represent 1:n
>relationships, in the same way that that's the only way to
>implement a many to many. I've never tried it. It means you can
>avoid null foreign keys.
That's the way to avoid 0:N, also, because the record that would
have a Null FK then simply has no record in the join table.
Er, yes. That's what I was saying. Glad you agree.
It's an obvious different way of modelling the relationship.
However, it increases the number of joins, and that can be a
problem, as I've found that with Jet there tends to be a marked
dropoff in performance when:
1. more than 3 joins must be executed
AND
2. one or more of the joins is not an INNER join.
In this case, to get a list of patients and their physicians, you'd
need two joins LEFT joins. To get a list of the patients for
doctors, though, it could all be done with inner joins.
Now, if you wanted to know the primary physician, though, you'd
need a 3rd join, and then you start getting into tricky stuff.
It's a little tricky form there on, though, because you have to
do things with join table indexes that you're like not
accustomed to doing. For the emergency room example, you'd have
to have a unique index on the PatientID so that only one record
in that table could be attached to a patient. Then you'd need a
non-unique index on the PhysicianID field. You would *not* want
a two-column PK for that table or a two-column unique index, one
or both of which is customary for a many-to-many table.
Of course, what if multiple physicians could be assigned,
[later] Then
you'd need a many-to-many table with PatientID and PhysicianID with a non-unique ID and/or PK on
those two fields.
Usual many to many junction table.
But that doesn't model the PrimaryPhysician attribute.
Then, how to implement the primary physician? At
first it would seem that you could do it with a Boolean field
added to the join table with a unique index on PatientID and the
Primary Boolean field. But that doesn't work, because once you
have a third Physician, you'd have collisions.
It's a completely separate relationship. One to many, physician to
patient. Either with the PK of physician posted as a FK in
patient, or with the the relation for relationship thing above.
I wouldn't do it that way, myself, as the primary physician is not
independent of the patient's physicians. That is, the primary
physician should be one of the doctors in the join table for that
patient. If it's not and you want a list of all the patient's
doctors, you have to do a UNION, which is both uneditable and
something of a performance drain. You could do UNION ALL to speed
it up, but there's nothing in the schema to prohibit the primary
physician being in both tables, so you could end up with
duplicates.
I've not thought about it that hard. The example you've just
quoted is a little complex. But there isnt a problem having 2
relationships between the same 2 tables is there? You'll need some
constraints adding. Probably a physician can't be the primary
physician AND one of the ordinary ones for a particular patient.
But maybe they can. The requirements don't tell us yet.
I think the table attached to the join is the most "correct" way to
model this, as a record cannot be created for a patient who doesn't
have a doctor and it can't be created for a doctor that is not
already associated with a patient. And the unique index on
PatientID insures that you can have only one primary physician per
patient.
In other words, the schema I've described models all the
requirements.
(actually, the one thing it does *not* model is the logical
requirement that if there is only one doctor attached to a patient,
that doctor must be the primary physician; but I don't think that
can be done in any schema without triggers)
Ahh, that's another of your options. Maybe I should read before I
type. Glad we agree!
I don't think we *do* agree, actually. I still think the SET TO
NULL option is usable, though not essential. Of course, I think the
same thing about CASCADE DELETE, though I think it's got more uses
than SET TO NULL.
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc mi******************@btinternet.com (Mike MacSween) wrote in
<3f*********************@pubnews.gradwell.net>: "David W. Fenton" <dX********@bway.net> wrote in message
news:93*********************@24.168.128.86... mi******************@btinternet.com (Mike MacSween) wrote in
<3f*********************@pubnews.gradwell.net>:
>"David W. Fenton" <dX********@bway.net> wrote in message
>news:93*********************@24.168.128.78...
>> mi******************@btinternet.com (Mike MacSween) wrote in
>> <3f*********************@pubnews.gradwell.net>:
>
>[]
>
>You're probably right. I'll look at it.
>
>> The allowance of NULL in the FK is not part of the
>> relationship -- it is a function of whether or not the FK is
>> required or not.
>
>I can't agree with that though. The allowance of NULL in the FK
>is very definitely part of the relationship, as it specifies
>(one of) the participation conditions, optional or mandatory.
In the human sense, yes, it's part of the relationship.
In the relational database sense the participation condition IS
part of the relationship. I assume that's what we're talking about
here. Relational databases built in Access.
I was speaking only of the way Jet stores information about the
relationships.
But it is
not a property of the relation in Jet.
I'm not sure whether you mean that or not. Do you mean relation or
relationship? . . .
In DAO, the index is not part of the relation.
. . . The nullness or not of an attribute in a relation is
a property of the attribute, so I suppose a property of the
relation. Relationships, especially 'one to many', can be
implemented by posting the primary key of one relation as a
foreign key in another relation, so the nullness or not of that
foreign key also tells us something about the relationship.
Yes, but it's not part of the relation objects themselves.
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
What do you mean when you use the word 'relation'?
Mike
mi******************@btinternet.com (Mike MacSween) wrote in
<3f*********************@pubnews.gradwell.net>: What do you mean when you use the word 'relation'?
The Jet structure that defines a relation, as represented in DAO.
There is no mechanism for designating or defining or even simply
indicating indexes as part of the relation definition. Indexes for
a relation are inherited from the fields in the relation itself,
but are not part of the relation definition *as implemented in
Jet*.
Yes, I agree that they are part of the definition of the
*relationship*. But they are not an inherent part of the Jet db
engine's structure that defines the relationship -- they are
secondarily inherited from the fields that participate in the
relation.
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
I meant "relation means table". Do you mean "relation means relationship".
A relationship (the logical, relational model thing) has participation
conditions. Mandatory or optional at each end. Whether Jet keeps a record of
that as a 'property' of some 'relationship' object is up to Jet. I've made a
participation condition mandatory at the 'many' end of the relationship by
making the FK not allowed null.
Yours, Mike MacSween
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:93***************************@24.168.128.86.. . mi******************@btinternet.com (Mike MacSween) wrote in
<3f*********************@pubnews.gradwell.net>:
What do you mean when you use the word 'relation'?
The Jet structure that defines a relation, as represented in DAO.
There is no mechanism for designating or defining or even simply
indicating indexes as part of the relation definition. Indexes for
a relation are inherited from the fields in the relation itself,
but are not part of the relation definition *as implemented in
Jet*.
Yes, I agree that they are part of the definition of the
*relationship*. But they are not an inherent part of the Jet db
engine's structure that defines the relationship -- they are
secondarily inherited from the fields that participate in the
relation.
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Yes, it is my understanding that the "relation" of "Relational Database" is
what we poor, unenlightened Access users call a "table" (and so do most of
the heavy-duty, industrial-strength databases, too). The late Dr. Codd used
"relation" in the context of "table" in his original definitions.
"Mike MacSween" <mi******************@btinternet.com> wrote in message
news:3f*********************@pubnews.gradwell.net. .. I meant "relation means table". Do you mean "relation means relationship".
A relationship (the logical, relational model thing) has participation
conditions. Mandatory or optional at each end. Whether Jet keeps a record
of that as a 'property' of some 'relationship' object is up to Jet. I've made
a participation condition mandatory at the 'many' end of the relationship by
making the FK not allowed null.
Yours, Mike MacSween
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:93***************************@24.168.128.86.. . mi******************@btinternet.com (Mike MacSween) wrote in
<3f*********************@pubnews.gradwell.net>:
What do you mean when you use the word 'relation'?
The Jet structure that defines a relation, as represented in DAO.
There is no mechanism for designating or defining or even simply
indicating indexes as part of the relation definition. Indexes for
a relation are inherited from the fields in the relation itself,
but are not part of the relation definition *as implemented in
Jet*.
Yes, I agree that they are part of the definition of the
*relationship*. But they are not an inherent part of the Jet db
engine's structure that defines the relationship -- they are
secondarily inherited from the fields that participate in the
relation.
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Oh, my, yes. The late Dr. Codd, indeed. Where was that, Larry, where we
last chatted with the late Dr. Codd? Was that at a university tea? Perhaps
Princeton? Or was it at that MIT conference back in '85? Ah, memory fails.
All I can remember is what a fascinating fellow he was and how jolly well
you and he got along.
"Larry Linson" <bo*****@localhost.net> wrote in message
news:UL*****************@nwrddc03.gnilink.net... Yes, it is my understanding that the "relation" of "Relational Database"
is what we poor, unenlightened Access users call a "table" (and so do most of
the heavy-duty, industrial-strength databases, too). The late Dr. Codd
used "relation" in the context of "table" in his original definitions.
"Mike MacSween" <mi******************@btinternet.com> wrote in message
news:3f*********************@pubnews.gradwell.net. .. I meant "relation means table". Do you mean "relation means
relationship".
A relationship (the logical, relational model thing) has participation
conditions. Mandatory or optional at each end. Whether Jet keeps a
record of that as a 'property' of some 'relationship' object is up to Jet. I've
made a participation condition mandatory at the 'many' end of the relationship
by making the FK not allowed null.
Yours, Mike MacSween
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:93***************************@24.168.128.86.. . mi******************@btinternet.com (Mike MacSween) wrote in
<3f*********************@pubnews.gradwell.net>:
>What do you mean when you use the word 'relation'?
The Jet structure that defines a relation, as represented in DAO.
There is no mechanism for designating or defining or even simply
indicating indexes as part of the relation definition. Indexes for
a relation are inherited from the fields in the relation itself,
but are not part of the relation definition *as implemented in
Jet*.
Yes, I agree that they are part of the definition of the
*relationship*. But they are not an inherent part of the Jet db
engine's structure that defines the relationship -- they are
secondarily inherited from the fields that participate in the
relation.
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
mi******************@btinternet.com (Mike MacSween) wrote in
<3f*********************@pubnews.gradwell.net>: "David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:93***************************@24.168.128.86. .. mi******************@btinternet.com (Mike MacSween) wrote in
<3f*********************@pubnews.gradwell.net>:
>What do you mean when you use the word 'relation'?
The Jet structure that defines a relation, as represented in
DAO.
There is no mechanism for designating or defining or even simply
indicating indexes as part of the relation definition. Indexes
for a relation are inherited from the fields in the relation
itself, but are not part of the relation definition *as
implemented in Jet*.
Yes, I agree that they are part of the definition of the
*relationship*. But they are not an inherent part of the Jet db
engine's structure that defines the relationship -- they are
secondarily inherited from the fields that participate in the
relation.
I meant "relation means table". Do you mean "relation means
relationship".
Well, we weren't talking Codd's theory. We were talking Jet, so I
was using Jet terminology, as I said repeatedly.
A relationship (the logical, relational model thing) has
participation conditions. Mandatory or optional at each end.
Whether Jet keeps a record of that as a 'property' of some
'relationship' object is up to Jet. I've made a participation
condition mandatory at the 'many' end of the relationship by
making the FK not allowed null.
And that's not part of the way Jet defines a relation. It's a
property of a field and determines what data can be in the foreign
key field, and is a property of the field, not of the relation.
In the wider sense, of course it's a property of the
"relationship." But in Jet, it's not part of the db engine's
definition of the relation, but a property of the field. Thus, it
is enforced at a different level within the engine than if it were
specifically part of the relation definition.
Now, it's a semantic question whether properties of the relation
inherited from the fields that participate in it are or are not
part of the relation. From an object-oriented point of view,
inherited properties are just as much a part of the encapsulating
object as the properties of the object not inherited from a
lower-level object.
At this point, other than making that distinction, I don't know
what my point was!
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
"Mike MacSween" <mi******************@btinternet.com> wrote in message
news:3f*********************@pubnews.gradwell.net. .. I don't know why I even
bothered asking the question in the first place.
Hey, you should've listen to me five days ago when I said you were a moron
and told you to get off the air.
"Murray Nelson" <mn****@alphalink.com.au> wrote in
news:3f******@news.alphalink.com.au: I've built a database using "Access '97" and it's running
well. I'm now wanting to write a macro which prints out a
report containing the number of records in some of the
queries. Is this possible?
Thanks in anticipation
Murray Nelson.
You don't need a macro to print a report. It's more work than just
doubleclicking on the report.
I suspect you really want to know how to count the number of
records in a query on your report.
Just create a report using design view, not bound to any query and
create a textbox on the report.
In the textbox, type =Dcount("fieldname","queryname")
Use a field name that will have no null values.
Create a textbox for each query you wish to count. Add/modify
labels as desired, save and run. My guess is that no one really needs the full relational model.
The ignorant are forced to guess because they lack the tools to know. They
generally guess wrong.
If you are in some way possess superior knowledge then perhaps you can do
better than hazard a guess and grace us with your relational DB acumen. I'm thinking the relational model is an abstract-academic-mathematical
thing, and we only need to use parts of it to get some value out of it.
No doubt, this is why you remain obstinately ignorant of the foundational
knowledge of your craft. In real professions, they jail people for
practicing without such knowledge.
Since it is not a real profession, then perhaps 'they' step down to
lynching.
"Anthony Garcia" <ag*******@go.com> wrote in message
news:o5**************@newssvr14.news.prodigy.com.. . My guess is that no one really needs the full relational model.
The ignorant are forced to guess because they lack the tools to know.
They generally guess wrong.
If you are in some way possess superior knowledge then perhaps you can do
better than hazard a guess and grace us with your relational DB acumen.
It takes no guessing to know that anyone engaged in data management needs
the full relational model. Whether they are sufficiently intelligent and
informed to recognize their own need is another matter.
"Bob Badour" <bb*****@golden.net> wrote in message
news:44********************@golden.net...
(snip) It takes no guessing to know that anyone engaged in data management needs
the full relational model.
That would come as a surprise to the 99% of IT professionals who *do not*
use the model to the full extent that you prefer!
TC
"TC" <a@b.c.d> wrote in message news:1066549817.619890@teuthos...
"Bob Badour" <bb*****@golden.net> wrote in message
news:44********************@golden.net...
(snip)
It takes no guessing to know that anyone engaged in data management
needs the full relational model.
That would come as a surprise to the 99% of IT professionals who *do not*
use the model to the full extent that you prefer!
You cut out the next point which is key. Widespread ignorance does not in
any way diminish the need.
"Bob Badour" <bb*****@golden.net> wrote in message
news:0P********************@golden.net... "TC" <a@b.c.d> wrote in message news:1066549817.619890@teuthos...
"Bob Badour" <bb*****@golden.net> wrote in message
news:44********************@golden.net...
(snip)
It takes no guessing to know that anyone engaged in data management needs the full relational model.
That would come as a surprise to the 99% of IT professionals who *do
not* use the model to the full extent that you prefer!
You cut out the next point which is key. Widespread ignorance does not in
any way diminish the need.
Would that be the primary key?
:-)
TC This discussion thread is closed Replies have been disabled for this discussion. Similar topics
34 posts
views
Thread by yensao |
last post: by
|
reply
views
Thread by Stylus Studio |
last post: by
|
1 post
views
Thread by Tim Fierro |
last post: by
|
6 posts
views
Thread by Shwetabh |
last post: by
|
7 posts
views
Thread by Fred Exley |
last post: by
|
13 posts
views
Thread by sulyokpeti |
last post: by
| | | | | | | | | | | | | | | | | | | |
