sql - How to make a normalized table of adults and children? -
adults may have 0 or more nephews , nieces. children may have 0 or more aunts , uncles. how can model normalized if adults , children stored in 1 table?
this test question got wrong , clarification on. in advance!
if that's similar how question worded, i'm wondering if it's partially trick question.
all adults , children stored in 1 table
that say, of people's information needs in 1 table, relationship between them not have be?
person --- personid pk gender dateofbirth firstname lastname personrelationship --- id pk -- not needed, simple primary keys personid fk -- elder descendantid fk -- youngling
find nieces/nephews of specific person:
select * person p inner join personrelationship pr on p.personid = pr.personid inner join person descendant on pr.descendantid = descendant.personid p.personid = 1
find aunts/uncles of specific person:
select * person p inner join personrelationship pr on p.personid = pr.descendantid inner join person ancestor on pr.personid = ancestor.personid p.personid = 3
so example data have.
person 1, 'm', null, 'uncle', 'tom' 2, 'f', null, 'aunt', 'danielle' 3, 'm', null, 'nephew', 'realname' personrelationship 1, 1, 3 -- 1 ancestor 3, 3 descendant of 1 2, 2, 3 -- 2 ancestor 3, 3 descendant of 2
Comments
Post a Comment