Re: Network Example: Sibling of Opposite Gender
Date: 7 Jan 2007 17:47:46 -0800
Message-ID: <1168220866.797182.88050_at_51g2000cwl.googlegroups.com>
Tonkuma, thanks for all your effort. You are in a class of people that includes the likes of Hugo Kornelis, Nick Malik and mAsterdam. I claimed the "Siblings of Opposite Build" Example (shown below) was unsolvable systematically in RM/RMDB. You have certainly showed it is neither impossible or impractical. Whether it is systematic according to RM, I will leave open for discussion.
Suppose Adam(30) has children named John(tall), Mary(short), Bob(fat), Sue(short/thin), Adam(10). Builds tall/short and fat/thin are opposites. Compose 4 queries (shown below), without explicitly referring to John's parent (Adam), John's build (tall) or relationships between builds in last query:
Below I have summarized/abbreviated the RMDB and dbd solutions to make them easier to compare. See earlier posts for details:
Sibling.person
id name age
1, 'adam', 30 2, 'john', NULL 4, 'mary', NULL 8, 'bob', NULL 9, 'sue', NULL
10, 'adam', 10
Sibling.link
parent child
1, 2 1, 4 1, 8 1, 9 1, 10
Sibling.build
'tall' 'athletic' 'petite' 'short' 'thin' 'fat'
Sibling.person_build
id build
2, 'tall' 4, 'short' 8, 'fat' 9, 'short' 9, 'thin'
Sibling.relationship
id verb
1, 'opposite'
Sibling.relation_value
id source target
1, 'tall', 'short' 1, 'short', 'tall' 1, 'fat', 'thin' 1, 'thin', 'fat' ------------------------------------------------------QUERY 1) Find John's siblings.
(!= (and (get person instance *)
(get (get * child john) child *)) john)
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;
QUERY 2) Find John's fat siblings.
(!= (and (get person instance *)
(get * build fat)
(get (get * child john) child *))
john)
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;
QUERY 3) Find John's siblings of opposite build.
(!= (and (get person instance *)
(get * build (get (get john build *) opposite *))
(get (get * child john) child *))
john)
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;
QUERY 4) Find 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).
(!= (and (get person instance *)
(get * build (get (get john build *) opposite *))
(get (get * child john) child *))
john)
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;
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);
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');
> Why is it necessary new sample? Extension to original sample will be better. It will demonstrate extendability and flexibility of dbd.
The updated "Siblings of Opposite Build" allowed each person's attribute to have multiple values and opposite values could not be determined by using the NOT function. I started a new example, instead of updating the old example, to make it easier to follow and compare.
When an expression (ie get person instance *) is executed from dbd's GUI, the tree view automatically expands and highlights each result step-by-step (ie john, mary, etc), even if they are 70-levels deep as in the Biological Taxonomy which includes approximately 35,000 entries. When an expression is executed from dbd's API, a cursor allows one to scroll thru the result set.
> So, I suspect that dbd is still in concept level or at the best general design level ... and hopefully he supply executable code (even alpha version is welcome).
Beta: www.dbfordummies.com/download/default.asp Received on Mon Jan 08 2007 - 02:47:46 CET