Re: Network Example: Sibling of Opposite Gender

From: Neo <neo55592_at_hotmail.com>
Date: 30 Dec 2006 13:28:22 -0800
Message-ID: <1167514102.585800.28970_at_s34g2000cwa.googlegroups.com>

> > [From prior Find Brother/Sister Thread] Cimode's query ..
> Corrected:
> select P2.name, P2.sex from person P2
> inner join link on P2.id = link.child
> inner join (select parent from
> link inner join person on child =
> person.id where person.name = 'John') B
> on link.parent = B.parent
> where P2.name <> 'John';

Thanks. I was able to verify the above in Access (replaced sub select with view). The purpose of the Find Brother/Sister Thread was to compare query expressions. Below is dbd's equivalent expression:

(!= (and (get person instance *)

            (get (get * child john) child *))
     john)

or even the following which does not restrict parents and children to being persons:

(!= (get (get * child john) child *) john)

Unlike the data network underlying the Find Brother/Sister Example which can be solved systematically with RM/RMDBs; the data network underlying this thread's example (Find Siblings of Opposite Gender) can't be solved systematically (ie without NULL, normalized, non-redundant, follows Information Principle, etc) with RM/RMDBs. Let's proceed with the example to verify this.

> > (A minor note, in the original example, it is unknown if Adam is a male or female)
> From "John's father (Adam)" [in example description], I thought Adam must be male.

You are correct, the example description referred to Adam as a father while the example data did not specify Adam's gender. I was inconsistent. Let Adam's gender be male. To keep the comparision easier, assume the following are the initial RMDB and dbd solutions.

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:

(new 'age) (new 'male 'gender) (new 'female 'gender)
(new 'opposite 'verb)
(set male opposite female) (set female opposite male)

(new 'adam 'person) (set adam gender male)    (set+ adam age '30)
(new 'john 'person)   (set john gender male)
(new 'jack 'person)   (set jack gender male)
(new 'mary 'person)  (set mary gender female)
(new 'adam 'person) (set+ (it) gender 'bisexual) (set+ (it) age '5)
(new 'adam 'person)                              (set+ (it) age '7)
(new 'adam 'person)                              (set+ (it) age '65)

(set  (and (get * name 'adam) (get * age 30))  child  john)
(set  (and (get * name 'adam) (get * age 30))  child  jack)

(set (and (get * name 'adam) (get * age 30)) child mary) (set (and (get * name 'adam) (get * age 30))

   child (and (get * name 'adam) (get * age 5))) (set (and (get * name 'adam) (get * age 30))

   child (and (get * name 'adam) (get * age 7))) (set (and (get * name 'adam) (get * age 65))

   child (and (get * name 'adam) (get * age 7)))

(; Get john's opposite gender siblings by getting persons

   whose gender is opposite and are children of john's parent    and that sibling is not himself. Gets Mary) (!= (and (get person instance *)

            (get * gender (get (get john gender *) opposite *))
            (get (get * child john) child *))
     john)

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.

(new 'xenon 'person)
(set+ (it) gender 'female)

(new 'pentium 'person)
(set+ (it) age '4086)
(set  (it) child xenon)
(set  (it) child john)

(new 'suzy 'person)
(set+ (it) gender 'female)

(new 'joeAnn 'person)
(set+ (it) gender 'male)
(set+ (it) gender 'female)
(set+ age '33)
(set (it) child suzy)
(set (it) child john)

Dbd's original query still works, however it now returns Mary, Xenon and Suzy. How can I do similar in the RMDB solution? (don't need to show the SQL to update the solution. The final table and query will be sufficient)

In addition, I now add that black and white are opposites. Then I get the thing whose relationship to black is the same as john's gender's relationship to mary's gender. Note that the query does not explicitly encode the relationship between the genders.

(new 'black 'color) (new 'white 'color) (set black opposite white) (set white opposite black)

(; Get the thing whose relationship to black

   is the same as john's gender's relationship to mary's gender.    Gets white.)
(get black (get (get john gender *) * (get mary gender *)) *)

How can I do the above in the RMDB solution without affecting the original query to find John's siblings of opposite gender? If not possible, go ahead modify schema/query. Received on Sat Dec 30 2006 - 22:28:22 CET