Re: Network Example: Sibling of Opposite Gender
Date: Sat, 30 Dec 2006 16:07:14 GMT
Message-ID: <S8wlh.38705$cz.569469_at_ursa-nb00s0.nbnet.nb.ca>
Tonkuma wrote:
>>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
> where P2.name <> 'John'
>
> Corrections:
> (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
> P1)
> (Line 5) Jonh ---> John
> (Line 6) on P2.id = B.id ---> link.parent = B.parent
>
> (Corrected)
> ------------------------------ 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
> =
> person.id where person.name = 'John') B
> on link.parent = B.parent
> where P2.name <> 'John';
> ------------------------------------------------------------------------------
>
> 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.
Bite your tongue! It sure as hell is not safe to allow 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.)
Tonkuma, perhaps you misunderstand some english terms because it is a second language for you, but 'gay' and 'bisexual' are sexual orientations not genders. Other genders might include 'hermaphrodyte' or 'neuter', but even then most individuals identify themselves with a predominant gender.
Regardless, I agree that assuming male or female will suffice in this context.
> 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)
> ,father VARCHAR(7)
> ,mother VARCHAR(7)
> );
>
> 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
> ;
>
> 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)
Ahh, now I see where you got the nutty idea that 'bisexual' is a gender... 'nuff said.
>>(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)
> VALUES ('adam', 5, 'bisexual', 'adam', 30);
>
>
>>(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.
That's not all I would complain about.
> 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'
> AND sibling.gender = opposite(john.gender)
> AND sibling.father = john.father
> -- AND sibling.name <> john.name
> ;
> ------------------------------------------------------------------------------
>
> john's sibling
> --------------
> mary
>
> 1 record(s) selected.
Except the new design allows more than one john and more than one mary etc. Thus for correctness, the query would have to change. This particular result remained unchanged only incidentally. Different allowable data in the same schema would break it.
Assuming the following relations:
person = { { pid personid, name name, age integer }
, key{ pid }
, key{ name, age }
}
gender = { { pid personid, gender gender }
, key{ pid }
}
parentage = { { parent personid, child personid }
, key{ parent, child }
}
// using the . operator for natural join, | for restrict, // [] for project and () to disambiguate order of operations:
WITH kid = person . gender . ( parentage rename child as pid ),
sib = kid rename all but parent prepending 'sib',
(
( kid . sib . ( gender rename pid as parent, gender as pgen ) )
| pgen = 'male' and gender != sibgender
) [ sibname ]
;
You may notice a number of things above:
I introduced a so-called surrogate key for person but not for parentage. While { name, age } is (presumably) unique and familiar, it is neither simple nor stable. While { parent, child } is not simple, it is unique, familiar and stable.
I used 'gender' as a type name, attribute name and relation name. The type, attribute and relation are, of course, distinct. Likewise, the type and the attribute called 'name' are distinct.
I used the unstable age instead of the stable date of birth. I used age to follow the previous examples. Presumably, the relations above are views, and the dbms calculates age.
Assume the dbms has some support for automatically assigning personid. Further assume the dbms can correctly handle the following assignments:
WITH t = person . gender,
insert t { name: 'adam', age: 65, gender: 'male' } ;
WITH kid = person . gender . ( parentage rename child as pid ),
parent = ( person rename pid as parent ) . parentage,
t = ( kid . ( parent rename all but parent prepending 'p_' ) ),
insert t { ( name, age, gender, p_name, p_age ) <<<EOT
'adam', 30, 'male', 'adam', 65
'john', 40, 'male', 'adam', 65
'mary', 37, 'female', 'adam', 65
'adam', 5, 'male', 'adam, 30
EOT
; Received on Sat Dec 30 2006 - 17:07:14 CET