Re: Network Example: Sibling of Opposite Gender
Date: 6 Jan 2007 19:19:58 -0800
Message-ID: <1168139998.356681.160990_at_i15g2000cwa.googlegroups.com>
Neo wrote:
> Notice that the key query in "Find Siblings of Opposite Gender" and
> "Find Siblings of Opposite Build" are nearly identical as shown below:
>
> (!= (and (get person instance *)
> (get * gender (get (get john gender *) opposite *))
> (get (get * child john) child *))
> john)
>
> (!= (and (get person instance *)
> (get * build (get (get john build *) opposite *))
> (get (get * child john) child *))
> john)
>
> I am wondering if adapting your RMDB solution for "Opposite Gender" to
> "Opposite Build" will have any significant affect on its query.
>>
--> Neo: Given example of hierarchy 1, get me the RM solution for the
problem , here is the dbd solution...
--> Anybody: here is the solution to your problem, what do you conclude
from it (the solution is far more efficient)
--> Neo: Thanks. Let's add a handicap to the problem of hierarchy 1,
can you give me the solution RM wise...
--> Anybody: OK here's the solution...Do you understand why your
previous approach was flawed?
--> Neo: Given example of hierarchy 2, get me the RM solution for the
problem, here is the dbd solution....
AND SO FORTH.....
<<
I agree that.
Neo should first show us dbd's documents like Description and/or User's
Guide to let us understand general concept, language elements,
terminology, basic syntax, etc.
(I hope also he provide us Language reference Like "SQL Reference" of
Oracle or DB2.)
I searched dbd on Google. But, I couldn't check all of serach result,
Because, there are too many DBD. Most of them related interface to RDB
or related to genes.
After that, he can show us samples to let us better understanding of
dbd's capability.
His sample always source code only. No result of execution
showed(sometimes, he included in comment "(:Get somrthing".).
So, I suspect that dbd is still in concept level or at the best general
design level.
I'll not respond to dbd's sample anymore until he opened such documents
and hopefully he supply executable code(even alpha version is welcome).
This would be my last answer to dbd sample except correction to my
mistake.
>>
Table Sibling.person
Name Age Gender Father Age_of_Father Mother
Age_of_Mother
'adam' 30 'male' NULL NULL 'john' NULL 'male' 'adam' 30 'jack' NULL 'male' 'adam' 30 'mary' NULL 'female' 'adam' 30 'adam' 5 'bisexual' 'adam' 30 'adam' 65 NULL NULL NULL 'adam' 7 NULL 'adam' 30 'adam' 7 NULL 'adam' 65
CREATE FUNCTION opposite (gender VARCHAR(10))
RETURNS VARCHAR(10) LANGUAGE SQL RETURN CASE gender
WHEN 'female' THEN 'male'
WHEN 'male' THEN 'female' END;
SELECT sibling.name AS "john's sibling"
FROM Sibling.person john, Sibling.person sibling
WHERE john.name = 'john'
AND sibling.gender = opposite(john.gender)
AND sibling.father = john.father
AND sibling.name <> john.name;
I believe the above query needs to qualify john's parent with his age without directly encoding age 30, to keep it resilient to future data requirements. Below I condense dbd's equivalent: <<
You are right.
As I wrote before.
> Table Sibling.person is not 3NF(even not 2NF). Many RDB peoples may
> complaint it.
> At the first, I should have made TABLE child(like Cimode's link table),
> too. And made tables 3NF like Cimode did.
> More, it may be better to create gender table, and define referential
> integrity for it from person table than use CHECK constraint.
My first table design was not good.
More important, it violated principal of RDB that is Primary key should
be unique. I should not use name as Primary key.
I should use Suroggate key like Cimode did in the thread "How to find
Brothers and Sisters?"
http://groups.google.com/group/comp.databases.theory/browse_frm/thread/4b7ef46115e678a5/6b14f55aee063148?lnk=st&q=&rnum=1&hl=en#6b14f55aee063148
Although he wrote "name+sex uniquely identifies person", he used Suroggate key instead of making name+sex as Primary key. I thought that he did good choice.
> Below I give John two new sisters named Xenon and Suzy via two new and
> separate parents named Pentium and JoeAnn. Pentium's age is 4086 and
> gender is unknown. JoeAnn's age is 33 and it's gender is both male and
> female.
I can't understand one person have two gender 'male' and 'female'.
Anyway, my first table design included fatal mistakes(as I explained)
from the RDB design principals point of view.
So, I want discuss based on new design which is more conform to the RDB
design principals. I'll show RDB tables data and query later.
>>
Suppose Adam(30) has children named John(tall), Mary(short), Bob(fat),
Sue(short/thin), Adam(5). Builds tall/short and fat/thin are opposites.
We want to find the following, without explicitly referring to John's parent (Adam) or John's build (tall) directly:
1) John's siblings. 2) John's fat siblings. 3) John's siblings of opposite build. 4) Persons with builds with whom Bob's build has same relationship asJohn's build's relationship with Mary's build (without explicitly referring to that relationship in the query). Below dbd script implements the above example.
......
(new 'opposite)
(new 'john 'person)
(new 'mary 'person)
new 'age)
(new 'adam 'person) (set+ (it) age '30) (set (it) child john) (set (it) child mary)
.....
<<
Why is it necessary new sample?
Extension to original sample will be better.
It will demonstrate extendability and flexibility of dbd.
I will start following tables and query corresponding the example in
your first post.
Then extend them to your new requirements.
CREATE TABLE Sibling.gender
(gender VARCHAR(10) NOT NULL PRIMARY KEY
);
INSERT INTO Sibling.gender
VALUES 'male', 'female';
CREATE TABLE Sibling.person
(id INTEGER NOT NULL PRIMARY KEY
,name VARCHAR(7) NOT NULL
,gender VARCHAR(10) REFERENCES Sibling.gender
);
INSERT INTO Sibling.person
VALUES
(1, 'adam', 'male') ,(2, 'john', 'male') ,(3, 'jack', 'male') ,(4, 'mary', 'female')
CREATE TABLE Sibling.link
(parent INTEGER NOT NULL REFERENCES Sibling.person
,child INTEGER NOT NULL REFERENCES Sibling.person
,PRIMARY KEY (parent, child)
);
INSERT INTO Sibling.link
VALUES
(1, 2) ,(1, 3) ,(1, 4)
;
CREATE TABLE Sibling.relationship
(id INTEGER NOT NULL PRIMARY KEY
,verb VARCHAR(16) NOT NULL
);
INSERT INTO Sibling.relationship
VALUES (1, 'opposite')
;
CREATE TABLE Sibling.relation_value
(id INTEGER NOT NULL
,source VARCHAR(10) NOT NULL ,target VARCHAR(10) NOT NULL ,PRIMARY KEY (id, source)
);
INSERT INTO Sibling.relation_value
VALUES (1, 'male', 'female')
, (1, 'female', 'male')
Though, this function is not mandatory, it makes query statements
simple.
CREATE FUNCTION relationship
(inVerb VARCHAR(16), inSource VARCHAR(10), inTarget
VARCHAR(10))
RETURNS CHAR(1)
LANGUAGE SQL
READS SQL DATA
RETURN COALESCE((SELECT 'Y'
FROM Sibling.relationship rs
, Sibling.relation_value rv
WHERE rs.verb = inVerb
AND rv.id = rs.id
AND rv.source = inSource
AND rv.target = inTarget)
,'N')
;
0) John's sibling of opposite gender
------------------------------ Commands Entered
------------------------------
SELECT sibling.name AS "opposite gender"
FROM Sibling.person john
, Sibling.link k_j
, Sibling.link k_s
, Sibling.person sibling
WHERE john.name = 'john'
AND k_j.child = john.id
AND k_j.parent = k_s.parent
AND relationship('opposite', sibling.gender, john.gender) = 'Y'
AND k_s.child = sibling.id
;
opposite gender
mary
1 record(s) selected.
>>
Suppose the original Adam (now known to be age 30) has a new child also
named Adam whose age is 5 and his gender is bisexual. Also suppose, there is a second new child again named Adam age 7 who has two parents:
Adam (age 30) and new person also named Adam (age 65). And the gender
of little Adam (age 5) is unknown. Here is how to add them in dbd
without affecting the original query.
<<
ALTER TABLE Sibling.person
ADD COLUMN age SMALLINT
;
UPDATE Sibling.person
SET age = 30
WHERE id = 1;
INSERT INTO Sibling.gender
VALUES 'bisexual'
INSERT INTO Sibling.person
(id, name, age, gender)
VALUES (5, 'adam', 5, 'bisexual')
, (6, 'adam', 65, NULL)
, (7, 'adam', 7, NULL);
INSERT INTO Sibling.link
VALUES (1, 5)
, (1, 7)
, (6, 7)
------------------------------ Commands Entered
------------------------------
SELECT sibling.name AS "opposite gender"
FROM Sibling.person john
, Sibling.link k_j
, Sibling.link k_s
, Sibling.person sibling
WHERE john.name = 'john'
AND k_j.child = john.id
AND k_j.parent = k_s.parent
AND relationship('opposite', sibling.gender, john.gender) = 'Y'
AND k_s.child = sibling.id
;
opposite gender
mary
1 record(s) selected.
> Below I give John two new sisters named Xenon and Suzy via two new and
> separate parents named Pentium and JoeAnn. Pentium's age is 4086 and
> gender is unknown. JoeAnn's age is 33 and it's gender is both male and
> female.
I can't understand one person have two gender 'male' and 'female'.
So, I took 'female'. It'll be not influence the query result.
INSERT INTO Sibling.person
(id, name, age, gender)
VALUES (21, 'xenon', NULL, 'female')
, (22, 'pentium', 4086, NULL)
, (23, 'suzy', NULL, 'female')
, (24, 'joeAnn', 33, 'female');
INSERT INTO Sibling.link
VALUES (22, 21)
, (22, 2)
, (24, 23)
, (24, 2)
------------------------------ Commands Entered
------------------------------
SELECT *
FROM Sibling.person
ORDER BY id;
ID NAME GENDER AGE
----------- ------- ---------- ------
1 adam male 30
2 john male -
3 jack male -
4 mary female -
5 adam bisexual 5
6 adam - 65
7 adam - 7
8 bob - -
9 sue - -
10 adam - 10
21 xenon female -
22 pentium - 4086
23 suzy female -
24 joeAnn female 33
14 record(s) selected.
- Commands Entered
SELECT sibling.name AS "opposite gender" FROM Sibling.person john , Sibling.link k_j , Sibling.link k_s , Sibling.person sibling WHERE john.name = 'john' AND k_j.child = john.id AND k_j.parent = k_s.parent AND relationship('opposite', sibling.gender, john.gender) = 'Y' AND k_s.child = sibling.id ;
opposite gender
mary
xenon
suzy
3 record(s) selected.
DELETE FROM Sibling.link
WHERE parent > 20;
DELETE FROM Sibling.person
WHERE id > 20;
> Suppose Adam(30) has children named John(tall), Mary(short), Bob(fat),
> Sue(short/thin), Adam(5). Builds tall/short and fat/thin are opposites.
> We want to find the following, without explicitly referring to John's
> parent (Adam) or John's build (tall) directly:
> 1) John's siblings.
> 2) John's fat siblings.
> 3) John's siblings of opposite build.
> 4) Persons with builds with whom Bob's build has same relationship as
> John's build's relationship with Mary's build (without explicitly
> referring to that relationship in the query). Below dbd script
> implements the above example.
CREATE TABLE Sibling.build
(build VARCHAR(10) NOT NULL PRIMARY KEY
);
INSERT INTO Sibling.build
VALUES 'tall', 'athletic', 'petite', 'short', 'thin', 'fat';
INSERT INTO Sibling.relation_value
VALUES (1, 'tall', 'short')
, (1, 'short', 'tall')
, (1, 'fat', 'thin')
, (1, 'thin', 'fat')
CREATE TABLE Sibling.person_build
(id INTEGER NOT NULL REFERENCES Sibling.person
,build VARCHAR(10) NOT NULL REFERENCES Sibling.build
,PRIMARY KEY (id, build)
);
INSERT INTO Sibling.person
(id, name, age)
VALUES ( 8, 'bob', NULL)
, ( 9, 'sue', NULL)
, (10, 'adam', 10);
INSERT INTO Sibling.person_build
VALUES ( 2, 'tall')
, ( 4, 'short')
, ( 8, 'fat')
, ( 9, 'short')
, ( 9, 'thin');
INSERT INTO Sibling.link
VALUES (1, 8)
, (1, 9)
, (1, 10);
0) John's sibling of opposite gender.
------------------------------ Commands Entered
------------------------------
SELECT sibling.name AS "opposite gender"
FROM Sibling.person john
, Sibling.link link_john
, Sibling.link link_sibling
, Sibling.person sibling
WHERE john.name = 'john'
AND link_john.child = john.id
AND link_john.parent = link_sibling.parent AND relationship('opposite', sibling.gender, john.gender) = 'Y' AND link_sibling.child = sibling.id
;
opposite gender
mary
1 record(s) selected.
- John's siblings.
(; Get john's siblings
by getting persons
who are children of john's parent
and are not himself)
(; Gets mary, bob, sue, little adam)
(!= (and (get person instance *)
(get (get * child john) child *))
john)
- Commands Entered
SELECT sibling.id, sibling.name AS "john's sibling" FROM Sibling.person john , Sibling.link k_j , Sibling.link k_s , Sibling.person sibling WHERE john.name = 'john' AND k_j.child = john.id AND k_j.parent = k_s.parent AND sibling.id = k_s.child AND sibling.id <> john.id;
- Commands Entered
ID john's sibling
----------- --------------
3 jack
4 mary
5 adam
7 adam
8 bob
9 sue
10 adam
7 record(s) selected.
2) John's fat siblings.
(; Get john's fat siblings
by getting persons
whose build is fat
and are children of john's parent
and are not himself)
(; Gets bob)
(!= (and (get person instance *)
(get * build fat)
(get (get * child john) child *))
john)
- Commands Entered
SELECT sibling.name AS "fat siblings" FROM Sibling.person john , Sibling.link k_j , Sibling.link k_s , Sibling.person_build sib_bld , Sibling.person sibling WHERE john.name = 'john' AND k_j.child = john.id AND k_j.parent = k_s.parent AND sib_bld.id = k_s.child AND sib_bld.build = 'fat' AND sibling.id = k_s.child AND sibling.id <> john.id;
fat siblings
bob
1 record(s) selected.
3) John's siblings of opposite build.
(; Get john's siblings of opposite build
by getting persons
whose build is opposite of john's build
and are children of john's parent
and are not himself)
(; Gets mary and sue)
(!= (and (get person instance *)
(get * build (get (get john build *) opposite *))
(get (get * child john) child *))
john)
- Commands Entered
SELECT sibling.name AS "opposite build" FROM Sibling.person john , Sibling.person_build john_bld , Sibling.link k_j , Sibling.link k_s , Sibling.person_build sib_bld , Sibling.person sibling WHERE john.name = 'john' AND john_bld.id = john.id AND k_j.child = john.id AND k_j.parent = k_s.parent AND sib_bld.id = k_s.child AND relationship('opposite', sib_bld.build, john_bld.build) = 'Y' AND sibling.id = k_s.child AND sibling.id <> john.id;
opposite build
mary
sue
2 record(s) selected.
- Commands Entered
SELECT sibling.name AS "Sue's opposite build" FROM Sibling.person john , Sibling.person_build john_bld , Sibling.link k_j , Sibling.link k_s , Sibling.person_build sib_bld , Sibling.person sibling WHERE john.name = 'sue' AND john_bld.id = john.id AND k_j.child = john.id AND k_j.parent = k_s.parent AND sib_bld.id = k_s.child AND relationship('opposite', sib_bld.build, john_bld.build) = 'Y' AND sibling.id = k_s.child AND sibling.id <> john.id;
Sue's opposite build
john
bob
2 record(s) selected.
> 4) Persons with builds with whom Bob's build has same relationship as
> John's build's relationship with Mary's build (without explicitly
> referring to that relationship in the query). Below dbd script
> implements the above example.
(; Get persons with builds with whom bob's build relationship is the
same as
john's build relationship to mary's build)
(; Gets sue)
(get * build (get (get bob build *)
(get (get john build *) * (get mary build *))
*))
Although, this function is not mandatory, it makes query statements
simple.
CREATE FUNCTION find_verb (inSource VARCHAR(10), inTarget VARCHAR(10))
RETURNS VARCHAR(16)
LANGUAGE SQL
READS SQL DATA
RETURN (SELECT verb
FROM Sibling.relationship rs
, Sibling.relation_value rv
WHERE rv.id = rs.id
AND rv.source = inSource
AND rv.target = inTarget)
;
- Commands Entered
SELECT answer.name AS "Bob's bld same as john & mary" FROM Sibling.person john , Sibling.person_build john_bld , Sibling.person mary , Sibling.person_build mary_bld , Sibling.person bob , Sibling.person_build bob_bld , Sibling.person answer , Sibling.person_build answer_bld WHERE john.name = 'john' AND mary.name = 'mary' AND bob.name = 'bob' AND john_bld.id = john.id AND mary_bld.id = mary.id AND bob_bld.id = bob.id AND relationship(find_verb(john_bld.build, mary_bld.build), bob_bld.build, answer_bld.build) = 'Y' AND answer.id = answer_bld.id ;
Bob's bld same as john & mary
sue
1 record(s) selected. Received on Sun Jan 07 2007 - 04:19:58 CET