Re: Network Example: Sibling of Opposite Gender

> Although Cimode was on the right track, none of his queries produce the
> correct result. Can you specifiy the one you think does?
Here is his original query and corrections for it. (Original)
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

(Line 3) on P2.id = link.parent ---> on P2.id = link.child
(Line 4) select id ---> select parent
(Line 4) link inner join person P1 ---> link inner join person (remove

------------------------------ Commands Entered
------------------------------
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

                on link.parent = B.parent

NAME SEX
------ ------
Mary Female
Jack Male

  2 record(s) selected.

> (A minor note, in the original example, it is unknown if Adam is a male or female)
You wrote at the first
> Suppose Adam has children named John(male), Jack(male) and Mary(female)
and we want to find John's sibling of opposite gender without refering to John's father (Adam) .....

>From "John's father (Adam)", I thought Adam must be male.

If I knew gender is sometime unknown.
It is apparent that I did not specify NOT NULL. This is commonsense of RDBMS.
The value of a column is always known or sometime not is usually extracted from business rules.
But, if NULLable or not is unknown, it is safe not to specify NOT NULL. (I hope you had included sample data whose gender is unknown.)

Gender is almost every situation 'male' or 'female' in Japan. (Even privately, it is rare coming out other than 'male' or 'female'(gay, bisexual, etc.)
I didn't see or hear any government, organization or company record for gender other than 'male' or 'female', or unknown, in Japan. So, I thought gender is stable that means gender is one of two and always known, then I thought verb opposite is not necessary. If I knew gender is more flexible and sometimes unknown, I wouldn't specify NOT NULL for gender and not neglect verb opposite. (I hope you had explained the reason of verb opposite. Only I saw your example, I couldn't imagine necessity of opposite.)

Go back to first.
If I knew gender is NULLable and there is a possibility of adding more gender,
I would not neglect verb opposite and define tables and function like this.
CREATE TABLE Sibling.person
(name VARCHAR(7) NOT NULL
,gender VARCHAR(10) CONSTRAINT gender_value

                    CHECK(gender IN ('male', 'female')
                          OR
                          gender IS NULL)

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)

Actually, I wrote first this FUNCTION and Query. After that time, as I wrote before, I thought that possible value of gender is only two.
So, I thought create FUNCTION opposite is redundant.

Start from this new Definition and Query. I'll write equivalent SQL.

> (new 'age)

ALTER TABLE Sibling.person

      ADD COLUMN age SMALLINT
      ADD COLUMN age_of_father SMALLINT
      ADD COLUMN age_of_mother SMALLINT
      DROP CHECK gender_value;

> (set+ adam age '30)
UPDATE Sibling.person

   SET age = 30
 WHERE name = 'adam';

> (new 'adam 'person)
> (set+ (it) age '5)
> (set+ (it) gender 'bisexual)
> (set (and (get * name 'adam) (get * age 30)) child (it))
ALTER TABLE Sibling.person

      ADD CONSTRAINT gender_value
          CHECK (gender IN ('male', 'female', 'bisexual')
                 OR
                 gender IS NULL);

INSERT INTO Sibling.person
       (name, age, gender, father, age_of_father)

> (new 'adam 'person)
> (set+ (it) age '65)

INSERT INTO Sibling.person

       (name, age)
VALUES ('adam', 65);

> (new 'adam 'person)
> (set+ (it) age '7)
> (set (and (get * name 'adam) (get * age 30)) child (it))
> (set (and (get * name 'adam) (get * age 65)) child (it))
INSERT INTO Sibling.person

       (name, age, father, age_of_father)
VALUES ('adam', 7, 'adam', 30)
     , ('adam', 7, 'adam', 65);

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.

Amyway, query doesn't need to modify. Result is same.

------------------------------ Commands Entered
------------------------------
SELECT sibling.name AS "john's sibling"
  FROM Sibling.person john
     , Sibling.person sibling
 WHERE john.name      =  'john'

  1 record(s) selected. Received on Sat Dec 30 2006 - 05:03:22 CET