(new 'male 'gender)
(new 'female 'gender)
(new 'adam 'person)
(new 'john 'person)
(set john gender male)
(new 'jack 'person)
(set jack gender male)
(new 'mary 'person)
(set mary gender female)
(new 'child 'verb)
(set adam child john)
(set adam child jack)
(set adam child mary)
(; Get john's brothers
by getting persons
whose gender is male
and is child of john's father
and that person is not himself)
(; Gets jack)
(!= (and (get person instance *)
(get * gender male)
(get (get * child john) child *))
john)
(; Get john's sisters
by getting persons
whose gender is female
and is child of john's father
and that person is not himself)
(; Gets mary)
(!= (and (get person instance *)
(get * gender female)
(get (get * child john) child *))
john)
Something like this should do...
SK: Suroggate key
NN: Non NULL
name+sex uniquely identifies person
nodes: id(SK), name, sex
1(SK), Adam, Male
2(SK), John, Male
3(SK), Jack, Male
4(SK), Mary, Female
link:parent(FK), child(FK)
parent, child
1, 2
1, 3
1, 4
Getting John's brother and sisters without involving Adam in da query
-------------------------------------------------------------------------
select P2.name, P2.sex from person P2
inner join link
on P2.id = link.parent
inner join (select id from link inner join person P1 on child =
person.id where person.name = 'Jonh') B
on P2.id = B.id
where P2.name <> 'John'
)
Thx. I am having trouble determining if our solutions in RMDB/SQL are
equivalent. I have verified the following in MS Access. Are they
equivalent? (I realize above query is for brothers and sisters where as
the following is just for brothers)
SELECT P2.name
FROM ((person INNER JOIN link ON person.ID = link.childID)
INNER JOIN link AS link2 ON link.parentID = link2.parentID)
INNER JOIN person AS P2 ON link2.childID = P2.ID
WHERE (((P2.name)<>"John")
AND ((person.name)="John")
AND ((P2.sex)="Male"));
select P2.name, P2.sex
from person P2 inner join link on P2.id = link.parent
inner join (select id from link inner join person P1
on child = person.id where person.name = 'John') B
on P2.id = B.id
where P2.name <> 'John'
and P2.sex='Female'
When I try to run the above query in Access, I get the following
message: "Syntax error (missing operator) in query expression P2.id =
link.parent inner join .... P2.id = B.id"
What am I doing wrong? Below is the query for john's sisters that
executes ok.
SELECT P2.name
FROM ((person INNER JOIN link ON person.id = link.child)
INNER JOIN link AS link2 ON link.parent = link2.parent)
INNER JOIN person AS P2 ON link2.child = P2.id
WHERE (((P2.name)<>"John")
AND ((person.name)="John")
AND ((P2.sex)="Female"));
person
id name sex
1 Adam Male
2 John Male
3 Jack Male
4 Mary Female
link
parent child
Adam John
Adam Jack
Adam Mary
> > Just for sisters...
> > select P2.name, P2.sex
> > from person P2 inner join link on P2.id = link.parent
> > inner join (select id from link inner join person P1
> > on child = person.id where person.name = 'John') B
> > on P2.id = B.id
> > where P2.name <> 'John'
> > and P2.sex='Female'
>
> When I try to run the above query in Access, I get the following
> message: "Syntax error (missing operator) in query expression P2.id =
> link.parent inner join .... P2.id = B.id"
>
> What am I doing wrong? Below is the query for john's sisters that
> executes ok.
>
> SELECT P2.name
> FROM ((person INNER JOIN link ON person.id = link.child)
> INNER JOIN link AS link2 ON link.parent = link2.parent)
> INNER JOIN person AS P2 ON link2.child = P2.id
> WHERE (((P2.name)<>"John")
> AND ((person.name)="John")
> AND ((P2.sex)="Female"));
>
>
Forgot to mention that the query I provided is SQL Server compliant .I
do not think Access does JOIN on selects. You may need to package the
inner select in a view then do a JOIN on that view...
I packaged the inner expression in query Q_B as follows:
SELECT person.id
FROM link INNER JOIN person ON link.child = person.id
WHERE (((person.name)='John'));
Updated original query as follows:
select P2.name
from person P2 inner join link on P2.id = link.parent
inner join Q_B B on P2.id = B.id
where P2.name <> 'John'
and P2.sex='Female'
However I still get the previous missing operator error. The SQL Server
query may not be appropriate. At the very least, on the second line of
query, "P2.id = link.parent" should be "P2.id = link.child" because
mary joins to link table via link.child not link.parent. In addition,
the inner select should probably get parent's id from link table and
not john's id from person table. How does this query ensure that
siblings share a common parent? Could someone explain.
> > > Just for sisters...
> > > select P2.name, P2.sex
> > > from person P2 inner join link on P2.id = link.parent
> > > inner join (select id from link inner join person P1
> > > on child = person.id where person.name = 'John') B
> > > on P2.id = B.id
> > > where P2.name <> 'John'
> > > and P2.sex='Female'
> >
> > Forgot to mention that the query I provided is SQL Server compliant .I do not think Access does JOIN on selects. You may need to package the inner select in a view then do a JOIN on that view...
>
> I packaged the inner expression in query Q_B as follows:
> SELECT person.id
> FROM link INNER JOIN person ON link.child = person.id
> WHERE (((person.name)='John'));
> Updated original query as follows:
> select P2.name
> from person P2 inner join link on P2.id = link.parent
> inner join Q_B B on P2.id = B.id
> where P2.name <> 'John'
> and P2.sex='Female'
I realize I made a mistake...
Please repackage
<<
create view as Q_B
SELECT DISTINCT person.id
FROM link INNER JOIN person ON link.child = person.id
WHERE (((person.name)='John'))>>
then
select P2.name from person P2
inner join link on Q_B B on P2.parent = B.id
where P2.name <> 'John'
Should be sufficient to get all childs of John's parent... (If I am not
mistaken..)
> select P2.name from person P2
> inner join link on Q_B B on P2.parent = B.id
> where P2.name <> 'John'
--> This view gets all sibling's for a specific parent id then
restricts name or some operator..Cumulated view should be...
Hope this helps...
To get John's parent's id, shouldn't the query select link.parent
instead of person.id?
> This view gets all sibling's for a specific parent id then restricts name ...
> select P2.name from person P2
> inner join link
> on Q_B B
> on P2.parent = B.id
> where P2.name <> 'John'
P2 is an alias for person table, so P2.parent is really person.parent,
but the person table doesn't have a parent field (link table does)?
Also is joining person and link tables based on Q_B valid?
In addition to Cimode's answer, here is a slight variation:
[ltjn@lelles ~/src/parents]$ cat parent.sql
drop table persons;
create table persons (
name char(10) not null primary key,
father char(10) references persons on delete restrict,
gender char(1) not null check (gender in ('M','F'))
);
insert into persons (name, gender) values ('Adam', 'M');
insert into persons (name, father, gender)
values ('Mary','Adam','F'),
('John','Adam','M'),
('Jack','Adam','M');
[ltjn@lelles ~/src/parents]$ db2 "select * from persons p where
p.father = (select father from persons where name = 'John') and name <>
'John' and gender = 'F'"
NAME FATHER GENDER
---------- ---------- ------
Mary Adam F
1 record(s) selected.
[ltjn@lelles ~/src/parents]$ db2 "select * from persons p where
p.father = (select father from persons where name = 'John') and name <>
'John' and gender = 'M'"
NAME FATHER GENDER
---------- ---------- ------
Jack Adam M
1 record(s) selected.
/Lennart
Setting up hierarchies on a relational perspective is not a difficult
thing to do. Putting a parent attribute in person does not allow to
have a 1:0 cardinality between person and sibling , a common error
done...A person does not nessecarily have a parent while may or may not
have a sibling...In other words there is only one correct way to do
that RM wise...
Really, in what way do you think the schema violate 1NF?
/Lennart
My money is on the nullable father.
A RTable is a set of facts, true propositions. All predicates must be
validated by TRUE and represent the SAME segment of reality. In the
design proposed, it is highly likely that the highest level proposition
(also known as root) have the root node either be NULL or point to
itself as opposed to the non root node elements. Therefore it is
likely to be unormalized design. In a word, there's a 1:0 cardinality
between parent and sibling. A parent may or may NOT have sibling. A
sibling may or may NOT have a parent (the root does not have a parent
by definitions)...
Regards..;)
> /Lennart
I would think this is more a design flaw than the table not formally
being in 1NF. No repeating groups. check. Data in the form of a
relation. check. Some root entry in 'persons' who has himself or one of
his successors as a father... erm.....hold on...
(I think that Marshall once pointed out that the technical term for
this is Furturama-NF, where Fry is his own grandfather)
Repeating groups is not the only prerequisite to state a table is in or
is not in 1NF.
There is no way you can be in 1NF if NULL values are permitted OR if
you have 2 different predicates in the same RTable...Only one predicate
per RTable.. That's by definition. Period.
> I would think this is more a design flaw than the table not formally
> being in 1NF. No repeating groups. check. Data in the form of a
> relation. check. Some root entry in 'persons' who has himself or one of
> his successors as a father... erm.....hold on...
That *design flaw* is called a denormalized schema...;).
>> I would think this is more a design flaw than the table not formally
>> being in 1NF. No repeating groups. check. Data in the form of a
>> relation. check. Some root entry in 'persons' who has himself or one of
>> his successors as a father... erm.....hold on...
>>
>> (I think that Marshall once pointed out that the technical term for
>> this is Furturama-NF, where Fry is his own grandfather)
> Repeating groups is not the only prerequisite to state a table is in or
> is not in 1NF.
> There is no way you can be in 1NF if NULL values are permitted OR if
> you have 2 different predicates in the same RTable...Only one predicate
> per RTable.. That's by definition. Period.
Where did you get that definition?
Check...
06/03 #2: WHAT FIRST NORMAL FORM MEANS NOT by F. Pascal (Updated 03/04)
06/03 #1: WHAT FIRST NORMAL FORM REALLY MEANS by C. J. Date
couple of things. First, It annoys me these papers are not publically
available. I understand one needs to earn a crust, but these seem
fundamental issues and as such, it would be nice to see them out in the
open.
Second, as far as I am concerned 1NF requires data that fits into a
relation. Hence no nulls or repeating groups. However, given Codd
invented 1NF and was (in)famously a proponent of nulls, I am uncertain
that the currently accepted definition of 1NF yet precludes them. This
is sad of course, but we are still subject to these definitions, even
though we promote their change.
Third, there may be some confusion between schema and semantics. While
the supplied table allows a nullable column, and hence should not be
1NF (although by many definitions including Codd's it is), this is
wholly different from the knowledge we have that everyone must have a
father. The latter is the design flaw I am referring to and it is
important not to confuse the two in your answers to the OP.
While the proper treatment of missing information is problematic and
subject to considerable controversy, I do not recall that Codd's
original specification of 1NF allowed NULL.
Codd's RM/V2 had lots of stuff that a lot of people would find
controversial or would leave a lot of folks scratching their heads.
It would not be ridiculous to assume that Codd himself underestimated
the implications of his early discovery would put generations of people
into forrmulate new mathematics to grasp such implications.
Nevertheless, his work has been continued and several of his
assumptions are either meant to be validated or invalidated by
knowledgeable technical community. I do believe his later "tolerance"
to NULL values, as opposed to the fundamental formulation that NULLS
would break the link between mathematical set theory and computing, was
meant for a purpose that will remain obscure.
After few years thinking about it, I formulated the following
hypothesis:
> The later acceptance of NULLS was meant not to pull RM furthermore from its mathematical background to make it a independent area of research that could perdure on its own.
> The acceptance of NULLS was meant to trigger further research on RM limits and scope.
> Some other reason for which Codd's presence will be missed.
Given the probable assumption we are only at the early stage of
discovering RM, I find safer to stick to fundamental refusal of NULLS
until proven otherwise. After all, Codd has proven that NULL would
break binary logic but the opposite still remain unproven.
I would like to know where Codd spelled out just what a repeating group
is (not saying he didn't just that I haven't seen it). At the time he
wrote his first papers, there was a construction supported by the IBM
access methods as well as IBM's IMS, known by various names which
typically included the term "variable length" that encouraged a special
case of what we equate with relation-valued attributes today (even
though these days we might equate that stuff with the "physical" layer).
This stuff was rampant and encouraged something that he was battling,
application-specific data that could not be shared by other applications
unless one app distorted itself in favour of the other. (I've heard
that his first stroke was encouraged by antipathy within IBM towards his
stirring of the waters.)
Today, as we well know, most products confuse representation with
expression and tend to store the straightforward (and admittedly useful)
for the purposes of manipulation and so-called "persistent" memory.
Also today, Darwen and Date more or less insist that there are certain
relations that can't be represented without 'artifice', eg., additional
attributes that aren't inherent in their original data. My impression
is that Codd was not interested in these cases, preferring to require
that users supply such attributes. I think that is fair enough. But I
think he was also very focussed on having a very straightforward
representation for users to look at. It is equally fair to try to
imagine a logical representation that is not part of the "physical"
layer but which allows rva-based expressions that are compatible with
the straightforward one. In other words, I think such relations could be
expressed without their extensions necessarily being materialized.
p
Sorry for bumping in late. I don see how you can prevent this anomaly
without a closure operator (disregarding triggers, and such). Do you
have a reference to Marshall's post?
/Lennart
I'll say sorry for the same thing, and also that I think you may be
hinting at a much more challenging problem than brothers and sisters (at
least it is for me): given a bunch of parents and offspring, who may not
have names (Bob B's use of artificial identifiers is exactly right when
one is looking at genealogy - we all have a great-grandfather, but often
don't know his name, eg., if he wasn't married to the
great-grandmother), find all the cousin pairs. Not just that, but find
who is second cousin, once removed, to who.
(note - according to wikipedia, uncle is just a familiar term for a cousin!)
Although I don't spend much time on family trees, it really gets on my
nerves how people who do rely on hierarchical systems, eg., after they
enter a person into their system, the system can't tell them that stuff
unless they have already enumerated it all. Maybe I'm being unfair to
those systems; if so I'll shut up about the topic if somebody corrects
my impression.
p
Too bad that Dr. Codd allowed NULLs when he created the term.
Yep, right from the start in RM1 as rule #3:
3. Systematic treatment of null values
The DBMS is required to support a representation of "missing
information and inapplicable information" that is systematic, distinct
from all regular values (for example, "distinct from zero or any other
number," in the case of numeric values), and independent of data type.
It is also implied that such representations must be manipulated by the
DBMS in a systematic way.
Those rules were not 'from the start'; they aren't in the 1970 paper,
and were introduced in the 1980s, IIRC (certainly not earlier than the
late 1970s).
> 3. Systematic treatment of null values
>
> The DBMS is required to support a representation of "missing
> information and inapplicable information" that is systematic, distinct
> from all regular values (for example, "distinct from zero or any other
> number," in the case of numeric values), and independent of data type.
> It is also implied that such representations must be manipulated by the
> DBMS in a systematic way.
--
Jonathan Leffler #include <disclaimer.h>
Email: jlef...@earthlink.net, jlef...@us.ibm.com
Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/
> --CELKO-- wrote:
>
>>>>, I do not recall that Codd's original specification of 1NF allowed NULL. <<
>>
>>Yep, right from the start in RM1 as rule #3:
Joe wouldn't know RM1 if it bit him on the ass. Codd's original
specification of 1NF appeared in his 1969 and 1970 papers and had
absolutely nothing to do with the 12 rules some vendor paid him to publish.
Joe is living proof that, if one cannot dazzle with brilliance, one can
always baffle with bullshit instead.
>>3. Systematic treatment of null values
>>
>>The DBMS is required to support a representation of "missing
>>information and inapplicable information" that is systematic, distinct
>>from all regular values (for example, "distinct from zero or any other
>>number," in the case of numeric values), and independent of data type.
>>It is also implied that such representations must be manipulated by the
>>DBMS in a systematic way
>
> I agree CELCO (If I undestood rightly his wrote information).
[snip]
> More over, thers is a possibility SALARY is UNKNOWN. But it is KNOWN
> that it is already PAID or not.
Judge: "Why didn't you pay the plaintiff?"
Defendant: "We did pay the plaintiff."
Judge: "Really? How much did you pay the plaintiff?"
Defendant: "We don't know, but we know we paid him."
Oh yeah, that'll stand up in court. ::rolls eyes::
You are thinking of his OLAP paper, which Arbor software commissioned.
That paper was supposedly written by someone else on his staff and Dr.
Codd put his name on it. That "cut & paste" of mine was taken from
Chris Date's description of Codd's final rules for Version 1.0 of the
RM.
The NULL for "attribute exists, value unknown" and "attribute missing,
so value is impossible" did not come along until RM Version 2.0.
In his book DATABASE IN DEPTH, Date lists:
RM1: The 1970 CACM paper
RM2: 1981 Turing Award Paper
RM3: Codd's 12 rules in 1985 which locked down Version 1.0
RM4: The Version 2.0 book in 1990
Date then asserts that his Third Manifesto is The Only One and True RM
:)
>> Joe is living proof that, if one cannot dazzle with brilliance, one can always baffle with bullshit instead.<<
Thank you for your logical argument and documentation. Oh wait; there
was only an ad hominem attack.
>> Judge: "Why didn't you pay the plaintiff?"
Defendant: "We did pay the plaintiff."
Judge: "Really? How much did you pay the plaintiff?"
Defendant: "We don't know, but we know we paid him." <<
Knowing existence without particulars is a common situation. To carry
on with your courtroom example:
Judge: "How do you know this man was murdered?"
Detective: "People do not shoot themselves 10 times in the back of the
head and dismember their own corpse."
Judge: "Really? Who murdered him?"
Detective: "We do not know yet."
Bob: "If you don't know who did it, then this cannot be a murder!
You must have absolute knowledge!"
Yu might want to look at the problem of missing data in statistics and
Data Theory. Years ago SPARC lists well over a dozen kinds of missing
data in data and their computerized representations. Then along came
fuzzy math and logic (the CSI guy who can tell you that the prep was
left handed and about 5'10" tall, but still not give you his name),
various multi-valued logics and a host of other things. SQL and its
NULLs are very vanilla.
> Date then asserts that his Third Manifesto is The Only One and True RM
> :)
He doesn't say quite that. He writes in Databases, Types, and the
Relational Model (TTM), p. 3, "...the model as we describe it departs in no
essential respects from Codd's original version as documented in [three
papers from 1969, 1970 and 1972]".
The TTM is therefore only a declaration of his views about which of Codd's
publications are to be preferred, with his analysis.
Roy
See what I mean? Joe wouldn't know it if it bit him on the ass.
It is too bad that Date decided to freeze his mindset on those early,
"work-in-process" papers. Dr. Codd was a mathematician -- his first
pwork was on cellular automata -- and he could see the problems that
needed to be addressed with RM ver 1.0 and then in ver 2.0. Date
could not make the jump to even ver 1.0 and got left behind.
If I can find it there is a wonderful quote from Aristotle about
settling on distinct classes and two-valued logic as a convention altho
the real world does not have such clear distinctions and absolute
knowledge.
Mr. Celko, has someone been pissing in your cornflakes?
It could just as easily be reversed as that later people sway in
the wind. That does not sound any more convincing than your swipe. I
give it as an example and do not claim that it is so. What is your
excuse?
Sincerely,
Gene Wirchenko
Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.
> "-CELKO-" <jcel...@earthlink.net> wrote:
>
>>>>The TTM is therefore only a declaration of his views about which of Codd's publications are to be preferred, with his analysis.<<
>>
>>It is too bad that Date decided to freeze his mindset on those early,
>>"work-in-process" papers. Dr. Codd was a mathematician -- his first
>>pwork was on cellular automata -- and he could see the problems that
>>needed to be addressed with RM ver 1.0 and then in ver 2.0. Date
>>could not make the jump to even ver 1.0 and got left behind.
>>
>>If I can find it there is a wonderful quote from Aristotle about
>>settling on distinct classes and two-valued logic as a convention altho
>>the real world does not have such clear distinctions and absolute
>>knowledge.
>
> Mr. Celko, has someone been pissing in your cornflakes?
>
> It could just as easily be reversed as that later people sway in
> the wind. That does not sound any more convincing than your swipe. I
> give it as an example and do not claim that it is so. What is your
> excuse?
Actually, it is a fine example of Joe's typical modis operandi. He made
a career out of invoking Date's name in the trade rags--generally by
putting absolutely absurd words in Date's mouth. For some reason, Date
seems compelled to reply to such nonsense. Date's compulsive replies
elevated Joe's public image far above the image one might expect of an
ignorant fool like Joe.
Luckily, Date has the sense to stay far away from usenet.
Bob Badour <bba...@pei.sympatico.ca> wrote:
> Luckily, Date has the sense to stay far away from usenet.
Just like yourself?
Paul...
--
plinehan __at__ yahoo __dot__ __com__
XP Pro, SP 2,
Oracle, 10.2.0.1 (Express Edition)
Interbase 6.0.2.0;
When asking database related questions, please give other posters
some clues, like operating system, version of db being used and DDL.
The exact text and/or number of error messages is useful (!= "it didn't work!").
Thanks.
Furthermore, as a courtesy to those who spend
time analysing and attempting to help, please
do not top post.